學生成績管理資料庫

---========修改資料庫=====---
-----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