关系数据库基本原理-SQL语言(打印).ppt

《关系数据库基本原理-SQL语言(打印).ppt》由会员分享，可在线阅读，更多相关《关系数据库基本原理-SQL语言(打印).ppt（94页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、关系数据库基本原理（2）SQL语言王传栋南京邮电大学计算机学院2内容与要求知识点（1）知识点一：关系模型概述（2）知识点二：关系数据结构（3）知识点三：关系代数理论（4）知识点四：关系数据库标准语言SQL（5）知识点五：关系数据库的规范化理论实验1 SQL语言的应用教学基本要求了解关系数据结构的基本概念，了解关系模型的各种操作和关系代数的基本原理，掌握关系数据模型的完整性约束机制，掌握SQL语言，了解函数依赖等基本概念，掌握关系模式的规范化概念、方法、原理与过程。3引言数据库的应用平台客观世界客观世界数据服务器数据服务器(DBMS)设计设计DB应用服务器应用服务器(软件软件)应用程序应用程序普

2、通用户普通用户DBA嵌入式嵌入式SQL交互式交互式SQL4引言SQL是关系数据库的标准语言对关系模型的发展和商用DBMS的研制起着重要的作用SQL语言是介乎于关系代数和元组演算之间的一种语言SQL语言的9个核心词汇Create,Alter,Drop,Select,Insert,Update,DeleteGrant,Revoke本章详细介绍SQL的核心部分内容数据定义数据查询数据更新嵌入式SQL5SQL简介SQL发展史 1970年美国IBM研究中心的连续发表多篇论文，提出关系模型1972年IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE语言1974年把S

3、QUARE修改为SEQUEL语言1978年，SEQUEL简称为SQL，即“结构式查询语言”SQL的发音仍为“sequel”，现在SQL已经成为一个标准，其发音更倾向于发“ess-cue-ell”6SQL简介SQL发展史SQL861986年10月，ANSI SQL标准1987年6月，ISO SQL标准SQL21992年8月，ISO SQL标准SQL31999年，ISO SQL标准在未来很长一段时间，SQL仍将是关系数据库领域的主流语言在软件工程、人工智能领域，SQL已显示出相当大的潜力7SQL简介SQL数据库的体系结构8SQL简介SQL数据库的体系结构1）一个SQL模式（Schema）是表和约束

4、的集合2）一个表由行集构成，一行是列的序列3）表类型：基本表、视图和导出表 4）基本表与存储文件是M:N联系存储文件与外部存储器的物理文件是一一对应的5）SQL语句执行对基本表和视图查询等操作在用户看来，两者是一样的，都是表6）SQL用户可以是应用程序，也可以是终端用户注：虽然SQL是国际公认的关系数据库标准，但标准的SQL文档中并没有使用“关系”和“数据库”这两个名词9SQL简介SQL组成1）数据定义语言，即SQL DDL用于定义SQL模式、基本表、视图、索引等结构2）数据操纵语言，即SQL DML数据查询数据更新：包括插入、删除和修改三种操作3）嵌入式SQL语言规则SQL语句嵌入在宿主语言

5、程序中的规则4）数据控制语言，即SQL DCL授权、完整性规则的描述、事务控制等10SQL简介SQL特点1）灵活和强大的查询功能SELECT语句能完成相当复杂的查询操作2）SQL不是一个应用开发语言只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能3）SQL是国际标准语言有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化4）SQL的词汇不多完成核心功能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用11SQL的数据定义SQL模式的创建和撤销注模式（Schema）一词，来自于“ISO SQL标准”协议Schema在SQL数据

6、库中相当于一个容器数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内创建SQL模式，就是定义一个存储空间在商业DBMS中，大多都使用Database代替Schema12SQL的数据定义SQL模式的创建和撤销创建CREATE SCHEMA AUTHORIZATION 撤销DROP SCHEMA CASCADERESTRICT方式CASCADE（级联式）RESTRICT（约束式）商业DBMS中CREATE Database DROP Database 13SQL的数据定义常用数据类型1）数值型：Integer，Smallint，Numeric(p,d)/DEC(p,d)2）字符

7、串型：Char(n)，Varchar(n)3）位串型：Bit(n)，Bit Varying(n)4）时间型：DATE，TIME注在很多DBMS中支持自定义数据类型，如在Oracle中CREATE DOMAIN AS 列级完整性约束;DROP DOMAIN ;14SQL的数据定义常用数据类型示例：在Oracle中定义数据类型与应用定义Create domain COLOR Char(6)Default?Constraint VALID_COLORSCheck(value in(Red,Yellow,Green,?);使用Create Table PART(partcolor COLOR,);15

8、SQL的数据定义基本表的创建、修改和撤销表的创建句法CREATE TABLE (域完整性约束条件,域完整性约束条件 ,);域完整性约束条件NOT NULL/NULL：列值是否可以为空 UNIQUE：列值唯一，不得重复DEFAULT：列值空缺时，由系统填写默认值 16SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件主键（PRIMARY KEY）子句格式：PRIMARY KEY()作用：提供实体完整性约束的说明说明系统一般自动在主键上建索引主键为单属性时，可直接在属性后的域完整性约束条件中，使用PRIMARY KEY定义主键 17SQL的数据定义基本表的创建、修改和撤销表的创建表

9、级完整性约束条件外键（FOREIGN KEY）子句格式：Foreign key 外键名()references (列名表2)on delete 作用：提供参照完整性约束的说明18SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGN KEY）子句on delete 子句的说明RESTRICT：主表的主键值行不得删除 CASCADE：主表主键值行删除，从表的相关行随之删除SET NULL：主表主键值行删除，从表的相关行的列值设为NULL，前提是该列的值可以为NULL19SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件检查（CHECK）子句 格式：

10、CHECK(约束表达式)作用：对某元组某属性取值的约束说明说明表创建后是一个空表，需要使用DML（insert，update，delete）语句装入或维护数据行20SQL的数据定义基本表的创建、修改和撤销表的创建示例21SQL的数据定义基本表的创建、修改和撤销表的创建示例22SQL的数据定义基本表的创建、修改和撤销表的创建示例23SQL的数据定义基本表的创建、修改和撤销表的创建问关于表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 S24SQL的数据定义基本表

11、的创建、修改和撤销表的修改句法ALTER TABLE 增加属性Alter table add ；删除属性Alter table drop cascaderestrictCASCADE：引用该列的视图和约束随之删除RESTRICT：只在没有被引用时才可删除不是所有的DBMS都支持25SQL的数据定义基本表的创建、修改和撤销表的修改句法ALTER TABLE 修改属性的类型Alter table modify modify alter column其他修改，如补充定义主键，撤销主键定义补充定义外键，撤销外键定义定义和撤销别名26SQL的数据定义基本表的创建、修改和撤销表的撤销句法DROP TABL

12、E cascaderestrict说明CASCADE：删除该基本表时，所有引用该基本表的视图和约束一起自动被删除RESTRICT：没有视图和约束引用该基本表时，才能撤销27SQL的数据定义索引的建立和撤销索引概念是物理存取路径，不属于逻辑数据模式RDBMS通常在主键上自动建立索引查询、更新时自动起作用（适当建立索引会提高查询速度）句法创建索引CREATE UNIQUE INDEX ON 基表名(ASCDESC ,ASCDESC );撤销索引DROP INDEX ;UNIQUE索引，只能建立在候选键上28SQL的数据定义索引的建立和撤销示例Create index Age_Index on S(

13、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_Indexon C(TNO DESC,Credit DESC);29SQL的数据更新数据插入句法1）单元组的插入INSERT INTO 基本表名(列名表)VALUES(元组值)2）多元组的插入：不是所有版本的DBMS都支持INSERT INTO 基本表名(列名表)VALUES(元组值),(元组值)3）查询结果的插

14、入INSERT INTO 基本表名(列名表)4）表的插入：INSERT INTO 基本表名1(列名表)TABLE 基本表名230SQL的数据更新数据插入说明若插入的元组值，其属性个数、次序和域，与表结构的定义一致，则列名表可以省略 否则需要列名表，且列名表的属性个数和次序，需与待插入值一一对应数据插入受关系完整性约束的制约数据类型实体完整性：主键值唯一，非空 参照完整性：不允许引用不存在的实体用户自定义的完整性31SQL的数据更新数据插入示例Insert Into S(Sno,Sname,Age,Sex,NativePlace)Values(S5,GU,20,M,NULL);Insert In

15、to S Values(S5,GU,20,M,NULL);Insert Into S(Sno,Sname,Age,Sex)Values(S5,GU,20,M);Insert Into S(Sname,Sno,Sex,Age)Values(GU,S5,M,20);Insert Into SC(S#,C#)Values(S5,C8);Insert Into SC Values(S5,C8,NULL);32数据插入示例Insert Into S_Grade(S#,AVG_Grade)Select Sno,AVG(Grade)From SCWhere Sno in(Select Sno From S

16、Where SexM)Group By SnoHaving AVG(Grade)80;Insert Into SC(Sno,Cno)TABLE SC4;SQL的数据更新33SQL的数据更新数据插入示例，问：Insert Into SC Values(S4,C569,105);Insert Into SC Values(S4,C5,105);34SQL的数据更新数据插入示例，问：Insert Into SC Values(S4,C5,95);Insert Into SC Values(S4,C4,95);如图示4条相关数据的插入顺序是什么？35SQL的数据更新数据删除句法DELETE FROM

17、WHERE 条件表达式 语义是从基本表中删除满足条件表达式的元组注意DELETE语句只能从一个基本表中删除元组WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件如果没有WHERE子句，则删除表的所有元组，表成为空表36SQL的数据更新数据删除示例Delete From SC Where Grade is NULL;Delete From C Where Cname Like%M%;Delete From SC Where Cno in(Select Cno From C Where Cname=MATHS);Delete From SC,CWhere SC.Cno=C.Cno an

18、d Cname=MATHS;Delete From SC Where Cno=C4 and Grade (Select AVG(Grade)From SC Where Cno=C4);37SQL的数据更新数据删除示例，问Delete From S Where Sname Like%U%;38SQL的数据更新数据删除示例，问Delete From S Where Sname Like%U%;Delete From SC Where Sno in(Select Sno From SWhere Sname Like%U%);Delete From S Where Sname Like%U%;39SQ

19、L的数据更新数据删除示例，问如图示4条相关数据的删除顺序是什么？Drop Table SC 与Delete From SC的区别是什么TRUNCATE TABLE SC40SQL的数据更新数据删除当LIU老师不再教授课程时，删除其基本信息Delete From TWhere Tname=LIU AND Tno Not in（Select Tno From C）当PM课程没人选修时，删除这门课程信息当WANG同学不再选修课程时，删除其基本信息41SQL的数据更新数据删除TRUNCATE TABLE 与 DELETE From 功能上相同：均删除表中的全部行但 TRUNCATE TABLE 比 D

20、ELETE 速度快，且使用的系统和事务日志资源少DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放42SQL的数据更新数据删除TRUNCATE TABLE 与 DELETE From TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变；新行标识所用的计数值重置为该列的种子，如果想保留标识计数值，应使用 DELETE如果要删除表定义及其数据，应使用 DROP TABLE 语句对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCAT

21、E TABLE，而应使用不带 WHERE 子句的 DELETE 语句由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器 TRUNCATE TABLE 不能用于参与了索引视图的表43SQL的数据更新数据修改句法UPDATE SET 列名=值表达式，列名=值表达式ROW=(元组)WHERE 条件表达式 语义修改基本表中满足条件表达式元组的指定属性值说明WHERE子句表示要修改的元组需满足的条件SET子句表示要修改的列及其新值或元组值UPDATE语句一次只能修改一个表中的元组44SQL的数据更新数据修改示例Update C Set Cname=SE Where Cno=C3;U

22、pdate SC Set Grade=Grade*1.1Where Sno in(Select Sno From S Where Sex=F);Update SC,S Set Grade=Grade*1.1Where SC.Sno=S.Sno and Sex=F;45SQL的数据更新数据修改示例Update SC Set Grade=Grade*1.05Where CnoC4 and Grade (Select AVG(Grade)From SC Where C#C4);Update CSet Row=(C3,SE,4,64,C1,T2)Where Cno=C3;46SQL的数据更新数据修改示

23、例Update SC Set Grade=60Where Grade is NULL;Update SC Set Grade=Grade+60Where Grade is NULL;47SQL的数据更新数据修改示例Update SC Set Grade is NULLWhere Grade=70 and Cno in(Select Cno From C Where Cname=MATH);Update SC Set Grade=NULLWhere Grade=70 and Cno in(Select Cno From C Where Cname=MATH);48SQL的数据更新数据修改示例Up

24、date SC Set Grade=Grade*1.5Where Sno=S2 and Cno=C3;Update C Set Tno=T3 Where Cno=C3;49SQL的数据更新数据修改示例Update C Set Cno=C3 Where Cno=C4;Delete From SC Where Cno=C4;Update C Set Cno=C3 Where Cno=C4;Update CSet Cno=NULLWhere Cno=C4;50SQL的数据查询SELECT语句的基本结构句型SELECT FROM WHERE 只有SELECT和FROM子句是每个SQL查询语句所必需的

25、语义示例查询选修了DB课程的学生的姓名51SQL的数据查询SELECT语句的基本结构示例SNAME(CNAME=DB AND S.SNO=SC.SNO AND SC.CNO=C.CNO(SSCC)Select Sname From S,SC,CWhere Cname=DB And S.Sno=SC.Sno And SC.Cno=C.Cno1000行1000行10000行52SQL的数据查询SELECT语句的基本结构示例SNAME(CNAME=DB(SSCC)DBMS的优化SNAME(SNO,SNAME(S)SNO(CNO(CNAME=DB(C)SNO,CNO(SC)Select Sname F

26、rom SWhere 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()1000110000=10001000=1000=1000=3）以上的信息2）显示TNO、CNO和选修人数3）显示时，查询结果按选修人数降序排列，人数相同按TNO升序、CNO降序排列62SQL的数据查询SELECT语句的完整结构应用示例Select Tno,C.Cno,COUNT(Sno)as 选修人数From C,SCWhere

27、 C.Cno=SC.CnoGroup By Tno,C.CnoHaving COUNT(*)=3Order By 3 DESC,Tno,C.Cno DESC63SQL的数据查询SELECT语句的完整结构GROUP BY HAVING 作用1）数据按GROUP BY子句列名序列中的列值进行分组2）组内数据按SELECT子句中的聚合函数进行计算3）提取满足HAVING子句的条件表达式值的分组注意HAVING子句支持聚合函数WHERE子句不支持聚合函数SELECT子句只能取聚集函数或GROUP BY子句指的列 64SQL的数据查询SELECT语句的完整结构ORDER BY ASCDESC,ASCDE

28、SC对查询结果按子句中指定列的值排序，如果ORDER BY后有多个列名先按第一列名值排序再对于第一列值相同的行，按第二列名值排序依次类推 列序号是在SELECT子句中出现的序号（选的列是聚集函数或表达式时）ASC表示升序，DESC表示降序，缺省时表示升序65SQL的数据查询SELECT语句的完整结构应用示例问：在使用聚合函数进行数据统计时，为什么有的语句使用了Group By子句，有的没有？什么情况下，应该有？可以没有？66SQL的数据查询查询中的限制和规定Select子句的规定子句描述查询输出的表格结构，即输出值的列名或表达式格式SELECTALLDISTINCT*释义AllDistinct

29、，保留消除查询结果中的重复行*是对From子句中表的所有列的简写列表达式列名、常量、算数运算符、函数、聚合函数67SQL的数据查询查询中的限制和规定别名用关键字AS为列、表、表达式起别名，AS可以省略集合运算(Select查询1)UNIONALL(Select查询2)(Select查询1)INTERSECTALL(Select查询2)(Select查询1)EXCEPTALL(Select查询2)备注并交差运算的前提：相同的关系模式关键字ALL，表示保留运算后的重复元组68SQL的数据查询查询中的限制和规定示例Select NativePlace,Sname,Sex From S;Select

30、Sno,Sname,Age,Sex,NativePlaceFrom S;Select*From S;69SQL的数据查询查询中的限制和规定示例Select Sname,Year(getdate()Age as BirthYearFrom S AS X;Select Sname as 姓名,BirthYear:as BIRTH,2011 Age as YEAY,Lower(Sex)as 性别From S;说明函数getdate(),year(),month(),day()函数upper(),lower()70SQL的数据查询查询中的限制和规定示例Select*From SWhere Sex=F

31、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=四川)71SQL的数据查询查询中的限制和规定示例Select SnoFrom SC;Select All SnoFrom SC;Select Distinct SnoFrom SC;Select top 5 SnoFrom SC;72SQL的数据查询条件表达式中的比较操作构成Where语句的

32、条件运算符73SQL的数据查询条件表达式中的比较操作算数比较运算查询学号是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;75SQL的数据查询条件表达式中的比较操作字符匹配运算查询籍贯不是上海的学生姓名Select Sname From S Where NativePlace=上海;Select Sname From SWhere NativePlace Like%上

33、海%;Select Sname From SWhere NativePlace Like%上%海%;Select Sname From SWhere NativePlace 上海;Select Sname From SWhere NativePlace Not Like%上海%;76SQL的数据查询条件表达式中的比较操作字符匹配运算注解LIKE与通配符“%”和“_”使用格式：not Like Escape“%”：字符串中所处位置的0n个字符“_”：字符串中所处位置的 1 个字符换码字符查询课程名以“DB_C%A”开头的课程号和学分Select Cno,Credit From CWhere Cn

34、ame like DB_C%A%Escape ;77SQL的数据查询条件表达式中的比较操作空值比较运算查询未缺考学生的学号和课程号Select Sno,Cno From SCWhere Grade is Null;Select Sno,Cno From SCWhere Grade is not Null;说明，在Where语句中“is NULL”不能用“=NULL”替换“is not NULL”不能用“NULL”替换78SQL的数据查询条件表达式中的比较操作空值比较运算查询未缺考过的学生的学号Select Distinct Sno From SCWhere Grade is Null;Sele

35、ct Distinct Sno From SCWhere Grade is not Null;Select Sno From SWhere Sno not in(Select Sno From SC Where Grade is Null);79SQL的数据查询条件表达式中的比较操作集合成员运算查询未选修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

36、Where Cno=SOME(C2,C3);Select Sno From SC Where Cno not in(C2,C3);Select Sno From SC Where Cno ALL(C2,C3);80SQL的数据查询条件表达式中的比较操作集合成员运算查询未选修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);81SQL的数据查询

37、条件表达式中的比较操作集合成员运算查询未选修LIU老师所授课程的学生学号Select 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(选过的学号);82SQL的数据查询条件表达式中的比较操作集合成员运算查询选修了LIU老师所授全部课程的学生姓名Select

38、 Sname From SWhere not Exists(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);83SQL的数据查询条件表达式中的比较操作集合成员运算查询至少有一门成绩超过S4一门成绩的学生学号Select Distinct Sno From SCWhere Grade Some(Select Grade From SCWhere Sno=S4 );84SQL的数据查询条件表达式中的比较操作集

39、合成员运算查询平均成绩最高的学生学号使用导出表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);85SQL的数据查询条件表达式中的比较操作集合空否的测试Select From RWhere NOTEXISTS (Select*From S Where S.A=R.A and );说明当内层集合非空，返回True，否则返回False86SQL的数据查询条件表达式中的比

40、较操作集合中是否有重复元组的判定查询只选修了一门课的学生的学号和姓名Select Sno,Sname From SWhere unique(Select Sno From SCWhere Sno=S.Sno);Select S.Sno,SnameFrom S,SCWhere S.Sno=SC.SnoGroup by S.Sno,SnameHaving Count(Cno)=1;87SQL的数据查询嵌套查询的改进写法导出表在From子句中使用子查询，子查询必须起别名命名的导出表只在From中起作用，离开From子句失效WITH语句和临时视图（SQL Server 2005以上版本）作用类同于导出

41、表，但语句的逻辑组织比含导出表更清晰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 );88视图虚表创建视图仅定义视图结构和抽取数据的规则不存储视图对应的数据在使用视图时动态地从相应的基本表中获取数据View用户的局部数据视角89视图创建句法CREATE VIEW 列名表 AS 说明列名表是否省略，要看具体情况对视图查询，和对基本表的查询一样，没有限制对视图的

42、更新（insert、update、delete）是受限制的撤销句法DROP VIEW 90视图视图的更新限制对视图的数据更新，最终要更新到基本表上只有“行列子集视图”的更新是不受限制的行列子集视图定义在单个基本表上定义时仅使用了选择、投影操作包含了基本表的候选键和所有的非空字段其他形式的视图都不允许更新操作，如视图的列定义中使用了表达式或函数视图定义来自多表连接视图定义中用到GROUP BY子句或聚集函数91视图示例构建成绩子模式，要求显示学号、姓名、课程号和成绩G视图不允许更新G(S#，SNAME，C#，SCORE)92视图示例Create View S_MALEasSelect Sno,S

43、name,AgeFrom SWhere Sex=M;视图S_MALE是可以更新的Insert into S_MALEValues(S5,LI,19);Insert into SValues(S5,LI,19,M,NULL);93视图示例Create View Grade-AVG(Sname,AVG_Grade)asSelect Sname,AVG(Grade)From SC,SWhere SC.SNO=S.SNOGroup by Sname;Create View Grade-AVGasSelect Sname,AVG(Grade)as AVG_Grade From SC,SWhere SC.SNO=S.SNOGroup by Sname;94视图优点提供逻辑数据独立性应该是映像的作用简化用户观点数据安全保护