c mysql查询数据_mysql查询数据

创表语句

create database school;

use school;

create table student(

s_id varchar(10),

s_name varchar(20),

s_age date,

s_sex varchar(10)

);

create table course(

c_id varchar(10),

c_name varchar(20),

t_id varchar(10)

);

create table teacher (

t_id varchar(10),

t_name varchar(20)

);

create table score (

s_id varchar(10),

c_id varchar(10),

score varchar(10)

);

insert into student (s_id, s_name, s_age, s_sex)

values  ('01' , '赵雷' , '1990-01-01' , '男'),

('02' , '钱电' , '1990-12-21' , '男'),

('03' , '孙风' , '1990-05-20' , '男'),

('04' , '李云' , '1990-08-06' , '男'),

('05' , '周梅' , '1991-12-01' , '女'),

('06' , '吴兰' , '1992-03-01' , '女'),

('07' , '郑竹' , '1989-07-01' , '女'),

('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)

values  ('01' , '语文' , '02'),

('02' , '数学' , '01'),

('03' , '英语' , '03');

insert into teacher (t_id, t_name)

values  ('01' , '张三'),

('02' , '李四'),

('03' , '王五');

insert into score (s_id, c_id, score)

values  ('01' , '01' , 80),

('01' , '02' , 90),

('01' , '03' , 99),

('02' , '01' , 70),

('02' , '02' , 60),

('02' , '03' , 80),

('03' , '01' , 80),

('03' , '02' , 80),

('03' , '03' , 80),

('04' , '01' , 50),

('04' , '02' , 30),

('04' , '03' , 20),

('05' , '01' , 76),

('05' , '02' , 87),

('06' , '01' , 31),

('06' , '03' , 34),

('07' , '02' , 89),

('07' , '03' , 98);

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

select a.s_id,a.score from (select * from score where c_id='01') as a,

(select * from score where c_id='02') as b

where a.s_id=b.s_id and a.score>b.score;

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

select a.s_id,a.score from (select * from score where c_id='01') as a,

(select * from score where c_id='02') as b

where a.s_id=b.s_id and a.score

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

select score.*,AVG(score.score) from score GROUP BY score.s_id HAVING AVG(score.score)>=60

# 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select score.*,AVG(score.score) from score GROUP BY score.s_id HAVING AVG(score.score)<60

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

select s.s_id,s.s_name,s2.countcou '选课数',s2.sumscore '总成绩' from student s,

(select s1.s_id,SUM(s1.score) as sumscore,COUNT(s1.c_id) as countcou  from score s1 GROUP BY s1.s_id) as s2

where s.s_id=s2.s_id

# 6、查询"李"姓老师的数量

select COUNT(*) from teacher t where t.t_name like('李%')

# 7、查询学过"张三"老师授课的同学的信息

select s.* from student s ,score s1,(select t.t_id,c.c_id from teacher t,course c where t.t_name='张三' and t.t_id=c.t_id) s2

where s.s_id = s1.s_id and s1.c_id=s2.c_id

# 8、查询没学过"张三"老师授课的同学的信息

select * from student where student.s_id not in

(select s.s_id from score s where s.c_id

in (select c.c_id from teacher t ,course c where t.t_id=c.t_id and t.t_name='张三'))

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select * from student

where s_id in(

select sc1.s_id

from score sc1,

score sc2

where

sc1.s_id = sc2.s_id

and sc1.c_id = '01'

and sc2.c_id = '02'

);

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select s.*,sc1.score,sc2.score

from student s

left join (select * from score where c_id = '01') sc1 on s.s_id = sc1.s_id

left join (select * from score where c_id = '02') sc2 on s.s_id = sc2.s_id

where sc1.c_id = '01' and sc2.c_id is null;

#11、查询没有学全所有课程的同学的信息

select s.* from student s

left join Score s1 on s1.s_id=s.s_id

group by s.s_id

having count(s1.c_id)

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select * from student where s_id in(

select distinct a.s_id from score a where a.c_id

in(select a.c_id from score a where a.s_id='01')

);

#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select * from student

where s_id in (select s_id from score t1 group by s_id

having group_concat(c_id) =

( select group_concat(c_id) as str2 from score

where s_id = '01')

and s_id != '01');

#14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from student s

where s.s_id not in(

select sc.s_id from score sc

where sc.c_id in(

select c.c_id from course c

where c.t_id = (

select t.t_id from teacher t

where t.t_name = '张三'

)

)

)

#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

#个体的某一个信息进行统计操作 使用having 和 GROUP BY

select student.s_id, student.s_name, AVG(score.score)

from student,score

where student.id = score.s_id and score.score<60

group by score.s_id

having count(*) > 1;

# 16、检索"01"课程分数小于60，按分数降序排列的学生信息

select s2.* from score s1,student s2

where s1.c_id=1

and s1.s_id=s2.s_id

and s1.score <60

ORDER BY s1.score

# 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s1.s_id,s1.c_id,s1.score,t1.avgscores

from score s1 left join (select s2.s_id,AVG(s2.score) AS avgscores from score s2 GROUP BY s2.s_id) as t1

on s1.s_id =t1.s_id

ORDER BY t1.avgscores desc

# 18、查询每门课程被选修的学生数

select s1.c_id,count(*) from score s1 GROUP BY s1.c_id

# 19、查询出只有两门课程的全部学生的学号和姓名

select s1.s_id,s1.s_name

from student s1,score s2

where s1.s_id=s2.s_id

GROUP BY s2.s_id

HAVING COUNT(*) = 2

# 20、查询男生、女生人数

select s.s_sex,count(*) AS '人数' from student s

group by s.s_sex

-- 21、查询名字中含有"风"字的学生信息

select * from student s where s.s_name like('%风%')

-- 22、查询同名同姓学生名单，并统计同名人数

select t1.* from student s2 left join (select s1.s_name,s1.s_sex,count(*) as '同名人数' from student s1 group by s1.s_sname)as t1 on s2.s_name = t1.s_name and s2.s_sex= t1.s_sex where t1.同名人数>1

-- 23、查询1990年出生的学生名单

select * from student s where YEAR(s.s_age) = 1990

# 24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

select s1.c_id, AVG(s1.score) '平均成绩'  from score s1

GROUP BY s1.c_id

ORDER BY AVG(s1.score) desc,s1.c_id asc

# 25、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select t1.*,s1.s_name from(select s2.s_id,AVG(s2.score)'平均成绩' from score s2 group by s2.s_id) as t1 left join student s1 on t1.s_id = s1.s_id where t1.平均成绩>=85

# 26、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select * from course c

inner join score sc on c.c_id = sc.c_id

inner join student stu on sc.s_id = stu.s_id

where c.c_name = '数学'

# 27、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select s1.s_name,c1.c_name,s2.score from student s1,course c1,score s2 where s1.s_id = s2.s_id and c1.c_id = s2.c_id and s2.score >70

# 28、查询不及格的课程

select distinct s1.c_id from score s1 where s1.score < 60

# 29、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select st.s_id,st.s_name,sc.score from student st

inner join score sc using(s_id)

where sc.c_id='01' and sc.score>=80;

# 30、求每门课程的学生人数

select s1.c_id,count(*)'学生人数' from score s1 GROUP BY s1.s_id

# 31、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select * from score s2 where EXISTS

(select * from score s1  where s1.s_id=s2.s_id and s1.c_id!=s2.c_id and s1.score=s2.score)

#32、查询每门功成绩最好的前两名

select * from score s1 where

(select COUNT(*) from score s2 where s1.c_id=s2.c_id and s2.score>s1.score) <2

ORDER BY s1.c_id

# 33、检索至少选修两门课程的学生学号

select s1.s_id from score s1 GROUP BY s1.s_id HAVING COUNT(*)>=2

# 34、查询选修了全部课程的学生信息

select s2.* from score s1 ,student s2 where s1.s_id=s2.s_id

GROUP BY s1.s_id HAVING COUNT(*)=(select COUNT(*) from course)