DB第3章3关系数据库标准语言——SQL.ppt

1,数据库原理与应用,2,第3章 关系数据库标准语言SQL,3.4 SQL的数据查询功能 3.4.1 SELECT语句介绍 3.4.2 SQL的查询实例及分析,3,3.4 SQL的数据查询功能,3.4.1 SELECT语句介绍 1. SELECT语句的语法 SELECT目标列组 FROM数据源 WHERE元组选择条件 GROUP BY分列组HAVING 组选择条件 ORDER BY排序列1排序要求1 ，n；,4,语法说明,(1) SELECT子句：指明目标列（字段、表达式、函数表达式、常量）。两个基本表中相同的列名表示为：表名.列名 (2) FROM子句：指明数据源。表间用“，”分割。数据源不在当前数据库中，使用“数据库名.表名”表示。一表多用，用别名标识。定义表别名：表名别名 (3) WHERE子句：元组选择条件。 (4) GROUP BY子句：结果集分组。当目标列中有统计函数，则统计为分组统计，否则为对整个结果集统计。子句后带上HAVING子句表达组选择条件（带函数的表达式）。 (5) HAVING短语：筛选出只有满足指定条件的组。 (6) ORDER BY子句：对查询结果表排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。,5,2. SELECT语句的操作符,(1) 算术操作符 +（加号）、（减号）、*（乘号）和 /（除号）。 (2) 比较操作符 =（等于）、（大于）、=（大于等于）、!=（不等于）、（小于大于）、!（不大于）和 !（不小于），共9种操作符。,(3) 逻辑操作符,7,(4) 组合查询操作符和其他SQL操作符,是针对传统关系运算的操作符 1) UNION：并查询，并在结果集中去掉重复行。 2) MINUS：差查询操作。 3) INTERSECT：交查询操作。,使用格式： 组合操作符 ,8,5) ALL：全部。保留重复值（有统计函数时要求计算重复值）。 格式为： ALL字段或 ALL字段组 6) DISTINCT：去掉重复值。在结果集中去掉重复值，或在统计函数中不计重复值。 格式为：DISTINCT字段或 DISTINCT字段组,4) *：取全部字段。格式为： * 或表名.*,9,10,3.4.2 SQL的查询实例及分析,学生课程库结构为：学生(学号，姓名，年龄，所在系)； 课程(课程号，课程名，先行课)； 选课(学号，课程号，成绩). 1. 简单查询：查询过程中只涉及到一个表的查询语句，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组,11,【例3-9】求数学系学生的学号和姓名。 SELECT 学号,姓名 FROM 学生 WHERE 所在系=数学系; 【例3-10】求选修了课程的学生学号。 SELECT DISTINCT 学号 FROM 选课; DISTINCT：消除取值相同的记录。,12,例子,【例3-11】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。 SELECT 学号,成绩 FROM 选课 WHERE 课程号=C1 ORDER BY 成绩 DESC,学号 ASC; order by：按某种顺序显示。使用格式： order by asc|desc， , asc：按升序排序； desc：按降序排序。,13,【例3-12】求选修课程C1且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.8输出。 SELECT 学号,成绩*0.8 FROM 选课 WHERE 课程号 = C1 AND 成绩 BETWEEN 80 AND 90; between and：查找值在指定范围内的元组。,14,例子,【例3-13】求数学系或计算机系姓张的学生的信息。 SELECT * FROM 学生 WHERE 所在系 IN (数学系,计算机系) AND 姓名 LIKE 张%; In: 用来查找值属于指定集合的元组。格式： 列名 in (常量1，常量2，常量n) Like：用于查找指定列中与匹配串常量匹配的元组。匹配串是一种特殊的字符串，它可以包含普通字符，还可以包含通配符。 通配符：_(下划线)：匹配任意一个字符。 %(百分号)：匹配0个或多个字符。,16,SQL Server 中四种匹配符的含义,% 代表零个或者多个任意字符 _ 代表一个任意字符 指定范围内的任意单个字符 不在指定范围内的任意单个字符 带有匹配符的字符串必须使用引号引起来，例如下面的示例: LIKE BR% 返回以“BR”开始的任意字符串。 LIKE Br% 返回以“Br”开始的任意字符串。 LIKE %een 返回以“een”结束的任意字符串。 LIKE %en% 返回包含“en”的任意字符串。 LIKE _en 返回以“en”结束的三个字符的字符串。 LIKE CK% 返回以“C”或者“K”开始的任意字符串。 LIKE S-Ving 返回长为四个字符的字符串，结尾是“ing”，开始是从S到V LIKE Mc% 返回以“M”开始且第二个字符不是“c”的任意字符串,17,1、如果用户要查找的数据中本身就包含了通配符，如SQL_Mail，就需要使用转义字符来区分通配符和实际存在的字符。其格式如下： LIKE 字符匹配串 ESCAPE 转义字符 如：查找对象名称为SQL_M开头，il结尾，中间有一个不确定字符的对象 SELECT * FROM objects WHERE object_name LIKE SQL#_M_il ESCAPE # 说明：这里使用了两个下划线符号，前一个由于有ESCAPE字符作标识，因而被认为是实际存在的下划线符号字符；后一个没有此标识，故被作为通配符。,18,2、使用来将通配符指定为普通字符： 如： SELECT * FROM object WHERE column1 LIKE %54% 它将返回所有包含54%的字符串。,19,【例3-14】求缺少了成绩的学生的学号和课程号。 SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL; Is null：判断取值为空。 Not is null：判断取值不为空。,20,2. 连接查询,连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接。 (1) 等值连接和非等值连接 表名1.列名1比较运算符表名2.列名2 比较运算符：=、=、= 和!=；列名称为连接字,【例5-15】查询每个学生的情况以及他(她)所选修的课程。 SELECT 学生.*,选课.* FROM 学生,选课 WHERE 学生.学号=选课.学号;,21,(2) 自身连接在一个表内进行自身的连接 例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。,课程的先行关系链为：C5C4C3C2C1， 课程的间接关系链为：C5C3C1。,22,【例3-18】查询每一门课的间接先行课。 SELECT A.课程号,A.课程名,B.先行课 FROM 课程 A,课程 B WHERE A.先行课=B.课程号,A,B,结果,23,(3) 外部连接,左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接符号为“*=”，右外部连接符号为“=*”。外部连接中不匹配的分量用NULL表示。,24,连接的结果集,25,【例3-19】用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句,内连接： SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门= 部门.部门号； 左外部连接： SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门*= 部门.部门号； 右外部连接： SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门 =*部门.部门号；,左外连接,右外连接,等价于： SELECT 职工.*,部门名称,电话 FROM 职工 LEFT JOIN 部门 ON （职工.所在部门=部门.部门号）,26,3. 嵌套查询（又称子查询）,嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,书写：上层 下层 处理：下层 上层,27,例3-20：查询与刘晨在同一个系的学生。,查询过程： （1）确定刘晨所在的系，即执行子查询； SELECT 所在系 FROM 学生 WHERE 姓名=刘晨,（2）在子查询的结果中查找所有在此系学习的学生。 SELECT * FROM 学生 WHERE 所在系 IN (计算机系),结果为：计算机系,(1) 使用In（或 not In）操作符的嵌套查询,28,SQL查询代码： SELECT * FROM 学生 WHERE 所在系 IN (SELECT 所在系 FROM 学生 WHERE 姓名=刘晨)；,思考：如果在查询结果中不出现刘晨同学， 应如何修改代码？,SELECT * FROM 学生 WHERE 所在系 = (SELECT 所在系 FROM 学生 WHERE 姓名=刘晨)；,(1) 使用IN操作符的嵌套查 【例3-20】求选修了数据结构的学生学号和姓名。 执行步骤： （1）在课程表中求出数据库课程的课程号； （2）根据找出的课程号在选课表中找出学了这门课程的学号； （3）根据学号在学生表中找出其姓名 SELECT 学号,姓名 FROM 学生 WHERE 学号 IN ( SELECT 学号 FROM 选课 WHERE 课程号 IN ( SELECT 课程号 FROM 课程 WHERE 课程名=数据结构);,找出选修了数据结构的学号,找出这个学号对应的学生信息,找出选修了数据结构的课程号,可用“=”代替,不可用“=”代替,30,该题也可以使用下面的连接查询表达。 SELECT 学生.学号,姓名 FROM 学生,课程,选课 WHERE 学生.学号=课程.学号 AND 课程.课程号 =选课.课程号 AND 课程.课程名=高等数学; 注： 子查询的限制 不能使用ORDER BY子句 层层嵌套方式反映了 SQL语言的结构化 有些嵌套查询可以用连接运算替代,31,(2) 使用比较符的嵌套查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。 与ANY或ALL谓词配合使用,注意：使用嵌套查询进行比较测试时，要求嵌套 语句必须是返回单值的查询语句,32,【例3-21】求C1课程的成绩高于刘晨的C1课程成绩的学生学号和成绩。,分析： （1）在学生表中找出刘晨的学号； （2）在选课表中找出刘晨的C1课程的成绩； （3）最后在选课表中求出C1课程成绩大于刘晨的学生学号和成绩。,使用2层嵌套： 第一层嵌套使用（大于）操作符号； 第二层嵌套使用（等于）操作符号,33,SELECT 学号，成绩 FROM 选课 WHERE 课程号=C1 AND 成绩 ( SELECT 成绩 FROM 选课 WHERE 课程号=C1 AND 学号= (SELECT 学号 FROM 学生 WHERE 姓名=刘晨)；,找出大于这个成绩的学生学号、成绩,找出刘晨的C1课程的成绩,找出刘晨的的学号,可用“in”代替,34,In操作符与比较操作符的区别,In操作符用于一个值与多个值的比较； 比较操作符用于一个值与另一个值之间的比较，当比较符后面的值需要通过查询才能得到时，就需要使用比较符嵌套查询。,(3) 使用ANY或ALL操作符的嵌套查询,格式为：字段比较符ANY|ALL子查询,【例3-22】求其他系中比计算机系某一学生年龄小的学生。 SELECT * FROM 学生 WHERE 年龄 计算机系;,找出计算机系学生年龄,找出比其中一个年龄小的学生,【例3-23】求其他系中比计算机系学生年龄都小的学生。,SELECT * FROM 学生 WHERE 年龄 计算机系；,37,(4) 使用EXISTS操作符的嵌套查询,【例3-24】求选修了C2课程的学生姓名。 SELECT 姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)；,SELECT 姓名,课程.课程号 FROM 学生,课程,选课 WHERE 课程.课程号=C2 AND 课程.课程号=选课.课程号 AND 学生.学号=选课.学号,EXISTS代表存在量词 。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。,38,【例3-25】求没有选修C2课程的学生姓名。,SELECT 姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)；,39,例子,【例3-26】查询选修了全部课程的学生的姓名。 SELECT 姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 课程 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程.课程号=课程号)；,40,【例3-27】求至少选修了学号为“0102”的学生所选修的全部课程的学生学号和姓名。 SELECT 学号,姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课 选课1 WHERE 选课1.学号=0102 AND NOT EXISTS (SELECT * FROM 选课 选课2 WHERE 学生.学号=选课2.学号 AND 选课2.课程号=选课1.课程号);,未选“0102”学生所选课的记录,不存在,41,【例3-30】求选修了C1课程但没有选修C2课程的学生学号。,SELECT 学号 FROM 选课 WHERE 课程号=C1 MINUS SELECT 学号 FROM 选课 WHERE 课程号=C2；,本例也可以用下面的EXISTS嵌套查询表示。,SELECT 学号 FROM 选课 选课1 WHERE 课程号=C1 AND NOT EXISTS (SELECT 学号 FROM 选课 选课2 WHERE 选课1.学号=选课2.学号 AND 选课2.课程号=C2)；,42,课堂练习,1.假设一个数据库中有关系： 产品关系 P（pno，pn，pr，ps），属性的含义依次为产品号、品名、单价和供应商；分别用关系代数表达式和SQL语句写出下列查询： 检索供应商“希望”公司产品的产品号、产品名和单价。 2假设一个数据库中有关系： 订单关系 R（rno，cno，pno，rd，qty）其属性的含义依次为订单号、客户号、产品号、日期和数量。规定一张订单只能订购一种产品。 用SQL语句写出下列查询： 检索每种产品的订单个数和订购产品的总量。,43,1. 关系代数：pno,pn,pr(ps=希望(P) SELECT pno,pn,pr FROM P WHERE ps=希望 2.SELECT count(rno),sum(qty) FROM R GROUP BY pno,44,4. 使用分组和SQL函数查询,【例3-31】求学生的总人数。 SELECT COUNT (*) FROM 学生；,45,例子,【例3-32】求选修了课程的学生人数。 SELECT COUNT(DISTINCT 学号) FROM 选课；,【例3-33】求课程和选修该课程的人数。,【例3-34】求选修课超过3门课的学生学号。,SELECT 课程号，COUNT(学号) FROM 选课 GROUP BY 课程号；,SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(