MySQL学习笔记（长期更新）

关忆北.

发布于 2021-12-07 16:21:10

9180

发布于 2021-12-07 16:21:10

文章被收录于专栏：关忆北.关忆北.

002-字段：这么多字段类型，该怎么定义

字段评估：需要考虑存储空间和可靠性的平衡

类型选择：确保数据不会超过取值范围

注意：精度问题：浮点数存在精度丢失的问题，对精度要求高的字段（如价格）不要使用，原因是MySQL采用二进制存储数据，如果尾数不是0或5，就需要使用四舍五入的方式来表达，定点数类型：DECIMAL(将十进制数的整数部分和小数部分拆开，分别转换成十六进制数存储)。主键问题 text类型长度不定，所以无法做主键，可使用char、varchar类型时间类型项目中使用时间类型优先考虑使用率最高的DATETIME,其占用存储空间更多、表达时间类型更为完整，取值范围更大。

总结

整数：INT。⼩数：DECIMAL。字符串：TEXT。⽇期与时间：DATETIME。

003-表：怎么创建和修改表

约束限定了表中数据应该满足的条件。

建表时给字段设置默认值的做法，就是默认约束。在插入时，如果不明确给字段赋值，那么系统会把设置的默认值自动赋值给字段。

约束类型：

默认约束：插入时如果没有指定值，则插入默认值
主键约束：保证数据的唯一性
外键约束：预防破坏表之间连接的行为
非空约束：字段值不能为空
唯一性约束：字段值不能重复
自增约束：字段在插入时自动+1，系统自动赋值

满足唯一约束的字段，可以为空值，但满足主键约束的字段，自动满足非空约束。（TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT）

004-增删改查：如何操作表中的数据？

插入：插⼊⼀条部分字段数据记录是可以的，但前提是，没有赋值的字段，⼀定要让MySQL知道如何处理，⽐如可以为空、有默认值，或者是⾃增约束字段，等等，否则，MySQL会提⽰错误的。

插入查询结果，MySQL⽀持把查询的结果插⼊到数据表中，我们可以指定字段，甚⾄是数值，插⼊到数据表中。

INSERT INTO 表名 (字段名)

SELECT 字段名或值

FROM 表名

WHERE 条件

修改：不要修改主键字段的值，主键是数据记录的唯一表示，修改主键的值可能破坏数据完整性。

Group By 作用：对查询出的数据分组，通常与聚合函数使用

HAVING：⽤于筛选查询结果，跟WHERE类似。

FROM:其后可以跟表或查询结果（派生表/子查询），意思是将查询的数据当作一个虚拟的数据表来看待，需要使用AS关键字对派生表进行取名。

ORDER BY：对查询结果排序，ASC升序、DESC降序。

LIMIT (startIndex,length)：显示部分查询结果

INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件
DELETE FROM 表名
WHERE 条件
UPDATE 表名
SET 字段名=值
WHERE 条件
SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，⾏数

主键冲突时处理：ON DUPLICATE

INSERT INTO demo.goodsmaster
SELECT *
FROM demo.goodsmaster1 as a
ON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;

当主键冲突时，唯一主键对应的数据进行更新。

05-主键：如何正确设置主键？

主键设计思路：

业务字段做主键
自增字段做主键
手动赋值字段做主键
尽量不要使用业务字段

总结：业务字段做主键，不排除后期业务需要会出现主键一致的问题。单系统应用可以使用自增主键不适用于多系统手动赋值可以确保在系统中的唯一性

06-外键和连接：如何做关联查询？

多表查询：把分散在多张表中的数据查询出来。

外键（FOREIGN KEY）和连接（JOIN）将多张表关联。

设计外键

有两张表A、B通过id进行关联，如果Id在A中时主键，那么A便称为主表，B表就是从表，B表中的id字段就是外键。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DkSOLEaN-1624283827558)(…/…/AppData/Roaming/Typora/typora-user-images/image-20210602212346409.png)]

外键约束

从表中定义定义的外键指定外键字段、对应主表中的字段，MySQL会根据外键约束的定义，监控主表中数据的删除操作，如果发现要删除的主表记录，正在被从表中某条记录的外键字段所引用，MySQL会提示错误，从而保证关联数据不会丢失。

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名）;

07-条件语句：WHERE与HAVING有什么不同

having：与group by连用实现对分组字段或分组计算函数进行限定

where：对数据表中的字段进行限定

having和where的区别：

如果需要连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选，所以WHERE比HAVING更高效
WHERE可以直接使用表中字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件，HAVING必须要与GROUP BY配置使用，可以把分组计算的函数和分组字段作为筛选条件

在需要对数据进行分组统计的时候，HAVING可以完成WHERE不能完成的任务。

SQL执行顺序

(8) SELECT (9)DISTINCT<Select_list>
(1) FROM <left_table> (3) <join_type>JOIN<right_table>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_list>
(11) LIMIT<limit_number>

https://img2018.cnblogs.com/blog/747662/201912/747662-20191208111950097-1165896309.png

关联表查询准则：

驱动表：多表关联时第一个被处理的表（基表），然后再使用此表的记录去关联其他的表，驱动表选择的原则：在对最终结果集没有影响的前提下，优先选择结果集最少的那张表作为驱动表。

找驱动表的规律：

LEFT JOIN 一般以左表为驱动表（RIGHT JOIN一般则是右表），INNER JOIN 一般以结果集少的表为驱动表，如果还觉得有疑问，则可用 EXPLAIN 来找驱动表，其结果的第一张表即是驱动表。你以为 EXPLAIN 就一定准吗？执行计划在真正执行的时候是可能改变的！绝大多少情况下是适用的，特别是 EXPLAIN

08-聚合函数：怎么高效的进行分组统计？

LEFT(str,n):表示返回字符串str最左边的n个字符串。

select left(b.transdate, 10), c.goodsname, sum(a.quantity), sum(a.salesvalue)
from transactiondetails as a
         join transactionhead as b on a.transactionid = b.transactionid
         join goodsmaster as c on a.itemnumber = c.itemnumber
group by left(b.transdate, 10), c.goodsname
order by left(b.transdate, 10), c.goodsname;
#(transdate,10)是时间信息，datetime类型的默认格式是YYYY-MM-DD，所以长度是10

sum()、avg()、min()、max():求和、平均、最大、最小值是根据分组计算，使用时需对分组结果有准确把握。

max(column1)、min(column2)是相互独立计算的，并不会出现在同一条记录上。

count(*)：统计一共有多少条记录

count(字段)：统计有多少个不为空的字段值

count(*)与group by连用统计分组内有多少条数据

计算直接在数据库中执行，比在应用层面完成相同的工作，效率高很多。

09-时间函数：时间类数据，MySQL是怎么处理的？

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等。

select extract(hour from transdate) from transactionhead;
select extract(day from transdate) from transactionhead;

DATE_ADD(date，interval 表达式type)：表示计算从时间点"date"开始，向前或者向后一段时间间隔的时间。"表达式"的值为时间间隔数，正数表示向后，负数表示向前，type表示时间间隔单位（比如年月日等）。
LAST_DAY(date)：表示获取日期时间"date所在月份的最后一天的日期"。

#获取2019年12月的数据
select date_add('2020-12-10',interval  -1 year );
select date_add(DATE_ADD('2020-12-10',interval -1 year ),INTERVAL -1 month );
select last_day(date_add(DATE_ADD('2020-12-10',interval -1 year ),INTERVAL -1 month ));
select date_add(last_day(date_add(DATE_ADD('2020-12-10',interval -1 year ),INTERVAL -1 month )),interval + 1 day );

09-时间函数：时间类数据，MySQL是怎么处理的？

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等。

select extract(hour from transdate) from transactionhead;
select extract(day from transdate) from transactionhead;

DATE_ADD(date，interval 表达式type)：表示计算从时间点"date"开始，向前或者向后一段时间间隔的时间。"表达式"的值为时间间隔数，正数表示向后，负数表示向前，type表示时间间隔单位（比如年月日等）。
LAST_DAY(date)：表示获取日期时间"date所在月份的最后一天的日期"。

#获取2019年12月的数据
select date_add('2020-12-10',interval  -1 year );
select date_add(DATE_ADD('2020-12-10',interval -1 year ),INTERVAL -1 month );
select last_day(date_add(DATE_ADD('2020-12-10',interval -1 year ),INTERVAL -1 month ));
select date_add(last_day(date_add(DATE_ADD('2020-12-10',interval -1 year ),INTERVAL -1 month )),interval + 1 day );

eg:商品1周一、周三、周五分别打9、75、88折

商品2周二、周四、周六分别打5、65、8折

select curdate()                                                                     as 日期,
       case dayofweek(curdate()) - 1 when 0 then 7 else dayofweek(curdate()) - 1 end as 周几,
       a.goodsname                                                                   as 商品名称,
       a.price                                                                       as 价格,
       ifnull(b.discountrate, 1)                                                     as 折扣率,
       a.price * ifnull(b.discountrate, 1)                                           as 折后价格,
       a.itemnumber
from goodsmaster as a
         left join discountrule as b on (a.itemnumber = b.itemnumber)
    #必须是一周中的某天  才满足促销条件
    and case dayofweek(curdate()) - 1 when 0 then 7 else dayofweek(curdate()) - 1 end = b.weekday;

CASE WHEN语法：

10-如何进行数学计算、字符串处理的条件和判断？

floor:向下取整

ceil:向上取整

round(a,b):保留小数

concat(s1,s2…):把字符串s1,s2拼接起来，组成一个字符串

cast(表达式 as char):表示将表达式的值转换成字符串

char_length(字符串):表示获取字符串的长度

space(n):表示获取一个由N个空格组成的字符串

IF语句

IFNULL（V1，V2）：表⽰如果V1的值不为空值，则返回V1，否则返回V2。
IF（表达式，V1，V2）：如果表达式为真（TRUE），则返回V1，否则返回V2。

其他常用方法：

11-索引：如何提高查询的速度？

explan中的extra:

Using index condition：执行时使用了索引
Using where：执行时通过Where条件进行了筛选
Using MRR：使用了顺序磁盘读取的策略

使用经常被用作筛选条件的字段做索引。

如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候，MySQL会选择使用最优的索引来执行查询操作。

Explain Type类型

system:系统表，少量数据，往往不需要进行磁盘IO
const:常量连接
eq_ref:主键索引（primary key）或者非空唯一索引（unique not null）等值扫描
ref:非主键非唯一索引等值扫描
range:范围扫描
index:索引树扫描
ALL:全表扫描（full table scan）

type由快到慢

system > const > eq_ref > ref > range > index > ALL

const索引条件:

命中主键（primary key）或者唯一索引（unique）
被连接的部分是一个常量值（const）

eq-ref索引条件：

联表（join）查询
命中主键（promary key）或者非空唯一索引（unique not null）
等值连接

create table user_balance (
  uid int primary key,
  balance int
)engine=innodb;

#表的主键中加入索引
explain
select *
from user
         left join user_balance on user.id = user_balance.uid
where user.id = user_balance.uid;

等值连接： select * from Table_1 t1, Table_2 t2 where t1.C = t2.D

ref索引条件:

命中非唯一索引
连表查询

range:

范围查询（between、in、>,>=,<,<=）

index:

需要扫描索引上的全部数据，仅比全表扫描快一点

ALL:

未命中索引，全表扫描

组合索引的原理

组合索引的多个和字段是有序的，遵循左对齐的原则。创建组合索引，排序方式：branchnumber、cashiernumber和itemnumber，因此筛选条件也要从左向右的原则，如果中断则后面的条件也无法使用索引。 branchnumber = 11 AND cashiernumber = 1 AND itemnumber = 100，包含了从左到右所有字段，所以可以最大限度使用索引。加入把条件换成cashiernumber = 1 AND itemnumber = 100，最左边的字段无法命中组合索引最左侧字段条件，所以无法使用组合索引。如果筛选是一个范围，若无法精确定位，也相当于中断。⽐如“branchnumber > 10 AND cashiernumber = 1 AND itemnumber = 100”这个条件，只能⽤到组合索引中branchnumber>10的部分，后⾯的索引就都⽤不上了。

12-事务：怎么确保关联操作正确执行？

正确使用事务，保证关联操作同时成功或同时失败回滚。

START TRANSACTION;
SELECT/UPDATE/CREATE/DELETE SQL语句
commit ;
rollback;

START TRANSACTION/BEGIN：表示开始事务，该语句后的SQL都属于当前事务
COMMIT:提交事务，执行当前事务的全部操作，使更改的数据生效
ROLLBACK:表示回滚当前事务的操作，取消对数据的修改

事务的四个特性：

原子性（atomicity）:表示事务的操作要么全部执行，要么全部不执行。一个整体，不能被打断。
一致性（consistency）:表示数据的完整性不会因为事务的执行而受到破坏。
隔离性（cisolation）:表示多个事务同时执行的时候，不互相打扰。不同的隔离级别，相互独立的程度不同。
持久性（durability）:表示事务对数据库的修改是永久有效的。

START TRANSACTION;
INSERT INTO mytrans
values (1, 1, 5);
UPDATE inventory
SET invquantity=invquantity - 5
where itemnumber = 1;
commit;
rollback;

MySQL并不会处理SQL语句中的错误，所以如果事务中的某SQL执行出现错误后提交会出现事务不一致的问题，如mytrans在插入时出现错误，inventory表执行成功，库存字段数据-5，这样会导致数据不一致的问题。为解决该问题，可以使用rollback进行回滚。

rollback回滚：在一个事务中，如果有一个SQL语句执行失败，那么当前事务便不会提交，相当于当前事务中的SQL并没有执行。

当操作多表时，未防止数据不一致的问题，保持操作的原子性，需要事务回滚rollback。

如何做好事务的隔离性？

会员张三是储值会员，他的会员卡⾥有100元。张三⽤会员卡到⻔店消费100元，他爱⼈⽤他的会员卡在⽹上消费100元。张三在⻔店消费结算的时候，开启了⼀个事务A，包括这样3个操作： 1. 读取卡内⾦额为100； 2. 更新卡内⾦额为0； 3. 插⼊⼀条销售流⽔。张三的爱⼈在⽹上购物，开启了⼀个事务B，也来读取卡内⾦额。如果B读取卡内⾦额的操作，发⽣在A更新卡内⾦额之后，并且在插⼊销售流⽔之前，那么B读出的⾦额应该是多少呢？如果B读出0元，那么，A有可能由于后⾯的操作失败⽽回滚。因此，B可能会读到⼀条错误信息，⽽导致本来可以成功的交易失败。有什么办法可以解决这个问题呢？

MySQL的锁：MySQL可以把A中被修改过而且还没有提交的数据锁住，让B处于等待状态，一直到A提交完成，或者失败回滚再释放锁，允许B读取这个数据，这样就可以防止A回滚导致B读取错误的可能。

锁的使用方式不同，隔离的程度也不同，隔离程度越高，消耗的资源越多。

READ UNCOMMITTED:可以读取事务中还未提交的被修改的数据
READ COMMITTED:只能读取事务中已经提交的被修改的数据
REPEATABLE READ:表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。（默认）
SERIALIZABLE:任何一个事务，一旦对某一个数据进行了任何操作，那么一直到这个事务结束，MySQL都会把这个数据锁住，禁止其他事务对这个数据进行任何操作

对于核心数据的更改操作，需要较高的隔离等级。

14-视图：如何简化查询？

create view viewName as select * from --查询语句;

子查询：嵌套在另一个查询中的查询。

派生表：如果我们在查询中把子查询的结果作为一个表来使用，这个表就是派生表。

子查询按返回结果集进行分类：

表子查询：返回的结果是一个行的集合，N行N列，（N>=1）。表子查询经常用于父查询的FROM子句中。
行子查询：返回的结果是一个列的集合，一行N列，（N>=1）。行子查询常用于父查询的FROM字句和WHERE字句中。
列子查询：返回的结果是一个行的集合，N行一列，（N>=1）。
标量子查询：返回的结果集是一个标量集合，一行一列。

子查询按返回结果集的调用方法进行分类：

where型子查询：内层查询结果当作外层查询的条件
from型子查询：内层查询的结果供外层再次查询
exists型子查询：把外层查询结果拿到内层，看内层查询是否成立

使用子查询规则：

一个子查询必须放在圆括号内
将子查询放在比较条件的右边以增加可读性
子查询不包含ORDER BY字句，对一个SELECT语句只能有一个ORDER BY字句，如果使用的话，只能放在主SELECT语句的最后。
子查询中可以使用两种比较条件：单行运算符（>, =, >=, <, <>, <=）和多行运算符（IN，ANY，ALL）。

15-存储过程：如何提高程序的性能和安全性？

16-游标：对于数据集中的记录，

该怎么逐条处理？

游标：对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。

18-权限管理：如何控制数据库访问，消除安全隐患？

角色是MySQL8.0中引入的新功能，相当于一个权限的集合，作用是方便管理拥有相同权限的用户，类似于RBAC权限框架。 恰当的权限设定，可以确保数据的安全性。

创建角色

create role 'roleName'
#可省略主机,表示该账号可以在任何主机上登录
@'localhost';

赋予角色权限

GRANT permissions on tableName TO roleName;

#查看是否添加角色成功
show GRANTS FOR 'roleName'

删除角色

DROP ROLE 'roleName';

**注意：**MySQL中创建了角色后，默认都是没有被激活的。

19-日志（上）：系统出现问题，如何及时发现？

通用查询日志

通用查询日志记录了所有用户的连接开始时间和截至时间，以及发给MySQL数据库服务器的所有SQL指令。

通过该命令可以还原具体场景，定位问题。

#查询日志记录
show variables like '%general_log%';
general_log : OFF
#说明通用日志是在关闭状态
general_log_file:是日志存放位置
#开启日志查询
set GLOBAL general_log = 'ON';
#修改日志存放位置
set @@global.general_log_file = 'pathValue'

在ssh中查看MySQL执行语句：

tail -f /var/lib/mysql/iZbp1etc398n6cac736htxZ.log

慢查询日志

慢查询日志是用来记录执行时间超过指定时长的查询。

当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，优化慢查询。

MySQL配置开启慢查询

vim /etc/my.cnf

#插入以下数据
slow_query_log=TRUE      											#开启慢日志
slow_query_log_file=/home/mysql02/mysql/logs/slow_query_log.txt    	#日志存放位置
long_query_time=5													#超时时间5秒（超过5秒就会被记录下来）

restart mysqld.service

long_query_time还可以通过set global long_query_time=xxx来设置，省略单位秒，设置完成后重启MySQL服务。

查询扫描过的最少记录数

慢查询判断条件

查询执行时间
扫描过的最少记录数

当前扫描过的最少记录数是0，long_query_time是5，意思是只要查询超过5秒，哪怕一个记录都没有扫描过，都要被记录到慢查询中。

错误日志

错误日志记录了MySQL服务启动、停止的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示。

二进制日志

主要记录数据的更新事件。

查看当前正在写入的二进制日志：

show master status;

查看所有二进制日志的SQL语句

show binary logs;

用二进制日志恢复数据(mysqlbinlog)

mysqlbinlog -start-positon=xxx -end postion=yyy 二进制文件名 | mysql -u -p;

执⾏⼆进制⽇志中从位置xxx开始，到yyy截⽌的所有数据更新操作。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-05-24 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度