/*
Created 2019/3/21
Modified 2019/3/25
Project
Model
Company
Author
Version
Database MS SQL 2005
*/
Create table [Students]
(
[學號] Char(4) NOT NULL,
[姓名] Nvarchar(20) NOT NULL,
[性別] Bit NOT NULL,
[電話] Varchar(16) NULL,
[生日] Datetime NULL,
Primary Key ([學號])
)
go
Create table [課程]
(
[課程編號] Char(1) NOT NULL,
[名稱] Nvarchar(20) NULL,
[學分] Integer NOT NULL,
Primary Key ([課程編號])
)
go
Create table [班級]
(
[學號] Char(4) NOT NULL,
[教授編號] Char(4) NOT NULL,
[課程編號] Char(1) NOT NULL,
[上課時間] Datetime NOT NULL,
[教室] Varchar(8) NOT NULL,
Primary Key ([學號],[教授編號],[課程編號])
)
go
Create table [教授]
(
[教授編號] Char(4) NOT NULL,
[科系] Varchar(5) NOT NULL,
[職稱] Nvarchar(4) NOT NULL,
[身份證字號] Char(10) NULL,
Primary Key ([教授編號])
)
go
Create table [員工]
(
[身份證字號] Char(10) NOT NULL,
[姓名] Nvarchar(20) NOT NULL,
[電話] Varchar(16) NULL,
[城市] Nvarchar(4) NOT NULL,
[街道] Nvarchar(10) NOT NULL,
[薪水] Money NOT NULL,
[保險] Money NOT NULL,
[扣稅] Money NOT NULL,
Primary Key ([身份證字號])
)
go
Create table [測驗]
(
[測驗編號] Char(4) NOT NULL,
[名稱] Nvarchar(20) NOT NULL,
[種類] Char(1) NULL,
[課程編號] Char(1) NULL,
Primary Key ([測驗編號])
)
go
Create table [結果]
(
[測驗編號] Char(4) NOT NULL,
[學號] Char(4) NOT NULL,
[成績] Integer NOT NULL,
[日期] Datetime NOT NULL,
Primary Key ([測驗編號],[學號])
)
go
Create table [地址]
(
[地址流水號] Bigint Identity NOT NULL,
[地址] Nvarchar(30) NOT NULL,
[學號] Char(4) NOT NULL,
Primary Key ([地址流水號],[學號])
)
go
Create table [家長]
(
[姓名] Nvarchar(20) NOT NULL,
[關係] Nvarchar(4) NULL,
[學號] Char(4) NOT NULL,
Primary Key ([姓名],[學號])
)
go
Alter table [班級] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [結果] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [地址] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [家長] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [班級] add foreign key([課程編號]) references [課程] ([課程編號]) on update no action on delete no action
go
Alter table [測驗] add foreign key([課程編號]) references [課程] ([課程編號]) on update no action on delete no action
go
Alter table [班級] add foreign key([教授編號]) references [教授] ([教授編號]) on update no action on delete no action
go
Alter table [教授] add foreign key([身份證字號]) references [員工] ([身份證字號]) on update no action on delete no action
go
Alter table [結果] add foreign key([測驗編號]) references [測驗] ([測驗編號]) on update no action on delete no action
go
Set quoted_identifier on
go
Set quoted_identifier off
go
--預存程序Stored Procedure
--建立
create Proc 學生選課總筆數
as
begin
select 班級.教授編號,員工.姓名,教授.科系,教授.職稱,班級.學號,學生.姓名,學生.性別,
班級.課程編號,課程.名稱,課程.學分,班級.上課時間,班級.教室
from 班級
inner join 學生 on 班級.學號=學生.學號
inner join 課程 on 班級.課程編號=課程.課程編號
inner join 教授 on 班級.教授編號=教授.教授編號
inner join 員工 on 教授.身份證字號=員工.身份證字號
order by 學生.學號
print '共有'+cast(@@rowcount as varchar)+'筆選課資料!!'
end
--執行預存程序
execute 學生選課總筆數
--簡寫
exec 學生選課總筆數
--再談execute
declare @tablename varchar(30)
set @tablename='學生'
exec('select * from '+@tablename)
--------------------------------------
declare @procname varchar(30)
set @procname='學生選課明細'
exec @procname
--有參數的預存程序
create proc 學生查詢
@name varchar(12)
as
begin
select * from 學生
where 姓名 like '%'+ @name+ '%'
end
