数据库系统课件：ch4 Advanced SQL

1、1DB华南理工大学 软件学院4.2华南理工大学 软件学院nSQL Data Types and SchemasnIntegrity Constraints nAuthorization4.3华南理工大学 软件学院ndate: Dates, containing a (4 digit) year, month and datelExample: date 2005-7-27ntime: Time of day, in hours, minutes and seconds.lExample: time 09:00:30 time 09:00:30.75ntimestamp: date plus t

2、ime of daylExample: timestamp 2005-7-27 09:00:30.75ninterval: period of timelExample: interval 1 daylSubtracting a date/time/timestamp value from another gives an interval valuelInterval values can be added to date/time/timestamp values4.4华南理工大学 软件学院nCan extract values of individual fields from date

3、/time/timestamplExample: extract (year from r.starttime) nCan cast string types to date/time/timestamp lExample: cast as datelExample: cast as time4.5华南理工大学 软件学院ncreate type construct in SQL creates user-defined typecreate type Dollars as numeric (12,2)ncreate domain construct in SQL-92 creates user

4、-defined domain typescreate domain person_name char(20) not nullnTypes and domains are similar. Domains can have constraints, such as not null, specified on them.4.6华南理工大学 软件学院nDomain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and tes

5、t queries to ensure that the comparisons make sense. nNew domains can be created from existing data typeslExample:create domain Dollars numeric(12,2) create domain Pounds numeric(12,2)nWe cannot assign or compare a value of type Dollars to a value of type Pounds. lHowever, we can convert type as bel

6、ow (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)4.7华南理工大学 软件学院nIntegrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. lA checking account must have a

7、balance greater than $10,000.00lA salary of a bank employee must be at least $4.00 an hourlA customer must have a (non-null) phone number4.8华南理工大学 软件学院nnot nullnprimary keynuniquencheck (P ), where P is a predicate4.9华南理工大学 软件学院nDeclare branch_name for branch is not null branch_name char(15) not nul

8、lnDeclare the domain Dollars to be not null create domain Dollars numeric(12,2) not null4.10华南理工大学 软件学院nunique ( A1, A2, , Am)nThe unique specification states that the attributes A1, A2, Amform a candidate key.nCandidate keys are permitted to be null (in contrast to primary keys).4.11华南理工大学 软件学院nche

9、ck (P ), where P is a predicateExample: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative.create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets = 0)4.12华南理工大学 软件学院nThe check clau

10、se in SQL-92 permits domains to be restricted:lUse check clause to ensure that an hourly_wage domain allows only values greater than a specified value.create domain hourly_wage numeric(5,2)constraint value_test check(value = 4.00)lThe domain has a constraint that ensures that the hourly_wage is grea

11、ter than 4.00lThe clause constraint value_test is optional; useful to indicate which constraint an update violated.4.13华南理工大学 软件学院nEnsures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.lExample: If “Perryridge

12、” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”.nPrimary and candidate keys and foreign keys can be specified as part of the SQL create table statement:lThe primary key clause lists attributes that co

13、mprise the primary key.lThe unique key clause lists attributes that comprise a candidate key.lThe foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By default, a foreign key references the primary key attributes of the r

14、eferenced table.4.14华南理工大学 软件学院create table customer(customer_name char(20),customer_street char(30),customer_city char(30),primary key (customer_name )create table branch(branch_name char(15),branch_citychar(30),assetsnumeric(12,2),primary key (branch_name )4.15华南理工大学 软件学院create table account(accou

15、nt_numberchar(10),branch_namechar(15),balanceinteger,primary key (account_number), foreign key (branch_name) references branch )create table depositor(customer_name char(20),account_number char(10),primary key (customer_name, account_number),foreign key (account_number ) references account,foreign k

16、ey (customer_name ) references customer )4.16华南理工大学 软件学院nAn assertion is a predicate expressing a condition that we wish the database always to satisfy.nAn assertion in SQL takes the formcreate assertion check nWhen an assertion is made, the system tests it for validity, and tests it again on every

17、update that may violate the assertionlThis testing may introduce a significant amount of overhead; hence assertions should be used with great care.nAsserting for all X, P(X) is achieved in a round-about fashion using not exists X such that not P(X)4.17华南理工大学 软件学院nEvery loan has at least one borrower

18、 who maintains an account with a minimum balance or $1000.00 create assertion balance_constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and

19、 depositor.account_number = account.account_number and account.balance = 1000)4.18华南理工大学 软件学院nThe sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. create assertion sum_constraint check (not exists (select * from branch where (select sum(amount

20、) from loan where loan.branch_name = branch.branch_name ) = (select sum (amount ) from account where loan.branch_name = branch.branch_name )4.19华南理工大学 软件学院Forms of authorization on parts of the database:nRead - allows reading, but not modification of data.nInsert - allows insertion of new data, but

21、not modification of existing data.nUpdate - allows modification, but not deletion of data.nDelete - allows deletion of data.Forms of authorization to modify the database schema (covered in Chapter 8):nIndex - allows creation and deletion of indices.nResource - allows creation of new relations.nAlter

22、ation - allows addition or deletion of attributes in a relation.nDrop - allows deletion of relations.4.20华南理工大学 软件学院nThe grant statement is used to confer authorizationgrant on to n is:la user-idlpublic, which allows all valid users the privilege grantedlA role (more on this in Chapter 8)nGranting a

23、 privilege on a view does not imply granting any privileges on the underlying relations.nThe grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).4.21华南理工大学 软件学院nselect: allows read access to relation,or the ability to query using the view

24、lExample: grant users U1, U2, and U3 select authorization on the branch relation:grant select on branch to U1, U2, U3ninsert: the ability to insert tuplesnupdate: the ability to update using the SQL update statementndelete: the ability to delete tuples.nall privileges: used as a short form for all t

25、he allowable privilegesnmore in Chapter 84.22华南理工大学 软件学院nThe revoke statement is used to revoke authorization.revoke on from nExample:revoke select on branch from U1, U2, U3n may be all to revoke all privileges the revokee may hold.nIf includes public, all users lose the privilege except those grant

26、ed it explicitly.nIf the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation.nAll privileges that depend on the privilege being revoked are also revoked.4.23华南理工大学 软件学院n 存储过程将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，当需要数据库提供与已定义好的

27、存储过程的功能相同的服务时，直接调用即可自动完成命令。n存储过程就像函数一样，也具有传递参数和执行逻辑表达式的功能，但存储过程又与函数不同，因为存储过程不返回取代其名称的值，也不能直接在表达式中使用。n由于存储过程提供了一种高效和安全的访问数据库的方法，它经常被用来访问数据和管理被修改的数据。4.24华南理工大学 软件学院nDefine a procedure that, given the name of a customer, returns the count of the number of accounts owned by the customer.create procedure

28、 account_count_proc (in title varchar(20), out a_count integer)begin select count(author) into a_count from depositor where depositor.customer_name = account_count_proc.customer_name endnProcedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement.declare a_c

29、ount integer;call account_count_proc( Smith, a_count);Procedures and functions can be invoked also from dynamic SQL4.25华南理工大学 软件学院nCompound statement: begin end, lMay contain multiple SQL statements between begin and end.lLocal variables can be declared within a compound statementsnWhile and repeat

30、statements:declare n integer default 0;while n 10 do set n = n + 1end whilerepeat set n = n 1until n = 0end repeat4.26华南理工大学 软件学院nFor looplPermits iteration over all results of a querylExample: find total of all balances at the Perryridge branch declare n integer default 0; for r as select balance f

31、rom account where branch_name = Perryridge do set n = n + r.balance end for4.27华南理工大学 软件学院nConditional statements (if-then-else)E.g. To find sum of balances for each of three categories of accounts (with balance =1000 and = 5000)if r.balance 1000 then set l = l + r.balanceelseif r.balance 5000 then

32、set m = m + r.balanceelse set h = h + r.balanceend if 4.28华南理工大学 软件学院n存储过程允许标准组件式编程l存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。n存储过程能够实现较快的执行速度l如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时

33、，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。4.29华南理工大学 软件学院n存储过程能够减少网络流量l对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。n存储过程可被作为一种安全机制来充分利用l系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的

34、数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。4.30华南理工大学 软件学院n触发器是在对表进行插入（INSERT）、更新（UPDATE）或删除（DELETE）操作时自动执行的存储过程，它是一种特殊类型的存储过程。 n触发器这种特殊类型的存储过程，是在基表被修改时自动执行的内嵌过程。 n触发器是独立存储在数据库中的独立对象。触发器是现代数据库管理系统用于响应数据变化的一种机制。触发器是实现数据维护规则的有效机制。 4.31华南理工大学 软件学院n如果有人试图在 Customer 表中添加或更改数据，下列 DML 触发器将向客户端显示一条消息。CREATE TRIGGER re

35、minder1ON Sales.CustomerAFTER INSERT, UPDATE AS RAISERROR (Notify Customer Relations, 16, 10);GO4.32华南理工大学 软件学院n比较不同版本的数据。n读取其他数据库的表中的数据。n在数据库中所有的相关表中级联所作的修改或删除n回滚无效的修改。n强制实现比由CHECK约束提供的限制更为复杂的限制。n执行本地和远程存储过程。4.33华南理工大学 软件学院4.34华南理工大学 软件学院应用程序应用程序应用程序ODBC DRIVER MANAGERORACLE DRIVERDB2 DRIVERSYBASE

36、DRIVERSQL SERVER DRIVERORACL DBMSDB2 DBMSSYBASE DBMSSQL SERVERDBMSOdbc APIOdbc API4.35华南理工大学 软件学院n用户的ODBC应用程序n驱动程序管理器(ODBC Driver Manager)l微软提供n数据库驱动程序(ODBC Driver)lDBMS厂商提供n数据库源(Data Source)4.36华南理工大学 软件学院n用户的ODBC应用程序如何和数据库交互lODBC API提供的接口函数lSQL4.37华南理工大学 软件学院n驱动程序管理器(ODBC Driver Manager)的主要作用l管理应用

37、程序和驱动程序的通信l装载、管理驱动程序l管理和配置数据源n负责的主要文件lsystem32odbc32.dll4.38华南理工大学 软件学院n数据库驱动程序(ODBC Driver)的作用l对相应的数据源进行各种操作n数据库驱动程序(ODBC Driver)的调用方式l以动态链接库形式(*.DLL)存在l由应用程序命令装载，由驱动程序管理器负责装载4.39华南理工大学 软件学院n数据源管理l数据源名（Data Source Name）4对应着一个具体数据库的连接4包括了服务器名、驱动程序、数据库名等信息l使用ODBC的第一步是首先要建立数据源名4.40华南理工大学 软件学院n提供了一组标准API，用于访问关系数据库nAPI类库位于java.sql.*和java.sqlx.*中n为Applet、Servlet和应用程序提供了统一的访问方式nJDBC提供的并不是嵌入式的结构化查询语言4.41华南理工大学 软件学院4.42华南理工大学 软件学院nJDBC应用程序n驱动程序管理器n驱动程序l需要把各个DBMS提供jdbc包放到某个路径下并指明classpathn数据