一、常见技巧与优化
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