學習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軟件環境需求