版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認(rèn)領(lǐng)
文檔簡介
1、第四章 SQL,引言,IBM SYSTEM R SEQUEL ANSI 標(biāo)準(zhǔn) SQL 1990 ISO 標(biāo)準(zhǔn) SQL 1992 SQL3 (SQL99),體系結(jié)構(gòu),View Table File,SQL,DDL 包括完整性與安全性 DML,SQL DDL,需要創(chuàng)建的結(jié)構(gòu) Table View IndexCreate {table, view, index} E.g.
2、Create Table DEPT ( DEPT# Number, DNAME Char(5), Budget Number( 7,2));,SQL DDL – 續(xù),索引 Create index on ( )E.g. Create inde
3、x I1 on EMP (E#); Create index I2 on EMP (Ename); 唯一性索引 E.g. Create unique index I1 on EMP (E#);,SQL DDL – 續(xù),聚集索引 元組按照索引值順序,物理上盡可能的存儲在一起 , 在索引值上執(zhí)行掃描(scan)操作時可以減少 I/O. E.g. Create cluster inde
4、x CI1 on EMP (E#);,基本查詢塊,典型的 SQL 查詢語句格式:select A1, A2, ..., Anfrom r1, r2, ..., rmwhere PAis 代表屬性ris 代表關(guān)系P 是謂詞.,Select 子句,select 短語用于列出所有要查詢的結(jié)果屬性. 例如查找 DEPT 關(guān)系中所有部門名字select dnamefrom DEPT注意: SQL 大小寫無關(guān)
5、,Select 子句-續(xù),SQL 的查詢結(jié)果中允許有重復(fù). 使用 distinct 消重復(fù).例如:查找 DEPT 關(guān)系中所有不同名的部門名字select distinct dnamefrom DEPT,Select 子句-續(xù),select 短語中可以包含 數(shù)學(xué)表達式 . 例如: select S#, Sname, Status ? 2from S.,Where 子句,where 短語由給出謂詞,其謂詞
6、由出現(xiàn)在from 短語中的關(guān)系的屬性組成.查找所有居住在 London 并且狀態(tài)大于 20的供應(yīng)商的供應(yīng)商號select S# from Swhere city=‘London’ AND status> 20比較操作結(jié)果可以用邏輯操作 and, or, 和 not相連.,Where 子句-續(xù),between 比較操作.查找狀態(tài)在 20 和 30 之間的供應(yīng)商的商號(也就是說要, ?20 并且 ?
7、30)select S#from S where status between 20 and 30,From 子句,from 短語列出的關(guān)系在執(zhí)行時要被掃描.查找 employee × department的結(jié)果 select ?from EMP, DEPT where emp.D#=dept.D#,重命名操作,SQL
8、使用別名( alias name)對關(guān)系和屬性重命名:old-name new-name查找所有供應(yīng)商的名字、商號和狀態(tài); 將 S# 重命名為 number、將 sname 重命名為 nameselect sname name, s# number, statusfrom S,元組變量,from 短語使用別名定義元組變量.查找所有已供應(yīng)零件的供應(yīng)商名字和零件號. select sx.sn
9、ame, spx.P# from S sx, SP Spx where sx.S#=spx.s#,串操作,SQL 含有串匹配操作. 末拌有兩個特殊的符號描述:%. 代表任意長的子串._. 代表任意的單字符.Find the names of all suppliers whose city name includes the substring “Main”.select s
10、namefrom s where city like ‘%Main%’,串操作-續(xù),SQL 包括其他串操作例如concatenation (using “||”) converting from upper to lower case (and vice versa) finding string length, extracting substrings, etc.,排序,List in alpha
11、betic order the names of all suppliers locating in London cityselect distinct snamefrom Swhere city=‘London’order by snamedesc 表示降序, asc 表示升序;缺省時升序E.g. order by sname desc,集合操作,union, intersect, 和 excep
12、t 集合操作自動消重復(fù),集合操作-續(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(s
13、elect city from P)Find all cities where have a supplier but no P.(select city from S)except(select city from P),聚集函數(shù),avg min max sum count,聚集函數(shù)-續(xù),Find the average QTY that is supplied by
14、 supplier s1. select avg (QTY) from SP where s#= ‘s1’Find the number of tuples in the supplier relation. select count (*) from SFind the number of suppliers who supply part. select coun
15、t (distinct s#) from SP,聚集函數(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 短語中出現(xiàn)在聚集函數(shù)外面的屬性必須要在 group by 列表中,聚集函數(shù)-續(xù),Fi
16、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ù),Note: having 短語 和 where短語的不同處 select d#, avg (SAL)from EMP
17、 where age 600,空值,元組的某些屬性有可能取空值, 記為 nullnull 表示一個未知的值或者表示一個不存在的值. 任何涉及 null 的算術(shù)運算的結(jié)果是 nullE.g. 5 + null returns null聚集函數(shù)計算中將忽略空值,空值-續(xù),is null 謂詞用于判斷空值.E.g. Find all Employee number which appear in the EMP re
18、lation with null values for d#.select E#from EMPwhere d# is null 任何與 null 的比較運算結(jié)果是 unknownE.g. 5 null or null = null,空值-續(xù),Total all part quantityselect sum (QTY)from SP上述語句忽略空值 QTY如果沒有非空的 QTY,結(jié)果是nu
19、ll 除了 count(*) ,所有聚集函數(shù)計算都忽略 null values .,嵌套子查詢,SQL provides a mechanism for the nesting of subqueries.A subquery is a select-from-where expression that is nested within another query.A common use of subqueries is to
20、 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 D
21、EPT where Dname=‘sale’),集合比較,Find all employees whose salary greater than some manager’s salary .select Enamefrom EMPwhere sal > some (select sal from EMP where E# i
22、n ( select mgr from DEPT)),集合比較-續(xù),Find the names of all employees whose salary greater than all manager’s salary . select Enamefrom EMPwhere sal > all(select sal f
23、rom EMPwhere E# in ( select mgr from DEPT)),集合比較-續(xù),Definition of set comparison F some r ? ??t ??r? s.t. (F t) F all r ?? ??t ??r? (F t) Where can be:
24、 ?????????????,集合比較-續(xù),(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,,集
25、合比較-續(xù),(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 in However, (= all) ? in,,測試空關(guān)系,The exists construct returns the value true
26、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 located in London.select distinct Sx.s
27、namefrom 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 wri
28、te this query using = all and its variants,唯一元組測試,The unique construct tests whether a subquery has any duplicate tuples in its result.Find all suppliers who are at most one department manager. select T.Ename
29、 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.
30、 select distinct T.Dname from DEPT T where not unique (select R.Enamefrom EMP Rwhere T.d# = R.d# and R.SAL >= 5000),刪除,Delete all suppliers who are in Londondelete from Swhere city = ‘L
31、ondon’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 constraints that will explained in Chapter 8,Delete th
32、e 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 changesSolution used in SQL:1.
33、First, compute avg salary and find all tuples to delete2.Next, delete all tuples found above (without recomputing avg or retesting the tuples),插入,Add a new tuple to Sinsert into Svalues (‘s6’, ‘wang ping’, 20,
34、‘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 nullinsert into Svalues (‘s7’, ‘Li hong’, 30, null),插入
35、-續(xù),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
36、 where statement is fully evaluated before any of its results are inserted into the relation (otherwise queries like insert into table1 select * from table1would cause problems),更新,Increase all employees w
37、ith salary over $4,000 by 6%, all other employees receive 5%.Write two update statements:update EMPset SAL = SAL ? 1.06where SAL > 4000update EMPset SAL = SAL ? 1.05where SAL ? 4000The order i
38、s 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#)No
39、te: 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#),更多舉例-續(xù),Find all part num
40、ber 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
41、 From P;,嵌入 SQL,The 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 to as a host lang
42、uage, and the SQL structures permitted in the host language comprise embedded SQL.,嵌入 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 identif
43、y embedded SQL request to the preprocessorEXEC SQL Note: this varies by language. E.g. the Java embedding uses # SQL { …. } ;,嵌入 SQL-續(xù),Query single tuple EXEC SQL Select Ename INTO :ename
44、 From EMP Where e# = ‘e1’ Query set tuples There are dismached problem between host language with sub-language, using middle relation to solve this question.Note: “:ename” called host variable which need
45、declared by special statement.,嵌入 SQL-續(xù),EXEC SQL BEGIN DECLARE SECTION; Char SQLSTATE[6]; Char P# [6]; int Weight;EXEC SQL END DECLARE SECTION; P#=‘P2;EXEC SQL Select P.weight INTO :weight FROM
46、 P WHERE P.P#=:P#;If SQLSTATE=‘00000’Then….Else….. ;,嵌入 SQL-續(xù),The statement for SQLSTATE EXEC SQL WHENEVER ConditonNot found no data was found ‘02000’Sqlerror an error occurred,舉例,Specify the query
47、 in SQL and declare a cursor for itEXEC SQLdeclare c cursor for select sname, cityfrom S, SP where S.s# = SP.s# and SP.QTY > :amount,From within a host language, find the names and citie
48、s of suppliers supply more than the variable amount quantity part.,嵌入 SQL-續(xù),The open statement causes the query to be evaluatedEXEC SQL open c The fetch statement causes the values of one tuple in the query resul
49、t 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ù),A variable called SQLSTATE in the SQL communication area (SQ
50、LCA) 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 va
51、ry with language. E.g. the Java embedding defines Java iterators to step through result tuples.,游標(biāo)更新,Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for sele
52、ct * 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,動態(tài) SQL,Allows programs to constr
53、uct 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.,動態(tài) SQL-續(xù),Example of the use of dynamic SQL from within
54、 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 e
55、xecute 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 data
56、base, send queries and updates, get back results.Applications such as GUI, spreadsheets, etc. can use ODBC,ODBC - 續(xù),Each database system supporting ODBC provides a "driver" library that must be linked with
57、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,
58、then a database connection handle.,ODBC - 續(xù),Opens database connection using SQLConnect(). Parameters for SQLConnect:connection handle,the server to which to connectthe user identifier, password Must also specify
59、types of arguments:SQL_NTS denotes previous argument is a null-terminated string.,ODBC 編程,int ODBCexample(){ RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllo
60、cEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS); { …. Do actual work … } SQLDisconnect(conn);
61、 SQLFreeConnect(conn); SQLFreeEnv(env); },ExerciseWrite the following queries, based on the following database exampleMovie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movie
62、Year, strName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presC#) Classes(class, type, country, numGuns, bore, displacement) Ships(name,
63、class, launched) Battles(name, date) Outcomes(ship, battle,result),In SQL. 1. Find Sandra Bullock’s birthdate 2. Find all executives worth at least $10,000,000 3. Find all the stars who either are male or liv
64、e in Malibu 4.Which stars appeared in movies produced by MGM in 1995? 5. Who is the president of MGM studio? 6. Find the countries whose ships had the largest number of guns. 7. Find the names of the ship with 1
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 眾賞文庫僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 分布式數(shù)據(jù)庫習(xí)題
- 分布式數(shù)據(jù)庫期中作業(yè)說明
- 分布式數(shù)據(jù)庫事務(wù)支持.pdf
- 分布式數(shù)據(jù)庫數(shù)據(jù)分配策略研究.pdf
- 分布式數(shù)據(jù)庫日志管理系統(tǒng).pdf
- 分布式數(shù)據(jù)庫查詢優(yōu)化技術(shù).pdf
- 分布式數(shù)據(jù)庫安全框架研究.pdf
- [學(xué)習(xí)]分布式數(shù)據(jù)庫系統(tǒng)查詢處理與優(yōu)化
- D-SQL分布式數(shù)據(jù)庫系統(tǒng)的啟動與恢復(fù).pdf
- 企業(yè)分布式數(shù)據(jù)庫管理的實現(xiàn).pdf
- 基于分布式數(shù)據(jù)庫的信息集成.pdf
- 分布式數(shù)據(jù)庫查詢優(yōu)化機制研究.pdf
- 基于WEB的分布式數(shù)據(jù)庫查詢.pdf
- 分布式數(shù)據(jù)庫拆分表常用的方法
- 分布式數(shù)據(jù)庫數(shù)據(jù)復(fù)制技術(shù)研究.pdf
- 分布式數(shù)據(jù)庫查詢優(yōu)化的研究.pdf
- 分布式數(shù)據(jù)庫查詢優(yōu)化算法研究.pdf
- 分布式數(shù)據(jù)庫開發(fā)平臺的研究.pdf
- 分布式數(shù)據(jù)庫系統(tǒng)復(fù)習(xí)題
- 分布式數(shù)據(jù)庫的聯(lián)合查詢優(yōu)化.pdf
評論
0/150
提交評論