数据库第三章.ppt

第三章关系数据库标准语言SQL语言 主要内容 3 1SQL概述 3 2查询语句 3 3更新语句 3 4SQLDDL 3 5SQLDCL 3 6视图 3 7嵌入式SQL 3 1SQL概述 一 SQL的发展及现状二 SQL数据库的体系结构三 SQL的功能四 SQL的形式五 SQL的特点 3 1SQL概述 一 SQL的发展及现状1974年 由Boyce和Chamberlin提出1975 1979 IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言SQL 86是第一个SQL标准SQL 89 SQL 92 SQL2 SQL 99 SQL3 大部分DBMS产品都支持SQL 成为操作数据库的标准语言 二 SQL数据库的体系结构 SQL 用户 BaseTableB1 ViewV1 ViewV2 BaseTableB2 BaseTableB3 BaseTableB4 StoredFileS1 StoredFileS1 StoredFileS1 StoredFileS1 外模式 模式 内模式 SQL语言支持的关系数据库的三级模式结构 二 SQL数据库的体系结构 基本概念 1 用户可以用SQL语言对视图 View 和基本表 BaseTable 进行查询等操作 在用户观点里 视图和表一样 都是关系 2 视图是从一个或多个基本表中导出的表 本身不存储在数据库中 只有其定义 可以将其理解为一个虚表 3 基本表是本身独立存在的表 每个基本表对应一个存储文件 一个表可以带若干索引 存储文件及索引组成了关系数据库的内模式 三 SQL的功能 数据定义 DDL 定义 删除 修改关系模式 基本表 定义 删除视图 View 定义 删除索引 Index 数据操纵 DML 数据查询数据增 删 改数据控制 DCL 用户访问权限的授予 收回 四 SQL的组成 分类 DATABASETABLECREATEVIEWINDEXSQLDDL ALTERDROPSQLQUERY SELECT FROM INSERT SQLSQLDML UPDATE DELETE GRANTSQLDCL REVOKEAUDIT嵌入式SQL 五 SQL的形式 交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释 嵌入式SQL能将SQL语句嵌入到高级语言 宿主语言 使应用程序充分利用SQL访问数据库的能力 宿主语言的过程处理能力一般需要预编译 将嵌入的SQL语句转化为宿主语言编译器能处理的语句 六 SQL的特点 4 具有查询 操作 定义和控制四种语言一体化的特点 它只向用户提供一种语言 但该语言具有上述多种功能 且每种操作只需一种操作符 高度非过程化的语言 用户只需提出 干什么 至于 怎么干 由DBMS解决 用户只需要早查询语句中提出需要什么 DBMS即可按路径存取 并把结果返回给用户 2 面向集合的语言 每一个SQL的操作对象是一个或多个关系 操作的结果也是一个关系 3 一种语法结构 两种使用方式 即可独立使用 又可嵌入到宿主语言中使用 具有自主型和宿主型两种特点 五 SQL的特点 5 语言简洁 易学易用 核心功能只有9个动词 语法简单 接近英语 SQL功能动词数据库查询SELECT数据定义CREATE DROP ALTER数据操纵INSERT UPDATE DELETE数据控制GRANT REVOKE 一 数据类型 1 字符型 CHAR n VARCHAR2 数字型 INT SMALLINT REAL 3 日期型 DATE TIME 二 函数 数字函数 ABS X SQRT X RAND X LOG X 字符函数 LENGTH X LOWER X UPPER X SUBSTRING expression start length 分组函数 COUNT MAX X MIN X AVG X SUM X 三 运算符 句法操作符 数值操作符 逻辑操作符 IN ANY ALL 查询表达式操作符 UNION 其它操作符 基本结构SelectA1 A2 AnFromR1 R2 RmWhereP 3 2查询语句 数据查询是数据库应用的核心功能 A1 A2 An p R1 R2 Rm 3 2查询语句 语句格式 P72 SELECT ALL DISTINCT 表名 列名 表达式 AS新列名 INTO 主变量1 主变量2 FROM表名 表别名 WHERE条件表达式 子查询 GROUPBY列名1 HAVING分组表达式 UNION INTERSECT EXCEPT SELECT FROM ORDERBY列名 列序号 ASC DESC SELECT FROM常用语句执行过程SELECT 投影FROM TABLE 内存WHERE 选取元组GROUP 分组HAVING 选择分组 UNION 查徇结果的集合运算SELECT ORDERBY 排序输出 A1 An p R1 Rm SelectA1 A2 AnFromR1 R2 RmWhereP 一 单表查询1 选择表中的列2 选择表中的行3 分组与组函数4 排序 一 单表查询 选择表中的列例1 求学生所在系及姓名SELECTSD SNFROMS 例2 求学生的全部信息SELECTS SN SA SDFROMS SELECT SELECTSnameNAME YearofBirth BIRTH 2003 SageBIRTHDAY LOWER Sdept DEPARTMENTFROMStudent 例3 查全体学生的姓名 出生年份和所有系 要求用小写字母表示所有系名 SELECTSname YearofBirth 2003 Sage LOWER Sdept FROMStudent 选择表中的行1 消除重复行 DISTINCT 缺省为ALL 例 求选修了课程的学生号SELECTDISTINCTS FROMS Where子句 运算符 比较 not 确定范围 BetweenAandB NotBetweenAandB确定集合 IN NOTIN字符匹配 LIKE NOTLIKE空值 ISNULL ISNOTNULL多重条件 AND OR NOT 用WHERE子句实现复杂查询条件 例 求年龄在18 22 含18 22 之间的学生名及年龄 或不在18 22之间 SELECTSN SAFROMSWHERESABETWEEN18AND22 WHERESA 18ANDSA22 例 求计算机系年龄小于19的姓名及年龄SELECTSN SAFROMSWHERESD CS ANDSA 19 2 选满足一定条件的行 3 查询条件来自集合 用INorNOTIN例 求信息工程系 计算机系 数学系的系名 学生名 或不是这些系的学生 SELECTSDDEPT SNNAMEFROMSWHERESDIN IE CS MA WHERESD IE ORSD CS ORSD MA WHERESDNOTIN IE CS MA WHERESD IE ANDSD CS ANDSD MA Where子句 Like 格式 NOT LIKE 匹配串 ESCAPE 换码字符 表示任意长度 0的任意字符 表示单个的任意字符ESCAPE 换码字符 匹配串中 换码字符 转义符 之后的字 被定义为普通字符 不作通配符用 4 字符匹配 例 列出课程名称中带有 的课号及课名 Selectcno cnameFromCourseWherecnameLIKE escape 例1 求课程名中有 数据库 的课程记录SELECT FROMCWHERECNLIKE 数据库 例2 求倒数第三 四个汉字为 系统 的课程名SELECTCNFROMCWHERECNLIKE 系统 例3 求以 DATA BASE 开头且倒数第五个字符为 S 的课程名SELECTCNFROMCWHERECNLIKE DATA BASE S ESCAPE 例 查缺少成绩的学生的学号和相应的课程号SELECTSno CnoFROMSCWHEREGradeISNULL 5 涉及空值的查询 GroupBy子句 将查询结果集按某一列或多列的值分组 值相等的为一组 一个分组以一个元组的形式出现只有出现在GroupBy子句中的属性 才可出现在Select子句中例 统计各系学生的人数 Selectsdept count asstu countFromStudentGroupBysdept 3 分组与组函数 组函数 组函数的使用格式 COUNT DISTINCT ALL 列名 SUM DISTINCT ALL 列名 AVG DISTINCT ALL 列名 MAX DISTINCT ALL 列名 MIN DISTINCT ALL 列名 组函数可用于SELECT子句中的目标列表中 或在HAVING子句的分组表达式中用作条件 对分出的每一组用HAVING进行筛选 筛选条件要用到组函数 例1 查询各个课程号与相应的选课人数SELECTCno COUNT Sno FROMSCGROUPBYCno 例2 查男女生人数SELECTsex COUNT SNO COUNTSNOFROMstuGROUPBYsex 例3 求选修了课程的学生人数SELECTCOUNT DISTINCTS FROMSC 例4 求选修各门课的人数及平均成绩SELECTC COUNT S AVG ALLGR FROMSCGROUPBYC 例5 求选修课程在5门以上且都及格的学生号及总平均分SELECTS AVG ALLGR FROMSCGROUPBYS HAVINGCOUNT C 5ANDMIN GR 60 Having与Where的区别 Where决定哪些元组被选择参加运算 作用于关系中的元组Having决定哪些分组符合要求 作用于分组 4 排序用ORDERBY子句对查询结果按照一个或多个列的值进行升 降排列输出升序为ASC 降序为DESC空值将作为最大值排序 例1 对选修 C5 课程的学生按成绩降序排列 同分数者按学号升序排列SELECTS GRFROMSCWHEREC C5 ORDERBYGRDESC S ASC 例2 求每个学生的总分并按总分降序排列 学号升序SELECTS SUM ALLGR FROMSCGROUPBYS ORDERBY2DESC S ASC 求95级学生中选修课程在5门以上且都及格的学生号及总平均分 并按平均成绩排序 A1 An p R1 Rm SelectA1 A2 AnFromR1 R2 RmWhereP 一 单表查询1 选择表中的列2 选择表中的行3 分组与组函数4 排序 主要内容 二 连接查询 多表连接查询 单表连接查询 外连接查询 复合条件连接查询 三 嵌套查询 一 单表查询 返回单个值的子查询返回一组值的子查询多重子查询 二 连接查询 多表连接查询 单表连接查询 外连接查询 复合条件连接查询 示范表EMP EMPNO ENAME MGR JOB SAL DEPTNO DEPT DEPTNO DNAME CITY SALGRADE GRADE LOSAL HISAL 1 多表连接 连接条件一 表名1 列名1比较运算符 表名2 列名2连接条件二 表名1 列名1BETWEEN 表名2 列名2AND 表名2 列名3 连接条件中的列名称为连接字段 其各字段应是可比的 执行过程 在表1中找到第一个元组 然后从头开始扫描表2 查找到满足条件的元组即进行串接并存入结果表中 再继续扫描表2 依次类推 直到表2末尾 再从表1中取第二个元组 重复上述的操作 直到表1中的元组全部处理完毕 例题 SELECTENAME SAL GRADEFROMEMP SALGRADEWHERESALBETWEENLOSALANDHISAL SELECTS S SN CN GFROMS C SCWHERES S SC S ANDC C SC C 例2 求每个职工的工资级别 例1 求选课情况 要求输出学号 姓名 课程名与成绩 2 单表连接 用表别名把一个表定义为两个不同的表进行连接 SELECTW ENAME W SAL M ENAME M SALFROMEMPW EMPMWHEREW MGR M EMPNOANDW SAL M SAL 例1 求比本部门职工工资低的部门负责人 例题 SELECTFIRST C THIRD CNAMEFROMCFIRST CSECOND CTHIRDWHEREFIRST PC SECOND C ANDSECOND PC THIRD C 例2 求每门课的间接先修课名 即先修课的先修课 3 外连接 在连接条件的某侧加上 或 表示该侧所对应的表中可形成一个各数据项均为空值的万能替代行 用来与另一侧对应的表中所有不满足条件的元组进行连接 外连接符 或 出现在左侧称为右外连接 出现在右侧称为左连接 两侧都出现的称为全外连接 SELECTDNAME ENAME JOBFROMDEPT EMPWHEREDEPT DEPTNO EMP DEPTNO 例1 求各部门名及职工名 工种 要求输出无职工的部门 职工以空值出现 例题 SELECTDNAME ENAME JOBFROMDEPT EMPWHEREDEPT DEPTNO EMP DEPTNO 例2 求各部门名及职工名 工种 要求输出未分配部门的职工 部门以空值出现 例题 SELECTDNAME ENAME JOBFROMDEPT EMPWHEREDEPT DEPTNO EMP DEPTNO 例3 求各部门名及职工名 工种 要求输出无职工的部门和未分配部门的职工 4 复合条件连接 WHERE子句中除了连接条件 还有其它限制条件 SELECTSN GRFROMS SCWHERES S SC S ANDSC C C6 ANDSC GR 90 例1 求选修 C6 课程且成绩超过90分的学生名与成绩 连接条件 限制条件 三 嵌套查询 在SELECT FROM WHERE语句结构的WHERE子句中可嵌入一个SELECT语句块其上层查询称为外层查询或父查询其下层查询称为内层查询或子查询SQL语言允许使用多重嵌套查询在子查询中不允许使用ORDERBY子句嵌套查询的实现一般是从里到外 即先进行子查询 再把其结果用于父查询作为条件 1 返回单个值的子查询 方法一 SELECTSN SAFROMSWHERESD SELECTSDFROMSWHERESN 刘力 例1 求与 刘力 同一个系的学生名 年龄 1 返回单个值的子查询 方法二 SELECTFIRST SN FIRST SAFROMSFIRST SSECONDWHEREFIRST SD SECOND SDANDSECOND SN 刘力 例1 求与 刘力 同一个系的学生名 年龄 2 返回一组值的子查询 SELECT FROMSWHERES IN SELECTS FROMSCWHEREC C6 ANDGR 90 例1 求选修 C6 课程且成绩超过90分的学生 方法一 例题 SELECTS FROMS SCWHERES S SC S ANDGR 90ANDC C6 例1 求选修 C6 课程且成绩超过90分的学生 方法二 连接查询 例题 方法一 SELECT FROMSWHERESD CS ANDSA ANY SELECTSAFROMSWHERESD CS 例2 求比计算机系中某一学生年龄小的其他系的学生 例题 方法二 SELECT FROMSWHERESD CS ANDSA SELECTMAX SA FROMSWHERESD CS 例2 求比计算机系中某一学生年龄小的其他系的学生 3 多重子查询 例1 求10 部门中工种与 SALES 部门中任一工种相同的职工 例题 SELECTENAME JOB SALFROMEMPWHEREDEPTNO 10 ANDJOBIN SELECTJOBFROMEMPWHEREDEPTNO SELECTDEPTNOFROMDEPTWHEREDNAME SALES 例题 SELECT FROMEMPWHERESAL SELECTMIN SAL FROMEMPWHEREENAMEIN CLARK MILLER ANDSAL SELECTMAX SAL FROMEMPWHEREENAMEIN CLARK MILLER 例2 求工资介于 CLARK 与 MILLER 两个之间的职工 例题 SELECTDEPTNO ENAME JOB SALFROMEMPWHEREJOBIN SELECTJOBFROMEMPWHEREDEPTNOIN SELECTDEPTNOFROMDEPTWHERECITY NEWYORK 例3 求工种与在纽约的职工的工种相同的职工 总结 连接查询 多表连接查询 单表连接查询 外连接查询 复合条件连接查询 嵌套查询 返回单个值的子查询返回一组值的子查询多重子查询 四 带有EXISTS的相关子查询 不相关子查询 子查询的查询条件不依赖于父查询的称为不相关子查询 相关子查询 子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询 CorrelatedSubquery 带EXISTS的子查询就是相关子查询EXISTS表示存在量词带有EXISTS的子查询不返回任何记录的数据 只返回逻辑值 True 或 False 例题 不相关子查询 SELECTSNFROMSWHERES IN SELECTS FROMSCWHEREC C1 例1 求所有选修了 C1 课程的学生名 例题 相关子查询 SELECTSNFROMSWHEREEXISTS SELECT FROMSCWHERES S SC S ANDC C1 例1 求所有选修了 C1 课程的学生名 执行过程 先在外层查询中取S表的第一个元组 记录 用该记录的相关的属性值 在内层WHERE子句中给定的 处理内层查询 若外层的WHERE子句返回 TRUE 值 则此元组送入结果的表中 然后再取下一个元组 重复上述过程直到外层表的记录全部遍历一次为止 说明 不关心子查询的具体内容 因此用SELECT Exists 子查询用来判断该子查询是否返回元组当子查询的结果集非空时 Exists为 True 当子查询的结果集为空时 Exists为 False NOTEXISTS 若子查询结果为空 返回 TRUE 值 否则返回 FALSE 例题 SELECTSNFROMSWHERENOTEXISTS SELECT FROMCWHERES S SC S ANDC C1 例2 列出没有选C1课程的学生的学号 姓名 例题 SELECTSNFROMSWHERENOTEXISTS SELECT FROMCWHERENOTEXISTS SELECT FROMSCWHERES S SC S ANDC C SC C 例3 查询选修了所有课程的学生的姓名 这门课他没选 这样的课是不存在的 例题 例4 查询至少选修了 S1 所选的全部课程的学生名 P S1选修了课程CXQ 学生SX选修了课程CX则 CX P Q 表示学生SX选修了 S1 所选的全部课程 P Q P Q X P Q X P Q X P Q X P Q 即 X P Q X P Q SELECTSNFROMSWHERENOTEXISTS X SELECT FROMSCSCXS1选课WHERESCX S S1 PANDNOTEXISTS SELECT FROMSCSCYWHERES S SCY S ANDSCX C SCY C Q FROM语句中使用子查询 SQL2 在FROM语句中使用子查询 对查询结果定义表名及列名例 求平均成绩超过80分的学号及平均成绩SELECTS AVG GFROM SELECTS AVG ALLG FROMSCGROUPBYS ASRA S AVG G WHEREAVG G 80 SELECTS AVG ALLG FROMSCGROUPBYS HAVINGAVG ALLG 80 五 SQL的集合操作 属性个数必须一致对应的类型必须一致属性名无关最终结果集采用第一个结果的属性名缺省为自动去除重复元组除非显式说明ALLOrderBy放在整个语句的最后 SQL的集合操作 并 例1 查询计算机系的学生或者年龄不大于19岁的学生 并按年龄倒排序 SELECT FROMSWHERESD CS UNIONSELECT FROMSWHEREAGE 19ORDERBYAGEDESC SQL的集合操作 交 例2 查询计算机系的学生并且年龄不大于19岁的学生 并按年龄倒排序 SELECT FROMSWHERESD CS INTERSEC SELECT FROMSWHEREAGE 19 ORDERBYAGEDESC SQL的集合操作 差 例3 查询选修课程1但没有选修课程2的学生 SELECTSN SDFROMSWHERES IN SELECTS FROMSCWHEREC 1 EXCEPT SELECTS FROMSCWHEREC 2 3 3更新语句 一 插入操作INSERT 二 删除操作DELETE 三 修改操作UPDATE 一 插入操作 1 插入单个元组 格式 INSERTINTO表名 列名1 VALUES 列值1 插入一已知元组的全部列值 插入一已知元组的部分列值 插入一已知元组的全部列值 INSERTINTOSVALUES 990021 陈冬 18 CS 男 例1 新增一个学生信息 INSERTINTOSC S C VALUES 9807121 C175 格式 INSERTINTO表名 列名1 子查询 例1 设关系S G S AVG G 把平均成绩大于80的男生的学号及平均成绩存入S G中 2 插入子查询的结果 一 插入操作 例题 INSERTINTOS G S AVG G SELECTS AVG ALLGRADE FROMSCWHERES IN SELECTS FROMSWHERESEX 男 GROUPBYS HAVINGAVG ALLGRADE 80 二 删除操作 格式 DELETEFROM表名 WHERE条件 只能对整个元组操作 不能只删除某些属性上的值只能对一个关系起作用 若要从多个关系中删除元组 则必须对每个关系分别执行删除命令从关系r中删除满足P的元组 只是删除数据 而不是定义 例1 删除学号为 95019 的学生 1 删除单个元组 二 删除操作 DELETEFROMSCWHERES 95019 DELETEFROMSWHERES 95019 例2 删除选课但无成绩的学生的选课信息 2 删除多个元组 二 删除操作 DELETEFROMSCWHEREGRADEISNULL DELETEFROMSC清空SC表 例3 删除选修 C4 且成绩小于该课程的平均成绩的记录 3 带子查询的删除语句 二 删除操作 DELETEFROMSCWHEREC C4 ANDG SELECTAVG ALLGRADE FROMSCWHEREC C4 三 修改操作 格式1 UPDATE表名 别名 SET列名 表达式 WHERE条件 格式2 UPDATE表名 别名 SET 列名 子查询 WHERE条件 例1 名为MARTIN的职工的工种改为MANAGER 工资增加收入20 1 修改单个元组的值 三 修改操作 UPDATEEMPSETJOB MANAGER SAL SAL 1 2WHERENAME MARTIN 例2 将所有学生的年龄增加1岁 2 修改多个元组的值 三 修改操作 UPDATESSETSA SA 1 例3 工种为SALESMEN的职工的工资改为工种平均工资的110 佣金为最低值 3 带子查询的修改语句 三 修改操作 UPDATEEMPSET SAL COMM SELECT1 1 AVG ALLSAL MIN COMM FROMEMPWHEREJOB SALESMEN WHEREJOB SALESMEN 例4 计算机系全体学生成绩上浮15 3 带子查询的修改语句 三 修改操作 UPDATESCSETGRADE GRADE 1 15 WHERES IN SELECTS FROMSWHERESD CS 数据库上机实验操作说明 1 win2000登陆2 在开始菜单 程序 MicrosoftSQL7 0 QueryAnalyzerSQLSERVER f3 svrLoginName USER2Password USER2connectioninformation选择第二个选项 SQLSERVER然后按OK3 STU1 WORK1数据库中 S C SC DEPT EMP SALGRADE 选择数据库 数据库查询SELECT数据定义CREATE DROP ALTER数据操纵INSERT UPDATE DELETE数据控制GRANT REVOKE SQL功能动词 3 4数据定义 一 定义基本表CREATETABLE 二 删除基本表DROPTABLE 三 修改基本表ALTERTABLE 四 定义索引CREATEINDEX 五 删除索引DROPINDEX 一 定义基本表 格式1 CREATETABLE表名 列名类型 长度 NOTNULL DEFAULT 常量 系统变量 NULL 列约束 PRIMARYKEY 列名 FOREIGNKEY 列名 REFERENCES表名 列名 CHECK条件 建立一个新表 表中无记录 一 定义基本表 格式2 CREATETABLE表名 列名 NOTNULL PRIMARYKEY 列名 FOREIGNKEY 列名 REFERENCES表名1 列名 AS子查询 建立一个带有子查询结果记录的新表 例题 CREATETABLES S CHAR 6 NOTNULL SNAMECHAR 8 NOTNULL SAGESMALLINT SDCHAR 10 SEXCHAR 2 DEFAULT 男 CHECK SEX 男 OR SEX 女 PRIMARYKEY S 例 建立学生S 课程C 选课SC三个表 S表 例题 CREATETABLEC C CHAR 6 NOTNULL CNAMECHAR 30 NOTNULL TNAMECHAR 8 PC CHAR 6 PRIMARYKEY C 例 建立学生S 课程C 选课SC三个表 C表 例题 CREATETABLESC S CHAR 6 NOTNULL C CHAR 6 NOTNULL GRSMALLINTDEFAULTNULL PRIMARYKEY S C FOREIGNKEY S REFERENCESS S FOREIGNKEY C REFERENCESC C CHECK GRISNULL OR GRBETWEENOAND100 例 建立学生S 课程C 选课SC三个表 SC表 例题 例 设关系S G S AVG G 把平均成绩大于80的男生的学号及平均成绩存入S G中 CREATETABLES G S CHAR 6 NOTNULL AVG GSMALLINTDEFAULTNULL AS SELECTS AVG ALLGRADE FROMSCWHERES IN SELECTS FROMSWHERESEX 男 GROUPBYS HAVINGAVG ALLGRADE 80 二 删除基本表 格式 DROPTABLE表名 CASCADE RESTRICT 删除一个表 及与该表相关的索引 视图 码和外部码 CASCADE连同引用该表的视图 完整性约束一起自动撤消RESTRICT无引用时 才可撤消 三 修改基本表 改变表名增加列改变列的数据类型删除列的约束删除列改变列名 基本表的修改操作 三 修改基本表 格式 ALTERTABLE表名 ADD新列名类型 长度 NOTNULL 列约束 MODIFY旧列名类型 长度 DROP列约束 RENAME旧表名TO新表名 增加新列 修改列 删除列的完整性约束 改变表名及列名 例题 例1 在S表中增加一个入学时间 为日期型ALETRTABLESADDSCOMEDATE 例2 把SA列的类型改为半字长整数ALETRTABLESMODIFYSASMALLINT 例3 删除对SN列的唯一约束ALETRTABLESDROPUNIQUE SN 删除列 改变列名 对删除列 改变列名的操作 可采取如下步骤实现 CREATETABLE AS 子查询 建一新表删除旧表改变表名 四 定义索引 格式 CREATE UNIQUE CLUSTER INDEX索引名ON表名 列名 ASC DESC 对指定的表的列建立索引 UNIQUE表示索引值唯一CLUSTER表示索引是聚簇索引索引一旦建立 交由系统使用和维护 例题 CREATEUNIQUEINDEXSIDXONS S ASC CREATECLUSTERINDEXSNCDXONS SNAMEASC CREATEUNIQUEINDEXCIDXONC C ASC CREATEUNIQUEINDEXSCIDXONSC S ASC C DESC 五 删除索引 格式 DROPINDEX索引名 例 DROPINDEXCIDX 3 5数据控制 一 授权GRANT 二 收回权限REVOKE 一 授权 1 DATABASE GRANTCREATETABONDATABASE数据库名TO 用户名 PUBLIC WITHGRANTOPTION 授予用户建立表的权限由DBA授权 只有CREATETAB一种权限WITHGRANTOPTION允许用户转授权 权限传播 PUBLIC表示所有用户 格式 2 TABLE级的基本表 GRANT SELECT INSERT UPDATE DELETE ALTER INDEX ALLPRIVILEGES ONTABLE表名TO 用户名 PUBLIC WITHGRANTOPTION 授予用户对表的操作权限由拥有该表的用户授权 格式 3 TABLE级的视图 VIEW GRANT SELECT INSERT UPDATE DELETE ALLPRIVILEGES ONTABLE视图名TO 用户名 PUBLIC WITHGRANTOPTION 授予用户对视图的操作权限由拥有该视图的用户授权 格式 4 TABLE级的列 GRANT SELECT INSERT UPDATE DELETE ALLPRIVILEGES 列名 ONTABLE 表名 视图名 TO 用户名 PUBLIC WITHGRANTOPTION 授予用户对列的操作权限由拥有该表或视图的用户授权 格式 例题 例1 GRANTCREATETABONDATABASE教学系统TOUSER1 例2 GRANTSELECT INSERTONTABLESCTOUSER1 USER2 USER5WITHGARNTOPTION 例3 GRANTALLPRIVILEGESONTABLESCTOUSER3 USER4 USER7 例题 例4 把查询Stu表和修改学生学号的权限授给用户U4 GRANTUPDATE Sno SELECTONTABLEStuTOU4 二 ORACLE授权机制 在ORACLE中对用户的授权分为系统级 表级 视图级 1 系统级 格式 GRANT CONNECT RESOURCE DBA TO 用户名 PUBLIC IDENTIFIEDBY口令 作用 授予用户对ORACLE系统的操作权限CONNECT 允许用户登录数据库 在获得表级或视图级授权后可作相应操作 但不能建立表 CREATETABLE RESOURCE 允许建表DBA 超级用户 2 表级 格式与标准SQL类似3 表级 格式与标准SQL类似例 GRANTCONNECT RESOURCETOXUNS GRANTTOUSER6IDENTIFIEDBYQWE 三 收回授权 由DBA或授于DBA权限的用户对其他用户进行权限回收 1 格式 REVOKECREATETABONDATABASE数据库名FROM 用户名 PUBLIC 2 格式 REVOKE 权限列表 ONTABLE 表名 视图名 FROM 用户名 PUBLIC 3 格式 REVORE 权限列表 列名 ONTABLE 表名 视图名 FROM 用户名 PUBLIC 4 格式 REVOKE DBA RESOURCE CONNECT FROM 用户名 PUBLIC 例题 例1把用户U4修改学生学号的权限收回REVOKEUPDATE Sno ONTABLEStuFROMU4 例2收回所有用户对表SC的查询权限REVOKESELECTONTABLESCFROMPUBLIC 例3把用户U5对SC表的INSERT权限收回REVOKEINSERTONTABLESCFROMU5 四 审计 AUDIT 以ORACLE为例 分为系统级审计和用户级审计两种1 系统级审计 由DBA进行 可监测登录要求 GRANT REVOKE及其他数据库级的操作2 用户级审计 由用户设置 主要针对用户自己创建的表或视图进行审计 监测对表或视图的访问及其他操作 3 设置审计 格式 AUDIT操作列表ON 表名 视图名 SYSTEM 4 取消审计 格式 NOAUDIT操作列表ON 表名 视图名 SYSTEM 5 审计结果存放 内容在数据字典表SYS AUDIT TRAIL中 可用SELECT FROMSYS AUDIT TRAIL 查看 3 6视图 视图是从一个或几个表 或视图 导出的一个特殊的表 视图是一个虚表数据库中只存放视图的定义视图对应的数据仍存放在原来的表中随着表中数据的变化 视图的数据随之改变 对视图的查询与基本表一样对视图的更新将受到一定的限制 视图 一 视图概念二 定义 删除视图三 查询视图四 更新视图五 视图的作用 一 视图概念 视图 3 6 视图是一个虚表数据库中只存放视图的定义视图对应的数据仍存放在原来的表中随着表中数据的变化 视图的数据随之改变 对视图的查询与基本表一样对视图的更新将受到一定的限制 视图概念示意图 二 视图定义 视图 3 6 1 建立视图格式 CREATEVIEW视图名 列名 AS子查询 WITH CHECKOPTION READONLY 表示视图不可更新 下述必须指定全部列名 某个目标列是组函数或表达式多表连接时 目标列中出现同名列不用原基本表的列名作为视图名 在定义视图时要么指定全部视图列 要么全部省略不写 如果省略了视图的属性列名 则视图的列名与子查询列名相同 子查询中通常不包含ORDERBY和DISTINCT子句 虚拟列 经过各种计算派生出的数据所设置的派生属性列 二 视图定义 视图 3 6 2 视图分类行列子集视图 从单个基本表导出 保留基本表的码 但去掉其它的某些列和部分行的视图 表达式视图 带虚拟列的视图 分组视图 子查询目标表带有组函数或子查询带有GROUPBY子句的视图 例1 建立计算机系学生视图CREATEVIEWCS SAS SELECTS SN SEX SAFROMSWHERESD CS WITHCHECKOPTION 行列子集视图 例2 建立计算机系选修 计算机体系结构 课程的学生视图CREATEVIEWCS SCA S NAME GR ASSELECTS S SN GRFROMS SC CWHERES S SC S ANDC C SC C ANDS SD CS ANDCN 计算机体系结构 例3 建立学生出生年份的视图CREATEVIEWBT S SNO NAME BIRTH ASSELECTS SN 2003 SAFROMS 表达式视图 虚拟列 例4 建立学生平均成绩视图CREATEVIEWS AVG G SNO AVG G ASSELECTS AVG ALLGR FROMSCGROUPBYS 分组视图 三 查询视图 视图 3 6 视图消解 ViewResolution 在对视图查询时 DBMS将进行有效性检查 表及视图 若存在 则从数据字典中取出视图定义 并把定义中的子查询与用户查询结合起来转换为等价的对基本表的查询 然后再执行 例1 求计算机系年龄小于20的学生SELECTS SNFROMCS SWHERESA 20 视图消解SELECTS SNFROMSWHERESD CS ANDSA 20 例2 求计算机系选修 C2 课程的学生SELECTS SNFROMCS S SCWHERECS S S SC S ANDSC C C2 例3 在学生平均成绩视图S AVG G中查询平均成绩在90分以上的学生号及成绩SELECT FROMS AVG GWHEREAVG G 90 系统转换后 SELECTS AVG ALLGR FROMSCWHEREAVG ALLGR 90GROUPBYS SELECTS AVG ALLGR FROMSCGROUPBYS HAVINGAVG ALLGR 90 正确的转换结果 四 更新视图 视图 3 6 1 更新视图即通过视图插入 INSERT 删除 DELETE 和修改 UPDATE 数据 实质上转换为对基本表的更新 2 为了防止用户对超出视图范围的基本表的数据进行操作 在定义视图时 应加上WITHCHECKOPTION子句 则在视图上更新数据时 DBMS将检查视图定义中的条件 不满足将拒绝执行 例1 SC S视图的 刘茜 的年龄改为20 转换前 UPDATECS SSETSA 20WHERESN 刘茜 转换后 UPDATESSETSA 20WHERESN 刘茜 ANDSD CS 例2 在CS S中插入 990075 吴迪 女 19 的学生记录 转换前 INSERTINTCS SVALUES 990075 吴迪 女 19 转换后 INSERTINTOSVALUES 990075 吴迪 19 CS 女 例3 删除CS S中年龄大于23的学生 转换前 DELETEFROMCS SWHERESA 23 转换后 DELETEFROMSWHERESA 23ANDSD CS 例4 修改 990075 学生平均成绩为90 转换前 UPDATES AVG GSETAVG G 90WHERESNO 990075 不可转换 左边程序操作失败 3 一般情况下 行列子集视图是可更新的 所以各RDBS均只允许对行列子集视图进行更新 4 不可更新的视图 各系统不太一致 由多个表导出的视图 不可更新视图的列来自表达式或常数 不可插 改 可删视图列是来自组函数 不可更新视图定义中含有GROUPBY子句 不可更新视图定义中内层嵌套的表与查询目标同一个表 不可更新在不允许更新的视图上定义的视图 不可更新 五 视图优点 视图 3 6 提供数据的逻辑独立性提供数据的安全保护功能简化用户的操作 对系统构成的视图 用户不必关心各表间的联系 同一数据多种用法 3 7嵌入式SQL语句 3 7 1嵌入式SQL的一般形式3 7 2嵌入式SQL语句与主语言之间的通信3 7 3不用游标的SQL语句3 7 4使用游标的SQL语句3 7 5动态SQL简介 SQL语言提供了两种不同的使用方式 交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差别 在程序设计的环境下 SQL语句要做某些必要的扩充 3 7嵌入式SQL语句 嵌入式SQL 将SQL语言嵌入到某种高级语言中使用 利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足 这种方式下使用的SQL语言称为嵌入式SQL EmbeddedSQL 嵌入SQL的高级语言称为主语言或宿主语言 嵌入式SQL的一般形式 3 7 1 为了区分SQL语句与主语言语句 需要 前缀 EXECSQL结束标志 随主语言的不同而不同以C为主语言的嵌入式SQL语句的一般形式EXECSQL 例 EXECSQLDROPTABLEStudent 以COBOL作为主语言的嵌入式SQL语句的一般形式EXECSQLEND EXEC例 EXECSQLDROPTABLEStudentEND EXEC 嵌入式SQL的一般形式 3 7 1 嵌入式SQL语句与主语言之间的通信 3 7 2 1 SQL通信区向主语言传递SQL语句的执行状态信息主语言能够据此控制程序流程2 主变量1 主语言向SQL语句提供参数2 将SQL语句查询数据库的结果交主语言进一步处理3 游标解决集合性操作语言与过程性操作语言的不匹配 1 SQL通信区 SQLCA SQLCommunicationAreaSQLCA是一个数据结构 SQLCA的用途SQL语句执行后 DBMS反馈给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息 据此决定接下来执行的语句 嵌入式SQL语句与主语言之间的通信 3 7 2 DBMS工作状态 运行信息SQLCA应用程序后继语句 1 SQL通信区 嵌入式SQL语句与主语言之间的通信 3 7 2 SQLCA的内容与所执行的SQL语句有关与该SQL语句的执行情况有关例 在执行删除语句DELETE后 不同的执行情况 SQLCA中有不同的信息 违反数据保护规则 操作拒绝没有满足条件的行 一行也没有删除成功删除 并有删除的行数无条件删除警告信息由于各种原因 执行出错 1 SQL通信区 SQLCA的使用方法SQL语句执行后 DBMS反馈给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息 据此决定接下来执行的语句 嵌入式SQL语句与主语言之间的通信 3 7 2 SQLCA在应用程序中用EXECSQLINCLUDESQLCA加以定义SQLCODE SQLCA中的一个存放返回代码的变量 每次SQL语句执行后均返回一个值 表示该SQL语句执行是否成功 以及不成功的原因 通常用预定义的常量SUCCESS表示成功 否则在SQLCODE中将存放错误代码 2 主变量 嵌入式SQL语句与主语言之间的通信 3 7 2 什么是主变量嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据在SQL语句中使用的主语言程序变量简称为主变量 HostVariable 2 主变量 嵌入式SQL语句与主语言之间的通信 3 7 2 主变量的类型输入主变量由应用程序对其赋值 SQL语句引用输出主变量由SQL语句赋值或设置状态信息 返回给应用程序一个主变量有可能既是输入主变量又是输出主变量 2 主变量 嵌入式SQL语句与主语言之间的通信 3 7 2 主变量的用途输入主变量指定向数据库中插入的数据将数据库中的数据修改为指定值指定执行的操作指定WHERE子句或HAVING子句中的条件输出主变量获取SQL语句的结果数据获取SQL语句的执行状态 2 主变量 嵌入式SQL语句与主语言之间的通信 3 7 2 指示变量一个主变量可以附带一个指示变量 IndicatorVariable 什么是指示变量整型变量用来 指示 所指主变量的值或条件指示变量的用途输入主变量可以利用指示变量赋空值输出主变量可以利用指示变量检测出是否空值 值是否被截断 2 主变量 嵌入式SQL语句与主语言之间的通信 3 7 2 定义主变量 EXECSQLBEGINDECLARESECTION 说明主变量和指示变量 EXECSQLENDDECLARESECTION引用主变量 在SQL语句中引用主变量和指示变量时 其变量名前应加 3 游标 嵌入式SQL语句与主语言之间的通信 3 7 2 为什么要使用游标SQL语言与主语言具有不同数据处理方式