MySQL 开发要点

最新推荐文章于 2022-12-13 10:44:37 发布
最新推荐文章于 2022-12-13 10:44:37 发布
阅读量681 收藏
点赞数
分类专栏： Java存储相关中间件 文章标签： mysql 开发
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kefengwang/article/details/54377437
版权

本文从开发层面全面梳理 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

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

2.2 Table

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 '用户表';
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;
idnamecitybirthdaycreatedupdated
1张三北京2000-01-012016-08-21 20:08:152016-08-21 20:08:15
2李四上海2003-01-012016-08-21 20:08:152016-08-21 20:08:15
3王五广州2006-01-012016-08-21 20:08:152016-08-21 20:08:15
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;
iduidamountcreatedupdated
11100.002016-08-21 20:10:282016-08-21 20:10:28
21200.002016-08-21 20:10:282016-08-21 20:10:28
32400.002016-08-21 20:10:282016-08-21 20:10:28
49500.002016-08-21 20:10:282016-08-21 20:10:28
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;
## 分区表
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

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;
idnamecity
2李四上海
1张三北京

2.4 Function

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

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

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 基本
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 统计
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 子表
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条记录，切记不要漏写关联条件。

SELECT u.name, o.id AS oid, o.amount
    FROM db.users AS u, db.orders AS o WHERE u.id=o.uid;
oidamountnamecitybirthday
1100.00张三北京2000-01-01
2200.00张三北京2000-01-01
3400.00李四上海2003-01-01
3.1.4.2 左连接

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

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;
oidamountnamecitybirthday
1100.00张三北京2000-01-01
2200.00张三北京2000-01-01
3400.00李四上海2003-01-01
NULLNULL王五广州2006-01-01
3.1.4.3 右连接

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

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;
oidamountnamecitybirthday
1100.00张三北京2000-01-01
2200.00张三北京2000-01-01
3400.00李四上海2003-01-01
4500.00NULLNULLNULL

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

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;
idnameorder_numbertotal_amoumt
1张三2300.00
2李四1400.00
3王五00.00
3.1.5 UNION [ALL]
  • 各个子查询的字段的个数和类型必须一致；
  • UNION 自动去重，UNION ALL 不去重。
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

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

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

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

DELETE FROM db.users WHERE id=8;

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

3.4 UPDATE

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

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

4.存储过程

4.1 使用游标

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 调用方法

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)

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 多余的记录，生成备份表

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

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 删除原表的多余记录

DELETE FROM scores
    WHERE EXISTS (
        SELECT 1
        FROM tmp_scores_extra t
        WHERE t.id=scores.id);

5.4 注意事项

  • 如果是对在线数据库操作，5.1 的大表统计很耗时，容易导致长时间锁表，应该按id段拆分成多批统计，最后再汇总；
  • 由于涉及数据量大，每步务必用到索引，避免全表扫描；
  • 增删改语句执行前，务必核对操作的数据准确无误，可先改为SELECT语句确认记录，至少确认记录条数一致。
kefeng-wang
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习要点（3/10）
weixin_44666368的博客
09-08 120
MySQL学习要点（3/10）第二节.使用SQL查询（Part 2）6）多表查询1.等值连接2.Join连接3.Union连接4.Union ALL连接5.INTERSECT和EXCEPT连接 第二节.使用SQL查询（Part 2） 6）多表查询 1.等值连接 i).笛卡尔集会在下面条件下产生: – 省略连接条件 – 连接条件无效 – 所有表中的所有行互相连接 ii).为了避免笛卡尔集， 可以在 ...
MySQL重点知识
weixin_50708764的博客
12-13 415
1.– 后面要加个空格，否则错误乱码，颜色要变成绿色才可以 2.命令行要执行的话，选中你需要执行的那行代码，在按F9键，或者蓝色箭头图标，如果是表格，就点击命令行的表数据，在点击右侧绿色刷新按钮 3.调节字体大小，在工具中-首选项-字体编译器设置-SQl编译器-更改即可 4.代码一句写完后加;, 5.转大写按tab键， 6.关键字写错，就不是大写，而且不是蓝色。 7.如果在写代码的时候，你已经写了诸如字段类型等代码，就不能在代码上直接修改，和java不一样，需要把该列删除，在查询表的信息，看错误信息还在不在
MySQL数据库要点汇总
TIMFannie的博客
08-10 458
MySQL数据库要点汇总
MySQL核心要点整理
chen
10-15 1802
一、MySQL概述 MySQL是一个开源的关系型数据库管理系统，现在属于Oracle公司。随着MySQL功能的不断完善，性能不断提高，又有开源免费的优势，越来越多的企业选择使用MySQL，而放弃商用收费的Oracle。 1.Mysql客户/服务器体系结构 1） 一个是MySQL服务器程序，指的是mysqld程序，运行在数据库服务器上，负责在网络上监听并处理来自客户端的服务请求根据这些请求去访...
MySQL知识点总结
09-02 112
目录 什么数据库 数据库分为： 关系型和非关系型： MySQL数据库 什么是SQL语句： SQL语句的分类： 常用的操作： 数据类型： 命名规范： 分类：字符、数字、日期、图片 什么是字段约束： 字段约束分为几类 主键约束： 非空约束： not null 唯一约束 :unique 默认约束：default 检查约束：check 外键约束： 基础函数有 条件查询有 聚合函数： 什么是分组 having:完成分组后过滤 什么是事务： 事务的特性：ACID 隔离
MySQL 开发规范 。。。。。
08-25
MySQL开发规范旨在优化数据库设计，提高查询性能，避免因不当的SQL使用对业务造成严重影响。以下是一些关键的规范要点： 1. **避免大事务**：大事务可能导致长时间锁定资源，影响其他并发操作。应将大事务拆分成小...
58到家MySQL数据库开发规范.docx
09-05
【MySQL数据库开发规范】 在开发基于MySQL的数据库系统时，遵循一套严格的规范至关重要，这有助于确保系统的稳定性、可维护性和高性能。以下是一些关键的规范要点： 1. **基础规范**： - **存储引擎**：所有表...
MYSQL数据库开发设计规范.doc
02-08
MySQL数据库开发设计规范是指导开发人员在构建数据库时遵循的一套标准和最佳实践，旨在确保数据库的可读性、易维护性和高效运行。规范涵盖了命名规范、设计规范等多个方面。 **命名规范** 命名规范是数据库设计的...
基于JAVA+MYSQL开发的网络考试系统
最新发布
06-07
【作品名称】：基于JAVA+MYSQL开发的网络考试系统 【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。 【项目介绍】：部署要点 ...
本章要点：MySQL数据库系统简介MySQL数据库系统的安装.ppt
12-14
MySQL数据库系统的安装、配置和使用是Web开发和数据处理的基础，本章将详细介绍MySQL数据库系统的简介、安装、配置、数据类型、结构化查询语言、数据库管理工具等知识点。 一、MySQL数据库系统简介 MySQL是一个多...
MySQL知识点汇总
LuckyPrinceyue的博客
12-13 1780
数据冗余
MySQL知识点整理汇总
weixin_45517168的博客
12-11 2355
2020年暑假期间我开始接触 MySQL，通过阅读了几本相关书籍以及网上的一些教程，我逐渐了解了 MySQL 的入门级内容。在暑假期间学完 MySQL 的基础知识后，我便将其搁置在一旁，没有再进行进一步的探索。 如今，我想要再次拾起 MySQL 时，却发现其中的一些语法和规则已经差不多遗忘。对 MySQL 进阶的需求，再加上以前学过的每本书籍（或每个教程）的知识侧重点偶尔不同，因此我打算用这篇博客来记录我所学习到的 MySQL 重要知识点（包括基础知识与进阶内容）
mysql随笔
u013181966的博客
08-14 555
一、数据库概述 数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。 ...
MySQL知识点（详细）
ziania_cumt的博客
04-13 2705
MySQL知识点（详细）
MySQL必知必会知识点总结一二
热门推荐
徐刘根的博客
06-24 3万+
一、MySQL官方文档地址 https://dev.mysql.com/doc/refman/5.7/en/ 二、MySQL常用命令 1、如何登陆mysql数据库 mysql -u username -p 1.1、指定IP、端口 mysql -uroot -h 192.168.1.213 -P3306 -p123456 -P（大写）：指定端口； -p（小写）：指定密码；...
整理mysql的28个知识点
灰太狼
05-08 4376
整理mysql26个知识点：1、MySQL的复制原理以及流程基本原理流程，3个线程以及之间的关联：主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；从：sql执行线程——执行relay log中的语句；2、MySQL中myi...
mysql面经文档、技术要点.pptx
11-09
MySQL面经文档、技术要点 MySQL作为关系型数据库的基石，被广泛应用于各种企业级应用系统和互联网平台，因其高效的数据处理能力、丰富的SQL语言支持以及易用性而备受开发人员青睐。在实际工作中，熟练掌握MySQL的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值