数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院第四章SQLSQL嵌入式SQLODBCJDBC数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院自从20世纪80年代以来，SQL就是一个通用的、功能极强的关系数据库语言。现在，SQL语言正从关系形式（ANSISQL-92标准）转向对象-关系形式（ANSISQL-99标准，1999年颁布）。SQL语言是1974年由Boyce和Chamberlin提出。1986年10月美国国家标准局(ANSl)批准了SQL作为关系数据库语言的美国标准。同年公布了SQL标准文本(简称SQL-86)。1987年6月国际标准化组织(ISO)也采纳了此标准。1989年，美国国家标准局（ANSI）采纳了新的规范SQL-89标准，取代SQL-86，同时SQL-89标准也被国际标准化组织(ISO)采纳。1992年，ANSI/ISO颁布了SQL2版本，标准的名称为SQL-92。SQL-92分称几个顺序级别：从代表SQL-89最小扩展集的“Entry”到“Intermediate”和“Full”。完成于1999年的SQL-99具有更加高级的特征（包括对象-关系特性），亦称SQL3。主要的几个数据库生产厂商并不可能完全遵守SQL-99（以及更老的SQL-92）。SQL 数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院Structured query language (SQL)1970s,IBMSequel(SystemR)1986,ANSIandISO,SQL_86;1987,IBM,SAA_SQL;1989,ANSI,SQL_89,anextendedstandardforSQL;SQL_92;1999SQL;SQL:2003数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院branch(branch_name,branch_city,assets)customer(customer_name,customer_street,customer_only)account(account_number,branch_name,balance)loan(loan_number,branch_name,amount)depositor(customer_name,account_number)borrower(customer_name,loan_number)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院Schema diagram数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院1 数据查询数据查询SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC， ASC|DESC ； 数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQL基于集合与关系运算并作了某些修改和增强典型的SQL查询形如:select A1,A2,.,Anfromr1,r2,.,rmwhere PAi 是属性ri 是表P是谓词等价于关系代数表达式A1, A2, ., An(P (r1xr2x.xrm)SQL查询的结果是一个关系.查询基本结构查询基本结构数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院select子句对应于关系代数的投影运算.用于列出想要的查询结果中的属性.从loan关系求所有分行的名称select branch_namefrom loan在“纯”关系代数语法中,对应查询是:branch_name(loan)select子句中用*表示“所有属性”select *from loanSQL是不分大小写的.select 子句子句数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQL允许关系与查询结果中出现重复元组.可用关键字distinct强制删除重复元组.从loan 关系求所有分行名并删除重复元组selectdistinctbranch_namefromloanselect子句可包含使用+,/运算以及常量和属性的算术表达式.selectloan_number, branch_name, amount 100from loan将loan 关系的amount 属性乘以100.数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院WHERE WHERE 子句子句 where 子句对应于关系代数的选择谓词. 其中的谓词涉及from子句中出现的关系的属性. 求Perryridge分行发出的所有额度超过$1200 的贷款的贷款号.select loan_numberfrom loanwhere branch_name = Perryridge and amount 1200 比较结果可用逻辑连接词and, or, not 组合在一起. 可对算术表达式的结果进行比较. select loan_numberfrom loanwhere amountbetween 90000and 100000数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQL包含串匹配运算用于比较字符串.模式用两个特殊字符描述:百分号(%).%字符可与任何字符串匹配.下划线(_)._字符与任一字符匹配.求所有街道名称包含子串“Main”的客户的姓名.select customer_namefrom customerwhere customer_street like %Main%若要匹配串“Main%”则需用likeMain%escape SQL支持许多串操作连接(“|”)大小写转换求串长度,取子串,etc.数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院按字母顺序列出在Perryridge分行有贷款的客户的姓名select distinct customer_namefrom borrower, loanwhere borrower.loan_number =loan.loan_number and branch_name = Perryridgeorder by customer_name可对每个排序属性使用desc指定降序,asc指定升序;升序是缺省顺序.E.g.order bycustomer_namedesc数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院谓词形式例子比较谓词BETWEEN谓词量化谓词IN谓词EXISTS谓词IS NULL谓词LIKE谓词expr1expr2|(subquery)expr1NOT BETWEEN expr2 and expr3exprSOME|ANY|ALL(subquery)expr NOT IN (subquery)NOTEXISTS(subquery)colname IS NOTNULLcloname NOT LIKE valESCAPE valp.price(subquery)c.discnt between 10.0 and 12.0c.discnt=all (subquery)pid in (select pid from orders)exist(select * )c.discnt is nullcname like A%数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院FROMFROM子句子句 from from 子句子句对应于关系代数的笛卡尔积运算. 它列出了查询中要扫描的关系. 求笛卡尔积 borrower x loan select *from borrower, loan 求所有在Perryridge分行有贷款的客户的姓名, 贷款号和贷款数量.select customer_name, borrower.loan_number, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number and branch_name = Perryridge数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院元组变量是在 fromfrom子句中用as as 子句定义的.求所有有贷款的客户的姓名和贷款号.select customer_name, T.loan_number, S.amount from borrower as T, loan as S where T.loan_number = S.loan_number求比位于Brooklyn的某分行资产多的分行的名字.select distinct T.branch_name from branch as T, branch as S where T.assets S.assets and S.branch_city = Brooklyn数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院集合操作集合操作关系的集合操作union, intersect, except 对应于关系代数运算以上操作自动删除重复元组;为了保留所有重复元组应使用对应的多重集版本union all, intersect all, except all.假如一个元组在r 中发生m 次,在s 中发生n 次, 则:在r union all s 中发生m + n 次在rintersect all s 中发生min(m,n) 次在rexcept all s 中发生max(0,m n) 次数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院求有贷款或账户的客户(selectcustomer_name from depositor)union(selectcustomer_name from borrower)求既有贷款又有账户的客户(selectcustomer_name from depositor)intersect(selectcustomer_name from borrower)求只有账户没有贷款的客户(selectcustomer_name from depositor)except(selectcustomer_name from borrower)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院聚集函数聚集函数以下函数对关系中某一列值的多重集进行计算并返回单个值avg: 平均值平均值min: 最小值最小值max: 最大值最大值sum: 总和总和count: 值的个数值的个数数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院聚集函数经常和聚集函数经常和Group BYGroup BY一起使用：一起使用：SELECT DISTINCT FROM WHERE GROUP BY HAVING 数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院求Perryridge分行的平均账户余额.select avg (balance)from accountwhere branch_name = Perryridge求customer 关系中的元组个数select count (*)from customer求银行存款人数.select count (distinct customer_name)from depositor数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院求每个分行的存款人数.select branch_name, count (distinctcustomer_name)from depositor, accountwhere depositor.account_number = account.account_numbergroup by branch_name求平均账户余额超过$1,200的所有分行名及平均余额.select branch_name, avg (balance)from accountgroup by branch_namehaving avg (balance) 1200注意:having子句中的谓词是在分组形成之后起作用的,而where子句中的谓词是在分组形成之前起作用的数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院嵌套子查询嵌套子查询SQL提供嵌套子查询的机制.子查询是嵌在另一个查询内部的select_from_where表达式.子查询的通常用法是执行集合成员检测,集合比较,以及集合基数.数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院既有账户又有贷款的客户.select distinct customer_namefrom borrowerwhere customer_name in (select customer_name from depositor)求有贷款但没有账户的客户select distinct customer_namefrom borrowerwhere customer_name not in (select customer_name from depositor)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院求在Perryridge分行既有账户又有贷款的客户select distinctcustomer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number and branch_name = “Perryridge”and (branch_name, customer_name) in(select branch_name, customer_namefrom depositor, accountwhere depositor.account_number = account.account_number)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院求在位于Brooklyn的所有分行都开了账户的客户.select distinct S.customer-namefrom depositor as Swhere not exists (select branch-namefrom branchwhere branch-city = Brooklyn)except(select R.branch-namefrom depositor as T, account as Rwhere T.account-number = R.account-number and S.customer-name = T.customer-name)如果子查询非空,则exists谓词返回true.exists r r not exists r r =数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院用EXISTS/NOT EXISTS实现全称量词SQL语言中没有全称量词 （For all）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P) 数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）；数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院unique谓词测试子查询的结果中是否有重复元组.求在Perryridge分行最多只开了一个账户的客户. select T.customer-name from depositor as T where unique ( select R.customer-name from account, depositor as R where T.customer-name = R.customer-name and R.account-number = account.account-number and account.branch-name = Perryridge)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院Selectcustomer_nameFromborrowerWhereexists(select*fromdepositorwheredepositor.customer_name=borrower.customer_name)集合操作集合操作:in,notin,some/all,=some/all,=some/all,some/all,exists,notexists,unique,notunique数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院查询语句：查询语句：SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC， ASC|DESC ； 数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院关系模式属性域完整性约束关系的索引关系的安全性和授权信息关系在磁盘上的物理存储结构不仅可以声明关系,还可以声明关系的其他信息,包括:2 数据定义数据定义DDL数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院关系关系/基表基表SQL关系用create table 命令定义命令定义:create table r (A1D1,A2D2,.,An Dn,(integrity-constraint1),.,(integrity-constraintk)r是关系名Ai是属性名Di是属性Ai 的域值的数据类型例如:create table branch(branch-name char(15)not null,branch-citychar(30),assetsinteger)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院 not null primary key(A1,.,An)Foreign Key (A1,An)referencetablerondeleterestrict/cascade/setNULL check (P),whereP是谓词create table account(account_numberchar(10),branch_namechar(15),balanceinteger,primary key(account_number), foreign key(branch_name) references branch)check (balance = 0)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院char(n).定长字符串,用户指定长度n.varchar(n). 变长字符串,用户指定最大长度n.int. 整数整数(依赖于机器的有限整数集合).smallint.小整数(依赖于机器的有限整数集合).numeric(p,d).定点数,用户指定精度为p位,小数点右边有n位.real, double precision.浮点数和双精度浮点数,精度依赖于机器.float(n).浮点数,用户指定精度为至少n位.域类型中允许空值.声明一个属性为not null将禁止该属性取空值.SQL-92中的create domain语句可创建用户定义的域类型create domain person-name char(20)not nullSQL域类型：数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院date.日期,包含年(4位),月,日E.g.date2001-7-27time. 一天中的时间,包含小时,分钟,秒E.g. time09:00:30 time09:00:30.75timestamp:日期加时间E.g.timestamp2001-7-2709:00:30.75Interval:时间段E.g.Interval1day从一个date/time/timestamp值减去另一个可得interval值Interval值可以加到date/time/timestamp值上可从date/time/timestamp抽取部分值E.g.extract(year fromr.starttime)可将字符串转换成date/time/timestampE.g.castas date数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院视图视图一般以下两点原因，我们希望用户工作在视图层上：出于安全上的考虑，不希望用户看到整个数据库的逻辑模式，而要隐藏掉部分数据；希望产生比逻辑模式更符合特定用户习惯的关系集合，也就是我们现在常说的个性化个性化个性化个性化服务。提供对某些用户隐藏某些数据的机制.创建视图命令:create view v as 其中:是任何合法查询表达式 v 是视图名数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院基于视图的操作定义查询删除受限更新定义基于该视图的新视图数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院创建视图命令创建视图命令:create view v as 其中:是任何合法查询表达式 v 是视图名数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院包含分行及其客户的视图create view all-customer as (select branch-name, customer-name from depositor, account where depositor.account-number = account.account-number) union (select branch-name, customer-name from borrower, loan where borrower.loan-number = loan.loan-number)查询Perryridge分行的所有客户select customer-namefrom all-customerwhere branch-name = Perryridge数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院视图更新视图更新用户角度：更新视图与更新基本表相同DBMS实现视图更新的方法视图实体化法（ViewMaterialization）视图消解法（ViewResolution）n一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)允许对行列子集视图进行更新对其他类型视图的更新不同系统有不同限制数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院数据操作数据操作数据删除DELETE数据插入INSERT数据更新UPDATE数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院DELETE FROM WHERE ；功能w删除指定表中满足WHERE子句条件的元组WHERE子句w指定要删除的元组w缺省表示要修改表中的所有元组删除数据删除数据数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院删除Perryridge分行的所有账户记录delete from accountwhere branch-name = Perryridge删除位于Needham的每个分行的所有账户.delete from accountwhere branch-name in (select branch-name from branch where branch-city = Needham)delete from depositorwhere account-number in (select account-number from branch, account where branch-city = Needhamand branch.branch-name = account.branch-name)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院插入单个元组语句格式:INSERTINTO (，)VALUES ( ， )n插入子查询结果语句格式: INSERT INTO ( ， ) 子查询子查询插入数据插入数据数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院insert into accountvalues (A_9732,Perryridge,1200)insert into account (branch_name, balance, account_number)values (Perryridge,1200,A_9732)insert into accountselect loan_number, branch_name, 200from loanwhere branch_name = Perryridge数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院语句格式UPDATE SET =，= WHERE ；可以：修改某一个元组的值修改多个元组的值带子查询的修改语句数据更新数据更新数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院 update accountset balance = balance 1.06where balance 10000 update accountset balance = balance 1.05where balance 10000updateSCSET Grade=0whereCS=(SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院drop table 命令从数据库删除关系的所有信息.alter table命令可用于向已有关系增加属性.关系的所有元组在新属性上的值为null.命令形如alter table r add A D其中A是新增属性名,D是A的域.alter table命令还可用于删除关系的属性alter table r drop A其中A是关系r的属性名许多数据库系统不支持删除属性数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院语句格式CREATE UNIQUE CLUSTER INDEX ON (, )；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引建立索引建立索引数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院索引索引CREATE INDEX H_INDEX ON STUDENT(HEIGHT)CREATE UNIQUE INDEX SC_INDEX ON SC(SNO ASC,CNO DESC)DROP INDEX H_INDEX数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院断言断言断言是表达要求数据库永远满足的条件的谓词.SQL的断言形如create assertion check作了某断言之后,系统将检查它的合法性,并对每一个可能破坏该断言的数据库更新进行检测这种检测会产生大量的开销;因此断言的使用应非常谨慎.象forallX,P(X)之类的断言是通过迂回的方式表达的notexistsXsuchthatnotP(X)数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQL语言提供了两种不同的使用方式：交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充Embedded SQL数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQL标准定义了将SQL嵌入到程序设计语言(如Pascal,PL/I,Fortran,C,andCobol)的内容.SQL查询所嵌入的语言称为宿主语言,而在宿主语言中可用的SQL结构即组成了embedded SQL.基本形式基于SystemR的SQL到PL/I的嵌入.EXECSQL语句用于向预处理器标识embeddedSQL请求EXECSQLEND-EXEC注意:依语言而变.E.g.Java嵌入使用#SQL.;数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院HostLanguage+EmbeddedSQLPreProcessorHostLanguage+ProcedureCallsHostLanguageCompilerObjectioncodeEmbeddedSQL处理过程数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院1.SQL通信区向主语言传递SQL语句的执行状态信息主语言能够据此控制程序流程2.主变量1）主语言向SQL语句提供参数2）将SQL语句查询数据库的结果交主语言进一步处理3.游标解决集合性操作语言与过程性操作语言的不匹配工作单元之间的通信方式工作单元之间的通信方式数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院SQLCA：SQLCommunicationAreaSQLCA是一个数据结构SQLCA的用途SQL语句执行后，DBMS反馈给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句与所执行的SQL语句有关SQLSQL通信区通信区数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院定义SQLCA用EXECSQLINCLUDESQLCA加以定义使用SQLCASQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理SQLCA的使用方法的使用方法数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据在SQL语句中使用的主语言程序变量简称为主变量（HostVariable）主变量主变量数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院n输入主变量n 指定向数据库中插入的数据n 将数据库中的数据修改为指定值n 指定执行的操作n 指定WHERE子句或HAVING子句中的条件n输出主变量n 获取SQL语句的结果数据n 获取SQL语句的执行状态数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志变量说明：变量说明：BEGIN DECLARE SECTION. . .END DECLARE SECTION数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院用SQL写查询并为它声明一个游标EXECSQLdeclare c cursor for select customer-name, customer-cityfrom depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account-numberand account.balance :amountEND-EXEC从宿主语言内查询在某账户中余额大于给定变量amount 的客户的姓名和城市.数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果每个游标区都有一个名字用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理游标游标数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院使用游标的步骤1.说明游标2.打开游标3.移动游标指针，然后取当前记录4.关闭游标数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院使用DECLARE语句语句格式EXECSQLDECLARECURSORFOR;功能是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。说明游标说明游标数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院open语句导致查询被执行EXECSQLopenc END-EXECfetch 语句可使查询结果中的一条元组的值放入宿主语语句可使查询结果中的一条元组的值放入宿主语言变量言变量.EXECSQL fetch c into :cn, :ccEND-EXEC循环调用fetch可逐条取得查询结果中的元组SQL通信区(SQLCA)中的变量SQLSTATE设置成02000以指示不再有数据了close 语句使数据库系统删除保存查询结果的临时关系语句使数据库系统删除保存查询结果的临时关系.EXECSQLclosecEND-EXEC注意:上述细节随语言而变.E.g.Java嵌入定义了Javaiterators来遍历结果中的元组.数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院通过声明游标是forupdate即可更新通过游标取得的元组 declare c cursor for select *from account wherebranch-name=Perryridgefor update修改游标当前位置上的元组 update account setbalance = balance+100where current of c数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院动态动态 SQL允许程序在运行时刻构造并提交SQL查询.例如char * sqlprog = “update account set balance = balance * 1.05 where account-number = ?”EXECSQLprepare dynprog from :sqlprog;char account 10=“A-101”;EXECSQLexecute dynprog using :account;动态SQL程序包含一个?,这是一个占位符,当SQL程序执行时提供相应的值.数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院ODBC（开放数据库互联标准）适用于客户-服务器体系结构，定义客户程序用以连接到数据库系统和发出SQL命令的API客户可以用同一ODBCAPI来连接到任何支持ODBC的数据库系统；ODBC允许用户同时连接到多个数据源并在这些数据源之间进行切换每个数据库系统必须提供一个驱动程序，受客户端的ODBC驱动程序管理器控制，负责与服务器连接和通讯以及进行所有必要的数据和查询格式转换ODBCAPI定义一个CLI(调用层接口)、一个SQL语法定义以及关于允许的CLI调用序列的规则数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院一个完整的ODBC由下列几个部件组成：1ODBC应用程序(Application)ODBC应用程序是用一般程序设计语言（如C语言等）编写的程序。2.ODBCAPI函数3ODBC管理器(Administrator) 该程序位于Windows控制面板(ControlPanel)的32位ODBC内，其主要任务是管理安装的ODBC驱动程序和管理数据源。4ODBC驱动程序管理器(DriverManager) 驱动程序管理器包含在ODBC32.DLL中，对用户是透明的。应用程序不能直接调用ODBC驱动程序，只可调用ODBC驱动程序管理器提供的ODBCAPI函数，再由ODBC驱动程序管理器负责把相应的ODBC驱动程序加载到内存中，同时把应用程序访问数据的请求传送给ODBC驱动程序。数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院5ODBC驱动程序ODBC驱动程序具体负责把SQL请求传送到数据源的DBMS中，再把操作结果返回到ODBC驱动程序管理器。后者再把结果传送至客户端的应用程序。 每种支持ODBC的数据库都拥有自己的驱动程序，一种驱动程序只能固定地与对应的数据库通信，不能访问其他数据库。6数据源 数据源就是需要访问的数据库。 应用程序若要通过ODBC访问一个数据库，则首先要创建一个数据源，主要工作是指定数据源名（DSN，datasourcename），使其关联一个目的数据库以及相应的ODBC驱动程序。所以说，数据源实际上是一种数据连接的抽象，指定了数据库位置和数据库类型等信息。数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院ODBC应用ODBCAPIODBC驱动管理器Oracle驱动器SQL Server驱动器其它数据库驱动器SQL Server服务器其它数据库服务器Oracle服务器数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院 工作流程： 应用程序要访问一个数据库，首先必须用ODBC管理器注册一个数据源。ODBC管理器根据数据源提供的数据库位置、数据库类型及ODBC驱动程序等信息，建立起ODBC与具体数据库的联系。应用程序将已创建好的数据源名提供给ODBC，ODBC就能建立起与相应数据库的连接，为访问数据库做好准备。在ODBC中，ODBCAPI函数不能直接访问数据库的，必须通过ODBC驱动程序管理器与数据库交换信息。ODBC驱动程序管理器在应用程序和数据源之间起着转换与管理的作用。数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院OpensdatabaseconnectionusingSQLConnect().ParametersforSQLConnect:connectionhandle,theservertowhichtoconnecttheuseridentifier,passwordProgramsendsSQLcommandstothedatabasebyusingSQLExecDirectResulttuplesarefetchedusingSQLFetch()ODBC CODE数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院Java编程语言具有坚固、安全、易于使用、易于理解和易于从网络自动下载等特性，已逐渐成为编写数据库应用程序的大众编程语言。Java通过与JDBC的结合，提供了良好的数据库访问性能。JDBC为数据库开发人员提供了一个标准的API。JDBCAPI目前新版本是JDBC3.0API。JDBC3.0组成：java.sql包和javax.sql包。javax.sql包是java.sql包的补充。JDBC 数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院JDBCJDBCJDBCJDBC驱动程序类型驱动程序类型驱动程序类型驱动程序类型 JavaApplicationJDBCDriverManagerJDBCAPIJDBCDriverAPIJDBCImplementationalternativesJDBCNetDriverJDBCODBCBridgeDriverDriverAODBCandDBDriverDriverBProprietarydatabaseaccessprotocolJDBCMiddleWareProtocol数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院OLEDB(ObjectLinkedandEmbedDataBase)对象链接与嵌入的数据库是Microsoft数据访问的基础，是MicrosoftOLE对象标准的一个实现，是COM对象并支持这种对象所有必需的接口。可访问包括关系型存储数据在内的多种格式的数据。OLEDB定义了三种类型的数据访问组件：（1)数据提供者：包含数据并将数据输出到其他组件中；（2)数据消费者：使用包含在数据提供者中的数据；（3)服务组件：处理和传输数据。数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院ADO（ActiveXDataObject，动态数据对象）是一种简单的对象模型，可以被数据使用者用来处理任何OLEDB数据。ADO是在OLEDB之上的高层接口集。ADO是介于OLEDB底层接口和应用程序之间的接口，避免了开发人员直接使用OLEDB底层接口的麻烦。它是面向对象的API，只要求开发者掌握几个简单对象的方法和属性。数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院ADO.NET的结构数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院NETFramework提供了4个.NETFramework数据提供程序：SQLSERVER.NETFramework数据提供程序；OLEDB.NETFramework数据提供程序，适用于OLEDB公开的数据源；ODBC.NETFramework数据提供程序，适用于ODBC公开的数据源；Oracle.NETFramework数据提供程序，适用于Oracle数据源。数据库系统设计与开发数据库系统设计与开发 北京邮电大学北京邮电大学计算机科学与技术学院计算机科学与技术学院思考题：考虑学生数据库，用关系代数和完成：S(SNO,SName,Sex,Age,Class,Dept),C(Cno,Cname,Dept),SC(SNO,Cno,Grade)）查询选修了课程编号为“C01”且成绩高于85分的学生的学号、姓名和成绩；）查询至少选修了三门课程的学生的学号和姓名；）查询计算机系张红同学选修的课程名称、学分和成绩。