联系我

MySQL知识拾遗

2020.05.18

前言

MySQL在实际工程开发当中的主要程度不言而喻,但前期一些简单的学习和认知还不够,还需要对其一些关键的知识点进行识记,才能在日后的使用时得心应手,规避很多性能扩展问题。因此,在此记录重学MySQL的一些重要知识点,可能是之前遗忘的,可能是之前没注意到的,也可能是之前都没见过却在实际当中有重要意义的。

关系数据库章节

  • 【理论】数据模型
    • 层次模型:树状
    • 网状模型
    • 关系模型:表格型,严谨的数学证明约束
  • 【约定】数据类型
    • TINYINT:范围在0~255
    • INT: 4字节整数类型,范围约+/-21亿
    • BIGINT: 8字节整数类型,范围约+/-922亿亿
    • REAL(FLOAT(24)):4字节浮点数,范围约+/-1038
    • DOUBLE: 8字节浮点数,范围约+/-10308
    • DECIMAL(M,N):由用户指定精度的小数,例如,DECIMAL(20,10)表示一共20位,其中小数10位,通常用于财务计算
    • CHAR(N):存储指定长度的字符串,例如,CHAR(100)总是存储100个字符的字符串
    • VARCHAR(N):存储可变长度的字符串,例如,VARCHAR(100)可以存储0~100个字符的字符串
    • BOOLEAN:存储True或者False
    • DATE:存储日期,例如,2018-06-22
    • TIME:存储时间,例如,12:20:59
    • DATETIME:存储日期+时间,例如,2018-06-22 12:20:59
  • 【补充】各数据库厂商还会支持特定的数据类型,例如JSON。
  • 【主流】
    • 商用数据库,例如:Oracle,SQL Server,DB2等;
    • 开源数据库,例如:MySQL,PostgreSQL等;
    • 桌面数据库,以微软Access为代表,适合桌面应用程序使用;
    • 嵌入式数据库,以Sqlite为代表,适合手机应用和桌面程序。
  • 【拾遗】各个数据库支持的各自扩展的功能,通常我们把它们称之为“方言”。
  • 【SQL操作】
    • DDL:创建表、删除表、修改表结构这些操作。通常,DDL由数据库管理员执行。
    • DML:添加、删除、更新数据的能力,这些是应用程序对数据库的日常操作。
    • DQL:查询数据,这也是通常最频繁的数据库日常操作。
  • 【约定】实际使用当中,最好SQL关键字总是大写,以示突出,表名和列名均使用小写。

主键章节

  • 【定义】对于关系表,有个很重要的约束,就是任意两条记录不能重复。不能重复不是指两条记录不完全相同,而是指能够通过某个字段唯一区分出不同的记录,这个字段被称为主键。
  • 【原则】记录一旦插入到表中,主键最好不要再修改,因为主键是用来唯一定位记录的,修改了主键,会造成一系列的影响。
  • 【原则】不使用任何业务相关的字段作为主键。如身份证号也是一种业务场景,如果身份证号升位了,或者需要变更,作为主键,不得不修改的时候,就会对业务产生严重影响。因此,身份证号、手机号、邮箱地址这些看上去可以唯一的字段,均不可用作主键。
  • 【产生规则】
    • 自增整数类型
    • 全局唯一GUID类型。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的
    • 雪花算法
  • 【注意】如果使用INT自增类型,那么当一张表的记录数超过2147483647(约21亿)时,会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。
  • 【补充】关系数据库实际上还允许通过多个字段唯一标识记录,即两个或更多的字段都设置为主键,这种主键被称为联合主键。不推荐使用!

外键章节

  • 【介绍】两张表存在一对多关系,可以通过外键把一张表的数据与另一张表关联起来
  • 【好处】通过定义外键约束,关系数据库可以保证无法插入无效的数据。
  • 【劣势】外键约束会降低数据库的性能,大部分互联网应用程序为了追求速度,并不设置外键约束,而是仅靠应用程序自身来保证逻辑的正确性。
  • 【多对多】通过中间表,定义“多对多”关系
  • 【一对一】用处:一些应用会把一个大表拆成两个一对一的表,目的是把经常读取和不经常读取的字段分开,以获得更高的性能。例如,把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles,大部分时候,只需要查询user_info表,并不需要查询user_profiles表,这样就提高了查询速度。

索引章节

  • 【定义】索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。
  • 【索引效率】索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。反过来,如果记录的列存在大量相同的值,例如gender列,大约一半的记录值是M,另一半是F,因此,对该列创建索引就没有意义。
  • 【好处】提高了查询效率
  • 【弊端】在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。
  • 【使用经验】无论是否创建索引,对于用户和应用程序来说,使用关系数据库不会有任何区别。这里的意思是说,当我们在数据库中查询时,如果有相应的索引可用,数据库系统就会自动使用索引来提高查询效率,如果没有索引,查询也能正常执行,只是速度会变慢。因此,索引可以在使用数据库的过程中逐步优化。

查询章节

  • 分页查询
    • 【语法】查询可以通过LIMIT <M> OFFSET <N>子句实现
    • 【约定】LIMIT总是设定为pageSize,OFFSET计算公式为pageSize * (pageIndex - 1)
    • 【约定】OFFSET超过了查询的最大数量并不会报错,而是得到一个空的结果集。
    • 【约定】在MySQL中,LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
    • 【注意】使用LIMIT <M> OFFSET <N>分页时,随着N越来越大,查询效率也会越来越低。
  • 聚合查询
    • 【定义】对于统计总数、平均数这类计算,SQL提供了专门的聚合函数,使用聚合函数进行查询,就是聚合查询,它可以快速获得结果。
    • 聚合函数种类
      • count
      • sum
      • avg
      • max
      • min
    • 【注意】如果聚合查询的WHERE条件没有匹配到任何行,COUNT()会返回0,而SUM()、AVG()、MAX()和MIN()会返回NULL
    • 【实例】也可以使用多个列进行分组。例如,我们想统计各班的男生和女生人数:SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
  • 多表查询
    • 【查询语法】查询多张表的语法是:SELECT * FROM <表1> <表2>
    • 【实例】SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c;
    • 【注意】使用多表查询可以获取M x N行记录;多表查询的结果集可能非常巨大,要小心使用。
  • 连接查询
    • 【规则】JOIN查询需要先确定主表,然后把另一个表的数据“附加”到结果集上;
    • 【常用】INNER JOIN是最常用的一种JOIN查询,它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>;
    • 【规则】JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

修改数据章节

  • update
    • 【规则】如果WHERE条件没有匹配到任何记录,UPDATE语句不会报错,也不会有任何记录被更新。
    • 【注意】UPDATE语句可以没有WHERE条件,这时,整个表的所有记录都会被更新。所以,在执行UPDATE语句时要非常小心,最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集,然后再用UPDATE更新。
    • 【约定】在使用MySQL这类真正的关系数据库时,UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。
  • delete
    • 【规则】delete语句执行后,如果WHERE条件没有匹配到任何记录,DELETE语句不会报错,也不会有任何记录被删除。
    • 【特别注意】和UPDATE类似,不带WHERE条件的DELETE语句会删除整个表的数据
    • 【约定】在使用MySQL这类真正的关系数据库时,DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。

MySQL章节

  • 可以使用命令连接mysql服务器:mysql -h 10.0.1.99 -u root -p
  • 【规则】命令行程序mysql实际上是MySQL客户端,真正的MySQL服务器程序是mysqld,在后台运行。
  • 管理MySQL
    • 【约定】要查看一个表的结构,使用命令:DESC XXXX;
    • 【约定】使用以下命令查看创建表的SQL语句:SHOW CREATE TABLE XXXX;
  • 实用SQL语句:
    • 如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。此时,可以使用REPLACE语句,这样就不必先查询,再决定是否先删除再插入:REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);若id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。
    • 如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句:INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;若id=1的记录不存在,INSERT语句将插入新记录,否则,当前id=1的记录将被更新,更新的字段由UPDATE指定。
    • 如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO ...语句
    • 如果想要对一个表进行快照,即复制一份当前表的数据到一个新表,可以结合CREATE TABLE和SELECT
    • 强制使用指定索引:在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。如:SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;