SQL语句的高级操作

提示：学习本文前，首先要熟练的掌握数据库的单表查询，以及多表查询的基本语句，本文主要讲述软件测试需要具体掌握的SQL语句的高级操作。创建好三个表
①t_stuent(id，sname，age，cid，phone，address)
②t_class（id，cname，teacher）
③t_grade(id，sid，chinese,math,english)

---

---

1.分组查询—group by

数据库中的分组查询就是将数据库中的数据进行分组，更直观的显示某些数据，将同一类的归为一组，例如求某个班级中的平均数，就需要以班为单位进行分组，或者求某个班的男生女生数量就需要按照性别进行分组，然后统计人数

数据库中的分组查询命令是 group by
例如：有一张学生信息表，要统计这个班的男生女生的人数
就需要用到分组查询，以性别为条件进行分组
输如查询命令，就能够查询出男女生的人数，注意：分组要和聚合函数一起使用，不使用聚合函数时，只能显示分组条件的这个字段

SELECT
	sex as '性别',
	COUNT(*) as '个数'
FROM
	t_student
GROUP BY
	sex;

2.分组查询—having

查询某个表中重名的人，也就是说首先要按照名字进行分组，但是有些不重名的也会显示，此时就需要对分组后的数据再进行限制也就是 having命令

SELECT
	sname as  '姓名',
	COUNT(*) as '重名个数'
FROM
	t_student
GROUP BY
	sname
HAVING
	COUNT(*) > 2;

简而言之，having命令和where命令作用一样，但是where限制适用于分组之前的查询 而 having用于分组之后的查询

例题：查询某个班语文成绩大于100分的语文同学的成绩的语文平均分，并且只显示平均分高于100分的班级

注意group by 与having的使用

SELECT
	c.cname as '班级名称',
	AVG(g.chinese) as '平均分'
FROM
	t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid
WHERE
	g.chinese > 100
GROUP BY
	c.cname
having 平均分 >100 ;

3.分组查询—排序

查询某个班的平均成绩后，要对平均成绩进行排序，用到的命令：asc 和desc
例如将成绩从小到大排序

SELECT
	c.cname AS '班级名称',
	AVG(g.chinese) AS '平均分'
FROM
	t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid
WHERE
	g.chinese > 60
GROUP BY
	c.cname
ORDER BY
	平均分 ASC;

最后代码中的ASC 可以省略不写
成绩倒叙排列 就将最后代码改为 ORDER BY DESC;
desc不可省略，不区分大小写

4.分组查询—限制数据显示数量

因为实际工作中数据库中的表中有大量的数据，有一些数据我们不使用，但是查询的时候全部显示需要浪费大量的时间与资源，我们可以使用limit命令来控制显示的数据的多少
例如只显示两组平均分数据，代码最后加上 limit 0,2
0代表从哪个开始，注意计算机中的下标从0开始
2代表几条数据

SELECT
	c.cname AS '班级名称',
	AVG(g.chinese) AS '平均分'
FROM
	t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid
WHERE
	g.chinese > 60
GROUP BY
	c.cname
ORDER BY
	平均分 ASC
LIMIT 0,2;

5.分组查询—case when…then…end

查询数据，使年龄18一下的输出为青少年，18-30岁称为青年，大于30为老年，可以不显示实际年龄

SELECT
	s.sname '姓名',
	s.age '年龄',
	(
		CASE
		WHEN s.age < 18 THEN
			'青少年'
		WHEN s.age >= 18
		AND s.age <= 30 THEN
			'青年'
		ELSE
			'老年'
		END
	) AS '阶段'
FROM
	t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid;

6.分组查询—内连接、左连接、右连接

内连接就是我么之前学的join 全称为 inner join inner可以忽略不写
内连接就是取表之间的交集 (inner) join
左连接就是显示左边表的全部加上两表的交集 left join
右连接就是显示右边表的全部加上两表的交集 right join

课后练习

1.计算所有学生的英语成绩平均分 ：

SELECT
	AVG(mark.english) '英语平均分'
FROM
	mark;

2.现有学号为11、 22 、33、 44、 55 用一条SQL语句实现列出这五个学生的数学成绩及其姓名、学生地址和电话号码

SELECT
   mark.math '数学成绩',
   NAME '姓名',
   Address '地址',
   Telno '电话号码'
FROM
   mark
JOIN cust ON mark.studentno = cust.Studentno;

3.查询所有学生的姓名，计算机成绩，按照计算机成绩从高到低排序

SELECT
	cust.`Name` '姓名',
	mark.computer '计算机成绩'
FROM
	mark
JOIN cust ON mark.studentno = cust.Studentno
ORDER BY mark.computer DESC;

4.查询所有总成绩大于240分的学生学号，姓名、总成绩，按照总成绩从高到低排序

SELECT
	cust.Studentno '学号',
	cust.`Name` '姓名',
	SUM(
		mark.english + mark.math + mark.computer
	) score
FROM
	mark
JOIN cust ON mark.studentno = cust.Studentno
GROUP BY
	cust.Studentno
	HAVING score > 240
ORDER BY
	score DESC;