教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net 数据库技术 教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net.

Presentation on theme: "教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net 数据库技术 教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net."— Presentation transcript:

1 教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net
数据库技术 教 师：曾晓东 电 话：

2 第4章 数据操作 4.1 数据查询功能 4.2 数据更改功能

3 4.1 数据查询功能 4.1.1 查询语句的基本结构 4.1.2 简单查询 4.1.3 多表连接查询 4.1.4 子查询

4 查询语句基本格式 SELECT <目标列名序列> --需要哪些列 FROM <数据源> --来自于哪些表
[WHERE <检索条件>] 根据什么条件 [GROUP BY <分组依据列>] [HAVING <组提取条件>] [ORDER BY <排序依据列>]

5 4.1.2 简单查询 之 1. 选择表中若干列

6 1. 查询指定的列 查询表中用户感兴趣的部分属性列。 例1：查询全体学生的学号与姓名。 例2：查询全体学生的姓名、学号和所在系。
SELECT Sno，Sname FROM Student 例2：查询全体学生的姓名、学号和所在系。 SELECT Sname，Sno，Sdept FROM Student

7 2. 查询全部列 例3．查询全体学生的记录 SELECT Sno，Sname，Ssex, Sage, Sdept FROM Student
等价于： SELECT * FROM Student

8 3. 查询经过计算的列 例4．查询全体学生的姓名及其出生年份。 SELECT Sname，2010 - Sage FROM Student
例5．查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为‘出生年份’常量值。 SELECT Sname，‘出生年份’, 2010-Sage

9 改变列标题 列名 | 表达式 [ AS ] 列标题 列标题 ＝列名 | 表达式 语法： 或： 例：
SELECT Sname 姓名，‘Year of Birth’ 出生年份, Sage 年份， FROM Student

10 改变列标题 例：从公司管理数据库(COMPANYINFO)查询每个人的薪水降低30%信息。 USE COMPANYINFO GO
SELECT 姓名, '原薪水'=薪水, '现薪水'=薪水-薪水*0.3 FROM employee 或 SELECT 姓名,薪水 AS 原薪水,薪水-薪水*0.3 AS 现薪水

11 4.1.2 简单查询 之 2. 选择表中若干元组

12 1. 消除取值相同的记录 例6．在修课表中查询有哪些学生修了课程，要求列出学生的学号。 SELECT Sno FROM SC
结果中有重复的行。 用DISTINCT关键字可以去掉结果中的重复行。 DISTINCT关键字放在SELECT词的后边、目标列名序列的前边。 SELECT DISTINCT Sno FROM SC

13 2. 使用TOP关键字 SQL Server 2000提供了TOP关键字，让用户指定返回前面一定数量的数据。 语法格式：
SELECT [TOP n | TOP n PERCENT] 列名1[,列名2,…列名n] FROM 表名 其中: TOP n:表示返回最前面的n行，n表示返回的行数。 TOP n PERCENT:表示返回的前面的n%行。

14 示例 从northwind数据库的customers表中返回前面10行数据。 USE northwind GO
SELECT TOP 10 * FROM customers 从northwind数据库中的customers表中返回前10%的数据。 SELECT TOP 10 PERCENT * FROM customers

15 3. 查询满足条件的元组 查询条件 谓 词 比较运算符 =, >, >=, <, <=, <>（或!=）
谓 词 比较运算符 =, >, >=, <, <=, <>（或!=） NOT+比较运算符 确定范围 BETWEEN…AND, NOT BETWEEN…AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 逻辑谓词 AND, OR

16 比较大小 例7．查询计算机系全体学生的姓名。 SELECT Sname FROM Student WHERE Sdept = '计算机系'
例8．查询年龄在20岁以下的学生的姓名及年龄。 SELECT Sname, Sage FROM Student WHERE Sage < 20 例9．查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade < 60

17 确定范围 用BETWEEN…AND和NOT BETWEEN…AND
如果列或表达式的值在[不在]下限值和上限值范围内，则结果为True，表明此记录符合查询条件。

18 示例 例10．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。
SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23 例11．查询年龄不在20～23之间的学生姓名、所在系和年龄。 WHERE Sage NOT BETWEEN 20 AND 23

19 确定集合 使用IN运算符。 用来查找属性值属于指定集合的元组。 格式为： 列名 [ NOT ] IN （常量1, 常量2, … 常量n）
当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录； NOT IN：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录

20 示例 例12．查询信息系、数学系和计算机系学生的姓名和性别。 例13．查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。
SELECT Sname, Ssex FROM Student WHERE Sdept IN ('信息系', '数学系', '计算机系') 例13．查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。 WHERE Sdept NOT IN ('信息系', '数学系', '计算机系')

21 字符匹配 使用LIKE运算符 一般形式为： 列名 [NOT ] LIKE <匹配串> 匹配串中可包含如下四种通配符：
_：匹配任意一个字符； %：匹配0个或多个字符； [ ]：匹配[ ]中的任意一个字符； [ ^ ]：不匹配[ ]中的任意一个字符

22 示例 例14．查询姓‘张’的学生的详细信息。 SELECT * FROM Student WHERE Sname LIKE '张%'
例15．查询学生表中姓‘张’、‘李’和‘刘’的学生的情况。 WHERE Sname LIKE ' [张李刘]%' 例16．查询名字中第2个字为‘小’或‘大’的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE '_[小大]%'

23 示例（续） 例17．查询所有不姓“刘”的学生。 SELECT Sname FROM Student
WHERE Sname NOT LIKE '刘%‘ 例18．查询学号的最后一位不是2、3、5的学生情况。 SELECT * FROM Student WHERE Sno LIKE '%[^235]'

24 涉及空值的查询 空值（NULL）在数据库中表示不确定的值。
例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。 判断某个值是否为NULL值，不能使用普通的比较运算符。 判断取值为空的语句格式为： 列名 IS NULL 判断取值不为空的语句格式为： 列名 IS NOT NULL

25 示例 例19．查询无考试成绩的学生的学号和相应的课程号。 SELECT Sno, Cno FROM SC
WHERE Grade IS NULL 例20．查询所有有考试成绩的学生的学号和课程号。 WHERE Grade IS NOT NULL

26 多重条件查询 在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。
用AND连接的条件表示必须全部满足所有的条件的结果才为True； 用OR连接的条件表示只要满足其中一个条件结果即为True。 例21．查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept = 'CS' AND Sage < 20

27 示例 查询所有在美国加利福尼亚州的出版社。 USE pubs GO SELECT pub_id AS 出版社代号,
pub_name AS 出版社名称, city AS 城市, state AS 州, country AS 国家 FROM publishers WHERE country='usa' AND state='ca'

28 示例 查询所有au_id满足前2个字符为“72”，第4个字符为“-”的作家的姓名和电话号码。 USE pubs GO
SELECT au_lname, au_fname, phone, au_id FROM authors WHERE au_id like '72_-%'

29 4.1.2 简单查询 之 3. 对查询结果进行排序

30 可对查询结果进行排序。 排序子句为： 对查询结果进行排序 说明：按<列名>进行升序（ASC）或降序（DESC）排序。
ORDER BY <列名> [ASC | DESC ] [,<列名> … ] 说明：按<列名>进行升序（ASC）或降序（DESC）排序。

31 示例 例22．将学生按年龄的升序排序。 SELECT * FROM Student ORDER BY Sage
SELECT Sno, Grade FROM SC WHERE Cno='c02' ORDER BY Grade DESC 例24．查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC

32 示例 查询titles中各类书的销售利润和书号，并按照各种书的销售利润降序排列。 USE pubs GO
SELECT title_id, price*ytd_sales AS profit FROM titles ORDER BY profit DESC

33 示例 查询titles中各类书的书号、价格、年销售量和销售利润，并用年销售量和书价进行升序排列。 USE pubs GO
SELECT title_id,price, ytd_sales, price*ytd_sales AS profit FROM titles ORDER BY ytd_sales, price

34 4.1.2 简单查询 之 4. 使用计算函数汇总数据

35 使用计算函数汇总数据 SQL提供的计算函数有： 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。
COUNT（[DISTINCT] <列名> ）：统计本列列值个数； SUM（[DISTINCT] <列名> ）：计算列值总和； AVG（[DISTINCT] <列名> ）：计算列值平均值； MAX（[DISTINCT] <列名> ）：求列值最大值； MIN（[DISTINCT] <列名> ）：求列值最小值。 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。

36 示例 例25．统计学生总人数。 SELECT COUNT(*) FROM Student 例26．统计选修了课程的学生的人数。
SELECT COUNT (DISTINCT Sno) FROM SC 例27 ．计算 号学生的考试总成绩之和。 SELECT SUM(Grade) FROM SC WHERE Sno = ' '

37 示例（续） 注意：计算函数不能出现在WHERE子句中 例28．计算’C01’号课程学生的考试平均成绩。
SELECT AVG(Grade) FROM SC WHERE Cno='C01' 例29．查询选修了’C01’号课程的学生的最高分和最低分。 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno='C01' 注意：计算函数不能出现在WHERE子句中

38 4.1.2 简单查询 之 5. 对查询结果进行分组计算

39 对查询结果进行分组计算 作用：可以控制计算的级别：对全表还是对一组。 目的：细化计算函数的作用对象。 分组语句的一般形式：
[GROUP BY <分组条件>] [HAVING <组过滤条件>]

40 1. 使用GROUP BY 例30．统计每门课程的选课人数，列出课程号和人数。
SELECT Cno as 课程号, COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno 对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。

41 Sno Cno Grade 951201 C01 80 C02 78 951202 90 952103 88 85 C03 91 C04 74 Sno Cno Grade 951201 C01 80 951202 90 952103 85 C02 78 88 C03 91 C04 74 Cno Count(Sno) C01 3 C02 2 C03 1 C04

42 例31．查询每名学生的选课门数和平均成绩。 SELECT Sno as 学号, COUNT(*) as 选课门数,
AVG(Grade) as 平均成绩 FROM SC GROUP BY Sno

43 2. 使用HAVING HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。
例32．查询修了3门以上课程的学生的学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3

44 示例 例33．查询修课门数等于或大于4门的学生的平均成绩和选课门数。 SELECT Sno, AVG(Grade) 平均成绩,
COUNT(*) 修课门数 FROM SC GROUP BY Sno HAVING COUNT(*) >= 4

45 示例 在所有价格超过10美元的书中，查询所有平均价格超过18美元的书的种类和平均价格。 USE pubs GO
SELECT type , AVG(price) AS avg_price FROM titles WHERE price>10 GROUP BY type HAVING AVG(price)>18

46 4.1.3 多表连接查询 若一个查询同时涉及两个或两个以上的表，则称之为连接查询。 连接查询是关系数据库中最主要的查询
连接查询包括内连接、外连接和交叉连接等。

47 连接基础知识 连接查询中用于连接两个表的条件称为连接条件或连接谓词。 一般格式为：
[<表名1.>][<列名1>] <比较运算符> [<表名2.>][<列名2>] 必须是可比的

48 内连接 SQL-92 内连接语法如下： SELECT … FROM 表名 [INNER] JOIN 被连接表 ON 连接条件

49 执行连接操作的过程： 首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。 表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， … 重复这个过程，直到表1中的全部元组都处理完毕为止。

50 例36.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = '计算机系'

51 SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno
例37. 查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。 SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = '信息系' AND Cname = 'VB'

52 例38．查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在的系。
SELECT Sname, Sdept FROM Student S JOIN SC ON S.Sno = SC. Sno JOIN Course C ON C.Cno = SC.cno WHERE Cname = 'VB'

53 自连接 为特殊的内连接 相互连接的表物理上为同一张表。 必须为两个表取别名，使之在逻辑上成为两个表。

54 例39. 查询与刘晨在同一个系学习的学生的姓名和所在的系。
SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = ‘刘晨’ AND S2.Sname != ‘刘晨’

55 外连接 只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。 ANSI方式的外连接的语法格式为：
FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON <连接条件> theta方式的外连接的语法格式为： 左外连接： FROM 表1, 表2 WHERE [表1.]列名(+) ＝ [表2.]列名 右外连接： FROM 表1, 表2 WHERE [表1.]列名＝ [表2.]列名(+)

56 例40. 查询学生的修课情况，包括修了课程的学生和没有修课的学生。
SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno

57 示例 从titles和titleauthor表中查询书的书号、书名、作者号和价格。 USE pubs GO SELECT
titles.title_id AS 书号, title AS 书名, au_id AS 作者号, price AS 价格 FROM titles INNER JOIN titleauthor ON titles.title_id=titleauthor.title_id

58 示例 从titles、authors和titleauthor表中查询书的书号、书名、作者号和作者名。 USE pubs GO
SELECT titles.title_id AS 书号, title AS 书名, authors.au_id AS 作者号, au_lname AS 作者名 FROM titles JOIN titleauthor ON titles.title_id=titleauthor.title_id JOIN authors ON authors.au_id=titleauthor.au_id

59 示例 authors 表与 titleauthor 表在各表的 au_id 列上相匹配。无论作品出版或未出版，所有作者均出现在结果集中。
USE pubs GO SELECT authors.au_lname AS 作者名, titleauthor.title_id AS 书号 FROM authors LEFT OUTER JOIN titleauthor ON authors.au_id = titleauthor.au_id

60 示例 在 titles 和 publishers 表之间的右向外联接将包括所有的出版商，甚至包括那些在 titles 表中没有书名的出版商。
SELECT titles.title_id AS 书号, titles.title AS 书名, publishers.pub_name AS 出版商 FROM titles RIGHT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id

61 4.1.4 子查询 在SQL语言中，一个SELECT－FROM－WHERE语句称为一个查询块。
子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句的 WHERE 或 HAVING 子句内，或其它子查询中 子查询的 SELECT 查询总是使用圆括号括起来。

62 使用子查询进行基于集合的测试的语句的一般格式为：
列名 [NOT] IN (子查询)

63 示例 例41. 查询与刘晨在同一个系的学生。 SELECT Sno, Sname, Sdept FROM Student
WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname = ‘刘晨’ ) AND Sname != ‘刘晨’ ② ①

64 示例 例42. 查询成绩为大于90分的学生的学号、姓名。 SELECT Sno, Sname FROM Student
WHERE Sno IN ( SELECT Sno FROM SC WHERE Grade > 90 ) ② ①

65 例43. 查询选修了“数据库基础”课程的学生的学号、姓名。
SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = '数据库基础') )

66 使用子查询进行比较测试 带比较运算符的子查询指父查询与子查询之间用比较运算符连接，
当用户能确切知道内层查询返回的是单值时，可用>、<、=、>=、<=、<>运算符。

67 例44. 查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。
SELECT Sno , Grade FROM SC WHERE Cno = 'c02‘ and Grade > ( SELECT AVG(Grade) from SC WHERE Cno = 'c02')

68 使用子查询进行存在性测试 一般使用EXISTS谓词。

69 例45.查询选修了‘c01’号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01')

70 注意 注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。
注2: 由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。

71 上句的处理过程 1.找外层表Student表的第一行，根据其Sno值处理内层查询 2.由外层的值与内层的结果比较，由此决定外层条件的真、假
…行。

72 例46.查询没有选修‘c01’号课程的学生姓名和所在系。
SELECT Sname, Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01')

73 习题 写出实现下列操作的Transact-SQL语句 从pubs数据库的authors表中返回所有的数据。
查询pubs数据库的authors表中的作者的姓（au_lname）、名（au_fname）和电话号码（phone）。 从Northwind 数据库的Products表中检索出所有单位价格（UnitPrice）超过50美元的货物名称（Product Name ）、货物代号（ProductID）以及每单位重量（QuantityPerUnit）。 查询在Northwind数据库的Employees表中以字母A作Firstname第一个字母的雇员的Firstname和Lastname。

74 4.2 数据更改功能 4.2.1 插入数据 4.2.2 更新数据 4.2.3 删除数据

75 4.2.1 插入数据 INSERT INTO <表名> [(<列名表>)] 插入单行记录的INSERT语句的格式为：
VALUES （值表） 功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序[或列名表顺序]赋给对应列名。

76 注意 值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。
如果<表名>后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。

77 示例 例1．将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中。 例2．在SC表中插入一新记录，成绩暂缺。
INSERT INTO Student VALUES (' ', '陈冬', '男', 18, '信息系') 例2．在SC表中插入一新记录，成绩暂缺。 INSERT INTO SC(Sno, Cno, XKLB) VALUES(‘ ’, ‘c01’, ‘必修') 实际插入的值为： (‘ ’, ‘c01’ ，NULL ，‘必修')

78 示例 在COMPANYINFO数据库中的product 表中插入如下记录，如表所示。 产品ID 产品名 类别ID 单价 库存量（件）
1000 电风扇 1 240 234 1001 冰箱 1850 600 1002 电视机 4560 1003 牙膏 2 2.5 100 1004 牙刷 3

79 示例 USE companyinfo GO INSERT product VALUES (1000,'电风扇',1,240,234)

80 4.2.2 更新数据 用UPDATE语句实现。 格式： UPDATE <表名>
SET <列名＝表达式> [,… n] [WHERE <更新条件>]

81 无条件更新 例1. 将所有学生的年龄加1。 UPDATE Student SET Sage = Sage + 1

82 有条件更新 1. 基于本表条件的更新 例2. 将‘9512101’学生的年龄改为21岁
UPDATE Student SET Sage = 21 WHERE Sno = ' '

83 2. 基于其他表条件的更新 例3：将计算机系全体学生的成绩加5分。 （1）用子查询实现
UPDATE SC SET Grade = Grade + 5 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机系' ) （2）用多表连接实现 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = '计算机系'

84 示例 在companyinfo数据库的product表中，由于国家政策的调价，将所有产品的单价上调10%。 USE companyinfo
GO UPDATE product SET 单价=单价*(1+0.1)

85 DELETE [ FROM ] <表名> [WHERE <删除条件>
4.2.3 删除数据 用DELETE语句实现 格式： DELETE [ FROM ] <表名> [WHERE <删除条件>

86 无条件删除 例1. 删除所有学生的选课记录。 DELETE FROM SC

87 有条件删除 （1）基于本表条件的删除。 例2．删除所有不及格学生的修课记录。 DELETE FROM SC
WHERE Grade < 60

88 基于其他表条件的删除 例3．删除计算机系不及格学生的修课记录。 （1）用子查询实现 （2）用多表连接实现 DELETE FROM SC
WHERE Grade < 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = ‘计算机系’ ) （2）用多表连接实现 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = ‘计算机系’AND Grade < 60