1.Mysql基础
1.1 mysql数据库的基本概述
1.1.1 为什么要用数据库
之前我们的数据是保存在内存中(变量 数组 集合)，这种方式保存数据，由于是存放在内存中，所以存取效率非常高，但是保存的数据量不大，并且随着程序的退出或电脑关机，存储在内存中的数据也会随着消失。
后面我们学到了文件，我们发现数据还可以保存在文件中，这样就实现了数据持久化保存，但是我们在操作数据的时候，需要进行频繁的io操作，这种操作的方式效率不高，而且我们也不能在文件中保存海量的数据，因为一旦文件比较大，对于数据的存取非常不方便。
这份时候，我们需要把数据保存在数据库中。
数据库有什么特点：
- 数据库保存的数据本质上还是以文件的形式存储的，所以能够做到持久化的存储数据。
- 由于数据库提供了一套完善的数据操作机制(sql语句)。所以在进行数据操作的时候，效率非常高。
1.1.2 数据库软件有哪些
数据库又称为存储数据的仓库。它是一个软件，能够帮助我们方便的管理数据。市面上有很多数据库软件：
Oracle 甲骨文公司的产品，是业界最流行的数据库产品之一。
sqlServer 微软的关系型数据库产品，它基于windows平台开发，在进行c# .net开发的时候，一般都会使用sqlServer。
DB2: IBM公司的产品
mysql:开源组织的产品，小巧免费，适合中小企业。
上面说的这些数据库都是关系型数据库。所谓关系型数据库就是数据以表的形式存储(行 列)
也有非关系型数据库,这种数据库存储数据不再以表的形式存储数据(redis mongodb)。我们在学习的时候，我们使用mysql数据库来学习。
1.2 使用mysql数据库
1.2.1 登录mysql数据库
登录的方式有两种，一种是在linux系统里面直接使用mysql自带的客户端进行登录。还有一种是使用远程客户端登录工具进行登录。
- mysql自带的客户端进行登录
[root@java2201 ~]# mysql -u root -p #登录mysql数据库
Enter password:
你也可以在登录的时候直接指定数据库的密码
[root@java2201 ~]# mysql -u root -pAdmin123! # -p后面跟的是登录数据库的密码
- 使用远程登录工具进行登录（sqlYog navicat）
1.2.2 mysql数据库的基本使用
1.2.2.1 数据库的基本命令
- 查看当前数据库服务器里面有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
Mysql5.7版本自带4个数据库，information_schema、mysql、performance_schema、sys。
(1) information_schema 主要保存mysql数据库的元数据信息，这些元数据信息包括mysql数据库的基本信息，数据库或表的名称，列的数据类型或访问权限。
(2) mysql数据库 主要保存登录mysql的用户信息（用户名 密码 权限等）。
(3) performance_schema 主要保存数据库在运行时产生的各种数据，比如日志信息。
(4) sys 主要是给DBA数据库管理员使用的，一般我们不会操作这个数据库。
- 创建数据库
create database [IF NOT EXISTS] 数据库名称
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
mysql> show databases; # 创建数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database day01;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| day01 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 查看mysql数据库默认的字符编码格式
mysql> show create database day01;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| day01 | CREATE DATABASE `day01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
- 删除数据库
mysql> drop database day01;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; # 此时day01数据库不存在了
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
- 修改数据库
mysql> alter database day01 default character set gbk
-> ; #将数据库的字符编码格式设置成gbk
Query OK, 1 row affected (0.00 sec)
mysql> show create database day01;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| day01 | CREATE DATABASE `day01` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
- 切换数据库
mysql> use mysql; # 切换到名称为mysql的数据库
1.2.2.2 数据表的基本命令
在mysql中，数据是以表的形式存储的。所以我们必须了解数据表相关的操作命令。
注意：操作数据表的时候，必须先要切换到对应的数据库里面去。
- 查看数据库里面的表信息
mysql> show tables; # 查看数据库里面的表信息
- 创建数据表
语法：
create table 表名(
字段名称1 数据类型,
字段名称2 数据类型,
......
字段名称n 数据类型
);
mysql> create table student(
-> id int,
-> name varchar(20),
-> age int
-> );
Query OK, 0 rows affected (0.01 sec)
- 查看表结构
mysql> desc student; #查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Field: 描述的是字段的名称
Type:描述字段的数据类型
Null:当前字段的值是否允许为空 YES 允许为空
Default:描述字段的默认值，前提是字段允许为NULL。
Extra:对字段的额外描述
- 删除表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)
1.2.2.3 修改表结构的命令
修改表结构的命令一般用于对数据表字段的数据类型进行修改、增加新字段、删除字段，表重命名等等。
- 在数据表里面添加一个字段
语法：
alter table 表名 add column 字段名称 数据类型 [not null];
mysql> alter table student add column address varchar(30) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 在指定的列之后新增
mysql> alter table student add column score int after age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 修改表字段的数据类型
语法：
alter table 表名 modify column 字段名称 数据类型 [not null];
mysql> alter table student modify column name varchar(10) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 删除表字段
语法：
alter table 表名 drop column字段名称;
mysql> alter table student drop column test;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 修改列名
语法：
alter table 表名 change 旧字段 新字段 数据类型
alter table student change column name sname varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 修改表名称
语法：
alter table 旧表名 rename to 新表名；
mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| stu |
+-----------------+
1 row in set (0.00 sec)
1.2.2.4 数据表的CRUD操作
- 向数据表中所有字段添加数据
insert into 表名(字段1，字段2，字段3，…) values(值1，值2，值3,…);
注意：值的顺序和个数必须和字段的顺序和个数保持一致。字符串的数据必须携带单引号，但是数字可以不需要。
mysql> insert into student(id,sname,age,score,address)
-> values(1,'eric',18,99,'USA'
-> );
Query OK, 1 row affected (0.00 sec)
我们也可以只给部分字段插入数据，但是非空的字段必须插入数据，否则报错。
mysql> insert into student(id,sname,address) values(3,'james','USA');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
如果我们给非空的字段不插入值：
mysql> insert into student(id,sname,score) values(3,'james',90); # address 不允许为空 所以报错
ERROR 1364 (HY000): Field 'address' doesn't have a default value
如果我们想给所有字段都设置的值的话，表名后面的字段名称可以不指定。
mysql> insert into student values(4,'lily',20,80,'ENG');
Query OK, 1 row affected (0.00 sec)
我们也可以一次性的批量新增数据。
语法： insert into 表名(字段列表) values (值列表1),(值列表2)…;
mysql> insert into student(id,sname,age,score,address)
-> values(1,'eric',12,80,'CHN'),
-> (2,'james',20,89,'USA'),
-> (3,'Lucy',18,88,'ENG'),
-> (4,'Merry',17,87,'GER');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
- 修改数据
语法：
update 表名 set 字段1 = 值1,字段2 = 值2 where 条件
注意：如果不加where条件，那么就修改表中的所有数据。
mysql> update student set sname='Oneal',address='ENG' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 删除数据
语法：
delete from 表名 删除表中的所有数据。
delete from 表名 where 条件 根据指定的条件删除对应的数据。
mysql> delete from student where id = 4; # 删除id为4对应的数据
Query OK, 1 row affected (0.01 sec)
mysql> delete from student; # 删除所有数据
Query OK, 3 rows affected (0.01 sec)
我们清空表还可以使用truncate table命令
mysql> truncate table student; # 清空整个数据表
Query OK, 0 rows affected (0.01 sec)
delete 和truncate table之间的区别：
truncate table只能清空所有表中的数据，但是delete不仅仅可以清空所有表的数据，还可以删除指定的数据。
delete语句删除的数据还可以回滚。但是truncate table命令删除的数据无法回滚的。
1.2.2.5 数据表的查询操作(重点)
- 查询所有列
语法： select * from 表名 (* 代表的是所有字段)
mysql> select * from student; # 查询所有字段的数据
+----+-------+------+-------+---------+
| id | sname | age | score | address |
+----+-------+------+-------+---------+
| 1 | eric | 12 | 80 | CHN |
| 2 | james | 20 | 89 | USA |
| 3 | Lucy | 18 | 88 | ENG |
| 4 | Merry | 17 | 87 | GER |
+----+-------+------+-------+---------+
- 查询部分指定的列
语法：select 字段1,字段2,字段3… from 表名
mysql> select id,sname,address from student; # 只查询部分字段的数据
+----+-------+---------+
| id | sname | address |
+----+-------+---------+
| 1 | eric | CHN |
| 2 | james | USA |
| 3 | Lucy | ENG |
| 4 | Merry | GER |
+----+-------+---------+
4 rows in set (0.00 sec)
我们也可以在查询的时候，给字段指定别名。我们可以使用as关键字来指定别名，当然也可以不使用as关键字
语法：select 字段1 as 别名1,字段2 as 别名2,字段3 as 别名3… from 表名
mysql> select id as '编号',sname as '姓名',address as '地址' from student;
+--------+--------+--------+
| 编号 | 姓名 | 地址 |
+--------+--------+--------+
| 1 | eric | CHN |
| 2 | james | USA |
| 3 | Lucy | ENG |
| 4 | Merry | GER |
+--------+--------+--------+
4 rows in set (0.00 sec)
也可以不要as
mysql> select id '编号',sname '姓名',address '地址' from student;
+--------+--------+--------+
| 编号 | 姓名 | 地址 |
+--------+--------+--------+
| 1 | eric | CHN |
| 2 | james | USA |
| 3 | Lucy | ENG |
| 4 | Merry | GER |
+--------+--------+--------+
4 rows in set (0.00 sec)
- 在查询的时候，我们可以进行列与列之间的运算
表数据如下：
mysql> select * from student;
+----+-------+------+---------+------+---------+------+
| id | sname | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 2 | james | 20 | USA | 67 | 99 | 79 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 4 | Merry | 17 | GER | 66 | 98 | 56 |
+----+-------+------+---------+------+---------+------+
4 rows in set (0.00 sec)
现在我们想查询所有学员的id 姓名 年龄 总成绩：
mysql> select id as '学号',sname as ‘姓名’,age as '年龄', (math+english+java) as '总分' from student;
+--------+--------------+--------+--------+
| 学号 | ‘姓名’ | 年龄 | 总分 |
+--------+--------------+--------+--------+
| 1 | eric | 12 | 244 |
| 2 | james | 20 | 245 |
| 3 | Lucy | 18 | 215 |
| 4 | Merry | 17 | 220 |
+--------+--------------+--------+--------+
4 rows in set (0.00 sec)
- 给查询的数据添加常量
mysql> select id,sname,age,(math+english+java) as '总分','java就业班' as '班级' from student where id = 1;
+----+-------+------+--------+---------------+
| id | sname | age | 总分 | 班级 |
+----+-------+------+--------+---------------+
| 1 | eric | 12 | 244 | java就业班 |
+----+-------+------+--------+---------------+
1 row in set (0.00 sec)
- 查询时去除重复记录
mysql> select distinct(sname) from student; # distinct(字段名) 根据字段名去重
+-------+
| sname |
+-------+
| eric |
| james |
| Lucy |
| Merry |
+-------+
1.3 mysql的条件查询
在实际工作中，查询数据表数据的时候，绝大多数的情况下都是根据业务需求进行条件查询。所以我们需要了解mysql中条件查询的一些基本语法。使用条件查询需要用到关键字where。
- 查询id为2的学员信息
mysql> select * from student where id = 2;
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 2 | james | 20 | USA | 67 | 99 | 79 |
+----+-------+------+---------+------+---------+------+
1 row in set (0.00 sec)
我们也可以在where关键字后面拼接一些逻辑关键字and or。
- 查询名称叫eric并且address是USA的学员信息(多个条件必须同时满足)
mysql> select * from student where name = 'eric' and address = 'USA';
+----+------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+------+------+---------+------+---------+------+
| 5 | eric | 13 | USA | 90 | 60 | 65 |
+----+------+------+---------+------+---------+------+
1 row in set (0.00 sec)
- 查询名称叫eric或者address是USA的学员信息(多个条件只要满足其一即可)
mysql> select * from student where name = 'eric' or address = 'USA';
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 2 | james | 20 | USA | 67 | 99 | 79 |
| 5 | eric | 13 | USA | 90 | 60 | 65 |
+----+-------+------+---------+------+---------+------+
3 rows in set (0.00 sec)
where后面也可以跟一些数学运算符( > < >= <= <>)
- 查询年龄大于15岁的学员信息
mysql> select * from student where age>=15;
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 2 | james | 20 | USA | 67 | 99 | 79 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 4 | Merry | 17 | GER | 66 | 98 | 56 |
+----+-------+------+---------+------+---------+------+
3 rows in set (0.00 sec)
mysql> select * from student where age <>20; # 查询年龄不等于20岁的员工信息 mysql中不等于我们一般使用<>
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 4 | Merry | 17 | GER | 66 | 98 | 56 |
| 5 | eric | 13 | USA | 90 | 60 | 65 |
+----+-------+------+---------+------+---------+------+
4 rows in set (0.00 sec)
区间查询的操作
- 查询java分数在80-90分这个区间的学员信息
mysql> select * from student where java >=80 and java <=90;
+----+------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+------+------+---------+------+---------+------+
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
+----+------+------+---------+------+---------+------+
1 row in set (0.00 sec)
我们也有专门的区间查询操作的关键字 between…and…
mysql> select * from student where java between 80 and 90;
+----+------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+------+------+---------+------+---------+------+
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
+----+------+------+---------+------+---------+------+
1 row in set (0.00 sec)
- 关于判空条件(is null is not null)
查询地址为null的学员信息
mysql> select * from student where address is null;
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 6 | curry | NULL | NULL | 95 | 95 | 95 |
+----+-------+------+---------+------+---------+------+
1 row in set (0.00 sec)
查询地址不为null的学员信息
mysql> select * from student where address is not null;
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 2 | james | 20 | USA | 67 | 99 | 79 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 4 | Merry | 17 | GER | 66 | 98 | 56 |
| 5 | eric | 13 | USA | 90 | 60 | 65 |
+----+-------+------+---------+------+---------+------+
5 rows in set (0.00 sec)
- NULL值和空值(“”)的使用
NULL值得值为空。“”值得是字段存在值，但是值的内容为空。
面试题: NULL和 ‘’ 之间的区别?
1、null是未知的，且占用空间的。null使得索引、索引统计和值都更加复杂，并且影响优化器的判断。但是空值(‘’)是不占用空间的，注意空值的’'之间是没有空格。
2、在进行count()统计某列的记录数的时候，如果采用的 NULL 值，会被系统自动忽略掉，但是空值是会进行统计到其中的。
mysql> select * from student;
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 2 | james | 20 | USA | 67 | 99 | 79 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 4 | Merry | 17 | GER | 66 | 98 | 56 |
| 5 | eric | 13 | USA | 90 | 60 | 65 |
| 6 | curry | NULL | NULL | 95 | 95 | 95 |
| 7 | Green | NULL | | 70 | 80 | 60 |
+----+-------+------+---------+------+---------+------+
7 rows in set (0.00 sec)
mysql> select count(age) from student; # 根据age字段统计数据表中的总记录数(不包括NULL值的记录)
+------------+
| count(age) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
mysql> select count(address) from student;# 根据address字段统计数据表中的总记录数（包括''值的记录）
+----------------+
| count(address) |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)
3、判断null使用is null或者is not null，但判断空字符使用 ='‘或者 <>’'来进行处理。
mysql> select * from student where address = '';
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 7 | Green | NULL | | 70 | 80 | 60 |
+----+-------+------+---------+------+---------+------+
1 row in set (0.00 sec)
mysql> select * from student where address is null;
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 6 | curry | NULL | NULL | 95 | 95 | 95 |
+----+-------+------+---------+------+---------+------+
1 row in set (0.00 sec)
4、如果我们想在某一字段上面建立索引(建立索引的目的是为了提高查询效率)。最好所在列的值不为NULL。可以为‘’。
- in关键字实现条件查询
in关键字可以实现等值判断，作用类似于or关键字的作用。
mysql> select * from student where age in(12,13,18,19); # 等值判断 只要年龄匹配in后面的任意一个值 都会显示出来
+----+-----------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-----------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 5 | eric | 13 | USA | 90 | 60 | 65 |
| 8 | 张三 | 18 | 中国 | 70 | 80 | 75 |
| 9 | 张无忌 | 18 | 中国 | 70 | 80 | 75 |
| 10 | 张 | 18 | 中国 | 70 | 80 | 75 |
| 11 | 小张 | 18 | 中国 | 70 | 80 | 75 |
| 12 | 小张李 | 18 | 中国 | 70 | 80 | 75 |
+----+-----------+------+---------+------+---------+------+
8 rows in set (0.00 sec)
- 模糊查询
查询条件不精确。我们就称之为模糊查询，模糊查询使用的关键字是like。我们使用like查询的时候，用到了两个通配符：
%：用来描述任意个字符
_：有且只能描述1个字符
需求1： 查询名称以张开头的学员信息
mysql> select * from student where name like '张%'; # %指代匹配0个1个或多个字符
+----+-----------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-----------+------+---------+------+---------+------+
| 8 | 张三 | 18 | 中国 | 70 | 80 | 75 |
| 9 | 张无忌 | 18 | 中国 | 70 | 80 | 75 |
| 10 | 张 | 18 | 中国 | 70 | 80 | 75 |
+----+-----------+------+---------+------+---------+------+
3 rows in set (0.00 sec)
需求2： 查询名称以张开头但是姓名只有2个字的学员信息
mysql> select * from student where name like '张_'; # _指代有且只会匹配一个字符
+----+--------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+--------+------+---------+------+---------+------+
| 8 | 张三 | 18 | 中国 | 70 | 80 | 75 |
+----+--------+------+---------+------+---------+------+
1 row in set (0.00 sec)
需求3：查询名称中包含张的学员信息
mysql> select * from student where name like '%张%';
+----+-----------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-----------+------+---------+------+---------+------+
| 8 | 张三 | 18 | 中国 | 70 | 80 | 75 |
| 9 | 张无忌 | 18 | 中国 | 70 | 80 | 75 |
| 10 | 张 | 18 | 中国 | 70 | 80 | 75 |
| 11 | 小张 | 18 | 中国 | 70 | 80 | 75 |
| 12 | 小张李 | 18 | 中国 | 70 | 80 | 75 |
+----+-----------+------+---------+------+---------+------+
5 rows in set (0.00 sec)
1.4 mysql中的聚合函数
聚合函数对一组值执行计算并返回单一(一行一列)的值。
- 统计当前数据表中有多少条记录
mysql> select count(*) from student; # * 代表的是字段值非NULL的任意字段
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
mysql> select count(age) from student; # age 以age字段为标准进行计数统计，如果age的值为NULL，那么对应的记录就不会统计进来。
+------------+
| count(age) |
+------------+
| 10 |
+------------+
1 row in set (0.00 sec)
- 统计当前表中学员的java成绩的总分
mysql> select sum(java) from student; # sum聚合函数进行求和运算
+-----------+
| sum(java) |
+-----------+
| 897 |
+-----------+
1 row in set (0.00 sec)
- 求学员中数学的最高分（最低分）
mysql> select max(math) from student; # max 最高分
+-----------+
| max(math) |
+-----------+
| 95 |
+-----------+
1 row in set (0.00 sec)
mysql> select min(math) from student;# min 最低分
+-----------+
| min(math) |
+-----------+
| 56 |
+-----------+
1 row in set (0.00 sec)
- 求学员中java学科的平均分
mysql> select avg(java) from student; # avg 求平均数 但是在统计的时候，不会将NULL值所在的记录统计进去。
+-----------+
| avg(java) |
+-----------+
| 74.7500 |
+-----------+
1 row in set (0.00 sec)
1.5 分页查询
分页使用的关键字是limit。limit后面跟两个参数。
select * from 表名 limit 参数,参数2。
参数1：指代的是查询记录的起始索引值。
参数2：查询数据的条数。
mysql> select * from student limit 0,4; # 查询第一页的数据，每页显示4条记录
+----+-------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-------+------+---------+------+---------+------+
| 1 | eric | 12 | CHN | 78 | 89 | 77 |
| 2 | james | 20 | USA | 67 | 99 | 79 |
| 3 | Lucy | 18 | ENG | 56 | 69 | 90 |
| 4 | Merry | 17 | GER | 66 | 98 | 56 |
+----+-------+------+---------+------+---------+------+
mysql> select * from student limit 4,4; # 查询第二页的数据，每页显示4条记录
+----+--------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+--------+------+---------+------+---------+------+
| 5 | eric | 13 | USA | 90 | 60 | 65 |
| 6 | curry | NULL | NULL | 95 | 95 | 95 |
| 7 | Green | NULL | | 70 | 80 | 60 |
| 8 | 张三 | 18 | 中国 | 70 | 80 | 75 |
+----+--------+------+---------+------+---------+------+
4 rows in set (0.00 sec)
mysql> select * from student limit 8,4; # 查询第三页的数据
+----+-----------+------+---------+------+---------+------+
| id | name | age | address | math | english | java |
+----+-----------+------+---------+------+---------+------+
| 9 | 张无忌 | 18 | 中国 | 70 | 80 | 75 |
| 10 | 张 | 18 | 中国 | 70 | 80 | 75 |
| 11 | 小张 | 18 | 中国 | 70 | 80 | 75 |
| 12 | 小张李 | 18 | 中国 | 70 | 80 | 75 |
+----+-----------+------+---------+------+---------+------+
4 rows in set (0.00 sec)
问题： 查询第n页的数据呢?
select * from student limit (n-1)*4,4 ;
n: 页码值
4：页面的容量(每页显示的条数)
注意：不同数据库，分页语句是不一样的。
1.6 查询排序
语法：排序关键字是order by。
select 字段名称 from student order by 字段名称(表达式) desc/asc。
desc: 降序排序 asc:升序排序
- 按照学员的math科目的成绩进行降序排序
mysql> select id,name,age,address,math from student order by math desc; # 按照指定的字段进行排序
+----+-----------+------+-----------+------+
| id | name | age | address | math |
+----+-----------+------+-----------+------+
| 6 | curry | NULL | NULL | 95 |
| 5 | eric | 13 | USA | 90 |
| 1 | eric | 12 | CHN | 78 |
| 7 | Green | NULL | | 70 |
| 8 | 张三 | 18 | 中国 | 70 |
| 9 | 张无忌 | 18 | 中国 | 70 |
| 10 | 张 | 18 | 中国 | 70 |
| 11 | 小张 | 18 | 中国 | 70 |
| 12 | 小张李 | 18 | 中国 | 70 |
| 2 | james | 20 | USA | 67 |
| 4 | Merry | 17 | GER | 66 |
| 13 | 马六 | 19 | 阿富汗 | 65 |
| 14 | 赵四 | 20 | 叙利亚 | 65 |
| 3 | Lucy | 18 | ENG | 56 |
+----+-----------+------+-----------+------+
14 rows in set (0.00 sec)
我们也可以根据表达式进行排序。
- 根据学员的总分进行降序排序
mysql> select id,name,age,address,(math + english + java) as '总分' from student order by (math + english + java) desc; # (math + english + java) 就是一个表达式。我们可以根据表达式进行排序。但是不能使用表达式的别名进行排序。
+----+-----------+------+-----------+--------+
| id | name | age | address | 总分 |
+----+-----------+------+-----------+--------+
| 6 | curry | NULL | NULL | 285 |
| 2 | james | 20 | USA | 245 |
| 1 | eric | 12 | CHN | 244 |
| 8 | 张三 | 18 | 中国 | 225 |
| 9 | 张无忌 | 18 | 中国 | 225 |
| 10 | 张 | 18 | 中国 | 225 |
| 11 | 小张 | 18 | 中国 | 225 |
| 12 | 小张李 | 18 | 中国 | 225 |
| 4 | Merry | 17 | GER | 220 |
| 3 | Lucy | 18 | ENG | 215 |
| 5 | eric | 13 | USA | 215 |
| 7 | Green | NULL | | 210 |
| 14 | 赵四 | 20 | 叙利亚 | 110 |
| 13 | 马六 | 19 | 阿富汗 | NULL |
+----+-----------+------+-----------+--------+
14 rows in set (0.00 sec)
如果写成：
mysql> select id,name,age,address,(math + english + java) as '总分' from student order by '总分' desc; # 排序不生效
+----+-----------+------+-----------+--------+
| id | name | age | address | 总分 |
+----+-----------+------+-----------+--------+
| 1 | eric | 12 | CHN | 244 |
| 2 | james | 20 | USA | 245 |
| 3 | Lucy | 18 | ENG | 215 |
| 4 | Merry | 17 | GER | 220 |
| 5 | eric | 13 | USA | 215 |
| 6 | curry | NULL | NULL | 285 |
| 7 | Green | NULL | | 210 |
| 8 | 张三 | 18 | 中国 | 225 |
| 9 | 张无忌 | 18 | 中国 | 225 |
| 10 | 张 | 18 | 中国 | 225 |
| 11 | 小张 | 18 | 中国 | 225 |
| 12 | 小张李 | 18 | 中国 | 225 |
| 13 | 马六 | 19 | 阿富汗 | NULL |
| 14 | 赵四 | 20 | 叙利亚 | 110 |
+----+-----------+------+-----------+--------+
14 rows in set (0.01 sec)
但是别名如果不带’'又可以。
mysql> select id,name,age,address,(math + english + java) as 总分 from student order by 总分 desc;
+----+-----------+------+-----------+--------+
| id | name | age | address | 总分 |
+----+-----------+------+-----------+--------+
| 6 | curry | NULL | NULL | 285 |
| 2 | james | 20 | USA | 245 |
| 1 | eric | 12 | CHN | 244 |
| 8 | 张三 | 18 | 中国 | 225 |
| 9 | 张无忌 | 18 | 中国 | 225 |
| 10 | 张 | 18 | 中国 | 225 |
| 11 | 小张 | 18 | 中国 | 225 |
| 12 | 小张李 | 18 | 中国 | 225 |
| 4 | Merry | 17 | GER | 220 |
| 3 | Lucy | 18 | ENG | 215 |
| 5 | eric | 13 | USA | 215 |
| 7 | Green | NULL | | 210 |
| 14 | 赵四 | 20 | 叙利亚 | 110 |
| 13 | 马六 | 19 | 阿富汗 | NULL |
+----+-----------+------+-----------+--------+
14 rows in set (0.00 sec)
如果我们使用升序排序，就使用asc进行排序
mysql> select id,name,age,address,(math + english + java) as 总分 from student order by 总分 asc;
+----+-----------+------+-----------+--------+
| id | name | age | address | 总分 |
+----+-----------+------+-----------+--------+
| 13 | 马六 | 19 | 阿富汗 | NULL |
| 14 | 赵四 | 20 | 叙利亚 | 110 |
| 7 | Green | NULL | | 210 |
| 3 | Lucy | 18 | ENG | 215 |
| 5 | eric | 13 | USA | 215 |
| 4 | Merry | 17 | GER | 220 |
| 8 | 张三 | 18 | 中国 | 225 |
| 9 | 张无忌 | 18 | 中国 | 225 |
| 10 | 张 | 18 | 中国 | 225 |
| 11 | 小张 | 18 | 中国 | 225 |
| 12 | 小张李 | 18 | 中国 | 225 |
| 1 | eric | 12 | CHN | 244 |
| 2 | james | 20 | USA | 245 |
| 6 | curry | NULL | NULL | 285 |
+----+-----------+------+-----------+--------+
14 rows in set (0.00 sec)
注意：如果我们的排序字段(表达式)对应的值为NULL。那么这条记录也会参与排序.默认是最小值，如果升序排序，就会显示在第一条。
任何数字和NULL值进行运算。最后得到的结果都是NULL。
在排序的时候，我们还可以进行多个条件的排序。
- 按照总分降序排序，也按照年龄进行降序排序。
语法：select 字段名称 from student order by 字段名称1(表达式) desc/asc, 字段名称2(表达式) desc/asc。
mysql> select id,name,age,address,(math + english + java) as '总分' from student order by (math + english + java) desc,age desc;
+----+-----------+------+-----------+--------+
| id | name | age | address | 总分 |
+----+-----------+------+-----------+--------+
| 6 | curry | NULL | NULL | 285 |
| 2 | james | 20 | USA | 245 |
| 1 | eric | 12 | CHN | 244 |
| 4 | Merry | 17 | GER | 220 |
| 3 | Lucy | 18 | ENG | 215 |
| 5 | eric | 13 | USA | 215 |
| 7 | Green | NULL | | 210 |
| 10 | 张 | 18 | 中国 | 207 |
| 12 | 小张李 | 23 | 中国 | 203 |
| 8 | 张三 | 16 | 中国 | 194 |
| 9 | 张无忌 | 17 | 中国 | 192 |
| 11 | 小张 | 19 | 中国 | 148 |
| 14 | 赵四 | 20 | 叙利亚 | 89 |
| 13 | 马六 | 25 | 阿富汗 | NULL |
+----+-----------+------+-----------+--------+
14 rows in set (0.00 sec)
1.7 分组查询
分组查询的关键字是group by。
语法：select 字段，聚合函数统计表达式 from 表名 group by 字段名称。
注意：如果我们的查询语句使用了group by关键字进行分组统计，那么select 后面就不能随意的写字段了。select后面必须带上分组的字段，以及进行对应统计的聚合函数表达式。
创建一张表：
mysql> create table emp(
-> id int not null,
-> name varchar(30) not null,
-> dept varchar(30) not null,
-> gender varchar(10) not null,
-> address varchar(30) not null
-> );
Query OK, 0 rows affected (0.01 sec)
插入的数据如下：
mysql> select * from emp;
+----+--------+-----------+--------+---------+
| id | name | dept | gender | address |
+----+--------+-----------+--------+---------+
| 1 | eric | 技术部 | 男 | USA |
| 2 | lily | 市场部 | 女 | CHN |
| 3 | ketty | 技术部 | 女 | CHN |
| 4 | lucy | 技术部 | 女 | USA |
| 5 | miller | 运营部 | 男 | USA |
| 6 | kobe | 运营部 | 男 | USA |
+----+--------+-----------+--------+---------+
6 rows in set (0.00 sec)
- 需求：查询并统计每个部分的员工人数
mysql> select dept as 部门,count(*) as 人数 from emp group by dept;
+-----------+--------+
| 部门 | 人数 |
+-----------+--------+
| 市场部 | 1 |
| 技术部 | 3 |
| 运营部 | 2 |
+-----------+--------+
3 rows in set (0.00 sec)
- 需求：统计不同性别的员工个数
mysql> select gender,count(*) from emp group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女 | 3 |
| 男 | 3 |
+--------+----------+
2 rows in set (0.00 sec)
- 需求：展示不同国籍的员工数量
mysql> select address,count(*) from emp group by address;
+---------+----------+
| address | count(*) |
+---------+----------+
| CHN | 2 |
| USA | 4 |
+---------+----------+
2 rows in set (0.00 sec)
- 需求：统计不同部门的平均薪资，并按照降序排序
mysql> select dept,avg(salary) from emp group by dept order by avg(salary) desc;
+-----------+-------------+
| dept | avg(salary) |
+-----------+-------------+
| 市场部 | 900.0000 |
| 技术部 | 850.0000 |
| 运营部 | 725.0000 |
+-----------+-------------+
3 rows in set (0.00 sec)
注意：如果一个sql语句设计到分组和排序，那么order by一定是在整个sql语句是最后面。
- 需求：统计不同部门的平均薪资，将大于800的部门平均薪资展示出来，并按照降序排序
这里涉及到分组之后的过滤，我们使用having关键字。
mysql> select dept,avg(salary) from emp group by dept having avg(salary)>800 order by avg(salary) desc;
+-----------+-------------+
| dept | avg(salary) |
+-----------+-------------+
| 市场部 | 900.0000 |
| 技术部 | 850.0000 |
+-----------+-------------+
2 rows in set (0.00 sec)
注意： where和having之间的区别
mysql> select dept,avg(salary) from emp where address = 'USA' group by dept having avg(salary)>100 order by avg(salary) desc; # 先查询美国的所有员工，然后按照部门进行分组，接下来过滤出平均薪资是100的部门信息，最后按照平均薪资进行降序排序。
+-----------+-------------+
| dept | avg(salary) |
+-----------+-------------+
| 技术部 | 775.0000 |
| 运营部 | 725.0000 |
+-----------+-------------+
2 rows in set (0.00 sec)
where:分组之前的过滤。
having:分组之后的过滤。
总结：SQL语句的分类
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
- DQL(数据库查询语言)
比如我们写的最多的查询语句 select语句
- DML(数据库操纵语言)
执行数据表更新的操作（insert update delete语句）
- DDL(数据库定义语言)
create语句(create table语句 、create index语句、create view语句、create database语句、alter语句… )
- DCL(数据库控制语言)
grant语句(给用户进行授权的语句、进行事务操作的语句 commit语句 rollback语句)
2.Mysql加强
2.1 数据库约束
什么是数据库约束：对用户操作表的数据进行约束。(非空 唯一 默认值 主键 自增长 外键…)。
2.1.1 默认值
当用户对默认值对应的字段进行数据插入的时候，如果不给对应字段设置值，就使用默认值。
CREATE TABLE student(
id INT,
`name` VARCHAR(20),
address VARCHAR(20) DEFAULT '贵州贵阳' -- 给address字段设置默认值 关键字是default
)
- 当设置默认值的字段没有插入数据的时候，mysql会给当前字段自动分配默认值。
INSERT INTO student(id,`name`) VALUES(1,'铁蛋')
- 当给默认的字段插入的数据是NULL值的时候，此时对应字段的值就为NULL。
INSERT INTO student(id,`name`,address) VALUES(2,'铁柱',NULL);
- 给默认值插入的数据是非NULL的值，那么对应的字段显示的就是用户插入的值。
INSERT INTO student(id,`name`,address) VALUES(3,'张三','广东广州');
2.1.2 非空约束
作用：就是强制用户必须给对应的字段设置值。设置的值必须不是NULL值。
ALTER TABLE student ADD COLUMN gender VARCHAR(2) NOT NULL; -- not null 设置该字段的非空约束
INSERT INTO student(id,`name`,address,gender) VALUES(4,'李四','广东东莞','男'); -- 正常添加 没有问题
INSERT INTO student(id,`name`,address,gender) VALUES(5,'王五','广东深圳',NULL); -- 由于插入NULL值 会报错，不允许添加NULL值
INSERT INTO student(id,`name`,address,gender) VALUES(6,'马六','江西南昌',''); -- 正常添加 对应的是内容为空的字符串
2.1.3 唯一约束
作用：顾名思义，字段的值不能重复，必须唯一。我们可以在创建表的时候，指定约束
CREATE TABLE student(
id INT,
`name` VARCHAR(20),
address VARCHAR(20) DEFAULT '贵州贵阳',-- 默认约束
gender VARCHAR(2) NOT NULL, -- 非空约束
telphone VARCHAR(20) UNIQUE -- UNIQUE 唯一约束
)
如何查看建立好的唯一约束:
INSERT INTO student(id,`name`,address,gender,telphone) VALUES(5,'王五','广东深圳','男','13545678902');
INSERT INTO student(id,`name`,address,gender,telphone) VALUES(6,'马六','广东广州','男','13545678902');-- 会报错 因为telphone字段重复
2.1.4 主键约束
主键约束： 非空 + 唯一
特别说明：在通常情况下，每张表都会设置一个主键字段，用于标记表中每条记录的唯一性。在开发中，对于主键字段的选择尽量避免使用业务字段，尽量选择跟业务没有实际关联的自动。
如何创建主键约束，我们一般在创建表的时候，就把主键约束给设置好。也可以使用alter语句增加索引。
ALTER TABLE student ADD PRIMARY KEY(id);
也可以在创建表的时候，就指定好主键索引。
CREATE TABLE student(
id INT PRIMARY KEY, -- 设置主键索引
`name` VARCHAR(20),
address VARCHAR(20) DEFAULT '贵州贵阳',-- 默认约束
gender VARCHAR(2) NOT NULL, -- 非空约束
telphone VARCHAR(20) UNIQUE -- UNIQUE 唯一约束
)
设置好之后，效果如下：
我们添加数据：
INSERT INTO student(`name`,address,gender,telphone) VALUES('eric','美国','男','15436780943'); -- 不报错 id为默认值0
INSERT INTO student(`name`,address,gender,telphone) VALUES('james','美国','男','15436780946'); -- 报错，id会重复
我们没有设置id的值，但是此时会报一个警告。默认的数据id为0.但是如果我们再次添加数据，会报错。
现在我们没有指定主键的生成策略，每次插入数据的时候，都需要自己手动的指定主键的值。其实mysql对主键的生成，有对应的策略
- 自增长策略
应用于数字类型的字段。快速生成主键编号，并且查询起来非常方便。
我们可以在数据表创建的时候，指定自增长策略。
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长
`name` VARCHAR(20),
address VARCHAR(20) DEFAULT '贵州贵阳',-- 默认约束
gender VARCHAR(2) NOT NULL, -- 非空约束
telphone VARCHAR(20) UNIQUE -- UNIQUE 唯一约束
)
当然我们也可以在表创建完成之后，设置自增长策略。
INSERT INTO student(`name`,address,gender,telphone) VALUES('james','美国','男','15436780948'); -- id 默认为1
INSERT INTO student(`name`,address,gender,telphone) VALUES('kobe','美国','男','15436780949'); -- id为2
INSERT INTO student(`name`,address,gender,telphone) VALUES('green','美国','男','15436780949'); -- telphone字段违反唯一约束，会报错 此时id为3的主键会被占用，下一次插入的数据id会从4开始
INSERT INTO student(`name`,address,gender,telphone) VALUES('green','美国','男','15436784549'); -- id为4
此时我们使用delete语句清空表数据。
DELETE FROM student;
INSERT INTO student(`name`,address,gender,telphone) VALUES('green','美国','男','15436784549'); # 此时id为5 还是从删除之前的数据开始自增长的。
此时我们使用truncate语句清空表数据。
TRUNCATE TABLE student;
INSERT INTO student(`name`,address,gender,telphone) VALUES('green','美国','男','15436784549'); # 此时id为1 主键的值从默认值1重新开始自增。
- UUID的策略
UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。在UUID的算法中，可能会用到诸如网卡MAC地址，IP，主机名，进程ID等信息以保证其独立性。
优点：全局唯一性、安全性、可移植性。
缺点：UUID占用内存空间过大，并且由于UUID不连续，查询的时候，性能非常差。
2.1.5 外键约束
作用：约束两张表的数据。
使用外键：
需要创建两张表，一张是部门表，一张是员工表。
-- 创建部门表(主表)
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门id
deptName VARCHAR(30) NOT NULL,-- 部门名称
location VARCHAR(30) NOT NULL -- 部门地址
)
-- 创建员工表(外表/副表)
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(30) NOT NULL,
deptId INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) -- 设置外键
)
添加数据：先添加主表，再添加副表。
修改数据：先修改副表，再修改主表。
删除数据：先删除副表，再删除主表。
新增数据：
-- 新增副表数据
INSERT INTO emp(empName,deptId) VALUES('eric',2) -- 可以正常添加
INSERT INTO emp(empName,deptId) VALUES('james',6) -- 报错 违反了外键约束
修改数据：
-- 修改部门 会报错
UPDATE dept SET id = 6 WHERE id = 5;
-- 修改员工报错
UPDATE emp SET deptId = 6 WHERE id = 2;
删除数据：
-- 删除部门表 报错
DELETE FROM dept WHERE id = 5;
-- 删除员工 正常删除
DELETE FROM emp WHERE id = 2;
通过上面，我们咋执行修改的时候，会出现很多问题。有的时候不能正常的执行修改删除的操作。此时我们可以通过级联操作来解决问题。
2.1.6 级联操作
当有了外键约束之后， 必须先删除或修改副表中的数据，才能修改或删除主表中的数据。但是有的时候，我们希望可以直接删除或修改主表中的数据，从而影响副表中的数据，此时我们可以使用级联操作来实现。
级联修改：on update cascade
级联删除：on delete cascade
我们先删除之前的外表(员工表)。再重新创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(30) NOT NULL,
deptId INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
ON UPDATE CASCADE ON DELETE CASCADE -- 设置外键 并设置级联操作
)
- 测试级联修改
修改主表中的数据：
UPDATE dept SET id = 6 WHERE id=5; # 副表中(emp)的部门id为5的会变成6
- 测试级联删除
删除主表中的数据：
DELETE FROM dept WHERE id = 1; # 主表中的数据删除之后，副表中对应的数据也会被删除
2.2 三大范式
范式：就是在创建数据表的时候，需要遵循的约束和规范。
第一范式：创建数据表的时候，表中的字段必须具备原子性。字段描述的是最小事物，不能再次分割。
创建的表字段如下：
id name(学员名称) score(课程分数)
上面的字段中，score就不符合规范。因为这个字段不具备原子性，能够再次分割。因为score包含很多科目的分数。
第二范式：在第一范式的基础上，创建的数据表。只能描述一件事物，不能一张表描述多个事物。
创建的表字段如下：
id(员工id) username(员工名称) address(员工地址) deptName(部门名称) location(部门地址)
上面的字段中，描述的就不是一件事物，而是多个事物(员工信息 部门信息)。这种情况一般要拆分成两张表来分开描述。
第三范式：在第二范式的基础上，每一列都直接与主键建立关系，通过主键就可以查询到相应的值，通过主键来描述记录的唯一性。
2.3 关联查询(多表查询)
关联查询我们也称之为多表查询，将多张相关联的数据查询出来。
需求：查询员工信息及其对应的部门信息。
SELECT * FROM emp,dept; -- 交叉连接查询，产生笛卡尔乘积现象（两张表的记录数相乘），带来的后果是查询出大量冗余的数据
我们需要基于这张笛卡尔乘积表来筛选出正确的记录。
2.3.1 内连接
内连接就是查询出多张表中相匹配的数据。
- 隐式内连接
语法： select 字段 from 表1,表2 where 表1.字段名称=表2.字段名称
SELECT * FROM emp,dept WHERE emp.deptId = dept.id; # where后面的条件 表名.字段名称
注意：select 字段后面的*号指的是查询两张表的所有字段。但是在实际业务需求中，我们并不是将所有字段全部查询出来，我们只需要查询部分字段就可以了。
SELECT emp.id,emp.empName,dept.deptName,dept.location FROM emp,dept WHERE emp.deptId = dept.id; # 字段的写法 表名.字段名称
但是通过表名.字段名称很麻烦的话，我们也可以给表起别名。
SELECT e.id,e.empName,d.deptName,d.location FROM emp AS e,dept AS d WHERE e.deptId = d.id; # as 给表起别名 我们也可以去掉as关键字
- 显式内连接
内连接有一个关键字 inner join
语法： select 字段 from 表1 inner join 表2 on 条件
SELECT emp.id,emp.empName,dept.deptName,dept.location FROM emp INNER JOIN dept ON emp.deptId = dept.id;
2.3.2 外连接
外连接：查询某一张表中的所有数据，如果另外一张中有互相匹配的数据则全部显示出来，如果没有与之匹配的数据，则以NULL值填充，拼接成一条完整的记录。
需求： 查询所有部门信息及其对应的员工信息。
外连接根据细分，又可以细分成左外连，右外连。
- 左外连接
查询的时候，以左表的数据为基准，将左表中的数据全部查询出来，如果右表中有与之匹配的数据，就显示，否则以NULL值填充，拼接成一条完整的记录。
语法格式：select 字段 from 表1 left (outer) join 表2 on 条件
SELECT dept.id,dept.deptName,dept.location,emp.empName FROM dept LEFT OUTER JOIN emp ON dept.id = emp.deptId;
-- outer关键字可以省略
SELECT dept.id,dept.deptName,dept.location,emp.empName FROM dept LEFT JOIN emp ON dept.id = emp.deptId;
此时会将左表(dept)中的数据全部显示出来
如果写成这样子:
SELECT dept.id,dept.deptName,dept.location,emp.empName FROM emp LEFT OUTER JOIN dept ON dept.id = emp.deptId
此时会将左表(emp)中的数据全部显示出来
- 右外连接
查询的时候，以右表中的数据为基准，将右表中的数据全部查询出来，如果左表中有与之匹配的数据，就显示，否则以NULL值填充，拼接成一条完整的记录。
语法格式：select 字段 from 表1 right (outer) join 表2 on 条件
SELECT dept.id,dept.deptName,dept.location,emp.empName FROM emp RIGHT OUTER JOIN dept ON dept.id = emp.deptId;
-- outer 也可以省略
SELECT dept.id,dept.deptName,dept.location,emp.empName FROM emp RIGHT JOIN dept ON dept.id = emp.deptId
2.3.3 自连接
自连接就是自己和自己相连。下面我们创建一张表：
CREATE TABLE employee (
empno INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工名称',
job VARCHAR(9) COMMENT '职位',
mgr INT COMMENT '上级领导编号',
hiredate DATE COMMENT '入职日期',
sal INT COMMENT '基本薪资',
comm INT COMMENT '奖金',
deptno INT COMMENT '部门编号'
);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7369,'史密斯','店员',7902,'1980-12-17',800,NULL,20);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'艾伦','售货员',7698,'1981-02-20',1600,300,30);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7521,'沃德','售货员',7698,'1981-02-22',1250,500,30);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566,'琼斯','经理',7839,'1981-04-02',2975,NULL,20);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7654,'马丁','售货员',7698,'1981-09-28',1250,1400,30);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7698,'布莱克','经理',7839,'1981-05-01',2850,NULL,30);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7782,'克拉克','经理',7839,'1981-06-09',2450,NULL,10);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7788,'斯科特','分析师',7566,'1987-04-19',3000,NULL,20);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7839,'国王','总统',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7844,'特纳','售货员',7698,'1981-09-08',1500,0,30);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7876,'亚当斯','店员',7788,'1987-05-23',1100,NULL,20);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7900,'詹姆斯','店员',7698,'1981-12-03',950,NULL,30);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7902,'福特','分析师',7566, '1981-12-03',3000,NULL,20);
INSERT INTO employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934,'米勒','店员',7782,'1982-01-23',1300,NULL,10);
需求：查询匹配的员工信息及其对应的领导人信息(员工编号 员工姓名 员工职位 上级领导人姓名 上级领导人职位)
自连接的特点是：在同一张表中，一个字段的值引用了另外一个字段的值。
SELECT e1.empno,e1.ename,e1.job,e2.ename,e2.job FROM employee AS e1,employee AS e2 WHERE e1.mgr = e2.empno;
需求：查询所有员工信息及其对应的领导人信息
SELECT e1.empno AS 员工编号,e1.ename AS 员工姓名,e1.job AS 员工职位,e2.ename AS 领导人姓名,e2.job AS 领导人职位
FROM employee AS e1 LEFT JOIN employee AS e2 ON e1.mgr = e2.empno;
作业：
- 见附件
2.4 表与表之间的关系
2.4.1 1对1
强调的是两张表之间的记录是一一对应的关系。本质上两张表的数据描述的是同一件事物，但是由于表中字段数目过多，为了方便对表进行管理和维护，我们可以考虑将一张大表拆分成两张小表，两张小表之间是一一对应的关系(两张表的主键的值是一一对应的)。
比如：商品表和商品描述表。 商品表中的记录和商品描述表中的记录的数量是一样的。商品表的id和商品描述表的id是一一对应的。
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30),
price INT
)
CREATE TABLE product_desc(
id INT PRIMARY KEY AUTO_INCREMENT,
`desc` VARCHAR(40)
)
案例：查询商品信息及其对应的商品描述信息：
SELECT product.*,product_desc.desc FROM product,product_desc WHERE product.id = product_desc.id;
2.4.2 1对多
强调的是两张表之间，表关系需要用主外键来描述。一个表中的外键字段必须引用另外一张表中的主键字段的值。
比如： 员工表和部门表。一个部门里面有多个员工，反过来， 一个员工对应一个部门。
案例省略。
2.4.3 多对多
强调的是两张表之间，表关系需要用中间表来描述。中间表需要有两个外键字段，这两个外键字段的值分别引用另外两张表的主键字段的值。
比如：用户表和角色表。一个用户会对应多个角色，反过来，一个角色会被多个用户所持有。
案例： 创建用户表、角色表、中间表。然后查询员工信息及其对应的部门信息。
-- 创建员工表
CREATE TABLE `user`(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
)
-- 创建角色表
CREATE TABLE role(
id INT PRIMARY KEY AUTO_INCREMENT,
roleName VARCHAR(20),
roleDesc VARCHAR(20)
)
-- 创建中间表
CREATE TABLE role_user(
userId INT,
roleId INT,
CONSTRAINT userId_fk FOREIGN KEY(userId) REFERENCES emp(id),
CONSTRAINT roleId_fk FOREIGN KEY(roleId) REFERENCES role(id)
)
-- 查询员工信息及其对应的部门信息
SELECT user.*,role.roleName,role.roleDesc
FROM `user`,role,role_user WHERE user.id = role_user.userId AND role.id = role_user.roleId
-- 查询指定的员工信息及其对应的部门信息
SELECT user.*,role.roleName,role.roleDesc
FROM `user`,role,role_user WHERE user.id = role_user.userId AND role.id = role_user.roleId AND user.id = 1;
2.5 嵌套子查询
嵌套查询就是在一个select语句里面包含另外的select子语句。子语句可以出现在where关键字之后，当查询条件。也可以跟在from关键字之后，当前一张新表进行查询。
子查询结果集的表现形式：
单行单列 – 查询条件
多行单列 – 查询条件
单行多例 – 查询条件
多行多列 – 当成一张新的表
嵌套子查询的案例：
-- 1.查询与员工SCOTT是同一个部门的员工
-- 1.1 查询SCOTT的部门编号
SELECT deptno FROM employee WHERE ename = 'SCOTT';
-- 1.2 根据SCOTT的部门编号，查询对应的员工信息
SELECT * FROM employee WHERE deptno = 20
-- 1.3 综合以上步骤，编写嵌套子查询语句
SELECT * FROM employee WHERE deptno = (
SELECT deptno FROM employee WHERE ename = 'SCOTT' -- 子select语句 它的结果集是单行单列的
);
-- 2.查询基本工资高于JONES的员工
-- 2.1 查询jone的基本工资
SELECT sal FROM employee WHERE ename = 'JONES';
-- 2.2 根据jones的工作，查询比他高的员工信息
SELECT * FROM employee WHERE sal = 2975;
-- 2.3 综合以上步骤，编写嵌套子查询语句
SELECT * FROM employee WHERE sal > (
SELECT sal FROM employee WHERE ename = 'JONES'
)
-- 3.查询基本工资高于20号部门所有员工的的员工信息
-- 3.1 查询20号部门最高的工资
SELECT MAX(sal) FROM employee WHERE deptno=20;
-- 3.2 查询工资大于3000的员工信息
SELECT * FROM employee WHERE sal > 3000;
-- 3.3 综合以上步骤，编写嵌套子查询语句
SELECT * FROM employee WHERE sal > (
SELECT MAX(sal) FROM employee WHERE deptno=20
)
-- 4.查询工作职位和工资与MARTIN相同的员工信息
-- 4.1 分别查询MARTIN这个人的职位和工资
SELECT job FROM employee WHERE ename = 'MARTIN';
SELECT sal FROM employee WHERE ename = 'MARTIN';
-- 4.2 根据职位和工资查询对应的员工信息
SELECT * FROM employee WHERE job = 'SALESMAN' AND sal = 1250;
-- 4.3 综合以上步骤，编写嵌套子查询语句
SELECT * FROM employee WHERE job = (
SELECT job FROM employee WHERE ename = 'MARTIN'
) AND sal = (
SELECT sal FROM employee WHERE ename = 'MARTIN'
);
-- 另外一种写法
SELECT * FROM employee WHERE(job,sal) IN(SELECT job,sal FROM employee WHERE ename = 'MARTIN')
-- 5.查询有两个以上直接下属的员工信息
-- 5.1 统计大于2的mgr的编号有哪些?
SELECT mgr FROM employee GROUP BY mgr HAVING COUNT(mgr) > 2;
-- 5.2 综合以上步骤，编写嵌套子查询语句
SELECT * FROM employee WHERE empno IN(
SELECT mgr FROM employee GROUP BY mgr HAVING COUNT(mgr) > 2
)
-- 6.查询各个部门基本工资最高的员工信息
-- 6.1 查询每个部门最高的薪资 和部门编号
SELECT deptno, MAX(sal) AS 'maxSal' FROM employee GROUP BY deptno;
-- 6.2 综合以上步骤，编写嵌套子查询语句
SELECT * FROM employee AS e1,(
SELECT deptno, MAX(sal) AS 'maxSal' FROM employee GROUP BY deptno
) AS e2
WHERE e1.sal = e2.maxSal AND e1.deptno = e2.deptno;
2.6 Mysql中的函数
Mysql中函数分为两种，一种是系统函数，我们直接拿来用就可以，还有一种是自定义函数。今天讨论是mysql中的系统函数。
2.6.1 日期函数
SELECT NOW(); -- 返回当前系统时间(年-月-日 时:分:秒)
SELECT CURDATE();-- 返回当前系统时间(年-月-日)
SELECT CURTIME();-- 返回当前系统时间(时:分:秒)
SELECT DATE('2022-9-18 14:25:33'); -- 根据指定时间，提取年月日部分
SELECT EXTRACT(YEAR FROM '2022-9-18 14:25:33') -- 返回指定时间的年部分
SELECT EXTRACT(YEAR FROM NOW())
SELECT EXTRACT(MONTH FROM '2022-9-18 14:25:33') -- 返回指定时间的月部分
SELECT EXTRACT(DAY FROM '2022-9-18 14:25:33') -- -- 返回指定时间的天部分
SELECT EXTRACT(HOUR FROM '2022-9-18 14:25:33') -- 返回指定时间的小时部分
SELECT EXTRACT(MINUTE FROM '2022-9-18 14:25:33') -- 返回指定时间的分钟部分
SELECT EXTRACT(SECOND FROM '2022-9-18 14:25:33') -- 返回指定时间的秒部分
SELECT TIME('2021-12-12 12:12:12') -- 返回指定时间的时分秒部分
SELECT DATE_ADD(NOW(),INTERVAL 7 DAY); -- 在指定的日期基础上添加时间 7天
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR); -- 在指定的日期基础上添加时间 1年
SELECT DATE_ADD(NOW(),INTERVAL 1 HOUR); -- 在指定的日期基础上添加时间 1小时
SELECT DATE_ADD(NOW(),INTERVAL 1 MINUTE); -- 在指定的日期基础上添加时间 1分钟
SELECT DATE_ADD(NOW(),INTERVAL -7 DAY); -- 根据指定的日期时间，减几天
SELECT DATE_SUB(NOW(),INTERVAL 2 YEAR); -- 根据指定的日期时间，减2年
SELECT ADDDATE('1999-9-9',30);-- 在指定的时间上添加指定的天数
SELECT ADDTIME('2022-9-7 15:30:30','2:30:55') -- 在指定的日期上添加一个时间 返回一个新的时间
SELECT DATEDIFF('2022-9-7','1989-12-30'); -- 返回两个日期之间的天数
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y') -- 将指定的日期进行格式化处理
2.6.2 数学函数
SELECT ROUND(1.7); -- 2 四舍五入
SELECT ROUND(1.1); -- 1 四舍五入
SELECT ROUND(1.20998,2) -- 四舍五入 保留小数点后面的2位
SELECT CEIL(1.01); -- 向上取整
SELECT FLOOR(1.9999); -- 向下取整
SELECT TRUNCATE(1.699,1); -- 按照指定的位数进行截取。不会进行四舍五入
SELECT MOD(10,3) -- 取余数
SELECT ABS(-6); -- 取绝对值
SELECT SQRT(4); -- 去平方根
SELECT POWER(2,0), POWER(2,2),POWER(-3,3),POWER(-2.5,3) -- 取幂值 2^0 2^2 -3^3 -2.5^3
SELECT RAND(); -- 取 0<=v<=1之间的小数
-- 如果我现在想取i<=R<=j之间的整数R 公式 SELECT FLOOR(i + RAND()*(j-i+1));
-- 5-20之间的随机整数
SELECT FLOOR(5 + RAND()*16)
2.6.3 字符串函数
-- length() 求字符串的长度
SELECT LENGTH('Hello,world')
-- concat() 字符串的追加
SELECT CONCAT('hello','mysql')
-- insert() 字符串的替换 参数1：指定的字符串 参数2:截取的起始索引值 参数3：截取字符串的长度 参数3：需要替换的新的内容
SELECT INSERT('hello',2,3,'mysql'); -- hmysqlo
-- replace() 字符串的替换 将‘o’替换成‘k’
SELECT REPLACE('hello world','o','k');
-- substr() 按照指定的规则截取字符串，并将截取的字符串返回 参数1：指定的字符串 参数2:截取的起始索引值 参数3：截取字符串的长度
SELECT SUBSTR('hello world',2,4);
-- trim() 去除两边的空格
SELECT TRIM(' hello,world ');
-- reverse() 反转字符串
SELECT REVERSE('abcdefg');
-- 从左边开始截取指定长度的字符串
SELECT LEFT('hello',4);
-- 从右边截取指定长度的字符串
SELECT RIGHT("hello",4);
-- 转小写
SELECT LOWER('HELLO WoRlD');
-- 转大写
SELECT UPPER('hello world');
2.7 mysql事务(重点)
什么事务：
事务指的是一组逻辑操作，这些操作是最小单位不可分割的。这些操作要么全部执行成功，要么全部执行失败。
事务的特性(ACID)：
原子性(Atomicity)：事务的逻辑操作是最小单位不可分割的。
一致性(Consistency)：事务提交前的数据和事务提交后的数据是一致的。
隔离性(Isolation)：多个事务之间应该是互相独立不能影响的。
持久性(Durability)：事务提交之后，发生的数据变化需要持久化的保存在数据表里面。
事务的分类：
事务分为隐式事务和显式事务两种。我们的DML语句（insert、update、delete）就是隐式事务。
隐式事务：没有事务的开始标记，也没有事务的结束标记。隐式事务具备自动提交事务的功能。
显式事务：事务需要有开始事务的标记，有结束事务的标记。进行DML语句操作之后，我们需要手动的提交事务。
我们mysql数据库事务的状态默认是自动提交
mysql> select @@autocommit; -- 查看当前会话的事务状态
+--------------+
| @@autocommit |
+--------------+
| 1 | # 1 自动提交
+--------------+
1 row in set (0.00 sec)
我们也可以更改事务的状态
mysql> set autocommit=0; -- 注意： 这种修改的方式是修改当前会话的事务状态 将当前会话的事务状态修改成手动提交事务
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
2.7.1 如何开启事务
start transaction; 开启事务 -- 事务开启的标志
commit; 提交事务 -- 事务结束的标志
rollback;回滚事务
演示事务：
mysql> start transaction; -- 开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student values(2,'miller','USA','男','13456709872');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+---------+--------+-------------+
| id | name | address | gender | telphone |
+----+--------+---------+--------+-------------+
| 1 | green | 美国 | 男 | 15436784549 |
| 2 | miller | USA | 男 | 13456709872 |
+----+--------+---------+--------+-------------+
2 rows in set (0.00 sec)
mysql> rollback; -- 回滚事务
Query OK, 0 rows affected (0.00 sec)
mysql> commit; -- 提交事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+-------+---------+--------+-------------+
| id | name | address | gender | telphone |
+----+-------+---------+--------+-------------+
| 1 | green | 美国 | 男 | 15436784549 |
+----+-------+---------+--------+-------------+
1 row in set (0.00 sec)
2.7.2 事务的并发问题
在数据库中，如果有多个事务同时对某些数据进行操作。此时如果没有事务隔离级别机制，就容易出现数据的安全问题，那么到底会导致出现哪些安全性问题呢?
脏读：一个事务读到了另外一个事务没有提交的数据。
虚读(不可重复读)：在一个事务A中进行数据的读操作，另外一个事务B在进行更新操作。如果事务A中频繁的对数据进行查询。此时会出现数据结果查询不一致的问题。
**幻读：**在事务A和事务B中进行数据的插入操作， 如果A先提交事务。事务B在插入数据之后，会出现插入的数据和预期不一样(比如我们的主键id)
要解决以上的问题，我们有必要了解事务的隔离级别。
事务隔离级别：
- read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。
- read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现,这是oracle数据库的默认隔离级别）
- repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在。mysql数据库的默认隔离级别）
- serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下。
2.7.2.1 读未提交(read uncommited)
一个事务读到另外一个事务没有提交的数据。
前期：准备一张account表。
- 在事务A里面执行以下操作:
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 1000 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> set autocommit = 0; # 关闭当前会话的事务自动提交功能
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation; # 查询当前数据库默认的事务隔离级别(可重复读)
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #设置当前会话的事务默认隔离级别为读未提交
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation; #
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
- 在事务B里面执行以下操作:
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 1000 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
- 在事务A里面开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
- 在事务B里面开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
- 在事务B里面进行数据更新的操作
mysql> update account set money=500 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 在事务A里面查询数据
mysql> select * from account; # 此时出现了查询到事务B没有提交的数据，出现了脏读问题。
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 500 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
2.7.2.2 读已提交(read commited）
- 在事务A和事务B里面同时设置事务的隔离级别是read commited。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
- 在事务A 和 事务B里面都开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
- 在事务B里面执行数据的修改操作.但是不提交事务
mysql> update account set money = 1000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 在事务A里面进行数据查询操作
mysql> select * from account; # 此时id=1的数据没有发生变化，说明解决了数据的脏读问题
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 500 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
- 如果我们在事务A里面频繁的查询数据，但是事务B提交，会出现查询数据不一致的问题，也就是虚读。
# 在事务A里面频繁的进行数据查询，我们发现数据都是一致的
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 500 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 500 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
此时我们在事务B里面提交事务：
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
在去事务A里面执行查询操作：
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 500 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 500 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from account; # 此时出现了数据查询结果不一致的问题。也就是虚读问题。
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 1000 |
| 2 | james | 1000 |
+----+-------+-------+
2.7.2.3 可重复读(repeatable read)
- 在事务A和事务B里面同时设置事务的隔离级别是repeatable read。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
- 在事务A 和 事务B里面都开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
- 在事务A里面插入数据，但是不提交
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 1000 |
| 2 | james | 1000 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> insert into account(name,money) values('kobe',1000);
Query OK, 1 row affected (0.00 sec)
- 在事务B里面插入数据也不提交
mysql> insert into account(name,money) values('curry',1000);
Query OK, 1 row affected (0.00 sec)
- 提交事务B，执行查询操作
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; # 此时记录的id为4 并不是预期中的3.出现了幻读问题
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 1000 |
| 2 | james | 1000 |
| 4 | curry | 1000 |
+----+-------+-------+
3 rows in set (0.00 sec)
- 提交事务A,执行查询操作
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+-------+-------+
| id | name | money |
+----+-------+-------+
| 1 | eric | 1000 |
| 2 | james | 1000 |
| 3 | kobe | 1000 |
| 4 | curry | 1000 |
+----+-------+-------+
4 rows in set (0.00 sec)
2.8 联合查询(补充)
联合查询就是将不同的select语句的结果集进行拼接。前提是不同select语句的字段数量要一样，并且字段的数据类型要保持一致。联合查询的关键字是union。
SELECT city_id,city_name FROM city
UNION
SELECT country_id,country_name FROM country;
我们也可以使用union all
SELECT city_id,city_name FROM city
UNION ALL
SELECT country_id,country_name FROM country;
注意：union 和 union all的区别：
union all是把结果集直接合并在一起不做任何处理，而union 是将union all后的结果进行一次distinct，去除重复的记录后的结果。
3.Mysql进阶
3.1 索引
3.1.1 索引的基本概述
索引(index)是帮助Mysql高效获取数据的一种数据结构。在数据库系统中，除了表数据，数据库还维护着满足特定查找算法的数据结构。这些数据结构以某种方式指向表数据。这样我们可以通过这种数据结构快速的实现数据表的数据查询。那么这种数据结构就是索引。
最左边的表(没有建立索引的情况)，我们要查询数据，需要从第一行开始逐行遍历，直到找到我们想要的数据，如果这张表的数据量很大，这种遍历查找数据的方式效率会非常低下。
为了提高数据的查询效率，右表(建立索引的情况)，可以维护一个平衡二叉树，通过平衡二叉树，可以提高数据的查询效率。
什么是平衡二叉树:
节点和根节点进行比较，如果当前节点比根节点还要大，就放在根节点的右边。反之放在根节点的左边。
索引的优势劣势：
优势：
- 类似于书籍的目录，通过创建索引可以快速的检索数据，提高数据的查询效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低排序成本，降低CPU的消耗。
劣势：
- 索引本质上也是一种数据结构，这种数据结构指向我们的数据表中的数据，这种数据结构也需要磁盘空间来维护。
- 虽然索引能够提高数据的查询效率，但是也会降低表更新的效率，比如update、delete、insert操作。如果使用这些更新操作来更变表中的记录，同时对应的索引结构也会随着发生变化。
- 如果一张表的数据是海量的。此时建立索引可能会适得其反，因为此时的索引结构也会非常庞大，会占用磁盘空间，并且也很难提高查询效率。
3.1.2 索引的结构
Mysql数据库目前提供了以下几种索引，不同的存储引擎，对于索引的支持是不一样的。
- btree索引，最常见的索引结构，大多数存储引擎都支持这种索引。
- hash索引，只有Memory引擎支持。
- R-TREE索引(空间索引)，只有MYSAIM存储引擎支持，用的很少。
- FULL-TEXT(全文索引)，只有MYSAIM存储引擎支持，用的很少。
注意：我们在后面讨论的索引都是基于BTree的B+树(基于平衡二叉树的多路搜索树)。其中我们常说的复合索引、唯一索引等都是基于B+树实现的。
3.1.3 BTREE结构
Btree又叫做多路搜索树，一颗m叉的BTree为例：
- 树中每个节点最多包含m个孩子(叶子节点除外)
- 除根节点和叶子节点之外，每个节点至少有（ceil(m/2)）个孩子。
- 如果根节点不是叶子节点，则至少有2个孩子
- 所有的叶子节点都在同一层。
- 每个节点由n个key和n+1个指针组成。n的取值范围是：[ceil(m/2)-1 <= n <= m-1]。以5叉树为例，n的取值范围是[2,4]。当n大于4的时候，中间节点要进行裂变(两边的节点分裂，中间的节点要裂变到父节点)。
现在我们以插入：C N G A H E K Q M F W L T Z D P R X Y S这些数据为例，演变的过程如下：
(1) 插入 C N G A
(2) 插入H,此时n>4。中间元素G向上分裂。
(3) 插入E K Q
(4) 插入M。中间元素M向上裂变到父节点
(5)插入F W L T
(6) 插入Z
(7) 插入D 向上裂变 还要插入P R X Y
(8)最后插入s
总结：BTree和二叉树相比，查询的效率更高了。因为Btree树它的层级比二叉树要少，并且保存的数据也会更多。对于相同的数据量来说，使用Btree要比二叉树更快。
3.1.4 B+Tree结构
- n叉的B+Tree，非叶子节点最多有n个key，指针也是n。
- 所有的非叶子节点可以当成是key的索引。叶子节点保存的是所有key的信息。查询任何key都要从根节点查询。
- 和Btree不同，B+Tree的叶子节点之间的地址是连续的。也就是在查询的时候，查询效率会更加稳定。
3.1.5 Mysql中的B+Tree
Mysql中索引数据结构对B+Tree进行了优化。在原来B+Tree的基础上增加了指向叶子节点的双向链表。这个双向链表提高区间查询的性能。
如果数据量很大，为了进一步提高数据的查询性能，Mysql中引入了Page这个概念，相当于对数据进行分页，把一部分数据放在Page中。这样做的目的是在B+Tree里面可以保存更多的数据。
每页可以存储16kb的数据。如果我们要查询数据的话，先要找到数据所属的最上层目录(Page)。通过page找到最底层的page之后，再进入page内部寻找对应的数据,进一步的提高查询效率。
我们可以查询，一个page的容量是多少?
mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 | # 16384字节换算成kb就是16384/1024 = 16kb
+------------------+-------+
1 row in set (0.01 sec)
问题：B+tree一般设计成几层?
答案：2-3层
如果3层的话预估：（第一层节点的k+指针的个数）X（第二层节点的k+指针的个数）X 叶子节点中保存记录的条数。
举例：
比如我们的主键占6个字节，指针占8个字节。一个主键和其对应的指针就占了14个字节。根节点就可以存储大约1170条主键+指针。
第二层节点(非叶子节点)又可以保存1170*1170条主键+指针。
第三次叶子节点，保存主键+主键之外的记录。我们预估1条记录占1kb。那么这页里面就可以保存16条记录。
综上可得，所有叶子节点保存的数据条数是1170*1170*16大约2000多万条数据，当然这只是保守估计。
3.1.6 索引的使用
索引的分类：
- 单值索引：就是一个索引只包含一个字段。
- 主键索引：索引字段是非空且唯一。
- 复合索引：一个索引包含多个字段。
创建索引的语法：
create index index_name on table_name(column_name,.....);
准备两张数据表：
CREATE TABLE city(
city_id INT PRIMARY KEY AUTO_INCREMENT,
city_name VARCHAR(20) NOT NULL,
country_id INT NOT NULL
)
CREATE TABLE country(
country_id INT PRIMARY KEY AUTO_INCREMENT,
country_name VARCHAR(20) NOT NULL
)
现在我们来给数据表创建索引。
需求：给city表中的city_name创建索引
CREATE INDEX idx_city ON city(city_name);
如何查看索引：
mysql> SHOW INDEX FROM city;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | city_id | A | 4 | NULL | NULL | | BTREE | | |
| city | 1 | idx_city | 1 | city_name | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
我们也可以删除索引：
DROP INDEX idx_city ON city;
我们也可以创建复合索引：
CREATE INDEX idx_city ON city(city_id,city_name,country_id);
3.2 视图
视图（View）是一张虚拟的表。视图并不存在在数据库里面。视图的数据来源于我们select语句查询出来的结果集。
视图相对于普通表有哪些优势?
- 简单，使用视图的用户，完全不需要关心数据表的结构是什么,查询条件是什么，涉及到了哪些表。对于用户来说，这些结果集已经是过滤好的数据。
- 安全，使用视图的用户只能访问他们被允许访问的数据。提高数据的安全性。
- 数据独立，一旦视图的结构确定了，可以屏蔽表结构变化给用户带来的影响。
3.2.1 视图的使用
创建视图的语法：
create view view_name as select 语句
# 基于员工表创建的视图
CREATE VIEW view_emp AS SELECT empno,ename,job,mgr,hiredate,deptno FROM employee;
查询视图：
由于视图是一张表，有行有列。所以我们可以通过select语句来对视图进行查询。
SELECT * FROM view_emp
我们可不可以修改视图？可以
UPDATE view_emp SET hiredate = '1983-1-25' WHERE empno = 7934;
删除视图：
DROP VIEW view_emp_dept;
3.3 存储过程和存储函数
存储过程和存储函数是事先经过编译并存储在数据库服务器中的sql语句的集合(sql片段)。使用存储过程或存储函数可以简化开发人员的许多工作。减少和数据库服务器之间的交互次数，节省服务器的资源。
存储过程和存储函数之间的区别在于返回值。存储过程没有返回值，而存储函数是有返回值的。
3.3.1 存储过程
创建存储过程的语法：
create procedure procedure_name(proc_paramerter....)
begin
-- 定义sql语句。
end;
3.3.1.1 编写第一个存储过程案例
现在我们编写一个简单的存储过程语句，向控制台输出“hello world”。
DELIMITER $ -- 存储过程语句开始的标识符
CREATE PROCEDURE proc_test01()
BEGIN
SELECT "hello world";
END $
DELIMITER -- 告诉数据库服务器，存储过程的语句全部结束
如何调用存储过程：
CALL proc_test01();
我们也可以查看数据库中存储过程：
SELECT NAME FROM mysql.proc WHERE db = 'day04'; -- 查询指定数据库中存储过程的名称
SHOW PROCEDURE STATUS; -- 查询所有存储过程的状态信息
SHOW CREATE PROCEDURE proc_test01; -- 查询指定存储过程的详细信息
我们也可以删除存储过程：
DROP PROCEDURE proc_test01
3.3.1.2 变量
- declare
declare声明的意思。可以定义一个局部变量。这个变量只能在begin…end块中生效。
declare var_name type [default value]
需求：声明一个变量，并将该局部变量输出。
DELIMITER $
CREATE PROCEDURE proc_test2()
BEGIN
-- 声明一个局部变量
DECLARE num INT DEFAULT 5;
SELECT num;
END $
DELIMITER
CALL proc_test2();
- set
我们也可以通过set关键字对局部变量的值进行修改。
DELIMITER $
CREATE PROCEDURE proc_test3()
BEGIN
-- 声明一个局部变量
DECLARE num INT DEFAULT 5;
-- 给变量num重新赋值
SET num = 15;
SELECT num;
END$
DELIMITER
CALL proc_test3();
在sql语句操作中，我们经常可以使用select …into 的语句进行赋值操作（把一个一行一列的结果赋予给一个变量）
需求：输出数据表中的记录条数。
DELIMITER $
CREATE PROCEDURE proc_test4()
BEGIN
-- 声明一个局部变量
DECLARE num INT ;
-- 给变量num重新赋值
SELECT COUNT(*) INTO num FROM city;
SELECT num;
END$
DELIMITER
CALL proc_test4();
3.3.1.3 if条件判断
基本语法：
if 条件 then 执行满足条件的语句
elseif 条件 then 执行满足条件的语句
......
else 不满足以上所有条件，需要执行的语句
end if;
需求：根据定义的身高，判断一个人身材类型。
180及以上 ----- 身材高挑
170-180 ----- 身材匀称
170一下 ----- 一般身材
DELIMITER $
CREATE PROCEDURE proc_test5()
BEGIN
DECLARE height INT DEFAULT 182;
DECLARE result VARCHAR(30);
IF height >= 180 THEN
SET result = '身材高挑';
-- select result;
ELSEIF height >=170 AND height <= 180 THEN
SET result = '身材匀称';
-- SELECT result;
ELSE
SET result = '身材一般';
-- SELECT result;
END IF;
SELECT result;
END$
DELIMITER
CALL proc_test5();
3.3.1.4 参数传递
语法格式：
create procedure procedure_name([in/out/inout] 参数名称 数据类型)
- IN 输入参数 就是用来接收用户传递的实参。如果IN不写，这个参数就是输入参数。
- OUT 输出参数 由于存储过程没有返回值的，如果我们的处理之后产生了数据结果，需要使用输出参数来接收。
- INOUT 既可以做输入参数，也可以做输出参数。
(1) IN 输入参数
需求：根据定义的身高，判断一个人身材类型。
CREATE PROCEDURE proc_test6(IN height INT)
BEGIN
DECLARE result VARCHAR(30);
IF height >=180 THEN
SET result = '身材高挑';
ELSEIF height >=170 AND height <= 180 THEN
SET result = '身材匀称';
ELSE
SET result = '身材一般';
END IF;
SELECT result;
END $
DELIMITER
CALL proc_test6(159);
(2) OUT 输出参数
需求：根据传入的身高，返回当前的身材类型。
DELIMITER $
CREATE PROCEDURE proc_test7(IN height INT,OUT result VARCHAR(20))
BEGIN
IF height >=180 THEN
SET result = '身材高挑';
ELSEIF height >=170 AND height <= 180 THEN
SET result = '身材匀称';
ELSE
SET result = '身材一般';
END IF;
END $
DELIMITER
CALL proc_test7(188,@jieguo);
-- @jieguo 会话变量，使用@连接，变量名称自定义 会话变量作用范围是当前会话。下一次数据库连接是无法使用这个变量的
SELECT @jieguo;
3.3.1.5 case语句
case语句的基本语法：
方式1：
case case_value
when value then 需要执行的语句
when value then 需要执行的语句
... ...
else 不满足以上所有条件的语句
end case;
方式2：
case
when 条件 then 执行满足条件的代码
when 条件 then 执行满足条件的代码
... ...
else 不满足以上所有条件的语句
end case;
需求： 给定一个月份，判断当前月份所在的季度。
DELIMITER $
CREATE PROCEDURE proc_test8(IN yuefen INT)
BEGIN
DECLARE result VARCHAR(20);
CASE
WHEN yuefen >= 1 AND yuefen <= 3 THEN
SET result = '第一季度';
WHEN yuefen >= 4 AND yuefen <= 6 THEN
SET result = '第二季度';
WHEN yuefen >= 7 AND yuefen <= 9 THEN
SET result = '第三季度';
WHEN yuefen >= 10 AND yuefen <= 12 THEN
SET result = '第四季度';
ELSE
SET result = '输入的月份不合规范';
END CASE;
SELECT result;
END $
DELIMITER
CALL proc_test8(4);
需求：将下列表变成下面的表信息：
转变后的效果如下：
SELECT `name` AS 姓名 ,
SUM(CASE WHEN `subject` = '语文' THEN fraction END) AS '语文',
SUM(CASE WHEN `subject` = '数学' THEN fraction END) AS '数学',
SUM(CASE WHEN `subject` = '英语' THEN fraction END) AS '英语',
SUM(fraction) AS '总分'
FROM t_score GROUP BY NAME;
3.3.1.6 循环语句 – while
语法格式：
while 循环条件 do
满足循环条件需要执行的代码
end while;
需求：计算1-n的值
DELIMITER $
CREATE PROCEDURE proc_test9(IN num INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
WHILE i <= num DO
SET total = total + i;
SET i = i + 1;
END WHILE;
SELECT total;
END $
DELIMITER
CALL proc_test9(100);
3.3.1.7 循环语句–repeat语句
它和while语句相反，满足条件就退出循环操作。
语法格式：
repeat
执行循环操作的语句
until 条件
end repeat;
需求：计算1-n的值。
DELIMITER $
CREATE PROCEDURE pro_test10(IN num INT)
BEGIN
DECLARE total INT DEFAULT 0;
REPEAT
SET total = total + num;
SET num = num -1;
UNTIL num = 0
END REPEAT;
SELECT total;
END $;
DELIMITER
CALL pro_test10(100);
3.3.1.8 循环语句–loop语句
语法格式：
[begin label:] loop
执行循环体的代码
end loop [end label]
上面的语法格式，由于没有退出循环的机制，所以是一个死循环。如果我们要退出循环，需要借助于leave实现。
需求：计算1-n的值。
DELIMITER $
CREATE PROCEDURE pro_test11(IN num INT)
BEGIN
DECLARE total INT DEFAULT 0;
ins: LOOP
IF num <=0 THEN
LEAVE ins;
END IF;
SET total = total + num;
SET num = num - 1;
END LOOP ins;
SELECT total;
END $
DELIMITER
CALL pro_test11(100);
3.3.1.9 游标
游标相当于一个指针，可以将指针对应的数据抓取出来。
需求： 将数据表中的数据通过游标抓取出来，并输出。
游标的语法：
declare 游标名称 cursor for select语句; -- 声明游标
open 游标名称 -- 打开游标
fetch 游标名称into 变量 -- 通过游标抓取数据，并将数据赋予给变量
close 游标名称 -- 关闭游标
DELIMITER $
CREATE PROCEDURE pro_test12()
BEGIN
DECLARE emp_id INT;
DECLARE emp_name VARCHAR(30);
DECLARE emp_age INT;
DECLARE emp_salary INT;
-- 声明游标
DECLARE emp_result CURSOR FOR SELECT * FROM emp;
-- 打开游标
OPEN emp_result;
FETCH emp_result INTO emp_id,emp_name,emp_age,emp_salary;
SELECT CONCAT('员工编号:',emp_id,' 员工名称:',emp_name,' 员工年龄:',emp_age,' 员工薪资:',emp_salary);
FETCH emp_result INTO emp_id,emp_name,emp_age,emp_salary;
SELECT CONCAT('员工编号:',emp_id,' 员工名称:',emp_name,' 员工年龄:',emp_age,' 员工薪资:',emp_salary);
FETCH emp_result INTO emp_id,emp_name,emp_age,emp_salary;
SELECT CONCAT('员工编号:',emp_id,' 员工名称:',emp_name,' 员工年龄:',emp_age,' 员工薪资:',emp_salary);
FETCH emp_result INTO emp_id,emp_name,emp_age,emp_salary;
SELECT CONCAT('员工编号:',emp_id,' 员工名称:',emp_name,' 员工年龄:',emp_age,' 员工薪资:',emp_salary);
-- 关闭游标
CLOSE emp_result;
END $
DELIMITER
CALL pro_test12()
这种写法是硬编码的写法，如果数据量很大，我们需要使用循环配合游标去实现。
DELIMITER $
CREATE PROCEDURE pro_test13()
BEGIN
DECLARE emp_id INT;
DECLARE emp_name VARCHAR(30);
DECLARE emp_age INT;
DECLARE emp_salary INT;
DECLARE has_data INT DEFAULT 1;
-- 声明游标
DECLARE emp_result CURSOR FOR SELECT * FROM emp;
-- has_data = 0 说明游标不指向任何数据
DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
-- 打开游标
OPEN emp_result;
REPEAT
FETCH emp_result INTO emp_id,emp_name,emp_age,emp_salary;
SELECT CONCAT('员工编号:',emp_id,' 员工名称:',emp_name,' 员工年龄:',emp_age,' 员工薪资:',emp_salary);
UNTIL has_data = 0
END REPEAT;
CLOSE emp_result;
END $
DELIMITER
CALL pro_test13();
3.3.2 存储函数
语法结构：
create function function_name(参数列表)
returns type
begin
......
end
案例：定义一个存储函数，统计指定表中的记录数。
DELIMITER $
CREATE FUNCTION getCount(sname VARCHAR(20)) -- 输入参数不要再使用in关键字指代了
RETURNS INT
BEGIN
DECLARE total INT;
SELECT COUNT(*) INTO total FROM t_score WHERE `name` = sname;
RETURN total;
END $
DELIMITER;
SELECT getCount('王海');
3.4 触发器
3.4.1 触发器的介绍
触发器是跟表有关的数据库对象，在执行insert/update/delete之前或者之后，触发触发器中定义的sql语句。根据触发器的特性，我们可以在开发中进行日志记录等操作。
触发器的类型主要有以下几个：
| 触发器的类型 | 触发器的描述 |
|---|---|
| insert类型 | new 表示将要新增的数据 |
| update类型 | old 表示修改之前的数据，new 表示修改之后的数据 |
| delete类型 | old表示已经删除的数据 |
需求：通过触发器记录对表数据变更的日志信息（增加、修改、删除）
- 创建一张日志表，记录触发器的日志信息
CREATE TABLE emp_logs(
id INT PRIMARY KEY AUTO_INCREMENT,
operation VARCHAR(20) NOT NULL COMMENT '操作类型，insert/update/delete',
operate_time DATETIME NOT NULL COMMENT '操作时间',
operate_id INT NOT NULL COMMENT '操作表id',
operate_params VARCHAR(50) COMMENT '操作参数'
)
- 创建触发器
-- 创建一个insert类型的触发器
DELIMITER $
CREATE TRIGGER emp_logs_insert_trigger
AFTER INSERT ON emp FOR EACH ROW -- 描述触发器触发的时机,影响表中的每行记录都会触发(行级触发)
BEGIN
INSERT INTO emp_logs(operation,operate_time,operate_id,operate_params)
VALUES('insert',NOW(),new.id,CONCAT('新增之后，id:',new.id,' 姓名:',new.name,' 年龄:',new.age,' 薪资:',new.salary));
END $
DELIMITER ;
-- 创建一个update类型的触发器
DELIMITER $
CREATE TRIGGER emp_logs_update_trigger
AFTER UPDATE ON emp FOR EACH ROW -- 描述触发器触发的时机,影响表中的每行记录都会触发(行级触发)
BEGIN
INSERT INTO emp_logs(operation,operate_time,operate_id,operate_params)
VALUES('update',NOW(),new.id,
CONCAT('修改之前，id:',old.id,' 姓名:',old.name,' 年龄:',old.age,' 薪资:',old.salary,
' 修改之后，id:',new.id,' 姓名:',new.name,' 年龄:',new.age,' 薪资:',new.salary));
END $
DELIMITER ;
-- 创建一个删除类型的触发器
DELIMITER $
CREATE TRIGGER emp_logs_delete_trigger
AFTER DELETE ON emp FOR EACH ROW -- 描述触发器触发的时机,影响表中的每行记录都会触发(行级触发)
BEGIN
INSERT INTO emp_logs(operation,operate_time,operate_id,operate_params)
VALUES('delete',NOW(),old.id,
CONCAT('删除之前，id:',old.id,' 姓名:',old.name,' 年龄:',old.age,' 薪资:',old.salary));
END $
DELIMITER ;
-- 删除触发器
DROP TRIGGER emp_logs_update_trigger;
-- 查看触发器
SHOW TRIGGERS
3.5 mysql存储引擎
和大多数数据库不同，mysql有存储引擎的概念。针对不同的业务需求，我们可以选择不同的存储引擎。存储引擎是存储数据，建立索引，更新数据等技术的实现方式，存储引擎是基于表的，不是基于数据库的。
和oracle sqlserver数据库不一样，oracle sqlserver只有1种存储引擎，但是mysql有很多存储引擎。比如innodb myisam memory csv…
我们可以通过show engines查询mysql支持的存储引擎。
mysql默认支持的存储引擎是InnoDB。接下来我们去了解不同的存储引擎的特性。我们以innodb和myisam为例。
3.5.1 InnoDB存储引擎
InnoDB是mysql数据库默认支持的存储引擎。这种存储引擎提供了具有提交、回滚等事务功能。相对于myisam存储引擎来说，innodb存储引擎的写效率差一些。并且会占用更多的磁盘空间来保留数据和索引。
- 支持事务
现在我们创建一张数据表，指定数据表的存储引擎。
CREATE TABLE goods_innodb(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
address VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=utf8; -- 指定数据库的存储引擎和字符编码格式
在窗口1，开启事务并且插入一条数据：
mysql> start transaction; -- 开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into goods_innodb(name,address) values('eric','usa'); -- 插入1条数据
Query OK, 1 row affected (0.00 sec)
在窗口2，查询数据：
mysql> select * from goods_innodb;
Empty set (0.00 sec) -- 此时查询的数据为空
回到窗口1，提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
回到窗口2，再查询数据：
mysql> select * from goods_innodb;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | eric | usa |
+----+------+---------+
1 row in set (0.00 sec)
通过这个案例，我们验证了innodb存储引擎是支持事务的。
- 支持外键
mysql只有innodb存储引擎支持外键的。我们可以测试一下，innodb存储引擎是否支持外键。
CREATE TABLE country_innodb(
coutry_id INT PRIMARY KEY AUTO_INCREMENT,
country_name VARCHAR(50) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE city_innodb(
city_id INT PRIMARY KEY AUTO_INCREMENT,
city_name VARCHAR(50) NOT NULL,
country_id INT NOT NULL,
CONSTRAINT fk_city_country FOREIGN KEY(country_id) REFERENCES country_innodb(coutry_id)
ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT * FROM country_innodb;
SELECT * FROM city_innodb;
DELETE FROM country_innodb WHERE coutry_id = 1;-- id=1的国家删除了，对应的城市信息也会被级联删除
3.5.2 MyISAM存储引擎
MyISAM存储引擎不支持事务，也不支持外键。这种存储引擎适用于无事务的场景。
- myisam存储引擎不支持事务
创建一张表，指定存储引擎是myisam
CREATE TABLE goods_myisam(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
address VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
在窗口1 开启事务，并插入数据，不提交事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into goods_myisam(name,address) values('eric','usa');
Query OK, 1 row affected (0.00 sec)
在窗口2查询数据
mysql> select * from goods_myisam; -- 没有提交事务，也能查询数据 说明myisam存储引擎不支持事务的
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | eric | usa |
+----+------+---------+
1 row in set (0.00 sec)
- myisam存储引擎不支持外键
创建基于myisam存储引擎的表
CREATE TABLE country_myisam(
coutry_id INT PRIMARY KEY AUTO_INCREMENT,
country_name VARCHAR(50) NOT NULL
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE city_myisam(
city_id INT PRIMARY KEY AUTO_INCREMENT,
city_name VARCHAR(50) NOT NULL,
country_id INT NOT NULL,
CONSTRAINT fk_city_country1 FOREIGN KEY(country_id) REFERENCES country_myisam(coutry_id)
ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
执行删除主表数据的操作
DELETE FROM country_myisam WHERE coutry_id = 1; -- 发现外表中的数据没有级联删除，所以基于myisam存储引擎的表不支持外键
总结：
innodb是mysql默认的存储引擎。对事务要求比较高的场景适合使用这种存储引擎，并且支持外键。
myisam存储引擎，对事务的场景没有要求，适用于查询比较多的场景。
3.6 sql执行计划分析
explain关键字它的作用就是获取mysql如何执行select语句的信息，包括select语句执行过程中表是如何连接的。
语法： explain select语句。
mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
| 字段 | 含义 |
|---|---|
| id | select子语句的序号，描述select子语句的执行顺序 |
| select_type | 描述select子语句的查询类型，常见的取值有SIMPLE(单表查询)，PRIMARY(主查询，整个select语句的最外层查询)，UNION(联合查询，UNION关键字中的第二个查询语句),SUBQUERY(嵌套子查询中内部的第一个select子语句) |
| table | 输出结果集的表 |
| type | 表示表的连接类型，性能由好到坏：system->const–>eq_ref->ref->range–>index–>all |
| possible_keys | 可能会用到的索引 |
| key | 实际用到的索引 |
| key_len | 索引的长度 |
| rows | 扫描行的数量 |
| Extra | 执行情况的额外描述 |
3.6.1 explain执行计划之id
id是select语句的查询序号，一组数字，表示select子语句的执行顺序。
- id值相同 sql执行的顺序就是从上到下
mysql> EXPLAIN SELECT * FROM t_role r,t_user u,user_role ur WHERE
-> r.id = ur.role_id AND u.id = ur.user_id;
- id值不同。值越大，执行的优先级就越高。
EXPLAIN SELECT * FROM t_role WHERE id = (
SELECT role_id FROM user_role WHERE user_id = (
SELECT id FROM t_user WHERE username = 'stu1')
)
3.6.2 explain执行计划之select_type
表示表的连接类型，常见取值如下：
| select_type | 含义 |
|---|---|
| SIMPLE | 基于单表的select语句查询，查询不包含子查询或者union |
| PRIMARY | 查询任何复杂select查询语句的最外层查询 |
| SUBQUERY | 嵌套子查询中的子查询语句 |
| DERIVED | 在from关键字后面跟的子查询 |
| UNION | 如果第二个select语句出现在union之后，则标记为union |
- simple
mysql> EXPLAIN SELECT * FROM t_role;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_role | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
- primary
EXPLAIN SELECT * FROM t_user WHERE id = (
SELECT user_id FROM user_role WHERE role_id = 9
)
- union
EXPLAIN SELECT * FROM t_user WHERE id = 1 UNION SELECT * FROM t_user WHERE id = 2
3.6.3 explain执行计划之type
type描述子select语句的访问类型，是一个衡量sql性能高低的重要标志。取值如下：
| type | 含义 |
|---|---|
| NULL | mysql不访问任何的表，比如查询系统函数 |
| system | 查询系统表，并且系统表中只有一行数据，一般不会出现 |
| const | 表示通过索引1次就找到了，返回的数据有且只有一行数据，一般通过主键索引和唯一索引来进行匹配。 |
| eq_ref | 使用主键索引或者唯一索引进行关联查询，查询的记录有且只有1条记录 |
| ref | 不使用唯一索引或主键索引进行扫描，返回匹配某个条件的所有行记录。本质上也是基于索引访问。 |
| range | 一般出现在区间查询 between > < in |
| index | 通过遍历索引树返回结果，效率比ALL高 |
| ALL | 遍历全表进行查询 |
- NULL 不访问任何的表，性能最高
EXPLAIN SELECT NOW();
EXPLAIN SELECT 'hello';
- const
EXPLAIN SELECT * FROM t_role WHERE role_name = '学生';
- eq_ref
EXPLAIN SELECT * FROM t_user u,t_role r WHERE u.id = r.id
- ref
CREATE INDEX idx_user_name ON t_user(`name`); -- 创建一个普通索引
EXPLAIN SELECT * FROM t_user WHERE `name` = '学生2';
- index
EXPLAIN SELECT `name` FROM t_user ;
- all
EXPLAIN SELECT * FROM t_role
3.6.4 explain执行计划之key
possible_keys:显示当前select子语句可能会用到的索引。索引可以有1个，也可以有多个。
key：当前select子语句实际上用到的索引，如果为NULL，则没有用到索引
key_len:索引占用的字节数，也就是索引的长度，在实际情况下，索引的长度越短越好。
EXPLAIN SELECT * FROM t_user WHERE id = 1
解读：
possible_keys：primary 标识可能会用到主键来进行查询。key:NULL 实际上没有用到索引检索，通过type:ALL得知，当前select查询语句是通过全表扫描查询的。
原因：由于sql语句跟上了条件where id = 1;所以mysql判定可能会根据主键来检索。但是由于select后面跟的是*。对应索引树里面没有包含所有字段的数据，所以只能回表进行查询。既然回表查询，使用索引也就没有了意义。
我们再看一种情况：
EXPLAIN SELECT NAME FROM t_user
解读：
由于当前的select语句没有跟where条件。所以mysql判断当前select语句可能不会用到索引，所以possible_keys：NULL。但是在查询的时候，我们只查询name字段，此时name字段对应的值恰好在索引树里面存在。所以在实际查询中走索引树会更加高效，key：id_user_name。
以下这种情况就比较常见了。可能会用到索引，实际上确实用上了索引。
EXPLAIN SELECT NAME FROM t_user WHERE `name` = 'stu1'
3.6.5 explain执行计划之extra
展示额外的执行计划信息。
| extra取值 | 含义 |
|---|---|
| using filesort | 称之为文件排序，执行效率最低，说明mysql在展示结果集的时候，会使用一个临时文件进行排序 |
| using temporary | mysql在进行排序的时候，会使用临时表来保存结果，效率也很低 |
| using index | 使用索引进行查询，效率不错 |
| using where | 要通过索引回表查询数据 |
- using where
EXPLAIN SELECT * FROM t_user WHERE id = 1
根据id(索引)查询数据，不够，还有其他数据(id之外的数据)1，此时需要回表查询。
- using filesort
EXPLAIN SELECT * FROM t_user ORDER BY username DESC;
在sql语句中出现order by字段的时候容易出现文件排序。
- using temporary
EXPLAIN SELECT role_code,COUNT(role_code) FROM t_role GROUP BY role_code;
如果出现group by字段，需要借助于临时表进行分组。此时性能比较低。
- using indx
EXPLAIN SELECT NAME FROM t_user WHERE `name` = 'stu1'
3.7 关于索引的失效问题
前期准备对应的数据表：
CREATE TABLE `tb_seller` (
`sellerid` VARCHAR (100),
`name` VARCHAR (100),
`nickname` VARCHAR (50),
`password` VARCHAR (60),
`status` VARCHAR (1),
`address` VARCHAR (100),
`createtime` DATETIME,
PRIMARY KEY(`sellerid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qf','微软中国','微软中国','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES(qf,'千锋教育','千锋教育上海校区','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
-- 创建联合索引
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(NAME,STATUS,address);
(1)全值匹配，对索引中所有列都指定具体值。
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1' AND address = '北京市';
思考：如果我们变更查询字段的顺序。
EXPLAIN SELECT * FROM tb_seller WHERE STATUS = '1' AND address = '北京市' AND NAME = '小米科技';
我们发现是没有问题的。
(2) 最左前缀法则
如果索引了多列，需要遵循最左匹配法则。指的是查询从索引的最左侧开始，并且不跳过索引中的列
以下都是满足最左匹配法则的：
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技';
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1';
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1' AND address = '北京市';
下面就是违背最左匹配法则：
EXPLAIN SELECT * FROM tb_seller WHERE STATUS = '1';
EXPLAIN SELECT * FROM tb_seller WHERE STATUS = '1' AND address = '北京市'; -- 也不行，违背了最左匹配法则
如果符合最左匹配法则，但是中间跳过了某一个列，此时只有最左边的列生效。
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND address = '北京市';
(3) 范围查询右边的列，不能使用索引
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS > 1 AND address = '北京市';
(4) 不要在索引列上进行运算，否则索引失效
EXPLAIN SELECT * FROM tb_seller WHERE SUBSTRING(NAME,3,2) = '科技';
(5) 数字类型字符串的值，不加单引号，也会造成索引失效
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS = 1; -- 由于status的值没有使用单引号，所以索引会失效
*(6) 尽量避免使用select ，提高查询效率
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1' AND address = '北京市';
下面这么写就没事
EXPLAIN SELECT NAME,STATUS,address FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1' AND address = '北京市';
EXPLAIN SELECT NAME FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1' AND address = '北京市';
EXPLAIN SELECT NAME,STATUS FROM tb_seller WHERE NAME = '小米科技' AND STATUS = '1' AND address = '北京市';
(7) 使用or关键字，也会导致索引失效
EXPLAIN SELECT * FROM tb_seller WHERE NAME='小米科技' OR createtime = '2088-01-01 12:00:00'; -- createtime非索引字段
EXPLAIN SELECT * FROM tb_seller WHERE NAME='小米科技' OR address = '北京市';-- 虽然address是索引字段，但是使用了or关键字，也会导致索引失效
(8)是%开头的模糊查询，也会导致索引失效
注意：如果仅仅只是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
EXPLAIN SELECT * FROM tb_seller WHERE NAME like '%微软中国';
但是下面这种情况，索引不会失效：
EXPLAIN SELECT * FROM tb_seller WHERE NAME LIKE '微软中国%';
如何避免模糊查询，索引失效，我们可以通过索引覆盖来解决问题。
EXPLAIN SELECT sellerid FROM tb_seller WHERE NAME LIKE '%微软中国';
EXPLAIN SELECT sellerid,NAME,address FROM tb_seller WHERE NAME LIKE '%微软中国';-- name address 都是索引字段
EXPLAIN SELECT sellerid,NAME,STATUS,address FROM tb_seller WHERE NAME LIKE '%微软中国'; -- NAME,STATUS,address都是索引字段
(9) 如果Mysql评估走索引比走全表还要慢，此时索引也会失效
首先我们创建一个单行索引：
CREATE INDEX idx_seller_address ON tb_seller(address);
EXPLAIN SELECT * FROM tb_seller WHERE address = '北京市'; -- 此时没有走索引
EXPLAIN SELECT * FROM tb_seller WHERE address = '西安市'; -- 此时走了索引
因为数据表tb_seller中的数据只有11条，address是北京市的数据10条，address是西安市的数据只有1条。此时mysql的sql解析器认为在查询address是北京市数据的时候，走全表扫描会比走索引更快，此时mysql会放弃走索引，而回表查询。
相反，address是西安市的数据比较少，mysql的sql解析器认为走索引会更加的高效，此时走索引。
(10) is NULL，is not NUll 有时候会索引失效
EXPLAIN SELECT * FROM tb_seller WHERE address IS NULL;
EXPLAIN SELECT * FROM tb_seller WHERE address IS NOT NULL;
由于在当前数据表中address字段的值都是非空的。此时查询is not null的数据记录，mysql解析器会根据address查询非空字段的数据。走全表扫描会更加高效，此时放弃走索引，索引失效。
相反address这个字段没有空值数据，查询is null的数据，mysql的解析器会认为走索引会更加高效，此时索引生效。
但是不要认为is null就索引不失效，is not null就一定是索引失效。
我们现在操作另一张表t_user的数据，我们将name字段设置成允许为空。执行sql语句
UPDATE t_user SET NAME = NULL WHERE id<>1;
此时表数据变更如下：
我们继续验证：
EXPLAIN SELECT * FROM t_user WHERE NAME IS NULL;
EXPLAIN SELECT * FROM t_user WHERE NAME IS NOT NULL;
此时name字段的值大部分为NULL。在查询name为is null的时候，mysql解析器认为走全表扫描更加高效，放弃走索引，所以索引失效。
相反，查询name的字段is not null。由于name不为null的数据比较少。mysql解析器认为此时走索引更加高效，索引不会失效。
(11) in走索引，not in 不走索引
EXPLAIN SELECT * FROM tb_seller WHERE sellerid IN('alibaba','sina','oppo');
EXPLAIN SELECT * FROM tb_seller WHERE sellerid NOT IN('alibaba','sina','oppo');
3.8 mysql的锁机制
3.8.1 锁的基本概述
在数据库中，数据也是一种可能被许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须要解决的问题。此时锁对数据库就显得尤为重要。
锁的分类：
-
根据锁的操作粒度分：
-
表锁：操作时，会锁定整张表的数据。
-
行锁：操作时，只会锁定当前操作的行数据。
-
-
从对数据的读写角度分：
- 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行，而互不影响。
- 写锁(排它锁)：当前操作没有完成之前，它会阻断其他的读锁和写锁。
相对于其他数据库而言，mysql不同的存储引擎支持不同的锁机制。
| 存储引擎 | 表级锁 | 行级锁 | 页面锁 |
|---|---|---|---|
| MYISAM | 支持 | 不支持 | 不支持 |
| INNODB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| DBD | 支持 | 不支持 | 支持 |
锁的特性：
表锁：偏向于MYISAM存储引擎。开销小，加锁快，不会出现死锁的问题。由于锁的粒度大，出生锁冲突的概率就越高，并发度很低。
行锁：InnoDB默认就支持行锁。开销大，加锁慢，可能会出现死锁的问题。由于锁的粒度小，出现锁冲突的概率就越低，并发度高。
3.8.2 MYISAM 表锁
MYISAM只支持表锁，如何给表加表锁？MYISAM存储引擎的表，在执行select语句的时候，mysql会自动的给涉及到的表加读锁。在执行更新操作的时候(insert update delete)，会自动的给涉及到的表加写锁。这个过程其实不需要用户干预。当然如果我们想看到读锁和写锁的效果，我们也可以显示加锁的语法：
加读锁：lock table table_name read;
加写锁：lock table table_name write;
3.8.2.1 读锁的案例
前提准备数据表：
CREATE TABLE tb_book(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(30),
publish_time DATE,
`status` CHAR(1)
)ENGINE=MYISAM CHARSET=utf8
INSERT INTO `day05`.`tb_book`(`id`,`name`,`publish_time`,`status`) VALUES ( NULL,'天龙八部','2001-8-1','1');
INSERT INTO `day05`.`tb_book`(`id`,`name`,`publish_time`,`status`) VALUES ( NULL,'射雕英雄传','2003-10-10','0');
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(30)
)ENGINE=MYISAM CHARSET=utf8
INSERT INTO `day05`.`tb_user`(`id`,`name`) VALUES ( NULL,'令狐冲');
INSERT INTO `day05`.`tb_user`(`id`,`name`) VALUES ( NULL,'田伯光');
分别打开两个操作数据库的客户端。
客户端1：
- 获取tb_book表的读锁
lock table tb_book read;
- 在客户端1执行查询tb_book的操作
mysql> select * from tb_book;
+----+-----------------+--------------+--------+
| id | name | publish_time | status |
+----+-----------------+--------------+--------+
| 1 | 天龙八部 | 2001-08-01 | 1 |
| 2 | 射雕英雄传 | 2003-10-10 | 0 |
+----+-----------------+--------------+--------+
2 rows in set (0.00 sec)
我们发现，可以正常查询数据。
我们再看客户端2：
- 我们执行查询tb_book的操作
mysql> select * from tb_book;
+----+-----------------+--------------+--------+
| id | name | publish_time | status |
+----+-----------------+--------------+--------+
| 1 | 天龙八部 | 2001-08-01 | 1 |
| 2 | 射雕英雄传 | 2003-10-10 | 0 |
+----+-----------------+--------------+--------+
2 rows in set (0.00 sec)
由于读锁是共享锁，所以共享锁作用于整张表，两个客户端都可以访问。那么我们操作读锁没有作用的表呢。
我们回到客户端1：
查询未被锁定的表
mysql> select * from tb_user;
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES
这是因为，我们的读锁操作的是tb_book,但是这个读锁没有释放，所以不能操作锁之外的其他表。举个例子我们在12306上面买票，如果我们没有付款，是不能购买其他票的。
我们再回到客户端2，去查询tb_user表。是可以查询到的。
mysql> select * from tb_user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 令狐冲 |
| 2 | 田伯光 |
+----+-----------+
2 rows in set (0.00 sec)
因为客户端2没有拿到客户端1的锁，所以可以正常查询没有被锁定的表的数据。
我们再看写操作：
- 在客户端1执行写操作：
mysql> insert into tb_book(name,publish_time,status) values('碧血剑','2000-12-1',1);
ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated
mysql> insert into tb_user(name) values('岳不群');
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES
我们发现插入报错。因为当前客户端获得是读锁，不能执行更新操作。
- 在客户端2执行写操作：
mysql> insert into tb_book(name,publish_time,status) values('鹿鼎记','2000-12-1',1);
此时光标一直处于闪烁状态，当客户端1释放锁之后，客户端2里面的数据才会插入成功。
- 在客户端1执行释放锁的命令
mysql> unlock tables; -- 释放锁的命令
- 再看客户端2插入的数据
mysql> insert into tb_book(name,publish_time,status) values('鹿鼎记','2000-12-1',1);
Query OK, 1 row affected (13.67 sec)
3.8.2.2 写锁的案例
- 在客户端1获取tb_book的写锁
mysql> lock table tb_book write; -- 给tb_book上写锁
- 在客户端1查询tb_book
mysql> select * from tb_book;
+----+-----------------+--------------+--------+
| id | name | publish_time | status |
+----+-----------------+--------------+--------+
| 1 | 天龙八部 | 2001-08-01 | 1 |
| 2 | 射雕英雄传 | 2003-10-10 | 0 |
| 3 | 碧血剑 | 2000-12-01 | 1 |
| 4 | 鹿鼎记 | 2000-12-01 | 1 |
+----+-----------------+--------------+--------+
4 rows in set (0.00 sec)
我们发现查询成功。
- 在客户端1查询tb_user
mysql> select * from tb_user;
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES
此时查询失败，因为这个表不再写锁的范围内。
- 在客户端1修改tb_book
mysql> update tb_book set name='连城诀' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
我们发现修改成功。
- 在客户端1修改tb_user
mysql> update tb_user set name='岳不群' where id = 2;
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES
修改失败，因为写锁不作用于这张表。
我们再回到客户端2：
- 在客户端2执行查询tb_book的操作。
mysql> select * from tb_book;
此时光标一直处于等待状态，只有客户端1释放写锁，客户端2才会查询到数据
我们在客户端1释放写锁
mysql> unlock tables; -- 释放锁的命令
回到客户端2：
mysql> select * from tb_book;
+----+-----------------+--------------+--------+
| id | name | publish_time | status |
+----+-----------------+--------------+--------+
| 1 | 天龙八部 | 2001-08-01 | 1 |
| 2 | 射雕英雄传 | 2003-10-10 | 0 |
| 3 | 碧血剑 | 2000-12-01 | 1 |
| 4 | 连城诀 | 2000-12-01 | 1 |
+----+-----------------+--------------+--------+
4 rows in set (11.72 sec)
在客户端2，我们可以直接查询tb_user。因为客户端1的写锁只作用域tb_book。
mysql> select * from tb_user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 令狐冲 |
| 2 | 田伯光 |
+----+-----------+
2 rows in set (0.00 sec)
- 在客户端1加写锁，在客户端2执行更新tb_book操作
mysql> update tb_book set name='倚天屠龙记' where id = 2;
此时光标会一直闪烁，只有客户端1释放写锁，客户端2才会更新数据成功。
- 在客户端1加写锁，在客户端2执行更新tb_user操作
mysql> update tb_user set name='岳不群' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
在客户端2，我们可以直接更新tb_user。因为客户端1的写锁只作用域tb_book。
3.8.3 InnoDB行锁
和myisam存储引擎一样，innodb存储引擎实现了读锁和写锁这两种锁。对应更新的操作(insert update delete)，INNODB会自动给涉及的数据加上写锁。对于select语句，innodb不会加任何锁。
首先我们准备一张表：
CREATE TABLE test_innodb_lock(
id INT,
`name` VARCHAR(20),
sex VARCHAR(5)
)ENGINE=INNODB CHARSET=utf8
CREATE INDEX idx_innodb_id ON test_innodb_lock(id);
CREATE INDEX idx_innodb_name ON test_innodb_lock(`name`);
- 我们在客户端1和客户端2分别关闭事务的自动提交功能。
mysql> set autocommit = 0;
- 在窗口1执行数据查询的操作
mysql> select * from test_innodb_lock;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | eric | 1 |
| 2 | lily | 1 |
| 3 | james | 0 |
| 4 | durant | 1 |
+------+--------+------+
4 rows in set (0.00 sec)
- 在窗口2执行数据查询的操作
mysql> select * from test_innodb_lock;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | eric | 1 |
| 2 | lily | 1 |
| 3 | james | 0 |
| 4 | durant | 1 |
+------+--------+------+
4 rows in set (0.00 sec)
为什么两个窗口都可以查询到数据，是因为对于普通的select语句，innodb存储引擎不会加任何锁。我们再来看看更新的操作：
现在我们窗口1和窗口2更新的是同一行数据。
- 在窗口1执行更新操作：
mysql> update test_innodb_lock set name = 'sunny' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 在窗口2对id为1的数据进行更新：
mysql> update test_innodb_lock set name = 'sunny' where id = 1;
此时光标一直闪烁，只有窗口1提交事务了(commit)，窗口2的数据才会真正修改完成。说明行锁生效了。
接下来，我们演示窗口1和窗口2修改的不是同一条记录。
- 窗口1修改id=3的数据
mysql> update test_innodb_lock set name = 'sunny1' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 窗口2修改id=4的数据
mysql> update test_innodb_lock set name = 'sunny1' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
由于操作的不是同一行，所以会修改成功。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
