你可能不知道的CRUD

来都来了 2021-04-20 16:19:58 ⋅ 15 阅读

 

本系列旨在系统学习提升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|
 

目标

  1. 插入102、103两条数据
  2. 更新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);
 

子查询

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

  1. 子查询:独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询;
  2. 相关子查询:相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次;

注:

  1. mysql默认关闭了缓存,所以每个子查询都是相关子查询
  2. 一般ORM开启了缓存。
SELECT empno, ename
FROM
t_emp
WHERE
sal > (
-- WHERE语句每过滤一条数据,子查询就会执行一次
SELECT sal FROM t_emp WHERE empno = 7499
)
AND empno != 7499;
 

代替子查询

使用FROM子查询,代替WHERE子查询
  1. 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|
 

小结

  1. where子句:需要满足条件才可以返回;
  2. 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

数据库日志分类

  1. 重做日志(redo log)
  2. 回滚日志(undo log)
  3. 二进制日志(binlog)
  4. 错误日志(errorlog)
  5. 慢查询日志(slow query log)
  6. 一般查询日志(general log)
  7. 中继日志(relay log)
  1. SQL操作的记录会被复制undo日志中;
  2. CRUD的结果会记录在redo日志中;
  3. 如果CRUD没问题,将redo日志中的数据同步到SQL文件中;
  4. 如果同步过程中出现问题,则之后再次同步即可。

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

事务机制

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

ACID

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

参考

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


全部评论: 0

    我有话说:

    大多数人都知道 Maven 版本号

    前言 大多数程序员或多或少会有这样苦恼经历 开发了一个公共组件 maven 版本号为: 1.0.0 然后很多项目都用了这个项目版本号 过了一段时间发现有bug,或者需要在组件中增加些代码,版本号

    面试官:HashMap为什么是线程安全

    一直以来只是知道HashMap是线程安全,但是到底HashMap为什么线程安全?

    Google技术:了解Google最新发布JS代码规范 最佳实践

    Google为了那些还熟悉代码规范人发布了一个JS代码规范。其中列出了编写简洁易懂代码所应该做最佳实践。

    小程序开源框架汇总,知道几个?

    想要开发出一套高质量小程序,运用框架?看过来

    需要 jQuery,但需要一个 DOM 库

    jQuery 已经逐渐退出历史舞台,但是它 API 将会以另外一种形式存在下去。

    为什么要使用 Node.js?这几点必须知道

    经过这几年发展,前端普遍进入了技术深水区,只会Web页面开发已经难以满足企业需求,Node逐渐成为了刚性技能。 但Node在业务上使用还没有那么普及,有的时候想用老板还同意,本文将从4个角度

    「转载」47 张图带 MySQL 进阶!!!

    我们在  138 张图带 MySQL 入门 中主要介绍了基本 SQL 命令、数据类型和函数,在具备以上知识后,可以进行 MySQL 开发工作了,但是如果要成为一个合格开发人员

    架构实战篇(十三):Spring Boot Logback 邮件通知

    日志对于应用程序来说是非常重要,当程序报错了,而知道是多么可怕一件事情,本文使用logback把程序报错信息邮件到开发者

    老板逼上微服务了吗?

    “ 这些年软件设计规模越来越庞大,业务需求也越来越复杂,针对系统性能、高吞吐率、高稳定性、高扩展等特性提出了更高要求。   图片来自 Pexels可以说业务需求是软件架构能力

    mongoHelper 0.3.9 发布,spring-data-mongodb 增强工具包,简化 CRUD 操作

    mongoHelper 是基于 spring-data-mongodb 增强工具包,简化 CRUD 操作,提供类 jpa 数据库操作。 传统关系型数据库及围绕它们构建 orm 在项目开发中有很

    架构实战篇(二)-Spring Boot整合Swagger,让API可视化

    还在跟前端对接上花费很多时间而没有效果吗?还在为写接口文档而烦恼吗?今天就教大家一个接口对接神器...

    「轻阅读」“来我公司做技术总监吧” “要写代码吗?” “写代码来干嘛?”

    标题来源于一段真实对话,老赵,小李都是我朋友,我作为中间人介绍他们认识,我们约在上海码农圣地--张江某咖啡馆。

    Cookie 和Session难,一个是Mapkey,一个是Mapvalue

    本文分别对Cookie与Session做一个介绍和总结,并分别对两个知识点进行对比分析,让大家对Cookie和Session有一个更深入了解,并对自己开发工作中灵活运用带来启示。

    想更好理解Node.js中Buffer吗?看一下这个。

    不论是否是科班出身,认真读完,想必会给带去一些收获.

    「轻阅读」Mysql调优不得不知细节

    多数时候数据库会成为整个系统瓶颈

    「轻阅读」图文并茂带了解分布式架构演进

    初始阶段架构初始阶段 小型系统 应用程序、数据库、文件等所有资源都在一台服务器上通俗称LAMP

    京东技术:Sieve—Android 内存分析系统 | 解决内存溢出问题

    内存问题是个老大难,对用户来说,泄漏或者合理内存使用最终会反映到性能和体验上,并且极易造成 OOM( Out Of Memories ) 而闪退, 而对开发者来说更为头疼......

    爆笑图解:我们是程序员

    程序员世界,