MYSQL实验二报告

实验要求：

本实验任务基于如下表结构：
学生表：Student_XXX，属性为：(Sno 学号, Sname 姓名, Ssex 性别, Sdept 所在院系, Sbirthday生日），其中学号Sno主码，各列数据类型自行确定。
课程表：Course_XXX，属性为：(Cno 课程号, Cname 课程名, Cpno 先行课, credit 课程学分)，其中课程号Cno主码，各列数据类型自行确定。
学生选课表：SC_XXX (Sno 学号, Cno 课程号, Grade 成绩)其中（Sno、Cno）为主码。

①向Student表中插入2行数据，1行为你的信息，另一行自定。
如李佳慧同学插入的记录为：
insert into student values (‘121002’,‘李佳慧’,‘女’,‘计算机系’,‘2001-07-02’)
②向Course表中插入2行数据，1行为本门课程的信息，另一行自定。
如刘新芳同学插入的数据结构课程信息为：
Insert into Course (Cno，Cname, Cpno, Ccredit)
Values(1021, ‘数据结构’, null, 4)
③向SC表中插入数据，插入你的这门课程的选课信息。
如刘新芳同学插入的选课记录为：
Insert into SC_LiuXinFang(Sno,Cno,Grade) values(2019001121, 1021, 85)
④ 使用Select语句查询三个表的信息，验证插入是否成功
⑤ 采用批量插入的方式，将以下记录分别导入到三个表中
学生表：Student
Sno Sname Sex Sdept SBirthday
121002 李佳慧 女 计算机系 2001/7/2
121001 刘鹏翔 男 计算机系 2000/2/15
121004 周仁超 男 计算机系 1999/3/2
124001 林琴 女 通信学院 2004/2/23
124002 杨春容 女 通信学院 1999/10/5
124003 徐良成 男 通信学院 2001/3/18
124004 刘良成 男 通信学院 2000/12/12
121005 王小红 女 计算机系 2001/12/1
121006 刘晨 男 计算机系 2003/2/14

课程表：Course
Cno Cname Cpno Credit
1004 数据库系统 1204 4
1012 计算机网络 4002 3
4002 数字电路 8001 3
8001 高等数学 NULL 6
1201 英语 NULL 5
1204 程序设计基础 8001 3

选课表：SC
Sno Cno Grade
121001 1004 92
121002 1004 85
121004 1004 56
124001 4002 34
124002 4002 74
124003 4002 87
121001 8001 94
121002 8001 32
121004 8001 81
124001 8001 58
124002 8001 73
124003 8001 21
121001 1201 93
121002 1201 67
121004 1201 63
124001 1201 92
124002 1201 null
124003 1201 86
121002 1204 50
121001 1204 null
121004 1204 90
124001 1204 89
121006 1204 78
121005 1012 68

（2）修改数据
①将姓“刘”的同学出生日期统一修改为‘2000-01-01’。
②将“计算机系”同学的选课信息中的成绩置0。
③使用Select语句查询表的信息，验证修改是否成功

（3）删除数据
①删除“计算机系”的姓“王”的同学。
②使用Select语句查询表的信息，验证删除是否成功

2.基本查询
（1）单表查询
①查询通信学院的所有女学生
②查询所有姓“刘”的学生
③查询各个学院各有多少学生
④查询平均成绩在80分以上的学生学号和平均成绩。
⑤选修课程号为1004和8001的学号。(既1004又8001)
选修课程号为1004或8001的学号。(注意去除重复值)
⑥查询选修了1门且平均分在80分以下课程的学生的学号。
⑦查询有1门以上课程不及格科目的学生学号（较难，选作）

(2) 连接查询
① 查询每个选课学生的学生姓名、课程号和分数。（2表连接）
② 查询学生的学号、姓名、选修课程的课程名及成绩。（3表连接）
③ 查询选修课程为“数字电路”的学生姓名和学号。
④ 对学生表和选课表做自然连接，并输出结果。
⑤ 查询每一门课程的间接先行课的课程号。（先行课的先行课课程号，自身连接查询，P86）
⑥ 以学生为主体显示学生的信息及其选课的信息。(左外连接，学生表为左边，选作)
(3) 嵌套子查询
①查询与刘晨在同一个系学习的学生。
②查询平均分（指某个学生选修的所有课程的平均分）高于“杨春容”的学生学号及成绩。
③查询其他系中存在比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）。
④查询其他院系中比计算机系学生年龄都小的学生姓名及年龄。
⑤查询没有选修“英语”课程的学生姓名。
⑥查询没有不及格科目的学生（SC表中Grade为NULL记为不及格）
⑦查询选修课程超过2门的学生的学号和姓名。

3.视图
（1）创建视图
①创建计算机系学生的视图。
② 创建计算机系学生的视图并要求在进行修改和插入操作时仍需保证该视图只有计算机系的学生。
③ 创建计算机系选修了1004课程的学生视图。
④ 将学生的学号和平均成绩定义为一个视图。
（2）查询视图
①查询计算机系学生视图中的男同学。
② 查询计算机系学生视图中选修了1201课程的学生。
③ 创建计算机系选修了1004课程的学生视图。
（3）更新视图
①将计算机系学生视图中的学号为121001的学生姓名改为自己的名字。
② 在计算机系学生视图中插入一个新的元组，元组内容为自己的学号等。
③ 删除计算机系学生视图中第②步插入的视图删除。

控制台语句：

create database wangmouren default charset utf8mb4;

use wangmouren；

create table IF NOT EXISTS student
(
sno char(20) comment '学号',
sname varchar(20) comment '姓名',
ssex char(2) comment '性别',
sdept varchar(20) comment '院系',
sbirthday date not null comment '出生日期'
)comment '学生表';

alter table student add primary key (sno);

insert into student values('10086','王某人1','男','软件系','2003-03-07'),('10085','王某人','女','软件系','2023-09-20');

select * from student;

create table IF NOT EXISTS course 
(cno char(20) comment '课程号',
cname varchar(20) comment '课程名',
cpno char(20) comment '先行课',
credit int comment '课程学分',
primary key(cno) 
) comment '课程表';

insert into course values('1001','数据库系统','0321',4),('9857','电影影评','1333',1);

select * from course;

create table sc 
(sno char(20) comment '学号',
cno char(25) comment '课程号',
grade int comment '成绩',
primary key(sno,cno)
)comment '课学生选表';

insert into sc values('10086','1001',90),('10085','9857',95);

select * from sc;

insert into student values ('121002','李佳慧','女','计算机系','2001-07-02'),('121001','刘鹏翔','男','计算机系','2000-02-15'),
('121004','周仁超','男','计算机系','1999-03-02'),
('124001','林琴','女','通信学院','2004-02-23'),
('124002','杨春容','女','通信学院','1999-10-05'),
('124003','徐良成','男','通信学院','2001-03-18'),
('124004','刘良成','男','通信学院','2000-12-12'),
('121005','王小红','女','计算机系','2001-12-01'),
('121006','刘晨','男','计算机系','2003-02-14');

insert into course (cno,cname,cpno,credit) values ('1004','数据库系统','1024',4),
('1012','计算机网络','4002',3),
('4002','数字电路','8001',3),
('8001','高等数学',null,6),
('1201','英语',null,5),
('1204','程序设计基础','8001',3);

insert into sc values ('121001','1004',92),
('121002','1004',85),
('121004','1004',56),
('124001','4002',34),
('124002','4002',74),
('124003','4002',87),
('121001','8001',94),
('121002','8001',32),
('121004','8001',81),
('124001','8001',58),
('124002','8001',73),
('124003','8001',21),
('121001','1201',93),
('121002','1201',67),
('121004','1201',63),
('124001','1201',92),
('124002','1201',null),
('124003','1201',86),
('121002','1204',50),
('121001','1204',null),
('121004',1204,90),
('124001',1204,89),
('121006',1204,78),
('121005',1012,68);

update student set sbirthday='2000-01-01' where sname Like '刘%';

select * from student;

 update sc set grade=0 where sno Like '121___';

select * from sc;

delete from student where sname Like '王%'&& sdept='计算机系';

select * from student;

//基本查询
select * from student where ssex='女'&& sdept='通信学院';

select * from student  where sname Like '刘%';

select sdept,count(*) from student group by sdept;

select sno,avg(grade) from sc group by sno having avg(grade)>=80;

select a.sno,a.cno,b.cno from sc a,sc b where (a.cno='8001' && b.cno='1004')&&(a.sno=b.sno);

select distinct sno from sc where cno='8001'||cno='1004' group by sno;

select sno from sc group by sno having avg(grade)<=80&& count(*)=1;

select sno,count(grade) from sc where grade<60 group by sno having count(grade)>1;

select s.sname,sc.cno,sc.grade from student s,sc where sc.sno=s.sno;

select s.sno,s.sname,c.cname,sc.grade from student s,course c,sc where sc.sno=s.sno&&c.cno=sc.cno;

select s.sno,s.sname,c.cname from student s,course c,sc where sc.cno='4002'&&c.cno='4002'&&s.sno=sc.sno;

select s.sno,sname,ssex,sdept,sbirthday,cno,grade from student s,sc where sc.sno=s.sno;

select a.cno,b.cpno from course a,course b where a.cpno=b.cno;

select * from student s left outer join sc on s.sno=sc.sno;

select sname from student where sdept=(select sdept from student where sname='刘晨');

select sno,avg(grade) from sc group by sno having avg(grade)>(select avg(grade) from student s,sc where s.sname='杨春荣'&&s.sno=sc.sno);

select * from student where sdept!='计算机系'&&sbirthday>(select min(sbirthday) from student where sdept='计算机系');

select *,(select DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),student.sbirthday)),'%Y'+0)as age from student where sdept!='计算机系'&&sbirthday>(select max(sbirthday) from student where sdept='计算机系');

select * from student sno not in(select sc.sno from course c,sc where c.cname='英语'&&c.cno=sc.cno);

select s.*,sc.grade from student s,sc where  s.sno not in(select sno from sc where grade is null)&&s.sno=sc.sno;

select s.sno,s.name from student s where s.sno in(select sno from sc group by sno having count(*)>=2);

//视图部分
create or replace view computer_view as select * from student where sdept='计算机系';

create or replace view computer_view as select * from student where sdept='计算机系' with cascaded check option;

create or replace view computer_view1 as select s.*,sc.cno from student s,sc where s.sdept='计算机系'&&s.sno=sc.sno&&sc.cno='1004';

create or replace view computer_view2 as select s.*,avg(sc.grade) from student s,sc where s.sno=sc.sno group by group by sc.sno;

select * from computer_view where ssex='男';

select cv.*,sc.cno from computer_view cv,sc where sc.cno='1201'&&sc.sno=cv.sno;

create or replace view computer_view3 as select s.*,sc.cno from student s,sc where sdept='计算机系'&&sc.cno='1004'&&sc.sno=s.sno;

update computer_view set sname='王某人1' where sno='121001';

insert into computer_view values ('_10085','王某人','男','计算机系','2023-09-20');

delete from computer_view where sno='_10085';

完成收工，good！

虽然“天不生无用之人，地不长无名之草”。但仍谨记“冰冻三尺，非一日之寒”。

执长剑纵马，执妙笔生花，我王某人在此邀请诸位与我共身！