1. mysql 执行顺序

1.1 sql 语句的执行顺序

from…left join … on… where…group by…聚合函数… having…select…order by… limit…

1.2 易混淆1：

order by作用于源表和生成的结果集 所以可以针对select 没有映射到的列进行排序，也可以针对select映射到的列使用新的虚拟表的字段名进行排序。
select name as studentName from student order by [name/studentName]

都行。

1.3 易混淆2

对于where执行顺序
where执行顺序是从左往右执行的，在数据量小的时候不用考虑，但数据量多的时候要考虑条件的先后顺序，此时应遵守一个原则：排除越多的条件放在第一个。

以上说法其实没必要过多关注，mysql不一定会按照我们手写的顺序从左到右执行，而是mysql本身的优化器会帮助我们重写sql语句，通过explain 和 show waings 可以看到重写的sql语句。
image
同样的，mysql 优化器也会将 select From a , b 重写为 select * a join b
image

2. join原理

2.1 Join执行原理解析

一个完整的SQL语句中会被拆分成多个子句，子句的执行过程中会产生虚拟表(vt)，但是结果只返回最后一张虚拟表

FROM:对左右两张表执行笛卡尔积，产生第一张表vt1。行数为n*m（n为左表的行数，m为右表的行数

ON:根据ON的条件逐行筛选vt1，将结果插入vt2中

JOIN添加外部行:，如果指定了LEFT JOIN(LEFT OUTER JOIN)，则先遍历一遍左表的每一行，其中不在vt2的行会被插入到vt2，该行的剩余字段将被填充为NULL，形成vt3；如果指定了RIGHT JOIN也是同理。但如果指定的是INNER JOIN，则不会添加外部行，上述插入过程被忽略，vt2=vt3（所以INNER JOIN的过滤条件放在ON或WHERE里 执行结果是没有区别的）

WHERE:对vt3进行条件过滤，满足条件的行被输出到vt4

SELECT:取出vt4的指定字段到vt5

2.2 牢记on和where的区别

on是连接的时候进行筛选，没有连接成功的还是会添加外部行到结果集中，但是where是对结果集进行筛选，会对所有的结果集进行筛选。

3.注意点总结

3.1 语法

1.聚合函数count() 需要注意的点。
count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。

2.between 包含边界值 not between 不包含边界值

4.常见错误sql及解决

4.1 组内最大

order by + group by
即使是有嵌套也是先执行group by 再执行order by 这也就是不能使用order by + group 取得组内最大值的原因 group by是对表中原来的字段进行分组的***，而不是处理之后的数据。
正确写法：通过子查询或者join


方法一:

select * from a left join b on a.filed=b.filed where b.createdate=(select max(createdate) from b where b.filed=a.filed)

不能通过order by + group by 取是因为不知道分组取数的原则是什么？
select max(createdate) from b where b.filed=a.filed 此处相当于 select max(createdate) from b group b.filed

方法二：

通过group by 和left join 组合使用

1
2
3
4
5
6
7
SELECT
	ph.* 
FROM
	pos_history ph
	RIGHT JOIN ( SELECT Max( create_date ) AS create_date, pos_number FROM pos_history GROUP BY pos_number ) ph2 ON ph.pos_number = ph2.pos_number 
	AND ph.create_date = ph2.create_date 
	) phresult

4.2 连接表查询更新

（在想要更改某个表之前，需要先查询同一个表，并根据查询结果进行更改）

例子： 把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
UPDATE student_score  set score
=
(
SELECT
	AVG( score ) 
FROM
	student_score 
WHERE
	cid IN ( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE NAME = 't1' ) ) 
	) 
WHERE
	cid IN (
SELECT
	cid 
FROM
	course 
WHERE
	tid = ( SELECT tid FROM teacher WHERE NAME = 't1' )
	)

You can’t specify target table ‘student_score’ for update in FROM clause
不能从一张表中查出数据然后再更改同一张表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
UPDATE student_score s
INNER JOIN (
/*a b 笛卡尔积  也可以直接把（select avg(score ..) 作为查询结果集常量的字段 */
SELECT
	a.cid,
	b.score 
FROM
	( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE NAME = 't1' ) ) a,
	(
SELECT
	AVG( score ) score 
FROM
	student_score 
WHERE
	cid IN ( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE NAME = 't1' ) ) 
	) b 
	) c ON s.cid = c.cid 
	SET s.score = c.score

即所有的set select 类型的更新，都可以转化为inner join set 后者再封装一层

4.3 组内取前 (in+limit 报错)

This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ mysql

遇到如上错误可以参考我的另一篇总结
https://zhongzhongbaby.github.io/2019/08/29/mysql%E7%BB%84%E5%86%85%E5%8F%96%E5%89%8D/

5.常用函数

5.1 常用函数

1.find_in_set()

有个文章表里面有个type字段，它存储的是文章类型，有 1头条、2推荐、3热点、4图文等等 。type中以 1,3,4 的格式存储,查找所有type中有4的图文类型的文章

select * from article where FIND_IN_SET(‘4’,type)

FIND_IN_SET(str,strlist)

在mysql中，有时我们在做数据库查询时，需要得到某字段中包含某个值的记录，但是它也不是用like能解决的，使用like可能查到我们不想要的记录，它比like更精准

2.concat （拼接字符串）

select concat (id, name, score) as info from tt2; –>1小明0

concat_ws() 指定分隔符 concat_ws(separator, str1, str2, …)

group_concat()将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
group_concat 常和 find_in_set 一起使用

3.ifnull(tNumTb.num,0)

返回第一个不为空的值

4.(case when pcOut.season=’1’ then ‘2’ when ….

5.substring()

6.Round(double,2) 四舍五入到指定位数的

7.日期函数

date_ForMate(,’%Y-%m_%d’);

UNiX_TIMESTAMP(create_date)

str_to_date()

datetime类型的比较大小

unix_timestamp(time1) > unix_timestamp(‘2011-03-03 17:39:05’)

time1 between ‘2011-03-03 17:39:05’ and ‘2011-03-03 17:39:52’;

6.技巧经验

  1. straight join 就是为了替换 inner join 驱动表顺序的不可控（由mysql 优化器决定）

  2. 使用SQL_CACHE查询
    对于那些变化不频繁的表，查询操作很固定，我们可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用SQL_CACHE 选项

    测试sql语句性能使用SQl_NO_CACHE

  3. delete 数据的时候一定要注意。小心把整张表都删除 例如

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    <!-- 删除后补记录数据 -->
        <delete id="deleteSignupCandidate">
            delete from signup_candidate_info where 1=1
            <choose>
                <when test="ids != null and ids != ''">
                    AND id in
                    <foreach collection="ids.split(',')" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </when>
                <otherwise>
                    AND 1=2
                </otherwise>
            </choose>
        </delete>

  4. CONVERT ( trim( a.NAME ) USING gbk ) 按照中文拼音排序， 但是会导致索引失效。

  5. in () 中值很多的时候，mysql优化器会认为全表扫描速度优于索引，如果和事实相反，可以使用force index 强制索引。

  6. 数据库设计阶段，不一定一定要严格按照三范式，大表需要经常关联的join数据可以字段冗余，一般就业务逻辑而言，生成一次一般就不会再修改了，但是在查询的时候需要经常查询，那么可以做数据冗余，满足第二范式即可。以空间换时间。

  7. 如果业务上需要按照中文姓名排序分页，可以增加姓名拼音字段。因为使用mysql函数，需要转utf8编码为gbk，而且不能使用索引。排序会很慢。

  8. 系统在分页的情况下，使用子查询可能比表连接性能高。对于没有分页的数据还是要优秀考虑 （需要考虑在什么情况下会先执行limit 后执行 子查询？？？？？？？？）

  9. left join 并不一定是左边的表是驱动表，而STRAIGHT_JOIN一定是左边的表是驱动表

  10. 在find_in_set 不能使用索引的情况下，可以考虑通过代码转换为in查询