数据库应用技术教程第三章深入SQL.ppt

数据库应用技术 第三章 深入SQL 1/81 3.1 SQL概述 vSQL的历史 1970, Structured English Query Language (“SEQUEL“)， IBM 1979, Oracle SQL的标准化过程 v从SQL-86(ANSI)到SQL-2003（ANSI, ISO） vSQL的设计初衷是给高级用户提供一种通用 的、易于学习和理解的数据库操作方式。 v演变为给程序员使用的数据库标准接口。 2/81 SQL的特点 v面向集合的操作方式，是关系代数的实现； v高度非过程化。只需要提出“What”，不需要 指出“How”； v上下文无关，运行结果取决于数据； v语言风格统一，类自然语言，简单易学； v既是自含式语言，又是嵌入式语言 3/81 SQL的分类 v查询（Data Query Language, DQL ） SELECT v操纵（Data Manipulate Language, DML ） INSERT, DELETE, UPDATE v定义（Data Definition Language, DDL ） CREATE, DROP, ALTER。 v控制（Data Control Language, DCL ） GRANT, REVOKE, COMMIT,ROLLBACK。 4/81 SQL的基本语法 vSQL语句不区分大小写，可以写在多行，各 个单词之间使用分隔符（空格，回车，制表 符）分隔。 vSQL语句中的关键字属于保留字，不能用于 其他地方。 v数字常量的写法和通常程序语言一致，字符 串使用单引号包含，字符串中的单引号使用 两个连续的单引号转义表示，不区分字符和 字符串。 5/81 SQL书写规范 v为便于书写和阅读，通常采用如下的书写规 范： v1、关键字大写，其他标识符小写 字段名大写？ v2、每个子句单起一行 v3、使用制表符缩进 6/81 标准与可移植性 v在SQL92标准中定义了四种级别：Entry, Transitional, Intermediate, and Full v国际标准与各个厂商之间的不兼容性。 v事实上的不可移植性 字符串连接|与+； 专有的查询关键字 数据类型与函数 特殊语义； v 可移植性神话 7/81 3.2 简单SELECT语句 8/81 3.2.1 基本语句 v语法 SELECT DISTINCT | ALL * | 查询项列表 FROM 表名; v说明 查询项：字段|表达式 AS 别名 无条件：查询出表的所有记录。次序不确定、不稳定， 依赖于数据库的实现。 *：按特定顺序列举所有字段。 9/81 3.2.1 基本语句 当明确要查询哪些字段的时候，将字段名称列在SELECT 的后面，这样语句含义清楚，性能也有所提高。 AS 别名 ：别名起到简化作用，通常还作为表达式的标 题。特别是很多使用SQL的开发工具直接使用SELECT语 句中每个查询表达式的名称作为标识符，这种情况下更 需要给出别名。表达式与别名之间的AS是一个冗余的关 键字，可以省略，直接以空白符分隔。 DISTINCT | ALL：在整个查询内容前加上DISTINCT，返 回结果中不重复的内容。不重复针对所有要查询的表达 式集合而言。ALL是缺省值，列出包括重复的所有记录。 另外，DISTINCT是一个比较费时间的操作，使用时需慎 重。 10/81 3.2.1 基本语句 v查询的内容可以是字段，也可以是由字段、常数通 过运算符、函数构成的复杂表达式。这种计算要对 表上的所有行都施加运算。 SELECT sid, UPPER(sname), salary + 100 FROM sales; 11/81 3.2.1 基本语句 v在某些情况下，也可能使用SQL计算纯粹常量表达 式的值，如检索服务器当前时间。 SELECT SYSDATE, 1+2 FROM sales; 依赖于表中记录的数目，将得到若干行（也可能 是0行）同样的结果。而这一般不是我们想要得 到的结果。 12/81 3.2.1 基本语句 v如果明确地只想得到一行结果 在MS-SQL和MySQL中，可以使用不带FROM子句的 SELECT语句； vSELECT 1+2 在Oracle中，不允许没有FROM子句。 系统提供了一个名叫dual的表，保证其中只有一条记录， 不依赖于表的计算可以使用这个表。 vSELECT SYSDATE, 1+2 FROM dual; 13/81 3.2.2 筛选 v可以使用WHERE子句来筛选出满足某些条件的记 录。 vWHERE 逻辑表达式 基本比较运算符 v=，=， v它们都具有通常的含义。比较运算符的两端都可以是字段或者表达式。 其他相当于比较运算符的结构 vBETWEEN AND vIN vLIKE vNULL的运算 逻辑运算符 vNOT AND OR 14/81 其他where条件 v1、BETWEEN AND v如果要限定某个表达式的值在某个区间内，可以使用这个关键字。 例：查询出薪水在1000和2000之间的记录的sid和salary值。 SELECT sid, salary FROM sales WHERE salary BETWEEN 1000 AND 2000; 这是一个闭区间，等于两端值的记录也会被查出来。另外，一般要 求区间前面的值小于后面的值，否则将查不出结果。 v2、IN IN运算符用来检验某个表达式的值是否包含在一个指定的值集合内。 例：查询出名字叫Tom、Jack或者Mary的销售员的sid和salary值。 SELECT sid, salary FROM sales WHERE sname IN (Tom, Jack, Mary); 15/81 v3、LIKE v如果要对字符串进行通配查找时，需要使用LIKE运算符。它 允许在表达式中使用专门的通配符，表示符合某种规则的结 果。 %：表示可以匹配0个或任意多个字符； _：表示通配一个字符。 v例1：查询出所有名字以S开头的销售员记录，例如Smith、Sa和S等都 符合这个条件。 SELECT sid, salary FROM sales WHERE sname LIKE S%; v例2：查询出来所有名字中含有第一个是a，第三个是b，中间可以是任 意字符的子串的顾客信息。 SELECT * FROM customers WHERE cname LIKE %a_b%; 16/81 如果要查询的通配表达式中含有%或者_做为通常的含义出现，那 么需要使用转义描述。 例3：查询名字以%开头，以_结尾的销售员id和工资。 SELECT sid, salary FROM sales WHERE sname LIKE %_ ESCAPE ; 在LIKE的后面加上ESCAPE关键字说明通配表达式中使用的转义字 符（上例为），则转义字符后面的直接字符不按照通配符对待。 v4、IS NULL 见后 17/81 可以使用逻辑运算符AND，OR，NOT将多个关系表达式连接在一起 ，构成复杂的逻辑表达式。它们具有通常的逻辑含义。在不使用括 号的时候，AND的优先级高于OR运算。 另外，对于一些特别的表达式的否定，可以使用另外的语法。如对IS NULL的否定可以写成IS NOT NULL。 逻辑运算 18/81 空值逻辑 v空值NULL用来表示 未知的(Unknown)，不适用的 (Inapplicable) v不能直接和空值进行比较，需要使用IS NULL和IS NOT NULL。 例4：查询地址非空的顾客信息。 SELECT * FROM customers WHERE address IS NOT NULL; 反例： SELECT * FROM sales WHERE salary 1000 OR salary 1000; SELECT * FROM sales WHERE salary 1000 or salary IS NULL ; 例6：欲发放奖金，为薪水的10%，如果薪水为空，按照1000计算 SELECT NVL(salary, 1000) * 0.1 FROM sales 20/81 3.2.3 聚集函数与分组统计 v聚集函数：对于多条记录统计出一个结果的函数。 包括：COUNT，MAX，MIN，AVG，SUM 聚集函数和NULL的关系 vCOUNT(*)：计数符合条件的所有记录，不考虑是否为空。 vCOUNT(字段)：此字段为空的行不计数在内。 vMAX、MIN：NULL不参与比较。 vAVG、SUM：NULL不参与运算。 聚集函数里面可以使用DISTINCT，只对不重复的字段进行统计。缺 省为ALL 21/81 聚集函数-cont v数据类型 SUM、AVG 只用于数值型。 MAN、MIN可用于数值、字符、日期。 vCount的几种用法 SELECT COUNT (*)返回记录数 SELECT COUNT (字段)字段为空的记录不参与计数 SELECT COUNT (DISTINCT字段)重复字段不参与计数 22/81 分组统计GROUP BY v分组子句GROUP BY 按照GROUP BY条件对记录进行分组，然后进行统计。每一个分组 得出一个结果。 可以使用多个条件，多个条件的次序关系？ v分组筛选子句HAVING 对分组后的每个组按照条件进行筛选； 只能在GROUP BY后面出现。 一般使用聚集函数对分组后数据的统计值进行筛选 直接使用分组字段通常无意义，其他字段非法 23/81 v例1：当前有多少订单。 SELECT COUNT(*) FROM orders; v例2：当前locations内有多少个邮编，不重复数有多少？ SELECT COUNT(postcode), COUNT(DISTINCT postcode) FROM locations; v例3：在locations中有多少个不重复的城市？ SELECT COUNT(DISTINCT city) FROM locations; v例4：在一周之前，有多少个顾客购买过产品？ SELECT COUNT(DISTINCT cid) FROM orders WHERE buy_date 1000; SELECT cid, SUM(dollars) FROM orders WHERE dollars 1000 GROUP BY cid HAVING COUNT(*) 1; SELECT pid, AVG(dollars) FROM orders WHERE qty 1000 GROUP BY pid; 27/81 小结 v返回数据 无GROUP BY子句，带聚集函数的查询一定只返回 一行数据，但是可能是空值。如果表中无数据，或者 无满足条件数据，或者所有符合条件数据都是空， COUNT返回0，其他函数则返回空值。 v带有聚集函数的SQL对查询表达式的要求 未使用GROUP BY，但出现了一个聚集函数，其他 表达式也必须是聚集函数 使用GROUP BY，查询表达式必须是 v使用聚组函数的表达式 v分组字段直接参与的表达式 v其他字段直接出现，为语法错误 HAVING子句的要求同查询表达式 v例9的三种写法 28/81 3.2.4 排序 vORDER BY 排序准则列表 排序准则：字段 | 表达式 | 别名 | 序号 ASC | DESC ASC：升序，缺省值。 DESC：降序。 字段名：不一定在SELECT中列出。 表达式：有些DBMS不支持。 序号：从1开始排。在多个SELECT进行集合运算时，特别适合使用 序号方式。 按照关系数据库的原理来说，ORDER BY只能是SELECT语句的最 后一个子句。 在排序结果中，NULL通常被排在一起，放在结果集的前面或后面（ 依赖于实现或者特别的选项）。 29/81 3.3 复杂的SELECT语句 30/81 3.3.1 连接查询 v将多个表的数据进行笛卡尔积，在此基础上进行查询，称为 连接查询(Join)。 v连接条件：在笛卡尔积的基础上，通常要根据多个表之间相 关联的某些字段之间的判断条件进行筛选，这种多表之间的 筛选条件称为连接条件。 v根据连接条件，可以分为：等值连接(Equal join)、不等值连 接(Non-equal join)、自连接(Self join)和外连接(Outer join) 。 31/81 u例1：找出所有顾客购买的商品名称和数量。 SELECT products.pname, orders.qty FROM products, orders WHERE products.pid = orders.pid; 或简写为： SELECT p.pid, p.pname, o.qty FROM products p, orders o WHERE p.pid = o.pid; u例2：找出住在Dallas或Duluth的顾客购买过产品的产品号。 SELECT o.pid FROM orders o, customers c WHERE o.cid = c.cid AND (c.city = Dollas OR c.city = Duluth); 32/81 自连接 v例3：列出每个销售员及其经理的姓名。（自连接） SELECT s1.sname as 下属, s2.sname as 经理 FROM sales s1, sales s2 WHERE s1.manager = s2.sid; v自连接 对于同一个表的两种不同角色的使用 并不真正复制数据，必须使用表的别名。 33/81 v例4：找出在同一城市居住的顾客对。 SELECT c1.cid, c2.cid FROM customers c1, customers c2 WHERE c1.city = c2.city; v自身配对不列出 vWHERE c1.city = c2.city AND c1.cid 1; 例8：找出与经理在同一城市的销售员。 SELECT s2.sname FROM sales s1, sales s2, locations l1, locations l2 WHERE s1.manager = s2.sid AND s1.lid = l1.lid AND s2.lid = l2.lid AND l1.city = l2.city; 36/81 3.3.2 子查询 v子查询：出现在另外一个SQL语句中的查询。 里面出现的SQL语句也被称为子查询(subquery)或内查询 (inner query) 外面的查询被称为外查询(outer query) v子查询出现的位置 一般出现在where子句中 按照不同的扩展语法，也可以出现在select、having和 from子句中 37/81 使用子查询的方法 v在where子句使用子查询，有以下几种方法 1.字段表达式 比较运算符 (subquery) 2.字段表达式 IN (subquery) 3.字段表达式 比较运算符 量词 (subquery) 4.NOT EXISTS (subquery) 38/81 1、直接使用子查询 v如果子查询返回一个数据，则可以作为直接数参与 普通比较运算。 例1：查询与编号为A01的销售员工资相同的其他人。 SELECT * FROM sales WHERE salary = (SELECT salary FROM sales WHERE sid = a01); 39/81 v例2：查询比Smith工资高的销售员信息。 SELECT * FROM sales WHERE salary (SELECT salary FROM sales WHERE sname = Smith); （需要保证只有一个叫 Smith的人） 40/81 v例3：查询比平均工资高的人员信息。 SELECT * FROM sales WHERE salary (SELECT AVG(salary) FROM sales); v例4：销售量第二高的单笔销售额。 SELECT MAX(dollars) FROM orders WHERE dollars ALL(SELECT salary FROM sales WHERE lid = L01); 44/81 例6：求通过居住在L01的销售员购买货物的顾客ID。 SELECT cid FROM orders WHERE sid = SOME (SELECT sid FROM sales WHERE lid = L01); 45/81 例7：薪水最高的sid及姓名。 SELECT sid, sname FROM sales WHERE salary = (SELECT MAX(salary) FROM sales); 或者 WHERE SALARY = ALL(SELECT salary FROM sales); 46/81 3、使用IN v除了使用量词外，也可以使用IN进行比较。 IN等价于 = SOME， NOT IN 等价于 SOME无意义 SOME (=) (SELECT MIN() ) ALL (=) (SELECT MAX() ) (SELECT COUNT(*) FROM ranks r2 WHERE r2.score (SELECT COUNT(DISTINCT score) FROM ranks r2 WHERE r2.score = (SELECT COUNT(*) FROM ranks r2 WHERE r2.score = (SELECT COUNT(*) FROM ranks r2 WHERE r2.score r2.score GROUP BY r1.id HAVING COUNT(*) (SELECT COUNT(*) FROM ranks r2 WHERE r2.score (SELECT COUNT(*) FROM ranks r2 WHERE r2.score r1.score OR (r2.score = r1.score AND r2.id r1.id) ORDER BY score, id; idscoreCOUNT(* ) 110 221 322 423 534 635 746 847 958 1059 73/81 3.5 DML语句 74/81 3.5.1 INSERT vINSERT:插入记录 INSERT INTO 表名 (字段列表) VALUES (值列表); v这种形式的INSERT语句一次只能插入一行。值列表要和字 段列表对应，数量和类型。 v当值列表与表定义顺序一致时，可以不给字段列表。 INSERT INTO 表名 (字段列表) 子查询; v可以利用子查询一次插入多行，子查询的结果列表要和字 段列表对应。 对于不在字段列表中的字段，按以下步骤决定其值： v表格定义时字段指定缺省值，置为缺省值。 v字段可以为空，置为NULL值。 v否则会出错。 v另外，可以在值列表中使用DEFAULT和NULL关键字。 75/81 INSERT INTO customers(discnt, cname, city, cid) VALUES (12, Basics, Dallas, C02); 例1：已创建一个新表sales_copy，定义与sales相同，现要将sales 中内容复制到里面。 INSERT INTO sales_copy SELECT * FROM sales; 例2：经过一段时间之后，sales中内容有所变化，现要将新填内容放 入sales_copy中。（假设sid不发生变化） INSERT INTO sales_copy SELECT * FROM sales WHERE cid NOT IN (SELECT cid FROM sales_copy); 76/81 3.5.2 DELETE vDELETE：用来删除表中一行或多行记录。 DELETE FROM 表名 WHERE 条件; 将表中符合条件的记录删除，如果不写WHERE条件，将删除 所有记录。 在条件中可以使用子查询。 例3：对于sales表中已经不存在的内容，将sales_copy表中对 应记录删除。 DELETE FROM sales_copy WHERE sid NOT IN (SELECT sid FROM sales); 77/81 3.5.3 UPDATE vUPDATE：用来更新表中一行或多行记录。 UPDATE 表名 SET 字段=表达式 , 字段=表达式 WHERE 条件; 将表中符合条件的记录的相应字段按照表达式重新赋值。表达 式中可以使用原值。 在条件和修改表达式中可以使用子查询。 例4：按照sales中的值修改sal