day41:MYSQL:select查询练习题

# 创建班级表
create table class(
cid int primary key auto_increment,caption varchar(32) not null
);

 创建学生表
create table student(
sid int primary key auto_increment,gender char(1)  null,class_id int (cid) on delete cascade on update cascade
);

 创建老师表
create table teacher(
tid int primary key auto_increment,tname varchar(32)  创建课程表
create table course(
cid int primary key auto_increment,cname varchar(32)  创建成绩表
create table score(
sid int primary key auto_increment,student_id int 
# 班级表插入记录
insert into  values
('1',三年二班'),(2三年三班3一年二班4二年一班);

 学生表插入记录
insert into student values
(男理解女钢蛋张三张一5张二6张四7铁锤8李三9李一10李二11李四12如花13刘三14刘一15刘二16刘四 老师表插入记录
insert into teacher values
(张磊李平刘海燕朱云海李春秋 课程表插入记录
insert into course values
(生物物理体育美术 成绩表插入记录
insert into score values
(766668997787796717100181920212223242526272829883031323334913536373890394043414244454647484952');

 1、查询所有的课程的名称以及对应的任课老师姓名 where
select 
    course.cname,teacher.tname
from 
    teacher,course
where 
    teacher.tid = course.teacher_id
    
 inner join
 
    teacher inner join course on teacher.tid = course.teacher_id

 2、查询学生表中男女生各有多少人
select 
    gender,count(*)
 
    student
group by
    gender

 3、查询物理成绩等于100的学生的姓名select 
    student.sname,score.num
 
    score,student,course
where
    score.student_id = student.sid 
    and
    score.course_id = course.cid
    
    course.cname = ""
    
    score.num = 100
    
 inner join 
 
    score inner join student on  score.student_id = student.sid 
    inner join course on score.course_id = course.cid
where
    course.cname = 
    score.num = 100

 where 写法
select
    student_id,student.sname,avg(num)

    score,student
where 
    score.student_id = student.sid
group by
    student_id
having
    avg(num) > 80


    score inner join student on score.student_id = student.sid
group by
    student_id
having
    avg(num) > 80

 选课数
 
    score
group by 
    student_id
    
 总成绩
select 
    student_id,sum(num)
 
    score
group by
    student_id

 student.sid
group by
    student_id

 
    score inner join student on score.student_id = ( 附加所有学生 ) right join 
select 
    student.sid,count(score.course_id),1)"> 
    score right join student on score.student_id = student.sid
group by
    student.sid

 left join 
 
    student left join score  on score.student_id = student.sid
group by
    student.sid

 6、 查询姓李老师的个数
select
    count(* 
    teacher
where 
    tname like 李%'

 7、 查询没有报李平老师课的学生姓名 1.报了李平老师课程的学生id是?
"""distinct 去重  
distinct student_id    ok
distinct(student_id)   ok
"""
select 
    distinct( student_id )
 course.teacher_id
    
    course.cid = score.course_id
     
    teacher.tname = "

 2.查询学生表,除了这个id的剩下的就是没有报李平老师课程的
select 
    student.sname
 
    student
where 
    sid not in (1号数据)

3.综合拼接
select
    student.sname
 (select 
    distinct( student_id )
")

 8、 查询物理课程的分数比生物课程的分数高的学生的学号

 1.物理课程学生分数
select 
    score.student_id as t1_id,score.num as num,course.cid,course.cname
 
    course inner join score on course.cid = score.course_id
where
    course.cname = "
    
 2.生物课程学生分数
select 
    score.student_id as t2_id,1)">"
    
    
 综合拼接

# 格式
select 
    t1.t1_id
from 
    (1) inner join (2) on 1.student_id = 2.student_id
where
    1.num > 2.num

select 
    t1.t1_id
 
    (select 
    score.student_id as t1_id,1)">") as t1 inner join (select 
    score.student_id as t2_id,1)">") as t2 on t1.t1_id = t2.t2_id
where
    t1.num > t2.num

 1.找物理和体育的课程id
select 
    cid
 
    course
where
    cname = " or cname =  2.找学习体育物理课程的学生id 
select 
    student_id
 
    score
where 
    course_id in (2,3)

 拼装数据
 (select 
    cid
 3.(同时)学习体育物理课程的学生id
)

group by
    score.student_id
having 
    count(*) = 2

 4.除了通过学习物理和体育的学生之外,剩下的都是没有同时学习的学生id
select     
    sid,sname
 (3号)
    
 综合拼装:
 (select 
    student_id
)

group by
    score.student_id
having 
    count(*) = 2)

 10、查询挂科超过两门(包括两门)的学生姓名和班级
挂科<60
select
    student_id,.caption
 student.sid
    inner join class on class.cid = student.class_id
where 
    num < 60
group by
    student_id
having 
    count(*) >= 2

 11、查询选修了所有课程的学生姓名 1.统计所有课程总数
select count(*)  course

 2.按照学生分类,总数量是1号查询出来的数据,等价于学了所有课程

    score inner join student on  score.student_id = student.sid
group by 
    score.student_id
having 
    count(*) = (1号)
    
 综合拼接
 student.sid
group by 
    score.student_id
having 
    count(*) = (select count(*) from course)

 12、查询李平老师教的课程的所有成绩记录 内联
select
    score.student_id,course.cname,1)"> 子查询 1.找李平老师的课程id
select 
    course.cid

    teacher.tname = "
 2.通过课程id号 找score里面的数据
select *
 (1号)

select 
    score.student_id,1)"> (select 
    course.cid
")

 13、查询全部学生都选修了的课程号和课程名 1.通过score表,找有成绩的学生个数
select
    count(distinct student_id)

    score

 2.按照课程分类,筛选学生个数等于13的课程id
select
    course_id
 
    score
group by
    course_id
having 
    count(*) = 13
    
select
    course_id,course
where
    score.course_id = course.cid
group by
    course_id
having 
    count(*) = (select
    count(distinct student_id)

    score)

 14、查询每门课程被选修的次数;
select 
    course_id,1)"> 
    score
group by 
    course_id

 15、查询只选修了一门课程的学生学号和姓名 1.按照学生分类,统计课程个数为1
 
    score
group by 
    student_id
having
    count(*) = 1

 2.顺手连带一个学生表student,通过id拿姓名

select 
    student_id,1)"> student.sid
group by 
    student_id
having
    count(*) = 1

 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select 
    distinct num,group_concat(student_id)

    score
group by
    num
order by
    num desc

  其他同学想法
select 
    avg(num),1)"> 
    score
group by
    student_id
order by 
    avg(num) desc 

 17、查询平均成绩大于85的学生姓名和平均成绩 子查询

 1.找学生id
 
    score
group by
    student_id
having 
    avg(num) > 85
    
 2.找学生表对应数据
select sname 
 
    student
where     
    id =select 
    sid,sname 
 
    student
where     
    sid = 
    score
group by
    student_id
having 
    avg(num) > 85)

 18、查询生物成绩不及格的学生姓名和对应生物分数
 score.course_id
    inner join student on student.sid = score.student_id
where
    score.num < 60
    "

 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名 1.找李平老师教的课程id
" 
 2 4

 2.学习李平老师课程的学生中,按照学生分类,找平均分最高的id
 
    score
where
    score.course_id )
group by
    score.student_id
order by 
    avg(num) desc limit 1

 3.通过学生id 顺带着连一张student学生表,找出姓名
 student.sid
    
    score.course_id  (1号)
group by
    score.student_id
order by 
    avg(num) desc limit 1
    
    
 4.综合拼接
 )
group by
    score.student_id
order by 
    avg(num) desc limit 1

 20、查询每门课程成绩最好的课程id、学生姓名和分数 1.找分数最大值.
 
    score
group by
    score.course_id

 2.找出该分数对应的那批学生

select 
    *
 
    score as t1 inner join student as t2 on t1.student_id  = t2.sid
    inner join (1号) as t3 on  t1.course_id = t3.course_id
    

select 
    t1.course_id,t2.sname,t3.max_num
 t2.sid
    inner join (select 
    course_id,1)"> 
    score
group by
    score.course_id) as t3 on  t1.course_id = t3.course_id

where
    t1.num = t3.max_num

 21、查询不同课程但成绩相同的课程号、学生号、成绩 
不同的课程 如果使用!= 相同的数据返回来又查询了一遍,翻倍,为了防止翻倍重复查询使用>或者< 
select 
    s1.student_id as s1_sid,s2.student_id as s2_sid,s1.course_id as s1_cid,s2.course_id as s2_cid,s1.num as s1_num,s2.num as s2_num
 
    score as s1,score as s2
where     
    s1.course_id > s2.course_id
    
    s1.num = s2.num

 23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名  1.学号为2的学生,选了什么学科
select 
    course_id
 
    score
where
    student_id = 2   1 3 4
    
 2.学过1 3 4 学科的学生都有谁
select 
    distinct student_id,1)">
    score inner join student  on score.student_id= student.sid
where 
    course_id in (1,3,1)">)
    
 (select 
    course_id
 
    score
where
    student_id = 2)

 24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数

 1.老师任何的最大数量是多少
任课数量为2的老师可能不止一个
select 
    count(*    
    course
group by
    teacher_id
order by 
    count(*) desc limit 1
    
 2.找最大任课数量为2的老师id
select 
    teacher_id
 
    course
group by
    teacher_id
having 
    count(*) = (select 
    count(*    
    course
group by
    teacher_id
order by 
    count(*) desc limit 1)


 3.通过老师id 找课程
 
    course
where
    teacher_id  (2号)


 综合拼接 2 4 
 (select 
    teacher_id
))

 4.通过该课程号,找其中的最大值(最大分数)
 
    score
where
    course_id  (3号)
group by
    course_id
    
)))
group by
    course_id


 5.把对应的学生姓名,最大分数拼在一起,做一次单表查询
 
    score as t1 inner join student as t2 on  t1.student_id = t2.sid
    inner join (4号) as t3 on t3.course_id = t1.course_id

select 
    *
 t2.sid
    inner join (select
    course_id,1)">)))
group by
    course_id) as t3 on t3.course_id = t1.course_id


 6.把分数是100分的最大值的学员查出来
 t1.course_id
where
    t1.num = t3.max_num
    

 t1.course_id
where
    t1.num = t3.max_num