MySql 数据库操作实例

本篇博客主要是将前面的知识，比如 MySql 基本操作、操作1、操作2 等以实例来进行练习。

案例描述

分别创建班级、学生、课程、成绩四表，并随机插入数据，然后根据所提问题进行练习。
其中 班级表 中需包含 班级id 、班级名称 和 班级描述 三种信息；
学生表 中需包含 学生id 、学号、姓名、邮箱、对应的班级id 等信息；
课程表 中应包含 课程id 、课程名称 两种信息；
分数表 中则需包含 id 、分数、学生id 、班级id 等信息。

创建

上述四表的创建代码如下

-- 创建班级表
CREATE TABLE if not exists classes (
id INT PRIMARY KEY auto_increment comment '班级id',
name VARCHAR(20) comment '班级名称',
`desc` VARCHAR(100) comment '班级描述'
);

-- 创建学生表
CREATE TABLE if not exists student (
id INT PRIMARY KEY auto_increment comment '学生id',
sn INT UNIQUE comment '学生学号',
name VARCHAR(20) DEFAULT 'unkown' comment '学生名字', -- 默认为unkown
qq_mail VARCHAR(20) comment '学生邮箱',
classes_id int comment '学生班级id',
FOREIGN KEY (classes_id) REFERENCES classes(id) -- 外键约束， class_id 受 classes表 中 id 数据的约束
);

-- 创建课程表
CREATE TABLE if not exists course (
id INT PRIMARY KEY auto_increment comment '课程id',
name VARCHAR(20) comment '课程名称'
);

-- 创建分数表
CREATE TABLE score (
id INT PRIMARY KEY auto_increment comment '分数id',
score DECIMAL(3, 1) comment '分数',
student_id int comment '学生id',
course_id int comment '班级id',
FOREIGN KEY (student_id) REFERENCES student(id), -- 外键约束，student_id 受 student表 中 id 数据的约束
FOREIGN KEY (course_id) REFERENCES course(id) -- 外键约束， course_id 受 course表 中 id 数据的约束
);

创建结果如下

插入数据

-- 为班级表添加数据，3班级
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

-- 为学生表插入数据，8个学生
insert into student(sn, name, qq_mail, classes_id) values
('09982','龙大','longda@qq.com',1),
('00835','龙二',null,1),
('00391','龙三',null,1),
('00031','龙四','longsi@qq.com',1),
('00054','龙五',null,1),
('51234','周星星','xingxing@qq.com',2),
('83223','彪仔',null,2),
('09527','关天下','tianxia@qq.com',2);

-- 为课程表插入数据
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

-- 为分数表插入7人数据
insert into score(score, student_id, course_id) values
-- 龙大
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 龙二
(60, 2, 1),(59.5, 2, 5),
-- 龙三
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 龙四
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 龙五
(81, 5, 1),(37, 5, 5),
-- 周星星
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 彪仔
(80, 7, 2),(92, 7, 6);

结果如下

内外连接~问题

问题1：查询周星星的成绩

该问题需要 学生表 与 成绩表 进行联查，用到了 inner join + on，还有条件过滤 where。
实现代码如下

select  score.id, score.score, student.name from score 
join student on student.id = score.student_id where student.name = '周星星';

注：from 和 join 后表的名称可以互换位置，不影响最终结果

结果如下

问题2：查询所有人的平均成绩以及其他信息

1）查询所有人的平均成绩

该问题主要是查询所有人的平均成绩，并输出每个人的学号、姓名信息。
即依然是需要对 成绩表 与 学生表 进行联查，用到了内连接 inner join + on ,还有分组查询 group by。
实现代码如下

select student.sn, student.name, avg(score.score) from score 
join student on student.id = score.student_id group by student.id;

上述代码相当于

select student.sn, student.name, avg(score.score) from score 
join student on student.id = score.student_id group by score.student_id;

结果如下

2）查询平均成绩最高的前三名

即以平均成绩作降序排序，然后输出前三名即可，代码如下

select student.sn, student.name, avg(score.score) from student  
join score on student.id=score.student_id group by student.id 
order by avg(score.score) desc limit 3;

其中limit是用于分页查询，具体作用可见博客 MySql 表操作指令小全与案例中查询部分的内容。运行结果如下

3）查询平均成绩排名第三的学生信息

还是以平均成绩作降序排序，然后输出第三名的信息即可，此处就只输出学号、姓名、平均成绩这三种信息。代码如下

select student.sn, student.name, avg(score.score) from student 
join score on score.student_id=student.id group by student.id 
order by avg(score.score) desc limit 2,1;

运行结果如下

问题3：查询所有人的成绩以及其他信息

此问题相较于问题2，其所查询的成绩为每个人每门课的成绩，即其他信息中还需包括每个人对应的课程名称。
1、该问题的求解应用到了 内连接 inner join + on ，排序查询 order by。
代码如下

select student.sn, student.name, course.name, score.score from student 
join score on score.student_id = student.id
join course on score.course_id = course.id order by student.id;

结果如下

2、利用外连接中 左连接 left join + on 进行求解
代码为

select student.sn, student.name, course.name, score.score from student 
left join score on score.student_id = student.id
left join course on score.course_id = course.id order by student.id;

结果如下

因为上述代码是以 student 表 为基准进行处理，因此结果中出现了 关天下 这一学生的信息，但由于他没有上课与成绩信息，所以显示的为 NULL。

3、利用外连接中 右连接 right join + on 进行求解
代码如下

select student.sn, student.name, course.name, score.score from student 
right join score on score.student_id = student.id
right join course on score.course_id = course.id order by student.id;

该程序是以 score 和 course 表为基准，所以他的结果为

即与内连接的结果一致！

自连接问题

问题描述：显示所有“计算机原理”成绩比“Java”成绩高的学生信息，包含学生名称与两科成绩！

select stu.name, computer.score, java.score from student stu 
join score computer on stu.id = computer.student_id
join score java on stu.id = java.student_id
join course cou1 on cou1.id = computer.course_id
join course cou2 on cou2.id = java.course_id
where cou1.name = '计算机原理' and cou2.name = 'Java'
and computer.score > java.score; 

注：其中运用了 ’起别名‘ 的技巧

结果如下

嵌套问题

问题1：查找同班同学

问题描述：在 student 表中查询与“龙五” 同学的同班同学：

未嵌套解法

1、最简单解法

1） 先查询出 龙五 同学的班级

select classes_id from student where name = '龙五';

2） 通过班级 id 查询出 龙五 同学的同班同学

select name from student where classes_id = 1;

结果如下

2、自连接解法

程序如下

select stu1.name from student stu1
join student stu2 on stu2.name = '龙五'
where stu2.classes_id = stu1.classes_id;

先找出 stu2 中 龙五 对应的班级号，然后再在 stu1 中找出相同班级号下的学生姓名。

结果为

嵌套解法

程序如下：

select name from student where classes_id = (select classes_id from student where name = '龙五');
或
select name from student where classes_id in (select classes_id from student where name = '龙五');

程序解释：输出 name 的 sql 的查询条件是另一条输出 classes_id 的 sql 的执行结果。

结果如下：

问题2：查找成绩信息

问题描述：查询所有比“中文系2019级3班”平均分高的成绩信息。
该问题有点复杂，首先需要从 classes 表中找出对应 id ，然后以此 id 找出 student 表中在此班的 学生id ，然后以这些学生id 去求解 score 表中这些学生的平均成绩，再以这 平均成绩 作为判决条件，求出 score 表中所有大于该平均分的成绩信息。

内连接解法

依据上面解释进行程序编写，如下

select * from score where score > (
select avg(score.score) from score
join student on student.id = score.student_id
join classes on classes.id = student.classes_id
where classes.name = '中文系2019级3班');

或

select * from score ,(
select avg(score.score) score  -- 起别名
from score
join student on student.id = score.student_id
join classes on classes.id = student.classes_id
where classes.name = '中文系2019级3班'
)tmp
where score.score > tmp.score;

上述程序结合 嵌套 与 内连接 进行实现的！

注意：由于avg是个内聚函数，除了在 分组查询 的时候在条件中能用否则不能在条件中使用，也就是说这个函数不适合用内连接，即下面这个语句就是错的

select * from score sco1 
join score sco2 on sco1.score > avg(sco2.score);

结果如图

嵌套解法

select * from score where score >  -- 求取大于平均成绩的成绩信息
(select avg(score) from score  -- 求取平均成绩
where student_id in  (select id from student  -- 求取 所求班级id 对应的 学生id
where classes_id in (select id from classes  -- 求取name = '中文系2019级3班'对应的 班级id
where name = '中文系2019级3班')
));

结果如下

合并查询问题

问题描述：查询id小于3，或者名字为“英文”的课程

非合并查询解法

直接利用 or 进行问题求解。程序如下

select * from course where id < 3 or name = '英文';

结果如下

合并查询解法

合并查询所用关键字为 union ，具体程序如下

select * from course where id < 3
union 
select * from course where name = '英文';

结果如下

注意：相比之下，or会忽略索引，而索引可有效提高系统查询效率！

以上为本篇博客主要内容，内容有点多，静下心，最好敲一遍，理解每个命令或每种用法的作用~

侵权删~