MySQL数据库高级SQL语句

目录

一、高级SQL语句

（一）select：查询语句

1.where：根据条件查询

2.in：显示已知值的数据

3.not?in：显示除了已知值的数据

4.between：显示两个值范围内的数据

5.like+通配符：模糊查询

6.order by：按关键字排序

（二）函数

1.数学函数

（1）函数类型

（2）示例

2.聚合函数

（1）函数类型

（2）示例

3.字符串函数

（1）函数类型

（2）示例

①??concat(字段1, 字段2, ....)?

②?字段1 || 字段2??

③?substr(x,y,z)?

④?length(x)

⑤ 替换

（三）group by分组

1.group by

2.having

（四）as：别名

（五）连接查询

1.inner join(内连接)

2.left join(左连接)

3.right join(右连接)

（六）联集

1.union

2.union all

（七）子查询

---

一、高级SQL语句

（一）select：查询语句

1.where：根据条件查询

select '字段' from 表名  where 条件
 
#示例：显示store_Name和store_info 字段 并且要找到Sales小于1000
select store_name from store_info where Sales > 1000;

2.in：显示已知值的数据

select 字段名  from 表名 where 字段 in (值1，值2....);

select * from store_info where store_name in ('Los Angeles', 'Houston');
#示例：查询store_info表中store_name是Los Angeles和Houston的记录

3.not?in：显示除了已知值的数据

select 字段名  from 表名 where 字段 not in (值1，值2....);

select * from store_info where store_name not in ('Los Angeles', 'Houston');
#示例：查询store_info表中除了store_name是Los Angeles和Houston之外的记录

4.between：显示两个值范围内的数据

select 字段名  from 表名 where 字段 between 值1 and 值2;

select * from store_info where date between '2020-12-06' and '2020-12-10';
#示例：查询store_info表中在2020-12-06和2020-12-10之间的记录

5.like+通配符：模糊查询

通配符	含义
%	表示零个，一个或者多个字符
_	下划线表示单个字符
A_Z	所有以A开头 Z 结尾的字符串 'ABZ' 'ACZ' 'ACCCCZ'不在范围内 下划线只表示一个字符 AZ 包含a空格z
ABC%	所有以ABC开头的字符串 ABCD ABCABC
%CBA	所有以CBA结尾的字符串 WCBA CBACBA
%AN%	所有包含AN的字符串 los angeles
_AN%	所有 第二个字母为 A 第三个字母 为N 的字符串

select 字段 from 表名 where 字段 like '通配符表达式'；

select * from store_info where store_name like '%os%';
#示例：查询store_info表中带有os的store_name值

6.order by：按关键字排序

select 字段名  from 表名 where 条件 order by 字段 [asc,desc];
asc ：按照升序进行排序的，是默认的排序方式   desc ：按降序方式进行排序

select store_name,sales,date from store_info order by Sales desc;
#示例：按store_info表中Sales的大小降序排序

（二）函数

1.数学函数

（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..........)	返回返回集合中最小的值

（2）示例

SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
#返回-1的平均值，0-1的随机数，5除以3的余数，2的3次方，距离1.89最近的整数
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
#返回数字1.8937的3位小数四舍五入后的值，数字1.235截断为2位小数的值，大于或等于5.2 的最小整数，小于或等于2.1的最大整数，集合中最小的值

2.聚合函数

（1）函数类型

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

（2）示例

select avg(Sales) from store_info;
#返回表中营业额的平均值
select count(Store_Name) from  store_info;
#返回Store_Name中非 NULL 值的个数
select count(distinct Store_Name) from  store_info;
#返回Store_Name中不重复的非 NULL 值的个数
select max(Sales) from  store_info;
#返回表中营业额的最大值
select min(Sales) from  store_info;
#返回表中营业额的最小值
select sum(Sales) from  store_info;
#返回表中营业额所有值之和

3.字符串函数

（1）函数类型

函数	描述
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为z 的字符串
eplace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
length(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 反转
trim()	返回去除指定格式的值
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同
upper(x)	将字符串 x 的所有字母变成大写字母

（2）示例

①??concat(字段1, 字段2, ....)?

拼接字段

select concat(Region, Store_Name) from location where Store_Name = 'Boston';
#将location表中的Region和Store_Name字段拼接起来

②?字段1 || 字段2??

字段拼接

select Region || ' ' || Store_Name FROM location where Store_Name = 'Boston';
#将location表中的Region和Store_Name字段以空格为分隔符拼接起来

③?substr(x,y,z)?

获取从字符串 x 中的第 y 个位置开始长度为z 的字符串

select substr(Store_Name,2,4) from location where Store_Name = 'New York';
#提取location表中Store_Name字段从第2个字符，4个字符长度的字段内容

④?length(x)

统计字符长度

select Store_Name,length(Store_Name) from location;
#统计Store_Name字段中字符的长度

⑤ 替换

select replace(Store_Name,'on','aa') from location;
#将location表中Store_Name字段记录的字符中的on替换成aa

（三）group by分组

查询结果进行汇总分组，

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

• group by 有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面

1.group by

select 字段1，sum(字段2) from 表名 group by 字段1；

select Store_Name, sum(Sales) from store_info group by Store_Name order by sales desc;
#示例：求store_info表中相同Store_Name营业额的总和并和Store_Name一起按降序排列

2.having

根据group by分组后的结果再进行条件过滤

select 字段,聚合函数(字段) from 表 group by 字段 having 条件表达式;

select Store_Name, sum(Sales) from store_info group by Store_Name having sum(Sales) > 1500;
#示例：求store_info表中相同Store_Name营业额的总和并显示Store_Name和营业额大于1500的记录

（四）as：别名

select "表格別名"."字段1" [AS] "字段別名" from "表格名" [AS] "表格別名";

select A.Store_Name Store, sum(A.Sales) "Total Sales" from store_info A group by A.Store_Name;
#示例：设置表名别名为A，基于营业额总和，将其按降序排列并分组后的表sum(Sales)定义别名为Total Sales

（五）连接查询

先准备location和store_info两个表

1.inner join(内连接)

只返回两个表中联结字段相等的行

select * from location A inner join store_info B on A.Store_Name = B.Store_Name ;
#返回location表和store_info表中Store_Name字段相等的行

2.left join(左连接)

返回包括左表中的所有记录和右表中联结字段相等的记录，不相等的行返回null

select * from location A left join store_info B on A.Store_Name = B.Store_Name ;
#返回包括location左表中的所有记录和store_info右表中Store_Name字段相等的行记录，不相等的行返回null

3.right join(右连接)

返回包括右表中的所有记录和左表中联结字段相等的记录，不相等的行返回null

select * from store_info A left join location B on A.Store_Name = B.Store_Name ;
#返回包括store_info右表中的所有记录和location左表中Store_Name字段相等的行记录，不相等的行返回null

（六）联集

将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类

1.union

生成结果的数据记录值将没有重复，且按照字段的顺序进行排序

[select 语句 1] union [select 语句 2];

select Store_Name from location union select Store_Name from store_info;
#示例：将location和store_info表中的Store_Name字段记录合并、去重

2.union all

将生成结果的数据记录值都列出来，无论有无重复

[select 语句 1] union all [select 语句 2];

select Store_Name from location union all select Store_Name from store_info;
#示例：将location和store_info表中的Store_Name字段所有记录合并,不去重

（七）子查询

连接表格，在where子句或having子句中插入另一个SQL语句

在where子句或having子句中插入的另一个SQL语句为内查询，其查询结果作为外查询（where子句或having子句）的查询条件

select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);

select Store_Name from location where (Store_Name) in (select Store_Name from store_info);
#示例：查询显示location表中Store_Name字段，其取值范围在store_info中的Store_Name字段
      （即查询location表和store_info中Store_Name字段相等的行记录）