数据库系统实践 IV 查询插入修改删除操作

知识

CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。

可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。

分为：

简单 CASE 函数

搜索 CASE 函数

简单 CASE函数

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个 WHEN 子句的简单表达式进行比较。

如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的 WHEN 子句所对应的结果表达式的值。

如果所有简单表达式的值与测试表达式的值都不相等，

若指定了 ELSE 子句 ,则返回 ELSE 子句中指定的结果表达式的值；若没有指定 ELSE 子句，则返回 NULL 。

例57.

查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：

当系号为 1 时，显示 “计算机系”；

当系号为 2 时，显示 “软件工程系”；

当系号为 3 时，显示 “物联网系”。

SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

搜索CASE函数

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

按从上到下的书写顺序计算每个 WHEN 子句的布尔表达式。

返回第一个取值为 TRUE 的布尔表达式所对应的结果表达式的值。

如果没有取值为 TRUE 的布尔表达式，

则当指定了 ELSE 子句时 , 返回 ELSE 子句中指定的结果；如果没有指定 ELSE 子句，则返回 NULL 。

例57用搜索CASE来做：

 

SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

例58.

查询“ M01F011” 号课程的考试情况，列出学号、课程号和成绩，同时将百

分制成绩显示为等级。

SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

统计每个班男生和女生的数量各是多少，

统计结果的表头为，班号，男生数量，女生数量。

SELECT 班号,
COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号

判断成绩的等级，

85~100 为“优”， 70~84 为“良”， 60~69 为“及格”， 60以下为“不及格”，并统计每一等级的人数。

SELECT
    CASE
        WHEN GRADE BETWEEN 85 AND 100 THEN '优'
        WHEN GRADE BETWEEN 70 AND 84 THEN '良'
        WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
        ELSE '不及格'
    END 等级, COUNT(*) 人数
FROM SC
GROUP BY
    CASE
        WHEN GRADE BETWEEN 85 AND 100 THEN '优'
        WHEN GRADE BETWEEN 70 AND 84 THEN '良'
        WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
        ELSE '不及格'
    END

子查询

一个 SELECT 语句称为一个查询块。

子查询是一个 SELECT 查询，它 嵌套 在 SELECT 、 INSERT 、 UPDATE 、 DELETE 语句的 WHERE 或 HAVING 子句内，或其它子查询中。

子查询的 SELECT 查询使用 圆括号 括起来。

子查询语句可以出现在任何能够使用表达式的地方，通常情况下，子查询语句用在外层查询的 WHERE 子句、 HAVING 子句或 EXISTS 中 。 

例65.查询所有的班名和系名。

SELECT 班名,系名
FROM 班级表 bjb JOIN
(SELECT 系号,系名 FROM 系表) AS xb
ON bjb.系号=xb.系号

 使用子查询进行基于集合的测试的语句的一般格式为：

列名 [NOT] IN ( 子查询 )

例66.查询与张三在同一个班学习的学生的姓名和所在的班号。

SELECT 姓名, 班号 FROM 学生表
WHERE 班号 IN
(SELECT 班号 FROM 学生表
WHERE 姓名 = '张三' )
AND 姓名 != '张三’

例67. 查询成绩为大于90分的学生的学号、姓名

不相关子查询
SELECT 学号, 姓名 FROM 学生表
WHERE 学号 IN
( SELECT 学号 FROM 成绩表
WHERE 成绩 > 90 ) 
相关子查询
SELECT 学号, 姓名 FROM 学生表
WHERE 学号 IN
( SELECT 学号 FROM 成绩表
WHERE 学生表.学号=学号 AND 成绩 > 90 )

例68. 查询选修了“操作系统”课程的学生的学号、姓名。

SELECT 学号, 姓名 FROM 学生表
WHERE 学号 IN
(SELECT 学号 FROM 成绩表
WHERE 课程号 IN
(SELECT 课程号 FROM 课程表
WHERE 课程名 = ‘操作系统') )

例69 查询选修了操作系统课程的学生的选课门数和平均成绩。

SELECT 学号, COUNT(*) 选课门数, AVG(成绩) 平均成绩
FROM 成绩表 WHERE 学号 IN
(SELECT 学号 FROM 成绩表 g JOIN 课程表 C
ON C.课程号 = g.课程号
WHERE 课程名 = '操作系统')
GROUP BY 学号

SELECT 学号, COUNT(*) 选课门数,
AVG(成绩) 平均成绩
FROM 成绩表 g JOIN 课程表 C
ON C.课程号 = g.课程号
WHERE 课程名 = '操作系统'
GROUP BY 学号

当查询列表中的列来自多张表时，这种形式的查询无法用子查询来实现，必须通 过连接的形式来完成。

使用子查询进行比较测试

使用子查询进行比较测试时，通过比较运算符（ = 、 <> 、 < 、 > 、 <= 、 <=），将一个表达式的值与子查询返回的值进行比较。

如果比较运算的结果为真，则比较测试返回 True 。

使用子查询进行比较测试的形式如下：

WHERE 表达式 比较运算符 （子查询）

要求子查询语句必须返回的是 单值 。

例70 查询选了M01F011课程且成绩高于此课程的平均成绩的学生的学号和成绩 。

SELECT 学号 , 成绩 FROM 成绩表
WHERE 课程号 = 'M01F011' AND 成绩 >
(SELECT AVG(成绩) FROM 成绩表
WHERE 课程号 = 'M01F011’)

例71.查询成绩最高的学生的学号。

SELECT 学号 FROM 成绩表
WHERE 成绩 =
(SELECT MAX(成绩) FROM 成绩表)

 使用子查询进行存在性测试

通常用 EXISTS 谓词，其形式如下：

WHERE [NOT] EXISTS （子查询）

带 EXISTS 谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。

EXISTS ：当子查询中有满足条件的数据时，返回真值，否则返回假值。

NOT EXISTS ：当子查询中有满足条件的数据时，返回假值；否则返回真值。

注 1 ：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外

层结果数决定。

注 2: 由于 EXISTS 的子查询只能返回真或假值，因此在这里给出列名无意义。所

以在有 EXISTS 的子查询中，其目标列表达式通常都用 * 。

例72 查询选修了M01F011课程的学生姓名。

SELECT 姓名 FROM 学生表
WHERE EXISTS
(SELECT * FROM 成绩表
WHERE 学号 = 学生表.学号
AND 课程号 = 'M01F011')

例73 查询没有选修M01F011课程的学生姓名和班号。

用多表连接实现（数据有误）

SELECT DISTINCT 姓名, 班号
FROM 学生表 S JOIN 成绩表 g
ON S.学号 = g.学号
WHERE 课程号 != 'M01F011'

用不相关子查询实现

在子查询中否定（数据有误）

SELECT 姓名, 班号 FROM 学生表
WHERE 学号 IN (
SELECT 学号 FROM 成绩表
WHERE 课程号 != 'M01F011')

在外层查询中否定

SELECT 姓名,班号 FROM 学生表
WHERE 学号 NOT IN (
SELECT 学号 FROM 成绩表
WHERE 课程号 = 'M01F011')

用相关子查询实现

在子查询中否定（数据有误）

SELECT 姓名,班号 FROM 学生表
WHERE EXISTS (
SELECT * FROM 成绩表
WHERE 学号 = 学生表.学号
AND 课程号 != 'M01F011')

 在外层查询中否定

SELECT 姓名,班号 FROM 学生表
WHERE NOT EXISTS (
SELECT * FROM 成绩表
WHERE 学号 = 学生表.学号
AND 课程号 = 'M01F011')

另一种相关子查询实现

在子查询中否定（数据有误）

SELECT 姓名, 班号 FROM 学生表 s
WHERE 学号 IN (
SELECT 学号 FROM 成绩表 g
WHERE s.学号=学号 AND 课程号 != 'M01F011')

在外层查询中否定

SELECT 姓名, 班号 FROM 学生表 s
WHERE 'M01F011' NOT IN (
SELECT 课程号 FROM 成绩表 g
WHERE s.学号=学号)

结论：对于否定条件的查询都应该使用子查询来实现，而且应该将否定放在外层。

例74 查询21226P班没有选修M01F011课程的学生的姓名和性别。

SELECT 姓名, 性别 FROM 学生表
WHERE 学号 NOT IN
(SELECT 学号 FROM 成绩表 WHERE
课程号 = 'M01F011')
AND 班号 = '21226P'

派生表

将子查询作为一个表来处理。

是在外部查询的 FROM 子句中定义的。存在范围为定义它的外部查询，只要外部查询一结束，派生表就不存在了。

定义派生表的查询语句要写在一对圆括号内，后面跟着 AS 子句和派生表的名称。每个派生表必须有自己的别名。

IN和EXISTS、NOT IN和NOT EXISTS 

IN 和 EXISTS

如果查询的两个表大小相当，那么用 IN 和 EXISTS 差别不大；如果两个表中一个

较小一个较大，则子查询表大的用 EXISTS ，子查询表小的用 IN 。

NOT IN 和 NOT EXISTS

无论哪个表大，用 NOT EXISTS 都比 NOT IN 要快，而且更准确。

CREATE TABLE #t1(c1 int,c2 int);

CREATE TABLE #t2(c1 int,c2 int);

INSERT INTO #t1 VALUES (1,2);

INSERT INTO #t1 VALUES (1,3);

INSERT INTO #t2 VALUES (1,2);

INSERT INTO #t2 VALUES (1,null);

SELECT * FROM #t1 WHERE c2 NOT IN( SELECT c2 FROM #t2)

-- 执行结果：无

SELECT * FROM #t1 WHERE NOT EXISTS( SELECT * FROM #t2 WHERE

#t2.c2=#t1.c2)

-- 执行结果： 1 3

NOT IN 出现了不期望的结果集，存在逻辑错误。如果子查询中返回的任意一条记

录含有空值，则查询将不返回任何记录。

 

插入数据

插入单行记录的 INSERT 语句的格式为：

INSERT INTO < 表名 > [(< 列名表 >)] VALUES ( 值表 )

功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序 [ 或列名表顺

序 ] 赋给对应列名。

值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。

如果 < 表名 >后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值 ( 可以为空 )

插入多行记录的 INSERT 语句的格式为：

INSERT INTO < 表名 > [(< 列名表 >)] VALUES ( 值表 ) [ ， …n]

插入来自其它表的记录

情况一：目标表已建好

INSERT INTO < 表名 > [(< 列名表 >)] 子查询

限制来自其它表的记录数量

情况二：目标表未创建

SELECT … INTO 新表名 FROM …

例1 将一个新生插入到学生表中，

学号：16001，姓名：张三丰，性别：男，出生日期“1996-01-30”，班级号是“31231P”。

INSERT INTO 学生表
VALUES ('16001', '张三丰', '男', '1996-01-30', '31231P')

例2 在学生表中插入一条新记录，

学号：16002，姓名：裘伯君，性别：男，出生日期“1995-02-14” 。

INSERT INTO 学生表 (学号,姓名, 性别,出生日期)
VALUES ('16002', '裘伯君', '男', '1995-02-14')

实际插入的值为：('16002', '裘伯君', '男', '1995-02-14‘,NULL)

例3 向学生表中插入两条记录，

学号分别为“ 16003 ”，“ 16004”，姓名分别为 “张三”，“李四”，性别分别为“男”，“女”，

出生日期分别为“1996-05- 12 ”，“ 1996-12-12 ”，班号都是“ 31231P ”。

INSERT INTO 学生表

VALUES ('16003', ' 张三 ', ' 男 ', '1996-05-12', '31231P'),

('16004', ' 李四 ', ' 女 ', '1996-12-12', '31231P')

例6

INSERT INTO bak_学生表 SELECT 学号,姓名 FROM 学生表

例7

INSERT INTO bak_学生表 SELECT TOP 10 学号,姓名 FROM 学生表

 更新数据

用 UPDATE 语句实现。格式：

UPDATE < 表名 > SET < 列名 = 表达式 > [,… n] [WHERE < 更新条件 >]

< 表名 > 需要修改数据的表的名称。

SET 子句指定要修改的列，表达式指定要修改后的新值。

WHERE 子句用于指定只修改表中满足条件的记录的相应列值。

(1)基于本表条件的更新

(2) 基于其他表条件的更新

例1. 将班级表中所有记录的人数改为50。

UPDATE 班级表 SET 人数= 50
UPDATE 班级表 SET 人数 = DEFAULT（更改为默认值）

例2. 将班级表中所有记录的人数改为40，系号改为1。

UPDATE 班级表 SET 人数= 40，系号=1

例3.将班级表中班号以数字2开始的班级的系号改为2。

UPDATE 班级表 SET 系号 = 2
WHERE 班号 LIKE ‘2%'
更新限制数量的记录
UPDATE TOP(2) 班级表 SET 系号 = 2
WHERE 班号 LIKE ‘2%’

例4.将成绩表中不及格的成绩加20分。

UPDATE 成绩表 SET 成绩=成绩+20
WHERE 成绩 <60

例5.将操作系统课的所有成绩加5分。

(1) 用子查询实现

UPDATE 成绩表 SET 成绩 = 成绩 + 5
WHERE 课程号 IN
(SELECT 课程号 FROM 课程表
WHERE 课程名 = '操作系统' )

2) 用多表连接实现

UPDATE 成绩表 SET 成绩 = 成绩 + 5
FROM 课程表 c JOIN 成绩表 g
ON g.课程号 = c.课程号 WHERE 课程名 = '操作系统'

例6. 将学分最低的课程的学分加2分。

UPDATE 课程表 SET 学分=学分+ 2
WHERE 学分=
(SELECT MIN(学分) FROM课程表)

例7. 修改全体学生的操作系统考试成绩，

修改规则如下：

成绩在 70~79 分的，加 5 分；成绩在 60~69 分的，加 10 分；

成绩在 60 以下分的，加 15 分；其他成绩不变。

UPDATE 成绩表 SET 成绩 = 成绩 +
CASE
WHEN 成绩 BETWEEN 70 AND 79 THEN 5
WHEN 成绩 BETWEEN 60 AND 69 THEN 10
WHEN 成绩 < 60 THEN 15
ELSE 0
END
FROM 课程表 c JOIN 成绩表 g ON c.课程号 = g.课程号
WHERE 课程名 = '操作系统'

删除数据

用 DELETE 语句实现。格式：

DELETE [ FROM ] < 表名 > [WHERE < 删除条件 >

< 表名 > 说明了要删除哪个表中的数据。

WHERE 子句说明只删除表中满足条件的记录。

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同，二者

均删除表中的全部行。但：

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。

TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事

务日志中记录页的释放。

锁的粒度不同。 DELETE 使用行锁， TRUNCATE 使用表锁锁定表和页。

TRUNCATE TABLE 语句不会使表中存在空页， DELETE 执行后表仍会有空页。

TRUNCATE TABLE 命令删除的数据是不可以恢复的； DELETE 命令删除的数据

将存储在系统回滚段中，需要的时候，数据可以回滚恢复。

TRUNCATE , DELETE , DROP 比较：

TRUNCATE TABLE ：删除内容删除定义，释放空间。

DELETE TABLE ：删除内容不删除定义，不释放空间。

DROP TABLE ：删除内容和定义，释放空间。

有条件删除 ( 只能用 DELETE)

(1) 基于本表条件的删除。

(2) 基于其他表条件的删除。

例1. 删除所有学生的选课记录。

DELETE FROM 成绩表

例2．删除所有不及格学生的修课记录。

DELETE FROM 成绩表 WHERE 成绩 < 60

删除限制数量的记录。

DELETE TOP(2) FROM 成绩表 WHERE 成绩 < 60

例3．删除电路基础不及格学生的修课记录。

(1) 用子查询实现

DELETE FROM 成绩表
WHERE 成绩 < 60 AND 课程号 IN (
SELECT 课程号 FROM 课程表
WHERE 课程名 = '电路基础' )

(2)用多表连接实现

DELETE FROM 成绩表
FROM 课程表 c JOIN 成绩表 g ON c.课程号 = g.课程号
WHERE 课程名 = '电路基础' AND 成绩 < 60

比较记录

MERGE语句通常用于比较两个表的记录，并根据结果进行同步，

同步的方式可以是将源表与目标表比较，根据差异在目标表中插入、更新或删除记录。

MERGE 目标表 AS target USING 源表
AS source(字段列表) ON 连接条件表达式
WHEN MATCHED THEN
插入、更新、删除语句
WHEN NOT MATCHED THEN
插入、更新、删除语句;

例1

将班级表中系号为 1的班级记录复制到尚未创建的部分班级表中。再将部分班级表的记录与班级表的记录比较，

如果班号存在，那么设置系号为 4 ；

如果班号不存在，那么将班级表中记录插入部分班级表中。

SELECT 班号, 班名,系号 INTO 部分班级表 FROM 班级表
WHERE 系号 =1
MERGE 部分班级表 target
USING 班级表 source ON target.班号 = source.班号
WHEN MATCHED THEN
UPDATE SET 系号=4
WHEN NOT MATCHED THEN
INSERT(班号, 班名,系号) VALUES (source.班号,source.班名,source.系号);

例2

将部分班级表的记录与班级表的记录比较，

如果班号存在，那么删除部分班级表对应记录；

如果班号不存在，那么将班级表中记录插入部分班级表中。

MERGE 部分班级表 AS target 
USING (SELECT 班号, 班名,系号 FROM 班级表) AS
source(班号, 班名,系号) 
ON target.班号 = source.班号
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (班号, 班名,系号) 
VALUES(source.班号,source.班名,source.系号);

SQL Server数据库迁移的几种方式

方法一：脚本法

选中要转移的数据库，右键 → 任务 → 生成脚本 ……

方法二：备份 / 还原法

选中要转移的数据库，右键 → 任务 → 备份 …… ；将备份文件可以 copy 到任何地方

，需要还原时选中本地已有的数据库 ( 没有需要新建 ) 右键 → 任务 → 还原 …… 。如

果在已建好的数据库上还原，须点击“选项”页， 勾上 覆盖现有数据库即可。

方法三：分离 / 附加法

首先分离数据库连接，然后将数据库文件 (.mdf 文件 )copy 到本地，然后右键 → 附

加 找到该 .mdf 文件即可。

方法四：导入 / 导出数据法

适用于 (1) 两个现有数据库之间数据复制 (2) 不同类型数据库之间数据的传递

右键 → 任务 → 导入数据 / 导出数据

练习

表单

Course[Cno Cname Credit Semester]

SELECT TOP (1000) [Cno]
      ,[Cname]
      ,[Credit]
      ,[Semester]
  FROM [db].[dbo].[Course]

SC[Sno Cno Grade]

SELECT TOP (1000) [Sno]
      ,[Cno]
      ,[Grade]
  FROM [db].[dbo].[SC]

 Student[Sno Sname Ssex Sage Sdept]

SELECT TOP (1000) [Sno]
      ,[Sname]
      ,[Ssex]
      ,[Sage]
      ,[Sdept]
  FROM [db].[dbo].[Student]

人才档案[编号 姓名 性别 出生日期 学历  政治面貌  民族 工资现状]

SELECT TOP (1000) [编号]
      ,[姓名]
      ,[性别]
      ,[出生日期]
      ,[学历]
      ,[政治面貌]
      ,[民族]
      ,[工资现状]
  FROM [db].[dbo].[人才档案]

题目

1. 用子查询实现如下查询:

(1)查询通信工程系成绩 80 分以上的学生学号和姓名。

(2)查询计算机系考试成绩最高的学生姓名。

(3)查询考试成绩比计算机系所有学生都低的学生姓名。

(4)查询年龄最大的男生的姓名和年龄。

(5)查询“C001”课程的考试成绩高于“C001”课程的平均成绩的学生的学号和“C001”课程成绩。

(6)查询没有选修“C003”课程的学生的姓名和所在系。

2. 创建一个新表，表名为 test，其结构为：(C1, C2, C3)，其中：

C1：整型，标识列。

C2：字符型，长度为 10 ，不允许空值，默认值为“B2”。

C3：字符型，长度为 10 ，允许空值。

试写出按行插入如下数据的语句(空白处表示空值)。

 

3. 将 “C001”课程的考试成绩加 10 分。

4. 将计算机系所有选修了“计算机文化学”课程的学生成绩加 10 分，分别用子查询和多表连接形式实现。

5. 修改全体学生“C001”课程的考试成绩，修改规则如下：

对通信工程系学生，成绩加 10 分；

对信息管理系学生，成绩加 5 分；

对其他系学生，成绩不变。

6. 删除修课成绩小于 50 分的学生的选课记录。

7. 删除信息管理系考试成绩小于 50 分的学生的该门课程的修课纪录，分别用子查询和多表 连接形式实现。

8．删除考试成绩最低的前 3 条记录。

9. 统计每个系男生和女生人数，分别写出下列两种统计结果的查询语句。

10. 统计人才档案表中各民族的人数，分为汉族和少数民族两类统计，统计结果如下：

 

 

参考答案

1.

Coming Soon

2.

Coming Soon

3.

Coming Soon

4.

Coming Soon

5.

Coming Soon

6.

Coming Soon

7.

Coming Soon

8.

Coming Soon

9.

Coming Soon

10.

Coming Soon