MySQL(三)-理论
参考文献
- MySQL 数据库面试题(2021最新版)
- 极客时间–MySQL实战45讲
- MySQL技术内幕 InnoDB存储引擎
- MySQL 设计与开发规范,很详细,你该注意了
- MySQL技术内幕 SQL编程
SQL四种语言: DDL,DML,DCL,TCL
DDL
-
DDL(Data Definition Language ) 数据库定义语言 statements are used to define the database structure or schema.
-
DDL是SQL语言的四大功能之一.用于定义数据库的三级结构,包括外模式、概念模式、内模式及其相互之间的映像,定义数据的完整性、安全控制等约束DDL
-
不需要commit.
1
2
3
4
5
6CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME
DML
-
DML(Data Manipulation Language)数据操纵语言statements are used for managing data within schema objects.
-
由DBMS提供,用于让用户或程序员使用,实现对数据库中数据的操作.DML分成交互型DML和嵌入型DML两类.依据语言的级别,
-
DML又可分成过程性DML和非过程性DML两种.
-
需要commit.
1
2
3
4
5
6
7
8SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN
LOCK TABLE
DCL
-
DCL(Data Control Language)数据库控制语言如授权,角色控制等
1
2
3
4-- 授权
GRANT
-- 取消授权
REVOKE
TCL
-
TCL(Transaction Control Language)事务控制语言
1
2
3
4-- 设置保存点
SAVEPOINT
-- 回滚
ROLLBACK
字段设计规范
-
INT
: 如无特殊需要,存放整型数字使用UNSIGNED INT
型,整型字段后的数字代表显示长度.比如 id int(11) NOT NULL -
DATETIME
: 所有需要精确到时间 (时分秒) 的字段均使用DATETIME
, 不要使用TIMESTAMP
类型.-
对于
TIMESTAMP
,它把写入的时间从当前时区转化为 UTC(世界标准时间)进行存储.查询时,将其又转化为客户端当前时区进行返回.而对于 DATETIME,不做任何改变,基本上是原样输入和输出. -
另外
DATETIME
存储的范围也比较大:-
timestamp
所能存储的时间范围为: ’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’. -
datetime
所能存储的时间范围为: ’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’.
-
-
但是特殊情况,对于跨时区的业务,TIMESTAMP 更为合适.
-
-
VARCHAR
: 所有动态长度字符串 全部使用VARCHAR
类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用 INT 之类的数字来代替;VARCHAR(N)
,-
N 表示的是字符数而不是字节数.比如 VARCHAR(255),可以最大可存储 255 个字符(字符包括英文字母,汉字,特殊字符等).但 N 应尽可能小,因为 MySQL 一个表中所有的 VARCHAR 字段最大长度是 65535 个字节,且存储字符个数由所选字符集决定.
-
如 UTF8 存储一个字符最大要 3 个字节,那么 varchar 在存放占用 3 个字节长度的字符时不应超过 21845 个字符.同时,在进行排序和创建临时表一类的内存操作时,会使用 N 的长度申请内存.(如无特殊需要,原则上单个 varchar 型字段不允许超过 255 个字符)
-
-
TEXT
: 仅仅当字符数量可能超过 20000 个的时候,才可以使用 TEXT 类型来存放字符类数据,因为所有 MySQL 数据库都会使用 UTF8 字符集.- 所有使用 TEXT 类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放,与大文本字段的隔离,目的是.如无特殊需要,不使用 MEDIUMTEXT、TEXT、LONGTEXT 类型
-
对于精确浮点型数据存储,需要使用
DECIMAL
,严禁使用FLOAT
和DOUBLE
. -
如无特殊需要,尽量不使用
BLOB
类型 -
如无特殊需要,字段建议使用
NOT NULL
属性,可用默认值代替NULL
-
自增字段类型必须是整型且必须为
UNSIGNED
,推荐类型为INT
或BIGINT
,并且自增字段必须是主键或者主键的一部分.
数据库三大范式
- 第一范式: 每个列都不可以再拆分.
- 第二范式: 在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分.
- 第三范式: 在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键.
范式的作用
- 它的主要目的是消除数据冗余、提高数据存储的效率,并确保数据的一致性和完整性.
- **消除数据冗余: ** 范式要求将数据分解为更小的关系,以避免在数据库中存储相同的信息多次.这有助于减少存储空间的需求,并提高数据更新的效率.
- **确保数据一致性: ** 通过规范化数据,可以减少数据更新时的不一致性.当数据分布在不同的表中时,如果有必要修改某个值,只需在一个地方进行修改,从而保持数据的一致性.
- **提高查询性能: ** 范式设计通常能够提高查询性能,因为查询可以更有效地利用关联表和索引,而不需要在大量冗余数据中进行搜索.
- **促使更好的设计: ** 范式强制设计者仔细思考数据之间的关系,从而推动更好的数据库设计.这有助于避免一些常见的设计问题,如插入、更新和删除异常.
- **支持数据的逻辑分离: ** 范式设计有助于将数据分解成更小的、相关的部分,从而支持数据库的逻辑分离.这样,可以更容易地进行修改、扩展和维护数据库.
范式的优点
- 范式的更新通常比反范式更快
- 当数据较好的范式化,很少或者没有重复的数据
- 范式化的数据比较小,可以放在内存中,操作比较快
范式的缺点
- 通常需要进行关联
反范式
- 优点:
- 所有的数据都在同一张表中,可以避免关联
- 可以设计有效的索引
- 缺点
- 表格内的冗余比较多,删除数据时候会造成表有些有用的信息丢失.
SQL如何执行
Oracle
1 | SQL语句--> |
MySQL
1 | SQL语句--> |
-
查看一条SQL语句到执行时间分析
1
2
3
4
5
6
7
8-- 查看是否开启,开启后可以让MySQL收集在SQL执行时所使用的资源情况
SELECT @@profiling;
-- 0代表关闭,1代表打开
SET profiling=1;
show profiles;
show profile for query 2;
常见问题
char、varchar的区别是什么?
varchar是变长而char的长度是固定的.
FLOAT和DOUBLE的区别是什么?
- FLOAT类型数据可以存储至多8位十进制数,并在内存中占4字节.
- DOUBLE类型数据可以存储至多18位十进制数,并在内存中占8字节.
请说明varchar和text的区别
- varchar可指定字符数,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字节.
- text类型不能有默认值.
- varchar可直接创建索引,text创建索引要指定前多少个字符.varchar查询速度快于text,在都创建索引的情况下,text的索引几乎不起作用.
- 查询text需要创建临时表.
varchar(50)中50的含义
- 最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样).
int(20)中20的含义
- 是指显示字符的长度,不影响内部存储,只是当定义了ZEROFILL时,前面补多少个 0
SQL执行加载顺序
1 | SELECT DISTINCT |
- 动态调整后执行顺序
1 | FROM < left_table > |
-
在MySQL中,SQL语句的执行顺序通常是以下顺序:
FROM
: 指定要查询的表.ON
: 执行要连接的表JOIN
: 执行各种连接操作.WHERE
: 筛选行,只选择满足条件的行.GROUP BY
: 按一个或多个列分组.WITH ROLLUP
: 按组计算每个聚合函数的值,并添加行,这些行包括所有行或组的聚合函数值.HAVING
: 筛选组,只选择满足条件的组.SELECT
: 选择要返回的列.DISTINCT
: 删除重复的行.ORDER BY
: 按一个或多个列对结果进行排序.LIMIT
: 限制结果集中的行数.
-
逻辑查询具体分析:
-
FROM
: 对FROM
子句中的左表<left_table>
和右表<right_table>
执行笛卡尔积,产生虚拟表VT1
-
ON
: 对虚拟表VT1
应用ON
筛选,只有那些符合<join_condition>
的行才被插入虚拟表VT2
-
JOIN
: 如果执行了OUTER JOIN
(如LEFT OUTER JOIN,RIGHT OUTER JOIN
),那么保留表中未匹配的行作为外部行添加到虚拟表VT2
中,产生虚拟表VT3
.如果FROM
子句包含两个以上表,则对上一个链接生成的结果表VT3
和下一个表重复执行步骤1~步骤3,直到处理完所有的表为止. -
WHERE
: 对虚拟表VT3
应用WHERE
过滤条件,只有符合<where_condition>
的记录才被插入虚拟表VT4
中.-
当前应用
WHERE
过滤器时,两种过滤是不被允许的:-
由于数据还没有分组,因此现在还不能在
WHERE
过滤器中使用where_condition=MIN(col)
这类统计的过滤1
2mysql> SELECT a from t WHERE a = MIN(a);
ERROR 1111 (HY000): Invalid use of group function -
由于没有进行列的选取操作,因此在
SELECT
中使用列的别名也是不被允许的,如SELECT city as c FROM t WHERE c = 'ShangHai'
是不允许出现的.1
2mysql> SELECT a as b from t WHERE b = 'c';
ERROR 1054 (42S22): Unknown column 'b' in 'where clause'
-
-
-
GROUP BY
: 根据GROUP BY
子句中列,对VT4
中的记录进行分组操作,产生VT5
-
WITH ROLLUP
: 对VT5
进行ROLLUP
操作,产生表VT6
-
HAVING
: 对虚拟表VT6
应用HAVING
过滤器,只有符合<having_condition>
的记录才被插入虚拟表VT7
中 -
SELECT
: 选择指定列,插入虚拟表VT8
中 -
DISTINCT
: 去除重复数据,产生虚拟表VT9
- 如果查询指定了
DISTINCT
子句,则会创建一张内存临时表(如果内存中存放不下就放到磁盘上).这张内存临时表的表结构和上一步产生的虚拟表一样,不同的是对进行DISTINCT
操作的列增加了一个唯一索引,以此来去除重复数据.
- 如果查询指定了
-
ORDER BY
: 将虚拟表VT9
中的记录按照<order_by_condition>
进行排序操作,产生虚拟表VT10
. -
LIMIT
: 取出指定行的记录,产生虚拟表VT11
,并返回给查询用户
-
MySQL SQL_MODE
-
MySQL 8.0中默认的SQL模式包括以下模式:
ONLY_FULL_GROUP_BY
、STRICT_TRANS_TABLES
、NO_ZERO_IN_DATE
、NO_ZERO_DATE
、ERROR_FOR_DIVISION_BY_ZERO
和NO_ENGINE_SUBSTITUTION
查看SQL_MODE
1 | SELECT @@global.sql_mode; |
严格模式
- 严格模式是指将
SQL_MODE
变量设置为STRICT_TRANS_TABLES
或STRICT_ALL_TABLES
中至少一种.
modes | 描述 |
---|---|
STRICT_TRANS_TABLES |
如果不能按给定的方式将值插入到事务表中,则中止语句。对于非事务性表,如果值出现在单行语句或多行语句的第一行中,则中止语句。 |
ALLOW_INVALID_DATES |
该选项并不完全对日期的合法性进行检查,指检查月份是否在1~12 之间,日期是否在1~31 之间,该模式仅对DATE 和DATETIME 类型有效,对TIMESTAMP 无效,因为TIMESTAMP 总是要求一个合法的输入 |
ANSI_QUOTES |
启用该模式后,不能使用双引号来引用字符串,因为它将被解释为识别符. |
RROR_FOR_DIVISION_BY_ZERO |
在INS(ERT 或UPDATE 过程中,如果数据被零除(或MOD(X,0) ),则产生错误(否则为警告).如果未使用该模式,那么数据被零除是MySQL 返回NULL .如果用到INSERT IGNORE 或UPDATE INGNORE 中,MySQL 生成被零除警告,但操作结果为NULL |
HIGH_NOT_PRECEDENCE |
NOT 运算符的优先级使得诸如 NOT a BETWEEN b AND c 之类的表达式被解析为 NOT (a BETWEEN b AND c) 。在某些旧版本的 MySQL 中,表达式被解析为 (NOT a) BETWEEN b AND c 。旧的更高优先级行为可以通过启用 HIGH_NOT_PRECEDENCE SQL 模式来获得 |
NO_ZERO_DATE |
NO_ZERO_DATE 模式影响服务器是否允许 '0000-00-00' 作为有效日期。其效果还取决于是否启用严格SQL模式。如果未启用此模式,则允许 '0000-00-00' 并且插入不会产生警告。 如果启用此模式,则允许 '0000-00-00' 并且插入会产生警告。 |
NO_ZERO_IN_DATE |
在严格模式下,不允许日期和月份为零.如"2011-00-01"和"2011-01-00"这样的格式是不允许的.采用日期或月份或零的格式MySQL会直接抛出错误而非警告 |
ONLY_FULL_GROUP_BY |
对于GROUP BY 聚合操作,如果在SELECT 中列没有在GROUP BY 中出现,那么这句SQL是不合法的. |
STRICT_ALL_TABLES |
对所有引擎的表都启用严格模式(STRICT_TRANS_TABLES 只对支持事务的表启用严格模式) |
ANSI |
等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE 的组合 |
ORACLE |
等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY-OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER 的组合 |
TRADITIONAL |
等同于STRICT_TRANS_TABLES 、 STRICT_ALL_TABLES 、 NO_ZERO_IN_DATE 、 NO_ZERO_DATE 、 ERROR_FOR_DIVISION_BY_ZERO 、 NO_ENGINE_SUBSTITUTION |
MySQL
中的NULL
1 | mysql> select 1=NULL; |
GROUP BY
子句把所有NULL
值分到同一组ORDER BY
子句中把所有NULL
值排列在一起- 在
MySQL
中,NULL
值在升序过程中总是首先被选出,即NULL
值在ORDER BY
子句中被视为最小值.
- 在
MySQL
的文件
文件 | 说明 |
---|---|
.ibd | InnoDB存储引擎独立表空间文件 |
.ibdata | InnoDB存储引擎共享表空间文件 |
.ib_logfile* | InnoDB存储引擎redo log文件 |
undo* | InnoDB存储引擎独立undo文件 |
auto.cnf | 用于存放MySQL实例的全局唯一的server-uuid的文件 |