sqlserver 基础(增、删、改、查、链表查询、建立聚焦索引、主键、创建约束、创建外键)、触发器、存储过程

来源:互联网 发布:酒店网络点评回复 编辑:程序博客网 时间:2024/03/29 16:14

数据库系统及应用实验一

试验名称:SQLServer 2008数据库中数据库和表的创建,及插入,更新,删除等数据操作

1、  试验目的

本试验的目的是熟悉 “数据库和表的创建”章节的内容。具体来说本实验包括如下知识点:

n  熟悉T-SQL中数据库创建的命令操作。

n  熟悉T-SQL中数据库表的创建的命令操作。

n  熟悉T-SQL中数据操作的命令和操作

2、  试验环境

本试验的环境为:安装了SQLServer 2008软件的Windows机器。

3、  试验要求

本试验要求完成两部分工作:一个是数据库的创建,一个是数据库中相关表的创建。其中数据库名称是“StudentDB”,该数据库中有如下的表:XSB(学生表),KCB(课程表),CJB(成绩表),具体要求如下:

3.1数据库的创建

第一步:使用图形界面方式创建名为StudentDB的数据库。

数据库StudentDB的逻辑文件初始大小为10MB,最大大小为50MB,数据库自动增长,增长方式为按5%比例增长。

数据库StudentDB的日志文件初始大小为2MB,最大可增长到5MB,按1MB增长。

数据库的逻辑文件名和物理文件名均采用默认值,事务日志的逻辑文件名和物理文件名也均采用默认值。

 

第二步:使用图形界面方式删除数据库StudentDB。

 

第三步:在SQLServer 2008 Management Studio的新建查询文本编辑框中输入创建数据库的T-SQL命令,创建名为StudentDB的数据库。

数据库StudentDB的逻辑文件初始大小为10MB,最大大小为50MB,数据库自动增长,增长方式为按5%比例增长。

数据库StudentDB的日志文件初始大小为2MB,最大可增长到5MB,按1MB增长。

 

数据库的逻辑文件名和物理文件名均采用默认值,事务日志的逻辑文件名和物理文件名也均采用默认值。

 

3.2  数据库中相关表的创建,

在SQL Server2008 Management Studio的新建查询文本编辑框中输入创建数据库的T-SQL命令,分别创建XSB,KCB,CJB,其中每个表的字段分别如下,其中各个字段的类型同学们需要自己确定:

(1)、XSB(学号,姓名,性别,专业,学分)其中学号为主键;

(2)、KCB(课程号,课程名,学分)其中课程号为主键;

(3)       、CJB(学号,课程号,分数)其中学号和课程号的组合为主键;

 

 

Create table XSB(

Num int 11 primary key auto_increament,

Name varchar(20) not null default null,

Sex boolean not null default 1,

Zhuanye varchar,

Score int not null default 0

);

 

 

3.3数据库中相关表的数据操作,

在SQL Server2008 Management Studio的新建查询文本编辑框中输入创建数据库的T-SQL命令,分别对XSB,KCB,CJB三个表中的数据进行操作:

(1)、插入10条记录;insert into XSB value(‘’,’’,);

(2)、按某一个条件来更新记录(条件自拟,比如将性别为男的同学的性别更新为女);

(3)       删除某些记录(条件自拟,比如所有男生同学的记录等);



SQL语句


-- net start mssqlserver // cmd启动sqlserver服务
-- net stop mssqlserver // cmd停止sqlserver服务


--删除数据库
use master
go
if exists(select * from sysdatabases where name='StudentDB')
drop database StudentDB
go








--创建数据库和数据库日志文件
create database StudentDB
on  primary  -- 默认就属于primary文件组,可省略
(
    name='StudentDB', 
    filename='D:\myDB\StudentDB.mdf',
    size=10mb, 
    maxsize=50mb, 
    filegrowth=5%
)
log on
(
    name='Student_log',
    filename='D:\myDB\Student_log.ldf',
    size=2mb,
    maxsize=5mb,
    filegrowth=1mb
)
 --切换数据库
use StudentDB
go


--创建学生表 XSB(学号,姓名,性别,专业,学分)其中学号为主键;
create table XSB
(
    Num int primary key ,
    Name varchar(20) not null default null,
Sex char(10) not null ,
Zhuanye varchar(20),
Score int not null default 0
);
--创建课程表 KCB(课程号,课程名,学分)其中课程号为主键;
create table KCB
(
syllabusId int primary key,
syllabusName varchar(20) not null ,
syllabusScore int not null default 0
);
--创建成绩 CJB(学号,课程号,分数)其中学号和课程号的组合为主键;
create table CJB
(
num int not null,
syllabusId int not null,
scheduleScore int not null default 0
);
--设置成绩表 CJB 组合主键
alter table CJB
add constraint num primary key (num,syllabusId)


--分别给KCB、CJB、CJB插入数据
--向学生表(XSB) 插入数据
insert into XSB(Num,Name,Sex,Zhuanye,Score) 
values(081101,'王林','男','计算机',50),
(081102,'张三','男','计算机',55),
(081103,'李四','女','通讯工程',56),
(081104,'王五','男','计算机',58),
(081105,'唐三藏','男','通讯工程',70),
(081106,'孙悟空','男','计算机',50),
(081107,'沙僧','男','通讯工程',50),
(081108,'猪八戒','男','计算机',50),
(081109,'观世音','女','通讯工程',50),
(081110,'玉皇大帝','男','计算机',50),
(081111,'如来佛祖','男','通讯工程',50)
--向课程表(KCB) 插入数据
insert into KCB(syllabusId,syllabusName,syllabusScore) 
values(101,'计算机基础',5),
(102,'程序设计与语言',4),
(103,'离散数学',4),
(104,'数据结构',4),
(105,'计算机原理',6),
(106,'操作系统',5),
(107,'数据库原理',5),
(108,'计算机网络',5),
(109,'软件工程',4),
(110,'sqlserver',6)
--向成绩表(CJB) 插入数据
insert into CJB(num,syllabusId,scheduleScore) 
values(81101,101,75),
(81101,102,70),
(81101,106,85),
(81102,101,94),
(81102,103,75),
(81103,104,63),
(81103,107,82),
(81104,108,99),
(81105,109,78),
(81106,110,97)


select * from CJB
select * from KCB
select * from XSB


--按某一个条件来更新记录(条件自拟,比如将性别为男的同学的性别更新为女)
update XSB set Sex='女' where Sex='男'


--删除某些记录(条件自拟,比如所有男生同学的记录等)
delete from XSB where Sex='女'



数据库系统及应用实验二

试验名称:SQLServer 2008数据库中强大的查询功能

1、  试验目的

本试验的目的是熟悉 “数据库查询”章节的内容。具体来说本实验包括如下两个部分知识点:

n  掌握T-SQL中数据库常用查询命令操作。

n  掌握where子句,比较表达式,模式匹配,子查询,连接,分组,排序等常用查询

2、  试验环境

本试验的环境为:安装了SQL Server 2008(Express)及SQL Server 2008 Management Studio软件的Windows机器,具体安装软件下载请参考http://course.onlinesjtu.com/mod/forum/discuss.php?d=650。

3、  试验要求

本试验要求完成上述的两个实验目的:

在实验一的基础上完成实验二的相关功能,具体的T-SQL命令如下。

(1)、查询所有学生的信息,从xsb

(2)、查询学生表中的学号,姓名和专业三个字段的信息;

(3)、查询学生表中专业为计算机的同学的信息;

(4)、查询学生表中学分在50-60的同学的信息;

(5)、查询学号,姓名,课程名,成绩信息,从学生表,课程表,成绩表三个表中进行联合查询;

(6)、统计课程号为001的课程的平均成绩;

(7)、统计课程号为001的选课人数;

(8)       、查询课程号为001的课程的姓名,课程名,成绩,并按成绩从高到低进行排序



SQL语句

 --切换数据库
use StudentDB
go


-- 查询所有学生的信息,从xsb
select * from XSB
--或者
select Num,Name,Sex,Zhuanye,Score from XSB


--查询学生表中的学号,姓名和专业三个字段的信息
select Num,Name,Zhuanye from XSB


--查询学生表中专业为计算机的同学的信息
select * from XSB where Zhuanye='计算机'


--查询学生表中学分在50-60的同学的信息
select * from XSB where Score >=50 and Score <=60


--查询学号,姓名,课程名,成绩信息,从学生表,课程表,成绩表三个表中进行联合查询;
select x.Num,x.Name,k.syllabusName,c.scheduleScore from XSB x ,KCB k,CJB c


--统计课程号为001的课程的平均成绩;
select avg(scheduleScore) as 评价成绩 from XSB inner join CJB  on XSB.Num = CJB.num where syllabusid =101


--统计课程号为001的选课人数;
select COUNT(*) as 选课人数 from XSB inner join CJB  on XSB.Num = CJB.num where syllabusid =101


--查询课程号为001的课程的姓名,课程名,成绩,并按成绩从高到低进行排序
select XSB.Name as 学生姓名,KCB.syllabusName as 课程名,CJB.scheduleScore as 成绩 from KCB LEFT JOIN CJB ON KCB.syllabusid=CJB.syllabusid LEFT JOIN XSB ON CJB.num=XSB.Num where KCB.syllabusid=101 order by CJB.scheduleScore desc


select * from KCB --课程表
select * from CJB --成绩表
select * from XSB --学生表


数据库系统及应用实验三

试验名称:SQLServer 2008数据库中视图,索引和数据完整性的实现

1、  试验目的

本试验的目的是熟悉 “数据库视图,索引和数据完整性”章节的内容。具体来说本实验包括如下两个部分知识点:

n  掌握T-SQL中数据库常用视图定义实现。

n  掌握索引的定义

n  掌握实体完整性,域完整性和参照完整性的实现

2、  试验环境

本试验的环境为:安装了SQL Server 2008(Express)及SQL Server 2008 Management Studio软件的Windows机器,具体安装软件下载请参考http://course.onlinesjtu.com/mod/forum/discuss.php?d=650。

3、  试验要求

本试验要求完成上述的两个实验目的:

在实验一的基础上完成实验二的相关功能,具体的T-SQL命令如下。

(1)、定义一个视图,该视图为从xsb,cjb,kcb进行联合查询,得出的关于学号,姓名,课程名和成绩的字段;

(2)、定义好(1)描述的视图后,对视图进行查询;

(3)、在test1数据库中创建test表,并未testid建立聚集索引;

(4)、在test1数据库中创建test2表,有test2id,value两个字段,value的类型为int,并使用check约束使其范围在1-100之间;

(5)、在test1数据库中创建test3表,有test3id,学号ID;学号ID为外键,主键为xsb中的id


SQL语句

use StudentDB
go
--T-sql创建视图

--(1)定义一个视图,该视图为从xsb,cjb,kcb进行联合查询,得出的关于学号,姓名,课程名和成绩的字段;
create view CS_student
as
select x.Num as 学号,x.Name as 姓名,k.syllabusName as 课程名,c.scheduleScore as 成绩 from KCB as k inner JOIN CJB as c on k.syllabusId = c.syllabusId inner JOIN XSB as x on c.num = x.Num
go




--(2)、定义好(1)描述的视图后,对视图进行查询;
select 姓名 from CS_student


--(3)、在test1数据库中创建test表,并未testid建立聚集索引;
create table test
(
testid int not null
)
--testid建立聚集索引
create clustered index IX_Test_TestCol1
on 
test(testid)
go


--(4)、在test1数据库中创建test2表,有test2id,value两个字段,value的类型为int,并使用check约束使其范围在1-100之间;
create table test2
(
test2id int not null,
value int not null
)
--check约束使其范围在1-100之间
alter table test2
add constraint CK_test2 check (value >= 0 and value <=100)
--插入数据车市 check约束
insert into test2(test2id,value) values (1,'此处输入测试值小于0 大于 100 的值 最后输入0-100中的值')




--(5)、在test1数据库中创建test3表,有test3id,学号ID;学号ID为外键,主键为xsb中的id
create table test3 
(
test3id int not null,
num int not null, --学号ID外键
)
--学号ID为外键,主键为xsb中的id
alter table test3 
add constraint FK_test3_XSB foreign key(num ) references dbo.XSB(Num)

数据库系统及应用实验四

试验名称:SQLServer 2008触发器、存储过程和事务管理

1、  试验目的

本试验的目的是熟悉“存储过程和触发器”、“其他概念”章节的内容。具体来说本实验包括如下两个部分知识点:

n  掌握触发器的功能和使用。

n  掌握事务的意义,以及与触发器的配合工作。

n  掌握自定义存储过程的编写与使用

2、  试验环境

本试验的环境为:安装了SQL Server 2008(Express)及SQL Server 2008 Management Studio软件的Windows机器,具体安装软件下载请参考http://course.onlinesjtu.com/mod/forum/discuss.php?d=650。

3、  试验要求

本试验在实验一的基础上,要求完成如下工作:

(1)、   定义一个触发器,保证每个学生选修的课程数不能超过3门。然后往CJB表中分别插入已选修3门课的学生(如学号为081101的学生)的新成绩记录和未选修3门课的学生(如学号为081111的学生)的新成绩记录,观察并记录插入前后CJB表的内容变化。

提示:在CJB上定义一个INSERT AFTER 触发器。全局变量@@CURSOR_ROWS可以表示查询结果的行数。

(2)、   学生选修《数据结构》课的工作包括:在成绩表(CJB)中插入该生成绩为NULL的记录,并在学生表(XSB)将该生的总学分中增加《数据结构》对应学分。此项工作要么完全完成,要么什么都不做。

n  要求编写学生选修《数据结构》课的存储过程,参数为学生的学号。

n  要求对已选修3门课的学生(如学号为081101的学生)和未选修3门课的学生(如学号为081111的学生)分别调用以上存储过程,观察并记录执行的过程以及相关表(即CJB、XSB)内容的变化。



SQL语句


use StudentDB
go
--查看数据库中是否已有触发器
-- select * from sysobjects where name='examineoptionalcourse'  
--查看触发器 exec examineoptionalcourse


--题目一、定义触发器
create trigger examineoptionalcourse
on CJB
with encryption --文本加密
after insert --向表中添加数据时激活触发器
as
declare @CURSOR_ROWS int
select @CURSOR_ROWS=c.num from CJB c inner join  inserted i on c.num = i.num --赋值
if (select COUNT(c.num) from CJB  c where c.num=@CURSOR_ROWS)> 3
begin
print '学员已经选修3门课程'
rollback transaction --回滚﹐避免加入 
end


--题目二、创建存储过程
create PROCEDURE studentUpdate
@Id int  --学号
as
insert into CJB (num,syllabusid,scheduleScore) select s.Num,k.syllabusid,'' FROM XSB as s left join KCB as k on s.Zhuanye=k.syllabusName where s.Num=@Id
update XSB set Score=k.syllabusScore from KCB k where num=@Id and Zhuanye=k.syllabusName


--题目二执行存储过程
exec studentUpdate 81101




select * from test3
select * from XSB
select * from CJB
select * from KCB

1 1
原创粉丝点击