MySQL高级SQL语句（一）

MySQL高级SQL语句（一）
- 一、高级SQL语句（进阶查询）
- - 1.1 select
  - 1.2 distinct
  - 1.3 where
  - 1.4 and 、or
  - 1.5 in
  - 1.6 between
  - 1.7 通配符
  - 1.8 like
  - 1.9 order by
- 二、函数
- - 2.1 数学函数
  - 2.2 聚合函数
  - 2.3 字符串函数
  - - 2.3.1 去除格式 trim()
    - 2.3.2 拼接的两种方法
    - 2.3.3 截取
    - 2.3.4 返回字符长度 length
    - 2.3.5 替换 replace
- 三、进阶查询
- - 3.1 group by（用于分组和汇总）
  - 3.2 having
  - 3.3 别名
  - 3.4 表的自我连接
  - 3.5 子查询
  - 3.6 exists

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 distinct

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

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

在这里插入图片描述

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.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.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.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.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.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.9 order by

order by：按关键字排序

注意：

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

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

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

示例：

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;
//获取销售额最大的那行数据记录。即先按销售额进行倒序排列，之后取第一行记录

在这里插入图片描述

二、函数

2.1 数学函数

数学函数	作用
abs(x)	返回x的绝对值
rand()	返回0到1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round(x)	返回离x最近的整数
round(x,y)	保留x的y位小数四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x,y)	返回数字x截断为y位小数的值（不四舍五入）
ceil(x)	返回大于或等于x的最小整数
floor(x)	返回小于或等于x的最大整数
greatest(x1,x2,…)	返回集合中最大的值
least(x1,x2,…)	返回集合中最小的值

示例：

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的最大整数；返回集合中最小的值

在这里插入图片描述

2.2 聚合函数

聚合函数	含义
avg()	返回指定列的平均值
count()	返回指定列中非NULL值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

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

示例1：

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

在这里插入图片描述
示例2：

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

在这里插入图片描述
示例3：

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

在这里插入图片描述
示例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的行，全表扫描

在这里插入图片描述

2.3 字符串函数

字符串函数	作用
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数x和y拼接成一个字符串
substr(x,y)	获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)	获取从字符串x中的第y个位置开始长度为z的字符串
length(x)	返回字符串x的长度
replace(x,y,z)	将字符串z替代字符串x中的字符串y
upper(x)	将字符串x的所有字母变成大写字母
lower(x)	将字符串x的所有字母变成小写字母
left(x,y)	返回字符串x的前y个字符
right(x,y)	返回字符串x的后y个字符
repeat(x,y)	将字符串x重复y次
space(x)	返回x个空格
strcmp(x,y)	比较x和y，返回的值可以为-1,0,1
reverse(x)	将字符串x反转

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”

在这里插入图片描述

2.3.2 拼接的两种方法

concat(x,y)
示例1：

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

在这里插入图片描述

示例2：

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

在这里插入图片描述

使用 || 符号（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;

在这里插入图片描述

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的字符串

在这里插入图片描述

2.3.4 返回字符长度 length

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

在这里插入图片描述

2.3.5 替换 replace

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

在这里插入图片描述

三、进阶查询

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字段值进行分组汇总，并计算每组的销售额，之后对销售额降序排序

在这里插入图片描述

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的记录

在这里插入图片描述

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的记录。

在这里插入图片描述

表格别名：
语法：
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字段进行分组汇总，并计算组销售额。即分组后求组内之和。

在这里插入图片描述

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;

在这里插入图片描述

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

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;

在这里插入图片描述

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;

在这里插入图片描述
示例2：

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

在这里插入图片描述
示例3：

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

在这里插入图片描述
示例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表相同的商店名，之后外查询遍历这个商店列名，求列表中所有商店的销售之和

在这里插入图片描述

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值