MySQL 高级SQL语句

前言：

MySQL 查询数据的执行过程

客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
MySQL 根据执行计划，调用存储引擎来执行查询。
将结果返回给客户端，同时缓存查询结果。

创建两个表，并插入数据，作为操作对象

use mydb;
create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');

create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');
insert into store_info values('Washington','1000','2020-12-09');
insert into store_info values('Chicago','800','2020-12-10');

use mydb;
create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');

create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');
insert into store_info values('Washington','1000','2020-12-09');
insert into store_info values('Chicago','800','2020-12-10');

一.MySQL进阶查询

1.select

显示表格中一个或数个字段的所有数据记录

语法：select "字段" from "表名";

select store_name from store_info;

2.distinct

不显示重复的数据记录

语法：select distinct "字段" from "表名";

select distinct store_name from store_info;

3.where

有条件查询

语法：select "字段" from "表名" where "条件";

select store_name from store_info where sales > 1000;

4.and、or 且、或

语法：select "字段" from "表名" where "条件1" {[and | or] "条件2"}；

select * from store_info where sales > 500 and sales < 1000;

select * from store_info where store_name='Log angeles' or store_name='Hoston';

select * from store_info where (store_name='Log angeles' or store_name='Hoston');

5.in

显示已知的值的数据记录

语法：select "字段" from "表名" where "字段" in ('值1', '值2', ...);

select * from store_info where store_name in ('Los Angeles', 'Houston');
                                          #显示包含这两个数据的内容

select * from store_info where store_name not in ('Los Angeles', 'Houston');
                                          #显示不包含这两个数据的内容

6.between

显示两个值范围内的数据记录

语法：select "字段" from "表名" where "字段" between '值1' and '值2';

select * from store_info where date between '2020-12-07' and '2020-12-09';

7.通配符

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

% ：百分号表示零个、一个或多个字符

_ ：下划线表示单个字符

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

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

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

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

'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。

例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。

8.like

匹配一个模式来找出我们要的数据记录

语法：select "字段" from "表名" where "字段" like {模式}；

select * from store_info where store_name like '%_os%';

9.order by

按关键字排序

语法：select "字段" from "表名" [ where "条件"] order by "字段" [asc,desc];

#ASC 是按照升序进行排序的，是默认的排序方式。

#DESC 是按降序方式进行排序。

select store_name,sales,date from store_info order by sales desc;

二.MySQL函数

1.数学函数

abs(x)                返回 x 的绝对值

rand()                返回 0 到 1 的随机数

mod(x,y)            返回 x 除以 y 以后的余数

power(x,y)            返回 x 的 y 次方

sqrt(x)                返回 x 的平方根

round(x)            返回离 x 最近的整数

round(x,y)            保留 x 的 y 位小数四舍五入后的值

truncate(x,y)        返回数字 x 截断为 y 位小数的值

ceil(x)                返回大于或等于 x 的最小整数

floor(x)            返回小于或等于 x 的最大整数

greatest(x1,x2...)    返回集合中最大的值，也可以返回多个字段的最大的值

least(x1,x2...)        返回集合中最小的值，也可以返回多个字段的最小的值

2.聚合函数

avg()                返回指定列的平均值

count()                返回指定列中非 NULL 值的个数

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

min()                返回指定列的最小值

max()                返回指定列的最大值

sum(x)                返回指定列的所有值之和

3.字符串函数

拓展：分割字符串的三种方法

[root@localhost ~]# i=abcdefg           定义变量          

[root@localhost ~]# echo ${i:2:2}               第一种       下标从0开始
cd
[root@localhost ~]# expr substr $i 3 2          第二种       下标从1开始
cd
[root@localhost ~]# echo abcdefg | cut -b 3-4   第三种       下标从1开始
cd

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

trim()                返回去除指定格式的值

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 反转

**upper **

concat

** 若在数据库的配置文件中加入了PIPES_AS_CONCAT的配置，也可以使用 || 来进行拼接**

substr

subtring

length

replace

**trim **

left 和 right

三.MySQL 查询函数

1.group by

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

GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；

凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面;

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

select store_name, sum(sales) from store_info group by store_name order bt sales desc;

2.having

用来过滤由 GROUP BY 语句返回的记录集，常与 GROUP BY 语句联合使用

HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。

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

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

3.别名

字段別名表格別名

语法：select "表格別名"."字段1" [as] "字段別名" from "表格名" [as] "表格別名";

select A.store_name store, sum(A.sales) "total sales" from store_info A group by A.store_name;

4.子查询

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

语法：select "字段1" from "表格1" where "字段2" [比较运算符]     #外查询语句

(select "字段1" from "表格2" where "条件");                    #内查询语句

#可以是符号的运算符，例如 =、>、<、>=、<=、!=；也可以是文字的运算符，例如 LIKE、IN、BETWEEN

select sum(sales) from store_info where store_name in (select store_name from location where region = 'West');

5.exists

用来根据条件过滤查询结果，并只返回满足条件的行

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

#这里的子查询作为条件进行判断。如果子查询返回至少一行结果，则外部查询的结果将包含该行

select * from store_info A where exists (select Store_Name from location B where B.store_name = A.store_name);

四.表连接查询

1.连接查询方式

inner join(内连接)：只返回两个表中联结字段相等的行

left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录 

right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录

1.内连接

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

select A.store_name from store_info A inner join location B on A.store_name= B.store_name;

2.左连接

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

select * from location A left join store_info B on A.store_name = B.store_name;

3.右连接

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

select * from location A right join store_info B on A.store_name = B.store_name;

4.full outer join 全外连接

是一种特殊的连接，返回左表和右表中所有的行记录，但是MySQL不支持

5.交集值

取两个SQL语句结果的交集

（1）内连接

内连接：

select A.字段 from 左表 A inner join 右表 B on A.字段=B.字段;
       
select A.字段 from 左表 A inner join 右表 B using(字段);

select A.store_name from store_info A inner join location B on A.store_name= B.store_name;

select distinct A.store_name from store_info A inner join location B on A.store_name= B.store_name;          
       #去除重复值

select A.store_name from store_info A inner join location B using(store_name);

（2）左连接

左连接：

select B.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;

select B.store_name from location A left join store_info B on A.store_name = B.store_name where B.store_name is not null;

（3）右连接

右连接：

select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where A.字段 is not null;

select A.store_name from location A right join store_info B on A.store_name = B.store_name where A.store_name is not null;

（4）子查询

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

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

select A.store_name from store_info A where exists (select B.store_name from location B where A.store_name = B.store_name);

（5）多表查询

select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;

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

（6）联集+分组

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类
UNION ：生成结果的数据记录值将没有重复，且按照字段的顺序进行排序

union 联集语法：

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

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

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

联集+分组：语法

select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A表 group by A.字段 having count(A.字段) > 1;
          
#此处的 A表 是由内查询语句返回的结果 抽象出的派生表

select A.store_name from (select distinct store_name from store_info union all select distinct store_name from location) A groupby A.store_name having count(A.store_name) > 1;

6.无交集值

显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

（1）求左表无交集

select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;

select 字段 from 左表 where 字段 not in (select 字段 from 右表);

select A.字段 from 左表 A where not exists (select B.字段 from 右表 B where A.字段 = B.字段);

select A.store_name from location A left join store_info B on A.store_name = B.store_name where B.store_name is null;

select store_name from location where store_name not in (select store_name from store_info);

select A.store_name from location A where not exists (select B.store_name from store_info B where A.store_name = B.store_name);

（2）求右表无交集

select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;

select 字段 from 右表 where 字段 not in (select 字段 from 左表);

select A.字段 from 右表 A where not exists (select B.字段 from 左表 B where A.字段 = B.字段);

select B.store_name from location A right join store_info B on A.store_name = B.store_name where A.store_name is null;

select store_name from store_info where store_name not in (select store_name from location);

select A.store_name from store_info A where not exists (select B.store_name from location B where A.store_name = B.store_name);

（3）求两个表无交集

select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null

union all

select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;

select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段) = 1;

select A.store_name from location A left join store_info B on A.store_name = B.store_name where B.store_name is null
    
-> union all
    
-> select B.store_name from location A right join store_info B on A.store_name = B.store_name where A.store_name is null;

select A.store_name from (select distinct store_name from store_info union all select distinct store_name from location) A group
by A.store_name having count(A.store_name) = 1;

标签： mysql sql 数据库

本文转载自: https://blog.csdn.net/lxplxplike/article/details/139819745
版权归原作者 Obsession1234 所有，如有侵权，请联系我们删除。