MySQL5.7.28_02_一张图片带你入门MySQL

最新推荐文章于 2024-06-22 19:36:08 发布

我爱2b哥

最新推荐文章于 2024-06-22 19:36:08 发布

阅读量273

点赞数

分类专栏： MySQL入门 MySQL基础 Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/yeming_666/article/details/105814567

版权

Mysql 同时被 3 个专栏收录

8 篇文章 0 订阅

订阅专栏

MySQL基础

2 篇文章 0 订阅

订阅专栏

MySQL入门

1 篇文章 0 订阅

订阅专栏

文章目录

MySQL基础知识点
Mysql基础思维导图

MySQL基础知识点

前面学会了安装，下面开始入门
在这里插入图片描述
链接：https://pan.baidu.com/s/18KDKZxnhmWH9Ebpdhy6DlA 提取码：7665

上面网盘地址自行取图“mysql入门基础思维导图.png”；下面是markdown转换后的格式

Mysql基础思维导图

数据库相关操作

创建出数据库

CREATE {DATABASE | SCHEMA} 数据库名;
检测数据库名称是否存在，不存在则创建 -
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名;
在创建数据库的同时指定编码方式 -
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名 [DEFAULT] CHARACTER SET [=] 字符集;
注意点也属于常识点：1，数据库名称要有意义
2，名称不要包含特殊字符或者使用MySQL关键字

查看当前服务器下的全部数据库

SHOW {DATABASES | SCHEMAS};

查看指定数据库的详细信息

SHOW CREATE {DATABASE | SCHEMA} 数据库名;

修改指定数据库的编码方式

ALERT {DATABASE | SCHEMA} 数据库名 [DEFAULT] CHARACTER SET [=] 字符集;

使用指定数据库

USE 数据库名;

查看当前的数据库

SELECT {DATABASE() | SCHEMA()};

删除指定数据库

DROP {DATABASE | SCHEMA} 数据库名;
如果数据库存在则删除 - DROP {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名;

数据表相关操作

数据表

是数据库最重要的组成部分之一，数据是保存在数据表中的
数据表由行(row)和列(column)组成
每个数据表中至少有一列，行可以有零行一行或者多行
表名要唯一，不要包含特殊字符，表名起的要有意义，明确含义

创建表

CREATE TABLE [IF NOT EXISTS] 表名(
字段名1 字段类型 [完整约束条件],
字段名2 字段类型 [完整约束条件],
…
)ENGINE=存储引擎 CHARSET=编码方式;
- UNSIGNED - 无符号，没有负数，从0开始
- ZEROFILL - 零填充，当数据的显示长度不够的事后可以使用前补0的效果填充至指定长度，字段会自动添加UNSIGNED属性；通常与int(11)配合使用(int在指定长度的情况下)
- NOT NULL - 非空约束，也就是插入值的时候这个字段必须要给值，值不能为空
- DEFAULT - 默认值，如果插入值的时候没有给字段赋值，则使用默认值
- PRIMARY KEY - 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
- AUTO_INCREMENT - 自动增长，只能用于数值列，而且配合索引使用，默认起始值从1开始，每次增长1
- UNIQUE KEY - 唯一性，一个表中可以有多个字段是唯一索引，童谣的值不能重复，但是NULL值除外
- FOREIGN KEY - 外键约束
CREATE TABLE A LIKE B; -
将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来
CREATE TABLE A AS SELECT 列1, 列2, …… FROM B LIMIT 0; -
将表B的字段结构复制到表A中来，但不会复制表B中的索引到表A中来。这种方式比较灵活可以在复制原表表结构的同时指定要复制哪些字段，并且自身复制表也可以根据需要增加字段结构。

后面CREATE TABLE A LIKE B; 和CREATE TABLE A AS SELECT 列1, 列2, …… FROM B LIMIT 0;这两种方式在复制表的时候均不会复制权限对表的设置。比如说原本对表B做了权限设置，复制后，表A不具备类似于表B的权限。

查看当前数据库下存在的数据表

SHOW TABLES;
SHOW [FULL] TABLES [{FROM | IN}] 数据库名 [LIKE ‘pattern’ | WHERE expr];

查看指定数据表的详细信息

SHOW CREATE TABLE 表名;

查看表结构

DESC 表名;
DESCRIBE 表名;
SHOW COLUMNS FROM 表名;

删除指定的数据表

DROP TABLE [IF EXIXTS] 表名;

表结构相关操作

添加字段 -
ALTER TABLE 表名 ADD 字段名称字段属性 [完整约束条件] [FIRST | AFTER 字段名称];
删除字段 -
ALTER TABLE 表名 DROP 字段名称;
添加默认值 -
ALTER TABLE 表名 ALTER 字段名称 SET DEFAULT 默认值;
删除默认值 -
ALTER TABLE 表名 ALTER 字段名称 DROP DEFAULT;
修改字段类型，字段属性 -
ALTER TABLE 表名 MODIFY 字段名称字段类型 [字段属性] [FIRST | AFTER 字段名称];
修改字段名称，字段类型，字段属性 -
ALTER TABLE 表名 CHANGE 原字段名称新增段名称字段类型字段属性 [FIRST | AFTER 字段名称];
添加主键 -
ALTER TABLE 表名 ADD PRIMARY KEY(字段名称);
删除主键 -
ALTER TABLE 表名 DROP PRIMARY KEY;
添加索引 -
ALTER TABLE 表名 ADD [UNIQUE KEY | INDEX] [索引名称] (字段名称);
删除索引 -
ALTER TABLE 表名 DROP 索引名称;
修改表名 -
- ALTER TABLE 原表名 RENAME [TO | AS] 新表名;
- RENAME TABLE 原表名 TO 新表名;
修改AUTO_INCREMENT的值 -
ALTER TABLE 表名 AUTO_INCREMENT = 值;

表数据的增删改查

新增数据 -
- 语法 -
  INSERT [INTO] 表名[(列名1, 列名2, ……)]{VALUE | VALUES}(值1, 值2, ……);
- 不指定列名 -
  INSERT 表名 VALUE(值1, 值2, ……);
  
  需要按照建表时的字段顺序给每一个字段赋值
- 指定列名 -
  INSERT 表名(列名1, 列名2, ……) VALUES(值1, 值2, ……);
  
  需要按照指定列名的顺序给每一个字段赋值
- INSERT …… SET形式 -
  INSERT 表名 SET 列1 = 值1, 列2 = 值2, ……;
- INSERT …… SELECT形式 -
  INSERT 表名1[(列名1, 列名2, ……)] SELECT 列名1, 列名2, …… FROM 表名2 [WHERE 条件]
  
  这个insert的字段与select的字段数量及每个字段对应类型需要一致
- 一次添加多条记录 -
  INSERT 表名[(列名1, 列名2, ……)] VALUES(值1, 值2, ……), (值1, 值2, ……), ……;
删除数据 -
- 语法 -
  DELETE FROM 表名 [WHERE 条件];
  
  如果不指定条件，则会删除表中所有数据，DELETE 清空表所有数据不会重置AUTO_INCREMENT的值，可以通过ALTER
- 彻底清空表 -
  TRUNCATE [TABLE] 表名;
  
  清除表中所有记录，同时也会重置AUTO_INCREMENT的值；效率极快；属于DDL语句
修改数据 -
- 语法 -
  UPDATE 表名 SET 列1 = 值1, 列2 = 值2, …… [WHERE 条件];
单表查询 -
- 语法 -
  SELECT select_expr,…… FROM 表名 [WHERE 条件] [GROUP BY {列名(单个) | 列名(多个)} HAVING 条件(二次筛选)] [ORDER BY{列名(单个) | 列名(多个) | 表达式} [ASC | DESC]] [LIMIT 索引,条数(限制结果集的显示条数)];
- 查询表中所有记录 -
  SELECT * FROM 表名;
- 指定字段查询 -
  SELECT 列1, 列2, …… FROM 表名;
- 库名.表名 -
  SELECT 列1, 列2, …… FROM 数据库名.表名;
- 给字段起别名 -
  SELECT 列1 [AS] 别名1, 列2 [AS] 别名2, …… FROM 数据库名.表名;
- 给表起别名 -
  SELECT 列1, 列2, …… FROM 表名 [AS] 别名;
- 表名.列名 -
  SELECT 表名.列1, 表名.列2, …… FROM 表名;
- WHERE条件，会筛选出符合条件的记录
  - 比较运算符 -
    >, >=, <, <=, =, !=, <>, <=>
  - 检测是否为NULL或NOT NULL -
    IS [NOT] NULL
  - 指定范围 -
    [NOT] BETWEEN 值1 AND 值2
  - 指定集合 -
    [NOT] IN(值1, 值2, ……)
  - 逻辑运算 -
    AND 逻辑与
    OR 逻辑或
  - 模糊匹配 -
    [NOT] LIKE
    % 匹配任意长度的字符串
    _ 匹配任意一个字符
- GROUP BY 分组
  - 意义：把值相同放到一个组中，最终查询出的结果指挥显示组中一条记录
  - 分组配合GROUP_CONCAT()查看组中某个字段的详细信息；就是将分组后字段值连接一起
  - 配合聚合函数使用
    - COUNT() - 统计记录总数，如果写的时COUNT(列)，字段中的值为null，不统计进来，写COUNT(*)会统计NULL值
    - SUM() - 求和
    - MAX() - 求最大值
    - MIN() - 求最小值
    - AVG() - 求平均值
  - 配合WITH ROLLUP 关键使用 - 会记录末尾添加一条记录，是上面所有记录的总和
  - HAVING 子句对分组结果进行二次筛选
- ORDER BY 列 [ASC | DESC] - ASC升序，DESC降序；不写默认ASC
- LIMIT 限制结果集显示条数
  - LIMIT 值 - 显示结果集的前几条记录
  - LIMIT offset, row_count - 从offset开始，显示几条记录
多表查询 -
- 笛卡儿积 -
  SELECT * FROM 表名1,表名2;
- 内连接
  - 查询两表中符合连接条件的记录 -
    SELECT 列1, 列2, …… FROM 表名1 INNER JOIN 表名2 ON 连接条件;
- 外连接
  - 左连接，先显示左表中的全部记录，再去右表中查询符合条件的记录不符合的以NULL代替 -
    SELECT 列1, 列2, …… FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 连接条件;
  - 右连接，先显示右表中的全部记录，再去左表中查询符合条件的记录不符合的以NULL代替 -
    SELECT 列1, 列2, …… FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 连接条件;
- 联合查询
  - UNION -
    SELECT 列1, 列2, …… FROM 表名1 UNION SELECT 列1, 列2, …… FROM 表名2;
  - UNION ALL -
    SELECT 列1, 列2, …… FROM 表名1 UNION ALL SELECT 列1, 列2, …… FROM 表名2;
    
    UNION ALL是简单的合并，合并所有；UNION会去表合并后表中重复的记录

常用函数

数学函数

ABS(x) - 返回x的绝对值
PI() - 返回圆周率π，默认显示6位小数
SQRT(x) - 返回非负数x的二次方根
MOD(x,y) - 返回x被y除后的余数
CEIL(x)、CEILING(x) - 返回不小于x的最小整数
FLOOR(x) - 返回不大于x的最大整数
ROUND(x)、ROUND(x,y) - 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位
SIGN(x) - 返回参数x的符号，-1表示负数，0表示0，1表示正数
POW(x,y)、POWER(x,y) - 返回x的y次乘方的值
EXP(x) - 返回e的x乘方后的值
LOG(x) - 返回x的自然对数，x相对于基数e的对数
LOG10(x) - 返回x的基数为10的对数
RADIANS(x) - 返回x由角度转化为弧度的值
DEGREES(x) - 返回x由弧度转化为角度的值
SIN(x)、ASIN(x) - 前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦
COS(x)、ACOS(x) - 前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦
TAN(x)、ATAN(x) - 前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切
COT(x) - 返回给定弧度值x的余切

字符串函数

CHAR_LENGTH(str) - 计算字符串字符个数
CONCAT(s1, s2, …) - 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
CONCAT_WS(x, s1, s2, …) - 返回多个字符串拼接之后的字符串，每个字符串之间有一个x
INSERT(s1,x,len,s2) - 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符
LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str) - 前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写
LEFT(s,n)、RIGHT(s,n) - 前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符
LPAD(s1,len,s2)、RPAD(s1,len,s2) - 前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；后者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符
LTRIM(s)、RTRIM(s) - 前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除
TRIM(s) - 返回字符串s删除了两边空格之后的字符串
TRIM(s1 FROM s) - 删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格
REPEAT(s,n) - 返回一个由重复字符串s组成的字符串，字符串s的数目等于n
SPACE(n) - 返回一个由n个空格组成的字符串
REPLACE(s,s1,s2) - 返回一个字符串，用字符串s2替代字符串s中所有的字符串s1
STRCMP(s1,s2) - 若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1
SUBSTRING(s,n,len)、MID(s,n,len) - 两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1) - 三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）
REVERSE(s) - 将字符串s反转
ELT(N,str1,str2,str3,str4,…) - 返回第N个字符串

日期和时间函数

CURDATE()、CURRENT_DATE() - 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定
CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE() - 这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date) - 前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
FROM_UNIXTIME(date) - 和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间
UTC_DATE()和UTC_TIME() - 前者返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
MONTH(date)和MONTHNAME(date) - 前者返回指定日期中的月份，后者返回指定日期中的月份的名称
DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d) - DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二
WEEK(d)、WEEKOFYEAD(d) - 前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周
DAYOFYEAR(d)、DAYOFMONTH(d) - 前者返回d是一年中的第几天，后者返回d是一月中的第几天
YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time) - YEAR(date)返回指定日期对应的年份，范围是1970_{2069；QUARTER(date)返回date对应一年中的季度，范围是1}4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值
EXTRACE(type FROM date) - 从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
TIME_TO_SEC(time) - 返回以转换为秒的time参数，转换公式为"3600小时 + 60分钟 + 秒"
SEC_TO_TIME() - 和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式
DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type) - 返回将起始时间加上expr type之后的时间，比如DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)表示的就是把第一个时间加1秒
DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type) - 返回将起始时间减去expr type之后的时间
ADDTIME(date,expr)、SUBTIME(date,expr) - 前者进行date的时间加操作，后者进行date的时间减操作

条件判断函数

IF(expr,v1,v2) - 如果expr是TRUE则返回v1，否则返回v2
IFNULL(v1,v2) - 如果v1不为NULL，则返回v1，否则返回v2
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END - 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

系统信息函数

VERSION() - 查看MySQL版本号
CONNECTION_ID() - 查看当前用户的连接数
USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER() - 查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的
CHARSET(str) - 查看字符串str使用的字符集
COLLATION() - 查看字符串排列方式

加密函数

PASSWORD(str) - 从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用
MD5(str) - 为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回
ENCODE(str, pswd_str) - 使用pswd_str作为密码，加密str
DECODE(crypt_str,pswd_str) - 使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

格式化函数

FORMAT(x,n) - 将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回
CONV(N,from_base,to_base) - 不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制
INET_ATON(expr) - 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特
INET_NTOA(expr) - 给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示
BENCHMARK(count,expr) - 重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间
CONVERT(str USING charset) - 使用字符集charset表示字符串str

字段类型

数字型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1字节	-2^7~27-1	0~2^8-1	小整数
SMALLINT	2字节	-2^15~215-1	0~2^16-1	大整数值
MEDIUMINT	3字节	-2^23~223-1	0~2^24-1	大整数值
INT或INTEGER	4字节	-2^31~231-1	0~2^32-1	大整数值
BIGINT	8字节	-2^63~263-1	0~2^64-1	极大整数值
FLOAT	4字节			单精度浮点数值
DOUBLE	8字节			双精度浮点数值
DECIMAL	对于DECIMAL(M,D)，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

字符类型

类型	范围	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过255个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215字节	中等长度文本数据
LOGNGBLOB	0-4294967295	二进制形式的极大文本数据
LONGTEXT	0-4294967295字节	极大文本数据

枚举集合

类型	大小（字节）	最多成员数
ENUM	64	65535
SET	64	64

时间类型

类型	大小（字节）	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
YEATR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期的时间值
TIMESTAMP	8	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

说明
- 1、整数类型 : 包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED(数字类型无符号化)、ZEROFILL(填充0)属性配合使用才有意义。

例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

2、实数类型 : 包括FLOAT、DOUBLE、DECIMAL。

DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。

而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

3、字符串类型 : 包括VARCHAR、CHAR、TEXT、BLOB

VARCHAR用于存储可变长字符串，它比定长类型更节省空间。

VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。

VARCHAR存储的内容超出设置的长度时，内容会被截断。

CHAR是定长的，根据定义的字符串长度分配足够的空间。

CHAR会根据需要使用空格进行填充方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

CHAR和VARCHAR的区别：

在mysql中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同：

MyISAM存储引擎：

建议使用固定长度的数列代替可变长度的数据列

InnoDB存储引擎：

1，建议使用VARCHAR类型，对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度，因此使用CHAR列不一定比可变长度的VARCHAR性能好。

2，由于CHAR平均占用空间多余VARCHAR，因此VARCHAR来消化需要处理的数据行的存储总量和磁盘I/O是比较好的。

TEXT和BLOB

TEXT和BLOB最主要的区别是BLOB能用来保存二进制数据比如照片，而TEXT只能保存字符串数据，比如文章和日记。根据存储的文本长度的不同和存储的字节不同我们可以使用：MEDIUMTEXT,LONGTEXT和MUDIUMBLOB,LONGBLOB.

4、枚举类型（ENUM） : 把不重复的数据存储为一个预定义的集合。

有时可以使用ENUM代替常用的字符串类型。

ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。

ENUM在内部存储时，其实存的是整数。

尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。

排序是按照内部存储的整数

5、日期和时间类型 : 尽量使用timestamp，空间效率高于datetime，

用整数保存时间戳通常不方便处理。

如果需要存储微妙，可以使用bigint存储。

存储引擎

MyISAM存储引擎

默认MyISAM的表会在磁盘中产生3个文件
- .frm -
  表结构文件
- .MYD -
  数据文件
- .MYI -
  索引文件
可以在创建的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持
- DATA DIRECORY [=] 数据保存的绝对路径
- INDEX DIRECTORY [=] 索引文件保存的绝对路径
MyISAM单表最大支持的数据量是2的64次方条记录
每个表最多可以建立64个索引
如果是复合索引，每个符合索引最多包含16个列，索引值最大长度是1000B
MyISAM引擎的存储格式
- 定长（FIXED 静态） -
  是指字段中不包含VARCHAER/TEXT/BLOB
- 动态（DYNAMIC） -
  只要字段中包含了VARCHAER/TEXT/BLOB
- 压缩（COMPRESSED） -
  myisampack创建

InnoDB存储引擎

涉及遵循ACID模型，支持事务，具有从服务端崩溃中恢复的能力，能够最大限度保护用户的数据
支持行锁，可以提升多用户并发时的读写性能
支持外键，保证数据的一致性和完整性
InnoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中
对于INSERT，UPDATE，DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能
创建InnoDB表之后会产生两个文件
- .frm -
  表结构文件
- .ibd -
  数据和索引存储表空间中
所有的表都需要创建主键，最好时配合上AUTIO_INCREMENT，也可以放到经常查询的列作为主键

MyISAM与InnoDB区别 :

	MyISAM	InnoDB
存储结构	每张表被存放在三个文件:1、.frm 用于存储表的定义2、.MYD 用于存放数据3、.MYI 用于存放表索引	innodb有两种存储方式，共享表空间存储和多表空间存储;两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm。如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持(锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的)	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取.
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

逻辑架构

逻辑分层

连接层 -
最上层，负责连接处理、授权认证、安全服务等功能的处理；包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层 -
第二层，在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，SQL接口，SQL解析，SQL分析优化，缓存查询的处理以及部分内置函数执行(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等。主要包含五个部分
- Mangement Service** -
  系统管理和控制工具，例如数据备份、安全、复制、集群
- SQL interface** -
  SQL接口；接收用户的SQL命令，并返回用户的查询结果；比如select …… from……就是调用SQL interface
- Parser解析 -
  解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。主要功能：
  　　a 、将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的；
  　　b、如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。
- Optimizer优化器 -
  查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。
  　　使用的是“选取-投影-联接”策略进行查询：
  用一个例子就可以理解： select uid,name from user where gender = 1;
  这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
- Cache Buffers -
  查询缓存：主要功能是将客户端提交给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值做一个对应。该 query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
  　　如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
引擎层 -
第三层，是底层数据存取操作实现部分，由多种存储引擎共同组成。真正负责MySQL中数据的存储和提取。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。
存储层 -
最后一层，也是持久化层，数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

SELECT查询过程

步骤一：
客户端向MySQL服务器发送一条SELECT查询请求
步骤二：
服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
步骤三：
服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
步骤四：
MySQL根据执行计划，调用存储引擎的API来执行查询
步骤五：
将结果返回给客户端，同时缓存查询结果

MySQL5.7.28_01_基于glibc的tar包安装
 MySQL5.7.28_02_一张图片带你入门MySQL
MySQL5.7.28_03_一张图片带你进阶MySQL
MySQL5.7.28_03-1_一篇简单文章让你理解B- Tree和B+ Tree理解
 MySQL5.7.28_03-2_MySQL中MyISAM与InnoDB引擎中的锁的简单理解
 MySQL5.7.28_03-3_理解MySQL主从复制(也叫做主从同步)
MySQL5.7.28_03-4_MySQL 分布式ID方案总结
 MySQL5.7.28_04_MySQL相关规范(实际工作中需要注意的)

我爱2b哥

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL5.7.28_02_一张图片带你入门MySQL

MySQL基础知识点前面学会了安装，下面开始入门链接：https://pan.baidu.com/s/18KDKZxnhmWH9Ebpdhy6DlA 提取码：7665
复制链接

扫一扫