創(chuàng)建學(xué)生信息表概要

1、"創(chuàng)建學(xué)生信息2"/create table xs_xingxi(學(xué)號 Char (20) not null primary key,姓名 Char (8) no* null,性別 Bit no* null default (1) zlJLjIiElWlsmalldatetime not null,班級 nvarchar (50) not null,專業(yè) Char (8) not null,總學(xué)分 Tinyint default 0,備注 Text null)insert into xs_xingxivalues ( ,1001101620004 '丸J繼檜 J r

2、lr, r 1984-11-25' r '100110162 '軟 f牛 工程100r )insert into xs_xingxivalues ( 1001101620005 張三 J 919 f ,1982-12-25 ,100110162 軟件工 程'，100, 1 ')insert into xs_xingxivalues ( 100110:1620006 ，李四 J fl ,1982-l-25, r ,100110162, , 軟件 I：程1,100, - )insert into :-:s_xingxivalues (110011016200

3、07 1 f 王五 J fl9f V983-8-25 J ,100110162 '軟件 匸程100,' )insert into xs_xingxivalues ( 1001101620008 , 趙六 J rl f 1986-9-25' , ,100110162' r '軟件 匸程1 zl00, 1 f)"創(chuàng)建課程信息2"/create table kecheng(課程號 Char (3) not null primary key, 課程名 Char (20) no* null,教師編號 nvarchar (50) not null

4、z開課了期 Tinyint default (1) not nullz 學(xué)時Tinyint not null ,學(xué)分Tinyint , insert into kechengvalues ( -001' , 'SQL數(shù)據(jù)庫，001,1, 64,5)insert into kechengvalues ( *002' z 計算機'，001,1,64,5)insert into kechengvalues ( 003* , 6VA程序設(shè)計，001,1,64,5)"創(chuàng)建學(xué)生選課信息農(nóng)*/create table stu_xuanke_chengj i(學(xué)號 C

5、har (20) not null,課程號 Char (3) noz null z成纟責(zé)Tinyint default (1),沖伉 Tinyint default 0,)insert into stu_xuanke_chengj ivalues(1001101GC0004,001',85',5)insert into stu_xuanke_chengj ivalues ( 1001101620004, -002 80，5)insert into stu_xuanke_chengj ivalues(1001101處0004, *003-r ,70 5)"創(chuàng)建教師信息衣

6、*/create table teacher_xinxi(教師編號 nvarchar (50) not null primary key,姓名 Char (8) not null,'t生別 Bit not null default; (1) zElMJsmalldatetime not null,職稱 Char (8) null,備注 Text null)insert into teacher_xinxivalues ( f001 王老師 J T, ' 1984-04-22 ' F 教授 J r f)insert into teacher_xinxivalues (-0

7、02 劉老師 J fl ' 1984-04-22 1 r 博士 J 1 f)insert into teacher_xinxivalues ( 1 003 1 , * 李老師 9 f 919 f 91984-04-22 1 , 講師 rz r f)/*外鍵*7ALTER TABLE kechengWith nocheck/對以前的數(shù)據(jù)不進彳了約束檢查*/FOREIGN KEY(教師編號)ADD CONSTRAINT fk_teacher_xinxi_num REFERENCES teacher_xinxi (教師編號) "觸法器*/create trigger tri_stu

8、_delete on xs_xingxiafter delete asdelete stu_xuanke_chengji xhere 7：號in (select 學(xué)左;-from deleted)"刪除關(guān)聯(lián)信息*/delete from xsxingxi where 學(xué)號='：001101620004 "按照學(xué)號查找學(xué)生/select * from xsxingxi where *7:*= ' 1001101620004 '"創(chuàng)建視圖按照諜程名查找學(xué):生成績等信息養(yǎng)/Create view v_kc_namel/*With encrypti

9、on */Asselect xsxingxi 學(xué)號'xs_xingxi 姓名,xsxingxi 專業(yè),kecheng 課稈名,kecheng 7：1 甘, stu_xuanke_cheng j i 成績from xsxingxi,stu_xuanke_chengj i,kechengwhere xs_xingxi =stu_xuanke_chengj i '7：號and stu_xuanke_chengj i 課程 號=kecheng.課程號Select * from v_kc_namel Where 課程名=線件代數(shù), "存儲過程proc_wcw,顯示指定學(xué)生指定課程的成績"Create proc proc_wcw2Qsname char(20),kcm varchar(20)AsSelect xsxingxi 姓名'kecheng 課程名,stu_xuanke_chengj i 成績From xsxingxi,stu_xuanke_chengj iz kechengWhere xs_xingxi 7：號=stu_x