SQL基础花式查询

27道SQL题：https://blog.csdn.net/Zhang_Yixuan_ss/article/details/80327936
SQL在线测验工具：http://sqlfiddle.com/#!9/a6c585/1

27道SQL题包含了SQL查询的基本知识，可以根据以下建表语句在以上给出SQL测验工具网站建表练习
主要用到的3个表
学生表：student: sno,sname,sex,sage,sdept
课程表：course:cno,cname,hours
选课表：sc:sno,cno,grade

建表语句选择SQL SERVER

-- borrowed from https://stackoverflow.com/q/7745609/808921
CREATE TABLE SC
    ([Sno] int, [Cno] varchar(3), [Grade] int)
;
    
INSERT INTO SC
    ([Sno], [Cno], [Grade])
VALUES
    (951201, 'C01', 40),
    (951201, 'C02', 70),
    (951201, 'C03', 90),
    (951204, 'C04', 85),
    (951204, 'C01', 70),
    (951205, 'C02', 65),
    (951205, 'C01', 44),
    (951205, 'C03', 99),
    (951205, 'C04', 77)
;


CREATE TABLE student
    ([Sno] int, [Sname] varchar(1), [Ssex] varchar(1), [Sage] int, [Sdept] varchar(3))
;
    
INSERT INTO student
    ([Sno], [Sname], [Ssex], [Sage], [Sdept])
VALUES
    (951201, 'A', 'M', 19, '计算机'),
    (951202, 'B', 'F', 20, '计算机'),
    (951203, 'C', 'M', 20, '计算机'),
    (951204, 'D', 'M', 22, '数学'),
    (951205, 'E', 'F', 21, '数学'),
    (951206, 'F', 'F', 18, '信息'),
    (951207, 'G', 'F', 19, '信息'),
    (951208, 'H', 'F', 20, '信息')
;


CREATE TABLE course
    ([Cno] varchar(3), [Cname] varchar(4), [Hours] int)
;
    
INSERT INTO course
    ([Cno], [Cname], [Hours])
VALUES
    ('C01', '计算机', 70),
    ('C02', '线性规划', 80),
    ('C03', '运筹学', 80),
    ('C04', '管理学', 70),
    ('C05', '科学', 90),
    ('C06', '人文', 60)
;

涉及知识点

• 子查询
  第4题：查询学生都选修了哪些课程,要求列出课程号

---

• 分组查询：gruop by

1、可按多个字段分组

group by creat_time,type  --按时间和类型分组

2、分组后取组内最大值的两种方法
注意group by后只能查一个统计的结果，也就是每一组得到一个唯一的值，所以SELECT后面没有用聚合函数的字段一定是GROUP BY 后面的字段
比如下面的select后的字段只有Cname没有用聚合函数，则是说明该字段是group by的分组根据

--统计每门课程的修课人数和考试最高分
SELECT Cname 课程名,COUNT(*) 修课人数,MAX(Grade) 考试最高分
FROM SC,course
WHERE course.Cno=SC.Cno
GROUP BY course.Cname

3、分组后取topn,这里取top10

select * from 
(select ROW_NUMBER() over (partition by Cno order by Grade desc) as rowId,* from SC) as result 
where result.rowId>10

/*
注意这里的ROW_NUMBER()是sql Server的写法，其余例子是mysql
select ROW_NUMBER() over (partition by Cno order by Grade desc) as rowId,* from SC
表示按课程分组后按成绩降序排列，并新增一列rowId表示每组的顺序，
所以以下代码是选出每门课程的最高成绩对应的记录
*/
select * from 
(select ROW_NUMBER() over (partition by Cno order by Grade desc) as rowId,* from SC) as result 
where result.rowId=1

4、分组+排序的两种方法
11题.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。（分组后的结果排序）
先联合再统计

SELECT student.Sname 学生姓名,student.Sno 学生学号,COUNT(SC.Sno) 选课门数
FROM student 
inner join SC ON SC.Sno=student.Sno
GROUP BY student.Sname,student.Sno
ORDER BY COUNT(SC.Sno) ASC

或先统计再联合，这里select后的结果可以命一个别名发挥临时表的作用

SELECT Sname,修课人数 FROM
(SELECT Sno,COUNT(Cno) 修课人数 FROM SC GROUP BY Sno) AS result,student
WHERE result.Sno=student.Sno

5、分组+条件查询

SELECT Student.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数
FROM SC 
--内联接join或inner join,内联系是比较运算符，只返回符合条件的行
    JOIN Student ON (SC.Sno = Student.Sno)
    JOIN Course ON (SC.Cno = Course.Cno)
GROUP BY Student.Sname
HAVING COUNT(distinct Course.Cno) >2

---

• 一些奇奇怪怪的查询

1、自连接查询

自连接查询常用于一张表中，假如有两个以上的字段，且这些字段有一定的关系，我们又刚好想摸清这些关系字段的数据，最经典的例子则是车站站点

20.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

--做法1
SELECT  DISTINCT(a.Sno),a.Sname,a.Sage from student a, student b where a.sno!=b.sno and a.Sage=b.Sage
--做法2
SELECT A.Sname 年龄相同的学生的姓名,A.Sage 年龄
FROM student A 
    inner join student B ON A.Sage IN(SELECT Sage FROM student WHERE A.Sage=B.Sage AND A.Sname!=B.Sname)
GROUP BY A.Sname,A.Sage
ORDER BY A.Sage

2、查询奇数行

--my sql
SELECT * FROM
(SELECT @rownum:=@rownum+1 AS rownum, data.*
FROM (SELECT @rownum:=0) r, data) a WHERE a.rownum%2=1

3、查询指定记录数：第7到第9条记录

--sql server,mysql可以用limit
select top 3 id from tablename
where id not in (
select top 6 id from tablename
)

---

• 一些奇奇怪怪的操作

1、移动求和

--sql server
--前1行和当前行求和
SELECT
    id,da,
    SUM(da) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM data

--前1行和当前行和后一行求和
SELECT
    id,da,
    SUM(da) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 'RunningTotal'
FROM data

--从第一行开始求和，UNBOUNDED PRECEDING，同理直到最后一行是UNBOUNDED FOLLOWING
SELECT
    id,da,
    SUM(da) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS 'RunningTotal'
FROM data

2、行转列
实现

image.png

得到

image.png

静态查询

--sql server
SELECT 姓名,

 max(CASE 课程 WHEN'chinese' THEN 分数 ELSE 0 END) chinese,
 max(CASE 课程 WHEN'math' THEN 分数 ELSE 0 END) math,
 max(CASE 课程 WHEN'English' THEN 分数 ELSE 0 END) English

FROM tb
GROUP BY 姓名

动态查询，也就是可能不止两门课

--sql server
--声明一个字符串变量
declare @sql varchar(500)
set @sql='select name'
select @sql=@sql+',max(case class when '''+class+''' then score else 0 end)['+class+']'
from (select distinct class from tb) a
--不断拼接成一个完整的查询语句
set @sql=@sql+' from tb group by name'
--执行组合字符串
exec(@sql)

2、列转行

--SQL SERVER 2000静态SQL。

select * from
  (select name,class='chinese',score=chinese from tb
   union all
   select name,class='math',score=math from tb
   union all
   select name,class='English',score=English from tb) t
order by name,case class when 'chinese' then1 when 'math 'then 2 when 'English' then 3 end

动态SQL

declare @sql varchar(8000)

select @sql=isnull(@sql+' union all ','')+' select name, [class]='
+quotename(Name,'''')+' , [score] = '+quotename(Name)+' from tb'
from syscolumns
where Name!='name'and ID = object_id('tb')
order by colid

exec(@sql+' order by name')

---

• 表的差集以及各种连接

1、表的差集：bonus表-salary表

SELECT
    * FROM bonus
WHERE bonus.id not in 
(SELECT id from salary where id = bonus.id)
--也可以把bonus.id not in 换成 not exists

2、表的各种连接

JOIN Table_B B ON A.id = B.id;  --等值连接，不会删除连接表中的重复列
JOIN Table_B B WHERE A.id = B.id;  --自然连接，会删除连接表中的重复列，这里是id
--不等连接使用的是除等于号运算符以外的其它比较运算符，如>、>=、<=、<、!>、!<和<> 等
inner join或join --根据条件取两表交集，无条件则匹配出的结果为笛卡尔积
left join --左连接，以左表为根基匹配，左表右表都有匹配成功，左表有右表没有，对应连接的右表字段为null
right join --右连接，以右表为根基匹配
CROSS JOIN --交叉连接，返回笛卡尔积
/*在连接查询中，通常不用 WHERE 而是用 ON，因为 WHERE 没有 ON 效率高。ON 指匹配到第一条成功的就结束，其他不匹配；若没有，不进行匹配而 WHERE 会一直匹配，进行判断。
*/

image.png