【MySQL】MySQL基础部分知识点

文章目录

1. SQL规范

1.1 SQL大小写规范

  • MySQL 在 Windows 环境下是大小写不敏感的

  • MySQL 在 Linux 环境下是大小写敏感的

    • 数据库名、表名、表的别名、变量名是严格区分大小写的

    • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

  • 推荐采用统一的书写规范

    • 数据库名、表名、表别名、字段名、字段别名等都小写
    • SQL 关键字、函数名、绑定变量等都大写

1.2 数据类型选用规范

  • 任何字段如果为非负数，必须是 UNSIGNED
  • 【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
    说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储
  • 【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型
  • 【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率

1.3 字段是否null相关

建表时尽可能加 not null default ‘’ 或 default 0

  • null不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null
  • 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

2. 使用正则表达式查询

选项 说明 例子 匹配值示例
^ 匹配文本的开始字符 ‘^b’，匹配以字母b开头的字符串 book,big,banana,bike
$ 匹配文本的结束字符 ‘st$’，匹配以st结尾的字符串 test,resist,persist
. 匹配任何单个字符 ‘b.t’，匹配任何b和t之间有一个字符的字符串 bit,bat,but,bite
* 匹配零个或多个在它前面的字符 ‘f*n’，匹配字符n前面有任意个字符f的字符串 fn,fan,faan,fabcn
+ 匹配前面的字符1次或多次 ‘ba+’，匹配以b开头后面至少有1个a的字符串 ba,bay,bare,battle
<字符串> 匹配包含指定的字符串的文本 ‘fa’，匹配包含fa的字符串 fan,afa,faad
[字符串集合] 匹配字符串集合中的任何一个字符 ‘[xz]’，匹配包含x或者z的字符串 dizzy,zebra,extra
[^] 匹配不再括号中的任何字符 '[ ^abc]匹配任何不包含a、b或c的字符串 desk、fox、f8ke
字符串{n,} 匹配前面的字符串至少n次 b{2}，匹配两个或更低的b bbb,bbbbb,bbbbbbb
字符串{n,m} 匹配前面的字符串至少n次，至多m次 b{2,4}，匹配含最少2个，最多4个b的字符串 bb,bbb,bbbb
  • 查询以特定字符或字符串开头的记录 字符‘^’匹配以特定字符或者字符串开头的文本。
    在fruits表中，查询f_name字段以字母‘b’开头的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP '^b';

3. SQL99语法新特性

3.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接

在SQL92标准中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

3.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

4. SELECT 语句的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个 虚拟表 传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的

5. SQL的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

  1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；

  2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；

  3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟
    表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表 vt7 。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序