03 关系数据库语言SQL（精品）

Click to edit Master title style,Chd.Wang，E-Mail：Chdwang,*,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,第3章 关系数据库语言,SQL,王传栋,南京邮电大学计算机学院软件工程系,内容提纲,1）,SQL,简介,SQL,数据库的体系结构,SQL,的组成,2）,SQL,的数据定义,SQL,模式、基本表和索引的创建和撤销,3）,SQL,的数据查询,SELECT,语句的句法,SELECT,语句的三种形式及各种限定,基本表的连接操作,SQL3,中的递归查询,2,Chd.Wang，E-Mail：Chdwang,内容提纲,4）,SQL,的数据更新,插入、删除和修改语句,5）视图,创建和撤消,视图的更新限制,6）嵌入式,SQL,预处理方式，使用规定，使用技术，卷游标，动态,SQL,语句,7）存储过程与,SQL/PLM,存储过程、存储函数、,SQL/PLM,3,Chd.Wang，E-Mail：Chdwang,引言,SQL,是关系数据库的标准语言,对关系模型的发展和商用,DBMS,的研制起着重要的作用,SQL,语言是介乎于关系代数和元组演算之间的一种语言,SQL,语言的9个核心词汇,Create, Alter, Drop, Select, Insert, Update, Delete,Grant, Revoke,本章详细介绍,SQL,的核心部分内容,数据定义,数据查询,数据更新,嵌入式,SQL,4,Chd.Wang，E-Mail：Chdwang,SQL,简介,SQL,发展史,1970年,美国,IBM,研究中心的,E.F.,Codd,连续发表多篇论文，提出关系模型,1972年,IBM,公司开始研制实验型关系数据库管理系统,SYSTEM R，,配制的查询语言称为,SQUARE,语言,1974年,把,SQUARE,修改为,SEQUEL,语言,1978年，,SEQUEL,简称为,SQL，,即“结构式查询语言”,SQL,的发音仍为“,sequel”，,现在,SQL,已经成为一个标准 ，其发音更倾向于发“,ess,-cue-ell ”,5,Chd.Wang，E-Mail：Chdwang,SQL,简介,SQL,发展史,SQL86,1986,年10月，,ANSI SQL,标准,1987年6月，,ISO SQL,标准,SQL2,1992年8月，,ISO SQL,标准,SQL3,1999,年，,ISO SQL,标准,在未来很长一段时间，,SQL,仍将是关系数据库领域的主流语言,在软件工程、人工智能领域，,SQL,已显示出相当大的潜力,6,Chd.Wang，E-Mail：Chdwang,SQL,简介,SQL,数据库的体系结构,7,Chd.Wang，E-Mail：Chdwang,SQL,简介,SQL,数据库的体系结构,1）一个,SQL,模式（,Schema）,是表和约束的集合,2）一个表由行集构成，一行是列的序列,3）表类型：基本表、视图和导出表,4）基本表与存储文件是,M:N,联系,存储文件与外部存储器的物理文件是一一对应的,5）,SQL,语句执行对基本表和视图查询等操作,在用户看来，两者是一样的，都是表,6）,SQL,用户可以是应用程序，也可以是终端用户,注：虽然,SQL,是国际公认的关系数据库标准，但标准的,SQL,文档中并没有使用“关系”和“数据库”这两个名词,8,Chd.Wang，E-Mail：Chdwang,SQL,简介,SQL,组成,1）数据定义语言，即,SQL DDL,用于定义,SQL,模式、基本表、视图、索引等结构,2）数据操纵语言，即,SQL DML,数据查询,数据更新,：包括,插入、删除和修改,三种操作,3）嵌入式,SQL,语言规则,SQL,语句嵌入在,宿主语言程序,中的规则,4）数据控制语言，即,SQL DCL,授权、完整性规则的描述、事务控制等,9,Chd.Wang，E-Mail：Chdwang,SQL,简介,SQL,特点,1）灵活和强大的查询功能,SELECT,语句能完成相当复杂的查询操作,2）,SQL,不是一个应用开发语言,只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能,3）,SQL,是国际标准语言,有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化,4）,SQL,的词汇不多,完成核心功能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用,10,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,SQL,模式的创建和撤销,注,模式（,Schema,）一词，来自于“,ISO SQL,标准”协议,Schema,在,SQL,数据库中相当于一个容器,数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内,创建,SQL,模式，就是定义一个存储空间,在商业,DBMS,中，大多都使用,Database,代替,Schema,11,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,SQL,模式的创建和撤销,创建,CREATE SCHEMA ,AUTHORIZATION ,撤销,DROP SCHEMA ,CASCADERESTRICT,方式,CASCADE（,级联式）,RESTRICT（,约束式）,商业,DBMS,中,CREATE Database ,DROP Database ,12,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,常用数据类型,1）数值型：,Integer，,Smallint,，Numeric(p, d) / DEC(p,d),2）,字符串型：,Char(n)，,Varchar,(n),3）,位串型：,Bit(n)，Bit Varying(n),4）时间型：,DATE，TIME,注,在很多,DBMS,中支持自定义数据类型，如在,Oracle,中,CREATE DOMAIN ,AS 列级完整性约束 ;,DROP DOMAIN ;,13,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,常用数据类型,示例：在,Oracle,中定义数据类型与应用,定义,Create domain,COLOR,Char(6) Default ?,Constraint,VALID_COLORS,Check ( value in (Red, Yellow, Green, ? ) ) ;,使用,Create Table PART (, ,partcolor,COLOR, ) ;,14,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,句法,CREATE TABLE (, 域完整性约束条件, , 域完整性约束条件 , , ) ;,域完整性约束条件,NOT NULL / ,NULL, ：,列值是否可以为空,UNIQUE ：,列值唯一，不得重复,DEFAULT ：,列值空缺时，由系统填写默认值,15,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,表级完整性约束条件,主键（,PRIMARY KEY）,子句,格式：,PRIMARY KEY ( ),作用：提供实体完整性约束的说明,说明,系统一般自动在主键上建索引,主键为单属性时，可直接在属性后的域完整性约束条件中，使用,PRIMARY KEY,定义主键,16,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,表级完整性约束条件,外键（,FOREIGN KEY）,子句,格式：,Foreign key ,外键名 ( ),references (列名表2) ,on delete ,作用：提供参照完整性约束的说明,17,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,表级完整性约束条件,外键（,FOREIGN KEY）,子句,on delete ,子句的说明,RESTRICT ：,主表的,主键值行,不得删除,CASCADE ：,主表,主键值行,删除，,从表的相关行,随之删除,SET NULL ：,主表,主键值行,删除，,从表的相关行,的列值设为,NULL，,前提是该列的值可以为,NULL,18,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,表级完整性约束条件,检查（,CHECK）,子句,格式：,CHECK (,约束表达式),作用：对某元组某属性取值的约束说明,说明,表创建后是一个空表，需要使用,DML（insert，update，delete）,语句装入或维护数据行,19,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,示例,20,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,示例,21,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,示例,22,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的创建,问关于表,T、S、C、SC,的创建顺序，下列选项正确的是？,A） C,T,SC,S,B） T,S,C,SC,C） S,T,C,SC,D） S,T,SC,C,E） T,C,S,SC,F） SC,C,S,T,G） SC,C,T,S,23,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的修改,句法,ALTER TABLE ,增加属性,Alter table ,add ；,删除属性,Alter table ,drop ,cascaderestrict,CASCADE：,引用该列的视图和约束随之删除,RESTRICT：,只在没有被引用时才可删除,不是所有的,DBMS,都支持,24,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的修改,句法,ALTER TABLE ,修改属性的类型,Alter table ,modify, ,modify, alter column,其他修改，如,补充定义主键，撤销主键定义,补充定义外键，撤销外键定义,定义和撤销别名,25,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,基本表的创建、修改和撤销,表的撤销,句法,DROP TABLE ,cascaderestrict,说明,CASCADE：,删除该基本表时，所有引用该基本表的视图和约束一起自动被删除,RESTRICT：,没有视图和约束引用该基本表时，才能撤销,26,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,索引的建立和撤销,索引概念,是物理存取路径，不属于逻辑数据模式,RDBMS,通常在主键上自动建立索引,查询、更新时自动起作用（,适当建立索引会提高查询速度,）,句法,创建索引,CREATE,UNIQUE,INDEX ,ON,基表名 (, ,ASC,DESC, , ,ASC,DESC ,) ;,撤销索引,DROP INDEX ;,UNIQUE,索引，只能建立在候选键上,27,Chd.Wang，E-Mail：Chdwang,SQL,的数据定义,索引的建立和撤销,示例,Create index Age_Indexon S(Age) ;,Create unique index Age_Index on S(Age ASC) ;,Create unique index,SC_Index,on SC(SNO, CNO DESC) ;,Drop index Age_Index, SC_Index ;,Create index C_Index,on C(TNO DESC,Credit DESC) ;,28,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的基本结构,句型,SELECT ,FROM ,WHERE ,只有,SELECT,和,FROM,子句是每个,SQL,查询语句所必需的,语义,示例,查询选修了,DB,课程的学生的姓名,29,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的基本结构,示例,SNAME,(,CNAME=DB AND S.SNO=SC.SNO AND SC.CNO=C.CNO,(S,SC,C),Select,Sname,From S, SC, C,Where,Cname,=DB,And S.,Sno,=SC.,Sno,And SC.,Cno,=C.,Cno,1000,行,1000,行,10000,行,30,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的基本结构,示例,SNAME,(,CNAME=DB,(S,SC,C),DBMS,的优化,SNAME,(,SNO,SNAME,(,S),SNO,(,CNO,(,CNAME=DB,(C),SNO,CNO,(,SC),Select,Sname,From S,Where,Sno,in ( Select,Sno,From SC,Where,Cno,in ( Select,Cno,From C,Where,Cname,=DB ) ;,CNO,(,CNAME=DB,(C),SNO,CNO,(,SC),SNO,SNAME,(,S) ,SNO,( ),SNAME,( ),1000,1,10000,= 1000,1000,= 1000,= 1000,=3）以上的信息,2）显示,TNO、CNO,和选修人数,3）显示时，查询结果按选修人数降序排列，人数相同按,TNO,升序、,CNO,降序排列,52,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的完整结构,应用示例,Select,Tno, C.,Cno, COUNT(,Sno,) as,选修人数,From C, SC,Where C.,Cno,= SC.,Cno,Group By,Tno, C.,Cno,Having COUNT(*)=3,Order By 3 DESC,Tno, C.,Cno,DESC,53,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的完整结构,GROUP BY,HAVING,作用,1）数据按,GROUP BY,子句列名序列中的列值进行分组,2）组内数据按,SELECT,子句中的聚合函数进行计算,3）提取满足,HAVING,子句的条件表达式值的分组,注意,H,AVING,子句支持聚合函数,W,HERE,子句不支持聚合函数,S,ELECT,子句只能取聚集函数,或,GROUP BY,子句指的列,54,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的完整结构,ORDER BY,ASC,DESC, ,ASC,DESC,对查询结果按子句中指定列的值排序，如果,ORDER BY,后有多个列名,先按第一列名值排序,再对于第一列值相同的行，按第二列名值排序,依次类推 ,列序号是在,SELECT,子句中出现的序号（选的列是聚集函数或表达式时）,ASC,表示升序，,DESC,表示降序，缺省时表示升序,55,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SELECT,语句的完整结构,应用示例,问：在使用聚合函数进行数据统计时，为什么有的语句使用了,Group By,子句，有的没有？,什么情况下，应该有？可以没有？,56,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,查询中的限制和规定,Select,子句的规定,子句描述查询输出的表格结构，即输出值的列名或表达式,格式,S,ELECT,ALL,DISTINCT,*,释义,All,Distinct，,保留消除,查询结果中的重复行,* 是对,From,子句中表的所有列的简写,列表达式,列名、常量、算数运算符、函数、聚合函数,57,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,查询中的限制和规定,别名,用关键字,AS,为列、表、表达式起别名，,AS,可以省略,集合运算,(,Select,查询1),UNION,ALL,(Select,查询2),(,Select,查询1),INTERSECT,ALL,(,Select,查询2),(,Select,查询1),EXCEPT,ALL,(,Select,查询2),备注,并交差运算的前提：相同的关系模式,关键字,ALL,，,表示保留运算后的重复元组,58,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,查询中的限制和规定,示例,Select,NativePlace,Sname, Sex,From S ;,Select,Sno,Sname, Age, Sex,NativePlace,From S ;,Select,*,From S ;,59,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,查询中的限制和规定,示例,Select,Sname,Year(,getdate,() Age as,BirthYear,From S AS X ;,Select,Sname,as,姓名, ,BirthYear,: as BIRTH,2011 Age as YEAY, Lower(Sex) as,性别,From S ;,说明,函数,getdate,(), year(), month(), day(),函数,upper(), lower(),60,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,查询中的限制和规定,示例,Select,*,From S,Where Sex=F Or,NativePlace,=,四川,(,Select * From S Where Sex=F),UNION,(Select * From S Where,NativePlace,=,四川),(,Select * From S Where Sex=F),UNION ALL,(Select * From S Where,NativePlace,=,四川),61,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,查询中的限制和规定,示例,Select,Sno,From SC ;,Select,All,Sno,From SC ;,Select,Distinct,Sno,From SC ;,Select,top 5,Sno,From SC ;,62,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,构成,Where,语句的条件运算符,63,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,算数比较运算,查询学号是,S3,的学生姓名,Select,Sname,From S Where,Sno,= S3 ;,查询年龄小于18的学生姓名,Select,Sname,From S Where Age =17 and Age=17 and Age=19),;,Select,Sno,From S Where Age19,;,65,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,字符匹配运算,查询籍贯不,是上海的学生姓名,Select,Sname,From S Where,NativePlace,= ,上海 ;,Select,Sname,From S,Where,NativePlace,Like %,上海% ;,Select,Sname,From S,Where,NativePlace,Like %,上,%,海% ;,Select,Sname,From SWhere,NativePlace, ,上海 ;,Select,Sname,From S,Where,NativePlace,Not Like %,上海% ;,66,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,字符匹配运算,注解,LIKE,与通配符“%”和“_”,使用格式：,not Like ,Escape ,“%” ：字符串中所处位置的0,n,个字符,“_”,：字符串中所处位置的 1 个字符,换码字符,查询课程名以“,DB_C%A”,开头的课程号和学分,Select,Cno, Credit,From C,Where,Cname,like DB_C%A%,Escape ,;,67,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,空值比较运算,查询未,缺考学生的学号和课程号,Select,Sno,Cno,From SC,Where Grade is Null ;,Select,Sno,Cno,From SC,Where Grade is not Null ;,说明，在,Where,语句中,“,is NULL”,不能用“=,NULL”,替换,“,is not NULL”,不能用“,NULL”,替换,68,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,空值比较运算,查询未,缺考,过,的学生的学号,Select Distinct,Sno,From SC,Where Grade is Null ;,Select Distinct,Sno,From SC,Where Grade is not Null ;,Select,Sno,From S,Where,Sno,not in ( Select,Sno,From SC,Where Grade is Null ) ;,69,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合成员运算,查询未,选修,C2,或,C3,课程的学生学号,Select,Sno,From SC,Where,Cno,= C2 or,Cno,= C3 ;,Select,Sno,From SC,Where,Cno,= (C2, C3) ;,Select,Sno,From SC Where,Cno,in (C2, C3) ;,Select,Sno,From SC Where,Cno,=SOME(C2, C3) ;,Select,Sno,From SC Where,Cno,not in (C2, C3) ;,Select,Sno,From SC Where,Cno,ALL(C2, C3) ;,70,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合成员运算,查询未,选修,C2,或,C3,课程的学生学号,Select,Sno,From S,Where,Sno,not in (,Select,Sno,from SC,Where,Cno,in (C2, C3) ) ;,Select,Sno,From S,Where,Sno,ALL (,Select,Sno,from SC,Where,Cno,in (C2, C3) ) ;,71,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合成员运算,查询,未,选修,LIU,老师所授课程的学生学号,Select,Sno,From SC, C, T,Where,Tname,=LIU,and SC.,Cno,=C.,Cno,and C.TNO=T.,Tno,;,Select,Sno,From SC Where,Cno,in (,Select,Cno,From C Where,Tno,in (,Select,Tno,From T Where,Tname,=LIU ) ) ;,Select,Sno,From S,Where,Sno,not in,(,选过的学号 ) ;,72,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合成员运算,查询选修了,LIU,老师所授全部课程的学生姓名,Select,Sname,From S,Where not Exists (,Select * From C,Where,Tno,in (Select,Tno,From T,Where,Tname,=LIU ),and not Exists (,Select * From SC,Where,Sno,=S.,Sno,and,Cno,=C.,Cno,) );,73,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合成员运算,查询至少有一门成绩超过,S4,一门成绩的学生学号,Select Distinct,Sno,From SC,Where Grade Some(,Select Grade From SC,Where,Sno,=S4 ) ;,74,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合成员运算,查询平均成绩最高的学生学号,使用导出表,Select S#,From ( Select,Sno, AVG(Grade),From SC Group By,Sno,) AS,TmpTable,(S#, AVG_Grade),Where AVG_Grade = ALL ( Select AVG(Grade),From SC,Group By,Sno,) ;,75,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合空否的测试,Select ,From R,Where,NOT,EXISTS,( Select *,From S,Where S.A=R.A and ) ;,说明,当内层集合非空，返回,True，,否则返回,False,76,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,条件表达式中的比较操作,集合中是否有重复元组的判定,查询只选修了一门课的学生的学号和姓名,Select,Sno,Sname,From S,Where unique( Select,Sno,From SC,Where,Sno,=S.,Sno,) ;,Select S.,Sno,Sname,From S, SC,Where S.,Sno,=SC.,Sno,Group by S.,Sno,Sname,Having Count(,Cno,) = 1 ;,77,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,嵌套查询的改进写法,导出表,在,From,子句中使用子查询，子查询必须起别名,命名的导出表只在,From,中起作用，离开,From,子句失效,WITH,语句和临时视图（,SQL Server 2005,以上版本）,作用类同于导出表，但语句的逻辑组织比含导出表更清晰,WITH,TmpTable,(S#, AVG_Grade) AS,Select,Sno, AVG(Grade) From SC Group By,Sno,Select S# From,TmpTable,Where AVG_Grade = ALL ( Select AVG_Grade,From,TmpTable,) ;,78,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,基本表的联接操作,略,79,Chd.Wang，E-Mail：Chdwang,SQL,的数据查询,SQL3,中的递归查询,略,80,Chd.Wang，E-Mail：Chdwang,SQL,的数据更新,数据插入,句法,1）单元组的插入,INSERT INTO,基本表名 (列名表),VALUES(,元组值),2）多元组的插入：,不是所有版本的,DBMS,都支持,INSERT INTO,基本表名 (列名表),VALUES (,元组值), , (元组值),3）查询结果的插入,INSERT INTO,基本表名 (列名表) ,4）表的插入：,INSERT INTO,基本表名1 (列名表),TABLE,基本表名2,81,Chd.Wang，E-Mail：Chdwang,SQL,的数据更新,数据插入,说明,若插入的元组值，其属性个数、次序和域，与表结构的定义一致，则列名表可以省略,否则需要列名表，且列名表的属性个数和次序，需与待插入值一一对应,数据插入受关系完整性约束的制约,数据类型,实体完整性：主键值唯一，非空