• 1203、字段约束、主键、外键、mysql索引、mysql用户管理

    文章目录

    一、字段约束

    约束是一种限制，设置在字段上，用来控制字段的赋值。

    1、概述

    1.1 字段约束分类：

    1、PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。

2、NOT NULL ：非空，用于保证该字段的值不能为空。

3、DEFAULT：默认值，用于保证该字段有默认值。

4、UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。

5、 FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2 查看表的字段约束条件

    mysql>desc  库.表；  

mysql>desc db1.t3;
字段名      数据类型        |---------->约束条件<------------|
                            空    键值    默认值   额外设置
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | char(50)     | YES  |     | NULL    |       |
| password | char(1)      | YES  |     | NULL    |       |
| uid      | int(11)      | YES  |     | NULL    |       |
| gid      | int(11)      | YES  |     | NULL    |       |
| comment  | varchar(200) | YES  |     | NULL    |       |
| homedir  | varchar(60)  | YES  |     | NULL    |       |
| shell    | varchar(30)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    1.3 基本约束

    字段约束的使用

    建表时给表头设置默认和不允许赋null值

    mysql> create table db1.t31(
    -> name char(10) not null,
    -> class char(7) default "nsd2202",
    -> likes set("game","film","music","eat") not null default "music");
Query OK, 0 rows affected (0.24 sec)

mysql> desc db1.t31;
+-------+----------------------------------+------+-----+---------+-------+
| Field | Type                             | Null | Key | Default | Extra |
+-------+----------------------------------+------+-----+---------+-------+
| name  | char(10)                         | NO   |     | NULL    |       |
| class | char(7)                          | YES  |     | nsd2202 |       |
| likes | set('game','film','music','eat') | NO   |     | music   |       |
+-------+----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    # 验证默认值和不允许为null
mysql> insert into db1.t31 values(null,null,null);
ERROR 1048 (23000): Column 'name' cannot be null		# 表头name不允许赋null值

mysql> insert into db1.t31 values("bob",null,null);
ERROR 1048 (23000): Column 'likes' cannot be null		# 表头likes不允许赋null值

mysql> insert into db1.t31 values("bob",null,"game,eat");
Query OK, 1 row affected (0.04 sec)						# 符合约束不报错

mysql> insert into db1.t31(name) values("jim");
Query OK, 1 row affected (0.04 sec)						# 不赋值的表头使用默认值赋值

mysql> insert into db1.t31 values("bob","nsd2023","game,music");
Query OK, 1 row affected (0.07 sec)						# 根据需要自定义字段的值

mysql> select * from db1.t31;
+------+---------+------------+
| name | class   | likes      |
+------+---------+------------+
| bob  | NULL    | game,eat   |
| jim  | nsd2202 | music      |
| bob  | nsd2023 | game,music |
+------+---------+------------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    查看表结构时 key 列 包括普通索引 、唯一索引 、主键

    唯一索引 （unique） 约束的方式：

    表头的值唯一(表头的值不能重复) 但可以赋null 值

    mysql> create database if not exists DB1;
Query OK, 1 row affected (0.01 sec)
                           |--姓名-------|-------身份证号---------|
mysql> create table DB1.t41(name char(10),hz_id char(18) unique);
Query OK, 0 rows affected (0.40 sec)

mysql> desc DB1.t41;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(18) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into DB1.t41 values("bob",null);			# 赋null值 可以
Query OK, 1 row affected (0.04 sec)

mysql> insert into DB1.t41 values("tom",111222333);
Query OK, 1 row affected (0.05 sec)

mysql> insert into DB1.t41 values("jim",111222444);
Query OK, 1 row affected (0.05 sec)

mysql> insert into DB1.t41 values("john",111222444);
ERROR 1062 (23000): Duplicate entry '111222444' for key 'hz_id'		# 值重复 报错 
mysql> select * from DB1.t41;
+------+-----------+
| name | hz_id     |
+------+-----------+
| bob  | NULL      |
| tom  | 111222333 |
| jim  | 111222444 |
+------+-----------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    2、主键

    2.1 使用规则

    主键使用规则
1、 字段值不允许重复，且不允许赋NULL值
2、一个表中只能有一个primary  key字段
3、多个字段都作为主键，称为复合主键，必须一起创建
4、主键字段的标志是PRI
5、主键通常与auto_increment  连用
6、通常把表中唯一标识记录的字段设置为主键[记录编号字段] 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.2 建表时，创建主键

    格式一：
    create  table  库.表（ 字段名 类型  primary key  , 字段名 类型 , ..... ）;

    • 1
    mysql> create table db1.t35(
    -> name char(10),
    -> hz_id char(18) primary key,
    -> class char(10));
Query OK, 0 rows affected (0.32 sec)

mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(18) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 验证
mysql> insert into db1.t35 values("bob","123","nsd2202");	# 不重复也不是null可以
Query OK, 1 row affected (0.04 sec)

mysql> insert into db1.t35 values("tom",null,"nsd2202");	# 空不可以
ERROR 1048 (23000): Column 'hz_id' cannot be null
mysql> insert into db1.t35 values("tom",456,"nsd2202");
Query OK, 1 row affected (0.03 sec)

mysql> insert into db1.t35 values("bob","123","nsd2203");	# 与第1条hz_id重复不可以
ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

mysql> select * from db1.t35;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    格式二：
    create  table  库.表（ 字段名 类型 , 字段名 类型 , primary key（字段名） ）;

    • 1
    mysql> create table db1.t36(
    -> name char(10),
    -> hz_id char(18),
    -> class char(10),
    -> primary key(hz_id) );
Query OK, 0 rows affected (0.23 sec)

mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(18) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    2.3 删除主键

    # 向表头下存储数据不受主键的限制
mysql> alter  table   库.表   drop primary  key ;

    • 1
    • 2
    mysql> alter table db1.t36 drop primary key;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(18) | NO   |     | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    2.4 添加主键

     alter  table  库.表  add  primary key(表头名);

    • 1
    mysql> alter table db1.t36 add  primary key(hz_id);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(18) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    2.5 复合主键

    表中的多个表头一起做主键 ；

    符合主键必须一起创建,一起删除；

    复合主键的约束方式: 多条记录 主键字段的值不允许同时相同

    create table 库.表(
    字段列表,
    primary key(字段列表)
)

    • 1
    • 2
    • 3
    • 4
    mysql> create  table  db1.t38(cip   varchar(15) , port  smallint ,  
    -> status  enum("deny","allow") , primary key(cip,port)
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> desc db1.t38;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip    | varchar(15)          | NO   | PRI | NULL    |       |
| port   | smallint(6)          | NO   | PRI | NULL    |       |
| status | enum('deny','allow') | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+


# 测试
mysql> insert into  db1.t38  values ("1.1.1.1",22,"deny");
Query OK, 1 row affected (0.06 sec)
mysql> insert into  db1.t38  values ("1.1.1.1",22,"deny");       # 同时相同报错
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'
mysql> insert into  db1.t38  values ("1.1.1.1",80,"deny");
Query OK, 1 row affected (0.04 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    2.6 主键与auto_increment连用:

    ​ 当给字段设置了auto_increment属性后，插入记录时，如果不给字段赋值，字段会通过自加1的计算结果赋值。

    ​ 要想让字段有自增长，那么字段必须有主键的设置才可以；
    ​ 查看表结构时 ，在 Extra （额外设置） 位置显示。

    建表时,创建有auto_increment 属性的表头,
实现的效果如下:
行号  姓名   班级       住址
1	 bob   nsd2107    bj
2	 bob   nsd2107    bj 
3    bob   nsd2107    bj 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    格式一
    create     table   db1.t39 (
	行号  int   primary key  auto_increment, 
	姓名   char(10) , 
    班级  char(7) , 
    住址 char(10)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    格式二
    create     table   db1.t40 (
行号  int    auto_increment, 
姓名   char(10) , 班级  char(7), 
住址 char(10),primary key(行号)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    mysql> desc db1.t39;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| 行号   | int(11)  | NO   | PRI | NULL    | auto_increment |
| 姓名   | char(10) | YES  |     | NULL    |                |
| 班级   | char(7)  | YES  |     | NULL    |                |
| 住址   | char(10) | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+

mysql> desc db1.t40;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| 行号   | int(11)  | NO   | PRI | NULL    | auto_increment |
| 姓名   | char(10) | YES  |     | NULL    |                |
| 班级   | char(7)  | YES  |     | NULL    |                |
| 住址   | char(10) | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    mysql> insert into db1.t40(姓名,班级,住址)values("bob","nsd2202","bj");
Query OK, 1 row affected (0.08 sec)

mysql> insert into db1.t40(姓名,班级,住址)values("bob","nsd2202","bj");
Query OK, 1 row affected (0.05 sec)

mysql> insert into db1.t40(姓名,班级,住址)values("tom","nsd2202","bj");
Query OK, 1 row affected (0.03 sec)

mysql> select * from db1.t40;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | bob    | nsd2202 | bj     |
|      2 | bob    | nsd2202 | bj     |
|      3 | tom    | nsd2202 | bj     |
+--------+--------+---------+--------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    也可以给自增长字段的赋值
    mysql> insert into db1.t40(行号,姓名,班级,住址)values(5,"ha","nsd2202","bj");
Query OK, 1 row affected (0.04 sec)

mysql> select * from db1.t40;                                           +--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | bob    | nsd2202 | bj     |
|      2 | bob    | nsd2202 | bj     |
|      3 | tom    | nsd2202 | bj     |
|      5 | ha     | nsd2202 | bj     |
+--------+--------+---------+--------+
4 rows in set (0.00 sec)


# 从新字段值开始+1
mysql> insert into db1.t40(姓名,班级,住址)values("momo","nsd2202","bj"); 
Query OK, 1 row affected (0.04 sec)

mysql> select * from db1.t40;             # 从5开始+1                              
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | bob    | nsd2202 | bj     |
|      2 | bob    | nsd2202 | bj     |
|      3 | tom    | nsd2202 | bj     |
|      5 | ha     | nsd2202 | bj     |
|      6 | momo   | nsd2202 | bj     |
+--------+--------+---------+--------+
5 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    delete删除所有行，再添加行,续接行号，而不是从 1 开始
    mysql> delete from db1.t40;     # 删除所有行

# 再添加行,续接行号，而不是从 1 开始
mysql> insert into db1.t40(姓名,班级,住址)values("zhu","nsd2202","bj"); 
mysql> insert into db1.t40(姓名,班级,住址)values("zhu","nsd2202","bj");
mysql> insert into db1.t40(姓名,班级,住址)values("zhu","nsd2202","bj");

mysql> select * from db1.t40;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      7 | zhu    | nsd2202 | bj     |
|      8 | zhu    | nsd2202 | bj     |
|      9 | zhu    | nsd2202 | bj     |
+--------+--------+---------+--------+
3 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    用truncate删除行， 再添加行， 从1开始
    mysql> truncate table db1.t40;				# 用truncate删除行， 再添加行， 从1开始
Query OK, 0 rows affected (0.15 sec)

mysql> select * from db1.t40;
Empty set (0.00 sec)

mysql> insert into db1.t40(姓名,班级,住址)values("zhu","nsd2202","bj");
mysql> insert into db1.t40(姓名,班级,住址)values("liu","nsd2202","bj");


mysql> select * from db1.t40;					# 行号从1开始
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | zhu    | nsd2202 | bj     |
|      2 | liu    | nsd2202 | bj     |
+--------+--------+---------+--------+
2 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    给已有表添加行号字段

    （通常把表中唯一标识记录的字段作为主键， 就是行号字段）

    mysql> select  * from db1.t3;  # 没加行号前查看
 
# 给db1库下t3表,每行添加行号
mysql> alter   table   db1.t3    add   id   int    primary key  auto_increment  first;
 
mysql> select  * from db1.t3;  # 添加后查看 
mysql>  select  * from  db1.t3  where   id <= 3;   # 查找用户使用行号做查询条件,可以快速查找到指定的行

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    3、外建

    **核心思想：**保证数据的一致性

    插入记录时，字段值在另一个表字段值范围内选择。

    3.1 使用规则

    外键的使用规则：

    表存储引擎必须是innodb;

字段类型要一致;

被参照字段必须要是索引类型的一种(通常是 primary key).

    • 1
    • 2
    • 3
    • 4
    • 5

    3.2 命令

    （1）创建外键命令格式
    create table   库.表(表头列表 , 
foreign key(表头名)   			  # 指定外键
references 库.表(表头名)   		# 指定参考的表头名
on update  cascade  			# 同步更新
on  delete  cascade 			# 同步删除
)engine=innodb;					# 指定存储引擎

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    （2）删除外键

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

    通过修改表删除外建
mysql> alter table 库.表 drop FOREIGN KEY   外键名;

    • 1
    • 2
    （3）添加外键

    在已有表里添加外键

    # 格式：
mysql> alter table 库.表 add  foreign key(表头名)  references 库.表(表头名)
on update cascade  on delete cascade;

    • 1
    • 2
    • 3
    (4) 查看外键

    通过显示建表命令查看表的外键 ，并获取外键名称

    mysql> show create  table db1.gz \G

    • 1

    3.3 案例：

    提个需求： 只给公司里已经入职的员工发工资

    员工表 yg

    员工编号yg_id姓名name
    1bob
    2bob
    首先创建存储员工信息的员工表

    # 创建员工表

    mysql> create table db1.yg(
    -> yg_id int primary key auto_increment,
    -> name char(16))
    -> engine=innodb;


    • 1
    • 2
    • 3
    • 4
    • 5
    #查看表的存储引擎
    mysql> show create table db1.yg \G				
*************************** 1. row ***************************
       Table: yg
Create Table: CREATE TABLE `yg` (
  `yg_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(16) DEFAULT NULL,
  PRIMARY KEY (`yg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    #没有表记录

    mysql> select * from db1.yg;
Empty set (0.00 sec)


    • 1
    • 2
    • 3
    然后创建工资表

    工资表 gz

    员工编号 工资

    gz_id pay

    #创建工资表

    mysql> create table db1.gz(
    gz_id int, pay float(7,2), 
    foreign key(gz_id) references db1.yg(yg_id) 
    on update cascade  
    on delete cascade 
)engine=innodb;
Query OK, 0 rows affected (0.20 sec)


# 通过查看表结构查看不到外键的设置 MUL 是 普通索引的标志
mysql> desc db1.gz;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | YES  | MUL | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    #查看外建

    通过显示建表命令查看表的外键 ，并获取外键名称

    mysql> show create  table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    #删除外键

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

    通过修改表删除外建
mysql> alter table 库.表 drop FOREIGN KEY   外键名;

    • 1
    • 2
    mysql> alter table db1.gz drop foreign key gz_ibfk_1;

mysql> show create  table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    #添加外键

    在已有表里添加外键

    # 格式：
mysql> alter table 库.表 add  foreign key(表头名)  references 库.表(表头名)
on update cascade  on delete cascade;

    • 1
    • 2
    • 3
    mysql> alter table db1.gz add  foreign key(gz_id)  references db1.yg(yg_id)  
     on update cascade  on delete cascade ;


mysql> show create  table db1.gz \G                                     *************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    外键验证：
    1   外键字段的值必须在参考表字段值范围内
2   验证同步更新（ on update cascade)
3   验证同步删除（ on delete cascade) 

    • 1
    • 2
    • 3
    1.外键字段的值必须在参考表字段值范围内
    mysql> insert into db1.yg(name) values("jerry"),("tom");
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
+-------+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.gz values(1,3000);
Query OK, 1 row affected (0.03 sec)

mysql> insert into db1.gz values(2,3000);
Query OK, 1 row affected (0.04 sec)

mysql> select * from db1.gz;
+-------+---------+
| gz_id | pay     |
+-------+---------+
|     1 | 3000.00 |
|     2 | 3000.00 |
+-------+---------+
2 rows in set (0.00 sec)

mysql> insert into db1.gz values(3,3000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> insert into db1.yg(name) values("lucy");
Query OK, 1 row affected (0.05 sec)

mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     3 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into db1.gz values(3,3000);
Query OK, 1 row affected (0.05 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    2.验证同步更新（ on update cascade)
    mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     3 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)

mysql> update db1.yg set yg_id=9 where yg_id=3;			# 把yg表里编号是3的改成9 

mysql> select * from db1.yg;			# 工资表里编号是 3 的自动变成 9
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     9 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    3.验证同步删除（ on delete cascade)
    mysql> select * from db1.yg;		# 删除前查看员工表记录
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     9 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)

mysql> delete from db1.yg where yg_id=2;		# 删除编号是2的员工
Query OK, 1 row affected (0.03 sec)

mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     9 | lucy  |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from db1.gz;			# 查看工资表也没有编号是2的工资了
+-------+---------+
| gz_id | pay     |
+-------+---------+
|     1 | 3000.00 |
|     9 | 3000.00 |
+-------+---------+
2 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    被参考的表不能删除
    mysql> drop table db1.yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails


    • 1
    • 2
    • 3

    给gz表的gz_id表头 加主键标签

    保证每个员工只能发1遍工资 且有员工编号的员工才能发工资

    mysql> alter table db1.gz add primary key(gz_id);	

# 如果重复发工资和没有编号的发工资了要delete form db1.gz;

mysql> insert into  db1.gz values(1,50000);
mysql> insert into  db1.gz values(9,50000);

mysql> insert into  db1.gz values(9,50000);
ERROR 1062 (23000): Duplicate entry '9' for key 'PRIMARY'
mysql> insert into  db1.gz values(1,50000);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into  db1.gz values(null,50000);
ERROR 1048 (23000): Column 'gz_id' cannot be null


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    二、mysql索引

    1、索引概述

    1.1什么是索引

    **大白话：**给表头加了索引标签之后，会对表头下的数据生成排队信息保存在表对应的文件里(表名.ibd) ；

    ​ 比如给db1库下t3表的表头加了索引 ，对应的存储文件是/var/lib/mysql/db1/t3.ibd 。

    官方对索引的介绍：

    是帮助MySQL高效获取数据的数据结构。

    为快速查找数据而排好序的一种数据结构。

    类似书的目录 。

    可以用来快速查询表中的特定记录，所有的数据类型都可以被索引。

    Mysql索引主要有三种结构：Btree、B+Tree 、Hash 。

    1.2 索引的优点

    可以大大提高MySQL的检索速度；

    索引大大减小了服务器需要扫描的数据量；

    索引可以帮助服务器避免排序和临时表；

    索引可以将随机IO变成顺序IO.

    1.3 索引的缺点

    虽然索引大大提高了查询速度，同时却会降低更新表的速度，

    如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。

    建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

    如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

    对于非常小的表，大部分情况下简单的全表扫描更高效。

    1.4 索引的分类：

    普通索引 (index)

    不应用任何限制条件的索引，该索引可以在任何数据类型中创建。

    字段本身的约束条件可以判断其值是否为空或唯一。

    创建该类型索引后，用户在查询时，便可以通过索引进行查询。

    唯一索引 (unique)

    使用UNIQUE参数可以设置唯一索引。

    创建该索引时，索引的值必须唯一。

    通过唯一索引，用户可以快速定位某条记录。

    主键是一种特殊唯一索引。

    全文索引 (FULLTEXT)

    使用FULLTEXT参数可以设置索引为全文索引。

    全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。

    查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

    在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，

    可以执行大小写敏感的全文索引。

    单列索引

    顾名思义，单列索引即只对应一个字段的索引。

    应用该索引的条件只需要保证该索引值对应一个字段即可。

    可以包括普通、唯一、全文索引。

    多列索引

    多列索引是在表的多个字段上创建一个索引。

    该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。

    要想应用该索引，用户必须使用这些字段中的第一个字段。

    2、普通索引(index)的管理

    普通索引(index)的使用规则（需要牢记）具体如下：

    一个表中可以有多个index；

    字段的值可以重复，且可以赋值为null；

    通常在where条件中的字段上配置Index；

    index索引字段的标志为MUL。

    2.1 创建普通索引(index)

    建表时创建索引命令格式

    CREATE TABLE  库.表(
字段列表 ,
INDEX(字段名) ,
INDEX(字段名) ,
);

    • 1
    • 2
    • 3
    • 4
    • 5
    CREATE TABLE db1.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)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.2 查看索引

    （1）查看表头是否有索引
    desc   库.表；

mysql> desc db1.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     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> system ls /var/lib/mysql/db1/tea4.ibd		# 保存排队信息的文件
/var/lib/mysql/db1/tea4.ibd


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    （2）查看索引详细信息
    show  index  from  库.表；

    • 1
    mysql> show index from db1.tea4\G		# \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		# 排队算法
      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: 
2 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    2.3 删除索引

    命令格式   
DROP  INDEX   索引名   ON  库.表；

    • 1
    • 2
    mysql> drop index id on db1.tea4;			# 删除索引 id   # 索引名:key_name=id


mysql> desc db1.tea4;						# 查看索引	# name的key=MUL
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | NO   |     | NULL    |       |
| name   | varchar(6)         | NO   | MUL | NULL    |       |
| age    | int(3)             | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> show index from db1.tea4\G			# 查看详细索引信息
*************************** 1. 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: 
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    2.4 添加索引

    在已有表添加索引命令格式

    CREATE  INDEX  索引名  ON  库.表(字段名)；

    • 1
    mysql> create index nianling on db1.tea4(age);

mysql> desc db1.tea4;

mysql> show index from db1.tea4\G


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    2.5 explain命令

    可以查看执行的查询select语句， 是否使用到索引做查询了

    mysql> desc db1.t3;     	# 所有表头都没有MUL 标记
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | char(50)     | YES  |     | NULL    |                |
| password | char(1)      | YES  |     | NULL    |                |
| uid      | int(11)      | YES  |     | NULL    |                |
| gid      | int(11)      | YES  |     | NULL    |                |
| comment  | varchar(200) | YES  |     | NULL    |                |
| homedir  | varchar(60)  | YES  |     | NULL    |                |
| shell    | varchar(30)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)


mysql> select count(*) from db1.t3;  		# 查看表的总行数
+----------+
| count(*) |
+----------+
|       22 |
+----------+
1 row in set (0.00 sec)




mysql> select * from db1.t3 where name="sshd";
+----+------+----------+------+------+-------------------------+-----------------+---------------+
| id | name | password | uid  | gid  | comment                 | homedir         | shell         |
+----+------+----------+------+------+-------------------------+-----------------+---------------+
| 18 | sshd | x        |   74 |   74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
+----+------+----------+------+------+-------------------------+-----------------+---------------+
1 row in set (0.00 sec)




mysql> explain select * from db1.t3 where name="sshd";    # 使用没有索引的表头做查询条件 找1条记录也要遍历整张表的所有行（key 、rows）
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   22 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)



mysql> create index   xingming  on  db1.t3(name); 			# 给db1库下的t3表的name表头下的数据创建排队信息,索引名叫xingming
mysql> desc db1.t3;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | char(50)     | YES  | MUL | NULL    |                |
| password | char(1)      | YES  |     | NULL    |                |
| uid      | int(11)      | YES  |     | NULL    |                |
| gid      | int(11)      | YES  |     | NULL    |                |
| comment  | varchar(200) | YES  |     | NULL    |                |
| homedir  | varchar(60)  | YES  |     | NULL    |                |
| shell    | varchar(30)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)


mysql> explain select * from db1.t3 where name="sshd";  		# 不会遍历所有行 (key 、rows)
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | ref  | xingming      | xingming | 51      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> select * from db1.t3 where name="sshd";
+----+------+----------+------+------+-------------------------+-----------------+---------------+
| id | name | password | uid  | gid  | comment                 | homedir         | shell         |
+----+------+----------+------+------+-------------------------+-----------------+---------------+
| 18 | sshd | x        |   74 |   74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
+----+------+----------+------+------+-------------------------+-----------------+---------------+
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82

    三、mysql用户管理

    1、用户授权

    1.1 什么是用户授权：

    数据库管理员root用户连接数据库服务后，添加普通用户、设置用户权限和用户密码。

    1.2 为什么要学习用户授权：

    默认情况，只允许数据管理员root 在本机访问数据服务。 默认不允许其他客户端访问服务 也不能使用其他用户在本机连接数据库服务

    1.3 用户授权命令格式

    mysql>  grant 权限列表 on 库名  
		to  用户名@"客户端地址"  
		identified by “密码”  
		WITH GRANT OPTION;        		# 授权权限可选项目

    • 1
    • 2
    • 3
    • 4

    1.4 参数说明

    （1）权限列表 及说明

    添加的用户对指定的库名具有的访问权限表示方式(就是学过的sql命令)

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

    权限说明

    命令权限命令权限
    select查看表记录process执行show processlist 和kill命令
    insert插入表记录file导入数据
    update更新表记录references创建外键
    delete删除表记录index索引的创建和删除
    crreate建库、表alter修改表
    drop删除库表视图索引show databases查看已有数据库
    reload可以执行flush[tables | logs privileges]super可以使用change master，purge master logs，set，终止查询
    shutdown执行mysql> shutdown 停止mysql服务execute调用函数、存储过程
    create user创建用户lock tables加锁和解锁
    event可以管理定时任务create routine创建函数、存储过程
    trigger可以管理触发器replication slave读取主服务器二进制日志
    create tablespace创建表空间replication client查看主/从服务器状态
    create temporary tables创建临时表create view创建视图
    alter routine修改函数、存储过程show view查看视图
    （2）库名

    使用户对服务器上的哪些库有访问权限？

    库名说明
    *** . ***所有库所有表，第1个星表示所有库名，第2个星表示所有表名
    库名.*表示一个库下的所有表 例如 tarena.*
    库名.表名表示一张表 例如 tarena.user
    （3）用户名

    添加用户时自定义即可，存储在mysql库下user 表的user字段下

    （4）客户端地址

    表示网络中的那些主机可以使用添加的用户连接数据库服务

    表示的方式有：

    表示方式说明
    %表示网络中的所有主机
    192.168.4.%表示 192.168.4网段内的所有主机
    192.168.4.1表示仅仅是192.168.4.1 一台主机
    localhost表示数据库服务器本机
    （5）密码

    添加的用户连接数据服务时使用的密码 ，要符合数据库服务的密码策略

    （6）WITH GRANT OPTION ：

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

    1.5 授权库

    mysql库 ： 存储用户权限信息。

    使用到了4张表，分别存储不同的授权信息

    表名描述
    user表保存已有的授权用户及权限
    db表保存已有授权用户对数据库的访问权限
    tables_priv表记录已有授权用户对表的访问权限
    columns_priv表保存已有授权用户对字段的访问权限

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

    mysql> use  mysql;
mysql> show tables;

    • 1
    • 2

    user表 #保存已有的授权用户及权限 （全局权限 权限all 库名 .）

    例如: grant  all on *.*  to pljadmin@"%" 

identified by "123qqq...A" with grant  option；


    • 1
    • 2
    • 3
    • 4

    db表 #保存已有授权用户对数据库的访问权限

    例如: grant  all on gamedb.* to adminONE@"%" identified by "123qqq...A" ;


    • 1
    • 2

    tables_priv表 #记录已有授权用户对表的访问权限

    create  database  bbsdb;

create table  bbsdb.t1(name char(10) , age int);

create table  bbsdb.t2(name char(10) , id int);

例如：grant  select,insert on bbsdb.t1 to adminTWO@"%" identified by "123qqq...A";

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    columns_priv表 #保存已有授权用户对字段的访问权限

    例如：grant  select,update(name) on bbsdb.t2 to adminthr@"%" identified by "123qqq...A";

    • 1
    (1) user 表的使用

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

    select host,user,authentication_string from mysql.user;

# user字段存储用户名
# host字段存储客户端地址
# authentication_string 字段存储连接密码(加密的密码)

    • 1
    • 2
    • 3
    • 4
    • 5
    mysql> select  host,user from  mysql.user;  查看表记录
mysql> select * from  mysql.user where user="pljadmin" \G  查看所有列
*************************** 1. row ***************************
                  Host: %
                  User: pljadmin
           ......此处省略一万行......
            Grant_priv: Y
		   ......此处省略一万行......
                plugin: mysql_native_password
 authentication_string: *F19C699342FA5C91EBCF8E0182FB71470EB2AF30
      password_expired: N
 password_last_changed: 2021-11-09 16:31:07
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    mysql> update mysql.user set  Grant_priv="N" where user="pljadmin";  修改表记录
mysql> flush privileges;  			# 刷新权限，不刷新不生效
mysql> show grants for pljadmin@"%"; 查看用户权限
+-----------------------------------------------+
| Grants for pljadmin@%                         |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'pljadmin'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select  * from  mysql.user where  user="pljadmin" \G   #查看表记录
*************************** 1. row ***************************
                  Host: %
                  User: pljadmin
           ......此处省略一万行......
            Grant_priv: N
		   ......此处省略一万行......
                plugin: mysql_native_password
 authentication_string: *F19C699342FA5C91EBCF8E0182FB71470EB2AF30
      password_expired: N
 password_last_changed: 2021-11-09 16:31:07
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    (2) db表的使用
    mysql> select host,user,db from  mysql.db;   	# 查看表记录 
+-----------+-----------+--------+
| host      | user      | db     |
+-----------+-----------+--------+
| %         | adminONE  | gamedb |
| localhost | mysql.sys | sys    |
+-----------+-----------+--------+
2 rows in set (0.00 sec)


mysql> select  * from  mysql.db where db="gamedb" \G 	 # 查看表记录 
*************************** 1. row ***************************
                 Host: %
                   Db: gamedb
                 User: adminONE
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          .....此处省略一万行.....

mysql> update mysql.db set  delete_priv="N",update_priv="N" where user="adminONE";  修改满足条件字段的值
mysql> flush privileges;
 
mysql> select  * from  mysql.db where db="gamedb"  \G      # 查看表记录 
*************************** 1. row ***************************
                 Host: %
                   Db: gamedb
                 User: adminONE
          Select_priv: Y
          Insert_priv: Y
          Update_priv: N
          Delete_priv: N

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    (3) tables_priv表的使用
    mysql> select  * from  mysql.tables_priv;#查看表记录  
+-----------+-------+-----------+------------+----------------+---------------------+---------------+-------------+
| Host      | Db    | User      | Table_name | Grantor        | Timestamp           | Table_priv    | Column_priv |
+-----------+-------+-----------+------------+----------------+---------------------+---------------+-------------+
| localhost | sys   | mysql.sys | sys_config | root@localhost | 2021-11-03 10:46:17 | Select        |             |
| %             | bbsdb | adminTWO  | t1         | root@localhost | 0000-00-00 00:00:00 | Select,Insert |             |
+-----------+-------+-----------+------------+----------------+---------------------+---------------+-------------+
2 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    (4) columns_priv表使用
    mysql> select  * from mysql.columns_priv;
+------+-------+----------+------------+-------------+---------------------+-------------+
| Host | Db    | User     | Table_name | Column_name | Timestamp           | Column_priv |
+------+-------+----------+------------+-------------+---------------------+-------------+
| %    | bbsdb | adminthr | t2         | name        | 0000-00-00 00:00:00 | Update      |
+------+-------+----------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    2、撤销授权

    2.1 删除已有授权用户的权限

    命令格式如下:

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

    • 1

    注意 : 库名的表示方式 要和 用户授权时的表示方式一样

    2.2 删除添加的用户

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

    • 1

    2.3 相关命令总结

    命令作用
    select user();显示登陆用户名及客户端地址
    show grants;用户显示自身访问权限
    show grants for 用户名@“客户端地址”;管理员查看已有授权用户访问权限
    set password=password(“新密码”);授权用户连接后修改连接密码
    set password for 用户名@“客户端地址”=password(“新密码”);管理员重置授权用户连接密码
    drop user 用户名@“客户端地址”;删除授权用户（必须有管理员权限）
    rename user 原用户名 to 新用户名 ;修改用户名

    3、实例练习

    host50 做数据库服务器

    host51 做客户端验证用户授权

    3.1 要求：

    	1.允许网络中的所有主机都可以使用数据服务器host50的数据管理员root用户连接 50 数据库服务 连接密码123qqq...A  对所有库表有完全权限 且有授权权限

    • 1
    grant all  on  *.*  to  root@"%"  identified by "123qqq...A" with grant option;

    • 1

    ​ 2.可以使用admin用户在host50主机，连接本机的数据库服务，连接密码是123qqq…A 仅对 服务器上的所有表有查询权限。

    mysql> grant select on *.* to admin@"localhost" identified by  "123qqq...A";

    • 1

    3.2 查看用户

    select  user , host from mysql.user;

    • 1
    mysql> select  user , host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | %         |			# 任意主机的root用户均可登陆
| admin     | localhost |
| mysql.sys | localhost |
| root      | localhost |			# 本机root用户登陆
+-----------+-----------+
4 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    3.3 查看用户访问权限

    show  grants  for  用户名@"客户端地址";

    • 1
    mysql> show grants for root@"%";
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show grants for admin@"localhost";
+--------------------------------------------+
| Grants for admin@localhost                 |
+--------------------------------------------+
| GRANT SELECT ON *.* TO 'admin'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    3.4 追加权限

    （库名 用户名 客户端地址都不变就是追加权限）

    grant  权限  on  库名  to  用户名@"客户端地址";

    • 1

    给admin用户追加插入记录的权限

    mysql>  grant insert on  *.* to admin@"localhost" ;


mysql> show grants for  admin@"localhost" ; 
+----------------------------------------------------+
| Grants for admin@localhost                         |
+----------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'admin'@'localhost' |
+----------------------------------------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    3.5 修改已有授权用户的连接密码

    修改的密码也要与当前数据库服务的密码策略匹配 ，

    修改的密码要使用password() 函数加密 ，

    密码存储在mysql.user表的authentication_string字段下,存储的是加密后的密码

    格式：

    mysql> set password for 用户名@"客户端地址"=password("新密码");

    • 1

    修改admin的密码

    mysql> set password for admin@"localhost"=password("123abc");


mysql>  select  user,host,authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user      | host      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root      | %         | *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 |
| admin     | localhost | *3620754A963ECB3D7296097F9DA00C1FA5476B03 |
+-----------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    3.6 连接测试

    在客户端host51 连接数据库服务器host50

    1.连接命令格式：
    mysql  -h192.168.4.50  -u用户名  -p密码

    • 1

    在51 主机 使用50 授权的root 用户连接 50 数据库服务器 :

    [root@host51 ~]# mysql -h192.168.4.50 -uroot -p123qqq...A

mysql> 


    • 1
    • 2
    • 3
    • 4
    2.查看连接服务器的主机名
    mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| host50     |
+------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    3.查看连接用户名和客户端地址
    mysql> select user();
+-------------------+
| user()            |
+-------------------+
| root@192.168.4.51 |
+-------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    4.显示访问权限
    mysql> show grants;			# 显示访问权限
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    5.测试用户权限
    # 因为拥有的是all 的权限 所有命令都可以执行 ，可以把没有用的库全删除。
mysql> drop database DB1;		# 删库
Query OK, 1 row affected (0.31 sec)

mysql> drop table  db1.t31;		# 删表
Query OK, 0 rows affected (0.13 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    6. 在host50主机撤销网络中所有主机使用root连接的权限
    # 只撤销 with grant option的权限。
mysql> revoke grant option on  *.* from root@"%";

# 只撤销用户删除记录的权限
mysql> revoke delete on  *.* from root@"%";

# 查看当前已有的权限
mysql> show grants for root@"%";
...
# 删除用户当前所有的权限
mysql> revoke all on  *.* from root@"%";

# 查看当前已有的权限
mysql> show grants for root@"%";
+----------------------------------+
| Grants for root@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
+----------------------------------+
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    7.删除用户

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

    例子： drop user  root@"%";
例子： drop user  admin@"localhost";

    • 1
    • 2
    8.与用户相关的命令
    [root@host51 ~]# mysql -uroot -pNSD2107...a    			# 管理员登录
mysql> grant select on  *.* to plj@"localhost" identified by "123qqq...A";  
mysql> exit; 断开连接


[root@host51 ~]# mysql -uplj -p123qqq...A  		# 使用plj 用户登录
mysql> set password=password("新密码");  		 # 用户登陆后修改自己的连接密码 (要复合密码策略要求)

mysql> select password("abc123...A"); 			# 加密函数 password（） 命令演示
+-------------------------------------------+
| password("abc123...A")                    |
+-------------------------------------------+
| *482907C0B13E321A83A84C2FBB881C5BD4377076 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> set password=password("abc123...A");  		# plj用户修自己的连接密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit; 						# 断开连接
Bye
[root@host51 ~]# mysql -uplj -p123qqq...A   		# 旧密码登陆报错
[root@host51 ~]# mysql -uplj -pabc123...A  			# 新密码登陆成功

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    9.数据库管理员可以重置授权用户的登陆密码,和给添加的用户改名
    重置 plj用户连接密码
    mysql> set password for  plj@"localhost"=password("123qqq...A"); 
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit

[root@host51 ~]# mysql -uplj -p123qqq...A   	 # 使用修改后的密码登陆


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    给添加的用户改名
    mysql> grant select on  *.* to yaya@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user , host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| yaya      | %         |
| mysql.sys | localhost |
| plj       | localhost |
| root      | localhost |
+-----------+-----------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    使用命令改名， 但客户端地址必须是 %
    mysql> rename user yaya to  jingyaya ;  
Query OK, 0 rows affected (0.01 sec)

mysql> select user , host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| jingyaya  | %         |
| mysql.sys | localhost |
| plj       | localhost |
| root      | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    通过修改表记录改用户名
    mysql> update mysql.user set user="panglijing" where user="plj" ;
mysql> flush privileges;
mysql> select user , host from mysql.user;
+------------+-----------+
| user       | host      |
+------------+-----------+
| jingyaya   | %         |
| mysql.sys  | localhost |
| panglijing | localhost |
| root       | localhost |
+------------+-----------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    10.用户权限的追加：在一样权限的基础上添加新权限。
    [root@host51 ~]# mysql -uroot -pNSD2107...a

mysql> grant select on *.*  to bob@"%" identified by "123qqq...A";

mysql> show grants for  bob@"%";
+----------------------------------+
| Grants for bob@%                 |
+----------------------------------+
| GRANT SELECT ON *.* TO 'bob'@'%' |
+----------------------------------+


mysql> grant insert on  *.* to bob@"%";

mysql> show grants for  bob@"%";
+------------------------------------------+
| Grants for bob@%                                 |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'bob'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    ect user , host from mysql.user;
    ±----------±----------+
    | user | host |
    ±----------±----------+
    | yaya | % |
    | mysql.sys | localhost |
    | plj | localhost |
    | root | localhost |
    ±----------±----------+

    
##### **使用命令改名， 但客户端地址必须是 %**
```sql
mysql> rename user yaya to  jingyaya ;  
Query OK, 0 rows affected (0.01 sec)

mysql> select user , host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| jingyaya  | %         |
| mysql.sys | localhost |
| plj       | localhost |
| root      | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    通过修改表记录改用户名
    mysql> update mysql.user set user="panglijing" where user="plj" ;
mysql> flush privileges;
mysql> select user , host from mysql.user;
+------------+-----------+
| user       | host      |
+------------+-----------+
| jingyaya   | %         |
| mysql.sys  | localhost |
| panglijing | localhost |
| root       | localhost |
+------------+-----------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    10.用户权限的追加：在一样权限的基础上添加新权限。
    [root@host51 ~]# mysql -uroot -pNSD2107...a

mysql> grant select on *.*  to bob@"%" identified by "123qqq...A";

mysql> show grants for  bob@"%";
+----------------------------------+
| Grants for bob@%                 |
+----------------------------------+
| GRANT SELECT ON *.* TO 'bob'@'%' |
+----------------------------------+


mysql> grant insert on  *.* to bob@"%";

mysql> show grants for  bob@"%";
+------------------------------------------+
| Grants for bob@%                                 |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'bob'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
  • 相关阅读:
    EasyCode(IntelliJ IDEA 2023.2.3)
    CTF-misc练习（https://buuoj.cn）之第二页
    【云原生 | Kubernetes 系列】--Gitops持续交付 ArgoCD 部署与概念
    ChatGLM lora微调时出现KeyError: ‘context‘的解决方案
    端口被占用怎么解决
    学习阶段单片机买esp32还是stm32?
    SpringSecurity
    Android学习笔记 1.2.3 Gradle的属性定义 && 1.2.4 增量式构建
    动态代理看这个就够了
    进程信号（linux）
  • 原文地址：https://blog.csdn.net/weixin_56619848/article/details/126836538