MySQL:实验一:交互式SQL

一．实验目的（1）掌握SQL数据定义语句（2）掌握SQL数据简单查询语句（3）掌握SQL数据复杂查询语句（4）掌握SQL索引操作语句（5）掌握SQL视图操作语句（6）了解实验平台DBMS的安装与基本使用二．实验内容1.创建学生课程数据库S-C（1）创建学生表（STUDENT表），语句如下： create table Student( id int auto_increment primary key, sno char(9) unique not null, sname varchar(20) unique not null, ssex char(6) default '女', sage tinyint, sdept enum('CS','IS','MA'), saddr json, tel char(11) unique, idno char(18) not null unique, pic blob, resume text, interest set('唱歌','跳舞','弹吉他') );在MYSQL命令行下的运行截图如下：（2）创建课程表（COURSE表），语句如下： create table Course ( cno char(4) primary key, cname char(40) not null, cpno char(4), ccredit smallint, foreign key (cpno) references Course (cno) );在MYSQL命令行下的运行截图如下：（3）创建选课表（SC表），语句如下： create table SC ( sno char(9), cno char(4), grade smallint, primary key (sno,cno), foreign key (sno) references Student (sno), foreign key (cno)references Course (cno) );在MYSQL命令行下的运行截图如下：2.修改表结构（1）将student表的sage列修改为sbirthday,数据类型改为date类型，可空。语句如下：alter table Student change sage sbirthday date null;在MYSQL命令行下的运行截图如下：（2）删除student 表中的sbirthday列。语句如下：alter table Student change drop sbirthday;在MYSQL命令行下的运行截图如下：（3）给student表的sept列添加默认值“CS”。语句如下：alter table Student alter sdept set default “CS”;在MYSQL命令行下的运行截图如下：（4）将student表的表名修改为S。语句如下：alter table Student rename to S;在MYSQL命令行下的运行截图如下：（5）给course表的cname列添加unique约束。语句如下：alter table Course add unique (cname);在MYSQL命令行下的运行截图如下：（6）删除studetn表。语句如下：drop table Student;在MYSQL命令行下的运行截图如下：3.数据查询操作（1）查询至少选修了一门直接先行课为5号课的课程的学生姓名。语句如下：select snamefrom Student,SC,Coursewhere Student.sno=SC.sno and SC.cno=Course.cno and cpno='5';在MYSQL命令行下的运行截图如下：（2）求仅选修了2号课程的学生学号。语句如下：select snofrom SCwhere cno='2';在MYSQL命令行下的运行截图如下：查询选修了全部课程的学生号码和姓名。语句如下：select sname,snofrom Studentwhere not exists(select * from Course where not exists(select *from SCwhere SC.sno=Student.sno and SC.cno=Course.cno));在MYSQL命令行下的运行截图如下：（4）查询选修了2号课程的学生姓名。语句如下：select snamefrom Student,SCwhere Student.sno=SC.sno and cno='2';在MYSQL命令行下的运行截图如下：（5）检索选课的学生中没有任何一门课程在84分以下的学生的信息，包括学号、姓名。语句如下：select *from Student where not exists(select *from SCwhere Student.sno=sno and grade<'84');在MYSQL命令行下的运行截图如下：检索学生成绩得过100分的课程的名称和学分。语句如下：select cname,ccreditfrom Coursewhere exists(select *from SCwhere cno=Course.cno and grade='100');在MYSQL命令行下的运行截图如下：（7）检索年龄大于19、同时有一门课程成绩在85分以上的学生信息，包括学号、姓名和系别。语句如下：select *from Studentwhere exists(select *from SCwhere sno=Student.sno and grade>'85' and sage>'19');在MYSQL命令行下的运行截图如下：（8）找出选修了学号为95002的学生所选修的某一门课的学生的学号。语句如下： select distinct snofrom SC xwhere cno in(select cnofrom SC ywhere sno='95002');在MYSQL命令行下的运行截图如下：找出选修了学号为95002的学生所选修的全部课程的学生学号。语句如下： select distinct sno from SC x where not exists( select * from SC y where not exists( select * from SC z where sno=x.sno and cno=y.cno and sno='95002' ) );在MYSQL命令行下的运行截图如下：找出至少同时选修了数据库和数据结构课程的学生的学号。语句如下：select snofrom SCwhere exists(select *from Coursewhere SC.cno=Course.cno and cname='数据库')and exists(select *from Coursewhere SC.cno=Course.cno and cname='数据结构');在MYSQL命令行下的运行截图如下：4.数据更新操作（1）插入一条学生基本信息，要求每列都给一个合理的值。语句如下：insertinto studentvalues(null,'95004','李华', '男','19','CS',null, null,'362330200409080765',null,null,'唱歌');在MYSQL命令行下的运行截图如下：（2）插入一条选课记录，给出必要的几个字段值。语句如下：insertinto scvalues('95004','1',null);在MYSQL命令行下的运行截图如下：（3）创建一个新的选课表，把所有选2号课程的选课记录插入到新的选课表中。语句如下：create table sc_cno2(sno char(9),cno char(4),grade smallint);insertinto sc_cno2select sc.sno,cno,gradefrom sc,studentwhere sc.sno=student.sno and cno='2';在MYSQL命令行下的运行截图如下：创建一个选课信息统计表，记录每个学生超过或等于其平均成绩的课程信息。语句如下：create table sc_grade_course(sno char(9),cno char(4),cname char(40),cpno char(4),ccredit smallint,grade smallint);insertinto sc_grade_courseselect sno,sc.cno,cname,cpno,ccredit,gradefrom sc,coursewhere grade>=(select avg(grade)from sc xwhere x.sno=sno)and sc.cno=course.cno;在MYSQL命令行下的运行截图如下：（5）将计算机科学与技术系全体学生的成绩置零。语句如下：update scset grade=0where sno in(select snofrom studentwhere sdept='cs');在MYSQL命令行下的运行截图如下：（6）删除学生表中刘晨的基本信息。语句如下：delete from student where sname='刘晨';在MYSQL命令行下的运行截图如下：5.视图的相关操作（1）建立信息系学生的视图IS_STUDENT，并要求进行修改和插入操作时需保证该视图只有信息系的学生。语句如下：create view is_studentasselect sno,sname,sagefrom studentwhere sdept='is'with check option;在MYSQL命令行下的运行截图如下：（2）向信息系学生视图中插入一条新的学生记录，其中学号为“201215129”，姓名为“赵新”，年龄为20岁。插入之后，查询验证是否成功。语句如下：insertinto is_studentvalues('201215129','赵新','20');select * from is_student;select* from student;具体见下文的调试解决方法(3)和(4)在MYSQL命令行下的运行截图如下：（3）将学生的学号及平均成绩定义为一个视图S_G；设计SQL语句向该视图插入一条新记录，验证该视图是否可以更新。语句如下：create view s_g(sno,avgg)asselect sno,avg(grade)from sc group by sno;insertinto s_gvalues('95003','88');该视图不可以进行插入和更新操作在MYSQL命令行下的运行截图如下：（4）将信息系中女生记录定义为一个视图F_STUDENT。语句如下：create view f_student(sno,sname,ssex,sage,sdept)asselect *from studentwhere sdept='is' and ssex='女';在MYSQL命令行下的运行截图如下：（5）删除信息系学生的视图IS_STUDENT。语句如下：drop view is_student;在MYSQL命令行下的运行截图如下：三．心得体会1.本次实验项目的收获如下：（1）通过本次实验课程，我学会了使用SQL语句对基本表进行定义(create table)、修改(alter table)和删除(drop table)操作，对视图进行定义(create view)和删除(drop view)操作，并能完成对基本表和视图进行的各种查询(select),以及对其中的数据进行更新操作：插入(insert)、修改(update)和删除(delete)。（2）帮助我更好地理解了数据库关系的完整性，总结如下：用户定义的完整性，指数据库表中的各属性列必须满足某种特定的数据类型或约束；实体完整性，要求每个关系有且仅有一个主键，每一个主键值必须唯一，并且不能为空值；参照完整性，是表与表之间的规则，对于关系相关的表，如果只对其中一个表进行插入、更新或删除操作，就会影响数据的完整性，显示错误。所以对一个表的主键进行操作时，若它是另一个表的外键，要先对另一个表中的外键进行操作。本次实验也让我深刻体会到了交互式SQL 的强大功能，我们可以通过SQL轻松地从大量的数据中获取我们想要的信息，在上机调试时，通过在终端键盘上键入SQL命令对数据库进行操作，SQL统一的语法结构提供了多种不同使用方式的做法，让我们灵活运用，操作方便。2.本次实验项目的不足之处如下：（1）作为初学者，在面对复杂的数据库结构和查询操作时，难以写出高效且准确的SQL语句，SQL的语法和概念可能比较抽象复杂，需要一定的时间和精力去理解掌握。（2）在SQL查询中出现错误，有时难以直接确定错误原因，SQL语句中的错误可能来自多个方面：SQL语法错误，逻辑错误，数据类型不匹配等，可能需要逐一调试排查并解决。（3）在对有相互关系的基本表进行操作时，容易出现破坏参照完整性的错误，需要我们细心地对多个表进行操作，保护关系的参照完整性。3.实验过程中的错误以及调试解决方法（1）数据更新操作中，删除student学生表中刘晨的基本信息时发生了错误，错误如下：该错误是由于student表和sc表之间有sno外键约束,该删除操作会破坏参照完整性，解决方法如下：先删除sc表中刘晨的相关信息再删除student表中刘晨的基本信息，这样就能成功运行了。修改表结构中，删除student表的操作也出现了上述类似的错误，错误如下：解决方法如下：先删除外键约束，再删除该表，这样就能成功运行了。视图的相关操作中，插入新记录时发生了错误，错误如下：这是由于在定义该视图时加上了with check option子句，之后对视图进行插入、修改和删除操作时都会对语句的sdept字段进行判断，如果sdept='is'，才可以进行语句的插入操作，反之就不能进行该操作。而本次语句的插入，没有指定sdept的值，所以该元组的sdept值是null，不等于'is'，就导致插入失败，解决方法如下：先修改该视图，去掉with check option子句，再对视图进行插入记录就可以成功运行了。继（3）之后，对该视图进行查询操作，却没有显示插入的新记录，而基本表student中显示了该新记录，如下：通过对视图进行修改，增加sdept属性列，且插入的记录中sdept='is'来解决，解决方法如下：此外，还有一种最简单的解决方法，直接使用基本表插入，解决方法如下：此时再对视图和基本表进行查询，都能成功地显示插入的新记录。