PowerPoint 演示文稿

Transcription

1 关系数据库标准语言 SQL

2 一 SQL 概述 认识 SQL SQL(Structured Query Language), 即结构化查询语言, 是关系数据库的标准语言, 是一个通用的 功能极强的关系数据库语言 ; 其功能并不仅仅是查询, 主要用来对存放在计算机中的数据库进行组织 管理和检索

3 一 SQL 概述 认识 SQL 974 年被提出, 并在 IBM 公司研制的关系数据库管理系统 System R 上实现 ; 986 年, 被批准作为关系数据库语言的美国标准 ; 987 年, 国际标准化组织 ISO 也通过了这一标准

4 一 SQL 概述 认识 SQL 当前, 几乎所有关系数据库管理软件都支持 SQL 许多软件厂商对 SQL 基本命令集进行了不同程度的扩充和修改, 所以, 实际上不同数据库管理系统之间的 SQL 语言不能完全通用 例如, 微软公司的 SQL Server 数据库系统支持的是 Transact- SQL( 简称 T-SQL), 而甲骨文公司的 Oracle 数据库所使用的 SQL 语言则是 PL-SQL

5 一 SQL 概述 SQL 语言特点 综合统一 集 DDL DML DCL 功能于一体, 语言风格统一 ; 关系模型中实体和实体间联系都用关系表示, 数据结构单一, 数据操作符统一 高度非过程化 只要提出 做什么, 无须指明 怎么做 ; 存取路径的选择和 SQL 的操作过程由系统自动完成

6 一 SQL 概述 SQL 语言特点 面向集合的操作方式 操作对象和结果都是集合 以同一种语法结构提供多种使用方式 可以独立使用 SQL 命令对数据库进行操作 ; 可以嵌入到高级语言程序中, 供程序员设计程序用 语言简洁, 易学易用 ; 完成核心功能 9 个动词 ; 接近英语口语

7 一 SQL 概述 MS SQL Server 体系结构 Microsoft SQL Server 的体系结构中包含 4 个主要部分组成 : 协议层 关系引擎 ( 查询处理器 ) 存储引擎 SQLOS

8 一 SQL 概述 MS SQL Server 体系结构

9 一 SQL 概述 MS SQL Server 数据库 数据库是存储和管理数据的对象 ; 表 视图 索引 存储过程等 从逻辑上看, 数据库呈现为各种数据库对象, 这些数据对象是用于编写数据库应用系统和管理数据库的单位 从物理上看, MS SQL Server 数据库主要包括文件及文件组, 还有页和盘区等, 它们描述了 SQL Server 如何为数据库分配空间

10 一 SQL 概述 MS SQL Server 数据库 SQL Server 中有两个主要的数据存储单位 : 页和区 ; 页 : 用于数据存储的最基本单位, 每个页的大小是 8KB 数据库表中的每行数据都不能跨页存储, 即表中的每一行数据字节数不能超过 892 区 : 用于管理空间的基本单位 每 8 个连接的页组成一个区, 大小为 64KB 区用于控制表和索引的存储

11 一 SQL 概述 MS SQL Server 数据库 SQL Server 中的每个数据库都必须包含下面两类文件 数据文件 : 用于存放所有数据和数据库中建立的所有逻辑对象 又分为主数据文件和辅助数据文件 日志文件 : 记录了用户对数据库进行的所有操作

12 一 SQL 概述 MS SQL Server 数据库 () 主数据文件 ( 主文件 ) 主文件是数据库的关键文件, 包含了数据库的启动信息, 并且存储数据 每个数据库必须有且仅能有一个主文件 ; 其默认扩展名为.mdf;

13 一 SQL 概述 MS SQL Server 数据库 (2) 辅助数据文件 ( 辅文件 ) 辅文件用于存储未包括在主文件内的其他数据 辅助文件的默认扩展名为.ndf 零个 一个或多个

14 一 SQL 概述 MS SQL Server 数据库 (3) 日志文件 (Transaction Log File) 日志文件用于保存恢复数据库所需的事务日志信息 每个数据库至少有一个日志文件, 也可以有多个 日志文件的扩展名为.ldf

15 一 SQL 概述 MS SQL Server 数据库 (4) 数据库文件组将多个文件归纳为同一组, 并赋予此组一个名称, 这就是文件组 主文件组 : 包含主要数据文件和任何没有明确指派给其他文件组的文件 用户定义文件组 : 使用 FileGroup 关键字指定的文件组

16 一 SQL 概述 MS SQL Server 数据库对象命名 完全限定名 : 包括服务器名 数据库名 数据库架构名和对象名 其格式为 : Server. database. schema. object 部分限定名 : 只包含对象完全限定名中的一部分的对象名称 对象全名中的前 3 部分可被省略 当省略中间部分时, 圆点符. 不可省略 未指出的部分使用默认值 服务器 - 默认为本地服务器 数据库 - 默认为当前数据库 数据库架构名 - 默认为 dbo

17 一 SQL 概述 MS SQL Server 系统数据库 SQL Server 数据库安装后会包含 4 个默认的系统数据库 master: 记录整个服务器安装信息和后续创建的所有数据库的信息 ; model: 模板数据库 ; msdb: 由 SQL Server 提供的一些功能服务组件所使用 ; tempdb:sql Server 的工作空间 存放临时表 中间数据等 mssqlsystemresource: 系统资源数据库 存放系统资源相关的信息

18 二 数据定义 实例 数据库 数据库 2 数据库 3 数据库 n 模式 模式 2 模式 n 表 表 2 视图索引

19 二 数据定义 SQL 数据定义语句 操作对象 操作方式 创建删除修改 模式 CREATE SCHEMA DROP SCHEMA 表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX ALTER INDEX

20 二 数据定义 模式 模式定义语句 : Create schema < 模式名 > authorization < 用户名 > [< 表定义子句 > < 视图定义子句 > < 授权定义子句 >]; 没有指定模式名, 则模式名隐含为用户名 ; 创建模式的用户必须拥有 DBA 权限, 或拥有 create schema 权限 ; 定义模式其实是定义一个命名空间, 在此空间中可以进一步创建基本表 视图, 定义授权

21 二 数据定义 模式 例 : 为用户 wang 创建一个模式 test create schema test authorization wang; 例 2: 为用户 zhao 创建是模式 test2, 并在其中定义表 tab create schema test2 authorization zhao create table tab( );

22 二 数据定义 模式 删除模式语句格式 : Drop schema < 模式名 > <cascade restrict>; cascade restrict 两者必选其一 ; cascade( 级联 ): 删除模式的同时删除其中所有的数据库对象 ; restrict( 限制 ): 模式中定义了数据库对象, 则拒绝执行该删除语句 ;

23 二 数据定义 模式 试比较 : Drop schema test2 cascade; Drop schema test2 restrict; 级联删除 拒绝执行

24 二 数据定义 数据库 创建 SQL Server 数据库可以在 SQL Server Management Stuio 中以图形界面的方式交互完成, 或者在查询界面下通过 SQL 语句完成 界面方式管理数据库 命令方式下管理数据库

25 二 数据定义 数据库 创建数据库语法格式 数据库名 CREATE DATABASE database_name [ ON 指定数据库的数据文件和文件组 指定主文件 [ PRIMARY ] [ <filespec> [,...n ] [, <filegroup> [,...n ] ] [ LOG ON { <filespec> [,...n ] } ] 指定数据库文件的属性 定义文件组的属性 ] 指定事务日志文件的属性 [ COLLATE collation_name ]

26 二 数据定义 数据库

27 二 数据定义 数据库 <filegroup> ::= 文件组的名称 { FILEGROUP filegroup_name [ CONTAINS FILESTRE [ DEFAULT ] <filespec> [,...n ] } 指定属于该文件组的文件, 文件组中各文件的描述和数据文件描述相同

28 二 数据定义 数据库 例 : 请在本地磁盘 D 的 data 文件夹中创建一个学生 - 课程数据库 ( 名称为 test), 该数据库只包含一个数据文件和日志文件, 文件名称分别为 test_data 和 test_log, 初始大小都为 3MB, 增长方式分别为 0% 和 MB, 数据文件最大为 500MB, 日志文件大小不受限制

29 二 数据定义 数据库 CREATE DATABASE test ON ( NAME = test_data, FILENAME = D:\data\test_data.mdf', SIZE = 3MB, MAXSIZE = 500MB, FILEGROWTH = 0%) LOG ON ( NAME = test_log, FILENAME = D:\data\test_log.ldf, SIZE = 3MB, MAXSIZE = unlimited,filegrowth = MB )

30 二 数据定义 数据库 例 2: 在 D 盘 data 文件夹中创建一个名为 test2 的数据库, 它有两个数据文件, 其中 : 主数据文件 test2_data, 初始大小为 20MB, 最大大小不限, 按 0% 增长 ; 个辅数据文件 test2_data2, 初始大小为 20MB, 最大大小不限, 按 0% 增长 ; 个日志文件 test2_log, 初始大小为 50MB, 最大大小为 00MB, 按 0MB 增长

31 二 数据定义 数据库 create database test2 on primary ( name='test2_data', filename='d:\data\test2_data.mdf', size=20mb, maxsize=unlimited, filegrowth=0% ), ( name='test2_data2', filename='d:\data\test2_data.ndf', size=20mb, maxsize=unlimited, filegrowth=0% ) log on ( size=20mb, maxsize=unlimited, filegrowth=0% ) log on ( name='test2_log', filename='d:\data\test2_log.ldf', size=50mb, maxsize=00mb, filegrowth=0mb );

32 二 数据定义 数据库 例 3: 在 D 盘 data 文件夹中创建一个具有 2 个文件组的数据库 TEST3 要求: 主文件组包括主文件 TEST3_dat, 文件初始大小为 20MB, 最大为 60MB, 按 5MB 增长 ; 个用户定义的文件组 TEST3Group, 包括文件 TEST3_dat2, 初始大小为 0MB, 最大为 30MB, 按 0% 增长 个日志文件 ( 省略不写 )

33 二 数据定义 数据库 create database test3 on primary ( name='test3_dat', filename='d:\data\test3_dat.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ), filegroup test3group ( name='test3_dat2', filename='d:\data\test3_dat2.ndf', size=0mb, maxsize=30mb, filegrowth=0% );

34 二 数据定义 数据库 Alter database database_name 修改数据库语法格式 {add file <filespec>[, n] [to filegroup filegroupname] 在文件组中增加数据文件 add log file <filespec>[, n] 增加日志文件 remove file logical_file_name [with delete] 删除数据文件 modify file <filespec> 更改文件属性 modify name=new_databasename 数据库更名 add filegroup filegroup_name 增加文件组 remove filegroup filegroup_name 删除文件组 modify filegroup filegroup_name 更改文件组属性 }

35 二 数据定义 数据库 例 : 修改数据库 test 现有数据文件 test_data 的属性, 将主数 据文件的最大大小改为 00MB, 增长方式改为按每次 5MB 增长 alter database test modify file ( name=test_data, maxsize=00mb, filegrowth=5mb )

36 二 数据定义 数据库 例 2: 先为数据库 test 增加数据文件 testbak 然后删除该数据 文件 alter database test add file ( name='testbak', filename='d:\data\testbak.ndf', size=0mb, maxsize=50mb, filegrowth=5% ) alter database test remove file testbak

37 二 数据定义 数据库 例 3: 为数据库 test 添加文件组 FGroup, 并为此文件组添加两 个大小均为 0MB 的数据文件

38 二 数据定义 数据库 alter database test add filegroup fgroup go alter database test add file ( name='test_data4', filename='c:\program Files\Microsoft SQL Server\MSSQL0.SQL2008\MSSQL\DATA\test_data4.ndf', size=0mb ), ( name='test_data5', filename='c:\program Files\Microsoft SQL Server\MSSQL0.SQL2008\MSSQL\DATA\test_data5.ndf', size=0mb ) to filegroup fgroup

39 二 数据定义 数据库 例 4: 将上题中添加的文件组 FGroup 删除 ( 被删除的文件组中 的数据文件必须先删除, 且不能删除主文件组 ) alter database test remove file test_data4 alter database test remove file test_data5 alter database test remove filegroup fgroup

40 二 数据定义 数据库 例 5: 为数据库 test 添加一个日志文件 例 6: 将上题中添加的日志文件删除 ( 不能删除主日志文件 ) alter database test add log file ( name='test_log2', filename='d:\data\test_log2.ldf', size=5mb, maxsize=0mb, filegrowth=mb ) alter database test remove file test_log2

41 二 数据定义 数据库 删除数据库语法格式 : DROP DATABASE, 数据库名组 例 : 将数据库 student 删除, 可使用下述语句 DROP DATABASE student 注 : 使用 DROP DATABASE 语句不会出现确认信息, 要小心使用, 并且不能删除系统数据库, 否则将导致服务器无法使用

42 二 数据定义 数据表 建立数据库最重要的一步就是创建其中的数据表, 即决定 : 数据库包括哪些表 每个表中包含哪些字段 每个字段的数据类型等创建表的实质就是定义表结构及约束等属性, 在创建数据表之前, 先要设计表, 即确定表名 所包含的各字段名 字段的数据类型 长度 是否为空值等等, 这些属性构成表结构

43 二 数据定义 数据表

44 二 数据定义 数据表 整数型 长度 : 存储数据所占用的字节数 ; 精度 : 最多可以存储的十进制数字的总位数

45 二 数据定义 数据表 精确数值型 : 由整数部分和小数部分构成 包括 decimal 和 numeric 两类 ; 数据的存储长度随精度变化而变化, 最少为 5 个字节, 最多为 7 个字节 ; 声明精确数值型数据的格式 :numeric decimal(p[,s]) 其中 p 为精度,s 为小数位数,s 的默认值为 0 声明精确数值型数据时, 其小数位数必须小于精度

46 二 数据定义 数据表 浮点型 ( 近似数值型 ): 不提供精确表示数据的精度, 可用于处理取值范 围非常大且对精确度要求不太高的数值量, 如一些统计量 有两种浮点型 :float[(n)] 和 real 两者通常使用科学计数法表示数据

47 二 数据定义 数据表 货币型当向表中插入 money 或 smallmoney 类型的值时, 必须在数据前面加上货币表示符号 ($), 并且数据中间不能有逗号 (,); 若货币值为负数, 则需要在符号 $ 的后面加上负号 (-) 例如 $ $

48 二 数据定义 数据表 字符型 char[(n)]: 定长字符数据类型 其中 n 定义字符型数据的长度,n 在 到 8000 之间, 缺省为 ; 当表中的列定义为 char(n) 类型时, 若实际要存储的串长度不足 n 时, 则在串的尾部添加空格以达到长度 n, 所以 char(n) 的长度为 n

49 二 数据定义 数据表 字符型 varchar[(n)]: 变长字符数据类型 其中 n 的规定与定长字符型 char 中 n 完全相同 ; 但这里 n 表示的是字符串可达到的最大长度 varchar(n) 的长度为输入的字符串的实际字符个数, 而不一定是 n 当列中的字符数据值长度接近一致时, 如姓名, 可使用 char; 而当列中的数据值长度显著不同时, 使用 varchar 较为恰当, 可以节省存储空间

50 二 数据定义 数据表 文本型 用于存储大量的字符数据, 如较长的备注 日志信息等 text: 数据的存储长度为实际字符数字节 ; ntext: 表示 Unicode 字符, 数据的存储长度是实际字符数的两倍

51 二 数据定义 数据表 日期时间型 用于存储日期和时间信息 date: 只存储日期数据, 不存储时间数据, 存储长度为 3 字节 time: 只存储时间数据 datetime: 存储日期和时间信息, 存储大小随着微秒数的位数 ( 精度 ) 而改变

52 二 数据定义 数据表

53 二 数据定义 数据表 列的定义

54 二 数据定义 数据表 以学生管理管理系统数据库为例, 创建学生表 (XSB) 课程表 (KCB) 和成绩表 (CJB) 学生表 (XSB) 列名 数据类型 长度 是否为空 默认值 说明 Stu_ID char 6 否 无 学号, 主键 Sname char 8 否 无 姓名 Ssex bit 默认值 是 性别 Sdate date 默认值 是 无 出生日期 Major char 2 是 无 专业 Tcredit int 默认值 是 0 总学分 Remark varchar 500 是 无 备注

55 二 数据定义 数据表

56 二 数据定义 数据表 课程表 (KCB) 列名 数据类型 长度 是否为空 默认值 说明 C_ID char 3 否 无 课程号, 主键 Cname char 6 否 无 课程名 Term tinyint 是 开课学期 Chour tinyint 是 0 学时 Credit tinyint 否 0 学分 成绩表 (CJB) 列名 数据类型 长度 是否为空 默认值 说明 Stu_ID char 6 否 无 学号, 主键 C_ID char 3 否 无 课程号, 主键 Grade int 默认值 是 0 成绩

57 二 数据定义 数据表

58 二 数据定义 数据表

59 二 数据定义 数据表 例 : 在 XSB 中增加一个新列 奖学金等级 ; alter table PXSCJ..XSB add 奖学金等级 tinyint null; 例 2: 在上题的基础上, 删除 奖学金等级 列 ; alter table PXSCJ..XSB drop column 奖学金等级 ;

60 二 数据定义 数据表 例 3: 修改 XSB 中已有列的属性 : 将名为 姓名 的列长度由原来的 8 改为 0,; 将名为 出生时间 的列的数据类型由原来的 date 改为 samlldatetime alter table PXSCJ..XSB alter column Sname char(0); alter table PXSCJ..XSB alter column Sdate smalldatetime;

61 二 数据定义 数据表 删除数据表语法格式

62 三 管理数据 插入表数据

63 三 管理数据 插入表数据 例 : 向 XSB 中插入如下一行数据 :080, 王林,, , 计算机,50,null

64 三 管理数据 插入表数据 例 2: 一次向 XSB 表中插入两行数据 :090, 王海,, , 软件工程,50,null;0902, 李娜,0, , 软件工程,52,null

65 三 管理数据 删除表数据 删除指定表中所有的行 :TRUNCATE TABLE tb_name

66 三 管理数据 插入表数据 例 : 将 pxscj 数据库的 xsb 表中总学分大于 52 的行删除 例 2: 将 pxscj 数据库的 xsb 表中备注为空的行删除

67 三 管理数据 修改表数据

68 三 管理数据 修改表数据 例 : 将 xsb 表中所有学生的总学分都增加 0 将姓名为 罗琳琳 的同学的专业改为 软件工程, 备注改为 提前修完学分, 学号改为 0826

69 四 思考题 修改表结构时, 关于 ALTER COLUMN ADD DROP COLUMN, 能否同时增加多列? 能否同时修改多列? 能否同时删除多列?

70 四 思考题 2 插入记录时, VALUSE 中的数值个数是否一定要和 COLUMN_LIST 列列表 中指定的列名个数一致? 插入空值能否省略? INSERT INTO XSB(Stu_ID,Sname,Ssex) VALUES('0870',' 王二 '); INSERT INTO XSB VALUES ('0877',' 王二 ',,' ',' 计算机 ','',' 补考 '); INSERT INTO XSB VALUES ('0877',' 王二 ',,' ',' 计算机 ',NULL,' 补考 ');

71 五 SQL 查询 SELECT 语句语法格式

72 五 SQL 查询 SELECT 语句的处理顺序. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH CUBE 或 WITH ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 0. ORDER BY. TOP 所有被使用的子句必须按语法说明中 显示的顺序严格的排序

73 五 SQL 查询 SELECT_list 语句语法格式

74 五 SQL 查询 Select_list 选择所有列 EG: 查询 PXSCJ 数据库中 XSB 表的所有数据 SELECT * FROM XSB; 2 选择表中指定的列 EG: 查询 PXSCJ 数据库的 XSB 表中各个同学的姓名 专业和总学分 SELECT XSB.Stu_ID,XSB.Sname,XSB.Tcredit FROM XSB;

75 五 SQL 查询 Select_list 3 消除结果集中的重复行 EG: 查询 XSB 表中的专业, 消除结果集中重复行 SELECT DISTINCT XSB.Major FROM XSB; 4 定义列别名 EG: 查询 XSB 表中学生的学号 姓名和总学分, 结果中各列的标题分别指定为 学号 姓名 和 总学分 SELECT XSB.Stu_ID AS 学号,XSB.Sname AS 姓名,XSB.Tcredit AS 总学分 FROM XSB;

76 五 SQL 查询 Select_list 5 限制结果集返回行数 EG: 查询 XSB 表中学生的姓名 专业和总学分, 返回结果集的前 6 行 SELECT TOP(6) XSB.Sname,XSB.Major,XSB.Tcredit FROM XSB; 比较 : 查询 XSB 表中学生的姓名 专业和总学分, 返回结果集的前 6% 行 SELECT TOP(6)PERCENT XSB.Sname,XSB.Major,XSB.Tcredit FROM XSB;

77 五 SQL 查询 Select_list 6 替换查询结果中的数据 EG: 查询 XSB 表中学生的学号 姓名和总学分 请对总学分按以下规则处理 : 若总学分为空值, 则替换为 尚未选课 ; 若总学分小于 50, 则替换为 不合格 ; 若总学分在 50 与 52 之间, 则替换为 合格 ; 若总学分大于 52, 则替换为 优秀 列标题更改为 等级

78 五 SQL 查询 Select_list SELECT XSB.Stu_ID,XSB.Sname, 等级 = CASE WHEN XSB.Tcredit IS NULL THEN ' 尚未选课 ' WHEN XSB.Tcredit <50 THEN ' 不合格 ' WHEN XSB.Tcredit >=50 and XSB.Tcredit <=52 THEN ' 合格 ' ELSE ' 优秀 ' END FROM XSB;

79 五 SQL 查询 Select_list 7 计算列值 EG: 按 50 分计算成绩并查询学生的成绩情况 SELECT CJB.Stu_ID,CJB.C_ID, 成绩 =CJB.Grade*.5 FROM CJB; 比较 :SELECT CJB.Stu_ID,CJB.C_ID,CJB.Grade=CJB.Grade*.5 FROM CJB;

80 五 SQL 查询 Select_list 8 聚合函数 ()SUM 和 AVG SUM/AVG ( [ ALL DISTINCT ] expression ) SUM/AVG 在计算时, 忽略 NULL 值 EG: 求学号为 080 的学生所学课程的平均成绩和总成绩 SELECT AVG(CJB.Grade) AS 平均成绩,SUM(CJB.Grade) AS 总成绩 FROM CJB WHERE CJB.Stu_ID='080';

81 五 SQL 查询 Select_list (2)MAX 和 MIN MAX/MIN ( [ ALL DISTINCT ] expression ) MAX/MIN 在计算时, 忽略 NULL 值 EG: 求选修 0 课程的学生的最高分和最低分 SELECT MAX(CJB.Grade) AS 最高分,MIN(CJB.Grade) AS 最低分 FROM CJB WHERE CJB.C_ID='0';

82 五 SQL 查询 Select_list (3)COUNT COUNT{ ( [ ALL DISTINCT ] expression ) *} COUNT 在计算时, 忽略 NULL 值 ;COUNT(*) 返回总数目, 包含空值 EG: 求学生的总数 SELECT COUNT(*) FROM XSB; EG: 统计备注不为空的学生数 SELECT COUNT(XSB.Remark) FROM XSB;

83 五 SQL 查询 WHERE 子句语法格式 < search_condition > ::= 判定运算 判定运算包括比较运算 模式匹配 范围比较 空值比较 CONTAIN 谓 词 FREETEXT 谓词和子查询 [,...n ] { [ NOT ] <predicate> ( <search_condition> ) } [ { AND OR } [ NOT ] { <predicate> ( <search_condition> ) } ]

84 五 SQL 查询 WHERE 子句语法格式

85 五 SQL 查询 WHERE 子句 表达式比较 { expression { = < >! = > > =! > < < =! < } expression /* 比较运算 */ EG: 查询 PXSCJ 数据库 XSB 表中学号为 080 的同学的情况 SELECT XSB.* FROM XSB WHERE XSB.Stu_ID='080'; EG: 查询 XSB 表中总学分大于 50 的同学的情况 SELECT XSB.* FROM XSB WHERE XSB.Tcredit>50; EG: 查询 XSB 表中通信工程专业总学分大于等于 52 的同学的情况 SELECT XSB.* FROM XSB WHERE XSB.Major=' 通信工程 ' AND XSB.Tcredit>=52;

86 五 SQL 查询 WHERE 子句 2 模式匹配 string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] /* 字符串模式匹配 */ 字符串和通配符 允许在字符串中搜索通配符 通配符列表 通配符说明示例 % 包含零个或多个字符的任意字符串 WHERE title LIKE '%computer%' _ ( 下划线 ) 任何单个字符 WHERE au_fname LIKE '_ean' [ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符 WHERE au_lname LIKE '[C-P]arsen' [^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符 WHERE au_lname LIKE 'de[^l]%'

87 五 SQL 查询 WHERE 子句 EG: 查询 PXSCJ 数据库 XSB 表中姓 王 且单名的学生的情况 SELECT XSB.* FROM XSB WHERE XSB.Sname LIKE ' 王 _'; EG: 查询 XSB 表中学号倒数第 3 个数字为, 且倒数第 个数在 ~5 之间的学生的学号 姓名和专业 SELECT XSB.Stu_ID,XSB.Sname,XSB.Major FROM XSB WHERE XSB.Stu_ID LIKE '%_[2345]';

88 五 SQL 查询 WHERE 子句 EG: 查询 XSB 表中名字包含 % 的学生学号和姓名 SELECT XSB.Stu_ID,XSB.Sname FROM XSB WHERE XSB.Sname LIKE '%#%%' ESCAPE'#';

89 五 SQL 查询 WHERE 子句 EG: 查询 PXSCJ 数据库 XSB 表中姓 王 且全名为 3 个字的学生的情况

90 五 SQL 查询 WHERE 子句 3 范围比较 expression [ NOT ] BETWEEN expression AND expression /* 指定范围 */ expression [ NOT ] IN ( subquery expression [,...n ] ) /*IN 子句 */ EG: 查询 PXSCJ 数据库 XSB 表中不在 989 年出生的学生的情况 SELECT XSB.* FROM XSB WHERE XSB.Sdate NOT BETWEEN '989--' AND ' '; EG: 查询 XSB 表中专业为 计算机 通信工程 或 无线电 的学生情况 SELECT XSB.* FROM XSB WHERE XSB.Major IN(' 计算机 ',' 通信工程 ',' 无线电 ');

91 五 SQL 查询 WHERE 子句 4 空值比较 expression IS [ NOT ] NULL /* 是否空值判断 */ 不使用 NOT 时, 若表达式 expression 的值为空值, 则返回 TRUE, 否则返回 FALSE; 使用 NOT 时, 结果相反 EG: 查询 PXSCJ 数据库 XSB 表中总学分尚不定的学生情况 SELECT XSB.* FROM XSB WHERE XSB.Tcredit IS NULL;

92 五 SQL 查询 子查询 子查询除了可以用在 SELECT 语句中, 也可以用在 INSERT UPDATE 和 DELETE 语句中 通常与 IN EXIST 谓词及比较运算符结合使用 ()IN 子查询 expression [ NOT ] IN ( subquery ) /*IN 子句 */ 用于进行一个给定值是否在子查询结果集中的判断 当表达式 expression 与子查询 subquery 的结果表中的某个值相等时,IN 谓词返回 TRUE, 否则返回 FALSE; 使用了 NOT, 则相反

93 五 SQL 查询 子查询 EG: 查询选修了课程号为 206 的课程的学生基本情况 SELECT XSB.* FROM XSB WHERE XSB.Stu_ID IN (SELECT CJB.Stu_ID FROM CJB WHERE CJB.C_ID='206'); 系统先执行子查询, 产生一个结果表, 再执行查询

94 五 SQL 查询 子查询 EG: 查询未选修离散数学的学生情况 SELECT XSB.* FROM XSB WHERE XSB.Stu_ID NOT IN ( SELECT CJB.Stu_ID FROM CJB WHERE CJB.C_ID IN ( SELECT KCB.C_ID FROM KCB WHERE KCB.Cname=' 离散数学 ' ) );

95 五 SQL 查询 子查询 (2) 比较子查询 expression { = < >! = > > =! > < < =! < } { ALL SOME ANY} ( subquery ) /* 比较子查询 */ ALL: 指定表达式要与子查询结果集中的每个值都进行比较, 满足时返回 TRUE, 否则返回 FALSE; SOME 或 ANY: 表达式只要与子查询结果集中的某个值满足比较的关系, 就 返回 TRUE, 否则返回 FALSE

96 五 SQL 查询 子查询 EG: 查询选修了离散数学的学生学号 SELECT CJB.Stu_ID FROM CJB WHERE CJB.C_ID IN ( SELECT KCB.C_ID FROM KCB WHERE KCB.Cname=' 离散数学 ' ); 可否将 IN 改为 =?

97 五 SQL 查询 子查询 EG: 查找比所有计算机系的学生年龄都大的学生 SELECT XSB.* FROM XSB WHERE XSB.Sdate <ALL (SELECT XSB.Sdate FROM XSB WHERE XSB.Major=' 计算机 '); 比较 : SELECT XSB.* FROM XSB WHERE XSB.Sdate < (SELECT XSB.Sdate FROM XSB WHERE XSB.Major=' 计算机 ');

98 五 SQL 查询 子查询 EG: 查找 206 号课程成绩不低于 0 号课程最低成绩的学生学号 SELECT CJB.Stu_ID FROM CJB WHERE CJB.C_ID='206' AND CJB.Grade >=ANY (SELECT CJB.Grade FROM CJB WHERE CJB.C_ID='0');

99 五 SQL 查询 子查询 ANY( 或 SOME) ALL 谓词与聚集函数 IN 谓词的转换

100 五 SQL 查询 子查询 (3)EXISTS 子查询 [NOT] EXISTS ( subquery ) /*EXIST 子查询 */ EXISTS 谓词用于测试子查询的结果是否为空表, 若结果集不为空, 则返回 TRUE, 否则返回 FALSE; 与 NOT 结合使用, 其返回值相反 由 EXISTS 引入的子查询的选择列表通常几乎都是由星号 (*) 组成 由于只是测试是否存在符合子查询中指定条件的行, 因此不必列出列名

101 五 SQL 查询 子查询 EG: 查询选修了 206 号课程的学生姓名 SELECT XSB.Sname FROM XSB WHERE EXISTS (SELECT CJB.* FROM CJB WHERE CJB.Stu_ID=XSB.Stu_ID AND CJB.C_ID='206');

102 五 SQL 查询 子查询 EG: 查询没有选修 206 号课程的学生姓名 SELECT XSB.Sname FROM XSB WHERE NOT EXISTS (SELECT CJB.* FROM CJB WHERE CJB.Stu_ID=XSB.Stu_ID AND CJB.C_ID='206'); 比较 :

103 五 SQL 查询 子查询 SELECT XSB.Sname FROM XSB WHERE XSB.Stu_ID NOT IN (SELECT CJB.Stu_ID FROM CJB WHERE CJB.C_ID='206'); SELECT XSB.Sname FROM XSB WHERE XSB.Stu_ID <> (SELECT CJB.Stu_ID FROM CJB WHERE CJB.C_ID='206');

104 五 SQL 查询 连接查询 在 T-SQL 中, 连接查询有两大表示形式 : 符合 SQL 标准的连接谓词表示形式 ; T-SQL 扩展的, 使用 JOIN 关键字的表示形式

105 五 SQL 查询 连接查询 连接谓词在 SELECT 语句的 WHERE 子句中使用比较运算符给出连接条件对表进行连接 EG: 查找 PXSCJ 数据库每个学生的基本情况以及选修的课程情况 SELECT XSB.*,CJB.* FROM XSB,CJB WHERE XSB.Stu_ID=CJB.Stu_ID; 比较 : SELECT XSB.*,CJB.C_ID,CJB.Grade FROM XSB,CJB WHERE XSB.Stu_ID=CJB.Stu_ID;

106 五 SQL 查询 连接查询 EG: 查找选修了 计算机基础 课程且成绩在 80 分以上的学生学号 姓名 课程名及成绩 SELECT XSB.Stu_ID,XSB.Sname,KCB.Cname,CJB.Grade FROM XSB,KCB,CJB WHERE XSB.Stu_ID=CJB.Stu_ID AND KCB.C_ID=CJB.C_ID AND KCB.Cname=' 计算机基础 ' AND CJB.Grade>=80;

107 五 SQL 查询 连接查询 2 以 JOIN 关键字指定的连接 <joined_table> ::= { 要连接的表连接类型连接条件 <table_source> <join_type> <table_source> ON <search_condition> } <table_source> CROSS JOIN <table_source> 交叉连接 <join_type> ::= [ { INNER { { LEFT RIGHT FULL } [ OUTER ] } } [ <join_hint> ] ] JOIN

108 五 SQL 查询 连接查询 以 JOIN 关键字指定的连接有三种类型 : 内连接 外连接 交叉连接 () 内连接 (INNER JOIN) EG: 查找 PXSCJ 数据库每个学生的基本情况以及选修的课程情况 SELECT * FROM XSB INNER JOIN CJB ON XSB.Stu_ID=CJB.Stu_ID; 执行结果将包含 XSB 表和 CJB 表的所有字段 ( 不去除重复字段 )

109 五 SQL 查询 连接查询 EG: 查询选修了 206 号课程且成绩在 80 分以上的学生姓名和成绩 SELECT XSB.Sname,CJB.Grade FROM XSB JOIN CJB ON XSB.Stu_ID=CJB.Stu_ID WHERE CJB.C_ID='206' AND CJB.Grade>=80; EG: 查找不同课程成绩相同的学生的学号 课程号和成绩 ( 自连接 ) SELECT a.stu_id,a.c_id,b.c_id,a.grade FROM CJB a JOIN CJB b ON a.grade=b.grade AND a.stu_id=b.stu_id AND a.c_id!=b.c_id;

110 五 SQL 查询 连接查询 EG: 查找选修了 计算机基础 课程且成绩在 80 分以上的学生学号 姓名 课程名和成绩 SELECT XSB.Stu_ID,XSB.Sname,KCB.Cname,CJB.Grade FROM XSB JOIN CJB JOIN KCB ON CJB.C_ID=KCB.C_ID ON XSB.Stu_ID=CJB.Stu_ID WHERE KCB.Cname=' 计算机基础 ' AND CJB.Grade>=80; 试比较 :

111 五 SQL 查询 连接查询 SELECT XSB.Stu_ID,XSB.Sname,KCB.Cname,CJB.Grade FROM XSB JOIN CJB ON XSB.Stu_ID=CJB.Stu_ID JOIN KCB ON CJB.C_ID=KCB.C_ID WHERE KCB.Cname=' 计算机基础 ' AND CJB.Grade>=80; SELECT XSB.Stu_ID,XSB.Sname,KCB.Cname,CJB.Grade FROM XSB JOIN CJB JOIN KCB ON XSB.Stu_ID=CJB.Stu_ID ON CJB.C_ID=KCB.C_ID WHERE KCB.Cname=' 计算机基础 ' AND CJB.Grade>=80;

112 五 SQL 查询 连接查询 (2) 外连接 (OUTER JOIN) 外连接的结果表不但包含满足连接条件的行, 还包括相应表中的所有行 左外连接 (LEFT OUTER JOIN): 结果表中除了包括满足连接条件的行外, 还包括左表的所有行 ; 右外连接 (RIGHT OUTER JOIN): 结果表中除了包括满足连接条件的行外, 还包括右表的所有行 ; 完全外连接 (FULL OUTER JOIN): 结果表中除了包括满足连接条件的行外, 还包括两个表的所有行 ; 其中 OUTER 关键字可以省略

113 五 SQL 查询 连接查询 EG: 查找所有学生情况, 以及他们选修的课程号, 若学生未选修任何课, 也要包括其情况 SELECT XSB.*,CJB.C_ID FROM XSB LEFT JOIN CJB ON XSB.Stu_ID=CJB.Stu_ID; SELECT XSB.*,CJB.C_ID FROM CJB RIGHT JOIN XSB ON XSB.Stu_ID=CJB.Stu_ID;

114 五 SQL 查询 连接查询 EG: 查找被选修了的课程的选修情况和所有开设的课程名 SELECT CJB.*,KCB.Cname FROM CJB RIGHT JOIN KCB ON CJB.C_ID=KCB.C_ID; SELECT CJB.*,KCB.Cname FROM KCB LEFT JOIN CJB ON CJB.C_ID=KCB.C_ID;

115 五 SQL 查询 连接查询 (3) 交叉连接 (CROSS JOIN) 将两个表进行笛卡尔积运算, 结果表由第一个表的每一行与第二个表的每一 行拼接后形成的表 EG: 列出学生所有可能的选课情况 SELECT XSB.Stu_ID,XSB.Sname,KCB.C_ID,KCB.Cname FROM XSB CROSS JOIN KCB;

116 五 SQL 查询 连接查询 EG: 列出软件工程学生所有可能的选课情况 SELECT XSB.Stu_ID,XSB.Sname,KCB.C_ID,KCB.Cname FROM XSB CROSS JOIN KCB WHERE XSB.Major=' 软件工程 ';

117 五 SQL 查询 连接查询 连接查询和子查询的区别 : 连接查询可以合并两个或多个表中的数据 ; 带子查询的 SELECT 语句的结果只能来自一个表

118 五 SQL 查询 FROM 子句

119 五 SQL 查询 FROM 子句 table_or_view_name [ [ AS ] table_alias ] /* 查询表或视图, 可指定别名 */ 指定 SELECT 语句要查询的表或视图, 表和视图可以是一个或多个 EG: 从 KCB 表中查找 0 号课程的开课学期 SELECT KCB.Term FROM KCB WHERE KCB.C_ID='0'; EG: 查找 080 号学生计算机基础课的成绩 SELECT CJB.Grade FROM CJB,KCB WHERE CJB.Stu_ID='080' AND KCB.Cname=' 计算机基础 ' AND KCB.C_ID=CJB.C_ID;

120 五 SQL 查询 FROM 子句 2 derived_table [ AS ] table_alias [ ( column_alias [,...n ] ) ] /* 子查询 */ 子查询可以在 FROM 子句中使用,derived_table 表示执行子查询返回的表, 必须使 用 AS 关键字为子查询产生的表定义一个别名 EG: 从 XSB 表中查找总学分大于 50 的男生的姓名和学号 SELECT STUDENT.Stu_ID,STUDENT.Sname FROM (SELECT XSB.* FROM XSB WHERE XSB.Tcredit>50)AS STUDENT WHERE STUDENT.Ssex=;

121 五 SQL 查询 FROM 子句 SELECT XSB.Stu_ID,XSB.Sname FROM XSB WHERE XSB.Tcredit>50 AND XSB.Ssex=; SELECT XSB.Stu_ID, XSB.Sname FROM (SELECT XSB.* FROM XSB WHERE XSB.Tcredit>50)AS STUDENT WHERE XSB.Ssex=;

122 五 SQL 查询 FROM 子句 EG: 在 XSB 表中查找 990 年 月 日以前出生的学生的姓名和专业, 分别使用 name 和 speciality 表示 SELECT m.name,m.speciality FROM(SELECT XSB.* FROM XSB WHERE XSB.Sdate<'99000') AS m(num,name,sex,birthday,speciality,score,mem); 比较 :

123 五 SQL 查询 FROM 子句 SELECT m.sname name,m.major speciality FROM(SELECT XSB.* FROM XSB WHERE XSB.Sdate<'99000') AS m; SELECT m.sname AS name,m.major AS speciality FROM(SELECT XSB.* FROM XSB WHERE XSB.Sdate<'99000') AS m; SELECT m.name,m.speciality FROM(SELECT XSB.* FROM XSB WHERE XSB.Sdate<'99000') AS m(name, speciality);

124 五 SQL 查询 GROUP BY 子句 [ GROUP BY group_by_expression] /*GROUP BY 子句, 指定分组表达式 */ GROUP BY 子句具有符合 ISO 的语法和不符合 ISO 的语法 在一条 SELECT 语句中只能使用一种语法样式 对于所有的新工作, 请使用符合 ISO 的语法 提供不符合 ISO 的语法的目的是为了实现向后兼容

125 五 SQL 查询 GROUP BY 子句 非 ISO 标准的 GOURP BY 子句 [ GROUP BY [ ALL ] group_by_expression [,...n ] [ WITH { CUBE ROLLUP } ] ] 分组表达式 指定 CUBE ROLLUP 操作 指定结果集内不仅包含由 GROUP BY 提供的行, 同时还包含汇总行

126 五 SQL 查询 GROUP BY 子句 EG: 查询 PXSCJ 数据库中各专业名 SELECT XSB.Major FROM XSB GROUP BY XSB.Major; 比较 : SELECT XSB.Major FROM XSB; SELECT DISTINC XSB.Major FROM XSB;

127 五 SQL 查询 GROUP BY 子句 EG: 求各专业的学生数 SELECT XSB.Major,COUNT(*) AS 学生人数 FROM XSB GROUP BY XSB.Major; EG: 求被选修的各门课程的平均成绩和选修该课程的人数 SELECT CJB.C_ID,AVG(CJB.Grade) AS 平均成绩,COUNT(CJB.Stu_ID) AS 选修人数 FROM CJB GROUP BY CJB.C_ID;

128 五 SQL 查询 GROUP BY 子句 <select> 列表中任何非聚合表达式中的每个表列或视图列都必须包括在 GROUP BY 列表中 ; GROUP BY 子句中的表达式可以包含 FROM 子句中表 派生表或视图的列 这些列不必显示在 SELECT 子句 <select> 列表中

129 五 SQL 查询 GROUP BY 子句 EG: 求每个专业的总人数 男生 女生人数及所有专业学生总人数 SELECT XSB.Major,XSB.Ssex,COUNT(*) AS 人数 FROM XSB GROUP BY XSB.Major,XSB.Ssex WITH ROLLUP; 比较 : SELECT XSB.Major,XSB.Ssex,COUNT(*) AS 人数 FROM XSB GROUP BY XSB.Major,XSB.Ssex;

130 五 SQL 查询 GROUP BY 子句 SELECT XSB.Major,XSB.Ssex,COUNT(*) AS 人数 FROM XSB GROUP BY XSB.Major,XSB.Ssex WITH CUBE;

131 五 SQL 查询 GROUP BY 子句 2 ISO 标准的 GOURP BY 子句 GROUP BY { 分组的字段名表达式 <column_expression> ROLLUP ( < composite element list > ) CUBE ( < composite element list > ) GROUPING SETS ( < grouping set item list >) 生成简单的 GROUP BY 聚合行以及小计行或超聚合行, 还生成一个总计行 生成简单的 GROUP BY 聚合行 ROLLUP 超聚合行和交叉表格行 } 在一个查询中指定数据的多个分组

132 五 SQL 查询 GROUP BY 子句 EG: 求每个专业的总人数 男生 女生人数及所有专业学生总人数 SELECT XSB.Major,XSB.Ssex,COUNT(*) AS 人数 FROM XSB GROUP BY ROLLUP(XSB.Major,XSB.Ssex); 比较 : SELECT XSB.Major,XSB.Ssex,COUNT(*) AS 人数 FROM XSB GROUP BY CUBE(XSB.Major,XSB.Ssex);

133 五 SQL 查询 GROUP BY 子句 EG: 根据专业和性别对人数进行聚合 SELECT XSB.Major,XSB.Ssex,COUNT(*) AS 人数 FROM XSB GROUP BY GROUPING SETS(XSB.Major,XSB.Ssex);

134 五 SQL 查询 Having 子句 [ HAVING <search condition> ] 使用 HAVING 子句来指定组或聚合的搜索条件 ; HAVING 只能与 SELECT 语句一起使用 ; HAVING 通常在 GROUP BY 子句中使用 如果不使用 GROUP BY 子句, 则 HAVING 的行为与 WHERE 子句一样 ; search condition 与 WHERE 子句的查询条件类似, 但 HAVING 子句中可以使用聚合函数, 而 WHERE 子句中不可以

135 五 SQL 查询 Having 子句 EG: 查找平均成绩在 85 分以上的学生的学号和平均成绩 SELECT CJB.Stu_ID,AVG(CJB.Grade) AS 平均成绩 FROM CJB GROUP BY CJB.Stu_ID HAVING AVG(CJB.Grade)>=85;

136 五 SQL 查询 Having 子句 EG: 查找选修课程超过 2 门且成绩都在 80 分以上的学生的学号 SELECT CJB.Stu_ID FROM CJB WHERE CJB.Grade>=80 GROUP BY CJB.Stu_ID HAVING COUNT(*)>2; 执行顺序 : WHERE 用于筛选由 FROM 子句指定的数据对象 ; GROUP BY 对 WHERE 的结果进行分组 ; HAVING 对分组数据进行过滤

137 五 SQL 查询 Having 子句 EG: 查找通信工程专业平均成绩在 85 分以上的学生的学号和平均成绩 SELECT CJB.Stu_ID,AVG(CJB.Grade) AS 平均成绩 FROM CJB,XSB WHERE XSB.Major=' 通信工程 ' AND XSB.Stu_ID=CJB.Stu_ID GROUP BY CJB.Stu_ID HAVING AVG(CJB.Grade)>=85; 比较 :

138 五 SQL 查询 Having 子句 EG: 查找通信工程专业平均成绩在 85 分以上的学生的学号和平均成绩 SELECT CJB.Stu_ID,AVG(CJB.Grade) AS 平均成绩 FROM CJB,XSB WHERE XSB.Major=' 通信工程 ' AND XSB.Stu_ID=CJB.Stu_ID GROUP BY CJB.Stu_ID HAVING AVG(CJB.Grade)>=85; 比较 :

139 五 SQL 查询 Having 子句 SELECT CJB.Stu_ID,AVG(CJB.Grade) AS 平均成绩 FROM CJB,XSB GROUP BY CJB.Stu_ID HAVING XSB.Major=' 通信工程 ' AND XSB.Stu_ID=CJB.Stu_ID AND AVG(CJB.Grade)>=85; 如果使用了 GROUP BY 子句, 则 HAVING 语句中的列要包含在 GROUP BY 子句 或聚集函数中

140 五 SQL 查询 ORDER BY 子句

141 五 SQL 查询 ORDER BY 子句 Chinese_PRC_: 按拼音排序 Chinese_PRC_Stroke_: 按笔画排序 CI : 不区分大小写 CS : 区分大小写 AI : 不区分重音 AS : 区分重音 KS : 区分假名类型 WS: 区分全半角

142 五 SQL 查询 ORDER BY 子句 EG: 将通信工程专业的学生按出生时间先后顺序排序 SELECT * FROM XSB WHERE XSB.Major=' 通信工程 ' ORDER BY XSB.Sdate; EG: 将计算机专业学生的 计算机基础 课程成绩按降序排列 SELECT XSB.Sname,KCB.Cname,CJB.Grade FROM XSB,CJB,KCB WHERE XSB.Major=' 计算机 ' AND KCB.Cname=' 计算机基础 ' AND XSB.Stu_ID=CJB.Stu_ID AND KCB.C_ID=CJB.C_ID ORDER BY CJB.Grade;

143 五 SQL 查询 ORDER BY 子句 EG: 查找通信工程专业学生的学号 姓名 出生时间, 并产生一个学生总人数行 SELECT XSB.Stu_ID,XSB.Sname,XSB.Sdate FROM XSB WHERE XSB.Major=' 通信工程 ' COMPUTE COUNT(XSB.Stu_ID);

144 五 SQL 查询 ORDER BY 子句 ORDER BY 子句可以与 COMPUTE BY 子句一起使用, 在对结果排序的同时还产生附加的汇总行 ; 如果使用 COMPUTE BY, 则还必须使用 ORDER BY 子句 表达式必须与在 ORDER BY 后列出的子句相同或是其子集, 并且顺序必须相同

145 五 SQL 查询 ORDER BY 子句 EG: 将学生按专业排序, 并汇总各专业人数和平均学分 SELECT XSB.* FROM XSB ORDER BY XSB.Major COMPUTE COUNT(XSB.STU_ID),AVG(XSB.TCREDIT) BY XSB.MAJOR;

146 五 SQL 查询 SELECT 语句的其他语法 INTO [INTO new_table] 包含 INTO 子句的 SELECT 语句执行后所创建的表的结构由 SELECT 所选择的列决定, 新创建的表中的记录由 SELECT 的查询结果决定 若 SELECT 查询结果为空, 则创建一个只有结构没有记录的表 EG: 由 XSB 表创建 计算机系学生 表, 包括学号和姓名 SELECT XSB.Stu_ID,XSB.Sname INTO 计算机系学生 FROM XSB WHERE XSB.Major=' 计算机 ';

147 五 SQL 查询 SELECT 语句的其他语法 2 UNION( 联合查询 ) SELECT 查询语句 { <query_specification> ( <query_expression> ) } SELECT 查询语句 UNION [ ALL ] <query_specification ( <query_expression> ) [ UNION [ ALL ] <query_specification> ( <query_expression> ) [...n ] ] 结果中包括所有行, 不去除重复行

148 五 SQL 查询 SELECT 语句的其他语法 使用 UNION 子句可以将两个或多个 SELECT 查询的结果合并成一个结果集 () 所有查询中的列数和列的顺序必须相同 ; (2) 数据类型必须兼容 ; (3) 若不指定 INTO 子句, 结果将合并到第一个表中

149 五 SQL 查询 SELECT 语句的其他语法 EG: 查找学号为 080 和学号为 0820 的两位同学的信息 SELECT XSB.* FROM XSB WHERE XSB.Stu_ID='080' UNION ALL SELECT XSB.* FROM XSB WHERE XSB.Stu_ID='0820';

150 五 SQL 查询 SELECT 语句的其他语法 3 EXCEPT 和 INTERSECT EXCEPT 和 INTERSECT 用于比较两个查询的结果, 返回非重复值 () 所有查询中的列数和列的顺序必须相同 ; (2) 数据类型必须兼容 ; EXCEPT: 从关键字左边的查询中返回右边查询没有找到的所有非重复值 INTERSECT: 关键字左右两边的两个查询都返回的所有非重复值 EXCEPT 和 INTERSECT 返回的结果集列名与关键字左侧的查询返回的列名相同

151 五 SQL 查询 SELECT 语句的其他语法 EG: 查找专业为计算机但性别不为男的学生信息 SELECT XSB.* FROM XSB WHERE XSB.Major=' 计算机 ' EXCEPT SELECT XSB.* FROM XSB WHERE XSB.Ssex=; EG: 查找总学分大于 42 且性别为男的学生信息 SELECT XSB.* FROM XSB WHERE XSB.Tcredit>42 INTERSECT SELECT XSB.* FROM XSB WHERE XSB.Ssex=;

152 五 SQL 查询 SELECT 语句的其他语法 4 SELECT 关键字后面也可以定义子查询 EG: 从 XSB 表中查找所有女生的姓名 学号及其与 080 号学生的年龄差距 SELECT XSB.Sname,XSB.Stu_ID,YEAR(XSB.Sdate)-YEAR ( (SELECT XSB.Sdate FROM XSB WHERE XSB.Stu_ID='080') ) AS 年龄差距 FROM XSB WHERE XSB.Ssex=0;

153 六 视图 从一个或几个基本表 ( 或视图 ) 导出的表 ; 数据库只存放视图的定义, 不存储视图所对应的数据, 数据存放在基本表中 ; 对视图的数据进行操作时, 系统根据视图的定义去操作与视图相关联的基本表 ; 视图一经定义后, 可以像表一样被查询 修改 删除和更新 ; 只有在当前数据库中才能创建视图, 视图不能与表同名

154 六 视图 优点 为用户集中数据, 简化用户的数据查询和处理 ; 屏蔽数据库的复杂性 ; 简化用户权限的管理 ; 便于数据共享 ; 可以重新组织数据以便输出到其他应用程序中

155 六 视图 创建视图 CREATE VIEW [ schema_name. ] view_name [ (column [,...n ] ) ] CREATE [ WITH <view_attribute> VIEW [ schema_name [,...n ].]] view_name /* 指出视图的属性 [ (column [,...n*/] ) ] 全部省略或全部指定 [ AS WITH select_statement <view_attribute> [,...n ] ] /* /* 指定视图创建的语句指出视图的属性 */ */ AS [ WITH select_statement CHECK OPTION ] [ ; ] /* /* 指出视图上所做的修改符合限制条件指定视图创建的语句 */ */ [ WITH CHECK OPTION ] [ ; ] /* 指出视图上所做的修改符合限制条件 */ 在下列情况下必须指定组成视图的所有列名 : 视图中的列是从算术表达式 函数或常量派生的 ; 两个或更多的列可能会具有相同的名称 ( 通常是由于联接的原因 ); 视图中的某个列的指定名称不同于其派生来源列的名称

156 六 视图 创建视图 EG: 创建计算机专业学生的视图 CS_XS, 并保证对该视图的修改都符合 专业为计算机 这一条件 CREATE VIEW CS_XS AS SELECT XSB.* FROM XSB WHERE XSB.Major=' 计算机 ' WITH CHECK OPTION;

157 六 视图 创建视图 EG: 创建 CS_KC 视图, 包括计算机专业各学生的学号 其选修的课程号以及成绩 要保证对该视图的修改都符合 专业为计算机 这一条件 CREATE VIEW CS_KC AS SELECT XSB.Stu_ID,CJB.C_ID,CJB.Grade FROM XSB,CJB WHERE XSB.Stu_ID=CJB.Stu_ID AND XSB.Major=' 计算机 ' WITH CHECK OPTION;

158 六 视图 创建视图 EG: 创建计算机专业学生的平均成绩视图 CS_KC_AVG, 包括学号和平均成绩 CREATE VIEW CS_KC_AVG(Stu_ID,Grade_avg) AS SELECT CJB.Stu_ID,AVG(CJB.Grade) FROM CJB GROUP BY CJB.Stu_ID; 比较 :

159 六 视图 创建视图 CREATE VIEW CS_KC_AVG AS SELECT CJB.Stu_ID,AVG(CJB.Grade) FROM CJB GROUP BY CJB.Stu_ID; CREATE VIEW CS_KC_AVG AS SELECT CJB.Stu_ID,AVG(CJB.Grade) AS Grade_avg FROM CJB GROUP BY CJB.Stu_ID;

160 六 视图 查询视图 视图定义后, 可以像查询基本表那样对视图进行查询 EG: 查找计算机专业的学生学号和选修的课程号 SELECT STU_ID,C_ID FROM CS_KC; EG: 查找平均成绩在 80 分以上的学生的学号和平均成绩 SELECT * FROM CS_KC_AVG WHERE GRADE_AVG>=80; 在使用视图查询时, 若其关联的基本表中添加了新字段, 则必须重新创 建视图才能查询到新字段

161 六 视图 查询视图 视图查询执行过程 ( 视图消解 ): 进行有效性检查, 确定查询涉及到的表或视图是否存在 ; 取出视图定义, 结合定义中的子查询和用户查询, 转换为等价的对基本表的查询 ; 执行查询

162 六 视图 查询视图 EG: 创建 CS_CJ 视图, 包含学生学号和平均成绩 查询平均成绩在 90 分以上的学生学号和平均成绩 CREATE VIEW CS_CJ(SNO,Gavg) AS SELECT CJB.Stu_ID,AVG(CJB.Grade) FROM CJB GROUP BY CJB.Stu_ID; GO SELECT * FROM CS_CJ WHERE Gavg>=90;

163 六 视图 查询视图 SELECT CJB.Stu_ID,AVG(CJB.Grade) FROM CJB WHERE AVG(CJB.Grade>=90 GROUP BY CJB.Stu_ID; SELECT CJB.Stu_ID,AVG(CJB.Grade) FROM CJB GROUP BY CJB.Stu_ID HAVING AVG(CJB.Grade>=90;

164 六 视图 查询视图 目前多数关系数据库系统对行列子集视图的查询均能进行正确转换 但对非行列子集视图的查询不一定能做转换, 因此这类查询应该直接对基本表进行 注 : 行列子集视图 : 从单个基本表导出来, 保留了主码, 只去掉了基本表的某些行和某些列 上例为非行列子集视图 ( 包含由聚集函数产生的列 ), 因此这类查询不能做转换, 应该直接对进本表进行查询

165 六 视图 更新视图 通过更新视图 ( 插入 修改 删除 ) 数据可以修改基本表数据 ; 对视图的更新, 通过视图消解, 最终转化为对基本表的更新操作 ; 不是所有的视图都可以更新, 只有对满足可更新条件的视图, 才能进行更新

166 六 视图 更新视图 可更新视图满足以下条件的视图 : 创建视图的 SELECT 语句中没有聚合函数, 且没有 TOP GROUP BY UNION 子句及 DISTINCT 关键字 ; 2 创建视图的 SELECT 语句中不包含从基本表列通过计算所得的列 ; 3 创建视图的 SELECT 语句的 FROM 子句中至少要包含一个基本表

167 六 视图 更新视图 () 插入数据 使用 INSERT 语句通过视图向基本表插入数据 EG: 向 CS_XS 视图中插入以下记录 :( 085, 刘明义,, , 计算机,50,NULL) INSERT INTO CS_XS VALUES('085',' 刘明义 ',,' ',' 计算机 ',50,NULL); 当视图所依赖的基本表有多个时, 不能向该视图插入数据

168 六 视图 更新视图 (2) 修改数据 使用 UPDATE 语句可以通过视图修改基本表的数据 EG: 将所有计算机专业学生的总学分增加 8. UPDATE CS_XS SET Tcredit=Tcredit+8; EG: 将 CS_KC 视图中学号为 080 的学生的 0 号课程成绩改为 90 UPDATE CS_KC SET Grade=90 WHERE Stu_ID='080' AND C_ID='0';

169 六 视图 更新视图 EG: 创建 CS_XK 视图, 包括计算机专业各学生的学号 姓名, 其选修的课程 名及成绩 将其中学号为 0803 的学生姓名改为张三三, 成绩改为 90 分 CREATE VIEW CS_XK AS UPDATE CS_XK SET Grade=90 SELECT XSB.Stu_ID,XSB.Sname,KCB.Cname,CJB.Grade FROM XSB,KCB,CJB WHERE Stu_ID='0803'; WHERE XSB.Major=' 计算机 ' AND XSB.Stu_ID=CJB.Stu_ID UPDATE CS_XK SET AND Sname=' 张三三 ' KCB.C_ID=CJB.C_ID WHERE Stu_ID='0803'; WITH CHECK OPTION; 若一个视图依赖于多个基本表, 一次修改该视图只能变动 一个基本表的数据 GO UPDATE CS_XK SET Grade=90,Sname=' 张三三 ' WHERE Stu_ID='0803';

170 六 视图 更新视图 (3) 删除数据使用 DELETE 语句可以通过视图删除基本表的数据 EG: 将 CS_XS 视图中女学生的记录删除 ( 删除计算机专业女学生的信息 ) DELETE FROM CS_XS WHERE Stu_ID=0; EG: 将 CS_XK 视图中吴明明的记录删除 DELETE FROM CS_XK WHERE Sname=' 吴明明 '; ( 无法实现 ) 对于依赖于多个基本表的视图, 不能使用 DELETE 语句

171 六 视图 修改视图 ALTER VIEW [ schema_name. ] view_name [ ( column [,...n ] ) ] [ WITH <view_attribute> [,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ] EG: 将 CS_XS 视图修改为只包含计算机专业学生的学号 姓名和总学分 ALTER VIEW CS_XS AS SELECT XSB.Stu_ID,XSB.Sname,XSB.Tcredit FROM XSB WHERE XSB.Major=' 计算机 ';

172 六 视图 删除视图 使用 DROP VIEW 可以删除一个或多个视图 DROP VIEW [ schema_name. ] view_name [...,n ] [ ; ] EG: 删除视图 CS_XS 和 CS_KC DROP VIEW CS_XS,CS_KC; 如果被删除的视图还导出了其他视图, 则在删除该视图的同时使用 CASCADE 级联删除语句将导出的所有视图一并删除 ; 基本表删除后, 由该基本表导出的所有视图没有被删除, 但均无法使用, 必须通过 DROP VIEW 语句进行删除

173 七 索引 索引 : 根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表 作用 : 快速存取数据 ; 保证数据记录的唯一性 ; 实现表与表之间的参照完整性 ; 在使用 ORDER BY GROUP BY 子句进行数据检索时, 利用索引可以减少排序和分组的时间

174 七 索引 为什么要给表加上主键? 为什么加索引后会使查询变快? 为什么不可以无节制地加索引?

175 七 索引 目前, 主流的 RDBMS 都是把平衡树 (B+ 树 ) 当做数据表默认的索引数据结构的 加了主键的表, 并不能被称之为 表 ; 没加主键的表, 它的数据无序的放置在磁盘存储器上, 一行一行的排列的很整齐, 跟我们认知中的 表 很接近 ; 给表设置了主键, 则表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构 ( 平衡树 ), 即整个表变成了一个索引

176 七 索引 从这里开始查找 索引 表数据 带主键的数据库表的存储结构 ( 平衡树 )

177 七 索引 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降 平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容, 破坏树结构 ; 每次数据改变时, DBMS 必须去重新梳理树 ( 索引 ) 的结构以确保它的正确, 这会带来不小的性能开销, 也就是为什么索引会给查询以外的操作带来副作用的原因

178 七 索引 索引的分类 MS SQL Server 支持在表中任何列 ( 包括计算列 ) 上定义索引, 按索引的组织方式分为聚集索引和非聚集索引 () 聚集索引 (clustered index) 将数据行的键值在表内排序并存储对应的数据记录, 使得数据表物理顺序与索引顺序一致 ( 索引中键值的逻辑顺序决定了表中相应行的物理顺序 ) 2 聚集索引确定表中数据的物理顺序, 一个表只能包含一个聚集索引, 该索引可以包含多个列 ( 组合索引 )

179 七 索引 索引的分类 (2) 非聚集索引 (nonclustered index) 非聚集索引完全独立于数据行的结构 数据存储在一个位置, 索引存储在另一位置, 索引带有指针指向数据的存储位置 2 非聚集索引中的项目按索引值的顺序存储, 表中的信息按另一种顺序存储 索引的逻辑顺序与磁盘上的物理存储顺序不同 3 一个表中可以有一个或多个非聚集索引

180 七 索引 索引的分类 聚集索引与非聚集索引的区别 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致 ; 聚集索引表记录的排列顺序与索引的排列顺序一致, 查询速度快, 对表进行修改会引起数据顺序的重组, 速度较慢 ; 非聚集索引指定了表中记录的逻辑顺序, 记录的物理顺序和索引的顺序不一致, 添加记录不会引起数据顺序的重组 ; MS SQL Server 默认在主键上建立聚集索引

181 七 索引 建议创建索引的列 在经常需要搜索的列上, 可以加快搜索的速度 ; 在作为主键的列上, 强制该列的唯一性和组织表中数据的排列结构 ; 在经常用在连接的列上, 这些列主要是一些外键, 可以加快连接的速度 ; 在经常需要根据范围进行搜索的列上创建索引, 因为索引已经排序, 其指定的范围是连续的 ; 在经常需要排序的列上创建索引, 因为索引已经排序, 这样查询可以利用索引的排序, 加快排序查询时间 ; 在经常使用 WHERE 子句中的列上面创建索引, 加快条件的判断速度

182 七 索引 不建议创建索引的列 在查询中很少使用或参考的列 有无索引, 并不能提高查询速度, 创建索引反而会降低了系统的维护速度和增大了空间需求 ; 只有很少数据值的列 由于列的取值很少, 在查询中, 需要在表中搜索的数据行的比例很大 增加索引, 并不能明显加快检索速度 ; 定义为 text image 和 bit 数据类型的列 这些列的数据量要么相当大, 要么取值很少, 不利于使用索引 当修改操作远远多于检索操作时, 不应该创建索引

183 七 索引 创建索引 CREATE [ UNIQUE ] /* 指定索引是否唯一 */ [ CLUSTERED NONCLUSTERED ] /* 索引的组织方式 */ INDEX index_name /* 索引名称 */ ON <object> ( column [ ASC DESC ] [,...n ] ) /* 索引定义的依据 */ EG: 根据 KCB 表的课程号列创建唯一聚集索引 ( 先将 KCB 的主键删除 ) CREATE UNIQUE CLUSTERED INDEX kc_id_ind ON KCB(C_ID); EG: 根据 CJB 表的学号和课程号创建复合索引 CREATE INDEX CJB_ind ON CJB(Stu_ID,C_ID);

184 七 索引 删除索引 DROP INDEX { index_name ON table_or_view_name [ n ] table_or_view_name.index_name [ n] } EG: 删除表 KCB 的一个索引名为 kc_name_ind 的索引 IF EXISTS(SELECT name FROM sysindexes WHERE name='kc_name_ind') DROP INDEX KCB.kc_name_ind;

185 七 索引 不足之处 虽然索引大大提高了查询速度, 同时却会降低更新表的速度 ; 建立索引会占用磁盘空间的索引文件 ; 索引只是提高效率的一个因素, 如果数据库有大数据量的表, 则需要建立优秀的索引, 或优化查询语句