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

关系数据库基本原理（2）SQL语言,王传栋南京邮电大学计算机学院,2,内容与要求,知识点（1）知识点一：关系模型概述（2）知识点二：关系数据结构（3）知识点三：关系代数理论（4）知识点四：关系数据库标准语言SQL（5）知识点五：关系数据库的规范化理论实验1SQL语言的应用教学基本要求了解关系数据结构的基本概念，了解关系模型的各种操作和关系代数的基本原理，掌握关系数据模型的完整性约束机制，掌握SQL语言，了解函数依赖等基本概念，掌握关系模式的规范化概念、方法、原理与过程。,3,引言,数据库的应用平台,嵌入式SQL,交互式SQL,4,引言,SQL是关系数据库的标准语言对关系模型的发展和商用DBMS的研制起着重要的作用SQL语言是介乎于关系代数和元组演算之间的一种语言SQL语言的9个核心词汇Create,Alter,Drop,Select,Insert,Update,DeleteGrant,Revoke本章详细介绍SQL的核心部分内容数据定义数据查询数据更新嵌入式SQL,5,SQL简介,SQL发展史1970年美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型1972年IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE语言1974年把SQUARE修改为SEQUEL语言1978年，SEQUEL简称为SQL，即“结构式查询语言”SQL的发音仍为“sequel”，现在SQL已经成为一个标准，其发音更倾向于发“ess-cue-ell”,6,SQL简介,SQL发展史SQL861986年10月，ANSISQL标准1987年6月，ISOSQL标准SQL21992年8月，ISOSQL标准SQL31999年，ISOSQL标准在未来很长一段时间，SQL仍将是关系数据库领域的主流语言在软件工程、人工智能领域，SQL已显示出相当大的潜力,7,SQL简介,SQL数据库的体系结构,8,SQL简介,SQL数据库的体系结构1）一个SQL模式（Schema）是表和约束的集合2）一个表由行集构成，一行是列的序列3）表类型：基本表、视图和导出表4）基本表与存储文件是M:N联系存储文件与外部存储器的物理文件是一一对应的5）SQL语句执行对基本表和视图查询等操作在用户看来，两者是一样的，都是表6）SQL用户可以是应用程序，也可以是终端用户注：虽然SQL是国际公认的关系数据库标准，但标准的SQL文档中并没有使用“关系”和“数据库”这两个名词,9,SQL简介,SQL组成1）数据定义语言，即SQLDDL用于定义SQL模式、基本表、视图、索引等结构2）数据操纵语言，即SQLDML数据查询数据更新：包括插入、删除和修改三种操作3）嵌入式SQL语言规则SQL语句嵌入在宿主语言程序中的规则4）数据控制语言，即SQLDCL授权、完整性规则的描述、事务控制等,10,SQL简介,SQL特点1）灵活和强大的查询功能SELECT语句能完成相当复杂的查询操作2）SQL不是一个应用开发语言只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能3）SQL是国际标准语言有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化4）SQL的词汇不多完成核心功能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用,11,SQL的数据定义,SQL模式的创建和撤销注模式（Schema）一词，来自于“ISOSQL标准”协议Schema在SQL数据库中相当于一个容器数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内创建SQL模式，就是定义一个存储空间在商业DBMS中，大多都使用Database代替Schema,12,SQL的数据定义,SQL模式的创建和撤销创建CREATESCHEMAAUTHORIZATION撤销DROPSCHEMACASCADERESTRICT方式CASCADE（级联式）RESTRICT（约束式）商业DBMS中CREATEDatabaseDROPDatabase,13,SQL的数据定义,常用数据类型1）数值型：Integer，Smallint，Numeric(p,d)/DEC(p,d)2）字符串型：Char(n)，Varchar(n)3）位串型：Bit(n)，BitVarying(n)4）时间型：DATE，TIME注在很多DBMS中支持自定义数据类型，如在Oracle中CREATEDOMAINAS列级完整性约束;DROPDOMAIN;,14,SQL的数据定义,常用数据类型示例：在Oracle中定义数据类型与应用定义CreatedomainCOLORChar(6)Default?ConstraintVALID_COLORSCheck(valuein(Red,Yellow,Green,?);使用CreateTablePART(partcolorCOLOR,);,15,SQL的数据定义,基本表的创建、修改和撤销表的创建句法CREATETABLE(域完整性约束条件,域完整性约束条件,);域完整性约束条件NOTNULL/NULL：列值是否可以为空UNIQUE：列值唯一，不得重复DEFAULT：列值空缺时，由系统填写默认值,16,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件主键（PRIMARYKEY）子句格式：PRIMARYKEY()作用：提供实体完整性约束的说明说明系统一般自动在主键上建索引主键为单属性时，可直接在属性后的域完整性约束条件中，使用PRIMARYKEY定义主键,17,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGNKEY）子句格式：Foreignkey外键名()references(列名表2)ondelete作用：提供参照完整性约束的说明,18,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGNKEY）子句ondelete子句的说明RESTRICT：主表的主键值行不得删除CASCADE：主表主键值行删除，从表的相关行随之删除SETNULL：主表主键值行删除，从表的相关行的列值设为NULL，前提是该列的值可以为NULL,19,SQL的数据定义,基本表的创建、修改和撤销表的创建表级完整性约束条件检查（CHECK）子句格式：CHECK(约束表达式)作用：对某元组某属性取值的约束说明说明表创建后是一个空表，需要使用DML（insert，update，delete）语句装入或维护数据行,20,SQL的数据定义,基本表的创建、修改和撤销表的创建示例,21,SQL的数据定义,基本表的创建、修改和撤销表的创建示例,22,SQL的数据定义,基本表的创建、修改和撤销表的创建示例,23,SQL的数据定义,基本表的创建、修改和撤销表的创建问关于表T、S、C、SC的创建顺序，下列选项正确的是？A）CTSCSB）TSCSCC）STCSCD）STSCCE）TCSSCF）SCCSTG）SCCTS,24,SQL的数据定义,基本表的创建、修改和撤销表的修改句法ALTERTABLE增加属性Altertableadd；删除属性AltertabledropcascaderestrictCASCADE：引用该列的视图和约束随之删除RESTRICT：只在没有被引用时才可删除不是所有的DBMS都支持,25,SQL的数据定义,基本表的创建、修改和撤销表的修改句法ALTERTABLE修改属性的类型Altertablemodifymodifyaltercolumn其他修改，如补充定义主键，撤销主键定义补充定义外键，撤销外键定义定义和撤销别名,26,SQL的数据定义,基本表的创建、修改和撤销表的撤销句法DROPTABLEcascaderestrict说明CASCADE：删除该基本表时，所有引用该基本表的视图和约束一起自动被删除RESTRICT：没有视图和约束引用该基本表时，才能撤销,27,SQL的数据定义,索引的建立和撤销索引概念是物理存取路径，不属于逻辑数据模式RDBMS通常在主键上自动建立索引查询、更新时自动起作用（适当建立索引会提高查询速度）句法创建索引CREATEUNIQUEINDEXON基表名(ASCDESC,ASCDESC);撤销索引DROPINDEX;,UNIQUE索引，只能建立在候选键上,28,SQL的数据定义,索引的建立和撤销示例CreateindexAge_IndexonS(Age);CreateuniqueindexAge_IndexonS(AgeASC);CreateuniqueindexSC_IndexonSC(SNO,CNODESC);DropindexAge_Index,SC_Index;CreateindexC_IndexonC(TNODESC,CreditDESC);,29,SQL的数据更新,数据插入句法1）单元组的插入INSERTINTO基本表名(列名表)VALUES(元组值)2）多元组的插入：不是所有版本的DBMS都支持INSERTINTO基本表名(列名表)VALUES(元组值),(元组值)3）查询结果的插入INSERTINTO基本表名(列名表)4）表的插入：INSERTINTO基本表名1(列名表)TABLE基本表名2,30,SQL的数据更新,数据插入说明若插入的元组值，其属性个数、次序和域，与表结构的定义一致，则列名表可以省略否则需要列名表，且列名表的属性个数和次序，需与待插入值一一对应数据插入受关系完整性约束的制约数据类型实体完整性：主键值唯一，非空参照完整性：不允许引用不存在的实体用户自定义的完整性,31,SQL的数据更新,数据插入示例InsertIntoS(Sno,Sname,Age,Sex,NativePlace)Values(S5,GU,20,M,NULL);InsertIntoSValues(S5,GU,20,M,NULL);InsertIntoS(Sno,Sname,Age,Sex)Values(S5,GU,20,M);InsertIntoS(Sname,Sno,Sex,Age)Values(GU,S5,M,20);InsertIntoSC(S#,C#)Values(S5,C8);InsertIntoSCValues(S5,C8,NULL);,32,数据插入示例InsertIntoS_Grade(S#,AVG_Grade)SelectSno,AVG(Grade)FromSCWhereSnoin(SelectSnoFromSWhereSexM)GroupBySnoHavingAVG(Grade)80;InsertIntoSC(Sno,Cno)TABLESC4;,SQL的数据更新,33,SQL的数据更新,数据插入示例，问：InsertIntoSCValues(S4,C569,105);InsertIntoSCValues(S4,C5,105);,34,SQL的数据更新,数据插入示例，问：InsertIntoSCValues(S4,C5,95);InsertIntoSCValues(S4,C4,95);如图示4条相关数据的插入顺序是什么？,35,SQL的数据更新,数据删除句法DELETEFROMWHERE条件表达式语义是从基本表中删除满足条件表达式的元组注意DELETE语句只能从一个基本表中删除元组WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件如果没有WHERE子句，则删除表的所有元组，表成为空表,36,SQL的数据更新,数据删除示例DeleteFromSCWhereGradeisNULL;DeleteFromCWhereCnameLike%M%;DeleteFromSCWhereCnoin(SelectCnoFromCWhereCname=MATHS);DeleteFromSC,CWhereSC.Cno=C.CnoandCname=MATHS;DeleteFromSCWhereCno=C4andGrade=3OrderBy3DESC,Tno,C.CnoDESC,63,SQL的数据查询,SELECT语句的完整结构GROUPBYHAVING作用1）数据按GROUPBY子句列名序列中的列值进行分组2）组内数据按SELECT子句中的聚合函数进行计算3）提取满足HAVING子句的条件表达式值的分组注意HAVING子句支持聚合函数WHERE子句不支持聚合函数SELECT子句只能取聚集函数或GROUPBY子句指的列,64,SQL的数据查询,SELECT语句的完整结构ORDERBYASCDESC,ASCDESC对查询结果按子句中指定列的值排序，如果ORDERBY后有多个列名先按第一列名值排序再对于第一列值相同的行，按第二列名值排序依次类推列序号是在SELECT子句中出现的序号（选的列是聚集函数或表达式时）ASC表示升序，DESC表示降序，缺省时表示升序,65,SQL的数据查询,SELECT语句的完整结构应用示例问：在使用聚合函数进行数据统计时，为什么有的语句使用了GroupBy子句，有的没有？什么情况下，应该有？可以没有？,66,SQL的数据查询,查询中的限制和规定Select子句的规定子句描述查询输出的表格结构，即输出值的列名或表达式格式SELECTALLDISTINCT*释义AllDistinct，保留消除查询结果中的重复行*是对From子句中表的所有列的简写列表达式列名、常量、算数运算符、函数、聚合函数,67,SQL的数据查询,查询中的限制和规定别名用关键字AS为列、表、表达式起别名，AS可以省略集合运算(Select查询1)UNIONALL(Select查询2)(Select查询1)INTERSECTALL(Select查询2)(Select查询1)EXCEPTALL(Select查询2)备注并交差运算的前提：相同的关系模式关键字ALL，表示保留运算后的重复元组,68,SQL的数据查询,查询中的限制和规定示例SelectNativePlace,Sname,SexFromS;SelectSno,Sname,Age,Sex,NativePlaceFromS;Select*FromS;,69,SQL的数据查询,查询中的限制和规定示例SelectSname,Year(getdate()AgeasBirthYearFromSASX;SelectSnameas姓名,BirthYear:asBIRTH,2011AgeasYEAY,Lower(Sex)as性别FromS;说明函数getdate(),year(),month(),day()函数upper(),lower(),70,SQL的数据查询,查询中的限制和规定示例Select*FromSWhereSex=FOrNativePlace=四川(Select*FromSWhereSex=F)UNION(Select*FromSWhereNativePlace=四川)(Select*FromSWhereSex=F)UNIONALL(Select*FromSWhereNativePlace=四川),71,SQL的数据查询,查询中的限制和规定示例SelectSnoFromSC;SelectAllSnoFromSC;SelectDistinctSnoFromSC;Selecttop5SnoFromSC;,72,SQL的数据查询,条件表达式中的比较操作构成Where语句的条件运算符,73,SQL的数据查询,条件表达式中的比较操作算数比较运算查询学号是S3的学生姓名SelectSnameFromSWhereSno=S3;查询年龄小于18的学生姓名SelectSnameFromSWhereAge=17andAge=17andAge19;,75,SQL的数据查询,条件表达式中的比较操作字符匹配运算查询籍贯不是上海的学生姓名SelectSnameFromSWhereNativePlace=上海;SelectSnameFromSWhereNativePlaceLike%上海%;SelectSnameFromSWhereNativePlaceLike%上%海%;SelectSnameFromSWhereNativePlace上海;SelectSnameFromSWhereNativePlaceNotLike%上海%;,76,SQL的数据查询,条件表达式中的比较操作字符匹配运算注解LIKE与通配符“%”和“_”使用格式：notLikeEscape“%”：字符串中所处位置的0n个字符“_”：字符串中所处位置的1个字符换码字符查询课程名以“DB_C%A”开头的课程号和学分SelectCno,CreditFromCWhereCnamelikeDB_C%A%Escape;,77,SQL的数据查询,条件表达式中的比较操作空值比较运算查询未缺考学生的学号和课程号SelectSno,CnoFromSCWhereGradeisNull;SelectSno,CnoFromSCWhereGradeisnotNull;说明，在Where语句中“isNULL”不能用“=NULL”替换“isnotNULL”不能用“NULL”替换,78,SQL的数据查询,条件表达式中的比较操作空值比较运算查询未缺考过的学生的学号SelectDistinctSnoFromSCWhereGradeisNull;SelectDistinctSnoFromSCWhereGradeisnotNull;SelectSnoFromSWhereSnonotin(SelectSnoFromSCWhereGradeisNull);,79,SQL的数据查询,条件表达式中的比较操作集合成员运算查询未选修C2或C3课程的学生学号SelectSnoFromSCWhereCno=C2orCno=C3;SelectSnoFromSCWhereCno=(C2,C3);SelectSnoFromSCWhereCnoin(C2,C3);SelectSnoFromSCWhereCno=SOME(C2,C3);SelectSnoFromSCWhereCnonotin(C2,C3);SelectSnoFromSCWhereCnoALL(C2,C3);,80,SQL的数据查询,条件表达式中的比较操作集合成员运算查询未选修C2或C3课程的学生学号SelectSnoFromSWhereSnonotin(SelectSnofromSCWhereCnoin(C2,C3);SelectSnoFromSWhereSnoALL(SelectSnofromSCWhereCnoin(C2,C3);,81,SQL的数据查询,条件表达式中的比较操作集合成员运算查询未选修LIU老师所授课程的学生学号SelectSnoFromSC,C,TWhereTname=LIUandSC.Cno=C.CnoandC.TNO=T.Tno;SelectSnoFromSCWhereCnoin(SelectCnoFromCWhereTnoin(SelectTnoFromTWhereTname=LIU);SelectSnoFromSWhereSnonotin(选过的学号);,82,SQL的数据查询,条件表达式中的比较操作集合成员运算查询选修了LIU老师所授全部课程的学生姓名SelectSnameFromSWherenotExists(Select*FromCWhereTnoin(SelectTnoFromTWhereTname=LIU)andnotExists(Select*FromSCWhereSno=S.SnoandCno=C.Cno);,83,SQL的数据查询,条件表达式中的比较操作集合成员运算查询至少有一门成绩超过S4一门成绩的学生学号SelectDistinctSnoFromSCWhereGradeSome(SelectGradeFromSCWhereSno=S4);,84,SQL的数据查询,条件表达式中的比较操作集合成员运算查询平均成绩最高的学生学号使用导出表SelectS#From(SelectSno,AVG(Grade)FromSCGroupBySno)ASTmpTable(S#,AVG_Grade)WhereAVG_Grade=ALL(SelectAVG(Grade)FromSCGroupBySno);,85,SQL的数据查询,条件表达式中的比较操作集合空否的测试SelectFromRWhereNOTEXISTS(Select*FromSWhereS.A=R.Aand);说明当内层集合非空，返回True，否则返回False,86,SQL的数据查询,条件表达式中的比较操作集合中是否有重复元组的判定查询只选修了一门课的学生的学号和姓名SelectSno,SnameFromSWhereunique(SelectSnoFromSCWhereSno=S.Sno);SelectS.Sno,SnameFromS,SCWhereS.Sno=SC.SnoGroupbyS.Sno,SnameHavingCount(Cno)=1;,87,SQL的数据查询,嵌套查询的改进写法导出表在From子句中使用子查询，子查询必须起别名命名的导出表只在From中起作用，离开From