MySQL数据库 综合项目实战（持续更新）

1.创建数据库和表格

 创建一个名为 mysql_exampleTest的数据库

# 创建数据库
create database mysql_exampleTest;
use mysql_exampleTest;

 

 在创建的数据库下创建后面练习所用的数据表：

1.学生表

# 学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);

 2.课程表

# 课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

 

3.教师表

# 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

4.成绩表

# 成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_Score INT(3),
PRIMARY KEY(s_id,c_id)
);

2.插入数据

由于我的MySQL输入不了中文（整了一下午都没整好o(╥﹏╥)o），暂时用拼音代替

1.插入学生表数据

# 插入学生表测试数据
insert into Student values('01' , 'zhaolei' , '1990-01-01' , 'man');
insert into Student values('02' , 'qiandian' , '1990-12-21' , 'man');
insert into Student values('03' , 'sunfeng' , '1990-05-20' , 'man');
insert into Student values('04' , 'liyun' , '1990-08-06' , 'man');
insert into Student values('05' , 'zhoumei' , '1991-12-01' , 'woman');
insert into Student values('06' , 'wulan' , '1992-03-01' , 'woman');
insert into Student values('07' , 'zhenzhu' , '1989-07-01' , 'woman');
insert into Student values('08' , 'wangju' , '1990-01-20' , 'woman');

 检查一下

2.插入课程表数据

# 课程表测试数据
insert into Course values('01' , 'Chinese' , '02');
insert into Course values('02' , 'Maths' , '01');
insert into Course values('03' , 'English' , '03');

 检查一下

3.插入教师表数据

# 教师表测试数据
insert into Teacher values('01' , 'zhangsan');
insert into Teacher values('02' , 'lisi');
insert into Teacher values('03' , 'wangwu');

 检查一下

 4.插入成绩表数据

# 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

检查一下

1.实战题目

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

分析：需要显示学生信息和课程分数，采用复合条件连接查询；

先利用单表查询看一下每个人的成绩

select Student.s_id,s_name,s_Score,c_id
from Student,Score 
where Student.s_id = Score.s_id;

添加条件进行筛选

select Student.s_id,s_name,s_Score,c_id
from Student,Score 
where Student.s_id = Score.s_id 
and Score.c_id in (01,02);

 再修改上述代码进一步取得我们需要的信息(尝试了一下单表比较繁琐也无法正确显示我们想要的信息） 

换思路，利用连接查询，把信息填入另一个新的表中，更加直观的显示，也方便比较

 如果连接查询这一步不清楚，或者有问题--->MySQL 数据库综合实战 错误集合-CSDN博客

select st.s_id,s_name,sc1.s_Score as 'Chinese',sc2.s_Score as 'Maths'
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
;

 再次添加筛选条件就简单方便多了

select st.s_id,s_name,sc1.s_Score as 'Chinese',sc2.s_Score as 'Maths'
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
where sc1.s_Score > sc2.s_Score
;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

 这一题和上一题一个技巧，稍微改一下就可以了

select st.s_id,s_name,sc1.s_Score as 'Chinese',sc2.s_Score as 'Maths'
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
where sc1.s_Score < sc2.s_Score
;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

分析：求平均值用聚合查询中的函数AVG（）来实现

select st.s_id,ROUND(AVG(s_Score)) as 'avg'
from Student st,Score se
where st.s_id = se.s_id
group by st.s_id
;

 

先利用AVG函数求出每个人的平均值，在用条件筛选

select st.s_id,AVG(s_Score) as 'avg'
from Student st,Score se
where st.s_id = se.s_id
group by st.s_id having AVG(s_Score) >= 60
;

 

 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

 分析：这一题和上一题大同小异，加个筛选条件就可以

select st.s_id,AVG(s_Score) as 'avg'
from Student st,Score se
where st.s_id = se.s_id
group by st.s_id having AVG(s_Score)<60 or AVG(s_Score) is null
;

 但是我发现，题目中要求我们把没有成绩的同学也显示出来，对比原本的表格我发现，08同学的成绩没有了

 然后我去查了一下课件发现左连接是允许有空值的，就换用左连接来使用，这里可以看出，单表查询好像是不能为空的（空值不显示）

select st.s_id,AVG(s_Score) as 'avg'
from Student st
left join Score se
on st.s_id = se.s_id
group by st.s_id having AVG(s_Score)<60 or AVG(s_Score) is null
;

 ok，这道题顺利解决

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

 分析：学生编号和姓名就不多说了，直接查就可以，选课总数和课程总成绩，两个总，选用SUM（）函数来实现

select st.s_id,st.s_name,c_id,s_Score
from Score se 
left join Student st 
on st.s_id = se.s_id
;

 

 先查出需要的信息，在进行总和和过滤

select st.s_id,st.s_name,count(c_id) as 'sum_Course',sum(s_Score) as 'sum_Score'
from Score se 
left join Student st 
on st.s_id = se.s_id
group by st.s_id
;

 

 更新中。。。