MySQL数据库查询练习（二）

MySQL数据库查询练习

练习01（行转列）：

行转列：

有一张学生成绩表sc（sno学号，clazz课程，score成绩），需要查询出每个学生的语文，数学，英语成绩（行转列，一个学生只要一行记录）。

查询得到如下结果：

方法一：

-- 行转列
select distinct a.sno, 
(select score from sc b where a.sno = b.sno and b.clazz = '语文') as '语文',
(select score from sc b where a.sno = b.sno and b.clazz = '数学') as '数学',
(select score from sc b where a.sno = b.sno and b.clazz = '英语') as '英语' from sc a;

方法二：

-- 行转列 用max()
select sno,
max(case clazz when '语文' then score end) '语文',
max(case clazz when '数学' then score end) '数学',
max(case clazz when '英语' then score end) '英语' 
from sc group by sno;

这里再加一个条件：汇总每个学生的总分，为了适应特殊情况，我将原表进行了改动：

得到如下结果：

-- 加一个条件进行汇总每个学生的总分 sum()
select sno,
sum(case clazz when '语文' then score else 0 end)'语文',
sum(case clazz when '数学' then score else 0 end)'数学',
sum(case clazz when '英语' then score else 0 end)'英语',
sum(score)'总分' 
from sc group by sno;

练习02（字符串函数）：

给一张楼号和门牌号的销售表，查询楼号和单元号：

运用字符串函数进行查询：

-- 查询
select * from sales;
-- 检索-的位置
select locate('-',snum) from sales;
-- 查询每个的楼号
select snum,left(snum,locate('-',snum) - 1) from sales;
-- 查询每个门牌号
-- 这里注意，一个字符串长度的问题 上方left可以，但是如果用right也是一样的函数方法就行出现错误
select snum,right(snum,locate('-',snum) + 1) from sales;-- 错误！！！因为这里会出现漏掉或者多出符号的情况

如下会有少一个字符或者多一个‘-’符号的情况，因为这里right是从最后面开始算，所以会进行多算或者少算是情况；

所以正确的是，这里有个规律：

-- 所以这里有个规律 8-502：【总长度-2】   8-1202：【总长度-2】  12-502：【总长度-3】
select snum,right(snum,length(snum) - locate('-',snum)) from sales;
select snum,convert(right(snum,length(snum)-locate('-',snum)),signed) from sales;
-- 也可以使用字段切割，把符号‘-’后面的门牌号切割出来
-- substr(str,pos) 或者 substr(str from pos)：从pos开始的位置，一直截取到最后。
-- 还有一种比较常用的是：
-- substr(str,pos,len) 或者 substr(str from pos len);从pos开始的位置，截取len个字符(空白也算字符)。
select snum,convert(substr(snum,locate('-',snum)+1),signed) from sales;

-- 查询后按照楼号排序
select * from sales order by convert(LEFT(snum,locate('-',snum)-1),signed);
-- 楼号从小到大，在按照门牌号从大到小排序
select * from sales order by convert(LEFT(snum,locate('-',snum)-1),signed ),
convert( substr(snum,locate('-',snum)+1),signed)  desc;