《2022年数据库系统概论,第五版,教程sql代码 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库系统概论,第五版,教程sql代码 .pdf（10页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1.0 createtriggersc_t afterupdateofgradeon sc referencignoldrowas oldtuple newrow as newtuple foreachrow when ( newtuple. grade=1.0 * oldtuple. grade) insertintosc_u ( sno , cno , oldgrade, newgrade) values( oldtuple. sno , oldtuple. cno , oldtuple. grade, newtuple. grade) /* 触发器 */ 2.0 begintransac
2、tion 读取账户甲的金额belance; balance=balance- amount; if( balance0) then 打印 金额不足 , 不能转账 ; rollback; else 读账户乙的余额BALANCE1 ; balance1=balance1+amount; 写回 balance1; commit; 3.0 createdatabasestudent; use student createtablestu ( Sno char( 11 ) Primarykey, Sname char ( 20 ) unique, Ssex char( 2), Sage SMALLINT
3、 , Sdeptchar ( 20 ) ) createtablecourse ( Cno char( 4) PRIMARYKEY , Cname char ( 40 )NOTNULL, Cpno CHAR( 4), CcreditSMALLINT , FOREIGNKEY ( Cpno ) referencescourse( Cno ) ) createtablesc ( Sno char( 11 ), Cno char( 4), Grade SMALLINT , PRIMARYKEY ( Sno , Cno ), FOREIGNKEY ( Sno ) referencesstu ( Sno
4、 ), FOREIGNKEY ( Cno ) referencescourse( Cno ) ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 10 页 - - - - - - - - - select* from course; 4.0 /* 6.0 建立下面二个关系模式 */ createdatabasework ; use work createtableworker ( Wnochar( 10 ), Wnamechar ( 3), age smallint, j
5、ob char( 3), WmoneySMALLINT , Wpubchar( 5), primarykey( Wno), foreignkey( Wpub) referencesclub( Wpub), check( age =( selectCOUNT( fid) from female)+(selectCOUNT( mid ) from male ) ); 5.0 createdatabasehomewark; use homewark createtables( sno char( 5) primarykey, sname char ( 30 ), statussmallint, ci
6、tychar( 20 ) ); select* from s; createtablep( pno char( 5) primarykey, pname char ( 10 ), colorchar ( 2), weightsmallint ); select* from p; createtablej ( jno char( 2) primarykey, jname chAR ( 15 ), citychar( 10 ) ); select* from j ; createtablespj ( sno char( 2), pno char( 2), jno char( 2), qty sma
7、llint, primarykey( sno , pno , jno ) ); select* from spj ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 10 页 - - - - - - - - - selectdistinctsno from spj where jno =j1; selectsno from spj where pno =p1 and jno =j1; selectsno from spj , p where spj . pno =p. p
8、no and jno =j1and color= 红 ; selectdistinctsno from spj where pno =p1 and pno in ( selectpno from p where color= 红 ); selectjno from spj , s, p where spj . pno =p. pno and spj . sno =s. sno and color= 红 and city!= 天津 ; selectjno from spj where pno in ( selectpno from p where color= 红 ) and sno in (s
9、electsno from s where city!= 天津 ); selectjno from spj where sno =s1; selectpname , qty 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 10 页 - - - - - - - - - from p, spj where spj . pno =p. pno and spj . jno =j2; selectdistinctp. pno from s , spj , p where s. sn
10、o =spj . sno and spj . pno =p. pno and city= 上海 ; createviewpro1 as selectsno , pno , qty from spj , j where spj . jno =j . jno and jname = 三建 ; selectdistinctpno , qty from pro1 ; select* from pro1 where pno =p1; 6.0 selectsname , sno from stu ; select* from stu orderbySdept, Sage desc ; selectCOUN
11、T( distinctsno ) from sc ; selectCOUNT(*) from stu ; selectcno , COUNT( sno ) from sc groupbyCno ; selectstu .*,sc .* from stu , sc where stu . sno =sc . sno ; selectsname from stu where Sno in ( selectSno from sc where Cno =2); selectsname , sage from stu where Sage any(selectSage名师资料总结 - - -精品资料欢迎
12、下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 10 页 - - - - - - - - - from stu where Sdept=cs) and Sdeptcs; selectsno , sname , sdept from stu where notexists ( select* from sc where Sno =stu . Sno and Cno =1); insert intostu ( Sno , Sname , Ssex , Sdept, sage ) values( 20121512
13、6, 张程 , 男 , cs, 18); select* from stu ; insert intosc ( Sno , Cno ) values( 201215125, 1); select* from sc ; UPDATEstu set Sage =22 where Sno =201215121; select* from stu ; updatestu set Sage =Sage +1; select* from stu ; deletefrom stu where Sno =201215126; select* from stu ; createviewis_stu as sel
14、ectsno , sname , sagefrom stu where Sdept=is; select* 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 10 页 - - - - - - - - - from is_stu; createviewis_s1( sno , sname , grade) as selectstu . Sno , sname , grade from stu , sc where Sdept=isand stu . Sno =sc . Sno
15、 and sc . Cno =1; select* from is_s1; 7.0 createtables( sno char( 5) primarykey, sname char ( 30 ), statussmallint, citychar( 20 ) ); select* from s; createtablep( pno char( 5) primarykey, pname char ( 10 ), colorchar ( 2), weightsmallint ); select* from p; createtablej ( jno char( 2) primarykey, jn
16、ame chAR ( 15 ), citychar( 10 ) ); select* from j ; droptablespj ; createtablespj ( sno char( 2), pno char( 2), jno char( 2), qty smallint, primarykey( sno , pno , jno ) ); select* from spj ; 8.0 select* 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 10 页 - - -
17、 - - - - - - from stu ; -1.0 selectsname from stus1 where exists ( select* from stus2 where s2 . Sdept=s1 . Sdeptand s2 . Sname = 刘晨 ); -2.0 select* from stu where Sdept=cs unionselect* from stu where Sage = 19 ; -3.0 select* from stu where Sdept=cs intersectselect* from stu where Sage = 19 ; -4.0 i
18、nsertintostu ( Sno , Sname , Ssex , Sdept, Sage ) values( 201215128, 陈丹 , 男 , is, 18 ); select* from stu -5.0 insertintosc ( Sno , Cno ) values( 201215128, 1); select* from sc ; -6.0 updatestu set Sage =22 where Sno =201215121; select* from stu ; -7.0 updatestu set Sage =Sage +5; select* from stu ;
19、-8.0 select* from stu where Sname isnullorSsex isnull 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 10 页 - - - - - - - - - or Sage isnullorSdeptisnull; -9.0 createviewdf ( sno , sname , grade) as selectstu . Sno , sname , grade from stu , sc where Sdept=isand
20、stu . Sno =sc . Sno and sc . Cno =1; select* from df ; -10.0 dropviewdf ; createviewdf ( sno , sname , grade) as selectstu . Sno , sname , grade from stu , sc where Sdept=isand stu . Sno =sc . Sno and sc . Cno =1; select* from df ; -11.0 createviewbt_s( sno , gave ) as selectsno , AVG( grade) from s
21、c groupbySno ; selectsno , gave from bt_s; -12.0 grantselect ontablestu to u1 ; -13.0 createtablesss (sno char( 9), cno char( 4), gradesmallint, primarykey( sno , cno ), foreignkey( sno ) referencesstu ( sno ) ondeletecascade onupdatecascade, foreignkey( cno ) referencescourse( cno ) ondeletenoactio
22、n onupdatecascade 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 10 页 - - - - - - - - - ); -14.0 createtablestud ( sno char( 9), sname char ( 8)notnull, ssex char( 2), sage smallint, primarykey( sno ), check( ssex = 女 or sname notlikems.%) ); 15.0 createviewsucceed selectGradefrom sc ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 10 页 - - - - - - - - -