一·理解SQL执行顺序
- 手写顺序
SELECT DISTINCT
<select_list>
FROM
<left_table><join_type>
JION
<right_tablr>ON<join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
Having
<having_condition>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
- 机读顺序
FROM
<left_table>
ON
<join_condition>
<join_type>
JION
<right_tablr>
WHERE
<where_condition>
GROUP BY
<group_by_list>
Having
<having_condition>
SELECT DISTINCT
<select_list>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
二·JOIN类型
三·索引
- 索引(Index)
帮助MySQL高效获取数据的数据结构,影响查找和排序。 - 分类
- 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:一个索引包含多个列
- 语法
创建
删除CREATE [UNIQUE] INDEX index_name ON mytable(columnname(lenght)); ALTER mytable ADD [unique] INDEX [indexname] ON (columnname(lenght));
查看DROP INDEX [indexname] ON mytable
SHOW INDEX FROM tabel_name \G
- 怎么创建索引
什么情况需要创建索引
什么情况不能创建索引
- 索引失效的原因