数据库原理与应用第5章

SQLServer20005.1SQL概述5.2数据定义5.3数据查询5.4数据更新5.5视图5.6数据控制机制和语句第五章关系数据库标准语言-SQLSQLServer2000一、SQL的发展

SQL（StructuredQueryLanguage，结构化查询语言）是关系数据库的标准语言

1974年由Boyce和Chamberlin提出

1975年至1979年IBM公司的关系数据库管理系统System-R实现了这种语言

1986年美国国家标准局（ANSI）颁布了SQL的美国标准，1987年国际标准化组织（ISO）通过了这一标准，现这两个标准称为SQL-861989年ANSI公布SQL-89标准

1992年ANSI公布SQL2标准（即SQL-92标准）

1999年ANSI公布SQL3标准5.1SQL概述SQLServer2000二、SQL优点

(1)集多种数据语言功能于一体，是一种综合统一的语言(2)高度非过程化

(3)面向集合的操作方式

(4)以同一种语法结构提供两种使用方式

(5)语言简洁、易学易用

(6)支持三级模式结构5.1SQL概述SQLServer2000注意：

(1)SQL语言是一种关系数据库语言，

提供数据的定义、查询、更新和控制等功能。

(2)SQL语言不是一个应用程序开发语言，只提供对数据库的操作能力，

不能完成屏幕控制、菜单管理、报表生成等功能，可成为应用开发语言的一部分；

(3)SQL语言不是一个DBMS，它属于DBMS语言处理程序。5.1SQL概述SQLServer2000

1、数据定义语言（ＤＤＬ）

创建、修改或删除数据库中各种对象，包括表、视图、索引等。

命令：create、alter、drop

2、查询语言（ＱＬ）

按照指定的组合、条件表达式或排序检索已存在的数据库中数据，不改变数据库中数据。

命令：select

3、数据更新语言

对已经存在的数据库进行元组的插入、删除、修改等操作

命令：insert、update、delete

4、数据控制语言（ＤＣＬ）

用来授予或收回访问数据库的某种特权

命令：grant、revoke三、SQL语句分类5.1SQL概述SQLServer2000操作对象创建语句删除语句修改语句基本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX视图CREATEVIEWDROPVIEW数据库CREATEDATABASEDROPDATABASEALTERDATABASE5.2数据定义SQLServer2000一、基本表的定义和维护1.定义基本表语句基本格式：createtable[<库名>.]<表名>

(<列名><数据类型>[<列级完整性约束条件>][,<列名><数据类型>[<列级完整性约束条件>][，…n][,<表级完整性约束条件>][，…n])；

5.2数据定义类型表示类型说明数值型数据SMALLINT半字长二进制整数。15bits数据INTEGER或INT全字长（四字长）整数。31bits数据DECIMAL(p[，q])十进制数，共p位，其中小数点后q位。0≤q≤p，q=0时可省略不写FLOAT双字长浮点数字符型数据CHARTER(n)或CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串特殊数据类型GRAPHIC(n)长度为n的定长图形字符串VARGRAPHIC(n)最大长度为n的变长图形字符串日期时间型DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间

SQL支持的数据类型5.2数据定义SQLServer2000

列级完整性约束条件——针对属性值的设置的限制条件，只涉及到一个列的数据，有以下5种：notnull约束不允许为空。unique约束不允许该列出现重复的属性值。default约束定义该列的缺省值。default约束的格式为：default<约束名><默认值>for<列名>

如：defaults1男for性别primarykey约束定义该列为主码check约束定义属性值的检查条件。不能直接跟在列后定义，而通过约束条件表达式来设置，check约束的格式为：

constraint<约束名>check(<约束条件>)如：constraintn1check(年龄between19and30)5.2数据定义SQLServer2000表级完整性约束条件——涉及到一个关系中多个列的限制条件，有以下3种：unique约束当要求列组的值不能有重复值时，就需要使用表级唯一性约束。unique约束约束的格式为：

constraint<约束名>unique(属性组)如：constraintu1unique（学号，姓名）primarykey约束

如果一个表的主键内有两个或两个以上的列，则必须使用表约束将这两列加入主键内。不能直接跟在列后定义，而通过约束条件表达式来设置。primarykey约束的格式为：

constraint<约束名>primarykey[clustered](<属性组>)foreignkey

约束

用于定义外码和被参照表。外键的数据类型必须和被参照表中的主码严格匹配。foreignkey约束的格式为：

constraint<约束名>foreignkey(<外码>)references<被参照表名>(<与外码对应的主码名>)5.2数据定义SQLServer2000例1：用SQL在学生数据库中建立如下基本表：学生（学号，姓名，年龄，性别，所在系）要求：学生表中以学号为主码，姓名不能为空，性别只能输“男”或“女”，年龄的缺省值为205.2数据定义createtable学生(学号char(5)primarykey,

姓名char(8)notnull,

年龄smallintdefault20,

性别char(2),

所在系char(20),constraintC1check(性别in(‘男’,‘女’)));SQLServer2000例2：用SQL在学生数据库中建立如下基本表：课程（课程号，课程名，先行课）要求：课程表中以课程号为主码createtable课程(课程号char(5)primarykey,

课程名char(20),

先行课char(5));5.2数据定义SQLServer2000例3：用SQL在学生数据库中建立如下基本表：选课（学号，课程号，成绩）要求：选课表中以学号和课程号为主码，成绩限定在0—100，并且还要求学号与学生表中的学号建立参照关系，课程号与课程表中的课程号建立参照关系。createtable选课(学号char(5),

课程号char(5),

成绩int,constraintC2check(成绩between0and100),constraintC3primarykey(学号,课程号),constraintC4foreignkey(学号)references学生(学号),constraintC5foreignkey(课程号)references课程(课程号));5.2数据定义SQLServer2000二、修改基本表

1、增加属性语句基本格式：

altertable<表名>add(<新列名><数据类型>[<列级完整性约束条件>][，…n])例4：向学生表中增加“家庭地址”和“电话”altertable学生add(家庭地址char(30),电话char(12))5.2数据定义SQLServer20002、删除属性语句基本格式：altertable<表名>dropcolumn<列名>例5：在学生表中删除“家庭地址”和“电话”altertable学生dropcolumn家庭地址,电话5.2数据定义SQLServer2000例6不能执行，因为在“性别”属性上已定义了check约束条件例7可以执行，因为在“姓名”属性上只定义了notnull约束例6：altertable学生dropcolumn性别例7：altertable学生dropcolumn姓名5.2数据定义注：不允许删除已定义列级完整性约束或表级完整性约束的属性，NOTNULL约束除外，要删除这些属性必须先删除该属性上的约束条件SQLServer20003、修改属性语句基本格式：

altertable<表名>modify(<列名><数据类型>[，…，n])注：只能改变宽度，增加notnull约束对于已有数据的表，只能将属性的宽度改为已有数据的宽度

例8：改变学生表中“所在系”的宽度altertable学生modify(所在系char(30))5.2数据定义SQLServer20004、删除完整性约束条件语句基本格式：

altertable<表名>drop<约束名>

例9：向学生表中删除“性别”属性上的约束C1，然后删除“性别”属性altertable学生dropC1altertable学生dropcolumn性别5.2数据定义5.2数据定义5、增加完整性约束条件语句基本格式：

ALTERTABLE<表名>ADDCONSTRAINT<新完整性约束名><完整性约束条件>例10：向课程表中增加完整性约束条件：每门课的学时小于8学时ALTERTABLE课程ADDCONSTRAINTX1CHECK（学时<8）

SQLServer2000三、删除基本表语句基本格式：

droptable

<表名>注：基本表一旦被删除，表中的数据及在此表基础上建立的索引，视图将自动地全部被删除，所以要特别小心

例11：droptable学生

不能执行，因为学生表已被选课表定义为它的参照表不能删除已被定义为其它表的参照表的表5.2数据定义SQLServer2000四、索引

1、索引的作用5.2数据定义通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。SQLServer20002.建立索引的原则

1)索引的建立和维护由DBA和DBMS完成。

2)大表应当建索引，小表则不必建索引。

3)对于一个基本表，不要建立过多的索引。

4)根据查询要求建索引。5.2数据定义SQLServer20003、建立索引语句基本格式：

create[unique][cluster]index<索引名>

on<表名>(<列名１>[asc|desc][，…，n]）

unique：用于指定为表创建唯一索引，即不允许存在索引值相同的两行。

cluster：用于指定创建的索引为聚簇索引。

asc升序，desc降序，默认为asc例12：为学生表建立按学号升序索引createindex学生_学号on学生(学号)例13：为选课表按学号升序和课程号降序建唯一索引createuniqueindex选课_学号on选课(学号asc,课程号desc)5.2数据定义SQLServer20004、删除索引语句基本格式：

dropindex<索引名>

例14：dropindex学生_学号5.2数据定义SQLServer2000语句格式：

select[ALL|DISTINCT]<目标列组>

from<数据源>[where<元组选择条件>][groupby<分组列名>[having<组选择条件>]][orderby<排序列1>asc|desc[，…]]5.3数据查询SQLServer2000select子句：用于指明查询结果集的目标列。目标列可以是直接从数据源中投影得到的属性以及与属性相关的表达式或数据统计的函数表达式，目标列甚至还可以是常数。from子句：用于指明查询的数据源，数据源可以是基本表或视图。where子句：描述选择条件。groupby子句：将查询结果的各行按一列取值相等的原则进行分组，如果有having短语，则查询结果只是满足指定条件的组orderby子句：查询结果按一定顺序排序。5.3数据查询SQLServer2000SQL的查询实例：1、单表查询（简单查询）（1）选择表中的若干列①选择表中的指定列语法格式：select<目标列表达式>[，….n]

from<数据源>

其中列的先后顺序可与表中不一致，可以根据需要改变列的显示顺序。5.3数据查询例1查询全体学生的学号与姓名SELECT学号，姓名FROM学生例2查询全体学生的姓名，学号，所在系SELECT姓名，学号，所在系FROM学生5.3数据查询5.3数据查询②查询全部列有两种方式：在SELECT后列出所有列名，可以改变列的排列顺序将目标列表达式指定为*例3查询全体学生的详细记录SELECT*FROM学生5.3数据查询

③查询经过计算的值<目标列表达式>是与属性列有关的表达式例4查询全体学生的姓名及其出生年份SELECT姓名，2011-年龄FROM学生④<目标列表达式>为常量或函数例5查询全体学生的姓名、出生年份和所在系，出生年份前增加属性列“YearofBirth”，并用小写字母表示所在系名。SELECT姓名，‘YearofBirth’，2011-年龄，ISLOWER（所在系）FROM学生ASNAMEBIRTH出生年份5.3数据查询(2)选择表中的若干元组①消除取值重复的行例6查询选修了课程的学生学号SELECT学号FROM选修DISTINCT5.3数据查询②查询满足条件的元组ⅰ)比较大小用于比较大小的运算符一般包括：=,>,<,>=,<=,!=或<>逻辑运算符NOT可以与比较运算符同用，表示对条件求非。例7查询CS系全体学生的名单SELECT姓名FROM学生WHERE所在系=‘CS’;5.3数据查询例8查询所有年龄在20岁以下的学生姓名及其年龄SELECT姓名，年龄FROM学生WHERE年龄<20（或WHERENOT年龄>=20）;例9查询考试成绩有不及格的学生的学号SELECTDISTINCT学号FROM选修WHERE成绩<60；5.3数据查询ⅱ)确定范围BETWEEN…AND…

和NOTBETWEEN…AND…例10查询年龄在20到23岁之间的学生的姓名、系别和年龄SELECT姓名，所在系，年龄FROM学生WHERE年龄BETWEEN20AND23;5.3数据查询ⅲ)确定集合谓词IN可以用来查找属性值属于指定集合的元组。在SQL中，集合的表示法使用圆括号括起来的一组元素值，元素值之间用逗号隔开。例11查询信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别。SELECT姓名，性别FROM学生WHERE所在系IN（‘IS’，‘MA’，‘CS’）；5.3数据查询ⅳ）字符匹配谓词LIKE可以用来进行字符串的匹配。语法格式：[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符“%”和“_”。其中：%：代表任意长度的字符串。例如：a%b表示以a开头，以b结尾的任意长度的字符串，如：acb，addgb，ab都满足该匹配串。

_:代表任意单个字符。例如：a_b表示以a开头，以b结尾的长度为3的任意字符串。如：acb，afb等。5.3数据查询例12查询学号为95001的学生的详细情况。SELECT*FROM学生WHERE学号LIKE‘95001’；例13查询所有姓“刘”的学生的姓名、学号和性别。SELECT姓名，学号，性别FROM学生WHERE姓名LIKE‘刘%’；5.3数据查询例14

查询姓“欧阳”且全名为三个汉字的学生的姓名。SELECT姓名FROM学生WHERE姓名LIKE‘欧阳__’；例15查询名字中第二个字为“阳”字的学生的姓名和学号SELECT姓名，学号FROM学生WHERE姓名LIKE‘__阳%’；5.3数据查询例16查询所有不姓刘的学生姓名。SELECT姓名FROM学生WHERE姓名NOTLIKE‘刘%’；

如果用户要查询的字符串本身就含有“%”或

“_”，这时就要使用ESCAPE‘<换码字符>’短语对通配符进行转义了。例17查询DB_Design课程的课程号和学分。SELECT课程号，学分FROM课程WHERE课程名LIKE‘DB\_Design’ESCAPE‘\’；SQLServer20005.3数据查询例18查询以‘DB_’开头且倒数第3个字符为i的课程的详细情况。SELECT*FROM课程WHERE课程名LIKE‘DB\_%i__’ESCAPE‘\’SQLServer2000SQLServer20005.3数据查询ⅴ)涉及空值的查询

ISNULL或ISNOTNULL例19查询缺少成绩的学生的学号和相应的课程号。SELECT学号，课程号FROM选课WHERE成绩ISNULL；注：这里的IS不能用=代替。SQLServer20005.3数据查询ⅵ）多重条件查询

逻辑运算符AND

和OR可用来联结多个查询条件，AND的优先级高于OR，但用户可以用括号改变优先级。例20查询计算机系年龄在20岁以下的学生的姓名。SELECT姓名FROM学生WHERE所在系=‘CS’AND年龄<20;SQLServer20005.3数据查询对查询结果排序

使用ORDERBY子句来指定按照一个或多个属性列的升序(ASC)或降序(DESC)重新排列查询结果中的行，其中升序(ASC)为默认值。例21查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECT学号，成绩FROM选课WHERE课程号=‘3’ORDERBY成绩DESC；SQLServer2000注：对于空值，若按升序排，含空值的元组将最后显示；若按降序排，空值的元组将最先显示。例22查询全体学生的情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROM学生ORDERBY所在系，年龄DESC;5.3数据查询SQLServer20005.3数据查询使用集函数

SQL提供的集函数主要有：COUNT([DISTINCT|ALL]*)统计元组个数COUNT([DISTINCT|ALL]<列名>)统计一列中值的个数SUM([DISTINCT|ALL]<列名>)计算一列值的总和（此列必须是数值型的）

AVG([DISTINCT|ALL]<列名>)计算一列值的平均值（此列必须是数值型的）MAX([DISTINCT|ALL]<列名>)求

一列值中的最大值MIN([DISTINCT|ALL]<列名>)求

一列值中的最小值SQLServer20005.3数据查询例23查询学生总人数。SELECTCOUNT(*)FROM学生；例24查询选修了课程的学生人数SELECTCOUNT(DISTINCT学号)FROM选课；学生人数SQLServer2000例25计算选修了1号课程的学生平均成绩。SELECTAVG(成绩)FROM选课WHERE课程号=‘1’；例26查询选修1号课程的学生最高分数。SELECTMAX(成绩)FROM选课WHERE课程号=‘1’；注：除count(*)外，列中的空值先去掉再计算5.3数据查询SQLServer2000例27：求选修了课程号为“c1”且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出SELECT学号，成绩*0.8FROM选课WHERE课程号=‘c1’AND成绩BETWEEN80AND90例28：检索数学系或计算机系姓“陈”的学生的信息

SELECT*FROM学生WHERE所在系IN(‘数学’，‘计算机’)AND姓

名LIKE‘陈%’SQLServer20005.3数据查询（5）对查询结果分组GROUPBY

子句和HAVING子句来实现分组统计。例29求各个课程号及相应的选课人数。SELECT课程号，COUNT(学号)FROM选课GROUPBY课程号；例30查询选修了3门以上课程的学生学号。SELECT学号FROM选课GROUPBY学号HAVINGCOUNT(*)>3SQLServer20005.3数据查询WHERE子句与HAVING短语的区别：WHERE子句与HAVING短语都用来做选择，其区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组，HAVING短语作用于组，从中选择满足条件的组。当WHERE子句与HAVING短语同时出现时，先运行WHERE子句选择元组，再进行分组统计。例如上例不能写成：

select学号from选课

groupby学号

wherecount(*)>3SQLServer2000例31：求各门课的课程号，最高分，最低分，平均分SELECT

课程号,MAX(成绩)AS最高分,MIN(成绩)AS最低分,AVG(成绩)AS平均分FROM选课GROUPBY课程号例32：求每个学生的学号、姓名及平均分SELECT

学生.学号,姓名,AVG(成绩)AS平均分FROM选课,学生WHERE学生.学号=选课.学号GROUPBY学生.学号,姓名5.3数据查询SQLServer20002、连接查询连接查询的数据源为多个表，连接条件通过where子句表达，连接条件和元组选择条件之间用and衔接。1)等值和非等值连接其一般格式为：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

比较运算符：=、>、<、>=、<=和!=；列名称为连接字段。

此外，连接条件还可以使用下面形式：[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

当连接运算符为“=”时，称为等值连接，其它运算符称为非等值连接。连接条件中的各连接字段类型必须是可比的，但不必是相同的。5.3数据查询SQLServer20005.3数据查询例33：查询每个学生及其选修课程的情况

SELECT学生.*,选课.*FROM学生,选课

WHERE学生.学号=选课.学号

连接运算有两种特殊的情况，一种为自然连接，另一种为广义笛卡尔积连接。SQLServer2000如果使用了一个以上的表，但没有where子句，则结果为广义笛卡尔积，其连接结果会产生一些没有意义的元组。例如：

SELECT学生.学号,课程号,成绩FROM学生,选课WHERE学生.学号=选课.学号

结果为学生表与选课表中对应的记录

SELECT学生.学号,课程号,成绩FROM学生,选课

结果为笛卡尔积5.3数据查询SQLServer20005.3数据查询

若在等值连接中把目标列中重复的属性列去掉，则为自然连接。如：上例用自然连接完成：SELECT学生.学号，姓名，性别，年龄，所在系，课程号，

成绩FROM学生，选课WHERE学生.学号=选课.学号SQLServer2000例34：查询学生的学号，姓名及所选修的课程名及成绩

SELECT学生.学号,姓名,课程名,成绩FROM学生,课程,选课

WHERE学生.学号=选课.学号and课程.课程号=选课.课程号例35：查询考试成绩有不及格的学生的学号、姓名SELECTDISTINCT学生.学号,姓名FROM学生,选课WHERE成绩<60AND学生.学号=选课.学号5.3数据查询SQLServer2000例36：查询每一门课的间接先行课（即先行课的先行课）SELECTA.课程号,A.课程名,B.先行课FROM课程A,课程BWHEREA.先行课=B.课程号2)自身连接

连接操作不只在两个表之间进行，一个表内也可以进行自身连接5.3数据查询SQLServer2000课程号课程名先行课c1 计算机导论NULLc2 C语言 c1c3 数据结构 c2c4 数据库原理 c3c5 软件工程 c4c6 高等数学NULLc7 DB_Design c3A课程号课程名先行课c1 计算机导论NULLc2 C语言 c1c3 数据结构 c2c4 数据库原理 c3c5 软件工程 c4c6 高等数学NULLc7 DB_Design c3B5.3数据查询SQLServer20003)外连接内连接：结果集中只保留了符合连接条件的元组，而排除了两个表中没有匹配的元组情况，前面所举的例子均属内连接

如果要求查询结果集中保留非匹配的元组，就要执行外部连接操作。左外部连接：结果集中保留连接表达式左表中的非匹配记录。左外部连接符号为“＊=”右外部连接：结果集中保留连接表达式右表中的非匹配记录。右外部连接符号为“=＊”外部连接中不匹配的分量用NULL表示。5.3数据查询SQLServer2000职工号姓名性别年龄所在部门

部门号部门名称电话1010李勇男201111生产科5661011刘晨女19

12计划科5781012王敏女221213一车间4671014张立男211314科研所

5.3数据查询内连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门=部门.部门号；SQLServer2000左外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门*=部门.部门号；右外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门=*部门.部门号；5.3数据查询SQLServer2000例37：所有学生的选课情况（包括没有选课的学生）

SELECT学生.学号,课程号,成绩FROM学生,选课

WHERE学生.学号*=选课.学号

例38：查询全部选课情况（包括学生表中没有的学生的选课信息）SELECT学生.学号,课程号,成绩FROM学生,选课WHERE学生.学号=*选课.学号

5.3数据查询SQLServer2000一个SELECT…FROM…WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询叫嵌套查询。

SQL语言允许多层嵌套查询，即：一个子查询中还可以嵌套其它子查询。注意：子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序。3、嵌套查询5.3数据查询有以下4种子查询：

带IN谓词的子查询带比较运算符的子查询带ANY或ALL的子查询带EXISTS谓词的子查询5.3数据查询5.3数据查询1）带IN谓词的子查询

使用IN运算符的子查询用于判断某个属性列的值是否在子查询的结果中。例39查询与“刘晨”在同一个系学习的学生信息。SELECT*FROM学生WHERE所在系IN(SELECT所在系

FROM学生

WHERE姓名=‘刘晨’）也可以用自身连接来完成：SELECTS1.*FROM学生S1,学生S2WHERES1.所在系=S2.所在系ANDS2.姓名=‘刘晨’；5.3数据查询例40求选修了高等数学的学生学号和姓名。SELECT学号，姓名FROM学生WHERE学号IN(SELECT学号FROM选课

WHERE课程号IN(SELECT课程号

FROM课程

WHERE课程名=‘高等数学'))；SQLServer2000也可以使用连接查询：SELECT学生.学号，姓名FROM学生，课程，选课WHERE学生.学号=课程.学号AND课程.课程号=选课.课程号AND课程.课程名='高等数学'；SQLServer2000例41：求选修了“计算机导论”的学生学号

SELECT学号FROM选课WHERE课程号IN(SELECT课程号FROM课程WHERE课程名='计算机导论')此例也可用连接查询：SELECT学号

FROM选课,课程WHERE选课.课程号=课程.课程号and课程名='计算机导论'5.3数据查询SQLServer2000例42：求没有选修“计算机导论”的学生学号SELECT学号FROM学生WHERE学号NOTIN(SELECT学号FROM选课WHERE课程号IN(SELECT课程号FROM课程WHERE课程名='计算机导论'))5.3数据查询SQLServer2000如下语句是否正确:SELECT学号FROM选课WHERE课程号NOTIN(SELECT课程号FROM课程WHERE课程名='计算机导论')(2)SELECT学号FROM选课,课程WHERE选课.课程号=课程.课程号AND课程名<>'计算机导论'5.3数据查询SQLServer20005.3数据查询2)使用比较运算符的子查询例39查询与“刘晨”在同一个系学习的学生信息。SELECT*FROM学生WHERE所在系IN(SELECT所在系

FROM学生

WHERE姓名=‘刘晨’）可以用“=”代替INSQLServer20005.3数据查询例43求c1课程成绩高于“王红”的学生学号，成绩SELECT学号,成绩FROM选课WHERE课程号=‘c1’AND成绩

>

(SELECT成绩FROM选课WHERE课程号='c1'AND学号=(SELECT学号FROM学生WHERE姓名='王红'))SQLServer20005.3数据查询3)使用ANY或ALL的嵌套查询

使用ANY或ALL操作符时，必须与比较符配合使用，其格式为：<字段><比较符>[ANY|ALL]<子查询>操作符语意>ANY大于子查询结果中的某个值，即表示大于查询结果中最小值>ALL大于子查询结果中的所有值，即表示大于查询结果中最大值<ANY小于子查询结果中的某个值，即表示小于查询结果中最大值<ALL小于子查询结果中的所有值，即表示小于查询结果中最小值>=ANY大于等于子查询结果中的某个值，即表示大于等于结果集中最小值>=ALL大于等于子查询结果中的所有值，即表示大于等于结果集中最大值<=ANY小于等于子查询结果中的某个值，即表示小于等于结果集中最大值<=ALL小于等于子查询结果中的所有值，即表示小于等于结果集中最小值=ANY等于子查询结果中的某个值，即相当于IN=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY不等于子查询结果中的某个值，!=(或<>)ALL不等于子查询结果中的任何一个值，即相当于NOTINSQLServer2000例44：求其他系中比计算机系某一学生年龄小的学生的信息。（即求年龄小于计算机系年龄最大者的学生）SELECT*FROM学生WHERE所在系<>’计算机’AND年龄<ANY(SELECT年龄FROM学生WHERE所在系='计算机')

此例也可用max()：SELECT

*FROM学生WHERE所在系<>'计算机'AND年龄<(SELECTMAX(年龄)FROM学生WHERE所在系=‘计算机’)

5.3数据查询SQLServer2000例45：检索所有课程成绩都在80分及80分以上的学生学号、姓名如下语句是否正确:SELECT学号,姓名FROM学生WHERE

学号IN(SELECT学号FROM选课WHERE成绩>80)(2)SELECT学号,姓名FROM学生

WHERE

学号NOTIN(SELECT学号FROM选课WHERE成绩<80)5.3数据查询正确语句:SELECTDISTINCT学生.学号,姓名FROM学生,选课

WHERE学生.学号=选课.学号AND学号NOTIN(SELECT学号FROM选课WHERE成绩<80OR成绩ISNULL)

SQLServer2000例46：求其他系中比计算机系学生年龄都小的学生的信息。（即求年龄小于计算机系年龄最小者的学生）SELECT*FROM学生WHERE年龄<ALL(SELECT年龄FROM学生WHERE所在系='计算机')AND所在系<>’计算机’也可以用集函数表示：SELECT*FROM学生WHERE年龄<(SELECTMIN(年龄)

FROM学生WHERE所在系='计算机')AND所在系<>’计算机’5.3数据查询SQLServer2000例47：求选课门数最多的学生的学号SELECT学号FROM选课GROUPBY学号HAVINGCOUNT(课程号)>=ALL(SELECTCOUNT(课程号)FROM选课GROUPBY学号)5.3数据查询SQLServer2000例48：求选课门数最多的学生的学号、姓名SELECT学号,姓名FROM学生WHERE学号IN(SELECT学号FROM选课

GROUPBY学号HAVINGCOUNT(课程号)>=ALL(SELECTCOUNT(课程号)FROM选课GROUPBY学号))5.3数据查询SQLServer2000或：SELECT学生.学号,姓名FROM学生,选课

WHERE学生.学号=选课.学号GROUPBY学生.学号,姓名

HAVINGCOUNT(课程号)>=ALL(SELECTCOUNT(课程号)FROM选课GROUPBY学号)5.3数据查询SQLServer20005.3数据查询4）使用EXISTS操作符的嵌套查询

EXISTS代表存在量词。EXISTS操作符后子查询的结果集中如果不为空，则产生逻辑真值“true”，否则产生假值“false”。例49求选修了C2课程的学生姓名。

也可以用连接查询实现：SELECT姓名FROM学生，选课WHERE学生.学号=选课.学号AND课程号=‘C2’;SELECT姓名FROM学生

WHEREEXISTS(SELECT*FROM选课

WHERE学生.学号=学号AND课程号='C2')；SQLServer20005.3数据查询例50求没有选修C2课程的学生姓名。SELECT姓名

FROM学生

WHERENOTEXISTS(SELECT*FROM选课

WHERE学生.学号=学号AND课程号='C2')；查询与“刘晨”在同一个系学习的学生信息SELECT*FROM学生S1WHEREEXISTS(SELECT*FROM学生S2WHERES1.所在系=所在系AND姓名=‘刘晨’）5.3数据查询5.3数据查询

SQL语言没有全称量词，但是总可以把带有全称量词的谓词转换为带有存在量词的谓词：例51查询选修了全部课程的学生的姓名SELECT姓名FROM学生

WHERENOTEXISTS(SELECT*FROM课程

WHERENOTEXISTS(SELECT*

FROM选课

WHERE学生.学号=学号AND课程.课程号=课程号))；5.3数据查询例52求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。

SELECT学号，姓名

FROM学生

WHERENOTEXISTS(SELECT*FROM选课选课1WHERE选课1.学号='S2'ANDNOTEXISTS(SELECT*FROM选课选课2WHERE学生.学号=选课2.学号AND选课2.课程号=选课1.课程号)；SQLServer2000例53：求选修了C1课程或选修了C2课程的学生的学号SELECT学号FROM选课WHERE课程号='C1'OR课程号='C2'4、组合查询或：SELECT学号FROM选课WHERE课程号='C1'

UNIONSELECT学号FROM选课WHERE课程号='C2'5.3数据查询5.3数据查询例54：求选修了C1课程又选修了C2课程的学生的学号如下语句是否正确：SELECT学号FROM选课WHERE课程号='C1'AND课程号='C2'应该为SELECT学号FROM选课WHERE课程号=‘C1’AND学号IN(SELECT学号FROM选课WHERE课程号='C2')SQLServer20001、插入数据

(1)插入单个元组语句格式：

INSERTINTO<表名>[(<属性列1>[,<属性列2>…)]

VALUES

(<常量1>[,<常量2>]…)注：a、在INTO子句中若没有指明任何列名，则在VALUES子句中必须在每个列上均有值，并且要与表中属性的逻辑顺序对应例：

INSERTINTO选课VALUES(‘98008’,‘c5’,70)INSERTINTO选课VALUES(‘98010’,‘c5’)INSERTINTO选课VALUES(‘c2’,‘98008’,70)

5.4数据更新对错错SQLServer2000b、如果某些列在INTO子句中没有出现，则新插入的记录在这些列上取空值，但如果这些列在表定义是定义为NOTNULL，则不能在INTO子句中省略例：

INSERTINTO选课(学号,课程号)VALUES(‘98008’,‘c4’)

INSERTINTO选课(学号,成绩)VALUES(‘98008’,70)c、INTO子句中列名与VALUES子句中的常量要求逻辑顺序一致例：INSERTINTO选课(学号,课程号)VALUES(‘98008’,‘c4’)

INSERTINTO选课(学号,课程号)VALUES(‘c1’,’98008’)5.4数据更新对错对错SQLServer2000例55将一个新学生记录(学号：‘98010’，姓名：‘张三’，年龄：20，所在系：‘计算机系’)插入到学生表中。INSERT

INTO学生

VALUES(‘98010’，‘张三’，20，‘计算机系’)；5.4数据更新SQLServer2000(2)插入子查询的结果集语句格式：INSERTINTO<表名>[(<属性列1>[,<属性列2>…)]<子查询>;例56：求每个系学生的平均年龄，并要求将结果存入数据库中。此例要求先建立系平均年龄表，再将各系的平均年龄写入此表中CREATETABLE系平均年龄(系名称CHAR(10),

平均年龄SMALLINT);INSERTINTO系平均年龄

SELECT所在系,AVG(年龄)FROM学生GROUPBY所在系5.4数据更新SQLServer2000

2、修改数据

语句格式：

UPDATE<表名>

SET<列名1>=<表达式1>[,<列名2>=<表达式2>][,…n][WHERE<条件>]注：

a、如果无WHERE子句，则表示修改表中全部元组例57：将学生表中每个学生年龄加1UPDATE学生SET年龄=年龄+15.4数据更新SQLServer2000b、可以通过WHERE子句中的条件表达式修改某一个元组的值例57将学生95001的年龄改为22岁。UPDATE学生SET年龄=22WHERE学号=‘95001’;c、WHERE子句中可以嵌入子查询

例58：将“高等数学”的成绩加5分

UPDATE选课SET成绩=成绩+5WHERE课程号IN(SELECT课程号FROM课程WHERE课程名='高等数学')5.4数据更新SQLServer20003、删除数据

语句格式：

DELETEFROM<表名>[WHERE<条件>]注：

a、如果无WHERE子句，则表示删除表中全部元组例59删除所有学生选课记录DELETEFROM选课5.4数据更新SQLServer2000b、可以通过WHERE子句中的条件表达式删除一个元组的值例60删除学号为95019的学生记录DELETEFROM学生WHERE学号=‘95019’;c、WHERE子句中可以嵌入子查询5.4数据更新SQLServer2000DELETEFROM学生WHERE所在系=‘数学’5.4数据更新d、一个DELETE语句只能删除一个表中的元组，即FROM子句中只能有一个表名，不允许有多个表名。例61：删除“数学系”的学生记录及该系学生的所有选课记录

DELETEFROM选课WHERE学号IN(SELECT学号

FROM学生WHERE所在系=‘数学’)

SQLServer2000

视图是从一个或者多个表或视图中导出的表，和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但视图是一个虚拟表，所以数据库中只存放视图的定义，而不存放视图的数据，这些数据来源于基本表。

使用视图的优点和作用

①可以使视图集中数据、简化和定制不同用户对数据库的不同数据要求。②使用视图可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。5.5视图SQLServer2000③视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。④视图大大地简化了用户对数据的操作。⑤视图可以让不同的用户以不同的方式看到不同或者相同的数据集。⑥在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化不会对应用程序产生不良的影