SELECT语句的基本句法 (2)

Select 基本语句SELECT语句的基本句法 1 关系代数合并数据集合的理论基础是关系代数，它是由E.F.Codd于1970年提出的。在关系代数的形式化语言中： 用表、或者数据集合表示关系或者实体。 用行表示元组。 用列表示属性。关系代数包含以下8个关系运算符 选取返回满足指定条件的行。 投影从数据集合中返回指定的列。 笛卡尔积是关系的乘法，它将分别来自两个数据集合中的行以所有可能的方式进行组合。 并关系的加法和减法，它可以在行的方向上合并两个表中的数据，就像把一个表垒在另一个表之上一样。 交返回两个数据集合所共有的行。 差返回只属于一个数据集合的行。 连接在水平方向上合并两个表，其方法是：将两个表中在共同数据项上相互匹配的那些行合并起来。 除返回两个数据集之间的精确匹配。此外，作为一种实现现代关系代数运算的方法，SQL还提供了： 子查询类似于连接，但更灵活；在外部查询中，方式可以使用表达式、列表或者数据集合的地方都可以使用子查询的结果。2 使用连接2.1 连接类型在关系代数中，连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。连接类型 定义内连接 只连接匹配的行左外连接 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行右外连接 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行全外连接 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。(H)(theta)连接 使用等值以外的条件来匹配左、右两个表中的行交叉连接 生成笛卡尔积它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配在INFORMIX中连接表的查询如果FROM子句指定了多于一个表引用，则查询会连接来自多个表的行。连接条件指定各列之间（每个表至少一列）进行连接的关系。因为正在比较连接条件中的列，所以它们必须具有一致的数据类型。SELECT语句的FROM子句可以指定以下几种类型的连接FROM子句关键字 相应的结果集CROSS JOIN 笛卡尔乘积（所有可能的行对）INNER JOIN 仅对满足连接条件的CROSS中的列LEFT OUTER JOIN 一个表满足条件的行，和另一个表的所有行RIGHT OUTER JOIN 与LEFT相同，但两个表的角色互换FULL OUTER JOIN LEFT OUTER 和 RIGHT OUTER中所有行的超集2.2 内连接（Inner Join）内连接是最常见的一种连接，它页被称为普通连接，而E.FCodd最早称之为自然连接。下面是ANSI SQL92标准select *from t_institution iinner join t_teller ton i.inst_no = t.inst_nowhere i.inst_no = 5801其中inner可以省略。等价于早期的连接语法select *from t_institution i, t_teller twhere i.inst_no = t.inst_noand i.inst_no = 58012.3 外连接2.3.1 左外连接(Left Outer Jion)select *from t_institution ileft outer join t_teller ton i.inst_no = t.inst_no其中outer可以省略。2.3.2 右外连接(Rigt Outer Jion)select *from t_institution iright outer join t_teller ton i.inst_no = t.inst_no2.3.3 全外连接(Full Outer)全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。在现实生活中，参照完整性约束可以减少对于全外连接的使用，一般情况下左外连接就足够了。在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接就变得非常有用了，你可以使用它来清理数据库中的数据。select *from t_institution ifull outer join t_teller ton i.inst_no = t.inst_no2.3.4 外连接与条件配合使用当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件加入到join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响：条件在join子句select *from t_institution ileft outer join t_teller ton i.inst_no = t.inst_noand i.inst_no = “5801”结果是：inst_no inst_name inst_no teller_no teller_name5801 天河区 5801 0001 tom5801 天河区 5801 0002 david5802 越秀区5803 白云区条件在where子句select *from t_institution ileft outer join t_teller ton i.inst_no = t.inst_nowhere i.inst_no = “5801”结果是：inst_no inst_name inst_no teller_no teller_name5801 天河区 5801 0001 tom5801 天河区 5801 0002 david2.4 自身连接自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。下面例子是在机构表中查找本机构和上级机构的信息。select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_namefrom t_institution ijoin t_institution son i.superior_inst = s.inst_no结果是：superior_inst sup_inst_name inst_no inst_name800 广州市 5801 天河区800 广州市 5802 越秀区800 广州市 5803 白云区2.5 交叉(无限制) 连接交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。select *from t_institution icross join t_teller t在交叉连接中没有on条件子句3 SELECT语句的基本句法3.1 SELECT-FROM-WHERE句型使用实例假设已建立三个表： student(sno,sname,sex,birth,height,class,address) course(cno,cname,credit) elective(sno,cno,grade)下面给出J-SQL各种形式的SELECT查询语句例子。除极个别的地方要做小小改动外，这些例子也可在T-SQL中运行通过。在结果集中对表达式列指定列别名。SELECT sno, grade-75 AS MarjinFROM elective;列出student表中所有学生的清单。SELECT *FROM student;使用DISTINCT，除去结果集中的重复行。SELECT DISTINCT sexFROM student;在WHERE子句中包含简单的条件grade80，输出表中行的子集。SELECT *FROM electiveWHERE grade80;在WHERE子句中包含日期比较，本例是列出1982年1月1日前出生的学生。SELECT *FROM studentWHERE birth#01-01-1982#; (注：在T-SQL应为 WHERE birth= 60 and grade175 and birth#01-01-1982# or sex=女;检索选修了课程号cno为c06的学生学号与姓名。第一种查询方法(联接查询)：SELECT student.sno, snameFROM student, electiveWHERE student.sno=elective.sno and cno=c06;检索选修了课程号cno为c06的学生学号要从elective表搜索，但学生的姓名却要在表student才能查到。这个语句执行时，先对FROM子句给定的两个表按WHERE的条件进行联接操作，最后按SELECT子句投影出所需的列。由于两个表都有SNO列，引用时要在SNO前用表名加以限定。第二种查询方法(嵌套查询)：SELECT sno, snameFROM studentWHERE sno in ( SELECT sno FROM elective WHERE cno=c06);这里外层WHERE子句中嵌有一个SELECT语句。执行时，先执行内层的查询，找出选修了课程号为c06的学生学号；然后再从外层查询中按学号找出相应的学生姓名。当查询涉及多个表时，使用嵌套结构可逐步求解层次分明。而且，嵌套查询的执行效率也高于需做乘积或联接运算的联接查询。本例的嵌套查询还可以写成下面的形式：SELECT sno, snameFROM studentWHERE c06 in ( SELECT cno FROM elective WHERE sno=student.sno);这里的内层查询称为相关查询，其查询条件依赖于外层查询中的某个值，所以要多次处理，反复求值以供外层查询使用。第三种查询方法(使用存在量词的嵌套查询)：SELECT sno,snameFROM studentWHERE exists (SELECT * FROM elective WHERE student.sno=elective.sno and cno=c06);谓词exists即存在量词($)，其语义是内层查询的结果非空(或者说，内层查询结果应包含任何行；又或者说，内层查询结果应至少有一行)时为真。检索选修课程名为english的学生学号和姓名。SELECT sno, snameFROM studentWHERE sno in ( SELECT sno FROM elective WHERE cno in ( SELECT cno FROM course WHERE cname=english);本例若用联接查询方法可写成如下代码：SELECT student.sno, snameFROM student, course, electiveWHERE student.sno=elective.sno and o=o and cname=english;检索至少同时选修了C03和C06两门课的学生学号。SELECT A.snoFROM elective AS A, elective AS BWHERE A.sno = B.sno and A.cno=C03 and B.cno=C06;本例中，在同一层查询内，同一个表elective出现了两次，引入表的别名A和B就是为了便于区分。若在A中找到C03的行，B中找到C06的行，且两行的SNO相同，那么这个学生就是至少同时选修了指定两门课的学生。把表的别名A和B看成是定义了两个元组变量将更容易理解。检索出没有选修maths课程的学生学号和姓名。SELECT sno, snameFROM studentWHERE sno not in ( SELECT sno FROM elective WHERE cno in ( SELECT cno FROM course WHERE cname=maths);请注意例中最外层的WHERE子句是使用了not in,若改为not exists则代码如下：SELECT sno, snameFROM studentWHERE not exists ( SELECT sno FROM elective WHERE student.sno=elective.sno and cno in ( SELECT cno FROM course WHERE cname=maths);检索出选修了全部课程的学生学号和姓名。SELECT sno, snameFROM studentWHERE not exists ( SELECT * FROM course WHERE not exists ( SELECT * FROM elective WHERE student.sno=elective.sno and o=o);本例的查询可以这样理解，在student依次指定每个学生，在course中不存在一门课程是这个学生没有学的。外层查询与内层查询可通过三种方式连接：表达式 NOT IN (内层查询)NOT EXISTS (内层查询)标量值 q ANY|ALL|SOME (内层查询) 注：标量值也称为常量或字面值; q 是算术比较运算符(如=,=,等; SQL规定ANY与SOME是同义词，后来的标准都改为SOME。例如，检索选修了课程号为C12的学生学号和姓名可表达为：SELECT sno, snameFROM studentWHERE sno = SOME ( SELECT sno FROM elective WHERE cno=c12);注意，IN与=SOME等价，故例中的=SOME可换成IN。但是NOT IN并不等价于SOME,与NOT IN等价的是ALL。在使用ANY,ALL,SOME时，一定要仔细地弄清楚他们的确切含义。检索至少有一门成绩超过200200105号学生某一门成绩的学生学号。SELECT DISTINCT snoFROM electiveWHERE gradesome ( SELECT grade FROM elective WHERE sno=200200105);聚合函数对一组值执行计算并返回单一的值。统计student表的学生总人数以及平均身高。SELECT count(*) AS 学生总人数, avg(height) AS 平均身高FROM student;在elective表统计选修了课程的学生人数以及找出所有成绩中的最高分、最低分。/* 本例用T-SQL语句实现 */use mydbgoselect count(distinct sno) as 选课人数,max(grade) as 最高分,min(grade) as 最低分from electivego聚合函数注意各个函数的含义： COUNT（*) 计算元组的个数COUNT（列名) 求一列中值的计算个数SUM（列名) 求一列中值的总和AVG（列名) 求一列中值的平均值MAX（列名) 求一列中值的最大值MIN（列名) 求一列中值的最小值3.2 SELECT语句的完整句法下面，我们以T-SQL为例，按SELECT 语句执行过程的顺序介绍各个子句的语法摘要。(1) FROM子句FROM子句的功能是指定SELECT(包括DELETE,UPDATE)语句中使用的表和视图。多种联接方式(2) WHERE子句(行条件子句)WHERE子句的功能是指定选取行子集的条件。(3) GROUP BY子句(分组子句)GROUP BY子句的功能是指定分组的条件。/* 本例按学号分组，统计每个学生的平均分，输出时按平均分降序排列 */use mydbgoSELECT sno, AVG(grade) as 平均分, MAX(grade) as 最高分FROM electiveGROUP BY snoORDER BY 平均分 DESC(4) HAVING子句(组条件子句)HAVING子句的功能是在分组的基础上指定选取某些组的条件。GROUP BY子句按学号SNO对ELECTIVE表中的行分组后，HAVING子句再消除选修少于两门课的组：USE mydbgoSELECT sno,avg(grade)FROM electiveGROUP BY snoHAVING COUNT(*) 1(5) SELECT子句(6) ORDER BY子句(排序子句)ORDER BY子句的功能是对结果集的排序。对elective表中的行按分数降序排列。USE mydbgoSELECT sno,gradeFROM electiveORDER BY 2 descORDER BY可以用来为GROUP BY子句的输出排序。下面的示例显示使用ORDER BY子句定义返回GROUP BY子句中的行的顺序：USE mydbgoSELECT cno,count(sno)FROM electiveWHERE grade is not nullGROUP BY cnoHAVING count(sno)0ORDER BY 2 desc,cno(7) INTO子句INTO子句的功能是创建新表并将查询结果集插入新表中。INTO子句应用举例。USE mydbgoSELECT year(birth) as age,count(sno) as s_countINTO m_studentFROM studentWHERE sex=男GROUP BY year(birth)HAVING year(birth)1981ORDER BY 2,year(birth) desc在这个例子中，用上了前面介绍的全部7个子句。先从student表中选出男生的行，再按出生年份分组，然后选出出生年份大于1981的那些组，计算每组的年龄及同一年龄组的人数，输出时按同一年龄组的人数升序排列，对人数相同的那些组再按出生年份降序排列，最后创建新表m_student，把结果集插入到新表中。3.3 SQL查询其它一些子句及关系代数表达式的实现上一节介绍了SELECT语句中主要子句的语法。下面介绍如何用SQL查询语句实现关系代数表达式，并论述查询的结构及其它的一些子句：(1) COMPUTE子句COMPUTE子句的功能是进行汇总计算，把生成的合计作为附加列放在结果集的最后面。COMPUTE子句语法格式： COMPUTE AVG|COUNT|MAX|MIN|STDEV|VAR|SUM (表达式) ， BY 表达式 ， 下列T-SQL SELECT语句使用COMPUTE子句生成student表中height列的和及平均值：use mydbgoSELECT sno,heightFROM studentCOMPUTE SUM(height),avg(height)下列查询在COMPUTE子句中加入可选的BY关键字，以生成每个sno的小计,即统计出每个学生修了几门课(已取得成绩的课)、总分和平均分：use mydbgoSELECT *FROM electiveWHERE grade is not nullORDER BY snoCOMPUTE count(sno),sum(grade),avg(grade) BY sno(2) UNION运算符(并集运算符)UNION运算符的作用是将两个或更多查询的结果组合为单个结果集。T-SQL UNION运算符语法： | () UNION ALL | () UNION ALL | () 下面的J-SQL例子说明了UNION的基本应用。若有多个SELECT语句参与并运算，可用UNION运算符分隔开。SELECT *FROM student_tUNIONSELECT *FROM student;(3) 交集和差集的操作在一些其它的SQL，进行交集和差集的操作可使用下列语句：交： (SELECT查询语句1) INTERSECT ALL (SELECT查询语句2)差： (SELECT查询语句1) EXCEPT ALL (SELECT查询语句2) 或(SELECT查询语句1) MINUS ALL (SELECT查询语句2)下面是求student和student_t两个表交集的代码：SELECT student.*FROM student INNER JOIN student_t ON student.sno=student_t.sno;利用NOT IN的性质仅属于第一个表而不属