文章目录
  1. 1.概述
  2. 2.数据定义语言(DDL)
    1. 2.1 Database
    2. 2.2 Table
    3. 2.3 View
    4. 2.4 Function
    5. 2.5 Procedure
    6. 2.6 Trigger
  3. 3.数据操纵语言(DML)
    1. 3.1 SELECT 语句（难点）
      1. 3.1.1 基本
      2. 3.1.2 统计
      3. 3.1.3 子表
      4. 3.1.4 JOIN（难点中的难点）
        1. 3.1.4.1 常规关联
        2. 3.1.4.2 左连接
        3. 3.1.4.3 右连接
      5. 3.1.5 UNION [ALL]
      6. 3.1.6 索引
    2. 3.2 INSERT
    3. 3.3 DELETE
    4. 3.4 UPDATE
  4. 4.存储过程
    1. 4.1 使用游标
    2. 4.2 调用方法
    3. 4.3 优化原则
  5. 5.综合实战（重复记录去重）
    1. 5.1 重复的组合，生成统计表
    2. 5.2 多余的记录，生成备份表
    3. 5.3 删除原表的多余记录
    4. 5.4 注意事项

本文从开发层面全面梳理 MySQL 各个开发要点，以帮助大家准确理解、熟悉掌握相关技术。简要讲解 Table/View等各种数据库对象，并重点讲解 SELECT/INSERT/DELETE/UPDATE 等使用频率最高的语句，最后讲解了存储过程和记录去重的综合实战。

作者：王克锋
出处：https://kefeng.wang/2016/12/18/mysql-development/
版权：自由转载-非商用-非衍生-保持署名，转载请标明作者和出处。

1.概述

　　本文的目的在于，和大家一起梳理 MySQL 最常用的开发要点，以查漏补缺并准确理解，能信手拈来灵活运用。起因是笔者在面试应聘者、与周边同事沟通时，发现一些开发者对 MySQL 的某些常用语法似懂非懂，实际操作时无从下手。
　　笔者以为，最基础最常用的，才是最需要熟练掌握的，然后再去学更高级的内容。

2.数据定义语言(DDL)

　　下面列出的 View/Function/Procedure/Trigger, 这些机制给我们开发者带来很大的方便，但它们对数据库性能有影响，而数据库性能经常成为系统中的瓶颈。所以，如果对性能要求高，那么数据库中应该只保存数据，逻辑处理尽量移到应用程序中进行。

2.1 Database

1
2
3
4
5
SHOW DATABASES; ## 当前主机数据库列表
DROP DATABASE IF EXISTS db; ## 删除数据库[如果存在]
CREATE DATABASE IF NOT EXISTS db
    CHARACTER SET UTF8; ## 创建数据库[如果不存在]
USE db; ## 切换db为默认数据库

2.2 Table

1
2
3
4
5
6
7
8
9
10
11
12
13
SHOW TABLES; ## 当前数据库中数据表

TRUNCATE TABLE db.users; ## 彻底清空数据表（清除碎片）
DROP TABLE IF EXISTS db.users; ## 删除数据表[如果存在]
CREATE TABLE IF NOT EXISTS db.users( ## 新建数据表[如果不存在]
    id INT AUTO_INCREMENT COMMENT '用户ID',
    name VARCHAR(32) NOT NULL COMMENT '用户名',
    city VARCHAR(16) NOT NULL COMMENT '城市',
    birthday DATE DEFAULT '2000-01-01' COMMENT '生日',
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY(id), UNIQUE uni_users_name(name), INDEX idx_users_city(city), INDEX idx_users_birthday(birthday)
) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '用户表';
1
2
3
4
5
INSERT INTO db.users(name, city, birthday) VALUES ## 没指定的列，取值为默认值
    ('张三', '北京', '2000-01-01'), ## id=1
    ('李四', '上海', '2003-01-01'), ## id=2
    ('王五', '广州', '2006-01-01'); ## id=3
SELECT * FROM db.users ORDER BY id;
id name city birthday created updated
1 张三 北京 2000-01-01 2016-08-21 20:08:15 2016-08-21 20:08:15
2 李四 上海 2003-01-01 2016-08-21 20:08:15 2016-08-21 20:08:15
3 王五 广州 2006-01-01 2016-08-21 20:08:15 2016-08-21 20:08:15 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE IF NOT EXISTS db.orders(
    id INT AUTO_INCREMENT COMMENT '订单ID',
    uid INT NOT NULL COMMENT '用户ID',
    amount DECIMAL(10,2) NOT NULL COMMENT '金额',
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY(id), INDEX idx_orders_uid(uid), INDEX idx_orders_amount(amount)
) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '订单表';
INSERT INTO db.orders(uid, amount) VALUES
    (1, 100),
    (1, 200),
    (2, 400),
    (9, 500);
SELECT * FROM db.orders ORDER BY id;
id uid amount created updated
1 1 100.00 2016-08-21 20:10:28 2016-08-21 20:10:28
2 1 200.00 2016-08-21 20:10:28 2016-08-21 20:10:28
3 2 400.00 2016-08-21 20:10:28 2016-08-21 20:10:28
4 9 500.00 2016-08-21 20:10:28 2016-08-21 20:10:28 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE db.users_bak1 LIKE db.users;
CREATE TABLE db.users_bak2 SELECT * FROM db.users WHERE city IN ('北京','上海') ORDER BY id;

DESC db.users; ## 查看表结构
SHOW CREATE TABLE db.users; ## 查看建表语句

## 临时表（建在内存中，当前会话可见）
DROP TEMPORARY TABLE IF EXISTS db.tmp_users;
CREATE TEMPORARY TABLE IF EXISTS db.tmp_users ...

## 修改表定义
ALTER TABLE db.users
    ADD temp VARCHAR(32) COMMENT '临时列',
    CHANGE temp temp VARCHAR(64) COMMENT '临时列2',
    ADD INDEX idx_users_temp(temp),
    DROP temp;
1
2
3
4
5
6
7
8
9
10
11
12
## 分区表
CREATE TABLE IF NOT EXISTS db.users(...)
ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '用户表'
PARTITION BY RANGE COLUMNS(birthday) (
    PARTITION Y1960 VALUES LESS THAN ('1960-01-01'),
    PARTITION Y1970 VALUES LESS THAN ('1970-01-01'),
    PARTITION Y1980 VALUES LESS THAN ('1980-01-01'),
    PARTITION Y1990 VALUES LESS THAN ('1990-01-01'),
    PARTITION Y2000 VALUES LESS THAN ('2000-01-01'),
    PARTITION Y2010 VALUES LESS THAN ('2010-01-01'),
    PARTITION YMAXV VALUES LESS THAN MAXVALUE
);

2.3 View

1
2
3
CREATE OR REPLACE VIEW db.view_users AS
    SELECT id,name,city FROM db.users WHERE city IN ('北京','上海') ORDER BY id DESC;
SELECT * FROM db.view_users;
id name city
2 李四 上海
1 张三 北京

2.4 Function

1
2
3
4
5
6
7
8
9
10
11
12
DROP FUNCTION IF EXISTS db.func_users;

DELIMITER $$ ## 设置定界符
CREATE FUNCTION db.func_users(v_id INT) RETURNS VARCHAR(32)
BEGIN
    DECLARE v_name VARCHAR(32) DEFAULT NULL;
    SELECT name INTO v_name FROM users WHERE id=v_id;
    RETURN v_name;
END$$
DELIMITER ; ## 恢复定界符

SELECT db.func_users(1); ## 张三

2.5 Procedure

1
2
3
4
5
6
7
8
9
10
11
12
DROP PROCEDURE IF EXISTS db.proc_users;

DELIMITER $$ ## 设置定界符
CREATE PROCEDURE db.proc_users(IN v_id INT, OUT v_name VARCHAR(32))
BEGIN
    SELECT name INTO v_name FROM users WHERE id=v_id;
END$$
DELIMITER ; ## 恢复定界符

SET @name=NULL;
CALL db.proc_users(1, @name);
SELECT @name; ## 张三

2.6 Trigger

1
2
3
4
5
6
7
8
9
10
11
DROP TRIGGER IF EXISTS db.trig_users;

DELIMITER $$ ## 设置定界符
CREATE TRIGGER db.trig_users AFTER UPDATE ON db.users FOR EACH ROW
BEGIN
    INSERT INTO db.users_bak1(id, NAME, city, birthday, created, updated)
        VALUES(OLD.id, OLD.name, OLD.city, OLD.birthday, OLD.created, OLD.updated);
    INSERT INTO db.users_bak2(id, NAME, city, birthday, created, updated)
        VALUES(NEW.id, NEW.name, NEW.city, NEW.birthday, NEW.created, NEW.updated);
END$$
DELIMITER ; ## 恢复定界符

3.数据操纵语言(DML)

3.1 SELECT 语句（难点）

3.1.1 基本

1
2
3
4
5
6
SELECT id, name, city, YEAR(CURDATE())-YEAR(birthday) AS age
    FROM db.users
    WHERE (name LIKE '张%' AND city IN ('北京', '上海'))
        OR birthday BETWEEN '1990-01-01' AND '2009-12-31'
    ORDER BY id DESC
    LIMIT 0,3; ## [m,]n

3.1.2 统计

1
2
3
4
5
6
SELECT MIN(birthday), MAX(birthday), COUNT(*) ## AVG(), SUM()
    FROM db.users
    WHERE city IN ('北京', '上海') ## 分组前筛选
    GROUP BY city ## 分组依据
    HAVING COUNT(*)>0 ## 分组后筛选
    ORDER BY id DESC LIMIT 0,3;

3.1.3 子表

1
2
3
4
SELECT t.*, (SELECT birthday FROM db.users WHERE NAME='李四') AS birthday4
    FROM (SELECT id, NAME, city, birthday FROM db.users WHERE id>1) AS t
    WHERE t.birthday>(SELECT birthday FROM db.users WHERE NAME='张三')
        AND EXISTS(SELECT 1 FROM db.orders AS o WHERE o.uid=t.id);

3.1.4 JOIN（难点中的难点）

3.1.4.1 常规关联

左右表严格关联上的记录才返回。
特别注意：无关联条件时，结果为m*n条记录，切记不要漏写关联条件。

1
2
SELECT u.name, o.id AS oid, o.amount
    FROM db.users AS u, db.orders AS o WHERE u.id=o.uid;

oid amount name city birthday
1 100.00 张三 北京 2000-01-01
2 200.00 张三 北京 2000-01-01
3 400.00 李四 上海 2003-01-01
3.1.4.2 左连接

除了严格关联上的记录，“左表”存在而右表不存在的记录也返回。

1
2
3
SELECT o.id AS oid, o.amount, u.name, u.city, u.birthday
    FROM db.users AS u LEFT JOIN db.orders AS o ON u.id=o.uid;
    ## WHERE o.id > 2;

oid amount name city birthday
1 100.00 张三 北京 2000-01-01
2 200.00 张三 北京 2000-01-01
3 400.00 李四 上海 2003-01-01
NULL NULL 王五 广州 2006-01-01
3.1.4.3 右连接

除了严格关联上的记录，“右表”存在而左表不存在的记录也返回。

1
2
SELECT o.id AS oid, o.amount, u.name, u.city, u.birthday
    FROM db.users AS u RIGHT JOIN db.orders AS o ON u.id=o.uid;

oid amount name city birthday
1 100.00 张三 北京 2000-01-01
2 200.00 张三 北京 2000-01-01
3 400.00 李四 上海 2003-01-01
4 500.00 NULL NULL NULL

左关联的实际应用：统计每个用户的订单个数和总金额。
如果用普通连接，则无订单用户将不用列出。

1
2
3
SELECT u.id, u.name, COUNT(o.id) AS order_number, IFNULL(SUM(o.amount),0) AS total_amoumt
    FROM db.users AS u LEFT JOIN db.orders AS o ON u.id=o.uid
    GROUP BY u.id;

id name order_number total_amoumt
1 张三 2 300.00
2 李四 1 400.00
3 王五 0 0.00

3.1.5 UNION [ALL]

  • 各个子查询的字段的个数和类型必须一致；
  • UNION 自动去重，UNION ALL 不去重。
    1
    2
    3
    SELECT * FROM db.users WHERE city='北京' UNION
    SELECT * FROM db.users_bak1 WHERE city='上海' UNION
    SELECT * FROM db.users_bak2 WHERE city='广州';

3.1.6 索引

索引缺点: 占空间；INSERT/DELETE/UPDATE 时慢(要更新索引区)。
有用子句: WHERE, ORDER BY, GROUP BY, JOIN
索引禁忌: YEAR(birthday)=2016, INSTR(city,'上')>0, name LIKE '%ABC%'
注意: 每次执行新写的SELECT语句，务必考虑相关数据表的数据量、是否能用到索引，可考查WHERE, ORDER BY, GROUP BY, JOIN等子句，或者使用EXPLAIN查看执行计划。

3.2 INSERT

1
2
3
4
5
6
INSERT INTO users(nick,city) VALUES
    ('张三', '北京'),
    ('李四', '上海'),
    ('王五', '广州'); ## 没指定的列，取值为默认值
INSERT INTO tmp_users(nick,city)
    SELECT nick, city FROM tmp_users;

唯一索引重复时的处理方法

1
2
3
4
5
INSERT INTO users(nick,city) VALUES('张三', '上海'); ## 默认用法，会报错
INSERT IGNORE INTO users(nick,city) VALUES('张三', '上海'); ## 冲突时忽略新记录
INSERT INTO users(nick,city) VALUES('张三', '上海')
    ON DUPLICATE KEY UPDATE city=VALUES(city); ## 覆盖原记录，未涉及列保持原值
REPLACE INTO users(nick,city) VALUES('张三', '上海'); ## 覆盖原记录，未涉及列全部更新为默认值

3.3 DELETE

1
DELETE FROM db.users WHERE id=8;

切忌 WHERE 条件有漏写而多删除数据！首次执行语句前，可改为 SELECT 确认涉及的数据；

3.4 UPDATE

1
UPDATE db.users SET updated=NOW() WHERE id<5;

切忌 WHERE 条件有漏写而多删除数据！首次执行语句前，可改为 SELECT 确认涉及的数据；

4.存储过程

4.1 使用游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE PROCEDURE db.proc_users(IN vi_id INT)
BEGIN
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(32);
    DECLARE v_logs VARCHAR(255) DEFAULT '';

    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE cur_users CURSOR FOR (SELECT id,NAME FROM db.users WHERE id<=vi_id);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_users;
    START TRANSACTION;
    REPEAT
        FETCH cur_users INTO v_id, v_name;
        IF !done THEN
            SET v_logs = CONCAT(v_logs, v_id, ',');
            UPDATE db.users SET updated=NOW() WHERE id=v_id;
        END IF;
    UNTIL done END REPEAT;
    COMMIT; ## ROLLBACK;
    CLOSE cur_users;

    SELECT v_logs; ## 可返回记录集，用于调试或Java程序
END$$

4.2 调用方法

1
CALL db.proc_users(12);

4.3 优化原则

  • 确保使用到索引；
  • 多条记录批量处理；
  • 多条记录批量提交。

5.综合实战（重复记录去重）

假设有张成绩数据表scores，每个学生的每个课程都有且只有一条记录，主要字段有：id(成绩ID) / sid(Student ID，学生ID) / cid(course ID, 课程ID) / score(成绩)。但现在某些学生的某些课程有多条记录，希望去除重复记录。

5.1 重复的组合，生成统计表

挑选有重复的 [sid, cid] 组合，存储于“统计表”， ## HAVING COUNT(1)>1
对于每个组合，记下其中最小的 id, 清除时要保留该id的记录 ## MIN(id)

1
2
3
4
5
INSERT INTO tmp_scores_stat(sid, cid, id_min, id_cnt)
    SELECT sid, cid, MIN(id), COUNT(1)
    FROM scores
    GROUP BY sid, cid
    HAVING COUNT(1)>1;

5.2 多余的记录，生成备份表

如果将要删除的记录，日后需要使用，则需要此步备份，否则此步可省掉。

1
2
3
4
5
6
7
8
9
CREATE TABLE tmp_scores_extra AS
    SELECT s.*
    FROM scores s
    WHERE EXISTS (
        SELECT 1
            FROM tmp_scores_stat t
            WHERE t.sid=s.sid
                AND t.cid=s.cid
                AND t.id_min!=s.id); ## 每个组合中，保留id最小的那条记录

5.3 删除原表的多余记录

1
2
3
4
5
DELETE FROM scores
    WHERE EXISTS (
        SELECT 1
        FROM tmp_scores_extra t
        WHERE t.id=scores.id);

5.4 注意事项

  • 如果是对在线数据库操作，5.1 的大表统计很耗时，容易导致长时间锁表，应该按id段拆分成多批统计，最后再汇总；
  • 由于涉及数据量大，每步务必用到索引，避免全表扫描；
  • 增删改语句执行前，务必核对操作的数据准确无误，可先改为SELECT语句确认记录，至少确认记录条数一致。
文章目录
  1. 1.概述
  2. 2.数据定义语言(DDL)
    1. 2.1 Database
    2. 2.2 Table
    3. 2.3 View
    4. 2.4 Function
    5. 2.5 Procedure
    6. 2.6 Trigger
  3. 3.数据操纵语言(DML)
    1. 3.1 SELECT 语句（难点）
      1. 3.1.1 基本
      2. 3.1.2 统计
      3. 3.1.3 子表
      4. 3.1.4 JOIN（难点中的难点）
        1. 3.1.4.1 常规关联
        2. 3.1.4.2 左连接
        3. 3.1.4.3 右连接
      5. 3.1.5 UNION [ALL]
      6. 3.1.6 索引
    2. 3.2 INSERT
    3. 3.3 DELETE
    4. 3.4 UPDATE
  4. 4.存储过程
    1. 4.1 使用游标
    2. 4.2 调用方法
    3. 4.3 优化原则
  5. 5.综合实战（重复记录去重）
    1. 5.1 重复的组合，生成统计表
    2. 5.2 多余的记录，生成备份表
    3. 5.3 删除原表的多余记录
    4. 5.4 注意事项