SQL Server数据库设计

Dear 丶 2021-09-12 02:01 527阅读 0赞

21实际是信息时代，借助于计算机和网络，人们可以方便地获得所需要的信息，也可以根据需要在网络上发布个人或单位的信息，供人们使用。

数据库（DataBase）技术是程序开发人员必须掌握的技术之一，因为多数应用系统都需要把数据进行存储、分类和检索。

Microsoft SQL Server 是微软公司的数据库产品，Microsoft SQL Server脱胎于Sybase SQL Server。在设计上，Microsoft SQL Server大量利用了Microsoft Windows操作系统的底层结构，直接面向Microsoft Windows，尤其是NT系列服务器操作系统的用户。它基本不能移植到其他操作系统上，就算勉强移植，也无法得到很好的性能。Microsoft SQL Server作为一个商业化的产品，他的优势是Microsoft产品所共有的——易用性。Microsoft SQL Server主要用于大规模联机事务处理（OLTP）、数据仓库和电子商务应用的数据库和数据分析平台。

**第一章 数据库的设计**

数据库文件由以下3部分组成。

数据库文件：\*.mdf。

次要数据库文件：\*.ndf。

日志文件：\*.ldf。

其中，次要数据文件库可选，可以有多个数据库文件和日志文件。

**1.1 创建数据库**

T-SQL创建数据库的语法如下。

CREATE DATABASE 数据库名

ON \[PRIMARY\]

(

<数据文件参数> \[, ...n\] \[<文件组参数>\]

)

\[LOG ON\]

(

\{<日志文件参数> \[, ...n\]\}

)

文件的具体参数的语法如下。

( \[NAME = 逻辑文件名,\]

FILENAME = 物理文件名

\[, SIZE = 大小\]

\[, MAXSIZE = \{最大容量 | UNLIMTED \}\]

\[, FILEGROWTH = 增长量\] ) \[, ...n\]

文件组参数的语法如下。

FILEGROUP 文件组名 <文件参数> \[, ...n\]

其中，“\[ \]”表示可选部分，“\{ \}”表示必需的部分。

**示例：**

创建一个数据文件和一个日志文件。

USE master  --设置当前数据库为master，以方便访问sysdatabases表  
GO  
IF EXISTS(SELECT \* FROM sysdatabases WHERE name = 'stuDB')  
DROP DATABASE stuDB  
CREATE DATABASE stuDB  
ON PRIMARY  --默认就属于PRIMARY主文件，可以省略  
(  
 /\*----数据文件的具体描述--\*/  
 NAME='stuDB\_data',  --主数据文件的逻辑名  
 FILENAME='D:/project/stuDB\_data.mdf',  --主数据文件的物理名  
 SIZE=5mb,  --主数据文件初始大小  
 MAXSIZE=100mb,  --主数据文件增长的最大值  
 FILEGROWTH=15%   --主数据文件的增长率  
)  
LOG ON  
(  
  /\*----日志文件的具体描述，各参数含义同上--\*/  
  NAME='stuDB\_log',  
  FILENAME='D:/project/stuDB\_log.ldf',  
  SIZE=2mb,  
  FILEGROWTH=1 mb  
)  
GO

**1.2 删除数据库**

删除数据库的语法如下。

DROP DATABASE 数据库名

例如：

DROP DATABASE stuDB

**第二章 数据库表管理**

** 2.1 使用SQL语句创建和删除表**

** 2.1.1 创建表**

创建表的语法如下。

CREATE TABLE 表名

(

字段1  数据类型  列的特性

字段2  数据类型  列的特性

...

)

其中，“列的特性”包括该列是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键。

考虑各种开发语言的兼容性，表中各字段名称推荐使用英文缩写。

**示例： **

创建学员信息表stuInfo。

USE stuDB   --将当前数据库设置为stuDB ，以便在stuDB数据库中建表  
GO  
IF EXISTS(SELECT \* FROM sysobjects WHERE name='stuInfo')  
 DROP TABLE stuInfo  
CREATE  TABLE  stuInfo  /\*-创建学员信息表-\*/  
(  
stuName  VARCHAR(20)  NOT  NULL ,  --学员姓名，非空（必填）  
stuNo   CHAR(6)  NOT  NULL,   --学号，非空（必填）  
stuAge  INT  NOT  NULL,  --年龄，INT类型不用指定大小，默认为4个字节  
stuID  NUMERIC(18,0),     --身份证号，NUMERIC (18,0)代表18位数字，小数位数为0  
stuSeat   SMALLINT  IDENTITY (1,1),   --座位号，自动编号（标识列），从1开始递增  
stuAddress   TEXT   --住址，允许为空，即可选输入  
)  
GO

**2.1.2 删除表**

删除表的语法如下。

DROP TABLE 表名

例如：

DROP TABLE stuInfo

**2.2 使用SQL语言创建和删除约束**

常用的约束类型如下。

主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空。

唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。

检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束

默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”。

外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列。

**2.2.1 增加约束**

ALTER TABLE 表名

ADD CONSTRAINT 约束名  约束类型  具体的约束说明

上述语法表示修改某个表，添加某个约束。其中，约束名的名称规定采用“约束类型\_约束字段”这种形式。

**示例：**

\---- 添加主键约束（stuNo作为主键）  
ALTER TABLE stuInfo  
ADD CONSTRAINT PK\_stuNo PRIMARY KEY (stuNo)  
\---添加唯一约束（身份证号唯一，因为每人的身份证号全国唯一）  
ALTER TABLE stuInfo  
ADD CONSTRAINT UQ\_stuID UNIQUE (stuID)  
\---添加默认约束（如果地址不填，默认为“地址不详”）  
ALTER TABLE stuInfo  
ADD CONSTRAINT DF\_stuAddress DEFAULT ('地址不详') FOR stuAddress  
\---添加检查check约束，要求年龄只能在15－40岁之间  
ALTER TABLE stuInfo  
ADD CONSTRAINT CK\_stuAge CHECK(stuAge BETWEEN 15 AND 40)  
\--添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）  
ALTER TABLE stuMarks  
   ADD CONSTRAINT FK\_stuNo           
     FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)  
GO

**2.2.2 删除约束**

删除约束的语法如下。

ALTER TABLE 表名

DROP CONSTRAINT 约束名

**例如：**删除stuInfo表中地址默认约束

ALTER  TABLE  stuInfo

DROP  CONSTRAINT  DF\_stuAddress

**2.3 使用SQL语句创建登录**

**2.3.1 创建登录帐户**

登录验证有两种方式：

SQL身份验证：适合于非windows平台的用户或Internet用户， 需要提供帐户和密码。

Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证。

所以，我们创建的登录用户应该有两种：SQL帐户和Windows帐户。

添加Windows登录帐户需要调用SQL Server 内置的系统函数sp\_grantlogin，调用的语法如下。

EXEC sp\_grantlogin   'windows域名/域帐户'

如果是本机，可用计算机名替换“windows域名”。

添加SQL登录帐户需要调用系统存储过程sp\_addlogin，调用语法如下。

EXEC sp\_addlogin  '账户名', '密码'

**示例：**

/\*--添加Windows登录帐户 --\*/

EXEC  sp\_grantlogin  'jbtraining/s26301'   -- windos用户：jbtraining/s26301，jbtraining表示域

/\*--添加SQL登录帐户--\*/

EXEC  sp\_addlogin  'zhangsan', '1234'      -- 帐户：zhangsan 密码：1234  
GO

**2.3.2创建数据库用户 **

创建数据库用户需要调用系统存储过程sp\_grantdbaccess，调用语法如下。

EXEC sp\_grantdbaccess '登录帐户名','数据库用户名'

**示例：**

/\*--在stuDB数据库中添加两个用户--\*/  
USE stuDB  
GO  
EXEC sp\_grantdbaccess  'jbtraining/S26301', 'S26301DBUser'  -- S26301DBUser为数据库用户名  
EXEC sp\_grantdbaccess  'zhangsan', 'zhangsanDBUser'

**2.3.3 给数据库用户授权**

授权的语法为：

GRANT 权限 \[ON  表名 \]  TO  数据库用户

**示例：**

USE  stuDB  
GO  
/\*--为zhangsanDBUser分配对表stuInfo的select, insert, update权限--\*/  
GRANT select, insert, update  ON  stuInfo  TO  zhangsanDBUser  
/\*--为S26301DBUser分配建表的权限--\*/  
GRANT  create  table  TO  S26301DBUser

**第三章 数据管理**

**3.1 使用T-SQL插入数据**

**3.1.1 使用INSERT插入数据行 **

使用INSERT语句一行一行插入数据是最常用的方法，其语法格式如下。

INSERT  \[INTO\] <表名>  \[列名\] VALUES <值列表>

**例如：**向学生表中插入一行数据

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)  
VALUES ('张青裁','上海松江',6,'ZQC@Sohu.com',0)

**3.1.2 一次插入多行数据**

**1、通过INSERT SELECT 语法将现有表中的数据添加到新表 **

INSERT INTO <新表名>(列名)  
SELECT <列名>  
FROM <源表名>

**例如： **

INSERT INTO TongXunLu ('姓名','地址','电子邮件')  
SELECT SName,SAddress,SEmail  
FROM Students

**2.、通过SELECT INTO 语句将现有表中的数据添加到新表**

这个新表是执行查询语句的时候创建的，不能够预先存在。

因为标识列的数据是不允许指定的，因此我们可以创建一个新的标识列，如法如下。

SELECT IDENTITY(数据类型,标识种子,标识增长量) AS 列名  
INTO 新表  
FROM 原始表

**例如：**

SELECT Students.SName,Students.SAddress,Students.SEmail，IDENTITY(int,1,1) As StudentID  
INTO TongXunLuEX  
FROM Students

**3、通过UNION关键字合并数据进行插入**

语法如下。

INSERT INTO <表名>(列名)  
SELECT <列名> UNION  
SELECT <列名> UNION

**例如：**

INSERT  STUDENTS (SName,SGrade,SSex)  
SELECT '测试女生1',7,0 UNION  
SELECT '测试女生2',7,0 UNION  
SELECT '测试女生3',7,0 UNION  
SELECT '测试女生4',7,0 UNION  
SELECT '测试男生5',7,1

**3.2 使用T-SQL更新数据**

使用T-SQL更新表中某行的语法如下。

UPDATE <表名> SET <列名 = 更新值>  \[WHERE <更新条件>\]

**例如：**

UPDATE Students  
SET SAddress ='北京女子职业技术学校家政班'  
WHERE SAddress = '北京女子职业技术学校刺绣班'

UPDATE Scores  
SET Scores = Scores + 5  
WHERE Scores <= 95

**3.3 使用T-SQL删除数据**

**3.3.1 使用DELETE删除数据**

使用T-SQL删除表中的数据，语法如下。

DELETE FROM <表名> \[WHERE <删除条件>\]

**例如： **

DELETE FROM Students WHERE SName = '张三'

**3.3.2 使用TRUNCARE TABLE 删除数据**

TRUNCATE TABLE 用来删除表中所有行的命令。

**例如：**要删除学员信息表中的所有记录行

TRUNCATE TABLE Students

**第四章 数据查询 **

**4.1 使用SELECT语句进行查询**

SELECT    <列名>  
FROM      <表名>  
\[WHERE    <查询条件表达式>\]  
\[ORDER BY <排序的列名>\[ASC或DESC\]\]

**4.2 查询排序**

如果需要按照一定的顺序排列查询语句中选中的行，需要使用ORDER BY 子句，并且排序可以是升序（ASC）或则降序（DESC）。

**例如：**查询学员成绩的时候，如果把所有的成绩都降低10%后加5分，在按照及格成绩的高低来进行排序。

SELECT StudentID As 学员编号,(Score\*0.9+5) As 综合成绩  
FROM Score  
WHERE (Score\*0.9+5)>60  
ORDER BY Score DESC

**4.3 模糊查询**

**4.3.1 使用LIKE进行模糊查询**

在数据更新、删除或者查询的时候，依然可以使用LIKE关键字来进行匹配查询，例如：

SELECT SName AS 姓名 FROM Students  
WHERE SName LIKE '张%'

或者查询不是八月份发行的A卡或者C卡：

SELECT \* FROM 数据表  
WHERE 编号 LIKE '00\[^8\]%\[A,C\]%'

**4.3.2 使用BETWEEN在某个范围内进行行查询**

使用BETWEEN可以查询那些介于两个已知值之间的一组未知值。例如：

SELECT StudentID, Score FROM SCore WHERE Score  
BETWEEN 60 AND 80

此外，BETWEEN查询在查询日期范围的时候使用得比较多。

例如：查询不在1992年8月1日到1993年8月1日之间订购的读书列表：

SELECT \* FROM Sales WHERE ord\_date  
NOT BETWEEN '1992-8-1' AND '1993-8-1'

**4.3.3 使用IN在列举值内进行查询**

**例如：**

SELECT SName AS 学员姓名,SAddress As 地址 FROM Students WHERE SAddress IN ('北京','广州','上海')

你还可以把IN关键字和NOT关键字合起来使用，这样可以得到所有不匹配列举值的行。

**4.4 SQL Server中的聚合函数 **

**1. SUM**

SUM返回表达式中所有行数的总和，SUM只能用于数字

**例如：**要得到商务付款的总数，执行一下查询：

SELECT SUM(ytd\_sales) FROM titles WHERE type = 'business'

**2.AVG**

AVG函数返回表达式中所有数值的平均值，AVG函数UE只能用于数字类型的列。

**例如：**

SELECT AVG(SCore) AS 平均成绩 From Score WHERE Score >=60

**3. MAX和MIN**

MAX返回表达式中的最大值，MIN返回表达式的最小值，它们都可以用于数字型、字符型以及日期/时间类型的列。

**例如：**

SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分,  
MIN (Score) AS 最低分 From Score WHERE Score >=60

**4 . COUNT**

COUNT返回提供的表达式中非空值的计算，COUNT可以用于数字和字符类型的列。

例如：查询及格人数的语句如下。

SELECT COUNT (\*)  AS 及格人数 From Score  
WHERE Score>=60

**4.5 分组查询 **

**4.5.1 使用Group By进行分组查询 **

SELECT CourseID, AVG(Score) AS 课程平均成绩  
FROM Score  
GROUP BY CourseID

**4.5.2 使用HAVING子句进行分组筛选 **

因为WHERE子句只能对没有分组统计前的数据进行筛选（例如成绩必须大于60分），对于分组后的条件的筛选必须使用HAVING子句。

SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩  
FROM Score  
GROUP BY StudentID,CourseID  
HAVING COUNT(Score)>1

**4.6 多表联接查询**

**4.6.1 内联接查询**

**1. 在WHERE子句中指定联接条件**

**例如：**查询学员姓名和成绩的语句。

SELECT Students.SName,Score.CourseID,Score.Score  
FROM Studnets,Score  
WHERE Students.Scode = Score.StudentID

**2. 在FROM子句中使用JOIN...ON**

**例如：**

SELECT S.SName,C.CourseID,C.Score  
From Students AS S INNER JOIN Score AS C  
ON C.StudentID = S.SCode

**4.6.2 外联接查询**

**1.左外联接查询**

**例如：**

SELECT  S.SName,C.CourseID,C.Score  
From Students AS S  
LEFT JOIN Score AS C ON C.StudentID = S.SCode

**2.右外联接查询**

**例如：**

SELECT Titles.Title\_id, Titles.Title, Publishers.Pub\_name  
FROM titles  
RIGHT OUTER JOIN Publishers ON Titles.Pub\_id = Publishers.Pub\_id

**第五章 T-SQL编程 **

**5.1 使用变量**

**5.1.1 局部变量 **

局部变量的名称必须以标记@作为前缀。

声明局部变量的语法如下。

DECLARE  @variable\_name  DataType

其中，variable\_name为局部变量的名称，DataType为数据类型。

**例如：**

DECLARE @name varchar(8)   --声明一个存放学员姓名的变量name，最多可以存储8个字符  
DECLARE @seat int                 --声明一个存放学员座位号的变量seat

局部变量的赋值有两种方法：使用SET语句和SELECT语句。

SET @variable\_name = value

SELECT @variable\_name = value

**5.1.2  全局变量**

SQL Server中的所有全局变量都使用@@标志作为前缀。

**5.2 输出语句**

常用的输出语句有两种，它们的语法分别如下。

print  局部变量或字符串

SELECT  局部变量  AS  自定义列名

其中，第二种方法是查询语句的特殊用法。

**示例：**

print '服务器的名称：' + @@SERVERNAME  
SELECT @@SERVERNAME AS '服务器的名称'

**5.3 逻辑控制语句**

**5.3.1 IF-ELSE条件语句**

IF (条件)  
     BEGIN  
          语句1  
          语句2  
          ...  
     END

ELSE  
     BEGIN  
        语句1  
        语句2  
        ...  
END

**示例：**

DECLARE @myavg float  
SELECT @myavg=AVG(writtenExam)  from stuMarks  
print '本班平均分'+convert(varchar(5),@myavg)  
IF (@myavg>70)  
  BEGIN  
    print '本班笔试成绩优秀，前三名的成绩为'  
    SELECT TOP 3 \* FROM stuMarks ORDER BY writtenExam DESC  
  END  
ELSE  
  BEGIN  
    print '本班笔试成绩较差，后三名的成绩为'  
    SELECT TOP 3 \* FROM stuMarks ORDER BY writtenExam DESC  
  END

**5.3.2 WHERE循环语句**

**语法：**

WHERE (条件)  
       语句或语句块  
       \[BREAK\]

可以在WHERE循环中使用CONTINUE和BREAK关键字来控制语句的执行。

**示例：**

INSERT INTO stuMarks(examNo,stuNo,writtenExam,LabExam)  --插入测试数据  
   VALUES('S271819','s25318',56,48)  
SELECT \* FROM stuMarks

DECLARE @n int  
WHILE(1=1) --条件永远成立  
  BEGIN  
    SELECT @n=COUNT(\*) FROM stuMarks WHERE writtenExam<60 --统计不及格人数  
    IF (@n>0)  
       UPDATE stuMarks SET writtenExam=writtenExam+2  --每人加2分  
    ELSE  
       BREAK  --退出循环  
  END  
print '加分后的成绩如下：'  
SELECT \* FROM stuMarks

**5.3.2 CASE多分支语句**

CASE  
     WHERE 条件1 THEN 结果1  
     WHERE 条件2 THEN 结果2  
     \[ELSE 其他结果\]  
END

**示例：**

SELECT \* FROM stuMarks  --原始成绩  
print 'ABCDE五级显示成绩如下：'  
SELECT stuNo,成绩= CASE   
                     WHEN writtenExam<60 THEN 'E'  
                     WHEN writtenExam between 60 AND 69 THEN 'D'  
                     WHEN writtenExam between 70 AND 79 THEN 'C'  
                     WHEN writtenExam between 80 AND 89 THEN 'B'  
                     ElSE 'A'  
                  END  
             FROM stuMarks

**第六章 事务、索引和视图 **

**6.1 事务 **

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作。

Transact-SQL使用下列语句来管理事务。

开始事务：BEGIN TRANSACTION

提交事务：COMMIT TRANSACTION

回滚（撤销）事务：ROLLBACK TRANSACTION

**示例：**

USE stuDB  
GO  
\--恢复原来的数据  
\--UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='李四'  
SET NOCOUNT ON --不显示受影响的行数信息  
print '查看转帐事务前的余额'  
SELECT \* FROM bank   
GO

/\*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--\*/  
BEGIN TRANSACTION  
/\*--定义变量，用于累计事务执行过程中的错误--\*/  
DECLARE @errorSum INT  
SET @errorSum=0  --初始化为0，即无错误

/\*--转帐：张三的帐户少1000元，李四的帐户多1000元\*/  
UPDATE bank SET currentMoney=currentMoney-800 WHERE customerName='张三'  
SET @errorSum=@errorSum+@@error  --累计是否有错误  
UPDATE bank SET currentMoney=currentMoney+800 WHERE customerName='李四'  
SET @errorSum=@errorSum+@@error  --累计是否有错误

print '查看转帐事务过程中的余额'  
SELECT \* FROM bank

/\*--根据是否有错误，确定事务是提交还是撤销---\*/  
IF @errorSum<>0  --如果有错误  
  BEGIN  
    print '交易失败，回滚事务'  
    ROLLBACK TRANSACTION  
  END   
ELSE  
  BEGIN  
    print '交易成功，提交事务，写入硬盘，永久的保存'  
    COMMIT TRANSACTION    
  END  
GO

print '查看转帐事务后的余额'  
SELECT \* FROM bank   
GO

**6.2索引**

索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。  
索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

使用T-SQL语句创建索引语法如下。

CREATE \[UNIQUE\] \[CLUSTERED|NONCLUSTERED\]   
    INDEX   index\_name  
     ON table\_name (column\_name…)  
      \[WITH FILLFACTOR=x\]

**示例：**

USE stuDB  
GO  
/\*--检测是否存在该索引(索引存放在系统表sysindexes中)----\*/  
IF EXISTS (SELECT name FROM sysindexes  
          WHERE name = 'IX\_stuMarks\_writtenExam')  
   DROP INDEX stuMarks.IX\_stuMarks\_writtenExam  --删除索引  
/\*--笔试列创建聚集索引:填充因子为30％--\*/  
CREATE NONCLUSTERED INDEX IX\_stuMarks\_writtenExam  
   ON stuMarks(writtenExam)  
    WITH FILLFACTOR= 30  
GO

/\*--指定按索引：IX\_stuMarks\_writtenExam查询--\*/  
SELECT \* FROM stuMarks  
  (INDEX=IX\_stuMarks\_writtenExam)  
    WHERE writtenExam BETWEEN 60 AND 90

**6.3 视图**

视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上。  
视图中并不存放数据，而是存放在视图所引用的原始表（基表）中。  
同一张原始表，根据不同用户的不同需求，可以创建不同的视图。

**使用T-SQL语句创建视图语法如下。**

CREATE VIEW view\_name   
   AS  
    <select语句>

**示例：**

USE stuDB  
GO  
/\*--检测是否存在：视图存放在系统表sysobjects中--\*/  
IF EXISTS (SELECT \* FROM  sysobjects  
          WHERE name = 'view\_stuInfo\_stuMarks')  
   DROP VIEW view\_stuInfo\_stuMarks  
GO

/\*--创建视图：查看学员的成绩情况--\*/  
CREATE VIEW view\_stuInfo\_stuMarks  
  AS  
    SELECT 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,  
      机试成绩=labExam,平均分=(writtenExam+labExam)/2  
        FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo  
GO

/\*--使用视图：视图是一个虚拟表，可以像物理表一样打开--\*/  
SELECT \* FROM view\_stuInfo\_stuMarks

**第七章 存储过程 **

存储过程（procedure）类似于C语言中的函数  
用来执行管理任务或应用复杂的业务规则  
存储过程可以带参数，也可以返回结果

**7.1 常用的系统存储过程**

**示例： **

\-- Purpose: 常用系统存储过程使用  
EXEC sp\_databases  --列出当前系统中的数据库  
EXEC  sp\_renamedb 'Northwind','Northwind1'--改变数据库名称(单用户访问)

USE stuDB  
GO  
EXEC sp\_tables  --当前数据库中查询的对象的列表  
EXEC sp\_columns stuInfo  --返回某个表列的信息  
EXEC sp\_help stuInfo  --查看表stuInfo的信息  
EXEC sp\_helpconstraint stuInfo --查看表stuInfo的约束  
EXEC sp\_helpindex stuMarks  --查看表stuMarks的索引  
EXEC sp\_helptext 'view\_stuInfo\_stuMarks' --查看视图的语句文本  
EXEC sp\_stored\_procedures  --返回当前数据库中的存储过程列表

**7.2 用户定义的存储过程**

**7.2.1 创建不带参数的存储过程**

**语法：**

CREATE  PROC\[EDURE\]  存储过程名  
          @参数1  数据类型 = 默认值 OUTPUT,  
            …… ,  
          @参数n  数据类型 = 默认值 OUTPUT  
          AS  
          QL语句  
GO

**示例：**

USE stuDB  
GO  
/\*---检测是否存在：存储过程存放在系统表sysobjects中---\*/  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'proc\_stu' )  
  DROP PROCEDURE  proc\_stu  
GO  
/\*---创建存储过程----\*/  
CREATE PROCEDURE proc\_stu  
  AS  
    DECLARE @writtenAvg float,@labAvg float --笔试和机试平均分变量  
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks  
    print '笔试平均分：'+convert(varchar(5),@writtenAvg)   
    print '机试平均分：'+convert(varchar(5),@labAvg)  
    IF (@writtenAvg>70 AND @labAvg>70)  
       print '本班考试成绩：优秀'  
    ELSE  
       print '本班考试成绩：较差'  
    print '--------------------------------------------------'  
    print '           参加本次考试没有通过的学员：'  
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo  
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo  
         WHERE writtenExam<60 OR labExam<60  
GO

/\*---调用存储过程----\*/  
EXEC proc\_stu  --调用存储过程的语法：EXEC 过程名 \[参数\]

**7.2.2 创建带输入参数的存储过程**

**示例：**

USE stuDB  
GO  
/\*---检测是否存在：存储过程存放在系统表sysobjects中---\*/  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'proc\_stu' )  
  DROP PROCEDURE  proc\_stu  
GO  
/\*---创建存储过程----\*/  
CREATE PROCEDURE proc\_stu  
  @writtenPass int,          --输入的参数：笔试及格线  
  @labPass int                 --输入的参数：机试及格线  
  AS  
    print '笔试及格线：'+convert(varchar(5),@writtenPass)  
    print '机试及格线：'+convert(varchar(5),@labPass)  
    print '--------------------------------------------------'  
    print '           参加本次考试没有通过的学员：'  
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo  
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo  
         WHERE writtenExam<@writtenPass OR labExam<@labPass  
GO

/\*---调用存储过程----\*/  
\--假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分。  
EXEC proc\_stu 60,55   
\--或这样调用：EXEC proc\_stu @labPass=55,@writtenPass=60

**存储过程输入参数允许采用默认值，修改上例。**

USE stuDB  
GO  
/\*---检测是否存在：存储过程存放在系统表sysobjects中---\*/  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'proc\_stu' )  
  DROP PROCEDURE  proc\_stu  
GO  
/\*---创建存储过程----\*/  
CREATE PROCEDURE proc\_stu  
  @writtenPass int=60,  
  @labPass int=60  
  AS  
    print '笔试及格线：'+convert(varchar(5),@writtenPass)  
       + '   机试及格线：'+convert(varchar(5),@labPass)  
    print '--------------------------------------------------'  
    print '           参加本次考试没有通过的学员：'  
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo  
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo  
         WHERE writtenExam<@writtenPass OR labExam<@labPass  
GO

/\*---调用存储过程----\*/  
EXEC proc\_stu   --都采用默认值：笔试和机试及格线都为60分

EXEC proc\_stu 64  --机试采用默认值：笔试及格线64分，机试及格线60分。

EXEC proc\_stu 60,55   --都不采用默认值：笔试及格线60分，机试及格线55分。

\--错误的调用方式：EXEC proc\_stu  ,55  --希望笔试采用默认值，机试及格线55分  
\--正确的调用方式：EXEC proc\_stu @labPass=55   --笔试采用默认值，机试及格线55分

**7.2.3 创建带输出参数的存储过程**

**示例：**

USE stuDB  
GO  
/\*---检测是否存在：存储过程存放在系统表sysobjects中---\*/  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'proc\_stu' )  
  DROP PROCEDURE  proc\_stu  
GO  
/\*---创建存储过程----\*/  
CREATE PROCEDURE proc\_stu  
  @notpassSum int OUTPUT, --OUTPUT关键字，否则视为输入参数  
  @writtenPass int=60,  --默认参数放后  
  @labPass int=60       --默认参数放后  
  AS  
    print '笔试及格线：'+convert(varchar(5),@writtenPass)  
       + '   机试及格线：'+convert(varchar(5),@labPass)  
    print '--------------------------------------------------'  
    print '           参加本次考试没有通过的学员：'  
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo  
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo  
         WHERE writtenExam<@writtenPass OR labExam<@labPass  
    /\*--统计并返回没有通过考试的学员人数--\*/  
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks  
      WHERE writtenExam<@writtenPass OR labExam<@labPass  
     
GO

/\*---调用存储过程----\*/  
DECLARE @sum int  
EXEC proc\_stu @sum OUTPUT ,64   --机试及格线采用默认值：笔试及格线64分，机试及格线60分。  
print '--------------------------------------------------'  
IF @sum>=3  
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'  
ELSE  
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'  
GO

**7.2.4 处理错误信息**

**RAISERROR语句的用法如下：**

RAISERROR 显示用户定义的错误信息时  
可指定严重级别，  
设置系统变量@@ERROR  
记录所发生的错误等

RAISERROR (msg\_id | msg\_str,severity,  
   state WITH option\[,...n\]\])

msg\_id：在sysmessages系统表中指定用户定义错误信息  
msg\_str：用户定义的特定信息，最长255个字符  
severity：定义严重性级别。用户可使用的级别为0–18级  
state：表示错误的状态，1至127之间的值  
option：指示是否将错误记录到服务器错误日志中

**示例：**

USE stuDB  
GO  
/\*---检测是否存在：存储过程存放在系统表sysobjects中---\*/  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'proc\_stu' )  
  DROP PROCEDURE  proc\_stu  
GO  
/\*---创建存储过程----\*/  
CREATE PROCEDURE proc\_stu  
  @notpassSum int OUTPUT, --OUTPUT关键字，否则视为输入参数  
  @writtenPass int=60,  --默认参数放后  
  @labPass int=60       --默认参数放后  
  AS  
    /\*------------------------错误处理---------------------------\*/  
    IF  (NOT @writtenPass BETWEEN 0 AND 100) OR(NOT @labPass BETWEEN 0 AND 100)  
       BEGIN  
         RAISERROR ('及格线错误，请指定0－100之间的分数，统计中断退出',16,1)  
         RETURN  --立即返回，退出存储过程  
       END  
    print '笔试及格线：'+convert(varchar(5),@writtenPass)  
       + '   机试及格线：'+convert(varchar(5),@labPass)  
    print '--------------------------------------------------'  
    print '           参加本次考试没有通过的学员：'  
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo  
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo  
         WHERE writtenExam<@writtenPass OR labExam<@labPass  
    /\*--统计并返回没有通过考试的学员人数--\*/  
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks  
      WHERE writtenExam<@writtenPass OR labExam<@labPass  
     
GO

/\*---调用存储过程----\*/  
DECLARE @sum int,@t int  
EXEC proc\_stu @sum OUTPUT ,888   --笔试及格线误输入604分  
SET @t=@@ERROR --如果出现了错误，执行了RAISERROR语句，系统全局@@ERROR将不等于0，表示有错  
print  '错误号：'+convert(varchar(5),@t)  
IF @t<>0   
   RETURN  --退出批处理，后续语句不再执行  
print '--------------------------------------------------'  
IF @sum>=3  
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'  
ELSE  
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'  
GO