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

1、1数据库应用技术第三章 深入SQL23.1 SQL概述vSQL的历史1970, Structured English Query Language (SEQUEL)， IBM1979, OracleSQL的标准化过程v从SQL-86(ANSI)到SQL-2003（ANSI, ISO）vSQL的设计初衷是给高级用户提供一种通用的、易于学习和理解的数据库操作方式。v演变为给程序员使用的数据库标准接口。3SQL的特点v面向集合的操作方式，是关系代数的实现；v高度非过程化。只需要提出“What”，不需要指出“How”；v上下文无关，运行结果取决于数据；v语言风格统一，类自然语言，简单易学；v既是自含式

2、语言，又是嵌入式语言4SQL的分类v查询（Data Query Language, DQL ）SELECTv操纵（Data Manipulate Language, DML ）INSERT, DELETE, UPDATEv定义（Data Definition Language, DDL ）CREATE, DROP, ALTER。v控制（Data Control Language, DCL ）GRANT, REVOKE, COMMIT,ROLLBACK。5SQL的基本语法vSQL语句不区分大小写，可以写在多行，各个单词之间使用分隔符（空格，回车，制表符）分隔。vSQL语句中的关键字属于保留字，不

3、能用于其他地方。v数字常量的写法和通常程序语言一致，字符串使用单引号包含，字符串中的单引号使用两个连续的单引号转义表示，不区分字符和字符串。6SQL书写规范v为便于书写和阅读，通常采用如下的书写规范：v1、关键字大写，其他标识符小写字段名大写？v2、每个子句单起一行v3、使用制表符缩进7标准与可移植性v在SQL92标准中定义了四种级别：Entry, Transitional, Intermediate, and Fullv国际标准与各个厂商之间的不兼容性。v事实上的不可移植性字符串连接|与+；专有的查询关键字数据类型与函数特殊语义；v 可移植性神话83.2 简单SELECT语句93.2.1 基

4、本语句v语法SELECT DISTINCT | ALL * | 查询项列表 FROM 表名;v说明查询项：字段|表达式 AS 别名 无条件：查询出表的所有记录。次序不确定、不稳定，依赖于数据库的实现。*：按特定顺序列举所有字段。103.2.1 基本语句当明确要查询哪些字段的时候，将字段名称列在SELECT的后面，这样语句含义清楚，性能也有所提高。AS 别名 ：别名起到简化作用，通常还作为表达式的标题。特别是很多使用SQL的开发工具直接使用SELECT语句中每个查询表达式的名称作为标识符，这种情况下更需要给出别名。表达式与别名之间的AS是一个冗余的关键字，可以省略，直接以空白符分隔。DISTIN

5、CT | ALL：在整个查询内容前加上DISTINCT，返回结果中不重复的内容。不重复针对所有要查询的表达式集合而言。ALL是缺省值，列出包括重复的所有记录。另外，DISTINCT是一个比较费时间的操作，使用时需慎重。113.2.1 基本语句v查询的内容可以是字段字段，也可以是由字段、常数通过运算符、函数构成的复杂表达式复杂表达式。这种计算要对表上的所有行都施加运算。SELECT sid, UPPER(sname), salary + 100FROM sales;123.2.1 基本语句v在某些情况下，也可能使用SQL计算纯粹常量表达式的值，如检索服务器当前时间。SELECT SYSDATE,

6、 1+2FROM sales;依赖于表中记录的数目，将得到若干行（也可能是0行）同样的结果。而这一般不是我们想要得到的结果。133.2.1 基本语句v如果明确地只想得到一行结果在MS-SQL和MySQL中，可以使用不带FROM子句的SELECT语句；vSELECT 1+2在Oracle中，不允许没有FROM子句。系统提供了一个名叫dual的表，保证其中只有一条记录，不依赖于表的计算可以使用这个表。vSELECT SYSDATE, 1+2 FROM dual;143.2.2 筛选v可以使用WHERE子句来筛选出满足某些条件的记录。vWHERE 逻辑表达式基本比较运算符v=，=，=，v它们都具有通

7、常的含义。比较运算符的两端都可以是字段或者表达式。其他相当于比较运算符的结构vBETWEEN ANDvINvLIKEvNULL的运算逻辑运算符vNOT AND OR15其他where条件v1、BETWEEN AND v如果要限定某个表达式的值在某个区间内，可以使用这个关键字。例：查询出薪水在1000和2000之间的记录的sid和salary值。SELECT sid, salaryFROM salesWHERE salary BETWEEN 1000 AND 2000;这是一个闭区间闭区间，等于两端值的记录也会被查出来。另外，一般要求区间前面的值小于后面的值，否则将查不出结果。v2、ININ运算

8、符用来检验某个表达式的值是否包含在一个指定的值集合内。例：查询出名字叫Tom、Jack或者Mary的销售员的sid和salary值。SELECT sid, salaryFROM salesWHERE sname IN (Tom, Jack, Mary);16v3、LIKEv如果要对字符串进行通配查找时，需要使用LIKE运算符。它允许在表达式中使用专门的通配符，表示符合某种规则的结果。%：表示可以匹配0个或任意多个字符；_：表示通配一个字符。v例1：查询出所有名字以S开头的销售员记录，例如Smith、Sa和S等都符合这个条件。SELECT sid, salaryFROM salesWHERE s

9、name LIKE S%;v例2：查询出来所有名字中含有第一个是a，第三个是b，中间可以是任意字符的子串的顾客信息。SELECT *FROM customersWHERE cname LIKE %a_b%;17如果要查询的通配表达式中含有%或者_做为通常的含义出现，那么需要使用转义描述。例3：查询名字以%开头，以_结尾的销售员id和工资。SELECT sid, salaryFROM salesWHERE sname LIKE %_ ESCAPE ;在LIKE的后面加上ESCAPE关键字说明通配表达式中使用的转义字符（上例为），则转义字符后面的直接字符不按照通配符对待。v4、IS NULL见后1

10、8可以使用逻辑运算符AND，OR，NOT将多个关系表达式连接在一起，构成复杂的逻辑表达式。它们具有通常的逻辑含义。在不使用括号的时候，AND的优先级高于OR运算。另外，对于一些特别的表达式的否定，可以使用另外的语法。如对IS NULL的否定可以写成IS NOT NULL。逻辑运算19空值逻辑v空值NULL用来表示 未知的未知的(Unknown)，不适用的，不适用的(Inapplicable)v不能直接和空值进行比较，需要使用IS NULL和IS NOT NULL。例4：查询地址非空的顾客信息。SELECT * FROM customersWHERE address IS NOT NULL;反例

11、：SELECT * FROM salesWHERE salary 1000 OR salary 1000;SELECT * FROM salesWHERE salary 1000 or salary IS NULL ;例6：欲发放奖金，为薪水的10%，如果薪水为空，按照1000计算SELECT FROM sales213.2.3 聚集函数与分组统计v聚集函数：对于多条记录统计出一个结果的函数。包括：COUNT，MAX，MIN，AVG，SUM聚集函数和NULL的关系vCOUNT(*)：计数符合条件的所有记录，不考虑是否为空。vCOUNT(字段)：此字段为空的行不计数在内。vMAX、MIN：NUL

12、L不参与比较。vAVG、SUM：NULL不参与运算。聚集函数里面可以使用DISTINCT，只对不重复的字段进行统计。缺省为ALL22聚集函数-contv数据类型SUM、AVG 只用于数值型。MAN、MIN可用于数值、字符、日期。vCount的几种用法SELECT COUNT (*)返回记录数SELECT COUNT (字段)字段为空的记录不参与计数SELECT COUNT (DISTINCT字段)重复字段不参与计数23分组统计GROUP BYv分组子句GROUP BY按照GROUP BY条件对记录进行分组，然后进行统计。每一个分组得出一个结果。可以使用多个条件，多个条件的次序关系？v分组筛选子

13、句HAVING对分组后的每个组按照条件进行筛选；只能在GROUP BY后面出现。一般使用聚集函数对分组后数据的统计值进行筛选直接使用分组字段通常无意义，其他字段非法24v例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：在一周之前，有多少个顾

14、客购买过产品？SELECT COUNT(DISTINCT cid)FROM ordersWHERE buy_date 1000;SELECT cid, SUM(dollars)FROM ordersWHERE dollars 1000GROUP BY cidHAVING COUNT(*) 1;SELECT pid, AVG(dollars)FROM ordersWHERE qty 1000GROUP BY pid;28小结v返回数据无GROUP BY子句，带聚集函数的查询一定只返回一行数据，但是可能是空值。如果表中无数据，或者无满足条件数据，或者所有符合条件数据都是空，COUNT返回0，其他函

15、数则返回空值。v带有聚集函数的SQL对查询表达式的要求未使用GROUP BY，但出现了一个聚集函数，其他表达式也必须是聚集函数使用GROUP BY，查询表达式必须是v使用聚组函数的表达式v分组字段直接参与的表达式v其他字段直接出现，为语法错误HAVING子句的要求同查询表达式v例9的三种写法293.2.4 排序vORDER BY 排序准则列表排序准则：字段 | 表达式 | 别名 | 序号 ASC | DESCASC：升序，缺省值。DESC：降序。字段名：不一定在SELECT中列出。表达式：有些DBMS不支持。序号：从1开始排。在多个SELECT进行集合运算时，特别适合使用序号方式。按照关系数据

16、库的原理来说，ORDER BY只能是SELECT语句的最后一个子句。在排序结果中，NULL通常被排在一起，放在结果集的前面或后面（依赖于实现或者特别的选项）。303.3 复杂的SELECT语句313.3.1 连接查询v将多个表的数据进行笛卡尔积，在此基础上进行查询，称为连接查询(Join)。v连接条件：在笛卡尔积的基础上，通常要根据多个表之间相关联的某些字段之间的判断条件进行筛选，这种多表之间的筛选条件称为连接条件。v根据连接条件，可以分为：等值连接(Equal join)、不等值连接(Non-equal join)、自连接(Self join)和外连接(Outer join)。32u例1：找

17、出所有顾客购买的商品名称和数量。FROM products, ordersWHERE products.pid = orders.pid;或简写为：FROM products p, orders oWHERE p.pid = o.pid;u例2：找出住在Dallas或Duluth的顾客购买过产品的产品号。SELECT o.pid FROM orders o, customers cWHERE o.cid = c.cid AND (c.city = Dollas OR c.city = Duluth);33自连接v例3：列出每个销售员及其经理的姓名。（自连接）SELECT s1.sname as

18、 下属, s2.sname as 经理FROM sales s1, sales s2WHERE s1.manager = s2.sid;v自连接对于同一个表的两种不同角色的使用并不真正复制数据，必须使用表的别名。34v例4：找出在同一城市居住的顾客对。FROM customers c1, customers c2WHERE c1.city = c2.city;v自身配对不列出vWHERE c1.city = c2.city AND c1.cid c2.cid;v不重复列出(a,b)和(b,a)vWHERE c1.city = c2.city AND c1.cid a1.salary;v当有多个

19、smith时，该问题的答案v例6：写出购买过商品的顾客姓名和产品名称。FROM customers c, products p, orders oWHERE o.cid = c.cid AND o.pid = p.pid;v一般来说，N个表连接需要N-1个连接条件。36例7：写出购买过单价超过1元的产品的顾客名单。（多表连接）SELECT ame FROM orders o, customers c, products pWHERE o.cid = c.cid AND o.pid = p.pid AND p.price 1;例8：找出与经理在同一城市的销售员。FROM sales s1, sa

20、les s2, locations l1, locations l2WHERE s1.manager = s2.sid AND s1.lid = l1.lid AND s2.lid = l2.lid AND l1.city = l2.city;373.3.2 子查询v子查询：出现在另外一个SQL语句中的查询。里面出现的SQL语句也被称为子查询(subquery)或内查询(inner query)外面的查询被称为外查询(outer query)v子查询出现的位置一般出现在where子句中按照不同的扩展语法，也可以出现在select、having和from子句中38使用子查询的方法v在where子

21、句使用子查询，有以下几种方法字段表达式 比较运算符 (subquery)字段表达式 IN (subquery)字段表达式 比较运算符 量词 (subquery)1.NOT EXISTS (subquery)391、直接使用子查询v如果子查询返回一个数据，则可以作为直接数参与普通比较运算。例1：查询与编号为A01的销售员工资相同的其他人。SELECT *FROM sales WHERE salary = (SELECT salaryFROM salesWHERE sid = a01);40v例2：查询比Smith工资高的销售员信息。SELECT *FROM sales WHERE salary

22、(SELECT salary FROM sales WHERE sname = Smith); （需要保证只有一个叫Smith的人）41v例3：查询比平均工资高的人员信息。SELECT *FROM salesWHERE salary (SELECT AVG(salary) FROM sales);v例4：销售量第二高的单笔销售额。SELECT MAX(dollars)FROM ordersWHERE dollars ALL(SELECT salary FROM sales WHERE lid = L01);45例6：求通过居住在L01的销售员购买货物的顾客ID。SELECT cidFROM o

23、rdersWHERE sid = SOME (SELECT sid FROM salesWHERE lid = L01);46例7：薪水最高的sid及姓名。SELECT sid, sname FROM sales WHERE salary = (SELECT MAX(salary) FROM sales); 或者WHERE SALARY = ALL(SELECT salary FROM sales);473、使用INv除了使用量词外，也可以使用IN进行比较。IN等价于 = SOME， NOT IN 等价于 ALL。v例8：没有订过货的顾客ID和姓名。SELECT cid, cnameFROM

24、customersWHERE cid NOT IN(SELECT cid FROM orders); 48例9：找出工作地点和薪金与Smith相同的其他人员。SELECT * FROM salesWHERE lid IN (SELECT lid FROM sales WHERE sname = Smith) AND salary IN (SELECT salary FROM sales WHERE sname = Smith); 当有多个Smith时，上面语句出错49v例9：找出工作地点和薪金与Smith相同的其他人员在Oracle中可以写作：SELECT * FROM salesWHERE

25、(lid, salary) IN (SELECT lid, salary FROM sales WHERE sname = Smith);还可以写为：WHERE lid | | salary IN (SELECT lid | | salary FROM sales WHERE sname = Smith);另外一种写法：SELECT * FROM sales aWHERE EXIST (SELECT * FROM sales WHERE sname = Smith AND lid = a.lid AND salary = a.salary);50例10：找出住在Duluth的顾客通过位于L01

26、的代理订货的订单号。写SQL51v例10：找出住在Duluth的顾客通过位于L01的代理订货的订单号。SELECT ordnoFROM ordersWHERE cid IN (SELECT cid FROM customers WHERE city = Duluth) AND sid IN (SELECT sid FROM sales WHERE lid = L01);在Oracle中可以写作SELECT ordnoFROM ordersWHERE (cid, sid) IN (SELECT cid, sid FROM customers c, sales a WHERE c.city = D

27、uluth AND a.lid = L01);52量词与其他形式之间的转换v很多情况下，比较运算符跟一个量词可以转换为其他的写法。如：比较运算符比较运算符其他写法其他写法= SOMEIN= ALL无意义 SOME无意义 ALLNOT IN SOME (=) (SELECT MIN() ) SOME (=) ALL (=) (SELECT MAX() ) ALL (=) (SELECT MIN() )IN可以转换为EXIST534、EXISTSv使用EXISTS和NOT EXISTS判断子查询是否返回行（不考虑具体数据）。通常将外部的数据引入到子查询内部作为条件。例11：没有通过A05订货的顾客

28、姓名。FROM customers cWHERE NOT EXISTS(SELECT *FROM orders oWHERE sid = A05 AND o.cid = c.cid);该语句同时也会返回从未订过货的顾客，若想滤掉可以： FROM orders o1, customers cWHERE o.cid = c.cid ANDNOT EXISTS (SELECT * FROM orders o2 WHERE sid = A05 AND o2.cid = c.cid);54使用NOT EXIST进行For-All查询例12：在所有销售员处都订过货的顾客ID。思考！写SQL。Select

29、distinct cid From customer cWhere not exists(select *from sale a where not exist(Select*from orders o where o.sid=c.sid and o.cid=c.cid) );55使用NOT EXIST进行For-All查询例12：在所有销售员处都订过货的顾客ID。对于顾客cid，不存在他没订过货的销售员。SELECT DISTINCT cid FROM customers cWHERE NOT EXISTS(SELECT * FROM sales a WHERE NOT EXISTS(SEL

30、ECT * FROM orders o WHERE o.sid = AND o.cid = c.cid );56子查询的使用建议v一般不需要DISTINCTv一般不需要排序逻辑表达式逻辑表达式运算符运算符INNOT IN , e op xSOME，ANY , e op xALLEXISTSNOT EXISTSeSeSxS xS SS573.3.3 集合运算vSELECT查询出来的结果是一个集合，两个查询语句的结果可以进行集合运算。这时两个查询的来源可以没有任何关系，只要求查询表达式的数目数目和对应数据类型数据类型相一致。58v集合运算有如下几种并集运算：取两个查询结果的并集。使用UNION关键

31、字。v例1：SELECT city FROM locationsUNIONSELECT city FROM customers;这个操作将合并完全相同的数据形成一个结果记录。如果要保留重复的记录，需要使用 UNION ALL代替UNION。交集运算：取两个查询结果的交集，使用INTERSECT关键字。差集运算：取两个查询结果的差集。使用MINUS（Oracle）或者EXCEPT（MSSql）关键字。v可以对集合运算的总体结果进行排序，在语句的最后面写ORDER BY。这种情况下使用序号作标识是比较方便的。593.3.4 其他高级SELECT语言v一、树状查询v二、灵活的子查询603.3.4 S

32、ELECT语句小结v如何理解SELECT 语句的执行过程？ SELECT FROM WHERE 简单比较，字段IN()，连接条件。组合条件 GROUP BY HAVING ORDER BYDB先看FROM子句，若多表则构造笛卡尔积，得到所有行；循环处理每个记录，判断时满足WHERE子句条件；若有子查询，则进行内层循环，外层变量此时做常量处理；若有GROUP BY子句，记录分组，对每一组视为一个整体，判断是否满足HAVING子句条件；对所有选出的记录集筛选出SELECT子句所需字段；最后一步处理ORDER BY子句。61SQL实际执行时需要进行语法分析，产生执行计划，对执行方式进行优化。一般说来

33、，v尽量使用连接而不是子查询，特别是子查询内部使用外查询字段值的；v尽量不把子查询作为表使用；v没有必要不要使用DISTINCT，GROUP BY，ORDER BY；v子查询中不可有ORDER BY子句。SQL的能力vSQL对关系代数是先备的，但不是可计算性的，原因是SQL非过程化。PL/SQL加入过程控制。SQL能力缺陷v聚组函数有限；v树状查询（闭包查询），ORACLE中有一个子句v文本检索 如：搜索，关键字5个出现2个（10）即可623.4 TOP-N问题vTOP-N问题是一个在实践中经常遇到的典型问题。v假设有一个表ranks，其中包含一个主键字段id，一个值字段score。问题是，按

34、照值字段的次序只查询出排名在某个范围的记录。具体区分有如下几种：M1：按照score（增序）排序，排在最前面N位的记录（基本的TOP-N问题）。如果值字段出现重复（并列）时，也要准确地取出N条记录。M2：按照score （增序）排序，排在最前面N位的记录，并且包含所有与第N条等值的记录（返回记录数目可能大于N）。M3：按照score （增序）排序，返回score不同值的出现位于前N个的所有记录。M4：按照score （增序）排序，排名在N1到N2之间的记录。（广义的TOP-N问题），一般不考虑并列，只考虑记录数目。v如网站数据分页浏览63专用方案-MySqlvMySql对此类问题的支持最为充分

35、。vSELECT语句的选项LIMIT (m,) n如果只有一个参数N，将只返回查询的前N个结果；如果有两个参数m和n，将返回结果集中从第m条记录（从零开始计数）开始的n个记录。配合ORDER BY子句就可以完成前面提出的问题。v例子例1：M1问题的MySql解。SELECT * FROM ranks ORDER BY score LIMIT 3;例2：M4问题的MySql解。SELECT * FROM ranks ORDER BY score LIMIT 2, 3;v在MySql中没有直接专用方法解决M2和M3问题。64专用方案-MSSQLv在MSSql中，可以在SELECT中使用TOP N选

36、项，其含义是返回查询的前N行。例3：M1问题的MSSql解。SELECT TOP 3 FROM ranks ORDER BY score; 例4：M2问题的MSSql解。SELECT TOP 3 WITH TIES * FROM ranks ORDER BY score;v通过子查询来解决M4问题例5：M4问题的MSSql解。SELECT TOP 3 * FROM ranks WHERE id NOT IN (SELECT TOP 2 id FROM ranks ORDER BY score) ORDER BY score;v在MSSql中没有直接专用方法解决M3问题。65专用方案-Oracl

37、evOracle对此类问题的支持比较弱。vOracle中提供了一个伪列（pseudo-column）ROWNUM，其使用语法如同一个字段一样，但并不是表中字段，因此称之为伪列。ROWNUM表示当前返回的记录是整个结果集合的第几个（从1开始计数）。因此可以写出这样的例子：例6：M1问题在Oracle中一个错误的解。SELECT * FROM ranksWHERE ROWNUM = 3ORDER BY score;很遗憾，上面的例子并不能得到期望的结果。因为ROWNUM这个伪列是作为一个WHERE条件出现的，所以要先于ORDER BY计算。所以上例的结果是选出开始的3条记录（任意次序），然后进行排

38、序。v在Oracle中如何解决上面的M1问题？66专用方案-Oraclev为在Oracle中解决上面的M1问题，需要使用一种特殊的语法，在FROM中使用子查询：例7：M1问题的Oracle解。SELECT * FROM (SELECT * FROM ranks ORDER BY score)WHERE ROWNUM (SELECT COUNT(*) FROM ranks r2 WHERE r2.score (SELECT COUNT(DISTINCT score) FROM ranks r2 WHERE r2.score = (SELECT COUNT(*) FROM ranks r2 WHE

39、RE 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 r1.score)AND 3 (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.i

40、d)ORDER BY score, id;idscoreCOUNT(*)1102213224235346357468479581059743.5 DML语句753.5.1 INSERTvINSERT:插入记录INSERT INTO 表名 (字段列表)VALUES (值列表);v这种形式的INSERT语句一次只能插入一行。值列表要和字段列表对应，数量和类型。v当值列表与表定义顺序一致时，可以不给字段列表。INSERT INTO 表名 (字段列表) 子查询;v可以利用子查询一次插入多行，子查询的结果列表要和字段列表对应。对于不在字段列表中的字段，按以下步骤决定其值：v表格定义时字段指定缺省值，置为缺省值。v字段可以为空，置为NULL值。v否则会出错。v另外，可以在值列表中使用DEFAULT和NULL关键字。76INSERT INTO customers(discnt, cname, city, cid)VALUES (12, Basics, Dallas, C02);例1：已创建一个新表sales_copy，定义与sales相同，现要将sales中内容复制到里面。INSERT INTO sales_copySELECT * FROM sales;例2：