你可能不知道的CRUD

本系列旨在系统学习提升Mysql技能，更完整内容可以参考阿里新零售数据库设计与实战

DB引擎

你可能不知道的CRUD

INSERT

情况一 Duplicate key

当批量更新，如果有重复的primary key,如果有一条失败，则全部失败。

更新时忽略错误行，继续执行该如何处理？

如： t_dept 表中已经存在 deptno = 40 的数据；

步骤一直接插入

INSERT INTO t_dept (deptno, dname, loc) VALUES 
(40, '企划部', '北京'),
(50, '培训部', '上海'),

错误信息

Error occurred during SQL query execution
Reason:
SQL Error [1062] [23000]: Duplicate entry '40' for key 't_dept.PRIMARY'

步骤二关键字–IGNORE

INSERT IGNORE  INTO t_dept (deptno, dname, loc) VALUES 
(40, '企划部', '北京'),
(50, '培训部', '上海')

情况二 upsert操作

目标：存在则更新，不存在则创建。

CREATE TABLE `t_emp_ip`  (
  `id` int(11) NOT NULL,
  `empno` int(11) NOT NULL,
  `ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `empno`(`empno`) USING BTREE,
  UNIQUE INDEX `ip`(`ip`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 原有数据
-- empno字段为： 唯一性约束
id|empno|ip         |
--|-----|-----------|
 1|  100|192.168.0.1|
 2|  101|192.168.0.2|

目标

插入102、103两条数据
更新101对应的IP

步骤一：错误示范

INSERT INTO t_emp_ip(id, empno, ip) VALUES
(4, 102, '192.168.0.100'),
(5, 103, '192.168.0.150'),
(6, 101, '192.168.0.200')

Error occurred during SQL query execution
Reason:
SQL Error [1062] [23000]: Duplicate entry '101' for key 't_emp_ip.empno'

步骤二正确操作

INSERT INTO t_emp_ip(id, empno, ip) VALUES
(4, 102, '192.168.0.100'),
(5, 103, '192.168.0.150'),
(6, 101, '192.168.0.200')
-- 子句更新｜｜插入
ON duplicate UPDATE ip=VALUES(ip);

结果

id|empno|ip           |
--|-----|-------------|
 1|  100|192.168.0.1  |
 2|  101|192.168.0.200| -- update
 4|  102|192.168.0.100| -- insert
 5|  103|192.168.0.150| -- insert

步骤三测试其他表

如果需要更新多个字段，则字句中需要罗列完整的字段。

INSERT INTO t_emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES
(8000, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, 0.9 ,10);

--

INSERT INTO t_emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES
(8000, 'MILLER', 'CLERK', 7782, '1982-01-23', 300.00, 100 ,0)
ON duplicate KEY UPDATE comm=VALUES(comm);

子查询

相关子查询就是要循环执行多次的子查询。

子查询：独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询；
相关子查询：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次；

注：

mysql默认关闭了缓存，所以每个子查询都是相关子查询。
一般ORM开启了缓存。

SELECT empno, ename
FROM
  t_emp
WHERE
  sal > (
    -- WHERE语句每过滤一条数据，子查询就会执行一次
    SELECT sal FROM t_emp WHERE empno = 7499
  )
  AND empno != 7499;

代替子查询

使用FROM子查询，代替WHERE子查询

FROM子查询只会执行一次，优先执行数据来源，所以不是相关子查询

-- 相关子查询代替方案
explain SELECT empno, ename
FROM t_emp e 
    JOIN (SELECT sal FROM t_emp WHERE empno = 7499) t
ON e.sal > t.sal AND e.empno != 7499;

表连接

内连接

内连接里，查询条件写在ON子句或where子句，效果相同

SELECT e.ename, e.dname 
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno AND d.deptno = 10;

SELECT e.ename, d.name 
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

外连接

内连接里，查询条件写在ON子句或where子句，效果相差很大.

-- 保留左表所有数据和右表关联
SELECT e.ename, e.ename 
FROM t_emp e
-- ON 条件如果符合则返回，否则返回null
LEFT JOIN t_dept d ON e.deptno = d.deptno
-- 不强求连接条件，会返回部门ID不等10的部门
AND d.deptno = 10;

返回结果

ename |ename |deptno|
------|------|------|
SMITH |SMITH |    20|
ALLEN |ALLEN |    30|
WARD  |WARD  |    30|
JONES |JONES |    20|
MARTIN|MARTIN|    30|
BLAKE |BLAKE |    30|
CLARK |CLARK |    10|
...

-- 左表数据必须满足 where 条件才会返回。
SELECT e.ename, d.name 
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

返回结果

ename |ename |deptno|
------|------|------|
CLARK |CLARK |    10|
KING  |KING  |    10|
MILLER|MILLER|    10|
MILLER|MILLER|    10|

小结

where子句：需要满足条件才可以返回；
on子句：完全以左表为主，

UPDATE

表连接修改

UPDATE t_emp SET sal = 10000
WHERE deptno = (SELECT deptno FROM t_dept WHERE dname = 'SALES');

UPDATE t_emp e 
-- 内连接，条件写在on || where 效果一样。
JOIN t_dept d ON e.deptno = d.deptno AND d.dname = 'SALES'
-- 更改多个表的字段
SET e.sal = 10000, d.dname = '销售部';

表连接删除

-- 删除t_emp、t_dept表符合条件的数据
-- 写谁删谁，不写不删除
DELETE e, d FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno AND d.dname = '销售部';

事务机制

为啥数据库需要有事务机制？

类比，操作重要代码，可能先要新建一个分之，避免混乱。

同理：如果数据的写入直接操作数据文件是非常危险的事情

如：给员工工资普普涨，但中途失败了，怎么确认那些成功，哪些失败。

就需要引入事务机制。

日志文件相当于数据副本

undo & redo

数据库日志分类

重做日志（redo log）
回滚日志（undo log）
二进制日志（binlog）
错误日志（errorlog）
慢查询日志（slow query log）
一般查询日志（general log）
中继日志（relay log）

SQL操作的记录会被复制undo日志中；
CRUD的结果会记录在redo日志中；
如果CRUD没问题，将redo日志中的数据同步到SQL文件中;
如果同步过程中出现问题，则之后再次同步即可。

undo & redo 对于事务机制的重要性不言而喻。

事务机制

事务是一个或多个SQL语句组成的整体，要么全部执行成功，要么全部执行失败。

ACID

原子性：要么成功要么失败；
一致性：不论并发多少，必须保证结果一致性；事务隔离，不能读写其他事务的临时日志；
隔离性：事务相互之间隔离；
持久性：一旦提交，结果便是永久性的，宕机可以恢复事务日志完成数据的持久化。

参考

阿里新零售数据库设计与实战
 Mysql-innoDB存储引擎(事物，锁，MVCC)
浅谈 MySQL 子查询及其优化
 Explain语法
 步步深入：MySQL架构总览->查询执行流程->SQL解析顺序
 MySQL到底有多少种日志类型需要我们记住的！

注意：本文归作者所有，未经作者允许，不得转载

你可能不知道的CRUD

DB引擎

你可能不知道的CRUD

INSERT

情况一 Duplicate key

步骤一 直接插入

步骤二 关键字–IGNORE

情况二 upsert操作

步骤一： 错误示范

步骤二 正确操作

步骤三 测试其他表

子查询

代替子查询

表连接

内连接

外连接

UPDATE

表连接修改

表连接删除

事务机制

undo & redo

数据库日志分类

事务机制

ACID

参考

全部评论: 0 条

加入圈子

热门文章

步骤一直接插入

步骤二关键字–IGNORE

步骤一：错误示范

步骤二正确操作

步骤三测试其他表