sql.txt

SQL 数据库 学生成绩管理代码 课程设计 同学们有福

七、创建数据库、数据表、视图、存储过程、自定义函数、触发器等语句和测试语句
1、创建数据库：

2、创建数据表
（1）、student表
use Sql
create table student
(sno char(8) primary key,
 sname char(10) not null,
 Ssex char(2) not null,
 Sbirth datetime,
 class char(4) not null )
（2）、score表
 create table score
(sno char(8) not null,
 cno char(4) not null,
      degree float not null)
(3)、course表
create table course
(cno char(5) not null primary key,
 cname varchar(10) not null,
 tno char(3) not null )
（4）、teacher表
 create table teacher
(tno char(5) not null primary key,
 tname varchar(10) not null,
 depart varchar(8) not null,
  Tsex char(2) not null,
  Tbirth datetime ,
  prof char(6) )
3向表中添加记录
（1）、向学生信息表中添加
insert into student values('101','柳红','女','1988-09-18','11')
insert into student values('102','风尘','男','1986-01-01','11')
insert into student values('103','张三','男','1988-11-01','11')
insert into student values('104','无情','女','1987-11-11','11')
insert into student values('105','李四','男','1986-12-05','13')
insert into student values('106','小红','女','1987-11-01','13')
insert into student values('107','飘雪','女','1986-10-01','12')
insert into student values('108','红尘','男','1988-9-08','11')
insert into student values('109','小四','男','1987-12-12','12')
insert into student values('110','小美','女','1987-08-28','12')
insert into student values('111','小雪','女','1989-08-18','11')
insert into student values('112','张云','男','1988-11-21','11')
insert into student values('113','张长','男','1988-01-01','11')
insert into student values('114','飘雪','女','1987-11-11','11')
insert into student values('115','赵飞','男','1985-12-05','13')
insert into student values('116','小昭','女','1987-11-01','13')
insert into student values('117','林林','女','1987-10-01','12')
insert into student values('118','刘辈','男','1986-9-08','11')
insert into student values('119','诸葛六','男','1988-12-12','12')
insert into student values('120','小乔','女','1987-08-28','12')  
 ………………
查看记录 
Select * from student
 
 （2）、向成绩表中添加
 insert into score values('101','01',88)
insert into score values('101','02',85)
insert into score values('101','03',80)
insert into score values('102','01',88)
insert into score values('102','02',85)
insert into score values('102','03',80)
insert into score values('103','01',83)
insert into score values('103','02',85)
insert into score values('103','03',90)
insert into score values('104','01',60)
 insert into score values('104','02',50)
insert into score values('104','03',45)
insert into score values('105','01',92)
insert into score values('105','02',88)
insert into score values('105','03',76)
insert into score values('106','01',50)
insert into score values('106','02',73)
insert into score values('106','03',64)
insert into score values('107','01',46)
insert into score values('107','02',62)
 insert into score values('107','03',88)
insert into score values('108','01',85)
insert into score values('108','02',80)
insert into score values('108','03',88)
insert into score values('109','02',85)
insert into score values('109','03',80)
insert into score values('109','01',83)
insert into score values('110','02',85)
insert into score values('110','03',90)
insert into score values('110','01',60)
 insert into score values('111','02',50)
  insert into score values('111','03',53)
   insert into score values('111','01',59)
insert into score values('112','03',45)
insert into score values('112','01',92)
 insert into score values('112','02',50)
insert into score values('113','02',88)
insert into score values('113','03',76)
insert into score values('113','01',50)
insert into score values('114','02',73)
insert into score values('114','03',64)
insert into score values('114','01',46)
insert into score values('115','02',62)


insert into score values('115','03',80)
insert into score values('115','01',88)
insert into score values('115','02',85)
insert into score values('116','03',80)
insert into score values('116','01',83)
insert into score values('116','02',85)
insert into score values('117','03',90)
insert into score values('117','01',60)
 insert into score values('117','02',50)
insert into score values('118','03',45)
insert into score values('118','01',92)
insert into score values('118','02',88)
insert into score values('119','03',76)
insert into score values('119','01',50)
insert into score values('119','02',73)
insert into score values('120','03',64)
insert into score values('120','01',46)
insert into score values('120','02',62)
 …………
查看记录 
Select * from score
 
（3）、向教师表中添加数据
insert into course values('01','计算机','11')
insert into course values('02','网络管理','12')
insert into course values('03','专业英语','13')
insert into course values('04','软件工程','14')
         …………
查看记录 
Select * from course
 
（4）、向课程表中添加数据
insert into teacher values('11','士刚','计算机系','男','1973-4-5','教授')
 insert into teacher values('12','刘红','计算机系','女','1975-12-1','副教授')
 insert into teacher values('13','张斐','管理系','女','1975-3-3','副教授')
 insert into teacher values('14','邓飞','英语系','男','1973-5-5','教授')
…………
查看记录 
Select * from teacher
  
4、一些查询语句
（1）、查询成绩大于学号为101的学生的课程为02的成绩的所有列。
select * from score where degree>(select degree from score where sno='101' and cno='02')
 
（2）、查询课程号01大于课程号02的最大值、并以分数降序排序的成绩表中所有列
select * from score s where s.cno='01' and s.degree>=(select max(degree) from score y where y.cno='02' ) order by degree desc
go
 select max(degree) as "02max" from score where cno='02'
  
(3)、查询性别为男的学号，姓名，班级，课程号和成绩的学生
select student.sno,student.sname,student.class,score.cno,score.degree from student,score where student.sno=score.sno and ssex='男'
 
（４）、查询成绩在60到80之间的所有列
select * from score where degree between 60 and 80
 
（５）、查询score表中至少有5名学生选修的并以0开头的课程的平均分
select avg(degree) as "平均分",cno from score where cno like '0%' group by cno having count(*)>=5
 
5、创建视图
（1）、创建所有11班的学生信息的视图
create view student11
as 
 select * from student where class='11'
查看视图中的记录
select * from student11
 
（2）、创建视图course_degree，其中的内容是选修计算机课程的学生信息，包括（sno,sname,cno,cname,degree），创建时加上with check option
create  view course_degree(sno,sname,cno,cname,degree)
as 
select score.sno,sname,score.cno,cname,degree from course ,student, score 
where score.cno=course.cno and student.sno=score.sno and  cname='计算机'
 with check option
查看视图中的记录
select * from course_degree
 
（3）、创建一个视图，其中的内容是成绩表中每门课程的
create view average
as 
select avg(degree) as '平均分' from score group by cno
查看视图中的记录
select * from average    
（4）、创建视图其中的内容是所有男教师和男学生的name,sex,birth
create view man
 as
 select sname as name,ssex as sex,sbirth as birth from student where ssex='男'
union select tname,tsex,tbirth from teacher  where tsex='男'
查看视图中的记录
select * from man
 
6、创建规则
规则的作用月CHECK约束的部分功能相同，在向表中的某列插入或更新数据时，用它来限制输入的新值的取值范围。而它与CHECK约束不同的是：
	CHECK约束是用CREATE TABLE语句在建表时指定的，而规则需要作为单独的数据库对象来实现。
	在一个列上只能使用一个规则。但可以使用多个CHECK约束。
	规则可以应用于多个列，还可以应用于用户自定义的数据类型，而CHECK约束只能应用于它定义的列。
 （1）、创建一个degree_rule规则
create rule degree_rule
 as
@values>0
把此规则绑定到score表中degree列
exec sp_bindrule 'degree_rule','score.degree'
在向成绩表中添加记录时，如果成绩degree<0，则插入不成功。


7、创建存储过程
 存储过程是存储在服务器上的例行程序及过程，在SQL SERVER只能感定义某个过程，其中记录了一系列的造作，每次应用程序只需调用该过程就可完成该操作，这种SQL SERVER中定义的过程就被称为存储过程。它可以完成以下功能：
	接受输入参数并返回多个输出值。
	包含T-SQL语句用以完成特定的SQL SERVER操作，其中可以有对其他存储过程的调用。
	返回一个指示成功与否及失败原因的状态代码给调用它的过程。
 存储过程不能接用过程名返回值，也不能直接在表达式中使用。

 
（1）、创建一个带输入参数的存储过程，调用此存储过程时，给出一个学生名，显示出此学生的学号，姓名，所学课程号，课程名称和对应的成绩
create proc student_name
@sname varchar(10)
as 
select student.sno,sname,score.cno,degree cname from student,score,course 
where student.sno=score.sno and score.cno=course.cno and sname=@sname
调用此存储过程，（此例是输出姓名为红尘的学生的信息）
exec student_name '红尘'
 
（2）、创建一个存储过程，在执行此存储过程时，如果没有给出参数（学生姓名），则输入全部的学生的学号，姓名，班级，任课教师编号及其姓名，所学课程名称和成绩，如果有，则显示此学生的以上信息。
create proc student_teacher
@sname varchar(10)=null
as
 if @sname is null
 select student.sno,sname,student.class,teacher.tno,teacher.tname,degree,cname from student,score,course,teacher
where student.sno=score.sno and score.cno=course.cno and course.tno=teacher.tno
 else
 select student.sno,sname,student.class,teacher.tno,teacher.tname,degree,cname from student,score,course,teacher
where student.sno=score.sno and score.cno=course.cno and course.tno=teacher.tno and sname=@sname
调用此存储过程 
exec student_teacher（没有实参） 
 
exec student_teacher '红尘' （查询姓名为红尘的学生的选课信息和成绩）
8、自定义函数
    函数是由一条或多条T-SQL语句组成的代码段，用语实现一些常用的功能，编写好的函数可以重复使用。用户自定义函数可以接受零个或多个输入参数，函数的返回值可以是一个数值，也可以是一个表。
	
  
（1）、创建一个用户自定义函数，用于输出同一个班级中的学生信息
create function studentclass
(@class char(5))
returns table
return(select * from student where class=@class)
调用自定义函数（本例是输出12班的学生信息）
select * from studentclass('12')
（2）、创建一个自定义函数，把某一学生所学课程名称，课程号及其成绩插入一个临时表中显示出来。
create function studentscore
(@sno varchar(5))
returns @student_score table
(sno char(5),
 sname varchar(10),
 cno char(5),
 cname varchar(10),
 degree float)
begin
 insert @student_score
select student.sno,sname,score.cno,cname,degree from student,score,course
where student.sno=score.sno and score.cno=course.cno and student.sno=@sno 
return
end
查看学号为103的学生的各科成绩。
select * from studentscore('103')

 
9、触发器
    触发器是一种特殊的存储过程，它不能显式地调用，而是在往表中插入记录、更新记录或者删除记录时，被自动的激活。所以，触发器可以用来对表实施复杂的完整性约束，当触发器所保护的数据发生改变时，触发器会自动被激活，从而防止对数据的不正确修改。在触发器中卡仪查询其他表，也可以执行更复杂的T-SQL语句。如果发现引起触发器执行的T-SQL语句执行了一个非法的操作，则可以通过回滚事务使语句不能执行，回滚后SQL SERVER会自动返回到此事务执行前的状态。
   SQL SERVER为每个触发器都查了两个专用表，inserted表和deleted表.这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也会被删除。
一个表中可以有多个具有不同名称的各种类型的触发器，每个触发器都可以完成不同的功能，但每个触发器只能作用在一个表上。

（1）、在成绩表中建立一个触发器，当向表中添加记录时，此学生的成绩都乘以1.2
create trigger scoreupdate on score
after insert
as 
 update score set degree=degree*1.2 from score where sno in (select sno from inserted )
向表中插入一条记录，检验触发器是否有用。
insert into score values('108','01','56')
（2）、在成绩表建立一个触发器，在向表中插入记录时，检验插入的课程号是否在课程表中的课程号的范围之内。如果在，则插入成功；否则，提示信息'没有这门课程'，回滚事务。
create trigger course_score
on score
after insert
as 
 if (select cno from inserted where cno in(select cno from course)) is null
  begin 
   print'没有这门课程'
   rollback transaction
  end
向表中添加一条记录，进行验证。
insert into score values('108','06','60')

10、建立索引
SQL SERVER的索引是一种物理结构，它能够提供一种以一列或多列的值为基础迅速查找表中行的能力。索引中记录了表中的关键值，提供了指向表中行的指针。它既可以在定义表时创建，也可以在定义表之后的任何时候创建。索引可以创建在一个列或多个列的组合上。
在教师信息表的姓名列上创建一个非惟一性的非聚簇索引
create nonclustered index ix_name on teacher(tname)