【mysql】sql语句平时总结

致最长的电影

已于 2023-04-20 11:28:08 修改

阅读量338

点赞数

分类专栏： Mysql 文章标签： mysql

于 2021-03-16 15:23:22 首次发布

本文链接：https://blog.csdn.net/yuxiangdeming/article/details/114883007

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

sql执行顺序

sql语句定义的顺序

(1) SELECT (2)DISTINCT<select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5)         ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) WITH {CUBE|ROLLUP}
(9) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>

sql语句执行顺序

(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

一共有十一个步骤，最先执行的是 from 操作，最后执行的是 limit 操作。每个操作都会产生一个虚拟表

from：对左表<left_table>和右表<right_table>执行笛卡尔积，产生虚拟表 V1
on：对虚拟表 V1 进行 on 筛选，只有符合<join_condition>的行才被插入到虚拟表 V2
join：如果指定了outer join（如left join、right join），那么保留表中未匹配的行作为外部行添加到虚拟表 V2，产生虚拟表 V3，如果 from 字句包含两个以上的表，则虚拟表 V3 和下一个表重复执行 from 到 join 的步骤
where：对虚拟表 V3 通过 where 过滤，只有符合 <where_condition> 的记录才会被插入虚拟表 V4
group by：根据 group by字句中的列，对 V4 中的记录进行分组操作，产生虚拟表 V5
CUBE|ROLLUP：对 V5 进行 cube 或 rollup 操作，产生表 V6
having：对虚拟表 V6 通过 having 过滤器，只有符合 <having_condition> 的记录才会被插入到 V7
select：执行 select 操作，选择指定的列，插入到虚拟表 V8
distinct：去重重复，产生虚拟表 V9
order by：将虚拟表 V9 中的记录按照 <order_by_list> 进行排序操作，产生虚拟表 V10
limit：取出指定行的记录，产生虚拟表 V11，幷返回给查询用户

on

优先级：
- 两者放置相同条件，之所以可能会导致结果集不同，就是因为优先级。on的优先级是高于where的
首先明确两个概念：
- left join 关键字会从左表 <left_table> 那里返回所有的行，即使在右表 <right_table> 中没有匹配的行
- 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户
在 left join下，两者的区别：
- on 是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表<left_table>的行
- where 则是在生成临时表之后使用的条件，此时已经不管是否使用了 left join 了，只要条件不为真的行，全部过滤掉

测试：

users表：

users_class：在这里插入图片描述

# sql执行流程：首先找到b表的class为一班的记录行on (a.no = b.no and b.class='一班') 。然后找到a的数据（即使不符合b表的规则，生成临时表返回用户）
select a.id, a.no, b.class from users a left join users_class b on (a.no = b.no and b.class='一班')

在这里插入图片描述

~~~mysql
# sql执行流程：首先生成临时表，然后执行where过滤b.class='一班'不为真的结果集，最后返回给用户
select a.id, a.no, b.class from users a left join users_class b on (a.no = b.no) where b.class='一班'
~~~

在这里插入图片描述

总结：
- 因为 on 会首先过滤掉不符合条件的行，然后才会进行其它运算，所以按理说 on 是最快的
- 在多表查询时，on 比 where 更具早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由 where 进行过滤，然后再计算，计算完后再由 having 进行过滤。
- 所有的连接条件都必需要放在 on 后面，不然前面的所有 left 和 right 关联将作为摆设，而不起任何作用

where 1 = 1

在查询页面，并且可查询的选项有多个，适用于多条件查询，避免 where 关键字后面的第一个词直接就是 and 而导致语法错误
如果不加 where 1 = 1，province 和 city 中有传参，那么这个sql将会出错
如果加 where 1 = 1，province 和 city 就算不传参，那么这个sql也不会报错，也是会查询到这张表的数据

<!-- mybatis 查询全国区县 -->
    <select id="selectAllCountryArea" resultType="com.xxx.xxxx.vo.AreaCodeVo">
        select *,district name,area_code areaCode from area_code where 1=1
        <if test="province !=null and province !='' ">
            and province = #{province}
        </if>
        <if test="shiqu !=null and shiqu !='' ">
            and city = #{shiqu}
        </if>
        and district is not null
    </select>

当有两个字段，进行模糊查询，但是前端传的只有一个字段（可以同时传省份或者城市名称）

select *,district name,area_code areaCode from area_code where 1=1 and (province like '%%' or city like '%%')

left join（左连接）

关联多张表查询、读取数据
左连接的含义就是求两个表的交集外加左表剩下的数据。从笛卡尔积的角度讲，就是从笛卡尔积中挑出 on 字句条件成立的记录，然后加上左表中剩余的记录。

在这里插入图片描述

以左表为主表，返回左表的所有行，如果右表中没有匹配，则依然会有左表的记录，右表字段用 null 填充
users表：
users_tag表：

有 on 的情况下

# 一对一  ----->>>>>   只有一个on条件
# users（左表，别名a）和users_tag（右表b）left join操作，关联键为id，关联的键是唯一的，最终输出以左表为准，右表匹配不上补null的结果
SELECT * FROM users a left join users_tag b on a.id = b.id

在这里插入图片描述

# 有两个on条件  ---->>>>>   关联条件中增加了b.dt = 20190909之后的输出结果。由于对b表进行了限制，满足条件的只有一个，但是由于没有where条件，因此依然要以左表为准。在b表中都没有符合条件的结果，因此在以左表为准的基础上，右边的所有字段都为空
select * from users a left join users_tag b on a.id = b.id and b.dt = 20190909

在这里插入图片描述

# 一对多
# users_tag（左表）关联条件为dt
select * from users_tag a left join users b on a.dt = b.dt

在这里插入图片描述

# 多对多
# 不要想当然进行关联查询，这里只是抛砖引玉
SELECT * FROM users a left join users_tag b on a.dt = b.dt

在这里插入图片描述

有 where 的情况下

# on里面有where的情况，将 b.dt = '20190909' 写到where里，发现只有一行，打破了  left join  以左表为主的限制。where在on后面执行，on生成的结果里选择满足条件的记录
select * from users a left join users_tag b on a.id = b.id where b.dt = '20190909'

在这里插入图片描述

结论：

1、on条件是在生成临时表时使用的条件，他不管on中的条件是否为真，都会返回左边表中的记录

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义，条件不为真的就全部过滤掉

有 is null 或者有 is not null 的情况

当条件写在on中：

select * from users a left join users_tag b on a.id = b.id and b.hobbies = '打篮球' where b.id is null

在这里插入图片描述

select * from users a left join users_tag b on a.id = b.id and b.hobbies = '打篮球' where b.id is not null

在这里插入图片描述

当条件写在where中：

select * from users a left join users_tag b on a.id = b.id where b.hobbies = '打篮球' and b.id is  null

在这里插入图片描述

select * from users a left join users_tag b on a.id = b.id where b.hobbies = '打篮球' and b.id is not null

在这里插入图片描述

right join

关联多张表查询、读取数据
与 left join相反，用于获取右表中的记录，即使左表没有对应匹配的记录，左表没有记录的地方均为NULL

cross join

cross join 是mysql中的一种连接方式，区别于内连接和外连接，对于 cross join，其实使用的就是笛卡尔积连接。当cross join 不使用 where 字句时，cross join 产生了一个结果集

注意：

1、cross join 的时候是不需要 on 或者using 关键字的，这个是区别于inner join 和 join 的

# 如果把 cross 改成 inner 得出来的结果是一样的
select * from users a cross join users_tag b

在这里插入图片描述

下图解释了表 users 和 users_tag 之间的笛卡尔积操作：

在这里插入图片描述

inner join

内连接就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中选出满足某条件的记录

# 得出来的结果，就是笛卡尔积之后符合on条件的数据（从笛卡尔积之后选符合条件的数据）
select * from users a inner join users_tag b on a.dt = b.dt

在这里插入图片描述

下图解释了表 users 和 users_tag 之间的内连接操作：

在这里插入图片描述

union

UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同

对两个结果集进行并集操作，重复数据只显示一次
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
```
select * from gc_dfys union select * from ls_jg_dfys
```
- 这个sql在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序
如果您想使用 ORDER BY 或LIMIT 字句来对全部UNION结果进行分类或限制，则应对单个的SELECT语句加圆括号，幷把ORDER BY或LIMIT放到最后一个的后面
```
(select a from tb1_name where a = 10 and B = 1)
union
(select a from tb1_name where a = 11 and B = 2)
order by a limit 10
```

union all

对两个结果集进行并集操作，重复数据全部显示
UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
从效率上说，UNION ALL 要比 UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL

select * from gc_dfys union all select * from ls_jg_dfys

使用UNION，则所有返回的行都是唯一的，如同您已经对整个结果集使用了DISTINCT，使用UNION ALL，则不会排重，返回所有的行。

order by

order by是用来写在 where 之后，给多个字段来排序的一个 DQL 查询语句
- 方式有：asc 升序（默认）、desc 降序，写在最前面的字段优先级最高，如果排序最终有好几条数据相同，则mysql内部随机排序，所以最好还是用主键再次排序一下。

# 优先以cno的升序来排列的，在cno相同的里面再以degree的降序来排列的
select * from score s order by s.cno, s.degree desc;

group by

group by 必须出现在 where 之后 order by 之前
根据 by 对数据按照哪个字段进行分组，或者是哪几个字段进行分组，再对若干个小分组进行数据处理
select 字句中的列名必须为分组列或列函数
语法：

# 根据单位名称分组，没有重复的单位，单位中所属市区shiqu这个字段可能有多个，因为数据库里有相同单位名称的行的数据，所以要对市区shiqu这个字段数据进行处理，只能取一个
select dwmc,max(shiqu) from JIYUE_ZONGHESUZHI group by dwmc

select dwdm, dwmc, shiqu, quyu from JIYUE_ZONGHESUZHI group by dwmc, dwdm, shiqu, quyu

常见聚合函数：
- ＡＶＧ:求某一列平均值
- ＣＯＵＮＴ:统计总行数
- ＳＵＭ：计算列总和
- ＭＩＮ:求某一列的最小值
- ＭＡＸ：求某一列的最大值

where 和 having 区别

having 非常类似于 where
唯一的差别是 where 过滤行，而 having 过滤分组
having 一定要和 group by 连用，用 group by 不一定有 having（只是起筛选条件用）

select 字段 from 表名 where 条件 group by 字段
或者
select 字段 from 表名 group by 字段 having 过滤条件

count(1) 和 count(*) 和 count(字段) 聚合函数

count() 是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数
执行效果上：
- count(1)：在统计结果的时候，包含列字段为null的数据
- count(*)：在统计结果的时候，包含列字段为 null 的数据，会忽略所有的列而直接统计所有的行数
- count(列名)：只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为null时，不统计

select count(*) from student;

limit

当数据量庞大的时候，可以查询前几条数据
limit只在mysql有用

# 通常0是可以省略的，直接写成 limit 10。 0代表从第0条记录后面开始，也就是从第一条开始
# 查询前10条数据
select * from student limit 0, 10;  或者  select * from student limit 10;
# 从数据库中的第5条后面的记录开始展示，10是偏移量。取数据库中的第5条之后的10条记录
select * from student limit 5, 10;

isnull 和 case when then else end 语法（流程控制函数）

判断一个字段是否为空值，返回一个特定的值

in

工作原理：

select * from article where uid in (select uid from user where status = 0)

1、先执行 in 中的查询，并且缓存结果集

2、缓存user中查询出来的uid，article表查询时把article表的uid与缓存数据比较，满足条件的数据加入结果集

in 常用于where 表达式中，其作用是查询某个范围内的数据

# 用法:
select * from table1 where field in (value1, value2, ...)
# 案例：
select * from company where company_name in ('沃太能源公司', '苏州度辰新材料有限公司')

not in 与 in 作用相反
更多情况下，in 列表项的值是不明确的，而可能是通过一个子查询得到的
```
select * from article where uid in (select uid from user where status = 0)
```
- 子查询中返回的结果必须是一个字段列表项
in 列表项不仅支持数字，也支持字符甚至时间日期类型等

如果是对索引字段进行操作，使用OR效率高于IN，但对于列表项不确定的时候（如需要子查询得到结果），就必须使用IN运算符。

in 或 or 在字段没有添加索引的情况下，所连接的字段越多，or 比 in 的查询效率低很多

select * from user where uid in (2, 3, 5)

select * from user where (uid = 2 or aid = 3 or aid = 5)

distinct

经常使用 distinct 返回不重复字段的条数（count（distinct id）），其原因是distinct 只能返回他的目标字段，而无法返回其它字段

# 查询公司名不重复的数据
select distinct company_name from company
# 如果这样写，会认为要过滤掉company_name 和 list_type这两个字段都重复的记录
select distinct company_name, list_type from company
# 这样写mysql会报错，因为distinct必须放在要查询字段的开头，
select list_type, distinct company_name from company
# 所以一般distinct用来查询不重复字段记录的条数
select count(distinct company_name) from company

全文搜索 Match Against用法（全文索引）

概念：
- 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。全文检索在大量的数据面前，能比like + % 快N倍，速度不是一个数量级，但是全文索引可能存在精度问题。
版本支持：
- 只有字段的数据类型为char、varchar、text及其系列才可以建全文索引
使用全文索引：
- 和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用match 和 against关键字，格式：match(columnName–字段) against (‘内容’)，比如：
```
# match()函数中
select * from fulltext_test where match(content, tag) against('xxx xx');
select * from student where match('name', 'address') against('聪 广东');
```
mysql索引类型：
- normal：表示普通索引
- unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
- fulltext：表示全文搜索的索引，FULLTEXT用于搜索很长一篇文章的时候，效果最好。如果就一两行字的，普通的索引也可以。

FIND_IN_SET 和 in 的区别

FIND_IN_SET ：

场景：

有个文章表里面有个type字段，它存储的是文章类型，有 1头条、2推荐、3热点、4图文等等。现在有篇文章他既是头条，又是热点，还是图文，type中以 1,3,4 的格式存储。那我们如何用sql查找所有type中有4的图文类型的文章呢？？

语法：

# str 要查询的字符串
# strlist 字段名  参数以“,”分割  如（1,2,5,6,9），查询字段(strlist)中包含(str)的结果，返回结果为null或记录
FIND_IN_SET(str,strlist)
例子：product_ids 是字段，2ce19877a55c4e51adde7f34898da1f8 是否在该字段之中
 select * from new_hot_type where find_in_set('2ce19877a55c4e51adde7f34898da1f8', product_ids)

区别例子

# 这样查不出来数据，除非 product_ids 字段的值等或者只有‘fe910b4efe4b4bab992d5c95a50289d6’时（和in前面的字符串完全匹配），才能查询有效。product_ids 不能参杂其它任何数据，里面有‘，’分割的数据也不行，即使‘fe910b4efe4b4bab992d5c95a50289d6’ 真的在product_ids 字段中
select * from new_hot_type where 'c9e85b3cfabd46978efda8600387e3fa' in(product_ids)

# 这个相当于where 条件永远为真
select * from new_hot_type where 'c9e85b3cfabd46978efda8600387e3fa' in('fe910b4efe4b4bab992d5c95a50289d6', 'c9e85b3cfabd46978efda8600387e3fa')

在这里插入图片描述

区别例子中，这两条sql到底有什么区别呢？为什么第一条不能取得正确的结果，而第二条却能取得结果。原因其实是第一条sql中 (product_ids) product_ids是变量，而第二条sql中 (‘fe910b4efe4b4bab992d5c95a50289d6’, ‘c9e85b3cfabd46978efda8600387e3fa’)是常量。

所以如果要让第一条sql能正确工作，需要用find_in_set():

select * from new_hot_type where find_in_set('c9e85b3cfabd46978efda8600387e3fa', product_ids)

总结：
所以如果product_ids字段是常量，则可以直接用IN，否则要用find_in_set()函数。

常量和变量

常量：
- 字符串常量：字符串常量指用单引号或双引号括起来的字符序列。在MySQL中推荐使用单引号
- 数值常量：数值常量可以分为整数常量和小数常量。
- 日期和时间常量：日期和时间常量使用特定格式的字符日期值表示，用单引号括起来。
- 布尔值常量：布尔值只有true和false两个值，SQL命令运行结果用1代表true，用0代表false。
- NULL值：适用于各种字段类型，通常表示“不确定的值”，NULL值参与的运算，结果仍为NULL值。

concat 和 concat_ws() 和 group_concat

concat 函数：
- 功能：将多个字符串连接成一个字符串
- 语法：concat(str1, str2,…)
  - 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。(注意null和为空的区别)
  - ```
   select concat(id, ',', name, ',', product_ids) as info from new_hot_type
```
concat_ws() 函数：
- 功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）
- 语法：concat_ws(separator, str1, str2, …)
  - 说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。
    - ```
    select concat_ws(',', id, name, product_ids) as info from new_hot_type
```
- 把分隔符指定为null，结果全部变成了null
  - ```
  select concat_ws(null, id, name, product_ids) as info from new_hot_type
```

group_concat() 函数：

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

举例：

# 使用group_concat()和group by显示相同名字的人的id号,从大到小排序，且用'_'作为分隔符:
select username, group_concat(id order by id desc separator '_') from mms_member group by username

# 上面的查询中显示了以username分组的每组中所有的id。接下来我们要查询以username分组的所有组的id和password：
select username, group_concat(concat_ws('-', id, `password`) order by id) from mms_member group by username

在这里插入图片描述

复制表数据到另一张表

表结构完全一样

  CREATE TABLE 表1 LIKE 表2;
  insert into 表1 select * from 表2

表结构不一样（这种情况下得指定列名）

# 复制表数据 表1是目标表要复制进去的表，表2是要复制的表数据
insert into 表1 (列名1,列名2,列名3) select replace(UUID(), "-", "") as id,列2,列3 from 表2

不同数据库，需要在表前面加数据库前缀，database.表名。

# 复制表数据 mms_member_company是目标表要复制进去的表，mms_member是要复制的表数据
insert into mms_member_company [字段...] select *[字段...] from mms_member

筛选两张表不同的数据 exists 和 not exists

exists：
- 总结：如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件
- 对外表用循环逐条查询，每次查询都会查看exists的条件语句。当 exists里的条件语句能够返回记录行时(无论记录行是多少，只要能返回)，条件就为真 , 返回当前循环到的这条记录。反之如果exists里的条件语句不能返回记录行，条件为假，则当前循环到的这条记录被丢弃。

# 如果mms_member表里username字段里有9132010079043489X6这个数据，将返回mms_member_old表所有数据。反之，如果没有mms_member表里username字段里没有9132010079043489X6这个数据，则什么数据都查不出来，为空
select * from mms_member_old where EXISTS(select * from mms_member where username='9132010079043489X6')

# 对mms_member_z7循环逐条查询，每次查询进入到 exists里条件语句，如果有返回行，则条件为真。意思就是mms_member_z7表对比mms_member表相同的部分。如果加not exists就是mms_member_z7对比mms_member不同的部分，结果打印出来
select * from mms_member_z7 where [not] exists(select * from mms_member where mms_member.id = mms_member_z7.id)
# not in 与上面那个sql是同样的效果
select * from mms_member where username not in (select username from mms_member_company);