SQL Server编程入门

SQL Server 编程入门
一、T—SQL 的组成
1、DML(数据操作语言 Data Manipulation Language)
查询、插入、删除和修改数据库中的数据。SELECT、INSERT、UPDATE、DELETE 等;
2、DCL(数据控制语言 Data Control Language)
用来控制存取许可、存取权限等。GRANT、REVOKE 等。
3、DDL(数据定义语言 Data Definition Language)
用来建立数据库、数据库对象和定义其列。CREATE TABLE 、DROP TABLE 等。
4、变量说明、流程控制、功能函数
定义变量、判断、分支、循环结构等。日期函数、数学函数、字符函数、系统函数等。
二、库
1、建立库
--判断是否存在该库,如果有则删除
USE master --设置当前数据库为 master,以便访问 sysdatabases表
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='stuDB')
DROP DATABASE stuDB
--建立数据库
CREATE DATABASE stuDB
ON PRIMARY --默认就属于 PRIMARY 主文件组,可省略
1SQL Server 编程入门 作者:徐佳
(
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=1MB
)
GO
2、删除库
USE master --设置当前数据库为 master,以便访问 sysdatabases表
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='stuDB')
DROP DATABASE stuDB

EXISTS()语句:检测是否存在 stuDB 数据库,如果存在 stuDB 数据库,则删除
sysdatabases 表在 master 数据库中,保存着当前系统中所有的数据库
三、表
1、建表
--判断是否存在该表,有则删除
2SQL Server 编程入门 作者:徐佳
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), --身份证号
stuSeat SMALLINT IDENTITY (1,1), --座位号,自动编号
stuAddress TEXT --住址,允许为空,即可选输入
)
GO
2、删表
USE 库名 --将当前数据库设置为 stuDB ,以便在 stuDB 数据库中建表
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='表名' )
DROP TABLE 表名
四、约束
1、主键(primary key)
ALTER TABLE stuInfo
ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)
3SQL Server 编程入门 作者:徐佳
2、唯一性(uinque)
ALTER TABLE stuInfo
ADD CONSTRAINT UQ_stuID UNIQUE (stuID)
3、默认填写(default('……' ) for)
ALTER TABLE stuInfo
ADD CONSTRAINT DF_stuAddress
DEFAULT ('地址不详') FOR stuAddress
4、检查(check(……))
ALTER TABLE stuInfo
ADD CONSTRAINT CK_stuAge
CHECK(stuAge BETWEEN 15 AND 40)
5、外键(foreign key(列名) references 主表名(列名))
ALTER TABLE stuMarks
ADD CONSTRAINT FK_stuNo
FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
6、删除约束
ALTER TABLE 有约束的表名 DORP 约束名
五、通配符

通配符 解释 示例
'_' 一个字符 A Like 'C_'
% 任意长度的字符串 B Like 'CO_%'
[ ] 括号中所指定范围内的一个字符 C Like '9W0[1-2]'
[^] 不在括号中所指定范围内的一个字符 D Like '%[A-D][^1-2]'
六、插入数据
INSERT 表名(列名) VALUES (插入的列值)
insert stuinfo(stuname,stuno,stuage,stuid,stuaddress)
values('张三',001,20,100,'hello')
1、注意事项
A、每次插入一行数据,不可能只插入半行或者几列数据,因此,插入的数据是否有效将
按照整行的完整性的要求来检验。
B、每个数据值的数据类型、精度和小数位数必须与相应的列匹配。
C、不能为标识列指定值,因为它的数字是自动增长的。
D、如果在设计表的时候就指定了某列不允许为空,则必须插入数据。
E、插入的数据项,要求符合检查约束的要求。
F、具有缺省值的列,可以使用 DEFAULT(缺省)关键字来代替插入的数值。
2、插入多行数据
(1)对象表存在
INSERT INTO <表名>(列名)
SELECT <列名> FROM <源表名>
INSERT INTO stuinfobak (stuname,stuno,stuage)
SELECT stuname,stuno,stuage FROM stuinfo

5SQL Server 编程入门 作者:徐佳
(Stuinfobak 表必须在数据库中存在)
(2)对象表不存在
SELECT (列名) INTO <表名> FROM <源表名>
SELECT stuname,stuno,stuage INTO stuinfobak1 FROM stuinfo
(Stuinfobak1 表必须在数据库中不存在)
(3)插入新的标识列
IDENTITY(数据类型,标识种子,标识增量)
SELECT IDENTITY(数据类型,标识种子,标识增长量) AS 列名
INTO 新表 FROM 原始表
SELECT Students,SName,Students,SAddress,Students,SEmail,
IDENTITY(int,1,1) As StudentID
INTO TongXunLuEX FROM Students
(4)插入多行内容
INSERT INTO <表名>(列名)
SELECT 列内容 UNION
SELECT 列内容 UNION
……
七、更新数据行
UPDATE <表名> SET <列名 = 更新值>
[WHERE <更新条件>]
UPDATE Students
SET SAddress ='某社区 22#楼 4 单元45号'
WHERE SAddress = '某社区 22#楼4 单元 45 号'
UPDATE Scores
6SQL Server 编程入门 作者:徐佳
SET Scores = Scores + 5
WHERE Scores <= 95
八、删除数据行
1、删除指定的行
DELETE FROM <表名> [WHERE <删除条件>]
注意:DELETE FROM不会只删单个字段,要删就是整行
2、删除所有记录
TRUNCATE TABLE <表名>
注意:不能用于有外键约束引用的表。删除后,表的结构、列、约束、索引不变。
九、查询
SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC(升)或 DESC(降)] ] --默认为升序
1、查询全部的行和列
SELECT * FROM Students
2、查询部分行
SELECT <列名>
FROM <表名>
WHERE <查询条件表达式>
7SQL Server 编程入门 作者:徐佳
3、自定义命名查询结果中的列名
(1)使用 AS 来命名列
SELECT 原表中列名 AS 查询结果中显示的列名
FROM <表名>
WHERE <查询条件表达式>
SELECT Stuno AS 学员编号,StuName AS学员姓名,StuAddress AS 学员地址
FROM stuinfo
WHERE SAddress like '地址不详'
(2)合并两列数据,以规定格式输出查询结果
SELECT 原表中列名+ '.' +原表中列名 AS 查询结果中显示的列名
FROM 表名
(3)使用=来命名列
SELECT 查询结果中显示的列名 = 原表中列名
FROM <表名>
WHERE <查询条件表达式>
SELECT 查询结果中显示的列名 = 原表中列名+ '.' +原表中列名
FROM 表名
(4)查询某几列为空的行
SELECT 列名 A
FROM 表名
WHERE 条件列名 B IS NULL
显示结果:B 列为空的 A列内容。
8SQL Server 编程入门 作者:徐佳
(5)显示结果中加入常量列
SELECT 列A = SName,列 B = SAddress,'常量列内容'AS 常量列名称
FROM Students
SELECT 姓名=SName,地址= SAddress,'72 中' AS 学校名称
FROM Students
(6)限制查询结果输出的行数
SELECT TOP 5 列名
FROM 表名
WHERE 条件
显示结果:符合条件的前五行。
(7)返回百分之多少行
SELECT TOP 20 PERCENT 列名
FROM 表名
WHERE 条件
显示结果:符合条件的行数的前 20%行。
(8)升序排列(默认)ASC
SELECT StudentID As 学员编号,(Score*0.9+5) As 综合成绩
FROM Score
WHERE (Score*0.9+5)>60
ORDER BY Score
(9)降序排列 DESC
SELECT Au_Lname +'.' +Au_fName AS EMP
From 表 A Union
SELECT fName +'.'+ LName AS EMP
9SQL Server 编程入门 作者:徐佳
From 表 B
ORDER BY EMP DESC
显示结果:混合查找两张表中的列,并按格式输出到新列中,并按新列排序输出。
(10)按多列排序
SELECT StudentID As 学员编号, Score As 成绩
FROM 表名
WHERE Score>60
ORDER BY Score,studentID
显示结果:先按 Score 排序,再按 studentID排序。
十、模糊查询
1、LIKE
查询时,字段中的内容并不一定与查询内容完全匹配,只要字段中含有这些内容。
SELECT StuName AS 姓名
FROM Stuinfo
WHERE stuname LIKE '徐%'
显示结果:姓为“徐”的人的名字。
2、IS NULL
把某一字段中内容为空的记录查询出来。
SELECT StuName AS 姓名,StuAddress AS 地址。
FROM Stuinfo
WHERE StuAddress IS NULL
显示结果:把地址栏为空的显示出来。
10SQL Server 编程入门 作者:徐佳
3、BETWEEN
把某一字段中内容在特定范围内的记录查询出来。
SELECT StuNo, Score
FROM Stumarks
WHERE Score BETWEEN 60 AND 80
显示结果:把分数 80>= Score >=60的显示出来。
4、IN
把某一字段中内容与所列出的查询内容列表匹配的记录查询出来。
SELECT StuName AS 学员姓名,StuAddress As 地址
FROM Stuinfo
WHERE StuAddress IN ('北京','广州','上海')
显示结果:把地址在('北京','广州','上海')里的显示出来。
十一、聚合函数
1、SUM(求和)
SELECT SUM(Score)
FROM Stumarks
WHERE 条件
显示结果:把符合条件的 Score 求和,然后显示结果。
2、AVG(求平均值)
SELECT AVG(SCore) AS 平均成绩
From Score
WHERE Score >=60
显示结果:把 Score >=60 的成绩求平均值,然后显示结果,显示的列名为“平均成绩”
11SQL Server 编程入门 作者:徐佳
3、MAX、MIN(求最大、最小值)
SELECT MAX (Score) AS 最高分, MIN (Score) AS 最低分
From Score
WHERE Score >=60
显示结果:把 Score>=60 中的最高分和最低分显示出来。
4、COUNT(计数)
SELECT COUNT (*) AS 及格人数
From Score
WHERE Score>=60
显示结果:把 Score 列中,>=60 的个数统计出来,然后显示统计数目。
十二、分组查询
1、单列分组查询
SELECT CourseID, AVG(Score) AS 课程平均成绩
FROM Score
GROUP BY CourseID
显示结果:按 CourseID 组求 Score 的平均值,然后将 CourseID 和平均值显示出来。
2、多列分组
SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 平均成绩
FROM Score
GROUP BY StudentID,CourseID
显示结果:显示所有学员的: “学员编号”(StudentID),“内部测试”(CourseID),“平均
成绩”(AVG(Score))。如果同一 CourseID 组中出现了相同的 StudentID,则显示出来的是这一
CourseID 组中相同的 StudentID 的平均成绩。
12SQL Server 编程入门 作者:徐佳
3、HAVING(追加条件)
SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 平均成绩
FROM Score
GROUP BY StudentID,CourseID
HAVING COUNT(Score)>1
显示结果:显示补考学员的: “学员编号”(StudentID),“内部测试”(CourseID),“平均
成绩”(AVG(Score))。如果同一 CourseID 组中的同一 StudentID 组中记录 Score 的次数,如果
次数>1,则显示出来“学员编号” (StudentID), “内部测试” (CourseID), “平均成绩” (AVG(Score))。
4、条件比较顺序
WHERE 子句从数据源中去掉不符合其搜索条件的数据。
GROUP BY子句搜集数据行到各个组中,统计函数为各个组计算统计值。
HAVING 子句去掉不符合其组搜索条件的各组数据行。
WHERE———>GROUP BY———>HAVING
十三、多表联接查询
1、分类
(1)内联接(INNER JOIN)
(2)外联接
A、左外联结 (LEFT JOIN)
B、右外联结 (RIGHT JOIN)
C、完整外联结 (FULL JOIN)
13SQL Server 编程入门 作者:徐佳
(3)交叉联接(CROSS JOIN)
2、多表内联结查询
(1)建立联接
SELECT S.SName,C.CourseID,C.Score
From Score AS C
INNER JOIN Students AS S
ON C.StudentID = S.SCode
显示结果:把 Score 表和 Students 表建立内联结,查询 C.StudentID = S.SCode 时,显示
S.SName,C.CourseID,C.Score 的内容。
(2)未建立联接
SELECT Students.SName, Score.CourseID, Score.Score
FROM Students,Score
WHERE Students.SCode = Score.StudentID
显示结果:同上。
(3)多表联接查询—三表联接
SELECT S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩
FROM Students AS S
INNER JOIN Score AS C
ON (S.SCode = C.StudentID)
INNER JOIN Course AS CS
ON (CS.CourseID = C.CourseID)
(4)区别
建立联接的查询速度比没有建立的快得多。
14SQL Server 编程入门 作者:徐佳
3、多表外联接查询
(1)左外联接
(LEFT JOIN 或 LEFT OUTER JOIN)
SELECT S.SName,C.CourseID,C.Score
From Students AS S
LEFT JOIN Score AS C
ON C.StudentID = S.SCode
显示结果:Students 为左表(left join)Score 为右表。左表中有的,右表中没有的显示空值
(NULL)。
(2)右外联接
(RIGHT JOIN或 RIGHT OUTER JOIN)
SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name
FROM titles
RIGHT OUTER JOIN Publishers
ON Titles.Pub_id = Publishers.Pub_id
显示结果:与左外连接相反。
(3)完整外联接
(FULL JOIN 或 FULL OUTER JOIN)
显示结果:左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的
选择列为空值;如果有匹配行,则显示结果包括左右表中的所有列值。
4、多表交叉联接查询
(CROSS JOIN)
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行一一组合,相当于两
个表“相乘”。
15SQL Server 编程入门 作者:徐佳
十四、数据库用户
1、创建登录帐户
(1)添加 Windows 登录帐户
EXEC sp_grantlogin 'jbtraining\S26301' (域名\用户名)
(2)添加 SQL 登录帐户
EXEC sp_addlogin 'zhangsan', '1234'
EXEC 表示调用存储过程,存储过程类似 C 语言的函数。
内置的系统管理员 帐户sa,密码默认为空,建议修改密码
2、创建数据库用户
USE 库名
GO
EXEC sp_grantdbaccess '登录帐户名','数据库用户名'
其中,“数据库用户“为可选参数,默认为登录帐户,即数据库用户默认和登录帐户同名。
3、给用户分配权限
GRANT 权限 [ON 表名 ] TO 数据库用户
权限:select、insert、update、delete、create table ……
4、系统内置的数据库用户
(1)dbo 用户
A、表示数据库的所有者(DB Owner)
B、无法删除 dbo 用户,此用户始终出现在每个数据库中
16SQL Server 编程入门 作者:徐佳
(2)guest 用户
A、适用于没有数据库用户的登录帐号访问
B、每个数据库可有也可删除
十五、T—SQL 编程
1、变量
(1)局部变量
A、局部变量必须以标记@作为前缀 ,如@age。
B、局部变量的使用也是先声明,再赋值。
C、声明局部变量
DECLARE @变量名 数据类型
D、赋值
SET @变量名 = 值
SELECT @变量名 = 值
(2)全局变量
A、全局变量必须以标记@ @作为前缀,如@@version
B、全局变量由系统定义和维护,我们只能读取,不能修改全局变量的值
C、全局变量的类型与含义







17SQL Server 编程入门 作者:徐佳
变 量 含 义
@@ERROR 最后一个 T-SQL 错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个 SQL 语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SQL Server的版本信息

2、输出语句
(1)print 局部变量或字符串
结果在消息窗口以文本方式显示。
(2)SELECT 局部变量 AS 自定义列名
结果在网格窗口以表格方式显示。
3、逻辑控制语句
(1)IF-ELSE 条件语句
IF(条件)
BEGIN
语句块……
END
ELSE
……
18SQL Server 编程入门 作者:徐佳
(2)WHILE 循环语句
WHILE(条件)
BEGIN
语句块……
(BREAK)-- 跳出循环,可选语句。
END
(3)CASE—END 多分支语句
CASE
WHEN 条件 1 THEN 结果 1
WHEN 条件 2 THEN 结果 2
……
(ELSE) -- 其他结果,可选语句。
END
(4)GO 批处理语句
语句 1
语句 2
……
GO -- 批处理语句的标志
A、 批处理是包含一个或多个 SQL 语句的组,从应用程序一次性地发送到 SQL Server
执行。SQL Server 将批处理语句编译成一个可执行单元,此单元称为执行计划。执行
计划中的语句每次执行一条。
B、 GO 是批处理的标志,表示 SQL Server 将这些 T-SQL 语句编译为一个执行单元,提高
执行效率。一般是将一些逻辑相关的业务操作语句,放置在同一批中,这完全由业务需
求和代码编写者决定。
C、 SQLServer 规定:如果是建库、建表语句、以及我们后面学习的存储过程和视图等,
则必须在语句末尾添加 GO 批处理标志。
19SQL Server 编程入门 作者:徐佳
十六、高级查询
1、简单的子查询
SELECT * FROM stuInfo
WHERE stuAge>( SELECT stuAge FROM stuInfo where stuName='姓名')
(1)子查询的一般用法
A、SELECT … FROM 表1 WHERE 字段 1 >(子查询)。
B、外面的查询称为父查询,括号中嵌入的查询称为子查询。
C、UPDATE、INSERT、DELETE 一起使用,语法类似于 SELECT 语句。
D、将子查询和比较运算符联合使用,必须保证子查询返回的值不能多于一个。
(2)子查询替换表连接
A、一般来说,表连接都可以用子查询替换,但有的子查询却不能用表连接替换。
B、子查询比较灵活、方便,常作为增删改查的筛选条件,适合于操纵一个表的数据。
C、表连接更适合于查看多表的数据。
2、IN(NOT IN)子查询
SELECT stuName FROM stuInfo
WHERE stuNo IN (SELECT stuNo FROM stuMarks WHERE writtenExam=60)
IN(在子查询范围内),NOT IN(不在子查询范围内)
IN(NOT IN)后面的子查询可以返回多条记录,常用 IN替换等于(=)的比较子查询。
3、EXISTS子查询
IF EXISTS (子查询)
语句……
A、如果子查询的结果非空,即记录条数 1 条以上,则 EXISTS (子查询)将返回真(true),
20SQL Server 编程入门 作者:徐佳
否则返回假(false) 。
B、EXISTS 也可以作为 WHERE 语句的子查询,但一般都能用 IN子查询替换。
C、EXISTS 和 IN一样,允许添加 NOT 取反,表示不存在。
十七、事务
1、使用 T-SQL语句来管理事务
开始事务:BEGIN TRANSACTION
提交事务:COMMIT TRANSACTION
回滚(撤销)事务:ROLLBACK TRANSACTION
2、判断某条语句执行是否出错
使用全局变量@@ERROR。@@ERROR 只能判断当前一条 T-SQL 语句执行是否有错,
为了判断事务中所有 T-SQL 语句是否有错,我们需要对错误进行累计;
如: SET @errorSum=@errorSum+@@error
3、事务必须具备 ACID四个属性
原子性(Atomicity):事务是一个完整的操作。事务的各步操作是不可分的(原子的);
要么都执行,要么都不执行
一致性(Consistency):当事务完成时,数据必须处于一致状态
隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是
独立的,它不应以任何方式依赖于或影响其他事务
永久性(Durability) :事务完成后,它对数据库的修改被永久保持,事务日志能够保持
事务的永久性
4、事务的分类
显示事务:用 BEGIN TRANSACTION明确指定事务的开始,这是最常用的事务类型
隐性事务:通过设置 SET IMPLICIT_TRANSACTIONS ON 语句,将隐性事务模式设置
21SQL Server 编程入门 作者:徐佳
为打开,下一个语句自动启动一个新事务。当该事务完成时,再下一个 T-SQL 语句又将启
动一个新事务
自动提交事务:这是 SQL Server 的默认模式,它将每条单独的 T-SQL 语句视为一个
事务,如果成功执行,则自动提交;如果错误,则自动回滚
5、事务例句
BEGIN TRANSACTION -- 开始事务
DECLARE @errorSum INT --定义变量接收错误语句数
SET @errorSum = 0 --初始化变量,无错误
SQL 语句 1
SET @errorSum=@errorSum+@@error
SQL 语句 2
SET @errorSum=@errorSum+@@error
……
IF @errorSum <> 0 --产生错误,回滚事务
BEGIN
ROLLBACK TRANSACTION
END
ELSE --如果无错误,提交事务
BEGIN
COMMIT TRANSACTION
END
GO
十八、索引
1、索引类型
唯一索引:唯一索引不允许两行具有相同的索引值
主键索引:为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。
22SQL Server 编程入门 作者:徐佳
主键索引要求主键中的每个值是唯一的,并且不能为空
聚集索引(Clustered):表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只
能有一个
非聚集索引(Non-clustered):非聚集索引指定表的逻辑顺序。数据存储在一个位置,索
引存储在另一个位置,索引中包含指向数据存储位置的指针。可以有多个,小于 249 个
2、使用 T-SQL语句创建索引
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_name (column_name…)
[WITH FILLFACTOR=x]
UNIQUE 表示唯一索引,可选。
CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引,可选。
FILLFACTOR 表示填充因子,指定一个 0 到 100 之间的值,该值指示索引页填满的空间所
占的百分比。
3、索引的优缺点
(1)优点
A、加快访问速度
B、加强行的唯一性
(2)缺点
A、带索引的表在数据库中需要更多的存储空间
B、操纵数据的命令需要更长的处理时间,因为它们需要对索引进行更新
4、创建索引的指导原则
(1)请按照下列标准选择建立索引的列
A、该列用于频繁搜索
23SQL Server 编程入门 作者:徐佳
B、该列用于对数据进行排序
(2)请不要使用下面的列创建索引
A、列中仅包含几个不同的值。
B、表中仅包含几行。为小型表创建索引可能不太划算,因为 SQL Server 在索引中搜索
数据所花的时间比在表中逐行搜索所花的时间更长
5、索引例句
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_writtenExam')
DROP INDEX stuMarks.IX_writtenExam
/*--笔试列创建非聚集索引:填充因子为 30%--*/
CREATE NONCLUSTERED INDEX IX_writtenExam
ON stuMarks(writtenExam)
WITH FILLFACTOR= 30
GO
/*-----指定按索引 IX_writtenExam 查询----*/
SELECT * FROM stuMarks (INDEX=IX_writtenExam)
WHERE writtenExam BETWEEN 60 AND 90
十九、视图
1、是么是视图
A、视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据
是建立在对表的查询基础上
B、视图中并不存放数据,而是存放在视图所引用的原始表(基表)中
C、同一张原始表,根据不同用户的不同需求,可以创建不同的视图
24SQL Server 编程入门 作者:徐佳
2、视图的用途
A、筛选表中的行
B、防止未经许可的用户访问敏感数据
C、降低数据库的复杂程度
D、将多个物理数据库抽象为一个逻辑数据库
3、使用 T-SQL语句创建视图
CREATE VIEW view_name
AS
<select 语句……>
GO
4、视图例句
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
25SQL Server 编程入门 作者:徐佳
二十、存储过程
1、什么是存储过程(procedure)
一组预编译的 SQL 语句,它可以包含数据操纵语句、变量、逻辑控制语句等。
2、存储过程的优点
A、执行速度更快
B、允许模块化程序设计
C、提高系统安全性
D、减少网络流通量
3、存储过程的分类
(1)系统存储过程
由系统定义,存放在 master 数据库中,类似 C 语言中的系统函数,系统存储过程的名
称都以“sp_”开头或”xp_”开头。
(2)用户自定义存储过程
由用户在自己的数据库中创建的存储过程,类似 C 语言中的用户自定义函数。
4、常用的系统存储过程
扩展存储过程:xp_cmdshell
可以执行 DOS 命令下的一些的操作,以文本行方式返回任何输出
调用语法:
EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
例句:
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
EXEC xp_cmdshell 'dir D:\bank\' --查看文件
26SQL Server 编程入门 作者:徐佳
系统存储过程 说明
sp_databases 列出服务器上的所有数据库。
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程。
sp_password 添加或修改登录帐户的密码。
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过
程、触发器或视图的实际文本。
5、使用 T-SQL语句创建和调用存储过程
创建:
CREATE PROC[EDURE] 存储过程名
@参数 1 数据类型 = 默认值,
…… ,
@参数 n 数据类型 = 默认值
AS
SQL 语句
GO

调用:
EXEC 过程名 [参数]
注意:
A、和 C 语言的函数一样,参数可选
B、参数分为输入参数、输出参数
27SQL Server 编程入门 作者:徐佳
C、输入参数允许有默认值
D、存储过程的最大大小为 128 MB
(1)不带参数的存储过程
创建:
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
(2)带输入参数的存储过程
创建:
CREATE PROCEDURE proc_stu
@writtenPass int=60, --输入参数可带默认值
28SQL Server 编程入门 作者:徐佳
@labPass int=60
AS
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 --都采用默认值
EXEC proc_stu 64 --第二个参数采用默认值
EXEC proc_stu @labPass=55 --第一个参数采用默认值
EXEC proc_stu 60,55 --都不采用默认值
(3)带输出参数的
创建:
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --输出(返回)参数
@writtenPass int=60, --推荐将默认参数放后面
@labPass int=60
AS
……
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
29SQL Server 编程入门 作者:徐佳
GO
调用:
/*---调用存储过程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64
print '--------------------------------------------------'
IF @sum>=3
print '未通过人数:'+convert(varchar(5),@sum)+ '人,
超过 60%,及格分数线还应下调'
ELSE
print '未通过人数:'+convert(varchar(5),@sum)+ '人,
已控制在 60%以下,及格分数线适中'
GO
6、处理存储过程中的错误
(1)PRINT 语句显示错误信息
可以使用 PRINT 语句显示错误信息,但这些信息是临时的,只能显示给用户
(2)RAISERROR 显示用户定义的错误信息
可指定严重级别,设置系统变量@@ERROR,记录所发生的错误等。
(3)RAISERROR 语句的用法
RAISERROR (msg_id | msg_str,severity,state WITH option[,...n]])
msg_id:在 sysmessages系统表中指定用户定义错误信息。
msg_str:用户定义的特定信息,最长 255 个字符。
severity:定义严重性级别。用户可使用的级别为 0–18 级。
state:表示错误的状态,1至 127 之间的值。
option:指示是否将错误记录到服务器错误日志中。
30SQL Server 编程入门 作者:徐佳
(4)例句
CREATE PROCEDURE proc_stu
@notpassSum int 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
…..其他语句同上例,略
GO

/*---调用存储过程,测试 RAISERROR 语句----*/
DECLARE @sum int, @t int
EXEC proc_stu @sum OUTPUT ,604
SET @t=@@ERROR
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
31SQL Server 编程入门 作者:徐佳
二十一、触发器
1、创建触发器的语法
CREATE TRIGGER trigger_name ON table_name
[WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL 语句……
GO
2、例句
(1)Insert触发器
CREATE TRIGGER trig_transInfo ON transInfo
FOR INSERT
AS
DECLARE @type char(4),@outMoney MONEY
DECLARE @myCardID char(10),@balance MONEY
SELECT @type=transType,@outMoney=transMoney,
@myCardID=cardID FROM inserted
IF (@type='支取')
UPDATE bank SET currentMoney=currentMoney-@outMoney
WHERE cardID=@myCardID
ELSE
UPDATE bank SET currentMoney=currentMoney+@outMoney
WHERE cardID=@myCardID
GO

(2)Delete触发器
CREATE TRIGGER trig_delete_transInfo ON transInfo
FOR DELETE
AS
print '开始备份数据,请稍后......'
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='backupTable')
SELECT * INTO backupTable FROM deleted
ELSE
INSERT INTO backupTable SELECT * FROM deleted
print '备份数据成功,备份表中的数据为:'
SELECT * FROM backupTable
GO
(3)Update 触发器
CREATE TRIGGER trig_update_bank ON bank
FOR UPDATE
AS
DECLARE @beforeMoney MONEY,@afterMoney MONEY
SELECT @beforeMoney=currentMoney FROM deleted
SELECT @afterMoney=currentMoney FROM inserted
IF ABS(@afterMoney-@beforeMoney)>20000
BEGIN
print '交易金额:'+convert(varchar(8),
ABS(@afterMoney-@beforeMoney))
RAISERROR ('每笔交易不能超过 2万元,交易失败',16,1)
ROLLBACK TRANSACTION
END
GO

33SQL Server 编程入门 作者:徐佳
CREATE TRIGGER trig_update_transInfo ON transInfo
FOR UPDATE
AS
IF UPDATE(transDate) --判断是否被更改
BEGIN
print '交易失败.....'
RAISERROR (‘安全警告:交易日期不能修改,由系统自动产生',16,1)
ROLLBACK TRANSACTION
END
GO
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值