day01sql

数据类型

整型

int 4字节
tinyint 1字节
smallint 2字节
mediumint 3字节
bigint 8字节

int(11)和int(21)区别
存储空间和存储范围没有区别
只是补零不同

create table test(
  a int(11) zerofill,
  b int(21) zerofill
);

insert  into test(a, b) value(1,1);
select *from test;

浮点型

float(M,D) 4字节 单精度 不精确
double(M,D) 8字节 双精度 比float精度高

M总位数
D小数点右边位数

精度丢失
四舍五入

更精确的数字类型
decimal
M(1,254)总精度
N(0,60)
存储空间变长

---

存储性别,省份等分类信息选择tinyint,enum
bigint优先于int
金钱优先decimal

---

数据类型number

tinyint 1woman/2man 1字节

数据类型string
char(5) woman/man 5字节
char(1) W/M 1字节
enum enum(‘woman’,‘man’)

gbk(2字节) 两个汉字对应四个字节
utf8(3字节)
utf8mb4(4字节)

char和varchar

char存储定长(0-255个字符长度).容易空间浪费
varchar存储变长

text 65535字节,约64kb,在数据库中格式多为溢出页,效率不如char

时间类型

data三字节 年月日
time三字节 时分秒
timestamp四字节 年月日时分秒 1970–2038 时区自动转换
datatime八字节 年月日时分秒 1000-9999
一般用timestamp

MySQL常见的数据对象

• DataBase/Schema(一对一)
• Table
• Index
• View/Trigger/Function/Procedure

view:
视图 一组查询语句构成的结果集,虚拟结构并不是实际数据
视图能简化数据库的访问,能够将多个查询语句结构化为一个虚拟结构
视图隐藏数据库表结构,提高数据库安全性
视图是一种权限管理,只对用户提供部分数据

create view grade_view as select *from grade where `rank`='A';

trigger
trigger触发器,在数据写入表之前或之后进行操作
使用trigger在每次更新用户表的时候触发更新积分表

权限

show privileges ;

Alter	Tables	To alter the table
Alter routine	Functions,Procedures	To alter or drop stored functions/procedures
Create	Databases,Tables,Indexes	To create new databases and tables
Create routine	Databases	To use CREATE FUNCTION/PROCEDURE
Create temporary tables	Databases	To use CREATE TEMPORARY TABLE
Create view	Tables	To create new views
Create user	Server Admin	To create new users
Delete	Tables	To delete existing rows
Drop	Databases,Tables	To drop databases, tables, and views
Event	Server Admin	To create, alter, drop and execute events
Execute	Functions,Procedures	To execute stored routines
File	File access on server	To read and write files on the server
Grant option	Databases,Tables,Functions,Procedures	To give to other users those privileges you possess
Index	Tables	To create or drop indexes
Insert	Tables	To insert data into tables
Lock tables	Databases	To use LOCK TABLES (together with SELECT privilege)
Process	Server Admin	To view the plain text of currently executing queries
Proxy	Server Admin	To make proxy user possible
References	Databases,Tables	To have references on tables
Reload	Server Admin	To reload or refresh tables, logs and privileges
Replication client	Server Admin	To ask where the slave or master servers are
Replication slave	Server Admin	To read binary log events from the master
Select	Tables	To retrieve rows from table
Show databases	Server Admin	To see all databases with SHOW DATABASES
Show view	Tables	To see views with SHOW CREATE VIEW
Shutdown	Server Admin	To shut down the server
Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
Trigger	Tables	To use triggers
Create tablespace	Server Admin	To create/alter/drop tablespaces
Update	Tables	To update existing rows
Usage	Server Admin	No privileges - allow connect only

Data privilege

data:
select insert,update,delete

definition privilege

database:
create alter drop
table
create alter drop
view/function/trigger/procedure
create alter drop

administrator privilege

shutdown database
replication slave
replication client
file privilege

show grants ;

所有数据库下的所有表*.*

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

授权语句

连接mysql必要的条件
网络要通畅
用户名密码要正确
数据库需要加IP白名单
更细粒度的验证(库,表,列权限类型等)

1)show privileges;查看数据库权限信息

2)help grant;

3)create user ‘netease’@‘localhost’ indentified by ‘netease163’;

4)grant select on . to ‘netease’@‘localhost’ whith grant option;所有库下数据表

5)grant select on . to ‘netease’@‘localhost’ indentified by ‘netease163’ whith grant option;

6)show grants;

7)show grants for netease@‘localhost’;
回收权限
8)revoke select from . on netease@‘localhost’;
重新赋权
9)grant insert on . to netease@‘localhost’;

10)help drop user;

11)show create table mysql.user\G

12)select * from mysql.db where user=‘netease’\G

13)select * from mysql.tables_priv where user=‘netease’\G

create user 'aa'@'localhost' indentified by 'aa111';

grant select on *.* to 'aa'@'localhost' whith grant option;

grant select on *.* to 'aa'@'localhost' indentified by 'aa111' whith grant option;

show grants;
show grants for aa@'localhost';
revoke select on *.* from aa@'localhost';
grant insert on *.* to aa@'localhost';

MySQL权限信息存储在MySQL库中
MySQL最细粒度权限是字段级
权限表:user host db

三种方法创建用户赋权

1使用mysql自带的命令

先创建用户,再赋权
create user 'aaa'@'localhost' identified by 'aaa163'
grant select on *.* to 'aaa'@'localhost' with grant option;

2更改数据库记录
首先向user表里面插入一条记录,根据自己的需要选择是否向db和table_priv表插入记录
执行flush privileges 命令,让权限信息生效

3grand语句会判断是否存在该用户,不存在则新建,存在则改密码

grant select on *.* to 'aaa'@'localhost' identified by 'aaa163' with grant option;

更改用户的密码

• 用新密码,grant语句重新授权
• 更新数据库记录,update user表的password
• 注意:用这种方法更改完需要flush privileges刷新权限信息

删除用户

drop user ‘aaa’@‘locolhost’

在表或视图上执行insert delete update可以激发触发器

管理权限(server admin)
create user
replication client
shutdown

mysql 权限信息存储结构

mysql权限信息是存在数据库表中
mysql账号对应的密码也加密存储在数据库表中
每一种权限类型在元数据里都是枚举类型,表明是否有该权限
与权限相关的表
user
db
tables_priv
columns_priv
当执行一个查询语句,先在user查看用户名密码,如果有全局特权则执行查询
否则查看是否有db权限,有则查询
无则查询tables_priv,
之后是columns_priv

mysql权限上的问题

• 使用binary二进制安装管理用户没有设置密码
• mysql默认的test库不受权限控制,存在安全风险
  解决办法
  mysql_secure_installation

权限相关的操作不要直接操作表,统一使用mysql命名
使用二进制安装mysql后,需要重置管理用户root密码
线上数据库不要留test库

1)help create index;

2)help alter table;

3)CREATE TABLE `order` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `orderid` int(10) unsigned NOT NULL,

  `bookid` int(10) unsigned NOT NULL DEFAULT '0',

  `userid` int(10) unsigned NOT NULL DEFAULT '0',

  `number` tinyint(3) unsigned NOT NULL DEFAULT '0',

  `address` varchar(128) NOT NULL DEFAULT '',

  `postcode` varchar(128) NOT NULL DEFAULT '',

  `orderdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `status` tinyint(3) unsigned zerofill DEFAULT '000',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_orderid` (`orderid`),

  UNIQUE KEY `idx_uid_orderid` (`userid`, `orderid`),

  KEY `bookid` (`bookid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4)show create table 'order'\G

5)alter table `order` add primary key (id);

6)alter table `order` add unique key idx_uk_orderid (orderid);

7)alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY(userid) REFERENCES user(userid);

8)alter table `order` drop foreign key constraint_uid;

9)alter table `order` modify userid int(10) unsigned not null default '0';

10)alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY(userid) REFERENCES user(userid);(报错)

11)alter table `order` modify userid int(10) not null default '0';

12)alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY(userid) REFERENCES user(userid);(成功)

13)alter table `order_1` add CONSTRAINT constraint_uid FOREIGN KEY(userid) REFERENCES user_1(userid);(报错)

14)alter table `order_1` add CONSTRAINT constraint_uid_new FOREIGN KEY(userid) REFERENCES user_1(userid);(成功)

15)create view order_view as select * from `order` where status=1;

DROP TABLE IF EXISTS `tb_account`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_account` (

  `account_id` int(11) NOT NULL AUTO_INCREMENT,

  `nick_name` varchar(20) DEFAULT NULL,

  `true_name` varchar(20) DEFAULT NULL,

  `sex` char(1) DEFAULT NULL,

  `mail_address` varchar(50) DEFAULT NULL,

  `phone1` varchar(20) NOT NULL,

  `phone2` varchar(20) DEFAULT NULL,

  `password` varchar(30) NOT NULL,

  `create_time` datetime DEFAULT NULL,

  `account_state` tinyint(4) DEFAULT NULL,

  `last_login_time` datetime DEFAULT NULL,

  `last_login_ip` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`account_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;



--

-- Table structure for table `tb_goods`

--



DROP TABLE IF EXISTS `tb_goods`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_goods` (

  `goods_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `goods_name` varchar(100) NOT NULL,

  `pic_url` varchar(500) NOT NULL,

  `store_quantity` int(11) NOT NULL,

  `goods_note` varchar(800) DEFAULT NULL,

  `producer` varchar(500) DEFAULT NULL,

  `category_id` int(11) NOT NULL,

  PRIMARY KEY (`goods_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;



--

-- Table structure for table `tb_goods_category`

--



DROP TABLE IF EXISTS `tb_goods_category`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_goods_category` (

  `category_id` int(11) NOT NULL AUTO_INCREMENT,

  `category_level` smallint(6) NOT NULL,

  `category_name` varchar(500) DEFAULT NULL,

  `upper_category_id` int(11) NOT NULL,

  PRIMARY KEY (`category_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;



--

-- Table structure for table `tb_order`

--



DROP TABLE IF EXISTS `tb_order`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_order` (

  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `account_id` int(11) NOT NULL,

  `create_time` datetime DEFAULT NULL,

  `order_amount` decimal(12,2) DEFAULT NULL,

  `order_state` tinyint(4) DEFAULT NULL,

  `update_time` datetime DEFAULT NULL,

  `order_ip` varchar(20) DEFAULT NULL,

  `pay_method` varchar(20) DEFAULT NULL,

  `user_notes` varchar(500) DEFAULT NULL,

  PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;



--

-- Table structure for table `tb_order_item`

--



DROP TABLE IF EXISTS `tb_order_item`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_order_item` (

  `order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `order_id` bigint(20) NOT NULL,

  `goods_id` bigint(20) NOT NULL,

  `goods_quantity` int(11) NOT NULL,

  `goods_amount` decimal(12,2) DEFAULT NULL,

  PRIMARY KEY (`order_item_id`),

  UNIQUE KEY `uk_order_goods` (`order_id`,`goods_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;