学生成绩管理数据库

---========修改数据库=====---
-----01修改主数据文件----

modify file
(
  name = 'Student'
  size = 20mb
  maxsize = 50mb
  filegrowth = 5 mb
 )
 go
 
 ------02 修改日志数据文件----
 
 modify file
 (
    name = 'Student_log'
    size = 10mb
    maxsize = unlimited
    filegrowth = 10%
  )
  go
 
 --========-S:学生表 T：教师表 C：课程表 SC：成绩表==============----
---==========1. 定义学生管理数据表====------从数据库中删除数据表
---删除数据表时，删除顺序与建立顺序相反，要考虑到外键的约束
if OBJECT_ID('SC')is not null
   drop table SC;
if object_id('T') is not null
   drop table T;
if OBJECT_ID('C') is not null
   drop table C;
if OBJECT_ID('S') is not null
   drop table S;
go
----验证用户数据表确实删除---
select name
from sys.objects
where type = 'U'
go
---===================================---
----建立学生表S
create table S
(
  S# char(5) primary key ,
  Sname char(6) unique not null,
  Ssex char(2) check(Ssex in ('男','女')),
  Sbirth smalldatetime ,
  Departname varchar(20),
  EntranceDate smalldatetime ,
  Place char(10),
  Political char(10),
  Note text,
 )
------------------------------------
  ---建立课程表C
  create table C
  (
     C# int primary key,
     Cname varchar(20) not null,
     Credit tinyint not null,
     note text ,
  )
  ------------------------------------
  ---建立教师表T
  create table T
  (
    T# int primary key,
    Tname char(10) unique,
    Tsex char(2) check (Tsex in ('男','女')),
    Tbirth smalldatetime,
    DepartName varchar(20),
    Degree char(10),
    Titleof char(10),
    Note text,
   )
 ---------------------------
 ----建立成绩表SC
 create table SC
 (
    S# char(5),
    C# int,
    T# int not null,
    Grade decimal(5,1) not null,
    primary key (S#,C#),
    foreign key (S#) references S(S#),
    foreign key (C#) references C(C#),
    foreign key (T#) references T(T#),
  )
  go
 -----------------------------------
  ---==删除数据表中数据顺序要与添加数据的顺序相反
  delete SC
  delete T
  delete C
  delete S
 
  go
-------------------------------------
-------向学生表S中插入数据
insert S
   values ('1000','高兰','女','1989-08-09','医学院','2008-09-02','北京','预备党员','无')
insert S
    values ('1001','张强','男','1990-01-14','计算机学院','2009-09-03','洛阳','党员','无')
insert S
   values ('1002','高梅','女','1989-08-19','医学院','2008-09-02','哈尔冰','党员','无')
insert S
    values ('1003','张浩','男','1988-11-26','外语学院','2007-09-03','咸阳','团员','无')
insert S
   values ('1004','高磊','男','1989-08-10','物理学院','2008-09-02','北京','预备党员','无')
insert S
    values ('1005','张兵','男','1990-08-30','计算机学院','2008-09-03','洛阳','无','无')
insert S
   values ('1006','赵诗云','女','1989-10-17','管理学院','2009-09-02','苏州','预备党员','无')
insert S
    values ('1007','刘海涛','男','1986-01-18','计算机学院','2005-09-03','洛阳','党员','无')
insert S
   values ('1008','李丽萍','女','1985-08-29','化工学院','2005-09-02','兰州','团员','无')
insert S
    values ('1009','王强','男','1990-01-19','计算机学院','2008-09-03','洛阳','党员','无')
insert S
   values ('1010','钟秋洁','女','1988-08-13','外语学院','2008-09-02','香港','无','华侨')
insert S
    values ('1011','慕容泓','男','1992-01-24','计算机学院','2003-09-03','洛阳','党员','无')
insert S
   values ('1012','钱俊涛','男','1987-08-09','医学院','2006-09-02','昆明','预备党员','无')
insert S
    values ('1013','纳兰康','男','1990-01-04','地球物理学院','2008-03-03','上海','党员','无')
 insert S
   values ('1014','顾兴涛','男','1989-11-10','医学院','2008-09-02','南京','团员','无')
insert S
    values ('1015','张衡','男','1990-01-10','计算机学院','2003-09-03','深圳','无','华侨')
insert S
   values ('1016','高雪梅','女','1987-08-24','石油学院','2007-03-02','北京','预备党员','无')
insert S
    values ('1017','李明和','男','1993-01-14','物理学院','2008-03-03','上海','党员','无')
insert S
   values ('1018','赵兴军','男','1989-09-08','数学学院','2009-09-02','天津','无','无')
insert S
    values ('1019','张小洁','女','1989-06-01','外语学院','2008-09-03','杭州','党员','无')
insert S
   values ('1020','田何敏','女','1988-08-15','医学院','2009-09-02','嘉庆','团员','无')
insert S
    values ('1021','张国龙','男','198-08-08','石油学院','2008-09-03','长沙','团员','无')
insert S
    values ('1022','张小薰','女','1989-12-25','政法学院','2009-09-03','绍兴','团员','无')
insert S
   values ('1023','萧肖','女','1988-03-02','城建学院','2007-09-02','西安','党员','无')
insert S
    values ('1024','包晓婷','女','1985-09-01','外语学院','2005-09-03','杭州','党员','无')
insert S
   values ('1025','何琪芸','女','1984-08-15','医学院','2005-09-02','延安','团员','无')
insert S
   values ('1026','赵恒','男','1986-02-15','物理学院','2003-09-02','西安','团员','无')
insert S
   values ('1027','何欣','女','1986-05-15','外语学院','2003-09-02','宝鸡','党员','无')

---------------------------------------------------------------------向课程表C中插入数据
insert C(C#,Cname,Credit) values(1,'大学英语',6)
insert C(C#,Cname,Credit) values(2,'高等数学',4)
insert C(C#,Cname,Credit) values(3,'C语言程序',5)
insert C(C#,Cname,Credit) values(4,'大学物理',4)
insert C(C#,Cname,Credit) values(5,'软件导论',3)
insert C(C#,Cname,Credit) values(6,'操作系统',4)
insert C(C#,Cname,Credit) values(7,'西方文化概论',2)
insert C(C#,Cname,Credit) values(8,'人文素质教育',1)
insert C(C#,Cname,Credit) values(9,'商务英语',4)
insert C(C#,Cname,Credit) values(10,'商务口语',2)
insert C(C#,Cname,Credit) values(11,'多媒体信息处理',2)
insert C(C#,Cname,Credit) values(12,'模拟电路',5)
insert C(C#,Cname,Credit) values(13,'土木工程论',5)
insert C(C#,Cname,Credit) values(14,'电子商务',1)
insert C(C#,Cname,Credit) values(15,'数据库应用',6)
insert C(C#,Cname,Credit) values(16,'大学艺术',1)

-------------------------------------------------
---向教师表T中插入数据
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)     
      values('100','李岚','女','1968-06-25','外语学院','本科','教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('101','赵君杰','男','1963-01-09','计算机学院','博士','教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('102','张晓晨','女','1980-11-03','物理学院','研究生','讲师')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf
)     values('103','秦康','男','1979-7-12','石油学院','硕士','讲师')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('104','张伟','男','1968-12-15','政法学院','博士','副教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
     values('105','李卫国','男','1984-11-24','物理学院','本科','助教')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
    values('106','**','男','1970-11-15','体育学院','研究生','副教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
     values('107','赵小雄','男','1978-09-22','石油学院','研究生','讲师')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
     values('108','许利利','女','1976-3-10','外语学院','博士','副教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
     values('109','钱小君','女','1975-05-25','外语学院','研究生','副教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('110','刘洪涛','男','1976-12-27','城建学院','硕士','教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('111','吕美新','女','1983-11-11','体育学院','本科','助教')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('112','刘静','女','1950-12-17','艺术学院','硕士','教授')
insert T(T#,Tname, Tsex, TBirth, DepartName, Degree, TitleOf)
      values('113','吕新交','男','1945-12-11','文学院','博士','教授')    
----------------------------------------------------------------
--向成绩表SC中插入数据
insert SC values('1000',1,108,76.0)
insert SC values('1000',11,100,56.0)
insert SC values('1004',9,108,76.0)
insert SC values('1010',10,109,89.0)
insert SC values('1022',1,108,97.0)
insert SC values('1022',15,104,34.0)
insert SC values('1010',16,100,76.0)
insert SC values('1011',7,100,69.0)
insert SC values('1013',10,109,72.0)
insert SC values('1013',6,110,89.0)
insert SC values('1000',16,103,83.0)
insert SC values('1002',3,107,80.0)
insert SC values('1005',7,100,61.0)
insert SC values('1000',11,101,58.0)
insert SC values('1004',2,106,94.0)
insert SC values('1020',1,108,91.0)
insert SC values('1005',13,100,91.0)
insert SC values('1007',7,108,96.0)
insert SC values('1007',11,101,85.0)
insert SC values('1009',8,100,89.0)
insert SC values('1000',12,107,80.0)
insert SC values('1005',1,108,76.0)
insert SC values('1017',4,102,74.0)
insert SC values('1000',16,103,23.0)
insert SC values('1011',1,108,76.0)
insert SC values('1003',6,100,73.0)
insert SC values('1024',7,104,72.0)
insert SC values('1003',8,109,76.0)
insert SC values('1024',11,100,79.0)
insert SC values('1020',10,109,77.0)
insert SC values('1018',1,108,96.0)
insert SC values('1007',6,110,64.0)
insert SC values('1018',4,111,86.0)
insert SC values('1001',9,109,88.0)
insert SC values('1003',12,101,89.0)
insert SC values('1020',5,101,80.0)
----------------------------------------------
------------2.修改数据表--------
---建立一个用于修改的数据表Text
if OBJECT_ID('Test') is not null
  drop table  Test
 create table  Test
 (
   F1 char(6)
  )
go
exec sp_help Test
go
--添加字段
alter table Test add F3 real
alter table  Test add  F4 real default 0.0
alter table  Test add F5 real not null
alter table  test add F6 real default 0.0
go
exec sp_help Test
go
----修改表名
exec sp_rename 'Test','Test_new'
go
exec sp_rename  'Test_new','Test'
go
-----修改字段
exec sp_rename 'Test.F1','F2','column'
go
-----修改字符类型，是否为空
alter table  Test alter column F2 char(2)
alter table Test alter column F2 int not null
----给字段加默认约束
alter table  Test add constraint df_F2 default 0 for F2
---给字段加检查约束
alter table Test add constraint ck_F2  check(F2>=0 and F2<=100)
go
exec  sp_help Test
go
---删除约束
alter table Test drop constraint df_F2,ck_F2 
---删除字段
alter table  Test drop column F2
go
exec sp_help Test
go
------3.删除数据表-----
drop table  Test

---------------------------更多演示----
---创建新表so 其表结构与S完全相同
if OBJECT_ID('so') is not null
   drop table  so
select * into so from S where 1=0
go
---向表so中添加记录
insert so(S#,Sname,Sbirth,Ssex)
  values ('1027','张峰','1900-09-08','男')
--
insert so
 select *from S
select *from so
 go
--修改表so的备注
 update so set Note='汉族'
 update  so
 set EntranceDate ='2007-08-09'
 where Place ='北京'
---删除表
drop table  so

--------------------------------------------------------------------
---============================================================-----------------数据库查询-------------------
-----01.选择指定列
--查询学生学号，姓名，院系
select S#,Sname,DepartName
from S
--
select T#, Tname ,Degree
from T
-----02.查询所有列
select *
from C
或
select C#,Cname ,Credit,note
from C
-----03.定义别名
---只修改查询显示，而不修改数据表的列名
select S# 学号,Sname 姓名,DepartName 学院名称
from S
----04替换结果中的数据
---查询时可调用函数形成新列
select Sname,DATEDIFF (YEAR,Sbirth,GETDATE()) 年龄
from S
---使用case语句
select S#,C#,Grade ,Kind=
  case
     when Grade>=80 then '优秀'
     when Grade<=60 then '及格'
     else '不及格'
  end
from SC

select S#,C#,Grade,
   case
      when Grade>=80 then '优秀'
      when Grade<=60 then '不及格'
      else '不及格'
    end Kind
from SC

--消除结果中重复行
select distinct Departname
from S
go 
--限制结果返回行数
select top 3 *
from T

select top 5 Sname,Ssex
from S
go
--限制结果返回行条件
select S#,C#,Grade
from SC
where Grade<60 and C#=7
go
--
select Sname
from S
where Departname = '计算机学院'
       and Ssex = '男'
       and DATEDIFF (YEAR,Sbirth,GETDATE())<20
go
--限制取值
select *
from S
where Departname  in ('计算机学院','外语学院') --where Departname  ='计算机学院' or Departname ='外语学院'--
go
--限制范围
select *
from T
where YEAR(Tbirth) between 1970 and 1979
go
--模式匹配
select *
from S
where Sname like '王%' and Ssex = '男'
go
--
select *
from T
where Tname like '张%'and LEN(Tname)=3
go
--
select *
from T
where Tname like '[^张]%'
go
--
select *
from T
where Tname like '%小%'and LEN(Tname)=3
go
-----连接
---查询每个学号选修课课程信息
select Sname,Cname,Grade
from S ,SC,C
where  S.S#=SC.S#  and SC.C# = C.C#
go
--
select Sname,Cname,Grade
from S inner join SC on S.S# = SC.S#
       inner join C on SC.C# = C.C#
go
--查询外语学院学生选修课但分数不及格的信息
select Sname ,Cname,Grade
from S,C,SC
where S.S# =SC.S# and SC.C# = C.C#
       and Grade<60 and Departname = '外语学院'
go
--查询课程名以‘数’开头的课程被学生选修的信息
select C.C#,Cname
from S inner join SC on S.S# = SC.S#
       inner join C on SC.C# = C.C#
where Cname like '数%'
go
--
select S# ,Cname
--from C left join SC on C.C#=SC.C#
from C right join SC on C.C#=SC.C#
go
--
select Sname,Cname
from S CROSS JOIN C
go
-----数据汇总
--统计5选修课程的学号的学生的总分，平均分、最高分、最低分
select SUM(Grade),AVG(Grade),MAX(Grade),MIN(Grade)
from SC
where C#=5
go
--
select COUNT(*),COUNT(Grade)
from SC
where C#=1
go
--
select *
from S
where Departname ='外语学院'
compute count(S#)
go
----排序
--查询1号课程的学生，并按分数的高低排序输出学生的学号和分数
select S#,Grade
from SC
where C#=1
order by Grade desc
go