SQL数据操作
2022-07-11 09:41:52 15 举报
AI智能生成
SQL操作知识简要概括
作者其他创作
大纲/内容
SQL执行顺序
SQL执行顺序:
1. FROM:对FROM子句中的前两个表执行笛卡尔积(Cartesian product)(交叉联接),生成虚拟表VT1
2. ON:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
3. OUTER(JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN 或(INNER JOIN),保留表(preserved table:左外部联接把左表标记为保留表,右外部联接把右表标记为保留表,完全外部联接把两个表都标记为保留表)中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3,直到处理完所有的表为止。
4. WHERE:对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
5. GROUP BY:按GROUP BY子句中的列列表对VT4中的行分组,生成VT5.
6. CUBE|ROLLUP:把超组(Suppergroups)插入VT5,生成VT6.
7. HAVING:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.SELECT:处理SELECT列表,产生VT8.
8. DISTINCT:将重复的行从VT8中移除,产生VT9.
9. ORDER BY:将VT9中的行按ORDER BY 子句中的列列表排序,生成游标(VC10).
10. TOP:从VC10的开始处选择指定数量或比例的行,生成表VT11,并返回调用者。
1. FROM:对FROM子句中的前两个表执行笛卡尔积(Cartesian product)(交叉联接),生成虚拟表VT1
2. ON:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
3. OUTER(JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN 或(INNER JOIN),保留表(preserved table:左外部联接把左表标记为保留表,右外部联接把右表标记为保留表,完全外部联接把两个表都标记为保留表)中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3,直到处理完所有的表为止。
4. WHERE:对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
5. GROUP BY:按GROUP BY子句中的列列表对VT4中的行分组,生成VT5.
6. CUBE|ROLLUP:把超组(Suppergroups)插入VT5,生成VT6.
7. HAVING:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.SELECT:处理SELECT列表,产生VT8.
8. DISTINCT:将重复的行从VT8中移除,产生VT9.
9. ORDER BY:将VT9中的行按ORDER BY 子句中的列列表排序,生成游标(VC10).
10. TOP:从VC10的开始处选择指定数量或比例的行,生成表VT11,并返回调用者。
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子句->最终结果
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 子句之后。
1. 不能在 WHERE 子句里面使用 SELECT 子句设置的别名,因为 WHERE 子句先于 SELECT 子句执行;
2. 不能在 WHERE 子句里面过滤 GROUP BY 子句的聚合结果,因为 WHERE 子句先于 GROUP BY 子句执行;
3. 可以在 ORDER BY 子句中引用 GROUP BY 子句聚合的结果,因为 ORDER BY 子句的执行顺序在 GROUP BY 子句之后。
7种join
笛卡尔积概念
笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录
join种类
CROSS JOIN:返回被连接的两个表所有数据行的笛卡尔积
INNER JOIN:求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法
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子句的简化版,它找出两个表中相同的列作为连接条件进行连接。有左自然连接,右自然连接和普通自然连接之分
示意图
SQL编写习惯
1. 不用SQL实现复杂的逻辑,不便于调试
2. 写完SQL先explain查看执行计划(SQL性能优化)
3. 设计表的时候,所有表和字段都添加相应的注释(SQL规范优雅)
4. 变更SQL操作先在测试环境执行,写明详细的操作步骤以及回滚方案,并在上生产前review
5. 写完SQL语句,检查where,order by,group by后面的列,多表关联的列是否已加索引,优先考虑组合索引。(SQL性能优化)
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(*)快的说法
关键字
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 操作符来进行正则表达式匹配
LIKE:LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的
ORDER BY:可以使用任何字段来作为排序的条件,从而返回排序后的查询结果;可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列
GROUP BY :分组语句根据一个或多个列对结果集进行分组;在分组的列上我们可以使用 COUNT, SUM, AVG,等函数做其他操作
HAVING: 用于分组后的其他筛选过滤操作
EXPLAIN: 查看SQL语句的执行计划,对应索引的使用以及查询级别等的分析使用
字符串操作函数:concat、strcmp、length、char_length、upper、ucase、lower、lcase
数值操作函数:abs、ceil、floor、mod、rand、round、truncate
聚集函数:avg、count、max、min、sum
0 条评论
下一页