MySQL高级SQL语句（一）

MySQL高级SQL语句（一）

一、高级SQL语句（进阶查询）

创建一个location表

创建一个store_info表：

1.1 select

select：显示表格中一个或数个字段的所有数据记录
语法：select 字段名 from 表名;
示例：

select store_name from store_info;
select store_name,sales from store_info;
1
2

1.2 distinct

distinct：不显示重复的数据记录。
语法：select distinct 字段名 from 表名;
示例：

select distinct store_name from store_info;   #对商店名进行去重
select distinct date from store_info;         #对日期进行去重
1
2

1.3 where

where：有条件查询。
语法：select 字段名 from 表名 where 条件;
示例：

select * from store_info where sales > 1000;  #查找销售额大于1000数据记录
select store_name from store_info where sales > 1000;   #查找销售额大于1000的商店名称
1
2

1.4 and 、or

• and：且
• or：或
  语法：
  select 字段名 from 表名 where 条件1 and 条件2;
select 字段名 from 表名 where 条件1 or 条件2;
  示例：

select * from store_info where sales > 500 and sales < 1000;
//查询销售额大于500且小于1000的数据记录

select * from store_info where sales > 1000 or (sales < 500 and sales > 200);
//查询销售额大于200且小于500的记录。或销售额大于1000的记录

select store_name from store_info where sales > 1000 or (sales < 500 and sales > 200);
//查询销售额大于200且小于500的商店名，或者销售额大于1000的商店名
1
2
3
4
5
6
7
8

1.5 in

in：显示已知的值数据记录
语法：

• in：遍历一个取值列表
  select 字段名 from 表名 where 字段名 in('值1','值2',...);
• not in：取反，查询不在这个范围的值
  select 字段名 from 表名 where 字段名 not in('值1','值2',...);
  示例：

select * from store_info where store_name in('Los Angeles','Houston');
//查询商店名为“Los Angeles”,以及商店名为“Houston”的记录

select * from store_info where store_name not in('Los Angeles','Houston');
//查询商店名称不是“Los Angeles” 和不是“Houston”的记录
1
2
3
4
5

1.6 between

between：显示两个值范围内的数据记录
语法：
select 字段名 from 表名 where 字段名 between '值1' and '值2';

select * from store_info where sales between 500 and 15000;
//查询销售额在500~1500之间的数据记录

select * from store_info where date between '2020-12-06' and '2020-12-10';
//查询店铺开业日期在2020-12-06和2020-12-10之间的记录
1
2
3
4
5

1.7 通配符

通常通配符都是跟like一起使用的

• %：百分号表示零个、一个或多个字符
• _：下划线表示单个字符

例：

1、'A_Z'：所有以'A'起头，另一个任何值的字符，且'Z'为结尾的字符串。
例如：'ABZ'和'A2Z'都符合这一个模式，而'AKKZ'并不符合（因为在A和Z之间有两个字符，而不是一个字符）。

2、'ABC%'：所有以'ABC'起头的字符串。
例如：'ABCD'和'ABCABC'都符合这个模式。

3、'%XYZ'：所有以'XYZ'结尾的字符串。
例如：'WXYZ'和'ZZXYZ'都符合这个模式。

4、'%AN%'：所有含有'AN'这个模式的字符串。
例如：'LOS ANGELES'和'SAN FRANCISCO'都符合这个模式。

5、'_AN%'：所有第二个字母为'A'和第三个字母为'N'的字符串。
例如：'SAN FRANCISCO'符合这个模式，而'LOS ANGELES'则不符合这个模式。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1.8 like

like：匹配一个模式来找出我们要的数据记录
select 字段名 from 表名 where 字段名 like {模式};
示例：

select * from store_info where store_name like '%os%';
//查询商店名称包含字符串“os”的记录

select * from store_info where store_name like '%on';
//查询商店名称以字符串“on”结尾的记录

select * from store_info where store_name like '_os%';
//查询商店名称的第二、第三个字符“os”的记录
1
2
3
4
5
6
7
8

1.9 order by

order by：按关键字排序

注意：

• 一般对数值字段进行排序
• 如果对字符类型的字段进行排序，则会按首字母排序

语法：select 字段名 from 表名 [where 条件] order by 字段名 [ASC,DESC];

ASC：是按照升序进行排序的，是默认的排序方式
DESC：是按降序方式进行排序
1
2

示例：

select * from store_info order by sales ASC;
//查看store_info表中的所有记录，按销售额进行升序排序（ASC可以不加，默认升序）

select * from store_info order by sales desc;
//查看store_info表中的所有记录，按销售额进行倒序排序

select * from store_info order by sales desc limit 1;
//获取销售额最大的那行数据记录。即先按销售额进行倒序排列，之后取第一行记录
1
2
3
4
5
6
7
8

二、函数

2.1 数学函数

示例：

select abs(-1),rand(),mod(5,3),power(2,3);
//返回-1的绝对值，0-1之间的随机数，5除以3的余数，2的3次方

select round(1.89),sqrt(2);
//返回离1.89最近的整数，返回2的平方根

select round(1.8937,3),truncate(1.235,2);
//返回1.8937的3位小数，并进行四舍五入；返回1.235的2位小数，不四舍五入

select ceil(5.2),floor(2.1),least(1.89,3,6.1,2.1);
//返回大于或等于5.2的最小整数；返回小于或等于2.1的最大整数；返回集合中最小的值
1
2
3
4
5
6
7
8
9
10
11

2.2 聚合函数

• count(列名)：只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行。
• count(*) ：包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL。

示例1：

select avg(sales) from store_info;
//求表中销售额字段的平均值

select sum(sales) from store_info;
//求表中销售额字段所有值的和
1
2
3
4
5

示例2：

select max(sales) from store_info;
//显示表中销售额字段的最大值

select min(sales) from store_info;
//显示表中销售额字段的最小值
1
2
3
4
5

示例3：

select count(store_name) from store_info;
//统计表中store_name字段有多少个非空值

select count(distinct store_name) from store_info;
//统计表中store_name字段有多少个不重复的非空值
1
2
3
4
5

示例4：比较count(列名)和count(*)

• count(列名)：会忽略null值的行
• count(*)：会统计所有行

#先准备一个数据表city
create table city(name char(20));
insert into city values('beijing');
insert into city values('nanjing');
insert into city values('hangzhou');
insert into city values();
insert into city values();

select count(name) from city;
//统计name字段有多少个非空值，会忽略字段值为null的行

select count(*) from city;
//统计所有行，不会忽略字段值为null的行，全表扫描
1
2
3
4
5
6
7
8
9
10
11
12
13

2.3 字符串函数

2.3.1 去除格式 trim()

语法：
select trim([ [位置] [要移除的字符串] from ] 字符串);

• [位置]：值可以为 leading（起头），trailing（结尾），both（起头及结尾）。
• [要移除的字符串]：从字符串的起头、结尾、或起头及结尾移除的字符串（缺省时为空格）

示例：

select trim(leading 'Ne' from 'Nuw York');
//移除“New York”开头的“Ne”

select trim(trailing 'k' from 'New York');
//移除“New York”结尾的“k”
1
2
3
4
5

2.3.2 拼接的两种方法

1. concat(x,y)
   示例1：

select concat('zhangsan','san');
//将字符串“zhang”和“san”拼接在一起

select concat('zhang','san','san');
//将3个字符串拼接在一起

select concat('zhang','','san');
//将字符串“zhang”、空格、“san”拼接在一起
1
2
3
4
5
6
7
8

示例2：

select concat(redion,store_name) from location;
//将location表中，region字段值和store_name字段值拼接在一起

select concat(region,' ',store_name) from location;
//将location表中，region字段值和store_name字段值拼接在一起，且中间加空格
1
2
3
4
5

2. 使用 || 符号（5.7之后的版本才支持）

如果sql_mode开启了PIPES_AS_CONCAT（可查看配置文件进行确认），"||"视为字符串的连接操作符而非”或“运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的。

 #将location表中，region字段值和store_name字段值拼接在一起。
 select Region || store_name from location;
 ​
 #将location表中，region字段值和store_name字段值拼接在一起，且中间加空格。
 select Region || ' ' || store_name from location;

1
2
3
4
5
6

2.3.3 截取

• substr(x,y)
• substr(x,y,z)

select substr(store_name,3) from location;
//从商店名称的第3个字符截取直到最后一个字符

select substr(store_name,2,4) from location;
//从商店名称的第2个字符开始截取长度为4的字符串

select substr(store_name,3) from location where store_name='Los Angeles';
//当商店名为“Los Angeles”时，从商店名称的第3个字符开始截取直到最后一个字符

select substr(store_name,2,4) from location where store_name='New York';
//当商店为“New York”时，从商店名称的第2个字符开始截取长度为4的字符串
1
2
3
4
5
6
7
8
9
10
11

2.3.4 返回字符长度 length

select store_name,legnth(store_name) from location;
//统计store_name字段值的字符长度
1
2

2.3.5 替换 replace

select replace(region,'st','stern') from location;
//将region字段值中的字符串“st”替换为“stern”
1
2

三、进阶查询

3.1 group by（用于分组和汇总）

对 group by 后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

• "group by"有一个原则，凡是在"group by"后面出现的字段，必须在select 后面出现；
• 凡是在select 后面出现的、且未在聚合函数中出现的字段，必须出现在"group by"后面。

语法：
select 字段1，sum(字段2) from 表名 group by 字段1;

示例：

select store_name from store_info group by store_name;
//对store_name字段值进行分组汇总

select store_name,count(store_name) from store_info group by store_name;
//对store_name字段值进行分组汇总，并统计组内每个非空值的数量。

select store_name,sum(sales) from store_info group by store_name;
//对store_name字段值进行分组汇总，并计算每组的销售额（即分组后求和）

select store_name,sum(sales) from store_info group by store_name order by sum(sales) desc;
//对store_name字段值进行分组汇总，并计算每组的销售额，之后对销售额降序排序
1
2
3
4
5
6
7
8
9
10
11

3.2 having

• 用来过滤有“group by”语句返回的记录集，通常与“group by”语句联合使用
• having语句的存在弥补了where关键字不能与聚合函数联合使用的不足
• where只能对原表中的字段进行筛选，不能group by后的结果进行筛选

语法：
select 字段1,sum(字段2) from 表格名 group by 字段1 having(函数条件);

示例：

select store_name,sum(sales) from store_info group by store_name having sum(sales)>1500;
//对store_name字段值进行分组汇总，计算每组的销售额，之后显示销售额大于1500的记录
1
2

3.3 别名

• 字段别名
• 表格别名

字段别名：
语法：
select 字段1,字段2 as 字段2的别名 from 表名; #as可省略

示例：

select store_name,sum(sales) as total_sales from store_info group by store_name having sum(sales)>1000;

#设置sum(sales)字段的别名为total_sales。
#对store_name字段进行分组汇总，并计算组内销售额之和，之后查询出组销售额大于1000的记录。


1
2
3
4
5
6

表格别名：
语法：
select 表格别名.字段1 [as] 字段别名 from 表名 [as] 表格别名; #as可省略

示例：

select A.store_name store,sum(A.sales) total_sales from store_info A group by A.store_name;
 #设置store_info表的别名为A，A.store_name字段的别名为store，sum(A.sales)字段的别名为total_sales。
 #对A表的store_Name字段进行分组汇总，并计算组销售额。即分组后求组内之和。
1
2
3

3.4 表的自我连接

例：对销售额字段进行排名，并显示名次

#count统计的是A表中销售额的每个值小于等于B表中每个值的次数。（A和B都是store_info的别名，即表的自我连接，自己跟自己对比。）
select A.store_name,A.sales,count(A.sales) rank from store_info as A,store_info as B where A.sales <= B.sales group by A.store_name,A.sales order by rank asc;

1
2
3

若表中有相同数值的销售额时：

select A.store_name,A.sales,count(A.sales) rank from store_info as A,store_info as B where A.sales<B.sales or (A.sales=B.sales and A.store_name=B.store_name) group by A.store_name,A.sales order by rank asc;

1
2

3.5 子查询

子查询：连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句。
语法：
select 字段1 from 表格1 where 字段2 [比较运算符] #外查询 (select 字段1 from 表格2 where 条件)； #内查询
​
#可以是符号的运算符，例如**=、>、<、>=、<=** ;也可以是文字的运算符，例如LIKE、 IN、BETWEEN
​
先执行内查询，再执行外查询。内查询的查询结果，作为外查询的条件

示例1：两张表连在一起

select * from store_info A,location B where A.store_name=B.store_name;
1

示例2：

select sales from store_info where store_name in (select store_name from location where region='West');
#内查询先查询出西部区域的商店名，之后外查询遍历这个商店名列表，查询出每个商店名对应的销售额
1
2

示例3：

select sum(sales) from store_info where store_name in (select store_name from location where region='West');
#内查询先查询出西部区域的商店名，之后外查询遍历这个商店名列表，求出列表中所有商店名的销售额之和
1
2

示例4：

select sum(sales) from store_info A where A.store_name in (select store_name from location B where B.store_name=A.store_name);

#内查询出B表和A表相同的商店名，之后外查询遍历这个商店列名，求列表中所有商店的销售之和
1
2
3

3.6 exists

• 用来测试内查询有没有产生任何结果，类似布尔值是否为真。
• 如果内查询有结果的话，系统就会执行外查询中的SQL语句。若是没有结果的话，那整个SQL语句就不会产生任何结果。

语法：
select 字段1 from 表名1 where exists (select * from 表名 where 条件);

示例：

select sum(sales) from store_info where exists (select * from location where region='west');
#内查询有结果的话，系统就会执行外查询中的 SQL语句

select sum(sales) from store_info where exists (select * from location where region='north');
#内查询没有产生结果，会返回null值
1
2
3
4
5