《分布式数据库sql语句ppt培训课件》由会员分享，可在线阅读，更多相关《分布式数据库sql语句ppt培训课件（64页珍藏版）》请在金锄头文库上搜索。
1、第四章 SQL,引言,IBM SYSTEM R SEQUELANSI 标准 SQL 1990ISO 标准 SQL 1992SQL3 (SQL99),体系结构,ViewTable File,SQL,DDL包括完整性与安全性DML,SQL DDL,需要创建的结构TableView Index Create table, view, index E.g. Create Table DEPT ( DEPT# Number,DNAME Char(5),Budget Number( 7,2);,SQL DDL 续,索引Create index on ( ) E.g. Create index I1 on
2、EMP (E#);Create index I2 on EMP (Ename);唯一性索引E.g. Create unique index I1 on EMP (E#);,SQL DDL 续,聚集索引元组按照索引值顺序，物理上尽可能的存储在一起 , 在索引值上执行扫描（scan）操作时可以减少 I/O. E.g. Create cluster index CI1 on EMP (E#);,基本查询块,典型的 SQL 查询语句格式: select A1, A2, ., An from r1, r2, ., rm where P Ais 代表属性 ris 代表关系 P 是谓词.,Select 子句
3、,select 短语用于列出所有要查询的结果属性.例如查找 DEPT 关系中所有部门名字 select dname from DEPT 注意: SQL 大小写无关,Select 子句-续,SQL 的查询结果中允许有重复.使用 distinct 消重复. 例如：查找 DEPT 关系中所有不同名的部门名字select distinct dname from DEPT,Select 子句-续,select 短语中可以包含 数学表达式 .例如: select S#, Sname, Status 2 from S.,Where 子句,where 短语由给出谓词，其谓词由出现在from 短语中的关系的属性
4、组成. 查找所有居住在 London 并且状态大于 20的供应商的供应商号 select S#from S where city=London AND status 20 比较操作结果可以用逻辑操作 and, or, 和 not相连.,Where 子句-续,between 比较操作. 查找状态在 20 和 30 之间的供应商的商号(也就是说要, 20 并且 30) select S# from Swhere status between 20 and 30,From 子句,from 短语列出的关系在执行时要被扫描. 查找 employee department 的结果 select from
5、EMP, DEPTwhere emp.D#=dept.D#,重命名操作,SQL 使用别名（ alias name）对关系和属性重命名: old-name new-name 查找所有供应商的名字、商号和状态; 将 S# 重命名为 number、将 sname 重命名为 name select sname name, s# number, status from S,元组变量,from 短语使用别名定义元组变量. 查找所有已供应零件的供应商名字和零件号.select sx.sname, spx.P# from S sx, SP Spx where sx.S#=spx.s#,串操作,SQL 含有串匹
6、配操作. 末拌有两个特殊的符号描述: %. 代表任意长的子串. _. 代表任意的单字符. Find the names of all suppliers whose city name includes the substring “Main”.select sname from swhere city like %Main%,串操作-续,SQL 包括其他串操作例如 concatenation (using “|”)converting from upper to lower case (and vice versa)finding string length, extracting subs
7、trings, etc.,排序,List in alphabetic order the names of all suppliers locating in London cityselect distinct sname from S where city=London order by sname desc 表示降序， asc 表示升序；缺省时升序 E.g. order by sname desc,集合操作,union, intersect, 和 except集合操作自动消重复,集合操作-续,Find all cities where have a supplier, a part, o
8、r both:(select city from S) union (select city from P) Find all cities where have both a supplier and a part.(select city from S) intersect (select city from P) Find all cities where have a supplier but no P.(select city from S) except (select city from P),聚集函数,avgminmaxsumcount,聚集函数-续,Find the aver
9、age QTY that is supplied by supplier s1.select avg (QTY) from SP where s#= s1 Find the number of tuples in the supplier relation.select count (*) from S Find the number of suppliers who supply part.select count (distinct s#) from SP,聚集函数-续,Find the number of part for each supplier.select sname, coun
10、t (distinct p#) from S, SP where S.s# = SP.s# group by sname注意: select 短语中出现在聚集函数外面的属性必须要在 group by 列表中,聚集函数-续,Find the number of all suppliers who have supplied part more than 600.select s#, avg (QTY) from SP group by s# having avg (QTY) 600,聚集函数-续,Note: having 短语 和 where短语的不同处select d#, avg (SAL)
11、from EMP where age 600,空值,元组的某些属性有可能取空值, 记为 null null 表示一个未知的值或者表示一个不存在的值.任何涉及 null 的算术运算的结果是 null E.g. 5 + null returns null 聚集函数计算中将忽略空值,空值-续,is null 谓词用于判断空值. E.g. Find all Employee number which appear in the EMP relation with null values for d#.select E# from EMP where d# is null任何与 null 的比较运算结果
12、是 unknown E.g. 5 null or null = null,空值-续,Total all part quantityselect sum (QTY) from SP 上述语句忽略空值 QTY 如果没有非空的 QTY，结果是null 除了 count(*) ，所有聚集函数计算都忽略 null values .,嵌套子查询,SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-where expression that is nested within another q
13、uery. A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality.,举例,Find all employees who have worked in sales department.select distinct Ename from EMP where d# in (select d# from DEPTwhere Dname=sale),集合比较,Find all employees whose salary greater than
14、some managers salary .select Ename from EMP where sal some (select sal from EMP where E# in ( select mgr from DEPT),集合比较-续,Find the names of all employees whose salary greater than all managers salary . select Ename from EMP where sal all (select sal from EMP where E# in ( select mgr from DEPT),集合比较
15、-续,Definition of set comparisonF some r t r s.t. (F t) F all r t r (F t)Where can be: ,集合比较-续,(5 some,) = true,0,5,0,) = false,5,0,5,(5 some,) = true (since 0 5),(read: 5 some tuple in the relation),(5 some,) = true,(5 = some,(= some) inHowever, ( some) not in,集合比较-续,(5 all,) = false,6,10,4,) = true,5,4,6,(5 all,) = true (since 5 4 and 5 6),(5 all,) = false,(5 = all,( all) not inHowever, (= all) in,测试空关系,The exists construct returns the value true if the argument subquery is nonempty.exists r r not exists r r = Note that X Y = X Y,
