史上最全面MySQL数据库中DML查询语句SELECT详解（收藏）

select 数据查询

1、创建表

学生表 student

create table student(
    sno varchar(20) primary key, # 学号
    sname varchar(20) not null, # 姓名
    ssex varchar(10) not null, # 性别
    sbirthday datetime, # 出生年月
    sclass varchar(20) # 所在班级
);

教师表 teacher

create table teacher(
    tno varchar(20) primary key, # 教师编号
    tname varchar(20) not null, # 教师名字
    tsex varchar(10) not null, # 教师性别
    tbirthday datetime, # 出生年月
    prof varchar(20) not null, # 职称
    depart varchar(20) not null # 所在部门
);

课程表 course

create table course(
    cno varchar(20) primary key, # 课程号
    cname varchar(20) not null, # 课程名称
    tno varchar(20) not null, # 教师编号
    foreign key(tno) references teacher(tno)
);

成绩表 score

create table score(
    sno varchar(20) not null, # 学号
    cno varchar(20) not null, # 课程号
    degree decimal, # 成绩
    foreign key(sno) references student(sno),
    foreign key(cno) references course(cno),
    primary key(sno, cno)
);

2、插入数据
添加学生信息

insert into student value('101','曾华','男','1977-09-01','95033');
insert into student value('102','匡明','男','1975-10-02','95031');
insert into student value('103','王丽','女','1977-01-23','95033');
insert into student value('104','李军','男','1977-02-20','95033');
insert into student value('105','王芳','女','1977-02-10','95031');
insert into student value('106','陆军','男','1977-06-03','95031');
insert into student value('107','李缩','男','1977-02-21','95033');
insert into student value('108','王解','女','1975-02-13','95031');
insert into student value('109','陆稍','男','1977-06-01','95031');

添加教师信息

insert into teacher value('834','李成','男','1958-12-01','副教授','计算机系');
insert into teacher value('856','张旭','男','1968-11-21','讲师','电子工程系');
insert into teacher value('825','王品','女','1972-05-05','助教','计算机系');
insert into teacher value('831','刘冰','女','1977-08-14','助教','电子工程系');
insert into teacher value('886','王灿','女','1985-03-24','讲师','经济管理系');

添加课程表

insert into course value('3-245','计算机导论','825');
insert into course value('3-105','操作系统','834' );
insert into course value('6-166','数字电路','856' );
insert into course value('9-888','高等数学','831' );
insert into course value('8-365','专业英语','886' );

添加成绩表

insert into score value('103','3-245','86');
insert into score  value('105','3-245','75' );
insert into score value('109','3-245','68' );
insert into score  value('103','3-105','92' );
insert into score value('105','3-105','88');
insert into score  value('109','3-105','76' );
insert into score value('101','3-105','91');
insert into score value('102','3-105','78');
insert into score  value('103','6-166','85' );
insert into score  value('105','6-166','79' );
insert into score  value('109','6-166','81' );

3、查询练习
1、查询student表的所有记录
select * from student;

2、查询student表中的所有记录的sname、ssex和sclass列
select sname, ssex, sclass from student;

3、查询教师所有的单位即不重复的depart列
select distinct depart from teacher; // distinct排重

4、查询score表中成绩在60-80之间的所有记录
select from score where degree between 60 and 80;
select from score where degree > 60 and degree < 80;

5、查询score表中成绩为85,86或87的记录
select * from score where degree in(85, 86, 87);

6、查询student表中”95031”班，或性别为’女’的同学记录
select * from student where sclass=’95031’ or ssex=’女’;

7、以sclass降序查询student表中的所有数据 //desc降序 asc升序默认是升序的一般不会写
select * from student order by sclass desc;

8、以cno升序、degree降序查询score表中的所有记录 //遇到相同的就降序
select * from score order by cno asc ,degree desc;

9、查询”95031”班级的人数
select count(*) from student where sclass = ‘95031’;

10、查询score表中最高分的学生学号和课程号。(子查询或排序)
select sno, cno from score where degree = (select max(degree) from score);
select sno, cno, degree from score order by degree desc limit 0, 1;
如果按照分数排序，select的时候一定要把degree放进去，0表示从第一行开始数一行，如果是1, 2则表示从第二行开始数两条

11、查询每门课的平均成绩
select * from course;
select avg(degree) from score where cno=’3-105’;
一门一门查询

下面是写在一句话中
select cno, avg(degree) from score group by cno;

12、查询score表中至少有两名学生选修的并以3开头的课程的平均分数
select cno, avg(degree),count() from score group by cno having count(cno)>=2 and cno like ‘3%’;
count()表示给出这个group的个数，此题也可以不加

13、查询分数大于70小于90的sno列
select sno,degree from score where degree>70 and degree<90;
select sno,degree from score where degree between 70 and 90;

14、查询所有学生的sname, cno和degree列
select sno, sname from student;
select sno, cno, degree from score;
需要用第一句话中的sname替换第二行的sno，下面使用多表查寻语句
select sname, cno, degree from student, score where student.sno=score.sno;

15、查寻所有学生的sno, cname和degree
select sno, cname, degree from course, score where course.cno=score.cno;

16、查询所有学生的sname、cname、degree列（来自三张表）
select sname, cname, degree from student, course, score where student.sno=score.sno and course.cno=score.cno;

select sname, cname, degree, student.sno as stu_sno, course.cno from student, course, score where student.sno=score.sno and course.cno=score.cno;

因为sno有歧义所以要加student.sno，可以用as去改名

17、查询95031班学生每门课的平均分
select cno, avg(degree) from score where sno in (select sno from student where sclass=’95031’) group by cno;

18、查询选修“3-105”课程的成绩高于109号同学“3-105”成绩的所有同学记录
select * from score where cno=’3-105’ and degree>(select degree from score where sno=’109’ and cno=’3-105’);

19、查询成绩高于学号为”109”、课程号为”3-105”的成绩的所有记录
select * from score where degree>(select degree from score where sno=’109’ and cno=’3-105’);

20、查询和学号108、101的同学童年出生的所有学生的sno、sname和sbirthday列
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

21、查询“张旭”教师任课的学生成绩
select degree from score where cno=(select cno from course where tno=(select tno from teacher where tname=’张旭’));

22、查询选修某课程的同学多于5人的教师姓名
select tname from teacher where tno = (select tno from course where cno=(select cno from score group by cno having count(*) > 5));

23、查询95033和95031班全体学生记录
select * from student where sclass in (‘95033’, ‘95031’);

24、查询85分以上成绩的课程cno
select cno from score where degree>85;

25、查询出“计算机系”教师所有课程的成绩表
select * from score where cno in(select cno from course where tno in (select tno from teacher where depart=’计算机系’));

26、查询计算机系和电子工程系不同职称的教师的tname和prof（职称在别的部门没有出现过）
union求并集
select from teacher where depart=’计算机系’ and prof not in (select prof from teacher where depart=’电子工程系’)
union
select from teacher where depart=’电子工程系’ and prof not in (select prof from teacher where depart=’计算机系’);

27、查询选修编号为3-105，且至少高于选修编号为3-245的同学的信息并按照degree降序排列
（至少表示大于其中任意一个，用any）
select * from score where cno = ‘3-105’ and degree > any(select degree from score where cno = ‘3-245’) order by degree desc;

28、查询选修编号为3-105，且成绩高于选修编号为3-245的同学的信息
（且表示大于所有选项，用all）
select * from score where cno = ‘3-105’ and degree > all(select degree from score where cno = ‘3-245’);

29、查询所有教师和同学的name、sex和birthday(别名as，默认会写第一组的名字所以只要改第一组的名字就ok)
select sname as name, ssex as sex, sbirthday as birthdy from student
union
select tname,tsex,tbirthday from teacher;

30、查询所有女教师和女同学的name、sex和birthday
select sname as name, ssex as sex, sbirthday as birthday from student where ssex = ‘女’
union
select tname, tsex, tbirthday from teacher where tsex = ‘女’;

31、查询成绩比该课程平均成绩低的同学的成绩表
select cno, avg(degree) from score group by cno;//查询一类课程的平均分
select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);

32、查询所有任课教师的tname和depart(任课说明在课程表中有课程)
select tname, depart from teacher where tno in (select tno from course);

33、查询至少有两名男生的班号(加条件分组筛选)
select sclass from student where ssex = ‘男’ group by sclass having count(*)>1;

34、查询student表中不姓王的同学记录
select * from student where sname not like ‘王%’;

35、查询student表中每个学生的姓名和年龄
年龄=当前年份-出生年份
select sname, year(now())-year(sbirthday) from student;

36、查询student表中最大最小sbirthday日期值
select sbirthday from student order by sbirthday;
select max(sbirthday) as ‘最大’,min(sbirthday) as ‘最小’ from student;

37、以班号和年龄从大到小排序查询student表中的全部记录
select from student order by sclass desc, year(now)-year(sbirthday) desc;
select from student order by sclass desc, sbirthday;

38、查询男教师及其所教的课程
select cname from course where tno in (select tno from teacher where tsex=’男’);

39、查询最高分同学的sno、cno和degree列
select * from score where degree = (select max(degree) from score);

40、查询和李军同性别的所有同学的sname
select sname from student where ssex = (select ssex from student where sname = ‘李军’);

41、查询和李军同性别并且同班的同学的sname
select sname from student where ssex = (select ssex from student where sname = ‘李军’) and sclass = (select sclass from student where sname = ‘李军’);

42、查询所有选修计算机导论课程的男同学的成绩表
select * from score where sno in (select sno from student where ssex = ‘男’) and cno = (select cno from course where cname = ‘计算机导论’);

43、假设使用如下命令建立了一个grade表：
create table grade(
low int(3),
upp int(3),
grade char(1)
);
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(50,59,’E’);

查询所有同学的sno、cno和grade列
select sno,cno,grade from score,grade where degree between low and upp;

查询表中全部信息
`select * from 表名;`
查询表中指定列的信息
`select 字段名1,字段名2 from 表名;`
数据去重
`select distinct 字段名…. From 表名;`
拼接结果
`select concat(字段名1,字段名2) from 表名;`
设置别名(注意：关键字as可以省略)
`select 字段名 as 别名 from 表名;  `
`select 字段名 别名 from 表名;    `

where 条件查询

select 字段名… from 表名 where 条件;
条件中比较运算符：( 等于:= 大于:> 大于等于:>= 小于:< 小于等于:<= 不等于:!= 或 <> )

where 字段名比较运算符值;

逻辑运算符( 并且:and 或 && 或:or 非:not 或 ! )

where 条件1 逻辑运算符条件2;

where not 条件;

范围查询

where 字段名 between 条件1  and 条件2;     //列在这个区间的值
where 字段名 not between 条件1 and 条件2;  //不在这个区间
where !( 字段名 between 条件1 and 条件2 );   //同样表示不在这个区间

集合查询( 判断列的值是否在指定的集合中 )

where 字段名 in(值1,值2);      //列中的数据是in后的值里面的
where 字段名 not in(值1,值2);  //不是in中指定值的数据

NULL值查询( 注意：列中值为null不能使用=去查询 )

where 字段名 is null;  //查询列中值为null的数据

模糊查询
%：表示0到多个字符，示例：

where 列 like '%0';  //表示以0结尾
where 列 like  '0%';  //表示以0开头
where 列 like  '%0%';  //表示数据中包含0

_：表示一个字符，可多次使用，示例：

where 列 like '%0_';  //数据结尾第二位是0

结果排序( 对查询出的结果按照一列或多列进行升序还是降序排列升序:asc 降序:desc )

where 条件 order by 字段名 [asc/desc]

分页查询( beginIndex:表示从第多少条数据开始 pageSize:表示每页显示的数据条数 )

where [条件] limit beginIndex,pageSize;
ex：每页显示3条数据
第一页: SELECT FROM 表名 LIMIT 0,3 —0,1,2
第二页: SELECT FROM 表名 LIMIT 3,3 —3,4,5
第三页: SELECT FROM 表名 LIMIT 6,3 —6,7,8
第四页: SELECT FROM 表名 LIMIT 9,3 —9,10,11
……
第七页: SELECT * FROM 表名 LIMIT 18,3 —18,19,20

beginIndex公式：(当前页数-1)*pageSize

聚集函数( 作用于一组数据，并对一组数据返回一个值 )

COUNT:统计结果记录数，若统计的是列，列中为Null，那么count将不会计算值
MAX: 统计计算最大值
MIN: 统计计算最小值
SUM: 统计计算求和
AVG: 统计计算平均值
分组函数( 注意：如果要对分组后的数据进行筛选，那么必须使用having关键字，条件写在having后 )

select 聚集函数 from 表名 where [条件] group by 列 having 分组后的条件
语句的执行顺序：FROM—> WHERE—>group by——>Having—>SELECT—>ORDER BY

Where和having的区别：

Where: 先过滤已有的数据(数据是已经存在的),在进行分组,在聚集计算
Having:先分组,在对每组进行计算,根据得到结果在过滤(分组把数据算出之后，在过滤)
注意：使用having的时候，它是可以使用别名的

=数据查询-多表=
交叉连接:又名笛卡尔积，使用交叉连接会产生笛卡尔积
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

select * from 表1,表2
内连接：过滤为空的数据(查询的实际上是两张表数据的交集部分) 目的 ==》解决笛卡尔积现象，正确查询了需要的数据

select from 表1,表2 where 表1.字段=表2.字段; //隐式内连接,使用where条件消除笛卡尔积
select from 表1 [inner] join 表2 on 表1.字段=表2.字段; //显式内连接,如果是多张表，则一直在join..on后依次添加join..on即可,inner关键字可被省略
外连接：左外连接、右外连接、全外连接
左外连接:以左边表为主，返回左边表中所有数据，若右表中无数据，则显示为NULL，请参考实际查询结果来理解

select * from 表1 left [outer] join 表2 on 表1.字段=表2.字段; //表1为左表，表2为右表,outer关键字可被省略
右外连接：以右边表为主，返回右表中所有数据，若左表中无数据，则显示为NULL，结合实际查询结果来理解

select * from 表1 right [outer] join 表2 on 表1.字段=表2.字段; //表1为左表，表2为右表,outer关键字可被省略
全外连接：返回涉及的多表中的所有数据，MYSQL中不支持该查询，仅限了解自连接：单表当作多表查询，直白的讲就是一张表中数据含有多种关系，使用多表查询的语法，来查询一张表，查询过程中一定要使用别名
多用在分类数据、省市县分类数据、权限…

select 表1.字段1,表2.字段2 from 表名 as 表1,表名 as 表2 where 表1.字段1=表2.字段2

子查询：将一个查询结果作为另一个查询的对象，直白的讲就是SQL语句嵌套

select from (select from 表名) as 别名
select * from where 条件->条件中包含查询语句
注意：1.查询结果的虚拟表必须取别名
2.字段与关键字一样，冲突时，需要给字段名加``,(Esc键下面、1的左边)
3.如果给虚拟结果表中的字段取了别名，则对虚拟结果表查询时，应该用表别名.虚拟表字段别名

WHERE 语句

分组 GROUP

HAVING 语句

排序 ORDER

嵌套查询

SELECT 基本查询语句

查询单个列

#查询Author表name列的值
select name from Author;

查询多个列

#查询Author表id,name两列的值
select id,name from Author;

查询所有列

#查询Author表所有列的信息
select * from Author;

查询不同的行（distinct去重）

#查询Article表所有作者
select distinct author from Article;

限制查询结果（limit分页）

#查询Article表前3行的文章类型
select type from Article limit 1,3;
select type from Article limit 3;

SELECT ORDER BY子句

对单个查询列进行排序（order by）

#Article表按aid从低到高查询作者姓名和文章类别
select aid,author,type from Article order by aid;

对多个查询列进行排序（order by a,b:a排序的基础上，b再排序)：

#Article表按aid和粉丝数从低到高查询作者姓名和文章类别
select aid,author,type,fans from Article order by aid,fans;

对单个列降序排列（desc降序，从高到低）

#按粉丝数降序排列Article信息
select * from Article order by fans desc;

对多个列降序排列

#按fans降序排列Article信息，再对articles排序
select * from Article order by fans desc,articles;
#以上仅对fans降序，articles仍为升序，articles降序需加desc

select..from..order by ..desc limit..;

#根据文章数降序排列查询前三条Article信息
select * from Article order by articles desc limit 3;

根据字符集进行排序

#如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY
select * from Article order by type;
#如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序
select * from Article order by convert(type using gbk);

SELECT grop by子句

对条件进行分组排序

#分别统计coco和vivi的文章数
select au_id,count(*) as '数目' from Article where author='coco' or author='vivi' group by au_id;

分组排序复合查询（having）

#根据aid统计文章总数大于5的
select au_id,count(*) as '数目' from Article group by au_id having count(*)>5;

with rollup实现在分组统计数据基础上再进行统计

#将Article按author进行分组，再统计每个人的总文章数
select author,sum(articles) as '总文章数' from Article group by author with rollup;

使用 coalesce 来设置一个可以取代 NUll 的名称

#coalesce 语法：select coalesce(a,b,c);
select coalesce(author,'昵称') ,sum(articles) as '总文章数' from Article group by author with rollup;

SELECT WHERE子句

where单个条件查询

#查询Python文章类型的QQ交流群号信息
select type,qq_group from Article where type='Python';

#查询粉丝数大于400的Article信息,按降序排列
select * from Article where fans>400 order by fans desc;

#查询粉丝数不是450的Article信息
select * from Article where fans!=450;
select * from Article where fans not in(450);

between ..and..(在什么之间）

#查询粉丝数在400到450之间的Article信息,按文章数降序排列
select * from Article where fans between 400 and 450 order by articles desc;

is null(查询某个列的值为空)

#查询fans为空的Article信息（没有则返回空表）
select * from Article where fans is null;

SELECT 数据过滤

and操作符查询多个条件，每多一个条件就多加一个and

#查询粉丝数为450且文章类型为Python的Article信息
select * from Article where fans=450 and type='Python';

or操作符查询匹配任一条件的行

#查询粉丝数为300或400的Article信息
select * from Article where fans=300 or fans=400;

and ..or..高级过滤（and计算次序更高，需要用圆括号明确分组操作符）

#查询文章数在10以上并且粉丝数为300或400的Article信息
select * from Article where (fans=300 or fans =400 )and articles>10;

in操作符（值由逗号分隔，括在圆括号中）

#查询粉丝数在400和500的Article信息，降序排列
select * from Article where fans in(400,500) order by fans desc;

not操作符与in操作符结合

#查询粉丝数不是300和400的Article信息，按文章数降序排列
select * from Article where fans not in(300,400) order by articles desc;

SELECT 通配符进行过滤查询

like操作符与百分号（%）通配符

#查询QQ群以6开头的Article信息
select * from Article where qq_group like '6%';

#查询作者名中有o的Article信息，按粉丝数降序排列
select * from Article where author like '%o%' order by fans desc;

#查询aid以2开头、1结尾的Article信息，按文章数降序排列
select * from Article where aid like '2%1' order by articles desc;

like操作符与下划线（_）通配符

#匹配单个字符
select * from Article where type like 'Seleni_m';

SELECT 正则表达式进行过滤查询

regexp正则提取匹配的字符

#查询Type中有SQL的Article信息
select * from Article where type regexp '.SQL';

regexp 正则或（|）

#查询学生粉丝数为300或400的Article信息，按文章数降序排列
select * from Article where fans regexp '300|400' order by articles desc;

regexp 正则和（[]）

#查询文章类型中e前有L或S的Article信息
select * from Article where type regexp '[SL]e' order by type;

regexp 正则匹配范围[a-b]

#查询文章数为20-25的Article信息
select * from Article where articles regexp '2[0-5]';

regexp 正则匹配特殊字符\转义（.*@!_等）

#查询姓名中有*的学生信息
select * from Article where type regexp '\\*';

SELECT 使用函数处理数据

concat()函数拼接

#将类型和对应的qq群连接起来，并按类型排序（a-z）
select concat(type,'(',qq_group,')')from Article order by type;

使用列别名

select concat(type,'(',qq_group,')') as '技术交流QQ群' from Article order by type;

upper()：将文本转换为大写

#将Article表的文章类型转换为大写TYPE_UPPER,列出type和TYPE_UPPER
select type,upper(type) as TYPE_UPPER from Article order by type;

lower（）：将文本转换为小写

#将Article表的文章类型转换为小写TYPE_LOWER,列出type和TYPE_LOWER
select type,lower(type) as TYPE_LOWER from Article order by type;

length()：返回字符串的长度

#计算Article表的文章类型的字符长度
select type,length(type) as TYPE_LENGTH from Article order by type;

Soundex()函数:匹配所有类似于要检索的字符串

#查询类型类似于APP的Article信息
select * from Article where SOUNDEX(type)=SOUNDEX('App');

Trim()函数去掉字符串左右两边的空格

select concat(TRIM(type),'(',TRIM(qq_group),')')from Article order by type;

执行算术计算

select type,fans,articles,fans/articles as avg_fans from Article order by type desc ;

日期函数

#获取系统当前日期时间 年-月-日 时：分：秒
select sysdate();
#获取系统当前日期 年-月-日
select curdate();
#获取系统当前时间 时：分：秒
select curtime();
#获取给定日期的年份——获取当前系统时间的年份
select year(CURDATE());
#获取给定日期的月份——获取当前系统时间的月份
select month(CURDATE());
#获取给定日期的天数——获取当前系统时间的天数
select day(CURDATE());
#获取当前时间的前一天
select date_add(CURDATE(),INTERVAL -1 day);
#获取当前时间的后一天
select date_sub(CURDATE(),INTERVAL -1 day);
#查看文章更新时间为2020-01-01 00:00:00的文章类型
select type,update_date from Article where update_date='2020-01-01 00:00:00';
#查看文章更新时间为2020-01-01的文章类型
select type,update_date from Article where date(update_date)='2020-01-01';
#查询2019年11月更新的文章(两种写法）
#写法一：between...and 指定匹配的日期范围
select type,update_date from Article where date(update_date) between '2019-11-01' and '2019-11-30';
#写法二：year() and month()指定年份和月份
select type,update_date from Article where year(update_date)=2019 and month(update_date)=11;

数值处理函数

#abs()返回一个数的绝对值
select abs(-5);
#cos()返回一个角度的余弦
select cos(30);
#sin()返回一个角度的正弦
select sin(60);
#tan()返回一个角度的正切
select tan(45);
#返回一个数的平方根
select sqrt(4);
#返回一个除操作的余数（m，n),除以n的余数
select mod(5,2);
#返回圆周率
select pi();
#返回一个随机数（小数）
select rand();

聚和函数

#AVG()函数返回列的平均值
#计算平均粉丝数
select avg(fans) as '平均粉丝数' from Article order by type desc ;
#COUNT()函数返回某列的行数
#COUNT(*)对表中行的数目进行计数， 不管表列中包含的是空值（ NULL）还是非空值
#统计类型总数
select count(*) from Article;
#COUNT(column)对特定列中具有值的行进行计数，忽略NULL值
#统计文章数
select count(articles) from Article;
#MAX()函数返回某列的最大值
#查询阅读量最多的文章类型
select max(fans) as '受众最大值' from Article;
#MIN()函数返回某列的最小值
select min(fans) as '受众最小值' from Article;
#SUM()函数返回某列值之和
#统计文章总数
select sum(articles) from Article;

组合聚集函数

#DISTINCT()函数只考虑不同值的平均值
select avg(distinct fans) as '平均粉丝数' from Article order by type desc ;
select avg(fans) as '平均粉丝数' from Article order by type desc ;
#组合聚集函数
select count(*) as '总数',
max(articles) as '文章数最大值',
min(articles) as '文章数最小值' ,
avg(fans) as '平均粉丝数'
from Article;

总结：SELECT子句顺序

SELECT:要返回的列或表达式
…
FROM:要检索的数据表
WHERE:行级过滤
…
GROUP BY:分组说明
HAVING：组级过滤
…
ORDER BY：输出时排序
…
LIMIT：要检索的行数
…