你可能不知道的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 executionReason: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 executionReason: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, enameFROM  t_empWHERE  sal > (    -- WHERE语句每过滤一条数据，子查询就会执行一次    SELECT sal FROM t_emp WHERE empno = 7499  )  AND empno != 7499;

 

代替子查询

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

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

 

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

 

表连接

内连接

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

 

  •  
SELECT e.ename, e.dname FROM t_emp eJOIN t_dept d ON e.deptno = d.deptno AND d.deptno = 10;
SELECT e.ename, d.name FROM t_emp eJOIN t_dept d ON e.deptno = d.deptnoWHERE d.deptno = 10;

 

外连接

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

 

  •  
-- 保留左表所有数据和右表关联SELECT e.ename, e.ename FROM t_emp e-- ON 条件如果符合则返回，否则返回nullLEFT 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 eLEFT JOIN t_dept d ON e.deptno = d.deptnoWHERE 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 = 10000WHERE 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语句组成的整体，要么全部执行成功，要么全部执行失败。

你可能不知道的CRUD_IT实战联盟

ACID

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