数据库代码.sql

简单的学生成绩管理系统～实现了学生成绩的登记

create database coursesystem
on primary
(
    name = courseselect,
   filename =  'e:\coursesystem.mdf',
   maxsize = 1MB,
  filegrowth = 1%
)
log on
(
    name = courseselect2,
   filename =  'e:\coursesystem.ldf',
   maxsize = 1MB,
  filegrowth = 1% 
)


exec sp_helpdb coursesystem
go
--建立、定义数据库--

use coursesystem
go

CREATE TABLE Inform_of_Course
(
   Cname CHAR(60),
   Cno int identity(110220,1),
   Cvolume int,
   Cteacher CHAR(20),
   Ccredit tinyint ,
   Courstime CHAR(60)
) 
alter table Inform_of_Course
add address char(100)
go
--添加 地址列

ALTER TABLE Inform_of_Course
ADD CONSTRAINT DF_address DEFAULT 'http://jwc.bit.edu.com' FOR address 
go 
--更改数据，并加入DEFAULT约束

INSERT INTO Inform_of_Course VALUES('SQL SERVER数据库',160,'张春霞',2,'1-16周，周三，18:40-21:30','信1002')
INSERT INTO Inform_of_Course VALUES('计算机组成原理',100,'王娟',4,'1-16周，周一周三，8:00-9:50',default)
INSERT INTO Inform_of_Course VALUES('软件工程',60,'朱玉玲',2,'9-16周，周二10:10-12:00','3-332')
INSERT INTO Inform_of_Course VALUES('微积分',200,'程杞元',6,'1-16周，周一周五，8:00-9:50',default)
INSERT INTO Inform_of_Course VALUES('大学生体验英语',44,'崔岚',4,'2-15周，周四14:00-15:50','3-302')
INSERT INTO Inform_of_Course VALUES('JAVA面向对象程序设计',60,'吕坤',2,'1-10周，周二，16:10-18:00，周五，8：00-9：50',default)
INSERT INTO Inform_of_Course VALUES('汇编语言与接口技术',100,'吕坤',3,'1-16周，周二，13:00-15:50','主-102')
INSERT INTO Inform_of_Course VALUES('汇编语言与接口技术',100,'张新全',3,'1-16周，周二，13:00-15:50','主-102')

delete from Inform_of_Course
where Cname = '大学生体验英语'

update Inform_of_Course
set Cvolume = Cvolume * 1.1
--更新，删除数据
select Cname,sum(Ccredit) as sumcredit from Inform_of_Course
where Ccredit>2
group by Cname 

select Cteacher,avg(Ccredit) as average
from Inform_of_Course
group by Cteacher 
having Cteacher like '%吕坤%'

select * from Inform_of_Course
go
---------------------建立课程信息表，并插入数据-----------------

CREATE TABLE Student
(
   Sno char(20) CHECK (Sno>=00000000 AND Sno <= 99999999),
   Sname CHAR(20),
   Ssex CHAR(2) CHECK(Ssex IN('男','女')),
   Spassword CHAR(20),
   Sclass CHAR(10)
)


INSERT Student(Sno,Sname,Ssex,Spassword,Sclass) VALUES('20062794','陈威龙','男','123456','11110602' )
INSERT Student(Sno,Sname,Ssex,Spassword,Sclass) VALUES('20062795','张三','男','123456','11110602' )
INSERT Student(Sno,Sname,Ssex,Spassword,Sclass) VALUES('20062796','李四','女','123456','11110602' )
INSERT Student(Sno,Sname,Ssex,Spassword,Sclass) VALUES('20062797','黄五','女','123456','11110603' )
INSERT Student(Sno,Sname,Ssex,Spassword,Sclass) VALUES('20062798','林六','男','123456','11110601' )

create clustered index cl_Sno on Student(Sno)
create nonclustered index ncl_Sclass on Student(Sclass)
create nonclustered index ui_Sname_Sno_Sclass on Student(Sname,Sno,Sclass)
go
exec sp_helpindex Student
go
--构建索引，学生信息量很大，对每一个学生唯一的学号创建唯一索引，有利于查找学生信息。 创建非聚集索引班级号，有利于查找每个班级的信息。
--组合索引联系了 学生名字，学号，班级号，能直观的找到准确学生的信息。
select Sno,Sname from Student
where Sno>'20062794'
order by Sclass
go

select Sname,
substring(Sno,5,8) as last_four_student_number , 
right(Sclass,6) as last_four_class_number
from Student
go

select Sclass,count(Sno) as allstudent from Student
group by Sclass with cube

select * from Student
go
---------------------建立学生信息表，并插入数据-----------------



CREATE TABLE Educate
(
   Course CHAR(60),
   Courseno int PRIMARY KEY(Courseno,Coursetime),
   Credit tinyint,
   Teacher CHAR(20),
   Coursetime CHAR(60) UNIQUE
)
drop table educate

INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('SQL SERVER数据库',110220,2,'张春霞','1-16周，周三，18:40-21:30')
INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('计算机组成原理',110221,4,'王娟','1-16周，周一周三，8:00-9:50')
INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('软件工程',110222,2,'朱玉玲','1-16周，周二，10:10-12:00')
INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('微积分',110223,6,'程杞元','1-16周，周五，8:00-9:50')
INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('JAVA面向对象程序设计',110225,2,'吕坤','1-10周，周二，16:10-18:00，周五，8：00-9：50')
INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('汇编语言与接口技术',110226,3,'吕坤','1-16周，周二，13:00-15:50')

select * from Educate
go

create trigger tr_insert on educate instead of insert
as
print '是否选择该课程'
 
INSERT Educate(Course,Courseno,Credit,Teacher,Coursetime) VALUES('数码摄影',110230,2,'煅炼','2-15周，周二，18:40-21:30')
--创建触发器

create function fn_coursetime(@Ctime char(60))
returns char(80)
as
begin
declare @timeinfo char(80)
set @timeinfo = 'timeinformation :  '+ @Ctime
return @timeinfo
end

select dbo.fn_coursetime(Coursetime) as timeinformation from educate
--创建自定义函数 

create view courseinfo
as select Course ,Courseno,coursetime,Cteacher,Cvolume
from educate as e inner join Inform_of_Course as i
on e.courseno = i.Cno

exec sp_depends courseinfo
--创建视图
create proc p_educate
as
select *from educate

exec p_educate

exec sp_depends p_educate
--创建执行存储过程


select course,(select avg(credit) from educate) as averagecredit,coursetime
from educate
--子语句嵌套

select a.Courseno,b.Cname,a.teacher
from educate as a
inner join Inform_of_Course as b
on a.courseno=b.Cno
--多表链接

select Course,Credit ,courseno as no_of_course
from Educate
where courseno>110222
group by Course ,Credit,courseno
having Credit>2
--使用数学函数 ‘>’

SELECT avg(credit) AS AvgPrice ,sum(credit) as Sumcredit ,count(*) as number_of_course FROM educate
GO
--使用聚合函数
select * from educate
where course like '%SQL%' 


declare @fulltime char(10)
set @fulltime='48'
select coursetime  +  '  fulltime='+ @fulltime as Coursetime_information from educate 
go
--使用字符串函数

select count(*) from Educate
go
---------------------建立已选课程信息，并插入数据-----------------


--课程信息表，学生信息，教师信息，选课信息，已选课程信息--