专栏首页关忆北.MySQL学习笔记(长期更新)

MySQL学习笔记(长期更新)

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的区别:

  1. 如果需要连接从关联表中 获取需要的数据,WHERE是先筛选后连接,而HAVING是先连接后筛选,所以WHERE比HAVING更高效
  2. 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>
关联表查询准则:

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

找驱动表的规律:

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:

  1. Using index condition:执行时使用了索引
  2. Using where:执行时通过Where条件进行了筛选
  3. 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读取错误的可能。

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

  1. READ UNCOMMITTED:可以读取事务中还未提交的被修改的数据
  2. READ COMMITTED:只能读取事务中已经提交的被修改的数据
  3. REPEATABLE READ:表示一个事务中,对一个数据读取的值,永远跟第一次读取的值一致,不受其他事务中数据操作的影响。(默认)
  4. 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型子查询:把外层查询结果拿到内层,看内层查询是否成立

使用子查询规则:

  1. 一个子查询必须放在圆括号内
  2. 将子查询放在比较条件的右边以增加可读性
  3. 子查询不包含ORDER BY字句,对一个SELECT语句只能有一个ORDER BY字句,如果使用的话,只能放在主SELECT语句的最后。
  4. 子查询中可以使用两种比较条件:单行运算符(>, =, >=, <, <>, <=)和多行运算符(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服务。

查询扫描过的最少记录数

慢查询判断条件
  1. 查询执行时间
  2. 扫描过的最少记录数

当前扫描过的最少记录数是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截⽌的所有数据更新操作。

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!
本文分享自作者个人站点/博客:https://blog.csdn.net/weixin_42313773复制
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • Mybatis学习笔记,持续更新

    mapper.xml 文件中执行的 insert sql 我们执行一个insert操作,参数是一个User对象

    星尘的一个朋友
  • HTML/CSS/JavaScript学习笔记【持续更新】

    HTML <font> 标签 定义和用法 <font> 规定文本的字体、字体尺寸、字体颜色。 实例 规定文本字体、大小和颜色: 1 <font size="3"...

    Angel_Kitty
  • Java学习笔记【持续更新】

    一个简单的java程序如下: class Sakura {   public static void main(String[] arges)   {     ...

    Angel_Kitty
  • mysql 学习笔记

    以前主要使用oracle做数据库,现在换成mysql了,发现不一样的地方还是挺多的,记录一下: 一、centos上的yum install方式安装  完全卸...

    菩提树下的杨过
  • MySQL学习笔记

    这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PR...

    林祈
  • mysql学习笔记

    基本结果有 SELECT 子句,FROM 子句,WHERE 子句组成的查询块 SELECT <字段名> /ntl&FROM <表或视图名> WHERE <...

    宸寰客
  • MySQL学习笔记

    ​ 数据库提供一个存储空间用于存放各种数据(其中包括整型、文本、小数、日期等),我们可以将数据库看作是一个存储数据的容器。

    杨校
  • MySQL 学习笔记

    来源:https://shockerli.net/post/1000-line-mysql-note/

    用户1516716
  • MySQL学习笔记

    (MySQL官网下载地址:http://dev.mysql.com/downloads/mysql/)

    Mirror王宇阳
  • MySQL学习笔记

    `create table users( name varchar(10), age int, height double);`

    用户4166051
  • mysql学习笔记

    InnoDB引擎与MyISAM引擎 mysql是关系型数据库。其中的存储引擎可以show engines来查看。我的版本是5.6.26的,查看版本用selec...

    wangxl
  • MySQL 学习笔记(二):数据库更新、视图和数据控制

    希希里之海
  • MySQL 学习笔记(二):数据库更新、视图和数据控制

    希希里之海
  • 【笔记归档】mysql学习笔记

    fankhu
  • Python 学习笔记 - 不断更新!

    太久不写python,已经忘记以前学习的时候遇到了那些坑坑洼洼的地方了,开个帖子来记录一下,以供日后查阅。

    py3study
  • Android学习笔记,不断更新

    ​ 所有以drawable开头的文件夹都是用来放图片的; ​ 所有以mipmap开头的文件夹都是用来放应用图标的 ​ 所有以values 开头的文件夹都是用...

    小锋学长生活大爆炸
  • vue-学习笔记(更新中...)

    xing.org1^
  • 学习linux笔记(不断更新)

    平时用的Mac,不想再去安装一遍双系统了,因此直接用docker安装了centos。主要步骤为到docker官网下载Stable并安装后,配置一下镜像源(htt...

    xiaoxi666
  • Java学习笔记, 不断更新

    (1)split表达式,就是一个正则表达式。* ^ | 等符号在正则表达式中属于一种有特殊含义的字符,如果使用此种字符作为分隔符,必须使用转义符即 \ 加以转...

    小锋学长生活大爆炸

扫码关注腾讯云开发者

领取腾讯云代金券