一、子查询

在一个sql当中，它的where条件来源于另外一个sql，或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。

1.1利⽤⼦查询进⾏过滤

--（1） 查询包含物品TNT2的所有订单编号
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
-- (2) 查询对应订单编号的用户ID
select cust_id from orders where order_num in(20005,20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
-- (3) 查询购买对应物品的用户信息
select cust_id,cust_name from customers where cust_id in(10001,10004);
+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10004 | Yosemite Place |
+---------+----------------+
-- 转换为嵌套SQL，子查询
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
    SELECT cust_id
    FROM orders
    WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' )
    );

注意：子查询是已知数据库中查询最慢的，尽量少使用

二、关系表

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

这样做的好处如下：
（1）供应商信息不重复，从⽽不浪费时间和空间；
（2）如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不⽤改动；
（3）由于数据⽆重复，显然数据是⼀致的，这使得处理数据更简单

2.1外键：

在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键就是在一个<br />     表中的字段，代表着这个数据属于谁<br />    了解：<br />        外键实现的方式，有两种：物理外键、逻辑外键<br />        物理外键：<br />            就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段<br />            需要在定义字段时，使用sql语句来实现<br />        逻辑外键：<br />            就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现

2.2一对一

就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个
如：
员工表：
id，姓名、性别、年龄、籍贯、联系方式、学历、工龄、。。。。
由上面的一个表，拆分成两个表
员工表：
id，姓名、联系方式、工龄、
12 张三 1010 3
13 李四 1020 2
详情表：
yid 性别、籍贯、学历、、、、、
12 男 山东 本科
13 男 山西 本科
上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。
一个员工有着一个对应的详情信息，存储在详情表中，
在详情表中的数据，也只属于某一个员工。

2.3一对多

在一个表中的一条数据对应着另外一个表中的多条数据
如：
商品分类
id 分类名
1 手机
2 电脑
商品
id 所属分类id，商品名
1 1 小米手机
2 1 华为手机

2.3多对多

举例：
一个班级有多个老师来讲课（化学、物理、数学、、、）
一个老师要带多个班级 （一班，二班，三班）

总结：
关系数据可以有效地存储和⽅便地处理。因此，关系数据库的可伸缩性远⽐⾮关系数据库要好。

三、表联结

联结是⼀种机制，⽤来在⼀条SELECT语句中关联表。
使⽤特殊的语法，可以联结多个表返回⼀组输出，联结在运⾏时关联表中正确的⾏。

需要查询出所有商品以及对应的供应商信息？
供应商名称，商品名称，商品价格，它们在vendors,products两个表

select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id  = products.vend_id
order by vend_name
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Sling          |       4.49 |
| ACME        | Carrots        |       2.50 |
| ACME        | Safe           |      50.00 |
| ACME        | Bird seed      |      10.00 |
| ACME        | Detonator      |      13.00 |
| ACME        | TNT (5 sticks) |      10.00 |
| ACME        | TNT (1 stick)  |       2.50 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Jet Set     | JetPack 2000   |      55.00 |
| Jet Set     | JetPack 1000   |      35.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+

1.vendors,products两个表联结原理：

在联结两个表时，你实际上做的是将第⼀个表中的每⼀⾏与第⼆个表中的每⼀⾏配对。
WHERE⼦句作为过滤条件，它只包含那些匹配给定条件（这⾥是联结条件）的⾏。

2.where必须：

如果没有where条件，第⼀个表中的每个⾏将与第⼆个表中的每个⾏配对，⽽不管它们逻辑上是
否可以配在⼀起
由没有联结条件的表关系返回的结果为笛卡⼉积。检索出的⾏的数⽬将是第⼀个表中的⾏数乘以
第⼆个表中的⾏数。

了使用where进行表的联结查询外，还可以使用另外一种联结方式，join

select vend_name,prod_name,prod_price
from vendors
inner join products on vendors.vend_id = products.vend_id;

上面这个sql就是使用了 join 的语法，进行了两个表的联结，在 on 后面 去定义了 联结的条件。
注：
在引⽤的列可能出现⼆义性时，必须使⽤完全限定列名（⽤⼀个点分隔的表名和列名）。

四、联结多个表

SQL对⼀条SELECT语句中可以联结的表的数⽬没有限制。

4.1内联结

只连接匹配的行

案例： 查询出订单号为20005的订单中购买的商品及对应的产品供应商信息

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
    where products.vend_id = vendors.vend_id
    and orderitems.prod_id = products.prod_id
    and order_num = 20005;

改写为 join 的语法

select prod_name,vend_name,prod_price,quantity
from orderitems
inner join products on orderitems.prod_id = products.prod_id
inner join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;

MySQL在运⾏时关联指定的每个表以处理联结。 这种处理可能是⾮常耗费资源的，因此应该仔
细，不要联结不必要的表。联结的表越多，性能下降越厉害。

4.2使⽤表别名 AS

别名除了⽤于列名和计算字段外，SQL还允许给表名起别名。
这样做有两个主要理由：
（1）缩短SQL语句；
（2）允许在单条SELECT语句中多次使⽤相同的表
应该注意，表别名只在查询执⾏中使⽤。与列别名不⼀样，表别名不返回到客户机

4.3⾃联结

自联结:当前这个表与自己这个表 做联结（join）
例子：假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
— 使用子查询（嵌套查询）

select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

— 使用 自联结方式查询

select p1.prod_id,p2.prod_name
from products as p1
join products as p2 on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';

— 改成where语句

select p1.prod_id,p2.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| DTNTR   | Detonator |
| FB      | Detonator |
| FC      | Detonator |
| SAFE    | Detonator |
| SLING   | Detonator |
| TNT1    | Detonator |
| TNT2    | Detonator |
+---------+-----------+

4.4外部链接

分为left join和right join：
left join ： 是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null
right join ：是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null

以用户表为基准，去关联查询 订单表数据

select customers.cust_id,orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;

另一种写法
select customers.cust_id,orders.order_num
from orders right join customers
on customers.cust_id = orders.cust_id;

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

select customers.cust_id,count(orders.order_num) as nums
from customers left join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;
+---------+------+
| cust_id | nums |
+---------+------+
|   10001 |    2 |
|   10002 |    0 |
|   10003 |    1 |
|   10004 |    1 |
|   10005 |    1 |
+---------+------+

4.5总结：

表联结
内部联结： where， inner join(join)
自联结 : 是在一个sql中，用当前这个表，连接自己这个表进行关联查询
外部联结： left join，right join

五、组合查询 UNION

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

5.1UNION规则

（1）UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔（因此，
如果组合4条SELECT语句，将要使⽤3个UNION关键字）。
（2）UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次
序列出）
（3）列数据类型必须兼容：类型不必完全相同，但必须是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 |
+---------+---------+------------+
    --在这个简单的例⼦中，使⽤UNION可能⽐使⽤WHERE⼦句更为复杂。
--但对于更复杂的过滤条件，或者从多个表（⽽不是单个表）中检索数据的情形，使⽤UNION可能会使处理
更简单。

注意：
UNION从查询结果集中⾃动去除了重复的⾏（换句话说，它的⾏为与单条SELECT语句中使
⽤多个WHERE⼦句条件⼀样）。
这是UNION的默认⾏为，但是如果需要，可以改变它。如果想返回所有匹配⾏，可使⽤UNION
ALL⽽不是UNION

5.2对组合查询结果排序

SELECT语句的输出⽤ORDER BY⼦句排序。在⽤UNION组合查询时，只能使⽤⼀条ORDER
BY⼦句，它必须出现在最后⼀条SELECT语句之后。
对于结果集，不存在⽤⼀种⽅式排序⼀部分，⽽⼜⽤另⼀种⽅式排序另⼀部分的情况，因此不允
许使⽤多条ORDER BY⼦句。

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)
order by prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1003 | TNT1 | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1001 | ANV01 | 5.99 |
| 1002 | OL1 | 8.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)
--这条UNION在最后⼀条SELECT语句后使⽤了ORDER BY⼦句。
--虽然ORDER BY⼦句似乎只是最后⼀条SELECT语句的组成部分，但实际上MySQL将⽤它来排序所有
SELECT语句返回的所有结果。

六、50道练习题

1. 查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数

SELECT student.SId,student.Sage,student.Sname,student.Ssex,sc1.Cname,sc1.score 
FROM student 
JOIN 
(
SELECT course1.SId,course1.Cname,course1.score FROM
(SELECT sc.SId,course.Cname,sc.score FROM sc JOIN course on course.Cid=sc.CId WHERE sc.Cid="01") as course1 JOIN
(SELECT sc.SId,course.Cname,sc.score FROM sc JOIN course on course.Cid=sc.CId WHERE sc.Cid="02") as course2 ON course1.SId=course2.SId WHERE course1.score>course2.score
) as sc1 
on sc1.SId=student.SId;

#2. 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT * FROM 
(SELECT sc.SId,course.Cname,sc.score FROM sc JOIN course on course.Cid=sc.CId WHERE sc.Cid="01") as course1 JOIN
(SELECT sc.SId,course.Cname,sc.score FROM sc JOIN course on course.Cid=sc.CId WHERE sc.Cid="02") as course2 ON course1.SId=course2.SId ;

#3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT * FROM 
(SELECT sc.SId,course.Cname,sc.score FROM sc JOIN course on course.Cid=sc.CId WHERE sc.Cid="01") as course1 LEFT JOIN
(SELECT sc.SId,course.Cname,sc.score FROM sc JOIN course on course.Cid=sc.CId WHERE sc.Cid="02") as course2 ON course1.SId=course2.SId ;


#4. 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM sc
WHERE SId NOT in(SELECT SId FROM sc WHERE CId="01")
AND CId="02";



#5.查询平均成绩大于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
SELECT student.SId,student.Sname,AVG(sc.score) AS avg_score FROM
student JOIN sc ON student.SId=sc.SId
GROUP BY student.SId,student.Sname
HAVING avg_score>=60;


#6.查询在 SC 表存在成绩的学⽣信息
SELECT DISTINCT student.* FROM 
student  JOIN sc 
ON sc.Sid=student.SId;

#7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT student.SId,student.Sname,COUNT(sc.CId) as count_cid,SUM(sc.score) as sum_score FROM
student LEFT JOIN sc ON student.SId=sc.SId
GROUP BY student.SId,student.Sname;

#8.查询「李」姓⽼师的数量
SELECT COUNT(teacher.TId) FROM teacher WHERE Tname LIKE '李%';


#9.查询学过「张三」⽼师授课的同学的信息
SELECT student.* FROM student JOIN sc ON sc.SId=student.SId 
JOIN course ON course.CId=sc.CId
JOIN teacher ON course.TId=teacher.TId
WHERE Tname="张三";

#10.查询没有学全所有课程的同学的信息
SELECT student.* FROM student
WHERE student.SId not in(SELECT SId FROM 
(SELECT student.SId,COUNT(sc.CId) as cs FROM 
student RIGHT JOIN
sc ON sc.SId=student.SId
GROUP BY student.SId
HAVING cs=3) as s1);


#11查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
SELECT DISTINCT stu.* FROM student AS stu 
JOIN sc on stu.SId=sc.SId
where sc.CId IN (SELECT sc.CId FROM sc WHERE sc.SId="01");


#12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT
    s2.Sid,
    student.Sname 
FROM
    sc AS s1
    JOIN sc AS s2 ON s1.Cid = s2.Cid
    AND s1.SId = "01" 
    AND s2.SId <> "01"
    JOIN student ON student.SId = s2.SId 
GROUP BY
    s2.Sid,
    student.Sname 
HAVING
    COUNT( s2.CId ) = ( SELECT COUNT(*) FROM sc WHERE SId = "01" );


#13.查询没学过"张三"⽼师讲授的任一门课程的学⽣姓名
SELECT student.Sname FROM student JOIN
(SELECT SId FROM sc WHERE CId in(SELECT CId  FROM course WHERE TId in(SELECT TId FROM teacher WHERE Tname="张三"))) as s1
ON student.SId = s1.SId;


#14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT student.SId,any_value(student.Sname),AVG(sc.score)
FROM student JOIN sc
ON student.SId = sc.SId
WHERE sc.score < 60
GROUP BY student.SId HAVING COUNT(sc.CId) >= 2;

#15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
SELECT student.*,sc.score
FROM student JOIN sc
ON student.SId = sc.SId 
WHERE  sc.CId="01" AND sc.score < 60
ORDER BY sc.score DESC;


#16.按平均成绩从高到低显示所有学⽣的所有课程的成绩以及平均成绩
SELECT
student.Sname,
s1.score AS "数学",
s2.score AS "语文",
s3.score AS "英语",
avg( s4.score ) AS "平均分" 
FROM
    student
    LEFT JOIN sc AS s1 ON student.SId  = s1.SId 
    AND s1.CId = "01"
    LEFT JOIN sc AS s2 ON student.SId  = s2.SId 
    AND s2.CId = "02"
    LEFT JOIN sc AS s3 ON student.SId  = s3.SId 
    AND s3.CId = "03"
    LEFT JOIN sc AS s4 ON student.SId  = s4.SId 
GROUP BY
    student.Sname,数学,语文,英语 
ORDER BY
    平均分 DESC;

#17.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：要求输出>=90 课程号和选修人数，查询结果按人数数降序排列，若人数数相同，按课程号升序排列
SELECT
    sc.cid,
    c.cname,
    max( sc.score ) AS '最高分',
    min( sc.score ) AS '最低分',
    round( avg( sc.score ), 2 ) AS '平均分',
    count( sc.cid ) AS '选修人数',
    SUM( CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END  ) / count( sc.cid ) AS '及格率',
    SUM( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END  ) / count( sc.cid ) AS '中等率',
    SUM( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END )/ count( sc.cid ) AS '优良率',
    SUM( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END ) / count( sc.cid ) AS '优秀率' 
FROM
    sc
    JOIN course AS c ON sc.cid = c.cid 
GROUP BY
    sc.cid,
    c.cname 
ORDER BY
    选修人数 DESC,
    sc.cid;



    #18.按各科平均成绩进⾏排序，并显示排名， Score 重复时保留名次空缺
SELECT
    s2.CId,
    s2.avg,
    COUNT( DISTINCT s1.avg ) AS rank 
FROM
    ( SELECT sc.CId, ROUND( AVG( sc.score ), 2 ) AS avg FROM sc GROUP BY sc.CID ) AS s1
    JOIN ( SELECT sc.CID, ROUND( AVG( sc.score ), 2 ) AS avg FROM sc GROUP BY sc.CID ) AS s2 ON s1.avg >= s2.avg 
GROUP BY
    s2.CId,
    s2.avg 
ORDER BY
    rank;




    #19.按各科平均成绩进⾏排序，并显示排名， Score 重复时不保留名次空缺
SELECT
    b.CId,
    b.avg,
    @i := @i + 1 AS rank 
FROM
    ( SELECT @i := 0 ) AS a,
    (
    SELECT
        sc.CId,
        ROUND( AVG( sc.score ), 2 ) AS avg 
    FROM
        sc
        JOIN course ON sc.CId = course.CId 
    GROUP BY
        sc.CId 
    ORDER BY
    avg DESC 
    ) AS b;



    #20.查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺
SELECT
    s2.CId,
    s2.sum,
    COUNT( DISTINCT s1.sum ) AS rank 
FROM
    ( SELECT sc.CId, ROUND( SUM( sc.score ), 2 ) AS sum FROM sc GROUP BY sc.CID ) AS s1
    JOIN ( SELECT sc.CID, ROUND( SUM( sc.score ), 2 ) AS sum FROM sc GROUP BY sc.CID ) AS s2 ON s1.sum >= s2.sum
GROUP BY
    s2.CId,
    s2.sum
ORDER BY
    rank;


    #21.查询学⽣的总成绩，并进⾏排名，总分重复时不保留名次空缺
SELECT
    b.CId,
    b.sum,
    @i := @i + 1 AS rank 
FROM
    ( SELECT @i := 0 ) AS a,
    (
    SELECT
        sc.CId,
        ROUND(sum( sc.score ), 2 ) AS sum
    FROM
        sc
        JOIN course ON sc.CId = course.CId 
    GROUP BY
        sc.CId 
    ORDER BY
    sum DESC 
    ) AS b;


    #22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
SELECT
    course.CId,
    course.Cname,
    CONCAT(
        ROUND( sum( CASE WHEN sc.score < 60 THEN 1 ELSE 0 END ) / count( sc.CId ), 2 ),
        "%" 
    ) AS "[60-0]",
    CONCAT(
        ROUND(
            sum( CASE WHEN sc.score < 70 AND sc.score >= 60 THEN 1 ELSE 0 END ) / count( sc.CId ),
            2 
        ),
        "%" 
    ) AS "[70-60]",
    CONCAT(
        ROUND(
            sum( CASE WHEN sc.score < 85 AND sc.score >= 70 THEN 1 ELSE 0 END ) / count( sc.CId ),
            2 
        ),
        "%" 
    ) AS "[85-70]",
    CONCAT(
        ROUND(
            sum( CASE WHEN sc.score >= 85 AND sc.score <= 100 THEN 1 ELSE 0 END ) / count( sc.CId ),
            2 
        ),
        "%" 
    ) AS "[100-85]" 
FROM
    course
    JOIN sc ON course.CId = sc.CId 
GROUP BY
    course.CId;



    #23.查询各科成绩前三名的记录
SELECT
    a.SId,
    a.CId,
    a.score 
FROM
    sc AS a
    LEFT JOIN sc AS b ON a.CId = b.CId 
    AND a.score < b.score 
GROUP BY
    a.SId,
    a.CId
HAVING
    COUNT( b.SId ) < 3
ORDER BY
    a.CId,
    a.score DESC;


    #24.查询每⻔课程被选修的学生数
SELECT
    sc.CId,
    COUNT( sc.SId ) 
FROM
    student
    JOIN sc ON student.SId = sc.SId 
GROUP BY
    sc.CId;

#25.查询出只选修两⻔课程的学⽣学号和姓名
SELECT s.SId,s.Sname
FROM student as s JOIN
sc ON sc.SId = s.SId
GROUP BY s.SId,s.Sname
HAVING COUNT(sc.CId) >=2;


#26.查询男女数
SELECT
    Ssex,
    COUNT( Ssex ) 
FROM
    student 
GROUP BY
    Ssex;

#27.查询名字中含有「风」字的学⽣信息
SELECT
    * 
FROM
    student 
WHERE
    Sname LIKE "%凤%";


    #28.查询同名同性学生名单，并统计同名人数
SELECT
    Sname,
    COUNT(*) 
FROM
    student 
GROUP BY
    Sname 
HAVING
    COUNT(*)> 1;

    #29.查询 1990 年出⽣的学⽣名单
SELECT
    * 
FROM
    student 
WHERE
    YEAR ( Sage )= 1990;



    #30.查询每⻔课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
    CId,
    AVG( score ) AS avg 
FROM
    sc 
    GROUP BY CId
ORDER BY
    avg ASC,
    CId DESC;


    #31.查询平均成绩⼤于等于 85 的所有学⽣的学号、姓名和平均成绩
SELECT
    s.SId,
    s.Sname,
    AVG( sc.score )  as avg
FROM
    student AS s,
    sc 
WHERE
    s.SId = sc.SId 
GROUP BY
    s.SId,
    s.Sname 
HAVING
    avg>85;


    #32.查询课程名称为「数学」，且分数低于 60 的学⽣姓名和分数
SELECT
    student.Sname,
    sc.score 
FROM
    student,
    sc,
    course 
WHERE
    student.SId = sc.SId 
    AND sc.CId = course.CId 
    AND course.Cname = "数学" 
    AND sc.score < 60;


    #33.查询所有学⽣的课程及分数情况（存在学⽣没成绩，没选课的情况）
SELECT
    student.Sname,
    course.Cname,
    sc.score 
FROM
    student
    LEFT JOIN sc ON student.SId = sc.SId
    LEFT JOIN course ON sc.CId = course.CId;


    #34.查询任何1课程成绩在 70 分以上的姓名、课程名称和分数
SELECT
    student.*,
    sc.* 
FROM
    student,
    sc 
WHERE
    student.SId = sc.SId 
GROUP BY
    student.SId,
    sc.CId 
HAVING
    sc.score > 70;

    #35.查询不及格的课程
    可以用group by 来取唯一，也可以用distinct
SELECT
    cid 
FROM
    sc 
WHERE
    score < 60 
GROUP BY
    cid;

    SELECT DISTINCT
    sc.CId 
FROM
    sc 
WHERE
    sc.score < 60;


    #36.查询课程编号为 01 且课程成绩在 80 分以上的学⽣的学号和姓名
SELECT
    student.Sname,
    student.SId 
FROM
    student
    JOIN sc ON student.SId = sc.SId 
WHERE
    sc.CId = "01" 
    AND sc.score >= 80;

    #37.求每⻔课程的学生数
SELECT
    CId,
    COUNT( CId ) 
FROM
    sc 
GROUP BY
    CId;


    #38.成绩不重复，查询选修「张三」老师所授课程的学⽣中，成绩最高的学生信息及其成绩
SELECT DISTINCT
    student.SId,
    student.Sname,
    sc.score 
FROM
    student
    JOIN sc ON student.SId = sc.SId
    JOIN course ON course.CId = sc.CId
    JOIN teacher ON teacher.TId = course.TId 
WHERE
    teacher.Tname = "张三" 
ORDER BY
    sc.score DESC 
    LIMIT 1;



    #39.成绩有重复的情况下，查询选修「张三」老师所授课程的学⽣中，成绩最高的学生信息及其成绩
SELECT DISTINCT
    student.SId,
    student.Sname,
    sc.score 
FROM
    student
    JOIN sc ON student.SId = sc.SId
    JOIN course ON course.CId = sc.CId
    JOIN teacher ON teacher.TId = course.TId 
WHERE
    teacher.Tname = "张三" 
    AND sc.score = (
    SELECT
        MAX( sc.score ) 
    FROM
        student
        JOIN sc ON student.SId = sc.SId
        JOIN course ON course.CId = sc.CId
        JOIN teacher ON teacher.TId = course.TId 
    WHERE
    teacher.Tname = "张三" 
    );


    #40.查询不同课程成绩相同的学学的学生编号、课程编号、学习成绩
SELECT
    a.SId,
    a.CId,
    b.CId,
    a.score 
FROM
    sc AS a
    JOIN sc AS b ON a.SId = b.SId 
WHERE
    a.CId <> b.CId 
    AND a.score = b.score 
GROUP BY
    a.SId,
    a.CId,
    a.score;

    #41.查询每门课程成绩最好的前两名
SELECT
    a.SId,
    a.CId,
    a.score 
FROM
    sc AS a
    LEFT JOIN sc AS b ON a.CId = b.CId 
    AND a.score < b.score 
GROUP BY
    a.SId,
    a.CId
HAVING
    COUNT( b.SId ) < 2
ORDER BY
    a.CId,
    a.score DESC;


    #42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT
    CId,
    COUNT( CId ) 
FROM
    sc 
GROUP BY
    CId 
HAVING
    COUNT( CId )>= 5;



    #43.检索至少选修两门课程的学生学号
SELECT
    SId,
    COUNT( CId ) 
FROM
    sc 
GROUP BY
    SId 
HAVING
    COUNT( CId )>= 2;

    #44.查询选修了全部课程的学生信息
SELECT
    student.* 
FROM
    sc
    JOIN student ON sc.SId = student.SId 
GROUP BY
    sc.SId 
HAVING
    COUNT(*) = ( SELECT COUNT(*) FROM course );

    #45.查询各学⽣的年龄，只按年份来算
#46.按照出生日期来算，当前月日 < < 出生年月的月日则，年龄减一
SELECT
    Sname,
    ( YEAR ( CURDATE())- YEAR ( student.Sage ) ) AS "学生年龄" 
FROM
    student;


    #46.按照出生日期来算，当前月日 < < 出生年月的月日则，年龄减一
SELECT
    Sname,
    TIMESTAMPDIFF(
        YEAR,
        student.Sage,
    CURDATE()) AS "学生年龄" 
FROM
    student;

    #47.查询本周过生日的学生
SELECT
    * 
FROM
    student 
WHERE
    WEEKOFYEAR( student.Sage ) = WEEKOFYEAR(CURDATE());


    #48查询下周过生日的学生
    SELECT
    * 
FROM
    student 
WHERE
    WEEKOFYEAR( student.Sage ) = WEEKOFYEAR(CURDATE())+1;

    #49.查询本月过生日的学生
SELECT
    * 
FROM
    student 
WHERE
MONTH(student.Sage) = MONTH(CURDATE());

#50查询下月过生日的学生
SELECT
    * 
FROM
    student 
WHERE
MONTH(student.Sage) = MONTH(CURDATE())+1;

七、面试题

. 用一条SQL语句查询出每门课都大于80分的学生姓名

+--------+--------+-------+
| name   | course | score |
+--------+--------+-------+
| 张三   | 语文   |    81 |
| 张三   | 数学   |    75 |
| 李四   | 语文   |    76 |
| 李四   | 数学   |    90 |
| 王五   | 语文   |    81 |
| 王五   | 数学   |   100 |
| 王五   | 英语   |    90 |
+--------+--------+-------+
7 rows in set (0.00 sec)

-- 先根据学员进行分组,看每个人的最低分
select name,min(score) from mst_stu group by name;
+--------+------------+
| name   | min(score) |
+--------+------------+
| 张三   |         75 |
| 李四   |         76 |
| 王五   |         81 |
+--------+------------+

-- 在使用分组过滤 having 筛选出最低分大于80
select name,min(score) as min_sc from mst_stu group by name having min_sc > 80;


-- 最终只需要符合要求的学员姓名
select min(score) as min_sc from mst_stu group by name having min_sc > 80;









2. 查询后一天 temperature 比前一天高的date
    比昨天温度高的ID

select * from mst_weather;
+----+------------+-------------+
| id | date       | temperature |
+----+------------+-------------+
|  1 | 2022-04-01 |          20 |
|  2 | 2022-04-02 |          25 |
|  3 | 2022-04-03 |          21 |
|  4 | 2022-04-04 |          24 |
+----+------------+-------------+

select s1.*,s2.*
from mst_weather as s1
join mst_weather as s2


+----+------------+-------------+----+------------+-------------+
| id | date       | temperature | id | date       | temperature |
+----+------------+-------------+----+------------+-------------+
-- |  1 | 2022-04-01 |          20 |  1 | 2022-04-01 |          20 |
|  2 | 2022-04-02 |          25 |  1 | 2022-04-01 |          20 |
-- |  3 | 2022-04-03 |          21 |  1 | 2022-04-01 |          20 |
-- |  4 | 2022-04-04 |          24 |  1 | 2022-04-01 |          20 |
-- |  1 | 2022-04-01 |          20 |  2 | 2022-04-02 |          25 |
-- |  2 | 2022-04-02 |          25 |  2 | 2022-04-02 |          25 |
-- |  3 | 2022-04-03 |          21 |  2 | 2022-04-02 |          25 |
-- |  4 | 2022-04-04 |          24 |  2 | 2022-04-02 |          25 |
-- |  1 | 2022-04-01 |          20 |  3 | 2022-04-03 |          21 |
-- |  2 | 2022-04-02 |          25 |  3 | 2022-04-03 |          21 |
-- |  3 | 2022-04-03 |          21 |  3 | 2022-04-03 |          21 |
|  4 | 2022-04-04 |          24 |  3 | 2022-04-03 |          21 |
-- |  1 | 2022-04-01 |          20 |  4 | 2022-04-04 |          24 |
-- |  2 | 2022-04-02 |          25 |  4 | 2022-04-04 |          24 |
-- |  3 | 2022-04-03 |          21 |  4 | 2022-04-04 |          24 |
-- |  4 | 2022-04-04 |          24 |  4 | 2022-04-04 |          24 |
+----+------------+-------------+----+------------+-------------+
16 rows in set (0.00 sec)

select s1.id
from mst_weather as s1
join mst_weather as s2
on datediff(s1.date,s2.date) = 1
and s1.temperature > s2.temperature;

+----+
| id |
+----+
|  2 |
|  4 |
+----+
2 rows in set (0.00 sec)





3. 查询每个主播的最大level以及对应的最小gap(注意:不是每个主播的最大level和最小gap)

select * from mst_zhubo;
+----------+-------+------+
| zhubo_id | level | gap  |
+----------+-------+------+
|      123 |     8 |   20 |
|      123 |     9 |   40 |
|      123 |     9 |   30 |
|      246 |     6 |   30 |
|      246 |     6 |   20 |
+----------+-------+------+
5 rows in set (0.00 sec)


-- 先查询每个主播的最大 level
select zhubo_id,max(level) from mst_zhubo group by zhubo_id;
+----------+------------+
| zhubo_id | max(level) |
+----------+------------+
|      123 |          9 |
|      246 |          6 |
+----------+------------+
2 rows in set (0.00 sec)

--在这个基础上,查询出每个主播所有符合最大level的数据

select * from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo group by zhubo_id)

+----------+-------+------+
| zhubo_id | level | gap  |
+----------+-------+------+
|      123 |     9 |   40 |
|      123 |     9 |   30 |
|      246 |     6 |   30 |
|      246 |     6 |   20 |
+----------+-------+------+
4 rows in set (0.01 sec)

-- 在这个基础上,按照主播分组,求最小的gap
select zhubo_id,level,min(gap)
from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo group by zhubo_id)
group by zhubo_id,level;

+----------+-------+----------+
| zhubo_id | level | min(gap) |
+----------+-------+----------+
|      123 |     9 |       30 |
|      246 |     6 |       20 |
+----------+-------+----------+
2 rows in set (0.01 sec)


4. 下表是每个课程class_id对应的年级(共有primary、middle、high三个),以及某种比率rate



mysql> select * from mst_class;
+----------+---------+------+
| class_id | grade   | rate |
+----------+---------+------+
| abc123   | primary | 70%  |
| abc123   | middle  | 65%  |
| abc123   | high    | 72%  |
| hjkk86   | primary | 69%  |
| hjkk86   | middle  | 63%  |
| hjkk86   | high    | 74%  |
+----------+---------+------+

select class_id,
max(CASE WHEN grade = 'primary' THEN rate ELSE 0 END) as 'primary',
max(CASE WHEN grade = 'middle' THEN rate ELSE 0 END) as 'middle',
max(CASE WHEN grade = 'high' THEN rate ELSE 0 END) as 'high'
from mst_class
group by class_id;

+----------+---------+--------+------+
| class_id | primary | middle | high |
+----------+---------+--------+------+
| abc123   | 70%     | 65%    | 72%  |
| hjkk86   | 69%     | 63%    | 74%  |
+----------+---------+--------+------+
2 rows in set (0.01 sec)

-- 使用IF()
select class_id,
max(IF(grade = 'primary',rate,0)) as 'primary',
max(IF(grade = 'middle',rate,0)) as 'middle',
max(IF(grade = 'high',rate,0)) as 'high'
from mst_class
group by class_id;





5.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value

这道题的SQL语句怎么写？

-- 先按照题设计表
create table `mst_a`(`key` varchar(10),`value` varchar(10));
create table `mst_b`(`key` varchar(10),`value` varchar(10));
insert into mst_a values('A','aaa'),('B','bbb'),('C','ccc');
insert into mst_b values('D','ddd'),('E','eee'),('A','abc');

mysql> select * from mst_a;
+------+-------+
| key  | value |
+------+-------+
| A    | aaa   |
| B    | bbb   |
| C    | ccc   |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from mst_b;
+------+-------+
| key  | value |
+------+-------+
| D    | ddd   |
| E    | eee   |
| A    | abc   |
+------+-------+
3 rows in set (0.00 sec)


-- 先查询出哪个key符合要求?
select mst_a.key,mst_a.value from mst_a join mst_b on mst_a.key = mst_b.key;

-- update mst_b set value = ? where key = ?
-- update mst_b as up ,(?) as b set up.value = ? where up.key = ?

update mst_b as up,(
    select mst_a.key,mst_a.value from mst_a join mst_b on mst_a.key = mst_b.key
    ) as b
set up.value = b.value where up.key = b.key

总结：

（1）update 后面是可以进行任何查询语句,这个作用等同于 from
（2）update 更新表,不能在set和where中用于子查询
（3）update 也可以对多个表进行更新 (sqlserver不行)
（4）IF()和casewhen作用相同

八、*事务

7.1定义：

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

7.2事务的语法

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

7.3事务的ACID特性

1. 原⼦性（Atomicity）

事务的原⼦性是指事务必须是⼀个原⼦的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只
允许出现两种状态之⼀。
全部执⾏成功
全部执⾏失败
事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，
会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。也就是说事务是⼀个不可分割的整体，就
像化学中学过的原⼦，是物质构成的基本单位。

2. ⼀致性（Consistency）

事务的⼀致性是指事务的执⾏不能破坏数据库数据的完整性和⼀致性，⼀个事务在执⾏之前和执⾏之
后，数据库都必须处以⼀致性状态。
⽐如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。

3. 隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数
据时，每个事务都有各⾃完整的数据空间。
⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。
隔离性分4个级别，7.5事务隔离级别会介绍。

4. 持久性（Duration）

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

7.4事务的并发问题

1.脏读：

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

2.不可重复读：

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

3.幻读：

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

7.5事务隔离级别

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

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

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

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

mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00
sec)

7.5.3读未提交（READ_UNCOMMITTED）

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果⼀个事务正在处理某⼀数
据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；⽽以此同时，允许另⼀个事务也
能够访问该数据。
脏读示例：
在事务A和事务B同时执⾏时可能会出现如下场景：

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

7.5.4读已提交（READ_COMMITTED）

读已提交是不同的事务执⾏的时候只能获取到已经提交的数据。 这样就不会出现上⾯的脏读的情况了。
但是在同⼀个事务中执⾏同⼀个读取,结果不⼀致
不可重复读示例
可是解决了脏读问题，但是还是解决不了可重复读问题。

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

7.5.5可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理过程中，多次读取同⼀个数据时，该数据的值和事务开始时刻是⼀致的。
因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。
幻读
幻读就是指同样的事务操作，在前后两个时间段内执⾏对同⼀个数据项的读取，可能出现不⼀致的结
果。
诡异的更新事件

7.5.6顺序读（SERIALIZABLE）

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

九、不同的隔离级别的锁的情况(了解)

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

十、隐式提交(了解)

DQL:查询语句
DML:写操作(添加,删除,修改)DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)
DCL:控制语⾔(给⽤户授权,或删除授权)
DDL（Data Defifine Language）：都是隐式提交。
隐式提交：执⾏这种语句相当于执⾏commit; DDL
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

十一、MySQL中的特性-扩展

11.1MySQL存储过程

储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。
类似java里的相同函数，用的时候直接调用。
优点:
（1）可以把⼀些复杂的sql进⾏封装,简化复杂操作
（2）保证了数据的完整性,防⽌错误
（3）简单的变动只需要更改存储过程的代码即可
（4）提⾼性能。因为使⽤存储过程⽐使⽤单独的SQL语句要快。(预先编译)
缺点:
（1）存储过程的编写⽐SQL语句复杂
（2）⼀般可能还没有创建存储过程的权限,只能调⽤

11.1.1创建存储过程

\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;
//

11.1.2执⾏储存

call p1()

11.1.3查看存储过程

show create procedure p1\G

11.1.4删除存储过程

drop procedure p1

11.2MySQL的触发器

触发器是MySQL响应写操作(增、删、改)⽽⾃动执⾏的⼀条或⼀组定义在BEGIN和END之间的
MySQL语句

11.2.1触发器语法：

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;

11.2.2触发器Demo

-- 创建⼀个删除的触发器,在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：
（1）如果触发器中SQL有语法错误,那么整个操作都会报错
（2）在INSERT触发器代码内，可引⽤⼀个名为NEW的虚拟表，访问被 插⼊的⾏;
（3）在DELETE触发器代码内，可以引⽤⼀个名为OLD的虚拟表，访问被删除的⾏;
（4）OLD中的值全都是只读的，不能更新。
（5）在AFTER DELETE的触发器中⽆法获取OLD虚拟表
（6）在UPDATE触发器代码中
（7）可以引⽤⼀个名为OLD的虚拟表访问更新以前的值
(8)可以引⽤⼀个名为NEW的虚拟表访问新 更新的值;

11.3MySQL中的视图

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。
如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

11.3.1视图的作⽤

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

   11.3.2视图的基础语法

   ```java 创建视图: 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;

<a name="7OnQe"></a>
## 十二、MySQL索引
跟书籍的目录索引一样。<br />索引（index）是帮助MySQL高效获取数据的数据结构（有序）。<br />MySQL按逻辑来分主要有以下几种索引类型：<br />（1）普通索引：仅加速查询<br />（2）唯一索引：加速查询 + 列值唯一（可以有null）<br />（3）主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个<br />（4）组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并<br />（5）全文索引：对文本的内容进行分词，进行搜索
<a name="d0mRM"></a>
### 12.1语句
```java
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

12.2普通索引

是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))

（4）删除索引

DROP INDEX index_name ON table

12.3唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

12.4主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

12.5组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

12.6全文索引

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

–创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
–修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
–直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

12.7聚簇索引和非聚簇索引

通过索引的键是否是主键来区分，是主键的是聚簇索引，其他都是辅助索引。

每个InnoDB表都需要一个聚簇索引。该聚簇索引可以帮助表优化增删改查操作。
如果你为表定义了一个主键，MySQL将使用主键作为聚簇索引。
如果你不为表指定一个主键，MySQL讲索第一个组成列都not null的唯一索引作为聚簇索引。
如果InnoBD表没有主键且没有适合的唯一索引（没有构成该唯一索引的所有列都NOT NULL），MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。
因此每个InnoDB表都有且仅有一个聚簇索引。

我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶⼦节点中已经把所有完整的⽤户记录都包含了，⽽MyISAM的索引⽅案虽然也使⽤树形结构，但
是却将索引和数据分开存储：

MyISAM会单独为表的主键创建⼀个索引，只不过在索引的叶⼦节点中存储的不是完整的数据记录，⽽是主键值 + ⾏号的组合。也就是先通过索引找到对应的⾏号，再通过
⾏号去找对应的记录！其它⾮主键索引也是⼀样的，这种情况我们称为’回⾏’。所以在MyISAM中所有的索引都是⾮聚簇索引，也叫⼆级索引
从辅助索引树到主键索引树的过程叫做“回表”。

十三、慢查询和sql优化

慢查询（慢查询⽇志）可⽤于查找需要很⻓时间才能执⾏的查询，因此是优化的候选者。

1.慢查询配置

2.创建表插入测试数据

3.通过explainln来查看某个语句的具体执行计划

字段分析：
id
在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问⽅法
possible_keys
可能⽤到的索引
key 实际上使⽤的索引
key_len
实际使⽤到的索引⻓度
ref 当使⽤索引列等值查询时，与索引列进⾏等值匹配的对象信息
rows 预估的需要读取的记录条数
fifiltered
某个表经过搜索条件过滤后剩余记录条数的百分⽐
Extra ⼀些额外的信息

4.给name字段添加普通索引

发现上面图片比较添加索引后数据检索速度大弧度提升，虽然添加索引会明显提高数据检索的效率，但每建立一个索引都会建立一个B+数，这是很费性能和存储空间的，所以不要随便添加索引。

5.应该用那些建立索引

（1）创建并使用自增数字来建立主键索引
（2）经常作为where条件的字段d建立索引
（3）添加索引的字段尽可能保持唯一性
（4）考虑联合索引并进行索引覆盖

联合索引的索引覆盖 (多个字段组合成了⼀个联合索引,在查询时,所要的字段和查询条件中的索引是⼀致)
注意索引绝不是加的越多越好(1.索引会占空间. 2.索引会影响写⼊性能)

6.索引使用注意事项

如下在索引上进行了运算，导致进行了全表扫描

当查询条件左右两侧类型不匹配的时候会发⽣隐式转换，隐式转换带来的影响就是可能导致索引失效⽽进⾏全表扫描。

通配符不能再头部使用

事实上，MySQL 只能使⽤⼀个单列索引。这样既浪费了空间，⼜没有提⾼性能（因为需要回⾏）
为了提⾼性能，可以使⽤复合索引保证列都被索引覆盖。

总结：

1.sql语句的优化
（1）避免子查询
（2）避免多表查询
2.索引优化
（1）适当建立索引
（2）合理使用索引