第五章 关系数据库标准语言――SQL

第五章关系数据库标准语言——SQL本章教学目标、重点和难点1.本章教学目标：使学生了解SQL的功能和语言特点；熟练掌握用SQL进行表的定义、数据查询和数据更新，掌握SQL的数据控制方法。2.教学重点：SQL的数据定义、数据查询、数据维护和数据控制表示方法3.教学难点：用SQL实现嵌套查询、组合查询、使用分组和SQL函数查询。本章内容1.SQL的功能及特点2.数据定义语句3.数据查询语句4.数据更新语句5.嵌入式SQL6.数据控制机制和语句SQL的功能及特点19741975-19791986-198719921999由Boyce和Chamberlin提出IBM公司研制了关系DBMS原型，并实现该语言美国批准SQL作为美国标准，随后ISO也通过这一标准公布了SQL-92标准公布了SQL-99，也称作SQL3SQL的功能及特点数据定义数据控制数据操纵SQL语句分类SQL的功能及特点SQL语句分类数据定义数据操纵数据控制定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。SQL的功能及特点SQL的特点1）SQL具有自主式语言和嵌入式语言两种使用方式；2）SQL具有语言简洁、易学易用的特点；SQL功能命令动词数据定义（数据模式定义、删除、修改）Create、Drop、Alter数据操纵（数据查询和维护）Select、Insert、Update、Delete数据控制（数据存取控制权和收权）Grant、RemoveSQL的功能及特点SQL的特点3）SQL支持三级模式结构存储文件1存储文件2基本表1基本表2基本表3基本表4视图1视图2SQL外模式模式内模式数据定义语句操作对象创建语句删除语句修改语句基本表CreatetableDroptableAltertable索引CreateindexDropindex视图CreateviewDropview数据库CreatedatabaseDropdatabaseAlterdatabase1.语句格式约定符号尖括号中括号[]大括号{}或分隔符|[,…n]实际语义任选项必选项，必选其中一项前面的项可重复多次数据定义语句2.一般语法规定SQL中的数据项（包括列项、表和视图）分隔符为“,”，其字符串常数的定界符用单引号“’”表示3.SQL特殊语法规定SQL的关键词一般使用大写字母表示；语句的结束符为“;”语句一般应采用格式化书写方式数据定义语句基本表的定义和维护1.基本表的定义定义基本表语句的一般格式为：CREATETABLE[〈库名〉]〈表名〉(〈列名〉〈数据类型〉[〈列级完整性约束条件〉][，〈列名〉〈数据类型〉[〈列级完整性约束条件〉]][，…n][，〈表级完整性约束条件〉][，…n])；数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE课程(课程号CHAR(5)PRIMARYKEY，课程名CHAR(20)，先行课CHAR(5))；数据定义语句（1）SQL支持的数据类型类型表示类型说明数值型数据SMALLINT半字长二进制证书，15bit数据INTEGER或INT全字长（4字长）整数，31bit数据DECIMAL(p[,q])十进制数，共p位，其中小数点后q位，0《q《p，q=0时可省略不写FLOAT双字长浮点数字符型数据CHARTER(n)或CHAR(n)长度为n的定长图形字符串VARCHAR(n)最大长度为n的变长字符串特殊数据类型GRAPHIC(n)长度为n的定长图形字符串VARGRAPHIC(n)最大长度为n的变长图形字符串日期时间型DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间数据定义语句（2）列级完整性的约束条件1）NOTNULL或NULL约束NOTNULL约束不允许字段值为空而NULL约束允许字段值为空。2）UNIQUE约束UNIQUE约束是惟一性约束，即不允许列中出现重复的属性值。3）DEFAULT约束DEFAULT为默认值约束。DEFAULT〈约束名〉〈默认值〉FOR〈列名〉4）CHECK约束CHECK为检查约束。CONSTRAINT〈约束名〉CHECK(〈约束条件表达式〉)数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE学生(学号CHAR(5)NOTNULLUNIQUE，姓名CHAR(8)NOTNULL，年龄SMALLINT，性别CHAR(2)，所在系CHAR(20)，DEFAULTC120FOR年龄，CONSTRAINTC2CHECK(性别IN('男'，'女')))；数据定义语句（3）表级完整性的约束条件1）UNIQUE约束UNIQUE约束是惟一性约束，当要求列祖的值不能有重复值时，使用UNIQUE约束定义。2）PRIMARYKEY约束定义主码，保证惟一性和非空性。CONTRAINT〈约束名〉PRIMARYKEY[CLUSTERED](〈列组〉)3）FOREIGNKEY约束用于定义参照完整性。CONTRAINT〈约束名〉FOREIGNKEY(〈外码〉)REFERENCES〈被参照表名〉(〈与外码对应的主码名〉)数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE选课(学号CHAR(5)，课程号CHAR(5)，成绩SMALLINT，CONSTRAINTC3CHECK(成绩BETWEEN0AND100)，CONSTRAINTC4PRIMARYKEY(学号，课程号)，CONSTRAINTC5FOREIGNKEY(学号)REFERENCES学生(学号)，CONSTRAINTC6FOREIGNKEY(课程号)REFERENCES课程(课程号))；数据定义语句2.修改基本表ALTERTABLE〈表名〉[ADD(〈新列名〉〈数据类型〉[完整性约束][，…n])][DROP〈完整性约束名〉][MODIFY(〈列名〉〈数据类型〉[，…n])]；(1)使用ADD子句增加新列【例5-2】向课程表中增加“学时”字段。ALTERTABLE课程ADD学时SMALLINT；(2)使用MODIFY子句修改列的原定义：MODIFY子句主要用于加宽原列的宽度(3)使用DROP子句删除指定的完整性约束条件【例5-3】删除学生表中对年龄的默认值的定义。ALTERTABLE学生DROPC1；数据定义语句3.删除基本表删除基本表的一般格式为：DROPTABLE〈表名〉；数据定义语句索引的定义和维护1.索引的作用1)使用索引可以明显地加快数据查询的速度；先将索引文件读入内存，根据索引项找到元组的地址，然后再根据地址将元组数据直接读入计算机。由于索引文件中只含有索引项和元组地址，所以文件小，一般可一次读入内存索引文件中的索引项是经过排序的，可以很快地找到索引项值和元组地址数据定义语句索引的定义和维护1.索引的作用1)使用索引可以明显地加快数据查询的速度；2)使用索引可保证数据的惟一性；3)使用索引可以加快连接速度。2.建立索引的原则1)索引的建立和维护由DBA和DBMS完成；2)大表应当建索引，小表则不必建索引；3)对于一个基本表，不要建立过多的索引；4)根据查询要求建索引。数据定义语句3.建立索引的格式建立格式为：CREATE[UNIQUE][CLUSTER]INDEX〈索引名〉ON〈表名〉(〈列名〉[〈次序〉][，〈列名〉[〈次序〉]]…)；1)表名是要建索引的基本表的名字；2)每个列名后面还可以用次序指定索引值的排列次序，次序可选ASC（升序）或DESC（降序），默认值为ASC；3)UNIQUE表示该索引的每一个索引值只对应唯一的数据记录；4)CLUSTER表示要建立的索引是聚簇索引，使基本表中数据的物理顺序与索引项的排列顺序一致。数据定义语句3.建立索引的格式【例5-4】为学生_课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立索引；课程表按课程号升序建惟一索引；选课表按学号升序和课程号降序建惟一索引。CREATEUNIQUEINDEX学号ON学生(学号)；CREATEUNIQUEINDEX课程号ON课程(课程号)；CREATEUNIQUEINDEX选课号ON选课(学号ASC，课程号DESC)；数据定义语句4.删除索引删除索引格式为：DROPINDEX〈索引名〉；例：删除学生表的学生姓名索引DROPINDEX学生姓名数据定义语句视图的定义和维护1.视图的优点视图是根据子模式设计的关系，由一个或几个基本表导出的虚表。1)视图能够简化用户的操作；视图机制可以减少用户对数据库中数据结构的调整操作，使用户将注意力集中在所关心的数据上。2）视图机制可以使用户以不同的方式看待同一数据；当多用户共享同一个数据库的数据时，通过视图机制可以实现各个用户对数据的不同使用要求。3）视图可以对机密的数据提供安全保护数据定义语句1.视图的优点4）视图对数据库的重构提供了一定程度的逻辑独立性；在关系数据库中，数据库的重构往往是不可避免的。但由于视图机制，将为应用程序提供了原来使用的关系，保持外模式。原表：学生（学号，姓名，性别，年龄，所在系）分成：SX（学号，姓名，年龄）SY（学号，性别，所在系）CREATEVIEW学生(学号，姓名，性别，年龄，所在系）ASSELECTSX.学号，SX.姓名，SX.性别，SY.年龄，SY.所在系FROMSX,SYWHERESX.学号=SY.学号；数据定义语句2.视图定义的格式一般格式为：CREATEVIEW〈视图名〉[(列名组)]AS〈子查询〉[WITHCHECKOPTION]；（1）WITHCHECKOPTION选项表示在对视图进行UPDATE、INSERT和DELETE操作时，要保证操作的数据满足视图定义中的谓词条件。该谓词条件是视图子查询中的WHERE子句的条件。数据定义语句2.视图定义的格式一般格式为：CREATEVIEW〈视图名〉[(列名组)]AS〈子查询〉[WITHCHECKOPTION]；（2）组成视图的属性列名全部省略或者全部指定若省略了视图的各个属性列名，则由子查询中的SELECT子句的目标列代替必须明确指定列名的情况①某个目标列是集函数或列表达式；②子查询中使用多个表（或视图），并且目标列中含有相同的属性名；③需要在视图中改用新的、更合适的列名。数据定义语句2.视图定义的格式【例5-6】建立计算机系学生的视图。CREATEVIEW计算机系学生ASSELECT学号，姓名，年龄FROM学生WHERE所在系=‘计算机系’；数据定义语句2.视图定义的格式【例5-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩CREATEVIEW学生成绩(学号，姓名，课程名，成绩)ASSELECT学生.学号，学生.姓名，课程.课程名，选课.成绩FORM学生，课程，选课WHERE学生.学号=选课.学号AND程.课程名=选课.课程号AND学生.所在系=‘计算机系’；数据定义语句2.视图定义的格式【例5-8】将学生的学号、总成绩、平均成绩定义成一个视图。CREATEVIEW学生成绩统计(学号，总成绩，平均成绩)ASSELECT学号，SUM（成绩），AVG（成绩）FORM选课GROUPBY学号；3.视图的删除DROPVIEW视图名数据定义语句4.视图的查询和维护视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。一般的数据库系统不支持对下列几种情况的视图进行数据更新操