首页 > 数据库 >（数据库系统概论|王珊）第三章关系数据库标准语言SQL-第四节：数据查询

（数据库系统概论|王珊）第三章关系数据库标准语言SQL-第四节：数据查询

时间：2023-02-15 09:35:24浏览次数：70  
标签：JOIN 王珊 查询 Sno 关系数据库 SQL Cno WHERE SELECT

★★★★★SELECT语句格式★★★★★

目录

数据库最核心的操作便是数据查询，SQL提供了SELECT语句实现该功能，其使用非常灵活而且有极其丰富的功能。格式如下

SELECT语句含义：根据WHERE子句的条件表达式从FROM子句指定的表、视图中找出满足条件的元组，再按照SELECT子句中的目标列表达式选出元组中的属性值形成结果表。如果有：

  • GROUP BY：结果按<列名1>的值进行分组，该属性列值相等的元组为一个组；通常会在每组中作用聚集函数；如果该子句还携带HAVING短语，则只有满足指定条件的组才予以输出
  • ORDER BY：结果表还要按<列名2>的值的升序或降序排序

在这里插入图片描述

一：单表查询（查询时只涉及一张表）

（1）选择表中的若干列

A：查询指定列

演示：

【例1】查询Student表中的学生及年龄

SELECT Sname,Sage from Student;

在这里插入图片描述

B：查询全部列

语法：

  • *作为通配符表示全部

演示：

【例2】查询Student表中全部列

SELECT * from Student;

在这里插入图片描述

C：查询经过计算的值

语法：SELECT子句的<目标列表达式>不仅可以是属性列，还可以是表达式，具体有

  • 算数表达式
  • 字符串常量
  • 函数
  • 列别名

①：算数表达式

演示：

【例3】根据年龄计算学生的出生日期

SELECT Sname,2022-Sage from Student;

在这里插入图片描述

②：字符串常量及函数

演示：

【例4】使用小写字母展示所在系别

SELECT Sname,LOWER(Sdept) from student;

在这里插入图片描述

③：使用列别名改变查询结果标题

演示：

【例5】查询Student表中的Sname和Sage，列标题起别名为“姓名”和年龄

SELECT Sname `姓名`,Sage `年龄` from student;

在这里插入图片描述

（2）选择表中的若干行（元组）

A：消除取值重复的行（DISTINCT）

语法：前面说过投影操作可能会导致相同的行出现所以其结果必须消除重复行。可以使用DISTINCT消除

演示：

【例6】：查询SC表的Sno列，然后消除重复学号

SELECT DISTINCT Sno from SC;

在这里插入图片描述

B：查询满足条件的元组

语法：通过WHERE子句实现，常用的查询条件如下
在这里插入图片描述

①：比较大小

演示：

【例7】在SC表中查询成绩大于85的同学的学号

SELECT Sno,Grade from SC
WHERE Grade > 85;

在这里插入图片描述

②：确定范围

演示：

【例8】查血年龄在闭区间[19,20]的学生

SELECT Sname,Sage from student
WHERE Sage BETWEEN 19 AND 20;

在这里插入图片描述
【例9】查血年龄不在闭区间[19,20]的学生

SELECT Sname,Sage from student
WHERE Sage NOT BETWEEN 19 AND 20;

在这里插入图片描述

③：确定集合

演示：

【例10】查询数字1是否在集合(1,2,3)中

  • 由于满足，所以会返回1
SELECT 1 IN (1,2,3);

在这里插入图片描述

【例11】查询数学系（MA）和计算机科学系（CS）学生的姓名

SELECT Sname,Sdept from student
WHERE Sdept IN('MA','CS');

在这里插入图片描述

【例12】查询非数学系（MA）和非计算机科学系（CS）学生的姓名

SELECT Sname,Sdept from student
WHERE Sdept NOT IN('MA','CS');

在这里插入图片描述

④：字符匹配

注意：

  • 可以使用=代替LIKE，使用!=代替NOT LIKE
  • %可以代替多个字符
  • _只能代替一个字符

演示：

【例13】查询所有男生

SELECT Sname from student
WHERE Ssex LIKE '男';

在这里插入图片描述
【例14】查询所有姓刘的学生（%代替多个）

SELECT Sname FROM student 
WHERE Sname like '刘%';

在这里插入图片描述

⑤：转义字符

注意：

  • ESCAPE '＼' 表示将“ ＼” 翻译为转义字符

演示：

【例15】假设Course表中有一门课叫做DB_Design

  • 如果不作处理，这里的_代替某个字符，产生歧义
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

⑥：涉及空值的查询

演示：

【例16】查询缺少成绩的学生的学号和相应的课程号

  • 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩
SELECT Sno Cno FROM SC
WHERE Grade IS NULL;

在这里插入图片描述

⑦：多重条件查询

注意：

  • AND和 OR来联结多个查询条件
  • AND的优先级高于OR
  • 可以用括号改变优先级

演示：

【例17】 查询计算机系年龄在20岁以下的学生姓名

SELECT * FROM student
WHERE Sdept='CS' AND Sage < 20;

在这里插入图片描述

（3）ORDER BY子句

语法：ORDER BY子句对查询结果按照一个或多个属性列进行排序

  • ASC-升序（默认）
  • DESC-降序

演示：

【例18】查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列

SELECT Sno,Grade
from SC
WHERE Cno='3'
ORDER BY Grade DESC;

在这里插入图片描述

【例19】查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

SELECT *
FROM student
ORDER BY Sdept,Sage DESC;

在这里插入图片描述

（4）聚集函数

语法：主要有以下几种
在这里插入图片描述

演示：

【例20】查询学生的总人数

SELECT COUNT(Sno)
FROM student;

在这里插入图片描述
【例21】查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno) Num
FROM SC;

在这里插入图片描述
【例22】 计算2号课程的学生平均成绩

SELECT AVG(Grade)
FROM SC 
WHERE Cno = '2';

在这里插入图片描述
【例23】查询选修2号课程的学生最高分数

SELECT Sno,MAX(Grade)
FROM SC 
WHERE Cno='2';

在这里插入图片描述

（5）GROUP BY子句

语法：GROUP BY子句将查询结果按某一列或多列的值分组，值相等的分为一组

  • 分组目的是为了细化聚集函数的作用对象：若未分组，聚集函数将会作用于整个查询结果；若分组，聚集函数将会作用于每一个组，也即每一个组都有一个函数值
  • 需要注意：WHERE子句作用于整个表或视图，从中选择出满足条件的元组；HAVING短语作用于组，从中选择满足条件的组

相信读完之后大家可能还是有点迷糊，举个例子。比如我要查询“各个课程对应的选课人数”，如果没有GROUP BY子句

SELECT Cno,Count(Sno)
FROM sc;

由于它会作用于整个查询结果，所以直接统计出了记录的条数
在这里插入图片描述
如果加入GROUP BY子句，按照课程号分组，那么GROUP BY会按照Cno进行分组，相同的为一组，然后在每组内统计Sno

SELECT Cno,Count(Sno)
FROM sc
GROUP BY Cno;

在这里插入图片描述

而如果我只想显示那些选课人数大于1以上的课程号呢，那么就可以使用HAVING短语，在组内进行筛选

SELECT Cno,Count(Sno)
FROM sc
GROUP BY Cno
HAVING Count(Sno) > 1;

在这里插入图片描述
演示：

【例24】查询平均成绩大于等于80分的学生学号和平均成绩

SELECT Sno,AVG(Grade)
FROM SC 
GROUP BY Sno
HAVING AVG(Grade) >= 80;

在这里插入图片描述

二：连接查询（查询时涉及多张表）

（1）等值连接和非等值连接

语法：在WHERE子句中写入连接条件（又叫做连接每谓词），其格式为
在这里插入图片描述
其中比较运算符有：=、>、<、>=、<=、!=

  • 当运算符为=时称之为等值连接
  • 当运算符不为=时称之为非等值连接

演示：

【例25】查询每个学生及其选修课程的情况

SELECT student.*,sc.*
FROM student,sc
WHERE student.Sno=sc.Sno;

在这里插入图片描述
【例26】使用自然连接（特殊的等值连接）完成【例25】

SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM student,sc
WHERE student.sno=sc.sno;

在这里插入图片描述
【例27】查询选修2号课程且成绩在80分以上的所有学生的学号和姓名

SELECT Student.Sno,Sname
FROM student,sc
WHERE student.Sno=sc.Sno AND //连接条件
	Cno='2' AND Grade > 80; //其他限定条件

在这里插入图片描述

（2）自身连接

语法：所谓自身连接就是指一个表与自己连接

演示：

【例28】查询每一门课的先修课的先修课

  • 在Course表中有的只是每门课的直接先修课，要想得到先修课的先修课，那么就必须先找到一门课的先修课，然后再按此先修课的课程号查找它的先修课

因此，为Course表取两个别名，分别为ONE和TWO

在这里插入图片描述

SELECT ONE.Cno,TWO.Cpno
FROM Course ONE,Course TWO
WHERE ONE.Cpno=TWO.Cno;

在这里插入图片描述

当然，还可以继续找先修课的先修课的先修课

SELECT ONE.Cno,THREE.Cpno
FROM Course ONE,Course TWO,course THREE
WHERE ONE.Cpno=TWO.Cno AND TWO.Cpno=THREE.Cno;

在这里插入图片描述

（3）连接JOIN

语法：SQL JOIN用于把来自两个或多个表的行结合起来，其格式如下

SELECT column_name(s)
FROM TABLE1//左表
<某某 JOIN>TABLE2//右表
ON TABLE1.column_name=TABLE2.column_name

有如下几类

  • INNER JOIN(JOIN)
  • LEFT JOIN(LEFT OUTER JOIN)
  • RIGHT JOIN(RIGHT OUTER JOIN)
  • FULL JOIN(FULL OUTER JOIN)

A：INNER JOIN(JOIN)

INNER JOIN(JOIN)：关键字在表中存在至少一个匹配时返回行
在这里插入图片描述
演示：

【例29】以sc和course的Cno作为比对标准，将相同连接在一起

SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc INNER JOIN course ON(sc.Cno=course.Cno);

在这里插入图片描述

B：LEFT JOIN(LEFT OUTER JOIN)

LEFT JOIN(LEFT OUTER JOIN)：以左表为标准，若右表中无匹配，则填NULL

在这里插入图片描述

SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc LEFT JOIN course ON(sc.Cno=course.Cno);

在这里插入图片描述

C：RIGHT JOIN(RIGHT OUTER JOIN)

RIGHT JOIN(RIGHT OUTER JOIN)：以右表为标准，若左表中无匹配，则填NULL

在这里插入图片描述

SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc RIGHT JOIN course ON(sc.Cno=course.Cno);

在这里插入图片描述

D：FULL JOIN(FULL OUTER JOIN)

FULL JOIN(FULL OUTER JOIN)：本质就是结合了LEFT JOIN和RIGHT JOIN

在这里插入图片描述

SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc FULL JOIN course ON(sc.Cno=course.Cno);

（4）复合条件连接

语法：没有什么新的东西，就是涉及多张表，多个条件的查询

演示：

【例30】查询每个学生的学号、姓名、选修的课程名及成绩

SELECT student.Sno,Sname,Cname,Grade
FROM student,course,sc
WHERE student.Sno=sc.Sno AND sc.Cno =course.Cno;

在这里插入图片描述

三：嵌套查询

在SQL中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称之为嵌套查询。比如

在下面的这个例子中，内层循环查出来的是符合Cno=2的Sno集合，外层循环则在该集合内查询是否有满足的Sno，有的话显示Sname即可

SELECT Sname //外层查询
FROM Student
WHERE Sno IN
	(
		SELECT Sno //内层查询
		FROM SC
		WHERE Cno='2'
	)

在这里插入图片描述

需要注意以下几点

  • 子查询的SELECT语句不能使用ORDER BY子句
  • 嵌套查询往往可以转换为对应的连接运算

（1）带有IN谓词的子查询

语法：嵌套查询中，子查询的结果往往是一个集合，所以IN在嵌套查询中使用次数最为频繁

演示：

【例31】查询与“刘晨”在同一个系学习的学生

  • 考虑时可以由内向外，先查询出刘晨所在的系，然后在该集合中查询满足该集合的学生姓名
SELECT student.Sno,Sname,Sdept FROM student WHERE Sdept IN
(SELECT Sdept FROM student WHERE Sname='刘晨');

在这里插入图片描述

当然嵌套查询有时也可以转化为连接完成

SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';

【例32】查询选修了课程名为“信息系统”的学生学号和姓名

  • 首先在Course表中找出“信息系统”的Cno，形成Cno的集合
  • 然后在SC表中找出哪些记录的Cno在Cno集合内，形成Sno集合
  • 最后在Student表中找出哪些记录的Sno在Sno集合内，形成最终的结果
SELECT Sno,Sname FROM Student WHERE Sno IN 
(SELECT Sno FROM SC WHERE Cno IN
	(SELECT Cno FROM Course WHERE Cname='信息系统')
);

在这里插入图片描述
当然本例也可以使用连接完成

SELECT student.Sno,Sname
FROM student,course,sc
WHERE Student.Sno=sc.Sno AND sc.Cno=course.Cno AND Cname='信息系统';

（2）带有比较运算符的子查询

语法：带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以使用>、<、=、>=、<=、!=等比较运算符

演示：

【例33】查询与“刘晨”在同一个系学习的学生

  • 和【例31】一样，只不过【例31】使用IN完成的
SELECT Sno,Sname,Sdept FROM student WHERE Sdept
=
(SELECT Sdept FROM student WHERE Sname='刘晨');

在这里插入图片描述

【例34】找出每个学生超过他选修课程平均成绩的课程号

  • 首先内层循环要计算该学生的平均成绩
  • 外层循环使用>=
  • 两个循环要用Sno关联在一起（相关子查询）
SELECT Sno,Cno FROM sc x WHERE Grade
>=
(SELECT AVG(Grade) from sc WHERE x.Sno=Sno);

在这里插入图片描述

√：不相关子查询和相关子查询

不相关子查询：子查询的查询条件不依赖于父查询

  • 像【例31】、【例32】这样的都是不相关子查询

查询时由里向外、逐层处理。每个子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件

相关子查询：子查询的查询条件依赖于父查询

  • 像【例34】这样的都是相关子查询

查询时首先会取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回为真，则将此元组放入结果表，然后再取外层表的下一个元组，接着重复上述过程

比如对于【例34】，其处理过程可能是下面这样的

①：从外层查询中取出sc的一个元祖x，将x.Sno的值(201215121)传递给内层查询

SELECT AVG(Grade)
FROM sc y
WHERE y.Sno='201215121';

②：执行内层查询，得到值88（近似），用该值代替内层查询，得到外层查询

SELECT Sno,Cno
FROM SC X
WHERE Grade >= 88

③：执行这个查询，得到

(201215121,1)
(201215121,3)

然后再取下一个元组重复上述过程即可

（3）带有ANY（SOME）或ALL谓词的子查询

语法：内层查询返回单个值时使用比较运算符。如果返回多个值要用ANY（有的是SOME）或ALL，然后同时使用比较运算符

在这里插入图片描述
另外ANY或ALL与聚集函数、IN等谓词有等价关系。也就是说在一些情况下，他们的写法效果作用是一样的
在这里插入图片描述

  • 例如<ALL意思是小于所有值，那么它的等价含义就是小于最小值，也即<MIN

演示：

【例35】查询其他系比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage FROM student WHERE Sage < ANY
	(SELECT Sage FROM student WHERE Sdept='CS')
AND Sdept!='CS';

在这里插入图片描述
由于<ANY等价于<MAX，所以其等价写法就是

SELECT Sname,Sage FROM student WHERE Sage <
	(SELECT MAX(Sage) FROM student WHERE Sdept='CS')
AND Sdept!='CS';

（4）带有EXISTS谓词的子查询

语法：EXISTS代表“存在量词\({\exists}\)”。带有EXISTS谓词的子查询不返回任何数据，只返回true或false。另外，由EXISTS引出的子查询，其目标列表达式通常都是*，因为给出列名没有实际意义

  • 若内层查询结果非空，则外层WHERE子句返回true
  • 若内层查询结果为空，则外层WHERE子句返回false

与EXISTS相对的便是NOT EXISTS

  • 若内层查询结果为空，则外层WHERE子句返回true
  • 若内层查询结果非空，则外层WHERE子句返回false

需要注意的是，一些带有EXISTS和NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；但是所有带IN谓词，比较运算符，ANY和ALL谓词的子查询都可以用带EXISTS谓词的子查询替换

演示：

【例36】查询所有选修了1号课程的学生姓名

  • 处理时，首先会取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（Sno）处理内层查询，若WHERE子句返回为true则取外层查询中该元组的Sname放入结果表
SELECT Sname FROM student WHERE 
EXISTS
(SELECT * from sc where Sno=student.Sno AND Cno='1');

在这里插入图片描述

【例37】查询与“刘晨”在同一个系学习的学生

  • 和【例31】一样，这里用EXISTS代替IN
SELECT Sno,Sname Sdept FROM student s1 WHERE
EXISTS
	(select * FROM student WHERE Sdept=s1.Sdept AND Sname='刘晨');

在这里插入图片描述

【例38】查询选修了全部课程的学生姓名

  • 它等价于：查询这样一个学生，没有一门课是它不选的

在这里插入图片描述

四：集合查询

语法：SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。主要有：

  • 并操作：UNION
  • 交操作：INTERSECT
  • 差操作：EXCEPT

需要注意的是查询结果的列数必须相同且对应项的数据类型也必须相同

演示：

【例39】查询计算机科学系的学生及年龄不大于19岁的学生

SELECT Sname,Sage,Sdept FROM student WHERE Sdept='CS' 
UNION
SELECT Sname,Sage,Sdept FROM student WHERE Sage<=19;

在这里插入图片描述

【例40】查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT * FROM student WHERE Sdept='CS'
INTERSECT
SELECT * FROM student WHERE Sage <=19;

标签：JOIN,王珊,查询,Sno,关系数据库,SQL,Cno,WHERE,SELECT
From： https://www.cnblogs.com/zhangxing-tech/p/17121549.html

相关文章

  • MySQL中，把查询的结果拼接成一个字符串。
    用法：group_concat(待拼接对象)输出：用逗号进行拼接后的字符串selectgroup_concat(emp_no)asemployeesfromdept_emp;  /*结果：employees       ......
  • Hive 面试题——HiveSQL 执行顺序
    描述今天刷到了一个面试题：hivesql执行顺序，接下来就从一个带有groupby的例子看看hivesql的执行顺序执行顺序为from..on..join..where..groupby..having......
  • mysql 主从复制
    概述主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库对这些日志重新执行（也叫重做），从而是的从库和主库数据保持同步。mysql也支持一台主库......
  • 1.mysql架构
    mysql架构mysql的基础架构图innoDb引擎的4大特性，区别插入缓冲（Insertbuffer/ChangeBuffer）产生的原因：索引存在在磁盘，主键索引由于天然自增，无须磁盘的随机IO，只......
  • sql语气查询去重的两种方法、sql的模糊查询、sql中的日期函数、mysql字符串截取之subs
    sql语气查询去重的两种方法以下两种都可以进行去重查询，区别是：用distinct去重，只能查询到去重的属性那一列，无法查询其他字段用groupby分组查询，可以根据需求查询对应的其......
  • Python 使用mysql.connector、pymysql和 MYSQLdb(MysqlClient)操作MySQL数据库
    MySQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。本文主要介绍安装mysql.connector,、pymysql......
  • MySQL SQL题
    1列转行 selectlcase(`name`)as`name`,sum(CASETYPEwhen'Breakfast'THEN`VALUE`else0end)asBreakfast,sum(CASETYPEwhen'Lunch'THEN`VALUE`e......
  • 干货！！!mysql如何进行优化,mysql优化环节
    MySQL优化环节有很多，我个人经验从硬件优化、软件优化、表结构设计、查询语句四个方面来考虑。硬件优化：MySQL软件本身是运行在硬件服务器之上，数据是存储在磁盘的。如果能......
  • Mybatis12 - 动态SQL
    动态SQLMybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。Oneofthemostpowerfulfeatures......
  • dinky-binlog-kafka-flinksql流程处理
    准确阶段：mysql：开启mysql日志kafka：需检查服务是否正常maxwell：这里采用19版本，过新的版本对java版本要求高，我这里是java8maxwell-1.19.0maxwell操作：cd/root/tar_temp/maxwell-......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99