数据库讲义ch11ch12.docx
《数据库讲义ch11ch12.docx》由会员分享，可在线阅读，更多相关《数据库讲义ch11ch12.docx（21页珍藏版）》请在冰豆网上搜索。
数据库讲义ch11ch12
11数据定义语言
前面描述的SELECT，INSERT，UPDATE，DELETE，COMMIT和ROLLBACK语句都可用来操纵数据库的数据。
这些语句统称为SQL数据操纵语言或DML（DataManipulationLanguage）。
DML语句可以用来修改数据库里的数据，但是不能修改数据库的结构。
例如，这些语句不能创建或删除表或字段。
另外一组SQL语句用来修改数据库的结构，通常称为SQL数据定义语言或者DDL（DataDefinitionLanguage），运用DDL语句，你可以：
定义和创建一个新表
删除一个不再需要的表
改变一个现存表的定义
定义数据的虚表（或者视图）
为数据库建立安全性控制
建立一个索引，使访问表的速度更快
通过DBMS，可以控制数据的物理存储
数据定义语言的核心基于三个SQL谓词。
CREATE，定义和创建一个数据库对象。
DROP，删除一个数据库对象。
ALTER，更改一个数据库对象。
11.1表定义
11.1.1创建一个表
字段定义：
表约束定义：
主键约束定义：
外键约束定义：
唯一约束定义：
检查约束定义：
11.1.2修改一个表
11.1.3删除一个表
11.1.4例子
SQLServer2000：
CREATETABLE[dbo].[ORDERS]（
[order_num][int]NOTNULL,
[order_date][datetime]NOTNULL,
[cust][int]NOTNULL,
[rep][int]NULL,
[mfr][varchar]（3）COLLATEChinese_PRC_CI_ASNOTNULL,
[product][char]（5）COLLATEChinese_PRC_CI_ASNOTNULL,
[qty][int]NOTNULL,
[amount][money]NOTNULL
）ON[PRIMARY]
GO
ALTERTABLE[dbo].[ORDERS]WITHNOCHECKADD
CONSTRAINT[PK_ORDERS]PRIMARYKEYCLUSTERED
（
[order_num],[...]
）ON[PRIMARY]
GO
ALTERTABLE[dbo].[ORDERS]ADD
CONSTRAINT[ISFOR]FOREIGNKEY
（
[mfr],
[product]
）REFERENCES[dbo].[PRODUCTS]（
[mfr_id],
[product_id]
）,
CONSTRAINT[PLACEDBY]FOREIGNKEY
（
[cust]
）REFERENCES[dbo].[CUSTOMERS]（
[cust_num]
）ONDELETECASCADE,
CONSTRAINT[TAKENBY]FOREIGNKEY
（
[rep]
）REFERENCES[dbo].[SALESREPS]（
[empl_num]
）
GO
ORACLE：
--Createtable
createtableORDERS
（
ORDER_NUMNUMBER（10）notnull,
ORDER_DATEDATEnotnull,
CUSTNUMBER（10）notnull,
REPNUMBER（10）,
MFRCHAR（3）notnull,
PRODUCTCHAR（5）notnull,
QTYNUMBER（10）notnull,
AMOUNTNUMBER（19,4）notnull
）
tablespaceUSERS
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial128K
next128K
minextents1
maxextents4096
pctincrease0
）;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableORDERS
addconstraintPK_ORDERSprimarykey（ORDER_NUM）
usingindex
tablespaceUSERS
pctfree10
initrans2
maxtrans255
storage
（
initial128K
next128K
minextents1
maxextents4096
pctincrease0
）;
altertableORDERS
addconstraintISFORforeignkey（MFR,PRODUCT）
referencesPRODUCTS（MFR_ID,PRODUCT_ID）;
altertableORDERS
addconstraintPLACEDBYforeignkey（CUST）
referencesCUSTOMERS（CUST_NUM）ondeletecascade;
altertableORDERS
addconstraintTAKENBYforeignkey（REP）
referencesSALESREPS（EMPL_NUM）;
11.2索引定义
索引是大多数SQL数据库管理系统提供的一种物理存储结构。
索引是一种结构，它基于一个或多个字段的值，提供对表中行的快速访问。
下图展示了Products表和两个创建在它上面的索引。
一个索引基于Description字段提供访问。
另一种基于该表的主键提供访问，该主键由MFR_ID字段和PRODUCT_ID字段组成。
DBMS使用索引就像你使用一本书的索引一样。
索引存储那些在行上的数据值和指向行的指针。
在索引里，数据值按升序或者降序排列，这样，DBMS可以快速搜索索引，来寻找一个特定的值。
然后，DBMS可以顺着指针找到包含该值的行。
对于进入表的SQL用户来讲，用或不用索引是完全透明的。
例如，考虑这样的SELECT语句。
寻找Size4widgets的数量和价格。
selectqty_on_hand,price
fromproduccts
wheredescription=’Size4Widgets’
该语句没有说明基于Description字段是否存在一个索引，DBMS可能在两种情况的任一种情况下执行这次查询：
如果没有Description字段的索引，DBMS处理的这次查询将按顺序扫描Products表，一行接一行，在每一行里检查Description字段。
为了确保DBMS能找到所有满足搜索条件的行，它必须检查表里的每一行。
对于一个具有成千上百万行的大型表来说，对表的扫描可能需要几分钟或者几小时。
以Description字段为索引，DBMS可以用少得多的工作去定位被请求的数据。
它搜索索引以便找到被请求的值（“Size4Widgets”），然后沿着指针找到表中被请求的行。
索引搜索非常快，这是因为索引是排好序的，并且索引的行非常小。
因为索引告诉DBMS该行在磁盘上的位置，从索引转到行非常快。
通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。
索引将占用磁盘空间，并且降低添加、删除和更新行的速度。
不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处。
然而，如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。
基本的CREATEINDEX语句的语法图：
为ORDERS表创建一个索引
createindexord_prod_idxonorders（mfr,product）
删除先前创建的索引
dropindexord_prod_idx
11.3视图
视图可以被看成是虚拟表或存储查询。
可通过视图访问的数据不作为独特的对象存储在数据库内。
数据库内存储的是SELECT语句。
SELECT语句的结果集构成视图所返回的虚拟表。
用户可以用引用表时所使用的方法，在Transact-SQL语句中通过引用视图名称来使用虚拟表。
使用视图可以实现下列任一或所有功能：
将用户限定在表中的特定行上。
例如，只允许雇员看见工作跟踪表内记录其工作的行。
将用户限定在特定列上。
例如，对于那些不负责处理工资单的雇员，只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列，而不能看见任何包含工资信息或个人信息的列。
将多个表中的列联接起来，使它们看起来象一个表。
聚合信息而非提供详细信息。
11.4其他数据对象的定义
12存储过程
12.1创建存储过程
语法：
CREATEPROC[EDURE]procedure_name[;number]
（
[{@parameterdata_type}[=default][OUTPUT]
][,...n]
）
ASsql_statement[...n]
12.1.1使用带有复杂SELECT语句的简单过程
下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。
该存储过程不使用任何参数。
CREATEPROCEDUREau_info_all
AS
SELECTau_lname,au_fname,title,pub_name
FROMauthorsaINNERJOINtitleauthorta
ONa.au_id=ta.au_idINNERJOINtitlest
ONt.title_id=ta.title_idINNERJOINpublishersp
ONt.pub_id=p.pub_id
12.1.2使用带有参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。
该存储过程接受与传递的参数精确匹配的值。
CREATEPROCEDUREau_info
@lastnamevarchar（40）,
@firstnamevarchar（20）
AS
SELECTau_lname,au_fname,title,pub_name
FROMauthorsaINNERJOINtitleauthorta
ONa.au_id=ta.au_idINNERJOINtitlest
ONt.title_id=ta.title_idINNERJOINpublishersp
ONt.pub_id=p.pub_id
WHEREau_fname=@firstname
ANDau_lname=@lastname
12.1.3使用OUTPUT参数
OUTPUT参数允许外部过程、批处理或多条Transact-SQL语句访问在过程执行期间设置的某个值。
下面的示例创建一个存储过程（titles_sum），并使用一个可选的输入参数和一个输出参数。
CREATEPROCEDUREtitles_sum
@@TITLEvarchar（40）='%',
@@SUMmoneyOUTPUT
AS
SELECT’TitleName’=title
FROMtitles
WHEREtitleLIKE@@TITLE
SELECT@@SUM=SUM（price）
FROMtitles
WHEREtitleLIKE@@TITLE
12.2变量的声明与赋值
12.2.1声明变量的语法
DECLARE
{{@local_variabledata_type}
|{@cursor_variable_nameCURSOR}
|{table_type_definition}
}[,...n]
12.2.2变量的赋值
SET{@local_variable=expression}
SELECT{@local_variable=expression}[,...n]
将先前使用DECLARE@local_variable语句创建的指定局部变量设置为给定的值。
@local_variable：
是给其赋值的声明变量。
expression：
是任何有效的Microsoft®SQLServer™表达式，包括标量子查询。
DECLARE@var1nvarchar（30）
SELECT@var1='GenericName'
SELECT@var1=
（SELECTCompanyName
FROMCustomers
WHERECustomerID='ALFKA'）
12.3控制流
Transact-SQL提供称为控制流语言的特殊关键字，用于控制Transact-SQL语句、语句块和存储过程的执行流。
12.3.1BEGIN...END
包括一系列的Transact-SQL语句，使得可以执行一组Transact-SQL语句。
BEGIN和END是控制流语言的关键字。
语法
BEGIN
{
sql_statement|statement_block
}
END
BEGIN...END语句块允许嵌套。
12.3.2IF...ELSE
在执行Transact-SQL语句时强加条件。
如果条件满足（布尔表达式返回TRUE时），则在IF关键字及其条件之后执行Transact-SQL语句。
可选的ELSE关键字引入备用的Transact-SQL语句，当不满足IF条件时（布尔表达式返回FALSE），就执行这个语句。
语法
IFBoolean_expression
{sql_statement|statement_block}
[ELSE
{sql_statement|statement_block}]
12.3.3WHILE
设置重复执行SQL语句或语句块的条件。
只要指定的条件为真，就重复执行语句。
可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。
语法
WHILEBoolean_expression
{sql_statement|statement_block}
[BREAK]
{sql_statement|statement_block}
[CONTINUE]
例
WHILE（SELECTAVG（price）FROMtitles）<$30
BEGIN
UPDATEtitles
SETprice=price*2
SELECTMAX（price）FROMtitles
IF（SELECTMAX（price）FROMtitles）>$50
BREAK
ELSE
CONTINUE
END
12.3.4WAITFOR
指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。
语法
WAITFOR{DELAY'time'|TIME'time'}
参数
DELAY
指示Microsoft®SQLServer™一直等到指定的时间过去，最长可达24小时。
'time'
要等待的时间。
可以按datetime数据可接受的格式指定time，也可以用局部变量指定此参数。
不能指定日期。
因此，在datetime值中不允许有日期部分。
TIME
指示SQLServer等待到指定时间。
示例
12.3.4.1使用WAITFORTIME
下例在晚上10:
20执行存储过程update_all_stats。
BEGIN
WAITFORTIME'22:
20'
EXECUTEupdate_all_stats
END
12.3.4.2使用WAITFORDELAY
下例说明如何对WAITFORDELAY选项使用局部变量。
将创建一个存储过程，该过程将等待可变的时间量，然后将所经过的小时、分钟和秒数信息返回给用户。
CREATEPROCEDUREtime_delay@@DELAYLENGTHchar（9）
AS
DECLARE@@RETURNINFOvarchar（255）
BEGIN
WAITFORDELAY@@DELAYLENGTH
SELECT@@RETURNINFO='Atotaltimeof'+
SUBSTRING（@@DELAYLENGTH,1,3）+
'hours,'+
SUBSTRING（@@DELAYLENGTH,5,2）+
'minutes,and'+
SUBSTRING（@@DELAYLENGTH,8,2）+
'seconds,'+
'haselapsed!
Yourtimeisup.'
PRINT@@RETURNINFO
END
12.3.5GOTO
将执行流变更到标签处。
跳过GOTO之后的Transact-SQL语句，在标签处继续处理。
GOTO语句和标签可在过程、批处理或语句块中的任何位置使用。
GOTO语句可嵌套使用。
语法
定义标签：
label:
改变执行：
GOTOlabel
参数
label
若有GOTO语句指向此标签，则其为处理的起点。
标签必须符合标识符规则。
不论是否使用GOTO语句，标签均可作为注释方法使用。
示例
DECLARE@tablenamesysname
SET@tablename=N'authors'
table_loop:
IF（@@FETCH_STATUS<>-2）
BEGIN
SELECT@tablename=RTRIM（UPPER（@tablename））
EXEC（"SELECT"""+@tablename+"""=COUNT（*）FROM"
+@tablename）
PRINT""
END
FETCHNEXTFROMtnames_cursorINTO@tablename
IF（@@FETCH_STATUS<>-1）GOTOtable_loop
12.3.6RETURN
从查询或过程中无条件退出。
RETURN即时且完全，可在任何时候用于从过程、批处理或语句块中退出。
不执行位于RETURN之后的语句。
语法
RETURN[integer_expression]
参数
integer_expression
是返回的整型值。
存储过程可以给调用过程或应用程序返回整型值。
示例
12.3.6.1从过程返回
CREATEPROCEDUREfindjobs@nmsysname=NULL
AS
IF@nmISNULL
BEGIN
PRINT'Youmustgiveausername'
RETURN
END
ELSE
BEGIN
SELECTo.name,o.id,o.uid
FROMsysobjectsoINNERJOINmaster..sysloginsl
ONo.uid=l.sid
WHEREl.name=@nm
END
12.3.6.2返回状态代码
下例检查指定作者所在州的ID。
如果所在的州是加利福尼亚州（CA），将返回状态代码1。
否则，对于任何其它情况（state的值是CA以外的值或者au_id没有匹配的行），将返回状态代码2。
CREATEPROCEDUREcheckstate@paramvarchar（11）
AS
IF（SELECTstateFROMauthorsWHEREau_id=@param）='CA'
RETURN1
ELSE
RETURN2
12.4游标
关系数据库中的操作会对整个行集产生影响。
由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。
由语句所返回的这一完整的行集被称为结果集。
应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。
这些应用程序需要一种机制以便每次处理一行或一部分行。
游标就是提供这种机制的结果集扩展。
声明游标
DECLAREcursor_nameCURSOR
FORselect_statement
打开游标
OPEN{cursor_name}
检索游标中特定的一行
FETCH[NEXT|PRIOR|FIRST|LAST]
FROM{cursor_name}
[INTO@variable_name[,...n]]
检查游标状态
@@FETCH_STATUS
@@FETCH_STATUS=0时FETCH语句成功。
关闭游标
CLOSE{cursor_name}
删除游标
DEALLOCATE{cursor_name}
示例
USEpubs
GO
--DeclarethevariablestostorethevaluesreturnedbyFETCH.
DECLARE@au_lnamevarchar（40）,@au_fnamevarchar（20）
DECLAREauthors_cursorCURSORFOR
SELECTau_lname,au_fnameFROMauthors
WHEREau_lnameLIKE"B%"
ORDERBYau_lname,au_fname
OPENauthors_cursor
--Performthefirstfetchandstorethevaluesinvariables.
--Note:
Thevariablesareinthesameorderasthecolumns
--intheSELECTstatement.
FETCHNEXTFROMauthors_cursor
INTO@au_l
升级会员 