1, 利用case when then end 进行数据分类统计。
用法1:select case when type=1 then '数学' when type=2 then '英语' when type=3 then '语文' else '其他' end askemu,count(*) from`kechengbiao` group by when type=1then '数学' when type=2 then '英语' when type=3 then '语文' else '其他'end
用法2:select case type when 1 then '数学' when 2 then '英语' when 3 then '语文' else '其他' end as kemu,count(*) from `kechengbiao` group by case type when 1 then '数学' when 2 then '英语' when 3 then '语文' else '其他' end
2, 利用 union all 同时查询多个表把查出的数据当成一个表再按照需求查询,一般用于排序
需求:表user1与表user2都有一个字段 time 获取两个表最新注册的10条数据
select * from ( select id,name,age,time,1 as type from user1 union all select id name age,time 2 from user2 ) as tmp order by time
desc limit 10
注:user1与user2查询的字段数目要一样,即使查询的两个表的字段不一样,可以给我两个表加一个标识字段例如上sql中的type,查询出来的结果中有type,值为1的就是user1的数据,值为2就是user2的数据。
3,利用 case when then else end 进行排序
需求:表user中的字段state值为1表示未开始,2表示进行中,3表示结束。要查询出数据要求进行中的数据靠前,未开始的再中间,结束的数据最后,其余按照时间排序
select id,name,time,case when state=1 then 11 when state=2 then 10 when state=3 then 12 else 13 end as sor from user order by sor asc,time desc
4,利用concat来比较两个相同字段表的不同数据
表a,表b有相同的字段time,uid,from_uid,is_read 但是现在需要找出两个表这些字段不同的数据
思路:把这么多字段可以当成一个字段来看待,利用concat连接把字段数据拼在一起取一个别名m。
注:由于m不是表中的字段不能出现在where中做条件,可以在having中做条件
select concat(time,uid,type,from_uid,is_read) as m from hao_wodehuifu HAVING m not in(select concat(time,uid,type,from_uid,is_read) as m from heixionghao_wodehuifu )
5,利用group by来查询一个表中不同的数据
表A中有age,sex,kecheng这三个字段现在需要统计在年龄,性别,课程上不同的人数
select count(*) from A group by age,sex,kecheng
6,当需要复杂的分类查询的时候,可以用case when then else end 配合group by来进行发咋的分组查询,但是如果查询需要连表查询的时候分组查询很慢,我们可以用union all来代替分组
例子1:select case when (u.time>100 and u.time<200) and (r.time>233 and r.time<500) then 'a'
when (u.time>333 and u.time<666) and (r.time>123 and r.time<359) then 'b' else 'c' end as `type`, count(distinct u.uid) from A as u inner join B as r on u.uid=r.uid group bywhen (u.time>100 and u.time<200) and (r.time>233 and r.time<500) then 'a'
when (u.time>333 and u.time<666) and (r.time>123 and r.time<359) then 'b' else 'c' end
上述例子可以改为:
例子2:select count(distinct uid) from A where time>100 and time<200 and uid in(
select uid from B where time>233 and time<500)
union all
select count(distinct uid) from A where time>333 and time<666and uid in(
select uid from B where time>123and time<359)
例子1与例子2的查询结果一样但是比例子2的慢很多
7,在一些条件下可以使用子查询来代替连表查询会更快
当一个表与自身连接的时候,我们可以使用子查询来代替内联查询会更快
例子1:select a1.* from A as a1 inner join A as a2 ON a1.uid=a2.uid where a1.time>222 and a1.time<333 and a2.time>666 and a2.time<888;
这个例子可以改写为:
例子2:select * from A where time>222 and time<333 and uid in(select uid from A where time>666 and time<888);
例子1与例子2查询的结果都是一样的