mysql查询连表操作题目详解

优宠

sql数据表
数据表
--1.学生表 student(s_id,s_name,s_age,s_sex)
    s_id 学生编号,s_name 学生姓名,sage 出生年月,s_sex 学生性别
--2.课程表 course(c_id,c_name,t_id)
    c_id 课程编号,c_name 课程名称,t_id 教师编号
--3.教师表 teacher(t_id,t_name)
    t_id 教师编号,t_name 教师姓名
--4.成绩表 sc(s_id,c_id,sc_score)
    s_id 学生编号,c_id 课程编号,sc_score 分数
导入数据方法：将以下 mysql 语句，完整复制到navicat窗口，然后运行即可导入，不需要另外创建表
(自带建表语句):这些语句第一条是创建表(create table),后面都是插入数据到表中(insert into table)学生表 student


create table student(s_id varchar(10),s_name varchar(10),s_age datetime,s_sex varchar(10));
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('09' , '张三' , '2017-12-20' , '女');
insert into student values('10' , '李四' , '2017-12-25' , '女');
insert into student values('11' , '李四' , '2017-12-30' , '女');
insert into student values('12' , '赵六' , '2017-01-01' , '女');
insert into student values('13' , '孙七' , '2018-01-01' , '女');课程表course


create table course(c_id varchar(10),c_name nvarchar(10),t_id varchar(10));
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');教师表teacher


create table teacher(t_id varchar(10),t_name varchar(10));
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');成绩表score


create table sc(s_id varchar(10),c_id varchar(10),sc_score decimal(18,1));
insert into sc values('01' , '01' , 80);
insert into sc values('01' , '02' , 90);
insert into sc values('01' , '03' , 99);
insert into sc values('02' , '01' , 70);
insert into sc values('02' , '02' , 60);
insert into sc values('02' , '03' , 80);
insert into sc values('03' , '01' , 80);
insert into sc values('03' , '02' , 80);
insert into sc values('03' , '03' , 80);
insert into sc values('04' , '01' , 50);
insert into sc values('04' , '02' , 30);
insert into sc values('04' , '03' , 20);
insert into sc values('05' , '01' , 76);
insert into sc values('05' , '02' , 87);
insert into sc values('06' , '01' , 31);
insert into sc values('06' , '03' , 34);
insert into sc values('07' , '02' , 89);
insert into sc values('07' , '03' , 98);
查询‘01’课程比'02'课程成绩高的学生信息及课程分数
解题思路：
-- 分析题目关键词，“学生信息”、“课程分数”、“课程编号”
-- 第一步：锁定使用表，学生表、成绩表，这两张表提供题目所需信息
-- 第二步：通过sid主键连接学生表和成绩表
SELECT
*
FROM student a
INNER JOIN sc b
on a.s_id=b.s_id
-- 第三部：进行同一学生不同成绩比较，那么就还需要连接一个成绩表，利用sid相同、cid不同进行关联
SELECT
*
FROM student a
INNER JOIN sc b
on a.s_id=b.s_id
INNER JOIN sc c
on a.s_id=c.s_id AND b.c_id='01' AND c.c_id='02'
-- 第四部：最终回到题目，只需要筛选一下课程，利用where比较分数即可
SELECT
*
FROM student a
INNER JOIN sc b
on a.s_id=b.s_id
INNER JOIN sc c
on a.s_id=c.s_id AND b.c_id='01' AND c.c_id='02'
where b.sc_score>c.sc_score;

1.1查询同时存在’01‘课程和’02‘课程的情况
-- 第一步分析题目的关键字'课程编号'，'同时存在'
-- 第二步锁定使用所需表，成绩表sc
-- 第三部需要用到inner join，实现一名学生的2门课程在同一行，左边的c_id等于‘01’课程，右边 的c_id等于‘02’课程
SELECT
*
FROM (SELECT * FROM sc WHERE c_id='01')a
INNER JOIN(SELECT * FROM sc WHERE c_id='02')b
on a.s_id=b.s_id;
--第四步，优化
SELECT
*
FROM sc a
INNER JOIN sc b
on a.s_id=b.s_id
WHERE a.c_id='01' and b.c_id='02';

1.2查询存在’01‘课程但可能不存在’02‘的课程情况（不存在时显示为null）
--通过left join实现一名学生两门课程在同一行，且左边c_id等于01课程，右边c_id 等于'02'课程
select
*
from (SELECT * FROM sc where c_id='01') a
LEFT JOIN sc b
on a.s_id=b.s_id and b.c_id ='02';
--优化（不用子查询）
select
*
from sc a
LEFT JOIN sc b
on a.s_id=b.s_id and b.c_id ='02'
WHERE a.c_id = '01';

1.3查询不存在’01‘课程但存在’02’课程的情况
SELECT
*
FROM sc
WHERE s_id not in (SELECT s_id FROM sc WHERE c_id ='01') and c_id='02';


2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- 分析题目关键字‘平均成绩’，‘学生编号’，‘学生姓名’，‘平均成绩大于等于60分的学生’
-- 通过学生表，成绩表得到题目所需信息
-- 成绩表按照s_id聚合，获取平均成绩，并且筛选出平均成绩≥60的s_id的平均成绩
SELECT
s_id,avg(sc_score) as avg_score
from sc
GROUP BY s_id
HAVING avg(sc_score)>=60
-- 利用inner join连接学生表，获取学生姓名
SELECT
a.s_id,a.s_name,avg_score
from student a
INNER JOIN
(SELECT
s_id,avg(sc_score) as avg_score
from sc
GROUP BY s_id
HAVING avg(sc_score)>=60)b
on a.s_id=b.s_id;


3.查询在sc表存在成绩的学生信息
-- 题目关键字‘sc表’，‘成绩’，‘学生信息’
-- 成绩表为主，左连接left join学生表，连接主键s_id
SELECT
b.*
FROM sc a
left join student b
on a.s_id = b.s_id
-- 使用group by，从成绩表中取出唯一sid，左连接学生表
SELECT
b.*
FROM (SELECT s_id FROM sc GROUP BY s_id)a
left join student b
on a.s_id = b.s_id

4.查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩（没成绩的显示为null）
-- 分析关键字‘学生编号’，‘学生姓名’，‘选课总数’，‘所有课程的总成绩’
-- 需要学生表和成绩表
-- 在sc表中s_id进行聚合，获取每个学生的选课总数，所有课程总成绩
SELECT
s_id,
COUNT(c_id) ct,
sum(sc_score) sum_score
from sc
GROUP BY s_id
-- 学生表为主，与获取的学生的选课总数，所有课程总成绩的汇总表进行左连接
SELECT
a.s_id,a.s_name,b.ct,b.sum_score
from student a
left JOIN
(SELECT
s_id,
COUNT(c_id) ct,
sum(sc_score) sum_score
from sc
GROUP BY s_id) b
on a.s_id = b.s_id


4.1 查有有成绩的学生信息
-- 学生表，成绩表
--筛选出所有有成绩的s_id
select s_id from sc group by s_id；
-- 利用有成绩的s_id对student表进行筛选
SELECT
*
from student
WHERE s_id in (select s_id from sc group by s_id)


5.查询【李】姓老师的数量
# 知识点
通配符一般在模糊查询时与“LIKE”配合使用
https://blog.csdn.net/Hughier/article/details/110531080
https://www.w3school.com.cn/sql/sql_wildcards.asp
匹配以某字符开头的内容xx%,以某字符结束的内容%xx,包含某字符的内容%xx%
匹配单个个数的任意字符_
查询员工姓名为四个字符组成的员工姓名和薪资
select name,salary from emp where name like '____';

-- 通过教师表得到所需数据，通过模糊查询+通配符查找姓李老师，在使用count统计姓李老师数量
SELECT
count(*)
FROM teacher
WHERE t_name like '李%';



6.查询学过 |张三| 老师授课同学的信息
-- 分析关键字：‘张三老师’，‘学生信息’
-- 所需信息表：学生表，课程表，成绩表，老师表
-- 关联课程表与教师表，得到课程c_id与教师t_name的关系
select a.*,t_name from course a inner join teacher b on a.t_id =b.t_id;
-- 与成绩表关联，得到学生s_id与学生s_id与教师t_name的关系
select a.*,t_name from sc a
inner join (select a.*,t_name from course a inner join teacher b on a.t_id =b.t_id)b
on a.c_id = b.c_id
-- 与学生表关联，获取学生信息
select a.*,t_name from student a
inner join (select a.*,t_name from sc a
inner join (select a.*,t_name from course a inner join teacher b on a.t_id =b.t_id)b
on a.c_id = b.c_id)b
on a.s_id=b.s_id
where t_name='张三';

市政公所

路过 帮顶 嘿嘿