第4章SQL-关系数据库的标准

第4章SQL-关系数据库的标准SQL概述SQL的数据操作SQL的数据定义SQL的数据控制视图的概念嵌入式SQL概述——SQL的发展和现状（1）SQL(结构化查询语言StructuredQueryLanguage)，是在1974年由Boyce和Chamberlin提出的（2）1975~1979，IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言（3）SQL-86是第一个SQL标准概述——SQL的发展和现状（4）SQL-89、SQL-92(SQL2)、SQL-99(SQL3)（5）大部分DBMS产品都支持SQL，成为操作数据库的标准语言（6）有方言，支持程度不同概述——SQL的特点和功能综合统一高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方法语言简捷，易学易用概述——SQL的特点和功能数据定义(DDL)Create、Drop、Alter数据操纵(DML)数据查询：Select数据修改：Insert、Update、Delete数据控制(DCL)Grant、Revoke交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释嵌入式SQL能将SQL语句嵌入到高级语言（宿主语言）使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力概述——SQL的形式概述——SQL体系结构（1）一个SQL数据库是表（Table）的集合。（2）一个表或者是基本表，或者是一个视图。基本表是实际存储在数据库中的表，而视图是由若干基本表或其它视图构成的查询语句定义的。（3）一个基本表对应一个或多个存储文件，一个存储文件也可存放一个或多个基本表。（4）用户可以用SQL语句对视图和基本表进行查询操作。在用户看来，视图和基本表是一样的，都是关系。SQL的数据查询数据查询是数据库应用的核心功能一、基本结构SelectA1,A2,...,AnFromr1,r2,...,rmWhereP;A1,A2,...,An(p(r1×r1×...×rm))SelectWhereFromSelect语句的含义对From子句中的各关系，作笛卡儿积（×）对Where子句中的逻辑表达式进行选择（σ）运算，找出符合条件的元组根据Select子句中的属性列表，对上述结果作投影（π）操作结果集，查询操作的对象是关系，结果还是一个关系，是一个结果集二、单表查询Select子句（选择表中若干列）Where子句（选择表中若干行）OrderBy子句（对查询结果排序）聚集函数单表查询——选择表中的若干列对应于关系代数的投影（π）运算，用以列出查询结果集中的期望属性。查询指定的列Select学号，学生From学生；目标列表达式目标列表达式中各个列的先后顺序可以与表中的顺序不一致，我们可以根据用户的需求改变列的顺序单表查询——选择表中的若干列查询全部列星号*：按关系模式中属性的顺序排列显式列出属性名：按用户顺序排列Select*From学生；查询经过计算的值例1：查询全体学生的姓名及其出生年份Select姓名，2002-年龄From学生；单表查询——选择表中的若干列更名为结果集中的某个属性改名使结果集更具可读性Select学号，姓名as学生姓名From学生；Select姓名as学生姓名，2002–年龄as出生年份From学生；单表查询——选择表中的若干元组消除取值重复的行Select子句的缺省情况是保留重复元组（ALL），可用Distinct去除重复元组Select所在系From学生；等价于SelectAll所在系From学生；SelectDistinct所在系From学生；查询满足条件的元组查询满足指定条件的元组可以通过Where子句来实现where子句对应与关系代数中的选择(σ)使where子句中的逻辑表达式返回True值的元组，是符合要求的元组，将被选择出来Where子句——运算符比较：=、、、=、=、!=、、!、!、not+~确定范围：BetweenAandB、NotBetweenAandB确定集合：IN、NOTIN字符匹配：LIKE，NOTLIKE空值：ISNULL、ISNOTNULL多重条件：AND、OR、NOTWhere子句——比较大小例2：查询城环系全体学生的名单例3：查询所有年龄在20岁以下的学生姓名及其年龄例4：查询考试成绩有不及格的学生的学号Where子句——确定范围谓词BETWEEN…AND…和NOTBETWEEN…AND…可以用来查找属性值在（或不在）指定范围内的元组。例5：查询年龄在18-20岁之间的学生的姓名和年龄例6：查询年龄不在18-20岁之间的学生的姓名和年龄Where子句——确定集合谓词IN可以用来查找属性值属于指定集合的元组。NOTIN用来查找属性值不属于指定集合的元组。例7：查询城环系、地空学院学生的姓名和性别例8：查询年龄不是18、20、22岁的学生的姓名Where子句——Like字符匹配：Like、NotLike与使用=和!=字符串比较运算符相比，使用通配符可使LIKE运算符更加灵活通配符%——匹配任意字符串_——匹配任意一个字符[]——指定范围([a-f])或集合([abcdef])中的任何单个字符。[^]——不属于指定范围([a-f])或集合([abcdef])的任何单个字符。大小写敏感Where子句——Like例9：列出汪姓的学生的学号、姓名Select学号，姓名From学生Where姓名LIKE‘汪%’；例10：列出汪姓且单名的学生的学号、姓名例11：列出姓汪、邹的学生的学号、姓名例12：例出不姓汪、邹的学生的学号、姓名Where子句——转义符escape例13：列出课程名称中带有‘_’的课号及课名。Select课程号，课程名称From课程Where课程名称LIKE‘%\_%’escape‘\’Where子句——空值查询例14：查询所有选了课而没有考试成绩的学生的学号和课程号Select学号，课程号From课程Where成绩IsNuLL；例15：查询所有有001号课的成绩的学生的学号OrderBy子句（对查询结果排序）指定结果集中元组的排列次序耗时ASC（缺省）、DESC对于空值，若升序，含空值的元组最先显示；若降序，则反之例16：列出城环系中的男生的学号、姓名、年龄，并按年龄进行排列（升序）OrderBy子句——实例例17：列出城环系中的男生的学号、姓名、年龄，并按年龄进行降序排列。Select学号、姓名、年龄From学生Where性别=‘男’OrderBy年龄Desc;例18：查询全体学生情况，查询结果按所在系的系名升序排列，同一系中的学生按年龄降序排列。聚合函数把一列中的值进行聚合运算，返回单值的函数五个预定义的聚合函数平均值：Avg（ALL|DISTINCT字段名）总和：Sum（ALL|DISTINCT字段名）最小值：Min（ALL|DISTINCT字段名）最大值：Max（ALL|DISTINCT字段名）计数：Count（ALL|DISTINCT字段名）Count(*)、Count(Distinct…)SUM、AVG的字段类型必须是数值型聚合函数——实例例19、查询学生总人数。SelectCount(*)From学生；例20、查询选修了课程的学生人数。例21、计算课程号为001的课程的平均值、并查询它的最高分和最低分。聚合函数——GroupBy子句将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现只有出现在GroupBy子句中的属性，才可出现在Select子句中例22：统计各系学生的人数。Select所在系，count(*)as学生数目From学生GroupBy所在系；聚合函数——Having子句针对聚合函数的结果值进行筛选（选择），它作用于分组计算结果集跟在GroupBy子句的后面，没有GroupBy则针对全表例23：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。Where和Having的区别聚合函数——Null聚合函数忽略NullCount：不计Sum：不将其计入Avg：具有Null的元组不参与Max/Min：不参与例24：Selectcount(sdept)From学生SelectAvg(sage)From学生若一个查询同时涉及两个以上的表，则称之为连接查询，包括：内连接自连接外连接复合条件连接三、连接查询它是条件连接，这个条件称为连接条件或连接谓词可以是等值连接，也可以是不等值连接新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性Inner是缺省的连接方式也可以用笛卡儿积+选择的方法实现内连接（InnerJoin）例25、查询每个学生的全部信息及其选课情况SELECT学生.*,选课.*FROM学生INNERJOIN选课ON学生.学号=选课.学号;SELECT学生.*,选课.*FROM学生,选课Where学生.学号=选课.学号;内连接——实例例26、查询每一门课的先修课SELECTDistinctf.*FROM课程fInnerJoin课程SOnF.课程编号=S.先修课程编号;自连接它是条件连接，且条件是必需的。若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上Null，而不是像Inner那样被忽略。新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性有左外连接、右外连接、全外连接外连接（OuterJoin）左外连接（LeftOuterJoin）：SELECT*FROM学生LEFTJOIN选课ON学生.学号=选课.学号;右外连接（RightOuterJoin）：SELECT*FROM学生RIGHTJOIN选课ON学生.学号=选课.学号;全外连接（FullOuterJoin）：SELECT*FROM学生FULLJOIN选课ON学生.学号=选课.学号;外连接——左外连接、右外连接、全外连接例27、查询选修001号课程且成绩在90分以上的所有学生信息。SELECT*FROM学生INNERJOIN选课ON学生.学号=选课.学号WHERE选课号=‘001’and选课.成绩90;例28、查询每个学生的学号、姓名、选课的课程名和成绩。复合条件连接四、子查询（嵌套查询）子查询是嵌套在另一查询中的Select-From-Where表达式（Where/Having）SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件可以用多个简单查询来构成复杂查询，以增强SQL的查询能力子查询中不使用OrderBy子句，OrderBy子句只能对最终查询结果进行排序返回单值的子查询，只返回一行一列父查询与单值子查询之间用比较运算符进行连接运算符：、=、=、=、、例29：找出与00015042同龄的学生子查询——单值比较Select*From学生Where年龄=(Select年龄From学生Where学号=‘00015042’)；子查询返回多行一列运算符：In、All、Some|Any、Exists子查询——多值标量值与子查询返回集中的某一个相等，trueIN被用来测试多值中的成员例30：查询选修‘C01’课程的学生的学号、姓名。Select学号,姓名From学生Where学号IN(Select学号From选课Where学号=‘C01’)；子查询多行1列子查询——多值成员In例31、查询选修了‘数据库’的学生的学号和姓名子查询——多值成员InSelect学号,姓名From学生Where学号IN(Select学号From选课Where课程号IN(Select课程号From课程Where课程名称=‘数据库’))；查询数据库的课程号查询选择了数据库课的学生