資料庫練習題2---創建與管理架構和基本表及資料操縱

請使用T-SQL完成以下內容：
1、 請在指定資料庫（首先在電腦最后一個磁盤上建立檔案夾，并建立資料檔案保存在該檔案夾里）內完成以下內容：
1） 依據資料表的結構創建相對應的資料表，表結構如下所示；
學生資訊表（student）
欄位名稱 欄位型別及長度 說明 備注
Sno Char（9） 學生學號 主關鍵字
Sname Char（6） 學生姓名 非空
Ssex Char（2） 學生性別 可為空
Sage Int 學生年齡 可為空
Sdept VarChar（8） 學生所在院系 可為空
課程資訊表（course）
欄位名稱 欄位型別及長度 說明 備注
Cno Char（4） 課程編號 主關鍵字
Cname VarChar（20） 課程名稱 非空
Cpno Char（4） 先行課 可為空
Ccredit Int 學分 可為空
選課資訊表（sc）
欄位名稱 欄位型別及長度 說明 備注
Sno Char（9） 學生學號 主關鍵字
Cno Char（4） 課程編號 主關鍵字
Grade Int 成績 可為空

create table Student (
	Sno   char(9)  primary key,
	Sname char(6)  not null,
	Ssex  char(2)  ,
	Sage  Int      ,
	Sdept VarChar(8))
go	
create table course (
	Cno   char(4)  primary key,
	Cname VarChar(20)  not null,
	Cpno  char(4)  ,
	Ccredit  Int    )
go
create table sc (
	Sno   char(9)  not null,
	Cno   char(4)  not null,
	Grade  Int     
	primary key (Sno,Cno)
	foreign key (Sno) references Student (Sno),
	foreign key (Cno) references course (Cno)	)
go	
 

2） 在表student中增加新欄位 “班級名稱（sclass）“；

Alter table student add sclass VarChar(20)
 

3） 在表student中洗掉欄位“班級名稱（sclass）”；

Alter table student drop column sclass
 

4） 修改表student中欄位名為“sname”的欄位長度由原來的6改為8；

Alter table student alter column Sname char(8)

5） 修改表student中欄位“sdept”名稱為“dept”，長度為20；

Alter table student drop column Sdept
Alter table student add dept VarChar(20)
 

6） 修改表student中sage欄位名稱為sbirth，型別為smalldatetime；

Alter table student drop column Sage
Alter table student add sbirth smalldatetime

7） 修改表student新名稱為stu_info；

exec sp_rename'student','stu_info'

8） 洗掉資料表student，

drop table student
 

2、創建教師授課管理資料庫JSSK，并完成以下內容；

1） 在資料庫JSSK中創建下列三張表；
表名：teachers
列名 資料型別 說明 描述
Tno 字符型，長度7 主鍵 教師號
Tname 字符型，長度10 非空 姓名
Tsex 字符型，長度2 默認取值為“男” 性別
Birthday 小日期時間型 允許空 出生日期
Dept 字符型，長度20 允許空 所在部門
Sid 字符型，長度18 身份證號
表名： lessons
列名 資料型別 說明 描述
Cno 字符型，長度10 主鍵 課程號
Cname 字符型，長度20 非空 課程名
Credit 短整型 學分
property 字符型，長度為10 課程性質
表名： shouke
列名 資料型別 說明 描述
Tno 字符型，長度7 主鍵 教師號
Cno 字符型，長度10 主鍵 課程名
Hours 整數 課時

create database jssk
create login User3 with password='123456',
default_database=jssk
go
use jssk
go
create user user3
go
create schema mzx authorization user3
go

create table teachers (
	Tno   char(7)  primary key,
	Tname char(10)  not null,
	Tsex  char(2)	default'男'	,
	Birthday  smalldatetime     ,
	Dept  char(20)				,
	Sid   char(18))
go	
create table  lessons (
	Cno   char(10)  primary key,
	Cname char(20)  not null,
	Credit	smallint	,
	property char(10))
go	
create table  shouke (
	Tno   char(7)  not null,
	Cno   char(10) not null,
	Hours int
	primary key (Tno,Cno)
	foreign key (Tno) references teachers (Tno),
	foreign key (Cno) references lessons (Cno)	)
go	
 

2） 在shouke表里添加一個授課類別欄位，列名為Type，型別為Char，長度為4；

Alter table shouke add Type char(4)

3） 將shouke表的Hours的型別改為smallint；

Alter table shouke alter column Hours smallint

4） 洗掉lessons表中的property列，

Alter table lessons drop column property

3、創建產品銷售資料庫CPXS，資料檔案的邏輯檔案名為cpxs_data，物理檔案名為D:\sql\cpxs.mdf；檔案初始大小為2MB，自動增長，每次增長1MB；日志檔案邏輯檔案名為cpxs_log，物理檔案為D:\sql\cpxs.ldf；檔案初始大小2MB，自動增長，每次增長15%；
1） 在資料庫CPXS中創建下列三張表；
表名：產品表（cp）表結構
列名 資料型別 說明 描述
Cpbh 字符型，長度6 主鍵 產品編號
Cpmc 字符型，長度30 非空 產品名稱
Jg 浮點型，長度8 允許空 價格
Kcl 整型，長度4 允許空 庫存量

表名：銷售商（xss）表結構
列名 資料型別 說明 描述
Xsbh 字符型，長度6 主鍵 銷售商編號
Xsmc 字符型，長度30 非空 銷售商名稱
Dq 字符型，長度10 允許空 地區
Fzr 字符型，長度8 允許空 負責人
Dh 字符型，長度12 允許空 電話
Bz 文本，長度16 允許空 備注

表名： 產品銷售（xss）表結構
列名 資料型別 說明 描述
Cpbh 字符型，長度6 主鍵 產品編號
Xsbh 字符型，長度6 主鍵 銷售商編號
Xssj Datetime，長度8 非空 銷售時間
Sl 整型，長度4 非空 數量
Je 浮點型，長度8 非空 金額

create database cpxs
on primary
(name=cpxs_data,
filename='D:\sql\cpxs.mdf',
size=3,
filegrowth=1)
log on
(name=cpxs_log,
filename='D:\sql\cpxs.ldf',
size=2,
filegrowth=15%)

create login User4 with password='123456',
default_database=cpxs
go
use cpxs
go
create user user4
go
create schema FPX authorization user4
go

create table cp (
	Cpbh   char(6)  primary key,
	Cpmc   char(30)  not null,
	Jg     float(8)		,
	Kcl    int      check( Kcl>=0 AND Kcl<=9999)  
	)
go	
create table xss (
	Xsbh  char(6)  primary key,
	Xsmc  char(30)  not null,
	Dq    char(10)	,
	Fzr   char(8)   ,
	Dh    char(12)	,
	Bz    char(16))
go	
create table Cxss (
	Cpbh   char(6)  not null,
	Xsbh   char(6)  not null,
	Xssj   Datetime ,	
	Sl     int  check(Sl>=0 AND Sl<=9999)   ,
	Je     float(8)
	primary key (Cpbh,Xsbh)
	foreign key (Cpbh) references cp (Cpbh),
	foreign key (Xsbh) references xss (Xsbh)	)
go