数据库系统与应用——第四章（续）

4.5.2多表连接查询操作
　　当一条查询涉及到多个表时，称为连接查询。连接查询在FROM子句中要写上所有有关的表名，在SELECT和WHERE子句中可引用任意有关表的属性名。当不同的表有相同的列名时，为了区分，可以在列名的前面加注表名（表名.属性名）。
　　主要的连接查询包括：
　(1) 相等和非相等连接
　(2) 自然连接：
　(3) 多元复合条件连接查询；
　(4) 自身连接（同一个表内）；
　(5) 外连接（连接表中有空值的情况）。
　　1. 相等连接和非相等连接
　　2．自然连接
　　3. 多元连接
　　4. 自身连接（同一个表内）
　　5. 外连接
　1. 相等连接和非相等连接
　　连接查询是从两个表中查询两个表的对应列值相等或不相等（进行大于、小于比较）的元组，进行并（UNION）操作后生成一张新表。连接运算的一般格式如下：
　　[<表1>.]<列名i> <比较运算符> [<表2>.]<列名j>
　　当比较运算符为相等时，称为等值连接。其他为非等值连接。连接条件中的进行连接运算的两个列必须是同类型的，必须是可比较的数据类型。
　　连接操作实际是先在两个关系之间实现广义笛卡儿积，再根据条件选择满足条件的元组组成新关系。
　　在图4-21中，有关系表A和B，如图4-21a)和b)，二者相等连接的条件是A.Y = B.U，相等连接产生的结果如图4-21c)。
例：列出所有任课教师的名字，去掉重复值：
select distinct tname 任课教师 from couse,teacher
where couse.tno=teacher.tno
输出结果：
任课教师
李力
张三
2．自然连接 　
　　在等值连接中，如果把目标列中重复的属性去掉，称为自然连接。
例1 ：查询JS2001班学生和其选修课情况：
select student.sno,sname,ssex,sage,sclass,cno,grade
from sc,student where student.sno=sc.sno and sclass=\"JS2001\"
输出结果：
J20001 李大 M 19 JS2001 G001 78
J20002 张三 F 20 JS2001 G002 85
本查询中，Sno属性在两个表中都有，查询结果只取一个，但是在WHERE子句中，为了区分二者，在Sno前面要加上表名前缀。
例2：统计各科成绩的最低、最高分和平均分，及课程名。
select cname,min(grade),max(grade),avg(grade) from sc,couse where sc.con=couse.cno group by cname
输出结果
cname,min(grade),max(grade),avg(grade)
vc    60         92         75
汇编  65         90         78

　3. 多元连接
　(1) 对两个以上的表进行连接查询。
　　例如，有学生表student，学生选课表sc，课程表course。查询数据结构课不及格的学生的学号、名字和成绩。SQL语句如下：
　　　SELECT student.sno,student.sname,grade FROM student, sc, course
　　　WHERE cname = \'DATA STRUCTURE\' AND grade < 60
　　　AND course.cno = sc.cno
　　　AND sc.sno = student.sno;
　　由于该查询的目标列分布在两个关系表中，学生的学号和名字在学生表student中，成绩在学生选课表sc中，课程名称在课程表course中，因此要用到三个表连接查询。
　(2) 使用别名查询
　　SQL语言中，允许对关系表使用别名，简化表名的书写。例如，前面的例子中，可以使用student的别名为 s，course的别名为 c，改写其如下：
　　　SELECT s.sno, s.sname,sc.grade FROM student s, sc, course c
　　　WHERE sc.cname = \'DATA STRUCTURE\' AND sc.grade < 60
　　　AND c.cno = sc.cno
　　　AND sc.sno = s.sno;使用JOIN和ON的连接查询
　　使用JOIN连接不同的表，使用ON给出两个表之间的连接条件。这是ANSI92标准进行多表查询的书写方式。
　例1，前面的例子可以改写如下：
　　SELECT s.sno, s.sname, sc.grade FROM student s JOIN sc ON sc.sno = s.sno
　　JOIN course c ON c.cno = sc.cno
　　WHERE sc.cname = \'DATA STRUCTURE\' AND sc.grade < 60

　例2：选择出大于平均销售数量的书的名字和价格：
　　SELECT titles.title, titles.price
　　FROM titles JOIN sales ON sales_id = titles_id
　　WHERE sales.qty > (SELECT AVG(qty) FROM sales)  

　4. 自身连接（同一个表内）
　自身连接是指使用同一个表的相同列进行比较连接。这时，对于同一个表应给出不同的别名。例如，查询每门课程的先修课程的先修课。可以在课程表中增加直接先修课属性，但是没有先修课程的先修课属性。查询必须先找到直接先修课程，再在同一个课程表中查找先修课程的先修课。构成对COURSE表的自身连接查询。

　例1，自身连接示例：有课程表如图4-22：为课程表取两个别名：Acourse和Bcourse.
自身连接查询SELECT Acourse.cno 课程号， Bcourse.cpno 先修课程的先修课
　　FROM course Acourse , course Bcourse
　　WHERE Acourse.cpno = Bcourse.cno输出结果如下：
　课程号, 先修课程的先修课程号
　　1 　　　　　　3
　　4
　　5  

　例2，选择具有相同价格的书
　　SELECT DISTINCT t1.price, t1.title
　　FROM titles t1 JOIN titles t2
　　ON t1.price = t2.price
　　AND t1.title_id <> t2.title_id

　5. 外连接
　　（连接表中有空值的情况）在前面连接中，返回到查询结果集中的仅是符合查询条件（WHERE搜索条件或HAVING条件）和连接条件的行。而采用外连接时，它返回到查询结果集中的不仅包含符合连接条件的行，而且还包括在左表（左外连接）、右表（右外连接）或两个连接表（全外连接）中的所有为空值的数据行。外连接分为左外连接，右外连接和全外连接。可以使用left outer join 、 right outer join 和full outer join分别表示左外连接、右外连接和全外连接。
　例1，查询每个学生的选课情况：学号、姓名、班级、课程号和成绩；假如某个学生没有选课，只输出学生的基本情况：学号、姓名、班级。
　　SELECT s.sno, sname, sclass, cno, grade
　　FROM student s , sc
　　WHERE s.sno = sc.sno(*)
　其中，在连接谓词的某边加符号* （SQL Server中使用 + 号）。在加*号的一边为空值的也作为查询结果输出。学生王者没有选课，查询结果如下：
　　s.sno sname sclass cno grade
　　J20001 李楷 Js2001 G001 78
　　J20002 张会 Js2001 G002 85
　　J20003 王者 JS2001

　例2 左外连接示例。假设在 city 列上连接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者；
　若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，使用左外连接。
　　USE pubs
　　SELECT a.au_fname, a.au_lname, p.pub_name
　　FROM authors a LEFT OUTER JOIN publishers p
　　ON a.city = p.city
　　ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
    不管是否与 publishers 表中的 city 列匹配，LEFT OUTER JOIN 均会在结果中包含 authors 表的所有行。注意：结果中所列的大多数作者都没有相匹配的数据，因此，这些行的 pub_name 列包含空值。

　例3，右外连接示例。若要在结果中包括所有的出版商，而不管城市中是否还有作者居住，使用右外连接。下面是右外连接的查询语句：
　　USE pubs
　　SELECT a.au_fname, a.au_lname, p.pub_name
　　FROM authors AS a RIGHT OUTER JOIN publishers AS p
　　ON a.city = p.city
　　ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

[ 本帖最后由 cayean 于 2007-1-26 11:22 编辑 ]

　　WHERE子句中参与比较的表达式还可以是由SELECT-FROM-WHERE结构组成的查询块，称为子查询或嵌套查询。连接条件本身可能是一个递归的 SQL 查询，而且可以嵌套多层。格式如下：
　　　SELECT … FROM …
　　　WHERE … =
　　(SELECT … FROM … WHERE … )
　　子查询的结果可以是单个数据值，元组的属性值，或一个新的关系表。子查询有多个返回值时，用比较符：
　　● (IN或NOT IN)R ：是或不是R中的一个值
　　● (<>，>，< )ALL R: 指'不等''大于'或'小于'R中的所有值。
　　● (=，>，< )ANY R: 指至少'等于''大于'或'小于'R中的一个值。

　例1，列出与李楷同班的同学的全部信息:
　　SELECT *
　　FROM STUDENT
　　WHERE sclass =（ SELECT sclass
　　FROM STUDENT
　　WHERE sname="李楷"）；
　输出结果如下：
　　sno sname ssex sage sclass
　　J20001 李 楷 m 19 JS2001
　　J20002 张 会 f 20 JS2001
　　J20003 王 者 m 20 JS2001

　例2，查找高于职工平均工资的职工信息：
　　SELECT *
　　FROM emptable
　　WHERE salary >（ SELECT AVG(salary)
　　FROM emptable）；

　例3．查询本学期选课超过8门的学生人数,没有成绩的选课表示本学期正在选修的课程。
　　SELECT COUNT(*)
　　FROM student
　　WHERE sno IN 注：选课>8的学号集 （ SELECT sno 选课的学号 FROM sc
　　WHERE grade IS NULL
　　GROUP BY sno 注：按学号分组 HAVING COUNT(*) > 8）；
4.5.4 联合查询
　　SELECT 查询的结果是元组的集合，多个查询结果可以进行集合的并(UNION)操作。
　　例如，查询选修了课程G001和选修了课程E001的学生的学号。
　　　SELECT sno
　　　FROM sc
　　　WHERE cno = " G001 " UNION
　　　SELECT sno
　　　FROM sc
　　　WHERE cno = " E001 "
4.6 视图的定义和操作
4.6.1 视图的基本概念
　　视图是查询结果的关系，是被存储的查询定义，视图的属性名由子查询确定。因此，视图数据在物理上是不存在的。但是可以看的，故称为视图。 视图是一个基于查询的逻辑表，视图本身并不存储数据，但它可以表示来自不同来源的数据，对应用程序来说，视图就相当一个表，数据可以从视图中查得，而且在权限许可的情况下，还可以通过视图来插入、更改和删除基本表中的数据。
　◆视图概念的优点：提供了一定的数据独立性，修改了基本表，通过建立视图，可不改变应用程序。
　◆ 通过视图简化了应用程序和用户查询；
　◆ 不同的用户通过视图从不同的观点观察数据；
　◆ 视图作为授权的单位提高了系统的安全性。
   
4.6.2视图的定义
　　由CREATE TABLE产生的表称为基本表，是数据库存储数据的基本单位。视图创建者必须拥有在视图定义中引用的任何对象的使用权方可创建视图。视图只能在当前数据库中建立。一个视图最多可以引用250个字段。视图的定义存放在数据字典中，只有在操作（查询或通过视图更新数据）视图时，系统才执行视图的子查询，产生视图数据。
定义视图的语句格式：
　　CREATE VIEW 视图名 [(列名[，列名]……)]
　　AS
　　　　子查询
　　　　[WITH CHECK OPTION]
　　视图名：在当前数据库中产生的视图名，一个视图可以参照当前数据库中的一个或多个表中的多个列。
　　列名：指视图的列名。视图的列名或者都指定，或者都不指定，缺省情况下，视图的列名与子查询中的目标列名相同。但下列情况必须明确指定列名：
　　· 视图中的列来自算术表达式、函数或常量；
　　· 查询子句中由于连接多个表，不同表中的列具有相同的列名；
　　· 视图中的列需要使用别名。
　　WITH CHECK OPTION选项：表示对视图的更新操作必须满足子查询语句中WHERE设置的条件。

　例1，在student表上定义视图student 2，视图具有3列属性：学号、姓名、班级。如下描述： CREATE VIEW student2
　AS
　　SELECT sno, sname, sclass
　　FROM student

　例2，在student表上建立视图，使用不同于基表的列名：
　CREATE VIEW student3（学号、姓名、班级）
　　AS
　　SELECT sno, sname, sclass
　　FROM student

　例3，在student表上定义视图csstud，只包含计算机系的学生。虽然视图中的数据只来源于student表，但是查询条件要用到班级名称和系的名称。视图定义如下描述：CREATE VIEW csstud
　AS
　　SELECT sno,sname, sex，birthday
　　FROM student，class，dept
　　WHERE student.sclass = class.classname
　　　　AND class.deptno = dept.deptno
　　　　AND deptname = '计算机'；
　可以定义一个基于视图的视图，而不是基于基本表的视图。也可以创建引用几个视图或者视图和表组合的视图。

　例4：建立第一个视图salesonly，是基于基本表创建的，第二个视图salespersons是基于第一个视图salesonly创建的。
　CREATE VIEW salesonly
　AS
　　SELECT name, department, badge
　　FROM employee
　　WHERE department='Sales'
　Go
　CREATE VIEW salespersons
　AS
　　SELECT name
　　FROM salesonly
4.6.3 视图的修改和删除
　 　　修改视图，就是修改数据字典中视图的定义。修改视图的语句格式:
　　ALTER  VIEW  视图名　AS　子查询
　　删除视图，就是从数据字典中撤消视图的定义。删除视图的语句格式:
　　　DROP VIEW 视图名

　　　例如，删除student2视图：
　　　　DROP VIEW student2

　　删除视图并不会影响被删除的视图所基于的基本表。只是视图的定义被从数据字典中删除。值得注意的是，如果允许在视图的定义中引用其他已定义的视图，当删除位于已经定义的另一视图内引用的视图时，其上定义的视图没有被删除，当调用该视图时，会返回错误。因此，由被删除的视图导出的视图也应该同时被删除。一般情况下，应当尽可能的基于基本表定义视图，而不是基于其他视图定义视图。
　
4.6.4.视图的操作
　 　
　 　　视图定义以后，用户可以象对基本表一样对视图进行操作，主要包括：
　◆ 查询视图：同对基本表的查询。
　◆ 更新视图：同对基本表的更新。
　1． 查询视图
　　由于视图是虚表，对视图的查询，是通过视图查询基本表中的数据。

　例1，查询视图student2，具有3个列属性：学号、姓名、班级。如下描述：
SELECT *　　FROM student2
　例2，查询视图csstud(只包含计算机系的学生)。如下描述：
SELECT *　　FROM csstud  
　　2 更新视图
　　由于视图是虚表，对视图的更新操作，是通过视图实现对基本表的插入、修改和删除数据的操作。为了防止通过视图对基本表中数据更新操作破坏数据的完整性，定义视图时可以使用WITH CHECK OPTION选项，检查更新数据是否满足基本表的约束条件。
　　在对视图的数据进行插入和修改的时候，和向基本表中插入数据一样，用户必须具有向基本表中插入数据的权限。如果视图上没有包括基本表中所有属性为NOT NULL的列，那么插入操作会因为那些列的值为NULL而失败。
　例3，对视图的更新操作示例：在视图student2中插入一个学生：
　　INSERT student2 VALUES("J20018","张 五"，"JS2002")
　例4，修改视图student2中一个学生的信息把学号由"J20038"改为"J20018"：
　UPDATE student2 SET sno = "J20038"WHERE sno = "J20018"例5，下面的例子向视图sales和表employees中 各插入了一行数据，name为Mark Leonard，department为Sales，badge为3331。如果插入的一行中department不为Sales，仍然能够向表employees中插入，但在视图Sales中检查不到这一行数据。视图的更新操作的语句如下：
　　CREATE VIEW sales
　　AS
　　　SELECT *
　　　FROM employees
　　　WHERE department='Sales'
　　Go
　　　INSERT INTO sales
　　　VALUES ('Mark Leonard', 'Sales', 3331)
　　通过视图更新的任何数据变化总是会影响基本表。视图允许建立虚表，它像基本表一样组织数据行，但是虚表是在视图被引用的时候动态创建的，把视图作为对数据的访问方式来使用是很方便的。
4.6.5. 使用视图的限制
　● 在一个基本表上建立的视图，只有包含基本表的主键才可以更新；
　● 一个视图最多只能有250个列；
　● 不能在视图上建立触发器和索引；
　● 对视图的一个更新语句只能影响一个基本表，所以由多表连接定义的视图不允许更新。
　● 定义视图语句不能使用UNION操作符。
　● 视图定义中用到GROUP BY子句或包含集合函数、计算列的数据不能修改。
　● 注意：不同的系统对视图的更新有不同的限制，使用时要参照具体的DBMS的说明。

　　SQL数据控制功能包括事务管理和并发控制，数据库的安全性和数据的完整性控制。本节只介绍SQL语言的安全性控制功能，就是把数据库对象的操作和存取权限授予用户或从用户手中收回权限。 DBMS通过SQL的GRANT授权语句或REVOKE撤消语句，把指定的权限授予或撤消权限的编排存入数据字典中，在用户操作数据时，DBMS根据数据字典中存入的授权情况，检查用户的操作的合法性。数据库中的权限包括CREATE、SELECT、INSERT、DELETE、UPDATE、INDEX、ALTER、REFERENCES、ALL等操作命令。
4.7.1授予权限GRANT
　 　　DBMS大都采用自主存取控制保证数据库数据的安全性。通过授权(authority)使不同的用户对不同的数据对象有不同的存取权限。SQL Server中的权限分为语句权限和对象权限。
   授予权限(GRANT)是指允许具有特定权限的用户有选择地、动态地把某些权限授予其他用户，必要时还可以收回这些权限。
　　授予用户权限的语句格式如下：
    GRANT 权限表 ON 操作对象
　　TO {用户1[，用户2]…..|PUBLIC}
　　[ WITH GRANT OPTION ]
　　参数说明:
　　ALL: 指定对象的所有权限授予用户。
　　PUBLIC: 将指定的权限授予所有用户。
　　WITH GRANT OPTION：它使得被授权的用户具有授权权限，即被授权的用户有权力将得到的指定权限再授予其他用户。
1. 语句权限
　　语句权限也称数据库级权限，主要是系统特权或DBA的权限。DBA或数据库的拥有者可以把某些SQL语句的执行命令授予其他用户，这种权限称为语句权限。语句权限包括：
　　CREATE DATABASE、 CREATE DEFAULT、 CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW、BACKUP DATABASE、BACKUP LOG
　　授予语句权限的格式如下：
　　GRANT { ALL | 语句权限1 [ ,...n ] }
　　TO 用户1 [ ,...n ]
例1，下面的示例给用户 Mary 和 John 以及 Windows NT 组 Corporate\BobJ 授予产生数据库和表的语句权限，如下描述：
　　GRANT CREATE DATABASE, CREATE TABLE
　　TO Mary, John, [Corporate\BobJ]
例2，将在数据库pubs上创建表、视图的权限授予用户'张力'
USE pubs
GO
GRANT CREATE VIEW , CREATE TABLE TO 张力
2. 对象权限
　　对象权限是指数据库对象创建以后，通常只有创建它的拥有者才可以访问该对象。拥有者可以把对象的访问权限授予其他的合法的数据库用户，其他用户才能访问该数据库对象，这种权限称为对象权限。
　　对象权限是指对表、视图、用户定义函数和存储过程等的操作权限。
　　当在表或视图上授予对象权限时，对象权限列表可以包括下列这些权限中的一个或多个操作：SELECT、INSERT、DELETE、 UPDATE 、REFENENCES 或 ALL。
    在存储过程上授予的对象权限只可以包括执行存储过程权限 EXECUTE。
    在标量值函数上授予的对象权限可以包括 EXECUTE 和 REFERENCES。
    授予对象权限的语句格式如下：
　　GRANT { ALL |对象权限1 [ ,...n ] }
　　{
　　[ ( column [ ,...n ] ) ] ON { table | view }
　　| ON { table | view } [ ( column [ ,...n ] ) ]
　　| ON { stored_procedure | extended_procedure }
　　| ON { user_defined_function }
　　 }
　　TO 用户1 [ ,...n ]
　　[ WITH GRANT OPTION ]
例1，把对表student的所有操作权授予用户'张力'，如下描述：
GRANT ALL ON student TO 张力;
例2，只把对表 couse 的只读访问权授予用户'王红'，同时允许王红把此权限授予其他人：
GRANT SELECT ON couse TO 王红
WITH GRANT OPTION ;
例3，王红可再把此权限授予他人，如李立，李立无权将他得到的权限再授予其他人。
GRANT SELECT ON couse TO 李立
例4，下例显示权限的优先顺序。首先给public 角色授予 SELECT 权限。然后，将特定的权限授予用户 Mary、John 和 Tom。于是这些用户就有了对 authors 表的所有权限，如下描述：
　　USE pubs
　　GO
　　GRANT SELECT ON authors TO public
　　GO
　　GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom
　　GO
　　在数据库内部，SQL Server提供了权限作为访问权设置的最后一道关卡。当数据库对象刚刚创建完成，只有拥有者可以访问该数据库对象。任何其他用户想访问该对象必须首先获得拥有者赋予他们的权限。拥有者可以授权给指定的数据库用户，这种权限被称为对象权限。
例5，将对表dept的INSERT,UPDATE,DELETE权限授予用户'张力'，如下描述：
　　GRANT INSERT, UPDATE, DELETE　ON dept TO 张力
　　权限只能授予本数据库的用户，或者获准访问本数据库的其他数据库的用户。如果将权限授予了public角色，则所有数据库里面的所有用户都将默认为获得了该项权限。如果将权限授予了guest用户，则所有可以连接上服务器的用户都默认获得了该项权限。
4.7.2 撤销权限
　 　　被授予的权限可以由DBA或权限授予者使用REVOKE把授予用户的权限收回。
　　撤销权限的语句格式：
　　　REVOKE [GRANT OPTION FOR ]
　　权限 ON 操作对象
　　　FROM {用户1[，用户2]…|PUBLIC}
　　　[ CASCADE ]
参数说明：
　　GRANT OPTION FOR :撤消用户授予其他用户权限的特权，仍保留用户自己的访问权限。
　　CASCADE :撤消用户的权限及GRANT OPTION特权。
1. 撤消语句权限
　　撤消语句权限如下描述：
　　　REVOKE { ALL | 权限1 [ ,...n ] }
　　　FROM {用户1[，用户2]…..|PUBLIC}
例1，撤消授予用户帐户Mary, John的产生表和产生缺省对象的权限，如下描述：
　　　REVOKE CREATE TABLE, CREATE DEFAULT
　　　FROM Mary, John
　　例2，撤消授予用户Joe 和 Corporate\BobJ 的产生表的权限，如下描述：
　　　REVOKE CREATE TABLE
　　　FROM Joe,[Corporate\BobJ]
例3：撤销用户张力在pubs上创建表、视图的权限，如下描述：
　　USE pubs
　　GO
　　REVOKE CREATE VIEW, CREATE TABLE
　　FROM 张力
2. 撤消对象权限
　　撤消对象权限如下描述：
　　REVOKE [ GRANT OPTION FOR ]
　　{ ALL | 对象权限1[ ,...n ] }
　　{
　　[( column [ ,...n ] )] ON {table |view }
　　|ON{ table|view } [( column [ ,...n ])]
　　|ON{stored_procedure|extended_procedure }
　　|ON{ user_defined_function }
　　}
　　FROM {用户1[，用户2]…..|PUBLIC} [ CASCADE ]
例1，撤消王红对表 couse的访问权时，必须同时撤消他的GRANT OPTION特权，如下描述：
　　REVOKE SELECT
　　ON couse
　　FROM 王红
　　WITH CASCADE ;
例2，只撤消王红的GRANT OPTION特权，同时仍保留王红对表 couse的访问权，如下描述：REVOKE 　　　　GRANT OPTION FOR SELECT
　　ON couse
　　FROM 王红;
　　注意：系统撤消了王红对表 couse的访问权，同时也要撤消李力（王红授予的）对表 couse的访问权。撤消会级连发生。
例3：撤销用户'张力'对表dept的INSERT，UPDATE权限，如下描述：
　　EVOKE INSERT，UPDATE
　　ON dept
　　FROM 张力

　　在前面的例子中，都是直接使用SQL语句，独立完成语句的功能，也就是说，假定有了一个SQL解释程序，它接受和执行SQL的各种命令，但是这种操作方式大多在交互环境下使用，仅限于数据库操作，没有数据处理的能力，应用相对较少。实际上，大部分把SQL语句嵌入到程序设计语言中，既实现数据库操作，又要对数据进行处理。能嵌入SQL语句的程序设计语言称为宿主语言，常用的宿主语言如C程序设计语言。对数据的处理功能由宿主语言完成，对数据库数据操纵由SQL语句完成。这样使用的SQL称为嵌入式SQL(embedded SQL)。
4.8.1嵌入式SQL语言的使用
　 　　使用嵌入式SQL，必须解决如下几个问题：
　　(1) SQL语言和宿主语言的数据类型可能不完全一致，必须解决数据类型的转换问题，这与DBMS和其支持的宿主语言有关。
　　(2) 用SQL语句查询数据库的结果是元组的集合，而宿主语言只支持数字型、字符性、数组和记录等数据类型，不支持集合和关系类型。为此，要通过SQL语句使用的宿主变量逐个地把每个元组传递给宿主语言中的程序变量。宿主语言编译器不能识别和接受在宿主语言中的SQL语句代码，必须把嵌入有SQL 语句的宿主语言程序翻译成标准的宿主语言语句再进行编译，或直接产生可执行的代码，这是首先要解决的问题。为此，通常先对嵌入有SQL语句的宿主语言程序进行预处理，翻译成标准的宿主语言语句，再用宿主语言编译器进行编译。数据库和宿主语言之间如何通过宿主变量传递数据和信息。
　　(3) 在宿主语言程序中如何判断SQL语句执行的正确或错误？　　

下面对这些问题逐一给出解答。

1.嵌入式SQL语言的使用格式
　　为了把SQL语句嵌入主程序设计语言中，并且在主程序设计语言的源代码中能够区分宿主语言的语句和嵌入的SQL语句，便于预处理程序识别它，必须有开始和结束的语句块标识符，把SQL语句括在其中。通常在SQL语句前面加上前缀表示"EXEC SQL"，并以"END_EXEC"作为语句结束的标志，构成EXEC SQL <SQL 语句> END_EXEC的嵌入语句块，如下所示：
开始标识符： 'EXEC SQL'
〈嵌入的SQL语句〉
结束标识符：'END_SQL'
　　说明：SQL语句的结束标志随着宿主语言的不同而有差别。因此，嵌入式SQL的确切语法依赖于宿主语言。当宿主语言为C、PL/1或Pascal时，嵌入语句块的结束符使用分号'；'，不使用'END_SQL'。
　　Oracle数据库系统提供了Pro*C语言，在C语言中可嵌入SQL语句，以分号'；'作为结束标识符。为了方便起见，下面的示例程序中，嵌入语句块以EXEC SQL开始，用分号'；'作为结束标志。

2 .共享主变量的声明
　　凡在SQL语句中使用的、用于与宿主语言交换数据的变量，称为宿主变量，简称主变量。主变量也必须用开始和结束标识符括起来进行声明。只有这样声明的主变量才能用于SQL与宿主语言交换数据，所以主变量是SQL和宿主语言共享的变量。
主变量是主语言的变量，所以主变量的说明必须遵从宿主语言的规定，但主变量类型必须是两种语言都能处理的。
主变量的声明格式： EXEC SQL BEGIN DECLARE SECTION
<SQL 宿主变量说明>
EXEC SQL END DECLARE SECTION
　　这些共享的变量在宿主语言中使用时同其他变量一样，当在SQL语句中使用宿主变量时，宿主变量前必须要冠以某种特殊符号，用以区别宿主变量和SQL语句中的列名。Oracle的宿主变量前使用冒号'：'，SQL Server中使用'@'符号。例如，@xy，@post等，称为SQL Server的局部变量。
　　在SQL中可以使用共享宿主变量实现与宿主语言之间传递具体的值。下面的例子是在C语言中嵌入SQL语句，使用共享宿主变量时在前面加上冒号。
例1，声明三个主变量，如下描述：
EXEC SQL BEGIN DECLARE SECTION
Char sname[20], ssex[1];
Char sclass[10];
EXEC SQL END DECLARE SECTION
　　说明：上面的例子夹在嵌入式SQL标识语句之间的是宿主变量的声明，只有宿主语言和SQL都能处理的变量类型才有意义。中间的语句说明了三个变量 sname,ssex,sclass，都是字符数组，用来保存学生的姓名、性别、班级编号。它们的说明形式必须遵从宿主语言的规定。例如，上面的例子中，就是因为宿主语言是C语言而采用的说明形式。

3 特殊的系统变量
　　每个数据库管理系统都提供一个专用的系统变量SQLSTATE，记录SQL语句执行的状态。每当执行完一个SQL语句之后，一个代码便被放入到该系统变量SQLSTATE中，该代码标识SQL语句执行情况。在程序中，要使用SQLSTATE主变量，也必须要事先说明，并在每一个SQL语句之后，检验SQLSTATE 的状态，判断SQL语句的执行结果。
SQLSTATE 的状态码为：
SQLSTATE='00000' 表示执行正确，
SQLSTATE='02000' 表示没有满足要求的元组。
说明SQLSTATE主变量示例：
EXEC SQL BEGIN DECLARE SECTION ;
char SQLSTATE [6]; // 5个字符和一个空字符
EXEC SQL END DECLARE SECTION;
检查SQLSTATE主变量的返回值示例：
EXEC SQL WHENEVER <条件><动作>
其中：
条件：为'NOT FOUND'(02000) 或'SQLERROR'(其他码)
动作:为CONTINUE（继续执行） 或 GO TO<语句标号>
　　在宿主语言程序中，依据专用的系统变量的值就可以判断SQL语句执行的正确与否。如果SELECT语句没有查到记录，系统就会把一个错误代码写入相应系统变量SQLSTATE中，SQLSTATE='00000'表示执行正确。

4.8.2预编译器
　　嵌入式SQL的处理，有两种方式：一种是扩充宿主语言的编译程序，使之能够处理SQL语句；另一种是采用预处理方式。目前多数系统采用预处理方式实现。预处理方式的处理过程如图4-23所示。
　　把嵌入式SQL语句转换成宿主语言对SQL函数的调用，最后生产为纯宿主语言的语句程序，完成该转换工作的程序称为预编译器。
　　程序员用宿主语言编写程序，但是其中在访问数据库数据的地方，嵌入SQL语句。由于SQL语句不是宿主语言的组成部分，必须首先把整个程序送到预处理程序中进行处理，该预处理程序把嵌入式SQL语句转换成在宿主语言对SQL函数的调用，最后生产为纯宿主语言的语句程序。然后，通过宿主语言编译器编译经由预处理器处理过的纯宿主语言的程序，产生目标代码，连接生成可执行代码。这样，就能实现SQL语句的功能，使整个程序则表现为一个整体。
4.8.3 使用游标（Cursor）的操作
　 　当查询结果为一组记录时，不能把提取的元组集合直接传递到应用程序中，必须先放到某种缓冲存储空间，这称为使用游标 CURSOR。因此，游标是系统为用户的查询结果开辟的数据缓冲区，存放SQL的查询结果。每个游标有一个单独的名字。
　　定义游标，是把该游标与相应的SELECT语句相关联，用于存放该 SELECT 语句的查询结果；打开游标，是执行对应的SELECT 语句，把查询结果放到游标缓冲区中；通过FETCH 函数和主变量从游标缓冲区的查询结果集合中逐一取出每条记录。
　　由于SELECT语句是基于集合操作的。无法将结果关系直接交给过程化的宿主语言程序（如C程序），游标正是在这两者之间架起的一座桥梁。DBMS 提供操作函数用于对一个给定游标中的结果集合的数据进行单行处理，通过逐一取出查询结果中的关系元组，逐一放入宿主变量中，完成SQL语言与宿主语言的通讯，该函数为FETCH()。
SQL Server还提供了系统变量@@FETCH_STATUS，保存FETCH函数的执行状态：
　@@FETCH_STATUS = 0 表示执行正确；
　@@FETCH_STATUS = -1 FETCH语句错；
　@@FETCH_STATUS = -2 取的行丢失。
　从概念上讲，游标由两部分内容组成：
　记录集：游标内SELECT语句的执行结果。
　游标位置：游标指针的当前位置。
　游标指针示意图如下图4-24所示　 　 　
　 　当查询结果为一组记录时，不能把提取的元组集合直接传递到应用程序中，必须先放到某种缓冲存储空间，这称为使用游标 CURSOR。因此，游标是系统为用户的查询结果开辟的数据缓冲区，存放SQL的查询结果。每个游标有一个单独的名字。
　　定义游标，是把该游标与相应的SELECT语句相关联，用于存放该 SELECT 语句的查询结果；打开游标，是执行对应的SELECT 语句，把查询结果放到游标缓冲区中；通过FETCH 函数和主变量从游标缓冲区的查询结果集合中逐一取出每条记录。
　　由于SELECT语句是基于集合操作的。无法将结果关系直接交给过程化的宿主语言程序（如C程序），游标正是在这两者之间架起的一座桥梁。DBMS 提供操作函数用于对一个给定游标中的结果集合的数据进行单行处理，通过逐一取出查询结果中的关系元组，逐一放入宿主变量中，完成SQL语言与宿主语言的通讯，该函数为FETCH()。
SQL Server还提供了系统变量@@FETCH_STATUS，保存FETCH函数的执行状态：
　@@FETCH_STATUS = 0 表示执行正确；
　@@FETCH_STATUS = -1 FETCH语句错；
　@@FETCH_STATUS = -2 取的行丢失。
　从概念上讲，游标由两部分内容组成：
　记录集：游标内SELECT语句的执行结果。
　游标位置：游标指针的当前位置。
　游标指针示意图如下图4-24所示： 　
使用游标要遵循如下的操作过程：
　(1). 用DECLARE语句声明游标,并定义游标类型和属性
　(2). 调用OPEN语句打开和填充游标
　(3). 执行FETCH语句读取游标中的单行数据
　(4). 如果需要,修改游标基表中的当前行数据
　(5). 执行CLOSE语句关闭游标
　(6). 执行DEALLOCATE语句删除游标,并释放它所占用的所有资源.
SQL Server中使用游标的过程如图4-25。： 　
　1. 定义游标定义
　　游标语句的核心是定义了一个游标标识名，并把游标标识名和一个查询语句关联起来。DECLARE语句用于声明游标，它通过SELECT查询定义游标存储的数据集合。语句格式为：
　　DECLARE 游标名称 [INSENSITIVE] [SCROLL]
　　CURSOR FOR select语句
　　[FOR{READ ONLY|UPDATE[OF 列名字表]}]
　参数说明：
　　INSENSITIVE选项：说明所定义的游标使用SELECT语句查询结果的拷贝，对游标的操作都基于该拷贝进行。因此，这期间对游标基本表的数据修改不能反映到游标中。这种游标也不允许通过它修改基本表的数据。
　　SCROLL选项：指定该游标可用所有的游标数据定位方法提取数据，游标定位方法包括PRIOR、FIRST、LAST、ABSOLUTE n 和RELATIVE n 选项。
　　Select语句：为标准的SELECT查询语句，其查询结果为游标的数据集合，构成游标数据集合的一个或多个表称作游标的基表。
　　在游标声明语句中，有下列条件之一时，系统自动把游标定义为INSENSITIVE游标：
　　SELECT语句中使用了DISTINCT、UNION、 GROUP BY或HAVING等关键字；
　　任一个游标基表中不存在唯一索引。
　　其他
　　READ ONLY选项：说明定义只读游标。
　　UPDATE [OF 列名字表]选项：定义游标可修改的列。如果使用OF 列名字表选项，说明只允许修改所指定的列，否则，所有列均可修改。
　　例如，查询教师名字和所教的课程名，定义游标TCURSOR的语句如下 ：
　　DECLARE TCURSOR CURSOR FOR
　　SELECT tname, cname
　　FROM teacher ,couse
　　WHERE teacher.tno = couse.tno
2. 打开游标
　　打开游标语句执行游标定义中的查询语句，查询结果存放在游标缓冲区中。并使游标指针指向游标区中的第一个元组，作为游标的缺省访问位置。查询结果的内容取决与查询语句的设置和查询条件。
　　打开游标的语句格式：
　　EXEC SQL OPEN 〈游标名〉
　　如果打开的游标为INSENSITIVE游标，在打开时将产生一个临时表，将定义的游标数据集合从其基表中拷贝过来。
　　SQL Server中,游标打开后，可以从全局变量@@CURSOR_ROWS中读取游标结果集合中的行数。
　　例1：打开前面所创建的查询教师姓名和所教课名称的游标。
　　OPEN tcursor
　　例2：显示游标结果集合中数据行数
　　SELECT 数据行数 = @@CURSOR_ROWS

3. 读游标区中的当前元组
　　读游标区数据语句是读取游标区中当前元组的值，并将各分量依次赋给指定的共享主变量。FETCH语句用于读取游标中的数据，语句格式为：
　　FETCH [[NEXT|PRIOR|FIRST|LAST| ABSOLUTE n| RELATIVE n]
　　FROM ] 游标名
　　[INTO @变量1, @变量2, ….]
　　其中：
　　NEXT：说明读取游标中的下一行，第一次对游标实行读取操作时，NEXT返回结果集合中的第一行。
　　PRIOR、FIRST、LAST、ABSOLUTE n 和RELATIVE n 选项只适用于SCROLL游标。它们分别说明读取游标中的上一行、第一行、最后一行、第n 行和相对于当前位置的第n 行。n 为负值时，ABSOLUTE n 和RELATIVE n 说明读取从游标结果集合中的最后一行或当前行倒数n行的数据。
　　INTO子句 说明将读取的数据存放到指定的局部变量中，每一个变量的数据类型应与游标所返回的数据类型严格匹配，否则将产生错误。
　　如果游标区的元组已经读完，那么系统状态变量SQLSTATE的值被设为02000，意为"no tuple found"。
　　例如，读取tcursor中当前位置后的第二行数据
　　FETCH RELATIVE 2 FROM tcursor

4. 利用游标修改数据
  SQL Server中的 UPDATE语句 和 DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。
UPDATE语句的格式为：
UPDATE table_name
SET 列名=表达式}[,…n]
WHERE CURRENT OF cursor_name
DELETE语句的格式为：
DELETE FROM table_name
WHERE CURRENT OF cursor_name
说明:
CURRENT OF cursor_name：
表示当前游标指针所指的当前行数据。CURRENT OF 只能在UPDATE和DELETE语句中使用。
注意：
  o使用游标修改基表数据的前提是声明的游标是可更新的。
  o 对相应的数据库对象（游标的基表）有修改和删除权限。
5. 关闭游标
  关闭游标后，游标区的数据不可再读。CLOSE语句关闭已打开的游标，之后不能对游标进行读取等操作，但可以使用OPEN语句再次打开该游标。
CLOSE语句的格式为：
CLOSE 游标名
例如：关闭tcursor游标如下描述：
CLOSE tcursor

6 删除游标语句
DEALLOCATE语句删除定义游标的数据结构，删除后不可再用。语句格式：
DEALLOCATE 游标名
例如，删除tcursor游标
DEALLOCATE tcursor

例1,读JS2001班的学生的学号和姓名：
#define MAX 30
EXEC SQL BEGIN DECLARE SECTION;
char TN[12], FU[20]； //定义主变量//
EXEC SQL END DECLARE SECTION;
char tarn1[30][12],tarn2[30][20]； //定义 C 变量//
. . . . . .
EXEC SQL //执行SQL语句，定义游标//
DCLARE Scursor CURSOR FOR //声明游标Scursor//
SELECT sno,sname //查询sno,sname//
FROM student //对student表chax 查询//
WHERE sclass = 'JS2001'； //sclass＝'JS2001'的班级//
EXEC SQL OPEN Scursor ； //打开游标//
for (i=0; i<MAX; i++)
{ EXEC SQL FETCH FROM Scursor
INTO @TN, @FU； //取到宿主变量//
tarn1= TN; //赋值到C数组变量//
tarn2= FU;
}
....................
EXEC SQL CLOSE Scursor ； //关闭游标//
EXEC SQL DEALLOCATE Scursor ； //删除游标//

例2，定义游标，使结果集包括 pubs 数据库的 authors 表中的所有行和列。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。
DECLARE authors_cursor CURSOR FOR //声明游标authors_cursor //
SELECT * FROM authors
OPEN authors_cursor //打开游标authors_cursor //
FETCH NEXT FROM authors_cursor //读游标authors_cursor中的一行 //
....................

例3, 定义和使用滚动游标
DECLARE tcursor SCROLL CURSOR FOR
SELECT tname, cname FROM teacher ,couse
WHERE teacher.tno = couse.tno;
OPEN tcursor;
取tcursor中当前位置向下的第二行数据
FETCH RELATIVE 2 FROM tcursor
取tcursor中最后一行数据
FETCH LAST FROM tcursor
取tcursor中当前位置向前的第4行数据
FETCH RELATIVE -4 FROM tcursor

例4 利用 @@FETCH_STATUS 控制一个 WHILE 循环中的游标活动。
DECLARE S_Cursor CURSOR FOR
SELECT sname, sno FROM student
OPEN S_Cursor
FETCH NEXT FROM S_Cursor
WHILE @@FETCH_STATUS = '000000'
BEGIN FETCH NEXT FROM S_Cursor END
CLOSE S_Cursor
DEALLOCATE S_Cursor

例5，职工普调工资，从最低工资调起，每人工资长10%，但工资总额不能超过50万元。当调完某个职工工资后，如果工资总额达到或超过50万元，就不再调了，另外，如果职工全部调了一遍，工资总额还没到50万元，也到此为止。现有职工表emp，定义了一个游标cl,游标的查询语句取出职工号和工资值，并按增序排列。下面是程序代码：
void addsalary()
{ EXEC SQL BEGIN DECLARE SECTION //声明SQL变量//
char empno[8], e_sno，SQLSTATE[6]；
float s_sal, e_sal；
EXEC SQL END DECLARE SECTION; //声明SQL变量完//
EXEC SQL DECLARE cl CURSOR FOR //定义游标 cl ,对emp表的eno,sal列可以作任何操作
SELECT eno, sal
FROM emp
ORDER BY sal ASC;
EXEC SQL OPEN cl; //打开游标/
EXEC SQL SELECT SUM(sal) INTO @s_sal FROM emp; //取工资总和到s_sal
while(s_sal < 500000.00)
{
EXEC SQL FETCH FROM cl
INTO @e_sno , @e_sal; //从游标中读出的数据放入e_sno,e_sal变量
If(SQLSTATE='02000') BREAK; //读完记录，退出//
EXEC SQL UPDATE emp
SET sal=sal*1.1 //游标内容更新，将empno=e_sno相应的sal*1.1
WHERE empno=@e_eno;
s_sal = s_sal+ e_sal *0.1; //计算工资总和到s_sal
};
EXEC SQL CLOSE cl; /关闭游标/}

  　SQL Server提供两种游标应用接口方法：第一种是符合ANSI标准的SQL游标语句，它们可以实现声明、打开、读取或关闭游标操作，这些语句可用在Transact_SQL语句或存储过程内；第二种是库函数形式，客户端的DB_Library 或ODBC应用程序可以调用这些函数。
  　游标语句增强了Transact_SQL对集合数据的处理能力，在SQL Server中，通过游标还可以修改或删除基表中的数据。

7，使用游标时应注意的问题：
　(1) 尽管使用游标比较灵活，可以实现对数据集中单行数据的直接操作，但游标会在下面几个方面影响系统的性能：
　　-使用游标会导致页锁与表锁的增加
　　-导致网络通信量的增加
　　-增加了服务器处理相应指令的额外开销
　(2) 使用游标时的优化问题：
　　-明确指出游标的用途：for read only或for update
　　-在for update后指定被修改的列

[ 本帖最后由 cayean 于 2007-1-26 12:46 编辑 ]

　　Transact-SQL 的缩写为T-SQL，T-SQL是SQL Server的数据库语言，它是对标准SQL语言的扩充。T-SQL以函数、数据类型、逻辑运算符及流程控制语句等增加了SQL语言的功能。
4.9.1 T-SQL简介
　1． 全局变量和局部变量
　　SQL Server 中分为全局变量和局部变量:
　● 全局变量前有两个@@符号：由系统定义和维护，存放SQL Server完成工作的状态，SQL Server中提供了30多个全局变量。例如，
　　@@SERVERNAME: 记录服务器的名称；
　　@@VERSION: 存放已安装的SQL Server 的版本号；
　　@@FETCH_STATUS: 存放FETCH命令执行的状态：成功=0；失败=-1；丢失=-2。
　　使用SELECT和系统存储过程sp_monitor可显示全局变量的当前值。
　● 局部变量前只有一个@符号：
　　局部变量用DECLARE语句声明，它只能在声明该变量的批语句和过程体内使用。例如：
　　DECLARE @var1 int,@var2 char(20)
　　局部变量由SELECT语句赋值。例如，
　● SELECT @var1=100,@var2='计算机'
　● SELECT @var1,@var2 显示当前值
　● 由FETCH INTO或SELECT INTO 子句赋值

　2． 流程控制语句
　　T-SQL主要的流程控制语句包括：
　　GO：批处理命令的结束标志；
　　IF ELSE ：条件选择语句；
　　BEGIN ... END ：定义语句块；
　　GOTO ：无条件转移语句；
　　WHILE ：循环语句；
　　BREAK ：循环跳出语句；
　　CONTINUE ：继续执行循环语句
　　WAITFOR : 设置语句执行的延迟时间；
　　RETURN ： 从查询或存储过程无条件跳出；
　　CASE 表达式 ：按表达式返回条件分别处理。
4.9.2 SQL Server实现数据完整性
　　数据完整性是确保数据库中数据一致、正确性以及符合企业规则的一种思想。假如在任何时刻，一个数据库的数据满足显式指定的验证规则集合，就说该数据库是满足完整性约束。
1. 满足数据完整性的数据要具有以下的特点：
　● 数据的值必须正确无误：数据的数据类型必须正确，数据的值必须在正确范围内。必须保证同一表格数据间的不相冲突。在不同表格间的数据也必须相互一致。
　● 从实施来分类，SQL Server支持两类数据完整性。一种是声明引用完整性，它在模式描述时候定义检查条件，并在数据插入、修改和删除的时候自动实施数据完整性。另一种是过程数据完整性，可以通过触发器和其他工具来维护数据完整性。
　● 数据完整性的分类为域完整性、实体完整性、引用完整性和用户自定义完整性。
例如：在一个数据库中有两个表，人事表和财务表。人事表记录了本单位的员工信息，财务表记录了本单位员工的借款情况。如果某个员工有借款，那么他不能从人事表中删除，这就是一种数据完整性约束。

　2. 约束的表示：
　　约束是强制数据完整性的首选方法。
　　SQL Server提供的实施数据完整性的途径主要是约束、规则、触发器、索引、数据类型和存储过程。其中约束总是在对象级上实现数据完整性，即数据完整性约束总是和表一起定义的。约束的用途是限制输入到表中的值的范围。SQL Server根据不同的用途提供了多种约束，分别是：
　　(1).DEFAULT（默认值）约束
　　(2).CHECK(核查)约束
　　(3).PRIMARY KEY(主键)约束
　　(4).UNIQUE(唯一性)约束
　　(5).FOREIGN KEY(外键)约束
　　　　SQL Server产生的各种约束对象包括：缺省对象约束，规则对象约束

　3. 定义约束的语句格式CREATE TABLE table_name
　　( column_name data_type
　　[ [CONSTRAINT constraint_name]
　　{ PRIMARY KEY [CLUSTERED | NON CLUSTERED]
　　|UNIQUE [CLUSTERED | NON CLUSTERED]
　　|[FOREIGN KEY]REFERENCESref_table[(ref_column)]
　　|DEFAULT constant_expression
　　|CHECK (logical_ expression)
　　}
　　] [,…,n]
　　<table_ constraint>::=
　　[CONSTRAINT constraint_name]
　　{PRIMARY KEY|UNIQUE [CLUSTERED |NON CLUSTERED] [(column[,…,n])]
　　|[FOREIGN KEY] [(column[,…,n])]
　　REFERENCES ref_table[(ref_column[,…,n])]
　　|CHECK (search_ condition)
　　} [,…,n]
　　　)
　　参数说明：
　　CONSTRAINT：是可选关键字，表示 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY 或 CHECK 约束定义的开始。
　　Constraint_name：约束的名字，约束名在数据库内必须是唯一的。
　　PRIMARY KEY：是通过唯一索引对给定的一列或多列强制实体完整性的约束。对于每个表只能创建一个 PRIMARY KEY 约束。
　　UNIQUE：是通过唯一索引为给定的一列或多列提供实体完整性的约束。一个表可以有多个UNIQUE 约束
　　CLUSTERED | NONCLUSTERED：是表示为 PRIMARY KEY 或 UNIQUE 约束创建聚集或非聚集索引的关键字。PRIMARY KEY 约束默认为 CLUSTERED，UNIQUE 约束默认为 NONCLUSTERED。在 CREATE TABLE 语句中只能为一个约束指定 CLUSTERED。如果在为 UNIQUE 约束指定 CLUSTERED 的同时又指定了 PRIMARY KEY 约束，则 PRIMARY KEY 将默认为 NONCLUSTERED。
　　FOREIGN KEY...REFERENCES：是为列中的数据提供引用完整性的约束。FOREIGN KEY 约束要求列中的每个值在被引用表中对应的被引用列中都存在。FOREIGN KEY 约束只能引用被引用表中为 PRIMARY KEY 或 UNIQUE 约束的列或被引用表中在 UNIQUE INDEX 内引用的列。
　　ref_table：是 FOREIGN KEY 约束所引用的表名。
　　(ref_column[,...n])：是 FOREIGN KEY 约束所引用的表中的一列或多列。


　例1，为student表中的国家列设置缺省值'中国':
　　CREATE TABLE student
　　( name char(20) NOT NULL,
　　country char(30) DEFAULT('中国')
　　)

　例2，为student表中的name列增加缺省值约束:
　　ALTER TABLE student
　　ADD
　　DEFAULT 'unknown' FOR name
4.9.3 触发器
　　触发器采用事件驱动机制,当某个触发事件发生时，触发器触发一系列操作。关系数据库中表示复杂约束的方法主要使用触发器。
　　触发器是一种特殊类型的存储过程，当一个触发器建立后，它作为一个数据库对象被存储。当事件出现时，触发器被触发，定义在触发器中的功能将被DBMS执行。
　　触发器建立在表一级，它与指定的数据修改操作相对应。SQL Server中的触发器可分为INSERT触发器、UPDATE触发器和DELETE触发器三种。
1. 触发器的主要优点
　(1) 触发器能够实施比外键约束，检查约束和规则对象等更为复杂的数据完整性检查。
　(2) 和约束相比，触发器提供了更多的灵活性。约束将系统错误信息返回给用户，但这些错误并不是总能有帮助，而触发器则可以打印错误信息，调用其他存储过程，或根据需要纠正错误。
　(3) 无论对表中的数据进行何种修改，录入或更新，触发器都能被激活，对数据实施完整性检查。
　(4) 触发器能够级联修改数据库中的表内容。

　2. 触发器支持的功能
　(1) 触发器可以在事件之前、之后执行，还可以替代事件本身。例如可以定义触发器在对某关系执行insert、 update和delete操作之前（或之后）触发。
　(2) 触发器代码可以引用事件中对于元组修改前后的值（OLD值和NEW值）。对于update语句，OLD值和NEW值意义很明确。
　(3) 对于update事件可以定义对哪个关系、或关系中的哪一列修改时，触发器触发。
　(4) 可以用WHEN子句来指定执行条件，当触发器被触发后，触发器功能代码只有在条件成立时才执行。
　(5) 触发器有语句级触发器和行级触发器之 分。所谓语句级触发器是指当update语句执行完触发一次，而行触发器是指当update语句每修改完一个元组就触发一次。
　(6) 触发器可以完成一些复杂的数据检查，可以实现某些操作的前后处理等。
　(7) 触发器定义的约束可以在任何颗粒级别上实现、表示动态的或静态的约束、延迟或不延迟进行触发检查、可以用SQL语句定义约束触发器的功能。

　3. 建立触发器的语句格式
　　CREATE TRIGGER 触发器名
　　ON [表名、视图名] [WITH ENCRYPTION]
　　FOR {INSERT,UPDATA,DELETE }
　　AS SQL操作语句
　　参数说明：
　　表名,视图名：为触发该触发器的表名或视图名。SQL Server中，只有表的所有者才有权建立触发器。
　　INSERT,UPDATA,DELETE ：说明触发触发器的事件。一个定义语句允许定义多个触发事件，用逗号分开，第2个只能是插入和更新语句。
　　WITH ENCRYPTION：该选项对触发器的定义文本加密。
　　SQL操作语句：指定触发器动作。该语句中可以指定多个触发器操作，这时要用BEGIN…END将它们组成语句块。
　 4.9.4 T-SQL存储过程
　 　1 存储过程的概念
　● 存储过程是存放在服务器上的预先定义与编译好的SQL语句的命名集合，是一个独立的数据库对象。
　● 存储过程在第一次执行时进行语法检查和编译。编译好的版本存储在过程高速缓存中用于后续调用，执行速度快。
　● 存储过程由应用程序激活，而不是由系统自动执行。
　● 存储过程可以由应用程序多次激活，提高重复任务的执行性能。
　● 存储过程可以接受输入参数和返回值。
　　存储过程的创建和处理过程如图4-27。
　　创建后先进行语法分析，语法正确的存储过程存入到系统表sysobjects和syscomments中。第一次执行时先进行优化和编译，编译后的执行计划放在过程高速缓存中，以备后续过程调用。所以，存储过程在第一次执行以后，通常不再需要进行语法分析、优化和编译，以后的过程调用只须把查询计划从高速缓存中调出执行即可。  

　　综合存储过程特点如下：(1) 存储过程作为一个程序逻辑处理单元，使多个应用程序可以共享应用程序的处理逻辑单元，所有的客户机程序可以使用同一个存储过程进行各种操作，从而确保数据访问和操作的一致性，也提高了应用程序的可维护性。
　(2) 由于存储过程在第一次执行之后，就驻存在高速缓存存储器中，因此可以提高系统的执行效率。
　(3) 存储过程提供了一种安全机制。如果用户被授予执行存储过程的权限，那么既使该用户没有执行访问在该存储过程中所参考的表或者视图的权限，该用户也可以完全执行该存储过程。
　(4) 减少了网络的流量负载。由于存储过程是存放在服务器端的，因此客户端要执行存储过程时，只需要传送一条命令即可，如果不使用存储过程，则需要传送许多条SQL语句。
　(5) 因为存储过程提供该前端应用程序共享的处理逻辑，若要改变业务规则或策略，只需改变存储过程和参数，不用修改应用程序。
　　　2 存储过程的创建
　　创建存储过程的语句如下：
　　　CREATE PROCEDURE〈过程名〉（参数表）
　　　AS
　　　SQL语句
　　　SQL Server创建存储过程的语句格式:CREATE PRO[CEDURE] [owner.]procedure_name[；number]
　　[(parameter1),…, [parameter255 ] ] ]
　　[{FOR REPLICATION} | {WITH RECOMPILE}
　　[{[WITH] | [,] } ENCRYPTION ] ]
　　　AS
　　　[FOR REPLICATION]
　　　sql_statements
　　参数说明：oprocedure_name:新建存储过程名；
　　onumber：区分同名的存储过程，如proc;1,proc;2。
　● 参数格式：@参数名 数据类型[=缺省值] [output]。
　● output：该参数为返回参数。oFOR REPLICATION ：说明所建立的存储过程用于SQL Server的数据复制。
　● WITH RECOMPILE：说明所建立的存储过程不在高速缓存中保存，每次执行重新编译。
　● ENCRYPTION：对存储在syscomments系统表中的存储过程定义文本进行加密，避免他人查看或修改。
　● sql_statements：定义存储过程的具体作用的SQL语句，可以包含任意多的SQL语句。sql语句中不能使用CREATE（VIEW、TRIGER、DEFAULT、RULE、PROCEDURE等）语句，同时要慎重使用其他的CREATE、DROP等语句。


　例1,创建一个不带参数的存储过程，列出图书借阅表中当前逾期的所有图书，定义存储过程：CREATE PROCEDURE overdate_books
　　AS
　　SELECT * FROM loan_books
    WHERE due_date < GETDATE()

　例如2，显示指定出版社的指定类型的图书 ,从用户读取需要的参数放入局部变量。
　　CREATE PROCEDURE publis_proc1
　　(@pub_name varchar(40),@type char(20))
　　AS SELECT pub_name,type,title
　　FROM titles, publishers
　　WHERE titles.pub_id=publishers.pub_id
　　AND pub_name = @pub_name
　　AND type = @type  

　例如3，为参数设置缺省值，返回指定类型的图书的数量和平均价格。缺省值放入局部变量。
　　CREATE PROCEDURE publis_proc2
　　(@count int OUTPUT,@avg_price money OUTPUT @type char(20)='business' )AS
　　SELECT @count=COUNT(*),@avg_price=AVG(price)
　　FROM titles
　　WHERE type = @type

[ 本帖最后由 cayean 于 2007-1-26 12:54 编辑 ]

　 1 SQL Server的系统变量@@ERROR

  　在不同的DBMS系统中，提供不同的系统变量名称检查错误信息。SQL Server专用的系统变量为@@ERROR
　　@@ERROR = 0 表示执行正确。
　　@@ERROR 0 表示执行错误。
　　每当完成 一条SQL 语句的执行时，如果语句执行成功，则 @@ERROR 设置为 0。若出现一个错误，则返回一条错误信息。@@ERROR 返回此错误信息代码，直到另一条 T-SQL 语句被执行。您可以在 sysmessages 系统表中查看与 @@ERROR 错误代码相关的文本信息。
　　由于 @@ERROR 在每一条语句执行后被清除并且重置，应在语句验证后立即检查它，或将其保存到一个局部变量中以备事后查看。
　　例如，在一个 UPDATE 语句中检测查冲突（错误 #547）。
　　USE pubs
　　GO
　　UPDATE authors
　　SET au_id = '172 32 1176'
　　WHERE au_id = "172-32-1176"
　　IF @@ERROR = 547
　　print "A check constraint violation occurred"

2. SQL Server错误消息

　　＊当SQL Server运行或T-SQL语句编译、执行过程中遇到错误时，它将显示一条错误消息，向用户报告发生的错误号、错误级别及错误原因等内容。
　　＊错误消息存储在sysmessages系统表中，每条消息由错误消息号、错误严重级别、状态和错误描述信息四部分组成。
　　＊错误描述信息有两类：一类是静态文本，另一类包含了类似C语言printf函数中的格式控制变量，如%d， %c ，%s等。这类错误消息在报告给用户之前，需要用SQL Server所接收到参数值替换格式控制变量，动态生成完整的错误描述信息。
例1：
　　SELECT * FROM abc
　　将产生208号错误，208号错误的描述信息是：
　　Invalid object name '%.*ls'.
　　在显示错误信息前，SQL Server将自动用所操作的表名abc替换%.*ls变量，最终向用户发出的错误消息为：Server: Msg 208, Level 16, State 1, Line 1
　　Invalid object name 'abc'.

3. 建立用户自定义错误消息

　　＊SQL Server提供了一种添加用户自定义错误消息的机制，使用系统存储过程sp_addmessage将用户自定义的错误消息存放到sysmessage表中。
　　＊用户定义的错误消息号应大于50000。
　　＊可以用RAISERROR语句从sysmessages表中检索信息，并把它发送给相应的用户。
　　系统存储过程sp_addmessage的语法格式：
　　sp_addmessage { number, severity, 'msg' }
　　[, 'language' ]
　　[, 'with_log' ]
　　[, 'replace' ]
例2：添加一条错误号为50002的错误消息。
　　sp_addmessage 50002, 16, 'This is a test%s'
　　＊RAISERROR语句返回用户所定义的错误消息，并且设置系统标识来记录所发生的错误：其语法格式如下：
　　RAISERROR ( { msg_id | msg_str }
　　{ ,severity ,state }
　　[, argument[,...n]] )
　　[WITH option]
例3：下面语句将产生50002号错误消息，并用字符串"aaa"替换格式控制变量%s：
RAISERROR (50002, 16, -1, "aaa")

4 删除用户定义的错误消息

　　当用户不再使用所定义的错误消息时，可使用系统存储过程sp_dropmessage删除。sp_dropmessage的格式为：
　　sp_dropmessage [@msgnum =] message_number
　　[ , [@lang = ] 'language']
例4：下面语句删除前面所定义的50002号错误消息：
sp_dropmessage 50002
5.使用SQL的一些常见问题
　(1).在SQL Server 7.0以后，一个汉字的长度为一个字符，而不是以往的两个字符。
　(2).创建表时，表名，字段名可以是中文名，但标点符号，必须是英文标点，如逗号","，园括号"("等。
　(3).测试一个字段的值是否为NULL使用
IS NULL而不是 = NULL
例1：在范例数据库pubs中检索表titles 中 price字段为NULL的行。
USE pubs
GO
SELECT * FROM titles WHERE price IS NULL

　(4).SQL Server中转义字符的使用
例2：查找所有以"%"开头的字符串
SELECT col1
FROM example
WHERE col1 LIKE '/%%' ESCAPE '/'
例3：查找"[X"开头，以"Z]"结尾，第三个字符为X，Y，Z中的任意一个的字符串。
SELECT col1
FROM example
WHERE col1 LIKE "t[X[XYZ]%Z]" ESCAPE "t"

　(5).SQL Server中SET命令的使用
＊T-SQL提供了一组SET命令，使用这些命令能设置某些选项，帮助分析T-SQL语句。如：
＊SET STATICTICS io ON：显示扫描数量，逻辑读，物理读和提前读数量。
例5：
SET STATICTICS io ON
USE pubs
SELECT * FROM authors
＊SET STATISTICS time ON：显示查询执行所需的CPU执行时间和总的耗费时间。
例6：
SET STATISTICS time ON
USE pubs
SELECT * FROM authors
＊SET ROWCOUNT n：使服务器仅显示查询结果的前 n 行。
例7：
SET rowcount 5
USE pubs
SELECT * FROM authors
例8：
SET rowcount 0
返回查询结果的所有行

　(6).SQL语句中注释的使用
<1> 单行语句
-- 注释说明
<2> 多行语句
/*****
first line
second line
*****/

　(7).IDENTITY列的使用
＊如果表中的一个列为IDENTITY列，则当用户向表中插入新的数据时，系统自动为该行的IDENTITY列赋值，并保证其值在表中的唯一性。
＊每个表中只有一个IDENTITY列。
＊IDENTITY列的数据类型只能为int, smallint, tinyint, numeric, decimal等。
＊可以对IDENTITY列设置初始值和增量值。

　(8).数据库中表的记录没有顺序
从SQL的角度来说，表中的记录没有顺序。在一个表中不存在类似"前十个记录"这种概念。
说明：
在SQL Server中，打开一个表时可以只返回前n行（Open Table－>Return Top）菜单。但这是指查询结果集中的前n行，而不是指表中记录的前n行。有可能后插入的数据在表的前面，只要在表的前面有空的存储空间（比如刚删除了一个表中的记录）

　(9).用INSERT 语句一次添加多个记录
INSERT 语句与DELETE语句和UPDATE语句有一点不同是，它一次只操作一个记录。然而，利用SELECT语句，可以使INSERT语句一次添加多个记录。
例9：从table2拷贝记录到table1。只有表table2中字段c3的值为'aaa'的记录才被拷贝。
INSERT table1 (c1, c2)
SELECT c3, c4
FROM table2
WHERE c3='aaa'
在对表中的数据进行处理前，可以用这种形式的INSERT语句为表中的数据建立备份或对数据进行预处理。
例如，设表table1, 表中字段为c1, c2；表table2, 表中字段为c1, c2, c3, 现要把table1中的记录拷贝到表table2中，同时table2的c3列的值设为常量ddd。
insert table2 select c1,c2, 'ddd' from table1
用SELECT...INTO语句复制一个表的结构及其中的数据
例10：如果要复制一个表的结构及其中的数据：
SELECT *
INTO newtable
FROM oldtable
即创建一个newtable表，且newtable表中的数据和oldtable中的数据一样。
说明：
用SELECT INTO语句可以用来建立临时表。
也可以指定只有特定的字段被用来创建这个新表。要做到这一点，只需在字段列表中指定想要拷贝的字段。另外，可以使用WHERE 子句来限制拷贝到新表中的记录。
例如：只拷贝second_column列的值等于'aaa'的记录的first_column列。
SELECT first_column
INTO newtable
FROM oldtable
WHERE second_column='aaa'

第1题
　简述关系数据库语言SQL的特点；
　第2题
　练习各种SQL语句。包括创建和修改数据库，对数据进行增、删、改操作，增加、修改索引操作，各种查询操作等（可以使用示例数据库Pubs或NorthWind ）。
　第3题
　什么是视图？视图的优点和定义格式？
　第4题
　什么是游标 ？嵌入式SQL中使用游标的操作过程?
　第5题
　什么是触发器？触发器实现的功能?
　第6题
　什么是存储过程？存储过程的优点是什么？
　第7题
　使用聚集函数、GROUP BY等在查询结果中产生汇总和统计数据：
　（1） 写一个SQL语句，统计各门课程的平均成绩超过85分的学生的名字、学号和成绩。
　（2） 写一个SQL语句，查询各门课程的平均成绩超过85分的学生的人数大于10人的所在班级的名字和平均成绩超过85分的人数。
　第8题
　在查询分析器中，使用示例数据库Pubs(或NorthWind)练习各种SQL语句。把操作命令保存到文件中。以批处理文件的形式发送到服务器ServerX上的子目录"数据库作业\SQL练习"中，文件名为：学号姓名.sql。 操作命令包括：
　(1) 对数据库数据进行增、删、改操作。
　(2) 对数据库表的增加、修改索引。
　(3) 对数据库数据进行各种查询操作。
　(4) 对查询结果进行分组、排序等操作。
　(5) 对视图创建、查询操作
　(6) 数据完整性的定义和修改操作
　(7) 游标的定义和使用
　(8) 触发器和存储过程的定义及使用。

答案：
第1题
　· 语言功能的一体化: 集数据定义DDL、数据操纵DML、数据控制DCL为一体。且它不严格区分数据定义和数据操纵，在一次操作中可以使用任何语句。结构的一体化：关系模型中唯一的结构类型就是关系表，这种数据结构的单一性,使得增、删、改、查询等操作都只须使用一种操作符。
　· 高度非过程化：只须提出"做什么"无须指 明"怎样做"。用户不必了解存取路径。面向集合的操作方式：在元组集合上操作,操作结果仍是元组集合。查找、插入、删除和更新都可以是元组集合。
　· 两种操作方式、统一的语法结构：既是自含式语言，又是嵌入式语言。作为自含式语言，可作为连机交互式使用；作为嵌入式语言，SQL语句可嵌入高级程序设计语言中使用。
　· 语言简洁、易学易用：数据定义、操纵和控制只用了9个动词。

　第2题　答案略。

　第3题
　· 什么是视图：
　　视图是查询结果的关系，是一个基于查询的逻辑表，视图本身并不存储数据，但它可以表示来自不同来源的数据，对应用程序来说，视图就相当一个表，数据可以从视图中查得，而且在权限许可的情况下，还可以通过视图来插入、更改和删除数据。
　· 视图的优点如下：
　　(1)提供了逻辑上的数据独立性：修改了基本表，通过建立视图，可以不改变应用程序。
　　(2)简化了用户查询；
　　(3)不同的用户通过视图可以从不同的观点观察数据；
　　(4)视图作为授权的单位提高了系统的安全性，通过视图可以对用户隐藏某些数据。

　第4题
　· 什么是游标：
　　通过嵌入式SQL语句进行查询时，当查询结果为一组记录时，不能把提取的元组集合直接传递到应用程序中，必须先放到某种缓冲存储空间，这称为使用游标 CURSOR。因此，游标是系统为用户的查询结果开辟的数据缓冲区，存放SQL的查询结果。
　· 嵌入式SQL中使用游标的操作过程：
　　(1). 定义游标(2). 打开游标(3). 逐一读游标区中的元组值；(4). 关闭游标(5) 删除游标

　第5题
　触发器是一种特殊类型的存储过程，当表中数据被修改时，系统自动执行触发器。
　当一个触发器建立后，它作为一个数据库对象被存储。当事件出现时，触发器被触发，定义在触发器中的功能将被DBMS执行。触发器建立在表一级，它与指定的数据修改操作相对应。SQL Server 中的触发器可分为INSERT触发器、UPDATE触发器和DELETE触发器三种。

　第6题
　(1) 存储过程是存放在服务器上的预先定义与编译好的SQL语句。存储过程在第一次执行时进行语法检查和编译。编译好的版本存储在过程高速缓存中用于后续调用，执行速度快。存储过程由应用程序激活，而不是由系统自动执行。
　(2) 存储过程特点：
　　·使多个应用程序可以共享应用程序的处理逻辑，所有的客户机程序使用同一个存储过程进行各种操作，从而确保数据访问和操作的一致性，也提高了应用程序的可维护性。
　　·由于存储过程在第一次执行之后，就驻存在高速缓存存储器中，因此可以提高系统的执行效率。
　　· 提供一种安全机制。如果用户被授予执行存储过程的权限，那么既使该用户没有执行访问在该存储过程中所参考的表或者视图的权限，该用户也可以完全执行该存储过程。
　　· 减少了网络的流量负载。由于存储过程是存在服务器端的，因此客户端要执行存储过程时，只需要传送一条命令即可，如果不使用存储过程，则需要传送许多条SQL语句。
　　· 因为存储过程提供该前端应用程序共享的处理逻辑，若要改变业务规则或策略，只需改变存储过程和参数。

　第7题
　(1) 统计每个学生的平均成绩和姓名
　　　select s.sname, AVG(grade)
　　　from student s ,sc
　　　where s.sno = sc.sno
　　　group by s.sname
　(2) 统计平均成绩超过85分的学生的名字、学号和成绩。
　　　Select s.sname,s.sno,avg(sc.grade)
　　　from student s, sc where s.sno = sc.sno
　　　　　group by s.sname,s.sno
　　　　　having avg(grade)>=85
　(3)查询平均成绩超过85分的学生的人数大于20人的班级的名字和平均成绩超过85分的人数。
　　　Select classname,count(*)
　　　from student s, sc ,class
　　　where s.sclass = class.classname
　　　　　AND s.sno IN
　　　　　( select sno
　　　　　　from sc
　　　　　　group by sno
　　　　　　having avg(grade)>=85)
　　　group by classname
　　　having count(*) >=20

　第8题
　/* 下面是SQL语句的练习部分示例 */
　/*************创建数据库****************/
　create database Business1
　on
　(name=Business1_dat,
　filename='f:\mssql7\Data1\businessdat.mdf',
　size=10,
　maxsize=50,
　filegrowth=5)
　log on
　(name=Business1_log,
　filename='f:\mssql7\Data1\businesslog.ldf',
　size=5MB,
　maxsize=25MB,
　filegrowth=5MB)
　go

　/*创建数据库一个主文件、2个次文件、2个日志文件*/
　create database archive1
　on
　primary(name=arch1,
　filename='f:\Program Files\mssql7\Data1\archdat1.mdf',
　size=10MB,
　maxsize=20,
　filegrowth=2),
　(name=arch2,
　filename='f:\Program Files\mssql7\Data1\archdat2.ndf',
　size=10MB,
　maxsize=20,
　filegrowth=2),
　(name=arch3,
　filename='f:\Program Files\mssql7\Data1\archdat3.ndf',
　size=10MB,
　maxsize=20,
　filegrowth=2)
　log on
　(name=archlog1,
　filename='f:\Program Files\mssql7\Data1\archlog1.ldf',
　size=10MB,
　maxsize=20,
　filegrowth=2),

　(name=archlog2,
　filename='f:\Program Files\mssql7\Data1\archlog2.ldf',
　size=10MB,
　maxsize=20,
　filegrowth=2)
　go

　/*利用文件组创建数据库*/
　create database company2
　on
　primary (name=pril_dat,
　filename='f:\Program Files\mssql7\Data1\pril1dat.mdf',
　size=10,
　maxsize=50,
　filegrowth=15%),
　(name=pri2_dat,
　filename='f:\Program Files\mssql7\Data1\pri21dat.mdf',
　size=10,
　maxsize=50,
　filegrowth=15%),
　filegroup
　comgroup1
　(name=grp1fi11_dat,
　filename='f:\Program Files\mssql7\Data1\g1fi11dt.ndf',
　size=10,
　maxsize=50,
　filegrowth=5),
　(name=grp2fi22_dat,
　filename='f:\Program Files\mssql7\Data1\g2fi22dt.ndf',
　size=10,
　maxsize=50,
　filegrowth=5),
　filegroup
　comgroup2
　(name=grp2fi11_dat,
　filename='f:\Program Files\mssql7\Data1\g2fi11dt.ndf',
　size=10,
　maxsize=50,
　filegrowth=5),
　(name=grp2fi21_dat,
　filename='f:\Program Files\mssql7\Data1\g2fi21dt.ndf',
　size=10,
　maxsize=50,
　filegrowth=5)

　log on
　(name=company1_log,
　filename='f:\Program Files\mssql7\Data1\comlog.ldf',
　size=5,
　maxsize=25,
　filegrowth=5)
　go

　/*修改大小*/
　use my_library
　go

　alter database my_library
　add file
　(name=testdat2,
　filename='f:\Program Files\mssql7\Data1\t1dat2.ndf',
　size=5MB,
　maxsize=100,
　filegrowth=5)
　go

　use company2
　go

　alter database company2
　add file
　(name=pri3_dat,
　filename='f:\Program Files\mssql7\Data1\pri31dat.mdf',
　size=5mb,
　maxsize=100mb,
　filegrowth=5mb),
　(name=pri4_dat,
　filename='f:\Program Files\mssql7\Data1\pri41dat.mdf',
　size=5mb,
　maxsize=100mb,
　filegrowth=5mb) to filegroup comgroup1

　alter database company2
　modify file
　(name=pri3_dat,
　size=20mb)
　go
　dbcc shrinkfile(pri3_dat,15)
　go

　/*删除数据库*/
　drop database archive1

　/*以下都是在pubs库中的操作*/
　/*产生表*/
　create table student
　(sno char(8) not null unique ,
　sname char(8) not null unique,
　ssex char(20) not null,
　sage char (12) not null,
　sadd char (40),
　primary key (sno)
　)

　/*修改表*/
　alter table student
　add sdept char(20)

　/*删除表*/
　drop table student

　/*查询表*/
　select sum(qty) '销售总量'
　from sales

　select avg(qty) '平均销量'
　from sales

　select stor_id '销量最小书',qty '最小量'
　from sales
　where qty=(select min(qty)
　　　　　　　　from sales)
　select *
　from sales
　order by qty desc

　select stor_id,sum(qty)'总量',avg(qty)'平均',max(qty)'最大量',min(qty)'最小量'
　from sales
　group by stor_id
　having count(*)>2

　select jobs.job_id,job_desc,lname+','+fname,hire_date
　from jobs
　join employee
　on jobs.job_id=employee.job_id

　/*插入表*/
　insert into dtproperties
　values('1','5','beijing','BJ','de','23')

　/*增加约束*/
　alter table discounts
　add constraint pk_id
　primary key(discounttype)

　/*产生缺省对象*/
　create default zero
　as 0
　sp_bindefault "zero","discounts.lowqty"

　/*增加约束*/
　alter table authors
　add constraint ck_id
　check(contract>=0)

　/*增加约束*/
　alter table authors
　add constraint uk_id
　unique(au_id)

　/*增加约束*/
　alter table titles
　add constraint foreignkey_pub_id
　foreign key(pub_id)
　references publishers(pub_id)

　/*创建视图*/
　create view author_view
　as
　select au_id,au_fname,au_lname,contract
　from authors
　where state='CA'

　/*通过视图插入数据*/
　insert into author_view
　(au_id,au_fname,au_lname,contract)
　values('123-23-3456','zhaoxia','yang',1)

　/*通过视图更新数据*/
　update author_view
　set contract=2
　where au_fname='zhaoxia'

　/*产生索引*/
　create index titleid_idx
　on titles(title_id)

　/*授权和撤消授权*/
　grant select on authors
　to cnk6
　revoke select on authors
　from cnk6

　/*创建存储过程*/
　CREATE PROCEDURE nocontract_au
　as
　select au_id ,contract
　from authors
　where contract=0

　/*创建触发器*/
　create trigger del
　on jobs
　for delete
　as delete from employee
　where jobs.job_id=employee.job_id

　/*创建、打开和使用游标*/
　declare a_ta_t cursor scroll for
　select au_lname,title,price
　from authors a,titleauthor ta,titles t
　where a.au_id=ta.au_id
　and ta.title_id=t.title_id
　and a.state="CA"
　for update of price

　declare @fetch_status int

　open a_ta_t
　fetch a_ta_t
　select @fetch_status=@@FETCH_STATUS
　while @fetch_status=0
　begin
　　update titles
　　set price=price/1.5
　　where current of a_ta_t
　　fetch a_ta_t
　　select @fetch_status=@@FETCH_STATUS
　end

　close a_ta_t
　deallocate a_ta_t

　declare @author varchar(20)
　set @author='作者'
　select @author,au_lname,au_fname
　from authors
　where state="ca"

　/*创建规则对象*/
　create rule list_rule
　as
　@list in('1389','0736','0877')
　sp_addmessage 50002, 16, 'This is a test%s'
　RAISERROR (50002, 16, -1, "aaa")