分布式數(shù)據(jù)庫(kù)sql語(yǔ)句課件

1、引言 IBM SYSTEM R SEQUEL ANSI 標(biāo)準(zhǔn) SQL 1990 ISO 標(biāo)準(zhǔn) SQL 1992 SQL3 (SQL99)引言 IBM SYSTEM R SEQUEL體系結(jié)構(gòu) View Table File 體系結(jié)構(gòu) ViewSQL DDL 包括完整性與安全性 DMLSQL DDL SQL DDL 需要?jiǎng)?chuàng)建的結(jié)構(gòu) Table View IndexCreate table, view, index E.g. Create Table DEPT ( DEPT# Number, DNAME Char(5), Budget Number( 7,2); SQL DDL 需要?jiǎng)?chuàng)建的結(jié)構(gòu)SQL

2、 DDL 續(xù) 索引 Create index on ( )E.g. Create index I1 on EMP (E#); Create index I2 on EMP (Ename); 唯一性索引 E.g. Create unique index I1 on EMP (E#); SQL DDL 續(xù) 索引SQL DDL 續(xù) 聚集索引 元組按照索引值順序，物理上盡可能的存儲(chǔ)在一起 , 在索引值上執(zhí)行掃描（scan）操作時(shí)可以減少 I/O. E.g. Create cluster index CI1 on EMP (E#);SQL DDL 續(xù) 聚集索引基本查詢塊 典型的 SQL 查詢語(yǔ)句格式:s

3、elect A1, A2, ., Anfrom r1, r2, ., rmwhere PAis 代表屬性ris 代表關(guān)系P 是謂詞.基本查詢塊 典型的 SQL 查詢語(yǔ)句格式:selectSelect 子句select 短語(yǔ)用于列出所有要查詢的結(jié)果屬性. 例如查找 DEPT 關(guān)系中所有部門名字select dnamefrom DEPT注意: SQL 大小寫無(wú)關(guān)Select 子句select 短語(yǔ)用于列出所有要查詢的結(jié)果Select 子句-續(xù) SQL 的查詢結(jié)果中允許有重復(fù). 使用 distinct 消重復(fù).例如：查找 DEPT 關(guān)系中所有不同名的部門名字select distinct dname

4、from DEPTSelect 子句-續(xù) SQL 的查詢結(jié)果中允許有重復(fù).Select 子句-續(xù) select 短語(yǔ)中可以包含 數(shù)學(xué)表達(dá)式 . 例如: select S#, Sname, Status 2from S.Select 子句-續(xù) select 短語(yǔ)中可以包含 數(shù)學(xué)表Where 子句where 短語(yǔ)由給出謂詞，其謂詞由出現(xiàn)在from 短語(yǔ)中的關(guān)系的屬性組成.查找所有居住在 London 并且狀態(tài)大于 20的供應(yīng)商的供應(yīng)商號(hào)select S# from Swhere city=London AND status 20比較操作結(jié)果可以用邏輯操作 and, or, 和 not相連.Wher

5、e 子句where 短語(yǔ)由給出謂詞，其謂詞由出現(xiàn)在fWhere 子句-續(xù) between 比較操作.查找狀態(tài)在 20 和 30 之間的供應(yīng)商的商號(hào)(也就是說(shuō)要, 20 并且 30)select S#from S where status between 20 and 30Where 子句-續(xù) between 比較操作.From 子句 from 短語(yǔ)列出的關(guān)系在執(zhí)行時(shí)要被掃描.查找 employee department的結(jié)果 select from EMP, DEPT where emp.D#=dept.D#From 子句 from 短語(yǔ)列出的關(guān)系在執(zhí)行時(shí)要被掃描.重命名操作 SQL 使用別名

6、（ alias name）對(duì)關(guān)系和屬性重命名:old-name new-name查找所有供應(yīng)商的名字、商號(hào)和狀態(tài); 將 S# 重命名為 number、將 sname 重命名為 nameselect sname name, s# number, statusfrom S重命名操作 SQL 使用別名（ alias name）對(duì)關(guān)系元組變量from 短語(yǔ)使用別名定義元組變量.查找所有已供應(yīng)零件的供應(yīng)商名字和零件號(hào). select sx.sname, spx.P# from S sx, SP Spx where sx.S#=spx.s#元組變量from 短語(yǔ)使用別名定義元組變量.串操作 SQL 含有串

7、匹配操作. 末拌有兩個(gè)特殊的符號(hào)描述:%. 代表任意長(zhǎng)的子串._. 代表任意的單字符.Find the names of all suppliers whose city name includes the substring “Main”.select snamefrom s where city like %Main%串操作 SQL 含有串匹配操作. 末拌有兩個(gè)特殊的符號(hào)描述串操作-續(xù) SQL 包括其他串操作例如concatenation (using “|”) converting from upper to lower case (and vice versa) finding str

8、ing length, extracting substrings, etc.串操作-續(xù) SQL 包括其他串操作例如排序 List in alphabetic order the names of all suppliers locating in London cityselect distinct snamefrom Swhere city=Londonorder by snamedesc 表示降序， asc 表示升序；缺省時(shí)升序E.g. order by sname desc排序 List in alphabetic order th集合操作 union, intersect, 和 ex

9、cept 集合操作自動(dòng)消重復(fù)集合操作 union, intersect, 和 excep集合操作-續(xù) Find all cities where have a supplier, 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

10、.(select city from S)except(select city from P)集合操作-續(xù) Find all cities where h聚集函數(shù) avg min max sum count聚集函數(shù) avg聚集函數(shù)-續(xù) Find the average QTY that is supplied by supplier s1. select avg (QTY) from SP where s#= s1Find the number of tuples in the supplier relation. select count (*) from SFind the number

11、of suppliers who supply part. select count (distinct s#) from SP聚集函數(shù)-續(xù) Find the average QTY th聚集函數(shù)-續(xù) Find the number of part for each supplier.select sname, count (distinct p#)from S, SPwhere S.s# = SP.s#group by sname注意: select 短語(yǔ)中出現(xiàn)在聚集函數(shù)外面的屬性必須要在 group by 列表中聚集函數(shù)-續(xù) Find the number of part聚集函數(shù)-續(xù) Fi

12、nd the number of all suppliers who have supplied part more than 600.select s#, avg (QTY)from SPgroup by s#having avg (QTY) 600聚集函數(shù)-續(xù) Find the number of all 聚集函數(shù)-續(xù) Note: having 短語(yǔ) 和 where短語(yǔ)的不同處 select d#, avg (SAL)from EMP where age 600聚集函數(shù)-續(xù) Note: having 短語(yǔ) 和 wher空值 元組的某些屬性有可能取空值, 記為 nullnull 表示一個(gè)未知

13、的值或者表示一個(gè)不存在的值. 任何涉及 null 的算術(shù)運(yùn)算的結(jié)果是 nullE.g. 5 + null returns null聚集函數(shù)計(jì)算中將忽略空值空值 元組的某些屬性有可能取空值, 記為 null空值-續(xù) 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 任何與 null 的比較運(yùn)算結(jié)果是 unknownE.g. 5 null or null null or nu

14、ll = null空值-續(xù) is null 謂詞用于判斷空值.空值-續(xù) Total all part quantityselect sum (QTY)from SP上述語(yǔ)句忽略空值 QTY如果沒有非空的 QTY，結(jié)果是null 除了 count(*) ，所有聚集函數(shù)計(jì)算都忽略 null values .空值-續(xù) Total all part quantity嵌套子查詢 SQL provides a mechanism for the nesting of subqueries.A subquery is a select-from-where expression that is nested

15、 within another query.A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality.嵌套子查詢 SQL provides a mechanism舉例 Find all employees who have worked in sales department.select distinct Enamefrom EMPwhere d# in (select d# from DEPT where Dname=sale)舉例 Find

16、 all employees who have集合比較 Find all employees whose salary greater than some managers salary .select Enamefrom EMPwhere sal some (select sal from EMP where E# in ( select mgr from DEPT)集合比較 Find all employees whose 集合比較-續(xù) Find the names of all employees whose salary greater than all managers salary

17、 . select Enamefrom EMPwhere sal all(select sal from EMPwhere E# in ( select mgr from DEPT)集合比較-續(xù) Find the names of all e集合比較-續(xù) Definition of set comparison F some r t r s.t. (F t) F all r t r (F t) Where can be: 集合比較-續(xù) Definition of set compa集合比較-續(xù)056(5 some) = true050) = false505(5 some) = true (s

18、ince 0 5)(read: 5 some tuple in the relation)(5 some) = true(5 = some (= some) in However, ( some) not in集合比較-續(xù)056(5 some) = true050) 集合比較-續(xù)056(5 all) = false6104) = true546(5 all) = true (since 5 4 and 5 6)(5 all) = false(5 = all ( all) not in However, (= all) in集合比較-續(xù)056(5= 5000) Find all departme

19、nts which ha刪除Delete all suppliers who are in Londondelete from Swhere city = LondonDelete all suppliers who supply part p2.delete from Swhere s# in ( select s# from SP where p#=p2)Note: Here has some problem with constraints that will explained in Chapter 8 刪除Delete all suppliers who areDelete the

20、record of all employees with salary below the average .delete from EMPwhere SAL 4000update EMPset SAL = SAL 1.05where SAL 4000The order is important更新Increase all employees with 更多舉例 Find all employees who have the lowest salary in each department. Select Ename, d#, SAL From EMP Where SAL in (Select

21、 min(SAL) From EMP Group By d#)Note: Above statement has error, the correct is: Select Ename, d#, SAL From EMP Where (d#,SAL) in (Select d#,min(SAL) From EMP Group By d#)更多舉例 Find all employees who ha更多舉例-續(xù) Find all part number and its total quantity Select p#, sum(QTY) totqty From SP Group By p#; o

22、r equivalently Select p#, (Select sum(QTY) From SP Where SP.p#=P.p#) totqty From P;更多舉例-續(xù) Find all part number an嵌入 SQLThe SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/I, Fortran, C, and Cobol.A language to which SQL queries are embedded is referred

23、 to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.嵌入 SQLThe SQL standard defines嵌入 SQL-續(xù) The basic form of these languages follows that of the System R embedding of SQL into PL/I.EXEC SQL statement is used to identify embedded SQL request to the prep

24、rocessorEXEC SQL Note: this varies by language. E.g. the Java embedding uses # SQL . ; 嵌入 SQL-續(xù) The basic form of the嵌入 SQL-續(xù) Query single tuple EXEC SQL Select Ename INTO :ename From EMP Where e# = e1 Query set tuples There are dismached problem between host language with sub-language, using middle

25、 relation to solve this question.Note: “:ename” called host variable which need declared by special statement. 嵌入 SQL-續(xù) Query single tuple嵌入 SQL-續(xù)EXEC SQL BEGIN DECLARE SECTION; Char SQLSTATE6; Char P# 6; int Weight;EXEC SQL END DECLARE SECTION; P#=P2;EXEC SQL Select P.weight INTO :weight FROM P WHE

26、RE P.P#=:P#;If SQLSTATE=00000Then.Else. ; 嵌入 SQL-續(xù)EXEC SQL BEGIN DECLARE嵌入 SQL-續(xù)The statement for SQLSTATE EXEC SQL WHENEVER ConditonNot found no data was found 02000Sqlerror an error occurred 嵌入 SQL-續(xù)The statement for SQLS舉例Specify the query in SQL and declare a cursor for itEXEC SQLdeclare c curso

27、r for select sname, cityfrom S, SP where S.s# = SP.s# and SP.QTY :amountFrom within a host language, find the names and cities of suppliers supply more than the variable amount quantity part.舉例Specify the query in SQL and嵌入 SQL-續(xù)The open statement causes the query to be evaluatedEXEC SQL open c The

28、fetch statement causes the values 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-續(xù)The open statement cau嵌入 SQL-續(xù) A variable called SQLSTATE in the SQL communication area

29、(SQLCA) gets set to 02000 to indicate no more data is availableThe close statement causes the database system to delete the temporary relation that holds the result of the query.EXEC SQL close cNote: above details vary with language. E.g. the Java embedding defines Java iterators to step through res

30、ult tuples.嵌入 SQL-續(xù) A variable called SQL游標(biāo)更新Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select * from EMP where city = Parise for updateTo update tuple at the current location of cursor update EMP set SAL = SAL + 100 where current of c游標(biāo)更新Can

31、update tuples fetched 動(dòng)態(tài) SQL Allows programs to construct and submit SQL queries at run time. The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed.動(dòng)態(tài) SQL Allows programs to cons動(dòng)態(tài) SQL-續(xù) Example of the use of dynamic SQL from with

32、in a C program.char * sqlprog = “update EMP set SAL = SAL * 1.05 where d# = ?”EXEC SQL prepare dynprog from :sqlprog; char account 10 = “A-101”;EXEC SQL execute dynprog using :account;動(dòng)態(tài) SQL-續(xù) Example of the use ofODBCOpen DataBase Connectivity(ODBC) standard standard for application program to comm

33、unicate with a database server.application program interface (API) to open a connection with a database, send queries and updates, get back results.Applications such as GUI, spreadsheets, etc. can use ODBCODBCOpen DataBase ConnectivityODBC - 續(xù)Each database system supporting ODBC provides a driver li

34、brary 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 - 續(xù)Each data

35、base system ODBC - 續(xù) Opens database connection using SQLConnect(). Parameters for SQLConnect:connection handle,the server to which to connectthe user identifier, password Must also specify types of arguments:SQL_NTS denotes previous argument is a null-terminated string.ODBC - 續(xù) Opens database conneODBC 編程int ODBCe