数据库(数据库完整性)

数据库完整性使用s_t数据库mipsql>uses_t；Databasechanged显式说明参照完整性的违约处理示例（级联删除SC表中相应的元组，级联更新SC表中相应的元组，当删除course表中的元组造成了与SC表不一致时拒绝删除，当更新course表中的cno时，级联更新SC表中相应的元组）定义完sc表，并插入数据进行操作检查。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.当学生的性别是男时，其名字不能以Ms.打头。mysql>createtablestudent-><snochar<9>,->snamechar<8>notnull,->ssexchar<2>,->sagesmallint,->adeptchar<20>,->primarykey<sno>,->check<ssex orsnamenotlike"msQueryOK,0rowsaffected<0.08sec>4.建立学生登记表Student，要求学号在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中的约束条件，要求学号改为在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插入数据: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>课后习题第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>触发器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数据库中触发器语句一般格式createtrigger［触发器名］before/after［触发事件］on［表名］foreachrow［触发体］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〉查看触发器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