学习SQL2005当中的例子

2019-11-03 08:36:31

字体：大中小

来源：转载

供稿：网友

/*例3-1　创建数据库表。*/
/*程序清单如下：*/
/*下面的例子将创建表S。*/
CREATE TABLE S
(
   sno char(10) NOT NULL       /*学号字段*/
         CONSTRAINT PK_sno PRIMARY KEY CLUSTERED/*主键约束*/
         CHECK (sno like '31300501[0-9][0-9]')/*检查约束*/,
   sname      char(8)     NULL, /*姓名字段*/
   sex     char(2)   NULL, /*性别字段*/
   age    int NULL, /*年龄字段*/
   dept   varchar(20)   NULL/*系别字段*/
)

/*例3-2　修改S表，增加一个班号列。*/
/*程序清单如下：*/
ALTER TABLE S
ADD
CLASS_NO CHAR(6)

/*例3-3　删除S表。*/
/*程序清单如下：*/
   DROP table S

/*再执行例3-1*/
/*例3-4　在S表中插入一条学生记录（学号：S7；姓名：郑冬；性别：女；年龄：21；系别：计算机）。
*/
/*程序清单如下：*/
INSERT INTO S VALUES ('3130050107','郑冬','女',21,'计算机')

/*例3-5   创建SC表（学生选课表），并向SC表中插入一条选课记录 ('3130050101', 'c1')。*/
/*程序清单如下：*/
CREATE TABLE SC
(
   sno char(10) NOT NULL,
   cno char(2)    NULL, /*课程编号字段*/
score numeric(4,1) NULL /*成绩字段*/
)
Go
INSERT INTO SC (sno,cno) VALUES ('3130050101', 'c1')
Go

/*例3-6   使用 column_list 及 VALUES 列表显式地指定将被插入每个列的值。*/
/*程序清单如下：*/
CREATE TABLE T1
( column_1 int,
column_2 varchar(30))
Go
INSERT T1 (column_2, column_1) VALUES ('This is a test',1)

/*例3-7   求出各位学生的平均成绩，把结果存放在新表AVGSCORE中。
程序清单如下：*/
/*首先建立新表AVGSCORE，用来存放学号和学生的平均成绩。*/
CREATE TABLE AVGSCORE
(SNO CHAR(10),
AVGSCORE SMALLINT)
Go
/*利用子查询求出SC表中各位学生的平均成绩，把结果存放在新表AVGSCORE中。*/
INSERT INTO AVGSCORE
SELECT SNO,AVG(SCORE)
FROM SC
GROUP BY SNO

/*例3-9   将所有学生年龄增加1岁*/
/*程序清单如下：*/
UPDATE S SET AGE=AGE+1

/*例3-10　下面的示例修改 SalesPerson 表中的 SalesYTD 列，以反映 SalesOrderHeader 表中记录的
最近销售情况。*/
/*程序清单如下：*/
USE AdventureWorks;
GO
UPDATE Sales.SalesPerson SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp JOIN Sales.SalesOrderHeader AS so
    ON sp.SalesPersonID = so.SalesPersonID
    AND so.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader
              WHERE SalesPersonID = sp.SalesPersonID)

/*例3-11　将UPDATE语句与TOP子句一起使用，更新Employee 表中10个随机行的VacationHours 列，使
之变成原来的1.25倍。*/
/*程序清单如下：*/
USE AdventureWorks;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25

/*例3-12   创建T表（教师基本情况表）的结构为T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分别表示教师的编
号，姓名，性别，年龄，职称，工资，系别。TC表（教师授课表）的结构为TC(TNO,CNO)分别表示教师的
编号，课程编号。*/

USE example
GO

CREATE TABLE t
(
       TNO char(10) NOT NULL
              CONSTRAINT PK_TNO PRIMARY KEY,/*教师编号字段*/
       TN char(10) NULL,/*教师姓名字段*/
       SEX char(2) NULL,/*教师性别字段*/
       AGE int NULL,/*教师年龄字段*/
       PROF char(20) NULL,/*教师职称字段*/
       SAL int NULL,/*教师工资字段*/
       DEPT char(10) NULL,/*教师所在系别字段*/
)
GO
INSERT INTO t VALUES('0001','张老师','男',41,'副教授',2200,'电力系')
GO
INSERT INTO t VALUES('0002','张益琳','女',32,'讲师',1500,'动力系')
GO

CREATE TABLE TC
(
       TNO char(10) NOT NULL,/*教师编号字段*/
       CNO char(10) NULL,/*教师执教课程字段*/
)
GO
INSERT INTO tc VALUES('0001','C5')
GO

/*把讲授C5课程的教师的工资增加100元。*/
/*程序清单如下：*/
UPDATE T SET SAL=SAL+100
WHERE TNO IN
(SELECT T.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND TC.CNO='C5')
/*通过连接查询找到讲授C5课程的教师编号。*/

/*例3-13 把所有教师的工资提高到平均工资的1.2倍*/
/*程序清单如下：*/
UPDATE T SET SAL =(SELECT 1.2*AVG(SAL) FROM T)

/*例3-17　给用户 Mary 授予多个语句权限。*/
/*程序清单如下：*/
GRANT CREATE DATABASE, CREATE TABLE
TO Mary

/*例3-18 为用户ZhangYiLin授予CREATE TABLE的语句权限。*/
/*程序清单如下：*/
GRANT CREATE TABLE
TO ZhangYiLin

/*例3-19　在权限层次中授予对象权限。首先，给所有用户授予SELECT权限，然后，将特定的权限授予用
户Mary、John和Tom*/
/*程序清单如下：*/
GRANT SELECT
ON s                          /*需先创建s表*/
TO public
GO
GRANT INSERT, UPDATE, DELETE
ON s
TO Mary, John, Tom          /*需首先创建用户Mary, John, Tom */
GO

/*例3-20 将查询T表和修改教师职称的权限授予USER3，并允许将此权限授予其他用户。*/
/*程序清单如下：*/
GRANT SELECT,UPDATE
ON T TO user3
WITH grant OPTION
/*上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予
USER4：*/
GRANT SELECT,UPDATE(PROF)
ON T
TO USER4

/*例3-20 将查询T表和修改教师职称的权限授予USER3，并允许将此权限授予其他用户。*/
/*程序清单如下：*/
GRANT SELECT,UPDATE
ON T TO user3
WITH grant OPTION
/*上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予
USER4：*/
GRANT SELECT,UPDATE(PROF)
ON T
TO USER4

/*例3-21 收回用户USER1对jobs表的查询权限。*/
/*程序清单如下：*/
REVOKE SELECT
ON jobs
FROM mary

/*例3-22 收回用户mary查询T表和修改教师职称的权限。*/
/*程序清单如下：*/
REVOKE SELECT,UPDATE(PROF)
ON T
FROM mary

/*例3-23　首先从 public 角色中收回 SELECT 权限，然后，收回用户 Mary,Jhon和Tom的特定权限。*/
/*程序清单如下：*/
REVOKE SELECT
ON s FROM public
GO
REVOKE INSERT, UPDATE, DELETE
ON s FROM Mary, John, Tom

/*例3-24　首先给public角色授予SELECT权限，然后拒绝用户Mary、John和Tom的特定权限*/
/*程序清单如下：*/
GRANT SELECT
ON s
TO public
GO
DENY SELECT, INSERT, UPDATE, DELETE
ON s
TO Mary,John,Tom

/*例3-25　使用两种注释类型的例子。*/
/*程序清单如下：*/
USE AdventureWorks
GO
-- First line of a multiple-line comment.
-- Second line of a multiple-line comment.
SELECT * FROM person.address
GO
/* 注释语句的第一行.
注释语句的第二行. */
SELECT * FROM Production.Product
GO
-- 在Transact-SQL语言调试过程中使用注释语句。
SELECT title, /* birthdate, */ hiredate
FROM humanresources.employee
-- 在代码后面加上注释。
USE AdventureWorks
GO
UPDATE sales.salesorderdetail
SET unitprice = unitprice * .9 -- 降低价格以提高市场份额。
GO

/*例3-26　创建一个@myvar 变量，然后将一个字符串值放在变量中，最后输出 @myvar 变量的值。*/
/*程序清单如下：*/
DECLARE @myvar char(20)
select @myvar = 'This is a test'
SELECT @myvar
GO

/*例3-27　通过查询给变量赋值。*/
/*程序清单如下：*/
USE adventureworks
GO
DECLARE @rows int
SET @rows = (SELECT COUNT(*) FROM humanresources.employee)
SELECT @rows

/*例3-28　在SELECT语句中使用由 SET 赋值的局部变量。*/
/*程序清单如下：*/
USE adventureworks
GO
DECLARE @phone char(12)
SET @phone = '398-555-0132'
SELECT RTRIM(firstname) + ' ' + RTRIM(lastname) AS Name, phone
FROM person.contact
WHERE phone = @phone
GO

/*例3-29　显示到当前日期和时间为止试图登录SQL Server的次数。*/
/*程序清单如下：*/
SELECT GETDATE() AS '当前的时期和时间',
   @@CONNECTIONS AS '试图登录的次数'

/*例3-30　在 FirstColumnHeading 列标题中为所有的行都显示字符串 xyz，然后，在
SecondColumnHeading 列标题中列出来自 Products 表的每个产品 ID。*/
/*程序清单如下：*/
SELECT 'xyz',Product.ProductID
FROM Production.Product

/*例3-31 创建一个具有 int 数据类型的表以显示值，并将该表放入一行中。*/
/*程序清单如下：*/
USE example
GO
CREATE TABLE t1
( a int NOT NULL, b int NOT NULL )
INSERT t1 VALUES (170, 75)

/*下面的查询在 a列和 b列上执行按位 AND 运算。*/
SELECT a& b FROM t1

/*例3-32　在SET语句使用的表达式中，括号使其首先执行加法，表达式的结果为18。*/
/*程序清单如下：*/
DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + 5)
SELECT @MyNumber

/*例3-33　通过行集函数OPENQUERY()执行一个分布式查询，以便从服务器local中提取表department中的
记录。*/
/*程序清单如下：*/
select * from openquery( SERVER , 'select * from Northwind.customers')

/*例3-34 计算 Adventure Works Cycles 的副总所用的平均休假小时数以及总的病假小时数。对检索到
的所有行，每个聚合函数都生成一个单独的汇总值。*/
/*程序清单如下：*/
USE AdventureWorks;
GO
SELECT AVG(VacationHours)as 'Average vacation hours',
    SUM (SickLeaveHours) as 'Total sick leave hours'
FROM HumanResources.Employee
WHERE Title LIKE 'Vice President%'

/*例3-35   下列语句返回产品的平均标价。如果不使用DISTINCT，AVG函数将计算出Production.Product
表中所有产品的平均价格*/
/*程序清单如下：*/
USE AdventureWorks;
GO
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product

/*例3-36 下面的示例查找居住在不同城市的人员数量*/
/*程序清单如下：*/
USE adventureworks
GO
SELECT COUNT(DISTINCT city)
FROM person.address
GO

/*例3-37 返回production.product表中成本最高的产品以及价格的标准偏差。*/
/*程序清单如下：*/
USE adventureworks
GO
SELECT MAX(standardcost),STDEV(listprice)
FROM production.product

/*例3-38　使用 LTRIM 函数删除字符变量中的起始空格。*/
/*程序清单如下：*/
DECLARE @string_to_trim varchar(60)
SET @string_to_trim = 'Five spaces are at the beginning of this
   string.'
SELECT 'Here is the string without the leading spaces: ' +
   LTRIM(@string_to_trim)

/*例3-39　使用可选的start_location参数从addressline1列的第2个字符开始查找“shoe”*/
/*程序清单如下：*/
USE adventureworks
SELECT CHARINDEX('shoe', addressline1,2) FROM person.address
WHERE addressid = '5'

/*例3-40   显示如何只返回字符串的一部分。该查询在一列中返回person.contact表中的姓氏，在另一
列中返回person.contact表中的名字首字母。*/
/*程序清单如下：*/
USE adventureworks
SELECT lastname, SUBSTRING(firstname, 1, 1)
FROM person.contact
ORDER BY lastname

/*例3-41　在第一个字符串（abcdef）中删除从第二个位置（字符 b）开始的三个字符，然后在删除的起
始位置插入第二个字符串，创建并返回一个字符串。*/
/*程序清单如下：*/
SELECT STUFF('abcdef', 2, 3, 'ijklmn')

/*例3-42　显示在humanresources.employee 表中雇用日期到当前日期间的天数。*/
/*程序清单如下：*/
USE adventureworks
SELECT DATEDIFF(day, hiredate, getdate()) AS diffdays
FROM humanresources.employee

/*例3-43　从GETDATE函数返回的日期中提取月份名。*/
/*程序清单如下：*/
SELECT DATENAME(month, getdate()) AS 'Month Name'

/*例3-44　此示例输出 AdventureWorks 数据库中订单的时间范围的列表。此时间范围为当前订单日期加
上 21 天。*/
/*程序清单如下：*/
USE AdventureWorks;
GO
SELECT DATEADD(day, 21, OrderDate)AS TimeFrame
FROM Sales.SalesOrderHeader
GO

/*例3-45　在同一表达式中使用CEILING()，FLOOR()，ROUND()函数。*/
/*程序清单如下：*/
select ceiling(13.4), floor(13.4), round(13.4567,2)
14    13    13.4600

/*例3-46　示例检索列表价格的第一位是 3 的产品的名称，并将ListPrice转换为 int。*/
/*程序清单如下：*/
USE AdventureWorks
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%'
GO

/*例3-47　用style 参数将当前日期转换为不同格式的字符串。*/
/*程序清单如下：*/
SELECT '101'=CONVERT(char, GETDATE(), 101),
'1'=CONVERT(char, GETDATE(), 1),
'112'=CONVERT(char, GETDATE(), 112)
08/21/2007                         08/21/07                           20070821

/*例3-48　从adventureworks数据库中返回person.contact表的首列名称*/
/*程序清单如下：*/
USE adventureworks
SELECT COL_NAME(OBJECT_ID('person.contact'), 1)

/*例3-49　检查 sysdatabases 中的每一个数据库，使用数据库标识号来确定数据库名称。*/
/*程序清单如下：*/
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
ORDER BY dbid

/*例3-50　查找所有产品的平均价格，用值$10.00替换production.product表listprice列中所有NULL条
目*/
/*程序清单如下：*/
USE adventureworks
SELECT AVG(ISNULL(listprice, $10.00))
FROM production.product

/*例3-51　显示带有语句块的IF条件。如果产品的平均价格不低于$15，那么就显示文本：Average
price is more than $15*/
/*程序清单如下：*/
USE adventureworks
IF (SELECT AVG(listprice) FROM production.product WHERE color = 'blue') < $15
BEGIN
   PRINT 'The following product are Excellent:'
   PRINT ' '
   SELECT SUBSTRING(name, 1, 15) AS name
   FROM production.product
   WHERE color = 'blue'
END
ELSE
   PRINT 'Average price is more than $15.'

/*例3-52　利用 BEGIN 和 END 语句使得 IF 语句在取值为 FALSE 时跳过语句块。*/
/*程序清单如下：*/
DECLARE @ErrorSaveVariable char

IF (@@ERROR <> 0)
BEGIN
   SET @ErrorSaveVariable = @@ERROR
   PRINT 'Error encountered, ' +
         CAST(@ErrorSaveVariable AS VARCHAR(10))
END

/*例3-53 用Go 语句作为批的结束语句。*/
/*程序清单如下：*/
USE adventureworks
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO
/*-- @MyMsg 在Go语句后失效。*/

/*例3-54　使用CASE函数去更改产品的分类显示，以使其更易于理解*/
/*程序清单如下：*/
USE adventureworks
SELECT   Category =
      CASE color
         WHEN 'red' THEN 'red color'
         WHEN 'black' THEN 'black color'
         WHEN 'silver' THEN 'silver color'
         WHEN 'yellow' THEN 'yellow color'
         WHEN 'blue' THEN 'blue color'
      END,
   CAST(name AS varchar(25)) AS 'Shortened name',
   listprice AS Price
FROM production.product
WHERE listprice IS NOT NULL
ORDER BY color, price
COMPUTE AVG(listprice) BY color

/*例3-55　使用搜索CASE函数，根据产品的价格范围将价格显示为文本注释*/
/*程序清单如下：*/
USE adventureworks
SELECT    'Price Category'=
     CASE
        WHEN listprice IS NULL THEN 'Not yet priced'
         WHEN listprice < 10 THEN 'Very Reasonable Title'
         WHEN listprice >= 10 and listprice < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive!'
      END,
   CAST(name AS varchar(20)) AS 'Shortened name'
FROM production.product
ORDER BY listprice
GO

/*例3-56　在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE。如果平均价格少于 $30，WHILE
循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加
倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。*/
/*程序清单如下：*/
USE adventureworks
WHILE (SELECT AVG(listprice) FROM production.product) < $30
BEGIN
   UPDATE production.product
      SET listprice = listprice * 2
   SELECT MAX(listprice) FROM production.product
   IF (SELECT MAX(listprice) FROM production.product) > $50
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear'

/*例3-57　利用GOTO语句求出从1加到5的总和。*/
/*程序清单如下：*/
declare    @sum   int,   @count   int
select   @sum=0,   @count=1
label_1:
select   @sum=@sum+@count
select   @count=@count+1
if   @count<=5
goto   label_1
select   @count,   @sum

/*例3-58　使用WAITFOR TIME语句，以便在晚上10:20执行存储过程 update_all_stats。*/
/*程序清单如下：*/
BEGIN
   WAITFOR TIME '22:20'
   EXECUTE update_all_stats
END

/*例3-59　显示如果在执行 findjobs 时没有给出用户名作为参数，RETURN 则将一条消息发送到用户的
屏幕上然后从过程中退出。如果给出用户名，将从适当的系统表中检索由该用户在当前数据库内创建的所
有对象名。*/
/*程序清单如下：*/
CREATE PROCEDURE findjobs @nm sysname = NULL
AS
IF @nm IS NULL
   BEGIN
     PRINT 'You must give a username'
      RETURN
   END
ELSE
   BEGIN
      SELECT o.name, o.id, o.uid
      FROM sysobjects o INNER JOIN master..syslogins l
         ON o.uid = l.sid
      WHERE l.name = @nm
   END

/*例4-2 使用三个 100 MB 的数据文件和两个 100 MB 的事务日志文件创建名为 Archive 的数据库。主文件是列表中的第一个文件，并使用 PRIMARY 关键字显式指定。事务日志文件在 LOG ON 关键字后指定。注意 FILENAME 选项中所用的文件扩展名：主要数据文件使用 .mdf，次要数据文件使用 .ndf，事务日志文件使用 .ldf。*/
/*程序清单如下：*/
CREATE DATABASE Archive
ON
PRIMARY
(NAME=Arch1,
FILENAME='c:/program files/microsoft sql server/mssql.1/mssql/data/archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:/program files/microsoft sql server/mssql.1/mssql/data/archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:/program files/microsoft sql server/mssql.1/mssql/data/archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:/program files/microsoft sql server/mssql.1/mssql/data/archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
(NAME=Archlog2,
FILENAME = 'c:/program files/microsoft sql server/mssql.1/mssql/data/archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO

/*例4-5　在磁盘上创建了一个备份设备。*/
/*程序清单如下：*/
USE [master]
GO
EXEC master.dbo.sp_addumpdevice
       @devtype = N'disk',
       @logicalname = N'test_backup',
       @physicalname = N'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Backup/1.bak'
GO
/*例5-4　在执行创建产品信息表的操作时，指定产品编号为主键值，
       并且创建一个聚簇索引*/
/*程序清单如下：*/
create table products(
id char(10) NOT NULL,
nname char(20) NOT NULL,
price money default 0.01,
quantity smallint NULL,
constraint pk_id primary key clustered (id)
)

/*例5-5 建立一个SC表，定义SNO，CNO共同组成SC的主键*/
/*程序清单如下：*/
CREATE TABLE SC
(SNO CHAR(5) NOT NULL,
CNO CHAR(5) NOT NULL,
SCORE NUMERIC(3),
CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO))

/*例5-6　创建一个学生信息表，其中name字段具有惟一性*/
/*程序清单如下：*/
Create table student(
id char(8),
nname char(10),
sex char(2),
constraint pk_student_id primary key (id),
constraint uk_identity unique (nname)
)

/*例5-7　创建了一个学生信息表，其中输入性别字段值时，只能接受“F”或者“M”，
而不能接受其他数据，并且为phonenum字段创建检查约束，
限制只能输入类似01082798123之类的数据，而不能随意输入其他数据。*/
/*程序清单如下：*/

USE db
create table student(
id char(8),
name char(8),
sex char(2),
phonenum int,
constraint chk_sex check(sex in ('F','M')),
constraint chk_phonenum check
(phonenum like '(010)[1-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]'))

/*例5-8 建立一个SC表，定义SCORE 的取值范围为0到100之间。*/
/*程序清单如下：*/

CREATE TABLE SC
(SNO CHAR(5),
CNO CHAR(5),
SCORE NUMERIC(5,1) CONSTRAINT SCORE_CHK CHECK(SCORE>=0 AND SCORE <=100))
/*例5-11 建立一个SC表，定义SNO,CNO为SC的外部键。*/
/*程序清单如下：*/
CREATE TABLE SC
(SNO CHAR(10) NOT NULL
CONSTRAINT S_FORE FOREIGN KEY REFERENCES S(SNO),
CNO CHAR(10) NOT NULL
CONSTRAINT C_FORE FOREIGN KEY REFERENCES C(CNO),
SCORE NUMERIC(3),
CONSTRAINT S_C_PRIM PRIMARY KEY (SNO,CNO))

/*例5-14　创建一个雇员信息表，然后在表中增加一个salary字段，删除表中的age字段，
并且修改memo字段的数据类型。*/
/*SQL语句的程序清单如下：*/

Use db
create table employees(
id char(8) primary key,
name char(20) not null,
department char(20) null,
memo char(30) null,
age int null,
)
alter table employees
add salary int null
go
alter table employees
drop column age
go
alter table employees
alter column memo varchar(200) null

/*例6-27 在分组查询中使用HAVING条件，查询平均成绩大于85的学生学号及平均成绩。*/
/*程序清单如下：*/
SELECT sno, AVG(score) AS 'AverageScore'
FROM sc
GROUP BY sno
HAVING AVG(score) >85
GO

/*例7-1　选择表s和sc中的部分字段和记录来创建一个视图，并且限制表s中的记录只能是计算机系的记录集合,视图定义为view_s*/
/*程序清单如下*/
create view view_s
as
select s.sname,s.age,s.sex,
sc.cno,sc.score
from
s,sc
where s.sno=sc.sno and s.dept='计算机'

/*例8-1　创建一个带有SELECT语句的简单过程，该存储过程返回所有员工姓名，Email地址，电话等。该存储过程不使用任何参数*/
/*程序清单如下*/
USE adventureworks
GO
CREATE PROCEDURE au_infor_all
AS
SELECT lastname, firstname, emailaddress, phone
    FROM person.contact
GO

/*例8-3　创建一个带有参数的简单存储过程，从视图中返回指定的雇员（提供名和姓）及其职务和部门名称，该存储过程接受与传递的参数精确匹配的值*/
/*程序清单如下*/
CREATE PROCEDURE GetEmployees
    @lastname varchar(40),
    @firstname varchar(20)
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @firstname AND LastName = @lastname;
GO

/*例9-2　创建一个触发器，在 s 表上创建一个插入、更新类型的触发器*/
/*程序清单如下*/

CREATE TRIGGER tr_s
ON s
FOR INSERT, UPDATE
AS
Begin
DECLARE @bh varchar(6)
SELECT @bh =inserted.sno FROM inserted
       /*获取插入或更新操作时的新值（学号）*/
End

/*例9-3建立一个触发器，当向sc表中添加数据时，如果添加的数据与s表中的数据不匹配（没有对应的学号），则将此数据删除*/
/*程序清单如下*/
CREATE TRIGGER sc_ins ON sc
FOR INSERT
AS
BEGIN
DECLARE @bh char(10)
Select @bh=Inserted.sno from Inserted
If not exists(select sno from s where s.sno=@bh)
Delete sc where sno=@bh
END

/*例9-5　创建一个修改触发器，该触发器防止用户修改表s的入学成绩*/
/*程序清单如下*/
create trigger tri_s_upd
on sc
for update
as
if update(score)
begin
raiserror('不能修改入学成绩',16,10)
rollback transaction
end
go
/*例9-8 建立一个与s表结构一样的表s1，当删除表s中的记录时，自动将删除掉的记录存放到s1表中*/
/*程序清单如下*/
CREATE TABLE [dbo].[s1](
       [birthday] [datetime] NULL,
       [sno] [char](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER tr_del ON s /*建立触发器*/
FOR DELETE /*对表删除操作*/
AS insert s1 select * from deleted /*将删除掉的数据送入表s1中*/
GO
/*例-9当删除表s中的记录时，自动删除表sc中对应学号的记录*/
/*程序清单如下*/
CREATE TRIGGER tr_del_s ON s
FOR DELETE
AS
BEGIN
DECLARE @bh char(10)
Select @bh=deleted.sno from deleted
Delete sc where sno=@bh
END

上一篇：开发环境下优化SQl语句的十个重要步骤

下一篇：SQL Server 2005 CE软件环境需求