SQL语句的高级操作

作者:神秘网友 发布时间:2020-09-09 11:13:27

SQL语句的高级操作

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
    • 2.分组查询---having
    • 3.分组查询---排序
    • 4.分组查询---限制数据显示数量
    • 5.分组查询---case when...then...end
    • 6.分组查询---内连接、左连接、右连接
    • 课后练习



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

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

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

SQL语句的高级操作

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

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

SQL语句的高级操作

简而言之,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 ;

SQL语句的高级操作

查询某个班的平均成绩后,要对平均成绩进行排序,用到的命令: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;

SQL语句的高级操作

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

因为实际工作中数据库中的表中有大量的数据,有一些数据我们不使用,但是查询的时候全部显示需要浪费大量的时间与资源,我们可以使用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;

SQL语句的高级操作

查询数据,使年龄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;

SQL语句的高级操作

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

SQL语句的高级操作
1.计算所有学生的英语成绩平均分 :

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

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

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

SQL语句的高级操作
3.查询所有学生的姓名,计算机成绩,按照计算机成绩从高到低排序

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

SQL语句的高级操作
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;

SQL语句的高级操作

SQL语句的高级操作相关教程

  1. 关于mysql中对数据先统计再排序的操作
  2. sql server 触发器
  3. PostgreSQL 逻辑结构 和 权限体系 介绍
  4. 《UNIX环境高级编程》第9章 进程关系
  5. 学生表sql语句练习
  6. 编译原理个人总结
  7. 数据库:SQLServer中in和 exists函数用法笔记
  8. 浅析MySQL存储引擎序列属性