mysql备忘录

前言：今天要用mysql时突然发现自己都要忘完了，sad

1. mysql 配置

Access Point Denied:当年出的问题

mysqld服务：net start / stop mysql

2. mysql 命令行

1. 打开mysql：mysql (-h localhost ) -u root -p ( -d DataBaseName)
2. 打开数据库：use (databasename)

3. 基本用法

创建：

• create

4. 理论知识

• 两类数据模型

  • 概念模型：用于规划设计
  • 逻辑模型（忽略物理模型）：计算机实现
    • 层次模型
    • 网状模型
    • 关系模型
    • 面向对象数据模型
    • …

• 概念模型：

  • 实体（entity）：可区别的事物
  • 属性（attribute）：实体的某一特征
  • 码（key）：唯一标识实体的属性集合
  • E-R图：
    • Entity-Relationship approach 实体-联系方法

• 关系模型：

  • 型（type）- 值（value）：对数据的结构和属性的说明 - 具体赋值

  • 模式（schema）：数据库中全体数据的逻辑结构和特征描述

    • 外模式（external）：用户模式
    • 内模式（internal）：存储模式
    • 模式只涉及型，不涉及值

  • 域（domain）：一组相同类型数值的集合

  • 笛卡尔积：组合（排列组合的那个组合，比如33得9种组合），其中每一种组合叫做一个*元组

  • 关系（relation）：D1*D2*…Dn的子集叫做在D1,D2..Dn商的关系，记作R(D1,D2…Dn)

  • 候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何真子集都不能再标识，则称该属性组为（超级码）候选码。

    例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是（超级码）候选码。

  • 主码：从多个候选码中任意选出一个做为主码，如果候选码只有一个，那么候选码就是主码。

  • 主属性就是包含在任何候选码(这里面当然包括主码)中的属性；非主属性就是不包含在任何候选码中的属性。

5. 查询

• 消除重复行
  • select distinct 列1,... from 表名;
• 统计不重复
  • SELECT COUNT(DISTINCT column(s)) FROM table
• 条件查询
  • select * from 表名 where 条件;
  • select * from students where id in(1,3,8);
  • select * from students where height is null;
    • null与’’是不同的
    • 查询没有填写身高的学生
• 优先级
  • 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符and比or先运算，如果同时出现并希望先算or，需要结合()使用
• 排序
  • select * from students where gender=1 and is_delete=0 order by id desc;
• 聚合函数
  • count
  • max
  • avg()
• 分组
  • select gender,group_concat(name) from students group by gender;
    • group_concat(字段名)可以作为一个输出字段来使用，表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
  • select gender,group_concat('(id=',id,',name=',name,')') from students where gender!=2 group by gender;

5.1. 子查询

一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低

SELECT cust_id FROM orders

WHERE order_num

IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');

5.2. EXPLAIN

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

• EXPLAIN SELECT * FROM user

• SIMPLE: 简单的select查询，查询中不包含子查询或者UNION
• 从好到坏
  • system > const > eq_ref > ref > range > index > all
  • Index: Full Index Scan，Index与All区别为index类型只遍历索引树

比如我们需要查询一个公司的同名同姓的员工的最小ID，我们会很自然的联想到直接MIN()函数直接取出结果

• SELECT MIN(id) FROM staff_info WHERE staff_name='WangYe'
  • type: ALL

为了能够使用索引来扫描全表，我们换一种思路，因为所要取的字段正好为索引字段，可以将该索引字段进行排序。由于该索引保存的数据结构为B-Tree，正好本身就自带顺序，按照升序排列该字段，再用LIMIT取出第1条数，正好就是该字段的最小值

• SELECT id FROM staff_info WHERE staff_name='WangYe' ORDER BY id ASC LIMIT 1
  • type: index

5.3. LIMIT

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   

SELECT * FROM table LIMIT 5; //检索前 5 个记录行 等价于LIMIT 0,n

5.4. CREATE

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

6. 主键与外键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键

• 用来保证数据完整性
• 唯一标识一条记录，不能有重复的，不允许为空

成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键

• 外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性
• 一个表可以有多个外键
• 外键可以有重复的, 可以是空值

6.1. 自增

• ``idint(11) NOT NULL AUTO_INCREMENT,

7. 引掣

• InnoDB 支持事务，MyISAM 不支持事务

• InnoDB 支持外键，而 MyISAM 不支持。

• InnoDB 是聚集索引，MyISAM 是非聚集索引。

  • 聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据

• InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快

• InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。

  • 一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一

• 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB

• 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

• Mysql 中 MyISAM 和 InnoDB 的区别有哪些？ - oscarwin的回答 - 知乎

8. ORM

ORM是“对象-关系-映射”的简称。（Object Relational Mapping，简称ORM）

• orm其实就是将类对象的语法翻译成sql语句的一个引擎
• orm语句 -- sql -- 调用pymysql客户端发送sql -- mysql服务端接收到指令并执行

# sqlAlchemy
@app.route("/")
def index():
  r1 = Role(name="用户1")
  db.session.add(r1)
  db.session.commit()    # flask默认使用事务，所以每一次操作都要提交事务

  r2 = Role(name="用户2")
  r3 = Role(name="用户3")
  r4 = Role(name="用户4")
  db.session.add_all([r2,r3,r4])    #多行时可以用session.add_all([x,x,x])
  db.session.commit()
  return "hello"

9. acid

• ACID是什么？描述一下？- 知乎

• 原子性： 一个事务可能会包含多种操作，比如转账操作包括从你的账户上扣钱和对方账户加钱。虽然内部有多步操作，但是外部看来它们是一个整体，要么都成功执行，要么都不要执行。否则事务进行到一半出现问题，就可能会出现你的账户扣了钱，但是对方账户没有收到钱的问题
• 一致性：事务必须使整个系统状态保持一致，比如你和对方来回转账，不管转了多少次，你们两个账户总金额总是一样的，不会出现你转出1000，对方只收到500的情况
• 隔离性：多个事务同时执行时，必须等前一个事务执行完，才能执行后一个事务，否则会造成数据紊乱。(同时执行的事务不能相互冒)比如你账户有1000元，你给A转账500元。事务进行到一半，也就是把500元从你的账户扣除，但是此时该事务还没有提交，B又向你转账1000元。此时第二个事务查询你的账户仍是1000元，并以这个数据进行后续操作。结果就是第一个事务执行完，你的账户变为500元，然后第二个事务执行完，你的账户变为2000元。银行亏大了。所以正确的做法是第二个事务等待第一个事务执行完，再读取你的账户数据就是正确的500元了。当然数据库中不可能所有事务都是这么串联的，否则效率太低，会通过不同级别的锁来控制事务的隔离性。
• 持久性：事务运行的结果对系统状态的影响必然是持久的，否则服务器一重启，数据全恢复前一天的记录，银行就甭干了

10. 脏读与脏写

设想一个事务已经将一些数据写入数据库，但还没有提交。另一个事务可以看到未提交的数据吗？如果是的话，这就叫做脏读。

如果两个事务尝试更新数据库中的相同数据，会发生什么情况？通常后面的写入会覆盖前面的写入。但是，如果先前的写入尚未提交，后面的写入又覆盖了这个尚未提交的值，会怎么样呢？这就叫做脏写

11. 写入偏差与幻读

现在想象一下，小明和小红是两位值班员工。两人都感到不适，所以他们都决定请假，他们恰好在同一时间点击按钮下班。

这种异常它既不是脏写，也不是丢失更新，而是写入偏差。如果这两个事务一个接一个地运行，那么第二个员工就不能休班了，这种异常行为只有在事务并发进行时才有可能

11.1. for update

select * from table where xxx for update

for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新，防止高并发时候数据出错。

12. 事务隔离等级

12.1. 读已提

• 从数据库读取时，只能看到已提交的数据（不脏读）。
• 写入数据库时，只会覆盖已经提交的写入数据（不脏写）

12.1.1. 读偏差

小明有两个账户，账户A和账户B，每个存有500元，一共1000元。现在发起一笔转账，从账户A转100元到账户B。发起转账交易后，小明马上查询两个账户的余额。不幸的是，对账户B的查询发生在转账事务提交前（显示500元），对账户A的查询发生在转账事务提交后（显示400元）—— 似乎有100元不翼而飞！

12.2. 快照隔离

快照隔离是一种比读已提交更强的隔离级别，它对长时间运行的只读查询（如备份和分析）非常有用。做法是，每个事务都从数据库的一致快照中读取——也就是说，事务始终可以看到事务开始时数据库中的所有数据。即使这些数据随后被其他事务更改，这个事务也只能看到该特定时间点的旧数据。

区别在于

• 读已提交为每个查询保留单独的快照
• 快照隔离为每个事务保留相同的快照。

在快照隔离级别下，如果我们发起多个长时间的查询请求，数据库系统往往需要为每个被查询的行保留N多个不同的版本，用一个唯一的、自增的事务ID来标识。因为正在进行的多个事务需要看到数据库在不同时刻的状态。这允许了数据库在处理长时间查询时，可以同时处理写入操作，且写入操作不会影响查询结果的正确性，且快照隔离也不会产生什么太大的额外开销

13. Mysql索引

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录

索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件

• 对于非常小的表，大部分情况下简单的全表扫描更高效
• MySQL里同一个数据表里的索引总数限制为16个

13.1. 类型

• InnoDB使用的是B+Tree

CREATE INDEX indexName ON table_name (column_name)

ALTER table tableName ADD INDEX indexName(columnName)

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

DROP INDEX [indexName] ON mytable; 

• 唯一索引
  • 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
  • CREATE UNIQUE INDEX indexName ON mytable(username(length))
• 显示索引
  • mysql> SHOW INDEX FROM table_name; \G

14. Mysql8 和mysql 5.7 的区别

从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，这个编码包含了所有 emoji 字符。多少年来我们使用 MySQL 都要在编码方面小心翼翼，生怕忘了将缺省的 latin 改掉而出现乱码问题

---

设置持久化

• MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取
• MySQL 8 新增了 SET PERSIST 命令，例如：SET PERSIST max_connections = 500;。MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

15. Mysql日志

15.1. 错误日志

• log_error = on
• log_warnings = 1|0
• tail -20 stu18.magedu.com.err

15.2. 查询日志

15.3. 查询日志（general log）

查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致IO非常大，影响MySQL性能，因此如果不是在调试环境下，是不建议开启查询日志功能的

判断MySQL数据库是否开启了查询日志

• show variables like '%general_log%';

看查询日志信息

• select * from mysql.general_log;

15.4. 慢查询日志(slow_query_log)

慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。 开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能

只要操作超过了指定时间，都可以称为”慢查询”，被记录到慢查询日志中

打开慢查询

• 使用 set global slow_query_log='ON' 开启慢查询日志，只是对当前数据库有效，如果MySQL数据库重启后就会失效。所以如果要永久生效，就要修改配置文件 my.cnf

慢查询指定时间设置

• set global long_query_time=0.05;

• Mysql8 和mysql 5.7 的区别_a…Z-CSDN博客
• [玩转MySQL之八]MySQL日志分类及简介 - 爱折腾的邦邦的文章 - 知乎
• 通俗易懂 事务、ACID、脏读、脏写、幻读、读已提交、快照隔离、读写锁、两阶段锁定 的区别与联系 - Pickle Pee的文章 - 知乎