12009-7-28 Information College · ChangJun
第 7章 表的操作
22009-7-28 Information College · ChangJun
[本章概要 ]
7.1 表数据的增加 —— 插入记录
7.2 表数据的删除 —— 删除记录
7.3 表数据的修改 —— 更新记录
7.4 表数据的检索 —— 查询记录
7.5 视图
32009-7-28 Information College · ChangJun
7.1 表数据的增加 ——
插入记录
7.1.1 使用企业管理器
7.1.2 使用 Transact-SQL语言（ DML）
将新行添加到表。
语法：
INSERT [INTO] <表名 > [（ <列名表 >） ]
VALUES （值列表）
其中,<列名表 >中的列名必须是表定义中有的列名，值列表中的值可以是常量也可以是 NULL
值，各值之间用逗号分隔。
42009-7-28 Information College · ChangJun
示例
1、使用简单的 INSERT
例 1．将新生记录（ 95020，陈冬，男，信息系,18岁）插入到 S表中。
INSERT INTO Student VALUES
(‘9521105’,‘ 陈冬’,'男 ',18,'信息系 ')
52009-7-28 Information College · ChangJun
2、插入与列顺序不同的数据例 2．将新课程,cname为“数据库基础”、
cno为,c04”,semeter为 6,ccredit为 6的记录插入 C表中。
INSERT INTO C(cname,Cno,semester，
ccredit)
VALUES(‘ 数据库基础 ’,‘c04’,6,6)
注：对于例 2，由于插入与列顺序不同的数据，
因此此时必须列出列名。
62009-7-28 Information College · ChangJun
3、插入值少于列个数的数据例 3．在 SC表中插入一新记录，成绩暂缺。
INSERT INTO SC(Sno,Cno)
VALUES('9521105','c01')
注：对于例 3，由于提供的值个数与表中的列个数不一致，因此此时必须列出列名。而且 SC
中的 Grade必须允许为 NULL。
此句实际插入的值为,('9521105','c01'，
NULL)
72009-7-28 Information College · ChangJun
7.2 表数据的删除 ——
删除记录
7.2.1 使用企业管理器
7.2.2 使用 Transac-SQL语言（ DML）
当确定不再需要某些记录时，就可以用删除语句 。
DELETE [ FROM ] <表名 > [WHERE <删除条件 >]
其中,<表名 >说明了要删除哪个表中的数据。
WHERE子句说明要删除表中的哪些记录 ―― 只删除满足 WHERE条件的记录。如果省略 WHERE子句，则是无条件删除，表示要删除表中的全部记录。
82009-7-28 Information College · ChangJun
示例
1、无条件删除无条件删除是删除表中全部数据，但保留表的结构。
例 1．删除所有学生的选课记录。
DELETE FROM SC -- SC成空表
2、有条件删除
（ 1）基于本表条件的删除。
例 2．删除所有不及格学生的修课记录。
DELETE FROM SC WHERE Grade < 60
（ 2）基于其他表条件的删除 （学过查询后再讲）
92009-7-28 Information College · ChangJun
7.3 表数据的修改
—— 更新记录
7.3.1 使用企业管理器
7.3.2 使用 Transac-SQL语言（ DML）
更改表中的现有数据语法：
UPDATE <表名 > SET <列名＝表达式 > [,… n]
[WHERE <更新条件 >]
其中,<表名 >给出了需要修改数据的表的名称。 SET
子句指定要修改的列，表达式指定修改后的新值。
WHERE子句用于指定需要修改表中的哪些记录。如果省略 WHERE子句，则是无条件更新，表示要修改 SET
中指定的列的全部值。
102009-7-28 Information College · ChangJun
示例
1．无条件更新例 1．将所有学生的年龄加 1。
UPDATE Student SET Sage = Sage + 1
2．有条件更新
（ 1）基于本表条件的更新。
例 2．将‘ 9512101’ 学生的年龄改为 21岁。
UPDATE Student SET Sage = 21
WHERE Sno = '9512101'
（ 2）基于其他表条件的更新 （学过查询后再讲）
112009-7-28 Information College · ChangJun
7.4 表数据的检索
—— 查询记录
7.4.1 使用企业管理器
7.4.2 使用 Transac-SQL语言（ DML）
从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。
122009-7-28 Information College · ChangJun
7.4 表数据的检索
—— 查询记录
7.4.1 使用企业管理器
7.4.2 使用 Transac-SQL语言（ DML）
查询语句是数据库使用中 最基本 和 最重要的语句之一。
其功能是从数据库中检索满足条件的数据。
查询的数据源可以来自一张表，也可以来自多张表甚至视图。
SELECT语句还可以对查询的结果进行排序、
汇总等。
132009-7-28 Information College · ChangJun
查询语句的基本结构：
SELECT <目标列名序列 > -- 需要哪些列
FROM <数据源 > -- 来自于哪些表
[WHERE <检索条件表达式 >] -- 根据什么条件
[GROUP BY <分组依据列 >]
[HAVING <组提取条件 >]
[ORDER BY <排序依据列 >]
142009-7-28 Information College · ChangJun
一、简单查询
1、选择表中若干列
（ 1）查询指定的列在很多情况下，用户可能只对表中的一部分属性列感兴趣，这时可通过在
SELECT子句的 <目标列名序列
>中指定要查询的列来实现。
例 1,查询全体学生的学号与姓名
SELECT Sno,Sname FROM
Student
Sno sname
9512101 李勇
9512102 刘晨
9512103 王敏
9521101 张立
9521102 吴宾
9521103 张海
9531101 钱小平
9531102 王大力结果为,
152009-7-28 Information College · ChangJun
（ 2）如果要查询表中的全部列例 3．查询全体学生的记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
等价于,SELECT * FROM Student
Sno Sname Ssex Sage Sdept
9512101 李勇 男 19 计算机系
9512102 刘晨 男 20 计算机系
9512103 王敏 女 20 计算机系
9521101 张立 男 22 信息系
9521102 吴宾 女 21 信息系
9521103 张海 男 20 信息系
9531101 钱小平 女 18 数学系
9531102 王大力 男 19 数学系结果为,
162009-7-28 Information College · ChangJun
（ 3）查询经过计算的列
SELECT子句中的 <目标列名序列 >可以是表中存在的属性列，也可以是表达式、
常量或者函数例 4．查询全体学生的姓名及其出生年份
SELECT Sname，
2002 - Sage FROM Student
结果为,
Sname （无列名 )
李勇 1982
刘晨 1981
王敏 1981
张立 1979
吴宾 1980
张海 1981
钱小平 1983
王大力 1982
172009-7-28 Information College · ChangJun
例 5．查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为‘ Year of Birth’ 常量值。
SELECT Sname，‘ Year of Birth’,
2002-Sage
FROM Student
查询结果,
Sname （无列名 ) （无列名 )
李勇 Year of Birth 1982
刘晨 Year of Birth 1981
王敏 Year of Birth 1981
张立 Year of Birth 1979
吴宾 Year of Birth 1980
张海 Year of Birth 1981
钱小平 Year of Birth 1983
王大力 Year of Birth 1982
182009-7-28 Information College · ChangJun
经过计算的列、函数的列和常量列的显示结果都没有列标题，通过指定列的别名可以改变查询结果的列标题。
改变列标题的语法格式为：
列名 | 表达式 [ AS ] 列标题或：列标题 ＝列名 | 表达式例如，对于例 5可写成：
SELECT Sname 姓名，‘ Year of Birth’ 出生年份,
2002-Sage 年份，
FROM Student
结果为：
姓名 出生年份 年份李勇 Year of Birth 1982
刘晨 Year of Birth 1981
王敏 Year of Birth 1981
张立 Year of Birth 1979
吴宾 Year of Birth 1980
张海 Year of Birth 1981
钱小平 Year of Birth 1983
王大力 Year of Birth 1982
192009-7-28 Information College · ChangJun
2、选择表中的若干元组
（ 1）消除取值相同的行本来在数据库表中不存在取值全都相同的元组，
但在进行了对列的选择后，在查询结果中就有可能出现取值完全相同的行了。
例 6．在修课表中查询有哪些学生修了课程，要求列出学生的学号。
SELECT Sno FROM SC
在这个结果中有许多重复的行。
Sno
9512101
9512102
9521102
9521103
9531101
SQL 中的 DISTINCT关键字可以去掉结果表中的重复行。
SELECT DISTICT Sno FROM
SC
则执行结果为,
202009-7-28 Information College · ChangJun
（ 2）查询满足条件的元组查询满足条件的元组是通过 WHERE子句 实现的。 WHERE子句常用的查询条件如表所示：
查询条件 谓 词比较（比较运算符） =,>,>=,<,<=,<>
（或 !=） NOT+上述比较运算符确定范围 BETWEEN AND,NOT BETWEEN
AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件（逻辑谓词 AND,OR
212009-7-28 Information College · ChangJun
1）比较大小例 7．查询计算机系全体学生的姓名。
SELECT Sname FROM Student
WHERE Sdept = '计算机系 '
结果为,
Sname
李勇刘晨王敏
222009-7-28 Information College · ChangJun
Sname Sage
李勇 19
钱小平 18
王大力 19
注意：取反操作的执行效率比较低例 8．查询所有年龄在 20岁以下的学生的姓名及年龄。
SELECT Sname,Sage FROM Student WHERE Sage < 20
或,SELECT Sname,Sage FROM Student
WHERE NOT Sage >= 20
结果为：
例 9．查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade < 60
232009-7-28 Information College · ChangJun
2）确定范围
BETWEEN?AND 和 NOT BETWEEN?AND 是一个逻辑运算符，可以用来查找属性值在或不在指定范围内的元组。 BETWEEN?AND? 的格式为,
列名 | 表达式 [ NOT ] BETWEEN 下限值
AND 上限值
BETWEEN?AND 一般用于对数值型数据进行比较。列名或表达式的类型要与下限值或上限值的类型相同。
含义 是：如果列或表达式的 值在下限值和上限值范围内，则结果为 True，表明此记录符合查询条件。
242009-7-28 Information College · ChangJun
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23
此句等价于：
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage >=20 AND Sage<=23
结果为：
Sname Sdept Sage
刘晨 计算机系
20
王敏 计算机系
20
张立 信息系 22
吴宾 信息系 21
张海 信息系 20
例 10．查询年龄在 20～ 23岁之间的学生的姓名、所在系和年龄
252009-7-28 Information College · ChangJun
例 11．查询年龄不在 20～ 23之间的学生姓名、
所在系和年龄。
SELECT Sname,Sdept,Sage FROM Student
WHERE Sage NOT BETWEEN 20 AND 23
此句等价于：
SELECT Sname,Sdept,Sage FROM Student
WHERE Sage <20 OR Sage>23
结果为：
Sname Sdept Sage
李勇 计算机系 19
钱小平 数学系 18
王大力 数学系 19
262009-7-28 Information College · ChangJun
3）确定集合
IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组。使用 IN的格式为：
列名 [ NOT ] IN （常量 1,常量 2,? 常量 n）
IN的含义为,当列中的值与 IN中的某个常量值相等时，则结果为 True，表明此记录为符合查询条件的记录；
NOT IN的含义 正好相反：当列中的值与某个常量值相同时，则结果为 False，表明此记录为不符合查询条件的记录；
272009-7-28 Information College · ChangJun
例 12．查询信息系、数学系和计算机系学生的姓名和性别。
SELECT Sname,Ssex FROM Student
WHERE Sdept IN ('信息系 ','数学系 ','计算机系 ')
此句等价于：
SELECT Sname,Ssex FROM Student
WHERE Sdept = '信息系 ' OR Sdept = '数学系 ' OR
Sdept = '计算机系‘
例 13．查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。
SELECT Sname,Ssex FROM Student
WHERE Sdept NOT IN ('信息系 ','数学系 ','计算机系 ')
此句等价于：
SELECT Sname,Ssex FROM Student
WHERE Sdept!= '信息系 ' AND Sdept!= '数学系 ' AND
Sdept!= '计算机系 '
282009-7-28 Information College · ChangJun
4）字符匹配
LIKE用于查找指定列名与匹配串常量匹配的元组。通配符用于表示任意的字符或字符串。
在 LIKE运算符前边也可以使用 NOT运算符，表示对结果取反。
LIKE运算符的一般形式为：
列名 [NOT ] LIKE <匹配串 >
匹配串中可包含如下四种 通配符,
_：匹配任意一个字符；
%：匹配 0个或多个字符；
[ ]：匹配 [ ]中的任意一个字符；
[^ ]：不匹配 [ ]中的任意一个字符。
292009-7-28 Information College · ChangJun
例 14．查询姓‘张’的学生的详细信息。
SELECT * FROM Student WHERE Sname LIKE ‘ 张 %’
结果为,Sno Sname Ssex Sage Sdept
9521101 张立 男 22 信息系
9521103 张海 男 20 信息系例 15．查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。
SELECT * FROM Student WHERE Sname LIKE ‘[ 张李刘 ]%’
结果为,Sno Sname Ssex Sage Sdept
9512101 李勇 男 19 计算机系
9512102 刘晨 男 20 计算机系
9521101 张立 男 22 信息系
9521103 张海 男 20 信息系
302009-7-28 Information College · ChangJun
例 16．查询名字中第 2个字为‘小’或‘大’字的学生的姓名和学号。
SELECT Sname,Sno FROM Student WHERE
Sname LIKE '_[小大 ]%'
结果为,Sname Sno
钱小平 9531101
王大力 9531102
例 17．查询所有不姓“刘”的学生。
SELECT Sname FROM Student
WHERE Sname NOT LIKE '刘 %'
例 18．从学生表中查询学号的最后一位不是 2,3,5的学生情况。
SELECT * FROM Student WHERE Sno LIKE '%[^235]'
312009-7-28 Information College · ChangJun
5）涉及空值的查询空值（ NULL） 在数据库中有特殊的含义，它表示不确定的值。判断某个值是否为 NULL值，不能使用普通的比较运算符（ =,!=等），而只能使用专门的判断 NULL
值 的子句来完成。
判断取值为空的语句格式为：列名 IS NULL
判断取值不为空的语句格式为：列名 IS NOT NULL
例 19．查询无考试成绩的学生的学号和相应的课程号。
SELECT Sno,Cno FROM SC WHERE Grade IS NULL
例 20．查询所有有考试成绩的学生的学号和课程号。
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL
322009-7-28 Information College · ChangJun
6）多重条件查询在 WHERE子句中可以使用逻辑运算符 AND和
OR来组成多条件查询。用 AND连接的条件表示必须全部满足所有的条件的结果才为 True，用
OR连接的条件表示只要满足其中一个条件结果即为 True。
例 21．查询计算机系年龄在 20岁以下的学生姓名。
SELECT Sname FROM Student
WHERE Sdept='CS' AND Sage<20
332009-7-28 Information College · ChangJun
（ 3）对查询结果进行排序排序子句的格式为：
ORDER BY <列名 > [ASC | DESC ] [,? n ]
其中 <列名 >为排序的依据列，可以是列名或列的别名。 ASC表示对列进行升序排序,DESC表示对列进行降序排序。如果没有指定排序方式，
则默认的排序方式为升序排序。
如果在 ORDER BY子句 中使用多个列进行排序，
则这些列在该子句中出现的顺序决定了对结果集进行排序的方式。当指定多个排序依据列时，首先按排在最前面的列进行排序，如果排序后存在两个或两个以上列值相同的记录，则对这些值相同的记录再依据排在第二位的列进行排序,?,
依此类推。
342009-7-28 Information College · ChangJun
例 21．将学生按年龄的升序排序。
SELECT * FROM Student ORDER BY Sage
例 22．查询选修了‘ c02’ 号课程的学生的学号及其成绩，查询结果按成绩降序排列。
SELECT Sno,Grade FROM SC
WHERE Cno='c02' ORDER BY Grade DESC
例 23．查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
SELECT * FROM Student
ORDER BY Sdept,Sage DESC
352009-7-28 Information College · ChangJun
（ 4）使用计算函数汇总数据计算函数也称为 集合函数或聚合函数、聚集函数,其作用是对一组值进行计算并返回一个单值。 SQL提供的计算函数有：
COUNT（ * ）：统计表中元组个数；
COUNT（ <列名 > ）：统计本列列值个数；
SUM（ <列名 > ）：计算列值总和（必须是数值型列）；
AVG（ <列名 > ）：计算列值平均值（必须是数值型列）；
MAX（ <列名 > ）：求列值最大值；
MIN（ <列名 > ）：求列值最小值。
上述函数中除 COUNT（ *）外，其他函数在计算过程中均忽略 NULL值。
362009-7-28 Information College · ChangJun
例 24．统计学生总人数。
SELECT COUNT（ *） FROM Student
例 25．统计选修了课程的学生的人数。
SELECT COUNT （ DISTINCT Sno） FROM SC
例 26 计算 9512101号学生的考试总成绩之和。
SELECT SUM(Grade) FROM SC WHERE Sno = '9512101'
例 27．计算’ C01’ 号课程学生的考试平均成绩。
SELECT AVG(Grade) FROM SC WHERE Cno='C01'
例 28．查询选修了’ C01’ 号课程的学生的最高分和最低分。
SELECT MAX(Grade),MIN(Grade) FROM SC
WHERE Cno='C01'
注意,计算函数不能出现在 WHERE子句中。
例：查询年龄最大的学生的姓名，如下写法是 错误 的：
SELECT Sname FROM Student WHERE Sage = MAX(Sage)
372009-7-28 Information College · ChangJun
（５）对查询结果进行分组计算有时我们需要对数据进行分组，然后再对每个组进行计算，而不是对全表进行计算。这时就需要用到分组子句,GROUP BY 。 GROUP BY可将计算控制在组一级。分组的目的是细化计算函数的作用对象。在一个查询语句中，可以使用任意多个列进行分组。
使用 GROUP BY时，如果在 SELECT的查询列表中包含计算函数，则是针对每个组计算出一个汇总值，
从而实现对查询结果的分组统计。
分组语句跟在 WHERE子句的后边，它的一般形式为：
GROUP BY <分组依据列 > [,? n ]
[HAVING <组提取条件 >]
382009-7-28 Information College · ChangJun
１）使用 GROUP BY
例 28．统计每门课程的选课人数，列出课程号和人数。
SELECT Cno as 课程号,COUNT(Sno) as
选课人数
FROM SC GROUP BY Cno
查询结果为,
课程号选课人数
c01 3
C02 4
c04 2
c05 3
c06 2
例 29．查询每名学生的选课门数和平均成绩。
SELECT Sno 学号,COUNT(*) 选课门数,AVG(Grade) 平均成绩 FROM SC
GROUP BY Sno
392009-7-28 Information College · ChangJun
２） 使用 HAVING
HAVING子句用于对分组后的结果再进行过滤，它的功能有点像 WHERE子句，但它用于组而不是对单个记录。在 HAVING子句中可以使用计算函数，但在 WHERE子句中则不能。 HAVING
通常与 GROUP BY子句一起使用。
例 30．查询修了 3门以上课程的学生的学号。
SELECT Sno FROM SC GROUP BY Sno
HAVING COUNT(*) > 3
结果为,Sno
9521102
402009-7-28 Information College · ChangJun
例 31．查询修课门数等于或大于 4门的学生的平均成绩和选课门数。
SELECT Sno,AVG(Grade) 平均成绩,
COUNT(*) 修课门数
FROM SC GROUP BY Sno HAVING
COUNT(*) >= 4
结果为,Sno 平均成绩 修课门数
9521102 74 4
412009-7-28 Information College · ChangJun
（１） 内连接在非 ANSI标准的实现中，是在 WHERE子句中指定表连接条件，在 ANSI SQL-92中，连接是在 JOIN子句中指定。
这些连接方式分别被称为 theta方式和 ANSI方式。我们这里介绍的是 ANSI方式的连接。
内连接的格式为：
FROM 表 1 [ INNER ] JOIN 表 2 ON <连接条件 >
在连接条件中指明两个表按什么条件进行连接，连接条件中的比较运算符称为连接谓词。连接条件的一般格式为：
[<表名 1.>][<列名 1>] <比较运算符 > [<表名 2.>][<
列名 2>]
注意,两个表的连接列必须是可比较的，即必须是语义相同的列，否则比较将是无意义的。
当比较运算符为等号（＝）时，称为等值连接，使用其他运算符的连接称为非等值连接。
３、多表连接查询
422009-7-28 Information College · ChangJun
例 32．查询每个学生及其修课的情况。
SELECT * FROM Student INNER JOIN SC
ON Student.Sno = SC.Sno
-- 将 Student与 SC连接起来
Sno Sname Ssex Sage Sdept Sno Cno Grade XKLB
9512101 李勇 男 19 计算机系 9512101 c01 90 必修
9512101 李勇 男 19 计算机系 9512101 c02 86 选修
9512101 李勇 男 19 计算机系 9512101 c06 NULL 必修
9512102 刘晨 男 20 计算机系 9512102 c02 78 选修
9512102 刘晨 男 20 计算机系 9512102 c04 66 必修
9512102 吴宾 女 21 信息系 9512102 c01 82 选修
9512102 吴宾 女 21 信息系 9512102 c02 75 选修
9512102 吴宾 女 21 信息系 9512102 c04 92 必修
9512102 吴宾 女 21 信息系 9512102 c05 50 必修
9521103 张海 男 20 信息系 9521103 c02 68 选修
9521103 张海 男 20 信息系 9521103 c06 NULL 必修
9531101 钱小平 女 18 数学系 9531101 c01 80 选修
9531101 钱小平 女 18 数学系 9531101 c05 95 必修
9531102 王大力 男 19 数学系 9531102 c05 85 必修
432009-7-28 Information College · ChangJun
从这个结果我们可以看到，两个表的连接结果中包含了两个表的全部列,Sno列重复了两次，这是不必要的。因此，在写查询语句时应当将这些重复的列去掉（在 SELECT子句中直接写所需要的列名，而不是写
*）。而且由于在连接后的表中有重复的列名（ Sno
列），因此，我们在 ON子句中对 Sno加上了表名前缀限制，指明是哪个表中的 Sno。
例 33．去掉例 32中的重复列。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,
Cno,Grade,XKLB FROM Student JOIN SC
ON Student.Sno = SC.Sno
例 34．查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
SELECT Sname,Cno,Grade FROM Student JOIN SC
ON Student.Sno = SC.Sno
WHERE Sdept = '计算机系 '
442009-7-28 Information College · ChangJun
可以为表提供别名，其格式为：
<源表名 > [ AS ] <表别名 >
例如：使用别名时例 34可写为：
SELECT Sname,Cno,Grade
FROM Student S JOIN SC ON
S.Sno = SC.Sno
WHERE Sdept = '计算机系 '
例 35．查询信息系修了 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'
452009-7-28 Information College · ChangJun
例 36．查询所有修了 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 '
462009-7-28 Information College · ChangJun
(2) 自连接是一种 特殊的内连接,它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
使用自连接时必须为两个表取别名，使之在逻辑上成为两张表。
例 37．查询与刘晨在同一个系学习的学生的姓名和所在的系。
SELECT S2.Sname,S2.Sdept
FROM Student S1 JOIN Student S2
ON S1.Sdept = S2.Sdept
WHERE S1.Sname = '刘晨 '
AND S2.Sname != '刘晨’
结果为,Sname Sdept
李勇 计算机系王敏 计算机系
472009-7-28 Information College · ChangJun
(3) 外连接在内连接操作中，只有满足连接条件的元组才能作为结果输出，但有时我们也希望输出那些不满足连接条件的元组的信息，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。
语法格式为：
FROM 表 1 LEFT | RIGHT [OUTER] JOIN 表 2 ON
<连接条件 >
482009-7-28 Information College · ChangJun
例 39．查询学生的修课情况，
包括修了课程的学生和没有修课的学生。
SELECT Student.Sno,
Sname,Cno,Grade
FROM Student
LEFT OUTER JOIN SC
ON Student.Sno = SC.Sno
也可以用右外连接实现：
SELECT Student.Sno,
Sname,
Cno,Grade FROM SC
RIGHT OUTER JOIN
Student
ON Student.Sno = SC.Sno
Sno Snam
e
Cno Grad
e
95121
01
李勇 c01 90
95121
01
李勇 c02 86
95121
01
李勇 c06 NULL
95121
02
刘晨 c02 78
95121
02
刘晨 c04 66
95121
02
吴宾 c01 82
95121
02
吴宾 c02 75
492009-7-28 Information College · ChangJun
4、子查询在 SQL语言中，一个 SELECT－ FROM－ WHERE
语句称为一个查询块。
如果一个 SELECT语句是嵌套在一个 SELECT、
INSERT,UPDATE或 DELETE语句中，则称之为子查询或内层查询；而包含子查询的语句则称为主查询或外层查询。子查询要写在圆括号中。
子查询语句可以出现在允许使用表达式的任何地方，但通常情况下一般是用在外层查询的 WHERE子句或 HAVING子句中，与比较运算符或逻辑运算符一起构成查询条件。
502009-7-28 Information College · ChangJun
使用 IN运算符时，如果该表达式的值与集合中的某个值相等，则此测试为 True；如果该表达式与集合中所有的值均不相等，则返回 False。
例 40．查询与“刘晨”在同一个系学习的学生。
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN (SELECT Sdept FROM
Student WHERE Sname = '刘晨 ')
Sno Sname Sdept
9512101 李勇 计算机系
9512102 刘晨 计算机系
9512103 王敏 计算机系
（ 1） 使用子查询进行基于集合的测试使用子查询进行基于集合的测试时，通过运算符 IN或 NOT IN，将一个表达式的值与子查询返回的结果集进行比较。
512009-7-28 Information College · ChangJun
例 41．查询成绩为大于 90分的学生的学号、姓名。
SELECT Sno,Sname FROM Student
WHERE Sno IN
( SELECT Sno FROM SC
WHERE Grade > 90 )
结果为,Sno Sname
9521102 吴宾
9531101 钱小平用多表连接实现：
SELECT Sno,Sname FROM Student
WHERE Student.Sno = SC.Sno AND
Grade > 90
522009-7-28 Information College · ChangJun
例 42．查询选修了“数据库基础”课程的学生的学号、
姓名。
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
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON Course.Cno = SC.Cno
WHERE Cname = '数据库基础 '
532009-7-28 Information College · ChangJun
用子查询进行基于集合的测试时，是先执行子查询，然后再在子查询的结果基础之上执行外层查询。
子查询只执行一次，子查询的查询条件不依赖于外层查询，我们将这样的子查询称为 不相关子查询或嵌套子查询 。
542009-7-28 Information College · ChangJun
（ 2）使用子查询进行比较测试通过比较运算符（ =,<>,<,>,<=,<=），将一个表达式的值与子查询返回的值进行比较。
注意：使用子查询进行比较测试时，要求子查询语句必须是返回单值的查询语句。
例 43．查询修了‘ c02’ 课程且成绩高于此课程的平均成绩的学生的学号和成绩。
SELECT Sno,Grade FROM SC
WHERE Cno = 'c02' and Grade > (
SELECT AVG(Grade) from SC
WHERE Cno = 'c02')
结果为,Sno Grade
9512101 86
9512102 78
552009-7-28 Information College · ChangJun
（ 3）使用子查询进行存在性测试使用子查询进行存在性测试时，一般使用
EXISTS谓词。带 EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。
例 44．查询选修了‘ c01’ 号课程的学生姓名。
SELECT Sname FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE Sno = Student.Sno AND
Cno = 'c01')
562009-7-28 Information College · ChangJun
注意,
1．带 EXISTS谓词的查询是先执行外层查询，
然后再执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。
2．由于 EXISTS的子查询只能返回真或假值，
因此在子查询中指定列名是没有意义的。所以在有 EXISTS的子查询中，其目标列名序列通常都用 ‘ *’ 。
572009-7-28 Information College · ChangJun
上述查询语句的处理过程为：
（ 1）找外层表 Student表的第一行，根据其 Sno
值处理内层查询；
（ 2）用外层的值执行内层查询，如果有符合条件的数据，则 EXISTS返回真值，否则返回假值。
如果 EXISTS返回为真，则外层结果中的当前行数据为符合条件的结果；否则，是不符合条件的结果；
（ 3）顺序处理外层表 Student表中的第 2、
3,? 行数据，直到处理完所有行。
582009-7-28 Information College · ChangJun
基于其他表条件的删除例 3．删除计算机系不及格学生的修课记录。
用子查询实现
DELETE FROM SC
WHERE Grade < 60 AND Sno IN (
SELECT Sno FROM Student
WHERE Sdept = ‘ 计算机系’ )
用多表连接实现
DELETE FROM SC
FROM SC JOIN Student
ON SC.Sno = Student.Sno
WHERE Sdept = ‘ 计算机系’ AND Grade
高级删除操作
592009-7-28 Information College · ChangJun
基于其他表条件的更新例 3．将计算机系全体学生的成绩加 5分。
用子查询实现
UPDATE SC SET Grade = Grade + 5
WHERE Sno IN
(SELECT Sno FROM Student
WHERE Sdept = ‘ 计算机系’ )
用多表连接实现
UPDATE SC SET Grade = Grade + 5
FROM SC JOIN Student ON SC.Sno =
Student.Sno
WHERE Sdept=‘ 计算机系 '
高级更新操作
602009-7-28 Information College · ChangJun
7.5 视图
7.5.1 什么是视图
1、视图的特点视图是一个 虚拟表,其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。
视图并不在数据库中以存储的数据值集形式存在，存储的是 SELECT 语句。
行和列数据在引用视图时动态生成。 SELECT
语句的结果集构成视图所返回的虚拟表。
用户可以用引用表时所使用的方法，在
Transact-SQL 语句中通过引用视图名称来使用虚拟表。
定义视图可以来自当前或其它数据库的一个或多个表，或者其它视图。
612009-7-28 Information College · ChangJun
2、视图的功能使用视图可以实现下列任一或所有功能：
将用户限定在表中的特定行上。
例如，只允许雇员看见工作跟踪表内记录其工作的行。
将用户限定在特定列上。
例如，对于那些不负责处理工资单的雇员，只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列，而不能看见任何包含工资信息或个人信息的列。
将多个表中的列联接起来，使它们看起来象一个表。
聚合信息而非提供详细信息。
例如，显示一个列的和，或列的最大值和最小值。
622009-7-28 Information College · ChangJun
3、视图的优点
（ 1） 简化数据查询语句
（ 2） 使用户能从多角度看待同一数据
（ 3） 提高了数据的安全性
（ 4） 提供一定的逻辑独立性
632009-7-28 Information College · ChangJun
7.5.2 视图的创建、修改与删除
1、企业管理器
2,Transact-SQL
（ 1）创建一个虚拟表，该表以另一种方式表示一个或多个表中的数据。语法,
CREATE VIEW view_name [ ( column
[,...n ] ) ]
AS
select_statement
CREATE VIEW 必须是查询批处理中的第一条语句。
642009-7-28 Information College · ChangJun
1）定义单源视图
2）定义多源视图
3）在已有视图上定义新视图
4）定义带表达式的视图
5）含分组统计信息的视图
652009-7-28 Information College · ChangJun
下例创建具有简单 SELECT 语句的视图。
当需要频繁地查询列的某种组合时，简单视图非常有用。
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
CREATE VIEW titles_view
AS
SELECT title,type,price,pubdate
FROM titles
GO
662009-7-28 Information College · ChangJun
（ 2）从当前数据库中删除一个或多个视图。语法：
DROP VIEW { view } [,...n ]
下例删除 titles_view 视图。
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
672009-7-28 Information College · ChangJun
（ 3）更改一个先前创建的视图。语法：
ALTER VIEW view_name [ ( column [,...n ] ) ]
AS select_statement
下例创建称为 All_authors 的视图，该视图包含全部的作者。权限授予了视图。
CREATE VIEW All_authors (au_fname,au_lname,address,
city,zip)
AS SELECT au_fname,au_lname,address,city,zip
FROM pubs..authors
GO
ALTER VIEW All_authors (au_fname,au_lname,address,
city,zip)
AS SELECT au_fname,au_lname,address,city,zip
FROM pubs..authors WHERE state = 'UT'
682009-7-28 Information College · ChangJun
3、存储过程浏览视图信息
（ 1） sp_help view_name
（ 2） sp_depends view_name
（ 3） sp_helptext view_name
692009-7-28 Information College · ChangJun
7、使用视图加强数据安全对不同用户授予不同的使用许可权通过使用 SELECT语句限制用户对某些底层基本表列的访问。
通过使用 where子句限制用户对某些底层基本表行的访问
第 7章 表的操作
22009-7-28 Information College · ChangJun
[本章概要 ]
7.1 表数据的增加 —— 插入记录
7.2 表数据的删除 —— 删除记录
7.3 表数据的修改 —— 更新记录
7.4 表数据的检索 —— 查询记录
7.5 视图
32009-7-28 Information College · ChangJun
7.1 表数据的增加 ——
插入记录
7.1.1 使用企业管理器
7.1.2 使用 Transact-SQL语言（ DML）
将新行添加到表。
语法：
INSERT [INTO] <表名 > [（ <列名表 >） ]
VALUES （值列表）
其中,<列名表 >中的列名必须是表定义中有的列名，值列表中的值可以是常量也可以是 NULL
值，各值之间用逗号分隔。
42009-7-28 Information College · ChangJun
示例
1、使用简单的 INSERT
例 1．将新生记录（ 95020，陈冬，男，信息系,18岁）插入到 S表中。
INSERT INTO Student VALUES
(‘9521105’,‘ 陈冬’,'男 ',18,'信息系 ')
52009-7-28 Information College · ChangJun
2、插入与列顺序不同的数据例 2．将新课程,cname为“数据库基础”、
cno为,c04”,semeter为 6,ccredit为 6的记录插入 C表中。
INSERT INTO C(cname,Cno,semester，
ccredit)
VALUES(‘ 数据库基础 ’,‘c04’,6,6)
注：对于例 2，由于插入与列顺序不同的数据，
因此此时必须列出列名。
62009-7-28 Information College · ChangJun
3、插入值少于列个数的数据例 3．在 SC表中插入一新记录，成绩暂缺。
INSERT INTO SC(Sno,Cno)
VALUES('9521105','c01')
注：对于例 3，由于提供的值个数与表中的列个数不一致，因此此时必须列出列名。而且 SC
中的 Grade必须允许为 NULL。
此句实际插入的值为,('9521105','c01'，
NULL)
72009-7-28 Information College · ChangJun
7.2 表数据的删除 ——
删除记录
7.2.1 使用企业管理器
7.2.2 使用 Transac-SQL语言（ DML）
当确定不再需要某些记录时，就可以用删除语句 。
DELETE [ FROM ] <表名 > [WHERE <删除条件 >]
其中,<表名 >说明了要删除哪个表中的数据。
WHERE子句说明要删除表中的哪些记录 ―― 只删除满足 WHERE条件的记录。如果省略 WHERE子句，则是无条件删除，表示要删除表中的全部记录。
82009-7-28 Information College · ChangJun
示例
1、无条件删除无条件删除是删除表中全部数据，但保留表的结构。
例 1．删除所有学生的选课记录。
DELETE FROM SC -- SC成空表
2、有条件删除
（ 1）基于本表条件的删除。
例 2．删除所有不及格学生的修课记录。
DELETE FROM SC WHERE Grade < 60
（ 2）基于其他表条件的删除 （学过查询后再讲）
92009-7-28 Information College · ChangJun
7.3 表数据的修改
—— 更新记录
7.3.1 使用企业管理器
7.3.2 使用 Transac-SQL语言（ DML）
更改表中的现有数据语法：
UPDATE <表名 > SET <列名＝表达式 > [,… n]
[WHERE <更新条件 >]
其中,<表名 >给出了需要修改数据的表的名称。 SET
子句指定要修改的列，表达式指定修改后的新值。
WHERE子句用于指定需要修改表中的哪些记录。如果省略 WHERE子句，则是无条件更新，表示要修改 SET
中指定的列的全部值。
102009-7-28 Information College · ChangJun
示例
1．无条件更新例 1．将所有学生的年龄加 1。
UPDATE Student SET Sage = Sage + 1
2．有条件更新
（ 1）基于本表条件的更新。
例 2．将‘ 9512101’ 学生的年龄改为 21岁。
UPDATE Student SET Sage = 21
WHERE Sno = '9512101'
（ 2）基于其他表条件的更新 （学过查询后再讲）
112009-7-28 Information College · ChangJun
7.4 表数据的检索
—— 查询记录
7.4.1 使用企业管理器
7.4.2 使用 Transac-SQL语言（ DML）
从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。
122009-7-28 Information College · ChangJun
7.4 表数据的检索
—— 查询记录
7.4.1 使用企业管理器
7.4.2 使用 Transac-SQL语言（ DML）
查询语句是数据库使用中 最基本 和 最重要的语句之一。
其功能是从数据库中检索满足条件的数据。
查询的数据源可以来自一张表，也可以来自多张表甚至视图。
SELECT语句还可以对查询的结果进行排序、
汇总等。
132009-7-28 Information College · ChangJun
查询语句的基本结构：
SELECT <目标列名序列 > -- 需要哪些列
FROM <数据源 > -- 来自于哪些表
[WHERE <检索条件表达式 >] -- 根据什么条件
[GROUP BY <分组依据列 >]
[HAVING <组提取条件 >]
[ORDER BY <排序依据列 >]
142009-7-28 Information College · ChangJun
一、简单查询
1、选择表中若干列
（ 1）查询指定的列在很多情况下，用户可能只对表中的一部分属性列感兴趣，这时可通过在
SELECT子句的 <目标列名序列
>中指定要查询的列来实现。
例 1,查询全体学生的学号与姓名
SELECT Sno,Sname FROM
Student
Sno sname
9512101 李勇
9512102 刘晨
9512103 王敏
9521101 张立
9521102 吴宾
9521103 张海
9531101 钱小平
9531102 王大力结果为,
152009-7-28 Information College · ChangJun
（ 2）如果要查询表中的全部列例 3．查询全体学生的记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
等价于,SELECT * FROM Student
Sno Sname Ssex Sage Sdept
9512101 李勇 男 19 计算机系
9512102 刘晨 男 20 计算机系
9512103 王敏 女 20 计算机系
9521101 张立 男 22 信息系
9521102 吴宾 女 21 信息系
9521103 张海 男 20 信息系
9531101 钱小平 女 18 数学系
9531102 王大力 男 19 数学系结果为,
162009-7-28 Information College · ChangJun
（ 3）查询经过计算的列
SELECT子句中的 <目标列名序列 >可以是表中存在的属性列，也可以是表达式、
常量或者函数例 4．查询全体学生的姓名及其出生年份
SELECT Sname，
2002 - Sage FROM Student
结果为,
Sname （无列名 )
李勇 1982
刘晨 1981
王敏 1981
张立 1979
吴宾 1980
张海 1981
钱小平 1983
王大力 1982
172009-7-28 Information College · ChangJun
例 5．查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为‘ Year of Birth’ 常量值。
SELECT Sname，‘ Year of Birth’,
2002-Sage
FROM Student
查询结果,
Sname （无列名 ) （无列名 )
李勇 Year of Birth 1982
刘晨 Year of Birth 1981
王敏 Year of Birth 1981
张立 Year of Birth 1979
吴宾 Year of Birth 1980
张海 Year of Birth 1981
钱小平 Year of Birth 1983
王大力 Year of Birth 1982
182009-7-28 Information College · ChangJun
经过计算的列、函数的列和常量列的显示结果都没有列标题，通过指定列的别名可以改变查询结果的列标题。
改变列标题的语法格式为：
列名 | 表达式 [ AS ] 列标题或：列标题 ＝列名 | 表达式例如，对于例 5可写成：
SELECT Sname 姓名，‘ Year of Birth’ 出生年份,
2002-Sage 年份，
FROM Student
结果为：
姓名 出生年份 年份李勇 Year of Birth 1982
刘晨 Year of Birth 1981
王敏 Year of Birth 1981
张立 Year of Birth 1979
吴宾 Year of Birth 1980
张海 Year of Birth 1981
钱小平 Year of Birth 1983
王大力 Year of Birth 1982
192009-7-28 Information College · ChangJun
2、选择表中的若干元组
（ 1）消除取值相同的行本来在数据库表中不存在取值全都相同的元组，
但在进行了对列的选择后，在查询结果中就有可能出现取值完全相同的行了。
例 6．在修课表中查询有哪些学生修了课程，要求列出学生的学号。
SELECT Sno FROM SC
在这个结果中有许多重复的行。
Sno
9512101
9512102
9521102
9521103
9531101
SQL 中的 DISTINCT关键字可以去掉结果表中的重复行。
SELECT DISTICT Sno FROM
SC
则执行结果为,
202009-7-28 Information College · ChangJun
（ 2）查询满足条件的元组查询满足条件的元组是通过 WHERE子句 实现的。 WHERE子句常用的查询条件如表所示：
查询条件 谓 词比较（比较运算符） =,>,>=,<,<=,<>
（或 !=） NOT+上述比较运算符确定范围 BETWEEN AND,NOT BETWEEN
AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件（逻辑谓词 AND,OR
212009-7-28 Information College · ChangJun
1）比较大小例 7．查询计算机系全体学生的姓名。
SELECT Sname FROM Student
WHERE Sdept = '计算机系 '
结果为,
Sname
李勇刘晨王敏
222009-7-28 Information College · ChangJun
Sname Sage
李勇 19
钱小平 18
王大力 19
注意：取反操作的执行效率比较低例 8．查询所有年龄在 20岁以下的学生的姓名及年龄。
SELECT Sname,Sage FROM Student WHERE Sage < 20
或,SELECT Sname,Sage FROM Student
WHERE NOT Sage >= 20
结果为：
例 9．查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade < 60
232009-7-28 Information College · ChangJun
2）确定范围
BETWEEN?AND 和 NOT BETWEEN?AND 是一个逻辑运算符，可以用来查找属性值在或不在指定范围内的元组。 BETWEEN?AND? 的格式为,
列名 | 表达式 [ NOT ] BETWEEN 下限值
AND 上限值
BETWEEN?AND 一般用于对数值型数据进行比较。列名或表达式的类型要与下限值或上限值的类型相同。
含义 是：如果列或表达式的 值在下限值和上限值范围内，则结果为 True，表明此记录符合查询条件。
242009-7-28 Information College · ChangJun
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23
此句等价于：
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage >=20 AND Sage<=23
结果为：
Sname Sdept Sage
刘晨 计算机系
20
王敏 计算机系
20
张立 信息系 22
吴宾 信息系 21
张海 信息系 20
例 10．查询年龄在 20～ 23岁之间的学生的姓名、所在系和年龄
252009-7-28 Information College · ChangJun
例 11．查询年龄不在 20～ 23之间的学生姓名、
所在系和年龄。
SELECT Sname,Sdept,Sage FROM Student
WHERE Sage NOT BETWEEN 20 AND 23
此句等价于：
SELECT Sname,Sdept,Sage FROM Student
WHERE Sage <20 OR Sage>23
结果为：
Sname Sdept Sage
李勇 计算机系 19
钱小平 数学系 18
王大力 数学系 19
262009-7-28 Information College · ChangJun
3）确定集合
IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组。使用 IN的格式为：
列名 [ NOT ] IN （常量 1,常量 2,? 常量 n）
IN的含义为,当列中的值与 IN中的某个常量值相等时，则结果为 True，表明此记录为符合查询条件的记录；
NOT IN的含义 正好相反：当列中的值与某个常量值相同时，则结果为 False，表明此记录为不符合查询条件的记录；
272009-7-28 Information College · ChangJun
例 12．查询信息系、数学系和计算机系学生的姓名和性别。
SELECT Sname,Ssex FROM Student
WHERE Sdept IN ('信息系 ','数学系 ','计算机系 ')
此句等价于：
SELECT Sname,Ssex FROM Student
WHERE Sdept = '信息系 ' OR Sdept = '数学系 ' OR
Sdept = '计算机系‘
例 13．查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。
SELECT Sname,Ssex FROM Student
WHERE Sdept NOT IN ('信息系 ','数学系 ','计算机系 ')
此句等价于：
SELECT Sname,Ssex FROM Student
WHERE Sdept!= '信息系 ' AND Sdept!= '数学系 ' AND
Sdept!= '计算机系 '
282009-7-28 Information College · ChangJun
4）字符匹配
LIKE用于查找指定列名与匹配串常量匹配的元组。通配符用于表示任意的字符或字符串。
在 LIKE运算符前边也可以使用 NOT运算符，表示对结果取反。
LIKE运算符的一般形式为：
列名 [NOT ] LIKE <匹配串 >
匹配串中可包含如下四种 通配符,
_：匹配任意一个字符；
%：匹配 0个或多个字符；
[ ]：匹配 [ ]中的任意一个字符；
[^ ]：不匹配 [ ]中的任意一个字符。
292009-7-28 Information College · ChangJun
例 14．查询姓‘张’的学生的详细信息。
SELECT * FROM Student WHERE Sname LIKE ‘ 张 %’
结果为,Sno Sname Ssex Sage Sdept
9521101 张立 男 22 信息系
9521103 张海 男 20 信息系例 15．查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。
SELECT * FROM Student WHERE Sname LIKE ‘[ 张李刘 ]%’
结果为,Sno Sname Ssex Sage Sdept
9512101 李勇 男 19 计算机系
9512102 刘晨 男 20 计算机系
9521101 张立 男 22 信息系
9521103 张海 男 20 信息系
302009-7-28 Information College · ChangJun
例 16．查询名字中第 2个字为‘小’或‘大’字的学生的姓名和学号。
SELECT Sname,Sno FROM Student WHERE
Sname LIKE '_[小大 ]%'
结果为,Sname Sno
钱小平 9531101
王大力 9531102
例 17．查询所有不姓“刘”的学生。
SELECT Sname FROM Student
WHERE Sname NOT LIKE '刘 %'
例 18．从学生表中查询学号的最后一位不是 2,3,5的学生情况。
SELECT * FROM Student WHERE Sno LIKE '%[^235]'
312009-7-28 Information College · ChangJun
5）涉及空值的查询空值（ NULL） 在数据库中有特殊的含义，它表示不确定的值。判断某个值是否为 NULL值，不能使用普通的比较运算符（ =,!=等），而只能使用专门的判断 NULL
值 的子句来完成。
判断取值为空的语句格式为：列名 IS NULL
判断取值不为空的语句格式为：列名 IS NOT NULL
例 19．查询无考试成绩的学生的学号和相应的课程号。
SELECT Sno,Cno FROM SC WHERE Grade IS NULL
例 20．查询所有有考试成绩的学生的学号和课程号。
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL
322009-7-28 Information College · ChangJun
6）多重条件查询在 WHERE子句中可以使用逻辑运算符 AND和
OR来组成多条件查询。用 AND连接的条件表示必须全部满足所有的条件的结果才为 True，用
OR连接的条件表示只要满足其中一个条件结果即为 True。
例 21．查询计算机系年龄在 20岁以下的学生姓名。
SELECT Sname FROM Student
WHERE Sdept='CS' AND Sage<20
332009-7-28 Information College · ChangJun
（ 3）对查询结果进行排序排序子句的格式为：
ORDER BY <列名 > [ASC | DESC ] [,? n ]
其中 <列名 >为排序的依据列，可以是列名或列的别名。 ASC表示对列进行升序排序,DESC表示对列进行降序排序。如果没有指定排序方式，
则默认的排序方式为升序排序。
如果在 ORDER BY子句 中使用多个列进行排序，
则这些列在该子句中出现的顺序决定了对结果集进行排序的方式。当指定多个排序依据列时，首先按排在最前面的列进行排序，如果排序后存在两个或两个以上列值相同的记录，则对这些值相同的记录再依据排在第二位的列进行排序,?,
依此类推。
342009-7-28 Information College · ChangJun
例 21．将学生按年龄的升序排序。
SELECT * FROM Student ORDER BY Sage
例 22．查询选修了‘ c02’ 号课程的学生的学号及其成绩，查询结果按成绩降序排列。
SELECT Sno,Grade FROM SC
WHERE Cno='c02' ORDER BY Grade DESC
例 23．查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
SELECT * FROM Student
ORDER BY Sdept,Sage DESC
352009-7-28 Information College · ChangJun
（ 4）使用计算函数汇总数据计算函数也称为 集合函数或聚合函数、聚集函数,其作用是对一组值进行计算并返回一个单值。 SQL提供的计算函数有：
COUNT（ * ）：统计表中元组个数；
COUNT（ <列名 > ）：统计本列列值个数；
SUM（ <列名 > ）：计算列值总和（必须是数值型列）；
AVG（ <列名 > ）：计算列值平均值（必须是数值型列）；
MAX（ <列名 > ）：求列值最大值；
MIN（ <列名 > ）：求列值最小值。
上述函数中除 COUNT（ *）外，其他函数在计算过程中均忽略 NULL值。
362009-7-28 Information College · ChangJun
例 24．统计学生总人数。
SELECT COUNT（ *） FROM Student
例 25．统计选修了课程的学生的人数。
SELECT COUNT （ DISTINCT Sno） FROM SC
例 26 计算 9512101号学生的考试总成绩之和。
SELECT SUM(Grade) FROM SC WHERE Sno = '9512101'
例 27．计算’ C01’ 号课程学生的考试平均成绩。
SELECT AVG(Grade) FROM SC WHERE Cno='C01'
例 28．查询选修了’ C01’ 号课程的学生的最高分和最低分。
SELECT MAX(Grade),MIN(Grade) FROM SC
WHERE Cno='C01'
注意,计算函数不能出现在 WHERE子句中。
例：查询年龄最大的学生的姓名，如下写法是 错误 的：
SELECT Sname FROM Student WHERE Sage = MAX(Sage)
372009-7-28 Information College · ChangJun
（５）对查询结果进行分组计算有时我们需要对数据进行分组，然后再对每个组进行计算，而不是对全表进行计算。这时就需要用到分组子句,GROUP BY 。 GROUP BY可将计算控制在组一级。分组的目的是细化计算函数的作用对象。在一个查询语句中，可以使用任意多个列进行分组。
使用 GROUP BY时，如果在 SELECT的查询列表中包含计算函数，则是针对每个组计算出一个汇总值，
从而实现对查询结果的分组统计。
分组语句跟在 WHERE子句的后边，它的一般形式为：
GROUP BY <分组依据列 > [,? n ]
[HAVING <组提取条件 >]
382009-7-28 Information College · ChangJun
１）使用 GROUP BY
例 28．统计每门课程的选课人数，列出课程号和人数。
SELECT Cno as 课程号,COUNT(Sno) as
选课人数
FROM SC GROUP BY Cno
查询结果为,
课程号选课人数
c01 3
C02 4
c04 2
c05 3
c06 2
例 29．查询每名学生的选课门数和平均成绩。
SELECT Sno 学号,COUNT(*) 选课门数,AVG(Grade) 平均成绩 FROM SC
GROUP BY Sno
392009-7-28 Information College · ChangJun
２） 使用 HAVING
HAVING子句用于对分组后的结果再进行过滤，它的功能有点像 WHERE子句，但它用于组而不是对单个记录。在 HAVING子句中可以使用计算函数，但在 WHERE子句中则不能。 HAVING
通常与 GROUP BY子句一起使用。
例 30．查询修了 3门以上课程的学生的学号。
SELECT Sno FROM SC GROUP BY Sno
HAVING COUNT(*) > 3
结果为,Sno
9521102
402009-7-28 Information College · ChangJun
例 31．查询修课门数等于或大于 4门的学生的平均成绩和选课门数。
SELECT Sno,AVG(Grade) 平均成绩,
COUNT(*) 修课门数
FROM SC GROUP BY Sno HAVING
COUNT(*) >= 4
结果为,Sno 平均成绩 修课门数
9521102 74 4
412009-7-28 Information College · ChangJun
（１） 内连接在非 ANSI标准的实现中，是在 WHERE子句中指定表连接条件，在 ANSI SQL-92中，连接是在 JOIN子句中指定。
这些连接方式分别被称为 theta方式和 ANSI方式。我们这里介绍的是 ANSI方式的连接。
内连接的格式为：
FROM 表 1 [ INNER ] JOIN 表 2 ON <连接条件 >
在连接条件中指明两个表按什么条件进行连接，连接条件中的比较运算符称为连接谓词。连接条件的一般格式为：
[<表名 1.>][<列名 1>] <比较运算符 > [<表名 2.>][<
列名 2>]
注意,两个表的连接列必须是可比较的，即必须是语义相同的列，否则比较将是无意义的。
当比较运算符为等号（＝）时，称为等值连接，使用其他运算符的连接称为非等值连接。
３、多表连接查询
422009-7-28 Information College · ChangJun
例 32．查询每个学生及其修课的情况。
SELECT * FROM Student INNER JOIN SC
ON Student.Sno = SC.Sno
-- 将 Student与 SC连接起来
Sno Sname Ssex Sage Sdept Sno Cno Grade XKLB
9512101 李勇 男 19 计算机系 9512101 c01 90 必修
9512101 李勇 男 19 计算机系 9512101 c02 86 选修
9512101 李勇 男 19 计算机系 9512101 c06 NULL 必修
9512102 刘晨 男 20 计算机系 9512102 c02 78 选修
9512102 刘晨 男 20 计算机系 9512102 c04 66 必修
9512102 吴宾 女 21 信息系 9512102 c01 82 选修
9512102 吴宾 女 21 信息系 9512102 c02 75 选修
9512102 吴宾 女 21 信息系 9512102 c04 92 必修
9512102 吴宾 女 21 信息系 9512102 c05 50 必修
9521103 张海 男 20 信息系 9521103 c02 68 选修
9521103 张海 男 20 信息系 9521103 c06 NULL 必修
9531101 钱小平 女 18 数学系 9531101 c01 80 选修
9531101 钱小平 女 18 数学系 9531101 c05 95 必修
9531102 王大力 男 19 数学系 9531102 c05 85 必修
432009-7-28 Information College · ChangJun
从这个结果我们可以看到，两个表的连接结果中包含了两个表的全部列,Sno列重复了两次，这是不必要的。因此，在写查询语句时应当将这些重复的列去掉（在 SELECT子句中直接写所需要的列名，而不是写
*）。而且由于在连接后的表中有重复的列名（ Sno
列），因此，我们在 ON子句中对 Sno加上了表名前缀限制，指明是哪个表中的 Sno。
例 33．去掉例 32中的重复列。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,
Cno,Grade,XKLB FROM Student JOIN SC
ON Student.Sno = SC.Sno
例 34．查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
SELECT Sname,Cno,Grade FROM Student JOIN SC
ON Student.Sno = SC.Sno
WHERE Sdept = '计算机系 '
442009-7-28 Information College · ChangJun
可以为表提供别名，其格式为：
<源表名 > [ AS ] <表别名 >
例如：使用别名时例 34可写为：
SELECT Sname,Cno,Grade
FROM Student S JOIN SC ON
S.Sno = SC.Sno
WHERE Sdept = '计算机系 '
例 35．查询信息系修了 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'
452009-7-28 Information College · ChangJun
例 36．查询所有修了 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 '
462009-7-28 Information College · ChangJun
(2) 自连接是一种 特殊的内连接,它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
使用自连接时必须为两个表取别名，使之在逻辑上成为两张表。
例 37．查询与刘晨在同一个系学习的学生的姓名和所在的系。
SELECT S2.Sname,S2.Sdept
FROM Student S1 JOIN Student S2
ON S1.Sdept = S2.Sdept
WHERE S1.Sname = '刘晨 '
AND S2.Sname != '刘晨’
结果为,Sname Sdept
李勇 计算机系王敏 计算机系
472009-7-28 Information College · ChangJun
(3) 外连接在内连接操作中，只有满足连接条件的元组才能作为结果输出，但有时我们也希望输出那些不满足连接条件的元组的信息，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。
语法格式为：
FROM 表 1 LEFT | RIGHT [OUTER] JOIN 表 2 ON
<连接条件 >
482009-7-28 Information College · ChangJun
例 39．查询学生的修课情况，
包括修了课程的学生和没有修课的学生。
SELECT Student.Sno,
Sname,Cno,Grade
FROM Student
LEFT OUTER JOIN SC
ON Student.Sno = SC.Sno
也可以用右外连接实现：
SELECT Student.Sno,
Sname,
Cno,Grade FROM SC
RIGHT OUTER JOIN
Student
ON Student.Sno = SC.Sno
Sno Snam
e
Cno Grad
e
95121
01
李勇 c01 90
95121
01
李勇 c02 86
95121
01
李勇 c06 NULL
95121
02
刘晨 c02 78
95121
02
刘晨 c04 66
95121
02
吴宾 c01 82
95121
02
吴宾 c02 75
492009-7-28 Information College · ChangJun
4、子查询在 SQL语言中，一个 SELECT－ FROM－ WHERE
语句称为一个查询块。
如果一个 SELECT语句是嵌套在一个 SELECT、
INSERT,UPDATE或 DELETE语句中，则称之为子查询或内层查询；而包含子查询的语句则称为主查询或外层查询。子查询要写在圆括号中。
子查询语句可以出现在允许使用表达式的任何地方，但通常情况下一般是用在外层查询的 WHERE子句或 HAVING子句中，与比较运算符或逻辑运算符一起构成查询条件。
502009-7-28 Information College · ChangJun
使用 IN运算符时，如果该表达式的值与集合中的某个值相等，则此测试为 True；如果该表达式与集合中所有的值均不相等，则返回 False。
例 40．查询与“刘晨”在同一个系学习的学生。
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN (SELECT Sdept FROM
Student WHERE Sname = '刘晨 ')
Sno Sname Sdept
9512101 李勇 计算机系
9512102 刘晨 计算机系
9512103 王敏 计算机系
（ 1） 使用子查询进行基于集合的测试使用子查询进行基于集合的测试时，通过运算符 IN或 NOT IN，将一个表达式的值与子查询返回的结果集进行比较。
512009-7-28 Information College · ChangJun
例 41．查询成绩为大于 90分的学生的学号、姓名。
SELECT Sno,Sname FROM Student
WHERE Sno IN
( SELECT Sno FROM SC
WHERE Grade > 90 )
结果为,Sno Sname
9521102 吴宾
9531101 钱小平用多表连接实现：
SELECT Sno,Sname FROM Student
WHERE Student.Sno = SC.Sno AND
Grade > 90
522009-7-28 Information College · ChangJun
例 42．查询选修了“数据库基础”课程的学生的学号、
姓名。
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
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON Course.Cno = SC.Cno
WHERE Cname = '数据库基础 '
532009-7-28 Information College · ChangJun
用子查询进行基于集合的测试时，是先执行子查询，然后再在子查询的结果基础之上执行外层查询。
子查询只执行一次，子查询的查询条件不依赖于外层查询，我们将这样的子查询称为 不相关子查询或嵌套子查询 。
542009-7-28 Information College · ChangJun
（ 2）使用子查询进行比较测试通过比较运算符（ =,<>,<,>,<=,<=），将一个表达式的值与子查询返回的值进行比较。
注意：使用子查询进行比较测试时，要求子查询语句必须是返回单值的查询语句。
例 43．查询修了‘ c02’ 课程且成绩高于此课程的平均成绩的学生的学号和成绩。
SELECT Sno,Grade FROM SC
WHERE Cno = 'c02' and Grade > (
SELECT AVG(Grade) from SC
WHERE Cno = 'c02')
结果为,Sno Grade
9512101 86
9512102 78
552009-7-28 Information College · ChangJun
（ 3）使用子查询进行存在性测试使用子查询进行存在性测试时，一般使用
EXISTS谓词。带 EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。
例 44．查询选修了‘ c01’ 号课程的学生姓名。
SELECT Sname FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE Sno = Student.Sno AND
Cno = 'c01')
562009-7-28 Information College · ChangJun
注意,
1．带 EXISTS谓词的查询是先执行外层查询，
然后再执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。
2．由于 EXISTS的子查询只能返回真或假值，
因此在子查询中指定列名是没有意义的。所以在有 EXISTS的子查询中，其目标列名序列通常都用 ‘ *’ 。
572009-7-28 Information College · ChangJun
上述查询语句的处理过程为：
（ 1）找外层表 Student表的第一行，根据其 Sno
值处理内层查询；
（ 2）用外层的值执行内层查询，如果有符合条件的数据，则 EXISTS返回真值，否则返回假值。
如果 EXISTS返回为真，则外层结果中的当前行数据为符合条件的结果；否则，是不符合条件的结果；
（ 3）顺序处理外层表 Student表中的第 2、
3,? 行数据，直到处理完所有行。
582009-7-28 Information College · ChangJun
基于其他表条件的删除例 3．删除计算机系不及格学生的修课记录。
用子查询实现
DELETE FROM SC
WHERE Grade < 60 AND Sno IN (
SELECT Sno FROM Student
WHERE Sdept = ‘ 计算机系’ )
用多表连接实现
DELETE FROM SC
FROM SC JOIN Student
ON SC.Sno = Student.Sno
WHERE Sdept = ‘ 计算机系’ AND Grade
高级删除操作
592009-7-28 Information College · ChangJun
基于其他表条件的更新例 3．将计算机系全体学生的成绩加 5分。
用子查询实现
UPDATE SC SET Grade = Grade + 5
WHERE Sno IN
(SELECT Sno FROM Student
WHERE Sdept = ‘ 计算机系’ )
用多表连接实现
UPDATE SC SET Grade = Grade + 5
FROM SC JOIN Student ON SC.Sno =
Student.Sno
WHERE Sdept=‘ 计算机系 '
高级更新操作
602009-7-28 Information College · ChangJun
7.5 视图
7.5.1 什么是视图
1、视图的特点视图是一个 虚拟表,其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。
视图并不在数据库中以存储的数据值集形式存在，存储的是 SELECT 语句。
行和列数据在引用视图时动态生成。 SELECT
语句的结果集构成视图所返回的虚拟表。
用户可以用引用表时所使用的方法，在
Transact-SQL 语句中通过引用视图名称来使用虚拟表。
定义视图可以来自当前或其它数据库的一个或多个表，或者其它视图。
612009-7-28 Information College · ChangJun
2、视图的功能使用视图可以实现下列任一或所有功能：
将用户限定在表中的特定行上。
例如，只允许雇员看见工作跟踪表内记录其工作的行。
将用户限定在特定列上。
例如，对于那些不负责处理工资单的雇员，只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列，而不能看见任何包含工资信息或个人信息的列。
将多个表中的列联接起来，使它们看起来象一个表。
聚合信息而非提供详细信息。
例如，显示一个列的和，或列的最大值和最小值。
622009-7-28 Information College · ChangJun
3、视图的优点
（ 1） 简化数据查询语句
（ 2） 使用户能从多角度看待同一数据
（ 3） 提高了数据的安全性
（ 4） 提供一定的逻辑独立性
632009-7-28 Information College · ChangJun
7.5.2 视图的创建、修改与删除
1、企业管理器
2,Transact-SQL
（ 1）创建一个虚拟表，该表以另一种方式表示一个或多个表中的数据。语法,
CREATE VIEW view_name [ ( column
[,...n ] ) ]
AS
select_statement
CREATE VIEW 必须是查询批处理中的第一条语句。
642009-7-28 Information College · ChangJun
1）定义单源视图
2）定义多源视图
3）在已有视图上定义新视图
4）定义带表达式的视图
5）含分组统计信息的视图
652009-7-28 Information College · ChangJun
下例创建具有简单 SELECT 语句的视图。
当需要频繁地查询列的某种组合时，简单视图非常有用。
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
CREATE VIEW titles_view
AS
SELECT title,type,price,pubdate
FROM titles
GO
662009-7-28 Information College · ChangJun
（ 2）从当前数据库中删除一个或多个视图。语法：
DROP VIEW { view } [,...n ]
下例删除 titles_view 视图。
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
672009-7-28 Information College · ChangJun
（ 3）更改一个先前创建的视图。语法：
ALTER VIEW view_name [ ( column [,...n ] ) ]
AS select_statement
下例创建称为 All_authors 的视图，该视图包含全部的作者。权限授予了视图。
CREATE VIEW All_authors (au_fname,au_lname,address,
city,zip)
AS SELECT au_fname,au_lname,address,city,zip
FROM pubs..authors
GO
ALTER VIEW All_authors (au_fname,au_lname,address,
city,zip)
AS SELECT au_fname,au_lname,address,city,zip
FROM pubs..authors WHERE state = 'UT'
682009-7-28 Information College · ChangJun
3、存储过程浏览视图信息
（ 1） sp_help view_name
（ 2） sp_depends view_name
（ 3） sp_helptext view_name
692009-7-28 Information College · ChangJun
7、使用视图加强数据安全对不同用户授予不同的使用许可权通过使用 SELECT语句限制用户对某些底层基本表列的访问。
通过使用 where子句限制用户对某些底层基本表行的访问
