mysql的技巧与优化

查找重复记录

商品表信息如下所示：

使用 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;

删除重复记录
使用 sql 语句删除商品名称重复的记录，只保留 id 最大的记录，代码如下所示：

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

选择随机记录

使用 sql 语句从 studentinfo 表中随机选择 1 名学生记录，代码如下所示：

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

使用 sql 语句从 studentinfo 表中随机选择 3 名学生记录，代码如下所示：

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

选择第 n 个最高记录
查询库存第 2 多的商品信息，代码如下所示：

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

分析：先按库存降序排列，查询库存最多的 2 个商品；再以前面查询结果按照升序排列，取第 1 个记录。当然这个过程也可以简化为以下代码：

select * from table_name
order by colunm_name desc
limit n-1,1 -- n-1 需要提前自己计算出具体数值

比较两个表的数据
现有两张表，请你查询出 A1 字段中，存在 t_a 表，但是不存在 t_b 表的数据，表数据如下图所示：

因为两张表数据量都比较少，我们通过观察不难发现，查询结果如下图所示

实现代码如下所示：

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

上面代码先通过子查询得到 t_b 表中 a1 字段的所有值，然后在外部查询中使用 not in 进行排除。这种方式本身没有错，但是在大量数据（百万级以上）的情况下，尤其是 A1 字段带有索引的情况下，查询速度会非常慢。
除了 not in 还可以使用 left join 实现，代码如下所示：

select t_a.* from t_a left join t_b
on t_a.A1 = t_b.A1
where t_b.B1 is null;

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

CREATE TABLE `TEST_TB_GRADE` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`USER_NAME` varchar(20) DEFAULT NULL,
`COURSE` varchar(20) DEFAULT NULL,
`SCORE` float DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 测试数据
insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE) values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 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;

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

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

all、any、some 查询
ALL 用在子查询前，通过比较运算符将一个表达式或列的值与子查询返回的一列值中的每一行进行比较，只要有一次比较的结果为 FALSE，则 ALL 测试返回 FALSE。
查询成绩比科目编号为“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);

SOME 与 ANY 的作用相同，自己尝试将代码中的 any 修改为 some 观察结果。
union 合并查询
有一张学生表 student 和一张教师表 teacher，表结构及测试数据如下所示：

要求查询所有师生的信息，代码如下所示：

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

代码中使用 union 将两个表合并到了一起，但是发现一个问题，学生表和老师表中都有一个 Jack 的记录，合并结果中却只有一个 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。
计算列
有一张 users 表，表结构及测试数据如下：

在存储用户信息时，姓和名是分开的，现在要求通过 sql 查询出用户信息，要求姓名用一个字段显示，代码如下：

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

计算列除了拼接字符串，还可以进行算术计算。
有一张 goods 表，表结构及测试数据如下：

price 字段表示单价，discount 字段表示折扣，要求查询出商品信息，并计算出商品的售价（即单价*折扣），代码如下：

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

排名查询
有一张 exam 表，表结构及测试数据如下：

要求查询出，科目编号为 2 的科目成绩及排名，代码如下：

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

代码中“(select @rownum:=0) b”的作用是给变量 rownum 赋值为 0。
从查询结果中看到，排名 3，4，5 的同学分数是一样的，排名却不一样，修改代码如下：

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;

上面的排名是没有问题的，但是也有一些情况下，要求当出现并列名次时，后面的同学进行跳跃排名，实现代码如下：

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;

分组统计
有一张
查询每个城市每个月份的点击量，代码如下：

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