SQL Server 基础系列篇_sql sever

喜欢记得点个赞哟，我是王睿，很高兴认识大家！

1、分离

use master
go

exec sp_detach_db 'EMIS301'
1
2
3
4

2、增、删、改代码

增：

use master
go
alter database EMIS301
add file
( name='emis301_data',
 filename='D:\Database301\emis301_data.ndf',
 size=5MB,
 maxsize=100MB,
 filegrowth=5MB
)
go

———————————————————————————————————————————
改：

alter database EMIS301
modify file
(
 name='emis301_data',
 filename='.ndf',
 size = 8Mb,
 maxsize=150Mb,
 filegrowth=
)
go


———————————————————————————————————————————
删：
alter database EMIS301
remove file emis301_data
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

3、创建数据库

4、2014企业版安装包

SQL Sever 2014企业版安装包

5、更改数据库名

use master
go
exec sp_renamedb 'EMIS308','EMIS301'
go
1
2
3
4

6、建表语句，设置种子数量与增量以及设置主键 代码

USE EMIS301
GO

CREATE TABLE table_student2_课程注册
(
	注册号 bigint IDENTITY (10000000,1) NOT NULL CONSTRAINT[PK_t_course_reg] PRIMARY KEY,
	学号   char(12)  NOT NULL,
	学程号 char(4)   NOT NULL,
	教师编号 char(12) NOT NULL,
	学年 char(4) NULL,
	学期 tinyint NULL,
	收费否 bit NULL,
	注册 bit NULL,
	成绩 decimal(5,2) NULL,
	学分 tinyint NULL

)
GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

可以看到第一列很长，那么代表什么呢？我们来分析一下这句代码：

注册号 bigint IDENTITY (10000000,1) NOT NULL CONSTRAINT[PK_t_course_reg] PRIMARY KEY,

1. 注册号表示 — 列名
2. IDENTITY (10000000,1)
   第一个参数表示：种子数目，第二个参数表示：种子增量
3. .NOT NULL — 不允许此项值为空
4. CONSTRAINT[PK_t_course_reg] PRIMARY KEY, 设置这一列为主键，然后它的主键名叫：PK_t_course_reg

7、导入数据与导出数据到表的方法

一丶 表的设计：

首先我们要准备一份 Microsoft Excel.xls 表格的数据文件，里面记录了所有学生的信息，有 学号、姓名、性别、出生日期、入学时间、班级代码、学籍状态、微信号、手机号码
9项内容，那么我们现在就要先设计一张与这些列名对应的数据库的表，我已经设计好了这张表，如图：

二丶 导入数据

那么这是一张新的表，没有任何数据，接下来我们的任务就是要从 Microsoft Excel.xls 表格的数据文件导入里面所有的学生信息，那么怎么导入呢？往下看！

右键点击 EMIS301 这个数据库，找到导入数据的选项，点击，之后会弹出：

点击下一步，注意数据源这里的选项要选择与你外面的数据文件对应的同一款软件，我们用的是Microsoft Excel软件，所以我们也要选择它：

注意：
目标选择 SQL SeverN…11.0这个选项
服务器名称是你本机的服务器
数据库选择环境中你所想存放的数据库文件里


左边选择第一个
右边选择你想配对的表



注意，这个环节最容易出错！
如果这里失败，说明你的数据库设计的表有问题，回去再重新查看一下，有几种常见原因：

• 列数设少或多了
• 数据范围不够或者超出

成功的话就会如下所示：

之后我们打开数据库的表查看数据，就会看到所有的数据都成功导入了进来！

OK，有没有感觉很神奇，很厉害，哈哈！计算机的世界还真是强大而又奇妙！

三丶 导出数据

数据源： Microsoft Excel 这一项
Excel 文件路径： 选择浏览，选择我们新建的Microsoft Excel表格文件

没有我们就新建

服务器名称：查看本机服务器
身份验证：Windows 身份验证
数据库：选择对应的数据库


左右边对应


下面的按着点击 Next（下一步就可以了）！ 这样就完成了将表中所有的数据全部拷贝到了表格文件中，就是这么神奇好玩哈哈！

8、表的操作

一丶 新增一列

USE EMIS301
GO

alter table 课程
add 学分 tinyint NOT NULL
go
1
2
3
4
5
6

二丶 删除一列

USE EMIS301
GO

alter table 班级
alter column 备注 varchar not null
go

alter table 系部
drop column 系部介绍
go
1
2
3
4
5
6
7
8
9
10

三丶 修改一列

修改列的类型

USE EMIS301
GO

alter table 班级
alter column 备注 varchar not null
go
1
2
3
4
5
6

四丶 重命名列名

USE EMIS301
GO

exec sp_rename '课程.先修课','先修课呀'
go
1
2
3
4
5

五丶 导入数据注意事项《补》
表在设置时，是不要都不为空，因为你导入一堆数据进入表中时，如果有一个值为空，那么就会与你设定的表中的列不为空发生冲突，这时候数据就无法成功导入进表里

9、主键、外键、唯一性约束、检查约束

一丶 主键

USE EMIS301
GO
ALTER TABLE 系部
ADD CONSTRAINT PK_专业 PRIMARY KEY(系部名称)
GO
1
2
3
4
5

二丶 外键

USE EMIS301
GO
ALTER TABLE 系部
ADD CONSTRAINT FK_系部_专业 FOREIGN KEY(系部代码) REFERENCES 学生(系部代码)
GO
1
2
3
4
5

三丶 唯一性约束

USE EMIS301
GO
ALTER TABLE 专业
ADD CONSTRAINT IX_专业 UNIQUE(专业代码)
GO
1
2
3
4
5

特性： 可以允许控制！

四丶 检查约束

USE EMIS301
GO
ALTER TABLE 成绩
ADD CONSTRAINT CK_成绩 CHECK(成绩>= 0 AND 成绩<= 100)
GO
1
2
3
4
5

10、设置外键+组合键（唯一性约束）、修改取值范围、设置某列的默认值

一丶 外键

/*	添加外键	*/
USE EMIS301
GO
ALTER TABLE 课程收费表
ADD CONSTRAINT FK_课程收费_学号 FOREIGN KEY(student_id) REFERENCES 学生(学号)
GO

解释：
ADD CONSTRAINT 外键名称 FOREIGN KEY(设置外键的列) REFERENCES 另一个表名(列名)
1
2
3
4
5
6
7
8
9

二丶 设置组合键（唯一性约束）

USE EMIS301
GO
ALTER TABLE 课程收费表
ADD CONSTRAINT IX_课程收费_学号科目学校 UNIQUE(student_id,course_code,school_term)
GO

解释：
ADD CONSTRAINT 组合键名称 UNIQUE(列名，列名，列名)
1
2
3
4
5
6
7
8

三丶 修改取值范围

USE EMIS301
GO
ALTER TABLE 课程收费表 add check(school_term between 1 and 10)

解释：
ALTER TABLE 表名 add check(列名 between 起始 and 截止)
GO
1
2
3
4
5
6
7

四丶 设置某列的默认值

/*	修改默认值	*/
USE EMIS301
GO
ALTER TABLE 课程收费表 
ADD charge tinyint NULL DEFAULT 0
GO

解释：
ADD 列名 数据类型 NULL 默认值 0
1
2
3
4
5
6
7
8
9

11、查询前10%的数据、查询并参与计算显示出数据、查询前10行数据、多重升序降序查询、去掉重复值、替换列名显示

1、查询学生表中的所有信息。

USE EMIS1
GO
SELECT * FROM 学生 
GO

2、查询学生表中所有学生的学号（别名为Number）、姓名（别名为Name）、性别(别名为Sex)和手机号码(别名为Telephone)，按别名输出信息。

SELECT 学号,姓名,性别,手机号码 FROM 学生
GO

3、查询学生表中所有学生的学号、姓名和年龄信息（年龄=现在年份-出生日期的年份）。

SELECT 学号,姓名,year(GETDATE())-year(出生日期) AS 年龄信息 FROM 学生
GO

4、查询学生表中班级代码列的信息，并去掉重复值。

SELECT DISTINCT 班级代码 FROM 学生
GO

5、查询课程注册表中学号、课程号和学年的前20行信息。

SELECT TOP 20 学号,课程号,学年 FROM 课程注册
GO

6、查询学生表的前10%行记录。


SELECT TOP 10 PERCENT * FROM 学生
GO

7、在课程注册表中按课程号的降序、教师编号的升序查询所有信息。

SELECT * FROM 课程注册 order by 课程号 DESC,教师编号 ASC
GO


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

12、附加（如何导入外部文件数据库）

第一步：找到我们的数据库资源，如图：

第二步：打开SQL Sever，右键点击附加

第三步：

第四步：成功！

13、条件查询、比较运算符查询、模糊查询、枚举查询、范围查询、空值查询、多重条件查询

USE EMIS1
GO

/* 条件查询 */
/* 查询课程注册表中 学年为2016的所有信息 */
SELECT * FROM 课程注册 where 学年='2016'
GO

/* 比较运算符查询 */
/* 查询课程注册表中成绩不低于80分的所有信息 */
SELECT * FROM 课程注册 where 成绩>=80
GO

/* 模糊查询 */
/* 查询课程注册表中学号前9位是“20150211”的所有信息 */
/* like    %   任意字符      _任意一个字 */
SELECT * FROM 课程注册 where 学号 like '20150211%'
GO
/* 拓展：表示查询学生表中名字含有张的学生的学号，姓名信息 */
SELECT 学号,姓名 FROM  学生 where 姓名 like '%张%'
GO

/* 枚举查询： */
/* 查询课程注册表中课程号为“0001” 和 “0002”的所有信息 */
SELECT * FROM 课程注册 where 课程号='0001' or 课程号='0002'
GO
/* 拓展：第二种表达方式（★推荐） */
SELECT * FROM 课程注册 where 课程号 in('0001','0002')
GO 

/* 范围查询 */
/* 查询课程注册表中成绩介于80到90之间的所有信息 */
SELECT * FROM 课程注册 where 成绩>=80 and 成绩<=90
GO
/*拓展：第二种表达方式（★推荐）*/
SELECT * FROM 课程注册 where 成绩 between 80 and 90
GO

/* 空值查询 */
/* 课程注册表中成绩是空的所有记录 */
SELECT * FROM 课程注册 where 成绩 is null
GO

/* 多重条件查询 */
/* 查询课程注册表中考试课程号为“0001”，且成绩不及格的所有信息 */
SELECT * FROM 课程注册 where 课程号='0001' and 成绩<60
GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

14、条件查询、比较运算符查询、模糊查询、枚举查询、范围查询、空值查询、多重条件查询

USE EMIS1
GO

/* 条件查询 */
/* 1、查询学生表中班级代码为“15wljs301”同班同学的学号、姓名和性别信息。 */
SELECT 学号,姓名,性别 FROM 学生 where 班级代码='15wljs301'
GO

/* 比较运算符查询 */ 
/* 2、查询课程注册表中考试不及格的所有学生的学号、课程号和教师编号。 */
SELECT 学号,课程号,教师编号 FROM 课程注册 where 成绩<60
GO

/* 模糊查询 */
/* 3、查询学生表中所有姓李的男学生信息。 */
SELECT * FROM 学生 where 姓名 like '李%'
GO

/* 范围查询 */
/* 4、查询学生表中1999年1月1日至2000年3月31日之间出生的学生学号、姓名和微信号。 */
SELECT 学号,姓名,微信号 FROM 学生 where 出生日期 between '1999-01-01' and '2000-03-31'
GO

/* 枚举查询 */
/* 5、查询课程表中学分为2分或3分的课程号和课程名称。 */
SELECT 课程号,课程名称 FROM 课程 where 学分 in('2','3');
GO 

/* 多重条件查询 */
/* 6、查询学生表中年龄为21岁的所有女学生的学号、姓名和手机号码。 */
SELECT   学号, 姓名,手机号码 FROM 学生 where  year(GETDATE())-year(出生日期)=21 and 性别='女'
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

15、年龄查询、条件查询、多表查询、姓氏查询

题目：

课程实训

1、查询教师表中年龄超过45岁的教师编号、姓名、学历和职称。
2、查询课程注册表中成绩介于80分与90分之间的学生学号、课程号和教师编号。
3、查询成绩大于或等于90分的学生学号、姓名、性别和课程号。
4、查询考试不及格的学生学号、姓名、课程名称、任教教师姓名和职称。
5、查询‘王’姓教师的教师编号、姓名、学历和职称。

代码：

USE EMIS1
GO

/* 1、	查询教师表中年龄超过45岁的教师编号、姓名、学历和职称。	*/
SELECT 教师编号,姓名,职称 FROM dbo.教师 where YEAR(GETDATE())-YEAR(出生日期)>45
GO

/* 2、	查询课程注册表中成绩介于80分与90分之间的学生学号、课程号和教师编号。	*/
SELECT 学号,课程号,教师编号 FROM 课程注册 where 成绩 between 80 and 90
GO

/* 3、	查询成绩大于或等于90分的学生学号、姓名、性别和课程号。	*/
SELECT 学生.学号,学生.姓名,学生.性别 课程号
FROM 课程注册	inner join 学生 on 学生.学号=课程注册.学号
				where 成绩 between 80 and 90
GO

/* 4、	查询考试不及格的学生学号、姓名、课程名称、任教教师姓名和职称。	*/
SELECT 学生.学号,学生.姓名,课程名称,教师.姓名 as 教师姓名,职称,成绩
FROM 课程注册	inner join 学生 on 学生.学号=课程注册.学号
				inner join 课程 on 课程.课程号=课程注册.课程号
				inner join 教师 on 教师.教师编号=课程注册.教师编号
				where 成绩<60
GO

/* 5、	查询‘王’姓教师的教师编号、姓名、学历和职称。	*/
SELECT 教师编号,姓名,学历,职称 FROM 教师 where 姓名 like '王%'
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

16、多表查询、个数计算、多重约束条件、两表间的交集、多表之间的关系分析

一丶 题目&答案
【1】查询15级网络技术301班所有姓陈的男同学的信息

USE EMIS5
GO

select 班级代码 from 班级 where 班级名称='15级网络技术301班' //通过班级名称找到班级代码

select * from 学生 
where 班级代码 in(select 班级代码 from 班级 where 班级代码='15级网络技术301班')
	and 姓名 like '陈%' and 性别='男'
go
1
2
3
4
5
6
7
8
9

【2】查询每个班的男女生各有多少人

select 班级代码,性别,count(*) as 总人数 from 学生 group by 性别,班级代码
1

【3】查询平均分大于80分的学生的学号、姓名和班级名称信息

select 学号 from 课程注册 group by 学号 having avg(成绩)>80	//平均分大于80分学生学号

select 学号,姓名,班级名称 from 学生 inner join 班级 on 学生.班级代码=班级.班级代码
	and	学号 in(select 学号 from 课程注册 group by 学号 having avg(成绩>80))
go
1
2
3
4
5

【4】查询大学英语成绩前三名的同学的学号，姓名以及所在系部名称

//先找到大学英语成绩排名前三的学生学号
select top 3 学号 from  课程注册 inner join 课程 
		on 课程注册.课程号=课程.课程号 and 课程名称='大学英语'	
select 学号,姓名,系部名称
from 学生 inner join 班级 on 学生.班级代码=班级.班级代码
		  inner join 专业 on 专业.专业代码=班级.专业代码
		  inner join 系部 on 系部.系部代码=专业.系部代码
where 学号 in(select top 3 学号 from 课程注册 inner join 课程
			on 课程注册.课程号=课程.课程号 and 课程名称='大学英语' order by 成绩 DESC)
go
1
2
3
4
5
6
7
8
9
10

思路

1、先在课程注册表中通过课程号关联课程表中的课程号，找到匹配的同一行，然后就可以知道它的课程名称是否为大学英语。
2. 然后再通过成绩找到前三名同学，约束条件是大学英语，而且要倒序从高到低排
3. 找到了这三名学生后，就可以在课程注册表中确定他们三位的学号，再进入到学生表中拿到他们的班级代码。
4. ，通过学生表的班级代码与班级表的班级代码，找到他们的专业代码，拿到他们的系部代码
5. 通过专业表的系部代码与系部的系部代码，就可以找到他们的系部名称。

【5】查询只有一门课程不及格的同学的最高分、最低分及平均分信息

//查询只有一门课程不及格的学生学号
select 学号 from 课程注册 where 成绩<=60 group by 学号 having count(成绩)=1

select 学号,max(成绩) as 最高分,min(成绩) as 最低分,avg(成绩) from 课程注册
	where 学号 in(select 学号 from 课程注册 where 成绩<60 group by 学号 
	having count(成绩)=1)
	group by 学号
go
1
2
3
4
5
6
7
8

二丶 所有表的结构

班级：

管理员：

教师：

教师任课：

教学计划：

课程：

课程类型：

课程收费：

课程注册：

系部：

学籍状态：

学生：

专业：

17、视图

USE EMIS1
GO
/*	1、创建一个名为“V_学生”的视图，在求查询学生表中与王迪同学同班的学生姓名、性别、出生日期和手机号码	*/

/*select 班级代码 from 学生 where 姓名='王迪';*/

create view V_学生

as

select 姓名,性别,出生日期,手机号码 from 学生 where 班级代码=(select 班级代码 from 学生 where 姓名='王迪')

/*	2、创建一个名为“V_网络专业”的视图，要求查询“计算机网络技术”专业的班级信息	*/
USE EMIS1
GO

create view V_网络专业

as
/*select 专业代码 from 专业 where 专业名称='计算机网络技术'*/

select 班级代码 from 班级 where 专业代码=(select 专业代码 from 专业 where 专业名称='计算机网络技术')


/*	3、将视图“V_网络专业”重命名为“V_网络技术专业”	*/

exec sp_rename 'V_网络专业','V_网络技术专业'
go

/*	4、删除名为“V_学生”和“V_网络技术专业”的视图	*/

drop view V_网络技术专业
drop view V_学生
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

18、索引信息

/*	在专业表中，对字段专业名称创建一个非聚集索引，索引名称为IX_专业名称	*/
USE EMIS301
GO
CREATE NONCLUSTERED INDEX IX_专业名称 ON 专业(专业名称)
GO

/*	通过系统存储过程查看数据表T_MAJOR的索引信息		*/
USE EMIS301
GO
EXEC SP_HELPINDEX '专业'
GO

/*	使用T-SQL语言来查看 ‘IX_专业代码’ 索引的统计信息	*/
USE EMIS301
GO
DBCC SHOW_STATISTICS('专业',IX_专业名称)
GO

/*	重命名索引	*/
USE EMIS301
GO
exec sp_rename '专业.IX_专业名称','IX_专业名称A'
GO

/*	删除索引	*/
USE EMIS5
GO
DROP INDEX 专业.IX_专业名称A
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

19、存储过程（上）

USE EMIS1
GO

/*1、编写一个输出“Hello, My Procedure!”字符串的存储过程*/
/*	解法一：	*/
create procedure P_zifuchuan01
as
print 'Hello, My Procedure!'
GO

exec P_zifuchuan01
GO

/*	解法二：	*/
create procedure P_zifuchuan02
as
select 'Hello, My Procedure!'
GO

exec P_zifuchuan02
GO



/*2、编写一个存储过程p_stu17wljs301，该存储过程查询班级代码为“17wljs301”的所有学生的学号、姓名、性别和出生日期。*/
create procedure P_stu17wljs301
as
SELECT 学号,姓名,性别,出生日期 from 学生 
where 班级代码='17wljs301'
GO

exec P_stu17wljs301
GO

/*3、编写一个存储过程p_stubjdm，该存储过程根据输入参数@bjdm（班级代码）显示相应班级的学生学号、姓名、性别和出生日期。*/

create procedure P_stubjdm @bjdm char(9)='17wljs301'
as
select 学号,姓名,性别,出生日期 from 学生
where 班级代码=@bjdm
GO

/*	方式一：	*/
declare @bjdm char(9)
set @bjdm='15wljs301'
EXEC P_stubjdm @bjdm
GO
/*	方式二：	*/
declare @bjdm char 
exec @bjdm=[dbo].P_stubjdm '15wljs301'
select 'Returen Value' = @bjdm
GO

/*1、编写一个存储过程p_stuage，该存储过程根据输入参数@age（年龄）显示相应年龄的学生学号、姓名、性别和年龄，其参数默认值为21。*/

create procedure P_stuage @age int = 21
as
select 学号,姓名,性别,出生日期 as 年龄 from 学生
where YEAR(GETDATE())-YEAR(出生日期)=@age
GO

/*	方式一：	*/
declare @age int
set @age=20
EXEC P_stuage @age
GO
/*	方式二：	*/
declare @age int 
exec @age=[dbo].P_stuage 20
select 'Returen Value' = @age
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

20、存储过程（下）

USE EMIS1
GO

/*	1、创建一个名为“P_学生总数” 的存储过程。要求查询学生表中与王迪同学同伴的学生总人数，并利用输出参数输出查询的总人数	*/
create procedure P_学生总数 @math int output
as
select @math=count(学号) from 学生 
where 班级代码 in(select 班级代码 from 学生 where 姓名='王迪')
GO



/*	2、执行存储过程“P_学生总数”。要求输出查询的总人数	*/
declare @x int 
exec P_学生总数 @x output
print convert(varchar(5),@x)
GO

/*	3、创建一个名为“P_学生总数1”的存储过程。要求查询学生表中与某同学同班的学生总人数，并利用输入参数输入学生姓名，输出参数输出查询的总人数	*/

create procedure P_学生总数1 @tname varchar(20)='王迪',@math int output
as 
select @math=COUNT(学号) from 学生 
where 班级代码 in(select 班级代码 from 学生 where 姓名=@tname)
go

USE EMIS1
GO

declare @kt int,@tname varchar(20)
set @tname = '王迪'			--	select @tname='王迪'
exec P_学生总数1 @tname,@kt output	
--exec P_学生总数1 '王迪',@kt output
print '某班学生总人数为：'+convert(varchar(5),@kt)+'人'
GO

/*	4、执行存储过程“P_学生总数1”。要求输入学生姓名“贾铭伟”，并输出查询与贾铭伟同班的总人数	*/
declare @kt int,@tname varchar(20)
set @tname = '贾铭伟'			--	select @tname='王迪'
exec P_学生总数1 @tname,@kt output	
--exec P_学生总数1 '王迪',@kt output
print '某班学生总人数为：'+convert(varchar(5),@kt)+'人'
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

21、触发器（上）

题目：

一丶 利用 触发器判断，添加学生表的数据时，需要判断一下班级表中是否存在与临时表里的班级代码，若存在提示 “学生数据插入成功” ，若不存在提示 “该班级不存在，不能输入学生数据！”。

/*	利用 触发器判断，添加学生表的数据时，需要判断一下班级表中是否存在与临时表里的班级代码，若存在提示 “学生数据插入成功” ，若不存在提示 “该班级不存在，不能输入学生数据！	*/

//设置：触发器
USE EMIS
GO

create trigger ISNERT_STU on 学生
for insert
as
declare @classcode char(9)
select @classcode=班级.代码 from inserted,班级
where inserted.班级代码=班级.班级代码
if(@classcode<> '')
	print '学生数据插入成功'
else
	begin
	print '该班级不存在，不能输入学生数据！'
	rollback transaction
	end
go

//添加数据到临时表中
INSERT INTO 学生
VALUES('2016030120','李白','男','1999-05-12','2016-09-01','16wlw301','01','libaiya','19911313600')
GO

//删除数据
delete 学生 where 学号='2016030120'
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

22、触发器（中）

1、创建一个名称为DE_CLASS的DELETE触发器：当删除“班级”数据表中的班级信息时，检查“学生”数据表中是否存在该班级的学生，如果存在，提示用户“该班级还有学生，不允许删除该班级的信息”，并撤消操作。否则提示“已成功删除！”
创建完成后，删除“班级”数据表中一条班级信息，测试触发器DELETE_CLASS是否被触发。

USE EMIS1
GO

create trigger DELETE_SHAN on 班级
for delete
as
declare @number int
select @number=COUNT(*) from deleted,学生
where deleted.班级代码=学生.班级代码
if(@number=0)
	begin
		print'已成功删除'
	end
else
	begin
	print'该班级还有学生，不允许删除该班级的信息'
	rollback transaction
	end
go

delete 班级 where 班级代码='16asd230 '
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

23、触发器（下）

题目：
一丶创建一个触发器，要求在表“学生”中添加记录时提示“某某学生记录已添加成功！”。并在表“学生”中添加一条记录验证触发器的执行。
二丶创建一个触发器，要求在表“系部”中添加记录时，如果系主任非空则提示“记录已添加成功！”，否则提示“系主任为空，撤消操作！”。在表“系部”中添加一条记录验证触发器的执行。
三丶创建一个触发器，禁止在表“学生”中添加数据。并在表“学生”中添加一条记录验证触发器的执行。

数据库中所有表的结构在请参考：SQL Server-【知识与实战IV】多表查询、个数计算、多重约束条件、两表间的交集、多表之间的关系分析

代码：

/*	
	1、	创建一个触发器，要求在表“学生”中添加记录时提示“某某学生记录已添加成功！”。
	并在表“学生”中添加一条记录验证触发器的执行。	
*/
USE EMIS1
GO
create trigger INSERT_STO on 学生
for insert
as
declare @name varchar(8)
select @name=姓名 from inserted
print @name+'学生的记录已添加成功！'
GO

//添加数据到临时表中
INSERT INTO 学生
VALUES('2016030120','李白','男','1999-05-12','2016-09-01','16wlw301','01','libaiya','19911313600')
GO
//删除数据
delete 学生 where 学号='2016030120'
go

/*
2、	创建一个触发器，要求在表“系部”中添加记录时，如果系主任非空则提示
“记录已添加成功！”，否则提示“系主任为空，撤消操作！”。在表“系部”
中添加一条记录验证触发器的执行。
*/

USE EMIS1
GO
create trigger INSERT_STT on 系部
for insert
as
declare @tname varchar(8)
select @tname=系主任 from inserted
if(@tname<>'')
	print '记录已添加成功！'
else
	begin
	print '系主任为空，撤消操作！'
	/*回滚，撤销操作*/
	rollback transaction
	end
GO

INSERT INTO 系部
VALUES('04','基础教育系','马云')
GO
INSERT INTO 系部
VALUES('05','科学技术系','')
GO

/*
3、	创建一个触发器，禁止在表“学生”中添加数据。并在表“学生”中添加一
条记录验证触发器的执行。
*/

USE EMIS1
GO
create trigger INSERT_STA on 学生
for insert
as
declare @xname varchar(8)
select @xname=姓名 from inserted, 班级
/*只要是添加，不管加什么数据都让它回滚*/
print '禁止添加数据、已自动回滚撤销操作'
rollback transaction
GO

INSERT INTO 学生
VALUES('2016030121','小刚','男','1999-05-12','2016-09-01','16wlw301','01','libaiya','19911313600')
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

喜欢记得点个赞哟，我是王睿，很高兴认识大家！