Mysql笔记

	整数类型： TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）、BIGINT	浮点数类型： FLOAT、DOUBLE	定点数类型： DECIMAL	文本类型： TEXT、CHAR、VARCHAR、ENUM 和 SET	时间类型： DATETIME、YEAR、TIME、DATE、TIMESTAMP
用法			用DECIMAL(M,D)来表示高精度小数。 M：一共有多少位，M<=65。 D：表示小数部分位数。	CHAR(M)：固定长度字符串必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。 VARCHAR(M)：可变长度字符串需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。 TEXT：字符串系统自动按照实际长度存储，不需要预先定义长度。 ENUM：枚举类型取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。 SET：是一个字符串对象取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值。
特点		取值范围大，但不精准	取值范围相对小，但是精准	text最灵活，但由于text类型的字段存储长度不固定，所以不被mysql允许做主键	优先考虑使用 DATETIME 类型因为虽然 DATETIME 类型占用的存储空间最多，但是它表达的时间最为完整，取值范围也最大。
存储原理		MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据，都是采用二进制的方式来进行存储的	把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储
其他	在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL；如果是字符串，只要不是主键，就用 TEXT；如果是日期与时间，就用 DATETIME。

主键

主键特点	作用	适合做主键的数据	不适合做主键的数据
一个数据表中最多一个主键	提高查询速度	手动赋值的字段，通过一定的逻辑，确保字段值在全系统的唯一性	业务字段：无法预测整个生命周期中业务字段的变更需求
唯一			自增字段：因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题

ps：使用自增id作为主键vs用雪花id

自增id优点：uuid 相对顺序的自增 id 来说是毫无规律可言的,新行的值不一定要比之前的主键的值要大,无法做到总是把新行插入到索引的最后,而是需要为新行寻找新的合适的位置从而来分配新的空间。这个过程需要做很多额外的操作。

自增id缺点：

别人一旦爬取你的数据库,就可以根据数据库的自增 id 获取到你的业务增长信息
对于高并发的负载，innodb 在按主键进行插入的时候会造成明显的锁争用

外键

外键作用	特点
1、确定从表中的外键字段与主表中的主键字段之间的引用关系	外键约束消耗系统资源高
2、确保从表中数据所引用的主表数据不会被删除，从而保证了 2 个表中数据的一致性

连接查询

内连接

外连接

说明

只返回符合连接条件的记录

返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。

关键字

这几个关键字等价：

JOIN
INNER JOIN
CROSS JOIN

左连接：LEFT JOIN

返回左边表中的所有记录，以及右表中符合连接条件的记录。

右连接：RIGHT JOIN

返回右边表中的所有记录，以及左表中符合连接条件的记录。

左、右指的是LEFT/ RIGHT JOIN 前后两张表的位置关系

where VS having

	where	having
优点	先筛选后连接执行效率高	可以使用分组中的计算函数
缺点	不能使用分组中的计算函数	先连接后筛选执行效率低
其他	where和having不互斥
举例	SELECT -> a.transdate, -> c.operatorname, -> SUM(b.quantity), -> SUM(b.salesvalue) -> FROM -> demo.transactionhead AS a -> JOIN -> demo.transactiondetails AS b ON (a.transactionid = b.transactionid) -> JOIN -> demo.operator AS c ON (a.operatorid = c.operatorid) -> WHERE a.transdate in ('2020-12-12','2020-12-11') -- 先按日期筛选 -> GROUP BY a.transdate , operatorname -> HAVING SUM(b.salesvalue)>100; -- 后按金额筛选

索引

是什么	当于图书馆的检索目录
优点	提高查询效率
缺点	增加存储空间的开销、增加数据操作上的开销
索引字段的选择	选择那些经常被用做筛选条件的字段作为索引
删除索引	DROP INDEX 索引名 ON 表名;
创建索引	单字段索引	多字段索引	组合索引
	直接给数据表创建索引： CREATE INDEX 索引名 ON TABLE 表名 (字段); 创建表的同时创建索引： CREATE TABLE 表名 ( 字段数据类型, …. { INDEX \| KEY } 索引名(字段) ) 修改表时创建索引： ALTER TABLE 表名 ADD { INDEX \| KEY } 索引名 (字段);	分两次分别创建索引： CREATE INDEX index_trans_branchnumber ON demo.trans (branchnumber); CREATE INDEX index_trans_cashiernumber ON demo.trans (cashiernumber);	CREATE INDEX 索引名 ON TABLE 表名 (字段1，字段2，...);
其他说明	给表设定主键约束或者唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引。	多索引时，MySQL 会选择使用最优的索引来执行查询操作	组合索引的多个字段是有序的，遵循左对齐的原则。如果中断，断点后面的条件就没有办法利用索引。如果筛选的是一个范围，如果没有办法无法精确定位，也相当于中断。

事务

事务是什么？	简称DML，使一组数据操作，要么全部执行，要么全部不执行，不会因为某种异常情况（比如硬件故障、停电、网络中断等）出现只执行一部分操作的情况
事务的语法？	START TRANSACTION 或者 BEGIN （开始事务）一组DML语句 COMMIT（提交事务） ROLLBACK（事务回滚）
事务的4个特性	一致性	隔离性	原子性	持久性
事务的4个特性	数据的完整性不会因为事务的执行而受到破坏	多个事务同时执行的时候，不互相干扰	操作要么全部执行，要么全部不执行	事务对数据的修改是永久有效的
说明	事务并不会自动处理 SQL 语句执行中的错误，如果对事务中的某一步数据操作发生的错误不做处理，继续提交，仍然会导致数据不一致。使用“DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;”这个语句，来监控 SQL 语句的执行结果，一旦发发生错误，就自动回滚并退出。 MySQL 支持 4 种事务隔离等级： READ UNCOMMITTED：可以读取事务中还未提交的被更改的数据。 READ COMMITTED：只能读取事务中已经提交的被更改的数据。 REPEATABLE READ：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是 MySQL 的默认选项。 SERIALIZABLE：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL 都会把这个数据锁住，禁止其他事务对这个数据进行任何操作。对于一些核心的数据更改操作，用较高的隔离等级，比如涉及金额的修改；但同时需要考虑资源的消耗，不能使系统整体的效率受到太大的影响。
举例

流程控制语句

语句	跳转语句		循环语句			条件判断语句
关键字	ITERATE	LEAVE	LOOP	WHILE	REPEAT	IF	CASE
说明	重新开始循环只能用在循环语句内	跳出循环或者跳出程序体可以用在循环语句内、或以 BEGIN 和 END 包裹起来的程序体内使用	LOOP 循环不能自己结束，需要用跳转语句 ITERATE 或者 LEAVE 来进行控制	先判断条件，再执行循环体中的操作	先执行操作，后判断条件
语法			`标签：LOOP 操作 END LOOP 标签;`	`WHILE 条件 DO 操作 END WHILE;`	`REPEAT 操作 UNTIL 条件 END REPEAT；`	`IF 表达式1 THEN 操作1 [ELSEIF 表达式2 THEN 操作2]…… [ELSE 操作N] END IF`	`CASE 表达式 WHEN 值1 THEN 操作1 [WHEN 值2 THEN 操作2]…… [ELSE 操作N] END CASE;`

游标

游标是什么	由 SELECT 语句返回的结果集无法满足要求，需要一种机制来每次处理一行或者一部分行，游标就是这种机制
特点	可以逐条查看数据集中的记录；一次只能查看一条记录；只能向一个方向扫描数据，并且不能跳跃；游标是只读的

日志

日志记录的顺序与并发操作执行的顺序完全一致
MySQL 的日志系统遵循 WAL（Write-Ahead Logging）机制，即先写日志，后写数据库
事务故障的恢复也必须要用到日志文件（回滚日志、重做日志、二进制日志）

补充：

wal的三个规则：

对数据的修改操作必须在写入数据库之前写入到日志文件中；
日志必须严格按序记录；
事务提交之后，必须在日志写入成功之后，才能回复事务处理成功

视图

是什么：是存储在服务器中的查询语句，可以通过编写不同的查询语句，从数据库中抽取需要的数据。

注意：所有的视图都可以查询，但不是所有的视图都可以更新。定义视图的查询语句若包含分组、聚合函数等，则不可用进行dml操作。

触发器

是什么	监听数据库的改变
特点	是一种存储程序事件驱动
优缺点	优点：确保数据的完整性比用事务来完成相同的操作更加高效，且消耗资源更少对数据的合法性进行检查可记录操作日志如果触发器中的操作失败了，那么触发这个触发器的数据操作也会失败，不会出现一个成功、一个失败的情况缺点：可读性差
语法	创建触发器的语法结构是 CREATE TRIGGER 触发器名称 {BEFORE\|AFTER} {INSERT\|UPDATE\|DELETE} ON 表名 FOR EACH ROW 表达式；查看触发器的语句是： SHOW TRIGGERS\G; 删除触发器的语法结构是： DROP TRIGGER 触发器名称;
举例	DELIMITER // CREATE TRIGGER demo.upd_membermaster BEFORE UPDATE -- 在更新前触发 ON demo.membermaster FOR EACH ROW -- 表示每更新一条记录，触发一次 BEGIN -- 开始程序体 IF (new.memberdeposit <> old.memberdeposit) -- 如果储值金额有变化 THEN INSERT INTO demo.deposithist ( memberid, transdate, oldvalue, newvalue, changedvalue ) SELECT NEW.memberid, NOW(), OLD.memberdeposit, -- 更新前的储值金额 NEW.memberdeposit, -- 更新后的储值金额 NEW.memberdeposit-OLD.memberdeposit; -- 储值金额变化值 END IF; END // DELIMITER ;

权限

操作	语法	说明
创建角色	CREATE ROLE 角色名;
给角色赋予权限	GRANT 权限 ON 表名 TO 角色名;	GRANT SELECT,INSERT,DELETE,UPDATE ON demo.invcount TO 'stocker';
查看角色权限	SHOW GRANTS FOR '{角色名}'
删除权限	DROP ROLE 角色名称;
创建用户	CREATE USER 用户名 [IDENTIFIED BY 密码];	CREATE USER 'zhangsan' IDENTIFIED BY 'mysql';
给用户授权	GRANT 角色名称 TO 用户名称;
查看用户权限	SHOW GRANTS FOR 用户名;
激活角色	SET global activate_all_roles_on_login=ON;	MySQL 中创建了角色之后，默认都是没有被激活的，必须使用该激活语句后才能使用

数据库设计

范式

宗旨：以业务需求为主，尽可能参照范式要求设计数据库，以减少冗余

范式说明：

第一范式	数据表中所有字段都是不可拆分的基本数据项。
第二范式	在满足第一范式的基础上，数据表中所有非主键字段，必须完全依赖全部主键字段，不能存在部分依赖主键字段的字段。
第三范式	在满足第二范式的基础上，数据表中不能存在依赖于非主键字段的字段。

ER模型

ER模型是什么：实体关系模型

三要素：

三要素	说明	表示
实体	实体分为两类，分别是强实体和弱实体强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。	矩形
属性	实体的特性	圆形
关系	实体之间的联系	菱形

如何把ER模型转换成数据表：

一个实体通常转换成一个数据表
一个多对多的关系，通常也转换成一个数据表
一个 1 对 1，或者 1 对多的关系，往往通过表的外键来表达，而不是设计一个新的数据表
属性转换成表的字段。

优化

分析查询效率

分析查询语句命令：
{ EXPLAIN | DESCRIBE | DESC }查询语句;

eg：
EXPLAIN SELECT itemnumber,quantity,price,transdate FROM demo.trans WHERE itemnumber=1 AND transdate>'2020-06-18 09:00:00' AND transdate<'2020-06-18 12:00:00';

结果：

字段说明：

id：是一个查询序列号
select_type：查询的类型（4种取值：SIMPLE：表示简单查询，不包含子查询和联合查询。PRIMARY：表示是最外层的查询。UNION：表示联合查询中的第二个或者之后的查询。DEPENDENTUNION：表示联合查询中的第二个或者之后的查询，而且这个查询受外查询的影响。）
table：表示与查询结果相关的表的名称
partition：表示查询访问的分区
key：表示优化器最终决定使用的索引是什么
key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就是空。
ref：表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录。如果这个值是“func”，就表示用函数的值与索引字段进行比对。
rows：表示为了得到查询结果，必须扫描多少行记录。
filtered：表示查询筛选出的记录占全部表记录数的百分比。
possible_key：表示 MySQL 可以通过哪些索引找到查询的结果记录。如果这里的值是空，就说明没有合适的索引可用。你可以通过查看 WHERE 条件语句中使用的字段，来决定是否可以通过创建索引提高查询的效率
Extra：表示 MySQL 执行查询中的附加信息。
type：表示表是如何连接的。至于具体的内容，你可以参考下查询分析语句输出内容说明。

提高查询效率：使用索引

注意：

使用LIKE关键字时注意：通配符在前面的筛选条件是不能用索引，eg：LIKE‘%aa’和WHERE字段 LIKE ‘%aa%’都不能使用索引，只有LIKE‘aa%才能用索引
只有当条件语句中只有关键字“OR”，并且“OR”前后的表达式中的字段都建有索引的时候，查询才能用到索引。

调整系统参数

my.ini 文件中的系统参数：

InnoDB_flush_log_at_trx_commit：默认的值是 1，意思是每次提交事务的时候，都把数据写入日志，并把日志写入磁盘。这样做的好处是数据安全性最佳，不足之处在于每次提交事务，都要进行磁盘写入的操作。在大并发的场景下，过于频繁的磁盘读写会导致 CPU 资源浪费，系统效率变低。这个参数的值还有 2 个可能的选项，分别是 0 和 2。其中，0 表示每隔 1 秒将数据写入日志，并将日志写入磁盘；2 表示，每次提交事务的时候都将数据写入日志，但是日志每间隔 1 秒写入磁盘。0 虽然效率更高一些，但是数据安全性方面不如 2。
InnoDB_buffer_pool_size：InnoDB 存储引擎使用缓存来存储索引和数据。这个值越大，可以加载到缓存区的索引和数据量就越多，需要的磁盘读写就越少
InnoDB_buffer_pool_instances：将 InnoDB 的缓存区分成几个部分。

eg：把 InnoDB_buffer_pool_instances 的值修改为 64，意思就是把 InnoDB 的缓存区分成 64 个分区，这样就可以同时有多个进程进行数据操作，CPU 的效率就高多了

注意：MySQL 的配置文件 my.ini 之后，保存的时候，记得用 ANSI 的格式

performance_schema

performance_schema是系统自带的数据库

performance_schema中重要的数据表：

表名	说明
setup_instruments	保存哪些对象发生的事件可以被系统捕获，把这些事件称作信息生产者
setup_consumers	指定了是否保存监控事件发生的信息，被称为消费者
events_statements_current	记录的是当前系统中的查询事件。表中的每一行对应一个进程，一个进程只有一行数据，显示的是每个进程中被监控到的查询事件。
events_statements_history	记录了系统中所有进程中最近发生的查询事件。这个表中包含的查询事件都是已经完成了的
events_statements_history_long	记录了系统中所有进程中最近发生的查询事件，表中包含的查询事件都是已经完成了的

TIPS

遇到 CPU 资源不足的问题，可以从 2 个思路去解决：

疏通拥堵路段，消除瓶颈，让等待的时间更短；
开拓新的通道，增加并行处理能力。

数据库安全

容灾

为了防止灾害出现，设置主从架构，从服务器实时自动同步主服务器的数据，一旦主服务器宕机，可以切换到从服务器继续使用。这样就可以把灾害损失降到最低。

可以采用一主一从，也可以采用一主多从的策略。

搭建主从服务器步骤：

确保从服务器可以访问主服务器，将主从服务器ip设置为同一网段内。（注：主服务器入口方向的 3306 号端口需要打开，否则从服务器无法访问主服务器的 MySQL 服务器。）
修改主从服务器的系统配置文件 my.ini，使主从服务器有不同的 ID 编号，并且指定需要同步的数据库。

主服务器my.ini文件：


binlog-do-db = table_a -- 需要同步的数据库
binlog-do-db = table_b -- 需要同步的数据库


server-id=1  -- 主服务器的ID设为1




从服务器my.ini文件：


binlog-do-db = table_a -- 需要同步的数据库
binlog-do-db = table_b -- 需要同步的数据库


server-id=2  -- 从服务器的ID设为1

分别重启主从服务器上的 MySQL 服务器。
在主服务器上创建数据同步用户，并赋予所有权限。
在从服务器上启动数据同步，开始从主服务器中同步数据。

备份

设置了主从服务器，也不是万无一失，还是要设置备份。

可参考的策略：每天晚上 12:00 做一个自动备份，循环备份 7 天，创建 7 个文件夹，从 Monday 到 Sunday，每个文件夹中保存对应的备份文件，新的覆盖旧的。

mysql命令

说明

关键字	说明
limit	limit a,b：展示从a+1条~b条的数据
ON DUPLICATE	插入数据时，若表的约束性条件被破坏，可用该关键字处理

举例

复制表结构

CREATE TABLE demo.importheadhist LIKE demo.importhead;

在某个字段后面添加字段

ALTER TABLE demo.importheadhist ADD confirmer INT AFTER supplierid;

修改表中的字段名称和类型

ALTER TABLE demo.importheadhist CHANGE quantity importquantity DOUBLE;

修改表中的字段类型：（modify只能修改字段属性）

ALTER TABLE demo.importheadhist MODIFY importquantity DECIMAL(10,3);

将表中字段“salesprice”改成不能重复，并且不能为空：

ALTER TABLE demo.goodsmaster
CHANGE COLUMN salesprice salesprice DECIMAL(10,2) NOT NULL UNIQUE;

联表查询

select account_id from user_account_info where user_id in (select user_id from inviter_relationship) order by id desc limit 101;

三表查询

select coupon_commodity_mapping.coupon_id,coupon_commodity_mapping.commodity_id from coupon left join coupon_register on coupon_register.coupon_uuid = coupon.uuid left join coupon_commodity_mapping on coupon_commodity_mapping.coupon_id = coupon.id where coupon_register.account_id = "246496738" and coupon_register.status in (2,4) and coupon_commodity_mapping.commodity_id = "LC000000015";

内连接

select*from coupon_register inner join coupon on coupon_register.coupon_uuid=coupon.uuid where coupon_register.account_id='{0}' and coupon_register.statusin(2,4) and coupon.type in {1};

查询某段时间内的结果

select count(*) from nup_user_info where create_time BETWEEN '2017-01-01 15:03:14' AND '2020-11-30 15:00:00';

查询成绩排名第3的同学信息

select * from xx order by score desc limit 2,1;

(LIMIT 后面可以跟 2 个参数，第一个参数表示记录的起始位置（第一个记录的位置是 0），第二个参数表示返回几条记录)

查询去重某字段后的总数

select count(distinct user_id) from user_community_identity_mapping where status = 1 and type = 1
select count(distinct car_order_no, account_id) from car_order_relation where account_id = "248548408";

查询手机号以98开头的数据

select mobile from user where identity = 5 and mobile like '98%' order by id limit 100

查找某A表中字段a不在B表中存在的数据

select * from A where a not in (select a from B)

查找字段a不等于“b”的数据

select * from A where a <> "b"

随机查询一条数据

SELECT * FROM users AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(userId) FROM `users`)-(SELECT MIN(userId) FROM users))+(SELECT MIN(userId) FROM users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY t1.userId LIMIT 1
select user_id from user_basic_info where id >= (select floor(rand() * (select max(id) from user_basic_info))) order by id limit 1