第 3 章 关系数据库标准语言SQL

3.1.1 语言的发展及标准化 • 在 70 年代初， E.F.Codd 首先提出了关系模型。 70 年代中期， IBM 公司在研制 SYSTEM R 关系数据库管理系统中研制了 SQL 语言，最早的 SQL 语言（叫 SEQUE

L2 ）是在 1976 年 11 月的 IBM Journal of R&D 上公布的。• 1979 年 ORACLE 公司首先提供商用的 SQL ， IBM 公司在 DB2 和 SQL/DS 数据库系统中也实现了 SQL 。• 1986 年 10 月，美国 ANSI 采用 SQL 作为关系数据库管理系统的标准语言 (ANSI X3. 135-1986 ），后为国际标准化组织（ ISO ）采纳为国际标准。

3.1 SQL 语言的基本概念与特点

• 1989 年，美国 ANSI 采纳在 ANSI X3.135-1989 报告中定义的关系数据库管理系统的 SQL 标准语言，称为 ANSI SQL 89 。

• 1992 年， ISO 又推出了 SQL92 标准，也称为 SQL2 。• 目前 SQL99( 也称为 SQL3) 在起草中，增加了面向对象的功能。• 结构化查询语言 SQL(Structured Query Language) 是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言，广泛应用于各种数据库。

一、关系数据库三级模式结构3.1.2 SQL 语言的基本概念

SQL

视图 1

基本表 2

视图 2

基本表 3 基本表 4基本表 1

存储文件 1 存储文件 2

外模式

模式

内模式

基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 存储文件的物理结构及存储方式等组成了关系数据库的内模式。存储文件的物理结构及存储方式等不同数据库管理系统往往是不同的，一般也是不公开的。

外模式对应于视图（ View ）和部分基本表（ Base Table ） ; 模式对应于基本表 ; 内模式对应于存储文件。

视图和基本表是 SQL 语言的主要操作对象，用户可以用 SQL 语言对视图和基本表进行各种操作。在用户眼中，视图和基本表都是关系表，而存储文件对用户是透明的。

3.1.3 SQL 语言的主要特点

1 、综合统一SQL 集数据定义语言 DDL 、数据操纵语言 DML 、数据控制语言 DCL 的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括：定义关系模式，插入数据，建立数据库 ;对数据库中的数据进行查询和更新 ;数据库重构和维护 ;数据库安全性、完整性控制。

SQL 集数据查询、数据操纵、数据定义 和数据控制功能于一体，主要特点包括：

2 、高度非过程化非关系数据模型的数据操纵语言是“面向过程”的，用“过程化”语言完成某项请求，必须指定存储路径。SQL 进行数据操作，只要提出“做什么”，而无须指明“怎么做”，因此无需了解存储路径。存储路径的选择以及 SQL 的操作过程由系统自动完成。这样可以减轻用户的负担，也提高了数据独立性。3 、面向集合的操作方式

非关系数据模型得采用提面向记录的操作方式，操作对象是一条记录。SQL 采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

4 、以同一种语法结构提供多种使用方式

SQL 既是独立的语言，又是嵌入式语言。作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入 SQL 命令对数据库进行操作 ;作为嵌入式语言， SQL 语句能够嵌入到高级语言（如 C 、 C++ 、 Jav

a ）程序中，供程序员设计程序时使用。而在两种不同的使用方式下， SQL 的语法结构基本上是一致的。

4 、以同一种语法结构提供多种使用方式

5 、语言简洁，易学易用SQL 功能极强，完成核心功能只用了 9 个动词，接近英语口语，所以容易学习，易于使用。

数据查询数据定义数据操纵数据控制

SELECTCREATE DROP ALTERINSERT UPDATE DELETEGRANT REVOKE

3.2.1 字段数据类型 整数数据类型 : bigint,int,smallint,tinyint 精确数值类型 : numeric,decimal 近似浮点数值数据类型 : float,real 日期时间数据类型 : datetime,smalldatetime 字符串数据类型 : char,varchar,text Unicode字符串数据类型 : nchar,nvarchar,ntext 二进制数据类型 : binary 、 varbinary 、 image 货币数据类型 : money,smallmoney 标记数据类型 : timestamp,uniqueidentifier

具体见书 P74－表 3.1

3.2 SQL 数据定义

1 、定义基本表 CREATE TABLE < 表名 >( <列名 > < 数据类型 > [列级完整性约束条件 ] [, <列名 > < 数据类型 > [列级完整性约束条件 ]] … [,< 表级完整性约束条件 >])

3.2.2 创建、修改和删除数据表

建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由 DBMS 自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

（ 1 ）实体完整性• 主码 (Primary Key)要求： ① 一个基本表中只能定义一个 Primary Key约束 ; ② 作为 Primary Key 的任何列不能出现空值 ; ③ 定义为表级约束的语法格式： [constraint 约束名 ] Primary Key (列 1 [ASC|DESC]［，……n])• 空值 (Null/Not Null)要求：该约束只能用于列级约束。• 唯一值 (Unique)要求：① 表示某一列或多个列的组合上的取值必须唯一，系统会自动 为其建立唯一索引 ; ② 定义为表级约束的语法格式： [constraint 约束名 ] Unique [Clustered|NonClustered] (列 1 [ASC|DESC]［，…… n])

例 1 、建立 Student 表 (Sno char(9) 主码、 Sname char(20) 不为空、 Ssex char(2) 、 Sage smallint 、 Sdept char(20))

CREATE TABLE Student ( Sno char(9) PRIMARY KEY, /* 在列级定义主码 */ Sname char(20) NOT NULL, Ssex char(2), Sage smallint, Sdept char(20) )CREATE TABLE Student ( Sno char(9), Sname char(20) NOT NULL, Ssex char(2), Sage smallint, Sdept char(20), /* 在表级定义主码 */ PRIMARY KEY (Sno) )

例 2 、建立 SC 表 ( Sno char(9) 不为空、 Cno char(4) 不为空、 Grade Smallint) ，将 Sno,Cno属性组定义为主码。

CREATE TABLE SC ( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint, PRIMARY KEY (Sno,Cno) /* 只能在表级定义主码 */ )

（ 2 ）参照完整性通过 FOREIGN KEY短语定义哪些列为外码，用 REFERENCES短语指明这些外码参照哪些表的主码。表级约束的语法格式为： [constraint 约束名 ] Foreign Key (子表列 1,……n) References 主表名 ( 主表列 1,……n) [ON DELETE {CASCADE|NO ACTION}] [ON UPDATE {CASCADE|NO ACTION}]

其中： CASCADE 是级联操作 ;

NO ACTION 是拒绝操作。

例 1 、建立 SC 表 ( Sno char(9) 不为空、 Cno char(4) 不为空、 Grade Smallint) ， (Sno,Cno) 是主码， Sno 和 Cno 分 别参照引用 Student 表的主码和 Course 表的主码。CREATE TABLE SC ( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint, PRIMARY KEY (Sno,Cno) /* 在表级上定义实体完整性 */ FOREIGN KEY (Sno) REFERENCES Student (Sno), /* 在表级上定义参照完整性 */ FOREIGN KEY (Cno) REFERENCES Course(Cno) )

例 2 、显示说明参照完整性的违约处理示例。CREATE TABLE SC( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint , PRIMARY KEY(Sno,Cno), Foreign Key (Sno) References Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE, Foreign Key (Cno) References Course(Cno) ON DELETE NO ACTION /*当删除 Course 表中的元组造成了与 SC 表不一致时拒绝删除 */ ON UPDATE CASCADE /*当更新 Course 表中的 Cno时，级连更新 SC 表中相应的元组 */)

（ 3 ）用户定义完整性Check 可用于定义用户自定义的完整性约束规则。语法格式为： [Constraint 约束名 ] CHECK ( 条件 )

例 1 、建立 Student 表，要求 Ssex列只允许取“男”或“女”。CREATE TABLE Student ( Sno char(9) PRIMARY KEY, /* 在列级定义主码 */ Sname char(20) NOT NULL, Ssex char(2) CHECK (Ssex IN (‘男’ ,’女’ )), Sage smallint, Sdept char(20) )

例 2 、建立 SC 表，要求 Grade 的值应该在 0 和 100 之间。CREATE TABLE SC ( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint CHECK (Grade>=0 and Grade<=100), PRIMARY KEY (Sno,Cno) /* 只能在表级定义主码 */ )

例 3 、建立 Student 表，要求当学生的性别是男时，其名字不能以 Ms.打头。CREATE TABLE Student ( Sno char(9), Sname char(20) NOT NULL, Ssex char(2), Sage smallint, Sdept char(20) ， PRIMARY KEY (Sno), CHECK (Ssex=‘女’ OR Sname NOT LIKE ‘Ms.%’ ) )

（ 4 ）完整性约束的命名SQL 在 CREATE TABLE 语句中提供了完全性约束命名子句 Constraint ，用来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件。例、建立学生登记表 Student ，要求学号在 90000～ 99999 之间，姓名不能取空值，年龄小于 30, 性别只能是“男”或“女”，主码为Sno 。CREATE TABLE Student

( Sno numeric(6) CONSTRAINT C1 Check (Sno BETWEEN 90000 AND 99999), Sname char(20) NOT NULL, Ssex char(2) CONSTRAINT C2 Check (Ssex IN (‘男’ ,’女’ )) Sage smallint CONSTRAINT C3 Check (Sage<30), Sdept char(20) ， Constraint StuentKey PRIMARY KEY (Sno) )

S 学号 SNO 姓名 SN 性别 SEX 年龄 AGE 系别 DEPT

S1 李涛 男 19 信息S2 王林 女 18 计算机S3 陈高 女 21 自动化S4 张杰 男 17 自动化S5 吴小丽 女 19 信息S6 徐敏敏 女 20 计算机

完整性示例“ 学生 - 课程”数据库中包括三个表：

C课程号 CNO 课程名 CN 学分 CT

C1 C 语言 4C2 离散数学 2C3 操作系统 3C5 数据结构 4C6 数据库 4C7 汇编语言 3C8 信息基础 2

SC 学号SNO

课程号SNO

成绩SCORE

S1 C1 90S1 C2 85S2 C1 84S2 C2 94S2 C3 83S3 C1 73S3 C7 68S3 C4 88S3 C5 85S4 C2 65S4 C5 90S4 C6 79S5 C2 89

例 1 、建立一个“学生”表 S ，它由学号 SNO 、姓名 SN 、性别 SEX 、年龄 AGE 、所在系 DEPT五个属性组成，其中学号属性为主键，姓名、年龄与性别不为空，假设姓名没有唯一并建立惟一索引，并且性别只能在“男”与“女”中选一个，年龄不能小于 0 。

返回本节首页

CREATE TABLE S ( SNO CHAR(5) PRIMARY KEY, SN VARCHAR(8) NOT NULL, SEX CHAR(2) NOT NULL CHECK (SEX IN ('男 ','女 ')), AGE INT NOT NULL CHECK (AGE>0), DEPT VARCHAR(20), CONSTRAINT SN_U UNIQUE(SN) )

[例2] 建立“课程”表 C ，它由课程号（ CNO ）、课程名（ CN ）、学分（ CT ）三个属性组成。CNO 为该表主键，学分大于等于 1 。

CREATE TABLE C ( CNO CHAR(5) NOT NULL PRIMARY KEY, CN VARCHAR(20), CT INT CHECK (CT>=1) )

CREATE TABLE SC ( SNO CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES S(SNO), CNO CHAR(5) NOT NULL, SCORE NUMERIC(3), CONSTRAINT S_C_P PRIMARY KEY (SNO,CNO), CONSTRAINT C_F FOREIGN KEY(CNO) REFERENCES C(CNO) )

[例 3] 建立“选修”关系表 SC ，定义 SNO,CNO 为 SC 的外部键， (SNO,CNO) 为该表的主键。

练习：建立如下表 Student(Sno,Sname,Ssex,Sage,Sdept) 要求： Sno 为主码， Sage 在 15～ 45 间 ( 包括 15和 45) ， Ssex 只能取男或女，默认值为男。 Course(Cno,Cname,Cpno,Ccredit) 要求： Cno 为主码。 Sc(Sno,Cno,Grade) 要求： Sno 和 Cno 为主码，利用 Sno 建立与表 Student 的关联、 Cno 建立与表 Course 的关联。 Grade 只能接收空值或 0～ 100( 包括 0 和 100)的值。

CREATE TABLE Student ( Sno CHAR(5) NOT NULL PRIMARY KEY, Sname VARCHAR(8), Ssex CHAR(2) DEFAULT ‘男’ CHECK (Ssex IN (‘男’ ,‘女’ )), Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Sdept CHAR(2) )CREATE TABLE Course ( Cno CHAR(2) NOT NULL PRIMARY KEY, Cname VARCHAR(20), Cpno CHAR(2), Ccredit SMALLINT)CREATE TABLE Sc ( Sno CHAR(5) NOT NULL FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(2) NOT NULL, Grade SMALLINT CHECK((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)), PRIMARY KEY(Sno,Cno), FOREIGN KEY(Cno) REFERENCES Course(Cno))

ALTER TABLE 表名 ALTER COLUMN 列名 新类型 [ ( 长度 [ , 小数位 ] ) ][ NULL | NOT NULL ]] ADD 新列名 AS 表达式或别名 [ ,...n ] [ WITH CHECK | WITH NOCHECK ]

ADD 完整性约束 DROP [ CONSTRAINT ] 约束名 | COLUMN 列名 } [ ,...n ] [CHECK | NOCHECK] CONSTRAINT { 约束名 [ ,...n ] }

2 、修改基本表

其中 :< 表名 > 指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件或原有列，ALTER子句用于修改原有的列定义。{CHECK|NOCHECK}CONSTRAINT 指定启用或禁用 constraint_name 。如果禁用，将来插入或更新该列时将不用该约束条件进行验证。此选项只能与 FOREIGN KEY 和 CHECK 约束一起使用。

[例 4] 向 S 表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE S ADD SCOME DATETIME

[例 5] 将年龄的数据类型改为半字长整数。 ALTER TABLE S ALTER COLUMN AGE SMALLINT

[例 6] 删除例 4 增加的“入学时间”列。 ALTER TABLE S DROP COLUMN SCOME

[例 7] 禁止 SC 中的参照完整性 C_F 。 ALTER TABLE S NOCHECK CONSTRAINT C_F

对学生表进行如下操作：• 将姓名列的长度先改为 6，不允许有空值。• 增加两列，性别 (char(2)) 电话 (varchar(11)) ；• 再将新建的两列删除。

练习：

alter table 学生 alter column 姓名 char(6) not nullgoalter table 学生 add 性别 char(2), 电话 varchar(11)goalter table 学生 drop column 性别 ,电话go

例 : 1) 为 xs表添加主键约束 ,该主键约束由学号单列组成 , 约束 名为 pk_xh. 2) 修改 xs表的主键约束 ,该主键约束由学号和姓名两列 组成 , 约束名为 pk_xh_xm.1)) ALTER TABLE xs ADD CONSTRAINT pk_xh PRIMARY KEY ( 学号 ) 2) ALTER TABLE xs DROP CONSTRAINT pk_xh GO ALTER TABLE xs ADD CONSTRAINT pk_xh_xm PRIMARY KEY( 学号 ,姓名 )

例 :为选课表添加外键约束 ,约束名为 FK_kcbh,使之与课程表建 立级联删除和级联更新操作 .ALTER TABLE 选课表ADD CONSTRAINT FK_kcbh FOREIGN KEY ( 课程编号 ) REFERENCES 课程表 ( 课程编号 )ON DELETE CASCADEON UPDATE CASCADEUPDATE 课程表SET 课程编号='1111'WHERE 课程编号 ='0001'DELETE 课程表WHERE 课程编号 ='1111'ALTER TABLE 选课表DROP CONSTRAINT FK_kcbh

例 :为选课表添加外键约束 ,约束名为 FK_xh,使之与学生表建 立级联删除和级联更新操作 .ALTER TABLE 选课表ADD CONSTRAINT FK_xh FOREIGN KEY ( 学号 ) REFERENCES 学生表 ( 学号 )ON DELETE CASCADEON UPDATE CASCADEALTER TABLE 选课表DROP CONSTRAINT FK_xh

例： 1）建立 XS表，包括学号 char(2) 姓名 char(6)，同时以学 号建立唯一性约束，约束名为 UK_xh. 2）为 XS表以姓名建立唯一性约束，约束名为 UK_xm。 3）删除 XS表中的 UK_xh的唯一性约束。CREATE TABLE XS( 学号 char(2) CONSTRAINT UK_xh UNIQUE, 姓名 char(6))ALTER TABLE XS ADD CONSTRAINT UK_xm UNIQUE( 姓名 )ALTER TABLE XS DROP CONSTRAINT UK_xh

CREATE TABLE score( 学号 char(4), 课号 char(1), 分数 decimal(5,2) CONSTRAINT CK_cj CHECK (分数 >=0 and 分数 <=100))INSERT scoreVALUES('0001','1',120)ALTER TABLE scoreDROP CONSTRAINT CK_cjALTER TABLE scoreADD CONSTRAINT CK_fs CHECK (分数 >=0 and 分数 <=150)

例：建立 score表，包括学号 char(4) 课号 char(1) 分数 decimal(5,2)，为分数建立检查约束，约束名为 CK_cj，要求分数只能在 0~100间

8.4.5 默认约束例 员工表 employee的 sex 列添加默认约束，默认值是“男”。ALTER TABLE employee ADD CONSTRAINT sex_default DEFAULT ‘男’ FOR sex

更改表 employee为 hire_date 列定义默认约束。ALTER TABLE employee ADD CONSTRAINT hire_date_df DEFAULT getdate( ) FOR hire_date

DROP TABLE < 表名 >

[例 8] 删除 S 表。 DROP TABLE S

3 、删除基本表

建立索引是加快查询速度的有效手段。可在基本表上建立一个或多个索引，以提供多种存取路径，加快查询速度。 系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。 按照索引记录的存放位置不同，可分为聚集索引 (Clustered Ind

ex) 与非聚集索引 (Non-Clustered Index)两类。 聚集索引是指索引项的顺序与表中记录的物理顺序一致的索引组织； 非聚集索引按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。在检索记录时，聚集索引会比非聚集索引速度快，一个表中只能有一个聚集索引，而非聚集索引可以有多个。

3.2.2 设计、创建和维护索引1 、索引的概念

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED ] INDEX < 索引名 >

ON < 表名 > | < 视图名 >(< 列名 > [ ASC | DESC ] [ ,...n ] )

2 、创建索引

[例 9] 为学生 -课程数据库中的 S 、 C 、 SC 三个表建立索引。其中 S 表按学号升序建惟一索引，C 表按课程号降序建立聚簇索引，SC 表按学号升序和课程号降序建非聚簇索引。

CREATE UNIQUE INDEX S_SNO ON S(SNO)CREATE CLUSTERED INDEX C_CNO ON C(CNO DESC)CREATE NONCLUSTERED INDEX SC_SNO_CNO ON SC(SNO ASC,CNO DESC)

1、按employee表的 employee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name)

2 、在学生表上创建一个名为学号 _index 的唯一性聚簇索 引，索引关键字为学号，降序。CREATE UNIQUE CLUSTEREDINDEX 学号 _indexON 学生表 ( 学号 DESC )

3、在选课表上创建一个名为学号 _课号 _index的非聚簇复合索引，索引关键字为学号 ,课号，升序CREATE NONCLUSTERED INDEX 学号 _课号 _indexON 学生表 ( 学号 ASC , 课号 ASC)

删除索引的命令语法 :

DROP INDEX < 索引名 >

[ 例 10] 删除 S 表的 S_SNO 索引。DROP INDEX S_SNO

3 、删除索引

SELECT [ALL|DISTINCT] 目标列表达式 [, 目标列表达式 ]... [INTO 新表名 ]FROM 表名或视图名 [, 表名或视图名 ] ... [WHERE 条件表达式 ] [GROUP BY 列名 1 [HAVING 条件表达式 ]] [ORDER BY 列名 2 [ASC|DESC]] ...

3.3 SQL 数据查询3.3.1 SELECT 命令的格式及其含义

1 、查询指定列

[例 11] 查询全体学生的学号与姓名。 SELECT SNO,SN FROM S [例 12] 查询全体学生的姓名、学号、所在系。 SELECT SN,SNO,DEPT FROM S

3.3.2 单表查询

在 Select子句中给出包含所选字段的一个列表，各个字段之间用逗号分隔，字段的次序可以任意指定。

如在 From子句中指定了两个表，而这两个表中又有同名的字段，使用这些字段时就应在其字段名前冠以表名。USE JWGLSelect student.student_id, student_name,monitor From student,class Where class.class_id=student.class_id

[例 13] 查询全体学生的详细记录。 SELECT * FROM S

等价于： SELECT SNO,SN,SEX,AGE,DEPT FROM S

2 、查询全部列

SELECT 子句的 < 目标列表达式 > 不仅可以是表中的属性列，也可以是有关表达式。[例 14] 查全体学生的姓名及其出生年份。 SELECT SN, 2005-AGE FROM S

输出的结果为： SN -------- --------- 李涛 1986 王林 1987 陈高 1984 张杰 1988 吴小丽 1986 徐敏敏 1985

3 、查询经过计算的列

[例 15] 查全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。 SELECT SN, ‘ 出生年份 :’,2005-AGE,lower(DEPT) FROM S

输出的结果为： SN -------- ------------- --------- --------- 李涛 出生年份 : 1986 信息 王林 出生年份 : 1987 计算机 陈高 出生年份 : 1984 自动化 张杰 出生年份 : 1988 自动化 吴小丽 出生年份 : 1986 信息 徐敏敏 出生年份 : 1985 计算机

设置字段别名：显示选择查询的结果时，第一行（即表头）中显示的是各个输出字段的名称。为了便于阅读，也可指定更容易理解的字段名来取代原来的字段名。设置别名的方法：原字段名 字段别名原字段名 AS 字段别名 字段别名 = 原字段名

用户可以通过别名来改变查询结构的列标题，这对于含自述表达式、函数名的目标列表达式尤为重要。上例可以定义如下：SELECT SN, ‘ 出生年份 :’ BIRTH , BIRTHDAY =2005-AGE, lower(DEPT) AS DEPARTMENTFROM S

输出的结果为： SN BIRTH BIRTHDAY DEPARTMENT -------- ------------- --------------- ---------------------- 李涛 出生年份 : 1986 信息 王林 出生年份 : 1987 计算机 陈高 出生年份 : 1984 自动化 张杰 出生年份 : 1988 自动化 吴小丽 出生年份 : 1986 信息 徐敏敏 出生年份 : 1985 计算机

3.3.3 WHERE子句的基本使用 1. 消除取值重复的行 2. 指定WHERE查询条件

返回本节首页

消除取值重复的行 [例 16] 查所有选修过课的学生的学号。SELECT SNO FROM SC结果为： SNO ---- S1 S1 S2 S2 S2 S3 S3 S3 S3 S4 S4 S4 S5

该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定 DISTINCT 短语：SELECT DISTINCT SNOFROM SC执行结果为： SNO ---- S1 S2 S3 S4 S5

例 显示 employee表中全部员工的姓名和年龄， 去掉重名。 ( 注：表中只有出生日期字段birth_date)SELECT DISTINCT employee_name AS 　姓名，　　 YEAR(GETDATE())-YEAR(birth_date) AS 　年龄FROM employee

常用的查询条件 查询条件 谓词 比较运算符 =,>,<.>=,<=,!=,<>,!>,!<；

Not ( 上述比较运算符构成的比较关系表达式 ) 确定范围 BETWEEN AND ， NOT BETWEEN AND

确定集合 IN ， NOT IN 字符匹配 LIKE ， NOT LIKE 空值 IS NULL ， IS NOT NULL 多重条件 AND ， OR ， NOT

查询满足条件的元组

(1)比较运算符 [例 17] 查计算机系全体学生的名单。 SELECT SN FROM S WHERE DEPT = ' 计算机 '[例 18] 查所有年龄在 20 岁以下的学生姓名及其年龄。 SELECT SN, AGE FROM S WHERE AGE <20或 SELECT SN, AGE FROM S WHERE NOT AGE>= 20

例 对 employee表，求出男员工的平均工资。SELECT AVG(wages) AS 平均工资 FROM employee WHERE sex='男‘例、在 student中检索男生且年龄大于 21岁的学生记录。(只有 birth 字段 )

例、在“ student” 中检索姓张或姓李的学生。

Select * FROM student Where sex =‘ 男’ AND Datediff(yy,birth,Getdate())

Select * FROM student Where Substring(姓名 ,1,1)=‘张’ OR Substring(姓名 ,1,1)=‘李’

(2)确定范围 [例 20] 查询年龄在 20 至 23 岁之间的学生的姓名、系别和年龄。 SELECT SN, DEPT, AGE FROM S WHERE AGE BETWEEN 20 AND 23与“ BETWEEN…AND…” 相对的谓词是“ NOT BETWEEN…

AND…” 。[例 21] 查询年龄不在 20 至 23 岁之间的学生姓名、系别和年龄。 SELECT SN, DEPT, AGE FROM S WHERE AGE NOT BETWEEN 20 AND 23

返回本节首页

例 对 employee 表，列出月工资在 2000 到 3000 之间的员 工名单。 SELECT * FROM employee WHERE wages BETWEEN 2000 AND 3000 语句中的 WHERE 子句还有等价的形式： WHERE wages>=2000 AND wages<=3000

(3)确定集合 [例 22] 查询信息系、自动化系和计算机系的学生的姓名和性别。

SELECT SN, SEXFROM SWHERE DEPT IN (' 信息 ', ' 自动化 ', ' 计算机 ')与 IN相对的谓词是 NOT IN ，用于查找属性值不属于指定集合的元组。

[例 23] 查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECT SN, SEXFROM SWHERE DEPT NOT IN (' 信息 ', ' 自动化 ', ' 计算机 ') 返回本节首页

例：在“ student” 中检索姓李、姓张、姓林、姓许的学生记录。Select * FROM student Where Substring( 姓名 ,1,1) IN (‘ 李’ ,‘ 张’ ,‘ 王’ ,‘ 刘’ )

等价于：Select * FROM student Where Substring( 姓名 ,1,1) ＝ ‘李’ OR Substring( 姓名 ,1,1) ＝‘张’ OR Substring( 姓名 ,1,1) ＝‘王’ OR Substring( 姓名 ,1,1) ＝‘刘’

(4)字符匹配 通配符 描述

% （百分号） 代表零个或更多字符的任意字符串。_ （下划线） 代表任何单个字符（长度可以为 0 ）。

[ ] （中扩号） 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。

[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。

字符匹配示例 1

[例 24] 查所有姓刘的学生的姓名、学号和性别。 SELECT SN, SNO, SEX FROM S WHERE SN LIKE ' 刘 %‘

[例 25] 查姓“欧阳”且全名为三个汉字的学生的姓名。 SELECT SN FROM S WHERE SN LIKE ' 欧阳 _'

返回本节首页

字符匹配示例 2

[例 26] 查名字中第二字为“阳”字的学生的姓名和学号。 SELECT SN, SNO FROM S WHERE SN LIKE '_ 阳 %'[例 27] 查所有不姓刘的学生姓名。 SELECT SN, SNO, SEX FROM S WHERE SN NOT LIKE ' 刘 %' 返回本节首页

字符匹配示例 3

[例 28] 查 DB_Design 课程的课程号和学分。 SELECT CNO, CT FROM C WHERE CN LIKE 'DB\_Design' ESCAPE '\' ESCAPE '\' 短语表示 \ 为换码字符，这样匹配串中紧跟在 \后面的字符 '_' 不再具有通配符的含义，而是取其本身含义，被转义为普通的 '_' 字符。 返回本节首页

例 对 employee表，列出所有的姓“张”的员工名单。 SELECT * FROM employee WHERE employee_name LIKE ‘张%' 语句中的WHERE子句还有等价的形式： WHERE LEFT(employee_name,1)= ‘张’例、从“ STUDENT” 中查找姓王、姓李和姓林的学生。

Select * FROM STUDENT Where 姓名 LIKE ‘[王李林 ]%'例、从“ STUDENT” 中检索学号末位数字不在 2~6范围内的 学生。 Select * FROM STUDENT Where student_id LIKE '%[^2-6]'

(5)涉及空值的查询 [例 29] 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号。 SELECT SNO, CNO FROM SC WHERE SCORE IS NULL注意这里的 'IS' 不能用等号（ '=' ）代替 [例 30] 查所有有成绩的记录的学生学号和课程号。 SELECT SNO, CNO FROM SC WHERE SCORE IS NOT NULL

返回本节首页

例 对 employee表，列出所有工资为空值的员工编号和姓名。 SELECT employee_id,employee_name FROM employee WHERE wages IS NULL例：删除 STUDENT表中姓名值为空的记录。　DELETE student WHERE 姓名 IS NULL

(6) 多重条件查询 1

逻辑运算符 AND 、 OR 和 NOT 可用来联结多个查询条件。他们的优先级 NOT最高，接着是 AND ，OR优先级最低，但用户可以用括号改变运算的优先顺序。

[例 31] 查计算机系年龄在 20 岁以下的学生姓名。SELECT SNFROM SWHERE DEPT=' 计算机 ' AND AGE<20

返回本节首页

多重条件查询 2

[例 32] IN 谓词实际上是多个 OR运算符的缩写，因此“查询信息系、自动化系和计算机系的学生的姓名和性别”一题，也可以用 OR运算符写成如下等价形式： SELECT SN, SEX FROM S WHERE DEPT=' 计算机 ' OR DEPT=' 信息 ' OR DEPT=' 自动化 '或 SELECT SN, SEX FROM S WHERE NOT(DEPT<>' 计算机 ' AND DEPT<>' 信息 ' AND DEPT<>' 自动化 ') 返回本节首页

3.3.4 常用库函数及统计汇总查询COUNT 返回组中项目的数量。

SUM 返回表达式中所有值的和，或只返回 DISTINCT 值的和。AVG 返回组中值的平均值。 MAX 返回组中值的最大值。 MIN 返回组中值的最小值。

返回本节首页

[例 33] 查询学生总人数。 SELECT COUNT(*) FROM S

[例 34] 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT SNO) FROM SC学生每选修一门课，在 SC 中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在 COUNT函数中用 DISTINCT 短语。

[例 35] 计算 C1 课程的学生人数、最高成绩、最低成绩及平均成绩。 SELECT COUNT(*),MAX(SCORE),MIN(SCORE),AVG(SCORE) FROM SC WHERE CNO='C1'

返回本节首页

3.3.5 分组查询 GROUP BY 子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果为一组对应统计产生一个函数值。否则，集函数将作用于每一个组，即每一组分别统计，分别产生一个函数值。 返回本节首页

[例 36] 查询各个课程号与相应的选课人数。 SELECT CNO, COUNT(SNO) FROM SC GROUP BY CNO该 SELECT 语句对 SC 表按 CNO 的取值进行分组，所有具有相同 CNO 值的元组为一组，然后对每一组作用集函数 COUNT 以求得该组的学生人数，执行结果为： CNO ---- ----- C1 3 C2 4 C3 1 C4 1 C5 2 C6 1 C7 1 返回本节首页

例 对 employee表，分别统计男女员工人数。 SELECT sex,COUNT(sex) AS 人数 FROM employee GROUP BY sex例 对 employee表，分别统计各部门男女员工的人数。 SELECT department_id, sex,COUNT(*) AS 人数 FROM employee GROUP BY department_id,sex例 用 GROUP BY句汇总出 student_course 表中学生的学号及 总成绩。

USE jwglGOSELECT '学号‘ = student_id , '总成绩‘ = sum(grade) FROM student_course GROUP BY student_id

[例 37] 查询有 3人以上学生（包括 3人）选修的课程的课程号及选修人数。 SELECT CNO, COUNT(SNO) FROM SC GROUP BY CNO HAVING COUNT(*)>=3结果为： CNO ---- ------ C1 3 C2 4

返回本节首页

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件组的统计值，则可以使用 HAVING 短语指定筛选条件。

例 对 employee表，列出部门平均工资大于 2000的部门编号。 SELECT department_id,AVG(wages) AS 平均工资 FROM employee GROUP BY department_id HAVING AVG(wages)>=2000例 用 GROUP BY句汇总出 student_course 表中总分大于 450分 的学生的学号及总成绩。

USE jwglGOSELECT '学号‘ = student_id , '总成绩‘ = sum(grade) FROM student_course GROUP BY student_id HAVING sum(grade)>450

3.3.6 查询的排序 如果没有指定查询结果的显示顺序， DB

MS 将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用 ORDER BY 子句指定按照一个或多个属性列的升序（ ASC ）或降序（ DESC ）重新排列查询结果，其中升序 ASC 为缺省值。

返回本节首页

查询的排序示例 1

[例 38] 查询选修了 3 号课程的学生的学号及其成绩，查询结果按分数的降序排列。 SELECT SNO, SCORE FROM SC WHERE CNO='C3' ORDER BY SCORE DESC 前面已经提到，可能有些学生选修了 C3 号课程后没有参加考试，即成绩列为空值。用 ORDER BY子句对查询结果按成绩排序时，在 SQL SERVER 2

000 中空值（ NULL ）被认为是最小值。返回本节首页

查询的排序示例 2

[例 39] 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。SELECT * FROM S ORDER BY DEPT,AGE DESC

返回本节首页

3.3.7 连接查询1 、 等值与非等值连接查询 当连接运算符为 = 时，称为等值连接。使用其它运算符称为非等值连接。 2 、 自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。 3 、 外连接

返回本节首页

等值与非等值连接查询从概念上讲 DBMS 执行连接操作的过程是，首先在表 1 中找到第一个元组，然后从头开始顺序扫描或按索引扫描表 2 ，查找满足连接条件的元组，每找到一个元组 , 就将表 1 中的第一个元组与该元组拼接起来，形成结果表中一个元组。表 2 全部扫描完毕后，再到表 1 中找第二个元组，然后再从头开始顺序扫描或按索引扫描表 2 ，查找满足连接条件的元组，每找到一个元组 , 就将表 1 中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表 1 全部元组都处理完毕为止。

返回本节首页

等值与非等值连接查询示例 [例 40] 查询每个学生及其选修课程的情况。 学生情况存放在 S 表中，学生选课情况存放在 SC表中，所以本查询实际上同时涉及 S 与 SC两个表中的数据。这两个表之间的联系是通过两个表都具有的属性 SNO实现的。要查询学生及其选修课程的情况，就必须将这两个表中学号相同的元组连接起来。这是一个等值连接。完成本查询的 SQL 语句为：

SELECT * FROM S, SC WHERE S.SNO=SC.SNO 返回本节首页

两种连接运算 连接运算中有两种特殊情况，一种称为广义笛卡尔积连接，另一种称为自然连接。 广义笛卡尔积连接是不带连接谓词的连接。两个表的广义笛卡尔积连接即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼接，因此结果表往往很大。 如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。

返回本节首页

[例 41] 自然连接 S 和 SC 表。 SELECT S.SNO, SN, SEX, AGE, DEPT, CNO, SCORE FROM S, SC WHERE S.SNO=SC.SNO

在本查询中，由于 SN 、 SEX 、 AGE 、 DEPT 、 CNO 和 SCORE 属性列在 S 与 SC 表中是唯一的，因此引用时可以去掉表名前缀。而 SNO 在两个表都出现了，因此引用时必须加上表名前缀。该查询的执行结果不再出现 SC.SNO 列。

返回本节首页

【例】从 student 及 student_course两个表中检索学生的学号、姓名、学习课程号及课程成绩。USE jwglGOselect student.student_id , student.student_name ,

student_course.course_id , student_course.grade from student , student_courseWHERE student.student_id = student_course.student_id

【例】从 student 、 course 及 student_course 三个表中检索学生的学号、姓名、学习课程号、学习课程名及课程成绩。USE jwglGO

SELECT student.student_id , student.student_name , student_course.course_id ,

course.course_name , student_course.grade from student , student_course , courseWHERE student.student_id =student_course.student_id AND course.course_id = student_course.course_id

例 Sales 数据库中部门表 department 和员工表 employee 的自然连接。SELECT a.department_name, b.*FROM department a INNER JOIN employee b ON b.department_id = a.department_id

例 对 Sales 数据库，列出销售“ G00001”产品的员工中，销售数量大于编号为“ E001” 的员工销售该类产品销售数量的那些员工的编号和销售数量。SELECT a.employee_id, a.order_num FROM sell_order a INNER JOIN sell_order b ON a.order_num > b.order_num AND a.goods_id = b.goods_id WHERE b.goods_id = 'G00001' AND b.employee_id = 'E001'

自身连接示例[例 42] 查询比张梅工资高的教师的姓名、工资和张梅的工资。要查询的内容均在同一表 T(教师表 ) 中，可以为表 T 分别取两个别名，一个是 X ，一个是 Y 。将 X ， Y 中满足比张梅工资高的行连接起来。这实际上是同一表

T 的大于连接。完成该查询的 SQL 语句为： SELECT X.TN,X.SAL AS SAL_B,Y.SAL AS 张梅的工资 FROM T AS X,T AS Y WHERE X.SAL>Y.SAL AND Y.TN=' 张梅 '结果为： TN SAL_B 张梅的工资 ------- -------------- -------------- 刘义 1500.0000 1300.0000 李刚 1500.0000 1300.0000 返回本节首页

外连接 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例 40 和例 41 的结果表中没有关于学生 S6 的信息，原因在于她没有选课，在 SC 表中没有相应的元组。但是有时我们想以 S表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（ Outer Join ）。 这样，我们就可以如下改写例 41 了：

返回本节首页

SELECT S.SNO, SN, SEX, AGE, DEPT, CNO, SCORE FROM S LEFT JOIN SC ON S.SNO=SC.SNO 结果为： SNO SN SEX AGE DEPT CNO SCORE ---- -------- ---- ---- -------------------- ---- ----- S1 李涛 男 19 信息 C1 90 S1 李涛 男 19 信息 C2 85 ………………………………………………………… S5 吴小丽 女 19 信息 C2 89 S6 徐敏敏 女 20 计算机 NULL NULL 从查询结果可以看到，学号为 S6 的学生没选课，但 S6 的信息也出现在查询结果中，上例中外连接符 LEFT [OUTER]

JOIN称其为左外连接。相应地，如果 RIGHT [OUTER] JOIN则称为右外连接， FULL [OUTER] JOIN称为全外连接。返回本节首页

3.3.8 合并查询 合并查询结果就是使用 UNION 操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。 UNION 操作会自动将重复的数据行剔除。必须注意的是，参加合并查询结果的各子查询的使用的表结构应该相同，即各子查询的数据数目相同，对应的数据类型要相融。

返回本节首页

[例 43] 从 SC 数据表中查询出学号为“ S1” 的同学的学号和总分，再从 SC 数据表中查询出学号为“ S5” 的同学的学号和总分，然后将两个查询结果合并成一个结果集。SELECT SNO AS 学号， SUM(SCORE) AS 总分FROM SCWHERE (SNO='S1')GROUP BY SNOUNIONSELECT SNO AS 学号 ,SUM(SCORE) AS 总分FROM SCWHERE (SNO='S5')GROUP BY SNO 返回本节首页

3.3.9 嵌套查询 1 、 带有IN谓词的子查询2 、 带有比较运算符的子查询3 、 带有ANY或ALL谓词的子查询4 、 带有EXISTS谓词的子查询

返回本节首页

带有 IN谓词的子查询[例 44] 查询与“王林”在同一个系学习的学生的学号、姓名和所在系。查询与“王林”在同一个系学习的学生，可以首先确定“王林”所在系名，然后再查找所有在该系学习的学生。所以可以分步来完成此查询：①确定“刘晨”所在系名 SELECT DEPT FROM S WHERE SN='王林 '结果为： DEPT -------------------- 计算机②查找所有在计算机系学习的学生。 SELECT SNO, SN, DEPT FROM S WHERE DEPT='计算机 ' 返回本节首页

结果为： SNO SN DEPT ---- -------- ------- S2 王林 计算机 S6 徐敏敏 计算机分步写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。 SQL 语句如下：SELECT SNO, SN, DEPTFROM S WHERE DEPT IN ( SELECT DEPT FROM S WHERE SN=' 王林 ')返回本节首页

本例中的查询也可以用我们前面学过的表的自身连接查询来完成： SELECT S1.SNO, S1.SN, S1.DEPT FROM S S1, S S2WHERE S1.DEPT = S2.DEPT AND S2.SN='王林 '

返回本节首页

带有比较运算符的子查询示例 例如：在例 44 中，由于一个学生只可能在一个系学习，也就是说内查询王林所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其 SQL 语句如下：

SELECT SNO, SN, DEPTFROM SWHERE DEPT =( SELECT DEPT

FROM S WHERE SN=' 王林 ' )

例 列出选修汇编语言课的所有学生的学号 . SELECT 学号 FROM 选课表 WHERE 课号 = ( SELECT 课号 FROM 课程表 WHERE 课名 =‘汇编语言’ )

例 列出“张文青”同学所有课程的课号和成绩。 SELECT 课号 ,成绩 FROM 选课表 WHERE 学号=( SELECT 学号 FROM 学生 WHERE 姓名=‘张文青’ )例 求软件专业所有必修课的课程设置情况 .

SELECT * FROM 课程表 WHERE 课号 IN ( SELECT 课号 FROM 必修课表 WHERE 必修专业 =‘软件’ )

带有 ANY 或 ALL 谓词的子查询示例 1[例 46] 查询其他系中比信息系任一学生年龄小的学生名单。

SELECT SN, AGE FROM S WHERE AGE<ALL( SELECT AGE FROM S WHERE DEPT='信息 ') AND DEPT <> '信息 ' ORDER BY AGE DESC

带有 ANY 或 ALL 谓词的子查询示例 2 本查询实际上也可以用集函数（请参阅表 3.6 ）实现。

SELECT SN, AGE FROM S WHERE AGE< ( SELECT MIN(AGE) FROM S WHERE DEPT=' 信息 ') AND DEPT <> ' 信息 'ORDER BY AGE DESC

事实上，用集函数实现子查询通常比直接用 ANY 或 ALL 查询效率要高。

例 求选修 2号课的学生中成绩比选修 1号课的最 低成绩要高的学生的学号和成绩 . SELECT 学号 , 成绩 FROM 选课表 WHERE 课号 =‘2’ AND 成绩 > ANY ( SELECT 成绩 FROM 选课表 WHERE 课号 =‘1’) SELECT 学号 , 成绩 FROM 选课表 WHERE 课号 =‘2’ AND 成绩 > ( SELECT min( 成绩 ) FROM 选课表 WHERE 课号 =‘1’)

带有 EXISTS谓词的子查询示例1 [例 47] 查询所有选修了 C1 号课程的学生姓名。 查询所有选修了 C1 号课程的学生姓名涉及 S 关系和 SC 关系，我们可以在 S 关系中依次取每个元组的 SNO 值，用此 S.SNO值去检查 SC 关系，若 SC 中存在这样的元组，其 SC.SNO 值等于用来检查的 S.SNO 值，并且其 SC.CNO='C1' ，则取此 S.

SN送入结果关系。将此想法写成 SQL 语句就是：SELECT SNFROM S WHERE EXISTS (SELECT * FROM SC WHERE SNO=S.SNO AND CNO='C1' )

带有 EXISTS谓词的子查询示例 2 [例 49] 查询选修了全部课程的学生姓名。 由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学生姓名，没有一门课程是他不选的。该查询涉及三个关系，存放学生姓名的 S 表，存放所有课程信息的 C 表，存放学生选课信息的 SC 表。其 SQL 语句为： SELECT SN FROM S WHERE NOT EXISTS ( SELECT * FROM C WHERE NOT EXISTS ( SELECT * FROM SC WHERE SNO=S.SNO AND CNO=C.CNO))

返回本节首页

3.3.10 存储查询结果到表中 使用 SELECT…INTO 语句可以将查询到的结果存储到一个新建的数据库表或临时表中。 [例 50] 从 SC 数据表中查询出所有同学的学号和总 分，并将查询结果存放到一个新的数据表 Cal_T

able 中。SELECT SNO AS 学号 ,SUM(SCORE) AS 总分 INTO Cal_Table FROM SC GROUP BY SNO返回本节首页

练习：以下各题对 student 、 course 、 sc 表操作。1 、删除 student 表的 stusno 索引 。

DROP INDEX stusno2 、查询以“ MIS_”开头，且倒数第二个汉字为“系”字的课程的详细情况。 Select * from course where cname LIKE ‘MIS\_% 系 _’ escape ‘\’

4 、查询性别为男、课程成绩及格的学生的信息及课程号、成绩

3 、查询选修了课程的学生人数。Select count(distinct sno) from sc

Select student.*,cno,grade from student,sc where student.sno=sc.sno and ssex=‘男’ and grade>=60

5 、查询与“钱恒”在同一个系学习的学生的学号、姓名及系的信息。Select sno,sname,sdept from student where sdept=(select sdept from student where sname=‘钱恒’ )此题也可用连接查询实现。

6 、查询选修了课程名为“信息系统”的学生学号、姓名和所在系。方法一：select sno,sname,sdept from student where sno in(select sno from sc where cno= (select con from course where cname=‘信息系统’ )方法二：select student.sno,sname,sdept from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname=‘信息系统’

7 、查询其他系中比 CS 系所有学生年龄均大的学生名单，并排序输出。Select sname from student where sage>ALL( select sage from student where sdept=‘CS’) and sdept<>’CS’或select sname from student where sage>(select max(sage) from student where sdept=‘CS’) and sdept<>’CS’

8 、查询所有未修 1 号课程的学生姓名。Select sname from student where not exists (select * from sc where sno=student.sno and cno=‘1’)或select sname from student where sno not in(select sno from sc where cno=‘1’)

9 、查询平均成绩大于 85 分的学号、姓名、平均成绩。Select sno,sname,avg(grade) from student,sc where student.sno=sc.sno group by student.sno,sname having avg(grade)>85

10 、查询各不同平均成绩所对应的学生人数 (给出平均成绩与其对应的人数 )

Select avgr,count(*) from (select sno,avg(grade) from sc group by sno) AS sg(sno,avgr) group by avgr

3.4 SQL 数据更新3.4.1 插入数据 3.4.2 修改数据 3.4.3 删除数据

返回本章首页

3.4.1 插入数据1 、 插入单个元组 插入单个元组的 INSERT 语句的格式为： INSERT [INTO] < 表名 >[(< 属性列 1>[,< 属性列 2>]...)] VALUES(<常量 1> [,<常量 2>]...)

2 、 插入子查询结果 插入子查询结果的 INSERT 语句的格式为： INSERT INTO < 表名 > [(< 属性列 1> [,< 属性列 2>]...)] 子查询

插入单个元组 [例 51] 将一个新学生记录（学号： S7；姓名：陈冬；性别：男；年龄： 18岁；所在系：信息）插入 S 表中。 INSERT INTO S VALUES('S7', ' 陈冬 ', ' 男 ','18',' 信息 ')[例 52] 插入一条选课记录（ 'S7' ， 'C1' ）。 INSERT INTO SC(SNO, CNO) VALUES('S7', 'C1') 新插入的记录在 Grade列上取空值。

插入子查询结果 [ 例 53] 对每一个系，求学生的平均年龄，并把结果存入 数据库。 对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。 CREATE TABLE DEPTAGE( DEPT CHAR(15), AVGAGE TINYINT) 然后对数据库的 S 表按系分组求平均年龄，再把系名和平均年龄存入新表中。 INSERT INTO DEPTAGE(DEPT, AVGAGE) SELECT DEPT, AVG(AGE) FROM S GROUP BY DEPT

3.4.2 修改数据 UPDATE < 表名 > SET <列名 >=< 表达式 >[,<列名 >=< 表达式 >]... [WHERE < 条件 >] 其功能是修改指定表中满足WHERE子句条件的元组。其中

SET子句用于指定修改方法，即用 < 表达式 > 的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

修改某一个元组的值

[例 54] 将学生 S3( 为学号 ) 的年龄改为 20 岁。UPDATE SSET AGE=20WHERE SNO='S3'

返回本节首页

修改多个元组的值[例 55] 将所有学生的年龄增加 1 岁。

UPDATE S SET AGE=AGE+1

返回本节首页

带子查询的修改语句 [例 56] 将计算机科学系全体学生的成绩置零。 UPDATE SC SET SCORE=0 WHERE ' 计算机 '=( SELECT DEPT FROM S WHERE SC.SNO=S.SNO)或 UPDATE SC SET SCORE=0 WHERE SNO IN ( SELECT SNO FROM S WHERE DEPT=' 计算机 ')

返回本节首页

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

DELETE 语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE 子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说， DELETE 语句删除的只是表中的数据，而不包括表的结构定义。

1 、 删除某一个元组的值 [例 57] 删除学号为 S7 的学生记录。 DELETE FROM S WHERE SNO='S7' 2 、 删除多个元组的值 [例 58] 删除所有的学生选课记录。 DELETE FROM SC 3 、 带子查询的删除语句 子查询同样也可以嵌套在 DELETE 语句中 [例 59] 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE ' 计算机 '= ( SELECT DEPT FROM S WHERE S.SNO=SC.SNO)

3.5 视图3.5.1 定义和删除视图3.5.2 查询视图3.5.3 更 新 视 图

返回本章首页

视图的相关概念关于视图 在关系数据库系统中，视图为用户提供了多种看待数据库数据的方法与途径，是关系数据库系统中的一种重要对象。 视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。通过视图能操作数据，基本表数据的变化也能在刷新的视图中反映出来。 视图在概念上与基本表等同，一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图上再定义新的视图，但对视图的更新（插入、删除、修改）操作则有一定的限制。

3.5.1 创建和删除视图1 、 创建视图

CREATE VIEW < 视图名 >[(< 列名 >[,< 列名>]...)]

AS < 子查询 > 2 、 删除视图

DROP VIEW < 视图名 > [例 62] 删除视图 IS_S1 。 DROP VIEW IS_S1

返回本节首页

创建视图示例[例 61] 建立信息系选修了 C1 号课程的学生的视图。

CREATE VIEW IS_S1(SNO, SN, SCORE) AS SELECT S.SNO, SN, SCOREFROM S,SC WHERE DEPT=' 信息 ' AND S.SNO=SC.SNO AND SC.CNO='C1'

返回本节首页

查询视图示例 [例 63] 在信息系学生的视图中找出年龄小于 20 岁的学生。

SELECT SNO, AGE FROM IS_S WHERE Sage<20视图是定义在基本表上的虚表，它可以和其他基本表一起使用，实现连接查询或嵌套查询。这也就是说，在关系数据库的三级模式结构中，外模式不仅包括视图，而且还可以包括一些基本表。返回本节首页

更新视图示例[例 64] 将信息系学生视图 IS_S 中学号为 S3的学生姓名改为“刘辰”。 UPDATE IS_S SET SN=' 刘辰 ' WHERE SNO='S3'

返回本节首页

不同的数据库对视图的更新有不同的规定，如下是 IBM的DB2 数据库中视图不允许更新的规定：1. 若视图是由两个以上基本表导出的，则此视图不允许更新。 2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行 INSERT和 UPDATE 操作，但允许执行 DELETE操作。 3. 若视图的字段来自集函数，则此视图不允许更新。4. 若视图定义中含有 GROUP BY子句，则此视图不允许更新。 5. 若视图定义中含有 DISTINCT短语，则此视图不允许更新。 6. 若视图定义中有嵌套查询，并且内层查询的 FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。7. 一个不允许更新的视图上定义的视图也不允许更新。 返回本节首页

在 SQL 系统中，有两个安全机制 : 一种是视图机制，当用户通过视图访问数据库时，他不能访问此视图外的数据，提供了一定的安全性。 主要的安全机制是权限机制。权限机制的基本思想是给用户授予不同类型的权限，在必要时，可以收回授权，使用户能够进行的数据库操作以及所操作的数据限定在指定范围内，禁止用户超越权限对数据库进行非法的操作，从而保证数据库的安全性。

3.6 SQL 数据控制

3.6.1 权限与角色1 、权限 数据库中权限：系统权限、对象权限 系统权限系统权限是指数据库用户能够对数据库系统进行某种特定的操作的权力。它由数据库管理员授予其他用户。如创建一个基本表（ CREATE TABLE ）的权力。 对象权限对象权限是指数据库用户在指定的数据库对象上进行某种特定的操作的权力。如 SELECT 、 INSERT 、 UPDATE 、 DELETE 、 ALL PRIVILEGES( 全部对象权限 ) 的权力。

2 、角色角色是多种权限的集合，可以把角色授予用户或角色。当要为某一用户同时授予或收回多项权限时，则可以把这些权限定义为一个角色，对此角色进行操作。

这样就避免了许多重复性的工作，简化了管理数据库用户权限的工作。

3.6.2 系统权限与角色的授予与收回 1 、系统权限与角色的授予

GRANT < 系统权限 >[,< 系统权限 >]... TO < 用户 >|角色 ...

2 、系统权限与角色的收回 REVOKE < 系统权限 >[,< 系统权限 >]... FROM < 用户 >|角色 ...

[ 例 65] 把创建表的权限授给用户U1。 GRANT CREATE TABLE TO U1 [ 例 66] 把U1所拥有的创建表权限收回。 REVOKE CREATE TABLE FROM U1

3.6.3 对象权限与角色的授予与收回 1 、对象权限与角色的授予 GRANT ALL|< 对象权限 >[(列名 [,列名 ]…)][,< 对象权限 >]... ON < 对象名 > TO < 用户 >|<角色 >| PUBLIC[,< 用户 >|<角色 >]... [WITH GRANT OPTION] 指定 WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户 ; 没有指定 WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限。2 、对象权限与角色的收回 REVOKE < 对象权限 >|<角色 >[,< 对象权限 >|<角色 >]… FROM < 用户名 >|<角色 >| PUBLIC[,< 用户 >|<角色 >]…

[ 例 67] 把查询 S表权限授给用户 U1。 GRANT SELECT ON S TO U1[ 例 68] 收回用户 U1对 S表的查询权限。 REVOKE SELECT ON S FROM U1

[ 例 ] 把对 Student表和 Course表的全部操作权限予用户 U2和 U3。 GRANT ALL PRIVILEGES ON Student,Course TO U2,U3[ 例 ] 把对表 SC的权限受予所有用户 。 GRANT SELECT ON SC TO PUBLIC [ 例 ] 把查询 Student表和修改学生学号的权限授给用户 U4. GRANT SELECT,UPDATE(Sno) ON Student TO U4

[ 例 ] 把对表 SC的 INSERT权限授予 U5用户，并允许将此权限再授予其他用户。 GRANT INSERT ON SC TO U5 WITH GRANT OPTION [ 例 ] 收回用户 U4对 SC表修改学生学号的权限。 REVOKE UPDATE(Sno) ON SC FROM U4

通过角色来实现将一组权限授予一个用户。步骤如下：1.创建一个角色 R1 。 sp_addrole ‘R1’

2. 使用 GRANT 语句，使角色 R1拥有 Student 表的 SELECT 、 UPDATE 、 INSERT权限。

GRANT SELECT,UPDATE,INSERT

ON Student TO R1

3. 将这个角色授予用户王平。 sp_addrolemember ‘r1’,’王平’4. 通过 R1角色收回王平的这 3 个权限 ,再删除 R1角色。 sp_droprolemember ‘r1’,‘王平’

sp_droprole 'r1'

1. 在 studentsdb 数据库的 student_info 和 grade 表中 , 使用 T-SQL 语句 GRANT, 完成如下授权定义或存取控制功能 .

(1)数据库用户”李卫平 db” 对 student_info 和 grade 表有 SELECT权力 .

(2) 将李勇设置为 SQL Sever登录账户 ,并添加为 studentsdb 数据库用户 , 使李勇对 student_info 和 grade 表有 INSERT 和 DELETE权力 .

(3) 创建数据库用户张星星 , 使他对 student_info 表有 SELECT权力 , 对 student_info 表的姓名列具有 UPDATE权力 .

(4) 用户李勇具有创建表的权力 .

(5) 数据库用户张扬具有查看各科成绩的最高分、最低分和平均分的权限，但不能查看每个人的成绩。

（ 1 ） GRANT SELECT ON student_info TO 李卫平 db GRANT SELECT ON grade TO 李卫平 db

(2) sp_addlogin ‘ 李勇’ EXEC sp_grantdbaccess ‘李勇‘ GRANT INSERT,DELETE ON student_info TO 李勇 GRANT INSERT,DELETE ON grade TO 李勇 (3) sp_addlogin ‘张星星’ EXEC sp_grantdbaccess ‘张星星’ GRANT SELECT ON student_info TO 张星星 GRANT UPDATE(姓名 ) ON student_info TO 张星星 (4) GRANT CREATE TABLE TO 李勇 (5) CREATE VIEW v_cj AS SELECT 课程编号 ,MAX( 分数 ),MIN( 分数 ),AVG( 分数 ) FROM grade GROUP BY 课程编号 sp_addlogin ‘张扬’ EXEC sp_grantdbaccess ‘张扬’ GRANT SELECT ON v_cj TO 张扬

2 。使用 T-SQL 语句 REVOKE撤销其所授予的权限。 （ 1 ）删除数据库用户李卫平 db 对 student_info 和 grade 表的 SELECT权限。 （ 2 ）删除数据库用户李勇对 student_info 和 grade 表的 INSERT 和 DELETE权限。 （ 3 ）删除数据库用户李勇创建表的权限。 （ 4 ）删除数据库用户张扬查看各科成绩的最高分、最低分和平 均分的权限。

(1)REVOKE SELECT ON student_info FROM 李卫平REVOKE SELECT ON grade FROM 李卫平

(2) REVOKE INSERT,DELETE ON student_info FROM 李勇 REVOKE INSERT,DELETE ON grade FROM 李勇

(3)REVOKE CREATE TABLE FROM 李勇(4) REVOKE SELECT ON v_cj from 张扬 DROP VIEW v_cj

3.7 嵌入式 SQL 语言 *

3.7.1 嵌入式 SQL简介 SQL 提供了 2 种使用方式： 一种是终端交互方式 ;

另一种是将 SQL 嵌入到某种高级语言如 C 语言中使用，嵌入 SQL 的高级语言称为主语言或宿主语言。

各类第 4 代开发工具或开发语言，如 VB 、 PB 、 VC 、 C＃、DELPHI 等，其通过 SQL来实现数据库操作均为嵌入式 SQL 应用。我们主要介绍 VB 中 SQL 的使用。

3.7.2 嵌入式 SQL 要解决的 3 个问题

一个 SQL 语句一般能处理一组记录，而主语言一次只能处理一个记录，为此必须协调两种处理的方式，使他们相互协调地处理。嵌入式 SQL 中引入游标机制来解决这个问题。游标是系统为用户开设的一个数据缓冲区，用来存放 SQL 语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一的获取记录，并赋给主变量，再由主语言程序作进一步处理。下面我们介绍如何使用游标。

3 、协调 SQL 集合式操作与高级语言记录式处理这间的关系

使用游标的步骤 使用游标有如下几个步骤：① 创建游标 DECLARE。② 打开游标 OPEN。③ 从游标的结果集中读取数据 FETCH。④ 对游标中的数据逐行操作。⑤ 关闭和释放游标 CLOSE。

1. 声明游标DECLARE 游标名 [SCROLL] CURSORFOR select语句[FOR READ ONLY|UPDATE [OF column_name_list]] 其中：SCROLL：表示取游标时可以使用关键字NEXT、 PRIOR、 FIRST、 LAST、 ABSOLUTE、 RELATIVE。每个关键字的含义将在介绍 FETCH子句时讲解。FOR READ ONLY：指出该游标结果集只能读不能修改。FOR UPDATE：指出该游标结果集可以被修改。OF column_name_list：列出可以被修改的列的名单。

2. 打开游标 OPEN 游标名

注意：① 当游标打开成功时，游标位置指向结果集的第一行 之前。② 只能打开已经声明但尚未打开的游标。

3. 从打开的游标中提取行FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE] FROM 游标名 [INTO 变量 ]其中：NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是 NEXT，即向下移动。 NEXT：取下一行数据。 PRIOR：取前一行数据。 FIRST：取第一行数据。 LAST：取最后一行数据。 ABSOLUTE：按绝对位置取数据。 RELATIVE：按相对位置取数据。

通过全局变量@@FETCH_STATUS提供关于游标活动的信息：@@FETCH_STATUS 保存着最后 FETCH语句执行后的状态信息，其值和含义如下：0 ：表示成功完成 FETCH 语句。-1：表示FETCH语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中不再有数据。-2：提取的行不存在。

4. 关闭游标CLOSE 游标名5. 释放游标释放 ( Deallocate)游标是指释放所有分配给此游标的资源，包括该游标的名字。 DEALLOCATE CURSOR 游标名注意一下关闭游标与释放游标的区别：关闭游标并不改变游标的定义，一个游标关闭后，不需要再次声明，就可以重新打开并使用它。但一个游标释放后，就释放了与该游标有关的一切资源，也包括游标的声明，游标释放后就不能再使用该游标了，如需再次使用游标，就必须重新定义。

【例】定义一个游标，将教师表 teacher中所有教师的 教师姓名、教师职称显示出来。USE jwglGODECLARE @teach_name VARCHAR(8),@teach_profession VARCHAR(16)DECLARE teacher_coursor SCROLL CURSOR FOR SELECT teacher_name,profession FROM teacher FOR READ ONLYOPEN teacher_coursorFETCH FROM teacher_coursor INTO @teach_name,@teach_professionWHILE @@FETCH_STATUS=0 BEGIN PRINT ‘教师姓名 :’+@teach_name+’职称 :’+ @teach_profession FETCH FROM teacher_coursor INTO teach_name ,@teach_profession ENDCLOSE teacher_coursorDEALLOCATE teacher_coursor

declare @name char(8),@xb char(2)declare xs cursor for select 姓名 , 性别 from 学生表 for read onlyopen xsfetch from xs into @name,@xbwhile @@fetch_status=0 begin print ' 姓名 '+@name+' 性别 '+@xb fetch from xs into @name,@xb endclose xsdeallocate xs

定义游标 xs,显示学生表中的所有同学的姓名和性别信息 .

使用游标修改数据 用户可以在 UPDATE 或 DELETE 语句中使用游标来更新、删除表或视图中的行，但不能用来插入新行。更新数据 UPDATE table_name|view_name

SET column_name = new_value[ ， ...n] WHERE CURRENT OF 游标名

USE jwglGODECLARE teacher_coursor CURSOR FOR SELECT teacher_name,profession FROM teacher FOR UPDATE OF professionOPEN teacher_coursorFETCH ABSOLUTE 3 FROM teacher_coursorUPDATE teacher SET profession = '教授 ' WHERE CURRENT OF teacher_coursorCLOSE teacher_coursorDEALLOCATE teacher_coursor

【例】通过游标将教师表 teacher 中记录号为 3 的章红老师的职称由“讲师”改为“教授”。

declare cj cursor for select * from 选课表 for update of 成绩open cjfetch next from cj while @@fetch_status=0 begin update 选课表 set 成绩 = case when 成绩 <60 then 成绩 +10 when 成绩 >=60 and 成绩 <70 then 成绩 +5 when 成绩 >=70 and 成绩 <80 then 成绩 +3 when 成绩 >=80 and 成绩 <90 then 成绩 +2 else 成绩 end where current of cj fetch next from cj endclose cjdeallocate cj

建立游标 cj,给选课表中的成绩加分 .成绩 <60 加 10 分 ;60~70 加5 分 ;70~80 加 3 分 ;80~90 加 2 分 ;90~100 不加分 .

删除数据DELETE [FROM] table_name|view_name

WHERE CURRENT OF 游标名 注意：

•使用游标的 DELETE 语句，删除一行后将游标位置向前移动一行。

declare @name char(8)declare xm cursor for select 姓名 from 学生表 for updateopen xmfetch next from xm into @namewhile @@fetch_status=0 begin if @name is null delete 学生表 where current of xm fetch next from xm into @name endclose xmdeallocate xm

建立游标 xm, 删除学生表中姓名值为 null 的记录 .

Microsoft SQL Server 2000提供了多种 API ，使得其它应用程序能够访问数据库资源，这些 API被称为数据库访问 API 。这些 API 有： ADO 、 ODBC 等。

ADO 数据访问对象是一组自动化对象。 Visual Basic 、 Visual C++ 、和 Visual FoxPro都可以使用

ActiveX 数据对象 (ADO) API来编写数据库应用程序。 ADO 数据访问对象的主要优点是易于使用、高速度、低内存支出和占用磁盘空间较少等功能。

1. SQL Server 数据库访问应用程序接口（ API ）3.7.3 第 4 代数据库应用开发工具或高级语言中 SQL 的使用

ODBC 是一个标准的访问数据库的编程接口。通过它应用程序可以与 SQL Server进行通信。在安装 SQL Server客户端的同时，安装程序将安装一个 SQL Server 的 ODBC驱动程序在系统中，以提供给其它应用程序或开发环境使用。

在使用 ADO 控件前，必须在 VB 中先通过“工程 / 部件”菜单命令选择“ Microsoft ADO Data Control 6.0(OLE DB)”选项，将 ADO 数据控件（ Adodc ）添加到工具箱。 Adodc两个重要的属性如下：

① ConnectionString属性 ConnectionString属性包含了用于与数据源连接的相关信息。

② RecordSource属性 RecordSource确定具体可访问的数据，这些数据构成记录集对象 Recordset 。该属性值可以是数据库中的单个表名、视图名、一个存储过程等，也可以是使用 SQL 查询语句的一个查询字符串。

2. VB 中 ADO 数据控件的使用

例：使用 ADO 数据控件来连接学生数据库的学生表、显示学生 表中的学生记录。① 在窗体上放置 ADO 数据控件 Adodc ，控件名采用默认名“ Adodc1” 。② 单击属性窗口中的 ConnectionString属性右边的“ ”按钮，弹出属性页对话框。

③ 采用“使用连接字符串”方式连接数据源。单击“生成”按钮，打开数据链接属性窗。在属性窗的“提供程序”选项卡内选择一个适合的 OLE DB 数据源，选择“ Microsoft OLE DB Provider for SQL Server” 。

④ 单击“下一步”或选择“连接”选项卡，进行数据源连接属性的设置，在“选择或输入服务器名称”中输入服务器名称。选择适合 SQL Server服务器的登录身份验证方式，然后选择“在服务器上选择数据库”并在对应的下拉框中选择数据库“学生”，如图。单击右下方的“测试连接”按钮可以测试连接是否有效。如果测试成功，点击“确定”，关闭“数据链接属性窗”。完成操作后， ConnectionString属性中自动产生了连接字符串“ Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=学生 ;Data Source=B19FCAACCBD44A2” 。

⑤　单击 Adodc1属性窗中的 RecordSource属性右边的“…”按钮，弹出记录源属性页对话框，如图。“命令类型”下拉框中选择记录源的命令类型，命令类型可以是： adCmdText 、 adCmdTable 、 adCmdStoredProc 和 adCmdUnknown ，它们分别是指记录来自于 SQL 查询语句、表、存储过程和其它 SQL 语句。当选择 adCmdText 和 adCmdUnknown时，可以在“命令文本 (SQL)”编辑框中输入 SQL 语句；选择 adCmdTable 和 adCmdStoreProc时可以在“ Table or Stored Procedure Name”下拉框中选择数据库中的表和存储过程。我们选择 adCmdTable 和 student 表。关闭记录源属性页，完成 ADO 数据控件的连接操作。

⑥ 在窗体中放置 DataGrid 控件，设置其 DataSource属性为“ Adodc1”(注 : 在”工程 / 部件”中选择” Microsoft DataGrid Control 6.0(OLEDB)) ，这样一个简单的应用就完成了。编译、执行这个工程，可以看到如图 14-13所示运行结果。

本章首页本章首页

练习：1 、在 VB 中作如下的窗口。

1.启动 VB ，在“新建工程”对话框中，选择”标准 EXE“ 图标来创建工程 1 。2, 在属性窗口中，修改 Caption属性为“课程录入”。3. 在窗体 form1 中添加标签和文本框。4. 在窗体 form1添加命令按钮控件。双击各按钮。输入代码

Private Sub Command1_Click() MsgBox " 已将数据存入数据库 "End Sub

Private Sub Command2_Click()MsgBox "取消刚才的操作 "End Sub

Private Sub Command3_Click()MsgBox "退出窗口 "Unload Form1End Sub

2. 设计窗体，通过输入学生学号分别从学生表、选课表和课程表查询指定学号的所选课程名称与各课程的成绩信息，其中学号、姓名、性别用文本框，学习成绩用数据网格控件。

提示：使用数据控件 ADODC1绑定数据网格控件，用 ADODC2绑定二个文本框。根据输入的学号使用 T-SQL 语句指定 ADODC1 控件的 ConnectionStringt 和 RecordSource属性。

ADODC2 控件的属性设置为： Adodc2.CommandType=adCmdText Adodc2.RecordSource=“select 姓名 , 性别 from 学生表” Adodc2.Visible=False

ADODC1 控件的属性设置为： Adodc1.CommandType=adCmdText Adodc1.RecordSource=“select 学号 ,课名 , 成绩 from 选课表 xk inner join 课程表 kc on xk.课号 =kc.课号”TEXT2 控件的属性设置为： Text2.DataSource=ADODC2 Text2.DataField=姓名Text3 控件的属性设置为： Text3.DataSource=ADODC2 Text3.DataField= 性别

Private Sub Command1_Click()Dim str1 As String, str2 As StringDim xh As Stringxh = Text1.Textstr1 = “select 姓名 ,性别 from 学生表 where 学号 ='" + xh + "'"str2 = “select 学号 ,课名 ,成绩 from 选课表 xk inner join 课程表 kc on xk. 课号 =kc. 课号 where 学号 ='" + xh + "'"Adodc2.CommandType = adCmdTextAdodc2.RecordSource = str1Adodc2.RefreshAdodc1.CommandType = adCmdTextAdodc1.RecordSource = str2Adodc1.RefreshEnd Sub

“ 查询”按钮的 Click事件代码为：

在程序中使用 ADO 数据对象，则必须在 VB 中先通过“工程 / 引用”菜单命令选择“ Microsoft ActiveX Data Objects 2.5 Library)” 选项，添加对 ADO 数据对象的引用， ADO 的对象名为 ADODB 。 ①数据连接对象 Connection 包括的属性有 ConnectionString 用来指定用于设置连接到数据源的信息，它 与 ADO Data 控件的 ConnectionString属性是一样的。 注：连接字符串的生成可使用 ADO 控件与 SQL Server 建立连接 产生的字符串，将其复制到代码中。 主要方法有： Close 可以关闭已经打开的数据库。 Execute 用于指定执行的 SQL 语句。 Open 可创建与数据库的连接。

3 、使用 ADO 数据对象开发应用程序

②数据集对象 Recordset

包括的常用属性有：BOF 为 True时，记录指针在记录表第一条记录前。EOF 为 True时，记录指针在最后一条记录后。

例：利用 ADO 数据对象访问学生库的的课程表，删除指定的课程编号的记录。

Private Sub Command1_Click()Dim cn As New ADODB.Connectioncn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=学生 ;Data Source=B19FCAACCBD44A2"cn.OpenDim rs As New ADODB.RecordsetSet rs = cn.Execute("select * from 课程表 where 课号 ='" + Text1.Text + "'")If rs.BOF Then MsgBox " 此课程没有，不能删除 "Else Set rs = cn.Execute("delete from 课程表 where 课号 ='" + Text1.Text + "'") MsgBox " 删除成功 !!"End Ifcn.CloseEnd Sub

Private Sub Command2_Click()Unload MeEnd Sub

1. ODBC简介开放式数据库连接（ Open Database Connectivity, ODBC ）是一种标准的数据访问途径，包含在所有 Microsoft Windows 操作系统中。对于应用程序开发人员而言， ODBC 是一个过程化的数据访问接口。 ODBC 的先进之处在于它能通过相同的 API 和 SQL语法访问不同厂商的数据库。

3 、使用 ODBC连接 SQL Server 数据库

2. 配置 ODBC 数据源为了连接到 SQL Server 数据库服务器，必须在 ODBC 中配置 SQL Server 数据源。数据源存储了如何与指定数据库提供者（如 SQL Server ）连接的信息。数据源名（ DSN ）是用文本关键字标识应用程序将要使用的连接记录，它有三种类型：基于文件的、系统的和用户的。

在 “控制面板 / 管理工具”中启动“ ODBC 数据源管理器”管理程序。其运行界面如下所示。

点击“用户 DSN” ，再点击“添加”按钮，开始添加一个新的数据源，这时，系统弹出 “创建新数据源”的对话窗口。

在驱动程序列表中选择“ SQL Server”驱动程序，表明将建立一个访问 SQL Server 数据库服务器的连接。点击“完成”后系统显示“建立新的数据源到 SQL Server”向导对话框，如图。

在“名称”输入框中输入新数据源的名称，这个名称将被要访问数据库的应用程序使用。在“说明”中可以输入对该数据源的说明文字。在“服务器”下拉框中选择需要连接的 SQL Server 数据库服务器名称，如果没有显示服务器名称，可以手工输入。点击“下一步”，系统显示“建立新的数据源到 SQL Server” 的窗口。

可以根据需要选择使用 Windows NT验证还是使用 SQL Server验证。

选择“更改默认的数据库为”复选框，在下方的下拉框中选择当前连接的 SQL Server 数据库服务器中的一个数据库作为默认数据库，这样，连接数据库的客户端应用程序就将选中的这个数据库作为默认的使用数据库。点击“下一步”，再点击“完成”，此时系统将显示 “ ODBC Microsoft SQL Server 安装”的对话框窗口，该窗口显示了对这个新数据源主要的连接描述，点击“测试数据源”，可以测试新数据源是否正确地连接到 SQL Server 数据库。

例 : 使用 ODBC连接学生数据库 , 浏览学生表中的信息 .

1. 打开 Windows 面板 ,双击控制面板中”管理” -”ODBC 数据源”图标 ,打开” ODBC 数据源管理器”对话框 , 选择”文件用户 DSN” 项 .

2. 单击”添加”按钮 , 在”创建数据源”对话框中选择服务器驱动程序为” SQL Server”,单击”完成”按钮 .

3. 在”创建到 SQL Server 的新数据源”对话框中输入数据源的名称、描述和正处 于连接的服务器，单击“下一步”按钮。4. 设置以 Windows登录还是以 SQL Server 身份登录 (登录名为 sa ，密码为空 ) ， 单击“下一步”按钮。5. 设置默认数据库为学生库。“下一步” ->” 完成” - >”测试连接” ->“确定”。6 。在 VB窗体中添加 DataGrid 表格控件和 Adodc 数据控件。 设置 Adodc1 控件的属性： 在 ConnectionString属性旁单击“…”按钮，在打开的“属性页”对话框中选 择“使用连接字符串”，单击“生成”按钮；选择使用的数据源名称、输入用户 名为 sa ，密码为空、输入要使用的初始目录 (设置为要访问的学生库 ) ，单击 “测试连接”按钮。“确定”。 在 RecordSource属性中选择学生表。 设置 DataGrid1 的 DataSource属性值为 Adodc1 。

习 题一、选择题1 、在 SQL 语言中授权的操作是通过 ________ 语句实现的。 A 、 CREATE B 、 REVOKE C 、 GRANT D 、 INSER

T2 、 SQL 语言的一体化特点是主要同 ( ) 相比较而言的。 A. 操作系统命令 B.非关系模型的数据语言 C. 高级语言 D. 关系模型语言3 、在嵌入式 SQL 语言中使用游标的目的在于 ________ 。 A 、区分 SQL 与宿主语言 B 、与数据库通信 C 、处理错误信息 D 、处理多行记录4 、设有关系 R= （ A ， B ， C ）。与 SQL 语句 SELECT DI

STINCT A FROM R WHERE B=17 等价的关系代数表达式是 ________ 。 A. ∏A(R) B. σB=17(R) C. ∏A(σB=17(R)) D. σB=17(∏A(R))

5、两个子查询的结果 ( )时，可以执行并、交、差操作。 A. 结构完全一致 B.结构完全不一致 C. 结构部分一致 C. 主键一致6、在 SQL 查询语句中，用于测试子查询是否为空的谓词是 ( )。 A.Exists B.Unique C.Some D.All7、使用 SQL 语句进行查询操作时，若希望查询结果中不出现重复元组，应在 Select子句中使用 ()保留字。 A.Unique B.All C.Except D.Distinct8、在视图上不可能完成的操作是 ( ) A.更新视图 B.查询 C.在视图上定义新的基本表 D.在视图上定义新视图9、 SQL 中涉及属性 Age 是否是空值的比较操作，写法 ( )是错误的。 A. Age Is Null B. Not(Age Is Null) C. Age=Null D.Age Is Not Null10 、假定学生关系是 S(S#,Sname,Sex,Age)，课程关系是 C(C#,CName,TEACHER)，学生选课关系是 SC(S#,C#,Grade)。要查找选修“数据库系统概论”课程的“男”学生学号，将涉及到关系 ( )。 A.S B.SC,C C.S,SC D.S,SC,C

二、填空题1、 SQL 操作命令 CREATE 、 DROP、 ALTER主要完成的是数据的 _________功能。2 、 ________ 为关系数据库语言国际标准语言。3 、 SQL 中文含义是 _____________ ，它集查询、操纵、定义和控制等多种功能。4、视图是从 ______________________导出的表。它相当于三级结构中的外模式。5、视图是虚表，它一经定义就可以和基本表一样被查询，但 ________ 操作将有一定限制。

6、 SQL 的数据更新功能主要包括 ___________ 、 _____________ 和 _____________ 三个语句。7、在字符匹配查询中，通配符“%” 代表 ______________ ，“ _” 代表 __________________ 。8、 SQL 语句具有 _____________ 和 ________________两种使用方式。9、 SQL 语言中，实现数据检索的语句是 ________________ 。10 、在 SQL 中如果希望将查询结果排序，应在Select语句中使用 _________ 子句。

三、简答与 SQL 操作表达1、简述 SQL 的定义功能。2 、简述 SQL 语言支持的三级逻辑结构。3 、解释本章所涉及的有关基本概念的定义：基本表、导出表、视图、索引、聚集、系统特权、对象特权、角色，并说明视图、索引、聚集、角色的作用。4、在对数据库进行操作的过程中，设置视图机制有什么优点？它与数据表之间有什么区别？

5、设有四个关系（只示意性给出一条记录）： S SPJSNO SNAME ADDRESS TEL SNO PNO JNO QTYSl SN1 上海南京路 68564345 S1 Pl J1 200 P JPNO PNAME SPEC CITY COLOR JNO JNAME LEADER BGP1 PNl 8X8 无锡 红 J1 JN1 王总 10S （ SNO ， SNAME ， ADDRESS ， TEL ）其中， SNO ：供应商代码 SNAME ：姓名 ADDRESS ：地址 TEL ：电话；J（ JNO ， JNAME ， LEADER ， BG）其中， JNO ：工程代码 JNAME ：工程名 LEADER ：负责人 BG：预算；P（ PNO ， PNAME ， SPEC， CITY， COLOR ）其中， PNO ：零件代码 PNAME ：零件名 SPEC：规格 CITY：产地 COLOR ：颜色；SPJ（ SNO ， JNO ， PNO ， QTY）其中， SNO ：供应商代码 JNO ：工程代码 PNO ：零件代码 QTY：数量；为每个关系建立相应的表结构，添加若干记录。完成如下查询：

1. 找出所有供应商的姓名和地址、电话。2. 找出所有零件的名称、规格、产地。3. 找出使用供应商代码为 S1供应零件的工程号。4. 找出工程代码为 J2 的工程使用的所有零件名称、数量。5. 找出产地为上海的所有零件代码和规格。6. 找出使用上海产的零件的工程名称。7. 找出没有使用天津产的零件的工程号。8. 求没有使用天津产的红色零件的工程号。9. 取出为工程 J1 和 J2提供零件的供应商代号。10.找出使用供应商 S2供应的全部零件的工程号。完成如下更新操作：1. 把全部红色零件的颜色改成蓝色 ; 2. 由 S10供给 J4 的零件 P6 改为由 S8供应，请作必要的修改。3. 从供应商关系中删除 S2 的记录，并从供应零件关系中删除相应的记录。4. 请将 (S2, J8, P4, 200) 插入供应零件关系。5. 将工程 J2 的预算改为 40万元。6. 删除工程 J8订购的 S4 的零件。请将“零件”和“供应零件”关系的连接定义一个视图，完成下列查询：1. 找出工程代码为 J2 的工程使用的所有零件名称、数量。2. 找出使用上海产的零件的工程号。

6、在嵌入式 SQL 中如何区分 SQL 语句和主语句的？举例说明。7、在嵌入式 SQL 中如何解决数据库工作单元与程序工作单元之间沟通的？8、 SQL 的集合处理方式与宿主语言的单记录处理方式之间如何协调 ?