50道基础Mysql查询语句

数据库创建：

-- 建表
create DATABASES lianxi50;
use lianxi50;
-- 学生表

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`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
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('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
 
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
 
-- 成绩表测试数据
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);

50道查询语句：

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 
select st.*,sc.s_score '语文',s.s_score '数学' from student st LEFT join score sc ON st.s_id=sc.s_id 
LEFT JOIN score s ON s.s_id=st.s_id
WHERE sc.c_id=01 AND s.c_id=02 AND sc.s_score>s.s_score;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select st.*,sc.s_score '语文',s.s_score '数学' from student st LEFT join score sc ON st.s_id=sc.s_id 
LEFT JOIN score s ON s.s_id=st.s_id
WHERE sc.c_id=01 AND s.c_id=02 AND sc.s_score<s.s_score;
 
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select * from(
SELECT s.*,AVG(sc.s_score) 'avg' from student s
LEFT JOIN score sc on sc.s_id=s.s_id
GROUP BY s.s_id) a
WHERE avg>=60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
select * from(
SELECT s.*,AVG(sc.s_score) 'avg' from student s
LEFT JOIN score sc on sc.s_id=s.s_id
GROUP BY s.s_id) a
WHERE avg<60 OR avg is null;
 
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s.s_id'学生编号',s.s_name'学生姓名',COUNT(c.c_name)'选课总数',SUM(sc.s_score) '课程总分' FROM student s
LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c on c.c_id=sc.c_id
GROUP BY s.s_id
-- 6、查询"李"姓老师的数量 
 select COUNT(*)'"李"姓老师的数量 '  from teacher WHERE t_name like '李%';
-- 7、查询学过"张三"老师授课的同学的信息 
SELECT s.* FROM student s 
LEFT JOIN score sc ON s.s_id=sc.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_name='张三'
-- 8、查询没学过"张三"老师授课的同学的信息 
 -- 张三老师教的课
SELECT s.* FROM student s LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c on c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.c_id
WHERE s.s_id not in(
SELECT s1.s_id FROM student s1 LEFT JOIN score sc1 ON sc1.s_id=s1.s_id
LEFT JOIN course c1 on c1.c_id=sc1.c_id
LEFT JOIN teacher t1 ON t1.t_id=c1.c_id
WHERE t1.t_name='张三')
GROUP BY s.s_id;
 
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * FROM student s
LEFT JOIN score sc ON sc.s_id=s.s_id
where sc.c_id=01 OR sc.c_id=02
GROUP BY s.s_id
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息-------------------------
SELECT * FROM student s
LEFT JOIN score sc ON sc.s_id=s.s_id
where sc.c_id='01' AND sc.c_id not in
(SELECT s.s_id FROM student s
LEFT JOIN score sc ON sc.s_id=s.s_id
where sc.c_id='02')
GROUP BY s.s_id;
-- 11、查询没有学全所有课程的同学的信息
select a.s_name '学生姓名',a.s_birth '出生日期',a.s_sex '性别' FROM(
SELECT s.*,COUNT(s.s_id) 'count' FROM student s
LEFT JOIN score sc ON sc.s_id=s.s_id
GROUP BY s.s_id
) a
WHERE count< (SELECT COUNT(1) FROM course)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT s2.* FROM student s2
LEFT JOIN score sc2 ON s2.s_id=sc2.s_id
LEFT JOIN course c2 ON sc2.c_id=c2.c_id
WHERE c2.c_id in(
SELECT c.c_id FROM student S 
LEFT JOIN score sc ON s.s_id=sc.s_id
LEFT JOIN course c ON sc.c_id=c.c_id
 WHERE S.s_id=01)
GROUP BY s2.s_id;
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息						GROUP_CONCAT	拼接字段
SELECT s.* FROM student s 
left JOIN score sc on sc.s_id=s.s_id
GROUP BY s.s_id HAVING GROUP_CONCAT(sc.c_id)=(
select  group_concat(sc2.c_id) from student st2
left join score sc2 on sc2.s_id=st2.s_id
where st2.s_id ='01');


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s.s_name '学生姓名' 
from student s 
LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
WHERE s.s_id not in(
select s.s_id 
from student s 
LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
WHERE c.t_id=01)
GROUP BY s.s_name

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.s_id '学号',s.s_name '姓名',COUNT(1) '不及格科目数',AVG(sc.s_score) '平均分' from student s
LEFT JOIN score sc on sc.s_id=s.s_id
where sc.s_score<60
GROUP BY s.s_id;
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select * from student s
LEFT JOIN score s2 on s.s_id=s2.s_id
WHERE s2.c_id=01 AND s2.s_score<60
ORDER BY s2.s_score desc;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s.*,s3.s_score '语文',s4.s_score '数学',s5.s_score '英语',AVG(s2.s_score) '平均分' from student s
LEFT JOIN score s2 on s.s_id=s2.s_id
LEFT JOIN score s3 ON s2.s_id=s3.s_id AND s3.c_id=01
LEFT JOIN score s4 ON s3.s_id=s4.s_id AND s4.c_id=02
LEFT JOIN score s5 ON s5.s_id=s4.s_id AND s5.c_id=03
GROUP BY s.s_id
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT c.c_id '课程ID',c.c_name '课程name',MAX(s.s_score) '最高分',MIN(s.s_score) '最低分',AVG(s.s_score) '平均分',
(SELECT COUNT(*) FROM score WHERE s_score>60 AND c_id=c.c_id)*100/ (SELECT COUNT(*) FROM score where c_id=c.c_id) '及格率',
(SELECT COUNT(*) FROM score WHERE s_score>70 AND s_score<80 AND c_id=c.c_id)*100/ (SELECT COUNT(*) FROM score where c_id=c.c_id) '中等率',
(SELECT COUNT(*) FROM score WHERE s_score>80 AND s_score<90 AND c_id=c.c_id)*100/ (SELECT COUNT(*) FROM score where c_id=c.c_id) '优良率',
(SELECT COUNT(*) FROM score WHERE s_score>90 AND c_id=c.c_id)*100 / (SELECT COUNT(*) FROM score) '优秀率'
FROM course c 
LEFT JOIN score s ON s.c_id=c.c_id
GROUP BY c.c_id;
 
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
SELECT @i:=@i+1 '排名',a.* FROM(
select s.*,s2.c_id,s2.s_score from student s
LEFT JOIN score s2 on s.s_id=s2.s_id
WHERE s2.c_id=01
ORDER BY s2.c_id,s2.s_score asc
) a,(SELECT @i:=0) i
UNION ALL
SELECT @k:=@k+1 '排名',a.* FROM(
select s.*,s2.c_id,s2.s_score from student s
LEFT JOIN score s2 on s.s_id=s2.s_id
WHERE s2.c_id=02
ORDER BY s2.c_id,s2.s_score asc
) a,(SELECT @k:=0) i
UNION ALL
SELECT @j:=@j+1 '排名',a.* FROM(
select s.*,s2.c_id,s2.s_score from student s
LEFT JOIN score s2 on s.s_id=s2.s_id
WHERE s2.c_id=03
ORDER BY s2.c_id,s2.s_score asc
) a,(SELECT @j:=0) i；
-- 20、查询学生的总成绩并进行排名
SELECT *,SUM(sc.s_score) '总分' from student s
LEFT JOIN score sc on sc.s_id=s.s_id
GROUP BY s.s_id
ORDER BY sc.s_score asc;
-- 21、查询不同老师所教不同课程平均分从高到低显示 
 select t.t_id '编号',t.t_name '姓名',AVG(s_score) '平均分' FROM score sc LEFT JOIN
course c on c.c_id=sc.c_id
LEFT JOIN 
teacher t on t.t_id=c.t_id
GROUP BY t.t_id
ORDER BY AVG(s_score)
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT a.* FROM
(SELECT st.*,c.c_id,c.c_name,s2.s_score FROM student st
LEFT JOIN score s2 ON st.s_id=s2.s_id
INNER JOIN course c ON c.c_id= s2.c_id AND c.c_id=01 order by s2.s_score desc LIMIT 1,2) a
UNION ALL
SELECT b.* FROM
(SELECT st.*,c.c_id,c.c_name,s2.s_score FROM student st
LEFT JOIN score s2 ON st.s_id=s2.s_id
INNER JOIN course c ON c.c_id= s2.c_id AND c.c_id=02 order by s2.s_score desc LIMIT 1,2) b
UNION ALL
SELECT c.* FROM
(SELECT st.*,c.c_id,c.c_name,s2.s_score FROM student st
LEFT JOIN score s2 ON st.s_id=s2.s_id
INNER JOIN course c ON c.c_id= s2.c_id AND c.c_id=03 order by s2.s_score desc LIMIT 1,2) c;


-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT *,COUNT(c.c_id),
CASE when (SELECT COUNT(*) FROM score WHERE s_score<100 AND s_score>85 AND c_id=c.c_id GROUP BY c_id) IS NULL THEN 0 
ELSE (SELECT COUNT(*) FROM score WHERE s_score<100 AND s_score>85 AND c_id=c.c_id GROUP BY c_id)/(SELECT COUNT(*) FROM score WHERE c_id=c.c_id GROUP BY c_id)*100 END '100-85',
 CASE when (SELECT COUNT(*) FROM score WHERE s_score<85 AND s_score>70 AND c_id=c.c_id GROUP BY c_id) IS NULL THEN 0 
 ELSE (SELECT COUNT(*) FROM score WHERE s_score<85 AND s_score>70 AND c_id=c.c_id GROUP BY c_id)/(SELECT COUNT(*) FROM score WHERE c_id=c.c_id GROUP BY c_id)*100 END '85-70',
 CASE when (SELECT COUNT(*) FROM score WHERE s_score<70 AND s_score>60 AND c_id=c.c_id GROUP BY c_id) IS NULL THEN 0 
 ELSE (SELECT COUNT(*) FROM score WHERE s_score<70 AND s_score>60 AND c_id=c.c_id GROUP BY c_id)/(SELECT COUNT(*) FROM score WHERE c_id=c.c_id GROUP BY c_id)*100 END '70-60',
 CASE when (SELECT COUNT(*) FROM score WHERE s_score<60 AND c_id=c.c_id GROUP BY c_id) IS NULL THEN 0  
 ELSE (SELECT COUNT(*) FROM score WHERE s_score<60 AND c_id=c.c_id GROUP BY c_id)/(SELECT COUNT(*) FROM score WHERE c_id=c.c_id GROUP BY c_id)*100 END '60-0'

FROM course c LEFT JOIN 
score sc on c.c_id=sc.c_id
GROUP BY c.c_id;
-- 24、查询学生平均成绩及其名次 
SELECT @i:=@i+1'名次',a.* from(
SELECT s.*,AVG(sc.s_score) '平均分' FROM student s
LEFT JOIN score sc on sc.s_id=s.s_id
GROUP BY s.s_name
ORDER BY AVG(sc.s_score) desc) a,(SELECT @i:=0) b;
-- 25、查询各科成绩前三名的记录
 select * from(select c.*,sc.s_score from 
 course c LEFT JOIN score sc ON c.c_id=sc.c_id WHERE c.c_id=01 ORDER BY c.c_name,sc.s_score desc LIMIT 0,3) a
 UNION ALL
 select * from(select c.*,sc.s_score 
from course c LEFT JOIN score sc ON c.c_id=sc.c_id WHERE c.c_id=02
 ORDER BY c.c_name,sc.s_score desc LIMIT 0,3) b 
 UNION ALL
select * from(select c.*,sc.s_score from
 course c LEFT JOIN score sc ON c.c_id=sc.c_id
 WHERE c.c_id=03 ORDER BY c.c_name,sc.s_score desc LIMIT 0,3) c；

-- 26、查询每门课程被选修的学生数 
 Select c.*,COUNT(c.c_id) '人数' FROM course c
LEFT JOIN score sc ON c.c_id = sc.c_id
LEFT JOIN student s on s.s_id=sc.s_id
GROUP BY c.c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT * FROM(
SELECT s.s_id,s.s_name,COUNT(s.s_id) 'number'
from student s LEFT JOIN score sc ON sc.s_id=s.s_id
GROUP BY s.s_id) a
WHERE number=2


-- 28、查询男生、女生人数
 SELECT *,COUNT(s_sex) '人数' from student GROUP BY s_sex;
-- 29、查询名字中含有"风"字的学生信息
 select * from student s
WHERE s.s_name LIKE '%风%'
GROUP BY s.s_id
-- 30、查询同名同性学生名单，并统计同名人数 
 SELECT s1.s_name,s1.s_sex,COUNT(s1.s_name) '人数' from student s1 
LEFT JOIN student s2 ON s1.s_name=s2.s_name and s2.s_sex=s1.s_sex AND s2.s_id!=s1.s_id
GROUP BY s1.s_name

-- 31、查询1990年出生的学生名单
select st.* from student st where st.s_birth like "1990%";
 
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
  SELECT st.*,avg(sc.s_score)'avg',sc.*  from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
WHERE sc.s_score is not null
GROUP BY st.s_id
ORDER BY avg DESC,sc.c_id;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT * from(
 SELECT s.*,AVG(sc.s_score) '平均分' from 
student s LEFT JOIN score sc ON sc.s_id=s.s_id
GROUP BY s.s_id) a 
WHERE 平均分>80;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
 select s.* from student s
LEFT JOIN score s2 ON s2.s_id=s.s_id
LEFT JOIN
-- 35、查询所有学生的课程及分数情况；
 select *from student s
LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c on c.c_id=sc.c_id
ORDER BY s.s_id
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select * from(
 select s.s_name '姓名',c.c_name '课程',MAX(sc.s_score) '最低分'from student s
LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c on c.c_id=sc.c_id
GROUP  BY s.s_id
) a
WHERE 最低分>70
-- 37、查询不及格的课程
 SELECT * from course c
LEFT JOIN score s on c.c_id=s.c_id
WHERE s.s_score<60
GROUP BY c.c_id
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT s.s_id '学号',s.s_name '姓名' FROM student s
LEFT JOIN score sc ON s.s_id=sc.s_id
WHERE sc.c_id=01 AND sc.s_score>=80;
-- 39、求每门课程的学生人数
 select c.c_name '课程名',COUNT(c.c_id) '人数' from course c
LEFT JOIN score s ON s.c_id=c.c_id
GROUP BY c.c_id;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 
 select * from student s
LEFT JOIN score sc ON sc.s_id=s.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_name='张三'
ORDER BY sc.s_score desc
LIMIT 0,1
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

select st.s_id,st.s_name,sc.c_id,sc.s_score from student st 
left join score sc on sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
where (
select count(1) from student st2 
left join score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id
where sc.s_score=sc2.s_score and c.c_id!=c2.c_id 
)>1


-- 42、查询每门功成绩最好的前两名 
SELECT a.* FROM
(SELECT st.*,c.c_id,c.c_name,s2.s_score FROM student st
LEFT JOIN score s2 ON st.s_id=s2.s_id
INNER JOIN course c ON c.c_id= s2.c_id AND c.c_id=01 order by s2.s_score desc LIMIT 1,2) a
UNION ALL
SELECT b.* FROM
(SELECT st.*,c.c_id,c.c_name,s2.s_score FROM student st
LEFT JOIN score s2 ON st.s_id=s2.s_id
INNER JOIN course c ON c.c_id= s2.c_id AND c.c_id=02 order by s2.s_score desc LIMIT 1,2) b
UNION ALL
SELECT c.* FROM
(SELECT st.*,c.c_id,c.c_name,s2.s_score FROM student st
LEFT JOIN score s2 ON st.s_id=s2.s_id
INNER JOIN course c ON c.c_id= s2.c_id AND c.c_id=03 order by s2.s_score desc LIMIT 1,2) c;

-- 借鉴(更准确,漂亮):
 
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
--     若人数相同，按课程号升序排列  
SELECT * from(
 select c.*,COUNT(c.c_id) '人数' from course c
LEFT JOIN score sc on c.c_id=sc.c_id
GROUP BY c.c_id) a
WHERE 人数>5
GROUP BY 人数 DESC,a.c_id;
-- 44、检索至少选修两门课程的学生学号 
select s_id from(
 select s.s_id,COUNT(sc.c_id) '数量' FROM student s
LEFT JOIN score sc on s.s_id=sc.s_id
GROUP BY s.s_id) a where 数量>=2;
-- 45、查询选修了全部课程的学生信息
select * from(
 select s.*,COUNT(sc.c_id) '数量' FROM student s
LEFT JOIN score sc on s.s_id=sc.s_id
GROUP BY s.s_id) a where 数量=(select COUNT(*) from course);
-- 46、查询各学生的年龄
SELECT *,timestampdiff(year,s.s_birth,now()) '年龄' FROM student s
-- 47、查询本周过生日的学生

select st.* from student st 
where week(now())=week(st.s_birth)
  -- 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
  -- 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写
-- 48、查询下周过生日的学生
SELECT s_id '学号',s_name'姓名',s_birth'出生日期',s_sex'性别' from student WHERE WEEK(s_birth)+1=WEEK(NOW());
-- 49、查询本月过生日的学生

select st.* from student st 
where MONTH(now())=MONTH(st.s_birth)
-- 50、查询下月过生日的生

SELECT s_id '学号',s_name'姓名',s_birth'出生日期',MONTH(s_birth)'月份',s_sex'性别' from student WHERE MONTH(s_birth)+1=MONTH(NOW()) OR MONTH(s_birth)=1;
 -- 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模