MySQL 技巧与优化

一、常见技巧与优化

1、查找重复记录

商品表信息如下所示：

pid	pname	NUM
1	P30	0
2	mate20	50
3	nova5	100
4	p30	10
5	p30	10

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

select pname,count(pname) 
from product 
group by pname 
having count(pname)>1;

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

select pname,num,count(*) 
from product 
group by pname,num 
having count(pname)>1 and count(num)>1;

2、删除重复记录

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

-- 删除商品名重复记录 
delete p1 from product p1 
inner join product p2 
where p1.pid<p2.pid and p1.pname=p2.pname; 
-- 查询商品表 
select * from product;

3、选择随机记录

有些业务需要从表中选择随机记录，比如：

• 在博客中选择一些随机的帖子，并在侧边栏中显示；

• 在店铺商品中选择一些随机的商品，并推荐到侧边导购栏；

• 在画廊中选择随机图片，并将其用作精选照片。

使用 sql 语句从 studentinfo 表中随机选择 1 名学生记录

select * from studentinfo
order by rand() 
limit 1;

使用 sql 语句从 studentinfo 表中随机选择 3 名学生记录

select * from studentinfo
order by rand()
limit 3;

4、选择第 n 个最高记录

查询库存第 2 多的商品信息

select * from
(select * from product
order by num desc
limit 2) as t1
order by num ASC
limit 1;

5、比较两个表的数据

现有两张表，请你查询出 A1 字段中，存在 t_a 表，但是不存在 t_b 表的数据

a1	A2		B1	B2	A1
A11	A21		B11	B21	A11
A12	A22		B12	B22	A12
A13	A23		B13	B23	A13
A14	A24		B14	B24	A14

select * from t_a where a1 not in
(select a1 from t_b)

6、行转列

现在有一张成绩表，表结构及数据如下所示：

id	USER_NAME	COURSE	SCORE
1	张三	数学	34
2	张三	语文	58
3	张三	英语	58
4	李四	数学	45
5	李四	语文	87
6	李四	英语	45
7	王五	数学	76
8	王五	语文	34
9	王五	英语	89

为了方便查看每个同学的成绩，使用 sql 语句查询

SELECT user_name ,
	MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
	MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
	MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语,
		sum(score) 总分
FROM test_tb_grade
GROUP BY USER_NAME;

7、exists 查询

查询成绩表中科目编号为 2 的考试成绩中是否存在不及格的学生，如果存在不及格的学生就将参加科目编号 2 考试的学生编号和成绩全部查询显示出来

SELECT StudentID,Exam FROM EXAM 
WHERE SubjectID=2 AND EXISTS (SELECT StudentID from EXAM WHEREExam<60)

8、all、any、some 查询

查询成绩比科目编号为“1”的这门课程的所有成绩都大的学生考试信息

SELECT * FROM Exam WHERE Exam > ALL(SELECT Exam from EXAM WHERE SubjectID=1);

查询成绩比科目编号为“1”的任意一个成绩都大的考试信息

SELECT * FROM Exam WHERE Exam > ANY(SELECT Exam from EXAM WHERE SubjectID=1);

9、union 合并查询

在 test 数据库中有一张学生表 student 和一张教师表 teacher，表结构及测试数据如下所示：

id	name	age		id	name	age
1	张三	18		1	lucy	26
2	李四	19		2	mary	30
3	王五	18		3	rose	25
4	jack	20		4	jack	20

要求查询所有师生的信息

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

合 并结果中却只有一个 jack 记录。这是因为 union 在合并时会删除重复记录，相当于 distinct，如果不想去重，可以 使用 union all。

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

**注意：**使用 union 查询有以下几个要点：

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

10、计算列

在 test 数据库中有一张 users 表，表结构及测试数据如下：

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

要求姓名用一个字段显示

select id,concat(first_name,last_name) as name from users;

在 test 数据库中有一张 goods 表，表结构及测试数据如下：

id	name	price	discount	stock
1	p30	3200	0.8	10
2	mate20	3400	0.9	20

price 字段表示单价，discount 字段表示折扣，要求查询出商品信息，并计算出商品的售价

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

11、排名查询

要求查询出，科目编号为 2 的科目成绩及排名 要求当出现并列名次时，后面的同学进行跳跃排名

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