SQL高级查询学习笔记

SQL高级查询： 函数查询， 分组查询， 多表查询， 子查询

一、函数查询

1.1 聚合函数

对一组值进行计算，得到一个返回值

SUM(),求和

AVG(),求平均

MIN()，MAX(),求最小值、最大值

COUNT()，计数

--求所有学生的平均年龄
SELECT AVG(sage) FROM student

--为查询结果起别名
SELECT AVG(sage) AS 平均年龄 FROM student

--求所有学生年龄的最大值、最小值和平均值以及学生人数
SELECT MAX(sage) AS 最大年龄, MIN(sage) AS 最小年龄, AVG(sage) AS 平均年龄, COUNT(sage) AS 总人数
FROM student

--求航天班所有学生年龄的最大值、最小值和平均值以及学生人数
SELECT MAX(sage) AS 最大年龄, MIN(sage) AS 最小年龄, AVG(sage) AS 平均年龄, COUNT(*) AS 总人数
FROM student
WHERE sclass='航天'

1.2 类型转换函数

CONVERT(datatype,expression,[date_style])

datatype:目标类型

expression：被转换对象

[date_style]：datatype是日期型(date)时需要设定

--将整数120转换为字符串
SELECT CONVERT(varchar,120)

--将字符串转换为实数
SELECT CONVERT(decimal(7,5),'3.1415')

--字符串转日期
SELECT CONVERT(date,'2018-01-10')

--日期转换为字符串
SELECT CONVERT(char,GETDATE())

--日期转yy.mm.dd型字符串
SELECT CONVERT(char,GETDATE(),102)
--日期转yy/mm/dd型字符串
SELECT CONVERT(char,GETDATE(),111)

日期转yy.mm.dd型字符串，date_style：102

日期转yy/mm/dd型字符串，date_style：111

1.3 日期型函数

GETDATE():获取当前日期和时间

--获取当前日期
SELECT GETDATE()

DATEADD(part,number,date): 给日期增加年、月、日或时间

part:要增加日期的哪一部分(year,month,day,hour,minute,second)

number：增加的数量；date:初始日期和时间

--日期在年份上加5年
SELECT DATEADD(year,5,'2012-5-31 10:10:10')

--日期在月份上加5月
SELECT DATEADD(month,5,'2012-5-31 10:10:10')

--日期在时间上加5小时
SELECT DATEADD(hour,5,'2012-5-31 10:10:10')

--为学生的出生日期增加2年(不会改变原始表中的数据)
SELECT DATEADD(year,2,sbirthday)
FROM student

DATEDIFF(part,date1,date2)：date2和date1在part上相减

part: year,month,day,hour,minute,second

--计算学生年龄
SELECT DATEDIFF(year,sbirthday,GETDATE()) AS age
FROM student 

DATENAME(part,date)： 获得date的part部分，以字符串返回，part部分含义同上

--获得当前日期的年份
SELECT DATENAME(year,GETDATE())

1.4 常用的数学函数

函数	说明
pi	返回π的值
sin	返回正弦值
cos	返回余弦值
exp	返回e的指定次幂
log	返回以e为底的自然对数
log10	返回以10为底的对数
sqrt	返回平方根
rand	返回0到1之间的随机数

1.5 常用的字符串处理函数

函数	说明
left(字符串，整数)	取左边部分字符
right(字符串，整数)	取右边部分字符
lower(字符串)	大写转小写
upper(字符串)	小写转大写
ltrim(字符串)	删除开头空格
rtrim(字符串)	删除末尾空格
reverse(字符串)	反转字符串
substring(字符串,开始位置,长度)	返回子串

二、分组查询 GROUP BY

--语法格式
SELECT <字段名表1> FEOM <表名>
[WHERE <条件>]
GROUP BY <字段名表2>
[HAVING <条件>]
[ORDER BY <字段名表3>]

注：

HAVING 子句表示对分组查询的结果再按照条件进行筛选；

<字段名表1>应是按照<字段名表2>分组后能够产生唯一值的字段

----查询各班学生年龄的最大值、最小值和平均值以及学生人数
SELECT sclass,MAX(sage) AS 最大年龄, MIN(sage) AS 最小年龄, AVG(sage) AS 平均年龄, COUNT(*) AS 总人数
FROM student
GROUP BY sclass

----查询各班学生年龄的最大值、最小值和平均值以及学生人数,并按平均年龄升序排列
SELECT sclass,MAX(sage) AS 最大年龄, MIN(sage) AS 最小年龄, AVG(sage) AS 平均年龄, COUNT(*) AS 总人数
FROM student
GROUP BY sclass
ORDER BY 平均年龄

--查询各班男生和女生人数
SELECT sclass,sgender,COUNT(*) AS 人数
FROM student
GROUP BY sclass,sgender
ORDER BY sclass

--查询各班张姓学生人数
SELECT sclass,COUNT(*) AS 张姓人数
FROM student
WHERE sname LIKE('张%')
GROUP BY sclass 

--查询姓'张'的学生人数大于1的班级及姓张的人数
SELECT sclass,COUNT(*) AS 张姓人数
FROM student
WHERE sname LIKE('张%') 
GROUP BY sclass
HAVING COUNT(*) > 1 

--查询各班来自不同省份的学生人数，并按班级降序排序
SELECT sclass,province,COUNT(*) AS 人数
FROM student
GROUP BY sclass,province 
ORDER BY sclass DESC

三、多表查询

3.1 笛卡尔乘积

--简单格式
SELECT <字段列表> FROM <数据表列表>

笛卡尔乘积：无条件的连接，无规则连接。若数据表1具有m条记录，数据表2具有n条记录，则笛卡尔乘积查询会产生m×n条记录。此种查询会产生无意义的记录

3.2 等值连接

--一般格式
SELECT <字段列表> FROM <数据表列表>
WHERE <等值条件>

--查询选课学生的学号、姓名、课程号和成绩等信息
SELECT * FROM score,student
WHERE student.numb = score.numb

3.3 多表等值连接

SELECT * 
FROM score,student,course
WHERE score.numb = student.numb
AND score.cnumb = course.cnumb

--添加其他条件，排序
SELECT * 
FROM score,student,course
WHERE score.numb = student.numb
AND score.cnumb = course.cnumb
AND student.name = '王宁'
ORDER BY course.cname, score.score

3.4 使用表的别名

SELECT * 
FROM student AS a,score AS b,course AS c
WHERE b.numb = a.numb
AND b.cnumb = c.cnumb
AND a.name = '王宁'
ORDER BY c.cname, b.score

3.5 去掉重复列

自然连接：去掉重复列的等值连接

SELECT a.numb, a.name, c.name, b.score 
FROM student AS a,score AS b,course AS c
WHERE b.numb = a.numb
AND b.cnumb = c.cnumb
AND a.name = '王宁'
ORDER BY c.cname, b.score

3.6 不等值连接

假设有两个表：成绩表score ; 等级表 grade

成绩表grade

numb	cnum	score
001	c01	90
002	c02	80

等级表grade

gstart	gend	grade
90	100	A
80	89	B
70	79	C
60	69	D
0	59	E

--将百分制分数划分为相应的等级
SELECT *
FROM score,grade
WHERE score.score BETWEEN grade.gstart AND grade.gend

3.7 内连接，使用INNER JOIN

--一般格式
SELECT <字段列表>
FROM 表1
INNER JOIN 表2 ON 连接规则1
INNER JOIN 表3 ON 连接规则2
...
[WHERE 条件]
[ORDER BY 字段列表]

--与WHERE子句等价的等值连接
SELECT * 
FROM score
INNER JOIN student ON student.numb = score.numb

SELECT * 
FROM score
INNER JOIN student ON score.numb = student.numb
INNER JOIN course ON score.cnumb = course.cnumb
WHERE student.name = '王宁'
ORDER BY course.cname, score.score

3.8 外连接 OUTER JOIN

内连接：只能显示出数据表之间相互匹配的记录
外连接：可以同时显示出数据表之间的不匹配项

假设有两个表：成绩表score ; 学生表 stu1

成绩表grade

numb	cnum	score
001	c01	90
002	c02	80

学生表 stu1

numb	name	gender
001	王宁	男
002	孙丽	女
003	李宏	男

左外连接，LEFT OUTER JOIN：查找左边表的不匹配项

--一般格式
SELECT <*|字段列表>
FROM 表1
LEFT OUTER JOIN 表2 ON 条件

上述查询可以显示出表1中与表2不匹配的记录

右外连接，RIGHT OUTER JOIN：查找右边表的不匹配项

--一般格式
SELECT <*|字段列表>
FROM 表1
RIGHT OUTER JOIN 表2 ON 条件

上述查询可以显示出表2中与表1不匹配的记录

SELECT *
FROM grade
INNER JOIN stu1 ON stu1.numb = grade.numb

上述查询只能显示出学号为001，002的学生信息和成绩

SELECT *
FROM grade
RIGHT OUTER JOIN stu1 ON stu1.numb = grade.numb

上述查询可以同时显示出学号为003号的学生相关信息，只是成绩信息为NULL

四、子查询(嵌套查询)

4.1 使用 IN 关键字

--查询班级人数不足5人的班级中学生的学号、姓名和班级
--班级人数不足5人的班级
SELECT sclass
FROM student
GROUP BY sclass
HAVING count(*)<5
--显示这些班级的学生信息
SELECT snumb,sname,sclass
FROM student
WHERE sclass IN(
SELECT sclass
FROM student
GROUP BY sclass
HAVING count(*)<5
 )

4.2 使用 EXISTS 关键字

 --如果"物理"班有人的成绩大于等于90，就把全班人的学号、姓名、班级、课程号、成绩显示出来
 --查询"物理"班成绩大于等于90的学生信息
 SELECT *
 FROM student
 INNER JOIN score ON score.snumb = student.snumb
 WHERE student.sclass = '物理' AND score.score >= 90
 --这个结果是有的，那么就要显示全班人的信息
 SELECT student.snumb,sname,sclass,cnumb,score
 FROM student
 INNER JOIN score ON score.snumb = student.snumb
 WHERE student.sclass = '物理' AND
 EXISTS (
 SELECT *
 FROM student
 INNER JOIN score ON score.snumb = student.snumb
 WHERE student.sclass = '物理' AND score.score >= 90
 )

4.3 比较运算

--显示航天班成绩在平均成绩以上的学生的班级、学号、姓名、课程号和成绩
 --所有参加考试的学生的所有课程的平均成绩
 SELECT AVG(score) AS 平均成绩
 FROM study
 --平均成绩以上的人的信息
 SELECT sclass,student.snumb,sname,cnumb,score
 FROM student
 INNER JOIN study ON study.snumb = student.snumb
 WHERE study.score > ANY(
 SELECT AVG(score) AS 平均成绩
 FROM study
 ) AND student.sclass = '航天'

4.4 子查询在FROM子句中

 --查询学生的班级数
 --首先查询学生来自哪些不同的班级
 SELECT DISTINCT sclass
 FROM student
 --统计班级的个数
 SELECT COUNT(*) AS 班级数
 FROM (
 SELECT DISTINCT sclass
 FROM student
 ) AS a

关键字 DISTINCT 用于去除重复行

参考资料：SQL Server数据库技术(Coursera)