MySQL高级（五）

五.MySQL优化与技巧

1.查找重复行

1. 使用 sql 语句查询表中重复的商品记录

   select pname,count(pname) -- 查找的列
   from product -- 表
   group by pname -- 按name分组
   having count(pname)>1;-- having条件
   

2. 使用 sql 语句查询表中商品名称和数量都重复的记录

   select pname,num,count(*)-- 查找的列
   from product -- 表
   group by pname,num -- 按name与num分组
   having count(pname)>1 -- having条件
   and count(num)>1;
   

2.删除重复行

1. 使用 sql 语句删除商品名称重复的记录，只保留 id 最大的记录

   • 表结构（内容）

   classid	classname
   1	网开1
   2	网开2
   3	网开3
   4	网开1
   5	网开2
   6	网开2

   -- 先进行查询
   select * -- 查询所有（两张表数据来自一张表客观看成一张表）
   from classdb p1 -- 明名为p1
   inner join classdb p2 -- p2
   -- p1<p2 
   where p1.classid<p2.classid-- 满足条件的p1有(1,2,4,5) p2(2,4,5,6)
   and p1.classname=p2.classname;-- 满足条件网开1(1,4),网开2(2,5,6)
   

   查询结果

   CLASSID	CLASSNAME	CLASSID1	CLASSNAME2
   1	网开1	4	网开1
   2	网开2	5	网开2
   2	网开2	6	网开2
   5	网开2	6	网开2

   但删除时

   delete p1 -- 数据来自同一张表删除一条数据及消失
   from classdb p1 
   inner join classdb p2 
   where p1.classid<p2.classid 
   and p1.classname=p2.classname;
   -- 网开1(1,4)条件清晰可见删除最小的id及1
   -- 网开2(2,5,6)p1保留6没有比6大的及无法删除，同时p1(5)与p2(6)对比删除5同时p2的5消失(来自同一数据表)
   -- 当p1.pid>p2.pid时网开1(1,4)删除最大的id及1网开（2,5,6）p1(2,5) p2(5,6) 
   p1>p2删除比2,5大的数据但是数据来自同一张表及（自己打自己）删除5，6
   

3.选择随机数

​ 随机如：

select * from classdb -- 表
order by rand() -- 随机数据
limit 1;-- 取第一条改变 数值即可获取第几条

4.选择第 n 个最高记录

​ 1.第2多的信息

select * from 
(select * from classdb order by classid desc limit 2)-- 查询商品降序的两条信息
as t1 order by classid ASC limit 1;-- 将查询过来的信息升序排列取第一条

​ 2.第n个高信息

select * from 
table_name -- 表名称
order by -- 排序
colunm_name desc -- 按什么降序
limit n-1,1 -- n-1 需要提前自己计算出具体数值 
-- 第几个就n-1,然后取几条数据

5.比较两个表的数据

​ 1.表a中有表b中没有

a表

A1	A2
11	21
12	22
13	23
14	24

b表

B1	B2	A1
11	21	11
12	22	12
13	23	11
14	24	16

select * from a 
where A1 -- 对比A表中的A1字段
not in (select A1 from b)-- 查询b表中的A1判断a表A1是否有，没有的输出
-- 缺陷：数据多时运行时间长

​ 2.方法二(a表中没有16所以没有显示)

A1	A2	B1	B2	A1
11	21	11	21	11
12	22	12	22	12
11	21	13	23	13
13	23	null	null	null
14	24	null	null	null

select a1.* from -- 查询a表所有数据 
a1 left join b1 -- 链接b表
on a1.A1 = b1.A1 -- 判断a表A1是否等于b表A1
where b1.B1 is null;-- 因为b表不存在数据所以是空
-- 使用where不使用and where先查询后判断 and是一起进行

A1	A2
13	23
14	24

6.行转列

SELECT USER_NAME,
 -- 根据name进行匹配其他成绩
 max(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,-- 因为表中有三条数据进行三次赋值分别是 最大值,0,0
 MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,-- 因为表中有三条数据进行三次赋值分别是 最大值,0,0
 max(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语,-- 因为表中有三条数据进行三次赋值分别是 最大值,0,0
 sum(score) 总分 
FROM test_tb_grade GROUP BY USER_NAME;-- 按名字排序

7. exists查询

EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询判断它是否返回行，如果至少返回 一行，那么 EXISTS 的结果是 true，此时外层查询语句将进行查询；如果子查询没有返回任何行那么 EXISTS 返回的结果是 false，此时外层语句将不进行查询。

SELECT StudentID,Exam FROM EXAM WHERE SubjectID=2 AND -- 查询SubjectID=2和
EXISTS -- 关键词
(SELECT StudentID from EXAM WHERE Exam<60) -- EXAM表中StudentID>60的字段

8. all、any、some 查询

• ALL 用在子查询前，通过比较运算符将一个表达式或列的值与子查询返回的一列值中的每一行进行比较，只要有一次比较的结果为 FALSE，则 ALL 测试返回 FALSE。

• ANY 与子查询在一起使用时，按照比较运算符、表达式或字段对子查询的结果的每一行进行一次计算和比较。只要有一次满足条件，那么 ANY 的结果就是真。

• SOME 与 ANY 的作用相同

​ 1.查询比科目1的成绩大的其他科目成绩：

SELECT * FROM Exam WHERE Exam > ALL-- 其他科目成绩进行对比
(SELECT Exam from EXAM WHERE SubjectID=1);-- 科目1的所有成绩

​ 2.查询其他科目任意一个都大比科目1的成绩的考试信息如：>any(1,2,3) 及大于3

SELECT * FROM Exam WHERE Exam > ANY
(SELECT Exam from EXAM WHERE SubjectID=1);-- 查询科目1的所有成绩

9. union 合并查询

​ UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中并删除重复的数据

• union 联合的两个 select 必须拥有相同数量的列；
• 列必须有相似的数据类型；
• 列的顺序必须相同；
• union 因为要去重，效率远不如 union all。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a31CywFv-1615968630538)(D:\学习笔记\MySql高级\image-20210314124029416.png)]

select id,name,age from student 
union
select id,name,age from teacher;

结果1：union

结果2：union all

发现jack少了这是因为 union 在合并时会删除重复记录，相当于 distinct，如果不想去重，可以使用 union all。

-- 结果2
select id,name,age from student 
union all 
select id,name,age from teacher;

10.计算列

​ 有时候客户机应用程序或报告程序中重新格式化

1.拼接

users表

id	fiest_name	last_name
1	关	羽
2	张	飞
3	刘	备

select id,concat(first_name,last_name) as name from users; -- 关键词concat

2.计算

goods表

id	name	price	di’scount	stock
1	p 30	32000	0.8	10
2	mate 20	34000	0.9	10

select id,name,price*discount as sale_price,stock from goods;

11.排名查询

​ 1. 排名问题

select examid,studentid,exam,@rownum:=@rownum+1 
as rank from exam a,
(select @rownum:=0) b -- 1.赋值变量为0
where SubjectId=2 	  -- 2.现根据表，条件筛选
order by 			  -- 3.查询排序条件降序
a.exam desc;		  -- 4.查询赋值

​ 缺陷：当成绩一样时出现同样成绩名称不一样

​ 改进：

select examid,studentid,exam, 
CASE
when @prev = a.exam then @rownum 
when @prev := a.exam then @rownum := @rownum+1 
end as rank
from exam a,(select @rownum:=0,@prev:=null) b 
where SubjectId=2
order by a.exam desc;
-- 1.子查询@rownum初始人0 @pare判断条件成绩
-- 2.根据表，筛选
-- 3.查询排序条件降序
-- 4.排序时case判断 因为是降序when 条件
-- @prev = a.exam null！=成绩 不成立（进行下一个判断）
-- @prev := a.exam 当 null！=成绩时赋值给 @prev等于当前成绩 排名加一 反复

跳跃排名：（不做重点）

select examid,studentid,exam,rank from (
select examid,studentid,exam, 
@rownum:=if(@prev=a.exam,@rownum,@inc) as rank, 
@inc:=@inc+1, 
@prev:=a.exam from exam a,(
select @rownum:=0,@prev:=null,@inc:=1
) 
b where SubjectId=2 
order by a.exam desc
) tb;

12.分组统计

-- 按地区，月份，分组统计地区月点击量
select city_name,state_month,sum(sx_sum) -- 查询名字，月份，点赞量
FROM sx_target -- 表
GROUP BY city_name,state_month -- 按地区月份分组

​ 缺陷：计算全部总量

select city_name,state_month,sum(sx_sum)
from sx_target 
group by city_name,state_month
with ROLLUP

with rollup 用来在分组的基础上再进行统计，例如 group by a,b with rollup，先根据(a,b)统计，然后根据(a) 统计，然后根据(null)统计。