SQL经典例题及课件.ppt

《SQL经典例题及课件.ppt》由会员分享，可在线阅读，更多相关《SQL经典例题及课件.ppt（281页珍藏版）》请在三一办公上搜索。

1、2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,第3章 关系数据库的标准语言SQL,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.1 SQL概述 3.2 数据定义语言 3.3 数据更新语言 3.4 数据查询语言 3.5 数据控制语言3.6 嵌入式SQL小结,内 容,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.1 SQL概述,SQL的发展历程 SQL的组成与功能 SQL语言的特点 SQL基本知识,2023/11/8,数据库原理与应用第3章 关系数据库的标准语

2、言SQL东北财经大学 李红,SQL语言1974年被提出，并首先在IBM公司研制的RDB原型系统System R上实现。它具有功能丰富、使用方式灵活、语言简洁易学等突出优点，深受计算机工业界和计算机用户的欢迎。1986年10月，美国国家标准局(ANSI)的DB委员会批准SQL作为RDB语言的美国标准。同年公布了标准SQL文本。1987年6月，国际标准化组织(ISO)将其采纳为国际标准(称“SQL86”)。此后出现了“SQL89”、“SQL2”(1992)和“SQL3”(1993)。,3.1.1 SQL的发展历程,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学

3、李红,SQL成为国际标准后，各个DB厂家纷纷推出各自的支持SQL的软件或与SQL接口的软件。趋势：各种计算机(微机、小型机、大型机)上的DBS，都采用SQL作为共同的数据存取语言和标准接口。成为不同DBS间互操作的基础。SQL对DB以外的领域也产生了很大影响(软件产品将SQL语言的数据查询功能与图形功能、软件工程工具、软件开发工具、人工智能程序结合起来，开发出功能更强的软件产品)。可预见，在未来一段相当长的时间内，SQL仍将是RDB的主流语言，而且在知识发现、人工智能、软件工程等领域，也具有广阔的应用前景。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李

4、红,3.1.2 SQL的组成与功能,数据定义语言 数据操纵语言 数据控制语言 嵌入式SQL,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数据定义语言,称为“SQL DDL”。用来定义RDB的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数据操纵语言,称为“SQL DML”。包括数据查询和数据更新两类。数据查询：对DB中的数据查询、统计、分组、排序等操作；数据更新：数据的插入、删除、修改等数据维护操作。,2023/11/8,数

5、据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数据控制语言,数据控制语言(简称DCL)也称为“SQL DCL”。数据控制包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,嵌入式SQL,规定了SQL语句嵌入在宿主语言程序中使用的各种规则。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.1.3 SQL语言的特点,自含式和嵌入式两种形式 语言简洁、易学易用 高度非过程化 支持RDB三级模式结构,2023/11/8,数据库原理与应用

6、第3章 关系数据库的标准语言SQL东北财经大学 李红,自含式和嵌入式两种形式,自含式SQL：能够独立地进行联机交互，用户只需在终端键盘上直接键入SQL命令就可以对DB进行操作；嵌入式SQL：能够嵌入到高级语言，如C、COBOL、FORTRAN、PL/1程序中来实现对DB的数据存取操作，给程序员设计应用程序提供了很大的方便。在自含式SQL和嵌入式SQL不同的使用方式中，SQL的语法结构基本上一致。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,语言简洁、易学易用,尽管SQL语言功能极强又有两种使用方式，但由于设计巧妙，其语言十分简洁，完成核心功能的语句

7、只用了9个动词。SQL语法很简单，容易学习和使用。SQL的命令动词表：,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,高度非过程化,SQL是一种第四代语言(4GL)，用户只需要提出“干什么”，无需具体指明“怎么干”；像存取路径选择和具体处理操作等，均由系统自动完成。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,支持RDB三级模式结构,全体基本表构成DB的概念模式视图和部分基本表构成DB的外模式DB的存储文件和它们的索引文件构成RDB的内模式,图,2023/11/8,数据库原理与应用第3章 关系数据库的标准

8、语言SQL东北财经大学 李红,全体基本表构成DB的概念模式,基本表是本身独立的表；SQL中的一个关系对应一个基本表；基本表按数据全局逻辑模式建立。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,视图：由一个或几个基本表导出的表。视图不独立存储在DB中，DB中只存放视图的定义而不直接存放视图对应的数据，这些数据仍存放在与视图相关的基本表中。视图是一个虚表，是根据外模式定义的，能满足用户和应用程序的数据格式要求。当基本表不适合用户直接查询的操作要求时，需要定义视图，以便于用户的查询操作。在数据查询时，SQL对基本表和视图等同对待。,视图和部分基本表构成D

9、B的外模式,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,在SQL中，基本表可以直接被用户操作，被直接使用的基本表也是外模式的一部分。基本表和视图构成了RDB的外模式，SQL支持RDB的外模式结构。用户可用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,一个关系对应一个表；一个或多个表对应一个存储文件；一个表可带若干个索引，索引也存放在存储文件中；存储文件的逻辑结构组成RDB的内模式。,DB的存储文件和它们的索引文

10、件构成RDB的内模式,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,SQL对RDB模式的支持,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.1.4 SQL基本知识,1.数据类型2.表达式与运算符 3.函数 4.语法规定与约定,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,1.数据类型,数值型 字符型 位串型 日期型,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数据类型是数据自身的特点，它的属性被放在表的列字段里。数

11、据类型用于给特定的列提供数据规则，它决定着数据在列中的存储方式和给列分配的数据长度，并且决定了此数据是字符、数字还是时间日期数据。每一个具体的SQL的实施方案都有自己特有的数据类型，因此有必要使用与具体的实施方案相关的数据类型。但所有的实施方案中的基本数据类型都是一样的。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数值型INT：长整数，也可写成INTEGERSMALLINT：短整数REAL：取决于机器精度的浮点数DOUBLE PRECISION：取决于机器精度的双精度浮点数FLOAT(n)：浮点数，精度至少为n位数字NUMBERIC(p,q)：定

12、点数，由p位数字组成，但不包括符号、小数点，小数点后面有q位数字，也可写成DECIMAL(p,q)或dec(p,q),2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,字符型CHAR(n)：长度为n的定长字符串，n是字符串中字符的个数VARCHAR(n)：具有最大长度为n的变长字符串,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,位串型BIT(n)：长度为n的二进制位串BIT VARYING(n)：最大长度为n的变长二进制位串,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学

13、李红,日期型 DATE：日期包含年、月、日格式为 YYYY-MM-DD TIME：时间包含一日的时、分、秒格式为 HHMMSS,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,有的系统可能还会提供货币型、文本型、图像型等类型。SQL支持空值的概念，空值是RDB中的一个重要概念，与空(或空白)字符串、数值0具有不同的含义，不能把它理解为任何意义的数据。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,2.表达式与运算符,比较运算符谓词逻辑运算符算术运算符,2023/11/8,数据库原理与应用第3章 关系数据库的标

14、准语言SQL东北财经大学 李红,表达式：由一个或多个值、运算符和函数组合而成，可计算出一个值，其数据类型一般为它的成分的数据类型。运算符：是保留的字或字符，主要用于在子句中执行比较和数学运算等操作。在SQL语句中运算符用于确定条件和建立语句中多个条件之间的连接。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,比较运算符其在SQL语句中用于测试单个的值。包括：、!或、用于测试数据相等、不等、小于、大于、小于等于和大于等于的各种情况。当条件满足时，返回True，否则返回False。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东

15、北财经大学 李红,谓词 IS NULL和IS NOT NULL BETWEENAND和NOT BETWEENAND IN LIKE EXISTS和NOT EXISTS UNIQUE ALL和ANY,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,IS NULL 和 IS NOT NULL用于判断字符串的值是否是空值。例如：查询缺少成绩（CJ）的学生的学号。可在WHERE子句中使用的表达式为：CJ IS NULL注意：不能用“”来查找NULL值。若使用“”，则表示要查找的是字符“NULL”，而不是NULL值。例如：查找不属于任何部门的职工。错误的表达式为

16、：BMHNULL,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,BETWEENAND 和 NOT BETWEENAND谓词BETWEEN用于判断值是否在给定的两个值之间，注意包括边值。例如：查找成绩（CJ）在90分到100分之间的学生。可在WHERE子句中使用表达式：CJ BETWEEN 90 AND 100 也可使用表达式：CJ90 AND CJ100显然在可读性上谓词BETWEEN更好一些。BETWEEN前面加上NOT表示否定。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,IN用于判断值是否属于指定的

17、集合。例如：查找职工表中工资（GZ）是否为以下几个值中的一个：4000，5000，6000。可在WHERE子句中使用表达式：GZ IN（4000，5000，6000）,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,LIKE可以用来进行字符串的匹配。其一般语法格式如下：NOT LIKE ESCAPE 在指定的关系中查找指定属性列值与相匹配的元组。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,说明1：中经常使用通配符(百分号)和_(下划线)，且可组合使用。其中：(百分号)：代表任意长度(长度可能为0)的字符串

18、；_(下划线)：代表任意单个字符。例如：A%B以A开头，以B结尾的任意长度的字符串。A_B以A开头，以B结尾长度为3的任意字符串。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,说明2：如果LIKE后面的匹配符中不含通配符，则可以用“”运算符取代谓词LIKE，用或！运算符取代谓词NOT LIKE。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,说明3：若要查询的字符串本身就含有“%”或“_”，则需要使用由ESCAPE指定的换码字符。例如：在WHERE子句中的LIKE谓词为：LIKECOM_cn_ESCAPE

19、（表示以COM_cn开始的最后一个字母任意的字符串）指定的换码字符为，匹配串COM_cn_中的第一个下划线前因为有换码字符，所以将其解释为下划线本身，而不再是通配符；第二个下划线则仍是通配符。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,EXISTS 和 NOT EXISTS格式为：EXISTS（子查询）。用于判断在指定的表中是否存在一行符合某种条件的数据。带有EXISTS谓词的子查询不返回任何数据，当子查询结果非空，返回逻辑真值“True”，否则，返回逻辑假值“False”。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL

20、东北财经大学 李红,UNIQUE用于在指定的表中判断每一行是否是惟一的。ALL和ANY用于将某个值同另一数据集中的每个值相比较。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,逻辑运算符 NOT AND OR,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,NOT将它使用的逻辑运算符的意义反转。如NOT BETWEEN、NOT IN、NOT EXISTS、NOT LIKE、NOT UNIQUE等。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,OR连接多个查询条件，

21、表示至少有一个查询条件成立。例如：查找年龄（NL）为20岁或25岁的学生。可使用表达式为：NL20 OR NL25,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,AND连接多个查询条件，表示所有的查询条件都成立。例如：查找年龄（NL）为25岁，并且家庭住址（ZZ）住在大连的女学生（XB表示性别）。可使用表达式为：NL25 AND ZZ大连AND XB女,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,算术运算符用于执行数学运算的功能。通常有四种操作符：+、*和。可单独使用，也可组合使用。,2023/11/8,

22、数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.函数,单行函数 统计函数,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,单行函数单行函数主要分为数值函数、字符函数、日期函数、转换函数等；它对查询的表或视图的每一行返回一个结果行。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数值函数：用于接受数值输入，返回数值。许多函数的返回值可精确到38位十进制数字，三角函数精确到36位十进制数字。单行字符函数：用于接受字符输入，可返回字符值或数值。日期函数：是操作DATE数据类型的值，所有日期

23、函数都返回一个DATE类型的值。转换函数：是将一种数据类型的值转换成另一种数据类型的值。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,统计函数统计函数是用来累加、合计和显示数据极限的函数，它主要用于给SQL语句提供统计信息。常用的统计函数有COUNT、SUM、MAX、MIN和AVG等。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,4.语法规定与约定,语句格式约定符号：其中的内容为必选项,它表示实际语义，不能为空；：其中内容为任选项；或：必选其中之一项；，n：表示前面的项可以重复多次。,2023/11/8

24、,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,语法规定 一般语法规定SQL中数据项（列项、表和视图）的分隔符为“,”；字符串常数的定界符用单引号“”表示。SQL特殊语法规定SQL的关键词一般使用大写字母表示；SQL语句的结束符为“；”；SQL采用格式化书写方式。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.2 数据定义语言,数据库 表 索引 视图 存储过程,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,数据定义语言（DDL）是SQL的一部分，用来定义RDB的模式、外模式和内模

25、式，以实现对RDB的基本表、视图以及索引文件等基本要素的定义、修改和删除等操作。DDL的功能包括：数据库、基本表、索引、视图及存储过程的定义、修改和删除等。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.2.1 数据库,1.创建数据库 2.修改数据库 3.撤消数据库,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,SQL中，DB被定义为对象(即基本表、视图和索引等)的集合。并包含DB中每一个对象的定义。DB在磁盘上是以文件为单位存储的，由数据文件和日志文件组成。数据文件中实际存放DB所有数据和对象，日志文

26、件用来存放(记录)用户对DB所进行的所有操作，它是维护DB完整性的重要工具。创建一个DB，就是定义了一个存储空间。,1.创建DB,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,语句格式：CREATE DATABASE 功能：定义DB名和DB的存储空间。说明：使用该语句的用户必须具有DBA的权限；DB的创建者被默认为该DB的所有者；在创建DB的过程中，要指定DB名称、设计所占用的存储空间和存放文件位置。创建DB至少需要定义数据文件和日志文件，省略系统选择默认值。对于数据文件和日志文件的定义不同SQL版本规定不同。例3-1：创建学生管理数据库 creat

27、e database 学生管理,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,语句格式：ALTER DATABASE 功能：修改指定的DB。说明：该语句只有DBA或具有CREATE DATABASE权限的DB拥有者才有权使用；该语句可以增加或删除DB中的文件，也可以修改DB文件的属性。,2.修改数据库,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,语句格式：DROP DATABASE 功能：撤消指定的一个或多个DB。说明：该语句只有DBA或具有CREATE DATABASE权限的DB拥有者才有权使用；当DB

28、处于正在被使用或正在恢复等非正常状态时，是不能被撤消的；该语句执行后，系统将无法恢复被撤消的DB，除非事先有DB的备份；当一个或多个DB被撤消后，其中所有的数据及DB对象都将被删除，所有的数据文件和日志文件也都将被删除，所占用的空间将被释放。,3.撤消数据库,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.2.2 表,1.创建基本表 2.完整性约束 3.修改基本表 4.撤消基本表,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,1.创建基本表,语句格式：CREATE TABLE(，);功能：定义基本表的表名

29、及其结构。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,说明：是所要定义的基本表的名字，基本表可以由一列(属性)或多列组成；创建基本表时必须说明每一列的数据类型；创建基本表的同时可定义与该基本表有关的完整性约束条件，它们被存入系统的DD中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件；若完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级也可定义在表级。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-2：建立学生管理DB中的基本表：学生(学号，姓名，

30、性别，年龄，班级号)课程(课程号，课称名，学分，周学时)考试(学号，课程号，成绩)create table 学生(学号 char(6)，姓名 char(10)，性别 char(2),年龄 smallint,班级号 char(5)）；create table 课程(课程号 char(6),课称名 char(20),学分 smallint，周学时 smallint)；create table 考试(学号 char(6),课程号 char(6),成绩 smallint)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,2.完整性约束,PRIMARY KEY

31、约束 FOREIGN KEY约束 UNIQUE约束 CHECK约束 NOT NULL或NULL约束,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,完整性约束的用途是限制输入到基本表中的值的范围。列级完整性约束：是行定义的一部分，只能应用在一列上；表级完整性约束：它的定义独立于列的定义，可应用在一个基本表中的多列上。当需要在一个基本表中的多列上建立约束条件时，只能建立表级约束。当完整性约束创建之后，它作为基本表定义的一部分，存入DD中。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,PRIMARY KEY约束

32、也称主关键字约束，是实体完整性约束。该约束用于定义主关键字，它能保证主关键字的惟一性和非空性。该约束可在列级或表级上进行定义，但不允许同时在两个级别上进行定义。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,PRIMARY KEY约束定义主关键字方法：PRIMARY KEY约束直接写在列名及其类型之后；按照语法在相应列名及类型后单独列出：CONSTRAINT PRIMARY KEY其中是PRIMARY KEY约束的名字。在CREATE TABLE语句列出基本表的所有列之后，再附加一个声明：PRIMARY KEY(，),2023/11/8,数据库原理与

33、应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-3：在创建学生基本表时，将学号定义为主关键字，列级PRIMARY KEY约束。create table 学生(学号 char(6),primary key,姓名 char(10),性别 char(2),年龄 smallint,班级号 char(5)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-4：在创建课程基本表时，将课程号定义为主关键字，并指定PK_KCH为PRIMARY KEY约束名(列级约束)。create table 课程(课程号 char(6)constraint p

34、k_kch primary key,课称名 char(20),学分 smallint,周学时 smallint)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-5：在创建考试基本表时定义了一个名为PK_KS的表级 PRIMARY KEY约束。create table 考试(学号 char(6),课程号 char(6),成绩 smallint,constraint pk_ks primary key(学号,课程号)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,FOREIGN KEY约束也称为外部关

35、键字或参照表约束，用于定义参照完整性，即用来维护两个基本表之间的一致性关系。外部关键字的建立主要是通过将一个基本表中的主关键字所在的列包含在另一个表中，这些列就是另一个表的外部关键字。FOREIGN KEY约束不仅可以与另一基本表上的PRIMARY KEY约束建立联系，也可以与另一基本表上的UNIQUE约束建立联系。当一行新的数据被加入到表格中，或对表格中已经存在的外部关键字上的数据进行修改时，新数据必须存在于另一基本表的主关键字上，或者为NULL。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,外部关键字的作用不只是对输入自身表格的数据进行限制，同

36、时也限制了对主关键字所在基本表的数据进行修改。当主关键字所在表的数据被另一张表的外部关键字所引用时，用户将无法对主关键字里的数据进行修改或删除，除非事先删除或修改引用的数据。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,FOREIGN KEY约束定义外部关键字方法 如果外部关键字只有一列，可在它的列名和类型后面直接用“REFERENCES”说明它参照了某个基本表的某些列(必须是主关键字)，其语法格式为：REFERENCES(),2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,在属性列表后面增加一个或几个外部

37、关键字说明，语法格式为：FOREIGN KEY()REFERENCES()其中，第一个“列名”是外部关键字；第二个“列名”是被参照基本表中的列名。在相应列名及其类型后单独列出，并指定约束名，语法格式为：CONSTRAINT FOREIGN KEY()REFERENCES(),2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-6：在创建考试基本表时，定义相应外部关键字。create table 考试(学号 char(6),课程号 char(6),成绩 smallint，constraint pk_ks1 primary key(学号,课程号)，con

38、straint pk_ks2 foreign key(学号)references 学生(学号)，constraint pk_ks3 foreign key(课程号)references 课程(课程号)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,学生、考试和课程三个基本表之间所建立的FOREIGN KEY约束,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,UNIQUE约束主要是用来确保不受PRIMARY KEY约束的列上的数据的惟一性。PRIMARY KEY约束与UNIQUE约束区别：UNIQUE约束，

39、主要用在非主关键字的一列或多列上要求数据惟一的情况；UNIQUE约束，允许该列上存在NULL值，而主关键字决不允许出现NULL值；可在一个表上设置多个UNIQUE约束，而在一个表上只能设置一个主关键字。UNIQUE约束也可在列级或在表级上设置。若在多于一列的表上设置UNIQUE约束，则必须设置表级约束。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,定义UNIQUE约束的方法：列级UNIQUE约束CONSTRAINT UNIQUE其中是UNIQUE约束的名字 表级UNIQUE约束CONSTRAINT UNIQUE(,),2023/11/8,数据库原理

40、与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-7：在创建学生基本表时，创建一个列级UNIQUE约束。create table 学生（学号 char(6)，primary key,姓名 char(10)，constraint un_xm unique,性别 char(2),年龄 smallint,班级号 char(5)）；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-8：在创建课程基本表时，创建一个表级UNIQUE约束。create table 课程(课程号 char(6),课称名 char(20),学分 smallint，

41、周学时 smallint，constraint un_one unique(课称名,学分)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,CHECK约束CHECK约束通过检查输入表列的数据的值来维护值域的完整性，它就像一个门卫，依次检查每一个要进入数据库的数据，只有符合条件的数据才允许通过。CHECK约束同FOREIGN KEY约束相同点都是通过检查数据值的合理性来实现数据完整性的维护。但FOREIGN KEY约束是从另一张表上获得合理的数据，而CHECK约束则是通过对一个逻辑表达式的结果进行判断来对数据进行检查。,2023/11/8,数据库原理与

42、应用第3章 关系数据库的标准语言SQL东北财经大学 李红,可在一列上设置多个CHECK约束，也可将一个CHECK约束应用于多列(表级约束)。当一列受多个CHECK约束控制时，所有的约束按照创建的顺序，依次进行数据有效性的检查。在SQL中，创建基本表时，定义CHECK约束的语法格式为：CONSTRAINT CHECK(),2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-9：规定学生的年龄在20岁到65岁之间。在创建学生基本表时，可在年龄上设置一个CHECK约束，使满足CHECK约束的数据才被DB接受。create table 学生(学号 char(

43、6)primary key,姓名 char(10),性别 char(2),年龄 smallint,constraint ch_nl check(年龄20 and 年龄65),班级号 char(5)；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,NOT NULL或NULL约束NOT NULL约束不允许列值为空，而NULL约束允许列值为空。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.修改基本表,语句格式：ALTER TABLE ADD,n DROPCONSTRAINT|COLUMN,n MODIFY,

44、n；功能：修改指定基本表的结构。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,说明：其中是指要修改的基本表；ADD子句用于在基本表中增加新列和新的完整性约束条件。新增加的列不能定义为“NOT NULL”，因为不论基本表中原来是否有数据，新增加的列一律为空值(NULL)；DROP子句用于在表中删除指定的完整性约束或指定的列；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,当在基本表中删除某一列时，所有引用该列的视图和约束也一起自动被删除。有的系统规定若删除某一列时，只有当没有视图和约束引用该列时，才能进行，

45、否则将被拒绝；MODIFY子句用于修改原有的列的定义，包括修改列名、列的宽度和列的数据类型。但是，有些系统一般不允许对列名和数据类型进行修改，以免丢失原有基本表中的数据。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例3-10：在学生基本表中增加“入学时间”列，其数据类型为日期型。alter table 学生 add 入学时间 datetime；例3-11：将课程基本表中的“周学时”列删除。alter table 课程 drop column 周学时；例3-12：删除考试基本表中的学号外部关键字，其名为pk_ks2。alter table 考试 d

46、rop constraint pk_ks2；,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,4.撤消基本表,语句格式：DROP TABLE 功能：撤消指定的基本表。说明：是所要撤消的基本表的名字；基本表一旦撤消，表中的数据以及此表上建立的索引和视图都将自动被删除，并且无法恢复，此时系统释放其所占用的存储空间；只有基本表的拥有者才可使用此语句；不能使用DROP撤消系统表。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.2.3 索引,1.索引的特点 2.索引的用途 3.创建索引的原则 4.索引的类型及选择5

47、.索引的创建 6.索引的撤消,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,1.索引的特点,索引：是一个指向表中数据的指针。DB中，索引具有与书籍中目录同样作用。查询被定向于DB中数据在数据文件中的地址。索引是SQL在基本表中列上建立的一种DB对象，也可称其为索引文件。它和建立于其上的基本表是分开存储的。建立索引的主要目的是提高数据检索的性能。索引可被创建或撤消，这对数据毫无影响。当索引被撤消，数据查询的速度可能会变慢。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,索引要占用物理空间，且常比基本表本身占用

48、的空间要大。索引建立后，它便记录了被索引列的每一个取值在表中的位置。当在表中加入新数据时，索引中也增加相应的数据项。当DB中的基本表建立了索引，进行数据查询时，首先在相应的索引中查找。若数据被找到，则返回该数据在基本表中的确切位置。一个基本表，可根据应用环境的需要创建若干索引以提供多种存取途径。通常，索引的创建和撤消由DBA或表的拥有者负责。用户不能也不必在存取数据时选择索引，索引的选择由系统自动进行。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,2.索引的用途,提高数据查询的速度进行数据查询时，如不使用索引，则需将数据文件分块，逐个读到内存，再进

49、行查找的比较操作(基本表的列和元组较多，数据文件很大)。使用索引，系统先将索引文件读入内存，根据索引项找到元组的地址，然后再根据地址将元组数据直接读入内存。索引文件只含有索引项和元组地址，文件小，一般可一次读入内存。索引文件索引项是有序的，可很快找到索引项值和元组地址。使用索引大大减少了磁盘的I/O次数，可加快查询速度。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,保证数据的惟一性索引的定义中包括定义数据惟一性的内容。当定义了数据惟一性的功能后，在对相关的索引项进行数据输入或数据更新时，系统要进行检查，以确保其数据惟一性成立。加快表连接的速度在进行

50、基本表连接操作时,系统需要对被连接的基本表的连接字段进行查询，其工作量是非常可观的。若在被连接的基本表的连接字段上创建索引，则可大大提高连接操作的速度。许多系统要求连接文件必须有相应索引，才能执行连接操作。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,例如:要列出学生表中所有学生某门课程的成绩，则要进行学生和考试这两个基本表的连接操作。在考试基本表中学号(外部关键字)列上建立索引，可提高表连接的速度。,2023/11/8,数据库原理与应用第3章 关系数据库的标准语言SQL东北财经大学 李红,3.创建索引的原则,索引的创建和维护由DBA和DBMS完成