一、体系结构

1. C/S结构模型

Client ：
Server ：

2. 实例

实例： mysqld守护进程+线程（M,IO,SQL,Purge…）+预分配的内存（独占）
公司： boss+员工（经理，干活的）+ 办公区

3. MySQL 核心体系结构（SQL语句的执行过程）

Server层 （实例层）
engine层 （存储层）
和磁盘交互。相当Linux文件系统
具体见图。
参考：https://www.jianshu.com/p/e872bc12f583

4. 对象逻辑结构

Linux MySQL
目录 库
文件 表

库： 库名 、库属性
表： 表名、表属性、列（列名、列属性）、行 -----》 元数据（数据字典）+数据

mysql> show databases;
mysql> use mysql
mysql> show tables;

5. 对象-物理存储结构-宏观

cd /data/3306/data

库： 对应着 OS 中的目录
表：
例如：
user 表 MyISAM
user.frm ： 存储数据字典信息（列相关信息）
user.MYD ： 存储数据行
user.MYI ： 存储索引

servers 表 InnoDB
servers.frm ： 存储数据字典信息（列相关信息）
servers.ibd ： 存储数据行和索引

6. InnoDB存储引擎-物理存储结构引入-微观结构

存储引擎：MySQL 内部的“文件系统”，和磁盘交互的结构。

segment（数据段） ： 表的数据存放的位置，包含1-N个extent（区、簇）
extent (区、簇) ： 包含了连续的64个pages，默认1M。
page (数据页) ： 最小IO单元，默认是16KB 。连续的4个OS block。

二、SQL基础应用

1. 什么是SQL？

关系型数据库当中通用的查询语言。全名：结构化查询语言。

2. SQL 标准 （ANSI/ISO）

SQL-89
SQL-92
SQL-99
SQL-03

3. SQL 常用分类

DDL ： 数据定义语言
DCL ： 数据控制语言
DML ： 数据操作语言

4. SQL_MODE ?

5.7+ 之后采用的是严格模式。
作用：
为了让我们SQL在执行时更加严谨、有意义，符合常识、逻辑、符合科学等。。。
例子：
1. 日期格式： 2020-07-01 0000-00-00
2. 除法运算： 除数不能为0

查看SQL_MODE: 
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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+设置SQL_MODE: 
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)set 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';
Query OK, 0 rows affected, 1 warning (0.00 sec)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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

5. 字符集和校对规则

• 字符集
  mysql> show charset;
  utf8
  utf8mb4

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | latin1                                                        |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | latin1                                                        |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /data/app/mysql-5.7.28-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+

utf8 和 utf8mb4 区别？
例如：
utf8不完整，emoji表情字符是不支持，utf8mb4是支持的。
根本原因是，utf8 字符最大长度为3字节，utf8mb4是4字节。

• 校对规则（排序规则）
  mysql> show collation;
  作用： 影响到了字符串的排序。

6. 数据类型

作用： 约束存储的数据更加有意义，符合对于这个列的定义。

• 数字类型

    		    字节量                  范围tinyint       1              0~255      -127~128int           4              0~2^32-1   -2^31 ~2^31-1
  

• 字符串类型
  char(10)
  定长类型的字符串类型。最多存储10个字符。如果存了5个,剩余空间用空格填充。

varchar(10)
变长类型的字符串类型。最多存储10个字符。如果存了5个,按需分配存储空间，另外需要1-2字节，存储字符长度。

怎么选择：一般情况下 变长字符串就用varchar，固定长度一般采用char类型

enum() ： 枚举类型
应用场景： 列中的数据，有限个数的值的时候，并且是有规律。
enum(‘bj’,‘sh’,‘tj’,…)
1 2

• 时间日期
  DATETIME
  范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

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

7.约束和其他表属性

Primary key 			 ： 主键约束 ，要求设置为主键的列，储值时，非空且唯一。每张表只有一个主键。
not null    			 :  非空约束，必须录入值
unique key  			 ： 唯一约束，不能重复值 
unsigned    			 ： 数字类型约束，无符号。
default        			 ： 设置默认值，一般配合not null 使用
auto_increment 			 ： 针对数字列，自动增长，一般配合主键
comment       			 ： 列或者表进行注释

8.DDL 应用

作用： 数据定义语言 。

• 库定义

创建库 
CREATE DATABASE test CHARSET utf8mb4;
CREATE DATABASE wordpress;查询库
SHOW DATABASES;
SHOW CREATE DATABASE test;
SHOW CREATE DATABASE wordpress;
修改库
ALTER DATABASE wordpress CHARSET utf8mb4;删除库(生产禁用！！！！)
DROP DATABASE wordpress;

• 表定义

建表
USE test;
CREATE TABLE stu (
id     INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname  VARCHAR(64) NOT NULL COMMENT '姓名',
age    TINYINT  UNSIGNED  NOT NULL DEFAULT 0  COMMENT '年龄',
gender ENUM('m','f','n')  NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';查表定义
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;修改表定义
-- 添加和删除字段
-- 1. 在表中添加telnum char(11) not null unique key comment '手机号'
ALTER TABLE stu ADD COLUMN  telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机号';-- 2. 在sname后添加a列
ALTER TABLE stu ADD COLUMN  a INT NOT NULL COMMENT '测试列' AFTER sname;-- 3. 在第一列前添加b列
ALTER TABLE stu ADD COLUMN  b INT NOT NULL COMMENT '测试列' FIRST ;-- 4. 删除添加的a,b列
ALTER TABLE stu DROP COLUMN a;
ALTER TABLE stu DROP COLUMN b;-- 5. 修改数据类型 
ALTER TABLE stu MODIFY telnum VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '手机号';
ALTER TABLE stu MODIFY telnum VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '手机号';-- 6. 修改列名及数据类型
ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT '手机号';删除表
drop table stu;

• DDL语句开发规范

库： CREATE DATABASE test CHARSET utf8mb4;
1. 库名要与业务有关
2. 库名不使用大写字母、数字开头。
3. 不要使用内置关键字
4. 建库要指定字符集。
5. 生产中禁止使用删库操作。

表： 
CREATE TABLE stu (
id     INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname  VARCHAR(64) NOT NULL COMMENT '姓名',
age    TINYINT  UNSIGNED  NOT NULL DEFAULT 0  COMMENT '年龄',
gender ENUM('m','f','n')  NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';

1. 表名：与业务有关,例如：wp_users，不使用大写字母、数字开头，不要太长（16以下）
2. 设置存储引擎、字符集、表注释
3. 表名、列名要使用内置关键字
4. 列名要有意义，长度（16以下）
5. 数据类型：合适的、足够的、简短
6. 每个表要有主键，一般是自增长、无关列数字列。
7. 每个列尽量是not null ，可以配合default
8. 每个列要有注释
9. 修改定义的操作，要在业务不繁忙期间去做。如果紧急可以使用pt-osc 。