Mysql易错知识点整理（待更新）

Note：这里根据 CSDN Mysql技能树 整理的易错题，可参考MySQL 有这一篇就够，MySQL详细学习教程（建议收藏），MySQL 菜鸟教程

一、数据库的使用

• 初始化账户（create + grant），参考mysql创建新用户并授权
• mysql登录，登录远程服务器，参考MySQL的两种登录方式，MySQL登录教程
  Note：
  • 如果Joe的开发机上已经部署了ssh服务，那么他不可以通过mysql -h Joe的开发机地址-u joe-p mysql在局域网内的任何计算机上使用。
  • mysql -h IP地址 -u 用户 -p 密码 -P 端口登录。
• 创建和删除数据库（create database 数据库名 或者 drop ...）
• 创建表（create）
• 自增字段（id INT PRIMARY KEY AUTO_INCREMENT）
• 删除表（drop table 表名），参考MySQL 有这一篇就够
• 数据库编码，参考MySql修改数据库编码为UTF8，show create database语法
• MySQL 存储引擎，确保数据表的存储引擎为innodb，参考MySQL常见的三种存储引擎，mysql索引详解
  Note：
  • InnoDB 支持事务和外键，有更好的并发能力。更新（删除）操作频率也高，或者要保证数据的完整性，比如OA自动化办公系统。
  • MyISAM 不支持事务和外键，结构简单，可以压缩为只读状态。以读写插入为主的应用程序，比如博客系统、新闻门户网站。
  • Memory 引擎将数据保存在内存中，重启会丢失数据，读速度快很快，适合作为会话表和缓存表。
  • 临时表不一定使用Memory引擎：
    • 内存临时表采用的是memory存储引擎
    • 磁盘临时表采用的是myisam存储引擎 (磁盘临时表也可以使用innodb存储引擎,通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎,
      从mysql5.7.6之后默认为innodb存储引擎,之前版本默认为myisam存储引擎。

Note：

• DDL代表数据定义语言，是一种有助于创建数据库模式的SQL命令。DDL中常用的命令有：create，drop，alter，truncate和rename等等。
• DML代表数据操作语言，是一种有助于检索和管理关系数据库中数据的SQL命令。DML中常用的命令有：insert，update，delete和select等等。

二、数据类型

• 数值的隐式类型转换，可参考MySQL 的隐式转换
  Note：
  • 若字符串是以数字开头，并且全部都是数字，则转换的数字结果是整个字符串；部分是数字，则转换的数字结果是截止到第一个不是数字的字符为止。比如varchar str = "123dafa" // 转换为数字是 123
  • DECIMAL(P,D)：P是表示有效数字数的精度，P范围为1〜65，D是表示小数点后的位数，D的范围是0~30，MySQL要求D小于或等于(<=)P 比如DECIMAL(4,2)，值为23.46。
  • 比如对于int类型的x和y，select id, (x^2 + y^2)/2 as result from points;输出的result类型为decimal类型。
• 时间默认值（default约束，这题datetime和timestamp都行)，可参考MySQL默认值约束，mysql datetime与timestamp区别
• 文本字段，可参考MySQL 中 varchar 和 char 区别，MySQL 中面对VARCHAR 和 TEXT该如何选择 ？，mysql一个字符占用多少个字节
  Note：
  • varchar类型的长度是可变的，而char类型的长度是固定的，当定义为 char(10)时，即使插入的内容是 abc 3 个字符，它依然会占用 10 个字符，其中包含了 7 个空字符的存储空间。
  • char 长度最大为 255 个字符，varchar 长度最大为 65535 个字符；varchar类型的查找效率比较低，而char类型的查找效率比较高，因此变长度使用 varchar，固定长度使用char。
  • MySQL 5.0.3版的一项更改包括将VARCHAR字段的最大长度从255个字符增加到65,535个字符。这使得VARCHAR类型比以往任何时候都更类似于TEXT。虽然两种数据类型共享的最大长度为65,535个字符，但VARCHAR中的VAR表示您可以将最大大小设置为1到65,535之间的任何值。 TEXT字段的最大固定大小为65,535个字符。
  • MEDIUMTEXT最多可存储16 MB的字符串，LONGTEXT最多可存储4 GB的字符串。
• 二进制字段（该题目有点问题，应该使用mediumBlob），可参考MySQL各个类型详解（看评论），Blob数据类型及应用，MySQL：BLOB与TEXT及其最大存储限制
  Note：
  • blob用于存放二进制数，最大字节数为65535，即文件大小最大为65K；而varchar最大字符数为65535。
  • char,varchar,text是以字符为基本单位；而blob是以字节为基本单位，没有字符集的说法。

三、数据修改

• 插入数据（将源表数据复制到目标表中），可参考INSERT INTO SELECT 用法，select into from用法（mysql中不可用），select into from语句
  Note：
  • INSERT INTO SELECT语句从一个表复制数据，然后把数据插入到一个已存在的表中，要求目标表和源表都存在，mysql支持INSERT INTO SELECT语句。
  • SELECT INTO FROM语句在插入时会自动创建目标表，并将源表中指定字段数据插入到目标表中，这里要求目标表不存在。但mysql中不可用 SELECT INTO FROM语句，建议先创建目标表，接着使用INSERT INTO SELECT语句把源表数据转存到目标表中。
• 更新数据
• 删除数据

四、数据查询

• select查询，参考sql和MySQL的查询语句的执行顺序
  Note：
  • from
  • on
  • join
  • where
  • group by(开始使用select中的别名，后面的语句中都可以使用)
  • avg,sum…
  • having
  • select
  • distinct
  • order by
  • limit
    所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
• where条件查询（指定查询语句的条件限制）
• 数值计算，参考MySQL运算符(外加优先级)
• 函数与过程（该题有问题），参考MySQL的存储函数与存储过程的区别，存储过程—输入参数(in) / 输出参数(out) / 输入输出参数(inout)，MySQL的存储过程—流程控制-case
  Note：
  存储函数与存储过程的区别
  • 存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
  • 存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
  • 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
  • 存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
  • 存储过程可以调用存储函数、但函数不能调用存储过程。
  • 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

五、数据库组成

• 表的基本结构（InnoDB存储引擎用B+Tree实现其索引结构，B+树相较于B-树的特点是：非叶子节点只存储键值信息；所有叶子节点之间都有一个链指针；数据记录都存放在叶子节点中），可参考mysql索引详解

• 建表语句（根据业务要求建表）

• 主键（主键或唯一键可以被引用为外键约束foregin key，可 级联更新 和 级联删除），可参考MySQL外键约束详解

• 唯一约束1（如何保证user输入的book唯一，在 isbn 列上加唯一约束或者设置为主键），唯一约束2（不修改表字段，可添加唯一约束），可参考MySQL添加唯一约束（UNIQUE）
  Note：
  唯一约束用来保证一列（或一组列）中的数据是唯一的，类似与主键，但是有以下区别：

  • 表可包含多个唯一约束，但每个表只允许一个主键。
  • 唯一约束列可包含 NULL 值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用。
  • 唯一约束不能用来定义外键。

• 自增序列（自增字段必须是主键；即使插入操作失败，自增计数仍然会被递增，下次操作使用下一个整数；），可参考关于 MySQL 自增 ID 的事儿，自增主键与UUID的优缺点
  Note：

  • 自增主键的优点：索引空间占比小、范围查询与排序都友好、避免像 UUID 这样随机字符串带来的页分裂问题；
    缺点：当系统与其它系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突（前提是老系统也是数字型的）。特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时，自增主键将是你的噩梦。
  • UUID唯一识别码 (Universally Unique Identifier)：是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。
    优点：出现数据拆分、合并存储的时候，能达到全局的唯一性。
    缺点：影响插入速度，并且造成硬盘使用率低；uuid之间比较大小相对数字慢不少， 影响查询速度；uuid占空间大， 如果你建的索引越多，影响越严重。
  • 当自增 ID 到达上限后，受到主键数据类型的影响，计数器发放的下一个 ID 也是当前这个 Max ID ，当执行语句时则会提示主键冲突：1062 - Duplicate entry ‘4294967295’ for key ‘PRIMARY’，此时可以直接选择 Bigint 类型，它的取值范围是无符号情况下： 0 ~ 2^64–1（18446744073709551615），在一般场景下它是足够使用：假设每秒增加的记录数为1亿/秒，大约5849年才会用完。

• 视图（ 视图是从一个或多个表中导出来的表，是一种虚拟存在的表，其物理上是不存在的，但可用于查询），可参考【MySQL视图】视图的概念、创建、查看、删除和修改

• 计税（存储函数，create function得到个人所得税函数），可参考MySQL的存储函数
  Note：

  create function individual_income_tax(salary decimal(12, 4)) 
  returns decimal(12, 4)
  deterministic
  begin 
      -- ...
  end;
  1
  2
  3
  4
  5
  6

• 创建存储过程，参考存储过程——输入参数（in）、输出参数（out）、输入输出参数（inout）
  Note：

  create procedure sp_idt(in salary decimal(12, 4), out tax decimal(12, 4), out take_home decimal(12, 4)) 
  begin 
      set tax = individual_income_tax(salary);
      set take_home = salary - tax;
  end;
  1
  2
  3
  4
  5

  触发器是一种特殊的存储过程。

• 删除函数（drop function individual_income_tax;）

• 触发器，交易审计（定义insert和delete类型的trigger），可参考MySQL触发器
  Note：

  • 触发器是一种特殊的存储过程，触发器和存储过程一样是一个能完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作（insert,update,delete）时自动触发这个SQL片段的执行, 无需手动调用。语法如下：

    create trigger 触发器名 before|after 触发事件
    on 表名 for each row
    begin
    	执行语句列表
    end;
    1
    2
    3
    4
    5

  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作，例如：

    create trigger trigger_test after insert
    on user for each row
    begin
    	insert into user_logs values(1,now,'有新用户增加');
    end 
    1
    2
    3
    4
    5

  • MYSQL中触发器中不能对本表进行insert,update,delete操作,以免递归循环触发
  • 尽量少使用触发器,假设触发器触发每次执行1s, insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就要花费500s, 而insert 500条数据一共是1s，那么insert的效率就非常的低了。
  • 触发器是针对每一行的,对增删改非常频繁的表上切记不要使用触发器，因为他会非常消耗资源。
  • 查看触发器：show triggers;，删除触发器drop trigger if exists trigger_test;。

• 存储引擎修改（将MyISAM修改成innoDB：alter table goods engine innodb;）

六、查询进阶

• 别名（as可省略），可参考mysql中别名(列别名和表别名)，MySQL 别名
  Note：
  不能在WHERE子句中使用列别名。原因是当MySQL评估求值WHERE子句时，SELECT子句中指定的列的值可能尚未确定。
• BETWEEN AND（配Where不配Having，WHERE age BETWEEN 18 AND 24相当于WHERE age >= 18 AND age <= 24），可参考MySQL中的BETWEEN...AND的用法（BETWEEN是闭区间）
• MySQL透视表（透视表的核心就是按照行列同时分组（首行还是列名，其余行为汇总的数据），然后对分组后的值进行某种汇总运算，MySQL用group by进行分组，用case when ... then ... when ... then ... end增加多个判断条件），可参考Sql 实现数据透视表功能，MySQL之数据分组与数据透视表
• 数据去重 - DISTINCT，可参考DISTINCT关键字理解
• 排序 - order by ，可参考MySQL order by关键字详解
  Note：
  • 按dept升序按salary降序排序：select id, name, dept, salary from employee order by dept, salary desc;
• 两个结果集取并集 - UNION（不包括重复行，进行默认规则的排序），可参考MySQL的union 和 union all
  Note：
  • UNION 和 UNION ALL 内部的 SELECT 语句必须拥有相同数量的列。
  • 每条 SELECT 语句中列的顺序必须相同，因为union时是按照字段顺序匹配，而不是按照字段名称匹配。
  • union 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
  • union all 对两个结果集进行并集操作，包括重复行，不进行排序。
• 正则表达式REGEXP 与 模糊查询like，可参考MySQL中的正则表达式（REGEXP），MySql like模糊查询语句用法，MySQL学习笔记: like和regexp的区别
  Note：
  • 可以在 WHERE 子句中使用LIKE子句，并且使用LIKE子句代替等号 =。

    '%a'     以a结尾的数据
    'a%'     以a开头的数据
    '%a%'    含有a的数据
    '_a_'    三位且中间字母是a的
    '_a'     两位且结尾字母是a的
    'a_'    两位且开头字母是a的
    1
    2
    3
    4
    5
    6

  • 搜索 Contacts.Phone 以查找以 00 结尾的所有电话号码：

    SELECT Surname, Surname, City, Phone
    FROM Contacts
    WHERE Phone REGEXP '\\d{8}00';
    1
    2
    3

• 分页查询 - limit，可参考MySQL中limit用法

七、内置函数

八、聚合和分组

九、子查询

十、连接查询

十一、索引