分布式数据库sql语句

1、第四章 SQL,引言,IBM SYSTEM R SEQUEL ANSI 标准 SQL 1990 ISO 标准 SQL 1992 SQL3 (SQL99),体系结构,View Table File,SQL,DDL 包括完整性与安全性 DML,SQL DDL,需要创建的结构 Table View 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 i

2、ndex I1 on 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, ., Anfrom r1, r2, ., rmwhere P Ais 代表属性 ris 代表关系 P 是谓

3、词.,Select 子句,select 短语用于列出所有要查询的结果属性. 例如查找 DEPT 关系中所有部门名字select dnamefrom DEPT 注意: SQL 大小写无关,Select 子句-续,SQL 的查询结果中允许有重复. 使用 distinct 消重复. 例如：查找 DEPT 关系中所有不同名的部门名字 select distinct dnamefrom DEPT,Select 子句-续,select 短语中可以包含 数学表达式 . 例如: select S#, Sname, Status 2from S .,Where 子句,where 短语由给出谓词，其谓词由出现在f

4、rom 短语中的关系的属性组成. 查找所有居住在 London 并且状态大于 20的供应商的供应商号select S# from Swhere city=London AND status 20 比较操作结果可以用逻辑操作 and, or, 和 not相连.,Where 子句-续,between 比较操作. 查找状态在 20 和 30 之间的供应商的商号(也就是说要, 20 并且 30)select S#from S where status between 20 and 30,From 子句,from 短语列出的关系在执行时要被扫描. 查找 employee department的结果 se

5、lect from EMP, DEPT where emp.D#=dept.D#,重命名操作,SQL 使用别名（ alias name）对关系和属性重命名:old-name new-name 查找所有供应商的名字、商号和状态; 将 S# 重命名为 number、将 sname 重命名为 nameselect sname name, s# number, statusfrom S,元组变量,from 短语使用别名定义元组变量. 查找所有已供应零件的供应商名字和零件号. select sx.sname, spx.P# from S sx, SP Spx where sx.S#=spx.s#,串操作

6、,SQL 含有串匹配操作. 末拌有两个特殊的符号描述: %. 代表任意长的子串. _. 代表任意的单字符. Find the names of all suppliers whose city name includes the substring “Main”. select snamefrom s where city like %Main%,串操作-续,SQL 包括其他串操作例如 concatenation (using “|”) converting from upper to lower case (and vice versa) finding string length, ext

7、racting substrings, etc.,排序,List in alphabetic order the names of all suppliers locating in London city select distinct snamefrom Swhere city=Londonorder by sname desc 表示降序， asc 表示升序；缺省时升序 E.g. order by sname desc,集合操作,union, intersect, 和 except 集合操作自动消重复,集合操作-续,Find all cities where have a supplier

8、, a part, or 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),聚集函数,avg min max sum count,聚集函数-续,F

9、ind the average 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. se

10、lect sname, count (distinct p#)from S, SPwhere 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 SPgroup by s#having avg (QTY) 600,聚集函数-续,Note: having 短语 和 where短语的不同处 select d

11、#, avg (SAL)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 EMPwhere d# is null 任何与

12、 null 的比较运算结果是 unknown E.g. 5 null or null = null,空值-续,Total all part quantity select 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 wit

13、hin another query. 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 Enamefrom EMPwhere d# in (select d# from DEPT where Dname=sale),集合比较,Find all employees whose salary

14、greater than some managers salary . select Enamefrom EMPwhere 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 Enamefrom EMPwhere sal all(select sal from EMPwhere E# in ( select mgr from DE

15、PT),集合比较-续,Definition of set comparison F 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) in However, ( some) not in,集合比较-续,(5 all,) = false,6,10

16、,4,) = true,5,4,6,(5 all,) = true (since 5 4 and 5 6),(5 all,) = false,(5 = all,( all) not in However, (= 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,举例,Find all suppliers who have supplied all parts

17、located in London. select distinct Sx.snamefrom S Sxwhere not exists (select pnamefrom Pwhere city = London) except(select Px.pnamefrom SP Tx, P Pxwhere Tx.p# = Px.p# andSx. S# = Tx.s#) Note: Cannot write this query using = all and its variants,唯一元组测试,The unique construct tests whether a subquery ha

18、s any duplicate tuples in its result. Find all suppliers who are at most one department manager. select T.Ename from EMP T where unique ( select R.Dname from DEPT R where T.e#= R.MGR ),Find all departments which have at least two employees whose salary grater than $5000. select distinct T.Dname from

19、 DEPT T where not unique ( select R.Ename from EMP R where T.d# = R.d# and R.SAL = 5000),删除,Delete all suppliers who are in London delete from Swhere city = London Delete all suppliers who supply part p2. delete from Swhere s# in ( select s# from SP where p#=p2)Note: Here has some problem with const

20、raints that will explained in Chapter 8,Delete the record of all employees with salary below the average . delete from EMPwhere SAL (select avg (SAL) from EMP) Problem: as we delete tuples from EMP, the average salary changes Solution used in SQL: 1.First, compute avg salary and find all tuples to d

21、elete 2.Next, delete all tuples found above (without recomputing avg or retesting the tuples),插入,Add a new tuple to S insert into S values (s6, wang ping, 20, shanghai)or equivalently insert into S (status, city, s#, sname)values (20, shanghai, s6, wang ping, ) Add a new tuple to S with city set to

22、null insert into Svalues (s7, Li hong, 30, null),插入-续,Provide as a gift for all loan customers of the Perryridge branch, a $200 savings account. insert into accountselect loan-number, branch-name, 200from loanwhere branch-name = Perryridge The select from where statement is fully evaluated before an

23、y of its results are inserted into the relation (otherwise queries like insert into table1 select * from table1would cause problems),更新,Increase all employees with salary over $4,000 by 6%, all other employees receive 5%. Write two update statements: update EMPset SAL = SAL 1.06where SAL 4000 update

24、 EMPset SAL = SAL 1.05where SAL 4000 The order is important,更多举例,Find all employees who have the lowest salary in each department. Select Ename, d#, SAL From EMP Where SAL in (Select min(SAL) From EMP Group By d#) Note: Above statement has error, the correct is: Select Ename, d#, SAL From EMP Where

25、(d#,SAL) in (Select d#,min(SAL) From EMP Group By d#),更多举例-续,Find all part number and its total quantity Select p#, sum(QTY) totqty From SP Group By p#; or equivalently Select p#, (Select sum(QTY) From SP Where SP.p#=P.p#) totqty From P;,嵌入 SQL,The SQL standard defines embeddings of SQL in a variety

26、 of programming languages such as Pascal, PL/I, Fortran, C, and Cobol. A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.,嵌入 SQL-续,The basic form of these languages follows that of the System R

27、embedding of SQL into PL/I. EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL Note: this varies by language. E.g. the Java embedding uses # SQL . ;,嵌入 SQL-续,Query single tuple EXEC SQL Select Ename INTO :ename From EMP Where e# = e1 Query set tuples There are d

28、ismached problem between host language with sub-language, using middle relation to solve this question. Note: “:ename” called host variable which need declared by special statement.,嵌入 SQL-续,EXEC SQL BEGIN DECLARE SECTION; Char SQLSTATE6; Char P# 6; int Weight; EXEC SQL END DECLARE SECTION; P#=P2; E

29、XEC SQL Select P.weight INTO :weight FROM P WHERE P.P#=:P#; If SQLSTATE=00000 Then. Else. ;,嵌入 SQL-续,The statement for SQLSTATE EXEC SQL WHENEVER Conditon Not found no data was found 02000 Sqlerror an error occurred,举例,Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor fo

30、r select sname, cityfrom S, SP where S.s# = SP.s# and SP.QTY :amount,From within a host language, find the names and cities of suppliers supply more than the variable amount quantity part.,嵌入 SQL-续,The open statement causes the query to be evaluated EXEC SQL open c The fetch statement causes the val

31、ues of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc Repeated calls to fetch get successive tuples in the query result,嵌入 SQL-续,A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to 02000 to indicate no more data is avail

32、able The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL close c Note: above details vary with language. E.g. the Java embedding defines Java iterators to step through result tuples.,游标更新,Can update tuples fetched by cursor by

33、declaring that the cursor is for update declare c cursor for select * from EMP where city = Parise for update To update tuple at the current location of cursor update EMP set SAL = SAL + 100 where current of c,动态 SQL,Allows programs to construct and submit SQL queries at run time. The dynamic SQL pr

34、ogram contains a ?, which is a place holder for a value that is provided when the SQL program is executed.,动态 SQL-续,Example of the use of dynamic SQL from within a C program.char * sqlprog = “update EMP set SAL = SAL * 1.05 where d# = ?”EXEC SQL prepare dynprog from :sqlprog; char account 10 = “A-10

35、1”;EXEC SQL execute dynprog using :account;,ODBC,Open DataBase Connectivity(ODBC) standard standard for application program to communicate with a database server. application program interface (API) to open a connection with a database, send queries and updates, get back results. Applications such a

36、s GUI, spreadsheets, etc. can use ODBC,ODBC - 续,Each database system supporting ODBC provides a driver library that must be linked with the client program. When client program makes an ODBC API call, the code in the library communicates with the server to carry out the requested action, and fetch results. ODBC program first allocates an SQL environment, then a database connection handle.,ODBC - 续,Opens database c