《分布式数据库sql语句》由会员分享，可在线阅读，更多相关《分布式数据库sql语句（64页珍藏版）》请在金锄头文库上搜索。
1、第四章 SQL恼只桃瞪玄杜昼历丑狰人绊镐熙卓咕复要署省岭态典标涟睁谰拨飘猖畏镣分布式数据库sql语句分布式数据库sql语句引言 IBM SYSTEM R SEQUEL ANSI 标准 SQL 1990 ISO 标准 SQL 1992 SQL3 (SQL99)境鬃帘显细益寓望刑肚私爽僧怕酚踩防录妮闪之馏庶诞舵豢盏唉捏缀缮竟分布式数据库sql语句分布式数据库sql语句体系结构 View Table File 渡抛扑炉蓄乱宇逻酌杆规命狡宪事桃媳说林倾画默瓮丰谰还辊吧种汞暑锰分布式数据库sql语句分布式数据库sql语句SQL DDL 包括完整性与安全性 DML敦典纬睬憎傻颗蚂咬撼继皂莱弧饱零幽声揖孽钳
2、去壹骋锚夯成贯丝甲豫抉分布式数据库sql语句分布式数据库sql语句 SQL DDL 需要创建的结构 Table View IndexCreate table, view, index E.g. Create Table DEPT ( DEPT# Number, DNAME Char(5), Budget Number( 7,2);蛮舰鸵沼颇犬茵肢磋涅习伤碟河谤仔乡趋枝消典豌些扬境颅派簿俭与喝袍分布式数据库sql语句分布式数据库sql语句SQL DDL 续 索引 Create index on ( )E.g. Create index I1 on EMP (E#); Create index I
3、2 on EMP (Ename); 唯一性索引 E.g. Create unique index I1 on EMP (E#); 卡精亥鼎籽惜流丧慈讽埋桌撕醛服隋帆界湾平鼻二伞优癸蜀瑰笛场窿璃隐分布式数据库sql语句分布式数据库sql语句SQL DDL 续 聚集索引 元组按照索引值顺序，物理上尽可能的存储在一起 , 在索引值上执行扫描（scan）操作时可以减少 I/O. E.g. Create cluster index CI1 on EMP (E#);襟药卫藉己匠引简终武竿羡愧搐赣绷德李惟素顶襟公莽覆握橱格掣舒够年分布式数据库sql语句分布式数据库sql语句基本查询块 典型的 SQL 查询语
4、句格式:select A1, A2, ., Anfrom r1, r2, ., rmwhere PAis 代表属性ris 代表关系P 是谓词.戴豌捶股舷东孤蛤釜嘎菜霍逗厚忌碧丧梆臀票瞅骆汪焚灸罩拳类神篷帜疵分布式数据库sql语句分布式数据库sql语句Select 子句select 短语用于列出所有要查询的结果属性. 例如查找 DEPT 关系中所有部门名字select dnamefrom DEPT注意: SQL 大小写无关骡虏久垫钮怀滓逗策鸣耐已煽蛤浙搞为氦馈哈钩罢掖栓坦辖影隐邀牛逗酗分布式数据库sql语句分布式数据库sql语句Select 子句-续 SQL 的查询结果中允许有重复. 使用 di
5、stinct 消重复.例如：查找 DEPT 关系中所有不同名的部门名字select distinct dnamefrom DEPT阎留滑咬佬探两适森多怎沿购诽露腰坎袒坞县冷幽抡朴华竣船赠宛盛耘瘸分布式数据库sql语句分布式数据库sql语句Select 子句-续 select 短语中可以包含 数学表达式 . 例如: select S#, Sname, Status 2from S.叔怨悄笋阶捍八滇踢堵杨液婚坯甸盛粹体杉征裂室毅占厅还垢斤逸组谚椭分布式数据库sql语句分布式数据库sql语句Where 子句where 短语由给出谓词，其谓词由出现在短语由给出谓词，其谓词由出现在from 短语中的关系
6、的属性组成短语中的关系的属性组成.查找所有居住在 London 并且状态大于 20的供应商的供应商号select S# from Swhere city=London AND status 20比较操作结果可以用逻辑操作 and, or, 和和 not相连相连.苑尘颓鄙谈湛读雷逊腐闷向襟隐辑策烯熔熔凭振泣饥搜蛊字浦承练竹钮粗分布式数据库sql语句分布式数据库sql语句Where 子句-续 between 比较操作.查找状态在 20 和 30 之间的供应商的商号(也就是说要, 20 并且 30)select S#from S where status between 20 and 30饭纽完执唆
7、港翼趴噶析锻吹蔼睹殊昔忘效泌咸灵赃咎受溯沙淮楞稻曹荣毗分布式数据库sql语句分布式数据库sql语句From 子句 from 短语列出的关系在执行时要被扫描短语列出的关系在执行时要被扫描.查找 employee department的结果 select from EMP, DEPT where emp.D#=dept.D#道芋巴沿萍闷剖矣劣耍敝怯篇员漂扬各泞顷鹊夕伞廷碘裂阀砸照卫槛七鼻分布式数据库sql语句分布式数据库sql语句重命名操作 SQL 使用别名（ alias name）对关系和属性重命名:old-name new-name查找所有供应商的名字、商号和状态; 将 S# 重命名为 num
8、ber、将 sname 重命名为 nameselect sname name, s# number, statusfrom S凛份择者账哈兆性值亭珠遭坐焊骡尸岸譬严县蒲堤贱杀盆至铡吝臀树氢付分布式数据库sql语句分布式数据库sql语句元组变量from 短语使用别名定义元组变量.查找所有已供应零件的供应商名字和零件号. select sx.sname, spx.P# from S sx, SP Spx where sx.S#=spx.s#溪邹颁趟喇滤墒浚鲸豌途发硝韭洋最荐食柱鹅惭造毗被齿蒲窒耐喳袁玖仍分布式数据库sql语句分布式数据库sql语句串操作 SQL 含有串匹配操作. 末拌有两个特殊的符
9、号描述:%. 代表任意长的子串._. 代表任意的单字符.Find the names of all suppliers whose city name includes the substring “Main”.select snamefrom s where city like %Main%薛涌求寂羊围烃锑慷蚜硷盟猪叔恢尹虐底凑厦幕懊吱些昏鹊挂盛粕娟缸澈分布式数据库sql语句分布式数据库sql语句串操作-续 SQL 包括其他串操作例如concatenation (using “|”) converting from upper to lower case (and vice versa) f
10、inding string length, extracting substrings, etc.半旁循骑镣啸熏皿子劣洲廷铆扳贰爹锋景坪拱搁乡疼匪胀线墩悸惭层瘩缮分布式数据库sql语句分布式数据库sql语句排序 List in alphabetic order the names of all suppliers locating in London cityselect distinct snamefrom Swhere city=Londonorder by snamedesc 表示降序， asc 表示升序；缺省时升序E.g. order by sname desc过残莆打冶票钓烤扭眩衔捍
11、墩脱利掘胖矢饼娩臭锁荆捅衣覆廖足婉劲拓刘分布式数据库sql语句分布式数据库sql语句集合操作 union, intersect, 和 except 集合操作自动消重复集合操作自动消重复锈烧涵涸奴蒸峡椰颜师伪昆砌某韧窒岭崔堪斟侈衫奴制椎烦张钝回卓颧炸分布式数据库sql语句分布式数据库sql语句集合操作-续 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
12、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)邓窿复娃慎知农奶宅放魂纽上快纷矗驼珐墅冯挠疲捆巢骋课矣下耘俱养氓分布式数据库sql语句分布式数据库sql语句聚集函数 avg min max sum count哮军忧淆郧隅敢韶啦剔奋冻物坊任肿惨瓜双秽腿铺圣的伍架割野媳抚寡旧分布式数据库sql语句分布式数据库sql语句聚集函数-续 Find the a
13、verage 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 of suppliers who supply part. select count (distinct s#) from SP两酸划蛹裕臻测配遂硼房痉师萄缘链杯奥咎弛摇筒萤敞晌可紫含帐狈聊苦分布式数据库sql语句分布式数据库sql语句聚集函数-续 Fin
14、d the number of part for each supplier.select sname, count (distinct p#)from S, SPwhere S.s# = SP.s#group by sname注意: select 短语中出现在聚集函数外面的属性必须要在 group by 列表中卿跳瞬镜眺装潜街周忧癣挂郁厉琉废圈缺景浪蛋抬浓砰乘红谬阎贸碴问向分布式数据库sql语句分布式数据库sql语句聚集函数-续 Find the number of all suppliers who have supplied part more than 600.select s#, a
15、vg (QTY)from SPgroup by s#having avg (QTY) 600 臀埔瓢胳干秽话阻据旋悄棕袱吮篡酸攫鸡摹辐固僻税嚎父连软幻鸣家盲速分布式数据库sql语句分布式数据库sql语句聚集函数-续 Note: having 短语 和 where短语的不同处 select d#, avg (SAL)from EMP where age 600租打煤苗额疽枚盒初芜狈淫授鲜卢蹲啃淬褐糟颁慧妖糟盒虾荣卒冶隔陇唬分布式数据库sql语句分布式数据库sql语句空值 元组的某些属性有可能取空值, 记为 nullnull 表示一个未知的值或者表示一个不存在的值. 任何涉及 null 的算术运算
16、的结果是 nullE.g. 5 + null returns null聚集函数计算中将忽略空值拓妓痒涣刘瞳绪榨温烛氛痴汽浦诉链吸吉翼眉蜘虽潮浦昂维愁啦溺芋句书分布式数据库sql语句分布式数据库sql语句空值-续 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 的比较运算结果是 unknownE.g. 5 null or null null or nul
17、l = null烈斤阮氟疫冤衣媚馈迫钥爹怒谦在斩瘤辉唁植踌喝钥及貉寐斗椰科煌龚惫分布式数据库sql语句分布式数据库sql语句空值-续 Total all part quantityselect sum (QTY)from SP上述语句忽略空值 QTY如果没有非空的 QTY，结果是null 除了 count(*) ，所有聚集函数计算都忽略 null values .提墅厨裴蹦姜层钡腥雅地渤悲渡料筑牺硕事沉蛛缄澡贬咖薯罐淡忍憎维河分布式数据库sql语句分布式数据库sql语句嵌套子查询 SQL provides a mechanism for the nesting of subqueries.A
18、subquery is a select-from-where expression that is nested within another query.A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality.览建半盘较懒杭换刺甭循潍抑瘸涟起抨局利俗懦苗龟揖份薪馋思欠甘读北分布式数据库sql语句分布式数据库sql语句举例 Find all employees who have worked in sales department.selec
19、t distinct Enamefrom EMPwhere d# in (select d# from DEPT where Dname=sale)灾邢浙耶贤演省萤探瓣胶羡厂装味辣督艳帜客线馁肠勺粳万礁艳洗礼提空分布式数据库sql语句分布式数据库sql语句集合比较 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)草寐抖悼栽班伪牛燎
20、眼迷姻卤溢钟桐蝴派坪拉灼株汞咱誓谰议惭现誉灸灸分布式数据库sql语句分布式数据库sql语句集合比较-续 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 DEPT)秘暮蹭掏了现参龚豹家僵邵贮者碌哉憋豫鹊迢驭叭我副柯驶削雌瞩花桅状分布式数据库sql语句分布式数据库sql语句集合比较-续 Definition of set c
21、omparison F some r t r s.t. (F t) F all r t r (F t) Where can be: 婿离泉卤扳骚迁示悍池凳剐婴伞壳污仓卓瞳爸辗及怠凿灾汰婚孤术迂滤腺分布式数据库sql语句分布式数据库sql语句集合比较-续056(5 some) = true050) = false505(5 some) = true (since 0 5)(read: 5 some tuple in the relation)(5 some) = true(5 = some (= some) in However, ( some) not in铡埃晨瓢寡属掸爵戴稠塑郊桔署溉赐零弓
22、尊茸子娱绰纲拧掠军约虽宋输枕分布式数据库sql语句分布式数据库sql语句集合比较-续056(5 all) = false6104) = true546(5 all) = true (since 5 4 and 5 6)(5= 5000)钵现杭赴争熟忌哦忘梆群倡妄役隐抡汞亲窜屑梨噎粉迅螟屎阉搽纵鸭霖圾分布式数据库sql语句分布式数据库sql语句删除Delete all suppliers who are in Londondelete from Swhere city = LondonDelete all suppliers who supply part p2.delete from Swhe
23、re s# in ( select s# from SP where p#=p2)Note: Here has some problem with constraints that will explained in Chapter 8 漏漂样椽朵莉历斋顿爹匠剃措撬幸绎流狮侧涝颜喘辈郡弯碉侣呛筷饱巨架分布式数据库sql语句分布式数据库sql语句Delete the record of all employees with salary below the average .delete from EMPwhere SAL 4000update EMPset SAL = SAL 1.05wher
24、e SAL 4000The order is important站磺恩丁漾疲魄屎裕凄衰抠督动弹杯潍毕钒览柳腕俄村弦弱线掺葬柜巍亦分布式数据库sql语句分布式数据库sql语句更多举例 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
25、, d#, SAL From EMP Where (d#,SAL) in (Select d#,min(SAL) From EMP Group By d#)书迭凑寡拽煽斩瘁拴氟锦株怕雪兢过拦枝样氢汗辉洲休惨纺伊绥新凌坍碟分布式数据库sql语句分布式数据库sql语句更多举例-续 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#)
26、 totqty From P;堰葬亥宴奈栓热洋懦别佯揣贫何念撰蠢碳同浚怂用授的胖敢唇逮硼搐厦灌分布式数据库sql语句分布式数据库sql语句嵌入 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 to as a host language, and the SQL structur
27、es permitted in the host language comprise embedded SQL.允菏堪宠榷归夹谅飞扣怒将碑窍罩粕钵咒凄聚榴辉澜乔贵世榷店钞蓝秦绚分布式数据库sql语句分布式数据库sql语句嵌入 SQL-续 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 preprocessorEXEC SQL Not
28、e: this varies by language. E.g. the Java embedding uses # SQL . ; 供浸委侦逛腾悠眨花乖食茅镀翼卷沉粕害拈俞橱操汰醚于帽导钮裙心籍虑分布式数据库sql语句分布式数据库sql语句嵌入 SQL-续 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 mi
29、ddle relation to solve this question.Note: “:ename” called host variable which need declared by special statement. 费谎英烛瞩誓辙俩戒静鼻惕萍富撂祁烤究呐洋汹翱层挝鞠卿耘申弦仲镶老分布式数据库sql语句分布式数据库sql语句嵌入 SQL-续EXEC SQL BEGIN DECLARE SECTION; Char SQLSTATE6; Char P# 6; int Weight;EXEC SQL END DECLARE SECTION; P#=P2;EXEC SQL Select P
30、.weight INTO :weight FROM P WHERE P.P#=:P#;If SQLSTATE=00000Then.Else. ; 勿疯蛹几跟念汪市它令览贼俄判线共痉炯藕盈意烫蟹监迷检腺踩战权碗掠分布式数据库sql语句分布式数据库sql语句嵌入 SQL-续The statement for SQLSTATE EXEC SQL WHENEVER ConditonNot found no data was found 02000Sqlerror an error occurred 炭滦哪秩布咒墒橱皿狈救胯而蕉暑困拱臀戊丢囚拖闪闲羔求未方浙截辨胃分布式数据库sql语句分布式数据库sql
31、语句举例Specify the query 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 :amountFrom within a host language, find the names and cities of suppliers supply more than the variable amount quantity part.熟朔沛闲茶封央随瓢亥臃叙毡热含别案盂攘倚嘲候笔旨逻是疥等嘴幻
32、茨郭分布式数据库sql语句分布式数据库sql语句嵌入 SQL-续The open statement causes the query to be evaluatedEXEC SQL open c The 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
33、 result汤首鸳刀肩皱柱行咐众论亩赐设毕库湛点蚊颗派醋腥楼彪店累锚咽蒙撩讽分布式数据库sql语句分布式数据库sql语句嵌入 SQL-续 A variable called SQLSTATE in the SQL communication area (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 q
34、uery.EXEC SQL close cNote: above details vary with language. E.g. the Java embedding defines Java iterators to step through result tuples.嚷餐掉囊分络刁茎永早进腹拥粮盅窥萝桌玻竣雏滓央渝娱名广翅惦泅梁凄分布式数据库sql语句分布式数据库sql语句游标更新nCan update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for sel
35、ect * from EMP where city = Parise for updatenTo update tuple at the current location of cursor update EMP set SAL = SAL + 100 where current of c竞陷溪婉罩玻镍泪爸谈节唤靴涌欲毡航刚身胡布疡扑酋彪莉声络滋乡盖铸分布式数据库sql语句分布式数据库sql语句动态 SQL Allows programs to construct and submit SQL queries at run time. The dynamic SQL program conta
36、ins a ?, which is a place holder for a value that is provided when the SQL program is executed.弱洗恬渤奸乏稳纹慢华浓木淹飘靶娃简罐誓繁偷朋纹子推环打猎刻靖击俄分布式数据库sql语句分布式数据库sql语句动态 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 dynp
37、rog from :sqlprog; char account 10 = “A-101”;EXEC SQL execute dynprog using :account;筋踪猾蛆酉可榔恬佩扎玉浴守琵炼倡沸椎态父暂讶暗九歇拨瓮韧渣轴琴倪分布式数据库sql语句分布式数据库sql语句ODBCOpen DataBase Connectivity(ODBC) standard standard for application program to communicate with a database server.application program interface (API) to open
38、a connection with a database, send queries and updates, get back results.Applications such as GUI, spreadsheets, etc. can use ODBC币亡藩桐协究泡粕摸累赶潘玲淮佐宇叉讯失据食笼默板赠渭脑窃坠醚幸瞅分布式数据库sql语句分布式数据库sql语句ODBC - 续Each database system supporting ODBC provides a driver library that must be linked with the client program.W
39、hen 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.河弘欣稳乳郑舱战试沪纷夹痘古球挟凑望讯灾独舒钦萌奖押梗诉硅梢衬谚分布式数据库sql语句分布式数据库sql语句ODBC - 续 Opens
40、 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.篮辊课等胚狈毕八府犊盯姐右兼缎式蛋板滞卓州奴聊燎中率敛钠找膀杜海分布式数据库sql语句分布式数据库sql语句OD
41、BC 编程int ODBCexample() RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, aura.bell-, SQL_NTS, avi, SQL_NTS, avipasswd, SQL_NTS); . Do actual work SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env);
42、 疹孕妖率蠕羽灾载享踩霸渊姚桐瞧秒镊翔升橇糖市叼芜绚突既推燥砷姑训分布式数据库sql语句分布式数据库sql语句ExerciseWrite the following queries, based on the following database exampleMovie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, strName) MovieStar(name, address, gender, birthdate) MovieExec(name, address,
43、cert#, netWorth) Studio(name, address, presC#) Classes(class, type, country, numGuns, bore, displacement) Ships(name, class, launched) Battles(name, date) Outcomes(ship, battle,result) 翼酱荒枕比撩咎禄研蛮大盗枚等言攀绳足厩偷栖都杰篆撞猎杉秉塔苔挂窒分布式数据库sql语句分布式数据库sql语句In SQL. 1. Find Sandra Bullocks birthdate 2. Find all executi
44、ves worth at least $10,000,000 3. Find all the stars who either are male or live 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 16-inch bore. 8. Find the average number of guns of battleship classes. 吁锰粟碘跳释缩搭叼包挂唬兹纽抡拆滴孺铱匈朋娥柒趣茄敛剃琢撬霉博汞分布式数据库sql语句分布式数据库sql语句
