第3章 SQL语言初步 2017/3/14.

Presentation on theme: "第3章 SQL语言初步 2017/3/14."— Presentation transcript:

1 第3章 SQL语言初步 2017/3/14

2 主要内容（一） 3.1 SQL简介 3.2 基本的数据定义 3.3 基本的数据操作 SQL的特点 SQL语言的分类 定义、删除与修改基本表
索引的创建和删除 3.3 基本的数据操作 插入、修改、删除数据 2017/3/14

3 3.1 SQL简介 历史 SQL: Structured Query Language 1974年，由Boyce和Chamber提出
年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel 1986年 ANSI公布第一个SQL标准； 1987、1989、1992….不断扩充； 目前有标准：SQL86、SQL92、SQL99、SQL2003、SQL2006、SQL2008 本章主要讲述SQL92语言的用法。 要求能熟练使用SQL语句在不同的RDBMS中完成数据库的基本操作。 2017/3/14

4 ANSI(American Natural Standard Institute,美国国家标准学会)
标准化 有关组织 ANSI(American Natural Standard Institute,美国国家标准学会) ISO(International Organization for Standardization,国际标准化组织) 2017/3/14

5 “具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持 SQL-92
有关标准 SQL-86 “数据库语言SQL” SQL-89 “具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持 SQL-92 “数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 SQL-3 增加对面向对象模型的支持 SQL2003 支持 XML，支持 Window 函数、Merge 语句等 SQL2006 继续增强 XML方面的特性 SQL2008 2017/3/14

6 单一的结构----关系，带来了数据操作符的统一 面向集合的操作方式 一次一集合 高度非过程化
特点 一体化 集DDL，DML，DCL于一体 单一的结构----关系，带来了数据操作符的统一 面向集合的操作方式 一次一集合 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径 两种使用方式，统一的语法结构 SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用） 语言简洁，易学易用 2017/3/14

7 SQL的基本概念 请比较： 视图（View） 基本表（Table） 存储文件（File）
ANSI SPARC报告中的数据库三级模式 不同点 视图（View） 视图从若干个Table或其他视图上生成 视图是一张虚表 视图属于外模式（子模式） Database中只有视图的定义，而无实际存储的数据 基本表（Table） 存储文件（File） *.dbf(FoxPro) *.mdb(access) *.mdf (sql server) 2017/3/14

8 SQL对三级模式的对应支持 返回 用户1 用户2 用户 外模式 外模式 视图 外模式的基本单位，与基本表有本质区别 模 式 模式 基本表1
模 式 模式 基本表1 基本表2 内模式 内模式 存储文件 DB 内模式的基本单位，存储基本表 2017/3/14 返回

9 图1 SPARC三级模式结构 图2 SQL支持的数据库模式
模式∕内模式映像 外模式∕模式映像 应用1 应用2 应用3 外模式A 外模式B 模 式 内 模 式 OS DBMS 数据库 SQL用户 视图1 视图2 基本表1 基本表2 基本表3 存储文件1 存储文件2 存储文件3 外模式 模 式 内模式 图1 SPARC三级模式结构 图2 SQL支持的数据库模式 不同之处： 三级模式中，一个应用程序只能开启一个外模式，SQL中则不然 三级模式中，应用程序只能访问外模式，SQL中可以是外模式和模式 不同之处： 三级模式中，一个应用程序只能开启一个外模式，SQL中则不然 三级模式中，应用程序只能访问外模式，SQL中可以是外模式和模式 2017/3/14 返回

10 SQL数据库体系结构 用户1 用户2 用户4 视图V1 用户3 基本表B1 基本表B2 基本表B4 基本表B3 存储文件S1 存储文件S2
VIEW Table Stored file 图3 SQL数据库体系结构 2017/3/14

11 SQL分类 数据定义语言 DDL (Data Definition Language)
用于创建、更改或删除数据库对象，如表、视图、索引及其它对象。 语句包括：CREATE、ALTER、DROP(对关系模式的操作) 数据操纵语言DML (Data Manipulation Language) 用于操纵数据库对象（如表）。 语句包括：SELECT、INSERT、UPDATE、DELETE(对关系的操作) 数据控制语言DCL (Data Control Language) 用于定义控制访问对象（如表）。 语句包括：GRANT、REVOKE(用户的操作权限) 方法 讲授 SQL 语句的分类 只介绍类别和目的 解释语言的具体含义及 4GL 的用途 2017/3/14

12 INSERT，UPDATE，DELETE
SQL语句的9个核心动词 SQL功能 操作符 数据查询 SELECT 数据定义 CREATE，ALTER，DROP 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE 2017/3/14

13 主流数据库 DB2 (IBM) SQL Server (Microsoft) Access (Microsoft) Oracle
MySQL(MySQL AB) PostgreSQL(加州大学伯克利分校) 2017/3/14

14 实验环境 安装SQL Server 2000/8/12 配置SQL Server 2000/8/12
2017/3/14

15 安装SQL Server 保证计算机的软硬件环境能满足SQL Server 的需要 根据所期望的用途和计算机的软硬件环境选择合适的版本和部件
Win7或以上 32位或64位OS .Net Framework + Windows Power Shell 2.0 2017/3/14

16 SQL Server 2012的管理工具和实用程序 管理工具 配置工具 性能工具
数据库引擎、Analysis Services、Reporting Services(报表服务)、Integration Service 配置工具 SQL Server 配置管理器、SQL Server错误和使用情况报告、Reporting Services 配置 性能工具 事件探查器、数据库引擎优化顾问 2017/3/14

17 3.2 基本的数据定义（一） 表TABLE 创建基本表 表结构的修改 删除基本表 2017/3/14

18 数据定义功能DDL概述 SQL的数据定义语句（DDL）包括以下语句： 注意：索引无修改语句！ 创建 删除 修改 表 视图 索引
CTEATE TABLE DROP TABLE ALTER TABLE 视图 CTEATE VIEW DROP VIEW ALTER VIEW 索引 CTEATE INDEX DROP INDEX 注意：索引无修改语句！ 2017/3/14

19 数据库的建立与撤消 建立一个新数据库 撤消一个数据库 指定当前数据库 create database 数据库名
drop database 数据库名 指定当前数据库 use 数据库名 2017/3/14

20 创建stuDB数据库 create database stuDB on primary -- 默认就属于primary文件组,可省略
( /*--数据文件的具体描述--*/ name='stuDB_data', -- 主数据文件的逻辑名称 filename='D:\stuDB_data.mdf', -- 主数据文件的物理名称 size=5mb, --主数据文件的初始大小 maxsize=100mb, -- 主数据文件增长的最大值 filegrowth=15%--主数据文件的增长率 ) log on ( /*--日志文件的具体描述,各参数含义同上--*/ name='stuDB_log', filename='D:\stuDB_log.ldf', size=2mb, filegrowth=1mb) 2017/3/14

21 数据类型 字符型 Char(n)：固定长度的字符串，如定义char(10), ’abcd’不足10位时空格补足
Varchar(n)：可变长度字符串，varchar(10), ’abcd’存储4位 n最大为8000 数值型 整数型 bigint：8字节、int：4字节、smallint：2字节、tinyint：1字节(8位，0-255之间整数) 小数型 numeric(p,s)：定点数，有效位共p位，小数位s位，0<=s<=p<=38, numeric(18,2)，小数点后共2位 decimal，用法同numeric 2017/3/14

22 Float, 字节数为4，32位，7个有效位；Double，字节数为8，64位，15个有效位 货币型
Money，8个字节，小数点后最多4位，多时四舍五入 Smallmoney，4个字节 日期型 datetime ：精确度1/300秒，8字节 Smalldatetime：范围缩小，精确度1秒，4字节 文本型 text 存储大量的文字时 布尔型 bit 取值0，1或空。True、false; yes、no等 2017/3/14

23 创建表 语法1： CREATE TABLE <表名>
(<列名> <数据类型> [NULL | NOT NULL], …, [PRIMARY KEY <主键>,] [FOREIGN KEY <外键> REFERENCES <外键所属表名>, …,] [CHECK <校验条件>,]); 例1. 建立student关系 CREATE TABLE student( sno NUMERIC(6,0) NOT NULL, sname CHAR(8) NOT NULL, age NUMERIC(3,0), sex CHAR(2), bplace CHAR(20)); 2017/3/14

24 创建表（续） 例2. 创建职工表： CREATE TABLE Employee
( Eno CHAR(4) NOT NULL UNIQUE , /* Eno取唯一值 */ Ename CHAR(8) NOT NULL , Sex CHAR(2) NOT NULL default('男'), /* Sex的默认值为“男”*/ Age INT NULL , Is_Marry CHAR(1) NULL , Title CHAR(6) NULL , Dno CHAR(2) NULL ); 2017/3/14

25 其中，自定义的表名或者列名若是由包含多个单词的词组所构成，则必须选择使用中括号（[ ]）或者双引号（“ ”）将此表名或列名括起在内
例3. 创建职工表2，其中表名或者列名包含多个单词构成： CREATE TABLE Employee2 ( [Employee no] CHAR(4) NOT NULL UNIQUE , "Employee name" CHAR(8) NOT NULL , Sex CHAR(2) NOT NULL , Age INT NULL , Is_Marry CHAR(1) NULL , Title CHAR(6) NULL , Dno CHAR(2) NULL ); 2017/3/14

26 语法2： 在查询结果基础上 创建新表 CREATE TABLE <表名> [AS <查询子句>]
[(<列名>[NOT NULL], …)] [CLUSTER <簇名> (<列名>, …)]; 例4. 创建女学生girl_student关系 CREATE TABLE girl_student AS SELECT sno, sname, age FROM student WHERE sex=‘女’； 在查询结果基础上 创建新表 MSSQL SERVER中实现： SELECT sno, sname, age INTO girl_student FROM student WHERE sex=‘女’； 2017/3/14

27 删除表 语法 DROP TABLE <表名1>[，<表名2>] 说明 DROP与DELETE区别： 例5. 删除表
此语句一执行，指定的表即从数据库中删除（表被删除，表在数据字典中的定义也被删除） 此表上建立的索引和视图也被自动删除 DROP TABLE语句一次可以删除多个表，多表间用逗号逗开。 DROP与DELETE区别： DELETE清空表中所有数据行 例5. 删除表 DROP TABLE student; 2017/3/14

28 修改表结构 语句 ALTER TABLE <表名>
[ ADD <列名><数据类型>[列的完整性约束]]| [ ADD <表级完整性约束>] [ ALTER COLUMN <列名><新的数据类型>] [ DROP COLUMN <列名>] [ DROP CONSTRAINT <表级完整性约束名>]; 说明 ADD：为表增加一新列或者表级完整性约束，具体规定与CREATE TABLE的相当，但新列必须允许为空（除非有默认值)； DROP COLUMN：在表中删除一个原有的列； ALTER COLUMN：修改表中原有列的数据类型；通常，当该列上有约束定义时，不能修改数据类型。 DROP CONSTRAINT：删除原有的表级约束。 2017/3/14

29 修改表结构（续） 对表增加列 语法：ALTER语句的形式： ALTER TABLE <表名>
ADD <列名> <数据类型> [NULL | NOT NULL], …; 例6. 在student表中增加一列address ALTER TABLE student ADD address CHAR(50); 例7. 在Employee表中增加一列负责人Emgr ALTER TABLE Employee ADD Emgr CHAR(4) NULL; 如果原表中已存在数据，则新定义的列必须设置为NULL （除非有默认值） ; 2017/3/14

30 修改表结构（续） 对表删除列 语法：ALTER语句的形式： ALTER TABLE <表名>
DROP COLUMN <列名> ,…; 例8. 删除新添列address ALTER TABLE student DROP COLUMN address; 2017/3/14

31 修改表结构（续） 增大某个列 语法： ALTER TABLE <表名>
ALTER COLUMN <列名> [<数据类型>] [NULL | NOT NULL], …; 例9. 修改student表的列bplace的数据类型。 ALTER TABLE student ALTER COLUMN bplace CHAR(30); （条件：列为空。改变数据类型和缩短长度） 例10. 修改列Emgr的数据类型为CHAR(10)。 ALTER TABLE Employee ALTER COLUMN Emgr CHAR(10); /* 只修改数据类型，不可修改列名*/ 如果列中已存在数据，则不能改变数据类型， 且不能缩短列的宽度，但可以增大列宽 2017/3/14

32 修改表中约束（详见第4章） 添加约束 删除约束 语法： 例11. 新增唯一性约束 例12. 删除新增的唯一性约束
ALTER TABLE <表名> ADD [CONSTRAINT <约束名> ]<具体约束>,…; 例11. 新增唯一性约束 ALTER TABLE student ADD CONSTRAINT uq_bplace UNIQUE(bplace); 删除约束 DROP [CONSTRAINT] <约束名> ,…; 例12. 删除新增的唯一性约束 DROP CONSTRAINT uq_bplace; 2017/3/14

33 *查看表 查看表的定义 查看表间的依赖关系 查看表上的约束 查看表中的数据 sp_help
显示：表的基本定义、表中的每个列的定义和表中标识列的定义 查看表间的依赖关系 sp_depends 显示：所有依赖于所查表的对象和所有所查表依 赖的对象 查看表上的约束 sp_helpconstraint 查看表中的数据 2017/3/14

34 *重命名 ATLER TABLE语句不支持列名的修改，如若需要重命名列名，则可以使用系统存储过程sp_rename来完成。
例13. 将表Emoployee2重命名为Emp。 EXEC sp_rename ‘Employee2’,‘Emp’ ; 系统存储过程sp_rename也可将表名进行重命名。 例14. 将Emp表中的列Employee name重命名为Ename。 EXEC sp_rename 'Emp.[Employee name]',‘Ename’,'column'; 2017/3/14

35 3.3 基本的数据操作 数据的更新操作： 表中增加元组 INSERT 修改表中数据 UPDATE 删除元组 DELETE
含有子查询的数据更新（3.5） 通过视图修改数据（3.6.4） 2017/3/14

36 INSERT插入数据 基本语法： 有两种方式： 必须注意： INSERT [ INTO ]
table_or_view [ ( column_list ) ] { data_values | select_statement } 有两种方式： 使用VALUES关键字直接给各列赋值 使用SELECT子句，把从其他表或视图中选取的数据插入。 必须注意： 输入项的顺序和数据类型必须与表中列的顺序和数据类型相一致。 可以不给全部列赋值，但没有赋值的列必须是可以空的列、有默认值的列、IDENTITY列(值自动增长)等。 字符型和日期型值插入时要用单引号括起来。 2017/3/14

37 在指定表中插入一行数据 语句基本格式 INSERT INTO <表名>[（<属性名清单>）]
VALUES (<常量清单>)； 说明 若有<属性名清单>，则<常量清单>中各常量为新记录中这些属性的对应值(根据语句中的位置一一对应)。 但该表定义时，说明为NOT NULL，且无默认值的列必须出现在<属性名清单>中，否则将出错。 若无<属性名清单>，则<常量清单>必须按照表中属性的顺序，为每个属性列赋值(每个属性列上都应有值)。 <常量清单>中，字符串常量和日期型常量要用单引号（英文符号）括起来。 2017/3/14

38 在指定表中插入一行数据（续） 例1. 在Employee表中插入一职工记录。 INSERT INTO Employee
VALUES ('2002' ,'胡一兵' ,'男',38,'1','工程师','01'); 例2. 在Employee表中插入一新职工：宋文彬，编号为2003，男。 INSERT INTO Employee (Eno,Ename,Sex) VALUES ('2003','宋文彬','男'); 2017/3/14

39 使用INSERT..SELECT语句插入多行（详见3.5）
可以将从一个或多个表或视图中选择来的数据添加到表中去。 可将数据添加到一行的部分或全部列中。 被插入数据的标语SELECT子句的结果集必须兼容，即列数、列序、数据类型都兼容。 从其它的表查询来的数据插入到表中 2017/3/14

40 UPDATE修改数据 语句格式 UPDATE <表名>
SET <列名> = <表达式> [,<列名>=<表达式>]n [WHERE <条件>]； 说明 本语句可修改符合<条件>记录中一个或多个列的值。 可使用SET子句指定要被修改的列及修改后的数据。 当没有WHERE子句指定修改条件时，表中所有行的指定列将被修改为SET子句给出的新数据。 新数据可以是指定的常量或表达式，或是来自于其他表的数据（通过SELECT子句）。 2017/3/14

41 UPDATE修改数据（续） 例3. 在工资表中，将所有职工的基本工资都增加50。 UPDATE Salary
SET Basepay = Basepay + 50; /* 修改多个元组的值 */ 例4. 将职工“胡一兵”修改为“胡一民”。 UPDATE Employee SET Ename= '胡一民' WHERE Ename= '胡一兵'; /* 修改某个元组的值 */ 2017/3/14

42 DELETE删除数据 语句格式 DELETE FROM <表名> [WHERE<条件>] 说明
若有指定WHERE<条件>项时，则从表中删除满足WHERE子句条件的所有元组。 若无WHERE<条件>项时，将删除<表名>中的所有记录。但是，该表的结构还在（包括属性、约束等），只是没有了记录，是个空表而已。 例5. 从职工表中删除Eno(职工号)为1003的记录。 DELETE FROM Employee WHERE Eno= '1003'; /* 删除某个元组的值 */ 例6. 删除Employee中的所有记录。 DELETE FROM Employee; /* 删除多个元组的值 */ 2017/3/14

43 3.2 基本的数据定义（二） 索引 INDEX 创建索引 删除索引 2017/3/14

44 索引 索引的概念和作用 索引的类型 如何使用企业管理器创建和管理索引 如何使用T-SQL语句创建和管理索引 2017/3/14

45 索引 是否有效地设计了索引(index)，是影响SQL Server性能的因素之一。
2017/3/14

46 索引的作用 在建立了索引的列作为查询条件时，索引能大大地提高SQL语句的执行速度。
如果把一个基本库表比作一本书，索引就好像书的目录，通过查询目录，可找到相关章节的页号，从而可迅速地找到那一节内容。不同的是，基本表可建立不止一个索引，它可按不同的属性或表达式建立多个索引。 索引还有能够加速ORDER BY和GROUP BY语句的执行、强制行的唯一性等优点。 2017/3/14

47 索引的使用示例 全表扫描缺点：十分耗时 为避免对基本表进行全表扫描，RDBMS核心一般都在主码 上自动建立一个索引
用全表扫描方法检查主码唯一性 全表扫描缺点：十分耗时 为避免对基本表进行全表扫描，RDBMS核心一般都在主码 上自动建立一个索引 2017/3/14

48 索引的使用示例 通过B+树索引查找基本表中是否已经存在新的主码值，可以提高效率 如果新插入记录的主码值是25
读取3个结点： 根结点（51） 中间结点（12 30） 叶结点（ ） 该主码值已经存在，不能插入这条记录 使用索引检查主码唯一 2017/3/14

49 索引的概念 SQL Server的索引是一种物理结构，它能够提供一种以一列或多列的值为基础迅速查找表中行的能力。
索引可在定义表时创建，也可在定义表之后的任何时候创建。 索引可创建在一列或多列的组合上，也可在数据库表的多个列上建立不同的索引。 2017/3/14

50 索引使用的场所 索引虽重要，也不是越多越好，因为： 可以不考虑建立索引的场所： 创建索引要花费时间和占用存储空间。
索引虽加快了检索速度，却减慢了数据修改的速度。 可以不考虑建立索引的场所： 很少或从来不作为查询条件的列。 在小表中通过索引查找行可能会比简单的进行全表扫描还慢。 只从很小的范围内取值的列。(性别列无需建立索引) 2017/3/14

51 索引使用的场所 在考虑是否在列上创建索引时，应考虑该列在查询中起到什么样的作用，以下情况适合创建索引： 用作查询条件的列，比如主键。
频繁按范围搜索的列，比如WHERE quantity>=5 and quantity<=20。 连接中频繁使用的列。 2017/3/14

52 索引的类型 按照索引的结构划分： 按照索引实现的功能划分： 聚簇索引(clustered) 非聚簇索引(notclustered)
唯一性索引 非唯一性索引 2017/3/14

53 索引的类型 聚簇索引 行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。 每个表只能有一个聚簇索引。
聚簇索引有利于范围搜索。 如表中没有创建其他的聚簇索引，则在表的主键列上自动创建聚簇索引。 2017/3/14

54 索引的类型 非聚簇索引 索引的逻辑顺序并不等同于表中行的存储顺序。 索引仅仅记录指向表中行的位置的指针。
非聚簇索引作为与表分离的对象存在，可为表中每一个常用于查询的列定义非聚簇索引。 很适合于那种直接匹配单个条件的查询，不太适合于返回大量结果的查询。 为一个表建立索引默认都是非聚簇索引，在一列上设置唯一性约束也自动在该列上创建非聚簇索引。 2017/3/14

55 cno cname credit 1 database 4 2 os 3 c cno cname credit 1 database 3 2
mis 未建立聚簇索引， 往表中添加一条记录： 4 mis 2 2017/3/14

56 在cname列上 建立聚簇索引 建立聚集索引后， 往表中添加一条记录后形成的新表： cno cname credit 3 c 4 1
database mis 2 os cname c database os mis 4 mis 2 2017/3/14

57 索引的类型 唯一性索引 一个唯一性索引能够保证在创建索引的列或多列的组合上不包括重复的数据。 聚簇索引和非聚簇索引都可以是唯一性索引。
在创建主键约束和唯一性约束的列上会自动创建唯一性索引。 2017/3/14

58 在cname列上 建立唯一性索引 建立唯一性索引后， 往表中添加一条记录后形成的新表： cno cname credit 1
database 3 2 os c 4 5 cname database os c 4 os 5 os 2017/3/14

59 创建索引 语句格式： CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX <索引名>
说明： ASC升序，DESC降序，缺省为ASC。 CLUSTERED为聚簇索引，一个表只有一个。 NONCLUSTERED非聚簇索引。 UNIQUE表示唯一索引。 例1. 按课程表的课程名建立索引。 CREATE UNIQUE INDEX course_name ON course(cname); 例2. 为职工表建立一索引，首先以部门值排序，部门相同时，再以职工号降序排序。 CREATE INDEX IX_Emp ON Employee (Dno , Eno DESC); 默认为非聚族索引 Unique 索引的每一个索引值只对应于表的唯一记录 2017/3/14

60 删除索引 语句格式 DROP INDEX <索引名1>[，<索引名2>] 说明 例3. 删除IX_Emp索引。
本语句将删除规定的索引。该索引在数据字典中的描述也将被删除。 <索引名>：规定使用“表名|视图名.索引名”的格式。 例3. 删除IX_Emp索引。 DROP INDEX Employee.IX_Emp; 2017/3/14

61 *查看索引 Enterprise Manager
T-SQL的系统存储过程sp_helpindex，结果返回该表上所有索引的名称、类型和建索引的列。 例4. 查看Employee表上所有索引的相关信息 exec sp_helpindex Employee 2017/3/14

62 回顾 SQL语言 DDL用于创建、更改或删除数据库对象 CREATE、ALTER、DROP 索引的概念——聚集索引和非聚集索引
DML用于操纵数据库对象（如表） SELECT,INSERT,UPDATE,DELETE 2017/3/14

63 作业 第一次上机实验内容 详见附录A： 在Microsoft SQL Server环境中，上机实现书本第3.2、3.3节中的各例题
“上机实验三 基本表的建立和修改” 在Microsoft SQL Server环境中，上机实现书本第3.2、3.3节中的各例题 2017/3/14

64 SQL语言初步 （二） 数据查询 （3.4节） 2017/3/14

65 查询数据 “查询”的含义：从数据库中获取数据 最基本方式：使用SELECT语句
结果集中包含哪些列，即要从表中取哪些列的数据 以何种条件从表中取数据，即表中的行被包含在结果集中的条件 要查询的哪些表，以及表之间的逻辑关系 结果集中的行的排列顺序 2017/3/14

66 SELECT语句 最基本的SELECT语句形式： SELECT select_list FROM table_source
2017/3/14

67 SELECT语句的完整语法 SELECT [ DISTINCT | ALL ] select_list FROM table_source
[ WHERE search_condition ] [ GROUP BY group_by_expression [ HAVING search_condition ] ] [ ORDER BY order_expression [ ASC | DESC ] ] Distinct 只有不重复的行返回，默认为all Group by 查询返回的行将按照某一个列或某几个列的值分组 Having 结果集要满足的条件，结果集有出生日期，2015-出生日期年龄大于25 Order by 排序，升序或降序，默认为升序 2017/3/14

68 检索数据 参数 描述 ALL 表示表中所有行都将被返回，包括重复的行(它是缺省的，不用显式写出)，默认为all DISTINCT
只有不重复的行被返回。这个子句在每个SELECT子句中只可使用一次 FROM 给出SELECT 语句中指定的域是从哪个表中获取的 WHERE 给出从表中选取记录的条件 GROUP BY 查询返回的行将按照某一个列或某几个列的值分组 HAVING 给出结果集中每个组必须满足的条件。该子句只和GROUP BY 子句联合使用 ORDER BY 将查询结果根据某个域或某几个域的值按照升序（ASC）或者降序（DESC）排列 2017/3/14

69 查询表中列 查询关系中所有信息 例1. 查询职工表的所有信息。
SELECT Eno, Ename, Sex, Age, Is_Marry, Title FROM Employee 该语句可以用以下语句代替： SELECT * -- “*”代替表中的所有列 指定要查询的列 例2. 查询职工的职称。 SELECT Title FROM Employee; Select distinct title from Employee 2017/3/14

70 消除结果集中重复行 使用DISTINCT关键字可去掉结果集中的重复行。 例3.消除结果集中重复行 SELECT Title
FROM Employee; SELECT DISTINCT Title SELECT ALL Title FROM Employee; 2017/3/14

71 在结果集中使用常量和计算值 SELECT '职工序号：',Eno, '职称：',Title FROM Employee;
例4.在结果集中使用常量 SELECT '职工序号：',Eno, '职称：',Title FROM Employee; 例5. 查询职工的姓名、出生年份、职称 SELECT Ename, 2010-Age, Title --改进 SELECT Ename, 2010-Age AS [Year of Birth], Title --等价于 SELECT Ename, [Year of Birth]= 2010-Age, Title 无列名 As 命名某一字段，列名加中括号[]，或双引号 2017/3/14

72 给结果集中的列指定别名 例6.给结果集中的列指定别名 SELECT Eno as '职工序号',Title as '职称'
FROM Employee; --等价于 SELECT '职工序号'=Eno, '职称'=Title 指定列名使用As 2017/3/14

73 条件选择查询 基本语法： SELECT select_list FROM table_source
WHERE search_condition (选择查询结果的条件） SQL Server 支持比较、范围、列表、字符串匹配等选择方法。 Where后加条件 2017/3/14

74 基于比较条件选择查询结果 WHERE子句的查询条件可以是比较布尔表达式。 例7.查询所有工程师的姓名、年龄。
SELECT Ename, Age FROM Employee WHERE Title= '工程师'; 例8.查询所有在2009年底前结束的项目情况。 SELECT * FROM Item WHERE End_date<'1/1/2010'; 2017/3/14

75 带限制条件的检索 除了比较符，可以在WHERE子句中使用下列关键字 关键字 含义 IN 判断值是否在所给的范围内 NOT IN
判断值是否不在所给的范围内 LIKE 判断值是否与一个给定的值相似 NOT LIKE 判断值是否与一个给定的值不相似 IS NULL 判断值是否为空 IS NOT NULL 判断值是否不为空 AND 判断是否满足AND两边所给的条件 OR 判断是否满足OR 两边所给的条件中的一个 BETWEEN…AND 判断值是否在所给的值之间 NOT ETWEEN…AND 判断值是否不在所给的值之间 Where title in(‘工程师’, ‘高工’); where title=‘工程师’ OR title=‘高工’ 条件：比较 Like 通配符, %abc%, abc%， 图书馆书目检索中的前端匹配，任意匹配，%任意长度字符串，长度可为0 _ 任意单个字符，’C\_%’ escape’\’ 去掉\的字符，_为字符而不是通配符 2017/3/14

76 基于列表条件选择查询结果 使用IN关键字可以查询符合列表中任何一个值的列。
例11.查询具有工程师及以上职称的职工姓名及所在部门号。 SELECT Ename, Dno FROM Employee WHERE Title= '工程师' OR Title= '高工'; --等价于 WHERE Title IN ('工程师' , '高工'); Or为之后会解释的逻辑运算符

77 基于字符串匹配条件选择查询结果 使用LIKE运算符可以完成对字符串的模糊匹配。 基本语法： SELECT select_list
FROM table_sourse WHERE EXPRESSION LIKE ‘STRING’ 其中，EXPRESSION为用作选择条件的列或表达式 指定的字符串中可以包含通配符，包括： %：代表任意多个字符。例：%jing _（下划线）：代表单个字符。例：_ouse [ ]：代表指定范围内的单个字符。例：[mh]ouse [^ ]：代表不再指定范围内的单个字符。例：[^mh]ouse 第1、2位为m, h 第1、2位不是m, h 2017/3/14

78 例12.从Item表中查找Iname（项目名称）中包含“性能研究”的项目名称。 SELECT Iname FROM Item
WHERE Iname LIKE '%性能研究%'; 例13.查找电子邮件地址在hotmail.com上的职工 SELECT Ename, Dno FROM Employee WHERE address LIKE 例14.查看电话号码第一位不是8的部门名 SELECT Dno, Dname WHERE Phone LIKE '[^8]%' 2017/3/14

79 基于空值（NULL）选择查询结果 NULL表示空值（一种未知的、不存在的或不可应用的数据）。
可以用IS NULL 或 IS NOT NULL 来检验一列是否为空值。 例15. 查询所有有电子邮件地址的客户 SELECT Ename, Dno FROM Employee WHERE address IS NOT NULL 例16. 从项目表中选取鉴定日期不为空的记录。 SELECT * FROM Item WHERE Check_date IS NOT NULL; 2017/3/14

80 基于范围条件选择查询结果 使用BETWEEN…AND…关键字 例9.查询所有在2008-2009年间启动的项目情况。 SELECT *
FROM Item WHERE Start_date between '1/1/2008' and '12/31/2009'; 例10.从Salary表中查找Basepay（基本工资）不在500和700间的元组，显示符合条件元组的所有属性。 FROM Salary WHERE Basepay NOT BETWEEN 500 AND 700; 2017/3/14

81 基于多个条件选择查询结果 WHERE子句中，也可以使用逻辑运算符来连接多个条件。 主要有逻辑运算符： AND OR NOT
相互之间可以转换与in 2017/3/14

82 WHERE ( Basepay BETWEEN 600 AND 700 )
例17. SELECT * FROM Salary WHERE ( Basepay BETWEEN 600 AND 700 ) AND ( Service IN (600.0,700.0,1300.0,2500.0) ) AND Eno LIKE '100_'; 本例将从Salary表中选取符合下列条件的元组： ① 基本工资在600与700之间； ② 津贴为600，700，1300，2500； ③ 职工号前三位为100。 2017/3/14

83 查询结果排序 默认情况：查询结果集中行的顺序是在表中的顺序。 通过ORDER BY子句，可以改变查询结果的显示顺序。 语法：
SELECT select_list FROM table_source WHERE search_condition ORDER BY order_expression [ASC | DESC] order_expression是排序依据的列名或列号 ASC表示按升序，DSEC表示按降序，默认为ASC 2017/3/14

84 查询结果排序（续） *返回有限的结果 语法： TOP n [ PRECENT ] 例18. 查询经费最高的三个项目名称及其经费。
N表示返回查询到的前n个结果 使用了precent参数，则n为百分比 例18. 查询经费最高的三个项目名称及其经费。 SELECT top 3 Iname,Outlay FROM Item ORDER BY Outlay DESC 2017/3/14

85 查询结果排序（续） ORDER BY 子句中也可以包括多个列 例19. 查询结果首先以部门号的降序排序，部门号相同的再以年龄的顺序排列。
SELECT * FROM Employee WHERE Is_Marry=1 ORDER BY Dno DESC, Age; 2017/3/14

86 聚合函数 聚合函数计算表中数据的总和，得到统计的结果。 SQL Server提供以下聚合函数：
COUNT([DISTINCT/ALL]*) --统计结果中元组个数 COUNT([DISTINCT/ALL]<列名>) --统计一列上元组个数 MAX(<列名>) --给出一列上的最大值 MIN(<列名>) --给出一列上的最小值 SUM([DISTINCT/ALL]<列名>) --给出一列上值的总和(只对数值型) AVG([DISTINCT/ALL]<列名>) --给出一列上值的平均值(只对数值型) 2017/3/14

87 SELECT count(*) FROM employee; /* 得到职称的个数*/
例20.统计记录数。 /* 得到employee表的记录数 */ SELECT count(*) FROM employee; /* 得到职称的个数*/ SELECT count(title) FROM employee; /* 得到职称的种类数*/ SELECT count(distinct title) FROM employee; 例21. 求所有项目的总经费、平均经费、最高经费和最低经费。 SELECT sum(Outlay) AS 总经费,avg(Outlay) AS 平均经费, max(Outlay) AS 最高经费,min(Outlay) AS 最低经费 FROM Item; 2017/3/14

88 数据分组 GROUP BY HAVING *COMPUTE 与 COMPUTE BY 2017/3/14
Group by 分组，having 对分组的输出，符合having条件的组才输出 Where与having都是跟条件，where是作用于表，having作用于查询结果 2017/3/14

89 GROUP BY 可以按一定的条件对查询的结果进行分组，再对每一组数据计算统计信息。 对查询结果分组的目的是为了细化集函数的作用对象。
一般地，当SELECT的<目标列表达式[别名]清单>中有聚合函数(COUNT、SUM等)时，才使用GROUP BY子句。 语法： SELECT select_list FROM table_source WHERE search_condition GROUP BY group_by_expression 2017/3/14

90 GROUP BY子句使用注意点： SELECT 子句中目标的一致性 不能把单行目标与聚合函数混在一起使用。 多行目标 聚合函数
SELECT Title,COUNT(Eno) FROM Employee; 多行目标 聚合函数 选择列表中的列包含在：①聚合函数②GROUP BY 子句 上例修正： 例22. 查询所属于每种职称的职工人数。 SELECT Title, COUNT(Eno) FROM Employee GROUP BY Title; 句子语法错误! 2017/3/14

91 例23. 把职工表中元组按部门分组，计算平均年龄；输出部门号和平均年龄两列。
SELECT Dno, AVG(Age) AS Average_Age FROM Employee GROUP BY Dno; 可以按多个条件分组 例24.查询每个部门内每种职称的职工人数。 SELECT Dno,Title, COUNT(Eno) GROUP BY Dno,Title ORDER BY Dno,Title Desc; 规律，group by 后跟的字段为，查询字段中除聚合函数外的字段 2017/3/14

92 HAVING短语的使用 SELECT [ DISTINCT | ALL ] select_list FROM table_source
[ WHERE search_condition ] [ GROUP BY group_by_expression [ HAVING search_condition ] ] [ ORDER BY order_expression [ ASC | DESC ] ] 2017/3/14

93 HAVING短语 用来向使用GROUP BY子句的查询中添加数据过滤准则。 WHERE子句和HAVING短语的区别： 作用条件不同
执行时间不同 GROUP BY子句分组之前先去掉不满足WHERE中条件的行； 而HAVING短语中的条件在分组之后被应用。 聚合函数使用不同 HAVING短语可以在条件中包含聚合函数； 但WHERE子句不可包含。 作用于被查询表，having作用于查询结果 Having后通常是包含聚合函数的 2017/3/14

94 例25.查询部门平均年龄小于40的部门及部门平均年龄。 SELECT Dno , AVG(Age) AS Average_Age
FROM Employee GROUP BY Dno HAVING AVG(Age)<40; 例26. 查询部门总基本工资发放高于2000的部门名称及具体发放总数。 SELECT Dname,SUM(Basepay) FROM Employee E,Department D,Salary S WHERE E.Eno=S.Eno and E.Dno=D.Dno GROUP BY Dname HAVING SUM(Basepay)>2000 2017/3/14

95 *COMPUTE与COMPUTE BY COMPUTE子句可以用来计算汇总数据。
若使用COMPUTE BY，则必须使用ORDER BY，后面出现的列的顺序必须与ORDER BY后出现的顺序相同，或者是他的子集。 Compute功能已被 sql server 2012废弃 2017/3/14

96 例27. 查询在2008年以后立项的项目经费，并统计总经费 SELECT SUM(Outlay) FROM Item
WHERE Start_date>‘1/1/2008’; 对比查询结果 SELECT Ino,Iname,Outlay WHERE Start_date>'1/1/2008' COMPUTE SUM(Outlay) ; 2017/3/14

97 例28. 查询每个部门人数及每个部门总基本工资发放情况。 SELECT Dname,COUNT(Ename),SUM(Basepay)
FROM Employee E,Department D,Salary S WHERE E.Eno=S.Eno and E.Dno=D.Dno GROUP BY Dname 对比查询结果 SELECT Dname,Ename,Basepay ORDER BY Dname COMPUTE COUNT(Ename),SUM(Basepay) BY Dname 2017/3/14

98 从多个表中查询数据----表连接 使用表连接可以从多个相关的表中查询数据。 连接条件 连接字段 用来连接两个表的条件称为连接条件或连接谓词。
常用格式 [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 比较运算符：=、>、<、>=、<=、!= [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> 连接字段 连接谓词中的列名称为连接字段。 连接条件中的各连接字段类型必须是可比的，但不必是相同的。 字段的类型要兼容，smallint, int, bigint是相互兼容的 Char与varchar兼容 Datetime与smalldatetime兼容 2017/3/14

99 连接查询的类型 广义笛卡尔积（交叉连接） 等值连接（含自然连接） 非等值连接查询 自身连接查询 外连接查询 复合条件连接查询
2017/3/14

100 对FROM子句中的表名指定别名 格式： 表名 AS 别名 或 表名 别名 例29.对FROM子句中的表名指定别名
SELECT e.Eno,e.Ename FROM Employee AS e FROM Employee e SELECT Eno,Ename FROM Employee 2017/3/14

101 交叉连接(广义笛卡尔积) 也叫非限制连接，将两个表不加任何约束的组合起来，即两个表的广义笛卡尔积。
交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。 语法： SELECT select_list FROM table1 CROSS JOIN table2 或 FROM table1 , table2 例30. 两个表的笛卡尔乘积查询。 SELECT e.Ename, e.Title, e.Dno, d.Dno, d.Dname FROM Employee e, Department d; 2017/3/14

102 等值连接(EquiJoin) 只有在两个表中匹配的行才能在结果集中出现。 等值连接 表的连接条件经常采用“主码=外部码”的形式。
连接运算符为 = 的连接操作 [<表名1>.]<列名1> = [<表名2>.]<列名2> 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。 表的连接条件经常采用“主码=外部码”的形式。 2017/3/14

103 语法： SELECT select_list FROM table1 [INNER] JOIN table2
ON table1.column1=table2.column2 和 FROM table1 ，table2 WHERE table1.column1=table2.column2 Join即表示inner join 后面一种较常用 等值连接与内连接可转换 2017/3/14

104 内连接(Inner Join) 内连接也称自然连接。 等值连接的一种特殊情况，把目标列中重复的属性列去掉。
<表名1>.<列名1> = <表名2>.<列名2> 自然连接必须是相同的属性组，外码；等值连接要求相等的分量，不一定是公共属性 自然连接除去重复属性 2017/3/14

105 例31. 输出Employee表和Department表中部门号相等的连接元组。
SELECT Ename, Title, e.Dno, d.Dno, Dname FROM Employee e INNER JOIN Department d ON e.Dno=d.Dno; 也可以在WHERE子句中指定连接类型和条件： FROM Employee e , Department d WHERE e.Dno=d.Dno; 对上例，用自然连接实现。 SELECT Ename, Title, e.Dno, Dname 内连接与where子句可转换 内连接与等值连接可转换 2017/3/14

106 *连接操作的执行过程 嵌套循环法(NESTED-LOOP) 排序合并法(SORT-MERGE) 索引连接(INDEX-JOIN)
2017/3/14

107 *嵌套循环法(NESTED-LOOP) select * from s,spj where s.sno=spj.sno;
PNO JNO QTY S1 P1 J1 200 S2 J3 100 J4 700 P2 J2 P3 400 500 J5 P5 S5 S3 S4 P6 300 S表 SNO SNAME STATUS CITY S1 精 益 20 天津 S2 盛 锡 10 北京 S3 东方红 30 S4 丰泰盛 S5 为 民 上海 results SNO SNAME CITY QTY S 精 益 天津 S 精 益 天津 S 精 益 天津 S 精 益 天津 S 盛 锡 北京 2017/3/14 ……

108 嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。 重复上述操作，直到表1中的全部元组都处理完毕 2017/3/14

109 *排序合并法(SORT-MERGE) SPJ表 S表 2017/3/14 results SNO SNAME CITY QTY SNO
PNO JNO QTY S1 P1 J1 200 J3 100 J4 700 P2 J2 S2 P3 400 500 J5 P5 S3 S4 P6 300 S5 S表 SNO SNAME STATUS CITY S1 精 益 20 天津 S2 盛 锡 10 北京 S3 东方红 30 S4 丰泰盛 S5 为 民 上海 results SNO SNAME CITY QTY S 精 益 天津 S 精 益 天津 S 精 益 天津 S 精 益 天津 S 盛 锡 北京 2017/3/14 ……

110 排序合并法(SORT-MERGE)常用于等值连接
首先按连接属性对表1和表2排序 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续 重复上述操作，直到表1或表2中的全部元组都处理完毕为止 2017/3/14

111 *索引连接(INDEX-JOIN) SPJ表 S表 ………… 2017/3/14 S2 S1 results SNO SNAME CITY
PNO JNO QTY S1 P1 J1 200 S2 J3 100 J4 700 P2 J2 P3 400 500 J5 P5 S5 S3 S4 P6 300 S2 S1 S表 SNO SNAME STATUS CITY S1 精 益 20 天津 S2 盛 锡 10 北京 S3 东方红 30 S4 丰泰盛 S5 为 民 上海 results SNO SNAME CITY QTY S 精 益 天津 S 精 益 天津 S 精 益 天津 S 精 益 天津 S 盛 锡 北京 ………… 2017/3/14

112 索引连接(INDEX-JOIN) 对表2按连接字段建立索引
对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组 2017/3/14

113 非等值连接 (Non-EquiJoin) 非等值连接 比较运算符：>、<、>=、<=、!=（<>）
连接运算符不为 = 的连接操作 形式： [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 比较运算符：>、<、>=、<=、!=（<>） [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> 2017/3/14

114 例32. 假设存在一张薪级表，查询每个职工的薪水及所属薪级情况。 --创建薪水级别表 CREATE TABLE Salgrade
( Grade decimal(1), Lowsalary decimal(6,2), Highsalary decimal(6,2) ); --非等值连接求解 SELECT Eno, Basepay, Grade FROM Salary, Salgrade WHERE Basepay BETWEEN Lowsalary AND Highsalaty; 2017/3/14

115 外连接 外连接中，可以只限制一个表，而对另一个表不加限制（即所有的行都出现在结果集中）。 外连接分为左外连接、右外连接和全外连接。
2017/3/14

116 左外连接语法： 右外连接语法： 全外连接语法： SELECT select_list
FROM table1 LEFT [OUTER] JOIN table2 ON table1.column1=table2.column2 右外连接语法： FROM table1 RIGHT [OUTER] JOIN table2 全外连接语法： FROM table1 FULL [OUTER] JOIN table2 2017/3/14

117 例33. 如果该部门没有职工，即没有任何一个职工元组与其匹配，则相应部门的职工补以空值。
SELECT d.Dno, d.Dname , e.Eno, e.Ename, e.Title FROM Department d LEFT JOIN Employee e ON d.Dno=e.Dno; --等价于 FROM Employee e, Department d WHERE e.Dno=*d.Dno; 现有高版本的SQL Server中外连接的用法不支持“=*”或“*=”。 *有些DBMS的外连接符可能为符号“＋”（例如Oracle）或者其他字符。 左连接：返回左侧表中不匹配元组，以及满足连接条件的元组 右连接：返回右侧表中不匹配元组 全外连接 2017/3/14

118 SELECT d.Dname, COUNT(Eno) FROM Department d JOIN Employee e
例34. 查询有员工存在的部门的员工人数。 SELECT d.Dname, COUNT(Eno) FROM Department d JOIN Employee e ON d.Dno=e.Dno GROUP BY d.Dname 例35. 查询所有部门的员工人数。 /*包含无员工存在的部门相关数据*/ FROM Department d LEFT JOIN Employee e 等价于 COUNT(*) 不等价于 COUNT(*) Join为内连接 Eno有空值重复 Count(*)时，左连接时，有Dname但后面的Eno为空也会被计数 2017/3/14

119 自身连接(Self-Join) 连接操作是在同一张表内进行自身连接，即将同一个表的不同行连接起来。 可看作一张表的两个副本之间进行的连接。
必须为表指定两个别名，使之在逻辑上成为两张表。 由于所有属性名都是同名属性，因此必须使用别名前缀 例36.查询Employee表中的同姓的职工。 SELECT e1.Ename,e2.Ename FROM Employee e1 JOIN Employee e2 ON left(e1.Ename,1)=left(e2.Ename,1) WHERE e1.Eno<e2.Eno Left(e1.Ename,1)Ename的左边第一个字符 2017/3/14

120 集合运算 SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。 集合操作视具体的RDBMS而定。
并操作（UNION） 交操作（INTERSECT） 差操作（EXCEPT或MINUS） 2017/3/14

121 合并结果集 使用UNION语句可以把两个或两个以上的查询产生的结果集合并为一个结果集。 语法：
SELECT select_list UNION [ALL] select_statement （查询语句） UNION中的每一个查询所涉及的列必须具有相同的列数，相同的数据类型，并以相同的顺序出现。 最后结果集中的列名来自第一个SELECT语句。 若UNION中包含ORDER BY子句，则将对最后的结果集排序。 在合并结果集时，默认将从最后的结果集中删除重复的行，除非使用ALL关键字。 2017/3/14

122 例37. 集合操作：并集UNION、交集INTERSECT、差集EXCEPT SELECT * FROM Employee
WHERE Title='工程师' UNION --并集 --INTERSECT --交集,SQL SERVER高版本中支持 --EXCEPT --差集,SQL SERVER高版本中支持 WHERE sex=‘男’; 上面一个select语句为一个集合，下面一个为一个集合 2017/3/14

123 子查询 嵌套子查询 相关子查询 2017/3/14

124 嵌套子查询 执行不依赖于外部查询。 子查询可以多次嵌套。 分为两种：子查询返回单个值和子查询返回一个值列表。
返回单个值，该值被外部查询的比较操作使用，该值可以是子查询中使用集合函数得到的值。 返回一个值列表，该列表被外部查询的IN、 NOT IN、ANY或ALL比较操作使用。 IN表示属于，NOT IN表示不属于。 ANY和ALL用于一个值与另一个组值的比较。 执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用 2017/3/14

125 使用IN的嵌套查询 例38. 查询所有参加了200901项目的职工号、姓名、职称。 SELECT Eno, Ename, Title
例38. 查询所有参加了200901项目的职工号、姓名、职称。 SELECT Eno, Ename, Title FROM Employee WHERE Eno IN ( SELECT Eno FROM Item_emp WHERE Ino= '200901' ); 例39. 查询职工1010所没有参加的所有项目情况。 SELECT * FROM Item WHERE Ino NOT IN ( SELECT Ino WHERE Eno='1010' ); 2017/3/14

126 如果能确定子查询的返回结果，总是单个值，则可以用“=”代替“IN”。
例40. 查询参加了项目“相变光盘性能研究”的职工情况。 SELECT * FROM Employee WHERE Eno IN ( SELECT Eno FROM Item_emp WHERE Ino = ( SELECT Ino FROM Item WHERE Iname='相变光盘性能研究' ) ); 2017/3/14

127 使用比较运算符的嵌套查询 比较运算符<、=、>、<=、>=、<>等，均可以作为嵌套查询的连接词。
例41. 查询所有大于平均年龄的职工的姓名和年龄。 SELECT Ename,Age FROM Employee WHERE Age > ( SELECT AVG(Age) FROM Employee ); WHERE子句中不得直接出现聚合函数。 错误用法： WHERE Age >AVG(Age); 2017/3/14

128 比较运算符还可以与ANY或ALL一起使用，ANY只要与子查询中一个值符合即可，ALL要与子查询中所有值相符合。
例42. 查询非02部门的年龄大于2号部门任意职工年龄的职工信息，并按年龄的升序排序 SELECT * FROM Employee WHERE Age > ANY ( SELECT Age WHERE Dno='02' ) AND Dno<>'02' ORDER BY Age; 等价于： Age>(SELECT MIN(Age) FROM Employee WHERE Dno='02' ) 2017/3/14

129 带有ANY或ALL谓词的子查询 ANY和ALL谓词有时可以用聚合函数实现 ANY与ALL与集函数的对应关系 = <>或!=
= <>或!= < <= > >= ANY IN -- <MAX <=MAX >MIN >= MIN ALL NOT IN <MIN <= MIN >MAX >= MAX 2017/3/14

130 使用BETWEEN的嵌套查询 [NOT] BETWEEN…AND也可以作为嵌套查询的连接词。
例43. 查询基本工资介于职工号为“1010”的工资和800元之间的职工号。 SELECT FIRST.Eno FROM Salary FIRST WHERE Basepay BETWEEN ( SELECT Basepay FROM Salary SECOND WHERE SECOND.Eno= '1010' ) AND 800; 2017/3/14

131 相关子查询 执行依赖于外部查询，多数情况下是在子查询的WHERE子句中引用了外部查询的表。
可以使用EXISTS关键字来判断查询结果中是否存在数据，EXISTS在一个子查询至少返回一行时成立。 2017/3/14

132 例44. 查询参加了项目的职工号、姓名。 SELECT Eno, Ename FROM Employee WHERE EXISTS
例44. 查询参加了项目的职工号、姓名。 SELECT Eno, Ename FROM Employee WHERE EXISTS ( SELECT * FROM Item_Emp WHERE Eno=Employee.Eno); Item_Emp表中项目号Ino与Eno对应，职工参加的项目号，在Item_Emp表中出现的Eno是有参加项目的 2017/3/14

133 相关子查询过程 查询参加了项目的 职工号、姓名。 SELECT Eno, Ename FROM Employee e
WHERE EXISTS ( SELECT * FROM Item_Emp WHERE Eno=e.Eno ); 找出 e 表第一条记录 将e表该记录的 eno 传递给内查询 内查询是否为空 NO 取e表该记录的eno、ename 放到结果集中 找出 e 表下一条记录 YES 取出放结果集 True 非空 2017/3/14

134 例45. 查询参加了全部项目的职工号和职工姓名。 /*在Item_Emp表中不存在职工没有参加的项目的相关记录*/
SELECT Eno,Ename FROM Employee WHERE NOT EXISTS ( SELECT * FROM Item FROM Item_Emp WHERE Item_Emp.Eno=Employee.Eno AND Item_Emp.Ino=Item.Ino )); 2017/3/14

135 例46. 查询参加了全部2009项目的职工号和职工姓名。 SELECT Eno,Ename FROM Employee
WHERE NOT EXISTS ( SELECT * FROM Item WHERE Item.Ino like '2009%' and NOT EXISTS FROM Item_Emp WHERE Item_Emp.Eno=Employee.Eno AND Item_Emp.Ino=Item.Ino)); 2009项目有多个 2017/3/14

136 在查询的基础上创建新表 使用SELECT INTO 语句可以在查询的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。 语法： SELECT select_list INTO new_table FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [HAVING search_condition ] [ORDER BY order_expression [ ASC | DESC ] ] 2017/3/14

137 例47. 为所有具有高工职称的职工新建一张职工表。 SELECT Eno, Ename, Dno INTO senior_engineer
FROM Employee WHERE Title='高工'; 2017/3/14

138 回顾 一、表中数据的增删改操作 INSERT,UPDATE,DELETE 二、表中数据查询 SELECT—— 单表查询完整的语法：
SELECT [ALL/DISTINCT] select-list FROM table_name | view_name [WHERE <条件>] [GROUP BY <列名清单>[HAVING <条件>]] [ORDER BY <列名清单>]； 三、WHERE子句中使用的关键字： IN、BETWEEN、LIKE 2017/3/14

139 回顾 四、SELECT INTO的用法： 五、多表连接查询—连接条件 交叉连接：Cross Join（不用） 等值/非等值连接
FROM <旧表名> [ WHERE……] [ ORDER BY……] 五、多表连接查询—连接条件 交叉连接：Cross Join（不用） 等值/非等值连接 内连接（自然连接）：Inner Join （最常用） 外连接： 左外连接：Left Outer Join 右外连接：Right Outer Join 全外连接：Full Outer Join 2017/3/14

140 回顾 六、自身连接 七、嵌套子查询 八、相关子查询 连接同一张表的两次(或更多)查询 使用IN 使用Between 使用比较运算符
使用Any或All 八、相关子查询 使用Exists的嵌套查询 2017/3/14

141 SQL语言初步（五） 表内数据的增删改 （带子查询部分） （3.5节） 2017/3/14

142 Insert 与子查询结合 Update 与子查询结合 Set中使用子查询 Delete与子查询结合 2017/3/14

143 INSERT与子查询的结合 格式： INSERT INTO<表名>[<属性名清单>] （子查询）；
例1. 假设存在一张表Engineer（字段延用Employee表中相关字段）：往该表里插入所有具有工程师以上职称的职工相关信息。 INSERT INTO Engineer SELECT Eno,Ename,Title,Dno FROM Employee WHERE Title in ('工程师','高工'); 2017/3/14

144 UPDATE与子查询的结合 格式： UPDATE <表名>
SET <列名>=<表达式>[，<列名>=<表达式>]n [WHERE<带有子查询的条件表达式>] 本语句执行时，将修改使<带有子查询的条件表达式>为真的所有元组。 例2. 使参加“200802”项目的职工，工资表中津贴的值都增加200。 UPDATE Salary SET Rest=Rest+200 WHERE Eno IN ( SELECT Eno FROM Item_Emp WHERE Ino= '200802'); 2017/3/14

145 SET子句中使用子查询 例3. 将所有工程师的工资改为职工的平均工资。 UPDATE Salary SET Basepay=
( SELECT AVG(Basepay) FROM Salary ) WHERE Eno IN ( SELECT Eno FROM Employee WHERE Title='工程师' ) 2017/3/14

146 DELETE与子查询的结合 格式： DELETE FROM <表名> [WHERE <带有子查询的条件表达式>]
本语句将删除所有使<带有子查询的条件表达式>为真的所有元组。 例4. 从Item_Emp表中，删除“丁为国”的所有元组。 DELETE FROM Item_Emp WHERE Eno= ( SELECT Eno FROM Employee WHERE Ename='丁为国' ); 2017/3/14

147 SQL语言初步（三） 视图 （3.6节） 2017/3/14

148 SQL数据库体系结构 用户1 用户2 用户4 视图V1 用户3 基本表B1 基本表B2 基本表B4 基本表B3 存储文件S1 存储文件S2
VIEW Table Stored file 图3 SQL数据库体系结构 2017/3/14

149 视图 基于某个查询结果的虚表。 定义视图的查询语句可以从一个表、多个表或者其他视图中引用数据。
视图所对应的数据并不实际存储在数据库中，数据库中只存储视图的定义。 可对已定义的视图进行查询操作，但对视图的更新操作（增、删、改）有一定限制。 2017/3/14

150 视图的作用 视图是用户一级的数据观点，由于有了视图，使数据库系统具有下列优点： 视图提供了逻辑数据独立性。
通过视图来集中、简化、自定义自己对数据库的感知。 数据的安全保护功能：可以作为一种安全机制，使用户只能通过视图访问数据，而不给用户访问基表的权限。 视图也就是外模式，查询等是在模式的基础上操作的 2017/3/14

151 为什么创建视图 通过视图可以将两个或多个表只查询的特定结果拿出来 视图一般用于给用户提供一个统一的接口而屏蔽掉无用或需要保密的信息
有些敏感的信息甚至只能给具有合适权限的人员，让用户只能查看他应该看的信息 基于视图开发，数据量少，速率快 2017/3/14

152 创建视图 在数据库中创建一个或多个表后，可使用CREATE VIEW语句或企业管理器创建视图。
可以代替表完成从表中查询、插入、更新和删除数据的操作。 创建视图语句执行后，此视图的定义即进入数据字典，对语句中的<子查询>并未执行，也即视图并未真正生成。只是在对视图查询时，才按视图的定义从基本表中将数据查出。 所以说，视图是虚表。 对视图不能建立索引 2017/3/14

153 创建视图 语句格式 CREATE VIEW <视图名> [<列名清单>] [WITH ENCRYPTION]
AS <子查询> [WITH CHECK OPTION] 说明 <视图名>给出所定义的视图的名称。 <列名清单>，若有则此清单给出了此视图的全部属性的属性名；否则，此视图的所有属性名即为子查询中SELECT语句中的全部目标列。 <子查询>为任一合法SELECT语句（但一般不含有ORDER BY，UNION等语法成分）。 [ WITH ENCRYPTION]表示视图定义文本被加密。 有[WITH CHECK OPTION]时，则今后对此视图进行INSERT、UPDATE和DELETE操作时，系统自动检查是否符合原定义视图子查询中的<条件表达式>。 with encryption的不能察看和修改原脚本 2017/3/14

154 例1 建立“02”号部门的职工视图，即从职工表中取出部门为“02”的元组组成一个视图。 CREATE VIEW V_Emp_02 AS
例1 建立“02”号部门的职工视图，即从职工表中取出部门为“02”的元组组成一个视图。 CREATE VIEW V_Emp_02 AS SELECT * FROM Employee WHERE Dno= '02' 以上视图只从单个基本表导出，且保留了原来的码，该视图称为行列子集视图，有些书上称为“简单视图” 。 2017/3/14

155 例2 建立“02”号部门的职工视图，并要求在进行修改和插入操作时仍需保证该视图只有“02”号部门的职工。
例2 建立“02”号部门的职工视图，并要求在进行修改和插入操作时仍需保证该视图只有“02”号部门的职工。 CREATE VIEW V_Emp_02_2 AS SELECT * FROM Employee WHERE Dno= '02' WITH CHECK OPTION 由于定义视图过程中带有WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，RDBMS会自动加上Dno= '02'的条件，如果条件判断成立，则进行插入、修改和删除操作；反之，则不予操作并返回错误。 2017/3/14

156 视图可以从一个基本表或视图导出，也可以是从多个基本表或多个视图导出。
定义视图时，若设置了一些派生属性（这些属性是原基本表没有的，其值是用一个表达式对原基本表的运算而得到的）则此视图称为带有表达式的视图，这些派生属性也称为虚拟列。 注意：由于计算属性没有属性名，此时必须为之设置别名，或在视图名后指明视图的属性名。 例3 在工资表上定义某个职工的实发工资V_salary。 CREATE VIEW V_salary(Eno,Ename,Act_salary) AS SELECT Employee.Eno,Ename, Basepay+Service+Price+Rest-Insure-Fund FROM Employee,Salary WHERE Employee.Eno=Salary.Eno 基于视图还可建立视图 2017/3/14

157 视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上（称为基于视图的视图），或建立在基本表与视图上。
例4 建立职工实发工资大于2000的职工实发工资视图。 CREATE VIEW V_salary2 AS SELECT * FROM V_salary WHERE Act_salary>2000 2017/3/14

158 定义视图时，若使用了集函数和GROUP BY子句的查询，则此视图称为分组视图。
例5 建立含部门及部门职工平均实发工资的视图。 CREATE VIEW V_Dept_Salary(Dno,Avg_Act_salary) AS SELECT Dno,AVG(Act_salary) FROM Employee,V_salary WHERE Employee.Eno=V_salary.Eno GROUP BY Dno 2017/3/14

159 *查看和修改视图基本信息 SQL SERVER 允许用户获得试图的一些有关信息，如：视图的名称、视图的所有者、创建时间等。视图的信息存放在以下几个SQL SERVER系统表中： Sysobjects:存放视图的名称等基本信息 Syscolumns:存放视图中定义的列 Sysdepends: 存放视图的依赖关系 Syscomments:存放定义视图的文本 2017/3/14

160 *管理视图 可以使用系统存储过程sp_help来显示视图的名称、拥有者及创建时间等信息。
可以使用系统存储过程sp_helptext来显示视图的定义语句。 例6 查看视图的文本信息 EXEC sp_helptext V_Dept_Salary 可以使用系统存储过程sp_depends查看视图与其他数据库对象之间的依赖关系。 例7 查看视图与其他数据库对象的依赖关系 EXEC sp_depends V_Dept_Salary 2017/3/14

161 修改视图 语句格式 ALTER VIEW <视图名> [<列名清单>] AS <子查询>
例8. 修改 V_Emp_02 的定义 ALTER VIEW V_Emp_02 AS SELECT * FROM Employee WHERE Dno= '02' AND Age<=40; 2017/3/14

162 删除视图 语句格式 DROP VIEW <视图名> 此语句将把指定视图的定义从数据字典中删除。
视图被删除后，该视图基表中存储的数据并不会受到影响。但任何建立在该视图上的其他数据库对象的查询将会发生错误。 删除视图必须用DROP VIEW语句。 例9. 从数据字典中删除 V_Emp_02 视图的定义。 DROP VIEW V_Emp_02 CREATE VIEW V_Emp_02 AS SELECT * FROM Employee WHERE Dno= '02' 2017/3/14

163 通过视图修改基表中数据 对视图进行的查询，对视图中的记录进行的插入、修改及删除都是作用在基表上的。 一个视图可以执行更新操作，需要以下条件：
定义视图的查询语句中没有集合函数，也没有top、distinct、group by和union子句。即查询语句的结果中的列应没有对基表中的列作修改。 定义视图的查询语句的FROM子句中至少要引用一个表。 还须注意不能在一个修改语句中对多个基表进行修改，即在UPDATE或INSERT语句中所用的列名必须是使用同一个表。 通过视图对基表进行删除时，要求该视图的基表只有一个，即视图定义的FROM子句只引用了一个表。 2017/3/14

164 例10. 对视图 V_Emp_02 的增删改将转化成对基本表Employee的增删改 INSERT INTO V_Emp_02
VALUES('1036','陈向东','男',25,1,'工程师','02'); UPDATE V_Emp_02 SET Ename='程向东' WHERE Eno='1036'; DELETE FROM V_Emp_02 WHERE Eno= '1036'; INSERT INTO Employee VALUES('1036','陈向东','男',25,1,'工程师','02'); UPDATE Employee SET Ename='程向东' WHERE Eno = '1036' ; 对视图中的记录进行的插入、修改及删除都是作用在基表上的 CREATE VIEW V_Emp_02 AS SELECT * FROM Employee WHERE Dno= '02' DELETE FROM Employee WHERE Eno= '1036'; 2017/3/14

165 在定义视图时，若加上子句WITH CHECK OPTION，则在对视图更新时，系统将自动检查原定义时的条件是否满足。若不满足，则拒绝执行。
INSERT INTO V_Emp_02 VALUES('1038','陈华','男',25,1,'助工','03'); 其输出结果为： （所影响的行数为 1 行） 例12. 带WITH CHECK OPTION子句的视图数据插入。 INTO V_Emp_02_2 VALUES('1039','陈华东','男',25,1,'助工','03'); 视图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。语句已终止。 CREATE VIEW V_Emp_02 AS SELECT * FROM Employee WHERE Dno= '02' CREATE VIEW V_Emp_02_2 AS WITH CHECK OPTION 2017/3/14

166 不是所有的视图都是可更新的，因为有些视图的更新不能有意义的转化成相应基本表的更新。
例13. 从职工表导出求每个部门的平均年龄的视图。 CREATE VIEW V_avg_age AS SELECT Dno,Avg(Age) AS Avg_age FROM Employee GROUP BY Dno 本语句对此视图的任何更新都无法转换成对职工表的更新。 2017/3/14

167 INSERT，UPDATE，DELETE
SQL语句的9个核心动词 SQL功能 操作符 数据查询 SELECT 数据定义 CREATE，ALTER，DROP 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE 2017/3/14