SQL server学习07-查询数据表中的数据（下）

一，自连接查询

二，多表查询

三，关系代数运算

1，笛卡尔乘积运算

1）交叉连接

2，连接运算

2）内连接

四，外连接

1，左外连接

2，右外连接

3，全外连接

五，子查询

1，非相关子查询

2，任务实施

1）使用比较运算符

2）使用[NOT] IN 运算符

3）使用[NOT] EXISTS子句

六，查询语句的其它应用

1，根据子查询修改相应的记录

2，使用SELECT INTO语句将查询到的记录创为一张新表

一，自连接查询

语法如下：

sql">SELECT 选择列表 
FROM 同一张表表名 AS 别名1, 同一张表表名 AS 别名2
WHERE 查询条件

可以看到，使用自连接时，必须给表起别名，来区分使用自连接时由自身表的一个镜像产生的另外一张表。

当所需要的信息都集中到了一张表中时，想要查询相关的信息只需要使用自连接完成相应的查询要求。例如，我现在有一张员工信息表emps，表结构如下👇

sql">-- 员工表
CREATE TABLE emps (
  eno int NOT NULL PRIMARY KEY,  -- eno员工号
  ene varchar(100),  --employee ene 员工姓名
  elr varchar(100)   --employee leader员工上司
);

因为每个员工的领导也是员工，所以领导的信息也在员工信息表中。

接下来开始向表中插入记录：

sql">-- 向员工表中插入数据
INSERT INTO emps (eno,ene,elr) VALUES
(10001, 'BOSS',NULL),     -- BOSS：公司老板
(10002, 'A', '10005'),  
(10003, 'B', '10001'),   
(10004, 'C', '10003'),
(10005, 'D', '10001'),
(20001, 'E', '20002'),
(20002, 'F', '10001'),
(20003, 'G', '10001'),
(30001, 'H', '10001'),
(30002, 'I', '30001'),
(30003, 'J', '20002')

任务：查询每名员工的上司是谁（姓名）

分析：因为查询的是 elr 领导列对应的员工号 eno 列，所以连接条件为：别名1.elr = 别名2.eno

对应的语句如下👇

sql">SELECT e1.eno AS 员工编号,e1.ene AS 员工名,e2.ene AS 领导名 
FROM emps AS e1, emps AS e2
WHERE e1.elr=e2.eno

结果集：

二，多表查询

在实际的查询中，用户所需要的数据并不全都在一张表中，而可能在多张表中。这时，就要用到多表连接查询。多表连接查询首先要在这些表中建立连接，再在连接生成的结果集中进行筛选。

例如，如果想要查询每名学生的成绩详情，包括姓名sne，课程名称cne，成绩get，这些信息分别保存在学生表students，课程表courses和成绩表score这 3 张表中，如下👇

sql">SELECT sne AS 学生名,courses.cne AS 课程名,get AS 成绩
FROM students,courses,score
WHERE students.sno=score.sno AND courses.cno=score.cno

在学习连接查询之前，需要先知道连接查询所基于的关系代数运算。

三，关系代数运算

关系代数的含义：

是一种抽象的查询语言，用对关系的运算来表达查询
是关系数据库标准语言SQL查询操作的理论基础
是研究关系数据语言的数学工具

关系代数的运算对象是关系，运算结果也是关系。这里仅学习常用的几种和连接查询相关的关系代数运算。

1，笛卡尔乘积运算

笛卡尔乘积是 SQL 中的一种操作，会将两个或多个表中的每一行进行组合，生成一个包含所有可能行组合的结果集。笛卡尔乘积的结果集的行数等于参与组合的表中行数的乘积。

假设有关系 $R$ 和 $S$ ，其中：

关系 $R$ 有 $r$ 个属性分量， $m$ 个元组。
关系 $S$ 有 $s$ 个属性分量， $n$ 个元组。

则这两个关系的笛卡尔积运算定义如下：

$R\times S=\left \{ t | t=<t^{r},t^{s}>\wedge t^{r}\in R \wedge t^{s}\in S\right \}$

也可以写成如下的形式👇：

$R \times S=\left \{ (t^{r},t^{s}) | t^{r} \in R,t^{s} \in S \right \}$

即，笛卡尔乘积 $R\times S$ 是由所有可能的有序对 $t$ 组成的集合，其中每个有序对的第一个元素来自集合 $R$ ，第二个元素来自集合 $S$ 。

1）交叉连接

在sql中，会使用交叉连接（CROSS JOIN），用于返回两个表的笛卡尔积。会将第一个表中的每一行与第二个表中的每一行进行组合，生成一个新的结果集。

例如，使用交叉连接 CROSS JOIN对下面两张表进行笛卡尔积运算：

这里添加了一个没有班级的系：Mathematics数学系。

sql">('2','Mathematics','QiangjunGong','1931111111')

之后得到的结果为 $R \times S=2*2=4$ 条记录（即共有 4 种组合），如下👇

sql">SELECT cne AS 班名,dne AS 系名 FROM dept
CROSS JOIN classes

2，连接运算

连接运算：从笛卡尔乘积中选取属性间满足比较条件（连接条件）的记录。

可以看到之前使用笛卡尔积运算后的结果集有两条是多余的，因为数学系（系部编号为2）现在还没有创建班级，不可能有记录。

2）内连接

语法如下：

sql">-- 多表内连接查询
SELECT 查询列表
FROM 表1
JOIN 表2 ON 表1.列名 比较运算符 表2.列名
JOIN 表3 ON 表2.列名 比较运算符 表3.列名
.
.
.

内连接是最常用的一种数据连接查询方式，特别是当两张表具有主外键关系时，通常会使用内连接查询。它使用比较运算符将各表中的共同的列进行匹配，最终查询出各表匹配的数据行，将两张表连接成一个新的数据集，在形成的数据集中没有不满足连接条件的数据行。

这个时候就需要使用连接条件：主表的主键=从表的从键。如下👇 dept.dno = classes.dno

sql">SELECT cne AS 班名,dne AS 系名 FROM dept
JOIN classes ON dept.dno = classes.dno;

可以看到，只有计算机系 Computer的记录，而没有数学系的记录（还没有班级）。

上面只连接了两张表，接下来连接三张表。

这里以之前查询每名学生的成绩详情，包括姓名sne，课程名称cne，成绩get为例，如下：

sql">SELECT sne AS 学生名,courses.cne AS 课程名,get AS 成绩
FROM students 
JOIN score   ON students.sno=score.sno 
JOIN courses ON courses.cno=score.cno

连接运算对应的查询就是连接查询。除了刚刚学习的交叉连接和内连接，连接查询还有外连接。接下来开始学习外连接。

四，外连接

连接可以分为内连接，外连接和交叉连接。其中外连接又分为左连接，右连接和全连接。

语法如下：

sql">SELECT 选择列表 
FROM    表1
连接类型 表2 ON 连接条件

--连接类型如下：
--1，左外连接  LEFT JOIN
--2，右外连接  RIGHT JOIN
--3，全外连接  FULL JOIN

例如：查询所有学生的选课情况（包括学生姓名，课程名，成绩，及无学生选修的课程）

sql">SELECT sne AS 学生名,courses.cne AS 课程名,get AS 成绩
FROM students 
LEFT JOIN score   ON students.sno=score.sno 
RIGHT JOIN courses ON courses.cno=score.cno

可以看到，与内连接不同的是，外连接返回的结果集除包含连接条件的行外，还可以返回FROM子句中至少一张表（或视图）的所有行（只要这些行满足检索条件，而无论它们是否满足连接件）。

就如上表所展示的数据那样，计算机组成原理Principle of Computer Organization和计算机网络Computer Network这两门课没有任何学生去选修，因此在学生名一栏及成绩一栏数据的值都为空NULL 。

返回所有行的表称为主表，另一个则为从表。

连接时，用主表的每一行数据区匹配从表。

上述例子：

在这个LEFT JOIN score ON students.sno = score.sno 语句中

students 是主表（左表），score 是从表（右表）。

因为使用 LEFT JOIN左外连接，所以左表中的所有记录都会被保留，即使在右表没有匹配的

记录。由于我之前添加的所有学生都参加了考试，因此没有成绩为空NULL的记录。
在这个 RIGHT JOIN courses ON courses.cno = score.cno 语句中

courses 是主表（右表），score 是从表（左表）。

由于使用了 RIGHT JOIN右外连接，右表courses 表中的所有记录都会被保留，即使在左表

score 中没有匹配的记录。由于课程表中有两门课无学生选修，因此学生名及成绩都为空。

当course在连接过程中，不作为主表，那么，就不会有空值NULL如下👇

实现效果与之前学的内连接相同👇

如果想要查询的记录集包括全部课程，在进行连接的过程中就需要将课程表courses作为主表👇

可以看到，以上语句虽然略有不同，但是实现的效果一样，唯一不变的就是连接过程中courses课程表也是主表之一。接下来继续学习外连接中的三个连接：左外连接，右外连接，全外连接。

1，左外连接

语法如下：

sql">SELECT 选择列表 
FROM      表1
LEFT JOIN 表2 ON 连接条件

在左外连接中，左表为主表，返回左表中的全部记录行以及右表中匹配行。

假设有两张表：表A和表B，此时让表A和表B进行左外连接。

sql">SELECT 选择列表 
FROM      A
LEFT JOIN B ON 连接条件

这里令A表为学生表students，B表为成绩表score。

任务如下：

查询所有学生的选课情况（包括学号，学生姓名，课程号，成绩），包括未选择任何课程的学生，并按班级和学号升序排序。

通过要求可知，连接是以学生表为主表。接下来编写语句：

sql">SELECT st.sno AS 学号,sne AS 学生名,st.cno AS 班级号,sc.cno AS 课程号,get AS 成绩
FROM students AS st
LEFT JOIN score AS sc  ON st.sno=sc.sno
ORDER BY st.cno,st.sno

结果集：

因为学生都选了课，所以记录集里面没有未选择任何课程的学生。现在再插入未选择课程的学生：

sql">('6','songjiang','male','2002.8.1','17911111111','2') -- 2班的学生

添加学生进去之后，不需要再到其它表中添加任何数据，执行左外连接的语句得到的结果集如下：

2，右外连接

语法如下：

sql">SELECT 选择列表 
FROM      表1
RIGHT JOIN 表2 ON 连接条件

在右外连接中，右表为主表，返回右表中的全部记录行以及左表中匹配行。

假设有两张表：表A和表B，此时让表A和表B进行右外连接。

sql">SELECT 选择列表 
FROM      A
RIGHT JOIN B ON 连接条件

这里令A表为学生表students，B表为成绩表score。

任务如下：

查询所有学生的选课情况（包括学号，学生姓名，课程号，成绩），包括未选择任何课程的学生，并按班级和学号升序排序。

通过要求可知，连接是以学生表为主表。接下来编写语句：

sql">SELECT st.sno AS 学号,sne AS 学生名,st.cno AS 班级号,sc.cno AS 课程号,get AS 成绩
FROM score AS sc
RIGHT JOIN students AS st  ON st.sno=sc.sno
ORDER BY st.cno,st.sno

结果集：

3，全外连接

语法如下：

sql">SELECT 选择列表 
FROM      表1
FULL JOIN 表2 ON 连接条件

与之前学的左外连接和右外连接不同，全外连接没有主从表，会直接返回左表和右表中的所有行。

任务依旧如下：

查询所有学生的选课情况（包括学号，学生姓名，课程号，成绩），包括未选择任何课程的学生，并按班级和学号升序排序。编写语句如下👇

sql">SELECT st.sno AS 学号,sne AS 学生名,st.cno AS 班级号,sc.cno AS 课程号,get AS 成绩
FROM students AS st
FULL JOIN score AS sc  ON st.sno=sc.sno
ORDER BY st.cno,st.sno

结果集：

如果将全外连接里面的表互换位置，则查询结果不变。

即执行如下语句和上面的语句得到的结果集一样。

sql">SELECT st.sno AS 学号,sne AS 学生名,st.cno AS 班级号,sc.cno AS 课程号,get AS 成绩
FROM score AS sc
FULL JOIN students AS st  ON st.sno=sc.sno
ORDER BY st.cno,st.sno

五，子查询

子查询既可以出现在选择列表中，也可以出现在FROM子句中，最常用的是出现在WHERE子句中。将一个查询语句嵌套在另一个查询语句中，称为嵌套查询。

里层的查询语句叫子查询，外层的查询语句叫父查询。子查询可以再包含子查询，至多可嵌套32层。嵌套查询的使用：

WHERE子句中需要使用聚合函数。由于WHERE子句中不能使用聚合函数（会报错），因此就必须使用嵌套查询。
可以把复杂查询分成一系列简单查询，一般这种情况的查询也可以使用连接查询完成。

所有的子查询可以分为两类：相关子查询和非相关子查询。

相关子查询的执行依赖于父查询。多数情况下是子查询的WHERE子句中引用了父查询的表，执行过程如下：

从父查询中取出一条记录，将记录相关列的值传给子查询，
执行子查询，得到子查询操作的值，
父查询根据子查询返回的结果或结果集，得到满足条件的行，
父查询取出下一条记录，重复做步骤1~3，直到外层的记录全部处理完。

较多使用的是非相关子查询。因为非相关子查询的执行不需要依赖父查询。

1，非相关子查询

执行过程如下：

执行子查询，其结果不被显示，而是传递给外部程序，作为父查询的条件使用。
执行父查询，并显示整个结果。

一般语法如下：

sql">WHERE 表达式 比较运算符(子查询)
WHERE 表达式 [NOT] IN(子查询)
WHERE [NOT] EXISTS(子查询)

其中：EXISTS后跟的子查询一般为相关子查询。

非相关子查询一般返回单值或一个列表。

返回单值，则使用比较运算符；
返回列表，则需要使用 IN 运算符。

2，任务实施

接下来通过完成相关的任务，进一步了解并熟悉嵌套查询。

1）使用比较运算符

任务1：查询和zs（学号为1）同班且同龄的学生。

学生表students中的数据如下👇

可以看到，有两个同学和张三zs一样都是00年出生：wangwu王五和 zhangqiang张强。

编写语句如下：

sql">SELECT st.sno AS 学号,sne AS 学生名,YEAR(sbd) AS 出生年份
FROM students AS st
WHERE cno=(SELECT cno FROM students WHERE sno='1') AND
YEAR(sbd)=(SELECT YEAR(sbd) FROM students WHERE sno='1')

得到的记录集如下：

-- 后面补上的内容 ↓ ↓ ↓

小任务2：查询成绩最高的学生学号。

2）使用[NOT] IN 运算符

在上面的小任务2中，只能看到学生学号，而不能看到指定的学生名，如果想要查询最高分的学生学号和姓名，就可以如下编写语句👇

由于查询列表中的学生名来自学生表中，因此需要内连上一张学生表students。
通过上面的查询，可以看到，最高分可能不止一个，有可能有多个同学的分数都是最高分，因此在最外层的查询中需要使用 IN 运算符。

sql">SELECT sc.sno AS 学号, sne AS 学生名, get AS 最高成绩分数
FROM score AS sc
JOIN students AS st ON st.sno = sc.sno
WHERE get = (SELECT MAX(get) FROM score);

任务3：查询选修了“SQLserver DataBase Application principle”课程的学生学号和姓名，及成绩

只有学生表students中有学生名，因此一定要有学生表。
课程表courses里面有课程号cno和课程名cne，是成绩表score的主表，因此可以通过成绩表的课程编号找到指定课程名。
以上三张表可以通过内连的方式组合在一起，最后再加上筛选条件：课程名，就行。

编写的语句如下👇

sql">SELECT st.sno AS 学号, sne AS 学生名,cne AS 课程名,get AS 成绩得分
FROM students AS st
JOIN score  AS sc ON st.sno = sc.sno
JOIN courses AS co ON co.cno=sc.cno 
WHERE co.cne =
(SELECT cne FROM courses WHERE cne='SQLserver DataBase Application principle')

如果在查询的时候不想知道课程名，及成绩得分，可以将内连的成绩表和课程表删掉，之后多写两句WHERE子句，如下👇：

（当然学生表和课程表之间没有主从关系，但可以通过成绩表将两张表联系起来）

3）使用[NOT] EXISTS子句

EXISTS用于检查子查询是否至少会返回一行数据，语法如下：

sql">WHERE [NOT] EXISTS(子查询)

如果子查询结果集为空，则EXISTS子句返回FALSE，否则返回TRUE。NOT EXISTS则相反。

这里的子查询实际上并不返回任何数据，所以由NOT EXISTS引出的子查询，其选择列表通常都用“ * ”表示。另外，请注意[NOT] EXISTS 关键字前面没有列名，常量或其它表达式。

任务1：查询没有选课的学生学号，姓名。

从数据中可得，只要学生表students的学号sno不存在成绩表score中，就表示该生没有选任何课。

编写语句如下👇

sql">SELECT sno AS 学号, sne AS 学生名
FROM students 
WHERE NOT EXISTS
(SELECT sno FROM score WHERE students.sno=score.sno)

也可以使用 NOT IN 运算符实现，编写语句如下：

sql">SELECT sno AS 学号, sne AS 学生名
FROM students 
WHERE sno NOT IN(SELECT sno FROM score)

六，查询语句的其它应用

在很多情况下，会将查询语句与数据操作语句结合使用。例如，根据子查询的结果删除或修改相应的记录。又如查询的结果形成新的表等。

1，根据子查询修改相应的记录

任务：将“SQLserver DataBase Application principle”课程的成绩加5分。

由于课程表courses和成绩表score之间存在主从关系，courses.cno=score.cno，因此可以从cno列入手，编写的语句如下👇

sql">-- 查看修改前的数据
SELECT st.sno AS 学生号,sne AS 学生名,cne AS 课程名,get AS 成绩得分
FROM students AS st
JOIN score AS sc ON sc.sno=st.sno
JOIN courses AS co ON co.cno=sc.cno
WHERE co.cno=(SELECT cno FROM courses WHERE cne='SQLserver DataBase Application principle' )

-- 修改成绩表
UPDATE score SET get=get+5
WHERE cno=
(SELECT cno FROM courses WHERE cne='SQLserver DataBase Application principle')

-- 查看修改后的数据
SELECT st.sno AS 学生号,sne AS 学生名,cne AS 课程名,get AS 成绩得分
FROM students AS st
JOIN score AS sc ON sc.sno=st.sno
JOIN courses AS co ON co.cno=sc.cno
WHERE co.cno=(SELECT cno FROM courses WHERE cne='SQLserver DataBase Application principle' )

2，使用SELECT INTO语句将查询到的记录创为一张新表

依旧是之前学习的查询语法，如下👇

sql">SELECT 列名或表达式
INTO 新表名   --创建新表并将结果集插入新表中
FROM 表名     --指定查询语句中所使用的表或视图
WHERE 查询条件   --设定检索条件。定义了源表中的行要满足SELECT语句的要求所必须达到的条件。只有符合条件的行才向结果集提供数据。即，不采用不符合条件的行数据。
GROUP BY 分组表达式  --分组子句
HAVING 分组条件   --和GROUP BY 一起使用，表示过滤组。
ORDER BY 列名或表达式 ASC升序/DESC降序  --为查询结果排序。

任务1：创建1班的花名册，名字为S1。

sql">SELECT sno AS 学号,sne AS 学生姓名
INTO S1
FROM students WHERE cno='1'

SELECT * FROM S1

之后去刷新对象资源管理器，可以看到通过查询得到的新表S1：