SQL数据操作

MYSQL - SELECT语句SQL代码：<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]MySQL执行顺序：开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

注意事项：1. 不能在 WHERE 子句里面使用 SELECT 子句设置的别名，因为 WHERE 子句先于 SELECT 子句执行；2. 不能在 WHERE 子句里面过滤 GROUP BY 子句的聚合结果，因为 WHERE 子句先于 GROUP BY 子句执行；3. 可以在 ORDER BY 子句中引用 GROUP BY 子句聚合的结果，因为 ORDER BY 子句的执行顺序在  GROUP BY 子句之后。

SQL执行顺序

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录

笛卡尔积概念

CROSS JOIN：返回被连接的两个表所有数据行的笛卡尔积

LEFT JOIN：LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录

RIGHT JOIN：右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录

OUTER JOIN：外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现。union表示合并加去重

USING：USING的功能相当于ON，区别在于USING指定一个属性名用于连接两个表，而ON指定一个条件; ON子句的语法格式为：table1.column_name = table2.column_name。当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)

NATURE JOIN : 自然连接就是USING子句的简化版，它找出两个表中相同的列作为连接条件进行连接。有左自然连接，右自然连接和普通自然连接之分

join种类

示意图

7种join

1. 不用SQL实现复杂的逻辑，不便于调试

2. 写完SQL先explain查看执行计划(SQL性能优化)

3. 设计表的时候，所有表和字段都添加相应的注释(SQL规范优雅)

4. 变更SQL操作先在测试环境执行，写明详细的操作步骤以及回滚方案，并在上生产前review

6. where后面的字段，留意其数据类型的隐式转换(SQL性能优化)

7. 尽量把所有列定义为NOT NULL(SQL规范优雅)

8. 修改或者删除SQL，先写WHERE查一下，确认后再补充 delete 或 update(SQL后悔药)

9. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释

10. 索引命名要规范，主键索引名为 pk_ 字段名;唯一索引名为 uk _字段名 ; 普通索引名则为 idx _字段名。(SQL规范优雅)

11. 查询中尽量避免使用SELECT * 以及加上LIMIT限制

12. IN 和 NOT IN 要慎用，会导致全表扫描

13. LIKE 因为没遵循最佳左前缀法则导致全表扫描

14. 索引列存在计算、函数、类型转换，会导致索引失效进行全表扫描

15. is null，is not null可能导致索引失效

16. where后面的列要注意隐式转换，会导致索引失效

17. count(*) 和 count(1) 效率是完全一样的，并没有count(1)会比count(*)快的说法

SQL编写习惯

DISTINCT： 查询出某个字段不重复的记录。可用distinct来返回不重复字段的条数count(distinct id)。

LIMIT：它后面可以接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目

OFFSET：selete * from testtable limit 2 offset 1; offset X是跳过X个数据，limit Y是选取Y个数据

UNION & UNION ALL：union all是直接连接，取到得是所有值，记录可能有重复； union 是取唯一值，记录没有重复；UNION和UNION ALL关键字都是将两个结果集合并为一个

IS NULL &  IS NOT NULL & <=> ：MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符和比较操作符

REGEXP：MySQL中使用 REGEXP 操作符来进行正则表达式匹配

ORDER BY：可以使用任何字段来作为排序的条件，从而返回排序后的查询结果；可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列

HAVING: 用于分组后的其他筛选过滤操作

EXPLAIN: 查看SQL语句的执行计划，对应索引的使用以及查询级别等的分析使用

字符串操作函数：concat、strcmp、length、char_length、upper、ucase、lower、lcase

数值操作函数：abs、ceil、floor、mod、rand、round、truncate

聚集函数：avg、count、max、min、sum

关键字

SQL数据操作