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

资源描述

《关系数据库标准语言SQL(上).ppt》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL(上).ppt（112页珍藏版）》请在三一办公上搜索。

1、第3章关系数据库标准语言SQL,SQL是Structured Query Language（结构化查询语言）的缩写。可以说查询是SQL语言的重要组成部分，但不是全部，SQL还包含数据定义、数据操纵和数据控制功能等部分。SQL已经成为关系数据库的标准数据语言，所以现在所有的关系数据库管理系统都支持SQL。,学习目标,了解SQL的发展过程和主要功能；熟练掌握SQL的CREATE TABLE语句和数据完整性约束的定义方法；熟练掌握SQL的数据操作语句，深刻理解数据完整性约束的作用；熟练掌握SQL SELECT语句，能够运用该语句完成关系数据库上的各种查询；理解关系数据库的视图概念，掌握视图的定义和

2、使用方法。,重点难点,SQL语言本身就是学习数据库的重点，所以要求学生要熟练掌握，具体就是要熟练掌握SQL CREATE TABLE语句、SQL INSERT语句、SQL DELETE语句、SQL UPDATE语句和SQL SELECT语句。本章学习的难点是CREATE TABLE语句中有关数据完整性约束的定义，以及SQL SELECT中复杂的连接和嵌套查询。,3.1 SQL语言概述,SQL标准源于1986年10月的ANSI标准1987年6月正式成为ISO标准SQL89提出了完整性特征SQL92标准逐步完善(基本、标准和完全级)SQL99增加了面向对象等最新的标准是2003年发布的SQL200

3、3SQL标准只是一个建议标准，主流产品一般也只达到了基本级的要求,SQL语言的主要特点,SQL是一种一体化的语言 SQL语言是一种高度非过程化的语言 SQL语言非常简洁 SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用,样本数据库,图3-1 样本数据库,数据定义功能与数据完整性约束的实现,SQL的定义功能包括模式、表、视图、存储过程、触发器、规则、默认值等的定义。,SQL中的模式(Schema),组织数据库对象的一种逻辑结构把一个数据库中的所有对象按用途划分为若干集合,模式组织举例,产品模式订购模式销售模式,定义模式,模式通常由DBA创建也可以由得到CREATE

4、SCHEMA授权的用户创建使用短语AUTHORIZATION owner_name可以指定模式的管理者（默认是创建者）,CREATE SCHEMA schema_name AUTHORIZATION owner_name,删除模式,只有在模式为空、即模式中不包含任何对象时才可以删除模式。,DROP SCHEMA schema_name,表的定义,一个表由若干列构成，自SQL Server 2005不仅可以定义实列(简称列，column_definition),而且还可以定义虚列(即计算列，computed_column_definition)理解：,CREATE TABLE schema_na

6、FAULT|CHECK(logical_expression)DEFAULT constant_expression,定义列(),列名（column_name）和数据类型（）必须指定，在SQL Server 2005中可以使用的数据类型如表3-1所示其他项目是各种约束,column_name NULL|NOT NULL CONSTRAINT constraint_name PRIMARY KEY|UNIQUE|FOREIGN KEY REFERENCES schema_name.referenced_table_name(ref_column)ON DELETE NO ACTION|CASCA

11、？外部关键字的作用？schema_name.referenced_table_name(ref_column)指出参照的表和列，默认模式时schema_name可以省略，列名相同时(ref_column)可以省略,column_name NULL|NOT NULL CONSTRAINT constraint_name PRIMARY KEY|UNIQUE|FOREIGN KEY REFERENCES schema_name.referenced_table_name(ref_column)ON DELETE NO ACTION|CASCADE|SET NULL|SET DEFAULT ON U

16、pression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY|UNIQUE|FOREIGN KEY REFERENCES referenced_table_name(ref_column)ON DELETE NO ACTION|CASCADE ON UPDATE NO ACTION|CHECK(logical_expression),CREATE TABLE schema_name.table_name(|,.n),定义计算列,其中column_name给出计算列的列名，computed_column_expression

17、是对应的计算表达式，PERSISTED说明物理存储计算列的值。其他选项和定义实列时类似。,column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY|UNIQUE|FOREIGN KEY REFERENCES referenced_table_name(ref_column)ON DELETE NO ACTION|CASCADE ON UPDATE NO ACTION|CHECK(logical_expression),定义表级完整性约束,如果某个完整性约

19、ogical_expression),CREATE TABLE schema_name.table_name(|,.n),表定义举例,例1：定义院系表，其中编号为主关键字，名称应该惟一、并且不允许为空值。create table student.院系(编号tinyint primary key,名称char(30)unique not null,负责人char(10),办公地点char(40),表定义举例,例2：定义学生表，其中规定：学号列是主关键字；院系列为计算列（取学号列的第3和第4个字符），并且院系值参照院系表的编号值（院系表是被参照表，主关键字是编号；参照表是学生表，外部关键字是院系）

20、，此约束说明一名学生一定属于某个院系；姓名列不允许为空值；性别必须取值“男”或“女”；学生的学籍状态为正常、留级、休学或退学。,create table student.学生(学号char(8)primary key,院系as convert(tinyint,substring(学号,3,2)persisted not null foreign key references student.院系(编号),姓名char(10)not null,性别char(2)check(性别=男 or 性别=女),生源char(6),状态char(4)check(状态in(正常,留级,休学,退学),表定义举例

21、,例3：定义教师表，其中规定：教师编号是主关键字；院系列为计算列（取教师编号的第1和第2个字符），并且院系值参照院系表的编号值，此约束说明一名教师一定属于某个院系；姓名列不允许为空值；性别必须取值“男”或“女”；职称的取值为教授、副教授、讲师或助教。,create table student.教师(教师编号char(6)primary key,院系as convert(tinyint,substring(教师编号,1,2)persisted not null foreign key references student.院系(编号),姓名char(10)not null,性别char(2)ch

22、eck(性别=男 or 性别=女),职称char(6)check(职称in(教授,副教授,讲师,助教),专业char(16),表定义举例,例4：定义课程表，其中规定：课程编号是主关键字；课程名称不允许为空值；责任教师参照教师表的教师编号值，此约束说明责任教师一定是已经在编的教师；学时不允许为空值；课程性质分为公共基础、专业基础、专业选修和任意选修。,create table student.课程(课程编号char(6)primary key,课程名称char(24)not null,责任教师char(6)constraint duty_teacher foreign key reference

23、s student.教师(教师编号),学时tinyint not null,课程性质char(8)check(课程性质in(公共基础,专业基础,专业选修,任意选修),表定义举例,例5：定义选课表，其中规定：学号和课程编号两列构成主关键字（需要表级完整性约束）；学号参照学生表的学号（约束选课的学生必须存在）；课程编号参照课程表的课程编号（约束被选的课程必须存在）；考试成绩取值在0100之间，并且默认为空值。,create table student.选课(学号char(8)foreign key references student.学生,课程编号char(6)foreign key refer

24、ences student.课程,考试成绩tinyint check(考试成绩between 0 and 100)default null primary key(学号,课程编号),修改表结构,ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL|NOT NULL|ADD|DROP CONSTRAINT constraint_name|COLUMN column_name,ALTER COLUMN修改已有列的定义，但是只能修改为兼容数据类型或重新定义是否允许空值；,修改表结构,ADD添加新列、或新的计算列或表级完整性约

26、定学生表的性别字段不允许为空值。ALTER TABLE 学生 ALTER COLUMN 性别char(2)NOT NULL 是否会影响原先定义在该列上的其他约束？,修改表结构举例,例7：删除课程表中的责任教师列。alter table student.课程drop column 责任教师如果捆绑了其他对象（如列约束），则需要先删除捆绑的对象。alter table student.课程drop duty_teacher,修改表结构举例,例8：为课程表增加责任教师列，列描述如下：字符类型，长度为6；允许空值；外部关键字，参照教师表的教师编号列，当删除被参照记录时该列置为空值，当修改被参照记录的主

27、关键字时级联修改参照记录的外部关键字值。,alter table student.课程add 责任教师char(6)nullconstraint ref_teacher foreign key references student.教师(教师编号)on delete set nullon update cascade,删除表,DROP TABLE schema_name.table_name,.n,一次可以删除多个表。不能使用DROP TABLE删除被FOREIGN KEY约束参照的表。如果要在同一个 DROP TABLE语句中删除参照表以及被参照表，则必须先列出参照表。,思考题,在SQL中C

28、REATE SCHEMA是一条什么命令？创建的SCHEMA是一种什么对象？它有什么作用？在表中定义约束时，PRIMARY KEY和UNIQUE有什么区别？在CREATE TABLE命令中哪些内容与定义参照完整性有关？讨论它们的具体作用。约束一般定义在列上，为什么还需要表级约束？定义计算列时选用关键词PERSISTED有什么作用？计算列是否可以作为参照列（外部关键字）？如果在定义约束时没有使用CONSTRAINT短语给出约束名，而事后又要删除该约束应该怎么办？对已有的表可以进行哪些方面的表结构修改？,数据操作功能,及数据完整性的作用,SQL的数据操作,插入操作及其完整性约束删除操作及其完整性约束

29、更新操作及其完整性约束,插入操作及其完整性约束,SQL的插入语句是INSERT，常用格式：INSERT INTO schema_name.table_name(column_list)VALUES(expression|DEFAULT|NULL,.n)其中 schema_name指出模式名 table_name指出表名column_list给出插入操作所涉及列的列表（默认是表的全部列）expression|DEFAULT|NULL,.n 给出对应于column_list的各个列的值,例9 插入院系表的第1个元组(记录),INSERT INTO student.院系(编号,名称,负责人,办公地点

30、)VALUES(1,信息管理学院,李嘉,教一楼3层),CREATE TABLE student.院系(编号 tinyint PRIMARY KEY,名称 char(30)UNIQUE NOT NULL,负责人 char(10),办公地点 char(40),例10 省略列名、插入院系表的第2个元组,INSERT INTO student.院系VALUES(2,经济管理学院,吴鸿忠,教三楼4层),CREATE TABLE student.院系(编号 tinyint PRIMARY KEY,名称 char(30)UNIQUE NOT NULL,负责人 char(10),办公地点 char(40),例

31、11,INSERT INTO student.院系VALUES(2,计算机学院,张秋霞,教二楼3层),CREATE TABLE student.院系(编号 tinyint PRIMARY KEY,名称 char(30)UNIQUE NOT NULL,负责人 char(10),办公地点 char(40),违背PRIMARY KEY 约束,例12,INSERT INTO student.院系VALUES(3,经济管理学院,张秋霞,教二楼3层),CREATE TABLE student.院系(编号 tinyint PRIMARY KEY,名称 char(30)UNIQUE NOT NULL,负责人

32、char(10),办公地点 char(40),违背UNIQUE约束,例13,INSERT INTO student.院系(编号,负责人,办公地点)VALUES(3,张秋霞,教二楼3层),CREATE TABLE student.院系(编号 tinyint PRIMARY KEY,名称 char(30)UNIQUE NOT NULL,负责人 char(10),办公地点 char(40),违背NOT NULL约束,例14,CREATE TABLE student.学生(学号 char(8)PRIMARY KEY,院系 AS CONVERT(Tinyint,SUBSTRING(学号,3,2)PERS

33、ISTED NOT NULL FOREIGN KEY REFERENCES student.院系(编号),姓名 char(10)NOT NULL,性别 char(2)CHECK(性别=男 OR 性别=女),生源 char(6),状态 char(4)CHECK(状态IN(正常,留级,休学,退学),插入学生表的第1个元组。,INSERT INTO student.学生(学号,姓名,性别,生源,状态)VALUES(04014101,曹波,男,湖北,正常),例15 插入学生表的第2条记录（指定正确的院系值）,注意：学生表的院系列是计算列，是不能被插入语句操作的。,CREATE TABLE studen

34、t.学生(学号 char(8)PRIMARY KEY,院系 AS CONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(编号),姓名 char(10)NOT NULL,性别 char(2)CHECK(性别=男 OR 性别=女),生源 char(6),状态 char(4)CHECK(状态IN(正常,留级,休学,退学),INSERT INTO student.学生(学号,院系,姓名,性别,生源,状态)VALUES(04014111,1,程鸣,女,湖南,正常),INSERT INTO

35、 student.学生 VALUES(04014111,程鸣,女,湖南,正常),正确语句：,例17,INSERT INTO student.学生 VALUES(04074122,吴红霞,女,浙江,正常),CREATE TABLE student.学生(学号 char(8)PRIMARY KEY,院系 AS CONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(编号),姓名 char(10)NOT NULL,性别 char(2)CHECK(性别=男 OR 性别=女),生源 cha

36、r(6),状态 char(4)CHECK(状态IN(正常,留级,休学,退学),违背参照完整性约束,例18,INSERT INTO student.学生 VALUES(04026205,张鸿飞,南,北京,正常),CREATE TABLE student.学生(学号 char(8)PRIMARY KEY,院系 AS CONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(编号),姓名 char(10)NOT NULL,性别 char(2)CHECK(性别=男 OR 性别=女),生源

37、char(6),状态 char(4)CHECK(状态IN(正常,留级,休学,退学),违背域完整性约束,CREATE TABLE student.教师(教师编号 char(6)PRIMARY KEY,院系 AS CONVERT(Tinyint,SUBSTRING(教师编号,1,2)PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(编号),姓名 char(10)NOT NULL,性别 char(2)CHECK(性别=男 OR 性别=女),职称 char(6)CHECK(职称IN(教授,副教授,讲师,助教),专业 char(16),插入教师表记

38、录，注意教师编号的PRIMARY KEY约束，院系是计算列及其REFRENCES约束等。,CREATE TABLE student.课程(课程编号 char(6)PRIMARY KEY,课程名称 char(24)NOT NULL,责任教师 char(6)CONSTRAINT duty_teacher FOREIGN KEY REFERENCES student.教师(教师编号),学时 tinyint NOT NULL,课程性质 char(8)CHECK(课程性质IN(公共基础,专业基础,专业选修,任意选修),插入课程表记录，注意相关约束。,例20,CREATE TABLE student.选课

39、(学号 char(8)FOREIGN KEY REFERENCES student.学生,课程编号 char(6)FOREIGN KEY REFERENCES student.课程,考试成绩 tinyint CHECK(考试成绩 BETWEEN 0 AND 100)DEFAULT NULL PRIMARY KEY(学号,课程编号),INSERT INTO student.选课(学号,课程编号,考试成绩)VALUES(04014122,B00016,88),例21,CREATE TABLE student.选课(学号 char(8)FOREIGN KEY REFERENCES student.学

40、生,课程编号 char(6)FOREIGN KEY REFERENCES student.课程,考试成绩 tinyint CHECK(考试成绩 BETWEEN 0 AND 100)DEFAULT NULL PRIMARY KEY(学号,课程编号),INSERT INTO student.选课 VALUES(04014111,X00201,NULL),例22,CREATE TABLE student.选课(学号 char(8)FOREIGN KEY REFERENCES student.学生,课程编号 char(6)FOREIGN KEY REFERENCES student.课程,考试成绩 t

41、inyint CHECK(考试成绩 BETWEEN 0 AND 100)DEFAULT NULL PRIMARY KEY(学号,课程编号),INSERT INTO student.选课VALUES(04014111,B00016,DEFAULT),例23,CREATE TABLE student.选课(学号 char(8)FOREIGN KEY REFERENCES student.学生,课程编号 char(6)FOREIGN KEY REFERENCES student.课程,考试成绩 tinyint CHECK(考试成绩 BETWEEN 0 AND 100)DEFAULT NULL PRI

42、MARY KEY(学号,课程编号),INSERT INTO student.选课(学号,课程编号)VALUES(04014101,B00018),例24违反参照完整性的输入,CREATE TABLE student.选课(学号 char(8)FOREIGN KEY REFERENCES student.学生,课程编号 char(6)FOREIGN KEY REFERENCES student.课程,考试成绩 tinyint CHECK(考试成绩 BETWEEN 0 AND 100)DEFAULT NULL PRIMARY KEY(学号,课程编号),INSERT INTO student.选课V

43、ALUES(04016116,B00017，52),插入操作时检查完整性？,检查实体完整性规则检查参照完整性规则检查用户定义完整性规则,检查实体完整性规则,如果插入元组的主关键字的属性不为空值、并且相应的属性值在关系中不存在（即保持唯一性），则可以执行插入操作，否则不可以执行插入操作。,检查参照完整性规则,如果是向被参照关系插入元组，则无须检查参照完整性；如果是向参照关系插入元组，则要检查外部关键字属性上的值是否在被参照关系中存在对应的主关键字的值，如果存在则可以执行插入操作，否则不允许执行插入操作。另外，如果插入元组的外部关键字允许为空值，则当外部关键字是空值时也允许执行插入操作。,检查用户

44、定义完整性规则,如果插入的元组在相应的属性值上遵守了用户定义完整性规则(包括CHECK约束、UNIQUE约束、NULL或NOT NULL约束)，则可以执行插入操作，否则不可以执行插入操作。,删除操作及其完整性约束,SQL的删除语句是DELETE，常用格式：DELETE FROM schema_name.table_nameWHERE 其中：schema_name指出模式名；table_name指出从哪个表删除记录；用来指出删除记录的条件，默认是删除全部记录。,例25,DELETE student.学生 WHERE 姓名=吴红霞,删除操作失败，违背参照完整性,CREATE TABLE stude

45、nt.选课(学号 char(8)FOREIGN KEY REFERENCES student.学生,课程编号 char(6)FOREIGN KEY REFERENCES student.课程,考试成绩 tinyint CHECK(考试成绩 BETWEEN 0 AND 100)DEFAULT NULL PRIMARY KEY(学号,课程编号),例26,DELETE student.教师 WHERE 教师编号=011122,ALTER TABLE student.课程ADD 责任教师char(6)NULLCONSTRAINT ref_teacher FOREIGN KEY REFERENCES s

46、tudent.教师(教师编号)ON DELETE SET NULL ON UPDATE CASCADE,删除成功。如果有课程记录的责任教师参照被删除记录，则将该课程记录的责任教师字段置为NULL。,举例27,DELETE student.选课 WHERE 课程编号=B00018 AND 考试成绩 IS NULL,删除成功，选课表不是被参照表。NULL是概念不是值，所以不能用比较运算符。,删除操作只需要检查参照完整性,如果删除的是参照关系的元组，则不需要进行参照完整性检查，可以执行删除操作。如果删除的是被参照关系的元组，则检查被删除元组的主关键字属性的值是否被参照关系中某个元组的外部关键字引用，

47、如果未被引用则可以执行删除操作；否则可能有4种情况：拒绝删除、空值删除、级联删除、默认值删除,REFERENCES referenced_table_name(ref_column,.n)ON DELETE NO ACTION|CASCADE|SET NULL|SET DEFAULT,更新操作及其完整性约束,SQL更新记录的语句是UPDATE，常用格式：UPDATE schema_name.table_nameSET column_name=expression|DEFAULT|NULL,.n WHERE 其中：schema_name指定模式名；table_name给出要更新数据的表；SET短

48、语说明要更新的列及其值；参数“,.n”说明一次可以更新多列。WHERE指定更新哪些记录，即用逻辑表达式指定更新条件。,举例28，29,UPDATE student.教师 SET 教师编号=040101 WHERE 姓名=刘昌勇,图3-1 样本数据库,与哪个完整性约束有关？,UPDATE student.教师 SET 教师编号=020101 WHERE 姓名=刘昌勇,例30 将04014122号学生的B00101课程的考试成绩修改为78分,举例30,UPDATE student.选课 SET 考试成绩=78 WHERE 学号=04014122 AND 课程编号=B00101,执行更新操作时检查

49、完整性,执行更新操作可以看作是先删除旧的元组，然后再插入新的元组。所以执行更新操作时的完整性检查综合了插入和删除操作的数据完整性检查。,思考题,执行插入操作时会进行哪些数据完整性检查？如果违背某项数据完整性约束结果会怎样？在INSERT语句中可以使用关键词DEFAULT插入默认值，如果在定义表时没有定义相应的默认值结果会怎样？表达式“考试成绩=NULL”是否正确？为什么？当插入的表有计算列时要注意什么？执行删除操作时会进行哪些数据完整性检查？,思考题,当删除被参照表的记录时会进行参照完整性检查，如果发现有参照表的记录参照被删除的记录时有哪些处理方法？具体如何实施？修改教师表的语句UPDATE student.教师 SET 院系=2 WHERE 姓名=张建平能正确执行吗？为什么？执行更新操作时会进行哪些数据完整性检查？为什么？,

展开阅读全文