首页 / Mysql / 快速学习MySQL基础知识

快速学习MySQL基础知识

这篇文章主要梳理了 SQL 的基础用法,会涉及到以下方面内容:SQL 是我们用来最长和数据打交道的方式之一,如果按照功能划分可分为如下 4 个部分:

这篇文章主要梳理了 SQL 的基础用法,会涉及到以下方面内容:

  • SQL大小写的规范
  • 数据库的类型以及适用场景
  • SELECT 的执行过程
  • WHERE 使用规范
  • MySQL 中常见函数
  • 子查询分类
  • 如何选择合适的 EXISTS 和 IN 子查询

了解 SQL

SQL 是我们用来最长和数据打交道的方式之一,如果按照功能划分可分为如下 4 个部分:

  • DDL,数据定义语言。定义数据库对象,数据表,数据列。也就是,对数据库和表结构进行增删改操作。
  • DML,数据操作语言。对数据表的增删改。
  • DCL,数据控制语言。定义访问权限和安全级别。
  • DQL,数据查询语言。用来查询数据。

平时在编写 SQL 时,可能发现许多 SQL 大小写不统一,虽然不会影响 SQL 的执行结果,但保持统一的书写规范,是提高效率的关键,通常遵循如下的原则:

  • 表名,表别名,字段名,字段别名等用小写。
  • SQL 保留字,函数名,绑定变量等用大写。
  • 数据表,字段名采用下划线命名。

目前排名较前的 DBMS:

快速学习MySQL基础知识

  • 关系型数据库:建立在关系模型上的数据库,在建表时,通常先设计 ER 图表示之间的关系。
  • 键值型数据库:以 key-value 的形式存储数据,优点是查找速度快,缺点是无法向关系型数据库一样使用如 WHERE 等的过滤条件。常见场景是作为内容缓存。
  • 文档型数据库,在保存时以文档作为处理信息的基本单位。
  • 搜索引擎:针对全文检索而设计。核心原理是 “倒排索引”。
  • 列式数据库:相对于如 MySQL 等行式存储的数据库,是以列将数据存在数据库中,由于列具有相同的数据类型,所以可以更好的压缩,从而减低系统的 I/O,适用于分布式文件系统,但功能相对有限。
  • 图形数据库,利用图的数据结构存储实体之间的关系。比如社交网络中人与人的关系,数据模型为节点和边来实现。

认识 SELECT

SELECT 一般是在学习 SQL 接触的第一个关键字,基础的内容就是不提了,这里整理常用的规范:

起别名

查询常数, 增加一列固定的常数列:

去重重复行

需要注意的是 DISTINCT 是对后面的所有列进行去重, 下面这种情况就会对 age 和 name 的组合进行去重。

排序数据,ASC 代表升序,DESC 代表降序

如先按照 name 排序,name 相等的情况下按照 age 排序。

限制返回的数量

SELECT 的执行顺序

了解了 SELECT 的执行顺序,才能更好地写出更有效率的 SQL。

对于 SELECT 顺序有两个原则:

  • 关键字的顺序不能颠倒:
  • SELECT 会按照如下顺序执行:

在逐一分析下这个过程前,我们需要知道在上面的每一个步骤中都会产生一个虚拟表,然后将这个虚拟表作为下一个步骤中作为输入,但这一过程对我们来说是不可见的:

  1. 从 FROM 语句开始,对 student 和 class 表进行 CROSS JOIN 笛卡尔积运算,得到虚拟表 vt 1-1;
  2. 通过 ON 筛选,在 vt1-1 的基础上进行过滤然后得到表 vt 1-2;
  3. 添加外部行。如使用左连接,右连接和全连接时,就会涉及到外部行,会在 vt1-2 的基础上增加外部行,得到 vt1-3。
  4. 如果超过两张表,就会重复上面的步骤。
  5. 在拿到最终的 vt1 的表数据后,会执行 WHERE 后面的过滤阶段,得到表 vt2.
  6. 接着到 GROUP 阶段,进行分组得到 vt3.
  7. 接着到 HAVING 阶段,对分组的数据进行过滤,得到 vt4.
  8. 后面进入 SELECT 阶段,提取需要的字段,得到 vt5-1,接着通过 DISTINCT 阶段,过滤到重复的行,得到 vt5-2.
  9. 然后对指定的字段进行排序,进入 ORDER BY 阶段,得到 vt6.
  10. 最后在 LIMIT 阶段,取出指定的行,对应 vt7,也就是最后的结果。

如果涉及到函数的计算比如 sum() 等,会在 GROUP BY分组后,HAVING 分组前,进行聚集函数的计算。

涉及到表达式计算,如 age * 10 等,会在 HAVING 阶段后,SELECT 阶段前进行计算。

  • 通过这里,就可以总结出提高 SQL 效率的第一个方法:

使用 SELECT 时指定明确的列来代替 SELECT * . 从而减少网络的传输量。

使用 WHERE 进行过滤

使用 WHERE 筛选时,常有通过比较运算符,逻辑运算符,通配符三种方式。

对于比较运算符,常用的运算符如下表。

快速学习MySQL基础知识

对于逻辑运算符来说,可以将多个比较运行符连接起来,进行多条件的筛选,常用的运算符如下:

快速学习MySQL基础知识

需要注意的是,当 AND 和 OR 同时出现时,AND 的优先级更高会先被执行。当如果存在 () 的话,则括号的优先级最高。

使用通配符过滤:

like:(%)代表零个或多个字符,(_)只代表一个字符

函数

和编程语言中的定义的函数一样,SQL 同样定义了一些函数方便使用,比如求和,平均值,长度等。

常见的函数主要分为如下四类,分类的原则是根据定义列时的数据类型:

  • 算术函数:

快速学习MySQL基础知识

  • 字符串函数

快速学习MySQL基础知识

需要注意的是,在使用字符串比较日期时,要使用 DATE 函数比较。

  • 日期函数

快速学习MySQL基础知识

  • 转换函数:

快速学习MySQL基础知识

CAST 函数在转换数据类型时,不会四舍五入,如果原数值是小数,在转换到整数时会报错。

在转换时可以使用 DECIMAL(a,b) 函数来规定小数的精度,比如 DECIMAL(8,2) 表示精度为 8 位 – 小数加整数最多 8 位。小数后面最多为 2 位。

然后通过 SELECT CAST(123.123 AS DECIMAL(8,2)) 来转换。

聚集函数

通常情况下,我们会使用聚集函数来汇总表的数据,输入为一组数据,输出为单个值。

常用的聚集函数有 5 个:

快速学习MySQL基础知识

其中 COUNT 函数需要额外注意,具体的内容可以参考这篇。

如何进行分组

在统计结果时,往往需要对数据按照一定条件进行分组,对应就是 GROUP BY 语句。

比如统计每个班级的学生人数:

GROUP BY 后也可接多个列名,进行分组,比如按照班级和性别分组:

HAVING 过滤和 WHERE 的区别

和 WHERE 一样,可以对分组后的数据进行筛选。区别在于 WHERE 适用于数据行,HAVING 用于分组。

而且 WHERE 支持的操作,HAVING 也同样支持。

比如可以筛选大于2人的班级:

子查询

在一些更为复杂的情况中,往往会进行嵌套的查询,比如在获取结果后,该结果作为输入,去获取另外一组结果。

在 SQL 中,查询可以分为关联子查询和非关联子查询。

假设有如下的表结构:

非关联子查询

子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件接着执行。

这里想要查询和胡一相同班级的同学名称:

这里先查到胡一的班级,只有一次查询,再根据该班级查找学生就是非关联子查询。

关联子查询

如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部

再举个例子, 比如查询比每个班级中比平均年龄大的学生姓名信息:

这里根据每名同学的班级信息,查找出对应班级的平均年龄,然后做判断。子查询每次执行时,都需要根据外部的查询然后进行计算。这样的子查询就是关联子查询。

EXISTS 子查询

在关联子查询中,常会和 EXISTS 一起使用。用来判断条件是否满足,满足的话为 True,不满足为 False。

比如查询参加过学校活动的学生名称:

同样 NOT EXISTS 就是不存在的意思,满足为 FALSE , 不满足为 True.

比如查询没有参加过学校活动的学生名称:

集合比较子查询

可以在子查询中,使用集合操作符,来比较结果。

快速学习MySQL基础知识

还是上面查询参加学校活动的学生名字的子查询, 同样可以使用 IN:

EXISTS 和 IN 的区别

既然 EXISTS 和 IN 都能实现相同的功能,那么他们之间的区别是什么?

现在假设我们有表 A 和 表 B,其中 A,B 都有字段 cc,并对 cc 建立了 b+ 索引,其中 A 表 n 条记录,B 表 m 条索引。

将其模式抽象为:

对于 EXISTS 来说,会先对外表进行逐条循环,每次拿到外表的结果后,带入子查询的内表中,去判断该值是否存在。

伪代码类似于下面:

for i in A
    for j in B
        if j.cc == i.cc:
         return result

首先先看外表 A,每一条都需要遍历到,所以需要 n 次。内表 B,在查询时由于使用索引进而查询效率变成 log(m) B+ 的树高,而不是 m。

进而总效率:n * log(m)

所以对于 A 表的数量明显小于 B 时,推荐使用 EXISTS 查询。

再看 IN ,会先对内表 B 进行查询,然后用外表 A 进行判断,伪代码如下:

for i in B
    for j in A
        if j.cc == i.cc:
         return result

由于需要首先将内表所有数据查出,所以需要的次数就是 m. 再看外表 A ,由于使用了 cc 索引,可将 n 简化至 log(n), 也就是 m * log(n).

所以对于 A 表的数据明显大于 B 表时,推荐使用 IN 查询。

总结一下对于 IN 和 EXISTS时,采用小表驱动大表的原则。

这里再扩展下 NOT EXISTS 和 NOT IN 的区别:

对于 NOT EXITS 来说,和 EXISTS 一样,对于内表可以使用 cc 的索引。适用于 A 表小于 B 表的情况。

但对于 NOT IN 来说,和 IN 就有区别了,由于 cc 设置了索引 cc IN (1, 2, 3) 可以转换成 WHERE cc=1 OR cc=2 OR cc=3 , 是可以正常走 cc 索引的。但对于 NOT IN 也就是转化为 cc!=1 OR cc!=2 OR cc!=3 这时由于是不等号查询,是无法走索引的,进而全表扫描。

也就是说,在设置索引的情况下 NOT EXISTS 比 NOT IN 的效率高。

但对于没有索引的情况,IN 和 OR 是不同的:

一、操作不同
1、in:in是把父查询表和子查询表作hash连接。
2、or:or是对父查询表作loop循环,每次loop循环再对子查询表进行查询。

二、适用场景不同
1、in:in适合用于子查询表数据比父查询表数据多的情况。
2、or:or适合用于子查询表数据比父查询表数据少的情况。

三、效率不同
1、in:在没有索引的情况下,随着in后面的数据量越多,in的执行效率不会有太大的下降。
2、or:在没有索引的情况下,随着or后面的数据量越多,or的执行效率会有明显的下降。

总结

这篇文章中主要归纳了一些 SQL 的基础知识:

在使用 SELECT 查询时,通过显式指定列名,来减少 IO 的传输,从而提高效率。

并且需要注意 SELECT 的查询过程会从 FROM 后开始到 LIMIT 结束,理解了整体的流程,可以让我们更好的组织 SQL.

之后详细介绍了 WHERE 进行过滤的操作符和常用的函数,这里要注意在比较时间时要使用 DATE 函数,以及如何对数据进行分组和过滤。

最后着重介绍了子查询,IN 和 EXISTS 的适用场景。

以上就是快速学习MySQL基础知识的详细内容,更多关于MySQL基础知识的资料请关注我是攻城狮其它相关文章!

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/5910.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏