更多详情内容请访问:MySQL 系列文章导读

1、SQL 语句

1.1 SQL 分类

  • DDL(Data Definition Languages、数据定义语言),这些语句定义了不同的数据库、表、视图、索引等数据库对象,还可以用来创建、删除、修改数据库和数据表的结构。

    • 主要的语句关键字包括 CREATEDROPALTER等。
  • DML(Data Manipulation Language、数据操作语言),用于添加、删除、更新和查询数据库记录,并检查数据完整性。

    • 主要的语句关键字包括 INSERTDELETEUPDATESELECT等。
  • DCL(Data Control Language、数据控制语言),用于定义数据库、表、字段、用户的访问权限和安全级别。

    • 主要的语句关键字包括 GRANTREVOKECOMMITROLLBACKSAVEPOINT 等。

因为查询语句使用的非常的频繁,所以很多人把查询语句单拎出来一类:DQL(数据查询语言)。

还有单独将 COMMITROLLBACK 取出来称为TCL (Transaction Control Language,事务控制语言)。

1.2 多表查询

1.2.1 笛卡尔积(交叉连接)

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数

image-20220622110008133

SQL92 中,笛卡尔积也称为交叉连接,英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN 表示交叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。在 MySQL 中如下情况会出现笛卡尔积:

#查询员工姓名和所在部门名称
SELECT last_name, department_name FROM employees, departments;
SELECT last_name, department_name FROM employees CROSS JOIN departments;
SELECT last_name, department_name FROM employees INNER JOIN departments;
SELECT last_name, department_name FROM employees JOIN departments;

1.2.2 多表查询分类

  1. 等值连接 VS 非等值连接
    • 等值连接:形如 SELECT emp.ename, dept.dname FROM emp, dept WHERE emp.deptNo = dept.deptNO;
    • 等值连接:形如 SELECT emp.ename, dept.dname FROM emp, dept WHERE emp.deptNo BETWEEN 3 AND 5;
  2. 自连接 VS 非自连接
    • table 1 和 table 2 本质是同一张表,只是用采取别名的方式虚拟两张表以代表不同意义。然后两个表再进行内连接,外连接等查询
  3. 内连接 VS 外连接
    • 内连接(inner join 可省略为 inner): 合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
    • 外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时,结果表中相应的列为空(NULL)
      1. 左外连接(LEFT OUTER JOIN
      2. 右外连接(RIGHT OUTER JOIN
      3. 满外连接(FULL OUTER JOIN):满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据,不过 MySQL 不支持,可以使用 LEFT JOIN UNION RIGHT JOIN 替代

1.2.3 UNION 的使用

  1. UNION:操作符返回两个查询的结果的并集,并且去除重复记录
  2. UNION ALL:操作符返回两个查询的结果集的并集,对于两个结果集的重复部分,不去重

注意:执行 UNION ALL 语句时所需要的资源比 UNION 语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去重,则尽量使用 UNION ALL 语句,提高数据查询效率

1.2.4 七种 JOIN 实现

1554979255233

1.2.5 SQL99 语法新特性(了解)

  1. 自然连接(NATURAL JOIN):可以理解为等值连接,它会自动查询两种连接表中所有相同的字段,然后进行等值连接

    • 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;
  2. USING 连接

    支持使用 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;

1.2.6 WHERE 和 HAVING

WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件

优点 缺点
WHERE 先筛选数据再关联,执行效率高 不能使用分组中的计算函数进行筛选
HAVING 可以使用分组中的计算函数 在最后的结果集中进行筛选,执行效率较低

1.3 子查询

1.3.1 子查询的分类

  • 按照查询的结果返回一条还是多条记录
    1. 单行子查询
    2. 多行子查询
  • 按照查询是否被执行多次
    1. 相关(关联)子查询:如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询
    2. 不相关(非关联)子查询:子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做不相关子查询

1.3.2 相关子查询

如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为关联子查询

相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。

image-20220622170311364

1.3.3 EXISTS 与 NOT EXISTS 关键字

  • 关联子查询通常也会和 EXISTS 操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行:
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行:
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS 关键字表示如果不存在某种条件,则返回 TRUE,否则返回 FALSE
# 查询公司管理者的employee_id,last_name,job_id,department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM   employees e1
WHERE  EXISTS ( SELECT * FROM   employees e2
                WHERE  e2.manager_id = e1.employee_id);

# 等价于
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM   employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

1.4 SQL 书写顺序

select[distinct]  
from  
join(如 left join)  
on  
where  
group by  
having  
union  
order by  
limit

1.5 SQL 执行顺序

from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by  
limit 

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

  1. 首先先通过 CROSS JOIN 求笛卡尔积,相当于得到虚拟表 vt1-1;
  2. 通过 ON 进行筛选,在虚拟表 vt1-1 的基础上进行筛选,得到虚拟表 vt1-2;
  3. 添加外部行。如果我们使用的是左连接、右链接或者全连接,就会涉及到外部行,也就是在虚拟表 vt1-2 的基础上增加外部行,得到虚拟表 vt1-3。

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

当我们拿到了查询数据表的原始数据,也就是最终的虚拟表 vt1,就可以在此基础上再进行 WHERE 阶段。在这个阶段中,会根据 vt1 表的结果进行筛选过滤,得到虚拟表 vt2

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

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

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

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

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

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

1.6 基本的注意点

  1. DISTINCT 关键字需要放到所有列名的前面,对其后面所有的列名进行去重
  2. 所有运算符或列值遇到 null 值,运算结果都为 null,唯独安全运算符(<=>)可以对 null 进行判断,两数都为 null 时返回值为 1,一个操作数为 null 时,返回值为 0
  3. 空值不等于空字符串,一个空字符串的长度是 0,而一个空值的长度是空,在 MySQL 中空值是占用空间的
  4. 分页显示公式:(当前页数-1) * 每页条数, 每页条数
  5. 如果我们使用了表的别名,在查询字段中、过滤条件中就只能使用别名进行代替,不能使用原有的表名,否则就会报错
  6. group by 中使用 WITH ROLLUP 关键字后,在所有查询出来的分组记录之后增加一条统计记录,注意:不能同时使用 ORDER BY 子句进行结果排序,因为他两互相排斥
  7. HAVING 不能单独使用吗,必须要跟 GROUP BY 一起使用
  8. 不能在 WHERE 子句中使用聚合函数
  9. MySQL 执行单条 INSERT 语句插入多行数据比使用多条 INSERT 语句快,所以在插入多条记录时最好选择使用单条 INSERT 语句的方式插入

2、函数

聚合函数不能嵌套使用

  1. AVG():只可以对数值类型

  2. SUM():只可以对数值类型

  3. MAX():可以对任意数据类型

  4. MIN():可以对任意数据类型

  5. COUNT()返回表中记录的总数(包括null),适用于任意数据类型

    COUNT(expr)返回 expr 不为空的记录的总数

问题:用 count(*),count(1),count(列名) 谁好呢?

对于 MyISAM 引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb 引擎的表用 count(*) 和 count(1) 直接读行数,复杂度是 O(n),因为 Innodb 真的要去数一遍。但好于具体的 count(列名)。

3、三大范式

  • 第一范式:字段具有原子性,不可再分(字段单一职责)
  • 第二范式:满足第一范式,满足数据表里的每一条数据记录,都是可唯一标识的,而且所有非主键字段,都必须完全依赖主键,不能只依赖主键的一部分
  • 第三范式:满足前 2 个范式的基础上,要求数据表中的所有非主键字段不能依赖于其他非主键字段

范式只是提出了设计的标准,实际上设计数据表时,未必一定要符合这些标准。开发中,会出现为了性能和读取效率违反范式化的原则,通过增加少量的冗余或重复的数据来提高数据库的读能力,减少关联查询,join 表的次数,实现空间换时间的目的。

  • 优点:数据的标准化有助于消除数据库中的数据冗余,第三范式通常被认为在性能、扩展和数据完整性方面达到了最好的平衡
  • 缺点:范式的使用,可能降低查询的效率。因为范式等级越高,设计出来的数据表就越多,越惊喜,数据冗余度越低,进行数据查询的时候可能需要多表关联,这不但代价昂贵,也可以使一些索引策略失效

END

本文作者:
文章标题:MySQL 基础知识概述
本文地址:https://www.pendulumye.com/mysql/335.html
版权说明:若无注明,本文皆PendulumYe原创,转载请保留文章出处。
最后修改:2022 年 07 月 13 日
千山万水总是情,给个一毛行不行💋