一篇博客帮你了解MySQL高级知识

一、子查询与表连接

子查询（嵌套SQL）

MySQL允许创建子查询（subquery），即嵌套在其他查询中的查询

利用子查询进行过滤

select cust_name,cust_contact
from customers
where cust_id in (select cust_idfrom orders
where order_num IN (select order_num from orderitems where prod_id = 'TNT2'));

--为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。
--最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句
--外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句
--最外层查询确实返回所需的数据

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

-- (1) 从customers表中检索客户列表。
select cust_id,cust_name from customers ;
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10002 | Mouse House |
| 10003 | Wascals |
| 10004 | Yosemite Place |
| 10005 | E Fudd |
+---------+----------------+
-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬。
select count(*) as orders from orders where cust_id = 10001;
+--------+
| orders |
+--------+
| 2      |
+--------+

-- 为了对每个客户执⾏COUNT()计算，应该将COUNT()作为⼀个⼦查询。
select cust_id,cust_name,
 (select count(*)
 from orders
 where orders.cust_id = customers.cust_id) as orders
 )
from customers
order by cust_name;
+---------+----------------+--------+
| cust_id | cust_name 		| orders |
+---------+----------------+--------+
| 10001 | Coyote Inc. 		| 2 	|
| 10005 | E Fudd 			| 1 	|
| 10002 | Mouse House 		| 0 	|
| 10003 | Wascals 			| 6 	|
| 10004 | Yosemite Place 	| 1 	|
+---------+----------------+--------+

orders是⼀个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

注意:子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法 （表名和列名由⼀个句点分隔）。因为有两个cust_id列，⼀个在customers中，另⼀个在 orders中，需要比较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名， MySQL将假定你是对orders表中的cust_id进行自身比较。

关系表

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。 在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。

-- 假如有⼀个包含产品⽬录的数据库表，其中每种类别的物品占⼀⾏。
-- 对于每种物品要存储的信息包括产品描述和价格，以及⽣产该产品的供应商信息。
产品表：
产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式
A6 ... ... 奥迪 ... ....
520li .. .... 宝⻢ ... ...
...

-- 现在，假如有由同⼀供应商⽣产的多种物品，那么在何处存储供应
-- 商信息（如，供应商名、地址、联系⽅法等）呢？
产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式
A6 ... ... 奥迪 ... ....
520li .. .... 宝马 ... ...
 A8 .. ... 奥迪 ... ...

相同数据出现多次不是⼀件好事，此因素是关系数据库设计的基础。

关系表的设计就是要保证把信息分解成多个表，⼀类数据⼀个表。

各表通过某些常用的值（即关系设计中的关系（relational））互相关联。

在这个例子中，可建立两个表，⼀个存储供应商信息，另⼀个存储产品信息。

-- vendors表包含所有供应商信息

|vend_id | vend_name | vend_address| vend_city ....

-- products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。
prod_id | vend_id | prod_name | prod_price | prod_desc

select customers.cust_name,customers.cust_id,count(orders.order_num) as
num_ord from customers left join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;

--vendors表的主键又叫作products的外键，它将vendors表与products表关联，利⽤供应商ID能 从vendors表中找出相应供应商的详细信息。 这样做的好处如下：

-- 供应商信息不重复，从而不浪费时间和空间； 
-- 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
-- 由于数据无重复，显然数据是⼀致的，这使得处理数据更简单

关系数据可以有效地存储方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

组合查询 UNION

MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。 这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则

- UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔（因此， 如果组合4条SELECT语句，将要使⽤3个UNION关键字）。
- UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如， 不同的数值类型或不同的日期类型）。

-- 假如需要价格⼩于等于5的所有物品的⼀个列表，⽽且还想包括供应商1001和1002⽣产的所有物品。
-- 先查询第⼀个结果
select vend_id,prod_id,prod_price from products where prod_price <= 5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
4 rows in set (0.00 sec)
-- 再查询第⼆个结果
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
5 rows in set (0.00 sec)
--使⽤union将两个sql⼀并执⾏
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
8 rows in set (0.09 sec)
-- 这条语句由前⾯的两条SELECT语句组成，语句中⽤UNION关键字分隔。
-- UNION指示MySQL执⾏两条SELECT语句，并把输出组合成单个查询结果集
-- 以下是同样结果,使⽤where的多条件来实现
select vend_id,prod_id,prod_price from products where prod_price <= 5 or
vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)
-- 在这个简单的例⼦中，使⽤UNION可能⽐使⽤WHERE⼦句更为复杂。
-- 但对于更复杂的过滤条件，或者从多个表（⽽不是单个表）中检索数据的情形，使⽤UNION可能会使处理更简单。

二、MySQL事务概述

事务（Transaction）是由⼀系列对系统中数据进行访问与更新的操作所组成的⼀个程序执⾏逻辑单元。

• 事物的语法
• 事物的的特性
• 事物的并发问题
• 事物的隔离级别
• 不同隔离级别的锁的情况
• 隐式提交

事务的语法

1. start transaction;/ begin; 
2. commit; 使得当前的修改确认 
3. rollback; 使得当前的修改被放弃

事务的ACID特性

1. 原子性（Atomicity）

事务的原子性是指事务必须是⼀个原子的操作序列单元。事务中包含的各项操作在⼀次执行过程中，只允许出现两种状态之⼀。

• 全部执行成功
• 全部执行失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错， 会回滚到事务开始前的状态，所有的操作就像没有发生⼀样。也就是说事务是⼀个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2. 一致性（Consistency）

事务的⼀致性是指事务的执行不能破坏数据库数据的完整性和⼀致性，⼀个事务在执行之前和执行之 后，数据库都必须处以⼀致性状态。

比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，而B账户没有加钱。

3. 隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。

隔离性分4个级别，下面会介绍。

4. 持久性（Duration

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服 务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。

事物的并发问题

1. 脏读

读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的 数据是脏数据。 

2. 不可重复读

同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同⼀数据，事务 B 在事务A 多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同⼀数据时，结果不⼀致。

3. 幻读

重复查询的过程中，数据就发生了量的变化（insert， delete）。

create table users(
 id int auto_increment primary key,
 name varchar(10),
 age int,
 account int
)engine = innodb default charset=utf8mb4;
insert into users values(null,'张三',25,10000),(null,'李四',20,100),(null,'王
五',23,0);

事物的的隔离级别

事务隔离级别	脏 读	不不可重复读	幻 读
读未提交（READ_UNCOMMITTED）	允许	允许	允许
读已提交（READ_COMMITTED）禁止	允许	允许
可重复读（REPEATABLE_READ）	禁止	禁止	允许
顺序读（SERIALIZABLE）	禁止	禁止	禁止

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。 ⼀般数据默认级别是读以提交或可重复读。

查看当前会话中事务的隔离级别

select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)

设置当前会话中的事务隔离级别

set session transaction isolation level read uncommitted; 

1. 读未提交（READ_UNCOMMITTED）

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果⼀个事务正在处理某⼀数 据，并对其进行了更新，但同时尚未完成事务，因此还没有提交事务；而以此同时，允许另⼀个事务也能够访问该数据。

脏读示例：

在事务A和事务B同时执行时可能会出现如下场景：

时间	事务A（存款）	事务B(取款）
T1	开始事务	——
T2	——	开始事务
T3	——	查询余额（1000元）
T4	——	取出1000元（余额0元）
T5	查询余额（0元）	——
T6	——	撤销事务（余额恢复1000元）
T7	存⼊入500元（余额500元）	——
T8	提交事务	——

余额应该为1500元才对。请看T5时间点，事务A此时查询的余额为0，这个数据就是脏数据，他是事务B造成的，很明显是事务没有进行隔离造成的。

2. 读已提交（READ_COMMITTED）

读已提交是不同的事务执行的时候只能获取到已经提交的数据。 这样就不会出现上面的脏读的情况了。 但是在同⼀个事务中执行同⼀个读取,结果不⼀致

不可重复读示例

可是解决了脏读问题，但是还是解决不了可重复读问题。

时间	事务A（存款）	事务B(取款）
T1	开始事务	——
T2	——	开始事务
T3	——	查询余额（1000元）
T4	查询余额（1000元）	——
T5	——	取出1000元（余额0元）
T6	——	提交事务）
T7	查询余额（0元）	——
T8	提交事务	——

事务A其实除了了查询两次以外，其它什什么事情都没做，结果钱就从1000变成0了了，这就是不不可重复读的 问题。

3. 可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理过程中，多次读取同⼀个数据时，该数据的值和事务开始时刻是⼀致的。 因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。

幻读

幻读就是指同样的事务操作，在前后两个时间段内执行对同⼀个数据项的读取，可能出现不⼀致的结 果。

诡异的更新事件

时间	事务A（存款）	事务B(取款）
T1	开始事务	——
T2	查询当前所有数据	开始事务
T3	——	插入一条数据
T4	查询当前所有数据	提交事务
T5	进行行范围修改	——
T6	查询当前所有数据	——
T7	提交事务	——

4. 顺序读（SERIALIZABLE）

顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执行，即事务只能⼀一个接⼀一个地处理理，不能并发。

不同隔离级别的锁的情况（了解）

1. 读未提交（RU）： 有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
2. 读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
3. 可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是⼀样的，并且没有幻读的情 况。
4. 序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了

隐式提交（了解）

DQL:查询语句

DML:写操作(添加,删除,修改)

DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)

DCL:控制语⾔(给⽤户授权,或删除授权)

DDL（Data Define Language）：都是隐式提交。

隐式提交：执行这种语句相当于执行commit; DDL

三、MySQL中的特性-扩展

MySQL存储过程

什么是存储过程？

⽬前使用的⼤多数SQL语句都是针对⼀个或多个表的单条语句。并非所有操作都这么简单，经常会有⼀个完整的操作需要多条语句才能完成。

例如以下的情形：

• 为了处理订单，需要核对以保证库存中有相应的物品。
• 如果库存有物品，需要预定以便不将它们再卖给别的⼈， 并减少可用的物品数量以反映正确的库 存量。
• 库存中没有的物品需要订购，这需要与供应商进⾏某种交互。

执行这个处理需要针对许多表的多条MySQL语句。可能需要执行的具体语句及其次序也不是固定的。

那么，怎样编写此代码?可以单独编写每条语句，并根据结果有条件地执行另外的

在每次需要这个处理时(以及每个需要它的应用中都必须做这些工作。

可以创建存储过程

• 存储过程简单来说，就是为以后的使⽤⽽保存 的⼀条或多条MySQL语句的集合。
• 储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调用。
• 存储过程就像脚本语言中函数定义⼀样。

为什么要使用存储过程?

优点：

• 可以把⼀些复杂的sql进行封装,简化复杂操作
• 保证了数据的完整性,防止错误
• 简单的变动只需要更改存储过程的代码即可
• 提高性能。因为使⽤存储过程比使用单独的SQL语句要快。(预先编译)

缺点：

• 存储过程的编写比SQL语句复杂
• ⼀般可能还没有创建存储过程的权限,只能调用

个人观点：

• 业务逻辑不要封装在数据库里面,应该由应⽤程序(JAVA、Python、PHP)处理。
• 让数据库只做它擅长和必须做的，减少数据库资源和性能的消耗。
• 维护困难，大量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动A影响B。
• 人员也难招聘，因为既懂存储过程，又懂业务的人少。使用困难。

创建存储过程

\d // 修改MySQL默认的语句结尾符 ; ，改为 // 。 

create procedure 创建语句 

BEGIN和END语句⽤来限定存储过程体

-- 定义存储过程
\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @i=@i+1;
end while;
end;
//

执行存储
call p1()

查看存储过程
show create procedure p1\G

删除存储过程
drop procedure p1

MySQL的触发器

MySQL语句在需要时被执行，存储过程也是如此。
但是，如果你想要某条语句(或某些语句)在事件发生时自动执行，怎么办呢?

例如：

• 每当增加⼀个顾客到某个数据库表时，都检查其电话号码格式是否正确;
• 每当订购⼀个产品时，都从库存数量中减去订购的数量;
• 无论何时删除⼀行，都在某个存档表中保留⼀个副本。

所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。

触发器定义

触发器是MySQL响应写操作(增、删、改)而自动执行的⼀条或⼀组定义在BEGIN和END之间的

MySQL语句 或可理解为：提前定义好⼀个或⼀组操作,在指定的SQL操作前或后来触发指定的SQL自动执行

触发器就像是JavaScript中的事件⼀样

举例：定义⼀个update语句,在向某个表中执行insert添加语句时来触发执行,就可以使用触发器

语法

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON tbl_name FOR EACH ROW trigger_stmt
 
说明：
# trigger_name：触发器名称
# trigger_time:触发时间，可取值：BEFORE或AFTER
# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
# tb1_name：指定在哪个表上
# trigger_stmt：触发处理SQL语句

-- 查看所有的 触发器
show triggers\G;

-- 删除触发器
drop trigger trigger_name;

触发器Demo

注意：如果触发器中SQL有语法错误,那么整个操作都会报错

-- 创建⼀个删除的触发器,在users表中删除数据之前,往del_users表中添加⼀个数据
-- 1,复制当前的⼀个表结构
create table del_users like users;
-- 2,创建 删除触发器 注意在创建删除触发器时,只能在删除之前才能获取到old(之前的)数据
\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id,old.name,old.age,old.account);
end;
//
\d ;
-- 3 删除users表中的数据去实验

tips：

• 在INSERT触发器代码内，可引用⼀个名为NEW的虚拟表，访问被插入的行
• 在DELETE触发器代码内，可以引用⼀个名为OLD的虚拟表，访问被删除的行;
  • OLD中的值全都是只读的，不能更新
  • 在AFTER DELETE的触发器中⽆法获取OLD虚拟表
• 在UPDATE触发器代码中
  • 可以引用⼀个名为OLD的虚拟表访问更新以前的值
  • 可以引用⼀个名为NEW的虚拟表访问新 更新的值;

练习题：

用触发器来实现数据的统计

    -- 1.创建⼀个表, users_count ⾥⾯有⼀个 num的字段 初始值为0或者是你当前users表中的count
    -- 2,给users表创建⼀个触发器
    -- 当给users表中执⾏insert添加数据之后,就让users_count⾥⾯num+1,
    -- 当users表中的数据删除时,就让users_count⾥⾯num-1,
    -- 想要统计users表中的数据总数时,直接查看 users_count

MySQL中的视图

什么是视图？

视图是虚拟的表与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。

视图仅仅是⽤来查看存储在别处的数据的⼀种设施或方法。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任⼀个检索。

如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

视图中的作用

1. 重用SQL语句。
2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
3. 使用表的组成部分而不是整个表。
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
6. 注意:视图不能索引，也不能有关联的触发器或默认值。

视图的基础语法
创建视图

create view v_users as select id,name,age from users where age >= 25 and age<= 35;
-- Query OK, 0 rows affected (0.00 sec)

view视图的帮助信息

mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

查看当前库中所有的视图

show tables; --可以查看到所有的表和视图
show table status where comment='view'; --只查看当前库中的所有视图

删除视图v_t1:

mysql> drop view v_t1;