《SQL语言简介》PPT课件.ppt

第三章结构化查询语言SQL,SQL的特点1.综合统一SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、插入数据建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统的开发提供了良好的环境。用户在数据库投入运行后，还可以根据需要随时地逐步修改模式，且并不影响数据库的运行，从而使系统具有良好的可扩展性2.高度非过程化非关系数据模型的数据操作语言是面向过程的语言，用其完成某项请求时，必须指定存取路径。而用SQL语言进行数据操作时，只要提出“做什么”，而无须指明“怎样做”，因此无须了解存取路径。存取路径的选择以及SQL语言的操作是由系统自动完成的。,3.面向集合的操作方式非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。而SQL语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。4.以同一种语法结构提供两种使用方式SQL语言是自含式语言，又是嵌入式语言。作为自含式语言，他能独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作；作为嵌入式语言。SQL语言能够嵌入到高级语言，供程序原设计程序时使用。5.语言简捷，易学易用SQL语言接近英语口语。,数据查询：按照给定的条件查询一个或多个关系中的某些或全部属性。数据定义：对基本表、视图（View）、索引（Index）数据操纵：对关系中的具体数据进行增加、删除和更新等操作；数据控制：对数据访问权限的授予与撤消。,3.1SQL查询也称为SELECT命令,基本形式是SELECT-FROM-WHERESQL基本查询模块的结构式:SELECTALL|DISTINCT,.FROM,WHEREGROUPBYHavingORDERBYASC|DESC；含义：根据WHERE子句的条件表达时，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性形成结果表。对应于关系代数的投影运算，用以列出查询结果集中的期望属性,在条件表达式中除了常用的比较运算符外,可用逻辑运算符和谓词：AND逻辑与OR逻辑或NOT逻辑非IN包含NOTIN不包含EXISTS存在UNION集合的并INTERSECT集合的交MINUS集合的差实现：简单查询联接查询使用库函数查询集合运算,SQL语言的使用方法以读者-借阅-图书数据库为例，3个关系模式:图书（总编号C,分类号C,书名C,作者C,出版单位C,单价N）读者（借书证号C,姓名C,性别C,单位C,职称C,地址C）借阅（借书证号C,总编号C,借阅日期D,备注M/C）3.1.1简单查询（单表查询）对单一关系（表）的查询可同时实现选择及投影运算、也可实现排序的功能。1.查询指定列例1:找出读者李欣张三所在的单位SELECT姓名,单位FROM读者WHERE姓名=李欣;2.查询全部列例2:查看所有读者的全部情况SELECT*FROM读者;,3.查询经过计算的值关系Student(Sno,Sname,Ssex,Sage,Sdept)例：查找学生的姓名及其出身年份SELECTSname,2007-SageFROMStudent目标列表达式不仅可以是算术表达式，还可以是字符串常量、函数等。例查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名SELECTSname,YearofBirth,2007-Sage,ISLOWER(Sdept)FROMStudent,用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达时、常量、函数名的目标表达式尤为有用。SELECTSnameasNAME,YearofBirthASBIRTH,2007SageASBIRTHDAY,ISLOWER(Sdept)asDEPARTMENTFROMStudent,4消除取值重复的行例3.列出馆中所有藏书的书名和出版单位SELECTDISTINCT书名,出版单位FROM图书;其中DISTINCT:从查询结果中去掉重复元组;,5对查询结果排序用ORDERBY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省值为ASC。例4.查找人民出版社的所有图书及单价,结果按降序排列,SELECT书名,出版单位,单价FROM图书WHERE出版单位=人民出版社ORDERBY单价DESC;例查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列SELECT*FROMStudentORDERBYSdept,SageDESC;,比较：、=、=、（或！=）等确定范围：BetweenAandB、NotBetweenAandB确定集合：IN、NOTIN字符匹配：LIKE，NOTLIKE空值：ISNULL、ISNOTNULL多重条件：AND、OR、NOT通配符%匹配任意字符串；_匹配任意一个字符,查询满足指定条件的元组可以通过WHERE子句实现。,6查询满足条件的元组,(1)比较大小Student(Sno,Sname,Ssex,Sage,Sdept)例：查询计算机系全体同学的名单SELECTSnameFROMStudentWHERESdept=CS;例：查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20;),（2）确定范围谓词BETWEEN。AND。和NOTBETWEEN。AND。可以用于查找属性值再（或不在）指定范围内的元组，其中，BETWEEN后式范围的下限（低值），AND后式范围的上限（高值）例5查找价格在10元和20元之间的图书,结果按分类号和单价升序排列SELECT书名,作者,单价,分类号FROM图书WHERE单价BETWEEN10AND20ORDERBY分类号,单价ASC;（3）确定集合谓词IN可以用语查找属性值属于指定集合的元组。例6查找人民出版社和科学出版社的所有图书及作者SELECT书名,作者,出版单位FROM图书WHERE出版单位IN(人民出版社,科学出版社);,IN可用OR来代替SELECT书名,作者,出版单位FROM图书WHERE出版单位=人民出版社OR出版单位=科学出版社;（4）字符匹配谓词LIKE可以用来进行字符串的匹配，其一般语法格式为：NPTLIKE匹配串ESCAPE换码字符查找指定的属性列值与匹配串相匹配的元组，匹配串可以是一个完整的字符串，也可以是含有通配符%和例7查找书名以“数据库”打头的所有图书和作者SELECT书名,作者FROM图书WHERE书名LIKE数据库%;,其中%作用与DOS中的*类似下划线“”代表任意一个单个字符，与DOS中的？类似。例:WHERE书名LIKE%数据库%;包含“数据库”的书名;WHERE书名LIKE%数据库;以“数据库”结尾的书名;如果LIKE后面的匹配串中不含有通配符，则可以用等于运算符取代LIKE谓词，用！=或（不等于）运算符取代NOTLIKE谓词,例8检索借了总编号为445501和445502两本书的借书证号SELECTX.借书证号FROM借阅X,借阅YWHEREX.借书证号=Y.借书证号ANDX.总编号=445501ANDY.总编号=445502;由于同一关系在一个查询模块中出现两次,必须引入别名X,Y,（5）对查询结果分组GROUPBY子句将查询结果表按某一列或多列值分组，值相等的为一组。例求各个课程号以及相应的选课人数SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;如果分组后还要求按一定的条件对这些元组进行筛选，最终只输出满足指定条件的组，则可以用HAVING短语指定筛选条件例查询选修了3门以上课程的学生学号SELECTScoFROMSCGROUPBYSnoHAVINGCOUNT(*)3WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。,3.1.2联接查询（1）等值与非等联接查询使用比较运算符：=!=例9:查询所有借阅了图书的读者姓名和所在单位SELECTDISTINCT姓名,单位FROM读者,借阅WHERE读者.借书证号=借阅.借书证号;(2)复合条件联接WHERE子句中可以有多个联接条件。例10:找出李华所借的所有图书书名和借阅日期SELECT姓名,所借图书:,书名,借阅日期FROM图书,借阅,读者WHERE读者.借书证号=借阅.借书证号AND借阅.总编号=图书.总编号AND姓名=李华;,例11:查找价格在20元以上已借出的图书，结果按降序排列SELECT*FROM图书,借阅WHERE图书.总编号=借阅.总编号AND单价=20ORDERBY单价DESC;3.1.3嵌套查询在SELECT-FROM-WHERE查询块内部再嵌入另一个查询块子查询是嵌套在另一查询中的Select-From-Where表达式（Where/Having）SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件可以用多个简单查询来构成复杂查询，以增强SQL的查询能力子查询中不使用OrderBy子句，OrderBy子句只能对最终查询结果进行排序,嵌套查询可以使用多个简单查询来构成复杂的查询，从而增强SQL的查询能力。例12找出借阅了“C语言程序设计”一书的读者姓名和所在单位联接查询方式实现:SELECT姓名,单位FROM读者,借阅,图书WHERE读者.借书证号=借阅.借书证号AND借阅.总编号=图书.总编号AND书名=C语言程序设计;,嵌套查询方式实现:SELECT姓名,单位FROM读者WHERE借书证号IN(SELECT借书证号FROM借阅WHERE总编号IN(SELECT总编号FROM图书WHERE书名=C语言程序设计）;执行嵌套查询是，每一个内层子查询是在上一级外层处理之前完成的，即外层用到内层的查询结果.常用谓词IN,不能用一系列OR来代替.,（1）首先在图书关系中找出“C语言程序设计的总编号,（2）然后在借阅关系中借阅了总编号是“C语言程序设计的借书证号,（3）最后在读者关系中找出姓名和单位,图书（总编号,分类号,书名,作者,出版单位,单价）读者（借书证号,姓名,性别,单位,职称,地址）借阅（借书证号,总编号,借阅日期,备注）,（1）带有IN的子查询例13:找出读者的姓名,所在单位,借阅日期，他们与王民一在同一天借了书.SELECT姓名,单位,借阅日期FROM读者,借阅WHERE借阅.借书证号=读者.借书证号AND借阅日期IN(SELECT借阅日期FROM借阅,读者WHERE借阅.借书证号=读者.借书证号AND姓名=王民一),SELECT姓名,单位,借阅日期FROM读者,借阅WHERE读者.借书证号=借阅.借书证号AND借阅日期IN(SELECT借阅日期FROM借阅WHERE借书证号IN（SELECT借书证号FROM读者WHERE姓名=王民一);,例14:找出藏书中比高等教育出版社的所有图书单价更高的书籍SELECT*FROM图书WHERE单价ALL(SELECT单价FROM图书WHERE出版单位=高等教育出版社)ALL表示与子查询结果的所有单价值相比都高才算满足条件,ANY表示与子查询结果的任何一个值相比,满足条件即可.,（2）带有ANY或ALL谓词的子查询父查询与多值子查询之间的比较需用All来连接标量值s比子查询返回集R中的每个都大时，sAllR为TrueAll表示所有all、=all、allall等价于notin,父查询与多值子查询之间的比较需用Some/Any来连接标量值s比子查询返回集R中的某一个大时sSomeR为True或sAnyR为TrueSome(早期用Any)表示某一个,只要有一个即返回真some、=some、some=some等价于in、some不等价于notin例15:找出藏书中所有与或在同一出版单位出版的书.SELECT*FROM图书WHERE出版单位=ANY(SELECT出版单位FROM图书WHERE书名IN(数据库导论,数据库原理);,3.1.4使用库函数查询常用的统计函数,自变量是表达式的值,按列计算.SQL的库函数有:COUNT(*):计算函数计算元组的个数;COUNT:对列的值计算个数;SUM:求和函数,对某一列的值求和(属性必须是数值类型)AVG:求平均函数,对某一列的值计算平均值(属性必须是数值类型)MAX:计算最大值,找出一列值中的最大值;MIN:计算最小值,找出一列值中的最小值;,例16:求藏书总册数SELECT藏书总册数:,COUNT(*)FROM图书;例17:求科学出版社图书的最高价格,最低价格和平均价格.SELECT最高:,MAX(单价),最低:,MIN(单价),平均:,AVG(单价)FROM图书WHERE出版单位=科学出版社;例18:求计算机科学系当前借阅了图书的读者人数.SELECT借书人数:,COUNT(DISTINCT借书证号)FROM借阅WHERE借书证号IN(SEECT借书证号FROM读者WHERE单位=计算机科学系）;,例19:用库函数找出藏书中比高等教育出版社的所有图书单价更高的书籍SELECT*FROM图书WHERE单价ALL(SELECTMAX(单价)FROM图书WHERE出版单位=高等教育出版社),例20:求各个出版社图书的最高价格,最低价格和平均价格.SELECT出版单位,MAX(单价),MIN(单价),AVG(单价)FROM图书GROUPBY出版单位;说明:GROUPBY按属性的取值对元组分组,然后对每一组分别使用库函数.例21:分别找出各个单位当前借阅图书读者人次.SELECT单位,借书人次,COUNT(借书证号)FROM借阅,读者WHERE读者.借书证号=借阅.借书证号GROUPBY单位;,例22:找出藏书中各个出版单位的册数,价格总额,并按总价降序,总价相同者按册数降序排列.SELECT出版单位,册数:,COUNT(*),总价:,SUM(单价)FROM图书GROUPBY出版单位ORDERBYSUM(单价)DESC,COUNT(*)DESC,例23:找出当前至少借阅了5本图书的读者及所在单位SELECT姓名,单位FROM读者WHERE借书证号IN(SELECT借书证号FROM借阅GROUPBY借书证号HAVINGCOUNT(*)=5)例24:分别找出借书人数超过10个人的单位及人数SELECT单位,借书人数:,COUNT(DISTINCT借书证号)FROM借阅,读者WHERE读者.借书证号=借阅.借书证号GROUPBY单位HAVINGCOUNT(DISTINCT借书证号)10;HAVING子句通常跟随在GROUPBY之后,其作用是对分组限定检索条件.条件中必须包含库函数。在查询中一般先用WHERE限定元组,然后用GROUPBY分组,最后用HAVING限定分组。,例25:找出没有借阅任何图书的读者及所在单位SELECT姓名,单位FROM读者WHERENOTEXISTS(SELECT*FROM借阅WHERE借阅.借书证号=读者.借书证号);也可写为如下形式:SELECT姓名,单位FROM读者WHERE借书证号NOTIN(SELECT借书证号FROM借阅WHERE借阅.借书证号=读者.借书证号);,3.1.5集合运算并UNION,差MINUS,交INTERSECTION.求一个SELECT子查询的结果与另一个SELECT子查询结果的并,差,交,子查询的结构与类型必须相互匹配.属性个数必须一致对应的类型必须一致属性名无关最终结果集采用第一个结果的属性名缺省为自动去除重复元组除非显式说明ALLOrderBy放在整个语句的最后,例26:有一个校友通讯录关系,包括姓名,职称,单位属性,相应的数据定义与读者关系一致,求校友与读者中具有教授,副教授职称人员的并集SELECT姓名,职称,单位FROM读者WHERE职称IN(教授,副教授)UNIONSELECT姓名,职称,单位FROM校友WHERE职称IN(教授,副教授),SELECT小结SELECT的一般语法:SELECT查询目标FROM关系WHERE条件表达式GROUPBY分组属性名HAVING组选择条件表达式ORDERBY排序属性序.查询目标可用以下格式:DISTINCT*表名.*|COUNT(*)|表达式,表达式.,条件表达式可用以下运算符:算术比较运算符=,=,=,!=,BETWEEN逻辑运算符AND,OR,NOT集合元素包含运算符IN,NOTIN存在量词EXISTS(SELECT子查询)集合运算符并NUION,差MINUS,交INTERSECTION通配符LINK,LINK%,3.2SQL数据定义（SQLDDL）,3.2.1SQL数据库的体系结构（三级模式结构）,SQL用户,视图V1,视图V2,基本表B1,基本表B2,基本表B3,基本表B4,分区1,文件1,文件2,分区2,文件3,文件4,存储模式(内模式),模式,外模式,本身独立存在的表，即实际存储在数据库中的表，而不是从其它表导出来的。基本表的集合组成关系模型全局概念模型,一个基本表可以跨一个或多个存储文件,而一个存储文件可以存放一个或多个基本表.一个存储文件与外存上的一个物理文件相对应。,数据库中的基本表包含了多个用户共享的数据，某一用户可能需要使用其中的一部分数据（可能从多个基本表），或者基本表的格式不直接满足用户的要求。可以从一个或几个基本表导出满足用户要求的数据“表”（区别于基本表，该“表”不是一个实际的表，是一个临时“表”、虚表）,（1）基本表(basetable)：本身独立存在的表，即实际存储在数据库中的表；（2）视图(view）：从一个或几个基本表或其它视图导出来的表，视图本身并不独立存储数据，系统只保存视图的定义。数据定义语言（DataDefinitionLanguage）,注：视图是基本表的虚表，索引是依附于基本表的，因此无修改视图和索引的操作。要修改时，先将它们删除，再重建,3.2.2定义基本表（CREAT）1.定义基本表创建一个基本表，对表名（关系名）和它所包括的各个属性名及其数据类型作出具体的规定。定义一组关系（基本表）、说明各关系的信息各关系的模式各属性的值域完整性约束索引安全性和权限,CreateTable表名（列名数据类型列级完整性约束条件，);数据类型：CHAR（n）字符串，长度为n个西文字符；INTERGER全字长整数，范围从负10位整数到正11位整数；SMALLINT半字长整数，范围从-99999到99999；DECIMAL（x,y）十进制数，包括小数点及符号位共x位，其中y位小数；FLOAT(x,y)双字长浮点数，范围从0.1e-307到0.9e+308，以指数形式表示DATE(日期型)MONEYTEXTIMAGEBINARYTIME,1DDLSQL的模式定义,例1：定义基本表：CREATTABLE图书（总编号CHAR（6）NOTNULL，/列级约束条件，总编号取值唯一，不许取空值分类号CHAR（8），书名CHAR（30），单价DECIMAL（10),CreateTableStudent(snochar(10)UNIQUE，snamechar(20)，sagesmallint,ssexchar(1),sdeptchar(2),2修改基本表ALTERTABLE表名ADD新列名数据类型完整性约束MODIFY列名数据类型；例2：增加表中的属性ALTERTABLE图书ADD（作者CHAR（8），出版单位CHAR（20）；3DDL删除表结构DropTABLE表名是把表的定义、表中的数据、其上的索引以及以该基本表为基础所建立的所有视图全部删除，并释放所占有的存储空间。DROPTABLE图书；,3.2.3定义视图是从一个或几个基本表（或视图）中导出的虚表数据库中只保存它的定义，是RDBMS提供给用户以多种角度观察数据库中数据的重要机制目的：建立视图一方面可简化查询命令，另一方面可限制某些用户的查询范围。创建视图、删除CreateView视图名（列名，列名）AS,1.从单个表中派生出的视图例4：建立管理工程系的读者视图，名称为GL-READER。CREATVIEWGL-READERASSELECT姓名，性别，职称，地址FROM读者WHERE单位=管理工程系视图一经定义，可以和基本表一样进行各种查询，也可进行修改操作SELECT*FROMGL_READER,2.从多个表中派生出的视图例5：建立各个单位当前借阅图书情况的简单统计视图，名称为DW-READER。CREATVIEWDW-READER（单位，借阅人数，借阅人次）AsSELECT单位，COUNT（DISTINCT借书证号）,COUNT（总编号）FROM借阅，读者WHERE读者.借书证号=借阅.借书证号GROUPBY单位；3.取消视图DROPVIEWDROPVIEWGL-READER；取消视图后，其定义以及在它基础上所建立的其它视图将自动删除。,3.2.4定义别名（同义词）用户可为基本表、视图和已存在的同义词起别名，所起的别名作为原对象的同义词使用。例7：为基本表借阅起一个同义词别名LOANCREATSYNONYMLOANFOR借阅；SELECTNUMAS学号，NAMEAS姓名FROM学生;SELECTX.借书证号,X.姓名FROM借阅X,借阅YWHEREX.借书证号=Y.借书证号ANDX.总编号=500088ANDY.总编号=100909;例8：删除同义词别名LOANDROPSYNONYMLOAN；,索引是一种数据结构，是对照表、指针表原理:数据库的基本功能就是满足用户提出的各种查询需求，一般有“顺序查询”和“索引查询”两类，“顺序查询”按照需查询的内容从第一条记录开始逐一比较，效率较低。“索引”就是为了提高查询的速度而引入的，根据关键字建立一个关键字和其对应的记录在原基本表中的物理地址（指针）的对照表索引文件，其中的记录按关键字已排序（基本表无须排序），因此便于使用一些快速查询算法（二分法或折半查找）。,DDL索引Index,3.2.5索引的建立为提高数据的查询速度，引出索引。索引的概念：,1、索引的建立CREATEINDEX索引名ON关系名（属性名ASC|DESC）;例如：CREATEINDEXTSNOON图书（借书证号ASC）;2、取消索引DROPINDEXDROPINDEXTSNO;,3.3SQL数据操纵对关系中的具体数据进行增、删、改操作。3.3.1插入数据两种格式：（1）向具体元组插入常量数据（插入一个元组）；（2）从子查询的结果输入到另一个关系中。格式为：INSERTINTO（，.）VALUES（，）,图书（总编号，分类号，书名，作者，出版单位，单价）；1.单行插入：一次只插入一个元组例1：向图书基本表中新加一个元组。INSERTINTO图书VALUES(449800,TP123/456，计算机基础，李华，高等教育出版社，17.10）;新增元组的各个列（属性）的值必须符合定义，列的顺序与定义一致。例2：向图书基本表中新加一个元组的部分字段。INSERTINTO图书（总编号，书名，单价）VALUES（449800，计算机基础，17.10）;,2多行插入：插入一个集合(子查询的结果）例:给CS系的学生开设必修课C05，建立选课信息InsertIntoSCSelectsno,cno,nullFromStudent,CourseWheresdept=CSandcno=C05,例3：建立一个各单位借阅图书情况统计基本表，名称为DW-JSH，每隔一段时间，如一个月，向此基本表追加一次数据。CREATTABLEDW-JSH（单位CHAR（20），借书人数SMALLINT，借书人次SMALLINT）；INSERTINTODW-JSH（单位，借书人数，借书人次）SELECT单位，COUNT（DISTINCT借书证号），COUNT（总编号）FROM借阅，读者WHERE读者.借书证号=借阅.借书证号GROUPBY单位；,3.3.2更新数据(UPDATE)改变符合条件的某个（某些）元组的属性值,格式：UPDATESET=，=WHERE1.修改一个元组的值例4：将例2插入的图书填上作者和出版单位。UPDATE图书SET作者=李华，出版单位=电子工业出版社WHERE总编号=4489003834；2.修改多个元组的值例5：将所有的图书的单价上调5%UPDATE图书SET单价=单价*1.05;,例6：将书名中包含“计算机”的书分类号改为“TP31/123”。UPDATE图书SET分类号=TP31/123WHERE书名=%计算机%；例7：把借书证号“7900805”改为“134465567”。UPDATE读者SET借书证号=134465567WHER