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

第五章 关系数据库标准语言SQL,本章教学目标、重点和难点,本章教学目标：使学生了解SQL的功能和语言特点；熟练掌握用SQL进行表的定义、数据查询和数据更新，掌握SQL的数据控制方法。2.教学重点：SQL的数据定义、数据查询、数据维护和数据控制表示方法 3.教学难点：用SQL实现嵌套查询、组合查询、使用分组和SQL函数查询。,本章内容,SQL的功能及特点数据定义语句数据查询语句数据更新语句嵌入式SQL数据控制机制和语句,SQL的功能及特点,1974,1975-1979,1986-1987,1992,1999,由Boyce和Chamberlin提出,IBM公司研制了关系DBMS原型，并实现该语言,美国批准SQL作为美国标准，随后ISO也通过这一标准,公布了SQL-92标准,公布了SQL-99，也称作SQL3,SQL的功能及特点,数据定义,数据控制,数据操纵,SQL语句分类,SQL的功能及特点,SQL语句分类,数据定义,数据操纵,数据控制,定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。,包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。,通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。,SQL的功能及特点,SQL的特点1）SQL具有自主式语言和嵌入式语言两种使用方式；2）SQL具有语言简洁、易学易用的特点；,SQL的功能及特点,SQL的特点3）SQL支持三级模式结构,数据定义语句,1.语句格式约定符号,数据定义语句,2.一般语法规定SQL中的数据项（包括列项、表和视图）分隔符为“,”，其字符串常数的定界符用单引号“”表示3.SQL特殊语法规定SQL的关键词一般使用大写字母表示；语句的结束符为“;”语句一般应采用格式化书写方式,数据定义语句,基本表的定义和维护1.基本表的定义,定义基本表语句的一般格式为：CREATE TABLE 库名表名(列名数据类型列级完整性约束条件，列名数据类型列级完整性约束条件，n，表级完整性约束条件，n)；,数据定义语句,建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).,CREATE TABLE 课程(课程号 CHAR(5)PRIMARY KEY，课程名 CHAR(20)，先行课 CHAR(5)；,数据定义语句,（1）SQL支持的数据类型,数据定义语句,（2）列级完整性的约束条件1）NOT NULL或NULL约束NOT NULL约束不允许字段值为空而NULL约束允许字段值为空。2）UNIQUE约束UNIQUE约束是惟一性约束，即不允许列中出现重复的属性值。3）DEFAULT约束DEFAULT为默认值约束。DEFAULT约束名默认值FOR列名4）CHECK约束CHECK为检查约束。CONSTRAINT约束名CHECK(约束条件表达式),数据定义语句,建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).,CREATE TABLE 学生(学号 CHAR(5)NOT NULL UNIQUE，姓名 CHAR(8)NOT NULL，年龄 SMALLINT，性别 CHAR(2)，所在系 CHAR(20)，DEFAULT C1 20 FOR 年龄，CONSTRAINT C2 CHECK(性别 IN(男，女)；,数据定义语句,（3）表级完整性的约束条件1）UNIQUE约束UNIQUE约束是惟一性约束，当要求列祖的值不能有重复值时，使用UNIQUE约束定义。2）PRIMARY KEY约束定义主码，保证惟一性和非空性。CONTRAINT约束名PRIMARY KEY CLUSTERED(列组)3）FOREIGN KEY约束用于定义参照完整性。CONTRAINT约束名FOREIGN KEY(外码)REFERENCES被参照表名(与外码对应的主码名),数据定义语句,建立基本表：学生(学号，姓名，年龄，性别，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).,CREATE TABLE 选课(学号 CHAR(5)，课程号 CHAR(5)，成绩 SMALLINT，CONSTRAINT C3 CHECK(成绩 BETWEEN 0 AND 100)，CONSTRAINT C4 PRIMARY KEY(学号，课程号)，CONSTRAINT C5 FOREIGN KEY(学号)REFERENCES 学生(学号)，CONSTRAINT C6 FOREIGN KEY(课程号)REFERENCES 课程(课程号)；,数据定义语句,2.修改基本表,ALTER TABLE表名 ADD(新列名数据类型完整性约束，n)DROP完整性约束名 MODIFY(列名数据类型，n)；,使用ADD子句增加新列【例5-2】向课程表中增加“学时”字段。ALTER TABLE 课程 ADD 学时 SMALLINT；使用MODIFY子句修改列的原定义：MODIFY子句主要用于加宽原列的宽度使用DROP子句删除指定的完整性约束条件【例5-3】删除学生表中对年龄的默认值的定义。ALTER TABLE 学生 DROP C1；,数据定义语句,3.删除基本表,删除基本表的一般格式为：DROP TABLE表名；,数据定义语句,索引的定义和维护1.索引的作用,使用索引可以明显地加快数据查询的速度；先将索引文件读入内存，根据索引项找到元组的地址，然后再根据地址将元组数据直接读入计算机。由于索引文件中只含有索引项和元组地址，所以文件小，一般可一次读入内存索引文件中的索引项是经过排序的，可以很快地找到索引项值和元组地址,数据定义语句,索引的定义和维护1.索引的作用,使用索引可以明显地加快数据查询的速度；使用索引可保证数据的惟一性；使用索引可以加快连接速度。,2.建立索引的原则,索引的建立和维护由DBA和DBMS完成；大表应当建索引，小表则不必建索引；对于一个基本表，不要建立过多的索引；根据查询要求建索引。,数据定义语句,3.建立索引的格式,建立格式为：CREATE UNIQUE CLUSTER INDEX索引名 ON表名(列名次序，列名次序)；,是要建索引的基本表的名字；每个后面还可以用指定索引值的排列次序，次序可选ASC（升序）或DESC（降序），默认值为ASC；UNIQUE表示该索引的每一个索引值只对应唯一的数据记录；CLUSTER表示要建立的索引是聚簇索引，使基本表中数据的物理顺序与索引项的排列顺序一致。,数据定义语句,3.建立索引的格式,【例5-4】为学生_课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立索引；课程表按课程号升序建惟一索引；选课表按学号升序和课程号降序建惟一索引。CREATE UNIQUE INDEX 学号 ON 学生(学号)；CREATE UNIQUE INDEX 课程号 ON 课程(课程号)；CREATE UNIQUE INDEX 选课号 ON 选课(学号 ASC，课程号 DESC)；,数据定义语句,4.删除索引,删除索引格式为：DROP INDEX 索引名；例：删除学生表的学生姓名索引DROP INDEX 学生姓名,数据定义语句,视图的定义和维护1.视图的优点视图是根据子模式设计的关系，由一个或几个基本表导出的虚表。,视图能够简化用户的操作；视图机制可以减少用户对数据库中数据结构的调整操作，使用户将注意力集中在所关心的数据上。2）视图机制可以使用户以不同的方式看待同一数据；当多用户共享同一个数据库的数据时，通过视图机制可以实现各个用户对数据的不同使用要求。3）视图可以对机密的数据提供安全保护,数据定义语句,1.视图的优点,4）视图对数据库的重构提供了一定程度的逻辑独立性；在关系数据库中，数据库的重构往往是不可避免的。但由于视图机制，将为应用程序提供了原来使用的关系，保持外模式。,原表：学生（学号，姓名，性别，年龄，所在系）分成：SX（学号，姓名，年龄）SY（学号，性别，所在系）,CREATE VIEW 学生(学号，姓名，性别，年龄，所在系）AS SELECT SX.学号，SX.姓名，SX.性别，SY.年龄，SY.所在系FROM SX,SYWHERE SX.学号=SY.学号；,数据定义语句,2.视图定义的格式,一般格式为：CREATE VIEW视图名(列名组)AS子查询 WITH CHECK OPTION；,（1）WITH CHECK OPTION选项 表示在对视图进行UPDATE、INSERT和DELETE操作时，要保证操作的数据满足视图定义中的谓词条件。该谓词条件是视图子查询中的WHERE子句的条件。,数据定义语句,2.视图定义的格式,一般格式为：CREATE VIEW视图名(列名组)AS子查询 WITH CHECK OPTION；,（2）组成视图的属性列名全部省略或者全部指定 若省略了视图的各个属性列名，则由子查询中的SELECT子句的目标列代替 必须明确指定列名的情况某个目标列是集函数或列表达式；子查询中使用多个表（或视图），并且目标列中含有相同的属性名；需要在视图中改用新的、更合适的列名。,数据定义语句,2.视图定义的格式,【例5-6】建立计算机系学生的视图。CREATE VIEW 计算机系学生 AS SELECT 学号，姓名，年龄 FROM 学生 WHERE 所在系=计算机系；,数据定义语句,2.视图定义的格式,【例5-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩CREATE VIEW 学生成绩(学号，姓名，课程名，成绩)AS SELECT 学生.学号，学生.姓名，课程.课程名，选课.成绩 FORM 学生，课程，选课 WHERE 学生.学号=选课.学号 AND 程.课程名=选课.课程号 AND 学生.所在系=计算机系；,数据定义语句,2.视图定义的格式,【例5-8】将学生的学号、总成绩、平均成绩定义成一个视图。CREATE VIEW 学生成绩统计(学号，总成绩，平均成绩)AS SELECT 学号，SUM（成绩），AVG（成绩）FORM 选课 GROUP BY 学号；,3.视图的删除 DROP VIEW,数据定义语句,4.视图的查询和维护,视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。一般的数据库系统不支持对下列几种情况的视图进行数据更新操作。1）由两个以上基本表导出的视图2）视图的字段来自字段表达式函数3）视图中有分组子句或使用了DISTINCT短语4）视图定义中有嵌套查询，且内层查询中涉及了与外层一样的导出该视图的基本表5）在一个不允许更新的视图上定义的视图,数据查询语句,数据查询的基本语法1.SELECT语句的语法,SELECT目标列组 FROM数据源 WHERE元组选择条件 GROUP BY分列组HAVING 组选择条件 ORDER BY排序列1排序要求1，n；,数据查询语句,1.SELECT语句的语法,SELECT子句：指明目标列（字段、表达式、函数表达式、常量）。基本表中相同的列名表示为：表名.列名FROM子句：指明数据源。表间用“，”分割。数据源不在当前数据库中，使用“数据库名.表名”表示。一表多用，用别名标识。定义表别名：表名.别名WHERE子句：元组选择条件。,数据查询语句,1.SELECT语句的语法,学生课程库结构为：学生(学号，姓名，年龄，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).,【例5-9】求数学系学生的学号和姓名。SELECT 学号，姓名 FROM 学生 WHERE 所在系=数学系；【例5-10】求选修了课程的学生学号。SELECT DISTINCT 学号 FROM 选课；,数据查询语句,1.SELECT语句的语法,(4)GROUP BY子句：结果集分组。当目标列中有统计函数，则统计为分组统计，否则为对整个结果集统计。子句后带上HAVING子句表达组选择条件（带函数的表达式）。(5)ORDER BY子句：排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。,【例5-11】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。SELECT 学号，成绩 FROM 选课 WHERE 课程号=C1 ORDER BY 成绩 DESC，学号 ASC；,数据查询语句,2.SELECT语句的操作符,(1)算术操作符+（加号）、（减号）、*（乘号）和/（除号）。(2)比较操作符=（等于）、（大于）、=（大于等于）、!=（不等于）、（小于大于）、!（不大于）和!（不小于），共9种操作符。,数据查询语句,数据查询语句,(4)组合查询操作符SQL的组合查询操作符是针对传统关系运算的操作符，包括UNION（并查询）、MINUS（差查询）和INTERSECT（交查询）其格式为：1）UNION：并查询，并在结果集中去掉重复行。2)MINUS：差查询操作。3)INTERSECT：交查询操作。4)*：取全部字段。格式为：*或表名.*5)ALL：全部。保留重复值（有统计函数时要求计算重复值）。格式为：ALL字段或 ALL字段组6)DISTINCT：去掉重复值。在结果集中去掉重复值，或在统计函数中不计重复值。格式为：DISTINCT字段或 DISTINCT字段组,数据查询语句,【例5-12】求选修课程C1且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.8输出。SELECT 学号，成绩*0.8 FROM 选课 WHERE 课程号=C1 AND 成绩 BETWEEN 80 AND 90；,【例5-13】求数学系或计算机系姓张的学生的信息。SELECT*FROM 学生 WHERE 所在系 IN(数学系，计算机系)AND 姓名 LIKE 张%；,1.简单查询实例,数据查询语句,【例5-14】求缺少了成绩的学生的学号和课程号。SELECT 学号，课程号 FROM 选课 WHERE 成绩 IS NULL；,数据查询语句,2.连接查询实例连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接,(1)等值连接和非等值连接 表名1.列名1比较运算符表名2.列名2比较运算符：=、=、=和!=；列名称为连接字段。,【例5-15】查询每个学生的情况以及他(她)所选修的课程。SELECT 学生.*，选课.*FROM 学生，选课 WHERE 学生.学号=选课.学号；,数据查询语句,【例5-16】求学生的学号、姓名、选修的课程名及成绩。SELECT 学生.学号，姓名，课程名，成绩 FROM 学生，课程，选课 WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号；【例5-17】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。SELECT 学生.学号，姓名，成绩 FROM 学生，选课 WHERE 学生.学号=选课.学号 AND 课程号=C1 AND 成绩90；,数据查询语句,(2)自身连接操作例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。,课程的先行关系链为：C5C4C3C2C1，课程的间接关系链为：C5C3C1。,数据查询语句,(2)自身连接操作,【例5-18】查询每一门课的间接先行课。SELECT A.课程号，A.课程名，B.先行课 FROM 课程 A，课程 B WHERE A.先行课=B.课程号,数据查询语句,(2)自身连接操作,【例5-18】查询每一门课的间接先行课。SELECT A.课程号，A.课程名，B.先行课 FROM 课程 A，课程 B WHERE A.先行课=B.课程号,数据查询语句,(3)外部连接操作,左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接符号为“*=”，右外部连接符号为“=*”。外部连接中不匹配的分量用NULL表示。,数据查询语句,(3)外部连接操作,内连接的结果集,数据查询语句,(3)外部连接操作,左连接的结果集,数据查询语句,(3)外部连接操作,右连接的结果集,数据查询语句,(3)外部连接操作,【例5-19】用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句,内连接：SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门=部门.部门号；左外部连接：SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门*=部门.部门号；右外部连接：SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门=*部门.部门号；,数据查询语句,3.嵌套查询,在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。,(1)使用IN操作符的嵌套查询【例5-20】求选修了高等数学的学生学号和姓名。SELECT 学号，姓名 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 选课 WHERE 课程号 IN(SELECT 课程号 FROM 课程 WHERE 课程名=高等数学)；,数据查询语句,3.嵌套查询,在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。,(1)使用IN操作符的嵌套查询【例5-20】求选修了高等数学的学生学号和姓名。该题也可以使用下面的连接查询表达。SELECT 学生.学号，姓名 FROM 学生，课程，选课 WHERE 学生.学号=课程.学号 AND 课程.课程号=选课.课程号 AND 课程.课程名=高等数学；,数据查询语句,3.嵌套查询,(2)使用比较符的嵌套查询【例5-21】求C1课程的成绩高于张三的学生学号和成绩。SELECT 学号，成绩 FROM 选课 WHERE 课程号=C1 AND 成绩(SELEC 成绩 FROM 选课 WHERE 课程号=C1AND 学号=(SELECT 学号 FROM 学生 WHERE 姓名=张三)；,数据查询语句,(3)使用ANY或ALL操作符的嵌套查询其格式为：ANY|ALL,数据查询语句,(3)使用ANY或ALL操作符的嵌套查询其格式为：ANY|ALL,数据查询语句,(3)使用ANY或ALL操作符的嵌套查询其格式为：ANY|ALL,【例5-22】求其他系中比计算机系某一学生年龄小的学生。SELECT*FROM 学生 WHERE 年龄 计算机系；,数据查询语句,(3)使用ANY或ALL操作符的嵌套查询其格式为：ANY|ALL,【例5-23】求其他系中比计算机系学生年龄都小的学生。SELECT*FROM 学生 WHERE 年龄 计算机系；,数据查询语句,(4)使用EXISTS操作符的嵌套查询,【例5-24】求选修了C2课程的学生姓名。SELECT 姓名 FROM 学生 WHERE EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)；Select 姓名From 学生，选课Where 学生.学号=选课.学号 and 课程号=c2,数据查询语句,(4)使用EXISTS操作符的嵌套查询,【例5-25】求没有选修C2课程的学生姓名。SELECT 姓名 FROM 学生 WHERE NOT EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)；不能写成以下：Select 姓名From 学生，选课Where 学生.学号=选课.学号 and 课程号c2,数据查询语句,(4)使用EXISTS操作符的嵌套查询,【例5-26】查询选修了全部课程的学生的姓名。SELECT 姓名 FROM 学生 WHERE NOT EXISTS(SELECT*FROM 课程 WHERE NOT EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程.课程号=课程号)；由于SQL没有全称量词的操作符，该题转换为查询这样的学生：没有一门课他不选修。,数据查询语句,(4)使用EXISTS操作符的嵌套查询,【例5-27】求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。SELECT 学号，姓名 FROM 学生 WHERE NOT EXISTS(SELECT*FROM 选课 选课1 WHERE 选课1.学号=S2 AND NOT EXISTS(SELECT*FROM 选课 选课2 WHERE 学生.学号=选课2.学号 AND 选课2.课程号=选课1.课程号)；,数据查询语句,4.组合查询Union并操作，Intersect交操作，MINUS差操作,【例5-28】求选修了C1课程或选修了C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 UNION SELECT 学号 FROM 选课 WHERE 课程号=C2；Select 学号From选课Where 课程号=C1 or 课程号=C2,数据查询语句,4.组合查询,【例5-29】求选修C1课程，并且也选修C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 INTERSECT SELECT 学号 FROM 选课 WHERE 课程号=C2；,数据查询语句,4.组合查询,【例5-30】求选修了C1课程但没有选修C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 MINUS SELECT 学号 FROM 选课 WHERE 课程号=C2；,数据查询语句,4.组合查询,【例5-30】求选修了C1课程但没有选修C2课程的学生学号。本例也可以用下面的EXISTS嵌套查询表示。SELECT 学号 FROM 选课 选课1 WHERE 课程号=C1 AND NOT EXISTS(SELECT 学号 FROM 选课 选课2 WHERE 选课1.学号=选课2.学号 AND 选课2.课程号=C2)；,数据查询语句,5.使用分组和SQL函数查询,数据查询语句,5.使用分组和SQL函数查询,【例5-31】求学生的总人数。SELECT COUNT(*)FROM 学生；【例5-32】求选修了课程的学生人数。SELECT COUNT(DISTINCT 学号)FROM 选课；,数据查询语句,5.使用分组和SQL函数查询,【例5-33】求课程和选修该课程的人数。SELECT 课程号，COUNT(学号)FROM 选课 GROUP BY 课程号；【例5-34】求选修课超过3门课的学生学号。SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(*)3；,数据更新语句,SQL的数据插入功能1.使用常量插入单个元组,格式为：INSERT INTO表名(属性列1，属性列2)VALUES(常量1，常量2)；,数据更新语句,SQL的数据插入功能1.使用常量插入单个元组,【例5-35】将一个新学生记录(学号：98010，姓名：张三，年龄：20，所在系：计算机系)插入到学生表中。INSERTINTO 学生VALUES(98010，张三，20，计算机系)；【例5-36】插入一条选课记录(学号：98011，课程号：C10，成绩不详)。INSERT INTO 选课(学号，课程号)VALUES(98011，C10)；,数据更新语句,2.在表中插入子查询的结果集INSERT INTO表名(属性列1，属性列2)子查询；,【例5-37】求每个系学生的平均年龄，并把结果存入数据库中。CREATE TABLE 系平均年龄(系名称CHAR(20)，平均年龄SMALLINT)；INSERT INTO 系平均年龄 SELECT 所在系，AVG(ALL年龄)FROM 学生 GROUP BY 所在系；,数据更新语句,3.SQL的数据修改功能UPDATE表名 SET列名=表达式，列名=表达式，n WHERE条件；,【例5-38】将学生表中全部学生的年龄加上2岁。UPDATE 学生 SET 年龄=年龄+2；【例5-39】将选课表中的数据库课程的成绩乘以1.2。UPDATE 选课 SET 成绩=成绩*1.2 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名=数据库)；,数据更新语句,3.SQL的数据删除功能DELETE FROM表名 WHERE条件；,【例5-40】删除艺术系的学生记录及选课记录。DELETE FROM 选课 WHERE 学号 IN(SELECT 学号 FROM 学生 WHERE 所在系=艺术系)；DELETE FROM 学生 WHERE 所在系=艺术系；,嵌入式SQL,嵌入式SQL的特点,1.嵌入式SQL应注意的问题 1)SQL和主语言的配合问题。2)合理选择主语言。2.SQL嵌入主语言时必须解决的三个问题(1)如何区别SQL和主语言 在所有的SQL语句前面加上前缀EXEC SQL；SQL语句的结束标志随主语言的不同而不同,嵌入式SQL,嵌入式SQL的特点,2.SQL嵌入主语言时必须解决的三个问题(2)使数据库的工作单元与程序工作单元之间能够通信 1)主语言通过主变量向SQL语句提供参数。2)SQL语句的当前工作状态和运行环境数据要返馈给应用程序。(3)使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。,嵌入式SQL,不用游标的SQL语句,1.几种不需要使用游标的SQL语句(1)用于说明主变量的说明性语句 SQL的说明性语句主要有两条：EXEC SQL BEGIN DECLARE SECTION；EXEC SQL END DECLARE SECTION；(2)数据定义和数据控制语句(3)查询结果为单记录的查询语句(4)数据的插入语句和某些数据删除、修改语句 独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。,嵌入式SQL,2.不用游标的查询语句EXEC SQL SELECT ALL|DISTINCT目标列表达式，n INTO主变量指示变量，n FROM表名或视图名，n WHERE条件表达式；1)在语句开始前要加EXEC SQL前缀。2)该查询语句中又扩充了INTO子句。3)在WHERE子句的条件表达式中可以使用主变量。,嵌入式SQL,2.不用游标的查询语句EXEC SQL SELECT ALL|DISTINCT目标列表达式，n INTO主变量指示变量，n FROM表名或视图名，n WHERE条件表达式；4)由于查询的结果集中只有一条记录，该语句中不必有排序和分组子句。5)INTO子句中的主变量后面跟有指示变量时 如果查询结果列值为NULL，指示变量为负值，结果列不向该主变量赋值；如果传递正常，指示变量的值为0；如果主变量宽度不够，则指示变量的值为数据截断前的宽度。6)如果查询结果并不是单条记录，则程序出错。,嵌入式SQL,2.不用游标的查询语句EXEC SQL SELECT ALL|DISTINCT目标列表达式，n INTO主变量指示变量，n FROM表名或视图名，n WHERE条件表达式；【例5-41】查询学号为主变量givesno、课号为主变量givecno的值的学生选课记录。EXEC SQL SELECT 学号，课程号，成绩 INTO:Sno，:Cno，:grade:gradeid FROM 选课 WHERE 学号=:givesno AND 课程号=:givecno；,嵌入式SQL,2.不用游标的数据维护语句(1)不用游标的数据删除语句【例5-42】删除学号由主变量Sno决定的学生记录。EXEC SQL DELETE FROM 学生 WHERE 学号=:Sno；,嵌入式SQL,2.不用游标的数据维护语句(2)不用游标的数据维护语句【例5-43】将计算机系所有学生的年龄都加上主变量Raise。EXEC SQL UPDATE 学生 SET 年龄=年龄+:Raise WHERE 所在系=计算机系；【例5-44】将计算机系学生的年龄置空。Raiseid=-1 EXEC SQL UPDATE 学生 SET 年龄=年龄+:Raise:Raiseid；,嵌入式SQL,2.不用游标的数据维护语句(3)不用游标的数据插入语句【例5-45】将学号为主变量Sno、课程号为Cno的选课记录，插到库中。EXEC SQL INSERT INTO 选课 VALUES(:Sno，:Cno)；,嵌入式SQL,使用游标的SQL,游标机制用于解决SQL查询结果为集合而主语言处理方式为记录方式的矛盾。在处理中，必须使用游标的SQL语句有两种：一种是查询结果为多条记录的SELECT语句，另一种是使用游标的DELETE语句和UPDATE语句。,嵌入式SQL,1.游标的4个命令(1)定义游标命令 EXEC SQL DECLARE游标名CURSOR FOR子查询 FOR UPDATE OF字段名1，n；删除和修改数据的语句中，WHERE为：WHERE CURRENT OF游标名【例5-46】定义按主变量DEPT查询系里学生的游标。EXEC SQL DECLARE SX CURSOR FOR SELECT*FROM 学生 WHERE 所在系=:DEPT；,嵌入式SQL,(2)打开游标命令 EXEC SQL OPEN 游标名；执行对应的查询语句，并将游标指向结果集的第一条记录前。打开的游标处于活动状态，可以被推进。【例5-47】打开SX游标。DEPT=计算机系 EXEC SQL OPEN SX；,嵌入式SQL,(3)推进游标命令 EXEC SQL FETCH游标名INTO主变量组；将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。【例5-48】将打开的指向系的游标向前推进。EXEC SQL FETCH SX INTO:Sno，:Sname，:Sage，:Sdept；(4)关闭游标命令 EXEC SQL CLOSE 游标名；,数据控制机制和语句,数据控制是系统通过对数据库用户的使用权限加以限制而保证数据安全的重要措施。,授权,拒绝访问,收权,数据控制机制和语句,数据控制机制,授权定义,存权处理,查权操作,具有授权资格的用户，如数据库管理员DBA或建表户DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。,数据库管理系统DBMS把授权的结果编译后存入数据字典中。,当用户提出操作请求时，系统要在数据字典中查找该用户的数据操作权限，当用户拥有该操作权时才能执行其操作，否则系统将拒绝其操作。,数据控制机制和语句,1.授权语句系统授权的授权语句格式为：GRANT系统特权组To用户组|PUBLIC WITH GRANT OPTION；1)PUBLIC指数据库的所有用户。2)WITH GRANT OPTION:获得权限的用户可以把该权限再授予别的用户。对象特权的授权格式为：GRANT ALL PRIVILIGES|对象特权组ON对象名 TO用户组|PUBLIC WITH GRANT OPTION；1)ALL PRIVILIGES指所有的对象特权。2)对象名指操作的对象标识，它包括表名、视图名和过程名等。,数据控制机制和语句,【例5-51】把修改学生学号和查询学生表的权力授予用户王平。GRANT UPDATE(学号)，SELECT ON 学生 TO 王平；【例5-52】把建立数据库和备份数据库的权力赋给用户王平。GRANT CREATE DATABASE，BACKUP DATABASE TO 王平；,数据控制机制和语句,2.收权语句 REVOKE语句的一般格式为：REVOKE权限组|ALL PRIVILIGES ON对象名 FROM用户名组|PUBLIC；其中：ON子句用于指定被收回特权的对象；ALL PRIVILIGES指收回所有特权；PUBLIC指所有用户。【例5-53】将用户王平的可以在学生表中修改学生学号的权利收回。REVOKE UPDATE(学号)ON 学生 FROM 王平；,数据控制机制和语句,3.拒绝访问语句 DENY ALL PRIVILIGES|权限组 ON 对象名 TO 用户组|PUBLIC；其中：ON子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。,