MySQL从删库到跑路（五）——SQL查询

作者：天山老妖s

链接：http://blog.51cto.com/9291927

1、查询所有字段

在select语句中使用星号“”通配符查询所有字段
在select语句中指定所有字段
select from tstudent;

2、查询指定字段

查询多个字段
select sname,sex,email from tstudent;

3、查询指定记录

在select 语句中通过where子句，对数据进行过滤，语法格式为：
select 字段名1,字段名2,…,字段名n from 表名where 查询条件
select sname,sex,email,class from tstudent where class='java';

4、带in关键字的查询

查询满足指定范围内的条件的记录，使用in操作符，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。
查新姓 王 刘 石的学生
select * from tstudent where left(sname,1) in ('王','刘','石');

5、带between and的范围查询

查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。
以下查询条件，查询学号100到150的学生，包括100和150
select from tstudent where convert(studentid,signed) between 100 and 150
等价于
select from tstudent where convert(studentid,signed)>=100
and convert(studentid,signed)<=150 
自动转换类型
select * from tstudent where studentid between 100 and 150

6、带like的字符匹配查询

百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符
下划线通配符‘_’，一次只能匹配任意一个字符
查找姓名中间字为“志”字的学生
select from tstudent where sname like '志' ;
查找姓名中有“志”字的学生
select from tstudent where sname like '%志%';

7、查询空值

在select语句中使用is null子句，可以查询某字段内容为空记录。
查找邮箱是空值的记录
select * from s where email is null;

8、带and的多条件查询

使用and连接两个甚至多个查询条件，多个条件表达式之间用and分开。
select * from tstudent where sex='男' and class='net' and studentid>20 and studentid<50;

9、带or的多条件查询

or操作符，表示只需要满足其中一个条件的记录即可返回。or也可以连接两个甚至多个查询条件，多个条件表达式之间用and分开。
select * from tstudent where sname like '%志%' or class='net';

10、查询结果不重复

在select语句中可以使用distince关键字指示mysql消除重复的记录值。
select distinct 字段名 from 表名;
查询一共有几个班
select distinct class from tstudent;

11、用limit限制查询结果的数量

limit关键字可以返回指定位置的记录。
limit [位置偏移量,] 行数
返回前10个学生
select from tstudent limit 10;
返回第11-20个学生，偏移量是10，就意味着从第11个开始取10条记录。
select from tstudent limit 10,10;

12、合并查询结果

利用union关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个select语句之间使用union或union all关键字分隔。
要求第一个sql语句返回的列和第二条返回的列数相同，
select studentid,sname from tstudent where studentid<=10
union
select studentid, sname from tstudent where sname like '王%';

13、为表和字段取别名

为字段取别名
mysql可以指定列别名，替换字段或表达式。
列名 [as] 列别名
select studentid as 学号,sname as 姓名,sex as 性别 from tstudent
select studentid 学号,sname 姓名,sex 性别 from tstudent
为表取别名 
为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用别名替代表原来的名称。
表名 [as] 表别名
select a.studentid 学号,a.sname 姓名,a.sex 性别 from tstudent as a;
select a.studentid 学号,a.sname 姓名,a.sex 性别 from tstudent a;

二、多表连接查询

1、内连接查询

内连接(inner join)使用比较运算符根据每个表共有的列的值匹配两个表中的行，并列出表中与连接条件相匹配的数据行，组合成新的记录。在内连接查询中，只有满足条件的记录才能出现在结果关系中。
语句3：隐式的内连接，没有inner join，形成的中间表为两个表的笛卡尔积。
select a.studentid, a.sname, b.mark from tstudent a, tscore b where a.studentid=b.studentid;
语句4：显示的内连接，一般称为内连接，有inner join，形成的中间表为两个表经过on条件过滤后的笛卡尔积。
select a.studentid, a.sname, b.mark from tstudent a inner jointscore b on a.studentid=b.studentid;
select a.studentid,a.sname,c.subjectname,b.mark from tstudent a join tscore b on a.studentid=b.studentid join tsubject c on b.subjectid=c.subjectid;

 

如果某列只在一张表中，就可以不用指明是哪个表中的列。
select a.studentid,a.sname,subjectname,mark from tstudent a join tscore b on a.studentid=b.studentid join tsubject c on b.subjectid=c.subjectid;

 

2、外连接查询

外连接分为左连接、右连接、全连接。
外连接返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。
外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（left outer join）、右外连接（right outer join）和全外连接（full outer join）。
三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 union 右外”。
左连接
包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。
左连接的结果集包括 left outer子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 
select a.studentid, a.sname, b.mark from tstudent a left join tscore b on a.studentid=b.studentid;

右连接：
右连接包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。
右连接是左连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。    
select a.studentid, a.sname, b.mark from tscore b right join tstudent a on a.studentid=b.studentid;

全连接：
全连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。mysql不支持全外连接。可以通过左外和右外求合集来获取全外连接的查询结果。
select a.studentid, a.sname, b.mark from tstudent a left join
tscore b on a.studentid=b.studentid
union 
select b.studentid, c.subjectname, b.mark from tscore b right join
tsubject c on b.subjectid=c.subjectid;

 

3、交叉连接

交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接有显式的和隐式的，不带on子句，返回的是两表的乘积，也叫笛卡尔积。
from子句中的表或视图可通过内连接或全连接按任意顺序指定；但是，用左或右向外连接指定表或视图时，表或视图的顺序很重要。
隐式交叉连接，没有cross join
select a.studentid, a.sname, b.mark from tstudent a,tscore b where a.studentid < 4;
显示交叉连接，有cross join
select a.studentid, a.sname, b.mark from tstudent a cross join tscore b where a.studentid < 4;

 

4、sql查询的原理

第一、单表查询：根据where条件过滤表中的记录，形成中间表；然后根据select的选择列选择相应的列进行返回最终结果。
第二、两表连接查询：对两表求积（笛卡尔积）并用on条件和连接连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。实例如下：
select a.studentid, a.sname, b.mark from tstudent a left join tscore b on a.studentid=b.studentid where a.studentid < 10;
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。

5、过滤条件

on条件：过滤两个连接表笛卡尔积形成中间表的约束条件。
where条件：在有on条件的select语句中是过滤中间表的约束条件。在没有on的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
将where条件移入on后面是不恰当的。推荐的做法是on只进行连接操作，where只过滤中间表的记录。

6、连接查询的适用场景

连接查询是sql查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。两表连接查询选择方式的依据：
a、查两表关联列相等的数据用内连接。
b、col_l是col_r的子集时用右连接。
c、col_r是col_l的子集时用左连接。
e、 col_r和col_l彼此有交集但彼此互不为子集时候用全连接。
f、求差操作的时候用联合查询。

三、对查询结果排序

mysql中可以通过在select使用order by子句对查询的结果进行排序。

1、单列排序

asc代表结果会以由小往大的顺序列出，而 desc 代表结果会以由大往小的顺序列出。默认升序asc排序。
select from tstudent order by birthday asc;
select from tstudent order by birthday desc;

2、多列排序

可以分别指定排序方向。
select a.studentid,a.sname,subjectname,mark from tstudent a join tscore b on a.studentid=b.studentid join tsubject c on b.subjectid=c.subjectid where c.subjectid='0001' order by mark desc,a.studentid desc;

四、分组查询

1、分组查询简介

分组查询是对数据按照某个或多个字段进行分组。
// 分组查询格式
select column
from table
[where condition]
[group by group_by_expression]
[having group_condition] // 过滤条件为聚合函数，使用having
[order by column];
聚合函数只能出现在select列表、having子句和order by子句中，不能出现在where子句中。如果要限制分组结果，只能使用having子句。
使用group by关键字时，在select列表中可以指定的项目是有限制的，select语句中仅允许是被分组的列，或是为每个分组返回一个值的表达式，例如用一个列名作为参数的聚合函数。
where子句：从数据源去掉不符合搜索条件的数据;
group by子句：分组，使用统计函数（聚合函数）为每组计算统计值;
having子句：在分好的组中去掉每组中不符合条件的数据行。

2、使用聚合函数查询

count()函数
select class,count(*) from tstudent group by class;
sum()函数
查询每个学生总分
select concat(a.studentid,' ',a.sname) ss,sum(b.mark) from tstudent a join tscore b on a.studentid=b.studentid group by ss;
avg()函数
统计每个班平均分
select class,avg(mark) from tstudent a join tscore b on a.studentid=b.studentid group by class;

3、多字段分组

统计每班每科平均分，需要按两列分组class和subjectname
select class,subjectname,avg(mark) from tstudent a join tscore b on a.studentid=b.studentid join tsubject c on b.subjectid=c.subjectid group by class,subjectname;

4、使用having过滤分组

查询平局分大于80的学生
select concat(a.studentid,' ',a.sname) ss,avg(b.mark) m from tstudent a join tscore b on a.studentid=b.studentid group by ss having m>80;

5、group by和order by使用

查找平均分大于80分，按平均分排序。
select concat(a.studentid,' ',a.sname) ss,avg(b.mark) m from tstudent a join tscore b on a.studentid=b.studentid group by ss having m>80 order by m;

6、在group by子句中使用with rollup

使用group by的with rollup子句可以检索出更多的分组聚合信息，不仅仅可以检索出各组的聚合信息，还能检索出本组类的整体聚合信息。
select class,subjectname,avg(mark) from tstudent a join tscore b on a.studentid=b.studentid join tsubject c on b.subjectid=c.subjectid group by class,subjectname with rollup;
能够统计每班每科的平均成绩，每班的平均成绩也能统计，全部班级的全部课程平均成绩也能统计。

五、子查询

1、带in关键字的子查询

in关键字进行子查询时，内层查询语句仅仅返回一个数据列，数据列里的值将提供给外层查询语句进行比较操作。 
select * from tstudent where studentid in (select distinct studentid from tscore where mark>98);

2、带exists关键字的子查询

exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断子查询是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。
select from tstudent where studentid='01001' and exists (select from tscore where studentid='01001');

3、带any、some关键字的子查询

any和some关键字是同义词，表示满足其中任一条件，允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
select from tstudent where studentid=any (select distinct studentid from tscore where mark>98)
等价于
select from tstudent where studentid=some (select distinct studentid from tscore where mark>98);
等价于
select from tstudent where studentid in (select distinct studentid from tscore where mark>98);
子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。
以下sql语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00010学号大的学生。
select from tstudent where studentid>some (select distinct studentid from tscore where mark>98)
以下sql语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00061学号小的学生。
select * from tstudent where studentid<some (select distinct studentid from tscore where mark>98);

4、带all关键字的子查询

all关键字与any和some不同，使用all时需要同时满足所有内层查询的条件。
以下sql语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00010学号小的学生。
select * from tstudent where studentid<all (select distinct studentid from tscore where mark>98)
以下sql语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00061学号大的学生。
select * from tstudent where studentid>all (select distinct studentid from tscore where mark>98);

六、使用正则表达式查询

正则表达式作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。mysql用where子句对正则表达式提供了初步的支持，允许指定用正则表达式过滤select检索出的数据。
在sql查询语句中，查询条件regexp后所跟的东西作为正则表达式处理。

1、查询以特定字符或字符串开头的记录

字符‘^’匹配以特定字符或者字符串开头的文本。
select * from tstudent where sname regexp '^刘平';

2、查询以特定字符或字符串结尾的记录

字符‘$’匹配以特定字符或者字符串结尾的文本。
select * from tstudent where cardid regexp '36$';

3、用符号"."来替代字符串中的任意一个字符

字符‘.’匹配任意一个字符。
select * from tstudent where sname regexp '.康.';

4、使用"*"和"+"来匹配多个字符

星号‘’匹配前面的字符任意多次，包括0次。
加号‘+’匹配前面的字符至少一次。
找出×××以19开始，以6结束的学生
select from tstudent where cardid regexp '^19.6$'
找出×××号中有123的学生
select from tstudent where cardid regexp '.123+.';

5、匹配指定字符串

正则表达式可以匹配指定字符串，只要匹配字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分隔符‘|’隔开。
select * from tstudent where sname regexp '武|尹|罗';

6、匹配指定字符中的任意一个

方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。不支持汉字。
select from tstudent where email regexp '[w-z]';
select from tstudent where cardid regexp '^[1-3,7]';

7、匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符。
select * from tstudent where cardid regexp '^[^1-7]';

8、使用{m}或者{m,n}来指定字符串连续出现的次数

“字符串{n,}”表示至少匹配n次前面的字符。“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。
查找×××中出现138并且后面有8位0-9的数字的学生。
select * from tstudent where cardid regexp '138[0-9]{15}';

 

喜欢的小伙伴们可以搜索我们个人的微信公众号“程序员的成长之路”点击关注或扫描下方二维码