深入浅出MYSQL数据库思维导图模板_ProcessOn思维导图、流程图

6.MYSQL存储引擎

MyISAM存储引擎

默认MyISAM的表会在磁盘中产生3个文件

.frm

表结构文件

.MYD

数据文件

.MYI

索引文件

可以在创建表的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持

DATA DIRECORY [=] 数据保存的绝对路径

INDEX DIRECTORY [=] 索引文件保存的绝对路径

MyISAM单表最大支持的数据量2的64次方条记录

每个表最多可以建立64个索引

如果是复合索引，每个符合索引最多包含16个列，索引值最大长度是1000B

MyISAM引擎的存储格式

定长（FIXED 静态）

是指字段中不包含VARCHAR/TEXT/BLOB

动态（DYNAMIC）

字段中包含了VARCHAR/TEXT/BLOB

压缩（COMPRESSED）

myisampack创建

InnoDB存储引擎

设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据

原子性（Atomiocity）

是不可分割的最小操作单位，要么同时成功，要么同时失败。

一致性（Consistency）

事务操作前后，数据总量不变

隔离性（Isolation）

多个事务之间。相互独立。

持久性（Durability）

当事务提交或回滚后，数据库会持久化的保存数据。

支持行级锁，可以提升多用户并发时的读写性能

支持外键，保持数据的一致性和完整性

InnoDB拥有自己独立的缓存池，常用的数据和索引都在缓存中

对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘的I/O，提高性能。

创建InnoDB表之后会产生两个文件

.frm表结构文件

.idb，数据和索引存储表空间中

所有的表都需要创建主键，最好配合上AUTO_INCREMENT，也可以放到经常查询的列作为主键。

事务

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

事务提交的两种方式

自动提交：

* mysql就是自动提交的

* 一条DML(增删改)语句会自动提交一次事务。

手动提交：

* Oracle 数据库默认是手动提交事务

* 需要先开启事务，再提交

修改事务的默认提交方式：

* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式： set @@autocommit = 0;

事务的隔离级别

read uncommitted：读未提交

* 产生的问题：脏读、不可重复读、幻读

read committed：读已提交（Oracle）

* 产生的问题：不可重复读、幻读

repeatable read：可重复读（MySQL默认）

* 产生的问题：幻读

serializable：串行化

* 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：
* select @@tx_isolation;

数据库设置隔离级别：
* set global transaction isolation level

7.MYSQL数据操作

添加记录

INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...)

不指定字段名称

INSERT tbl_name VALUE(value...)

按照建表时的字段顺序进行赋值

列出指定字段

INSERT tbl_name(字段名称,...) VALUES(值,...)

INSERT ... SET 的形式

INSERT tbl_name SET 字段名称=值,...;

INSERT ... SELECT

INSERT tbl_name[(字段名称,...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]

一次添加多条记录

INSERT tbl_name[(字段名称,...)] VALUES(值,...),
(值,...),
(值,...)

修改记录

UPDATE tbl_name SET 字段名称=值,字段名称=值[WHERE条件]

如果不添加条件，整个表中的记录都会被更新

删除记录

DELETE FROM tbl_name [WHERE 条件]

如果不添加条件，表中所有记录都会被删除

DELETE清空数据表的时候，不会重置AUTO_INCREMEMT的值，可以通过ALTER语句将其重置为1

彻底清空数据表

TRUNCATE [TABLE] tbl_name;

清除表中所有记录

会重置AUTO_INCREMENT的值

查询记录

SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]

查询表中所有记录

SELECT * FROM tbl_name;

指定字段的信息

SELECT 字段名称,... FROM tbl_name;

库名.表名

SELECT 字段名称,... FROM db_name.tbl_name

给字段起别名

SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;

给数据表起别名

SELECT 字段名称,... FROM tbl_name [AS] 别名;

表名.字段名的形式

SELECT tbl_name.col_name,... FROM tbl_name;

去重name

SELECT DISTINCT name FROM tbl_name;

不能显示其他列

SELECT id,name FROM tbl_name GROUP BY name;

一般查询不重复记录使用group by

WHERE条件

筛选出符合条件的记录

比较运算符

=,<,>,!=,<>,<=,>=

<=> 和=的区别

<=>可以检测NULL值

IS [NOT] NULL

检测值是否为NULL或者NOT NULL

指定范围

[NOT] BETWEEN ... AND

指定集合

[NOT] IN(,,,...);

逻辑运算符

AND

逻辑与

OR

逻辑或

匹配字符串

[NOT] LIKE

%

任意长度的的字符串，零个，一个或多个

_

任意一个字符

例如：要求用户名中包含三

SELECT id,username,age,sex FROM user
WHERE username LIKE '%三%';

例如：用户名长度为三位的用户

SELECT id,username,age,sex FROM user
WHERE username LIKE '___';

GROUP BY 分组

把值相同的放到一个组里面，最终查询出的结果只会显示组中一条记录。

分组配合GROUP_CONCAT()查看组中某个字段的详细信息

SELECT GROUP_CONCAT(suername),age,sex,addr FROM user
GROUP BY sex;

配合聚合函数使用

COUNT()

统计记录总数,一般选择非空列(主键)

如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来

如果写COUNT(*)会统计NULL值(不推荐使用)

SUM()

求和

MAX()

求最大值

MIN()

求最小值

AVG()

求平均值

聚合函数：将一列数据组为一个整体，进行纵向的计算。
注意：聚合函数的计算排除null值。

配合WITH ROLL关键字使用

会在记录末尾添加一条记录，是上面所有记录的总和

HAVING子句对分组结果进行二次筛选

HAVING后面可以加通过聚合函数操作的列或者SELECT查询到的列

例如：查询分组后人数大于3的

SELECT address
GROUP_CONCAT(username) AS usersDetail
COUNT(*) AS totalUsers
FROM user
GROUP BY addr
HAVING COUNT(*)>3;

HAVING和WHERE的区别：

WHERE在分组前限定，如果不满足则不参与分组，
HAVINg在分组后限定，如果不满足则不会被查询出来。

WHERE后不可以跟聚合函数，HAVING可以进行聚合函数的判断

ORDER BY 排序

ORDER BY 字段名称 ASC(升序(默认))|DESC(降序);

ORDEER BY 字段名称 [case1],[case2]...;满足case1才会判断case2

LIMIT 限制结果集显示条数

LIMIT 值

显示结果集前几条记录

LIMIT offset,row_count

从offset开始，显示row_count条记录，offset从0开始

开始的索引=( 当前的页码 - 1 ) * 每页显示的条数

SELECT * FROM tbl_name LIMIT 0,3;第一页
SELECT * FROM tbl_name LIMIT 3,3;第二页
SELECT * FROM tbl_name LIMIT 6,3;第三页

多表查询

笛卡尔积的形式

内连接的形式

查询两个表中符合连接条件的记录

SELECT 字段名称,... FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件

外连接的形式

左外连接

SELECT 字段名称,.. FROM tbl_name1
LEFT OUTER JOIN tbl_name
ON 连接条件;

先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的用NULL代替。

右外连接

SELECT 字段名称,.. FROM tbl_name1
RIGHT OUTER JOIN tbl_name
ON 连接条件;

先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的用NULL代替。

外键约束

只有InnoDB存储引擎支持外键

保持数据的一致性和完整性

创建外键

建表时指定外键

[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)

子表的外键字段和主表的主键字段要相似；
如果是数值型要求一致，并且无符号也要一直；
如果是字符型，要求类型一致，长度可以不同。

如果外键字段没有创建索引，MySQL会自动添加索引

子表的外键关联的必须是父表的主键

外键约束的参照操作

CASCADE

从父表删除或更新，子表也更着删除或者更新，级联的操作

SET NULL

从父表删除或者更新记录，并且设置子表的外键列为NULL。

NO ACTION | RESTRIC

拒绝对父表做更新或者删除操作

动态添加外键

ALTER TABLE tbl_name
ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);

动态添加外键之前，表中的记录一定是合法的记录，不能有脏值，否则外键添加不成功。

动态删除外键

ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;

特殊形式的查询

子查询

SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name);

内层语句查询的结果可以作为外层语句查询的条件

由IN引发的子查询

由比较运算符引出的子查询

由EXISTS引发的子查询

ANY SOME ALL

|运算符 | ANY |SOME| ALL |
|<、>= |最小值|最大值|最大值
|<、>= |最大值|最大值|最小值|
| = |任意值|任意值| |
|<>、！=| | |任意值|

INSERT ... SELECT

CREATE ... SELECT

创建表的时候将查询的数据写进去

CREATE TABLE tbl_name1 LIKE tbl_name2;

创建一个同某个表结构一致的表

联合查询

UNION

SELECT 字段名称,.. FROM tbl_name1
UNION
SELECT 字段名称,... FROM tbl_name2;

会去掉表中重复的数据

UNION ALL

SELECT 字段名称,.. FROM tbl_name1
UNION ALL
SELECT 字段名称,... FROM tbl_name2;

简单的合并

自身连接查询

无限级分类

8.MYSQL常用函数

数学函数

CEIL()

进一取整

FLOOR()

舍掉小数部分

ROUND()

四舍五入

TRUNCATE()

截取小数点后几位

MOD()

取余数

ABS()

取绝对值

PI()

圆周率

RAND()或者RANG(X)

0~1之间的随机数

EXP()

计算e的x次方

字符串函数

CHAR_LENGTH()

字符串的字符数

LENGTH()

返回字符串的长度

CONCAT(s1,s2,...)

将字符串合并成一个字符串

如果拼接的字符串中有NULL，返回的结果就是NULL

CONCAT_WS(x,s1,s2,...)

以指定分隔符x拼接字符串

如果使用NULL进行拼接，返回的就是NULL

如果拼接的字符串有NULL，不会影响其他的

UPPER()、UCASE()、LOWER()、LCASE()

大小写转换

REVERSE()

反转函数

LEFT()|RIGHT()

返回字符串前几个或者后几个字符

TRIM()

去除前后空格

LPAD(S1,LENGTH,S2)

在s1的LENGTH左填充s2,LENGTH小于s1时为截取

RPAD(S1,LENGTH,S2)

在s1的右填充s2至LENGTH,LENGTH小于s1时为截取

REPLACE(s,s1,s2)

替换s中s1为s2

日期时间函数

CURDATE()、CURRENT_DATE()

返回当前日期

CURTIME()、CURRENT_TIME()

返回当前时间

NOW()、CURRENT_TIMESTAMP()、SYSDATE()

返回当前的日期时间

MONTH(CURDATE())

返回日期的月份

MONTHNAME(CURDATE())

返回月份的名字

DAYNAME(NOW())

返回星期几

DAYOFWEEK(NOW())

返回一周内的第几天

YEAR(NOW())、MONTH(NOW())、DAY(NOW())、HOUR(NOW())、MINUTE(NOW())、SECOND(NOW())

DATEDIFF(time1,time2)

两个时间相差几天

DATE_FORMAT(日期，格式字符串)

日期转字符串：
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

STR_TO_DATE(字符串，日志格式)

字符串转日期：
SELECT STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');

其他常用函数

VERION()

版本

CONNECTION_ID()

当前连接数

USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

当前登录的用户

LAST_INSERT_ID()

得到上一次插入操作产生的AUTO_INCREMENT的值

MD5()、PASSWORD()

进行加密

PASSWORD()是密码加密算法

常用SQL语句

查看上一步操作产生的警告信息

SHOW WARNINGS;

mysql注释

# 注释内容

-- 注释内容

CHECK TABLE tbl_name

检测表

REPAIR TABLE tbl_name

修复表

数据库设计

多表之间的关系

一对一

一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键

一对多（多对一）

在多的一方建立外键，只想一的一方的主键

多对多

多对多的关系实现需要借助第三张中间表。
中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。
要遵循后边的范式要求，必须先遵循前边的所有所有范式要求

第一范式（1NF）

每一列都是不可分割的原子数据项

第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

1.什么是数据库？

数据库是按照数据结构来组织、存储和管理数据的仓库

常见数据库

Oracle

DB2

SQL Server

MySQL