MySQL数据库原理及应用实验报告书

PAGEPAGE33实验1创建和维护数据库一、实验目的（1）掌握在Windows平台下安装与配置MySQL8.0的方法。（2）掌握启动服务并登录MySQL8.0数据库的方法和步骤。（3）了解手工配置MySQL8.0的方法。（4）掌握MySQL数据库的相关概念。（5）掌握使用Navicat工具和SQL语句创建和删除数据库的方法。二、实验内容及步骤（1）在Windows平台下安装与配置MySQL8.0。（2）在服务对话框中，手动启动或者关闭MySQL80服务。（本题要求：①掌握基本操作②记住没有启动服务时的错误提示，在使用过程中要学会解决问题）【操作方法】选择【控制面板】|【管理工具】|【服务】，在打开的【服务】窗口中找到“MySQL80”服务项，点击左上角的“启动此服务”按钮即可启动此服务。同样，在该窗口中，点击左上角的“停止此服务”按钮即可停止此服务。（3）使用Net命令启动或关闭MySQL80服务。（在下面空行处写出相应的命令）启动服务：netstartmysql80关闭服务：netstopmysql80（4）分别用Navicat工具和命令行方式登录MySQL。（在下面空行处写出相应的命令）mysql-hlocalhost-uroot-p按【Enter】键后，按照提示输入root用户的密码即可成功登录MySQL。（5）创建数据库。①使用Navicat管理工具创建学生信息管理数据库gradem。②使用SQL语句创建数据库MyDB。（在下面空行处写出所用的SQL语句）CREATEDATABASEMyDB;（6）查看数据库属性。①在Navicat中查看创建后的gradem数据库和MyDB数据库的状态，查看数据库是否存在，当前是打开还是关闭状态。②利用SHOWDATABASES命令显示当前的所有数据库。SHOWDATABASES;（7）删除数据库。①使用Navicat图形工具删除gradem数据库。【操作方法】（1）启动Navicat，并确保与服务建立连接。（2）在【连接】窗格中展开服务器，鼠标右击要删除的数据库gradem，从快捷菜单中选择【删除数据库】命令。（3）在弹出的【确认删除】对话框中，单击【确定】按钮，确认删除。②使用SQL语句删除MyDB数据库。（在下面空行处写出所用的SQL语句）DROPDATABASEMyDB;③利用SHOWDATABASES命令显示当前的所有数据库。（将查询结果截图放在下面空行处）SHOWDATABASES;四、实验总结1、收获2、存在的问题

实验2创建与维护数据表一、实验目的(1)掌握表的基础知识。(2)掌握使用Navicat管理工具和SQL语句创建表的方法。(3)掌握表的修改、查看、删除等基本操作方法。二、实验内容及步骤(1)在gradem数据库中创建表1～表5所示结构的表。表1student表的表结构字段名称数据类型长度小数位数是否允许NULL值说明snochar10否主键snamevarchar8是ssexchar2是sbirthdaydate是saddressvarchar50是sdeptchar16是specialityvarchar20是表2course表(课程名称表)的表结构字段名称数据类型长度小数位数是否允许NULL值说明cnochar5否主键cnamevarchar20否表3sc表(成绩表)的表结构字段名称数据类型长度小数位数是否允许NULL值说明snochar10否组合主键、外键cnochar5否组合主键、外键degreedecimal41是表4teacher表(教师表)的表结构字段名称数据类型长度小数位数是否允许NULL值说明tnochar3否主键tnamevarchar8是tsexchar2是tbirthdaydate是tdeptchar16是表5teaching表(授课表)的表结构字段名称数据类型长度小数位数是否允许NULL值说明cnochar5否组合主键、外键tnochar3否组合主键、外键ctermtinyint1是(2)向表1至表5输入数据记录，见表6～表10。表6学生关系表studentsnosnamessexsbirthdaysaddresssdeptspeciality20050101李勇男1987-01-12山东济南计算机工程系计算机应用20050201刘晨女1988-06-04山东青岛信息工程系电子商务20050301王敏女1989-12-23江苏苏州数学系数学20050202张立男1988-08-25河北唐山信息工程系电子商务表7课程关系表coursecnocnamecnocnameC01数据库C03信息系统C02数学C04操作系统表8成绩表scsnocnodegree20050101C019220050101C028520050101C038820050201C029020050201C0380表9教师表teachertnotnametsextbirthdaytdept101李新男1977-01-12计算机工程系102钱军女1968-06-04计算机工程系201王小花女1979-12-23信息工程系202张小青男1968-08-25信息工程系表10授课表teachingcnotnoctermC011012C021021C032013C042024(3)修改表结构。①将student表进行复制，复制后的表名为student_copy1。②向student_copy1表中增加“入学时间”列，其数据类型为日期型。③将student_copy1表中的sdept字段长度改为20。④将student_copy1表中的speciality字段删除。(4)利用SQL命令完成以下对表的操作①利用createtable命令完成student表和course表的定义。CREATETABLEstudent(snochar(10)PRIMARYKEY,snamevarchar(8),ssexchar(2),sbirthdaydate,saddressvarchar(50),sdeptchar(16),specialityvarchar(20));CREATETABLEcourse(cnochar(5)PRIMARYKEY,cnamevarchar(20)notnull);②利用droptable命令删除表student_copy1。DROPTABLEstudent_copy1;四、思考题(1)主键可以建立在“值可以为NULL”的列上吗？不可以(2)在定义基本表语句时，NOTNULL参数的作用是什么？NOTNULL指不允许为空值，该列必须输入数据。五、实验总结：1、收获2、存在的问题

实验3简单数据查询一、实验目的(1)掌握SELECT语句的基本用法。(2)使用WHERE子句进行有条件的查询。(3)掌握使用IN和NOTIN，BETWEEN…AND和NOTBETWEEN…AND来缩小查询范围的方法。(4)利用LIKE子句实现字符串匹配查询。二、实验内容及步骤在上次实验建立的Gradem数据库中完成下面查询： (1)查询所有学生的基本信息；select*fromstudent;查询所有课程的基本信息；select*fromcourse;查询所有学生的成绩信息。select*fromsc;(2)查询所有学生的学号、姓名、性别和出生日期。selectsno,sname,ssex,sbirthdayfromstudent;(3)查询所有课程的课程名称。selectcnamefromcourse;(4)查询前10门课程的课号及课程名称。select*fromcourselimit10;或：select*fromcourselimit0,10;(5)查询所有学生的姓名及年龄。selectsname,year(curdate())-year(sbirthday)fromstudent;(6)查询所有年龄大于18岁的女生的学号和姓名。selectsno,snamefromstudentWhereyear(curdate())-year(sbirthday)>18andssex=’女’;(7)查询所有男生的信息。select*fromstudentWheressex=’男’;(8)查询所有任课教师的姓名（tname）和所在系别（tdept）。selecttname,tdeptfromteacher;(9)查询“电子商务”专业的学生姓名、性别和出生日期。selectsname,ssex,sbirthdayfromstudentWherespeciality=’电子商务’;(10)查询Student表中的所有系名。selectdistinctsdeptfromstudent；(11)查询“C01”课程的开课学期。selectctermfromteachingWherecno=’c01’;(12)查询成绩在80～90分之间的学生学号及课号。selectsno,cnofromscWheredegreebetween80and90;或：selectsno,cnofromscWheredegree>=80anddegree<=90;(13)查询在1970年1月1日之前出生的男教师信息。select*fromteacherWheretbirthday<’1970-1-1’andtsex=’男’;(14)输出有成绩的学生学号。selectdistinctsnofromscWheredegreeisnotnull;(15)查询所有姓“刘”的学生信息。select*fromstudentWheresnamelike’刘%’;(16)查询生源地不是山东省的学生信息。select*fromstudentWheresaddressnotlike’%山东省%’;(17)查询成绩为79分、89分或99分的记录。select*fromscWheredegree=79ordegree=89ordegree=99;或：select*fromscWheredegreein(79,89,99);(18)查询名字中第二个字是“小”字的男生的学生姓名和地址。selectsname,saddressfromstudentWheresnamelike’_小%’andssex=’男’;(19)查询名称以“计算机”开头的课程名称。selectcnamefromcourseWherecnamelike’计算机%’;(20)查询计算机工程系和软件工程系的学生信息。select*fromstudentWheresdept=’计算机工程系’orsdept=’软件工程系’;或：selectcnamefromcourseWherecnamein(’计算机工程系’,’软件工程系’);三、思考题1、LIKE的通配符有哪些？分别代表什么含义？%：代表任意多个任意字符；_：代表1个任意字符。2、知道学生的出生日期，如何求出其年龄？利用表达式year(curdate())-year(sbirthday)，可求出学生的年龄。3、IS能用“=”来代替吗？不能关键字ALL和DISTINCT有什么不同的含义？ALL：保留查询结果中的所有行，包括取值重复的行；DISTINCT：去掉查询结果中的重复行。四、实验总结：1、收获2、存在的问题

实验4分组与排序子句的使用一、实验目的：(1)利用GROUPBY子句对查询结果分组。(2)利用ORDERBY子句对查询结果排序。(3)掌握聚集函数的使用方法。二、实验内容及步骤在Gradem数据库中完成下面查询：(1)统计有学生选修的课程的门数。selectcount(distinctcno)fromsc;(2)计算“c01”课程的平均成绩。selectavg(degree)fromscwherecno=’c01’;或：selectavg(degree)fromscgroupbycnohavingcno=’c01’;(3)查询选修了“c03”课程的学生的学号及其成绩，查询结果按分数降序排列。selectsno,degreefromscwherecno=’c03’orderbydegreedesc;(4)查询各个课程号及相应的选课人数。selectcno,count(*)fromscgroupbycno;(5)统计每门课程的选课人数和最高分。selectcno,count(*),max(degree)fromscgroupbycno;(6)统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。selectsno,count(*),sum(degree)fromscgroupbysnoorderbycount(*)desc;(7)查询选修了3门以上课程的学生学号。selectsnofromscgroupbysnohavingcount(*)>3;(8)查询成绩不及格的学生学号及课号，并按成绩降序排列。selectsno,cnofromscwheredegree<60orderbydegreedesc;(9)查询至少选修一门课程的学生学号。selectdistinctsnofromsc;或：selectsnofromscgroupbysnohavingcount(*)>=1;(10)统计输出各系学生的人数selectsdept,count(*)fromstudentgroupbysdept;(11)统计各系的男、女生人数。selectsdept,ssex,count(*)fromstudentgroupbysdept,ssex;(12)统计各班人数。selectclassno,count(*)fromstudentgroupbyclassno;(13)统计各班男、女生人数。selectclassno,ssex,count(*)fromstudentgroupbyclassno,ssex;(14)统计各系的老师人数，并按人数升序排序。selecttdept,count(*)fromteachergroupbytdeptorderbycount(*);(15)统计不及格人数超过10人的课程号。selectcnofromscwheredegree<60groupbycnohavingcount(*)>10;(16)统计选修人数超过10人的课程号。selectcnofromscgroupbycnohavingcount(*)>10;(17)查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序。select*fromstudentwheresdept=’软件工程系’andssex=’男’orderbysbirthdayasc,saddressdesc;三、思考题(1)聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUPBY子句中？可以直接用在SELECT子句和HAVING子句；不能直接用在WHERE子句和GROUPBY子句。(2)WHERE子句与HAVING子句有何不同？作用对象不同：WHERE子句作用于被查询的表；HAVING子句作用于结果组，选择满足条件的结果组。四、实验总结：1、收获2、存在的问题

实验5多表连接查询一、实验目的(1)掌握SELECT语句在多表查询中的应用。(2)掌握多表连接的几种连接方式及应用。二、实验内容及步骤在Gradem数据库中完成下面查询：(1)查询电子工程系女学生的学生学号、姓名及考试成绩。selecta.sno,sname,degreefromstudenta,scbwherea.sno=b.snoandsdept=’电子工程系’andssex=’女’;(2)查询“孙小玲”同学所选课程的成绩。(不考虑重名)selectdegreefromstudenta,scbwherea.sno=b.snoandsname=’孙小玲’;(3)查询“李新”老师所授课程的课程名称。selectcnamefromteachera,courseb,teachingcwherea.tno=c.tnoando=oandtname=’李新’;(4)查询女教师所授课程的课程号及课程名称。selectb.*fromteachera,courseb,teachingcwherea.tno=c.tnoando=oandtsex=’女’;(5)查询至少选修一门课程的女学生姓名。selectdistinctsnamefromstudenta,scbwherea.sno=b.snoandssex=’女’;或：selectsnamefromstudenta,scbwherea.sno=b.snoandssex=’女’groupbya.snohavingcount(*)>=1;(6)查询姓“王”的学生所学的课程名称。selectcnamefromstudenta,courseb,sccwherea.sno=c.snoando=oandsnamelike‘王%’;(7)查询选修“JAVA程序设计基础”课程且成绩在80～90分之间的学生学号及成绩。selectsno,degreefromcoursea,scbwhereo=oandcname=’JAVA程序设计基础’anddegreebetween80and90;(8)查询选修“JAVA程序设计基础”课程且成绩在80～90分之间的学生姓名及成绩。selectsname,degreefromstudenta,courseb,sccwherea.sno=c.snoando=oandcname=’JAVA程序设计基础’anddegreebetween80and90;(9)查询课程成绩及格的男同学的学生信息及课程号与成绩。selecta.*,cno,degreefromstudenta,scbwherea.sno=b.snoandssex=’男’anddegree>=60;(10)查询选修“c04”课程的学生的平均年龄。selectavg(year(curdate())-year(sbirthday))fromstudenta,scbwherea.sno=b.snoandcno=’c04’;(11)查询学习课程名为“高等数学”的学生学号和姓名。selecta.sno,snamefromstudenta,scb,coursecwherea.sno=b.snoando=oandcname=’高等数学’;(12)查询“张青”教师任课的课程号，选修其课程的学生的学号和成绩。selectb.*fromteachera,scb,teachingcwherea.tno=c.tnoando=oandtname=’张青’;(13)查询“张青”教师任课的课程号，选修其课程的学生的姓名。selecto,snamefromteachera,scb,teachingc,studentdwherea.tno=c.tnoando=oandb.sno=d.snoandtname=’张青’;(14)查询在第3学期所开课程的课程名称及成绩。selectcname,degreefromcoursea,scb,teachingcwhereo=oando=oandcterm=3;(15)查询“c02”号课程不及格的学生信息。selecta.*fromstudenta,scbwherea.sno=b.snoandcno=’c02’anddegree<60;(16)查询软件工程系成绩在90分以上的学生姓名、性别和课程名称。selectsname,ssex,cnamefromstudenta,scbwherea.sno=b.snoandsdept=’软件工程系’anddegree>=90;(17)查询同时选修了“a01”和“a02”课程的学生姓名和成绩。selectsname,b.degree,c.degreefromstudenta,scb,sccwherea.sno=b.snoandb.sno=c.snoando=’a01’ando=’a02’;三、思考题(1)指定一个较短的别名有什么好处？为了简化表名(2)内连接与外连接有什么区别？内连接中，只有在两个表中匹配的行才能在结果集中出现；外连接中，可以只限制一个表，而对另一个不加限制。全外连接可以对两个表都不加限制。(3)“=”与IN在什么情况下作用相同？WHERE条件中用于判断的值只有一个时，“=”与“IN”作用是相同的。四、实验总结：1、收获2、存在的问题

实验6嵌套查询一、实验目的(1)掌握嵌套查询的使用方法。(2)掌握相关子查询与嵌套子查询的区别。(3)掌握带IN谓词的子查询的使用方法。(4)掌握带比较运算符的子查询的使用方法。二、实验内容及步骤在Gradem数据库中利用嵌套查询完成下面查询操作：(1)查询“张旭光”同学所选课程的成绩。selectdegreefromscwheresno=(selectsnofromstudentwheresname=’张旭光’);(2)查询“李新”老师所授课程的课程名称。selectcnamefromcoursewherecnoin(selectcnofromteachingwheretno=(selecttnofromteacherwheretname=’李新’));(3)查询女教师所授课程的课程号及课程名称。select*fromcoursewherecnoin(selectcnofromteachingwheretnoin(selecttnofromteacherwheretsex=’女’));(4)查询姓“王”的学生所学的课程名称。selectcnamefromcoursewherecnoin(selectcnofromscwheresnoin(selectsnofromstudentwheresnamelike‘王%’));(5)查询选修“大学英语”课程且成绩在80～90分之间的学生学号及成绩。selectsno,degreefromscwheredegreebetween80and90andcno=(selectcnofromcoursewherecname=’大学英语’);(6)查询学习课程名为“高等数学”的学生学号和姓名。selectsno,snamefromstudentwheresnoin(selectsnofromscwherecno=(selectcnofromcoursewherecname=’高等数学’));或：selectsno,snamefromstudentwheresnoin(selectsnofromsca,coursebwhereo=oandcname=’高等数学’);或：selectsno,snamefromstudenta,scbwherea.sno=b.snoandcno=(selectcnofromcoursewherecname=’高等数学’);(7)查询选修“C04”课程的学生的平均年龄。selectavg(year(curdate())-year(sbirthday))fromstudentwheresnoin(selectsnofromscwherecno=’c04’);(8)查询在第3学期所开课程的课程名称及成绩。selectcname,degreefromcoursea,scbwhereo=oandcnoin(selectcnofromteachingwherecterm=3)(9)查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。select*fromscwherecnoin(selectcnofromteachingwheretno=(selecttnofromteacherwheretname=’钱军’))(10)查询与“孙小玲”同一个系的同学姓名。selectsnamefromstudentwheresdept=(selectsdeptfromstudentwheresname=’孙小玲’);(11)查询学号比“于晓梅”同学大，而出生日期比他小的学生姓名。selectsnamefromstudentwheresno>(selectsnofromstudentwheresname=’于晓梅’)andsbirthday<(selectsbirthdayfromstudentwheresname=’于晓梅’);(12)查询出生日期大于所有女同学出生日期的男同学的姓名及系别。selectsname,ssexfromstudentwheressex=’男’andsbirthday>all(selectsbirthdayfromstudentwheressex=’女’);(13)查询成绩比平均成绩高的学生的学号及成绩。selectsno,degreefromscwheredegree>(selectavg(degree)fromsc);(14)查询成绩比该课程平均成绩高的学生的学号及成绩。selectsno,degreefromscawheredegree>(selectavg(degree)fromscbwhereo=o);(15)查询不讲授“C01”课的教师姓名。selecttnamefromteacherwheretnonotin(selecttnofromteachingwherecno=’c01’);(16)查询“C02”号课程不及格的学生信息。select*fromstudentwheresnoin(selectsnofromscwherecno=’c02’anddegree<60);(17)查询没有选修“C02”课程的学生学号及姓名。selectsno,snamefromstudentwheresnonotin(selectsnofromscwherecno=’c02’);(18)查询选修了“思想品德教育”课程的学生学号、姓名及系别。selectsno,sname,ssexfromstudentwheresnoin(selectsnofromscwherecno=(selectcnofromcoursewherecname=’思想品德教育’));三、思考题(1)子查询一般分为几种？两种：相关子查询和不相关子查询(2)相关子查询的执行过程是什么？1）子查询为父查询的每一个元组（行）执行一次，父查询将子查询引用列的值传给子查询；2）如果子查询的任何行与其匹配，则父查询取此行放入结果表；3）回到1），直到处理完外部表的每一行。四、实验总结：1、收获2、存在的问题

实验7数据更新一、实验目的：(1)掌握利用INSERT命令实现对表数据的插入操作。(2)掌握利用UPDATE命令实现对表数据的修改操作。(3)掌握利用DELETE命令实现对表数据的删除操作。二、实验内容及步骤在Gradem数据库中完成下面操作：注意：在完成实验题目前先备份Student、SC、Course这3个表，备份表名自定。(1)向Student表中插入记录("2005020301"，"张静"，"1981-3-21"，"女"，"计算机系"，"电子商务")。(2)插入学号为“2005030201”、姓名为“李四”的学生信息。(3)将学号为“2007010101”的学生姓名改为“张华”，系别改为“数字媒体系”，专业改为“多媒体技术”。(4)将“王佳丽”同学的专业改为“计算机信息管理”。(5)删除学号为“2005030201”的学生记录。(6)删除“电子工程系”所有学生的选课记录。(7)删除SC表中尚无成绩的选课记录。(8)把“张涛”同学的选课记录全部删除。三、思考题(1)DROP命令和DELETE命令的本质区别是什么？DROP：删除表，包括表的数据和表的结构；DELETE：删除表中的数据。(2)利用INSERT、UPDATE和DELETE命令可以同时对多个表进行操作吗？不可以，只能同时对一个表进行操作四、实验总结：1、收获2、存在的问题

实验8数据查询与维护综合实验一、实验目的：(1)进一步掌握SELECT语句的功能及使用方法。(2)进一步掌握SELECT语句中各种查询条件的表示方法；(3)进一步掌握连接查询和嵌套查询的综合应用；(4)进一步掌握利用INSERT命令实现对表数据的插入操作；(5)进一步掌握利用UPDATE命令实现对表数据的修改操作；(6)进一步掌握利用DELETE命令实现对表数据的删除操作。二、实验内容及步骤在Gradem数据库中完成下面操作：(1)查询以‘DB_’开头，且倒数第3个字符为‘i’的课程的详细情况；（提示：用转义字符）select*fromcoursewherecnamelike'db/_%i__'escape'/';(2)查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；selectsname,sno,cno,cnamefromstudenta,courseb,sccwherea.sno=c.snoando=oandsnamelike'_阳%';思考能否用嵌套查询？不能(3)列出选修了‘高等数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；selecta.sno,sname,sdept,o,degreefromstudenta,courseb,sccwherea.sno=c.snoando=oand(cname=’高等数学’orcname=’大学英语’)或：selecta,sno,sname,sdept,cno,degreefromstudenta,scbwherea.sno=b.snoandcnoin(selectcnofromcoursewherecname=’高等数学’orcname=’大学英语’)（4）列出选修了‘高等数学’和‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；（提示：用自连接）selecta.sno,sname,sdept,o,o,b.degree,c.degreefromstudenta,scb,scc,coursed,courseewherea.sno=b.snoandb.sno=c.snoando=oando=oandame='高等数学'andame='大学英语';或：selecta.sno,sname,sdept,cno,degreefromstudenta,scbwherea.sno=b.snoandb.snoin(selectsnofromscb,coursecwhereo=oandcname='高等数学')andb.snoin(selectsnofromscb,coursecwhereo=oandcname='大学英语')(5)只选修“大学英语”和“高等数学”两门课程的学生的基本信息；Selecta.*fromstudenta,scbWherea.sno=b.snoandb.snoin(selectsnofromsca,coursebwhereo=oandcname='大学英语')andb.snoin(selectsnofromsca,coursebwhereo=oandcname='高等数学')Groupbyb.snohavingcount(*)=2;或：Select*fromstudentWheresnoin(selectsnofromsca,coursebwhereo=oandcname='大学英语')andsnoin(selectsnofromsca,coursebwhereo=oandcname='高等数学')andsnoin(selectsnofromscGroupbysnohavingcount(*)=2);或：selecta.*fromstudenta,scb,scc,coursed,courseewherea.sno=b.snoandb.sno=c.snoando=oando=oandame='大学英语'andame='高等数学'anda.snoin(selectsnofromscgroupbysnohavingcount(*)=2);（第3个答案需要大家好好理解，最后的“anda.snoin(selectsnofromscgroupbysnohavingcount(*)=2)”不能直接写成“groupbysnohavingcount(*)=2”）(6)查询缺少成绩的所有学生的详细情况；selecta.*fromstudenta,scbwherea.sno=b.snoanddegreeisnull;selecta.*fromstudentaleftjoinscbwherea.sno=b.snoanddegreeisnull;(7)查询与‘张玉忠’(假设姓名唯一)年龄不同的所有学生的信息；select*fromstudentwhereyear(curdate())-year(sbirthday)<>(selectyear(curdate())-year(sbirthday)fromstudentwheresname=’张玉忠’);(8)查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；selecta.sno,sname,avg(degree)fromstudenta,scbwherea.sno=b.snogroupbya.snohavingavg(degree)>(selectavg(degree)fromstudenta,scbwherea.sno=b.snoandsname=’张力’)(9)列出只选修一门课程的学生的学号、姓名、院系及成绩；selecta.sno,sname,sdept,degreefromstudenta,scbwherea.sno=b.snogroupbya.snohavingcount(*)=1;(10)查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；selecta.sno,sname,cnofromstudenta,scbwherea.sno=b.snoandcnoin(selectcnofromstudenta,scbwherea.sno=b.snoandsname=’张力’)(11)至少选修“数据库”或“数据结构”课程的学生的基本信息；selecta.*fromstudenta,courseb,sccwherea.sno=c.snoando=oand(cname=’数据库’orcname=’数据结构’);或：selecta.*fromstudenta,courseb,sccwherea.sno=c.snoando=oandcnamein(’数据库’,’数据结构’);或：select*fromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewherecname=’数据库’orcname=’数据结构’);(12)列出所有课程被选修的详细情况，包括课程号、课程名、学号及成绩；selecto,cname,sno,degreefromcoursea,scbwhereo=o;(13)查询只被一名学生选修的课程的课程号、课程名；selectcno,cnamefromcoursewherecnoin(selectcnofromscgroupbycnohavingcount(*)=1)(14)使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；selectsno,snamefromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewherecname=’数据结构’);(15)使用嵌套查询查询其它系中年龄小于计算机系的某个学生的学生姓名、年龄和系别；Selectsname,year(curdate())-year(sbirthday),sdeptfromstudentwheresdept<>’计算机系’andyear(curdate())-year(sbirthday)<any(selectyear(curdate())-year(sbirthday)fromstudentwheresdept=’计算机系’);或：Selectsname,year(curdate())-year(sbirthday),sdeptfromstudentwheresdept<>’计算机系’andyear(curdate())-year(sbirthday)<(selectmax(year(curdate())-year(sbirthday))fromstudentwheresdept=’计算机系’);(16)使用ANY、ALL查询，列出其他院系中比CS系所有学生年龄小的学生；Select*fromstudentwheresdept<>’CS系’andyear(curdate())-year(sbirthday)<all(selectyear(curdate())-year(sbirthday)fromstudentwheresdept=’CS系’);(17)分别使用连接查询和嵌套查询，列出与‘张力’在一个系的学生的信息；selecta.*fromstudenta,studentbwherea.sdept=b.sdeptandb.sname=’张力’;select*fromstudentwheresdept=(selectsdeptfromstudentwheresname=’张力’);(18)使用集合查询列出软件工程系的学生以及性别为女的学生名单；select*fromstudentwheresdept=’软件工程系’unionselect*fromstudentwheressex=’女’;四、实验总结：1、收获2、存在的问题

实验9索引、视图的创建与管理一、实验目的：(1)理解索引的概念与类型。(2)掌握创建、更改、删除索引的方法。(3)掌握维护索引的方法。(4)理解视图的概念。(5)掌握创建、更改、删除视图的方法。(6)掌握使用视图来访问数据的方法。二、实验内容及步骤1、使用Navicat图形工具创建、管理索引(1)在数据库gradem下创建workinfo表。创建表的同时在id字段上创建名为index_id的唯一性索引。workinfo表结构内容如下表所示：字段名字段描述数据类型主键外键非空唯一id编号Char(10)是否是是sname职位名称VarChar(20)否否是否type职位类别VarChar(10)否否否否address工作地址VarChar(50)否否否否wages工资Int否否否否contents工作内容TinyText否否否否extra附加信息Text否否否否【操作方法】如下图：(2)创建索引。为sname字段创建索引index_name，在type和address字段上创建名为index_t的组合索引。(3)删除索引。删除workinfo表的唯一性索引index_id。2、使用SQL语句创建、管理索引(1)利用createtable语句在gradem数据库中创建数据表writers，其表结构内容如下表所示。在创建表的同时在w_id字段上添加名称为uniquex的唯一索引。字段名字段描述数据类型主键外键非空唯一W_id编号Char(10)是否是是w_name作者姓名VarChar(20)否否是否w_address作者地址VarChar(50)否否否否w_age年龄Int否否是否w_note说明Text否否否否(2)使用createindex语句在w_address和w_age字段上建立名称为multiidx的组合索引。Createindexmultiidxonwriters(w_address,w_age);(3)删除索引。利用altertable语句将索引uniquex删除.Altertablewritersdropindexuniquex;利用dropindex语句将nultiidx索引删除。Dropindexmultiidxonwriters;3、使用SQL语句创建、管理视图(1)创建视图。①创建一个名为sc_view1的视图，从数据库gradem的sc表中查询出成绩大于90分的所有学生选修成绩的信息（学号、课程号、成绩）。Createviewsc_view1asselect*fromscwheredegree>90;②创建一个名为sc_view2的视图，从数据库gradem的sc表中查询出成绩小于80分的所有学生的学号、课程号、成绩信息。Createviewsc_view2asselect*fromscwheredegree<80;③创建一个名为sc_view3的视图，由数据库gradem的student、course、sc表创建一个显示“20070303”班学生选修课程(包括学生姓名、课程名称、成绩)的视图。Createviewsc_view3asselectsname,cname,degreefromstudenta,courseb,sccWherea.sno=c.snoando=oandclassno=’20070303’;④创建一个从视图sc_view1中查询出课程号“c01”的所有学生的视图sc_view4。Createviewsc_view4asselect*fromsc_view1wherecno=’c01’;(2)修改视图的定义。修改视图sc_view1，使其从数据库gradem中查询出在第3学期开课，且成绩大于90分的所有学生选修成绩的信息（学号、课程号、成绩）。Alterviewsc_view1asselect*fromscwheredegree>90andcnoin(selectcnofromteachingwherecterm=3);或：Alterviewsc_view1asselecta.*fromsca,teachingbwhereo=oandcterm=3anddegree>90;(3)视图的删除。①将视图sc_view1删除。Dropviewsc_view1;或：dropviewifexistssc_view1;(4)管理视图中的数据。①从视图sc_view2中查询出学号为“2007030125”、课程号为“a01”的学生选修成绩的信息。Select*fromsc_view2wheresno=’2007030125’andcno=’a01’;②将视图sc_view2中学号为“2007030122”、课程号为“c02”的成绩改为87。Updatesc_view2setdegree=87wheresno=’2007030122’andcno=’a02’;③从视图sc_view2中将学号为“2007030123”、课程号为“a01”的学生信息删除。Deletefromsc_view2wheresno=’2007030123’andcno=’a01’;三、思考题(1)视图上能创建索引吗？不能(2)向视图中插入的数据能进入到基本表中去吗？能(3)修改基本表的数据会自动反映到相应的视图中去吗？会四、实验总结：1、收获2、存在的问题

实验10SQL编程基础一、实验目的：(1)理解常量与变量的概念；(2)掌握常量与变量的使用方法；(3)掌握表达式的使用方法；(4)理解SQL流程控制语句的使用；(5)掌握常用函数的功能及使用方法；二、实验内容及步骤(1)定义一个整型局部变量iAge和可变长字符型局部变量vAddress，并分别赋值20和“中国山东”，最后输出变量的值，并要求通过注释对批处理中语句的功能进行说明。delimiter//CREATEPROCEDURES1()BEGIN/*创建局部变量，并赋值*/declareiageint;declarevaddressvarchar(20);setiage=20;setvaddress="中国山东";/*输出变量的值*/selectiage;selectvaddress;end//delimiter;(2)通过全局变量获得当前MySQL服务器的版本。SELECT@@versionAS'当前MYSQL版本';(3)求1～100的偶数和。delimiter//CREATEPROCEDURES2()BEGINSET@i=0,@sum=0;while@i<=100DOBEGINSET@sum=@sum+@i;SET@i=@i+2;END;ENDWHILE;SELECT@sum;END//delimiter;(4)对于字符串“WelcometoMySQL”，进行以下操作。（查阅联机丛书）①将字符串转换为全部大写。SET@str='WelcometoMySQL';SELECTUPPER(@str);②将字符串转换为全部小写。SET@str='WelcometoMySQL';SELECTLOWER(@str);③去掉字符串前后的空格。SET@str='WelcometoMySQL';SELECTTRIM(@str);④截取从第12个字符开始的10个字符。SET@str='WelcometoMySQL';SELECTSUBSTRING(@str,12,10);(5)使用日期型函数，获得输出结果见下表（参考）。输出结果：（日期使用系统当前日期）年份月份日期星期几20231127星期一delimiter//CREATEPROCEDURES3()BEGINSET@date=CURDATE();SET@weekd=WEEKDAY(@date);SET@ww='星期一';CASEWHEN@weekd='0'THENSET@ww='星期一';WHEN@weekd='1'THENSET@ww='星期二';WHEN@weekd='2'THENSET@ww='星期三';WHEN@weekd='3'THENSET@ww='星期四';WHEN@weekd='4'THENSET@ww='星期五';WHEN@weekd='5'THENSET@ww='星期六';WHEN@weekd='5'THENSET@ww='星期七';ENDCASE;SELECTYEAR(@date)AS'年份',MONTH(@date)AS'月份',DAY(@date)AS'日期',@wwAS'星期几';END//delimiter;(6)根据sc表中的成绩进行处理：成绩大于等于60分的显示“及格”，小于60分的显示“不及格”，为NULL的显示“无成绩”。SELECTsno,cno,degree,(caseWHENdegree>=60THEN'及格'WHENdegree<60THEN'不及格'ELSE'成绩为空'END)AS'level'FROMsc;或:delimiter//createprocedureqqq1()BEGINdeclaredoneintdefault0;declarev_snovarchar(10);declarev_cnovarchar(4);declarev_degreedecimal(28);declarecjvarchar(8);declaret_cursorcursorforselectsno,cno,degreefromsc;declarecontinuehandlerfornotfoundsetdone=1;setdone=0;opent_cursor;fetch_loop:loopbeginfetcht_cursorintov_sno,v_cno,v_degree;ifv_degree>60THENsetcj='及格';elseifv_degreeisnotnullthensetcj='不及格';elsesetcj='无成绩';endif;endif;end;ifdone=1thenleavefetch_loop;elseselectv_sno,v_cno,v_degree,cj;endif;endloopfetch_loop;end//delimiter;(7)利用SQL条件语句，在student表中查找“李艳”同学的信息，若找到，则显示该生的学号、姓名、班级名称及班主任，否则显示“查无此人”。delimiter//CREATEPROCEDUREaa4()BEGINIF(SELECTsnameFROMstudentWHEREsname='李艳')isnullTHENBEGINSELECT'查无此人'AS'学生信息';SELECT*FROMstudentWHEREsname='李艳';END;ELSESELECTsname,sno,classnoFROMstudentWHEREsname='李艳';ENDIF;END//delimiter;三、思考题(1)全局变量与局部变量的区别是什么？全局变量主要影响整个MySQL实例的全局设置局部变量的作用范围实在BEGIN…END语句块中(2)使用变量的前提是什么？用户变量和局部变量需要提前声明，且变量名不能与命令和函数名相同。四、实验总结：1、收获2、存在的问题

实验11-12存储过程/存储函数的创建与使用一、实验目的：(1)理解存储过程和函数的概念。(2)掌握创建各种存储过程和函数的方法。(3)掌握执行存储过程和函数的方法。(4)掌握查看、修改、删除存储过程和函数的方法。二、实验内容及步骤1)使用SQL语句创建存储过程(1)创建不带参数的存储过程。①创建一个从student表查询班级号为“20200101”班的学生资料的存储过程proc_1，其中包括学号、姓名、性别、出生年月等。调用proc_1存储过程，观察执行结果。/*创建存储过程*/delimiter//CREATEPROCEDUREproc_1()BEGINSELECTsno,sname,ssex,sbirthdayFROMstudentWHEREclassno='20200101';END//delimiter;/*调用存储过程*/callproc_1();②在gradem数据库中创建存储过程proc_2，要求实现如下功能：查询存在不及格情况的学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、系别等。调用proc_2存储过程，观察执行结果。/*创建存储过程*/delimiter//CREATEPROCEDUREproc_2()BEGINSELECTa.sno,sname,ssex,o,cname,degree,sdeptFROMstudenta,scb,coursecWHEREa.sno=b.snoANDo=oANDdegree<60;END//delimiter;/*调用存储过程*/callproc_2();(2)创建带输入参数的存储过程。创建一个从student表查询学生资料的存储过程proc_3，其中包括学号、姓名、性别、出生年月、班级等。要查询的班级号通过执行语句中的输入参数传递给存储过程。并调用该存储过程查询班级号为“20200103”的学生的相关资料。/*创建存储过程*/delimiter//CREATEPROCEDUREproc_3(classVARCHAR(8))BEGINSELECTsno,sname,ssex,sbirthday,classnoFROMstudentWHEREclassno=class;END//delimiter;/*调用存储过程*/callproc_3('20200103');(3)创建带输出参数的存储过程。创建一个从sc表查询某一门课程考试成绩总分的存储过程proc_4。在以上存储过程中，要查询的课程号通过执行语句中的输入参数cno_tmp传递给存储过程，sum_degree作为输出参数用来存放查询得到的总分。执行此存储过程，计算“a01”课程的考试成绩总分，观察执行结果。/*创建存储过程*/delimiter//CREATEPROCEDUREproc_4(INcno_tmpvarchar(4),OUTsum_degreeINT)BEGINSELECTSUM(degree)INTOsum_degreeFROMscWHEREcno=cno_tmp;END//delimiter;/*调用存储过程*/callproc_4('a01',@num);SELECT@num;2)使用SQL语句查看、修改和删除存储过程(1)查看存储过程。查看存储过程proc_1、proc_3的定义。SHOWPROCEDURESTATUSLIKE'proc_1';SHOWPROCEDURESTATUSLIKE'proc_3';(2)删除存储过程。将存储过程proc_1删除。DROPPROCEDUREproc_1;三、实验总结：1、收获2、存在的问题

实验12触发器的创建与管理一、实验目的：(1)理解触发器的概念与类型。(2)理解触发器的功能及工作原理。(3)掌握创建、更改、删除触发器的方法。(4)掌握利用触发器维护数据完整性的方法。二、实验内容及步骤1)使用SQL语句创建触发器(1)创建插入触发器并进行触发器的触发执行。为表sc创建一个插入触发器student_sc_insert，当向表sc插入数据时，先判断插入的学号是否有效地存在于student表中，如果插入的学号在student表中不存在，先在student表中插入该学号，再向表sc插入数据。delimiter//createtriggerstudent_sc_insertbeforeinsertonscforeachROWBEGINif(selectsnofromstudentwheresno=new.sno)isNULLTHENin