數(shù)據(jù)庫(數(shù)據(jù)庫完整性)

數(shù)據(jù)庫完整性使用s_t數(shù)據(jù)庫mipsql>uses_t；Databasechanged顯式說明參照完整性的違約處理示例（級聯(lián)刪除SC表中相應(yīng)的元組，級聯(lián)更新SC表中相應(yīng)的元組，當(dāng)刪除course表中的元組造成了與SC表不一致時拒絕刪除，當(dāng)更新course表中的cno時，級聯(lián)更新SC表中相應(yīng)的元組）定義完sc表，并插入數(shù)據(jù)進行操作檢查。iysql>createtablesc<snochar<9>notnull,enochar<4>notnull,Gradesmallint->primaryke^CSno,Cno>,foreignkey<Sno>referencesstudentCSno>ondeleteascade->onupdatecascade,foreigfnkey<Cno>referencescou.rse<Cno>ondeletenoction->onupdatecascade>；lueryOK,0rowsaffected<0.09sec>mysql>select*fromsc；snoeno!Grademysql>select*fromsc；snoeno!Grade92!92!85!88!90!80!!200215121!1!200215121!2!200215121!3!200215122!2!200215122!35rowsinset<0.03sec>ysql>createtablecourse<Cnochar<4>primarykey,-> Cnamechar<40>,Cpnochar<4>,Ccreditsmallint,-> foreignkey<Cpno>referencescourse<Cno>>；ueryOK,0rowsaffected<0.06sec>nysql>insertintocourse<Cno,Cname,CCredit>values<J2*,fmath,,J2J>；3ueryOK,1rowaffected<0.05sec>nysql>insertintocourse<Cno,Cname,CCredit>ualiwW6’，'DC','2’〉；3ueryOK,1rowaffected<0.00sec>nysql>insertintocourseualues<J7J,JpascalJJ6J,J4J>；3ueryOK,1rowaffected<0.00sec>nysql>insertintocourseumliwW5’，'，W,'4’〉；3ueryOK,1rowaffected<0.00sec>nysql>insertintocourseualues<J1J,JdbsJ,J5J,J4J>；3ueryOK,1rowaffected<0.00sec>nysql>insertintocourseumliwW3’，'is','，'4’〉；3ueryOK,1rowaffected<0.00sec>nysql>insertintocourseualues<J4,,,os,,,6,J.,3,>；3ueryOK,1rowaffected<0.00sec>2.Student表的Ssex只允許取“男”或“女”。mysql>createtablestudent-><snochar<9>primarykey,->snamechar<8>notnull,->ssexchar<2>check<ssexin<JmJ,JwJ->sagesmallint,->sdeptchar<20>->>；QueryOK,0rowsaffected<0.05sec>3.當(dāng)學(xué)生的性別是男時，其名字不能以Ms.打頭。mysql>createtablestudent-><snochar<9>,->snamechar<8>notnull,->ssexchar<2>,->sagesmallint,->adeptchar<20>,->primarykey<sno>,->check<ssex orsnamenotlike"msQueryOK,0rowsaffected<0.08sec>4.建立學(xué)生登記表Student，要求學(xué)號在90000?99999之間，姓名不能取空值，年齡小于30,性別只能是“男”或“女”。mysql>createtablestudent-><snonumeric<6>,->constraintclcheck<snobetween90000and->snamechar<20>->notnull,->sagenumeric<3>,->constraintc3check<sage<30>,->ssexchar<2>,->constraintc4check<ssexin<JmJ,JwJ->constraintstudentkei/primarykey<sno>>；QueryOK,0rowsaffected<0.00sec>mysql>insertintostudentualues<J95121J,JliuiFongJ,J,*n*>；QueryOK,1rowaffected<0.00sec>mysql>insertintostudentualues<J95122J,Jliuchengf,f19J,JmJ>QueryOK,1rowaffected<0.00sec>mysql>insertintostudentualues<J95123J,Jvjangming,,f18J,JwJ>QueryOK,1rowaffected<0.00sec>mysql>insertintostudentualues<J95125J,JzhangliJ,Jwr>；QueryOK,1rowaffected<0.00sec>5.修改表Student中的約束條件，要求學(xué)號改為在900000?999999之間，年齡由小于30改為小于40。mysql>altertablestudent->addconstraintclcheck<snobetween900000and999999>QueryOK,0rowsaffected<0.02sec>Records:0Duplicates:0Warnings:0mysql>altertablestudent->addconstraintc3check<sage<40>；QueryOK,0rowsaffected<0.02sec>Records:0Duplicates:0Uarnings:0插入數(shù)據(jù):mi/sql>insertintostudentualues<J951210,,fliuxiaozhi,,J35*,f>；QueryOK,1rowaffected<0.00sec>mi/sql>insertintostudentualues<J951220*,fliuchengji,,J3?f,fm*>；QueryOK,1rowaffected<0.00sec>mi/sql>insertintostudentualues<J951230*,fuangmingliJ,J38*,f>；QueryOK,1rowaffected<0.00sec>mi/sql>insertintostudentualues<J951250*,*Ehanglili*,*30J,JwJ>；QueryOK,1rowaffected<0.00sec>課后習(xí)題第6題。mysql>createdatabasevjokers；QueryOK,1rowaffected<0.02sec>inysql>usewoJeers；Databasechanged 曷mysql>createtabledept<deptnochap<20>,->deptnamechar<20>,managerchap<10>,phonenumberchar<30>,->primarykey<deptno>>；QueryOK,0rowsaffected<0.01sec>nysql>createtableempCempnochai'<10>,->empnamechar<20>,empagechai'<4>,constraintclcheclt<enpage<=60>,->jobchar<30>,salchar<10>,deptnochar<20>,primarykey<enpno>,foreignkey<deptno>referencesdept<deptno>>；QueryOK,0rowsaffected<0.02sec>觸發(fā)器mysql>uses_t；Databasechangedmysql>createtablestudent<Snochai'<9>primarykey,-> Snamechai'<20>Linique,-> Ssexchar<2>,Sagesmallint,Sdeptchar<20>>；QueryOK,0rowsaffected<0.00sec>mysql>createtablecourse<Cnochar<4>primarykey,-> Cnamecbar<40>,Cpnochar<4>,Ccreditsmallint,-> foreignkey<Cpno>referencescourse<Cno>>；QueryOK,0rowsaffected<0.02sec>mysql>createtablesc<Snochar<9>,Cnocbar<4>,GradesmaHint,ppimarykey<Sno,Cno>,->foreignkey<Sno>referencesstudent<Sno>,->foreic(nkey<Cno>referencescourse<Cno>>；QueryOK,0rowsaffected<0.00sec>l.MySQL數(shù)據(jù)庫中觸發(fā)器語句一般格式createtrigger［觸發(fā)器名］before/after［觸發(fā)事件］on［表名］foreachrow［觸發(fā)體］ysql>createtriggerstudent_trigger->beforeupdateonstudent->foreachrow->updatestudentsetsage=,22Jwheresno=,200215121,；ueryOK,0rowsaffected<0.05sec>

mysql>createtriggercourse_trigger->beforeupdateoncourse->foreachrow->updatestudentsetcpno=,4rwhereenoQueryOK,0rowsaffected<0.02sec>mysql>createtriggersc_trigger->beforeupdateonsc->foreachrow->updatestudentsetcno=,4Jwheresno=,1025812033JQiiEryOH.日fdwsmFFectEd sec〉查看觸發(fā)器showtriggers;mysql>shoutriggers；!Trigger !Euent!Table!Statement!chapacter_set_client!Timing!Created!sql_mode!Definer!chapacter_set_client!collation_connection!DatabaseCollation!icourse_triggeriUPDATEicourseiupdatestudentset!BEFORE!NULL! !ODBCPlocalhostiutf8_general_ci ilatin1_swedish_ciiisc_trigger iUPDATEisc iupdatestudentset5812033J !BEFORE!NULL! !ODBCPlocalhost!utf8_general_ci