mysql-07 数据查询

发表于 | 更新于 | 分类于 ， | 评论数： | 热度： ℃
mysql-07 数据查询

mysql-07 数据查询

数据查询是数据库的核心操作

常见格式

1
2
3
4
5
6
select all|distinct 目标表达式
from 表名|视图名
where 条件表达式
group by 列名1
having 条件表达式
order by 列名2 ASC|DESC

执行顺序

1）from

2）join

3）on

4）where

5）group by（按照后续条件分组）

6）聚集函数（avg，sum等）

7）having（按照后续条件筛选分组）

8）计算所有表达式

9）select

10）distinct（删除重复行）

11）order by（对结果排序）

注意

1）select 默认是all，如果想去掉结果表中的重复行，必须加上distinct。

2）where 子句常用的查询条件中，and优先级高于or；

3）where 子句常用的查询条件中，between and包括了上下限，例如[20,23]包括了20和23；

4）同样的，not between and结合上一条来理解，not [20,23]表示结果表里不会有20和23；

5）where 子句常用的查询条件中，!=，<>均表示不等于

1、where子句常用查询条件

查询条件 谓词
比较 =，>，<，>=，<=，!=，<>，!>，!<
确定范围 between and，not between and
确定集合 in，not in
字符匹配 like，not like
空值 is null，is not null
多重条件 and，or，not

举例

1
2
3
4
5
6
#查询20~23岁（包括20,23）的学生的姓名
select sname from student where sage between 20 and 23;
或者
select sname from student where sage in (20,21,22,23);
或者
select sname from student where sage=20 or sage=21 or sage=22 or sage=23;

PS：in谓词实际上是多个or运算的缩写

2、字符匹配查询

1
2
3
4
5
#语法
[not] like '匹配串' [escape '转码字符']

#匹配串
可以是完整字符串或者带通配符的字符串

1）通配符

  • % 代表任意长度（包括0），例如a%b可以是abc，abbbc，ab等。
  • _ （下横线）代表任意单个字符，例如a_b可以是abc，afc等。

2）转义

如果用户要查询的字符串本身就有通配符%和_，那么就需要使用escape进行转义换码了。

举例

1
2
3
4
5
6
7
查询以 DB_ 开头的且倒数第三个字符为i的课程的详细情况
select * from course
where cname like 'DB\_%i__' escape'\';

#解析
1）escape'\' 表示反斜杠\作为转义字符，紧跟在\之后的那一个字符将不再具有通配符含义，将被转义为普通字符；
2）匹配字符串'DB\_%i__'中，第一个下划线被转义为普通字符，%和i后面的两个_仍然作为通配符。

3、对结果排序

1）ASC（默认）：升序

2）DESC：降序

关于空值

空值可以按照假想的最大值来看，如下：

  • 升序时，含空值的行最后显示；

  • 降序时，含空值的行最先显示；

举例

1
2
3
4
5
#查询全体学生情况，查询结果按照系号升序排列，同一系学生按照年龄降序排列
select * from student
order by sdept,sage DESC;

PS：先按照系号排序，然后再分别针对每个系按年龄排序

4、聚集函数

函数 功能
count（*） 统计行数
count（[distinct\ all] 列名） 统计一列中值的个数
sum（[distinct\ all] 列名） 统计总和（必须是数值型）
avg（[distinct\ all] 列名） 统计平均值（必须是数值型）
max（[distinct\ all] 列名） 求最大值
min（[distinct\ all] 列名） 求最小值

1）distinct：取消重复值

2）all（默认）：不取消重复值

注意

1）当上述函数遇到空值，除了count（*）外，其他函数均跳过空值；因为该函数仅仅是统计行数而已，某行的部分列取空值不影响它统计行数。

2）where子句中不能用聚集函数作为条件表达式。聚集函数只用于select子句和group by中的having子句。

5、group by子句

　　group by子句将查询结果按照某一列或者多列的值分组，值相等的为一组；如果未对查询结果分组，聚集函数会作用于整个查询结果（即，将整个查询结果当成一个组），分组后聚集函数分别作用于每一个组，即每一个组有各自的函数值。

举例

1）查询选修了三门以上课程的学生的学号

1
2
3
select sno from sc
group by sno
having count(*)>3;

这里先按学号分组，每一组表示一个学生的选课情况，再用count（*）统计出每一组的行数（即选课数量）。

2）查询平均成绩大于等于90分的学生的学号

1
2
3
4
5
6
7
8
9
#错解
select sno from sc
where avg(grade)>=90
group by sno;

#正解
select sno from sc
group by sno
having avg(grade)>=90;

解析：where子句中不能用聚集函数作为条件表达式，那么，为什么不可以呢？因为sql关键字的执行顺序where是在gourp by之前，也就是说会在分组之前计算平均值，因此计算出来的平均值并不是某个学生的平均成绩，而是全体学生的平均成绩。所以，不可以在where子句中使用聚集函数，因为那个根本没有意义。

6、连接查询

　　若一个查询同时设计两个或以上的表，就需要用到连接查询。连接查询是关系数据库中最主要的查询，主要包括等值连接、非等值连接、自然连接、自身连接、外连接等。

1）等值连接（运算符为 = ）

2）非等值连接（运算符是除了 = 之外的其他运算符）

PS：连接谓词中的列名称为字段，连接条件中各个连接字段的名字不必相同，但是字段类型必须是可比的。如果同一个字段在查询的两个或多个表都出现了，则需要加上表名前缀；如果是唯一的字段，那么可以不必加表名前缀。

举例

1）等值连接查询

1
2
3
4
5
#查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
select student.sno,sname from student，sc
where student.sno=sc.sno
and sc.cno=2
and sc.grade>90;

2）自身连接查询

1
2
3
4
5
#查询每一门课的间接先修课，即先修课的先修课
#要查询同一个表两次，因此要为course表定义两个别名：first、second
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno;

注意：上述语句逻辑关系是，1表的cpno等于2表的cno，然后select出2表对应的cpno，就完成了从1表的cpno到2表的cpno的过程，查询到了先修课的先修课。

3）自然连接

悬浮元组：两个表做自然连接时，互相之间不存在公共属性值上相等的某些元组会被舍弃，这些被舍弃的元组称为悬浮元组，如上图中，R的第四个元组和S的第五个元组皆是被舍弃的悬浮元组。

4）外连接查询

外连接（outer join）：如果把悬浮元组也保存在关系（表）中，而在其他属性上填上空值null，则称作外连接；

左外连接（left outer join或left joi）：只保留左边关系R的悬浮元组；

右外连接（right outer join或right join）：只保留右边关系S的悬浮元组；

如下图：

外连接查询语句举例

1
2
3
4
5
6
7
8
#查询每个学生机器选修课程的情况
1）使用基本关系运算
select student.*,sc.* from student,sc
where student.sno=sc.sno;

2）使用左外连接查询
select student.sno,sname,ssex,sage,sdept,cno,grade
from student left outer join sc on (student.sno=sc.sno)

5）多表连接查询

1
2
3
4
#查询每个学生学号、姓名、选修课程名以及成绩（涉及三张表）
select student.sno,sname,cname,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno;

6）注意

　　八种常见数据库关系代数运算中，5中基本运算（并、差、笛卡尔积、选择、投影）可以用以表达另外三种运算（交、连接、除），这三种运算虽然可以简化表达，但是理解起来有点难度而且不太好记，建议掌握5钟基本运算即可。

7、嵌套查询（nested query）

1）查询块：一个select-from-where语句称为一个查询块；

2）嵌套查询：一个查询块嵌套在另一个查询块的where子句或者having短语条件中，称为嵌套查询；

举例

7.1、不相关子查询：子查询独立，不依赖于父查询（整个查询称为不相关嵌套查询）
1
2
3
4
5
6
7
8
9
10
11
12
13
#查询与“刘晨”在同一个系学习的学生的学号
select sno from student
where sdept in(
	select sdept from student
	where sname="刘晨");
	
#查询选修了课程名为“信息系统”的学生的学号和姓名
select sno,sname from student
where sno in(
	select sno from sc
	where cno in(
		select cno from course
		where cname="信息系统"))；
2）相关子查询：子查询不独立，需要依赖于父查询（整个查询称为相关嵌套查询）
1
2
3
4
5
6
7
#找出每个学生超过他自己选修课程平均成绩的课程号
select sno,cno 
from sc x
where grade>=(
	select avg(grade)
	from sc y
	where y.sno=x.sno);

　　上述语句中，x，y均为sc表的别名，又称元组变量，可以表示sc表的一个元组。内层查询查的是某个学生的平均成绩，那么”某个“具体指哪个，就需要由外层查询里x.sno来指定了，内外层相关联，故称为相关嵌套查询。

可以分步骤理解：

1
2
3
4
5
6
7
8
9
10
11
1）假设外层查询传入内层的学号是“201215121”，则执行内层查询
select avg(grade) 
from sc y
where y.sno='201215121';

2）假设上一步骤查询结果是88分，则执行外层查询
select sno,cno
from sc x
where grade>=88;

3）外层查询取出下一个元组，重复上述步骤，知道外层所有sc的元组都处理完毕。
3）any、all谓词子查询
1
2
3
4
5
> any 大于子查询结果中某个值
> all 大于子查询结果中所有值
!=（或<>） any 不等于子查询结果中的某个值
!=（或<>） all 不等于子查询结果中的任何一个值
其余描述省略

举例

1）查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
1
2
3
4
5
6
7
select sname,sage
from student
where sage<any(
	select sage
	from student
	where sdept='cs')
and sdept<>'cs';

2）查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄

1
把上例中的any改为all即可

4）exists子查询

　　exists子查询不返回任何数据，只返回逻辑值“true”或者“false”；若内层查询非空，则返回true；否则返回false；而not exists返回则刚好相反。

举例

1）查询选修了1号课程的学生姓名

1
2
3
4
5
6
select sname
from student
where exsits(
	select *
	from sc
	where sno=student.sno and cno='1');

2）查询没有选修1号课程的学生姓名

1
2
3
4
5
6
select sname
from student
where not exsits(
	select *
	from sc
	where sno=student.sno and cno='1');

8、集合查询

集合操作主要包括并（union）、交（intersect）、差（except）三种操作。

1）查询计算机科学系学生，以及年龄大于19岁的学生学号

1
2
3
4
5
select sno from student
where sdept='cs'
union
select sno from student
where sage>19;

2）查询计算机科学系中，年龄大于19岁的学生学号

1
2
3
4
5
select sno from student
where sdept='cs'
intersect
select sno from student
where sage>19;

3）查询计算机科学系中，年龄不大于19岁的学生学号

1
2
3
4
5
select sno from student
where sdept='cs'
except
select sno from student
where sage>19;

注意：除了上述方法，当然还可以在where中加入and等条件完成查询。

欢迎打赏，谢谢
------ 本文结束------
0%