3 - 字段约束｜MySQL索引｜MySQL用户管理

字段约束

设置在表头上，用来限制字段赋值
包括：

• 是否允许给字段赋null值 默认是允许赋null值
• 默认值 在不配置的时候，mysql服务默认分配的是null值
• 键值：就是设置在字段上的标签 每种标签都有各自的功能，默认字段没有标签
• 额外设置 字段可以自增长 默认没有自增长的设置

查看表的字段约束条件使用的命令：

mysql > desc db1.t1

建表的时候给表头设置默认值和不允许为空值

create table db1.t31(name char(10) not null,class char(7) default "xxx",likes set("money","game","film") not null default "film,music");

• 修改已有表中字段的默认值

create table db1.t34 (name char(10),age tinyint); # 建表是没有设置默认值和不允许为null
desc db1.t34

alter table db1.t34 modify name char(10) not null default "" ,modify age tinyint unsigned not null default 25; # 修改字段不允许为null 设置默认值

主键

查看表结构时 key 列包含

• 唯一索引 （unique）字段的唯一值（表头值不能重复 可以复null值）

create table DB1.t43(name char(10),hz_id char(18) unique); # 身份证号唯一 
mysql > insert into DB1.t43 values("bob",null)

• 主键 表头的值 不允许赋null 且不能重复
  表结构中key的标志是PRI
  
  语法格式一：

mysql> create table db1.t35(name char(10),hz_id char(10) primary key ,class cahr(10));

语法格式二：

mysql> create table db1.t36(name char(10),hz_id char(10),class char(10),primary key(hz_id))

删除主键命令格式 向表头下存储数据不受主键的限制

alter table db1.t36 drop primary key;

添加主键标签

alter table db1.t36 add primary key(hz_id)

• 复合主键
  多个表头一起做主键，复合主键字段的值不允许同时重复且不允许赋null值
  

mysql> create table db1.t37(cip varchar(15),port smallint ,status enum("allow","deny"),primary key(cip,port))

删除复合主键

alter table db1.t37 drop primary key; # 不能单个删除

添加复合主建

alter table db1.t37 add primary key(cip,port);

主键使用总结：无论是一个表头做主键 还是多个表头做主键 约束的方式都是不允许给表赋重复的值和null值

• 主键与auto_increment 连用
  当给表头设置了auto_increment属性后，插入记录时，如果不给表头赋值 表头会通过自加1的计算结果赋值
  要想让表头有自增长的功能，那么必须有主键的设置才可以。
  查看表结构时 在Extra位置显示

create table db1.t38(行号 int primary key auto_increment ,姓名 char(10),班级 char（7）,住址 char(10));
insert into db1.t39(姓名,班级,住址) values("bob","nsd2107","bg");
insert into db1.t39(姓名,班级,住址) values("bob","nsd2107","bg");

也可以自定义自增长 字段的值
自增长列 truncate后从1开始 delete继续编号

给已有表添加行号字段

alter table db1.t3 add id int primary key auto_increment first;

外键

外键的核心思想：保证数据的一致性
插入记录时，字段值在另一个表字段值范围内选择
外键使用规则：

• 表存储引擎必须时innodb
• 字段类型要一致
  外键：foreign key
  创建外键命令格式

create table 库.表名(表头列表,
foreign key(表头名)  # 指定外键
references 库.表(表头名) # 指定参考的表头
on update cascade  # 同步更新
on delete cascade # 同步删除
)engine=innodb;

创建工资表

create table db2.gz(gz_id int ,pay float(7,2),
foreign key(gz_id) references yg(yg_id)
on update cascade on delete cascade
)engine=innodb;

查看存储引擎

删除外键 通过外键名称，删除表头的外键设置

alter table db2.gz drop FOREIGN KEY gz_idfk_1(外键名)

在已有表里添加外键

alter table 库.表 add foreign key(表头名) references 库.表(表头名) on update cascade on delete cascade;

MySQL索引

索引介绍

给表头加了索引标签之后，会对表头下的数据生成排队信息保存
在表对应的文件里（表名.ibd）比如 给db1 库下t3表的表头加了索引
对应的存储文件是 /var/lib/mysql/db1/t3.ibd

优缺点

优点：

• 大大提高检索速度
• 减少服务器扫描的数据量
• 将随机io变成顺序io
• 可以帮助服务器避免排序和临时表
  缺点：
• 减慢表中修改速度
• 占用磁盘空间

索引使用规则

• 一个表中可以头多个索引
• 通常在where条件中的字段上配置索引
• 可以重复 且可以为null值
• index索引字段的标志为mul

索引的分类

• 普通索引 值给表中一个加索引 （index）
• 唯一索引 (unique)
• 全文索引 char archar text类型 数据量比较大的 (fulltext)
• 单列索引 (index(姓名))
• 多列索引 (index(姓名,年龄…))

索引的管理

• 创建普通索引（index）
  • 建表时创建索引

create database if not exists home; 
use home ;
create table tea4 (
id char(6) not null,
name varchar(6) not null,
age int(3) not null,
gender enum('boy','girl') default 'boy',
index(id),index(name)
);

查看新建tea4表的字段结构，可以发现两个非空索引字段的KEY标志为MUL：

mysql> DESC tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | NO   | MUL | NULL    |       |
| name   | varchar(6)         | NO   | MUL | NULL    |       |
| age    | int(3)             | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+

查看索引的详细信息

//查看详细信息
mysql> SHOW INDEX FROM tea4\G
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE                          //使用B树算法
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name                      //索引名称
 Seq_in_index: 1
  Column_name: name                           //字段名称
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 

• 删除索引

drop index id on home.tea4; # 删除name字段的索引

• 添加索引

create index nianling on tea4(age);      //针对指定字段创建索引

• 验证select是否使用到索引做查询 explain命令

explain select * from db1.t3 where name='sshd';

给db1库下的t3表加索引名

create index xingming on db1.t3(name)

用户管理

用户授权

• 什么是用户授权
  数据库管理员root用户登陆服务后，添加普通用户设置权限和用户密码。
  默认情况 只允许数据管理员root在本机访问数据服务 默认不允许其他客户端访问服务，也不能使用其他用户在本机
• 用户授权命令格式

grant 权限列表 on 库名 to 用户名@"客户端地址" identified by "密码" [with grant option](可以用grant添加权限)

库名：表示方式：

权限列表：

• ALL 表示所有权限 表示所有命令
• USAGE 表示无权限 （除了查看之外权限都没有 show desc ）
• SELECT、UPDATE、INSERT 表示只有个别权限
• SELECT、UPDATE（字段1，字段n）表示权限仅对指定字段有访问权限

用户名：添加用户时 自定义即可 存储在mysql库下user表的user字段下
客户端地址：网络中的哪些主机可以使用添加的用户连接数据库服务 表示的方式有：

• % 表示网络中的所有主主机
• 192.168.4.% 表示192.168.4网段内的所有主机
• 192.168.4.1 表示仅仅是192.168.4.1 一台主机
• localhost 表示数据库服务器本机

with grant option 让添加的用户也可以使用grant命令再添加用户，但用户本身要对mysql库有insert权限

权限撤销

• 删除已有授权用户的权限
• 库名必须和授权时的表示方式一样
  语法：

revoke 权限列表 on 库名 from 用户名@"客户端地址";

删除添加的用户

drop user 用户名@"客户端地址";

---

两台主机 50 数据库服务器 51客户端

1. 添加用户dba007，对所有库和所有表有完全权限、且有授权权限，密码为123qqq…A 客户端为网络中的所有主机。

mysql > grant all on *.* to dba007@"%" identified by "123qqq...a" with grant option;
# 查看已添加的用户权限
mysql > show grants for dba007@"%";
+---------------------------------------------------------------+
| Grants for dba007@%                                           |
+---------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba007'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+

登陆的用户查看自己的访问权限

mysql> show grants;

//查看可以使用root用来连接的客户端地址
mysql> select host,user from mysql.user where user="root";
+-------------+------+
| host        | user |
+-------------+------+
| 192.168.4.% | root | //192.168.4.0/24 网段所有主机
| localhost   | root |  //本机登录
+-------------+------+
2 rows in set (0.00 sec)

2. 允许192.168.4.0/24网段主机使用root连接数据库服务器，对所有库和所有表有完全权限、密码为123qqq…A

[root@host51 ~]# mysql -h192.168.4.50 -udba007 -p123qqq...a
mysql> grant all on *.* to root@"192.168.4.%" identified by "123qqq...a";
mysql> select user,host  from mysql.user;

3. 撤销root从本机访问权限，然后撤销

[root@host50 ~]# mysql -hlocalhost -uroot -pNSD123...a
//查看登录用户信息
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> revoke all on *.* from root@"localhost";
mysql> show grants;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost'                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

4. 恢复root本机登陆的权限

[root@dbsvr1 ~]# mysql -h192.168.4.50 -udba007 -p123qqq...A //dba007用户登录
//设置root用户本机登录的权限 
mysql> grant all on  *.* to root@"localhost" identified by "NSD123...a" with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW GRANTS FOR root@localhost;              //查看权限
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | //有了
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

5. 允许任意主机使用webuser用户连接数据库服务器，仅对tarena库有查询,插入,更新,删除记录的权限，密码为123qqq…A

//数据库管理员本机登录
[root@host50 ~]# mysql -hlocalhost -uroot -pNSD123...a
mysql> grant select,insert,update,delete on tarena.* to webuser@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for webuser@"%";  //查看webuser用户权限 ，对所有库表没有任何软件仅对tarena库有权限
+---------------------------------------------------------------------+
| Grants for webuser@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tarena`.* TO 'webuser'@'%' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

6. 撤销webuser用户权限，使其仅有查询记录权限。

//在50主机管理员登录
[root@host50 ~]# mysql -hlocalhost -uroot -pNSD123...a
mysql>
//撤销 webuser用户权限，使其仅有查询记录权限。
mysql> revoke insert,update,delete on tarena.* from webuser@"%";
Query OK, 0 rows affected (0.00 sec)
//查看webuser用户权限
mysql> show grants for webuser@"%";
+---------------------------------------------+
| Grants for webuser@%                           |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%'          |
| GRANT SELECT ON `tarena`.* TO 'webuser'@'%' | //只剩select权限了
+---------------------------------------------+
2 rows in set (0.00 sec)

7. 删除dba007用户

//查看已有的授权用户 是否有dba007用户
mysql> select user , host from mysql.user where user="dba007";
+--------+------+
| user   | host |
+--------+------+
| dba007 | %    |
+--------+------+
1 row in set (0.00 sec)
//删除dba007用户
mysql> drop user dba007@"%";
Query OK, 0 rows affected (0.00 sec)

用户权限追加

grant select on *.* to bob@"%" identified by "Ammm"
# 追加权限
grant insert on *.* to bob@'%'

授权库mysql库的使用：保存grant命令的执行结果 使用到了4张表 分别存储不同的授权信息

mysql> use mysql;
mysql> show tables;

可以通过查看表记录获取已有授权用户及访问权限
也可以修改表记录 修改授权用户的访问权限

user表的使用

查看当前数据库服务已有的用户

select host,user,authentication_string from mysql.user;
# user字段存储用户名
# host字段存储客户端地址
# authentication_string存储链接密码