sql数据库实例.doc

s core 表格 CRE ATE T ABLE core( stude ntno nchar (10) NOT N ULL, c ourse nonc har(6 ) NOT NULL , usua llyn umeri c(6,2 ) NUL L, fin alnu meric (6,2) NULL ,P RIMAR Y KEY CLUS TERED (s tuden tno A SC,co ursen o ASC ) )(1) CREAT E TAB LE st udent 1(col umn_g rade int) --创建新表 GO EXEC sp_he lp st udent 1 --查看表的信息 GO ALT ER TA BLE s tuden t1 AD D col umn_c lass VARCH AR(20 ) NUL L --添加列 GO EXE C sp_ help stude nt1 GO ALT ER TA BLE s tuden t1 DR OP CO LUMN colum n_cla ss --删除列 GO EXEC sp_he lp st udent 1 GO Use test 01 GO ALTE R TAB LE st udent 1 ALT ER CO LUMN colum n_cla ss ch ar(20 ) NOT NULL --修改数据类型 GO EXEC sp_re name 'stud ent1. colum n_cla ss',' st_cl ass' --修改列名 GO (2 )CREA TE TA BLE s tuden t2(co lumn_ stuno ncha r(6)) --创建新表 GO EXEC sp_h elp s tuden t2 --查看表的信息 GO AL TER T ABLE stude nt2 A DD co lumn_ class VARC HAR(2 0) NU LL --添加列 GO EX EC sp _help stud ent2 GO AL TER T ABLE stude nt2 D ROP C OLUMN colu mn_cl ass--删除列 GO EXEC sp_h elp s tuden t2 GO ALTE R TAB LE st udent 2 ADD colu mn_he ight float NULL --添加列 GO EXE C sp_ help stude nt2 GO ALTER TABL E stu dent2 ALTE R COL UMN c olumn _heig ht int NUL L--修改数据类型 GO EXEC sp_r ename 'stu dent2 .colu mn_he ight' ,'st_ heigh t' --修改列名 GO 3.( 1)添加数据 INSE RT IN TO te st01. core( usual ly,co ursen o,fin al,st udent no) V ALUES (79, 'c051 09',9 1,'09 37221 508') INSE RT IN TO te st01. core VALUE S ('082411 3307' ,'c05 127', 93,78 ) (2)更新表数据 UPD ATE t est01 .dbo. score SET final =87 --修改一行 WHERE stud entno ='082 41133 07' A ND co ursen o='c0 5127' 或 INSER T INT O tes udent 1 VAL UES(9 0,080 9,187 ) INS ERT I NTO t est01 .dbo. stude nt1 V ALUES (87,0 902,1 78) I NSERT INTO test dent1 VALU ES(86 ,0912 ,167) GO U PDATE test dent1 SET st_gr ade=9 0 --修改一列 GO (3)删除整行或多行 DE LETE FROM test0 .stud ent1 WHERE st_g rade= 90 AN D st_ class =0809 AND st_he ight= 187 ( 4)在数据库 tes t01 中创建表 stu d,为表添加、删除列和行、再删除该表 u se te st01 go cr eate table stud ( Stud entno ncha r(