MySQL 基础 - CRUD

本章不是简单的 CRUD，是带有一些进阶的 CRUD 操作

选用 InnoDB 引擎

最大特点是支持事物机制。适合读多写多的场景中使用。

• TokuDB：写多读少

  只支持 Linux 平台。当 InnoDB 单表数据超过 2000 万，它的读写性能严重下降。我们要定期的将它归档，由于归档表存储的数据非常大，归档时需要瞬时写入很多数据。

  事物和插入性能是 InnoDB 的很多倍。

导入 SQL 文件

demo-练习库.sql，由于练习库的文件不是很多，这里直接贴出来

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50721
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50721
 File Encoding         : 65001

 Date: 08/10/2018 12:37:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_bonus
-- ----------------------------
DROP TABLE IF EXISTS `t_bonus`;
CREATE TABLE `t_bonus` (
  `empno` int(4) NOT NULL,
  `job` varchar(20) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `comm` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_dept
-- ----------------------------
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
  `deptno` int(2) NOT NULL,
  `dname` varchar(20) DEFAULT NULL,
  `loc` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_dept
-- ----------------------------
BEGIN;
INSERT INTO `t_dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `t_dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `t_dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `t_dept` VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

-- ----------------------------
-- Table structure for t_emp
-- ----------------------------
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
  `empno` int(4) NOT NULL,
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `comm` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_emp
-- ----------------------------
BEGIN;
INSERT INTO `t_emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30);
INSERT INTO `t_emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30);
INSERT INTO `t_emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30);
INSERT INTO `t_emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30);
INSERT INTO `t_emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10);
INSERT INTO `t_emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10);
INSERT INTO `t_emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30);
INSERT INTO `t_emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30);
INSERT INTO `t_emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10);
COMMIT;

-- ----------------------------
-- Table structure for t_salgrade
-- ----------------------------
DROP TABLE IF EXISTS `t_salgrade`;
CREATE TABLE `t_salgrade` (
  `grade` int(11) NOT NULL,
  `losal` decimal(10,2) DEFAULT NULL,
  `hisal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_salgrade
-- ----------------------------
BEGIN;
INSERT INTO `t_salgrade` VALUES (1, 700.00, 1200.00);
INSERT INTO `t_salgrade` VALUES (2, 1201.00, 1400.00);
INSERT INTO `t_salgrade` VALUES (3, 1401.00, 2000.00);
INSERT INTO `t_salgrade` VALUES (4, 2001.00, 3000.00);
INSERT INTO `t_salgrade` VALUES (5, 3001.00, 9999.00);
COMMIT;

-- ----------------------------
-- Table structure for t_emp_ip
-- ----------------------------
DROP TABLE IF EXISTS `t_emp_ip`;
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;

SET FOREIGN_KEY_CHECKS = 1;

• t_dept：部门表
• t_emp：员工表
• t_emp_ip：给员工分配的 IP 地址
• t_salgrade：工资等级表
• t_bonus：奖金表

以上表模仿的是 Oracle 数据库练习表的部分结构。t 开头表示是表，v 开头表示是视图

数据插入

忽略已存在数据

insert into t_dept(deptno, dname, loc)
values (40, '企划部', '北京'),
       (50, '培训部', '上海'),
       (60, '后勤部', '北京'),
       (70, '技术部', '北京'),
       (80, '市场部', '北京');

40 这条数据已经在数据库中存在了，违反了主键约束，所以这条数据一条数据都插入不进去。

可以使用 ignore 关键字，来 忽略 报错的那一条数据，其他的数据继续插入。

insert ignore into t_dept(deptno, dname, loc)
values (40, '企划部', '北京'),
       (50, '培训部', '上海'),
       (60, '后勤部', '北京'),
       (70, '技术部', '北京'),
       (80, '市场部', '北京');

实现不存在就插入，存在就更新

使用 on duplicate key update 子句

insert into t_emp_ip(id, empno, ip)
values (5, 8004, '192.168.99.44'),
       (6, 8005, '192.168.99.45'),
       (7, 8006, '192.168.99.46'),
       (8, 8001, '192.168.99.47')
on duplicate key update ip=values(ip);

查询

要不要使用子查询？

MySQL 数据库默认关闭了缓存，所以每个子查询都是相关子查询。

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

由于没有缓存，当表数据多，子查询就会很慢。

select empno, ename
from t_emp
where sal > (select sal from t_emp where empno = 7499)
  and empno != 7499;

这里的子查询就是一个相关子查询，where 语句每过滤一条数据，子查询就会执行一次。

笔者疑问

笔者这里通过查询计划，并没有看到会执行很多次。难道是版本不同的问题？

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t_emp	NULL	range	PRIMARY	PRIMARY	4	NULL	13	33.33	Using where
2	SUBQUERY	t_emp	NULL	const	PRIMARY	PRIMARY	4	const	1	100	NULL

对于以下结论，笔者持怀疑态度。包括对持久层框架中可以使用也是怀疑。

这里先不管上面的疑问。视频中说到，要解决这个相关子查询：因为 MyBatis 等持久层框架开启了缓存功能，其中以及缓存就会保存子查询的结果，所以可以放心使用子查询。

结论：SQL 控制台中不使用子查询，持久层框架中可以使用，需要配置 1 对 1 或则 1 对多的关系。

如何替代子查询？

使用 from 子句，替代 where 子查询

explain
select empno, ename
from t_emp e
         join (select sal from t_emp where empno = 7499) t
              on e.sal > t.sal
                  and empno != 7499;

执行计划刚好和笔者疑问的计划相反

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_emp	NULL	const	PRIMARY	PRIMARY	4	const	1	100	NULL
1	SIMPLE	e	NULL	range	PRIMARY	PRIMARY	4	NULL	13	33.33	Using where

外连接的 JOIN 条件

内连接是等值连接，符合条件的就会被查询出来。在内连接里，查询条件写在 ON 子句 或则 WHEWE 子句， 效果相同。

--- 内连接：查询有部门的员工名称和部门名称
-- 条件写在 ON 中
select e.ename, d.dname
from t_emp e
         join t_dept d on e.deptno = d.deptno and d.deptno = 10;

-- 条件写在 where 中
select e.ename, d.dname
from t_emp e
         join t_dept d on e.deptno = d.deptno
where d.deptno = 10;

对于外连接，on 和 where 子句中的条件，效果不同。如下

-- 左外连接，在 on 中的条件 不强求必须满足 才能查询出
-- 如果不满足，就用 null 连接
select e.ename, d.dname
from t_emp e
         left join t_dept d on e.deptno = d.deptno and d.deptno = 10;

-- 而在 where 中的条件，则是 必须满足 才能查询出
select e.ename, d.dname
from t_emp e
         left join t_dept d on e.deptno = d.deptno
where d.deptno = 10;

表连接修改

update 语句中的 where 子查询如何改成表连接？

比如下面的语句：将符合条件的员工薪资修改为 10000。使用了子查询。

update t_emp
set sal = 10000
where deptno = (
    select deptno
    from t_dept
    where dname = 'SALES'
)

改写成表连接方式过滤数据

-- 由于使用的是内连接：on 里面的条件写在 where 也是一样的，所以也可以改写到 where 中
update t_emp e join t_dept td on e.deptno = td.deptno
    and td.dname = 'SALES'
set e.sal    = 10000,
    td.dname = '销售部'
-- 上面的 update 语句，一次只能修改一张表的数据，使用这种方式，可以修改多张表的表字段数据

表连接删除

delete 语句中也可以使用表连接

-- 同样，可以删除多张表中的数据， delete 后就是写的要删除表的别名
-- 本列中，删除了上面被更新的两张表中的数据
delete e,td
from t_emp e
         join t_dept td on e.deptno = td.deptno and td.dname = '销售部'
-- 要删除的数据用的内连接查询。部门编号一致，并且部门名称是销售部