目录
数据库中的相关定义
数据：是数据库中存储的基本对象
数据的含义称为数据的语义，数据与其语义是不可分的
数据库：是长期存储在计算机内、有组织的、可共享的大量数据的集合
数据库系统的基本特征：
（a）数据按一定的数据模型组织、描述和存储
（b) 实现用户共享、冗余度较小、易扩展
（c）数据独立性高（ 独立性高主要体现在物理独立性和逻辑独立性 ）
（1） 物理独立性：数据的物理结构（包括存储结构、存取方式等）的改变，如存储设备的更换、物理存储的更换、存取方式改变等都不能影响数据库的逻辑结构，从而不致引起应用程序的变化
（2）逻辑独立性：数据库总体逻辑结构的改变，如修改数据库模式、增加新的数据类型、改变数据间联系等，不需要相应修改应用程序，着 就是数据的逻辑独立性
数据库管理系统：位于用户与操作系统之间的一层数据管理软件，是基础软件，是一个大型复杂的软件系统 ，数据库管理系统的用途，科学地组织和存储数据、高效地获取和维护数据
数据库系统 ：是由数据库及其管理软件组成的系统【由这些组成，数据库（数据），数据库管理系统（软件）、数据库管理员（人员），系统平台（硬件平台），系统平台（软件平台）】
概念模型：也称信息模型，它是按用户的观点来对数据和信息建模，用于数据库设计。
实体 ：客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。
实体型 ：用实体名及其属性名集合来抽象和刻画同类实体
实体内部的联系：通常是指组成实体的各属性之间的联系
实体之间的联系 ：通常是指不同实体集之间的联系
元组：表中的一行即为一个元组
码：唯一标识实体的属性集称为码
模式：模式也称逻辑模式或概念模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图
外模式：外模式也称用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示
内模式：内模式也称存储模式，一个数据库只有一个内模式。它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式
数据库系统的软硬件层次结构图
数据库管理的三个阶段
| 人工管理 | 文件系统 | 数据库系统 | |
| 应用背景 | 科学计算 | 科学计算、管理 | 大规模管理 |
| 硬件背景 | 无直接存取设备 | 硬盘、磁鼓 | 大容量硬盘 |
| 软件背景 | 没有操作系统 | 有文件系统 | 有数据库管理系统 |
| 处理方式 | 批处理 | 联机实时处理批处理 | 联机实时处理 分布处理 批处理 |
| 数据管理者 | 人 | 文件系统 | 数据库管理系统 |
| 数据面向对象 | 某个应用程序 | 某个应用程序 | 现实世界 |
| 数据共享程度 | 无共享 冗余度大 | 共享性差 冗余度大 | 共享性大 冗余度小 |
| 数据独立性 | 不独立，完全依赖与程序 | 独立性差 | 具有高度的物理独立性和一定的逻辑独立性 |
| 数据结构化 | 无结构 | 记录内有结构整体无结构 | 整体结构化，用数据模型描述 |
| 数据控制能力 | 应用程序自己控制 | 应用程序自己控制 | 由DBMS提供数据安全性、完整性、并发控制和恢复 |
创建学生管理数据库
create database xsgl
on primary
(
name='xsgl',
filename='d:\xsgl_data.mdf',
size=10MB,
maxsize=unlimited,
filegrowth=1MB
)
log on
(
name='xsgl_log',
filename='d:\xsgl_log.mdf',
size=1MB,
maxsize=unlimited,
filegrowth=10%
)sp_helpdb xsgl --sp_helppdb '数据库名'(用来查寻 system procedure)
删除数据库
use master
go
drop database xsgl --注意xsgl是数据库名
go
数据库的分离、附加
sp_detach_db 'xsgl'
sp_attach_db xsgl,
'd:\xsgl_data.mdf' --附加数据库在数据库中创建数据表
--本例主要有三个表组成，接下来都是对这三个表进行的增删改查
--1.创建学生表
create table xs
(
学号 varchar(10) primary key,--primary key 是主键的意思
姓名 varchar(8),
性别 varchar(2),
出生时间 datetime,
专业 varchar(12),
总学分 float,
照片 varchar(100)
)
--2.创建课程表 kc
create table kc
(
课程号 varchar(6) primary key,
课程名 varchar(18),
学分 float,
学时数 int
)
--3.创建成绩表
create table cj
(
学号 varchar(10) ,
课程号 varchar(6) ,
成绩 float
primary key(学号,课程号)
)在数据表中添加数据
注意：在添加数据的时候，记得切换到xsgl数据库进行操作
use xsgl
go
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030101','秦建兴','男','1990/8/9','电子商务',20) --在xs表中添加数据
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030102','张吉哲','男','1990/12/1','电子商务',20)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030103','刘鑫','男','1990/3/23','电子商务',24)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030104','王兆伟','男','1991/5/18','电子商务',24)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030105','牛学文','男','1989/9/20','电子商务',19)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030106','马艳玲','女','1990/12/20','电子商务',20)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030107','张玉莹','女','1990/6/28','电子商务',24)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030108','耿娇','女','1991/6/6','电子商务',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030109','王丹','女','1990/11/23','电子商务',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030110','朱丹','女','1989/9/28','电子商务',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030301','尹相桔','女','1990/1/1','网络工程',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030302','李楠楠','女','1990/12/31','网络工程',12)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030303','王东冬','男','1990/6/29','网络工程',12)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030304','徐朝海','男','1990/10/28','网络工程',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030305','王小男','男','1991/12/3','网络工程',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030306','贾志儒','男','1990/4/8','网络工程',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030307','孙美娜','女','1990/9/3','网络工程',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030312','沈柯辛','女','1990/7/27','网络工程',13)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030313','王祥俊','女','1989/9/3','网络工程',17)
insert xs(学号,姓名,性别,出生时间,专业,总学分) values ('2009030315','么芳芳','女','1990/4/28','网络工程',17)
insert cj(学号, 课程号, 成绩) values ('2009030101','A001',97) --在cj表中添加数据
insert cj(学号, 课程号, 成绩) values ('2009030101','A002',93)
insert cj(学号, 课程号, 成绩) values ('2009030101','A005',94)
insert cj(学号, 课程号, 成绩) values ('2009030101','J001',99)
insert cj(学号, 课程号, 成绩) values ('2009030101','J002',77)
insert cj(学号, 课程号, 成绩) values ('2009030101','J003',86)
insert cj(学号, 课程号, 成绩) values ('2009030102','A001',61)
insert cj(学号, 课程号, 成绩) values ('2009030102','A002',62)
insert cj(学号, 课程号, 成绩) values ('2009030102','A005',78)
insert cj(学号, 课程号, 成绩) values ('2009030102','J001',44)
insert cj(学号, 课程号, 成绩) values ('2009030102','J002',76)
insert cj(学号, 课程号, 成绩) values ('2009030102','J003',74)
insert cj(学号, 课程号, 成绩) values ('2009030103','A001',96)
insert cj(学号, 课程号, 成绩) values ('2009030103','A002',90)
insert cj(学号, 课程号, 成绩) values ('2009030103','A005',87)
insert cj(学号, 课程号, 成绩) values ('2009030103','J001',88)
insert cj(学号, 课程号, 成绩) values ('2009030103','J002',85)
insert cj(学号, 课程号, 成绩) values ('2009030103','J003',89)
insert cj(学号, 课程号, 成绩) values ('2009030104','A001',85)
insert cj(学号, 课程号, 成绩) values ('2009030104','A002',83)
insert cj(学号, 课程号, 成绩) values ('2009030104','A005',71)
insert cj(学号, 课程号, 成绩) values ('2009030104','J001',88)
insert cj(学号, 课程号, 成绩) values ('2009030104','J002',77)
insert cj(学号, 课程号, 成绩) values ('2009030104','J003',66)
insert cj(学号, 课程号, 成绩) values ('2009030105','A001',64)
insert cj(学号, 课程号, 成绩) values ('2009030105','A002',61)
insert cj(学号, 课程号, 成绩) values ('2009030105','A005',86)
insert cj(学号, 课程号, 成绩) values ('2009030105','J001',74)
insert cj(学号, 课程号, 成绩) values ('2009030105','J002',45)
insert cj(学号, 课程号, 成绩) values ('2009030105','J003',78)
insert cj(学号, 课程号, 成绩) values ('2009030106','A001',82)
insert cj(学号, 课程号, 成绩) values ('2009030106','A002',90)
insert cj(学号, 课程号, 成绩) values ('2009030106','A005',67)
insert cj(学号, 课程号, 成绩) values ('2009030106','J001',77)
insert cj(学号, 课程号, 成绩) values ('2009030106','J002',78)
insert cj(学号, 课程号, 成绩) values ('2009030106','J003',85)
insert cj(学号, 课程号, 成绩) values ('2009030107','A001',91)
insert cj(学号, 课程号, 成绩) values ('2009030107','A002',89)
insert cj(学号, 课程号, 成绩) values ('2009030107','A005',81)
insert cj(学号, 课程号, 成绩) values ('2009030107','J001',90)
insert cj(学号, 课程号, 成绩) values ('2009030107','J002',96)
insert cj(学号, 课程号, 成绩) values ('2009030107','J003',94)
insert cj(学号, 课程号, 成绩) values ('2009030108','A001',66)
insert cj(学号, 课程号, 成绩) values ('2009030108','A002',63)
insert cj(学号, 课程号, 成绩) values ('2009030108','J001',76)
insert cj(学号, 课程号, 成绩) values ('2009030108','J002',77)
insert cj(学号, 课程号, 成绩) values ('2009030109','A001',78)
insert cj(学号, 课程号, 成绩) values ('2009030109','A002',86)
insert cj(学号, 课程号, 成绩) values ('2009030109','J001',81)
insert cj(学号, 课程号, 成绩) values ('2009030109','J002',84)
insert cj(学号, 课程号, 成绩) values ('2009030110','A001',63)
insert cj(学号, 课程号, 成绩) values ('2009030110','A002',63)
insert cj(学号, 课程号, 成绩) values ('2009030110','J001',75)
insert cj(学号, 课程号, 成绩) values ('2009030110','J002',78)
insert cj(学号, 课程号, 成绩) values ('2009030301','A001',85)
insert cj(学号, 课程号, 成绩) values ('2009030301','A002',78)
insert cj(学号, 课程号, 成绩) values ('2009030301','J001',84)
insert cj(学号, 课程号, 成绩) values ('2009030301','J002',90)
insert cj(学号, 课程号, 成绩) values ('2009030302','A001',86)
insert cj(学号, 课程号, 成绩) values ('2009030302','A002',67)
insert cj(学号, 课程号, 成绩) values ('2009030302','J001',74)
insert cj(学号, 课程号, 成绩) values ('2009030302','J002',55)
insert cj(学号, 课程号, 成绩) values ('2009030303','A001',74)
insert cj(学号, 课程号, 成绩) values ('2009030303','A002',73)
insert cj(学号, 课程号, 成绩) values ('2009030303','J001',86)
insert cj(学号, 课程号, 成绩) values ('2009030304','A001',83)
insert cj(学号, 课程号, 成绩) values ('2009030304','A002',85)
insert cj(学号, 课程号, 成绩) values ('2009030304','J001',77)
insert cj(学号, 课程号, 成绩) values ('2009030304','J002',67)
insert cj(学号, 课程号, 成绩) values ('2009030305','A001',61)
insert cj(学号, 课程号, 成绩) values ('2009030305','A002',85)
insert cj(学号, 课程号, 成绩) values ('2009030305','J001',83)
insert cj(学号, 课程号, 成绩) values ('2009030305','J002',68)
insert cj(学号, 课程号, 成绩) values ('2009030306','A001',77)
insert cj(学号, 课程号, 成绩) values ('2009030306','A002',79)
insert cj(学号, 课程号, 成绩) values ('2009030306','J001',93)
insert cj(学号, 课程号, 成绩) values ('2009030306','J002',85)
insert cj(学号, 课程号, 成绩) values ('2009030307','A001',86)
insert cj(学号, 课程号, 成绩) values ('2009030307','A002',98)
insert cj(学号, 课程号, 成绩) values ('2009030307','J001',77)
insert cj(学号, 课程号, 成绩) values ('2009030307','J002',81)
insert cj(学号, 课程号, 成绩) values ('2009030312','A001',33)
insert cj(学号, 课程号, 成绩) values ('2009030312','A002',89)
insert cj(学号, 课程号, 成绩) values ('2009030312','J001',74)
insert cj(学号, 课程号, 成绩) values ('2009030312','J002',71)
insert cj(学号, 课程号, 成绩) values ('2009030313','A001',73)
insert cj(学号, 课程号, 成绩) values ('2009030313','A002',85)
insert cj(学号, 课程号, 成绩) values ('2009030313','J001',90)
insert cj(学号, 课程号, 成绩) values ('2009030313','J002',91)
insert cj(学号, 课程号, 成绩) values ('2009030315','A001',88)
insert cj(学号, 课程号, 成绩) values ('2009030315','A002',97)
insert cj(学号, 课程号, 成绩) values ('2009030315','J001',92)
insert cj(学号, 课程号, 成绩) values ('2009030315','J002',90)
insert kc(课程号,课程名,学分,学时数) values('A001','英语',4,50)
insert kc(课程号,课程名,学分,学时数) values('A002','数学',4,50)
insert kc(课程号,课程名,学分,学时数) values('A005','哲学',2,40)
insert kc(课程号,课程名,学分,学时数) values('J001','计算机基础',4,60)
insert kc(课程号,课程名,学分,学时数) values('J002','数据结构',5,60)
insert kc(课程号,课程名,学分,学时数) values('J003','操作系统',5,72)
insert kc(课程号,课程名,学分,学时数) values('J005','数据库SQL Server',4,72)
sp_help cj --sp_help '数据表名' 查询 表的结构
经过上面的新建数据库，创建3个表，向表添加数据可得下面的图片效果，接下来的步骤，都会以这三个表格的内容进行操作
 |  |  |
如何删除某个表中的字段，我们以xs表中的照片字段为例
|  |
如何给cj表中的学号添加外键，外键以xs表的‘学号’作为参考
外键：外键是允许我们在两个表中的数据之间建立引用链接的一列或一组列。 该引用链接有助于将外键列数据与引用表数据的数据进行匹配。 被引用的表称为父表，而涉及外键的表称为子表。 此外，如果外键引用同一表的另一列，则此引用类型称为自引用。
alter table cj
add constraint fk_cj_学号
foreign key (学号) references xs(学号)既然上面的语句就是创建外键的语句，那我们如何验证外键创建是否成功，同时外键的效应是否生效，下面的图片显示，给cj表中插入数据时，消息框报错，那是因为在xs表中已经有学号为‘200803101’和课程号为‘A001’的数据了，如果在继续在cj表中添加就违反了外键的规则。
那我该如何取消外键的约束呢
alter table cj
drop constraint fk_cj_学号 |  |
通过执行上面的代码，cj表下面的键就少了。为了下面能够继续，请重新执行上上个新建外键的代码。
初级查询
下面进行查询代码的练习
--1.查学生的学号，姓名 （下面两句都能实现查询学号，姓名的操作）
--2.查询 某个人
--3.给表的列 取一个别名
--4.查询所有学生的年龄
5.去除重复的数据 ，查一下有哪些专业
6.取出前几行
7.查看大表的行数
8.根据已有的表 创建新表
9.创建一个空的新表
10.查 年龄在35以下的 女同学
11. 查 年龄在29-31之间的同学 >=29 <=31
12.想查 选修了 A001,J003的学生的学号
13.模糊查询 like 而不是 =
14.总学分 第二个是 数字9
15.集合函数
-- A001这门课的最高分
16.A001这门课的平均分
17.分组 group by
----求 男生、女生的平均年龄
18.在cj表中统计每门课的平均成绩
19.在xs表中分按专业统计出男生和女生的平均年龄及人数，
--结果按性别排序
20.在xs表中分按专业统计出男生和女生的平均年龄及人数，
--只显示 平均年龄在31岁以上的
21.查询cj表中平均成绩大于80分的学生的学号、
--平均分，并按分数由高到低排序。
22.查询至少有10人选修的课程号及选课人数
23.查询最低分是80分以上的同学的学号
24.查询cj表中总分大于250分的学生的
--学号、总分，并按分数由高到低排序 count与sum的区别 
中级查询
内连接
select * from xs a,cj b
where a.学号=b.学号
select * from xs a
inner join cj b on a.学号=b.学号1.查询选修了 哲学 这门课的学生的姓名
2.秦建兴 选修了哪些课程（显示课程名）
外连接
select * from xs a left outer join cj b
on a.学号=b.学号 3.查询姓王的男学生姓名、成绩
--分析:涉及到 学生表 和 成绩
4.查询网络工程专业学生姓名、
--所学课程及每门课程对应的成绩
5.按专业分别统计男生、女生的平均成绩
6.查询学生姓名，课程编号，课程名称，成绩
7.查询学生编号为“2009030304”学生的学生姓名、
----平均成绩
8.查询选修课程编号为“J001”的女生的最高分
子查询
1.查询选修了哲学的学生的学号与姓名
2. 在xs表中查询和“刘鑫”在
--同一个专业的所有男同学信息。
3.求选修了课程学分为5分的学生的学号及姓名
4. 在kc表中查询学分大于最小学分的课程信息
5.查询 所有没选修A001课程的学生的学号及姓名
6.查询平均分低于80分的学生学号与姓名
7. 找出每门课都在80分以上的人
--方法一
select a.* from xs a,
(select cj.学号 from cj
group by cj.学号
having(min(cj.成绩))>80) b
where a.学号=b.学号
----相关子查询，方法二
select * from xs a
where exists (
select cj.学号 from cj
where cj.学号=a.学号
group by cj.学号
having(min(cj.成绩))>80
)
--方法三
select * from xs a
where not exists (
select b.学号 from cj b
where b.学号=a.学号
and b.成绩<=80
) and exists
(
select b.学号 from cj b
where b.学号=a.学号
)
--方法四
select * from xs a
where not exists (
select b.学号 from cj b
where b.学号=a.学号
group by b.学号
having(min(b.成绩))<=80
)
8.找出至少有一门课不及格的同学姓名及学号
----相关子查询
--有一门课不及格 min(成绩)<60
9.查询所有选修了 "数据结构" 课程的
---学生的学号,姓名及成绩
视图
1.创建视图 电子商务专业学生成绩视图
create view v_电子商务专业学生成绩
as
select a.*,b.成绩,b.课程号 from xs a,cj b
where a.学号=b.学号
and a.专业='电子商务'
with check option2.修改 电子商务专业学生成绩视图
alter view v_电子商务专业学生成绩
as
select a.*,b.成绩,b.课程号 from xs a,cj b
where a.学号=b.学号
and a.专业='电子商务'
with check option3.使用视图
select * from v_电子商务专业学生成绩4.查询视图
--在电子商务专业学生成绩视图中查询名为“张吉哲”
select * from v_电子商务专业学生成绩 a
where a.姓名='张吉哲'
5.删除视图
drop view v_电子商务专业学生成绩6..加密
create view v_电子商务专业学生成绩
with encryption
as
select a.*,b.成绩,b.课程号 from xs a,cj b
where a.学号=b.学号
and a.专业='电子商务'
sp_helptext v_电子商务专业学生成绩7.查询视图的依赖
sp_depends v_电子商务专业学生成绩8.通过视图修改某人某门课的成绩
update v_电子商务专业学生成绩
set 成绩=99
where 学号='2009030102'
and 课程号='A001'存储器
1.创建存储过程
--根据学生姓名和课程名，查成绩
create procedure stu_cj1
@name char(10),@cname char(16),--输入参数
@cj float output --输出参数
as
select @cj=成绩 from
xs a,kc b,cj c
where a.学号=c.学号
and b.课程号=c.课程号
and a.姓名=@name
and b.课程名=@cname
go2.使用存储过程
declare @fs float
execute stu_cj1 '王丹','计算机基础',@fs output
select '王丹','计算机基础',@fs
3. 删除存储过程
drop procedure stu_cj1存储过程的练习
--根据学生姓名 查学生学号
create procedure stu_xh
@xm varchar(10),
@xh varchar(10) output
as
select @xh=学号 from xs
where 姓名=@xm
--使用
declare @xuehao varchar(10)
execute stu_xh '王丹',@xuehao output
select '王丹',@xuehao
--任务:根据输入参数:课程名
--查这门课的平均分
drop procedure dbo.queryAvg
create procedure queryAvg
@cname varchar(20),
@avg_score float output
as
declare @kch varchar(10)
select @kch=课程号 from kc
where 课程名=@cname
select @avg_score=round( avg(成绩),2)
from cj where 课程号=@kch
go
--使用
declare @平均分 float
execute queryAvg '计算机基础',@平均分 output
select '计算机基础',@平均分
----------
综合练习题:
设计一过程，输入参数是:(1)某门课的课程号，(2)目标通过率
根据目标通过率调整学生分数(cj1表)，
如实际通过率>=目标通过率则什么也不做，
否则按不及格学生成绩高低调整为60分，
使实际通过率>=目标通过率
(在自定义函数内不允许修改表!!!!)
(只能用 存储过程来修改表中的数据！)
drop table cj1
select * into cj1 from cj
drop procedure p_adjust
create procedure p_adjust
@kch varchar(10),
@goal_rate float
as
declare @这门课的总人数 int
declare @实际通过人数 int
declare @实际通过率 float
declare @需调整分数的人数 int
declare not_pass_cursor cursor for
select 学号,成绩 from cj1 where 课程号=@kch
and 成绩<60 order by 成绩 desc
for update --cj1表上 学号、课程号上必须加主键,否则报 游标是只读的错
declare @学号 varchar(10)
declare @成绩 float
declare @调整计数 int
--1.得到选修这门课的总人数
select @这门课的总人数=count(*) from cj1
where 课程号=@kch
--2.得到这门课的实际通过人数
select @实际通过人数=count(*) from cj1
where 课程号=@kch and 成绩>=60
--3.计算出这门课的实际通过率
set @实际通过率=(@实际通过人数*1.0/@这门课的总人数)*100
--4.与目标通过率进行比较
if @实际通过率< @goal_rate
begin
--算一下，需调整分数的人数
set @需调整分数的人数=
ceiling((@goal_rate - @实际通过率)/100
*@这门课的总人数)
--游标是这门课不及格的学生,按成绩倒序
open not_pass_cursor --打开游标
fetch next from not_pass_cursor --提取游标数据
into @学号,@成绩
set @调整计数=0
while @@fetch_status =0 and
@调整计数 < @需调整分数的人数
begin
select @学号,@成绩 --显示被调整的人
update cj1 set 成绩=60
where current of not_pass_cursor
set @调整计数 = @调整计数 + 1
fetch next from not_pass_cursor --提取游标数据
into @学号,@成绩
end
close not_pass_cursor
deallocate not_pass_cursor
end
select count(*) from cj1
where 课程号='A001'
and 成绩<60
--执行存储过程，让A001这门课 80%的学生通过
execute p_adjust 'A001',80
触发器
--当向cj表插入一条记录时，检查
--（1）该记录的学号是否在xs表中；
--（2）该记录的课程号是否在kc表中
--如不在则不允许插入
drop trigger check_trig
create trigger check_trig
on cj
for insert --1.后触发器
--instead of insert--2.替代触发器
as
if exists( select * from inserted a
where a.学号 not in (select b.学号 from xs b)
or a.课程号 not in (select c.课程号 from kc c) )
begin
raiserror('违背数据的一致性',16,1)--16级别；1状态
rollback transaction
end
go 
--触发器练习：对cj表建一触发器,若插入的成绩
-->100或为负数，则提示出错，并取消操作
create trigger check_成绩_trig
on cj
for insert
as
declare @成绩 float
select @成绩=成绩 from inserted
if @成绩>100 or @成绩<0
begin
raiserror('成绩不能>100或<0',16,1)
rollback tran
end
go
--验证 测试
select * from cj where 学号='2009030101'
delete from cj where 学号='2009030101'
and 课程号='A001'
insert into cj (学号,课程号,成绩)
values('2009030101','A001',101)
sp_help cj
alter table cj drop constraint ck_cj
--任务：对xs表中一触发器，不允许修改 总学分这一列
create trigger update_xs_trig
on xs
for update
as
if update(总学分)
begin
raiserror('不允许修改xs表的总学分列',7,2)
rollback tran
end
go
select * from xs
update xs set 总学分=35 where 学号='2009030101'
---------------
--建一delete触发器,当从xs表中删除一个学生时，
--从cj表自动删除这个学生的所有记录
create trigger delete_trig
on xs
after delete
as
delete from cj
where 学号=(select 学号 from deleted)
go
select * from xs
delete from xs where 学号='2009030105'
select * from cj where 学号='2009030105'
delete from xs where 学号='2009030105'
-------------------------------------------
--instead of 触发器
drop table books
create table books
( BookKey int identity(1,1),
BookName nvarchar(10) not null,
Color nvarchar(10) not null,
ComputedCol as (BookName+Color),
pages int)
--下面的语句错误(输入了 identity列)
insert into books(BookKey,BookName,Color,pages)
values(3,'计算机','红色',30)
select * from books
--创建视图
drop view book_view
create view book_view
as
select BookKey,BookName,Color,ComputedCol,Pages
from books
--在没有触发器时，下面的语句也错
--identity列，计算列
insert into book_view(BookKey,BookName,Color,ComputedCol,
pages)
values(3,'江苏概况','绿色','江苏概况绿色',31)
select * from book_view
delete from book_view
--创建一替代触发器
create trigger instead_ins
on book_view --触发条件之一
instead of insert --触发条件之二
as
declare @书名 nvarchar(10)
declare @颜色 nvarchar(10)
declare @页数 int
select @书名=BookName,@颜色=Color,
@页数=pages from inserted
insert into books(BookName,Color,pages)
values(@书名,@颜色,@页数)
-- insert into books(BookName,Color,pages)
-- values('江南','绿色',31)
go
--测试一下
--创建一视图，v_cj1_kc1 是cj1表和kc1表的连表
---往这个视图插入一条记录不成功，改成替代触发器就能成功
select * from kc1
select * from cj1
create view v_cj1_kc1
as select a.*,b.学号,b.成绩 from kc1 a, cj1 b
where a.课程号=b.课程号
select * from v_cj1_kc1
insert into v_cj1_kc1
values('J999','测试',4,41,'2009030101',60)
--视图或函数 'v_cj1_kc1' 不可更新，因为修改会影响多个基表。
create trigger instead_ins
on v_cj1_kc1
instead of insert
as
begin
declare @课程号 char(10),@课程名 char(10)
declare @学分 int, @学时数 int , @学号 char(12), @成绩 float
select @课程号=课程号,@课程名=课程名,@学分=学分,
@学时数=学时数,@学号=学号, @成绩=成绩 from inserted
insert kc1(课程号,课程名,学分,学时数) values ( @课程号,@课程名,@学分,@学时数)
insert cj1(课程号,学号,成绩) values( @课程号,@学号,@成绩)
end
go
select * from cj1 where 课程号='J999'
select * from kc1 where 课程号='J999'
------------------------------------
--用触发器实现数据一致性(典型例子)
当向cj表中添加某学生某课程的成绩时，
如该成绩>=60分
则该学生的xs表中的总学分加上这门课的学分数
create trigger update_总学分
on cj
for insert
as
declare @xf int,@cj numeric,@kch char(4),
@xh char(10)
set @cj=(select 成绩 from inserted)
set @kch=(select 课程号 from inserted)
set @xh=(select 学号 from inserted)
set @xf=(select 学分 from kc where 课程号=@kch)
if @cj>=60
begin
update xs set 总学分=总学分+@xf
where 学号=@xh
end
go
select * from cj order by 学号
select * from kc
select * from xs where 学号='2009030102'
insert into cj(学号,课程号,成绩)
values('2009030102','A005',61)
delete from cj where 学号='2009030102'
and 课程号='A005'
drop trigger update_总学分
-----------
管理触发器
-------------
DDL触发器
--1.服务器级别的DDL触发器
drop trigger dis_drop_database on all server
create trigger dis_drop_database
on all server --服务器上的触发器 drop_database
for drop_database
as
begin
raiserror('对不起,你不能删除数据库',16,10)
rollback tran
end
go
--测试验证触发器
create database test1
drop database test1
--2.数据库级别的DDL触发器
drop trigger dis_drop_table on database
create trigger dis_drop_table
on database --数据库上的触发器 DROP_TABLE
for drop_table
as
begin
raiserror('对不起,xsgl数据库中的表不能删除',16,10)
rollback tran
end
go
--测试验证触发器
drop table xs
--------------------------------
第10章实训
create procedure DispTab
@selectkey varchar(20)
as
declare @tabname varchar(20)
select @tabname=
case
when @selectKey like '%客户%' then '客户信息'
when @selectKey like '%销售%' then '销售信息'
when @selectKey like '%订单%' then '订单信息'
end
if @tabName is NULL
print '没有找到对应的表'
else
execute ('select * from '+@tabname)
go
execute DispTab 'aaaa'
----------------
在"订单信息"表上建后触发器，
当用户插入新的订单时，
按订货量相应地减少该货品的库存量(货品信息表)
select * from 订单信息 -- (数量列上有数量信息)
select * from 货品信息 -- (库存列)
create trigger upda_库存量
on 订单信息
for insert
as
declare @orderNum int,@goodno int
select @goodno=货品编码,
@orderNum=数量 from inserted
update 货品信息
set 库存量=库存量-@orderNum
where 编码=@goodno
go
--验证触发器
select * from 订单信息
select * from 货品信息
where 编码=1
insert into 订单信息(订单号,销售工号,货品编码,
客户编号,数量)
values(7,1,1,1,20)
-------------------------------------------------
创建一替代触发器
替代 插入订单信息表的insert语句
如订单数量小于等于 货品信息表中的库存量
则在触发器中往订单信息表中插入记录
否则，仅提示信息，不插入记录
create trigger Check_库存量
on 订单信息
instead of insert
as
declare @ordernum int,@stored int,@goodno int
select @goodNo=货品编码,@orderNum=数量 from inserted
select @stored=库存量 from 货品信息
where 编码=@goodno
if @orderNum > @stored
raiserror('订货量超库存',7,1)
else
insert 订单信息 select * from inserted
go
select * from 货品信息
insert into 订单信息(订单号,销售工号,货品编码,
客户编号,数量)
values(9,1,1,1,20)
select * from 订单信息
------------------------------------------
创建一替代触发器，如果插入xs表中的学生的专业不是
网络工程，软件工程，电子商务就不让添加
drop trigger trig_insert_xs
create trigger trig_insert_xs
on xs
instead of insert
as
declare @专业 varchar(20)
select @专业=专业 from inserted
if @专业!='网络工程' and @专业!='软件工程'
and @专业!='电子商务' and @专业 is null
begin
print @专业
raiserror('专业不对',16,1)
end
else
insert into xs select * from inserted
insert into xs values('201105001','haha','男',
'20181221','软件工程',11,null)
select * from xs
------------------------------------------
--事务
select * from cj
模拟事务:
-- 把学号 2009030101同学的 A002课的成绩
减4分 加到A001上去
begin tran --事务开始
update cj set 成绩=成绩-4
where 学号='2009030101'
and 课程号='A002'
update cj set 成绩=成绩+4
where 学号='2009030101'
and 课程号='A001'
commit --事务提交 rollback
事务特性:ACID
A:atomic 原子性
C:consistency 一致性
I:isolation 隔离性
D:duration 持久性
T-SQL语句
--T-Sql编程
--例1.
print 'Hello World'
declare @xh char(10),@xm char(10) --声明变量
set @xh='2009030101' --给变量赋值
select @xm=姓名 from xs
where 学号=@xh
print @xm
--例2.
--声明变量 @my_var1 varchar(5) 另一变量 @my_var2 varchar(8)
-------@my_var1 赋值为 '你好'; @my_var2赋值为 '你更好'
-------把两变量拼接后输出
declare @my_var1 varchar(5),@my_var2 varchar(8)
select @my_var1='你好',@my_var2='你更好'
--set @my_var1='你好'
--set @my_var2='你更好'
print @my_var1+':'+@my_var2
--例3
declare @num int
set @num=( select COUNT(*) from xs )
select @num as '总人数'
--print @num
print @@rowcount
select xs.学号 from xs group by xs.学号
print @@error
--例4
select 5/4 as '5/4'
select 25/(3*6),25%(3*6)
select 10&20,10|20,10^20,~20
select 5^4
select 131
--例5
----分支
declare @a int,@b int
select @a=1,@b=2
if @a<@b
print 'a<b'
else
print 'a不小于b'
declare @M char(3),@N char(2)
select @M='ABC',@N='EF'
if @M> @N
print 'A的ASCII码大于E的ASCII码'
else
print 'A的ASCII码小于E的ASCII码'
--例6 条件控制 (作业)
--某同学 '2009030101' 课程号='J002' 如果分数低于80，加5分
----否则不加分
declare @score decimal
select @score=isnull(成绩,0) from cj
where 学号='2009030101' and 课程号='J002'
if @@ROWCOUNT !=0
if @score <80
begin
update cj set 成绩=成绩+5
where 学号='2009030101' and 课程号='J002'
print '学号为 2009030101 的 J002 课已加5分'
end
else
print '学号为 2009030101 的 J002 课不能加分'
else
print '2009030101 同学 J002课 没有成绩'
-------------------------------------------------
--if exists 是否存在 @@rowcount
--例7.查询cj表里是否有 J005这门课，如果有 查询它的平均分
if exists( select * from cj where 课程号='J001' )
--select count(*) from cj where 课程号='J001'
--if @@ROWCOUNT>=1
begin
declare @avg float
set @avg=(select avg(成绩) from cj where 课程号='J005' )
select '已开设 J005这门课' as 信息,@avg as 平均分
end
else
print '没有开过 J005这门课 '
--例8 case when语句 类似于java switch 多判断
select 学号,
(case 课程号 --简单表达式
when 'J001' then '计算机基础'
when 'J002' then '数据结构'
else '不知道什么课'
end) as 课程名,
成绩
from cj
select 学号,
(case --搜索表达式
when 课程号='J001' then '计算机基础'
when 课程号='J002' then '数据结构'
else '不知道什么课'
end) as 课程名,
成绩
from cj
--学生任务:把成绩 转成五级制
select 学号,课程号,
( case
when 成绩>=90 then '优'
when 成绩>=80 then '良'
when 成绩>=70 then '中'
when 成绩>=60 then '及格'
else '不及格'
end ) '成绩(五级制)'
from cj
-------------------------------------------
*
***
*****
*******
*********
--1.先形成公式 行号与 空格数 星号数的关系
第1行: 8空，1星 空格数=9-行数 ; 星号数= 行数*2 -1
--2.一行作为一个字符串，该字符串由 空格和星号组成
declare @line varchar(30)
declare @rowcount int,@spacecount int,@starcount int
set @rowcount=1
while @rowcount<=5
begin
set @line=''
set @spacecount=9-@rowcount
set @starcount=2*@rowcount -1
--1.拼一行中的空格
while @spacecount>=1
begin
set @line=@line+' '
set @spacecount=@spacecount-1
end
--2.拼一行中的星号
while @starcount>=1
begin
set @line=@line+'*'
set @starcount=@starcount-1
end
print @line
set @rowcount=@rowcount+1
end
如何将sqlserver转换成mysql
这是mssr2的一种工具可以直接使用
链接: https://pan.baidu.com/s/1i58Xk-eWCFQukQ-kC5fdyQ?pwd=y338 提取码: y338 复制这段内容后打开百度网盘手机App，操作更方便哦
1090
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
