数据库复习

例题1（数据库）--(1) 建立一个数据库，数据库名为：我班同学库。

create database 我班同学库--(2) 用企业管理器在我班同学库中建立如下数据表。

--①同学表(学号char(6)，姓名nchar(4)，性别nchar(1)，出生日期--datetime，身高decimal(5,2)，民族nchar(5)，身份证号char(18)，宿--舍编号char(6))。

use 我班同学库create table 同学表(学号char(6) primary key,姓名nchar(4) not null,性别nchar(1) check ( 性别='男' or 性别='女'),出生日期datetime not null,身高decimal(5,2) not null,民族nchar(5) default '汉族',身份证号char(18) not null,宿舍编号char(6) not null)--②宿舍表(宿舍编号char(6)，宿舍电话号码char(12))。

use 我班同学库create table 宿舍表(宿舍编号char(6) primary key,宿舍电话号码char(12)check (宿舍电话号码like'0551-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))--(3)use 我班同学库create table 课程表(课程号char(6) primary key,课程名称varchar(10) not null)use 我班同学库create table 选课表(学号char(6) foreign key (学号) references 同学表(学号) ,课程号char(6) foreign key (课程号) references 课程表(课程号), 分数decimal(5,2) not null,primary key (学号,课程号))--(5) 创建一默认值m-性别，默认取男，并将其应用到同学表的性别列上create default m_性别as '男'exec sp_bindefault m_性别,'同学表.性别'--(6) 创建一规则r_分数，要求分数必须是在-100之间的数值，并将其应用到选课表的分数列上。

create rule r_分数as @分数between 0 and 100exec sp_bindrule r_分数,'选课表.分数'--3. 向以上数据表中输入所在班同学的真实数据。

（每张表至少条数据）--(1)insert into 同学表(学号,姓名,性别,身高) values ('s10','张三','男',1.60)--(2)update 同学表set 性别='女' where 学号='s10'--(3)delete from 同学表where 学号='s10'例题2（视图）--1 创建学生成绩视图，要求查询所有选课学生的学号、姓名、课程名称及分数。

create view 学生成绩视图asselect 学生表.学号,姓名,课程名称,分数from 学生表,课程表,成绩表where 学生表.学号=成绩表.学号and 课程表.课程号=成绩表.课程号--2 创建班级学生视图，要求显示班级名称，学号，姓名，性别。

create view 班级学生视图asselect 班级名称,学号,姓名,性别from 学生表,班级表where 学生表.班级号=班级表.班级号select * from 班级学生视图--3 通过班级学生视图，添加学号为，姓名为张三，性别为男的学生信息。

insert into 班级学生视图(学号,姓名,性别) values ('001','张三','男')4 通过班级学生视图，将学号为的学生性别改为女。

update 班级学生视图set 性别='女' where 学号= '001'5 通过班级学生视图，删除学号为的学生信息。

delete from 班级学生视图where 学号='001'6 创建教师任课门数统计视图，要求查询每位教师的教师号及其任课的门数。

create view 教师任课门数统计视图asselect 教师号, count(distinct 课程号) as 任课门数from 成绩表group by 教师号select * from 教师任课门数统计视图7 创建学生课程视图，要求查询选修两门以上课程的学生学号和选课门数。

create view 学生课程视图asselect 学号,count(distinct 课程号) as 选课门数from 成绩表group by 学号having count(distinct 课程号)>=2select * from 学生课程视图8 修改学生成绩视图，要求查询选课在三门以上且各门课程均及格的学生的学号及其总成绩。

alter view 学生成绩视图asselect 学号,count(distinct 课程号) as 选课门数,sum (分数) as 总成绩from 成绩表group by 学号,分数having count(distinct 课程号)>=3 and 分数>=60select * from 学生成绩视图9 删除学生成绩视图。

drop view 学生成绩视图例题3（自定义函数加存储过程）--(1) 创建一个用户自定义函数：[查询某班学生信息]，通过输入班级名称，查询学生的姓名、性别、班级编号、班级名称。

并调用该函数查询。

create function 某班学生信息(@班级名称varchar(10))returns table asreturn (select 姓名,性别,学生表.班级号,班级名称from 学生表,班级表where 学生表.班级号= 班级表.班级号and 班级名称= @班级名称)(2) 创建一个带参数的存储过程：[查询某老师带过的学生]，通过输入老师姓名，查询某某老师带过的学生的姓名、老师姓名、成绩的存储过程。

并带参数，执行该存储过程。

create proc 某老师带过的学生@教师名varchar(20)asselect 学生表.姓名as 学生姓名, 教师表.姓名as 教师姓名, 分数from 学生表,成绩表,教师表where 学生表.学号= 成绩表.学号and 教师表.教师号= 成绩表.教师号and 教师表.姓名= @教师名--(3) 创建一个带参数的存储过程：[查询某课程不及格的学生]，通过输入课程名称，查询选修某课程的学生的学号、课程名称、成绩的存储过程。

并带参数，执行该存储过程。

create proc 某课程不及格的学生@课程名称varchar(8) asselect 学号,课程名称,分数from 成绩表,课程表where 成绩表.课程号= 课程表.课程号and 分数>=60and 课程名称= @课程名称--（）创建一个带参数的存储过程：[查询某姓学生信息]，通过输入学生的姓，来查询学生的基本信息的存储过程。

并带参数，执行该存储过程。

create proc 某姓学生信息@姓名varchar(10) asselect *from 学生表where 姓名like @姓名+ '%'exec 某姓学生信息'刘'--（）编写一个自定义函数，根据出生日期计算年龄。

create function age (@出生日期datetime)returns int asbegindeclare @年龄intset @年龄= 0set @年龄= datediff(yy,@出生日期,getdate())return @年龄endprint dbo.age ('1992-3-13')select dbo.age ('1991-3-24')--（）编写一个存储过程，修改“课程信息表”中的课程名称，带两个参数：课程编号和修改后的课程名称。

create proc 课程名称的修改@课程号varchar(10), @课程名称varchar(10) asupdate 课程表set 课程名称= @课程名称where 课程号= @课程号select 课程号,课程名称from 课程表where 课程名称= @课程名称and 课程号= @课程号--（）编写一个存储过程：通过输入班级名称和课程名称，查询学生的姓名、班级名称、课程名称和分数，并显人数、最高分、最低分和平均分(返回个数据表和个标量值)。

create view 联合表asselect 姓名,班级名称,课程名称,分数from 成绩表,学生表,班级表,课程表where 学生表.学号= 成绩表.学号and 课程表.课程号= 成绩表.课程号and 学生表.班级号= 班级表.班级号create proc 查询存储@班级名称varchar (10),@课程名称varchar (10),@人数int output,@最高分int output,@最低分int output,@平均分decimal(6,2) outputasbeginselect 姓名,班级名称,课程名称,分数from 联合表where 班级名称= @班级名称and 课程名称=@课程名称select @人数= count(*),@最高分= max(分数),@最低分= min(分数),@平均分= avg(分数)from 联合表where 班级名称= @班级名称and 课程名称= @课程名称enddeclare @人数int ,@最高分int ,@最低分int ,@平均分decimal(6,2) exec 查询存储'03电子商务','大学语文',@人数output ,@最高分output ,@最低分output ,@平均分output例题4（触发器）--1在学生表上创建一个触发器：[修改学号]，当修改该表中的学号时，提示用户不允许修改该表中的主键，并回滚事务。

create trigger 修改学号on 学生表forupdateasif update(学号)beginraiserror ('不允许修改该表中的主键',16,1)rollback transactionend---2在学生表上先建立一个约束，约束性别的取值只能为男或女。