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

1. 第2章 关系数据库标准语言——SQL

2. 2.1 SQL语言的基本概念与特点 • 2.2 了解SQL Server 2000 • 2.3 创建与使用数据库 • 2.4 创建与使用数据表 • 2.5 创建与使用索引 • 2.6 数据查询 • 2.7 数据更新 • 2.8 视图 • 2.9 数据控制

3. 数据查询 数据定义 数据操纵 数据控制 结构化查询语言 Structured Query Language

4. 2.1 SQL语言的基本概念与特点 • 2.1.1 SQL语言的发展及标准化 • SQL语言的发展 大型数据库 Sybase INFORMIX SQL Server Oracle DB2 INGRES ---------------- 小型数据库 FoxPro Access SQL SEQUEL Chamberlin

5. 2.1.2 SQL语言的基本概念 • 基本表（Base Table） • 一个关系对应一个基本表 • 一个或多个基本表对应一个存储文件 • 视图（View） • 视图是从一个或几个基本表导出的表，是一个虚拟的表 S(SNo，SN，Sex，Age，Dept) S_Male(SNo，SN，Age，Dept) 无数据，只有定义 在数据库中只存有S_Male的定义，数据仍在S表中 Sex='男'

6. SQL语言支持的关系数据库的三级模式结构

7. 2.1.3 SQL语言的主要特点 • SQL语言是类似于英语的自然语言，简洁易用 • SQL语言是一种非过程语言 • SQL语言是一种面向集合的语言 • SQL语言既是自含式语言，又是嵌入式语言 • SQL语言具有数据查询、数据定义、数据操纵和数据控制四种功能

8. 2.2 了解SQL Server 2000 SQL Server是一个关系数据库管理系统 企业版（Enterprise Edition） 标准版（Standard Edition） 个人版（Personal Edition） 开发者版（Developer Edition）

9. 2.2.1 SQL Server 2000的主要组件

10. 2.2.2 企业管理器 文本文件 • 由Enterprise Manager产生的SQL脚本是一个后缀名为.sql的文件 • 企业管理器的管理工作 管理复制 管理数据库 管理登录和许可 管理数据库对象 管理SQL Server Agent 管理备份 管理SQL Server Mail

11. 2.2.3 查询分析器 • 使用查询分析器的熟练程度是衡量一个SQL Server用户水平的标准。

12. 2.3 创建与使用数据库 数据库 存放数据库数据和数据库对象的文件 主要数据文件(.mdf ) +次要数据文件(.ndf ) 只有一个 可有多个 数据文件1 … 数据文件n 事务日志文件 记录数据库更新情况，扩展名为.ldf 当数据库破坏时可以用事务日志还原数据 库内容

13. 文件组 • 文件组（File Group）是将多个数据文件集合起来形成的一个整体 • 主要文件组+次要文件组 • 一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用 • 日志文件不分组，它不能属于任何文件组

14. 2.2.1 SQL Server的系统数据库 系统信息 ： 磁盘空间 ；文件分配和使用 ；系统级的配置参 数；登录账号信息 ；SQL Server初始化信息； 系统中其他系统数据库和用户数据库的相关信息 Master Model数据库存储了所有用户数据库和Tempdb数 据库的创建模板 通过更改Model数据库的设置可以大大简化数据 库及其对象的创建设置工作 系统默认数据库 Model Msdb 存储计划信息以及与备份和还原相关的信息 Tempdb数据库用作系统的临时存储空间 存储临时表,临时存储过程和全局变量值 ，创建临 时表 ，存储用户利用游标说明所筛选出来的数据 Tempdb

15. 2.2.2 SQL Server的实例数据库 虚构的图书出版公司的基本情况 pubs • 重建实例数据库 • 安装目录\MSSQL\Install中： • Instpubs.sql • Instnwnd.sql 实例数据库 包含了一个公司的销售数据 Northwind

16. 2.3.3 创建用户数据库 • 用Enterprise Manager 创建数据库 • 用SQL命令创建数据库 CREATE DATABASE database_name [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ]

17. CREATE DATABASE Teach ON ( NAME=Teach_Data, FILENAME='D:\TeachData.mdf', SIZE=10, MAXSIZE=50, FILEGROWTH=5) LOG ON ( NAME=Teach_Log, FILENAME='D:\TeachLog.ldf', SIZE=5, MAXSIZE=25, FILEGROWTH=5) • [例2-1] 用SQL命令创建一个教学数据库Teach，数据文件的逻辑名称为Teach_Data，数据文件物理地存放在D：盘的根目录下，文件名为TeachData.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为Teach_Log，日志文件物理地存放在D：盘的根目录下，文件名为TeachLog.ldf，初始存储空间大小为10MB，最大存储空间为25MB，存储空间自动增长量为5MB。

18. 2.2.4 修改用户数据库 • 用Enterprise Manager修改数据库 • 用SQL命令修改数据库 ALTER DATABASE database_name { ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name] | ADD LOG FILE <filespec> [,...n] | REMOVE FILE logical_file_name [WITH DELETE] | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE <filespec> | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name } | SET < optionspec > [ ,...n ] [ WITH < termination > ] | COLLATE < collation_name > }

19. [例2-2] 修改Northwind数据库中的Northwind文件增容方式为一次增加2MB。 ALTER DATABASE Northwind MODIFY FILE ( NAME = Northwind, FILEGROWTH = 2mb )

20. 2.2.5 删除用户数据库 • 用Enterprise Manager删除数据库 • 用SQL命令删除数据库 DROP DATABASE database_name [,...n] [例2-3] 删除数据库Teach。 DROP DATABASE Teach

21. 2.2.6 查看数据库信息 • 用Enterprise Manager查看数据库信息 • 用系统存储过程显示数据库信息 用系统存储过程显示数据库结构 用系统存储过程显示文件信息 用系统存储过程显示文件组信息 Sp_helpdb [[@dbname=] 'name'] Sp_helpfile [[@filename =] 'name'] Sp_helpfilegroup [[@filegroupname =] 'name']

22. EXEC Sp_helpdb Northwind EXEC Sp_helpfile Northwind EXEC Sp_helpfilegroup

23. 2.4 创建与使用数据表 • 2.4.1 数据类型 bigint，int，smallint，tinyint 整数数据 numeric和decimal 精确数值 float和real 近似浮点数值 datetime与smalldatetime 日期时间数据

24. char、varchar、text 字符串数据 nchar、nvarchar与ntext Unicode字符串数据 binary、varbinary、image 二进制数据 money与smallmoney 货币数据 timestamp和uniqueidentifier 标记数据

25. 2.4.2 创建数据表 • 用Enterprise Manager创建数据表 相关属性定义 “字段名” “数据类型” 字段的“长度”、“精度”和“小数位数” “允许空” “默认值” 同一表中不许有重名字段 系统默认为NULL

26. 用SQL命令创建数据表 CREATE TABLE <表名> (<列定义>[{，<列定义>|<表约束>}]) [例2-4] 用SQL命令建立一个学生表S。 CREATE TABLE S ( SNo CHAR(6), SN VARCHAR(8), Sex CHAR(2) DEFAULT '男', Age INT, Dept VARCHAR(20)) <列名> <数据类型> [DEFAULT] [{<列约束>}] 缺省值为“男”

27. 2.4.3 定义数据表的约束 SQL Server的数据完整性机制 数据的完整性 约束（Constraint） 默认（Default） 规则（Rule） 触发器（Trigger） 存储过程（Stored Procedure） 正确性 有效性 相容性

28. 完整性约束的基本语法格式 [CONSTRAINT <约束名> ] <约束类型> NULL/NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

29. NULL/NOT NULL约束 • NULL表示“不知道”、“不确定”或“没有数据”的意思 • 主键列不允许出现空值 [CONSTRAINT <约束名> ][NULL|NOT NULL] [例2-5] 建立一个S表，对SNo字段进行NOT NULL约束。 CREATE TABLE S ( SNo CHAR(6) CONSTRAINT S_Cons NOT NULL, SN VARCHAR(8), Sex CHAR(2), Age INT, Dept VARCHAR(20)) 可省略约束名称 : SNo CHAR(6) NOT NULL

30. UNIQUE约束（惟一约束） • 指明基本表在某一列或多个列的组合上的取值必须惟一 • 在建立UNIQUE约束时，需要考虑以下几个因素： • 使用UNIQUE约束的字段允许为NULL值。 • 一个表中可以允许有多个UNIQUE约束。 • 可以把UNIQUE约束定义在多个字段上。 • UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引，缺省为非聚集索引。 • UNIQUE用于定义列约束 [CONSTRAINT <约束名>] UNIQUE • UNIQUE用于定义表约束 [CONSTRAINT <约束名>] UNIQUE（<列名>[{,<列名>}]）

31. SN_Uniq可以省略 SN CHAR(8) UNIQUE [例2-6] 建立一个S表，定义SN为惟一键。 CREATE TABLE S ( SNo CHAR(6), SN CHAR(8) CONSTRAINT SN_Uniq UNIQUE, Sex CHAR(2), Age INT, Dept VARCHAR(20)) [例2-7] 建立一个S表，定义SN+SEX为惟一键，此约束为表约束。 CREATE TABLE S ( SNo CHAR(6), SN CHAR(8) UNIQUE, Sex CHAR(2), Age INT, Dept VARCHAR(20), CONSTRAINT S_UNIQ UNIQUE(SN, Sex))

32. 不能为NULL 不能重复 • PRIMARY KEY约束（主键约束） • 用于定义基本表的主键，起惟一标识作用 • PRIMARY KEY与UNIQUE 的区别： • 一个基本表中只能有一个PRIMARY KEY，但可多个UNIQUE • 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL • 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL

33. PRIMARY KEY用于定义列约束 CONSTRAINT <约束名> PRIMARY KEY PRIMARY KEY用于定义表约束 [CONSTRAINT <约束名>] PRIMARY KEY (<列名>[{,<列名>}]) [例2-8] 建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键。 CREATE TABLE S ( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN CHAR(8), Sex CHAR(2), Age INT, Dept VARCHAR(20)) CREATE TABLE C ( CNo CHAR(5) CONSTRAINT C_Prim PRIMARY KEY, CN CHAR(20), CT INT)

34. [例2-9] 建立一个SC表，定义SNo+CNo为SC的主键。 CREATE TABLE SC ( SNo CHAR(5) NOT NULL, CNo CHAR(5) NOT NULL, Score NUMERIC(4,1), CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo))

35. FOREIGN KEY约束（外键约束） [CONSTRAINT<约束名>] FOREIGN KEY REFERENCES <主表名> (<列名>[{,<列名>}]) 主表 从表 引用 主键 外部键

36. [例2-10] 建立一个SC表，定义SNo,CNo为SC的外部键。 CREATE TABLE SC ( SNo CHAR(5) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo), CNo CHAR(5) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo), Score NUMERIC(4,1), CONSTRAINT S_C_Prim PRIMARY KEY (SNo,CNo));

37. CHECK约束 CHECK约束用来检查字段值所允许的范围 在建立CHECK约束时，需要考虑以下几个因素： • 一个表中可以定义多个CHECK约束。 • 每个字段只能定义一个CHECK约束。 • 在多个字段上定义的CHECK约束必须为表约束。 • 当执行INSERT、UNDATE语句时CHECK约束将验证数据。 [CONSTRAINT <约束名>] CHECK (<条件>)

38. [例2-11] 建立一个SC表，定义Score的取值范围为0～100之间。 CREATE TABLE SC ( SNo CHAR(5), CNo CHAR(5), Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score <=100)) [例2-12] 建立包含完整性定义的学生表。 CREATE TABLE S ( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN CHAR(8) CONSTRAINT SN_Cons NOT NULL, Sex CHAR(2) DEFAULT '男', Age INT CONSTRAINT Age_Cons NOT NULL CONSTRAINT Age_Chk CHECK (Age BETWEEN 15 AND 50), Dept CHAR(10) CONSTRAINT Dept_Cons NOT NULL)

39. 2.4.4 修改数据表 • 用Enterprise Manager 修改数据表的结构 • 用SQL命令修改数据表 ALTER TABLE <表名> ADD <列定义> | <完整性约束定义> ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型> [NULL|NOT NULL] ALTER TABLE<表名> DROP CONSTRAINT <约束名>

40. [例2-13] 在S表中增加一个班号列和住址列。 ALTER TABLE S ADD Class_No CHAR(6), Address CHAR(40) • 使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。 [例2-14] 在SC表中增加完整性约束定义，使Score在0～100之间。 ALTER TABLE SC ADD CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)

41. [例2-15] 把S表中的SN列加宽到10个字符。 ALTER TABLE S ALTER COLUMN SN CHAR(10) • 不能改变列名； • 不能将含有空值的列的定义修改为NOT NULL约束； • 若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型； • 只能修改NULL/NOT NULL约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。 [例2-16] 删除S表中的主键。 ALTER TABLE S DROP CONSTRAINT S_Prim

42. 2.4.5 删除基本表 • 用Enterprise Manager删除数据表 • 用SQL命令删除数据表 DROP TABLE <表名> 只能删除自己建立的表，不能删除其他用户所建的表

43. 2.4.6 查看数据表 • 查看数据表的属性 属性包括：数据表的名称，所有者，创建日期，文件组，记录的行数，数据表中的字段名称、结构和类型等。 • 查看数据表中的数据 在Enterprise Manager中，用右键单击要查看数据的表，从快捷菜单中选择“打开表”，再选择其子菜单中的“返回所有行” 。

44. 2.5 创建与使用索引 加快查询速度 保证行的惟一性 • 2.5.1 索引的作用 • 2.5.2 索引的分类 排列的结果存储在表中 只有一个 排列的结果不存储在表中 可以有多个 聚集索引与非聚集索引 聚集索引：查询速度快 非聚集索引：更新速度快 唯一索引 有UNIQUE，自动建立非聚集的惟一索引 有PRIMARY KEY，自动建立聚集索引 复合索引 将两个或多个字段组合起来建立的索引， 单独的字段允许有重复的值

45. 2.5.3 创建索引 • 用Enterprise Manager创建索引 • 用索引创建向导创建索引 • 直接创建索引 • 用SQL命令创建索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [次序] [{,<列名>}] [次序]…) 建立惟一索引 建立聚集索引 ASC或DESC，默认为ASC

46. [例2-18] 为表SC在SNo和CNo上建立惟一索引。 CREATE UNIQUE INDEX SCI ON SC(SNo,CNo) [例2-19] 为教师表T在TN上建立聚集索引。 CREATE CLUSTER INDEX TI ON T(TN) 注意： （1）改变表中的数据（如增加或删除记录）时，索引将自动更新。 （2）索引建立后，在查询使用该列时，系统将自动使用索引进行查询。 （3）索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。

47. 2.5.4 查看与修改索引 • 用Enterprise Manager查看和修改索引 • 用Sp_helpindex存储过程查看索引 Sp_helpindex [@objname =] 'name' [例2-20] 查看表SC的索引。 EXEC Sp_helpindex SC 表的名称

48. 用Sp_rename存储过程更改索引名称 Sp_rename '数据表名.原索引名', '原索引名' [例2-21] 更改T表中的索引TI名称为T_Index。 EXEC Sp_rename 'T.TI', 'T_Index', 'index'

49. 2.5.5 删除索引 不能删除由CREATE 或ALTER命令创建的索引，也不能删除系统表中的索引 • 用Enterprise Manager删除索引 • 用DROP INDEX命令删除索引 DROP INDEX数据表名.索引名 [例2-22] 删除表SC的索引SCI。 DROP INDEX SC.SCI

50. 2.6 数据查询 • 2.6.1 SELECT命令的格式与基本使用 SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]] 〈列名〉[AS 别名1] [{，〈列名〉[ AS 别名2]}] [INTO 新表名] FROM〈表名1或视图名1〉[[AS] 表1别名] [{，〈表名2或视图名2〉[[AS] 表2别名]}] [WHERE〈检索条件〉] [GROUP BY <列名1>[HAVING <条件表达式>]] [ORDER BY <列名2>[ASC|DESC]] 投影 选取