第3章 SQL 语言基础

null数据库原理与应用数据库原理与应用人民邮电出版社第3章 SQL语言基础及数据定义功能 第3章 SQL语言基础及数据定义功能 3.1 SQL语言概述 3.2 SQL语言支持的数据类型 3.3 数据定义功能 3.4 索引概述概述SQL（Structured Query Language）是用户操作关系数据库的通用语言。 它包含数据定义、数据查询、数据操作和数据控制等与数据库有关的全部功能。 SQL已经成为关系数据库的标准语言，现在所有的关系数据库管理系统都支持SQL。 3.1 SQL语言概述 3.1 SQL语言概述 3.1.1 SQL语言的发展 3.1.2 SQL语言的特点 3.1.3 SQL语言功能概述 3.1.1 SQL语言的发展3.1.1 SQL语言的发展1986年10月由美国ANSI 公布最早的SQL标准。 1989年4月，ISO提出了具备完整性特征的SQL，称为SQL-89（SQL1)。 1992年11月，ISO又公布了新的SQL标准，称为SQL-92（以上均为关系形式） （SQL2) 。 1999年颁布SQL-99（SQL3) ，是SQL92的扩展。3.1.2 SQL语言的特点3.1.2 SQL语言的特点1. 一体化 风格统一，功能全面 2. 高度非过程化 只需要描述“做什么” 3. 简洁 命令少，语法简单易懂 4. 使用方式多样 单独使用、嵌套使用3.1.3 SQL语言功能概述3.1.3 SQL语言功能概述四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。 3.2 SQL语言支持的数据类型3.2 SQL语言支持的数据类型数值型 字符串型 日期时间型 货币型数值型数值型准确型 整数 Bigint: 8字节， Int：4字节 Smallint：2字节， Tinyint：1字节 Bit：1位，存储1或0 小数 Numeric（p,q）或Decimal（p,q）， 其中：p为数字位长度，q：小数位长度。 近似型 Float：8字节 Real：4字节字符串型字符串型普通编码字符串类型 统一字符编码字符串类型 二进制字符串类型普通编码字符串类型普通编码字符串类型Char（n）:定长存储，n<=8000 Varchar（n）：不定长存储（按实际长度存储），长度最大不超过n , n<=8000 注：n 为字符个数 Text：存储大于8000字节的文本统一字符编码字符串类型统一字符编码字符串类型nchar（n）:定长存储，n<=4000 nvarchar（n）：不定长存储，长度最大不超过n , n<=4000 ntext：存储大于8000字节的文本 特点：每个字符占两个字节二进制字符串类型二进制字符串类型Binary(n)：固定长度，n <= 8000。 Varbinary(n)：可变长度，n <＝8000 。 注：n为二进制数据的字节数 image：大容量、可变长二进制字符数据，可用于存储文件。日期时间型日期时间型Datetime：8字节，年月日时分秒毫秒 （例：‘2008/02/03 10:30:00.000’ ） SmallDateTime：4字节，年月日时分 （例：‘2008/02/03 10:30:00’ ）日期时间型日期时间型日期的输入格式： 英文数字格式：oct 25 2011 数字加分隔符格式：2011-10-25 或2011/10/25 纯数字格式： 20111025时间的输入格式： 12小时格式： 2011-10-25 4:35:20 pm 24小时格式： 2011-10-25 16:35:20货币类型货币类型Money：8个字节，精确到货币单位的千分之十。 Smallmoney：4个字节，精确到货币单位的千分之十。 限制到小数点后 4 位。 可以带有适当的货币符号。例如，100 英镑可 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示为 £100。3.3 数据定义功能 3.3 数据定义功能 3.3.1 基本表 3.3.2 数据完整性约束 3.3.1 基本表3.3.1 基本表定义基本表 修改基本表 删除基本表定义基本表定义基本表使用CREATE TABLE语句实现， 语法格式： CREATE TABLE <表名>（ <列名> <数据类型> [列级完整性约束定义] {, <列名> <数据类型> [列级完整性约束定义］ … } [, 表级完整性约束定义 ] ） 在列级完整性约束定义处可以定义的约束在列级完整性约束定义处可以定义的约束NOT NULL：限制列取值非空。 DEFAULT：给定列的默认值。 UNIQUE：限制列取值不重。 CHECK：限制列的取值范围。 PRIMARY KEY：指定本列为主键。 FOREIGN KEY：定义本列为引用其他表的外键。使用形式为： ［FOREIGN KEY（<外键列名>）］ REFERENCES <外表名>（<外表列名>） 几点说明几点说明NOT NULL和DEFAULT只能是列级完整性约束； 其他约束均可在表级完整性约束处定义。 注意以下几点： 如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义； 如果表的主键或唯一值约束由多个列组成，则也只能在表级完整性约束处定义，并将相关列用括号括起来，如：PRIMARY KEY（列1｛[，列2 ] …｝）； 如果在表级完整性约束处定义外键，则“FOREIGN KEY (<外键列名>)”部分不能省。约束定义约束定义1 列取值非空约束 <列名> <类型> NOT NULL 例: sname char(10) NOT NULL 2 列取默认值约束 <列名> <类型> DEFAULT <默认取值> 例: 工资 numeric(5,1) DEFAULT 2000约束定义约束定义3 唯一值约束 在定义列时定义唯一值（仅用于单列） <列名> <类型> UNIQUE 例: 产品名 varchar(20) UNIQUE 在定义完列时定义唯一值（用于单列或多列） UNIQUE （<列名序列>） 例: UNIQUE (订单编号, 商品名称) 约束定义约束定义4 CHECK约束 在定义列时定义CHECK约束（仅针对该列） <列名> <类型> CHECK (<约束>) 例: 最低工资 numeric(5,1) CHECK(最低工资>1000) 在定义完列时定义CHECK约束（用于单列或多列） <列名> <类型> CHECK (<约束>) 例: 最低工资 numeric(5,1),最高工资 numeric(5,1), CHECK(最高工资>=最低工资) 约束定义（续）约束定义（续）5 表主键约束 在定义列时定义主键（仅用于单列主键） 列定义 PRIMARY KEY 例： SNO char(7) PRIMARY KEY 在定义完列时定义主键（用于单列或多列主键） PRIMARY KEY （<列名序列>） 例： PRIMARY KEY(SNO) PRIMARY KEY(SNO，CNO)约束定义（续）约束定义（续）指明本表外键列引用的表及表中的主键列。 6 外键引用约束[ FOREIGN KEY （<本表列名>）] REFERENCES <外表名>（<外表主键列名>）例： FOREIGN KEY （sno） REFERENCES 学生表（sno） Student表结构Student表结构CREATE TABLE Student ( Sno CHAR(7) PRIMARY KEY, Sname NCHAR(5) NOT NULL, Ssex NCHAR(1), Sage TINYINT, Sdept NVARCHAR(20) )Student表结构Student表结构CREATE TABLE Student ( Sno CHAR(7), Sname NCHAR(5) NOT NULL, Ssex NCHAR(1), Sage TINYINT, Sdept NVARCHAR(20), PRIMARY KEY (Sno) )Course表结构Course表结构CREATE TABLE Course ( Cno CHAR(6) PRIMARY KEY, Cname NVARCHAR(20) NOT NULL, Ccredit TINYINT, Semester TINYINT )Course表结构Course表结构CREATE TABLE Course ( Cno CHAR(6), Cname NVARCHAR(20) NOT NULL, Ccredit TINYINT, Semester TINYINT, PRIMARY KEY (Cno) )SC表结构SC表结构CREATE TABLE SC ( Sno CHAR(7) NOT NULL REFERENCES Student ( Sno ), Cno CHAR(6) NOT NULL REFERENCES Course ( Cno ), Grade TINYINT, PRIMARY KEY ( Sno, Cno ), ) SC表结构SC表结构CREATE TABLE SC ( Sno CHAR(7) NOT NULL, Cno CHAR(6) NOT NULL, Grade TINYINT, PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) ) 修改基本表结修改基本表结在定义基本表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTER TABLE语句实现。 ALTER TABLE语句可以对表添加列、删除列、修改列的定义，也可以添加和删除约束。ALTER TABLE语句语法ALTER TABLE语句语法ALTER TABLE <表名> [ ALTER COLUMN <列名> <新数据类型>] | [ ADD <列名> <数据类型> [约束] | [ DROP COLUMN <列名> ] | [ADD [constraint 约束名] 约束定义 | [DROP [constraint] <约束名> ] 示例示例例1．为SC表添加“修课类别”列，此列的列名为Type，数据类型为NCHAR(2)，允许空。 ALTER TABLE SC ADD Type NCHAR(2) NULL示例示例例2．将新添加的Type列的数据类型改为NCHAR(4)。 ALTER TABLE SC ALTER COLUMN Type NCHAR(4)示例示例例3.删除SC表的Type列。 ALTER TABLE SC DROP COLUMN Type 删除基本表删除基本表当确信不再需要某个表时，可以将其删除。 删除表的语句格式为： DROP TABLE <表名> { [, <表名> ] … } 例：删除test表： DROP TABLE test3.3.2 数据完整性约束 3.3.2 数据完整性约束 完整性约束条件的作用对象 实现数据完整性 完整性约束条件的作用对象完整性约束条件的作用对象完整性检查是围绕完整性约束条件进行的，因此，完整性约束条件是完整性控制机制的核心。完整性约束条件的作用对象可以是表、元组和列。 列级约束 元组约束 关系约束列级约束列级约束列级约束主要是对列的类型、取值范围、精度等的约束，具体包括： 对数据类型的约束：包括数据类型、长度、精度等。 对数据格式的约束：如规定学号的前两位表示学生的入学年份，第三位表示系的编号，第四位表示专业编号，第五位代表班的编号等等。 对取值范围的约束：如学生的成绩取值范围为0～100。 对空值的约束。 元组约束元组约束元组的约束是元组中各个字段之间的联系的约束， 如： 开始日期小于结束日期 某项工作的最低工资小于最高工资关系约束关系约束指若干元组之间、关系之间的联系的约束。 比如: 学生表中学号的取值不能重复也不能取空值， 学生修课表中的学号的取值受学生表中的学号取值的约束 实现数据完整性 实现数据完整性 实现数据完整性一般在服务器端完成。 在服务器端实现数据完整性的方法主要有两种。 一种是在定义表时声明数据完整性，采用这种方法时可以在定义表的同时定义完整性约束，也可以在定义表之后添加这些约束。 一种是在服务器端编写触发器来实现数据完整性。 实现约束实现约束1. PRIMARY KEY 约束 2. UNIQUE 约束 3. FOREIGN KEY 约束 4. DEFAULT 约束 5. CHECK 约束ALTER TABLE语句语法ALTER TABLE语句语法ALTER TABLE <表名> [ ALTER COLUMN <列名> <新数据类型>] | [ ADD <列名> <数据类型> [约束] | [ DROP COLUMN <列名> ] | [ADD [constraint 约束名] 约束定义 | [DROP [constraint] <约束名> ] 工作表结构工作表结构CREATE TABLE 工作表( 工作编号 CHAR(6) NOT NULL, 最低工资 NUMERIC(7,2), 最高工资 NUMERIC(7,2) )职工表结构职工表结构CREATE TABLE 职工表( 职工编号 CHAR(7) NOT NULL, 姓名 NCHAR(5) NOT NULL, 工作编号 CHAR(6), 工资 NUMERIC(7,2), 电话号码 CHAR(8) )PRIMARY KEY 约束PRIMARY KEY 约束保证实体完整性。 每个表有且只有一个PRIMARY KEY 约束。 格式： ALTER TABLE 表名 ADD [ CONSTAINT 约束名 ] PRIMARY KEY ( 列名 [, … n ] )示例示例例：对职工表和工作表分别添加主键约束。 ALTER TABLE 职工表 ADD CONSTRAINT PK_EMP PRIMARY KEY (职工编号) ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY (工作编号)UNIQUE 约束UNIQUE 约束确保在非主键列中不输入重复值。 应用在客观具有唯一性质的列上，如身份证号、社会保险号等。 注意事项： 允许有一个空值。 在一个表中可以定义多个UNIQUE约束。 可以在一个列或多个列上定义UNIQUE约束。添加UNIQUE约束添加UNIQUE约束语法格式： ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] UNIQUE（<列名> [, … n] ） 为职工表的“电话号码”列添加UNIQUE约束。 ALTER TABLE 职工表 ADD CONSTRAINT UK_SID UNIQUE（电话号码）FOREIGN KEY约束FOREIGN KEY约束实现了参照完整性。 外键所引用的列必须是有PRIMARY KEY约束或UNIQUE约束的列。 格式： ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] FOREIGN KEY（<列名>） REFERENCES 引用表名（<列名>）示例示例例．为职工表的工作编号添加外键引用约束，此列引用工作表的工作编号列。 ALTER TABLE 职工表 ADD CONSTRAINT FK_job_id FOREIGN KEY（工作编号） REFERENCES 工作表（工作编号） DEFAULT约束 DEFAULT约束用于提供列的默认值， 一个列只能有一个默认值约束，一个默认值约束只能用在一个列上。 在向表中插入数据时系统检查DEFAULT约束。 格式： ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] DEFAULT 默认值 FOR 列名示例示例例．定义职工表的工资的默认值为1000。 ALTER TABLE 职工表 ADD CONSTRAINT DF_SALARY DEFAULT 1000 FOR 工资CHECK约束CHECK约束用于将列的取值限制在指定的范围内，使数据库中存放的值都是有意义的。 在执行INSERT和UPDATE语句时系统自动检查CHECK约束。 可约束同一个表中多个列之间的取值关系。 格式： ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] CHECK （逻辑表达式）示例1示例1例1．添加限制最工作表中低工资必须大于等于800元的约束。 ALTER TABLE 工作表 ADD CONSTRAINT CHK_Salary CHECK ( 最低工资 >= 800 )示例2示例2例2．限制工作表的最高工资大于等于最低工资的约束 。 ALTER TABLE 工作表 ADD CONSTRAINT CHK_Job_Salary CHECK ( 最高工资 >= 最低工资 ) 示例3示例3添加限制职工表的电话号码的每1位的取值均是0～9的数字的约束。 ALTER TABLE 职工表 ADD CONSTRAINT CHK_Emp_Phone CHECK (电话号码 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') 工作表的完整定义工作表的完整定义CREATE TABLE 工作表( 工作编号 CHAR(6) NOT NULL PRIMARY KEY, 最低工资 NUMERIC(7,2) CHECK (最低工资 >= 800), 最高工资 NUMERIC(7,2), CHECK (最高工资 >= 最低工资) )职工表的完整定义职工表的完整定义CREATE TABLE 职工表( 职工编号 CHAR(7) NOT NULL PRIMARY KEY, 姓名 NCHAR(5) NOT NULL, 工作编号 CHAR(6), 工资 NUMERIC(7,2) DEFAULT 1000, 电话号码 CHAR(8) UNIQUE , CHECK (电话号码 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), FOREIGN KEY(工作编号) REFERENCES工作表(工作编号) )3.4 索引3.4 索引3.4.1 基本概念 3.4.2 索引分类 3.4.3 创建和删除索引基本概念基本概念索引与图书中的 目录 工贸企业有限空间作业目录特种设备作业人员作业种类与目录特种设备作业人员目录1类医疗器械目录高值医用耗材参考目录 类似。 图书的目录注明了各部分内容所对应的页码，而数据库中的索引是一个表中所包含的值的列表，其中注明了表中的各行数据所在的存储位置。 在数据库中，索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。基本概念基本概念可以为表中的单个列建立索引，也可以为一组列建立索引。 索引由索引项组成，索引项由来自表中每一行的一个或多个列（称为索引关键字）组成。 当在多个列上建立索引时，系统按索引列出现的先后顺序对索引列进行排序。 索引及数据间对应关系 索引及数据间对应关系 索引分类 索引分类 聚集索引 非聚集索引索引分类 索引分类 聚集索引 数据按索引列进行物理排序 一个表只能包含一个聚集索引 对经常要搜索连续范围的值的列特别有效 不适用于建立在频繁更改的列上索引分类 索引分类 非聚集索引 与图书的目录类似。 数据存储在一个地方，索引存储在另一个地方，索引带有指向数据存储位置的指针。 索引中的索引项按索引键值顺序存储。 null非聚集索引可以在有聚集索引的表和无聚集索引的表上定义。 可以在一个表上建立多个非聚集索引（SQL Server 2000支持在一个表上最多建立249个非聚集索引）。 可以为在表中查找数据时常用的每个列创建一个非聚集索引。 唯一索引唯一索引唯一索引可以确保索引列不包含重复的值。 在多列唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的。 聚集索引和非聚集索引都可以是唯一的。 创建索引 创建索引 CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX <索引名> ON <表名> (<列名> [<顺序> [, <列名>[<顺序>] …]] UNIQUE：创建唯一索引。 CLUSTERED：创建聚集索引。 NONCLUSTERED：创建非聚集索引。 “顺序”包括ASC（升序）和DESC（降序），表示索引键值的排序顺序，默认为ASC。 默认是创建非聚集索引。 示例示例例1 在Student表的Sname列上创建非聚集索引。 CREATE INDEX Sname_ind ON Student ( Sname ) 例2 在职工表的“电话号码”列上创建一个唯一的聚集索引。 CREATE UNIQUE CLUSTERED INDEX Phone_ind ON 职工表(电话号码)示例（续）示例（续）例3 在职工表的“姓名”列上创建一个非聚集索引， 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 索引键值按“姓名”升序排序。 CREATE INDEX EName_ind ON 职工表 (姓名 ASC) 例4 在last_name和first_name两个列（假设在authors表中）上创建一个非聚集复合索引。 CREATE INDEX TwoName_ind ON authors (last_name, first_name)删除索引删除索引索引一经建立，就由数据库管理系统自动使用和维护，不需要用户干预。 如果频繁地对数据进行增加、删除和更改操作，则系统会花费很多时间来维护索引，这会降低数据的修改效率； 存储索引需要占用额外的空间。 删除索引的语法格式： DROP INDEX <表名>.<索引名> 例5 删除职工表“姓名”列上的EName_ind索引。 DROP INDEX 职工表.EName_ind