对于数据分析工作来说,查询语句在SQL语言中,是非常重要的。今天,本篇就分享一些MySQL中查询语句的方法。
目录
一、数据准备
(1) 数据表介绍
1.学生表student(学号Sid,姓名Sname,出生年月datetime,性别Ssex)
2.课程表course(课程号Cid,课程名Cname,教师编号Tid)
3.教师表teacher(教师编号Tid,教师名Tname,教师职位Toccupation)
4.成绩表sc(学号Sid,课程Cid,成绩score)
(2) 创建数据表
学生表
create table student(Sid varchar(10),Sname varchar(10),Sbirth datetime,Ssex varchar(10));
insert into Student values(‘01’ , ‘赵颖’ , ‘1990-01-26’ , ‘男’);
insert into Student values(‘02’ , ‘符已画’ , ‘2002-12-21’ , ‘女’);
insert into Student values(‘03’ , ‘孙小风’ , ‘1998-5-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1995-8-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅梅’ , ‘1985-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-02-02’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1974-07-01’ , ‘女’);
课程表
create table course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into course values(‘01’ , ‘概率论’ , ‘02’);
insert into course values(‘02’ , ‘高等数学’ , ‘01’);
insert into course values(‘03’ , ‘大学英语’ , ‘03’);
教师表
create table teacher(Tid varchar(10),Tname varchar(10),Toccupation varchar(10));
insert into teacher values(‘01’ , ‘张全风’,‘副教授’);
insert into teacher values(‘02’ , ‘李丽滢’,‘副教授’);
insert into teacher values(‘03’ , ‘王丰’,‘教授’);
成绩表
create table sc(Sid varchar(10),Cid varchar(10),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’ , 85);
insert into sc values(‘03’ , ‘02’ , 42);
insert into sc values(‘04’ , ‘01’ , 60);
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);
二、基本查询
2.1 对列查询
(1) 查询student表的全部数据
select * from student;
(2) 查询sc表中Sid,score列数据,并将score列命名为成绩
select Sid,score '成绩' from sc;
2.2 去重查询
查询teacher表中不重复的职位名称
select distinct Toccupation from teacher;
2.3 条件查询
2.3.1 比较运算符(> = <)
(1) 查询student表中所有男生的数据
select * from student where Ssex="男";
(2) 查询sc表中成绩不及格的学生信息
select * from sc where score<60;
2.3.2 like字符
(1) 查询student表中姓"李"的同学信息
select * from student where Sname like "%李"
(2) 查询student表中姓名里包含"梅"的同学信息
select * from student where Snane like "%梅%"
2.3.3 逻辑运算符(and | or)
(1) 查询student表中1995年出生的男同学信息
select * from student where year(Sbirth)=1995 and Ssex="男";
(2) 查询sc表中课程为01或03的学生成绩
select * from student where score='01' or score='03';
2.3.4 between…and…
查询sc表中,成绩在75—90之间的学生信息
select * from sc where score between 75 and 90;
#也可以写为
select * from sc where score>75 and score<90;
2.3.5 in字符
查询student表中,出生在1990、1998、2002年学生的信息
select * from student where year(Sbirth) in (1990,1998,2002);
注:比较运算符中的=,后面只能根据一个数据;而in字符后面可以跟一组数据
2.4 排序查询
在sc表中,按score降序,Cid 升序查询前5条学生信息(desc 降序,asc 升序)
select * from sc order by score desc,Cid asc limit 0,5;
0代表开始的条数,5代表查询的条数(0可省略,若省略,默认从0开始)
2.5 分组查询
(1) 查询sc表中,每个课程的最低分、最高分、平均分
select Cid,min(score) '最低分',max(score) '最高分',avg(score) '平均分' from sc group by Cid;
(2) 查询sc表中,每个课程学生的所修人数
select Cid,count(*) '选修人数' from sc group by Cid;
(3) 查询sc表中,至少有6个人选的课程
select Cid from sc group by Cid having count(*)>=6;
三、 高级查询
3.1 子查询
在SQL语言中,将一个查询语句嵌套在另一个查询语句中的查询称作嵌套查询,又称子查询
(1) 查询sc表中,最高分的学号,课程编号
select Sid,Cid from sc where score=(select max(score) from sc);
(2) 查询选修【高等数学】课程,男同学的成绩
select Cid,score from sc where Sid in (select Sid from student where Ssex="男") and Cid=(select Cid from course where Cname="高等数学");
注 : where后面跟了两个限定条件,一是对性别的限定,二是对课程名的限定。其中,由于从student中筛选出的男生学号不唯一,所以是个集合,需要用in来连接!
(3) 查询王丰教师所教学生的学号、姓名、出生年月、性别
分析:王丰教师在teacher表,而学生信息在student。通过course表可以查询到王丰教师所教课程,进而在sc表中查询所教学生的学号,最终,依照筛选出的学号在student表中进行查询。
select * from student where Sid in (select Sid from sc where Cid = (select Cid from course where Tid = (select Tid from teacher where Tname="王丰")));
温馨提示:新手在练习复杂子查询的时候,可以先将各个部分先查询出来,理清嵌套的逻辑,最终组合在一起。
3.2 多表查询
虽然,在子查询中已经涉及到不同表之间的查询;但最终查询出的结果还一张表里的东西,因此,多表查询可实现将多个表里的内容呈现在一张新表上。
多表的连接方式:内连接(inner join),完全连接(full join),左连接(left join),右连接(right join)
(1) 查询所有同学的学号、姓名、所选课程的平均成绩,并按降序排列
select a.Sid,Sname,平均分 from student a left join (select Sid,avg(score) '平均分' from sc group by Sid) b on a.Sid=b.Sid order by 平均分;
注:多表查询时,需要将各个表命名。在上述例子中,student表为a表,筛选表为b表,两者表通过学号Sid进行连接.最终在筛选时,一定要注明是a表的Sid,还是b表的Sid,否则会报错!!
(2) 查询课程【01】比课程【02】成绩高的学生信息及分数
select a.Sid,a.score '课程01分数',b.score '课程02分数' from (select Sid,score from sc where Cid="01") a
left join (select Sid,score from sc where Cid="02") b on a.Sid=b.Sid where a.score>b.score;
(3) 查询各门课程的编号、名称、及格率,中等率、优良率、优秀率**
及格:>=60 ,中等:(70,80),优良:[80,90),优秀:[90,100]
流程控制函数:case…when…(类似多重IF函数)
case when 条件一 then value1 else then value11 when 条件二 then value2 else value22…end(一定不要忘了end!)
示例:查询各个课程的及格率
select Cid,sum(case when score>=60 then 1 else 0 end)/count(*) '及格率' from sc group by Cid;
汇总:
select a.Cid,Cname,及格率,中等率,优良率,优秀率 from course a
left join (select Cid,sum(case when score>=60 then 1 else 0 end)/count(*) '及格率' from sc group by Cid) b on a.Cid=b.Cid
left join (select Cid,sum(case when score>70 and score<80 then 1 else 0 end)/count(*) '中等率' from sc group by Cid) c on b.Cid=c.Cid
left join (select Cid,sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) '优良率' from sc group by Cid) d on c.Cid=d.Cid
left join (select Cid,sum(case when score>=90 and score<=100 then 1 else 0 end)/count(*) '优秀率' from sc group by Cid) e on d.Cid=e.Cid;
3.4 日期查询
函数介绍:
A.日期函数:
date_add(date,interval xx year/day/hour/minute/second ) 查询从某天(date)起间隔xx年/月/日/时/分/秒
year(date) 返回date所在年
month(date) 返回date所在月
week(date) 返回date所在周(一年以52周计算)
now() 返回当前日期
B.转换函数:
convert(value,type) type:signed整数型、date日期型、binary二进制型等
(1) 查询本月过生日同学的信息
select * from student where month(now())=month(Sbirth);
(2) 查询下个月过生日同学的信息
select * from student where month(Sbirth)=month(now())+1;
(3) 查询本周过生日同学的信息
分析:将学生的出生年转换为今年(2020年),再使用week函数判断是否相等
select * from student where week(date_add(Sbirth,interval(convert(year(now()),signed)-convert(year(Sbirth),signed)) year))=week(now());
括号比较多,仔细书写!
(4) 查询下周过生日同学的信息
select * from student where week(date_add(Sbirth,interval(convert(year(now()),signed)-convert(year(Sbirth),signed)) year))=week(now())+1;
(5) 查询各个学生的年龄,仅按年份计算,并按升序排列
select Sname,year(now())-year(Sbirth) '年龄' from student order by 年龄 asc;
(6) 查询各个学生的年龄 ,按出生日期计算,当前月日<出生月日,则年龄减1,按升序排列
分析:将2020年转化为各个学生的出生年份,进行当前月日与出生月日比较,通过IF函数判断,如果为真,则年龄减1;如果为假,则年龄不变
select *,if(date_add(now(),interval (convert(-year(now()),signed))+convert(year(Sbirth),signed) year)<Sbirth,year(now())-year(Sbirth)-1,year(now())-year(Sbirth)) '真实年龄' from student order by 真实年龄 asc;