Mysql多表查询练习

最新推荐文章于 2024-10-02 15:21:15 发布

低调的繁华6

最新推荐文章于 2024-10-02 15:21:15 发布

阅读量410

点赞数

分类专栏： MySql 文章标签： MySql

本文链接：https://blog.csdn.net/weixin_42696130/article/details/84954614

版权

MySql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

-- 多表设计 `student, teacher, course`
-- 教师表

CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8;

create table teacher(
id int primary key auto_increment,
name varchar(20)
);

-- 学生表
create table student(
   id int primary key auto_increment,
   name varchar(20),
   city varchar(20),
   age int
);
-- 课程表
create table course(
   id int primary key auto_increment,
   name varchar(20),
   teacher_id int,
   foreign key (teacher_id) references teacher(id)
);
-- 中间表
create table student_course(
   student_id int,
   course_id int,
   score int,
   foreign key (student_id) references student(id),
   foreign key (course_id) references course(id)
);

insert into teacher values(null,'胡歌');
insert into teacher values(null,'韩雪');
insert into teacher values(null,'李梅梅');

insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',25);
insert into student values(null,'小明','北京',20);

insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);

insert into student_course values(1,1,80);
insert into student_course values(1,2,90);
insert into student_course values(1,3,85);
insert into student_course values(1,4,78);
insert into student_course values(2,2,53);
insert into student_course values(2,3,77);
insert into student_course values(2,5,80);
insert into student_course values(3,1,71);
insert into student_course values(3,2,70);
insert into student_course values(3,4,80);
insert into student_course values(3,5,65);
insert into student_course values(3,6,75);
insert into student_course values(4,2,90);
insert into student_course values(4,3,80);
insert into student_course values(4,4,70);
insert into student_course values(4,6,95);
insert into student_course values(5,1,60);
insert into student_course values(5,2,70);
insert into student_course values(5,5,80);
insert into student_course values(5,6,69);
insert into student_course values(6,1,76);
insert into student_course values(6,2,88);
insert into student_course values(6,3,87);
insert into student_course values(7,4,80);
insert into student_course values(8,2,71);
insert into student_course values(8,3,58);
insert into student_course values(8,5,68);
insert into student_course values(9,2,88);
insert into student_course values(10,1,77);
insert into student_course values(10,2,76);
insert into student_course values(10,3,80);
insert into student_course values(10,4,85);
insert into student_course values(10,5,83);

# 多表查询

create table A(
id int primary key auto_increment,
name varchar(20) not null
);
insert into A values(1,'苹果');
insert into A values(2,'橘子');
insert into A values(3,'香蕉');

create table B(
id int primary key auto_increment,
price double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);

-- 只查询水果表
select * from a;

-- 只查询价格表
select * from b;

-- 笛卡尔积
select * from a,b where a.id = b.id;-- 隐式内连接

-- 内连接: select * from 表名a inner join 表b on 条件
select * from a INNER JOIN b on a.id = b.id
select * from a inner join b on a.id = b.id;-- 显示内连接

-- 外连接
-- 1. 左外连接: select * from 表a left outer join 表b on 条件

-- 需求 : 列出所有的水果的信息和价格
select * from b left outer join a on a.id = b.id;
SELECT * from a left join b on a.id = b.id;

-- 2. 右外连接: select * from 表a right outer join 表b on 条件

-- 需求 : 列出所有的价格信息和水果
select * from a right outer join b on a.id = b.id;

select * from b left outer join a on a.id = b.id;

-- 3. 全外连接 select * from 表a full outer join 表b on 条件
-- 需求 : 查询所有的水果的信息，和价格的信息

select * from a full outer join b on a.id = b.id;-- mysql不支持这种写法而已

-- mysql的全外连接如何实现
-- 左外连接联合右外连接
select * from a left outer join b on a.id = b.id
union -- union只显示不重复的数据，union all，将所有的数据都展示
select * from a right outer join b on a.id = b.id;

# 关联子查询
-- 需求 : 查询年龄最大的学生信息

-- 查询出最大的年龄
SELECT * from student
where age IN(SELECT max(age) from student)

select max(age) from student;-- 25

-- 查询年龄为25岁的学生的信息
select * from student where age = 25;
SELECT * from student where age in(25);
select * from student
where age = (select max(age) from student);

-- -- in 的用法 :
-- 需求：查询分数不及格的所有的学生信息
SELECT * FROM student where student.id in(select student_id from student_course where score < 60)
-- 从中间表中，查询出不及格的学生的id
select student_id FROM student_course WHERE score<60;

select student_id from student_course where score < 60;
-- 从学生表中，查询对应id的学生信息

-- in:当查询条件是多个，或者不确定时，要使用in
select * from student
where id in (select student_id from student_course where score < 60);

-- all 的用法 :
-- 需求 : 查询年龄最大的学生的信息
SELECT * from student where age in(SELECT max(age) from student)
SELECT * from student where id in(SELECT id from student where age in(SELECT MAX(age) from student ) )
SELECT * from student where age >=all(SELECT age from student)
-- 先查询出所有的学生的年龄
select age from student

-- 只要>=所有年龄就是要找最大年龄
select * from student where age >= all(select age from student);
-- all()中只能放sql语句不能直接放值
select * from student where age >= all(18,22,25);-- 错误

-- any 和 some 的用法 :
-- 查询成绩是90的学生的信息
SELECT * from student where id in(SELECT student_course.student_id from student_course where student_course.score=90)
SELECT * from student where id = SOME(SELECT student_course.student_id from student_course where student_course.score=90)
SELECT * from student where id = ANY(SELECT student_course.student_id from student_course where student_course.score=90)
-- 从中间表中查询90分的学生id
select student_id from student_course where score = 90;

-- 从学生表中查询信息
select * from student
   where id in (select student_id from student_course where score = 90);

-- 如果查询结果是多个或不确定时，但是我就想用= 怎么办？？
select * from student
   where id = some(select student_id from student_course where score = 90);

select * from student
   where id = any(select student_id from student_course where score = 90);



   -- as 定义 `临时表`
-- 需求 : 查询不及格的学生信息和不及格分数
select student.*,score from student,(SELECT * from student_course WHERE student_course.score<60) as temp where student.id = temp.student_id;
select student.*,temp.* from student,(SELECT * from student_course WHERE student_course.score<60) as temp where student.id = temp.student_id;

SELECT * FROM student,(SELECT student_course.student_id,student_course.score from student_course where student_course.score <60) as temp where student.id = temp.student_id;
-- 从中间表中查询不及格的学生的id和分数
   select student_id,score from student_course where score < 60;
-- 将上面的子查询认作是一张临时表as
   select student.*,score from student,
           (select student_id,score from student_course where score < 60) as temp
               where temp.student_id = student.id;

-- 子查询练习
-- 需求 : 查询数学成绩比语文成绩高的所有学生信息
select * from student_course where course_id in (SELECT id from course where `name` ='语文')
select * from student_course where course_id in (SELECT id from course where `name` ='数学')

SELECT * FROM student where id in(
SELECT temp_chinese.student_id
               from
               (select * from student_course where course_id in (SELECT id from course where `name` ='语文')) as temp_chinese,
               (select * from student_course where course_id in (SELECT id from course where `name` ='数学')) as temp_maths
               where temp_chinese.student_id = temp_maths.student_id AND temp_maths.score>temp_chinese.score);

-- 1.1从课程表中查询出数学的id
select id from course where name = '数学';

-- 1.2从课程表中查询出语文的id
select id from course where name = '语文';

-- 2.1 从中间表中查询数学成绩
select student_id,score from student_course
where course_id = (select id from course where name = '数学');

-- 2.2 从中间表中查询语文成绩
select student_id,score from student_course
where course_id = (select id from course where name = '语文');

-- 3.从2个临时表中进行内连接查询获取学生的id

select temp_math.student_id from
   (
   select student_id,score from student_course
   where course_id = (select id from course where name = '数学')
   ) as temp_math,
   (
   select student_id,score from student_course
   where course_id = (select id from course where name = '语文')
   ) as temp_chinese
       where temp_math.student_id = temp_chinese.student_id
           and temp_math.score > temp_chinese.score;

-- 4.从学生表中查询出（1，5，6）的学生信息
   select * from student
       where id in(
               select temp_math.student_id from
           (
           select student_id,score from student_course
           where course_id = (select id from course where name = '数学')
           ) as temp_math,
           (
           select student_id,score from student_course
           where course_id = (select id from course where name = '语文')
           ) as temp_chinese
               where temp_math.student_id = temp_chinese.student_id
                   and temp_math.score > temp_chinese.score
                   );

-- mysql 自带函数 (知道即可)
-- 加密方法 *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
select password('123');
select password("1111")
-- 字符方法
select ucase('itheima');
select lcase('ITHEIMA');
-- java 0基 sql 1基
select substring('王晓晓',2,2);
-- 数字方法
select abs(-5);
select ceil(3.14);
select floor(3.14);

-- 日期方法
select now();
select current_date();
select current_time();

# sql 强化练习

-- 1   查询平均成绩大于70分的同学的学号和平均成绩
SELECT student_id,AVG(score) as `平均成绩`
   FROM student_course
   GROUP BY student_id
   HAVING AVG(score)>70;

   select student_id,avg(score) as 平均成绩 from student_course
       group by student_id
           having avg(score) > 70;

-- 2   查询所有同学的学号、姓名、选课数、总成绩
SELECT student.id as '学号' ,student.`name` as '姓名', temp.选课数,temp.总成绩
   from student ,(
       SELECT student_id,sum(score) as '总成绩',COUNT(score) as '选课数'
           FROM student_course
               GROUP BY student_id) as temp
                   where student.id = temp.student_id;

-- 2.1 从中间表中按学号分组查询出学号、选课数、总成绩
   select student_id,count(*),sum(score)
       from student_course group by student_id;

-- 2.2 临时表和学生表内连接查询
-- 子查询中的聚合函数不能直接使用！！！必须通过别名来使用
select student.id,student.name,temp.选课数,temp.总成绩 from student,
   (
       select student_id,count(*) as 选课数,sum(score) as 总成绩
       from student_course group by student_id
   ) as temp
       where temp.student_id = student.id;




-- 3   查询学过赵云老师所教课的同学的学号、姓名
SELECT * from student where student.id in
   (SELECT student_id FROM student_course where student_course.course_id in(
       SELECT id from course where course.teacher_id in
           (SELECT id from teacher where teacher.name='赵云')));

SELECT * from student_course where
-- 3.1 查询赵云的id
   select id from teacher where name = '赵云';
-- 3.2 从课程表中查询赵云所教的课程id
   select id from course
       where teacher_id = (select id from teacher where name = '赵云');
-- 3.3 从中间表查询学过赵云老师课程的学生id
   select student_id from student_course
       where course_id in (
           select id from course
               where teacher_id = (select id from teacher where name = '赵云')
                           );

-- 3.4从学生表中查询上面的学号的姓名即可
   select * from student where id in (
           select student_id from student_course
               where course_id in (
                   select id from course
                       where teacher_id = (select id from teacher where name = '赵云')
                           )
                                   );

-- 4   查询没学过关羽老师课的同学的学号、姓名
SELECT * from student where student.id not in(
       SELECT DISTINCT(student_id) FROM student_course where course_id in(
           SELECT id from course where teacher_id =(
                   select id from teacher where name ="关羽")));

-- 4.1 查询出学过关羽老师课的同学的学号、姓名
-- 4.2 取反
   select * from student where not id in (
           select student_id from student_course
               where course_id in (
                   select id from course
                       where teacher_id = (select id from teacher where name = '关羽')
                           )
                                   );

   -- 5   查询学三门课以下的同学的学号、姓名
   select id as '学号' ,name as '姓名' from student where id in
   (SELECT student_id from student_course GROUP BY student_id
   HAVING COUNT(course_id)>3 ORDER BY student_id ASC);
-- 5.1 从中间表按学号分组查询学号和选课数，条件：三门以下
select student_id ,count(*) from student_course
       group by student_id having count(*) < 3;
-- 5.2 多表查询内连接
   select student.id,student.name from student,
           (
       select student_id ,count(*) from student_course
           group by student_id having count(*) < 3
           ) as temp
               where temp.student_id = student.id;

-- 6   查询各科成绩最高和最低的分
   SELECT course_id,MAX(score),MIN(score)
           FROM
               student_course GROUP BY course_id;

   select course_id,max(score),min(score)
           from
               student_course group by course_id;

-- 7   查询各个城市的学生数量
SELECT city , COUNT(*) FROM student GROUP BY city;
   select city , count(*) from student group by city;

-- 8   查询不及格的学生信息和课程信息
SELECT student.*,course.* from student,course ,
   (SELECT * from student_course where score<60) as temp
       where student.id = temp.student_id
           and temp.course_id = course.id ;
-- 8.1 从中间表查询不及格的学生id，课程id
   select student_id , course_id from student_course
       where score < 60;
-- 8.2 3表联查
select student.*,course.* from student,course,
   (
   select student_id , course_id from student_course
       where score < 60
   ) as temp
       where temp.student_id = student.id
           and temp.course_id = course.id;

-- 9 统计每门课程的学生选修人数（超过四人的进行统计）

select course_id,count(*) as 选修人数 from student_course          
group by course_id having 选修人数 > 4;