mysql day03学习笔记-杜老师（动力节点）_动力节点mysql课堂笔记 下载

mysql day03学习笔记-杜老师（动力节点）

1.insert语句可以一次插入多条记录吗？

可以的
语法： insert into t_birthday(字段名1，字段名2) values (),(),();

mysql> insert into t_birthday(id,birth,create_time) values(2,'2022-10-22',now()),(3,'2022-10-22',now());
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_birthday;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
|    3 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

2.快速复制表

2.1把后面的查询表的结果当作一张表给创建出来，可以完成一张表的快速复制，同时复制表中的数据也就都存在了

mysql> create table t_birthday_copya as select * from t_birthday;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t_birthday_copya
    -> ;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
|    3 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2.把带有where条件的查询结果当作一张表创建出来。

mysql> create table t_birthday_copyb as select id ,birth, create_time from t_birthday where id=2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t_birthday_copyb;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

2.3.向其中一张表中插入另一张表中的所有的数据。

案例：向t_birthday_copya表中插入t_birthday_copyb表的所有的数据。

mysql> select * from t_birthday_copya;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
|    3 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from t_birthday_copyb;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into t_birthday_copya select * from t_birthday_copyb;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t_birthday_copya;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
|    3 | 2022-10-22 | 2022-10-22 23:34:38 |
|    2 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
4 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

3.快速删除表中的数据

删除表t_birthday中id=2的数据。

mysql> delete from t_birthday_copya where id=2;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t_birthday_copya;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
|    3 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

可以恢复数据的删除语句（当然前提是开始事务，事务不开启，删除语句执行之后，数据回滚是不能恢复数据！！！）
delete 语句（DML）删除数据的原理？
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放
这种删除的缺点是：删除效率比较低。
这种删除的优点是：支持回滚。

#案例：
#1 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

#2 删除id=3的数据
mysql> delete from t_birthday_copya where id=3;
Query OK, 1 row affected (0.00 sec)

#3 查看数据库数据
mysql> select * from t_birthday_copya;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
+------+------------+---------------------+
1 row in set (0.00 sec)

#4 回滚
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

#5 查看表发现数据又回复了。
mysql> select * from t_birthday_copya;
+------+------------+---------------------+
| id   | birth      | create_time         |
+------+------------+---------------------+
|    1 | 2022-10-22 | 2022-10-22 21:59:20 |
|    3 | 2022-10-22 | 2022-10-22 23:34:38 |
+------+------------+---------------------+
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

truncate语句删除数据的原理？
这种数据的删除效率比较高，表被一次性截断，物理删除。
优点：删除的效率快
缺点：不支持回滚。
用法：truncate table 表名

mysql> select * from t_car;
+----+---------+-----------+-------------+--------------+----------+
| id | car_num | brand     | guide_price | produce_time | car_type |
+----+---------+-----------+-------------+--------------+----------+
|  1 | 1001    | 宝马520Li |       10.00 | 2020-10-11   | 燃油车   |
|  2 | 1002    | 奔驰E300L |       55.00 | 2020-11-11   | 新能源   |
|  3 | 1003    | 丰田霸道  |       30.00 | 2000-10-11   | 燃油车   |
|  4 | 1003    | 丰田霸道  |       30.00 | 2000-10-11   | 燃油车   |
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_car;
Empty set (0.01 sec)
# 此时回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
#回滚之后还是一个空的表。数据没有回来也就意味着不支持回滚。
mysql> select * from t_car;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

大表非常大，上亿条记录？？？
删除的时候使用delete,也许需要执行一个小时才能删除完！效率较低。
可以选择使用truncate删除表的数据。只需要不到一秒的时间就删除结束。效率较高。
但是在使用之前，需要考虑是否真的需要删除所有数据，因为此操作删除之后，不能恢复数据。是真的删除了

删除表操作？
drop table 表名； //这不是删除表中的数据，是把表给删除了。

3.1 delete，drop,truncate的区别？

4.对表结构的增删改？

什么是对表结构的增删改？就是在表中，添加一个字段，删除一个字段，修改一个字段。
对表结构的修改：alter
注意：开发阶段的时候，已经确定了表结构，一旦出现问题，那么这将是表的设计者的责任）

5.约束

5.1.什么是约束?

约束对应的英语单词：constraint
在创建表的时候，可以给表的字段加上一些约束，来保证这个数据的完整性和有效性。

5.2.约束包括哪些？

非空约束：not null
唯一性约束：unique
主键约束：primary key(简称pk)
外键约束：foreign key(简称fk)
检查约束：check(mysql不支持，oracle支持)
我们这里重点学习四个约束：
not null
unique
primary key
foreign key

5.3 非空约束：not null

非空约束的字段不能为null;
添加：xxxx.sql这种文件被称之为sql脚本文件。sql脚本文件中编写了大量的这种sql语句。我们执行sql语句的时候，该文件中的所有的sql语句会被全部执行的，批量的sql语句，可以使用sql脚本文件。
在mysql当中怎末执行sql脚本哪？
使用source命令执行
not null只有列级约束，没有表级约束

5.4 唯一性约束:unique

唯一性约束unique约束的字段不能重复但是可以是null

mysql>  drop table if exists t_car;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_car(id,name) values(1,'奔驰');
Query OK, 1 row affected (0.01 sec)

#重复插入相同的数据报错。
mysql> insert into t_car(id,name) values(1,'奔驰');
ERROR 1062 (23000): Duplicate entry '奔驰' for key 'name'
1
2
3
4
5
6
7
8

新需求：name和type两个字段联合起来有唯一性。
也就是说只有两个字段的数值全部重复表示重复了。

mysql>  drop table if exists t_car;
Query OK, 0 rows affected (0.01 sec)

mysql>  create table t_car(id int(3),name varchar(255) ,type varchar(255),unique(name,type));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> insert into t_car(id,name,type) values(1,'梦幻','电力');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into t_car(id,name,type) values(2,'哈哈','燃油');
Query OK, 1 row affected (0.00 sec)

mysql>   insert into t_car(id,name,type) values(3,'哈哈1','燃油');
Query OK, 1 row affected (0.00 sec)

mysql>   insert into t_car(id,name,type) values(3,'哈哈1','燃油');
ERROR 1062 (23000): Duplicate entry '哈哈1-燃油' for key 'name'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

如果约束没有添加在列的后面表示是表级约束。
如果约束添加在列的后面是列级约束。
什么时候使用表级约束哪？
需要给表中多个字段联合起来添加某一个约束的时候，需要使用表级约束。
unique 和not null 可以联合吗？
可以联合

mysql> drop table if exists t_car;
Query OK, 0 rows affected (0.01 sec)

mysql>  create table t_car(id int(3) unique not null,name varchar(255) ,type varchar(255));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc t_car;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(3)       | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| type  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在mysql当中，如果一个字段被unique,not null约束的话，该字段自动变成了主键字段。（ 注意：oracle中不一样的)

5.5. 主键约束（ primary key -pk）

什么是主键？有啥用？
主键是每一行记录的唯一标识，
主键是每一行记录的身份证号！！！。
记住：任何一张表都要有一个主键，没有主键，这张表无效。
主键的特征：not null,unique
怎么给一张表中添加约束哪？
在添加的字段后面加一个primary key

mysql> drop table if exists t_car;
Query OK, 0 rows affected (0.01 sec)

#一个字段做主键：单一主键
mysql>  create table t_car(id int(3) primary key,name varchar(255) ,type varchar(255));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc t_car;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(3)       | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| type  | varchar(255) | 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

如果有多个字段加主键：复合主键
在实际的开发中，不建议使用复合主键
建议使用单一的主键。

mysql> drop table if exists t_car;
Query OK, 0 rows affected (0.01 sec)

#一个字段做主键：单一主键
mysql>  create table t_car(id int(3) ,name varchar(255) ,type varchar(255),primary key(id,name));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql>  desc t_car;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(3)       | NO   | PRI | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
| type  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

5.5.1 一个主键约束能加两个吗?

不能，报错。
主键只能是一个。
其中复合主键也是一个。复合主键是两个字段联合做一个主键。
主键值一般都是数字，是定长的，不建议使用varchar
主键除了：单一主键和复合主键外，还可以进行这样飞类？
自然主键：主键值是一个自然数，和业务没有关系。
业务主键：主键值和业务紧密相连，例如拿银行卡账号做主键值。这就是业务主键！
在实际开发中使用业务主键多还是自然主键多？
自然主键使用比较多，因为主键只要做不不重复就行，不需要有意义。
业务主键不好，因为主键一旦和业务挂钩。那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用，尽量使用自然主键。

mysql> drop table if exists t_car;
Query OK, 0 rows affected (0.01 sec)

#一个字段做主键：单一主键 auto_increment表示自增，从1开始，以1递增。
mysql>  create table t_car(id int(3) primary key auto_increment, name varchar(255) ,type varchar(255));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql>  desc t_car;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(3)       | NO   | PRI | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
| type  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6.外键约束（foreign key）

外键约束
业务背景：设计一个数据库表，来描述”班级和学生“的信息？
第一种方案：请设计数据库表，来描述”班级和学生“的信息
第一种方案：班级和学生存储在一张表中？？？
t_student 有no,name,classno,classname.表设计完之后，发现班级的名称有大量重复的数据，造成空间的浪费。这个设计是比较失败的
第二种方案：班级一张表，学生一张表
t_class classno,classname
t_student no,name，cno(班级编号)[fk引用t_class这张表的classno]
如果不添加一个约束给cno这个字段的话，可能会出现在向学生表中插入数据的时候，写错班级编号。
一旦添加一个外键约束的话，这个外键字段就不能随便写了。
所以为了保证t_student的表中的数据都是有效的，那么就要给cno字段添加外键约束，那么cno字段就是外键字段，cno字段中的每一个值都是外键值。
t_student
注意：t_class是父表，t_student是子表
删除表/数据的顺序：先删除子表再删父表。
创建表/数据的顺序：先创建父表再创建子表

drop table if exists t_class；
drop table if exists t_student;
create table t_class(
    classno   int primary key,
    classname   varchar(255)
);
create table t_student(
    no          int primary key auto_increment,
    name     varchar(255),
    cno        int,
#外键（外键的编号） references  主表(引用的主表字段)
    foreign key(cno) references  t_class(classno)
)

insert into t_class(classno, classname ) values(100,'哈哈1');
insert into t_class(classno, classname ) values(101,'哈哈哈2');

insert into t_student(no,name,cno) values(1,'哈哈1',100);
insert into t_student(no,name,cno) values(2,'哈哈哈2',101);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

7.存储引擎

7.1 什么是存储引擎，有什么用哪？

存储引擎是mysql数据库中特有的一个术语。它是存储数据的一种方式。
存储引擎不同，存储数据的方式不同

7.2 怎么给表设置存储引擎？

可以在创建表的时候指定存储引擎。
可以在创建表的时候在小括号后面 ENGINE =来设置存储引擎。
在CHARSET =后面设置字符集编码格式
结论： MySQL中默认的存储引擎是InnoDB. MySQL中默认的编码方式是UTF-8

# 展示创建表的sql语句
mysql> show create table t_birthday;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                  |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_birthday | CREATE TABLE `t_birthday` (
  `id` int(3) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11

7.3 怎么查看mysql支持哪些存储引擎？

当前mysql的版本：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

命令：show engines \G

mysql> show engines \G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES   # mysql支持MEMORY存储引擎
     Comment: Hash based, stored in memory, useful for temporary tables #基于哈希，存储在内存中，用于临时表
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 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
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

7.4. mysql常用的存储引擎

对于一张表来说，只要是主键，或者加有unique约束的字段会自动创建索引。
MyISAM存储引擎？
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 -存储表结构的定义（mytable.frm）
数据文件-存储表行的内容（mytable.MYD）
索引文件-存储表上索引（mytable.MYI）
灵活的AUTOINCREMENT处理
优势： 可被转换为压缩，只读表来节省空间
InnoDB存储引擎？
mysql默认存储引擎，此引擎支持事务保证数据的安全，支持数据库崩溃后自动恢复机制，行级锁定
InnoDB中表空间tablespace被用于存储表的内容
MySQL的效率不是很高。
MEMORY存储引擎？
数据存储在内存中，表级锁机制。不能包含text或BLOB字段
## 8.事务

8.1.什么是事务？

一个事务就是一个完整的业务逻辑。（自己的理解就是一件事情或者说一个任务的开始到结束的过程以及记录）。
什么是一个完整的业务逻辑？
假设转账，爸爸给我转1000块，最后我手机上多1000块，爸爸手机上少了1000块。这就是一个完整的业务逻辑。
这是最小的工作单元，要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功，或者同时失败，这样最终的钱的数额才是正确的，

8.2.只有DML语句才会有事务的说法，其他语句和事务无关

insert,update,delete只有以上的三个语句才和事务有关系，其他的都没有关系。
因为只有这三个语句才是对数据进行增，删，改的。
数据安全是首位的！！！

8.3.假设所有的业务，只要一条DML语句就能完成，那么事务机制还有存在的必要吗？

没有，事务的存在是因为一个业务的完成需要多条DML语句联合起来。

8.4.事务是如何做到多条语句能够同时成功或者同时失败？

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
事务开启了：
执行1：爸爸开启转帐了，要扣他的钱
执行2：我开启接收转账了，要收他的钱
事务结束了:
在事务的执行过程可以去提交事务，也可以去回滚事务。
提交事务： 清空事务性活动的日志文件。将最终的数据持久化到数据库当中。这也标志事务的完成，并且是一种成功的结束
回滚事务： 清空事务性活动的日志文件。将所有的DML操作全部取消。这标志着事务的结束，并且是一种失败的结束

8.5 怎么提交事务，怎么回滚事务？

提交事务：commit；语句
回滚事务: rollback； 语句
在mysql中的事务是默认提交的。
提交以后的数据进入数据库是不能进行回滚操作的。

8.6 如何关闭事务自动提交

先执行start transaction;此语句代表关闭事务自动提交。
1

当进行多次插入操作的时候，输入

rollback；
1

发现数据没有加入数据库。可以证明关闭事务的自动提交了

8.7. 事务的特性

A（Atomicity）：原子性 说明事务是最小的工作单元，不可再分。
C(Consistency): 一致性 所有的事务要求，在同一个事务当中，要么同时成功，要么同时失败。保证数据的一致性
I(Isolation): 隔离性 A事务和B事务具有一定的隔离性。 A事务在操作一张表的时候，B事务也在操作一张表
D(Durability):持久性 事务最终结束的一种保障。事务提交，就相当于将没有保存的数据保存到硬盘中。

8.8. 事务的隔离特性

事务和事务之间的隔离级别：读未提交（read uncommited），读已提交(read commited)，可重复读(repeatable read)，序列化/串行化(serializable)
大多数据库用的是二档起步

读未提交（read uncommited）：事务A可以读取到事务B未提交的数据。这种隔离级别存在的问题是：脏读（Dirty read）现象.我们称之读到了脏的数据。由于innodb在update时，会加行锁， 事务1没结束，事务2的update会阻塞，天然避免了脏写。

读已提交(read commited) ：事务A可以读取到事务B提交之后的数据。解决了脏读。这种隔离级别存在的问题是：不可重复读取现象.就是说在事务开启之后，第一次读取到的数据是3条，当前事务还没有结束，第二次读取到的数据是4条，3不等于4 称之为不可重复读取。这种隔离级别是比较真实的数据，每一次读取到的数据是绝对的真实。oracle数据库默认的是此隔离级别。

可重复读(repeatable read)：事务A开启之后，不管多久，在事务A中读取到的数据都是一致的。即使事务B将数据库中的数据易经修改了，并且提交了，事务A读取到的数据还是没有发生改变。这就是可重复读。解决了脏读、不可重复读。这种隔离级别存在的问题是：幻读，每一次读取到的数据都是幻想不真实。mysql中默认的隔离级别是此隔离级别。

序列化/串行化(serializable)：这是最高隔离级别，效率最低，解决了所有的问题。这种隔离级别表示事务排队。

8.8.1 验证读未提交

查看隔离级别：select @@transaction_isolation;

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

测试的表t_user;
事务A:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(id,name) values(3,"eric");
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5

此时查看事务B:

mysql> use bjpowernode;
Database changed
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
|  3 | eric |
+----+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

可以看出事务B读取到事务A未提交的数据。
由于innodb在update时，会加行锁， 事务1没结束，事务2的update会阻塞，天然避免了脏写

8.8.2 验证读已提交

修改事务的隔离级别：

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
1
2

然后退出cmd命令窗口重新进入；
查看事务的隔离级别：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

事务A添加了一条数据并进行查询数据库:

mysql> use bjpowernode
Database changed
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
+----+------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(id,name) values(3,"ha");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
|  3 | ha   |
+----+------+
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

此时我们停下查看发现：事务A可以在数据库中查询到添加的数据。而事务B是看不到此条数据的
然后事务A执行了提交的命令：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
1
2

此时我们停下之后。事务B再去执行查看数据库的命令。

事务B也开启事务查询数据库:

mysql> use bjpowernode
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

此时停下查看：发现在数据库中没有事务A中添加的数据。
然后当事务A执行提交命令之后，我们可以看到数据库中添加的这条数据，这就是读已提交。

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
|  3 | ha   |
+----+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

8.8.3 验证可重复读

修改事务的隔离级别：

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
1
2

然后退出cmd命令窗口重新进入；
查看事务的隔离级别：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

事务A:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t_user where id=3;
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5

事务B:

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
|  3 | ha   |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
|  3 | ha   |
+----+------+
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

总结：事务B查出来的数据开始是什么样，就一直是什么样。事务A在删除表中的数据的时候，对事务B的表现出来的数据一点影响都没有。这就是可重复读

8.8.4 验证序列化/串行化

修改事务的隔离级别：

mysql> set global transaction isolation level read serializable;
Query OK, 0 rows affected (0.00 sec)
1
2

然后退出cmd命令窗口重新进入；
查看事务的隔离级别：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

事务A:

mysql> use bjpowernode;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(id,name) values(4,"haha");
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7

我们此时停下去查看另一个事务B.
然后接着去执行提交命令，再去查看另一个事务B.

事务B:

mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
-
1
2
3
4
5

发现此时下面的光标在闪烁没有任何反应。
然后当事务A提交数据之后，数据就出来了。这就是序列化。

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | eric |
|  3 | ha   |
|  4 | haha |
+----+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

9. 索引

9.1. 索引概述

索引在数据库中起到非常重要的作用,如果没有索引在数据量庞大时查询的结果会非常非常的慢,索引类似于现实中的书籍的目录
索引的作用就是能够在查询时迅速的锁定到查询的位置,并且不会做全表扫描(全表扫描代表将整张表全部查看(字段与数据))
如果没有索引造成全表扫描则会非常影响执行效率,如果加上索引后则会解决这一问题,但是仅仅只针对于设置索引的字段,并且设置索引后数据的增删改会降低速度
设置索引后数据的增删改效率会降低的原因是每次的增删改数据都会重新编排索引
每张表中至少设置一个索引(规范),并且主键约束就是一种索引

9.2. 索引查询测试

explain：解释。

使用索引以及不使用索引查询效率区别
索引查询效率命令格式:explain select 字段名... from 表名
不适用索引查询

explain select * from t_user;
mysql> explain select * from t_user;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
扫描2条记录：说明没有使用索引。 type=ALL;
explain select id,name from  t_user;
type=ref

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

使用索引查询

explain select id from tb_index_demo
1

以上查询是使用与不使用索引的查询语句以及结果
经过结果看第一个查询在使用字段或*号时其type类型为ALL代表没有使用索引查询,ALL是全表扫明
第二个查询在使用的字段为id时其type类型为index代表是使用了索引查询,index是使用了索引查询
添加：
select * from t_user where id=1;mysql发现id字段上有索引对象，他会按照这个索引对象去查找对象所在的编号，根据这个编号找到对应的地址。立马转换成对应的sql语句。
什么时候使用索引？条件1：条件数据量大。条件2：该字段经常出现在where后面，以条件的形式存在，也就是这个字段总是被扫描。条件3：该字段很少的DML（数据库管理语言）因为DML后，索引需要重新排序。建议不要随意添加索引。因为索引添加过多，维护也是很困难的。

9.3. 索引的创建与使用

创建索引后需要在表中设置字段,MySQL中的primary key以及unique约束都是默认带有索引的,所以设置这两个约束时不需要单独设置索引
在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存储，在MyISAM存储引擎中，索引存储在一个.MYI的文件中。在Innodb存储引擎中，索引存储在逻辑名称tablespace中；在MEMORY存储引擎当中，索引存储在内存当中。索引在MySQL当中都是一个树的形式，BTREE。一个自平衡二叉树。
创建索引格式:create index 添加索引名称 on 表名(字段名);
修改索引格式:alter table 表名 add index 索引名称(字段名);
查看索引格式:show index from 表名;

1. 创建索引实例:

# 创建索引
create index index_01 on tb_index_demo(name);
# 使用索引查询
explain select `name` from tb_index_demo
# 未使用索引查询
explain select age from tb_index_demo
# 查看表中的所有索引
show index from tb_index_demo
1
2
3
4
5
6
7
8

9.4. 查看索引

用于查看表中设置的所有的索引
查看索引格式:show index from 表名;

9.5. 删除索引

如果现有的索引有不使用的或不想用的以及设置错误的则可以删除
删除索引格式1:drop index 索引名称 on 表名
删除索引格式2:alter table 表名 drop index 索引名称
删除索引格式3:alter table 表名 primary key
删除索引格式4:alter table 表名 drop unique
删除索引

# 删除索引
drop index index_01 on tb_index_demo
alter table tb_index_demo drop index index_01
1
2
3

9.6. 索引有失效的时候，什么时候索引失效？

失效的第一种情况：
select * from emp where ename like ‘%T’
ename上即使添加了索引，也不会走索引，为什么？
原因是因为模糊匹配当中以"%“开头了！尽量避免模糊查询的时候以”%"开始。这是一种优化的手段 /策略
失效的第二种情况：
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。
失效的第三种情况：
使用复合索引的时候，没有使用左侧的列查找，索引失效
什么是复合索引？
两个字段，或者更多的字段联合起来添加一个索引。叫做复合索引。

创建索引格式:create index 添加索引名称 on 表名(字段名1，字段名2);
如果条件是字段名2的话，索引会失效。
失效的第四种情况？
在where当中索引列参加了运算，索引失效。
create index emp_sal_index on emp(sal);
explain select * from emp where sal =800; 这走的是索引
explain select * from emp where sal+1 =800; 这个索引失效。
失效的第五种情况？
在where当中索引列使用了函数
explain select *from emp where lower(ename) =‘smith’;

9.7 索引的分类

索引是各种数据库的优化重要手段。优化的时候优先考虑的因素就是索引。索引在数据库当中分了很多类？
单一索引：一个字段添加索引
复合索引：两个字段或者更多的字段添加索引
主键索引：主键上添加索引
唯一性索引：具有unique约束的字段上添加索引
注意：唯一性比较弱的字段上添加索引用处不大

10. 视图

10.1. 视图概述

视图在MySQL中是一种特殊的存在,他是一个查询的结果集,通过查询语句创建,创建视图后查询视图就与查询结果相同
视图是虚表,不是真实存在的
使用视图的好处

1. 访问复杂数据更加渐变(视图是使用查询语句创建的)
2. 可以用于不同用户展示不同数据使用
3. 可以提高检索效率
4. 隐藏表的实现细节

10.2 创建视图

只有DQL语句才能以view的形式创建
create view 视图名称 as 查询语句;这里的语句必须是DQL语句。
创建视图语法格式:create view 视图名称 as 查询语句;

# 创建视图
create view emp_dept_view as select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno
1
2

10.3. 修改视图

修改视图语法格式:alter view 视图名称 as 查询语句;

# 修改视图
alter view emp_dept_view as select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
1
2

10.4. 删除视图

删除视图语法格式:drop view [if exists] 视图名称;

# 删除视图
drop view abcd_view
drop view if exists abcd_view
drop view if exists emp_dept_view
1
2
3
4

10.5. 视图的注意事项

视图创建后可以修改数据,但不能增删数据,修改内容后会同步主表,表中的增删改同样会同步到视图中

# 修改视图中的数据
update emp_dept_view set sal = sal + 100 where empno=7369
select * from emp
# 删除视图中的数据
delete from emp_dept_view where empno=7369
# 新增视图中的数据
insert into emp_dept_view values(7567,'Tony',1230.00,20,'主播部','郑州');

1
2
3
4
5
6
7
8
9

C:Create(创建)
R:Retrive(检索)
U: Update(修改)
D:Delete(删除)

11. 数据库设计的三范式

11.1. 数据库设计三范式概述

数据库设计三范式主要针对数据库的设计为主,数据库的设计的规范,后期的设计都需要按照此规范设计
数据库设计三范式会让数据库扩展性,维护性,安全性会更高,数据库更加健壮

11.2. 数据库设计第一范式

数据库中不能出现重复的记录,每个字段的原子性不能再分

1. 不符合第一范式实例

以上表数据中id数据重复,id可以理解为学生学号,一旦重复则代表学生信息不安全,所以学生id应设置为唯一,最好是使用MySQL的主键以及自增字段

2. 符合第一范式实例

为了解决上面出现问题,将id改为主键或唯一即可

修改后则不会出现重复数据

11.3. 数据库设计第二范式

数据库第二范式是建立在第一范式的基础上的,要求所有非主键字段应该完全依赖主键,不能产生部分依赖
一章中只能存在一个主键,否则会出现问题

1. 不符合第二范式实例

上面的设计就存在问题了,编号重复,以及姓名重复,如果是设计数据库则不应该出现类似的重复数据,此时就应该将一张表中的学生以及教室,抽离出来,多出一张表后再次将其关联起来,这样可以友好的解决数据库重复数据的问题

2. 符合第二范式实例

设计学生表

设计教师表

设计学生教师关系表

学生表教师表以及关联表之间的关系

上图是学生教师以及关联表之间的关系,是将原本的单张表分成3份,通过某一张表指定他们的关系
看似教师与学生没有任何的关联,但实际使用过第三张关系表将其相连

11.4. 数据库设计第三范式

数据库设计的第三范式是在第一与第二范式的基础之上建立,会将主键依赖向下传递,而不再使用关系表作为依托

1. 不符合第三范式实例

以上数据表设计的问题是不符合第三范式,原因是班级出现了重复数据,并且设置班级时也须要将班级的所有名称全部加上,出现这种问题则可以重新将班级抽离
将学生与班级分离,学生表中只要id,name,phone,c_id连接班级表的主键,班级表中只需要使用id,class_name即可

2. 符合第三范式实例

以上的设计就是将学生与班级抽离出来,并且将班级编号当做学生中的外键关联起来,让其存在关系,如果 有多张表则依此类推,通过外键相关联

11.4. 数据库设计三范式的区别

1. 第一范式有主键,具有原子性(主键字段不可重复),并且字段不可分割(学生编号与学生姓名)
2. 第二范式是建立在第一范式的基础上,表1和表2没有任何的直接依赖,而是通过表3将其产生间接依赖(同第三张表将学生以及教室相关联),产生关系后即可使用
3. 第三范式是没有任何依赖传递,表1直接依赖于表2(通过主外键关系建立关联),这种也是常用的方式

数据库中的实际设计一定要遵循数据库设计规范,否则数据库中会存在大量的冗余数据或冗余字段等等,这样会导致后期的数据库维护起来非常麻烦

12. 数据库之删库删表(慎用!!!)

删库一时爽,天窗泪两行:删库删表的操作是不可逆的(尽管有可能找回,但也会有数据丢失),尤其是在工作中,千万不能乱用(尽量别用),并且进公司后也不会给带有删除权限的数据库用户

1. 删库

语法格式:drop database [if exists] 数据库名称;

#### 1. 删库
drop database if exists `中文数据库`;
1
2

2. 删表

语法格式:drop table [if exists] 表名1,表名2,表名n...

#### 1. 删单张表
drop table if exists a;
#### 2. 删多张表
drop table if exists a,b;
1
2
3
4

13. 数据库DBA命令

1. 数据库DBA命令概述

用于数据库中的增删改查用户,并且可以赋予用户特定权限(例如:数据库的增删改查),后期进入公司后也会分配不同权限的用户,一般情况分配的都是根据自己需要完成的功能决定,数据库DBA命令一般是由数据库管理员使用,所以一般接触不到

2. 数据库DBA操作

1. 创建用户

使用root权限用户创建用户
语法格式:create user 用户名 identified by '密码'

2. 授权用户

1. MySQL8.0授权方式

授权用户是给用户一些特定权限(例如:表的增删改查等)
语法格式:grant 权限 on 数据库.数据表 to 用户名@登录ip identified by 密码 with grant 是否赋予用户创建权限
MySQL8.0版本设置权限的用户名ip地址要与设置时的ip地址相同

2. MySQL8.0内授权方式

给用户授权使用
语法格式:grant 权限 on 数据库.数据表 to 用户名@登录ip identified by 密码 with grant 是否赋予用户创建权限

3. 撤销权限

将原本赋予的权限收回
语法格式:revoke 权限 on 数据库.数据表 from 用户名

4. 创建授权实例

# 1. 创建用户
create user liang identified by 'zebra'
# 2. 给用户授权,如果给的是星号(*)则代表所有库或所有表
grant select,update on db_lianxi.* to 'zebra'@'%'
grant * on db_lianxi.* to 'zebra'@'%'
# 3. 移除用户权限
revoke update on db_lianxi.* from 'zebra'
1
2
3
4
5
6
7

添加：一对多 两张表 多的表加外键
多对多 三张表 关系表两个外键
一对一 外键唯一
数据库设计三范式是理论上的。实践和理论有时候有偏差。
最终的目的是为了客户的需求，有的时候拿冗余换执行速度。因为在sql当中表的连接次数越多，效率越低。（笛卡尔积）
有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低