参考文献

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
    6
    CREATE
    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
    8
    SELECT
    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

字段设计规范

  1. INT: 如无特殊需要,存放整型数字使用UNSIGNED INT型,整型字段后的数字代表显示长度.比如 id int(11) NOT NULL

  2. 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 更为合适.

  3. VARCHAR: 所有动态长度字符串 全部使用 VARCHAR 类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用 INT 之类的数字来代替；VARCHAR(N),

    • N 表示的是字符数而不是字节数.比如 VARCHAR(255),可以最大可存储 255 个字符(字符包括英文字母,汉字,特殊字符等).但 N 应尽可能小,因为 MySQL 一个表中所有的 VARCHAR 字段最大长度是 65535 个字节,且存储字符个数由所选字符集决定.

    • 如 UTF8 存储一个字符最大要 3 个字节,那么 varchar 在存放占用 3 个字节长度的字符时不应超过 21845 个字符.同时,在进行排序和创建临时表一类的内存操作时,会使用 N 的长度申请内存.(如无特殊需要,原则上单个 varchar 型字段不允许超过 255 个字符)

  4. TEXT: 仅仅当字符数量可能超过 20000 个的时候,才可以使用 TEXT 类型来存放字符类数据,因为所有 MySQL 数据库都会使用 UTF8 字符集.

    • 所有使用 TEXT 类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放,与大文本字段的隔离,目的是.如无特殊需要,不使用 MEDIUMTEXT、TEXT、LONGTEXT 类型

  5. 对于精确浮点型数据存储,需要使用 DECIMAL,严禁使用 FLOAT 和 DOUBLE.

  6. 如无特殊需要,尽量不使用 BLOB 类型

  7. 如无特殊需要,字段建议使用 NOT NULL 属性,可用默认值代替 NULL

  8. 自增字段类型必须是整型且必须为 UNSIGNED,推荐类型为 INT 或 BIGINT,并且自增字段必须是主键或者主键的一部分.

数据库三大范式

  • 第一范式: 每个列都不可以再拆分.
  • 第二范式: 在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分.
  • 第三范式: 在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键.

范式的作用

  • 它的主要目的是消除数据冗余、提高数据存储的效率，并确保数据的一致性和完整性.
  • **消除数据冗余: ** 范式要求将数据分解为更小的关系，以避免在数据库中存储相同的信息多次.这有助于减少存储空间的需求，并提高数据更新的效率.
  • **确保数据一致性: ** 通过规范化数据，可以减少数据更新时的不一致性.当数据分布在不同的表中时，如果有必要修改某个值，只需在一个地方进行修改，从而保持数据的一致性.
  • **提高查询性能: ** 范式设计通常能够提高查询性能，因为查询可以更有效地利用关联表和索引，而不需要在大量冗余数据中进行搜索.
  • **促使更好的设计: ** 范式强制设计者仔细思考数据之间的关系，从而推动更好的数据库设计.这有助于避免一些常见的设计问题，如插入、更新和删除异常.
  • **支持数据的逻辑分离: ** 范式设计有助于将数据分解成更小的、相关的部分，从而支持数据库的逻辑分离.这样，可以更容易地进行修改、扩展和维护数据库.

范式的优点

  • 范式的更新通常比反范式更快
  • 当数据较好的范式化,很少或者没有重复的数据
  • 范式化的数据比较小,可以放在内存中,操作比较快

范式的缺点

  • 通常需要进行关联

反范式

  • 优点:
    • 所有的数据都在同一张表中,可以避免关联
    • 可以设计有效的索引
  • 缺点
    • 表格内的冗余比较多,删除数据时候会造成表有些有用的信息丢失.

SQL如何执行

Oracle

1
2
3
4
5
6
7
SQL语句-->
语法检查-->
语义检查-->
权限检查-->
共享池检查
	-->硬解析-->优化器-->执行
	-->软解析-->执行

MySQL

1
2
3
4
5
6
7
8
9
10
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;
    img

常见问题

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT
	< select_list > 
FROM
	< left_table > 
	< join_type > JOIN < right_table > 
	ON < join_condition > 
WHERE
	< where_condition > 
GROUP BY
	< group_by_list >  ... [WITH ROLLUP]
HAVING
	< having_condition > 
ORDER BY
	< order_by_condition > 
LIMIT < limit_number>
  • 动态调整后执行顺序
1
2
3
4
5
6
7
8
9
10
FROM < left_table > 
ON < join_condition > 
< join_type > JOIN < right_table > 
WHERE < where_condition > 
GROUP BY < group_by_list > ...[WITH ROLLUP]
HAVING < having_condition > 
SELECT 
DISTINCT < select_list > 
ORDER BY < order_by_condition > 
LIMIT < limit_number>

  • 在MySQL中,SQL语句的执行顺序通常是以下顺序:

    1. FROM: 指定要查询的表.
    2. ON: 执行要连接的表
    3. JOIN: 执行各种连接操作.
    4. WHERE: 筛选行,只选择满足条件的行.
    5. GROUP BY: 按一个或多个列分组.
    6. WITH ROLLUP: 按组计算每个聚合函数的值,并添加行,这些行包括所有行或组的聚合函数值.
    7. HAVING: 筛选组,只选择满足条件的组.
    8. SELECT: 选择要返回的列.
    9. DISTINCT: 删除重复的行.
    10. ORDER BY: 按一个或多个列对结果进行排序.
    11. LIMIT: 限制结果集中的行数.

  • 逻辑查询具体分析:

    1. FROM: 对FROM子句中的左表<left_table>和右表<right_table>执行笛卡尔积,产生虚拟表VT1

    2. ON: 对虚拟表VT1应用ON筛选,只有那些符合<join_condition>的行才被插入虚拟表VT2

    3. JOIN: 如果执行了OUTER JOIN(如LEFT OUTER JOIN,RIGHT OUTER JOIN),那么保留表中未匹配的行作为外部行添加到虚拟表VT2中,产生虚拟表VT3.如果FROM子句包含两个以上表,则对上一个链接生成的结果表VT3和下一个表重复执行步骤1~步骤3,直到处理完所有的表为止.

    4. WHERE: 对虚拟表VT3应用WHERE过滤条件,只有符合<where_condition>的记录才被插入虚拟表VT4中.

      • 当前应用WHERE过滤器时,两种过滤是不被允许的:

        1. 由于数据还没有分组,因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类统计的过滤

          1
          2
          mysql> SELECT a from t  WHERE a = MIN(a);
          ERROR 1111 (HY000): Invalid use of group function

        2. 由于没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的,如SELECT city as c FROM t WHERE c = 'ShangHai'是不允许出现的.

          1
          2
          mysql> SELECT a as b from t  WHERE b = 'c';
          ERROR 1054 (42S22): Unknown column 'b' in 'where clause'

    5. GROUP BY: 根据GROUP BY子句中列,对VT4中的记录进行分组操作,产生VT5

    6. WITH ROLLUP: 对VT5进行ROLLUP操作,产生表VT6

    7. HAVING: 对虚拟表VT6应用HAVING过滤器,只有符合<having_condition>的记录才被插入虚拟表VT7中

    8. SELECT: 选择指定列,插入虚拟表VT8中

    9. DISTINCT: 去除重复数据,产生虚拟表VT9

      • 如果查询指定了DISTINCT子句,则会创建一张内存临时表(如果内存中存放不下就放到磁盘上).这张内存临时表的表结构和上一步产生的虚拟表一样,不同的是对进行DISTINCT操作的列增加了一个唯一索引,以此来去除重复数据.

    10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_condition> 进行排序操作,产生虚拟表VT10.

    11. LIMIT: 取出指定行的记录,产生虚拟表VT11,并返回给查询用户

MySQL SQL_MODE

查看SQL_MODE

1
2
3
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
SHOW VARIABLES LIKE '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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
mysql> select 1=NULL;
+--------+
| 1=NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select NULL=NULL;
+-----------+
| NULL=NULL |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

mysql> drop table if EXISTS t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (a char(5)) ENGINE = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t SELECT 'a';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t SELECT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t SELECT 'b';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t SELECT 'c';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t SELECT NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT a,count(1) from t GROUP BY a;
+------+----------+
| a    | count(1) |
+------+----------+
| a    |        1 |
| NULL |        2 |
| b    |        1 |
| c    |        1 |
+------+----------+
4 rows in set (0.00 sec)

mysql> SELECT * from t ORDER BY a;
+------+
| a    |
+------+
| NULL |
| NULL |
| a    |
| b    |
| c    |
+------+
5 rows in set (0.00 sec)
  • 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的文件