0
0

MySQL 高级SQL语句

前言：

MySQL 查询数据的执行过程

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

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

  1. use mydb;
  2. create table location (Region char(20),Store_Name char(20));
  3. insert into location values('East','Boston');
  4. insert into location values('East','New York');
  5. insert into location values('West','Los Angeles');
  6. insert into location values('West','Houston');
  8. create table store_info (Store_Name char(20),Sales int(10),Date char(10));
  9. insert into store_info values('Los Angeles','1500','2020-12-05');
  10. insert into store_info values('Houston','250','2020-12-07');
  11. insert into store_info values('Los Angeles','300','2020-12-08');
  12. insert into store_info values('Boston','700','2020-12-08');
  13. insert into store_info values('Washington','1000','2020-12-09');
  14. insert into store_info values('Chicago','800','2020-12-10');
  1. use mydb;
  2. create table location (Region char(20),Store_Name char(20));
  3. insert into location values('East','Boston');
  4. insert into location values('East','New York');
  5. insert into location values('West','Los Angeles');
  6. insert into location values('West','Houston');
  8. create table store_info (Store_Name char(20),Sales int(10),Date char(10));
  9. insert into store_info values('Los Angeles','1500','2020-12-05');
  10. insert into store_info values('Houston','250','2020-12-07');
  11. insert into store_info values('Los Angeles','300','2020-12-08');
  12. insert into store_info values('Boston','700','2020-12-08');
  13. insert into store_info values('Washington','1000','2020-12-09');
  14. insert into store_info values('Chicago','800','2020-12-10');

一.MySQL进阶查询

1.select

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

  1. 语法：select "字段" from "表名";
  3. select store_name from store_info;

2.distinct

不显示重复的数据记录

  1. 语法：select distinct "字段" from "表名";
  3. select distinct store_name from store_info;

3.where

有条件查询

  1. 语法：select "字段" from "表名" where "条件";
  3. select store_name from store_info where sales > 1000;

4.and、or 且、或

  1. 语法：select "字段" from "表名" where "条件1" {[and | or] "条件2"}；
  3. select * from store_info where sales > 500 and sales < 1000;
  5. select * from store_info where store_name='Log angeles' or store_name='Hoston';
  7. select * from store_info where (store_name='Log angeles' or store_name='Hoston');

5.in

显示已知的值的数据记录

  1. 语法：select "字段" from "表名" where "字段" in ('值1', '值2', ...);
  3. select * from store_info where store_name in ('Los Angeles', 'Houston');
  4.                                           #显示包含这两个数据的内容
  6. select * from store_info where store_name not in ('Los Angeles', 'Houston');
  7.                                           #显示不包含这两个数据的内容

6.between

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

  1. 语法：select "字段" from "表名" where "字段" between '值1' and '值2';
  3. select * from store_info where date between '2020-12-07' and '2020-12-09';

7.通配符

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

  1. % ：百分号表示零个、一个或多个字符
  3. _ ：下划线表示单个字符
  5. 'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。
  6. 例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
  8. 'ABC%': 所有以 'ABC' 起头的字符串。
  9. 例如，'ABCD' 和 'ABCABC' 都符合这个模式。
  11. '%XYZ': 所有以 'XYZ' 结尾的字符串。
  12. 例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
  14. '%AN%': 所有含有 'AN'这个模式的字符串。
  15. 例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
  17. '_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。
  19. 例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。

8.like

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

  1. 语法：select "字段" from "表名" where "字段" like {模式}；
  3. select * from store_info where store_name like '%_os%';

9.order by

按关键字排序

  1. 语法：select "字段" from "表名" [ where "条件"] order by "字段" [asc,desc];
  3. #ASC 是按照升序进行排序的，是默认的排序方式。
  5. #DESC 是按降序方式进行排序。
  7. select store_name,sales,date from store_info order by sales desc;

二.MySQL函数

1.数学函数

  1. abs(x)                返回 x 的绝对值
  3. rand()                返回 0 到 1 的随机数
  5. mod(x,y)            返回 x 除以 y 以后的余数
  7. power(x,y)            返回 x 的 y 次方
  9. sqrt(x)                返回 x 的平方根
  11. round(x)            返回离 x 最近的整数
  13. round(x,y)            保留 x 的 y 位小数四舍五入后的值
  15. truncate(x,y)        返回数字 x 截断为 y 位小数的值
  17. ceil(x)                返回大于或等于 x 的最小整数
  19. floor(x)            返回小于或等于 x 的最大整数
  21. greatest(x1,x2...)    返回集合中最大的值，也可以返回多个字段的最大的值
  23. least(x1,x2...)        返回集合中最小的值，也可以返回多个字段的最小的值

2.聚合函数

  1. avg()                返回指定列的平均值
  3. count()                返回指定列中非 NULL 值的个数
  5. #count(*) 包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL
  6. #count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行
  8. min()                返回指定列的最小值
  10. max()                返回指定列的最大值
  12. sum(x)                返回指定列的所有值之和

3.字符串函数

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

  1. [root@localhost ~]# i=abcdefg           定义变量          
  3. [root@localhost ~]# echo ${i:2:2}               第一种       下标从0开始
  4. cd
  5. [root@localhost ~]# expr substr $i 3 2          第二种       下标从1开始
  6. cd
  7. [root@localhost ~]# echo abcdefg | cut -b 3-4   第三种       下标从1开始
  8. cd
  1. concat(x,y)            将提供的参数 x 和 y 拼接成一个字符串
  3. substr(x,y)            获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
  5. substr(x,y,z)        获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
  7. length(x)            返回字符串 x 的长度
  9. replace(x,y,z)        将字符串 z 替代字符串 x 中的字符串 y
  11. trim()                返回去除指定格式的值
  13. upper(x)            将字符串 x 的所有字母变成大写字母
  15. lower(x)            将字符串 x 的所有字母变成小写字母
  17. left(x,y)            返回字符串 x 的前 y 个字符
  19. right(x,y)            返回字符串 x 的后 y 个字符
  21. repeat(x,y)            将字符串 x 重复 y 次
  23. space(x)            返回 x 个空格
  25. strcmp(x,y)            比较 x 和 y，返回的值可以为-1,0,1
  27. 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 后面;

  1. 语法：select "字段1", sum("字段2") from "表名" group by "字段1";
  3. select store_name, sum(sales) from store_info group by store_name order bt sales desc;

2.having

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

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

  1. 语法：select "字段1", sum("字段2") from "表格名" group by "字段1" having (函数条件);
  3. select store_name, sum(sales) from store_info group by store_name having sum(sales) > 1000;

3.别名

字段別名 表格別名

  1. 语法：select "表格別名"."字段1" [as] "字段別名" from "表格名" [as] "表格別名";
  3. select A.store_name store, sum(A.sales) "total sales" from store_info A group by A.store_name;

4.子查询

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

  1. 语法：select "字段1" from "表格1" where "字段2" [比较运算符]     #外查询语句
  3. (select "字段1" from "表格2" where "条件");                    #内查询语句
  5. #可以是符号的运算符，例如 =、>、<、>=、<=、!=；也可以是文字的运算符，例如 LIKE、IN、BETWEEN
  7. select sum(sales) from store_info where store_name in (select store_name from location where region = 'West');

5.exists

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

  1. 语法：select "字段1" from "表格1" where exists (select * from "表格2" where "条件");
  3. #这里的子查询作为条件进行判断。如果子查询返回至少一行结果，则外部查询的结果将包含该行
  5. select * from store_info A where exists (select Store_Name from location B where B.store_name = A.store_name);

四.表连接查询

1.连接查询方式

  1. inner join(内连接)：只返回两个表中联结字段相等的行
  3. left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录 
  5. right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录

1.内连接

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

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

2.左连接

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

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

3.右连接

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

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

4.full outer join 全外连接

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

5.交集值

取两个SQL语句结果的交集

（1）内连接

  1. 内连接：
  3. select A.字段 from 左表 A inner join 右表 B on A.字段=B.字段;
  4.        
  5. select A.字段 from 左表 A inner join 右表 B using(字段);
  7. select A.store_name from store_info A inner join location B on A.store_name= B.store_name;
  9. select distinct A.store_name from store_info A inner join location B on A.store_name= B.store_name;          
  10.        #去除重复值
  12. select A.store_name from store_info A inner join location B using(store_name);

（2） 左连接

  1. 左连接：
  3. select B.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;
  5. 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） 右连接

  1. 右连接：
  3. select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where A.字段 is not null;
  5. 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）子查询

  1. select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);
  2.         
  3. select A.字段 from 左表 A where exists (select B.字段 from 右表 B where A.字段 = B.字段);
  5. select store_name from store_info A where A.store_name in (select B.store_name from location B);
  7. 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）多表查询

  1. select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;
  3. select A.store_name from store_info A, location B where A.store_name = B.store_name;

（6）联集+分组

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

  1. union 联集语法：
  3. [select 语句 1] union [select 语句 2];
  5. union all ：将生成结果的数据记录值都列出来，无论有无重复
  7. 语法：[select 语句 1] union all [select 语句 2];

  1. 联集+分组：语法
  3. select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A表 group by A.字段 having count(A.字段) > 1;
  4.           
  5. #此处的 A表 是由内查询语句返回的结果 抽象出的派生表
  7. 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）求左表无交集

  1. select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;
  3. select 字段 from 左表 where 字段 not in (select 字段 from 右表);
  5. select A.字段 from 左表 A where not exists (select B.字段 from 右表 B where A.字段 = B.字段);
  7. 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;
  9. select store_name from location where store_name not in (select store_name from store_info);
  11. 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）求右表无交集

  1. select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;
  3. select 字段 from 右表 where 字段 not in (select 字段 from 左表);
  5. select A.字段 from 右表 A where not exists (select B.字段 from 左表 B where A.字段 = B.字段);
  7. 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;
  9. select store_name from store_info where store_name not in (select store_name from location);
  11. 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）求两个表无交集

  1. select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null
  3. union all
  5. select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;
  7. select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段) = 1;
  9. 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
  10.     
  11. -> union all
  12.     
  13. -> 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;
  15. select A.store_name from (select distinct store_name from store_info union all select distinct store_name from location) A group
  16. by A.store_name having count(A.store_name) = 1;

标签： mysql sql 数据库
本文转载自: https://blog.csdn.net/lxplxplike/article/details/139819745
版权归原作者 Obsession1234 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL 高级SQL语句”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？