《数据库基础教程5第五章数据库语言SQL.ppt》由会员分享，可在线阅读，更多相关《数据库基础教程5第五章数据库语言SQL.ppt（97页珍藏版）》请在三一办公上搜索。
1、第五章 数据库语言SQL,2,概述,SQL的发展1974年，由Boyce和Chamberlin提出19751979，IBM San Jose Research Lab的关系数据库管理系统原型System R实施了这种语言SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)、SQL-99(SQL3),3,概述,现状大部分DBMS产品都支持SQL，成为操作数据库的标准语言有方言，支持程度不同,4,数据定义（DDL）定义、删除、修改关系模式（基本表）定义、删除视图（View）定义、删除索引（Index）数据操纵（DML）数据查询数据增、删、改数据控制（DCL）用户访问权限的授予、收回
2、,概述SQL的功能,5,交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释,概述SQL的形式,6,嵌入式SQL能将SQL语句嵌入到高级语言（宿主语言）使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句,概述SQL的形式,7,数据定义Create、Drop、Alter数据操纵数据查询：Select数据修改：Insert、Update、Delete数据控制Grant、Revoke,概述SQL的动词,8,数据查询是数据库应用的核心功能基本结构Select A1,A2,.
3、,AnFrom r1,r2,.,rmWhere PA1,A2,.,An(p(r1r1.rm),数据查询,9,Select语句的含义,对 From 子句中的各关系，作笛卡儿积（）对 Where 子句中的逻辑表达式进行选择（）运算，找出符合条件的元组根据 Select 子句中的属性列表，对上述结果作投影（）操作,10,Select语句的含义,结果集查询操作的对象是关系，结果还是一个关系，是一个结果集，是一个动态数据集,11,Select子句,对应于关系代数的投影（）运算，用以列出查询结果集中的期望属性,12,SQL具有包的特性Select 子句的缺省情况是保留重复元组（ALL），可用 Distin
4、ct 去除重复元组Select Distinct sdept From StudentSelect All sdept From Student去除重复元组:费时需要临时表的支持,Select子句重复元组,13,Select子句*与属性列表,星号*表示所有属性星号*：按关系模式中属性的顺序排列，并具有一定的逻辑数据独立性显式列出属性名：按用户顺序排列Select*From StudentSelect Student.*，cno，gradeFrom Student，SCWhere Student.sno=SC.sno,14,Select子句更名,为结果集中的某个属性改名使结果集更具可读性Sele
5、ct sno as stu_no，cno as course_no，gradeFrom SCSelect sno，sname，2001-sage as birthdayFrom Student,15,Where 子句,where子句对应与关系代数中的选择()查询满足指定条件的元组可以通过Where子句来实现使where子句中的逻辑表达式返回True值的元组，是符合要求的元组，将被选择出来,16,Where 子句运算符,比较：、=、=、not+确定范围：Between A and B、Not Between A and B确定集合：IN、NOT IN字符匹配：LIKE，NOT LIKE空值：IS
6、 NULL、IS NOT NULL多重条件：AND、OR、NOT,17,Where 子句Like,字符匹配：Like、Not Like通配符%匹配任意字符串_ 匹配任意一个字符大小写敏感,18,Where 子句Like,例：列出姓张的学生的学号、姓名。Select sno，snameFrom StudentWhere sname LIKE 张%,19,Where 子句Like,例：列出张姓且单名的学生的学号、姓名。Select sno，snameFrom StudentWhere sname LIKE 张_ _,20,Where 子句转义符 escape,例：列出课程名称中带有_的课号及课名。
7、Select cno，cnameFrom CourseWhere cname LIKE%_%escape,21,From 子句,From子句对应与关系代数中的笛卡儿积（）列出将被扫描的关系（表）例：列出所有学生的学号、姓名、课号、成绩。Select Sudent.sno，sname，SC.cno，gradeFrom Student，SCWhere Student.sno=SC.sno,22,From 子句元组变量,为 From 子句中的关系定义元组变量方便关系名的引用在同一关系的笛卡儿积中进行辨别例：列出与95001同岁的同学的学号,姓名,年龄SelectT.sno,T.sname,T.sag
8、eFromStudent as T,Student as SWhereS.sno=95001 ANDT.sage=S.sage,23,Order By子句,指定结果集中元组的排列次序耗时ASC（缺省）、DESC、未选中的属性例：列出CS系中的男生的学号、姓名、年龄，并按年龄进行排列（升序）Select sno,sname,sageFrom StudentWhere sdept=CSOrder By sage ASC,24,SQL的集合操作,SQL的结果集是“包”多个 Select 语句的结果可以进行集合操作，使结果为“集合”（default）SQL-92支持参加集合操作的关系（结果集）必须是相
9、容的,25,SQL的集合操作相容,属性个数必须一致对应的类型必须一致属性名无关最终结果集采用第一个结果的属性名缺省为自动去除重复元组除非显式说明ALLOrder By放在整个语句的最后,26,SQL的集合操作并,union（并，对应与关系代数的），标准SQL都支持的采用集合的观点，合成多个查询的结果select-without-order by.UNION ALL select-without-order by.UNION ALL select-without-order by.ORDER BY integer ASC|DESC,.,27,例：查询计算机系的学生或者年龄不大于19岁的学生，并按
10、年龄倒排序。,SQL的集合操作并,28,intersect（交，对应与关系代数的），并不是所有的DBMS都支持例：查询计算机系的学生并且年龄不大于19岁的学生，并按年龄倒排序。,SQL的集合操作交,29,except（差，对应与关系代数的-），并不是所有的DBMS都支持例：查询计算机系的男生。,SQL的集合操作差,30,数据定义语言（Data Definition Language）Create、Drop、Alter定义一组关系（基本表）、说明各关系的信息各关系的模式各属性的值域完整性约束索引安全性和权限,数据定义语言（DDL）,31,字符型char(n)、varchar(n)数值型integ
11、er、smallintnumeric(p,d)real、double、float(n)日期/时间型datetime,DDLSQL中的域类型,32,域定义语句（SQL-92支持）需重复使用的Create Domain stu_name varchar(20)Create Domain zip_code char(6),DDLSQL中的域类型,33,Create Table r(A1D1，A2D2，AnDn，）其中：r 关系名（表名）、Ai 关系 r 的一个属性名Dn 属性Ai域值的域类型主键声明：primary key(Aj1，Aj2，Ajvm),DDLSQL的模式定义,34,Create Do
12、main stu_name varchar(20)Create Table Student(sno char(10)primary key(sno),sname stu_name,sage smallint,ssex char(1),sdept char(2),DDLSQL的模式定义,35,Create Table Course(cno char(10)primary key(cno),cname varchar(20),credit smallint)Create Table SC(sno char(10)not null,cno char(10)not null,grade smallin
13、t,primary key(sno,cno),DDLSQL的模式定义,36,用SQL删除关系（表）将整个关系模式（表结构）彻底删除表中的数据也将被删除Drop Table rDrop Table student;,DDL删除表结构 Drop,37,删除表中的某属性去除属性及相应的数据Alter Table r Drop A,DDL修改模式 Alter,38,增加表中的属性向已经存在的表中添加属性allow null已有的元组中该属性的值被置为NullAlter Table r Add A DAlter Table student phone char(16);,DDL修改模式 Alter,39
14、,属性的默认值用户不提供某属性的值时，默认值被使用初始值TimeStampDDL中：ModifyDate char(30)Default TimeStamp;Alter Table student Add ID integer Default AutoIncrement;,DDL Default Value,40,定义一个新的域（用户定义的域）需要重复使用的域必须具有相同类型的属性Create Domain As；Create Domain datelog As char(30)default timestamp域的删除Drop Domain datelog各DBMS的方法是不同的Restri
15、ct/Cascade使用该域的属性的处理,DDL 域定义,41,索引是一种数据结构，是对照表、指针表索引是为了加速对表中元组的检索而创建的一种分散存储结构（B树）索引是对表而建立的，由除存放表的数据页面以外的索引页面组成索引是把双刃剑，减慢更新的速度索引不是SQL标准的要求,DDL 索引 Index,42,索引的种类聚簇索引（Clustered Index）非聚簇索引（Non-Clustered Index）,DDL 索引 Index,43,聚簇索引（Clustered Index）表中的元组按聚簇索引的顺序物理地存放根级页面-中间层页面-叶级页面（数据页面）一个表中只能有一个聚簇索引更新的复
16、杂性，需要大量的临时空间,DDL 索引 Index,44,非聚簇索引（Non-Clustered Index）表中元组存储的物理顺序与索引的顺序无关叶级索引页面是指向数据页面的指针每个表可有多个非聚簇索引,DDL 索引 Index,45,CREATE UNIQUE CLUSTERED|NONCLUSTEREDINDEX index-name On TableName(Column,Column,)Create Index YearIndex On Movie(year);Create Clustered Index SnoIndex On student(sno);Drop Index Yea
17、rIndex;,DDL 索引 Index,46,查询与更新的评估查询多？更新多？索引的覆盖Where表达式Where表达式的顺序索引越多越好吗？了解优化器,DDL 索引的使用,47,数据添加用SQL的插入语句，向数据库表中添加数据按关系模式的属性顺序Insert Into Student Values(95001,张三,27,M,CS)按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）Insert Into Student(sno,sname,sage)Values(95002,李四,26),48,把一列中的值进行聚合运算，返回单值的函数五个预定义的聚合函数平均值：Avg总和：Su
18、m最小值：Min最大值：Max计数：CountCount(*)、Count(Distinct),聚合函数,49,Group By将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现只有出现在Group By子句中的属性，才可出现在Select子句中例：统计各系学生的人数。Select sdept，count(*)as stu_countFrom StudentGroup By sdept,聚合函数,50,Having针对聚合函数的结果值进行筛选（选择），它作用于分组计算结果集跟在Group By子句的后面，没有Group By则针对全表例：列出具有两门（含）以上不及
19、格的学生的学号、不及格的课目数。Select sno，count(sno)From SCWhere grade=2,聚合函数,51,Having 与 Where的区别Where 决定哪些元组被选择参加运算，作用于关系中的元组Having 决定哪些分组符合要求，作用于分组聚合函数的条件关系必须用Having，Where中不应出现聚合函数,聚合函数,52,聚合函数忽略NullCount：不计Sum：不将其计入Avg：具有 Null 的元组不参与Max/Min：不参与例：Select count(sdept)From StudentSelect Avg(sage)From Student,聚合函数
20、Null,53,子查询是嵌套在另一查询中的 Select-From-Where 表达式（Where/Having）SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件可以用多个简单查询来构成复杂查询，以增强SQL的查询能力子查询中不使用 Order By 子句，Order By子句只能对最终查询结果进行排序,子查询（Subquery）,54,返回单值的子查询，只返回一行一列父查询与单值子查询之间用比较运算符进行连接运算符：、=、=、例：找出与95001同龄的学生Select*From StudentWhere sage=(Select sage From Student W
21、here sno=95001),子查询单值比较,55,子查询返回多行一列运算符：In、All、Some(Any)、Exists,子查询多值,56,标量值与子查询返回集中的某一个相等，true IN 被用来测试多值中的成员例：查询选修C01课程的学生的学号、姓名。Select sno,snameFrom StudentWhere sno IN(Select sno From SCWhere cno=C01),子查询多值成员In,57,例：查询选修了 数据库的学生的学号和姓名Select sno,snameFrom StudentWhere sno IN(Select sno From SC Wh
22、ere cno IN(Select cnoFrom CourseWhere cname=数据库),子查询多值成员In,58,多值比较：多行一列父查询与多值子查询之间的比较需用All来连接标量值s比子查询返回集R中的每个都大时，sAll R 为True All表示所有 all、=all、all all 等价于 not in例：找出年龄最小的学生Select*From Student Where sage all(Select sage From Student),子查询多值比较 ALL,59,多值比较：多行一列父查询与多值子查询之间的比较需用Some/Any来连接标量值s比子查询返回集R中的某一
23、个都大时s Some R为True 或 s Any R为True Some(早期用Any)表示某一个,只要有一个即返回真 some、=some、some=some 等价于 in、some 不等价于 not in,子查询多值比较Some/Any,60,例：找出不是最小年龄的学生Select*From studentWhere sage some(Select sage From Student),子查询多值比较,61,例：找出具有最高平均成绩的学号及平均成绩Select sno,avg(grade)From SC Group By sno Having avg(grade)=all(Select
24、 avg(grade)From SC Group By sno),子查询多值比较,62,Exists+子查询用来判断该子查询是否返回元组当子查询的结果集非空时，Exists为True当子查询的结果集为空时，Exists为False不关心子查询的具体内容，因此用 Select*,子查询存在判断Exists,63,具有外部引用的子查询，称为相关子查询（Correlated Queries）外层元组的属性作为内层子查询的条件,子查询相关子查询,64,例：列出选修了C01课程的学生的学号、姓名Select sno,snameFrom StudentWhere Exists(Select*From SC
25、 Where SC.sno=Student.sno And cno=C01),子查询Correlated&Exists,65,例：列出得过100分的学生的学号、姓名Select sno,snameFrom StudentWhere Exists(Select*From SCWhere SC.sno=Student.sno Andgrade=100),子查询Correlated&Exists,66,例：查询选修了C01课程的学生的系主任Select managerFrom departmentWhere Exists(Select*From studentWhere sdept=departme
26、nt.depid And Exists(Select*From SCWhere SC.sno=student.sno Andcno=C01),子查询Correlated&Exists,67,例：列出没有选C01课程的学生的学号、姓名Select sno,snameFrom StudentWhere Not Exists(Select*From SCWhere SC.sno=Student.sno Andcno=C01),子查询Correlated&Not Exists,68,例：查询选修了所有课程的学生的姓名(ForAll)Select snameFrom StudentWhere Not E
27、xists(Select*From CourseWhere Not Exists(Select*From SCWhere Student.sno=SC.sno AndSC.cno=Co),子查询Correlated&Not Exists,69,SQL-92支持多列的成员资格测试（ASA7.0不支持）例：找出同系、同年龄、同性别的学生Select*from Student as TWhere（T.sdept,T.sage,T.ssex)IN(Select sdept,sage,ssexFrom student as SWhere S.sno T.sno),子查询多列元组的比较,70,SQL-92
28、允许在 From中使用查询表达式必须为其取名例：查询平均成绩大于75分的学号、姓名、平均成绩 Select stu_no,sname,avg_gradeFrom Student,(Select sno,avg(grade)From SCGroup By sno)as S(stu_no,avg_grade)Where Student.sno=S.stu_no Andavg_grade 75,派生关系,71,NULL表示数据的缺失一个确实存在，但我们不知道的值对本实体此数值无意义，可能是设计上的失误是SQL的关键字，用于任何类型描述缺失的值UNKONW是三值逻辑的一个真值(True/False/U
29、nkonw),空值和连接,72,NULL的运算法则对NULL值和其他任何值作算术运算时，结果为NULL对NULL值和其他任何值作比较时，结果为UNKOWNIs Null/Is Not Null,空值和连接,73,Unkown视True为1，False为0，Unkown为1/2AND：取小OR：取大NOT：取1的补真值表不必死记硬背,空值和连接,74,关系的连接连接操作是以两个关系为输入、将另一个关系作为结果返回笛卡儿积、条件连接、自然连接、外连接自然连接（Natural）：连接条件是两个关系中具有公共名字的所有属性对均相等，不需要其他条件对每个相等属性对之一进行投影（去除重复属性）Studen
30、t Natural Join SCStudent SC,75,关系的连接内连接（Inner Join）：它是条件连接，要求参加连接的关系中至少有一对元组满足连接要求，才能生成连接关系可以是等值连接，也可以是不等值连接新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性Inner是缺省的连接方式也可以用笛卡儿积+选择的方法实现Student Inner Join SC on Student.sno=SC.sno,76,关系的连接外连接（Outer Join）：它是条件连接，且条件是必需的。若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他
31、属性位置上放上Null，而不是像Inner 那样被忽略。新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性有左外连接、右外连接、全外连接,77,关系的连接左外连接（Left Outer Join）：Select*From(Student Left Outer Join SCon Student.sno=SC.sno)右外连接（Right Outer Join）：Select*From(Student Right Outer Join SCon Student.sno=SC.sno)全外连接（Full Outer Join）：Select*From(Student Full Outer
32、 Join SCon Student.sno=SC.sno),78,数据库的修改,数据库修改包括插入数据、修改数据、删除数据,数据删除（Delete）只能对整个元组操作，不能只删除某些属性上的值只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令Delete From r Where P从关系 r 中删除满足P的元组，只是删除数据，而不是定义,79,删除单个元组例：删除学号为95001的学生的选课信息Delete From SCWhere sno=95001删除多个元组例：删除选课而未参加考试的学生的选课信息Delete From SCWhere grade is
33、null删除整个关系中的所有数据例：删除所有学生的选课信息Delete From SC,80,数据插入（Insert）单行插入：一次只插入一个元组例：新增一个学生信息Insert Into StudentValues(98001,Gloria,25,F,CS)多行插入：插入一个集合例:给CS系的学生开设必修课C05，建立选课信息Insert Into SCSelect sno,cno,nullFrom Student,CourseWhere sdept=CS andcno=C05,81,数据更新（Update）改变符合条件的某个（某些）元组的属性值例：将95001学生转入MA系Update S
34、tudentSet sdept=MAWhere sno=95001例：所有学生年龄加1Update StudentSet sage=sage+1,82,数据更新（Update）例：将选修C05课程的学生的成绩改为该课的平均成绩Update SCSet grade=(Select avg(grade)From SCWhere cno=C05)Where cno=C05,先计算avg，再做Update,83,视图（View）是从一个或几个基本表（或视图）中导出的虚表数据库中只保存它的定义是RDBMS提供给用户以多种角度观察数据库中数据的重要机制创建视图、删除Create View v as Dro
35、p View v例：计算机系的花名册Create View CS_Stu asSelect sno,sname,ssexFrom StudentWhere sdept=CS,84,视图（View）视图名可以出现在任何关系名可以出现的地方例：列出计算机系的男生Select sno,snameFrom CS_StuWhere ssex=M 例：建立学生平均成绩视图Create View avg_grade(sno,avg)asSelect sno,avg(grade)From SCGroup By sno,85,视图（View）例：找出平均成绩大于等75的学生Select*From avg_gra
36、deWhere avg=75注意：此例的使用方法是非标准的。,86,视图（View）视图的更新：单表、原始属性构成的视图可以更新（像基本表那样更新）由多表构成的视图由很大的限制视图中的非原始属性也不能更新例：Update avg_gradeSet avg=100Where sno=95001,87,视图（View）视图的作用简化用户的操作不同的用户可从不同的角度看待同一数据支持一定的逻辑数据独立性数据的安全性,88,数据定义语言（Data Definition Language）Create、Drop、Alter定义一组关系（基本表）、说明各关系的信息各关系的模式各属性的值域完整性约束索引安全
37、性和权限各关系在磁盘上的物理存储结构,数据定义语言（DDL）,89,SQL中的域类型字符型char(n)varchar(n)数值型intsmallintnumeric(p,d)real、doublefloat(n)日期/时间型datetime,90,域定义语句（SQL-92支持）需重复使用的Create Domain stu_name varchar(20)Create Domain zip_code char(6),91,SQL的模式定义Create Table r(A1D1，A2D2，AnDn，）其中：r 关系名（表名）Ai 关系 r 的一个属性名 Dn 属性Ai域值的域类型主键声明：pr
38、imary key(Aj1，Aj2，Ajvm)校验声明：check(逻辑表达式),92,ExamplesCreate Domain stu_name varchar(20)Create Table Student(sno char(10)not null,sname stu_name,sage smallint,ssex char(1),sdept char(2),primary key(sno),check(ssex in(M,F),93,Create Table Course(cno char(10)not null,cname varchar(20),credit smallint,pr
39、imary key(cno)Create Table SC(sno char(10)not null,cno char(10)not null,grade smallint,primary key(sno,cno),check(grade=0 and grade=100),94,用SQL删除关系（表）将整个关系模式（表结构）彻底删除Drop Table r用SQL删除表中的某属性去除属性及相应的数据Alter Table r Drop A用SQL增加表中的属性向已经存在的表中添加属性已有的元组中该属性的值被置为NullAlter Table r Add A D,95,数据添加用SQL的插入语句
40、，向数据库表中添加数据按关系模式的属性顺序Insert Into Student Values(95001,张三,27,M,CS)按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）Insert Into Student(sno,sname,sage)Values(95002,李四,26),96,NullNull是unknown与Null的比较都返回unknown(unknown or unknown)unknown(true and unknown)unknown(false and unknown)=false(unknown and unknown)=unknown,97,练习,针对学生-课程体系，作SQL查询列出各系的学生数（人数多的排在前面）找出各科成绩均在85分以上（含）的学生的学号、姓名找出有三门课程的成绩在75分以下的学生的学号、姓名列出数据库成绩的前五名学生的学号、姓名、成绩,
