《A first course in database systems(8)》由会员分享，可在线阅读，更多相关《A first course in database systems(8)（38页珍藏版）》请在金锄头文库上搜索。
1、8 System Aspects of SQLSQL in a programming environmentTransactions in SQLSecurity and User Authorization 8.1 SQL in a Programming EnvironmentWhy do we need other programming languages in database application systems?A database just provides sharing data source for a special environment.Many users c
2、an not use SQL to manipulate database in client(客户端客户端) directly.Many conventional programming languages allow connection and manipulation of database. Java, PowerBuilder, Delphi, C/C+,VB8.1 SQL in a Programming EnvironmentJava program 1JDBCdatabaseJDBCODBCJDBCJava classWeb ServerBrowserDBAJava prog
3、ram 2Delphi program 18.1 SQL in a Programming Environment宿主语言宿主语言 嵌套嵌套 SQL编译预处理编译预处理宿主语言宿主语言 函数调用函数调用函数库函数库目标代码目标代码编译编译包含包含 SQL 语句的典型编程系统的框架如下：语句的典型编程系统的框架如下：8.1 SQL in a Programming EnvironmentThe difference of SQL and conventional(传统的传统的) programming language:SQL uses the relational data model. Co
4、nventional programming languages use a data model with integers, reals, characters, and so on.impedance mismatch(匹配失衡匹配失衡)How to manipulate database by conventional programming languages?1.connect to database, build a session.2.send a sequence of SQL statements, receive and deal with the result.3.cl
5、ose the session. 8.1 SQL in a Programming EnvironmentThe SQL/Host Language Interface：Declare Shared Variables。EXEC SQL BEGIN DECLARE SECTION;char studioName50, studioAddr256;char SQLSTATE6;EXEC SQL END DECLARE SECTION;Use Embed SQL。EXEC SQL INSERT INTO Studio(name, address)VALUES(:studioName, :studi
6、oAddr);Use Precompiler statement, EXEC SQL。Use SQLSTATE。Use Cursor (游标游标 ,光标光标)。8.1 SQL in a Programming Environment建立和使用游标的步骤：建立和使用游标的步骤：游标说明游标说明DECLARE SCROLL CURSOR FOR SELECT 打开游标打开游标OPEN 推进推进FETCH NEXT | PRIOR | FIRST | LAST | RELATIVE n | ABSOLUTE n FROM INTO 变量名变量名, FETCH_STATUS=0 表示正常。表示正常。更
7、新数据更新数据 UPDATE | DELETE table WHERE CURRENT OF 关闭游标关闭游标CLOSE 8.2 模式中的存储过程模式中的存储过程存储过程存储过程 ( stored procedure ) 与函数：与函数：在数据库中存储的一组在数据库中存储的一组 SQL 指令，有唯一命名，供应指令，有唯一命名，供应用程序调用。用程序调用。过程的优势：过程的优势： 标准化；高效率；高安全性。标准化；高效率；高安全性。创建过程：创建过程：Create procedure ()Local declarationsProcedure body;Create function () re
8、turn Local declarationsFunction body;8.2 模式中的存储过程模式中的存储过程例如：例如：SQL Server 中的存储过程中的存储过程Create procedure new_dept( id integer, dname char(40), head char(10) )Asbegininsert into department(deptid, deptname, headerid) values(id, dname, head) end过程中的参数默认是输入型过程中的参数默认是输入型, 可以指定可以指定 OUTPUT 表示输表示输入输出型。函数中的参数
9、只可采用输入型。入输出型。函数中的参数只可采用输入型。局部变量说明：局部变量说明：DECLARE var 扩充的扩充的 SQL 语句语句 Transact-SQL ，包括变量定义，条件，包括变量定义，条件 IFELSE ，循环，循环 WHILE 等流程控制语句。等流程控制语句。 函数中使用函数中使用 RETURN 语句返回函数值。语句返回函数值。8.2 模式中的存储过程模式中的存储过程如何调用过程？如何调用过程？EXEC 参数参数=值值 例如：例如：EXEC new_dept id=3, dname=北京代理北京代理, head=null或或EXEC new_dept 3, 北京代理北京代理,
10、 null如何修改过程？如何修改过程？Alter procedure new_dept 如何删除过程？如何删除过程？Drop procedure new_dept;过程中如何返回计算结果？过程中如何返回计算结果？1.单个结果值可用单个结果值可用 OUTPUT 参量返回。参量返回。2.函数中使用函数中使用 Return 子句返回一个或一组结果值。子句返回一个或一组结果值。8.2 模式中的存储过程模式中的存储过程例如：例如：给定某种商品货号给定某种商品货号 prodid，计算其最高销售单价，计算其最高销售单价 maxp ( OUTPUT )。 Create Procedure maxPrice(
11、pid char(20), maxp numeric(12,2) OUTPUT ) AS begin Select maxp=max(singlecost) From salesitem Where prodid = pid end下面语句在下面语句在 SQL 中完成调用。中完成调用。 Declare maxp Numeric(12,2); Exec maxPrice TV9475, maxp; Select maxp;8.2 模式中的存储过程模式中的存储过程例如：例如：给定某种商品货号给定某种商品货号 prodid，检索销售该商品的订，检索销售该商品的订单号单号 orderno 及销售单价及
12、销售单价 singlecost。 Create Function orderbyprod( pid char(20) ) RETURNS TABLE AS Return ( Select orderno, singlecost from salesitem Where prodid = pid )下面语句调用该函数并显示结果。下面语句调用该函数并显示结果。 Select * From orderbyprod(TV9475);8.3 Delphi 数据库连接数据库连接在在Delphi中连接数据库中连接数据库.doc8.4 Java 数据库连接数据库连接如何用如何用 Java 建立数据库应用：建立
13、数据库应用：以数据库以数据库 Sybase ASA7 为例为例.必要的构件：必要的构件：1.JDK；2.JDBC(一个类库文件一个类库文件jar，其中包含某个驱动器，其中包含某个驱动器Driver，如，如com.sybase.jdbc.SybDriver) JDBC由两部分构成：接口规范和实现。由两部分构成：接口规范和实现。 接口规范是统一的；而实现由数据库厂商提供。接口规范是统一的；而实现由数据库厂商提供。 JDBC仅负责连接数据库，转发仅负责连接数据库，转发SQL指令和结果。指令和结果。连接前的准备：连接前的准备：1.确认数据库服务器已启动。确认数据库服务器已启动。2.JDBC 类库文件类
14、库文件(如如jdbcdrv5.jar)设在合适的类路径设在合适的类路径CLASSPATH8.4 Java 数据库连接数据库连接如何连接数据库：如何连接数据库：连接数据库需要连接数据库需要4个参数：个参数：1.数据库数据库 URL(Uniform Resource Locator)如：如：jdbc:sybase:Tds:localhost:2638/SALES?JCONNECT_VERSION=5注意：每种注意：每种 DBMS 有不同的有不同的 URL。应参考相关文档。应参考相关文档。2.驱动器驱动器 Driver如如“com.sybase.jdbc.SybDriver”注意：每种注意：每种 D
15、BMS 使用不同的驱动器。应参考相关文档。使用不同的驱动器。应参考相关文档。3.用户名用户名如如“dba”4.口令口令如如“sql” 8.4 Java 数据库连接数据库连接import java.sql.*;.private Connection con;public void connectDriverManager(String url,String drv,String user,String pwd)tryClass.forName(drv).newInstance();con = DriverManager.getConnection (url, user, pwd);catch (
16、SQLException ex)printSQLException(ex);catch (Exception ex)ex.printStackTrace();以上方法用于建立数据库连接。以上方法用于建立数据库连接。8.4 Java 数据库连接数据库连接下面方法执行一个下面方法执行一个 Select 语句，加载某客户信息并初始语句，加载某客户信息并初始化当前对象。化当前对象。 public Class Customer . public int loadCustomer(int custid) throws Exception String select = select customer.cu
17、stid, name, prov, city, phone, unit, count(distinct salesorder.orderno), sum(quantity), sum(singlecost*quantity) + from customer Natural Left Outer Join (salesorder key join salesitem) + where customer.custid = ? + group by customer.custid,name,prov,city,phone,unit; try PreparedStatement prepSt = co
18、n.prepareStatement(select); prepSt.setInt(1,custid); ResultSet rs = prepSt.executeQuery();8.4 Java 数据库连接数据库连接 if (rs.next() this.custid = rs.getInt(1);name = rs.getString(2);prov = rs.getString(3);city = rs.getString(4);phone = rs.getString(5);unit = rs.getString(6);ordernum = rs.getInt(7);quantity
19、= rs.getInt(8);amount = rs.getDouble(9);prepSt.close(); elseprepSt.close();throw new Exception(客户：客户：+custid+不存在不存在); catch (SQLException ex) dbcon.printSQLException(ex); return custid;8.5 Transactions in SQL一组操作作为一个单元，按次序全部执行，称一组操作作为一个单元，按次序全部执行，称为事务为事务 Transaction。 事务的特性：事务的特性：ACIDAtomicity：原原子子性性
20、，一一个个事事务务中中的的一一组组操操作作，要要么么全部执行，要么一点也不执行。全部执行，要么一点也不执行。 Consistency：一致性，保持数据库的正确状态。：一致性，保持数据库的正确状态。Isolation：隔隔离离性性，多多个个事事务务并并发发运运行行时时，作作用用效效果果相互分开。相互分开。Durability：持持久久性性，事事务务完完成成后后，即即使使系系统统发发生生故故障，事务的结果不丢失。障，事务的结果不丢失。 通过加锁通过加锁(locking)，日志，日志(logging)和提交和提交(commit)保持事务特性保持事务特性8.5 Transactions in SQLH
21、ow to control a transaction execution:The SQL statement commit causes the transaction to end successfully; all row updates made during the transaction become permanent in the database and visible to concurrent(并发并发) users.The SQL statement rollback causes the transaction to abort, or terminate unsuc
22、cessfully; all row updates made during the transaction are reversed and the prior versions of these row are put in place and become visible again to concurrent users.Commit and rollback both cause the transaction to end.8.5 Transactions in SQLProblems caused by parallel transactions:同时更新同一数据同时更新同一数据
23、脏读脏读 dirty read不可重复读不可重复读 non-repeatable read虚行虚行(幻象元组幻象元组) phantom rows例如：飞机售票系统例如：飞机售票系统 A 售票点售票点 B 售票点售票点 航班信息航班信息8.5 Transactions in SQLWrite lock:If a data is updated by transaction A, transaction B can not update or delete this data before commit or rollback of A.Transaction ATransaction BData
24、 TUpdate T=T10CommitUpdate T=T208.5 Transactions in SQLDirty data:Dirty data is a common term for data that has been written by a transaction that has not yet committed.Dirty read:A dirty read is a read of dirty data.Transaction ATransaction BData TSelect T=100Update set T=T+10Select T=110Rollback？8
25、.5 Transactions in SQLnon-repeatable read:Transaction A performs a read, but transaction B modifies or deletes that data later. If A reads the same row again, different data will be got.Transaction ATransaction BData TSelect T=100Select T=110Select T=100CommitUpdate set T=T+10？8.5 Transactions in SQ
26、Lphantom(幻象幻象) rows:Transaction A does a query on a set of rows to perform an operation. Transaction B modifies the table such that a query of A would have given a different result. The table may be inconsistent.Transaction ATransaction BData TSelect ? where t=100Insert (t=100)CommitSelect ? where t
27、=100？8.5 Transactions in SQL读锁读锁(read lock)：当数据被一个事务施加写锁时，其他事务不可施加当数据被一个事务施加写锁时，其他事务不可施加对该数据的读锁或写锁。当数据被一个事务施加读对该数据的读锁或写锁。当数据被一个事务施加读锁时，其他事务可以施加对该数据的读锁，但不可锁时，其他事务可以施加对该数据的读锁，但不可施加写锁。施加写锁。8.5 Transactions in SQLTypes of isolation levels: L0 read-uncommitted: Dirty reads, non-repeatable reads and phant
28、om reads can occur. L1 read-committed:Dirty reads are prevented; non-repeatable reads and phantom reads can occur. L2 repeatable-read: Dirty reads and non-repeatable reads are prevented; phantom reads can occur. L3 Serializable: Dirty reads, non-repeatable reads, and phantom reads are prevented. 8.5
29、 Transactions in SQL事务不论通过事务不论通过 Commit 还是还是 Rollback 结束，均释结束，均释放自己所施加的写锁和读锁。放自己所施加的写锁和读锁。当客户程序连接某个数据库时，可设置某个隔离当客户程序连接某个数据库时，可设置某个隔离级别。级别。服务器可为所有用户设置一个缺省的隔离级别。服务器可为所有用户设置一个缺省的隔离级别。8.6 Security and User Authorization A key problem of database security:Access Control(访问控制访问控制)Protected objects and ope
30、rations in database:Table：AlterReferencesInsert / Delete / Update / SelectAllView：Delete / Insert / Select / UpdateProcedure：ExecuteUsers & GroupsGrant / RevokeDatabase objects (Tables, Views, Procedures)Create / Drop / Alter 8.6 Security and User AuthorizationDBA:Database Administrator, has the hig
31、hest priority for all objects. DBA can be assigned as a special priority to someone by authorizationDBA can create database objects for others.Owner:When some user create a database object, then the user is “owner” of this object, and has the priority to perform all the operations for the object.Eve
32、ry database object has an unique owner. Full name of a table is “owner.table”, such as dba.customer.Owner can be omitted only if current user is the owner of the table. 8.6 Security and User AuthorizationAuthorization(授权授权) modelmembershipGroupUserDB ObjectOperationnamepasswordpermissionpermissionna
33、memembership 用用户户和和组组是是被被授授权权对对象象；DBA把把对对特特定定数数据据库库对对象象的的特特定定操操作作的的权权力力授授予予用用户户或或组组；用用户可从所在组中继承许可权利。户可从所在组中继承许可权利。8.6 Security and User AuthorizationHow to build a group?Grant Connect To commonuser;Grant Group To commonuser;How to grant authority to a group?Grant insert, delete, update, select On db
34、a.customer to commonuser;WITH GRANT OPTION8.6 Security and User AuthorizationHow to build a user in a group?Grant Connect To zhanghui Identified By aabb;Build a user zhanghui, password is aabb.Grant membership in Group commonuser To zhanghui;Let zhanghui in commonuser.The user own all power of group
35、 commonuser.8.6 Security and User AuthorizationHow to grant authority to a user?Grant insert, update On dba.department to zhanghui;WITH GRANT OPTIONGrant insert, update On dba.department to zhanghui WITH GRANT OPTION;8.6 Security and User AuthorizationGrant DiagramsSuppose user A is the owner of tab
36、le T, SBy user A:Grant select, insert On T to B WITH GRANT OPTION;Grant select On S to B, C WITH GRANT OPTION;By user B:Grant select On T to C ;Grant select On S to C ;ATS*ATI*ASI*ASS*BTS*BTI*CSS*BSS*CTSCSS8.6 Security and User AuthorizationHow to revoke authority from a group? Revoke insert, delete
37、, update On dba.customerFrom commonuser;How to delete a group or a user？Revoke Group From commonuser;Commonuser 不再作为一个组；但还是一个用户。不再作为一个组；但还是一个用户。Revoke Connect From commonuser;Commonuser 作为一个用户被撤销。作为一个用户被撤销。注意：注意：删除一个用户会自动删除其作为所有者的对象。删除一个用户会自动删除其作为所有者的对象。若一个用户正连接数据库，则不能删除。若一个用户正连接数据库，则不能删除。 8.6 Security and User AuthorizationHow to revoke authority from a user? Revoke insert, select On T From B;CasecadeRevoke insert, select On T From B CASCADE;RestrictRevoke insert, select On T From B Restrict;Revoke Grant Option Revoke Grant Option FOR insert, select On T From B CASCADE;
