1、第3章 关系数据库语言SQL,2,内容提纲,1）SQL简介SQL数据库的体系结构SQL的组成2）SQL的数据定义SQL模式、基本表和索引的创建和撤销3）SQL的数据查询SELECT语句的句法SELECT语句的三种形式及各种限定基本表的连接操作,3,内容提纲,4）SQL的数据更新插入、删除和修改语句5）视图创建和撤消视图的更新限制6）嵌入式SQL（自学）7）存储过程与SQL/PLM（自学）,4,引言,SQL是关系数据库的标准语言对关系模型的发展和商用DBMS的研制起着重要的作用SQL语言是介乎于关系代数和元组演算之间的一种语言SQL语言的9个核心词汇Create, Alter, Drop, Se
2、lect, Insert, Update, DeleteGrant, Revoke本章详细介绍SQL的核心部分内容数据定义数据查询数据更新,5,SQL简介,SQL发展史 1970年美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型1972年IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE语言1974年把SQUARE修改为SEQUEL语言1978年，SEQUEL简称为SQL，即“结构式查询语言”,6,SQL简介,SQL发展史SQL861986年10月，ANSI SQL标准1987年6月，ISO SQL标准SQL21992年8月，ISO S
3、QL标准SQL31999年，ISO SQL标准在未来很长一段时间，SQL仍将是关系数据库领域的主流语言在软件工程、人工智能领域，SQL已显示出相当大的潜力,7,SQL简介,SQL数据库的体系结构,8,SQL简介,SQL数据库的体系结构1）一个SQL模式（Schema）是表和约束的集合2）一个表由行集构成，一行是列的序列3）表类型：基本表、视图和导出表 4）基本表与存储文件是M:N联系存储文件与外部存储器的物理文件是一一对应的5）SQL语句执行对基本表和视图查询等操作在用户看来，两者是一样的，都是表6）SQL用户可以是应用程序，也可以是终端用户,9,SQL简介,SQL组成1）数据定义语言，即SQ
4、L DDL用于定义SQL模式、基本表、视图、索引等结构2）数据操纵语言，即SQL DML数据查询数据更新：包括插入、删除和修改三种操作3）嵌入式SQL语言规则SQL语句嵌入在宿主语言程序中的规则4）数据控制语言，即SQL DCL授权、完整性规则的描述、事务控制等,10,SQL简介,SQL特点1）灵活和强大的查询功能SELECT语句能完成相当复杂的查询操作2）SQL不是一个应用开发语言只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能3）SQL是国际标准语言有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化4）SQL的词汇不多完成核心功
5、能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用,11,SQL的数据定义,SQL模式的创建和撤销注模式（Schema）一词，来自于“ISO SQL标准”协议Schema在SQL数据库中相当于一个容器数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内创建SQL模式，就是定义一个存储空间在商业DBMS中，大多都使用Database代替Schema,12,SQL的数据定义,SQL模式的创建和撤销创建CREATE SCHEMA AUTHORIZATION 撤销DROP SCHEMA CASCADERESTRICT方式CASCADE（级联式）RESTRICT（约束式）
6、商业DBMS中CREATE Database DROP Database ,13,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 ;,14,SQL的数据定义,常用数据类型示例：在Oracle中定义数据类型与应用定义Create domain COLOR Ch
7、ar(6) Default ?Constraint VALID_COLORSCheck ( value in (Red, Yellow, Green, ? ) ) ;使用Create Table PART ( partcolor COLOR , ) ;,15,SQL的数据定义,基本表的创建、修改和撤销表的创建句法CREATE TABLE ( 域完整性约束条件 , 域完整性约束条件 , ) ;域完整性约束条件NOT NULL / NULL ：列值是否可以为空 UNIQUE ：列值唯一，不得重复DEFAULT ：列值空缺时，由系统填写默认值,16,SQL的数据定义,基本表的创建、修改和撤销表的创建
8、表级完整性约束条件主键（PRIMARY KEY）子句格式：PRIMARY KEY ( )作用：提供实体完整性约束的说明说明系统一般自动在主键上建索引主键为单属性时，可直接在属性后的域完整性约束条件中，使用PRIMARY KEY定义主键,17,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGN KEY）子句格式：Foreign key 外键名 ( ) references (列名表2) on delete 作用：提供参照完整性约束的说明,18,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGN KEY）子句on dele
9、te 子句的说明RESTRICT ：主表的主键值行不得删除 CASCADE ：主表主键值行删除，从表的相关行随之删除SET NULL ：主表主键值行删除，从表的相关行的列值设为NULL，前提是该列的值可以为NULL,19,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件检查（CHECK）子句 格式：CHECK (约束表达式)作用：对某元组某属性取值的约束说明说明表创建后是一个空表，需要使用DML（insert，update，delete）语句装入或维护数据行,20,SQL的数据定义,基本表的创建、修改和撤销表的创建示例,21,SQL的数据定义,基本表的创建、修改和撤销表的创
10、建示例,22,SQL的数据定义,基本表的创建、修改和撤销表的创建示例,23,SQL的数据定义,基本表的创建、修改和撤销表的创建问关于表T、S、C、SC的创建顺序，下列选项正确的是？A） C T SC SB） T S C SCC） S T C SCD） S T SC CE） T C S SCF） SC C S TG） SC C T S,24,SQL的数据定义,基本表的创建、修改和撤销表的修改句法ALTER TABLE 增加属性Alter table add ；删除属性Alter table drop cascaderestrictCASCADE：引用该列的视图和约束随之删除RESTRICT：只在
11、没有被引用时才可删除不是所有的DBMS都支持,25,SQL的数据定义,基本表的创建、修改和撤销表的修改句法ALTER TABLE 修改属性的类型Alter table modify modify alter column其他修改，如补充定义主键，撤销主键定义补充定义外键，撤销外键定义定义和撤销别名,26,SQL的数据定义,基本表的创建、修改和撤销表的撤销句法DROP TABLE cascaderestrict说明CASCADE：删除该基本表时，所有引用该基本表的视图和约束一起自动被删除RESTRICT：没有视图和约束引用该基本表时，才能撤销,27,SQL的数据定义,索引的建立和撤销索引概念是物
12、理存取路径，不属于逻辑数据模式RDBMS通常在主键上自动建立索引查询、更新时自动起作用（适当建立索引会提高查询速度）句法创建索引CREATE UNIQUE INDEX ON 基表名 ( ASCDESC , ASCDESC ) ;撤销索引DROP INDEX ;,UNIQUE索引，只能建立在候选键上,28,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)
13、;Drop index Age_Index, SC_Index ;Create index C_Indexon C(TNO DESC,Credit DESC) ;,29,SQL的数据查询,SELECT语句的基本结构句型SELECT FROM WHERE 只有SELECT和FROM子句是每个SQL查询语句所必需的 语义示例查询选修了DB课程的学生的姓名,30,SQL的数据查询,SELECT语句的基本结构示例SNAME(CNAME=DB AND S.SNO=SC.SNO AND SC.CNO=C.CNO(SSCC)Select Sname From S, SC, CWhere Cname=DB A
14、nd S.Sno=SC.Sno And SC.Cno=C.Cno,31,SQL的数据查询,SELECT语句的基本结构示例SNAME(CNAME=DB(SSCC)DBMS的优化SNAME(SNO,SNAME(S)SNO(CNO(CNAME=DB(C)SNO,CNO(SC)Select Sname From SWhere Sno in ( Select Sno From SC Where Cno in ( Select Cno From C Where Cname=DB ) ;,32,SQL的数据查询,SELECT语句的基本结构常见的3种SELECT语句写法示例：查询选修C2课程的学生学号和姓名1
15、）连接查询Select S.Sno, SnameFrom S, SCWhere S.Sno=SC.Sno and Cno=C2语句的执行步骤1）笛卡尔积2）等值连接2）选择和投影,33,SQL的数据查询,SELECT语句的基本结构常见的3种SELECT语句写法示例：查询选修C2课程的学生学号和姓名2）IN嵌套查询Select Sno, Sname From S Where Sno in( Select Sno From SC Where Cno=C2 ) ;Select Sno, Sname From S Where C2 in( Select Cno From SC Where Sno=S.
16、Sno ) ;区别1）由里到外，内层查询（执行1次）结果供给外层使用2）依赖于外层值，内层（执行n次）结果供给外层使用,34,SQL的数据查询,SELECT语句的基本结构常见的3种SELECT语句写法示例：查询选修C2课程的学生学号和姓名3）EXISTS嵌套查询Select Sno, Sname From SWhere EXISTS ( Select * From SCWhere Sno=S.Sno and Cno=C2 ) ;说明1）3个位置特殊，需注意2） EXISTS表示“”，判定内层查询结果是否为非空， 即至少存在一个元组（满足外查询的条件）,35,SQL的数据查询,SELECT语句的
17、基本结构基本句型的综合应用1）检索学习课程号为C2课程的学生学号与成绩SNO，Grade（CNO = C2（SC）Select Sno, GradeFrom SCWhere Cno=C2 ;2）检索学习课程号为C2的学生学号与姓名SNO，SNAME（CNO = C2（S SC）略,36,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用3）检索至少选修LIU所授课程一门课程的学生学号与姓名 SNO，SNAME（TNAME = LIU（S SC C T）Select S.Sno, Sname From S, SC, C, TWhere Tname=LIU and S.Sno= SC.
18、Sno and SC.Cno=C.Cno and C.TNO=T.Tno ;Select Sno, Sname From S Where Sno in ( Select Sno From SC Where Cno in ( Select Cno From C Where Tno in ( Select Tno From T Where Tname=LIU ) ) ) ;,37,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用4）检索选修课程号为C2或C4的学生学号SNO（CNO = C2 CNO = C4（SC） Select Sno From SCWhere Cno = C2
19、or Cno = C4 ;Select Sno From SCWhere Cno in (C2, C4) ;Select Sno From SCWhere Cno =SOME(C2, C4) ;,38,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用5）检索至少选修课程号为C2和C4的学生学号1（1 = 4 2 = C2 5 = C4（SCSC）Select X.SnoFrom SC as X, SC as YWhere X.Sno=Y.Sno and X.Cno=C2 and Y.Cno=C4 ;,FROM子句中，可以用 as为表和视图取一别名，这种别名只在本句中有效,39,S
20、C,SC,SCSC,思考：至少选了2门课的学生学号？至少有两个人选的课程号？,40,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用6）检索不学C2课的学生姓名与年龄SNAME，AGE(S) SNAME，AGE(CNO = C2(S SC)不能用连接查询写Select Sname, Age From SWhere Sno not in (Select Sno From SC Where Cno=C2 ) ;Select Sname, Age From S Where not Exists ( Select * From SCWhere Sno = S.Sno and Cno=C2
21、 ) ;,41,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名SNAME（S （SNO，CNO（SC） CNO（C）Select Sname From SWhere not Exists (Select * From CWhere not Exists ( Select * From SCWhere Sno=S.Sno and Cno=C.Cno) );,42,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名理解逐行扫描S表中的每条记录，对每一个Sno，都顺序扫描C表， 读取全部的Cno，形成（Sno，C
22、no）对于每一个Sno的（Sno，Cno） 全集，都在SC表中查找有没有与之对应的行若一个Sno的（Sno，Cno） 全集，都在SC表中找到对应关系，则这个Sno的学生就选修了全部的课程,43,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名理解在SQL中没有全称量词，涉及到“全部”的问题只能通过“(x)P ( x(P)”的转化来解决，即：查询“没有一门课程是他没选修的”的学生姓名SQL规定：子查询的where句可以调用上面任意层次的主查询中关系的属性,44,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名
23、、理解1）查询未选过的所有课程select * from C where not exists ( select * from SC where Cno = C.Cno )2）查询某学生（设学号为X）未选过的所有课程select * from C where not exists( select * from SC where Sno=X and Cno=C.Cno),45,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名、理解3）遍历S表，验证每个学生是不是这个X，若不是就把Sname投影出来select Sname from S where no
24、t exists ( select * from C where not exists (select * from SC where Sno=S.Sno and Cno=C.Cno) ) ;,46,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用8）检索所学课程包含学生S3所学课程的学生学号SNO，CNO（SC） CNO（SNO = S3（SC）Select Distinct Sno From SC as XWhere not Exists (Select * From SC as YWhere Y.Sno=S3 and not Exists ( Select * From S
25、C as ZWhere Sno=X.Sno and Cno=Y.Cno) );,47,SQL的数据查询,SELECT语句的基本结构基本句型的综合应用8）检索所学课程包含学生S3所学课程的学生学号理解“包含学生S3所学课程”是“全部”的一个限定条件思考：查询选修了LIU老师所授全部课程的学生学号？,48,SQL的数据查询,SELECT语句的完整结构句型执行过程 读取FROM子句中的数据源，执行笛卡尔积（） 选择（）满足WHERE子句值的元组 按GROUP BY子句指定的列值，将元组进行分组 在分出的所有组中，选取满足HAVING子句值的组 按SELECT子句的指定项，求值投影（）输出 按ORDE
26、R BY子句对式的输出进行排序,49,SQL的数据查询,SELECT语句的完整结构查询中的5个聚合函数,50,SQL的数据查询,SELECT语句的完整结构应用示例统计选修课程的人次数Select COUNT(Sno) as 人次数From SC ;统计选修课程的人数Select COUNT(Distinct Sno) as 人数From SC ;,51,SQL的数据查询,SELECT语句的完整结构应用示例统计男学生的总人数和平均年龄Select COUNT(*) as 总人数, AVG(Age) as 平均年龄From SWhere Sex = M ;按性别统计学生的总人数和平均年龄Selec
27、t Sex, COUNT(*) as 总人数, AVG(Age) as 平均年龄From SGroup By Sex ;,52,SQL的数据查询,SELECT语句的完整结构应用示例统计每门课程的学生选修人数，要求显示课程号、课程名和学生人数Select C.Cno, Cname, COUNT(Sno) as 学生人数From C, SCWhere C.Cno = SC.CnoGroup By C.Cno, Cname ;,53,SQL的数据查询,SELECT语句的完整结构应用示例按教师号统计每位教师每门课程的学生选修人数，要求：1）仅显示选修人数在3人（=3）以上的信息2）显示TNO、CNO和
28、选修人数3）显示时，查询结果按选修人数降序排列，人数相同按TNO升序、 CNO降序排列,54,SQL的数据查询,SELECT语句的完整结构应用示例Select Tno, C.Cno, COUNT(Sno) as 选修人数From C, SCWhere C.Cno = SC.CnoGroup By Tno, C.CnoHaving COUNT(*)=3Order By 3 DESC, Tno, C.Cno DESC,55,SQL的数据查询,SELECT语句的完整结构GROUP BY HAVING 作用1）数据按GROUP BY子句列名序列中的列值进行分组2）组内数据按SELECT子句中的聚合函数
29、进行计算3）提取满足HAVING子句的条件表达式值的分组注意HAVING子句支持聚合函数WHERE子句不支持聚合函数SELECT子句只能取聚集函数或GROUP BY子句指的列,56,SQL的数据查询,SELECT语句的完整结构ORDER BY ASCDESC, ASCDESC对查询结果按子句中指定列的值排序，如果ORDER BY后有多个列名先按第一列名值排序再对于第一列值相同的行，按第二列名值排序依次类推 列序号是在SELECT子句中出现的序号（选的列是聚集函数或表达式时）ASC表示升序，DESC表示降序，缺省时表示升序,57,SQL的数据查询,SELECT语句的完整结构应用示例问：在使用聚合
30、函数进行数据统计时，为什么有的语句使用了Group By子句，有的没有？什么情况下，应该有？可以没有？,58,SQL的数据查询,查询中的限制和规定Select子句的规定子句描述查询输出的表格结构，即输出值的列名或表达式格式SELECTALLDISTINCT *释义AllDistinct，保留消除查询结果中的重复行* 是对From子句中表的所有列的简写列表达式列名、常量、算数运算符、函数、聚合函数,59,SQL的数据查询,查询中的限制和规定别名用关键字AS为列、表、表达式起别名，AS可以省略集合运算(Select查询1) UNIONALL(Select查询2)(Select查询1) INTERS
31、ECTALL(Select查询2)(Select查询1) EXCEPTALL(Select查询2)备注并交差运算的前提：相同的关系模式关键字ALL，表示保留运算后的重复元组,60,SQL的数据查询,查询中的限制和规定示例Select NativePlace, Sname, Sex From S ;Select Sno, Sname, Age, Sex, NativePlaceFrom S ;Select * From S ;,61,SQL的数据查询,查询中的限制和规定示例Select Sname, Year(getdate() Age as BirthYearFrom S AS X ;Sele
32、ct Sname as 姓名, BirthYear : as BIRTH, 2011 Age as YEAY, Lower(Sex) as 性别From S ;说明函数getdate(), year(), month(), day()函数upper(), lower(),62,SQL的数据查询,查询中的限制和规定示例Select *From SWhere Sex=F Or NativePlace=四川(Select * From S Where Sex=F)UNION(Select * From S Where NativePlace=四川)(Select * From S Where Sex
33、=F)UNION ALL(Select * From S Where NativePlace=四川),63,SQL的数据查询,查询中的限制和规定示例Select SnoFrom SC ;Select All SnoFrom SC ;Select Distinct SnoFrom SC ;Select top 5 SnoFrom SC ;,64,SQL的数据查询,条件表达式中的比较操作构成Where语句的条件运算符,65,SQL的数据查询,条件表达式中的比较操作算数比较运算查询学号是S3的学生姓名Select Sname From S Where Sno = S3 ;查询年龄小于18的学生姓名S
34、elect Sname From S Where Age =17 and Age=17 and Age19 ;,67,SQL的数据查询,条件表达式中的比较操作字符匹配运算查询籍贯不是上海的学生姓名Select Sname From S Where NativePlace = 上海 ;Select Sname From SWhere NativePlace Like %上海% ;Select Sname From SWhere NativePlace Like %上%海% ;Select Sname From SWhere NativePlace 上海 ;Select Sname From SW
35、here NativePlace Not Like %上海% ;,68,SQL的数据查询,条件表达式中的比较操作字符匹配运算注解LIKE与通配符“%”和“_”使用格式：not Like Escape “%” ：字符串中所处位置的0n个字符“_” ：字符串中所处位置的 1 个字符换码字符查询课程名以“DB_C%A”开头的课程号和学分Select Cno, Credit From CWhere Cname like DB_C%A% Escape ;,69,SQL的数据查询,条件表达式中的比较操作空值比较运算查询未缺考学生的学号和课程号Select Sno, Cno From SCWhere Gra
36、de is Null ;Select Sno, Cno From SCWhere Grade is not Null ;说明，在Where语句中“is NULL”不能用“= NULL”替换“is not NULL”不能用“ NULL”替换,70,SQL的数据查询,条件表达式中的比较操作空值比较运算查询未缺考过的学生的学号Select Distinct Sno From SCWhere Grade is Null ;Select Distinct Sno From SCWhere Grade is not Null ;Select Sno From SWhere Sno not in ( Sel
37、ect Sno From SC Where Grade is Null ) ;,71,SQL的数据查询,条件表达式中的比较操作集合成员运算查询未选修C2或C3课程的学生学号Select Sno From SCWhere Cno = C2 or Cno = C3 ;Select Sno From SCWhere 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, C
38、3) ;Select Sno From SC Where Cno ALL(C2, C3) ;,72,SQL的数据查询,条件表达式中的比较操作集合成员运算查询未选修C2或C3课程的学生学号Select Sno From SWhere Sno not in (Select Sno from SCWhere Cno in (C2, C3) ) ;Select Sno From SWhere Sno ALL (Select Sno from SCWhere Cno in (C2, C3) ) ;,73,SQL的数据查询,条件表达式中的比较操作集合成员运算查询未选修LIU老师所授课程的学生学号Selec
39、t Sno From SC, C, TWhere Tname=LIUand 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 ( 选过的学号 ) ;,74,SQL的数据查询,条件表达式中的比较操作集合成员运算查询选修了LIU老师所授全部课程的学生姓名Select Sname From SWhere not Ex
40、ists (Select * From CWhere Tno in (Select Tno From T Where Tname=LIU ) and not Exists ( Select * From SCWhere Sno=S.Sno and Cno=C.Cno) );,75,SQL的数据查询,条件表达式中的比较操作集合成员运算查询至少有一门成绩超过S4一门成绩的学生学号Select Distinct Sno From SCWhere Grade Some(Select Grade From SCWhere Sno=S4 ) ;,76,SQL的数据查询,条件表达式中的比较操作集合成员运算查
41、询平均成绩最高的学生学号使用导出表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 ) ;,77,( ) AS RESULT ( S#, AVG_GRADE ),SELECT S#, AVG_GRADEFROMWHERE AVG_GRADE 80;,用导出表：,SELECT S#，AVG（GRADE） FROM SCGROUP BY S#,78,
42、SQL的数据查询,条件表达式中的比较操作集合空否的测试Select From RWhere NOTEXISTS ( Select * From S Where S.A=R.A and ) ;说明当内层集合非空，返回True，否则返回False,79,SQL的数据查询,条件表达式中的比较操作集合中是否有重复元组的判定查询只选修了一门课的学生的学号和姓名Select Sno, Sname From SWhere unique( Select Sno From SCWhere Sno=S.Sno ) ;Select S.Sno, SnameFrom S, SCWhere S.Sno=SC.SnoGr
43、oup by S.Sno, SnameHaving Count(Cno) = 1 ;,80,SQL的数据查询,嵌套查询的改进写法导出表在From子句中使用子查询，子查询必须起别名命名的导出表只在From中起作用，离开From子句失效WITH语句和临时视图（SQL Server 2005以上版本）作用类同于导出表，但语句的逻辑组织比含导出表更清晰WITH TmpTable(S#, AVG_Grade) ASSelect Sno, AVG(Grade) From SC Group By SnoSelect S# From TmpTableWhere AVG_Grade = ALL ( Select AVG_Grade From TmpTable ) ;,
