SQL基础查询语句详解

2024-02-23 18:53:52

SQL基础查询语句详解

基本表的创建

查询语句详细介绍
单表查询

选择表中的若干列
选择表中的若干元组
Order by 子句
聚集函数
group by子句

连接查询
嵌套查询

未完成待续

基本表的创建

3.3.2 定义基本表
3.5 建立一个“学生”表Student

Create table Student(
	Sno char(9) primary key,/* 列级完整性约束条件，Sno是主码 */
	Sname char(20) unique, /* Sname	取唯一值 */
	Ssex char(2),
	Sage smallint,
	Sdept char(20) 
);

3.6 建立“课程”表Course

Create table Course(
	Cno char(4) primary key,/* Cno是主码 */
	Cpno char(4),/* 先修课 */
	Ccredit smallint,
	foreign key(Cpno) references Course(Cno)
	/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);

3.7 建立学生选课表SC

Create table SC(
	Sno char(9),
	Cno char(4),
	Grade smallint,
	primary key (Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/
	foreign key(Sno) references Student(Sno),
	/*表级完整性约束条件，Sno是外码，被参照表是Student*/
	foreign key(Cno) references Course(Cno)
	/*表级完整性约束条件，Sno是外码，被参照表是Student*/
);

查询语句详细介绍

单表查询

选择表中的若干列

【3.16】查询全体学生的学号与姓名

select Sno, Sname
from Student;

【3.18 】查询全体学生的详细记录

select * from Student;

【3.20 】查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示系名

select Sname,’Year of Birth:’,2014-Sage,LOWER(Sdept)
from Student;

用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤其有用。3.20:

Select Sname NAME,’Year of Birth:’ BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
from Student;

选择表中的若干元组

消除重复的行：使用distinct消除相同的行
【3.21】查询选修了课程的学生学号

Select distinct Sno from SC;

【3.22】查询满足条件的元组：查询计算机科学系全体学生名单

Select Sname from Student 
where Sdept=‘CS’;

【3.24】查询考试成绩不及格的学生学号

Select distinct Sno
from SC where Grade<60;

这里使用了distinct，当一个学生有多门成绩不合格，他的学号也只会出现一次
【3.26】查询年龄不再20～23岁之间的学生姓名、系别和性别

Select Sname,Sdept,Sage
from Student 
where Sage not between 20 and 23;

【3.28】查询既不是计算机科学系（CS）、数学系（MA），也不是信息系（IS）学生姓名和性别

Select Sname,Sdept,Sage
from Student
where Sdept not in(‘CS’,’MA’,’IS’);

通配符%和_:
% 代表任意长度（可以为0）的字符串。
_代表任意单个字符。
【3.30】查询所有姓刘的学生的姓名、学号、性别

Select Sname,Sno,Ssex
from Student
where Sname like ‘刘%’;

【3.31】查询姓“欧阳”且全名为三个汉字的学生姓名

Select Sname
From Student
where Sname like ‘欧阳_’;

【3.32】查询名字第二个字为“阳”的学生学号和姓名

Select Sname,Sno
from Student
where Sname like’_阳%’;

如果用户要查询的字符串本身就含有通配符%或_，就需要escape ’<换码字符>’短语对通配符进行转义
【3.34】查询DB_Design课程的课程号和学分

Select Cno,Ccredit
from Course
where Cname like ‘DB\_Design’ escape’\’;

【3.35】查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

Select * from Course
where Cname like ‘DB\_%i__’ escape ‘\’;

【3.36】查询缺少考试成绩的学生的学号和相应的课程号

Select Sno,Cno from SC
where Grade is null;

多重条件查询：默认情况下，and的优先级高于or，但是用户可以通过括号改变优先级。
【3.38】计算机科学系年龄在20岁以下的学生姓名

Select Sname from Student
where Sdept=‘CS’ and Sage<20;

Order by 子句

默认为ASC，可选为DESC。对于空值，排序时显示的次序由具体系统实现来决定。
【3.40】查询全体学生情况，查询结果按所在系号升序排列，同一系中的学生按年龄降序排列。

Select * from Student
order by Sdept,Sage desc;

聚集函数

sql提供了许多聚集函数，主要有：

Count(*)						
Count([distinct|all]<col name>) 
Sum([distinct|all]<col name>)	
avg([distinct|all]<col name>)	
max([distinct|all]<col name>)
min([distinct|all]<col name>)

当聚集函数遇到空值时，除了count(*)外，都跳过空值而只处理非空值。
where子句中是不能用聚集函数作为条件表达式的，聚集函数只能用于select子句和group by中的having子句

【3.42】查询选修了课程的学生人数

Select count(distinct Sno) from SC;

【3.44】查询选修1号课程的学生的最高分数

Select max(Grade) from SC
where Cno=‘1’;

【3.45】查询学生2018001选修课程的总学分数

Select sum(Ccredit) from SC,Course
where Sno=‘2018001’ and SC.Cno=Course.Cno;

group by子句

【3.46】求各个课程号及相应的选课人数

Select Cno,count(Sno)
from SC
group by Cno;

【3.47】查询选修了三门以上课程的学生学号

Select Sno from SC
group by Sno
having count(*) > 3;

Where子句与having短语的区别在于作用的对象不同。where子句作用于基本表或视图，从中选择满足条件的元组。having短语作用于组，从中选择满足条件的组。
【3.48】查询平均成绩大雨等于90分的学生学号和平均成绩
下面的语句是不对的 =>

select Sno,AVG(Grade) from SC
where AVG(Grade)>=90
group by Sno;

因为where子句是不能用聚集函数作为条件表达式的，应该将其改为having：

select Sno,AVG(Grade) from SC
group by Sno
Having AVG(Grade)>=90;

连接查询

等值与非等值连接查询
【3.49】查询每个学生及其选修课程的情况

Select Student.*, SC.*
from Student, SC
where Student.Sno=SC.Sno;
/*将Student与SC中同一学生的元组连接起来*/

关系型数据库管理系统执行该连接操作的一种可能过程是：首先在表Student中找到第一个元组，然后从头开始扫描SC表，逐一查找与Student第一个元组的Sno相等的SC元组，找到后将这两个元组拼接起来，形成结果表中的一个元组。SC全部查找完后，再从Student找第二个元组……直到Student全部元组都处理完毕。这就是嵌套循环连接算法的基本思想

可以在SC表Sno上建立了索引，根据Sno值通过索引找到相对应的SC元组。在等值连接中把目标列中重复的属性列去掉则为自然连接
【3.50】对【3.49】用自然连接完成

Select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno;

本例中，由于Sname，Ssex，Sage，Sdept，Cno和Grade属性列在Student表与SC表中是唯一的，所以引用时可以去掉表名前缀；而Sno在两个表中都出现了，因此引用必须加上表名前缀。

一条SQL语句可以同时完成选择和连接查询，这时where子句是由连接谓语和选择谓语组成的复合条件。
【3.51】查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

Select Student.Sno, Sname
from Student,SC
where Student.Sno=SC.Sno and/*连接谓语*/
	SC.Cno=‘2’ and SC.Grade>90;/*其他限定条件*/

该查询的一种优化执行过程是，先从SC中挑选出Cno=‘2’并且Grade>90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接的到最终的结果关系。

自身连接
【3.52】查询每一门课程的间接先修课（即先修课的先修课）。
在Course表中只有每门课的直接先修课信息，而没有先修课的先修课，要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号查找它的先修课程。这就要将Course表与自身连接。一个表与其自己进行连接，称为表的自身连接。
为此，要为Course表取两个别名，一个是FIRST，一个是SECOND。

Select FIRST.Cno,SECOND.Cpno
from Course FIRST,Course SECOND
where FIRST.Cpno = SECOND.Cno;

外连接
在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如果学生没有选课，在SC表中没有相应的元组，那么Student中这些元组在连接时就被舍弃了。

有时想以Student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍把Student的悬浮元组保存在结果关系。而在SC表的属性上填空值NULL，这时候需要使用外连接。

【3.53】

Select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student 
left outer join SC on (Student.Sno=SC.Sno);
/* 
也可以使用USING来去掉结果中的重复值：
From Student left outer join SC using(Sno);
*/

左外连接列出左边关系（如本例中Student）中所有的元组，右外连接列出右边关系中所有的元组。

多表连接
连接操作中除了可以是两表连接、一个表与其自身连接，还可以是多表连接。
【3.54】查询每个学生的学号、姓名、选修的课程名及成绩

select Student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.SNo=SC.Sno and SC.Cno=Course.Cno;

关系数据库管理系统执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接。本例的一个可能的执行方式是，先将Student表与SC表进行连接，的到每个学生的学号、姓名、所选课程号和相应的成绩，然后再将其与Course表进行连接，的到最终结果。

嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句成为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询（nested query）。例如：

Select Sname /*外层查询或父查询*/
From Student
where Sno in(
	Select Sno /*内层查询或子查询*/
	from SC
	where Cno=‘2’
);

本例中，下层查询块Select Sno from SC where Cno=‘2’是嵌套在上层查询块select Sname from Student where Sno in的where条件中的。上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。
SQL允许多层嵌套查询，但是子查询的SELECT语句不能使用order by子句，order by子句只能对最终结果查询。
嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层查询的方式来构造程序正是SQL中“结构化”的含义所在。

带有in谓语的子查询
在嵌套查询中，子查询的结果往往是一个集合，所以谓语in是嵌套查询中最经常使用的谓词。

【3.55】查询与“刘晨”在同一个系学习的学生。
先分步完成此查询，在构成嵌套查询
1⃣️ 确定“刘晨”所在系名

Select Sdept 
from Student
where Sname=‘刘晨’;

结果为CS
2⃣️ 查找所有在CS系学习的学生。

Select Sno,Sname,Sdept
from Student
where Sdept=‘CS’;

将第一步查询嵌入到第二步查询的条件中，构造嵌套查询如下：

Select Sno,Sname,Sdept
from Student
where Sdept in (
	Select Sdept
	from Student
	where Sname=‘刘晨’;
);

子查询的查询条件不依赖父查询，称为不相关子查询。一种求解方法是由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。的到如下的语句：

Select Sno,Sname,Sdept
from Student
where Sdept in (‘CS’);

然后执行该语句。本例也可以用自身连接来完成。

【3.56】查询选修了课程名为“信息系统”的学生学号和姓名
Student和Course两个表之间没有直接联系，必须通过SC表建立它们之间的联系。所以本查询实际上设计三个关系。

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,SC,Course
where Student.Sno=SC.Sno and
	SC.Cno=COurse.CNo and
	Course.Cname=‘信息系统’;

【3.55】和【3.56】中子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询（correlated subquery），整个查询语句称为**相关嵌套查询（correlated nested query）**语句。

带有比较运算符的子查询
【3.57】找出每个学生超过他自己选修课程平均成绩的课程号。

Select Sno,Cno
from SC x
where Grade>=(
	Select avg(Grade)
	from SC y
	where y.Sno=x.Sno
);

x是表SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层查询是求一个学生所有选修课程平均成绩的，至于哪个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的，因此这类查询称为相关子查询。
这个语句的一种可能的执行过程采用一下三个步骤。
1
2
3
然后外层查询取出下一个元组重复做上述1至3步骤的处理，直到外层的SC元组全部处理完毕。

求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。

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

码农公寓