数据库讲义ch11ch12.docx

1、数据库讲义ch11ch1211 数据定义语言前面描述的SELECT，INSERT，UPDATE，DELETE，COMMIT和ROLLBACK语句都可用来操纵数据库的数据。这些语句统称为SQL数据操纵语言或DML（Data Manipulation Language）。DML语句可以用来修改数据库里的数据，但是不能修改数据库的结构。例如，这些语句不能创建或删除表或字段。另外一组SQL语句用来修改数据库的结构，通常称为SQL数据定义语言或者DDL（Data Definition Language），运用DDL语句，你可以： 定义和创建一个新表 删除一个不再需要的表 改变一个现存表的定义 定义数据的

2、虚表(或者视图) 为数据库建立安全性控制 建立一个索引，使访问表的速度更快 通过DBMS，可以控制数据的物理存储数据定义语言的核心基于三个SQL谓词。 CREATE，定义和创建一个数据库对象。 DROP，删除一个数据库对象。 ALTER，更改一个数据库对象。11.1 表定义11.1.1 创建一个表字段定义：表约束定义：主键约束定义：外键约束定义：唯一约束定义：检查约束定义：11.1.2 修改一个表11.1.3 删除一个表11.1.4 例子SQL Server 2000：CREATE TABLE dbo.ORDERS ( order_num int NOT NULL , order_date d

3、atetime NOT NULL , cust int NOT NULL , rep int NULL , mfr varchar (3) COLLATE Chinese_PRC_CI_AS NOT NULL , product char (5) COLLATE Chinese_PRC_CI_AS NOT NULL , qty int NOT NULL , amount money NOT NULL ) ON PRIMARY GOALTER TABLE dbo.ORDERS WITH NOCHECK ADD CONSTRAINT PK_ORDERS PRIMARY KEY CLUSTERED

4、( order_num , . ) ON PRIMARY GOALTER TABLE dbo.ORDERS ADD CONSTRAINT ISFOR FOREIGN KEY ( mfr, product ) REFERENCES dbo.PRODUCTS ( mfr_id, product_id ), CONSTRAINT PLACEDBY FOREIGN KEY ( cust ) REFERENCES dbo.CUSTOMERS ( cust_num ) ON DELETE CASCADE , CONSTRAINT TAKENBY FOREIGN KEY ( rep ) REFERENCES

5、 dbo.SALESREPS ( empl_num ) GOORACLE：- Create tablecreate table ORDERS ( ORDER_NUM NUMBER(10) not null, ORDER_DATE DATE not null, CUST NUMBER(10) not null, REP NUMBER(10), MFR CHAR(3) not null, PRODUCT CHAR(5) not null, QTY NUMBER(10) not null, AMOUNT NUMBER(19,4) not null)tablespace USERS pctfree 1

6、0 pctused 40 initrans 1 maxtrans 255 storage ( initial 128K next 128K minextents 1 maxextents 4096 pctincrease 0 );- Create/Recreate primary, unique and foreign key constraints alter table ORDERS add constraint PK_ORDERS primary key (ORDER_NUM) using index tablespace USERS pctfree 10 initrans 2 maxt

7、rans 255 storage ( initial 128K next 128K minextents 1 maxextents 4096 pctincrease 0 );alter table ORDERS add constraint ISFOR foreign key (MFR, PRODUCT) references PRODUCTS (MFR_ID, PRODUCT_ID); alter table ORDERS add constraint PLACEDBY foreign key (CUST) references CUSTOMERS (CUST_NUM) on delete

8、cascade; alter table ORDERS add constraint TAKENBY foreign key (REP) references SALESREPS (EMPL_NUM);11.2 索引定义索引是大多数SQL数据库管理系统提供的一种物理存储结构。索引是一种结构，它基于一个或多个字段的值，提供对表中行的快速访问。下图展示了Products表和两个创建在它上面的索引。一个索引基于Description字段提供访问。另一种基于该表的主键提供访问，该主键由MFR_ID字段和PRODUCT_ID字段组成。DBMS使用索引就像你使用一本书的索引一样。索引存储那些在行上的数据值

9、和指向行的指针。在索引里，数据值按升序或者降序排列，这样，DBMS可以快速搜索索引，来寻找一个特定的值。然后，DBMS可以顺着指针找到包含该值的行。对于进入表的SQL用户来讲，用或不用索引是完全透明的。例如，考虑这样的SELECT语句。寻找Size 4 widgets的数量和价格。select qty_on_hand, price from produccts where description=Size 4 Widgets该语句没有说明基于Description字段是否存在一个索引，DBMS可能在两种情况的任一种情况下执行这次查询：如果没有Description字段的索引，DBMS处理的这次

10、查询将按顺序扫描Products表，一行接一行，在每一行里检查Description字段。为了确保DBMS能找到所有满足搜索条件的行，它必须检查表里的每一行。对于一个具有成千上百万行的大型表来说，对表的扫描可能需要几分钟或者几小时。以Description字段为索引，DBMS可以用少得多的工作去定位被请求的数据。它搜索索引以便找到被请求的值(“Size 4 Widgets”)，然后沿着指针找到表中被请求的行。索引搜索非常快，这是因为索引是排好序的，并且索引的行非常小。因为索引告诉DBMS该行在磁盘上的位置，从索引转到行非常快。通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索

11、引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处。然而，如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。基本的CREATE INDEX语句的语法图：为ORDERS表创建一个索引create index ord_prod_idx on orders(mfr,product)删除先前创建的索引drop index ord_prod_idx11.3 视图视图可以被看成是虚拟表或存储查询。可通过视图访问的数据不作为独特的对象存储在数据库内。数据库内存储的是 SELECT 语句。SELECT 语句的结果集构成

12、视图所返回的虚拟表。用户可以用引用表时所使用的方法，在 Transact-SQL 语句中通过引用视图名称来使用虚拟表。使用视图可以实现下列任一或所有功能： 将用户限定在表中的特定行上。例如，只允许雇员看见工作跟踪表内记录其工作的行。 将用户限定在特定列上。例如，对于那些不负责处理工资单的雇员，只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列，而不能看见任何包含工资信息或个人信息的列。 将多个表中的列联接起来，使它们看起来象一个表。 聚合信息而非提供详细信息。11.4 其他数据对象的定义12 存储过程12.1 创建存储过程语法：CREATE PROCEDURE procedure_n

13、ame ; number ( parameter data_type = default OUTPUT ,.n )AS sql_statement .n 12.1.1 使用带有复杂 SELECT 语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。CREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER

14、JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id12.1.2 使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。CREATE PROCEDURE au_info lastname varchar(40), firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a IN

15、NER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = firstname AND au_lname = lastname12.1.3 使用 OUTPUT 参数OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输

16、入参数和一个输出参数。CREATE PROCEDURE titles_sum TITLE varchar(40) = %, SUM money OUTPUTAS SELECT Title Name = title FROM titles WHERE title LIKE TITLE SELECT SUM = SUM(price) FROM titles WHERE title LIKE TITLE12.2 变量的声明与赋值12.2.1 声明变量的语法DECLARE local_variable data_type | cursor_variable_name CURSOR | table_ty

17、pe_definition ,.n12.2.2 变量的赋值SET local_variable = expression SELECT local_variable = expression ,.n 将先前使用 DECLARE local_variable 语句创建的指定局部变量设置为给定的值。local_variable：是给其赋值的声明变量。expression：是任何有效的 Microsoft SQL Server 表达式，包括标量子查询。DECLARE var1 nvarchar(30)SELECT var1 = Generic NameSELECT var1 = (SELECT Co

18、mpanyName FROM Customers WHERE CustomerID = ALFKA)12.3 控制流Transact-SQL 提供称为控制流语言的特殊关键字，用于控制 Transact-SQL 语句、语句块和存储过程的执行流。12.3.1 BEGIN.END包括一系列的 Transact-SQL 语句，使得可以执行一组 Transact-SQL 语句。BEGIN 和 END 是控制流语言的关键字。 语法BEGIN sql_statement | statement_block ENDBEGIN.END 语句块允许嵌套。12.3.2 IF.ELSE在执行 Transact-SQL

19、 语句时强加条件。如果条件满足（布尔表达式返回 TRUE 时），则在 IF 关键字及其条件之后执行 Transact-SQL 语句。可选的 ELSE 关键字引入备用的 Transact-SQL 语句，当不满足 IF 条件时（布尔表达式返回 FALSE），就执行这个语句。语法IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 12.3.3 WHILE设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE

20、 关键字在循环内部控制 WHILE 循环中语句的执行。语法WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE 例WHILE (SELECT AVG(price) FROM titles) $50 BREAK ELSE CONTINUEEND12.3.4 WAITFOR指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。语法WAITFOR DELAY time | TIME time 参数DELAY指示 Microsoft SQL Se

21、rver 一直等到指定的时间过去，最长可达 24 小时。time要等待的时间。可以按 datetime 数据可接受的格式指定 time，也可以用局部变量指定此参数。不能指定日期。因此，在 datetime 值中不允许有日期部分。TIME指示 SQL Server 等待到指定时间。示例12.3.4.1 使用 WAITFOR TIME下例在晚上 10:20 执行存储过程 update_all_stats。BEGIN WAITFOR TIME 22:20 EXECUTE update_all_statsEND12.3.4.2 使用 WAITFOR DELAY下例说明如何对 WAITFOR DELAY

22、 选项使用局部变量。将创建一个存储过程，该过程将等待可变的时间量，然后将所经过的小时、分钟和秒数信息返回给用户。CREATE PROCEDURE time_delay DELAYLENGTH char(9)ASDECLARE RETURNINFO varchar(255)BEGIN WAITFOR DELAY DELAYLENGTH SELECT RETURNINFO = A total time of + SUBSTRING(DELAYLENGTH, 1, 3) + hours, + SUBSTRING(DELAYLENGTH, 5, 2) + minutes, and + SUBSTRIN

23、G(DELAYLENGTH, 8, 2) + seconds, + has elapsed! Your time is up. PRINT RETURNINFOEND12.3.5 GOTO将执行流变更到标签处。跳过 GOTO 之后的 Transact-SQL 语句，在标签处继续处理。GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。GOTO 语句可嵌套使用。语法定义标签： label : 改变执行： GOTO label 参数label若有 GOTO 语句指向此标签，则其为处理的起点。标签必须符合标识符规则。不论是否使用 GOTO 语句，标签均可作为注释方法使用。示例DECLARE

24、 tablename sysnameSET tablename = Nauthorstable_loop: IF (FETCH_STATUS -2) BEGIN SELECT tablename = RTRIM(UPPER(tablename) EXEC (SELECT + tablename + = COUNT(*) FROM + tablename ) PRINT END FETCH NEXT FROM tnames_cursor INTO tablenameIF (FETCH_STATUS -1) GOTO table_loop12.3.6 RETURN从查询或过程中无条件退出。RETU

25、RN 即时且完全，可在任何时候用于从过程、批处理或语句块中退出。不执行位于 RETURN 之后的语句。语法RETURN integer_expression 参数integer_expression是返回的整型值。存储过程可以给调用过程或应用程序返回整型值。示例12.3.6.1 从过程返回CREATE PROCEDURE findjobs nm sysname = NULLAS IF nm IS NULL BEGIN PRINT You must give a username RETURN ENDELSE BEGIN SELECT o.name, o.id, o.uid FROM sysob

26、jects o INNER JOIN master.syslogins l ON o.uid = l.sid WHERE l.name = nm END12.3.6.2 返回状态代码下例检查指定作者所在州的 ID。如果所在的州是加利福尼亚州 (CA)，将返回状态代码 1。否则，对于任何其它情况（state 的值是 CA 以外的值或者 au_id 没有匹配的行），将返回状态代码 2。CREATE PROCEDURE checkstate param varchar(11)ASIF (SELECT state FROM authors WHERE au_id = param) = CA RETUR

27、N 1ELSE RETURN 212.4 游标关系数据库中的操作会对整个行集产生影响。由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。声明游标DECLARE cursor_name CURSOR FOR select_statement打开游标OPEN cursor_name 检索游标中特定的一行FETCH NEXT | PRIOR | FIRST | L

28、AST FROM cursor_name INTO variable_name ,.n 检查游标状态FETCH_STATUSFETCH_STATUS =0时FETCH 语句成功。关闭游标CLOSE cursor_name 删除游标DEALLOCATE cursor_name 示例USE pubsGO- Declare the variables to store the values returned by FETCH.DECLARE au_lname varchar(40), au_fname varchar(20)DECLARE authors_cursor CURSOR FORSELECT au_lname, au_fname FROM authorsWHERE au_lname LIKE B%ORDER BY au_lname, au_fnameOPEN authors_cursor- Perform the first fetch and store the values in variables.- Note: The variables are in the same order as the columns- in the SELECT statement. FETCH NEXT FROM authors_cursorINTO au_l