数据库原理与应用

实验内容：
实验一：创建数据库

1.使用企业管理器或sql语句创建一个名为“SM”的数据库，初始大小为3MB，最大为50MB，数据库自动增长，增长方式按10%；
日志文件初始大小为2MB，最大大小不受限制，按1MB增长。
create database SM
on primary
(name=sm_data,
filename='D:\SQL\SM_DATA.MDF',
size=3，
maxsize=50,
filegrowth=10%
)
log on
(name=SM_LOG,
filename='D:\SQL\SM_LOG.LDF',
size=2，
maxsize=unlimited,
filegrowth=1
)

实验二：创建表
1. 在数据库SM中创建学生表student,课程表course,选课表sc
student(sid,sno,clno,sname,ssex,sage,sbir)
说明：sid int identity(1,1) 序号
sno 为主关系键，为字符类型 学号
clno 字符类型， 班级号
sname 字符类型，并不为空
ssex 字符类型，check的值的范围为男女
sbir 日期类型 出生日期
sage int；
A: create table student
(
sid int identity(1,1),
sno nchar(8) constraint pk_sno primary key,
clno nchar(4),
sname nvarchar(20) not null,
ssex nchar(2) constraint ck_ssex check(ssex='男' or ssex='女'),
sbir date,
sage int,
)
course(cno,cname,ccredits,ctno,cpno,ctime)
说明：cno 字符类型，主关系键
cname 字符类型，唯一键
ccredits 学分，精确数值型，精确长度为2，小数位为1
ctno ，cpno 字符类型
ctime 整型

B：create table course
(
cno nchar(4) constraint pk_cno primary key,
cname nvarchar(20) constraint uk_cn unique,
ccredits decimal(2,1),
ctno char(8),
cpno char(8),
ctime int,
)
sc(sno,cno,score)
说明：sno+cno为主键，并且sno是student的外部键，cno是course的外部键。
score精确数值型，精确长度为4，小数位为1
C： create table sc
(
sno nchar(8) constraint fk_sno foreign key(sno) references student(sno),
cno nchar(4) constraint fk_cno foreign key(cno) references course(cno),
score decimal(4,1) constraint ck_score check(score between 0 and 100),//检查约束
constraint pk_sc primary key(sno,cno)
)


实验三：表的维护(ALTER TABLE)
1.用sql语句修改表course的列属性,将cname的长度改为40,且不允许空***
需要先将约束删除 才可设置不允许空
alter table course
drop constraint uk_cn
alter table course
alter column cname nvarchar(40) not null
2.用sql语句向表student中增加列email,且要求输入的电子邮件地址必须包括"@"
alter table student
add email char(50) constraint ck_email check(email='like %@%')***
3.用sql语句删除表student中的列sbir
alter table student
drop column sbir
4.删除sname列上的约束。
not null的 直接修改 不能删除***
alter table student
alter column sname nvarchar(30)


实验四：简单数据查询
在实验二的基础上，再在sm数据库中新建表teacher，包括如下数据项
te

acher(tno,tname,age,sal,dno)
tno为教职工编号，tname姓名，age年龄，sal为月薪，dno为部门号
create table teacher
(
tno nchar(8) constraint pk_tno primary key,
tname nvarchar(30) not null,
age int,
sal money,
dno int,
)
在student，course，sc，teacher四张表中进行下列查询

1，查询所有0002部门职工的信息；

select *
from teacher
where dno='0002'


2，查询1984年和1985年出生的女生的信息；
select * from student
where ssex='女' and sbir
between '1984-1-1' and '1985-12-31'

3，查询0001部门、0002部门或0003部门的职工信息；

select * from teacher
where dno in('0001','0002','0003')

4，查询学号为03004的同学正在学习的课程；
select * from sc
select cno from sc
where sno='03004' and score is null

5，查询姓王的同学的信息。
sname like '王%'

6，查询有多少名学生的物理课成绩不及格；
select cname,COUNT(sno) 不及格人数
from sc,course
where https://www.sodocs.net/doc/0f4485370.html,o=https://www.sodocs.net/doc/0f4485370.html,o and
cname='物理' and score<60
group by cname

7，求女学生的学生总数；
select COUNT(sno)
from student
where ssex='女'


8，求职工的最高工资、最低工资和平均工资；
select MAX(sal),MIN(sal),AVG(sal)
from teacher

9，查询职工的年薪，并按年薪的升序排列；
select tname 姓名,sal*12 年薪
from teacher
order by sal asc

10，求每个班的学生数
select count(sno)
from student
group by clno

11，查询每个学生已获得的学分
（成绩及格即得相应课程的学分）。
course(cno,cname,ccredits,ctno,cpno,ctime)
sc(sno,cno,score)
select sno,sum(ccredits)
from sc,course
where https://www.sodocs.net/doc/0f4485370.html,o=https://www.sodocs.net/doc/0f4485370.html,o
and score>60
group by sno

实验五 复杂查询
1. 查询体育课成绩不及格的男生名单
select sname from sc,student,course
where sc.sno=student.sno and https://www.sodocs.net/doc/0f4485370.html,o=https://www.sodocs.net/doc/0f4485370.html,o and cname='体育' and ssex='男' and score<60
2. 将04001班全体学生的成绩置0
update sc
set score=0
where (sno=(select sno from s where clno='04001'))
3. 删除04002班全体学生的选课记录
delete from sc
where (sno=(select sno from s where clno='04002'))

4. 查询所有选修了001号课程的学生的姓名
select sname from sc,student
where sc.sno=student.sno and cno='001'

5. 查询其他班中比04001班所有学生年龄都小的学生，并按年龄的降序输出
select sname,2016-year(sbirth) age
from student
where (2016-year(sbirth)< all (select sname min(sage)
as minage
from s
where clno='04001')
order by sage desc
6.查询没有选修数据库课程的学生的信息
select * from student
where(not exists (select * from sc,course
where https://www.sodocs.net/doc/0f4485370.html,o=https://www.sodocs.net/doc/0f4485370.html,o and cname='数据库')
实验六 创建视图
1.建立04002班

学生的视图
create view 班级0402
as
select sno,sname
from student
where clno=0402
2.建立04001班学生的视图，输出其学号、姓名、和年龄，并且更换列名
create view 班级0401(id,学号,班级,姓名,性别,年龄,email)
as select *
from student
where clno=0401
3.将学生的学号和平均成绩建立一个视图
create view 平均成绩(学号,Avg)
as select sc.sno,avg(score)
from sc
group by sno
4. 建立04003班学生选修了0001号课程的学生的视图.
create view 选修c1的0403班学生
as select SNAME
from sc,student
where sc.sno=student.sno and cno='c1' and clno='0403'

实验七 数据安全
1.在服务器中创建test数据库的用户yy和登陆账号。
2.把对表sc的插入权授予给用户yy，并允许将此权限再授予其他用户
3. 把在test数据库中的建表权限授予给用户yy。
4. 把查询表s和修改学生编号sno的权限授予给户yy。
5. 收回用户yy在test数据库中的建表权。
6. yy用户拒绝对C表的查看权限 实验八 索引的创建与使用，默认与规则的创建

1. 使用sql语句在表course的cname字段上创建一个唯一性的聚集索引，索引排列顺序为降序。
create unique clustered index qq on course (cname desc)

2. 使用sql语句在表course的credit字段上创建一个非唯一性的非聚集索引。
create nonclustered index qq2 on course
实验九.规则和默认
1. 在sm数据库中创建一个学生年龄的默认值，并将默认值捆绑到sm数据库表student的sage列上。
create default age
as '19'
exec sp_bindefault age,'student.sage'


2. 在sm数据库中定义规则，使用该规则的列的值被限制为必须大于0，并将规则捆绑到sm数据库表teacher的sal列上。最后对表teacher
执行插入语句，查看执行结果。
create rule rule1
as @rule1 >0
exec sp_bindrule 'rule1','sc.score'

实验十 存储过程、触发器

存储过程

1.在学生表中，根据姓名查询并显示该同学的信息，否则显示“查无此人”（使用if……else语句）
use student
go
create proc p2 (@sname nvarchar(20))
as
if exists (select * from s where sname =@sname )
select * from s where sname=@sname
else
print '查无此人'


2. 查看并显示选修指定课程的人数。
create proc p4 (@sno char(10))
as
select COUNT (sno) from sc where sno=@sno


3. 1+2+...+100（使用while语句）
declare @i int,@s int
set @i=1
set @s=0
while(@i <=100)
begin
set @s=@s+@i
set @i=@i +1
end
print @s

4.使用case表达式，判断课程表中ctno列的值，如果为“00”，则显示“专业基础课”；如果为“01”，则显示“公共基础课”；
如果为“02”，则显示“专业课”；否则显示“待定

”。
select ctno,课号=
case
when ctno='00' then '专业基础课'
when ctno='01' then '公共基础课'
when ctno='02' then '专业课'
else'待定'
end
from c



5.给定学号，根据该学生的平均分，评定等级，90以上‘一等',80以上‘二等',70分以上'三等',其他'补考'
create proc p3 (@sno char(10))
as
declare @grade char(10)
select @grade =
case
when avg(score)>=90 then '一等'
when avg(score)>=80 and avg(score)<90 then '二等'
when avg(score)>=70 and avg(score)<80 then '三等'
else '补考'
end
from sc

触发器
create trigger 触发器名
instead of/for/after insert,delete,update
on 表名
as
sql语句
1.不允许向数据表s添加数据
create trigger tr1
on s
instead of delete
as
select * from s
print'不能向s表中添加数据'

2.不允许修改数据表s中李四的数据
inserted 表格中保存了即将被添加的记录行,结构与原表相同
deleted 表格中保存即将被删除的记录行,结构与原表相同
修改数据相当于删除旧记录添加新记录
create trigger tr2
on s
for update
as
if exists (select sn from deleted where sn ='张三')
print'不能修改张三的信息'
rollback
end
3.删除学生信息的同时删除该生的选课信息
create trigger tr6
on s
for delete
as
delete from sc where sno=(selecet sno from deleted)
4.每添加一条选课记录，课程表中相应课程的选修人数加1 *
create trigger tr3
on sc
for insert
as
update c set cnum=cnum+1
where cno=(select cno from inserted)

5. 每当添加、删除、修改s表中数据,相应班级人数进行更新 *
create trigger tr5
on sc
for insert,delete,update
as
update c set cnum=cnum+1
where cno=(select cno from inserted)
update c set cnum=cnum-1
where cno=(select cno from deleted)


备注：查找的具体数据可以根据你所创建的具体表做相应调整