关系数据库语言SQL(4).ppt

数据库原理,信息工程学院 宋莉,2,第3章 关系数据库语言SQL,学习要求 掌握SQL语言的基本功能 使用SQL语言产生和修改数据库表 熟练掌握SQL语言操纵数据库 重点 熟练正确的使用SQL完成对数据库的查询、插入、删除、更新操作 在使用具体的SQL时，有意识地和关系代数、关系演算等语言进行比较，了解他们各自的特点,3,第3章 关系数据库语言SQL,3.1 SQL概述 3.2 SQL语言的数据类型 3.3 SQL中数据定义 3.4 SQL中数据操纵 3.5 SQL中视图的定义和操作,4,3.1 SQL概述,3.1.1 SQL 的产生与发展 3.1.2 SQL的特点 3.1.3 SQL的基本概念,5,3.1.1 SQL 的产生与发展,SQL语言的发展 SQL名为结构化查询语言, 实际功能包括数据定义、数据操纵和数据控制。 SQL标准的制定使得几乎所有的数据库厂家都采用SQL语言作为其数据库语言。但各家又在SQL标准的基础上进行扩充，形成自己的语言。 SQL Server扩充SQL标准形成Transact-SQL，简称T-SQL。,6,3.1.1 SQL 的产生与发展,1974年由Boyce和Chamberlin提出，在IBM公司的System R上实现。 1986年被美国国家标准局（ANSI）批准为关系数据库语言的美国标准。 1987年国际标准化组织（ISO）通过这一标准,7,3.1.2 SQL的特点,1.综合统一 2.高度非过程化 3.面向集合的操作方式 4.以同一种语法结构提供多种使用方式 5.语言简洁，易学易用,8,SQL的特点,Create Drop Alter Select Delete Insert Update Grant Revoke,数据定义,数据操纵,数据控制,功能极强，完成核心功能只用了9个动词,高度非过程化,面向集合的操作方式,以同一种语法结构提供多种使用方式,9,3.1.3 SQL的基本概念,SQL支持关系数据库三级模式结构 外模式对应视图（View）和部分基本表（Base Table） 模式对应基本表 内模式对应存储文件（Stored File）,10,3.1.3 SQL的基本概念,11,3.1.3 SQL的基本概念,基本表 本身独立存在的表 SQL中一个关系就对应一个基本表 一个(或多个)基本表对应一个存储文件 一个表可以带若干索引 存储文件 逻辑结构组成了关系数据库的内模式 物理结构是任意的，对用户透明 视图 从一个或几个基本表导出的表 数据库中只存放视图的定义而不存放视图对应的数据 视图是一个虚表 用户可以在视图上再定义视图,12,第3章 关系数据库语言SQL,3.1 SQL概述 3.2 SQL语言的数据类型 3.3 SQL中数据定义 3.4 SQL中数据操纵 3.5 SQL中视图的定义和操作,13,3.2 SQL语言的数据类型,SQL语言与其它计算机语言一样，有自己的词法和语法。关系模式中的所有的属性都必须指定数据类型。 可以使用的数据类型包括： SQL Server系统提供的基本数据类型， 基于系统数据类型创建的用户自定义数据类型。,14,3.2.1基本数据类型,二进制数据： 二进制数据由十六进制数表示 包括Binary、Varbinary（最长8K）、Image（最长231 1）三种。 二进制数据类型常用于存储图象等数据。 字符型数据类型： 字符数据由字母、符号和数字等组成 在输入字符数据时应将数据引在引号内 字符类型包括: Char、Varchar、Text,15,3.2.1基本数据类型,Unicode(统一字符编码标准)数据类型 ： 有nChar、nVarchar和nText三种 每个Unicode字符的存储长度为2字节。 日期和时间数据类型 ： datetime (8字节） 取值范围:（1/1/175312/31/9999） smalldatetime (4字节） 取值范围: （ 1/1/19006/6/2079）,16,3.2.1基本数据类型,数字数据类型： 整数型：有Smallint、 Int、 Bigint、和Tinyint。 精确数值型 ： decimal(p,s) numeric(p,s) 近似数值型 ： 以 float 和 real 数据类型存储，数据的精度由二进制数字系统的精度决定。,17,3.2.1基本数据类型,货币数据类型 ： money（8 个字节） 取值范围: （-263 263 -1 ） Smallmoney （4 个字节） 取值范围: （-214,748.3648 214,748.3647） 特殊数据类型 ： Bit（位数据类型） Timestamp（时间戳数据类型） Uniqueidentifier（唯一标识符数据类型） sql_variant类型 table类型,18,3.2.2用户自定义数据类型,用户定义的数据类型的功能： 利用用户定义的数据类型可以使在不同表中重复出现的各列具有相同的特性、使相似的数据种类标准化。 可以将默认和规则绑定到用户定义的数据类型上，那么该规则和默认就可以约束使用此数据类型的所有列。,19,3.2.2用户自定义数据类型,创建用户定义的数据类型 使用企业管理器来定义 使用系统存储过程来定义SP_ADDTYPE 格式： 创建用户定义数据类型 EXEC sp_addtype type_name,phystype ,nulltype|identity 重新命名用户定义的数据类型 EXEC RENAME 旧类型名，新类型名 删除用户定义的数据类型 EXEC SP_DROPTYPE 类型名,20,3.2.2用户自定义数据类型,例1: 在“教学管理”数据库中创建一个用户定义数据类型telephone, 然后删除 USE 教学管理 EXEC sp_addtype telephone, varchar(20),null EXEC sp_droptype telephone,注意: 创建用户定义数据类型,必须先选择需要创建用户数据类型的数据库 不能删除正在被表或其他数据库对象使用的用户定义数据类型。,21,3.2.3 SQL语句格式符号,大括号 ： 大括号中的内容为必选参数，其中可有多个选项，各选项之间用竖线分隔，用户必须选择其中的一项。 方括号 ： 方括号中的内容为可选项，用户根据需要选用。 竖线| ： 表示参数之间或的关系。 省略号“”： 表示重复前面的语法单元。 尖括号： 表示下面有子句定义。,22,第3章 关系数据库语言SQL,3.1 SQL概述 3.2 SQL语言的数据类型 3.3 SQL中数据定义 3.4 SQL中数据操纵 3.5 SQL中视图的定义和操作,23,3.3 SQL数据定义语言DDL,3.3.1 数据库的定义、修改和删除 3.3.2 基本表的定义、修改和删除 3.3.3 创建和管理索引 3.3.4 模式的定义和删除,24,3.3.1 数据库的定义、修改和删除,1.数据库的定义 2.数据库的修改 3.数据库的删除,25,1. 创建数据库,用户必须拥有创建数据库的权限才能在SERVER端创建数据库。创建数据库的方法： 用Enterprise Manager创建数据库 用Create Database Wizard创建数据库 在Query Analyzer中用Transact_SQL语句创建数据库,26,用Enterprise Manager建库,进入Enterprise Manager界面后： 展开并选择服务器组 展开并选择要建立数据库的服务器 选中“数据库” 按右键选择“新建数据库”,27,输入数据库名称,28,打开数据文件,29,日志文件位置、大小,打开事务日志,30,创建数据库的语句格式,CREATE DATABASE database_name ON PRIMARY ,n , ,n LOG ON ,n FOR LOAD FOR ATTACH : = ( NAME = logical_file_name, FILENAME = os_file_name ,SIZE = size ,MAXSIZE = max_size UNLIMITED ,FILEGROWTH = growth_increment) ,n : = FILEGROUP filegroup_name ,n,31,创建用户数据库示例,创建“教学”数据库 CREATE DATABASE 教学 ON ( NAME = 教学_dat, FILENAME = e:教学.mdf, SIZE = 2, MAXSIZE = 10, FILEGROWTH = 1) LOG ON ( NAME = 教学_ log, FILENAME = e:教学.ldf, SIZE = 5MB, MAXSIZE = 15MB, FILEGROWTH = 2MB ),32,说明,SQL Server数据库中的数据在逻辑上被组织成一系列对象，当一个用户连接到数据库后，他所看到的是这些逻辑对象，而不是物理的数据库文件。 在用户创建一个新的数据库时，仅仅是创建了一个空壳，必须在其中创建对象（如表等）才能使用。,33,说明,创建新数据库时，SQL Server使用MODEL数据库作为模板。MODEL数据库包含了用于管理所有数据库中的对象的系统表。 要创建数据库，用户必须是sysadmin或dbcreator服务器角色的成员，或被明确赋予了执行create database语句的权限。,34,说明,建数据库的用户将成为该数据库的所有者。 在一个服务器上，最多可以创建 32,767 个数据库。 数据库名称必须遵循标识符规则。 当新的数据库创建时，SQL Server会自动更新“master”数据库的“sysdatabase”系统表。,35,2. 删除数据库,当确认一个数据库没有需要时，才可以删除该数据库。 删除数据库的方法： （1）使用企业管理器删除数据库 （2）使用T-SQL语句删除数据库 语句格式： DROP DATABASE database_name ,.n ,36,说明：,1、当用户正在使用该数据库时，不能被删除 2、当数据库正在被恢复时，不能被删除 3、当数据库正在参与复制时，不能被删除,37,3. 修改数据库,创建数据库后，可以对其原始定义进行更改。更改包括： 扩充分配给数据库的数据或事务日志空间 收缩分配给数据库的数据或事务日志空间 添加或删除数据和事务日志文件 创建文件组 创建默认文件组 更改数据库的配置设置 ,38,修改数据库的语句格式,ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE ,39,修改数据库的例子,例: 增加“教学”数据库中数据文件的大小为20MB USE 教学 GO ALTER DATABASE 教学 MODIFY FILE ( NAME =教学_dat, SIZE = 20MB) 注意： 在一个ALTER DATABASE 语句中只能实现一种修改数据库的操作,40,修改数据库的例子,例：在教学数据库创建一个名为TestGROUP的文件组 ALTER DATABASE 教学 ADD FILEGROUP TestGROUP,41,3.3 SQL数据定义语言DDL,3.3.1 数据库的定义、修改和删除 3.3.2 基本表的定义、修改和删除 3.3.3 创建和管理索引 3.3.4 模式的定义和删除,42,3.3.2 基本表的定义、修改和删除,1.表的创建 2.表的修改 3.表的删除,1. 表的创建,设计时应注意： 表的各列及每一列的数据类型、列宽 哪些列允许空值。（空值表示未知、不可用或将在以后添加的数据。） 是否要使用以及何时使用约束、默认设置或规则 所需索引的类型，哪里需要索引 哪些列是主键，哪些是外键,43,44,1. 表的创建,例如，需要在“教学”数据库创建一学生情况表，设计出以下表结构：,45,1. 表的创建,（1）使用企业管理器创建表： 选择要新建表的数据库，按右键，选择新建/表 （2）使用SQL语句创建表,46,（1）使用企业管理器创建表,47,（1）使用企业管理器创建表,48,（2）使用SQL语句创建表,表的定义格式： CREATE TABLE 表名 （列名 数据类型缺省值列级约束 ,列名 数据类型缺省值列级约束. ，UNIQUE（列名，列名.） ，PRIMARY KEY（列名，列名） ，FOREIGN KEY （列名，列名） REFERENCES 表名（列名，列名） ,CKECK (条件) ),49,表的定义格式,缺省值(DEFAULT):指定列的缺省值； NOT NULL：列值不能为空值； UNIQUE：取值唯一的列名； PRIMARY KEY：主关键字（主码）列名； FOREIGN KEY：引用外部码的列名； REFERENCES：被引用的外部表的表名和列名 ； CKECK ：指定表级约束条件。,如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。,50,Create Table语句实例,例：建立“教学”数据库中的三个关系表 实例数据库： 设“教学”数据库中的三个关系为： 学生关系: 学生情况表（学号，姓名，性别，年龄， 院系） 课程关系: 课程设置表（课号，课名，先修课号，学分） 选课成绩关系 学生成绩表（学号，课号，成绩）,51,Create Table语句实例,1). 创建学生情况表 CREATE TABLE 学生情况表 ( 学号 CHAR(6) NOT NULL PRIMARY KEY CLUSTERED, 姓名 CHAR(8) NOT NULL, 年龄 SMALLINT, 性别 CHAR(2) CONSTRAINT CK_性别 CHECK (性别=男 or 性别=女), 院系 VARCHAR(20) ),52,Create Table语句实例,2). 创建课程设置表 CREATE TABLE 课程设置表 ( 课号 CHAR(5) PRIMARY KEY, 课名 VARCHAR(20) NOT NULL, 先修课号 CHAR(5), 学分 CHAR(5) ),53,Create Table语句实例,3). 创建学生成绩表 CREATE TABLE 学生成绩表1( 学号 CHAR(6), 课号 CHAR(5), 成绩 SMALLINT, CONSTRAINT PK_成绩 PRIMARY KEY (学号,课号), CONSTRAINT FK_学号 FOREIGN KEY(学号) REFERENCES 学生情况表1 (学号), CONSTRAINT FK_课号 FOREIGN KEY(课号) REFERENCES 课程设置表1 (课号) , CONSTRAINT CK_grade CHECK(成绩 BETWEEN 0 AND 100) ),54,2. 表的修改,（1）使用企业管理器修改： 选中要修改的表，按右键，选择设计表 （2）使用SQL语句修改,55,（2）使用SQL语句修改,增加、删除、修改列定义或完整性约束条件，其格式： ALTER TABLE 表名 ADD子句 增加新的列定义或列说明 DROP子句 删除表中的列或列说明 ADD CONSTRAINT子句 增加约束 DROP CONSTRAINT子句 删除约束,56,修改表结构需注意,当表名改变以后，与此相关的某些对象如视图、存储过程将无效。 当为表增加一新列时，应使新增加的列有默认值或允许为空。 在改变列的数据类型时，有下列要求： 原数据类型必须能够转换为新数据类型 新类型不能为timestamp类型 如果被修改的列是identity列，则新数据类型必须是有效的identity数据类型。,57,ALTER TABLE语句实例,例. 在学生情况表中增加班级列、入学时间列 ALTER TABLE 学生情况表1 ADD 班级 CHAR(5) NULL, 入学时间 smalldatetime NULL,58,ALTER TABLE语句实例,例.在学生成绩表中增加一个名为编号的自动编号列 ALTER TABLE 学生成绩表1 ADD 自动编号 int IDENTITY,注意：对于每个表只能创建一个标识列,59,ALTER TABLE语句实例,例: 在学生情况表中增加入学时间列的默认值 ALTER TABLE 学生情况表 WITH NOCHECK ADD CONSTRAINT DF_Sentrance DEFAULT (getdate() FOR 入学时间,60,ALTER TABLE语句实例,例. 删除学生情况表中入学时间列 ALTER TABLE 学生情况表 DROP DF_Sentrance, COLUMN 入学时间,注意:如果要删除或修改的属性列上有约束（默认值，完整性）的定义,必须同时删除列和约束,才能删除掉列,61,ALTER TABLE语句实例,例. 将课程设置表中学分属性的数据类型修改为INT ALTER TABLE 课程设置表 ALTER COLUMN 学分 INT,注意:如果要修改的列上有约束的定义,必须首先删除列约束,然后修改列定义，再添加列约束定义,62,ALTER TABLE语句实例,例. 修改学生成绩表的参照完整性约束，使得当在学生情况表中删除一个记录（或更新一个记录的主码值）时，学生成绩表中相应外码值的记录也被级联删除（或更新）,63,ALTER TABLE语句实例,ALTER TABLE 学生成绩表 DROP CONSTRAINT FK_学号 ALTER TABLE 学生成绩表 ADD CONSTRAINT FK_学号 FOREIGN KEY(学号) REFERENCES 学生情况表 (学号) ON DELETE CASCADE ON UPDATE CASCADE,注意:如果要修改约束的定义,必须先删除后增加,64,3.表的删除,（1）使用企业管理器删除表 （2）使用SQL删除表,65,使用SQL删除表,删除表DROP DROP TABLE table_name 删除表，将基本表的定义、表中的数据、索引，以及由此表导出的视图定义都被删除。,66,DROP TABLE语句实例,例如，删除学生情况表 DROP TABLE 学生成绩表 DROP TABLE 学生情况表,注意:要删除带有参照完整性约束的被参照表，必须先删除参照表,67,3.3 SQL数据定义语言DDL,3.3.1 数据库的定义、修改和删除 3.3.2 基本表的定义、修改和删除 3.3.3 创建和管理索引 3.3.4 模式的定义和删除,68,3.3.3 创建和管理索引,1 索引的概念 2 索引的分类 3 索引的创建 4 删除索引,69,1 索引的概念,索引是关系数据库的内部实现技术，属于内模式的范畴 RDBMS中索引一般采用B+树、HASH索引来实现 B+树索引具有动态平衡的优点 HASH索引具有查找速度快的特点 采用B+树，还是HASH索引则由具体的RDBMS来决定,70,1 索引的概念,索引(INDEX)是提高查询效率的有效措施。当一个关系的记录数很多时，为查询符合条件的元组要对整个关系进行扫描。 例如： SELECT * FROM 学生情况表 WHERE 姓名=赵利 假定学生关系包含20000个记录，为查询一个记录，要对20000个记录逐一检查核对。 定义在某关系属性上的索引是一个数据结构，可显著提高依据该属性值的查询效率。,71,1 索引的概念,优点： 加速数据的检索速度； 强制实施行的唯一性； 实现表与表之间的参照完整性； 加速连接、OREDR BY和GROUP BY子句对数据的检索； 查询优化器依赖于索引起作用。,72,1 索引的概念,缺点： 创建索引要花费时间和占用存储空间； 维护索引是一个复杂的算法，需要一定的时间 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度,73,1 索引的概念,创建索引的一般原则 创建索引一般遵循一定的指导原则，并非多多益善 考虑创建索引的列 主键列 连接中频繁使用的列（外键） 经常需要根据条件搜索的列 经常需要排序的列,74,1 索引的概念,不考虑创建索引的列 在查询中很少使用和参考的列 只有很少值的列 更新多而检索少的列 定义为TEXT、IMAGE和BIT数据类型的列 小表（行数很少的表）,75,1 索引的概念,建立索引 DBA或表的属主（即建立表的人）根据需要建立 有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE 维护索引 DBMS自动完成 使用索引 DBMS自动选择是否使用索引以及使用哪些索引,76,3.3.3 创建和管理索引,1 索引的概念 2 索引的分类 3 索引的创建 4 删除索引,77,2 索引的分类,按照索引的组织方式 “聚集索引” “非聚集索引” 按照实现的功能 “唯一索引” 既可以采用聚集索引的结构，又可以采用非聚集索引的结构 由两列或更多列组成的索引称为“复合索引”,78,聚集索引的特征 ：,聚集索引确定表中数据的物理顺序。使得数据表中行的物理存储顺序与索引中行的物理顺序完全相同。 一个数据库中可以建立多个聚集索引，一个表中只能建立一个聚集索引。 在默认情况下，SQL SERVER为PRIMARY KEY（主键）约束所建立的索引为聚集索引。 应在创建任何非聚集索引之前创建。以免引起SQL SERVER重构非聚集索引。,79,非聚集索引的特征 ：,非聚集索引的数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。 索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。 如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。 可以为在表中查找数据时常用的列创建一个非聚集索引。,80,唯一索引的特征,唯一索引可以确保索引列不包含重复的值。 在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。 在创建主键约束和唯一约束时自动创建。 在同一个列组合上创建唯一索引而不是非唯一索引可为查询优化器提供附加信息；所以最好创建唯一索引。,81,复合索引的特征,对一个表中的两列或多列的组合进行索引 复合索引的最大列数为16，且这些列必须位于同一个表中。 在使用复合索引检索时，把被索引列（两列或多列）作为一个单元进行搜索。 复合索引中的列顺序可以与表中的列顺序不同。,82,3.3.3 创建和管理索引,1 索引的概念 2 索引的分类 3 索引的创建 4 删除索引,83,3 索引的创建,在企业管理器中创建索引 使用CREATE INDEX语句,84,在企业管理器中创建索引,企业管理器-数据库-表对象-表名称-设计表-管理索引-新建索引,85,使用CREATE INDEX语句,语句格式： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名 ON表名(列名ASC|DESC，列名 ASC|DESC) ON FILEGROUP ,86,3 索引的创建,解释: UNIQUE: 唯一索引, 既不允许存在索引值相同的两行 CLUSTERED| NONCLUSTERED :聚集（非聚集）索引。其中行的物理排序与索引排序相同。一个表只允许创建一个聚集索引。 ASC|DESC：升序|降序,87,创建索引的例子,例：根据学生情况表的院系列创建非聚集索引 CREATE INDEX S_Sdept_speciality ON 学生情况表(系名,专业),88,创建索引的例子,例：根据学生情况表的院系、班级和学号列创建一个唯一索引。 CREATE UNIQUE INDEX S_Sdept_Class_Sno ON 学生情况表(系名,班级,学生ID ),在同一个列组合上创建唯一索引而不是非唯一索引可为查询优化器提供附加信息；所以最好创建唯一索引。,89,3.3.3 创建和管理索引,1 索引的概念 2 索引的分类 3 索引的创建 4 删除索引,90,4 删除索引,从当前数据库中删除一个或多个索引 使用企业管理器 企业管理器-数据库-表对象-表名称-设计表-管理索引-索引名-删除 使用SQL语句,91,使用SQL语句删除索引,语句格式: DROP INDEX 表名.索引名； 解释: 删除索引时,必须同时指出表名和索引名: 表名.索引名 DROP INDEX 语句不适用于通过CREATE TABLE 或 ALTER TABLE 语句的 PRIMARY KEY 或 UNIQUE 选项创建的索引,92,删除索引,例：删除学生情况表的索引S_Sdept_speciality和S_Sdept_Class_Sno DROP INDEX 学生情况表.S_Sdept_speciality , 学生情况表.S_Sdept_Class_Sno,93,索引说明,(1) 使用SQL语言，用户可以随时在基本表上建立索引、增加新索引、删除索引。用户根据具体应用环境需要的情况，可动态地调整索引结构，达到较高的查询性能。 (2) 用户只能建立、修改和删除索引，不能在数据操作中引用索引，在数据操作执行中如何使用索引完全由系统决定。从而保证了增加和删除索引时不必修改应用程序，支持数据的物理独立性。,94,3.3 SQL数据定义语言DDL,3.3.1 数据库的定义、修改和删除 3.3.2 基本表的定义、修改和删除 3.3.3 创建和管理索引