关系数据库标准语言SQL.ppt

1、第五章 关系数据库标准语言SQL,5.1 SQL概述,SQL是1974年由Boyce Chamberlin提出。 1979年IBM公司首次在其关系数据库系统System R上实现了SQL。 1986年10月由美国国家标准局（简称ANSI）的数据委员会X3H2批准了SQL作为关系数据库语言的美国国家标准，同年公布了SQL标准文本（简称SQL86）。1987年国际标准化组织（简称ISO）也通过了这一标准。此后ANSI不断修改和完善SQL标准，并于1989年公布了SQL89标准，1992年又公布了SQL92标准。SQL92标准将其内容分为三个级别，即基本级、标准级和完全级。尽管如此，SQL-92标准

2、也不够完备，且正在进行修改。,5.1.1 SQL的特点,1、一体化语言。 2、高度非过程化。 3、语言简洁，易学易用。 4、统一的语法结构对待不同的工作方式。,5.1.2 SQL的主要功能,SQL是一种介于关系代数与关系演算之间的结构化查询语言，其功能并不仅仅是查询，还包括数据定义、数据操纵和数据控制。SQL语言是一个通用的、功能极强的关系数据库语言。 1、数据定义功能 2、数据操纵功能 3、数据控制功能 VFP在SQL方面支持数据定义、数据查询和数据操纵功能，但在具体实现方面也存在一些差异。另外，由于VFP自身在安全控制方面的缺陷，所以它没有提供数据控制功能。,5.2 SQL的数据定义功能,

3、标准SQL的数据定义功能非常广泛，一般包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则的定义和索引的定义等若干部分。 5.2.1 表的定义及维护 1、表结构的定义 语句格式：CREATE TABLE ( (,列级完整性的约束条件,n ，,n );,说 明： (1)SQL支持的数据类型 不同的数据库系统支持的数据类型不完全相同。使用最多的是字符型数据和数值数据。 (2)省略为自由表，否则为数据库中表。 (3)为新建表的表名。 (4)数据类型(字段宽度,小数位数)指定了字段名、字段类型、字段宽度及小数位数。字段类型可以用一个字符表示。,(5)列级完整性约束条件 NOT NULL或NUL

4、L约束：NULL允许该字段值为空；NOT NULL不允许字段值为空。缺省值为NOT NULL。 UNIQUE约束：是唯一性约束。 DEFAULT约束：是缺省值约束。 格式为：DEFAULT FOR CHECK约束：是检查约束。 格式为:CONSTRANT CHECK ERROR约束：输入的值不符合CHECK子句的合法值时，显示的错误信息。 格式为：ERROR字符型文本信息,(6)表级完整性约束条件 PRIMARY KEY约束：是实体完整性约束。用于定义主码，它能保证主码的唯一性和非空性。 PRIMARY KEY约束格式为：CONSTRAINT PRIMARY KEY（） FOREIGN KEY

5、约束：是外码和参照表约束，它用于定义参照完整性。 FOREIGN KEY约束格式为：CONSTRANT FOREIGN KEY() REFERENCES()(),例如：用SQL命令建立下列三个基本表，基表结构为： 学生（学号，姓名，年龄，性别，所在系）； 课程（课程号，课程名，先行课）； 选课（学号，课程号，成绩）； CREATE TABLE 学生(学号 CHAR(5) NOT NULL UNIQUE, 姓名 CHAR(8) NOT NULL, 性别 SMALLINT, 所在系 CHAR(20),DEFAULT C1 20 FOR 年龄, CONSTRAINT C2 CHECK(性别 IN(男

6、, 女);,CREATE TABLE 课程(课程号 CHAR(5) PRIMARY KEY, 课程名 CHAR(20), 先行课 CHAR(5); CREATE TABLE 选课(学号 CHAR(5), 课程号 CHAR(5), 成绩 SMALLINT, CONSTRAINT C3 CHECK(成绩 BETEWWN 0 AND 100), CONSTRAINT C4 PRIMARY KEY(学号，课程号)， CONSTRAINT C5 FOREIGN KEY(学号) REFERENCES 学生(学号) CONSTRAINT C6 FOREIGN KEY(课程号) REFERENCES 课程(课

7、程号);,例题说明： （1）在学生表中定义的列组约束条件是：学号不能为空且不能出现重复值；姓名不能为空；年龄的缺省值为20；性别必须为“男”或“女”。 （2）在课程表中定义了课程号为主码的表级约束条件。 （3）在选课表中定义了学号和课程号为主码的约束；定义了成绩值在0到100之间的约束；定义了学号为外码，其参照表为学生表，该外码对应学生表中的学号的约束；还定义了课程号为外码，其参照表为课程表，该外码对应课程表中的课程号的约束。,2、表结构的修改,语句格式：ALTER TABLE ADD 完整性约束,n DROP MODIFY ,n; 语句功能：该语句可以添加（ADD）新字段、删除（DROP）字

8、段或修改（MODIFY）原字段的定义。,播放演示,例如：向课程表中增加“学时”字段。 ALTER TABLE 课程 ADD 学时 SMALLINT; 例如：将选课表中课程号的宽度改为6。 ALTER TABLE 选课 MODIFY 课程号 CHAR(6); 例如：删除学生表中对年龄的默认值的定义。 ALTER TABLE 学生 DROP C1;,3、删除基本表,语句格式：DROP TABLE ; 例如：删除学生表。 DROP TABLE 学生; 如果表是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表；否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出

9、现错误提示。所以要删除数据库中的表时，最好应该使数据库是当前数据库。 基本表一旦被删除，表中的数据及在此表基础上建立的索引、视图将自动地全部被删除。,5.2.2 索引的定义及维护,1、建立索引 语句格式： CREATE UNIQUECLUSTER INDEX ON (,是要建索引的基本表的名称。 （4）是要按其建立索引的列名。 （5）指定了索引值的排列顺序，有ASC(升序)和DESC(降序)两个值，缺省为ASC。,例如：为学生表按学号升序建立索引STUSNO。CREATE UNIQUE INDEX Stusno ON 学生(学号); 例如：为课程表按课程号升序建立唯一索引COUCNO。 CRE

10、ATE UNIQUE INDEX Coucno ON 课程(课程号); 例如：为选课表按学号升序和课程号降序建立唯一索引SCNO。 CREATE UNIQUE INDEX SCno ON 选课(学号 ASC，课程号 DESC);,5.2.3 视图的定义及维护,1、视图的定义 语句格式：CREATE VIEW视图名,)AS查询语句; 说 明： （1）组成视图的属性列名全部省略或者全部指定。若省略了视图的各个属性列名，则该视图的属性为查询语句中指定的列名。 （2）查询语句可以是任意的SELECT查询语句，它说明并限制了视图中的数据。 （3）必须明确指定组成视图的各列名的情况。,例如：建立计算机系学

11、生的视图。 CREATE VIEW COMPUTER AS SELECT 学号,姓名，年龄 FROM 学生 WHERE 所在系=“计算机”； （上例为从单个表派生出的视图，视图还可以由多个表导出。）,例如：由学生、课程和选课三个表，定义包括学号、姓名、课程名和成绩属性的数学系学生成绩视图。 CREATE VIEW 成绩(学号,姓名,课程名,成绩) AS SELECT 学生.学号,学生.姓名，课程.课程名,选课.成绩 FROM 学生,课程,选课 WHERE 学生.学号=选课.学号 AND 课程.课程名=选课.课程号 AND 学生.所在系=“数学”；,建立视图时，用一个查询来建立视图的SELECT

12、子句可以包含算术表达式或集函数，这些表达式或集函数与视图的其他字段一样对待，由于它们是计算得来的，并不存储在表内，所以称为虚字段。 例如：定义一个视图，它包含学号、总成绩、平均成绩三个属性。 CREATE VIEW 成绩统计(学号,总成绩,平均成绩) AS SELECT 学号,SUM(成绩)，AVG(成绩) FROM 选课；,2、视图的删除,语句格式：DROP VIEW视图名 说 明：上述命令与VFP中删除视图的命令DELETE VIEW 视图名等价。 视图删除后，视图的定义将从数据字典中删除，而由该视图导出的其他视图的定义却仍存在数据字典中，但这些视图已失效。为了防止用户在使用时出错，要用视

13、图删除语句把那些失效的视图一一删除。同样，在某个基本表被删除后，由该基本表导出的所有视图定义虽然没有被删除，但均已无法使用了。,3、关于视图的说明,在VFP中视图是可更新的，但是这种更新是否反映在基本表中则取决于视图更新属性的设置。在VFP中视图有它特殊的概念和用途，在关系数据库中，视图始终不真正含有数据，它总是原来表的一个窗口。所以，虽然视图可以像表一样进行各种查询，但是插入、更新和删除操作在视图上却有一定的限制。 在一般情况下，当一个视图是由单个表导出时可以进行插入和更新操作，但不能进行删除操作;当视图是从多个表导出时，插入、更新和删除操作都不允许进行。这种限制是很必要的，它可以避免一些潜

14、在问题的发生。,5.3 SQL的数据更新功能,数据更新是指对数据的操作功能，主要包括数据的插入、更新、删除三个方面的操作。 5.3.1 数据插入 SQL的数据插入语句有两种使用形式：一种是使用常量，一次插入一条记录；另一种是插入子查询的结果，一次插入多条记录。 1、使用常量插入一条记录 2、在表中插入子查询的结果,1、使用常量插入一条记录,语句格式： INSERT INTO (,) VALUES(常量1), 常量2，) 语句功能：将新记录插入指定表中。 说 明： （1）新记录的值为的值。 （2）如果INTO子句中有属性列选项，则没有出现在子句中的属性将取空值。 （3）如果INTO子句中没有指明

15、任何列名，则新插入的记录必须在每个列上均有值。,例如：将一新生记录(学号:20050110,姓名: 张亮,年龄: 20，所在系: 数学)插入到学生表中。 INSERT INTO 学生 VALUES(20050110, 张亮, 20, 数学)； 例如：在选课表中插入一条记录(学号: 20030211,课程号: C10,成绩不详)。 INSERT INTO 选课 VALUES(20030211, C10)；,2、在表中插入子查询的结果,语句格式：INSERT INTO (,)； 例如：求每个系学生的平均年龄，并把结果存入数据表中。 CREATE TABLE 系平均年龄(系名 CHAR(20),平均

16、年龄 SMALLINT)； INSERT INTO 系平均年龄 SELECT 所在系,AVG(ALL 年龄) FROM 学生 GROUP BY 所在系；,5.3.2 数据更新,语句格式：UPDATE表名 SET=,= WHERE ； 语句功能：将表中符合条件的记录的某些列用表达式的值替代。 说 明： (1)指定要更新数据的记录所在的表名及该表所在的数据库名。 (2)SET=指定被更新的字段及该字段的新值。 (3)WHERE条件表达式指明将要更新符合条件表达式的记录。并且一次可以更新多个字段;如果不使用WHERE子句，则更新全部记录。,例如：将学生表中全部学生的年龄加上2岁。 UPDATE 学生

17、 SET 年龄=年龄+2 例如：将选课表中的VB课程的成绩乘以1.4。 UPDATE 选课 SET 成绩成绩*1.4 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名= VB)；,5.3.3 数据删除,语句格式：DELETE FROM表名 WHERE 条件表达式1； 说 明： (1)指定要删除数据的表名。 (2)WHERE选项指明只对满足条件的记录删除。如果不使用WHERE子句，则删除该表中的全部记录。 例如：删除数学系学生的选课记录。 DELETE FROM 选课 WHERE 学号IN (SELECT 学号 FROM 学生 WHERE 所在系=数学)；,播放演示

18、,5.4 SQL的数据查询功能,5.4.1 SELECT语句介绍 1、SELECT语句格式 SELECTALL|DISTINCT FORM WHERE GROUP BY ,HAVING ORDER BYASC|DESC,ASC |DESC 语句功能：从指定的表中选择满足条件的记录。,播放演示,SELECT-SQL命令的格式包括三个基本子句：SELECT子句、FROM子句、WHERE子句，还包括操作子句：ORDER子句、GROUP子句以及其他一些选项。 说 明： （1）SELECT子句用来指定查询结果中的目标列，目标列组用一个*号来表示时，指所有的字段。 （2）ALL选项：表示选出的记录中包括重

19、复记录，这是缺省值；DISTINCT则表示选出的记录中不包括重复记录。 （3）FROM子句用于指明查询的数据源。 定义表别名的格式为： 。,（4）WHERE子句通过条件表达式描述表中记录的筛选条件，将不满足条件的元组筛选掉。 （5）GROUP BY子句对记录按分组列的值分组，分组可以使同组的元组集中在一起，常用于数据分组统计。 （6）GROUP BY子句后可带上HAVING子句作为组筛选条件，组选择条件为带有函数的条件表达式，它决定着整个组记录的取舍条件。 （7）ORDER BY子句指定查询结查中记录按排序列排序，默认升序。选项ASC表示升序，DESE表示降序。,2、SELECT 子句的操作符

20、,SELECT语句中使用的操作符包括算术操作符、比较操作符、逻辑操作符、组合查询操作符。 （1）算术操作符 算术操作符在SQL语句中表达数学运算操作。 SQL的算术操作符有：+、-、*、/ 四种。 （2）比较操作符 比较操作符用于测试两个数据的关系。 SQL的比较操作符有：=、=、!（不大于）、!（不小于）九种。 （3）逻辑操作符 P94表5-3列出了SQL的主要逻辑操作符的语义。,（4）组合查询操作符 组合查询操作符的使用格式为： UNION:并查询操作符。操作结果将和的结果合并，即取和的元组，并在结果中去掉重复行。 MINUS:差查询操作符。操作结果为取在中且中没有的记录。 INTERSE

21、CT:交查询操作符。操作结果为取和中共有的记录。 SELECT查询命令的使用非常灵活，用它可以构造各种各样的查询。,5.4.2 简单查询,简单查询是指在查询过程中只涉及一个表的查询语句，可有简单的查询条件。是最基本的查询语句。 例如：求计算机系学生的学号和姓名。 SELECT 学号，姓名 FROM 学生 WHERE 所在系= 计算机； 例如：求选修了课程的学生学号，结果去掉重复值。 SELECT DISTINCT 学号 FROM 选课；,例如：求选修了课程C1的学生学号和成绩，结果按成绩降序排列，如果成绩相同则按学号升序排列。 SELECT 学号,成绩 FROM 选课 WHERE 课程号= C

22、1 ORDER BY 成绩 DESC，学号 ASC； 例如：求物理系或数学系姓王的学生的信息。 SELECT * FROM 学生 WHERE 所在系 IN (物理, 数学) AND 姓名 LINK 王%；,例如：求缺少成绩的学生的学号和课程号。 SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL； 例如：求选修课程C1且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。 SELECT 学号,成绩*0.8 FROM 选课 WHERE 课程号=C1 AND 成绩 BETWEEN 80 AND 90;,5.4.3 连接查询,若查询涉及到两个以上的表，则称之为

23、连接查询。连接查询是关系数据库中最主要的查询功能。 1、等值连接和非等值连接 连接查询中，用来连接两个表的条件称为连接条件或连接谓词。连接条件的一般格式为： . 连接谓词中的列名称为连接字段；连接条件中，连接字段类型必须是可比的，但连接字段不一定是同名的。,当连接运算符为“=”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算称非等值连接。当等值连接中的连接字段相同，并且在SELECT子句中去除了重复字段时，则该连接操作为自然连接。 例如：查询每个学生的情况以及他所选修的课程。 SELECT 学生.*，选课.* FROM 学生, 选课 WHERE 学生.学号=选课.学号；,例如：查询选

24、修C1课程且成绩为90分以上的学生学号、姓名及成绩。 SELECT 学生.学号,姓名,成绩 FROM 学生，选课 WHERE 学生.学号=选课.学号 AND 课程号= C1 AND 成绩90； 例如:查询学生的学号、姓名、选修的课程名及成绩。 SELECT 学生.学号,姓名,课程名,成绩 FROM 学生,课程,选课 WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号；,2、自身连接,连接操作不只是在两个表之间进行，一个表内还可以进行自身连接操作。表自身的连接操作称为自身连接。 例如：查询每一门课的间接先行课（即先行课的先行课）。 SELECT A.课程号，A.课程名,B.先

25、行课 FROM 课程 A,课程 B WHERE A.先行课=B.课程号；,说 明： （1）同一查询语句中，当一个表有两个作用时，需要对表起别名，应用中使用表的别名。上例中的A和B分别是课程的别名，A和B分别作为独立表使用。 （2）该题的连接条件是A表中的先行课与B表中的课程号等值连接，其结果集中“B.先行课”为“A.课程号”的间接先行课字段。由于A和B都是课程的别名，所以该例是自身连接操作。,5.4.4 嵌套查询,将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。 1、使用IN操作符的嵌套查询 当IN操作符后的数据集需要通过查询得到时，就需要使用IN嵌

26、套查询。 例如：查询选修了高等数学的学生学号和姓名。 SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 课程 WHERE 课程号= 高等数学)；,2、使用比较符的嵌套查询,例如：查询选修了课程C1的成绩高于张亮的学生学号和成绩。 SELECT 学号，成绩 FROM 选课 WHERE 课程号C1 AND 成绩 (SELECT 成绩 FROM 选课 WHERE 课程号= C1 AND 学号 (SELECT 学号 FROM 学生 WHERE 姓名张亮)；,3、使用ANY或ALL操作符的嵌套查询,使用ANY或ALL操作符时必须与比较符配合使用，其格式为：

27、 ANY|ALL 例如：查询其他系中比数学系某一学生年龄小的学生（即查询年龄小于数学系最大者的学生）。 SELECT * FROM 学生 WHERE 年龄 数学；,例如：查询其他系中比数学系学生年龄都小的学生。 SELECT * FROM 学生 WHERE 年龄 数学；,4、使用EXISTS操作符的嵌套查询,EXISTS代表存在量词。EXISTS操作符后子查询的结果集中如果不为空，则产生逻辑真值“TRUE”，否则产生假值“FALSE”。 例如：查询选修了C2课程的学生姓名。 SELECT 姓名 FROM 学生 WHERE EXISTS(SELECT * FROM 选课 WHERE 学生.学号=

28、学号 AND 课程号=C2)；,例如：查询没有选修C2课程的学生姓名。 SELECT 姓名 FROM 学生 WHERE NOT EXISTS(SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号= C2)；,例如：查询选修了全部课程的学生的姓名。 SELECT 姓名 FROM 学生 WHERE NOT EXISTS(SELECT * FROM 课程 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号=课程号)；,例如：查询至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。 SELECT 学号，姓名