數(shù)據(jù)庫(kù)系統(tǒng)課件：ch4 Advanced SQL

1、Chapter 4: Advanced SQLChapter 4: Advanced SQLSQL Data Types and SchemasIntegrity Constraints AuthorizationBuilt-in Data Types in SQL date: Dates, containing a (4 digit) year, month and dateExample: date 2005-7-27time: Time of day, in hours, minutes and seconds.Example: time 09:00:30 time 09:00:30.7

2、5timestamp: date plus time of dayExample: timestamp 2005-7-27 09:00:30.75interval: period of timeExample: interval 1 daySubtracting a date/time/timestamp value from another gives an interval valueInterval values can be added to date/time/timestamp valuesBuild-in Data Types in SQL (Cont.)Can extract

3、values of individual fields from date/time/timestampExample: extract (year from r.starttime) Can cast string types to date/time/timestamp Example: cast as dateExample: cast as timeUser-Defined Typescreate type construct in SQL creates user-defined typecreate type Dollars as numeric (12,2)create doma

4、in construct in SQL-92 creates user-defined domain typescreate domain person_name char(20) not nullTypes and domains are similar. Domains can have constraints, such as not null, specified on them.Domain ConstraintsDomain constraints are the most elementary form of integrity constraint. They test val

5、ues inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data typesExample:create domain Dollars numeric(12,2) create domain Pounds numeric(12,2)We cannot assign or compare a value of type Dollars to a value of type Pounds. Ho

6、wever, we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)Integrity ConstraintsIntegrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consis

7、tency. A checking account must have a balance greater than $10,000.00A salary of a bank employee must be at least $4.00 an hourA customer must have a (non-null) phone number Constraints on a Single Relation not nullprimary keyuniquecheck (P ), where P is a predicateNot Null Constraint Declare branch

8、_name for branch is not null branch_name char(15) not nullDeclare the domain Dollars to be not null create domain Dollars numeric(12,2) not nullThe Unique Constraintunique ( A1, A2, , Am)The unique specification states that the attributes A1, A2, Amform a candidate key.Candidate keys are permitted t

9、o be null (in contrast to primary keys).The check clausecheck (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 (bra

10、nch_name), check (assets = 0)The check clause (Cont.)The check clause in SQL-92 permits domains to be restricted:Use 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)The

11、domain has a constraint that ensures that the hourly_wage is greater than 4.00The clause constraint value_test is optional; useful to indicate which constraint an update violated.Referential IntegrityEnsures that a value that appears in one relation for a given set of attributes also appears for a c

12、ertain set of attributes in another relation.Example: If “Perryridge” 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”.Primary and candidate keys and foreign keys can be specified as part of the SQL crea

13、te table statement:The primary key clause lists attributes that comprise the primary key.The unique key clause lists attributes that comprise a candidate key.The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By defau

14、lt, a foreign key references the primary key attributes of the referenced table.Referential Integrity in SQL Examplecreate table customer(customer_namechar(20),customer_streetchar(30),customer_citychar(30),primary key (customer_name )create table branch(branch_namechar(15),branch_citychar(30),assets

15、numeric(12,2),primary key (branch_name )Referential Integrity in SQL Example (Cont.)create table account(account_numberchar(10),branch_namechar(15),balanceinteger,primary key (account_number), foreign key (branch_name) references branch )create table depositor(customer_namechar(20),account_numbercha

16、r(10),primary key (customer_name, account_number),foreign key (account_number ) references account,foreign key (customer_name ) references customer )AssertionsAn assertion is a predicate expressing a condition that we wish the database always to satisfy.An assertion in SQL takes the formcreate asser

17、tion check When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertionThis testing may introduce a significant amount of overhead; hence assertions should be used with great care.Asserting for all X, P(X) is achieved in a round-about

18、 fashion using not exists X such that not P(X)Assertion ExampleEvery loan has at least one borrower 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 whe

19、re loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and depositor.account_number = account.account_number and account.balance = 1000)Assertion ExampleThe sum of all loan amounts for each branch must be less than the sum of all account balances at the branc

20、h. create assertion sum_constraint check (not exists (select * from branch where (select sum(amount ) from loan where loan.branch_name = branch.branch_name ) = (select sum (amount ) from account where loan.branch_name = branch.branch_name )AuthorizationForms of authorization on parts of the database

21、:Read - allows reading, but not modification of data.Insert - allows insertion of new data, but not modification of existing data.Update - allows modification, but not deletion of data.Delete - allows deletion of data.Forms of authorization to modify the database schema (covered in Chapter 8):Index

22、- allows creation and deletion of indices.Resource - allows creation of new relations.Alteration - allows addition or deletion of attributes in a relation.Drop - allows deletion of relations.Authorization Specification in SQLThe grant statement is used to confer authorizationgrant on to is:a user-id

23、public, which allows all valid users the privilege grantedA role (more on this in Chapter 8)Granting a privilege on a view does not imply granting any privileges on the underlying relations.The grantor of the privilege must already hold the privilege on the specified item (or be the database adminis

24、trator).Privileges in SQLselect: allows read access to relation,or the ability to query using the viewExample: grant users U1, U2, and U3 select authorization on the branch relation:grant select on branch to U1, U2, U3insert: the ability to insert tuplesupdate: the ability to update using the SQL up

25、date statementdelete: the ability to delete tuples.all privileges: used as a short form for all the allowable privilegesmore in Chapter 8Revoking Authorization in SQLThe revoke statement is used to revoke authorization.revoke on from Example:revoke select on branch from U1, U2, U3 may be all to revo

26、ke all privileges the revokee may hold.If includes public, all users lose the privilege except those granted it explicitly.If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation.All privileges that depend on the privileg

27、e being revoked are also revoked.Stored Procedure 存儲(chǔ)過程將常用的或很復(fù)雜的工作，預(yù)先用SQL語(yǔ)句寫好并用一個(gè)指定的名稱存儲(chǔ)起來，當(dāng)需要數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過程的功能相同的服務(wù)時(shí)，直接調(diào)用即可自動(dòng)完成命令。存儲(chǔ)過程就像函數(shù)一樣，也具有傳遞參數(shù)和執(zhí)行邏輯表達(dá)式的功能，但存儲(chǔ)過程又與函數(shù)不同，因?yàn)榇鎯?chǔ)過程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。由于存儲(chǔ)過程提供了一種高效和安全的訪問數(shù)據(jù)庫(kù)的方法，它經(jīng)常被用來訪問數(shù)據(jù)和管理被修改的數(shù)據(jù)。SQL ProceduresDefine a procedure that, given the na

28、me of a customer, returns the count of the number of accounts owned by the customer.create procedure 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 endProcedures can

29、 be invoked either from an SQL procedure or from embedded SQL, using the call statement.declare a_count integer;call account_count_proc( Smith, a_count);Procedures and functions can be invoked also from dynamic SQLProcedural ConstructsCompound statement: begin end, May contain multiple SQL statement

30、s between begin and end.Local variables can be declared within a compound statementsWhile and repeat statements:declare n integer default 0;while n 10 do set n = n + 1end whilerepeat set n = n 1until n = 0end repeatProcedural Constructs (Cont.)For loopPermits iteration over all results of a queryExa

31、mple: find total of all balances at the Perryridge branch declare n integer default 0; for r as select balance from account where branch_name = Perryridge do set n = n + r.balance end forProcedural Constructs (cont.)Conditional statements (if-then-else)E.g. To find sum of balances for each of three

32、categories of accounts (with balance =1000 and = 5000)if r.balance 1000 then set l = l + r.balanceelseif r.balance 5000 then set m = m + r.balanceelse set h = h + r.balanceend if 存儲(chǔ)過程具有以下優(yōu)點(diǎn)存儲(chǔ)過程允許標(biāo)準(zhǔn)組件式編程存儲(chǔ)過程在被創(chuàng)建以后可以在程序中被多次調(diào)用，而不必重新編寫該存儲(chǔ)過程的SQL 語(yǔ)句。而且數(shù)據(jù)庫(kù)專業(yè)人員可隨時(shí)對(duì)存儲(chǔ)過程進(jìn)行修改，但對(duì)應(yīng)用程序源代碼毫無(wú)影響（因?yàn)閼?yīng)用程序源代碼只包含存儲(chǔ)過程的調(diào)用語(yǔ)

33、句），從而極大地提高了程序的可移植性。存儲(chǔ)過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度如果某一操作包含大量的Transaction-SQL 代碼或分別被多次執(zhí)行，那么存儲(chǔ)過程要比批處理的執(zhí)行速度快很多。因?yàn)榇鎯?chǔ)過程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過程時(shí)，查詢優(yōu)化器對(duì)其進(jìn)行分析、優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計(jì)劃。而批處理的Transaction- SQL 語(yǔ)句在每次運(yùn)行時(shí)都要進(jìn)行編譯和優(yōu)化，因此速度相對(duì)要慢一些。存儲(chǔ)過程具有以下優(yōu)點(diǎn)存儲(chǔ)過程能夠減少網(wǎng)絡(luò)流量對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象的操作（如查詢、修改），如果這一操作所涉及到的 Transaction-SQL 語(yǔ)句被組織成一存儲(chǔ)過程，那么當(dāng)在客戶計(jì)算機(jī)上調(diào)

34、用該存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語(yǔ)句，否則將是多條SQL 語(yǔ)句，從而大大增加了網(wǎng)絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。存儲(chǔ)過程可被作為一種安全機(jī)制來充分利用系統(tǒng)管理員通過對(duì)執(zhí)行某一存儲(chǔ)過程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問權(quán)限的限制，避免非授權(quán)用戶對(duì)數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。觸發(fā)器 Trigger觸發(fā)器是在對(duì)表進(jìn)行插入（INSERT）、更新（UPDATE）或刪除（DELETE）操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程，它是一種特殊類型的存儲(chǔ)過程。 觸發(fā)器這種特殊類型的存儲(chǔ)過程，是在基表被修改時(shí)自動(dòng)執(zhí)行的內(nèi)嵌過程。 觸發(fā)器是獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中的獨(dú)立對(duì)象。觸發(fā)器是現(xiàn)代數(shù)據(jù)庫(kù)管理系統(tǒng)用于響應(yīng)數(shù)據(jù)變化的一種機(jī)制。觸

35、發(fā)器是實(shí)現(xiàn)數(shù)據(jù)維護(hù)規(guī)則的有效機(jī)制。 Trigger Example如果有人試圖在 Customer 表中添加或更改數(shù)據(jù)，下列 DML 觸發(fā)器將向客戶端顯示一條消息。CREATE TRIGGER reminder1ON Sales.CustomerAFTER INSERT, UPDATE AS RAISERROR (Notify Customer Relations, 16, 10);GO觸發(fā)器用途 比較不同版本的數(shù)據(jù)。讀取其他數(shù)據(jù)庫(kù)的表中的數(shù)據(jù)。在數(shù)據(jù)庫(kù)中所有的相關(guān)表中級(jí)聯(lián)所作的修改或刪除回滾無(wú)效的修改。強(qiáng)制實(shí)現(xiàn)比由CHECK約束提供的限制更為復(fù)雜的限制。執(zhí)行本地和遠(yuǎn)程存儲(chǔ)過程。ODBC(Op

36、en DataBase Connectivty：開放數(shù)據(jù)庫(kù)互連)ODBC是Microsoft Windows Open Standards Architecture (WOSA,Windows開放服務(wù)體系)的重要組成部分,由Microsoft公司于1991年底發(fā)布,短短幾年已成為事實(shí)上的工業(yè)標(biāo)準(zhǔn).它建立了一組規(guī)范,提供了一套分層(隨著層的擴(kuò)展,數(shù)據(jù)服務(wù)能力不斷的增強(qiáng))的標(biāo)準(zhǔn)API(支持SQL),它解決了嵌入式SQL接口的非規(guī)范核心,數(shù)據(jù)應(yīng)用系統(tǒng)用它來訪問任何提供了ODBC驅(qū)動(dòng)程序(一組DLL)的數(shù)據(jù)庫(kù),結(jié)束了過去針對(duì)不同的數(shù)據(jù)庫(kù)系統(tǒng)開發(fā)須掌握相應(yīng)數(shù)據(jù)訪問API的時(shí)代.事實(shí)上,可將ODBC體系看

37、作統(tǒng)一的數(shù)據(jù)訪問界面,而使這種統(tǒng)一的數(shù)據(jù)訪問成為可能的就是各數(shù)據(jù)庫(kù)產(chǎn)品廠商提供的相應(yīng)的ODBC Provider(ODBC提供者即ODBC 驅(qū)動(dòng))。ODBC應(yīng)用的結(jié)構(gòu)應(yīng)用程序應(yīng)用程序應(yīng)用程序ODBC DRIVER MANAGERORACLE DRIVERDB2 DRIVERSYBASE DRIVERSQL SERVER DRIVERORACL DBMSDB2 DBMSSYBASE DBMSSQL SERVERDBMSOdbc APIOdbc APIODBC的體系結(jié)構(gòu)（1）用戶的ODBC應(yīng)用程序驅(qū)動(dòng)程序管理器(ODBC Driver Manager)微軟提供數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序(ODBC Driver

38、)DBMS廠商提供數(shù)據(jù)庫(kù)源(Data Source)ODBC的體系結(jié)構(gòu)（2）用戶的ODBC應(yīng)用程序如何和數(shù)據(jù)庫(kù)交互ODBC API提供的接口函數(shù)SQLODBC的體系結(jié)構(gòu)（3）驅(qū)動(dòng)程序管理器(ODBC Driver Manager)的主要作用管理應(yīng)用程序和驅(qū)動(dòng)程序的通信裝載、管理驅(qū)動(dòng)程序管理和配置數(shù)據(jù)源負(fù)責(zé)的主要文件system32odbc32.dllODBC的體系結(jié)構(gòu)（4）數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序(ODBC Driver)的作用對(duì)相應(yīng)的數(shù)據(jù)源進(jìn)行各種操作數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序(ODBC Driver)的調(diào)用方式以動(dòng)態(tài)鏈接庫(kù)形式(*.DLL)存在由應(yīng)用程序命令裝載，由驅(qū)動(dòng)程序管理器負(fù)責(zé)裝載ODBC的體系結(jié)構(gòu)（5）數(shù)據(jù)源管理數(shù)據(jù)源名（Data Source Name）對(duì)應(yīng)著一個(gè)具體數(shù)據(jù)庫(kù)的連接包括了服務(wù)器名、驅(qū)動(dòng)程序、數(shù)據(jù)庫(kù)名等信息使用ODBC的第一步是首先要建立數(shù)據(jù)源名JDBC提供了一組標(biāo)準(zhǔn)API，用于訪問關(guān)系數(shù)據(jù)庫(kù)API類庫(kù)位于java.sql.*和java.sqlx.*中為Applet、Servlet和應(yīng)用程序提供了統(tǒng)一