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

2019/12/1,数据库系统原理,韶关学院计算机科学与技术系,第5章关系数据库标准语言SQL,5.1SQL概述及特点5.2数据定义语句5.3数据查询语句5.4数据更新语句5.5嵌入式SQL5.6数据控制机制和语句,5.1SQL概述及特点,1.SQL的主要功能(1)数据定义功能定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。(2)数据操纵功能包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。(3)数据控制功能通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。,2.SQL的特点,(1)SQL具有自含式和嵌入式两种形式。(2)SQL具有语言简洁、易学易用的特点。(3)SQL支持三级模式结构。全体基本表构成了数据库的模式。视图和部分基本表构成了数据库的外模式。数据库的存储文件和它们的索引文件构成了关系数据库的内模式。,5.2数据定义语句,5.2.1基本表的定义和维护1.定义基本表定义基本表语句的一般格式为：CREATETABLE库名表名(列名数据类型列级完整性约束条件，列名数据类型列级完整性约束条件，n，表级完整性约束条件，n)；,(1)SQL支持的数据类型,(2)列级完整性的约束条件,针对属性值设置的限制条件。1)NOTNULL或NULL约束。NOTNULL约束不允许字段值为空，而NULL约束允许字段值为空。2)UNIQUE约束。惟一性约束，即不允许列中出现重复的属性值。3)DEFAULT约束。默认值约束。DEFAULT约束名默认值FOR列名4)CHECK约束。检查约束。CONSTRAINT约束名CHECK(约束条件表达式),(3)表级完整性约束条件,涉及到关系中多个列的限制条件。1)UNIQUE约束。惟一性约束。2)PRIMARYKEY约束。定义主码，保证惟一性和非空性。CONTRAINT约束名PRIMARYKEYCLUSTERED(列组)3)FOREIGNKEY约束。用于定义参照完整性。CONTRAINT约束名FOREIGNKEY(外码)REFERENCES被参照表名(与外码对应的主码名),CREATETABLE学生(学号CHAR(5)NOTNULLUNIQUE，姓名CHAR(8)NOTNULL，年龄SMALLINT，性别CHAR(2)，所在系CHAR(20)，DEFAULTC120FOR年龄，CONSTRAINTC2CHECK(性别IN(男，女)；CREATETABLE课程(课程号CHAR(5)PRIMARYKEY，课程名CHAR(20)，先行课CHAR(5)；CREATETABLE选课(学号CHAR(5)，课程号CHAR(5)，成绩SMALLINT，CONSTRAINTC3CHECK(成绩BETWEEN0AND100)，CONSTRAINTC4PRIMARYKEY(学号，课程号)，CONSTRAINTC5FOREIGNKEY(学号)REFERENCES学生(学号)，CONSTRAINTC6FOREIGNKEY(课程号)REFERENCES课程(课程号)；,【例5-1】,建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).,2.修改基本表和删除基本表,ALTERTABLE表名ADD(新列名数据类型完整性约束，n)DROP完整性约束名MODIFY(列名数据类型，n)；(1)使用ADD子句增加新列【例5-2】向课程表中增加“学时”字段。ALTERTABLE课程ADD学时SMALLINT；(2)使用MODIFY子句修改列的原定义(3)使用DROP子句删除指定的完整性约束条件【例5-3】删除学生表中对年龄的默认值的定义。ALTERTABLE学生DROPC1；删除基本表的一般格式为：DROPTABLE表名；,5.2.2索引的定义和维护,1.索引的作用1)使用索引可以明显地加快数据查询的速度。2)使用索引可保证数据的惟一性。3)使用索引可以加快连接速度。2.建立索引的原则1)索引的建立和维护由DBA和DBMS完成。2)大表应当建索引，小表则不必建索引。3)对于一个基本表，不要建立过多的索引。4)根据查询要求建索引。,3.建立和删除索引的格式,建立格式为：CREATEUNIQUECLUSTERINDEX索引名ON表名(列名次序，列名次序)；删除索引格式为：DROPINDEX索引名；【例5-4】为学生_课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立索引；课程表按课程号升序建惟一索引；选课表按学号升序和课程号降序建惟一索引。CREATEUNIQUEINDEX学号ON学生(学号)；CREATEUNIQUEINDEX课程号ON课程(课程号)；CREATEUNIQUEINDEX选课号ON选课(学号ASC，课程号DESC)；,5.2.3视图的定义和维护,1.视图的优点1)视图能够简化用户的操作。2)视图机制可以使用户以不同的方式看待同一数据。3)视图对数据库的重构提供了一定程度的逻辑独立性。4)视图可以对机密的数据提供安全保护。2.视图定义的格式一般格式为：CREATEVIEW视图名(列名组)AS子查询WITHCHECKOPTION；,视图举例,【例5-6】建立计算机系学生的视图。CREATEVIEW计算机系学生ASSELECT学号，姓名，年龄FROM学生WHERE所在系=计算机系；【例5-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩。CREATEVIEW学生成绩(学号，姓名，课程名，成绩)ASSELECT学生.学号，学生.姓名，课程.课程名，选课.成绩FORM学生，课程，选课WHERE学生.学号=选课.学号AND程.课程名=选课.课程号AND学生.所在系=计算机系；,3.视图的删除、查询和维护,视图删除语句的一般格式为：DROPVIEW视图名；视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。,5.3数据查询语句,5.3.1数据查询的基本语法1.SELECT语句的语法SELECT目标列组FROM数据源WHERE元组选择条件GROUPBY分列组HAVING组选择条件ORDERBY排序列1排序要求1，n；,语法说明,(1)SELECT子句：指明目标列（字段、表达式、函数表达式、常量）。基本表中相同的列名表示为：表名.列名(2)FROM子句：指明数据源。表间用“，”分割。数据源不在当前数据库中，使用“数据库名.表名”表示。一表多用，用别名标识。定义表别名：表名别名(3)WHERE子句：元组选择条件。(4)GROUPBY子句：结果集分组。当目标列中有统计函数，则统计为分组统计，否则为对整个结果集统计。子句后带上HAVING子句表达组选择条件（带函数的表达式）。(5)ORDERBY子句：排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。,2.SELECT语句的操作符,(1)算术操作符+（加号）、（减号）、*（乘号）和/（除号）。(2)比较操作符=（等于）、（大于）、=（大于等于）、!=（不等于）、（小于大于）、!（不大于）和!、=、90；,例子,(2)自身连接例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。,课程的先行关系链为：C5C4C3C2C1，课程的间接关系链为：C5C3C1。,A,B,结果,【例5-18】,查询每一门课的间接先行课。SELECTA.课程号，A.课程名，B.先行课FROM课程A，课程BWHEREA.先行课=B.课程号,(3)外部连接,左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接符号为“*=”，右外部连接符号为“=*”。外部连接中不匹配的分量用NULL表示。,职工表部门表,连接的结果集,内连接的结果集,左外部连接的结果集,内连接：SELECT职工.*，部门名称，电话FROM职工，部门WHERE职工.所在部门=部门.部门号；左外部连接：SELECT职工.*，部门名称，电话FROM职工，部门WHERE职工.所在部门*=部门.部门号；右外部连接：SELECT职工.*，部门名称，电话FROM职工，部门WHERE职工.所在部门=*部门.部门号；,【例5-19】,用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句,3.嵌套查询,使用IN操作符的嵌套查询【例5-20】求选修了高等数学的学生学号和姓名。SELECT学号，姓名FROM学生WHERE学号IN(SELECT学号FROM选课WHERE课程号IN(SELECT课程号FROM课程WHERE课程名=高等数学)；该题也可以使用下面的连接查询表达。SELECT学生.学号，姓名FROM学生，课程，选课WHERE学生.学号=课程.学号AND课程.课程号=选课.课程号AND课程.课程名=高等数学；,(2)使用比较符的嵌套查询,【例5-21】求C1课程的成绩高于张三的学生学号和成绩。SELECT学号，成绩FROM选课WHERE课程号=C1AND成绩(SELEC成绩FROM选课WHERE课程号=C1AND学号=(SELECT学号FROM学生WHERE姓名=张三)；,(3)使用ANY或ALL操作符的嵌套查询,格式为：字段比较符ANY|ALL子查询,例子,【例5-22】求其他系中比计算机系某一学生年龄小的学生。SELECT*FROM学生WHERE年龄计算机系；【例5-23】求其他系中比计算机系学生年龄都小的学生。SELECT*FROM学生WHERE年龄计算机系；,(4)使用EXISTS操作符的嵌套查询,【例5-24】求选修了C2课程的学生姓名。SELECT姓名FROM学生WHEREEXISTS(SELECT*FROM选课WHERE学生.学号=学号AND课程号=C2)；【例5-25】求没有选修C2课程的学生姓名。SELECT姓名FROM学生WHERENOTEXISTS(SELECT*FROM选课WHERE学生.学号=学号AND课程号=C2)；,例子,【例5-26】查询选修了全部课程的学生的姓名。SELECT姓名FROM学生WHERENOTEXISTS(SELECT*FROM课程WHERENOTEXISTS(SELECT*FROM选课WHERE学生.学号=学号AND课程.课程号=课程号)；,例子,【例5-27】求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。SELECT学号，姓名FROM学生WHERENOTEXISTS(SELECT*FROM选课选课1WHERE选课1.学号=S2ANDNOTEXISTS(SELECT*FROM选课选课2WHERE学生.学号=选课2.学号AND选课2.课程号=选课1.课程号)；,4.组合查询,【例5-28】求选修了C1课程或选修了C2课程的学生学号。SELECT学号FROM选课WHERE课程号=C1UNIONSELECT学号FROM选课WHERE课程号=C2【例5-29】求选修C1课程，并且也选修C2课程的学生学号。SELECT学号FROM选课WHERE课程号=C1INTERSECTSELECT学号FROM选课WHERE课程号=C2；,【例5-30】求选修了C1课程但没有选修C2课程的学生学号。,SELECT学号FROM选课WHERE课程号=C1MINUSSELECT学号FROM选课WHERE课程号=C2；本例也可以用下面的EXISTS嵌套查询表示。SELECT学号FROM选课选课1WHERE课程号=C1ANDNOTEXISTS(SELECT学号FROM选课选课2WHERE选课1.学号=选课2.学号AND选课2.课程号=C2)；,5.使用分组和SQL函数查询,【例5-31】求学生的总人数。SELECTCOUNT(*)FROM学生；,例子,【例5-32】求选修了课程的学生人数。SELECTCOUNT(DISTINCT学号)FROM选课；【例5-33】求课程和选修该课程的人数。SELECT课程号，COUNT(学号)FROM选课GROUPBY课程号；【例5-34】求选修课超过3门课的学生学号。SELECT学号FROM选课GROUPBY学号HAVINGCOUNT(*)3；,5.4数据更新语句,5.4.1数据插入语句1.使用常量插入单个元组格式为：INSERTINTO表名(属性列1，属性列2)VALUES(常量1，常量2)；,例子,【例5-35】将一个新学生记录(学号：98010，姓名：张三，年龄：20，所在系：计算机系)插入到学生表中。INSERTINTO学生VALUES(98010，张三，20，计算机系)；【例5-36】插入一条选课记录(学号：98011，课程号：C10，成绩不详)。INSERTINTO选课(学号，课程号)VALUES(98011，C10)；,2.在表中插入子查询的结果集,INSERTINTO表名(属性列1，属性列2)子查询；【例5-37】求每个系学生的平均年龄，把结果存入数据库中。CREATETABLE系平均年龄(系名称CHAR(20)，平均年龄SMALLINT)；INSERTINTO系平均年龄SELECT所在系，AVG(ALL年龄)FROM学生GROUPBY所在系；,5.4.2数据修改语句,UPDATE表名SET列名=表达式，列名=表达式，nWHERE条件；【例5-38】将学生表中全部学生的年龄加上2岁。UPDATE学生SET年龄=年龄+2；【例5-39】将选课表中的数据库课程的成绩乘以1.2。UPDATE选课SET成绩=成绩*1.2WHERE课程号=(SELECT课程号FROM课程WHERE课程名=数据库)；,5.4.3数据删除语句,DELETEFROM表名WHERE条件；【例5-40】删除艺术系的学生记录及选课记录。DELETEFROM选课WHERE学号IN(SELECT学号FROM学生WHERE所在系=艺术系)；DELETEFROM学生WHERE所在系=艺术系；,5.5嵌入式SQL,5.5.1嵌入式SQL的特点1.嵌入式SQL应注意的问题1)SQL和主语言的配合问题。2)合理选择主语言。2.SQL嵌入主语言时必须解决的三个问题(1)如何区别SQL和主语言(2)使数据库的工作单元与程序工作单元之间能够通信1)主语言通过主变量向SQL语句提供参数。2)SQL语句的当前工作状态和运行环境数据要返馈给应用程序。(3)使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾,5.5.2不用游标的SQL语句,1.几种不需要使用游标的SQL语句(1)用于说明主变量的说明性语句SQL的说明性语句主要有两条：EXECSQLBEGINDECLARESECTION；EXECSQLENDDECLARESECTION；(2)数据定义和数据控制语句(3)查询结果为单记录的查询语句(4)数据的插入语句和某些数据删除、修改语句独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。,2.不用游标的查询语句,EXECSQLSELECTALL|DISTINCT目标列表达式，nINTO主变量指示变量，nFROM表名或视图名，nWHERE条件表达式；1)在语句开始前要加EXECSQL前缀。2)该查询语句中又扩充了INTO子句。3)在WHERE子句的条件表达式中可以使用主变量。4)由于查询的结果集中只有一条记录，该语句中不必有排序和分组子句。5)INTO子句中的主变量后面跟有指示变量时：结果列值为NULL，指示变量为负值，结果列不向该主变量赋值；传递正常，指示变量的值为0；主变量宽度不够，则指示变量的值为数据截断前的宽度。,例子,【例5-41】查询学号为主变量givesno、课号为主变量givecno的值的学生选课记录。EXECSQLSELECT学号，课程号，成绩INTO:Sno，:Cno，:grade:gradeidFROM选课WHERE学号=:givesnoAND课程号=:givecno；3.不用游标的数据维护语句(1)不用游标的数据删除语句【例5-42】删除学号由主变量Sno决定的学生记录。EXECSQLDELETEFROM学生WHERE学号=:Sno；,(2)不用游标的数据修改语句,【例5-43】将计算机系所有学生的年龄都加上主变量Raise。EXECSQLUPDATE学生SET年龄=年龄+:RaiseWHERE所在系=计算机系；【例5-44】将计算机系学生的年龄置空。Raiseid=-1EXECSQLUPDATE学生SET年龄=年龄+:Raise:Raiseid；(3)不用游标的数据插入语句【例5-45】将学号为主变量Sno、课程号为Cno的选课记录，插到库中。EXECSQLINSERTINTO选课VALUES(:Sno，:Cno)；,5.5.3使用游标的SQL,(1)定义游标命令EXECSQLDECLARE游标名CURSORFOR子查询FORUPDATEOF字段名1，n；删除和修改数据的语句中，WHERE为：WHERECURRENTOF游标名【例5-46】定义按主变量DEPT查询系里学生的游标。EXECSQLDECLARESXCURSORFORSELECT*FROM学生WHERE所在系=:DEPT；,(2)打开、推进和关闭游标命令,EXECSQLOPEN游标名；执行对应的查询语句，并将游标指向结果集的第一条记录前。打开的游标处于活动状态，可以被推进。EXECSQLFETCH游标名INTO主变量组；将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。EXECSQLCLOSE游标名；【例5-47】打开SX游标。DEPT=计算机系EXECSQLOPENSX；【例5-48】将打开的指向系的游标向前推进。EXECSQLFETCHSXINTO:Sno，: