MySQL之SQL基础

1. SQL介绍

结构化查询语言，关系型数据库中通用的一类语言

2. SQL常用类型

• DDL：Data Definition Language，数据定义语言
• DCL：Data Control Language，数据控制语言
• DML：Data Manage Language，数据操作语言
• DQL：Data Query Language，数据查询语言

3. SQL中的各种名字

3.1 sql_mode

• 作用：规范SQL语句书写方式

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.2 字符集（charset）及校对规则（collation）

3.2.1 字符集

mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

默认是 latin1，建议使用 utf8mb4

3.2.2 校对规则

• 作用：影响排序操作，简单的来说就是大小写是否敏感

3.3 数据类型

3.3.1 数值类型

类型	存储大小	存储长度	十进制数字范围
tinyint	1B	8 bit	0~255，-128~127
int	4 B	32 bit	0~2^32-1，-2^31~2^31-1
bigint	8 B	64 bit	0~2^64-1，-2^63~2^63-1

• 说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

3.3.2 字符类型

• varchar在存储数据时，会先判断字符长度，然后合理分配空间；而char类型不会判断，立即分配空间
• varchar除了存储字符串本身之外，还会使用额外1~2个字节存储字符串长度
• 括号中的数字指的是字符的个数，无关字符类型；不同种类的字符占用的存储空间不一样
  • 英文和数字：每个字符占1个字节
  • 中文：在utf8、utf8mb4中每个字符占3个字节
  • emoji：在utf8mb4中每个字符占4个字节

char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

以上数据类型需要考虑周全，会影响到索引应用

3.3.3 时间类型

DATETIME （8字节）
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP （4字节）
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

3.3.4 二进制类型

3.4 约束

• primary key：主键约束，唯一+非空，每张表只能有一个主键，作为聚簇索引
• not null：非空约束，必须非空，建议每个列都设置为非空
• unique key：唯一约束，必须不重复的值
• unsigned：针对数字列，非负数，无符号数

3.5 其他属性

• default：默认值
• comment：注释
• auto_increment：自增长

4. 扩展

线上DDL（alter）操作对生产的影响

在MySQL中，DDL语句在对表进行操作时，是要锁“元数据表”的，此时所有修改类命令无法正常运行

• 在对大表、业务繁忙的表进行线上DDL操作时要谨慎

• 尽量避开业务繁忙期间进行DDL

• 建议使用：pt-osc（pt-online-schema-change）、gh-osc 工具进行DDL操作，减少锁表影响
  在MySQL中，DDL语句在对表进行操作时，是要锁“元数据表”的，此时所有修改类命令无法正常运行

• 在对大表、业务繁忙的表进行线上DDL操作时要谨慎

• 尽量避开业务繁忙期间进行DDL

• 建议使用：pt-osc（pt-online-schema-change）、gh-osc 工具进行DDL操作，减少锁表影响

• 如果是8.0版本，可以不使用pt工具