学习Linux个人笔记：MySQL-体系结构，SQL基础应用

一、体系结构

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