MySQL 学习记录一数据库、表设计与常用 SQLMySQL 的第一部分先整理基础内容包括数据库和表的操作、字段类型、约束、增删改查、多表查询、子查询以及常用函数。SQL 语句本身不复杂容易出问题的地方主要是字段类型选择、空值处理、查询条件、多表连接和数据更新范围。很多语句单独执行没有问题放到一起时才会看出表结构是否合理。一、SQL 语句的分类常见 SQL 可以分为几类。DDL数据定义语言用来操作数据库、表和字段结构。CREATE ALTER DROP TRUNCATE例如创建表、增加字段、修改字段类型都属于 DDL。DML数据操作语言用来修改表中的数据。INSERT UPDATE DELETEDQL数据查询语言主要是SELECTDCL数据控制语言用来管理权限。GRANT REVOKETCL事务控制语言常见命令COMMIT ROLLBACK SAVEPOINT平时写得最多的是 DML 和 DQL。DDL 的执行需要更谨慎因为修改的是表结构有些操作会影响整张表。二、数据库的基本操作查看数据库SHOW DATABASES;创建数据库CREATE DATABASE study_db;指定字符集CREATE DATABASE study_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;使用数据库USE study_db;查看当前所在数据库SELECT DATABASE();删除数据库DROP DATABASE study_db;DROP DATABASE会删除数据库中的所有表和数据执行前需要确认数据库名称。创建中文数据库或表时字符集通常使用utf8mb4。MySQL 中的utf8最多保存三个字节的字符utf8mb4能够保存更完整的 Unicode 字符。三、创建一张用户表先创建一张简单的用户表CREATE TABLE sys_user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, password VARCHAR(100) NOT NULL, phone VARCHAR(20), email VARCHAR(100), gender TINYINT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 1, birthday DATE, balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted TINYINT NOT NULL DEFAULT 0 ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;查看表结构DESC sys_user;也可以查看完整建表语句SHOW CREATE TABLE sys_user;四、字段类型怎么选字段类型不仅影响能保存什么数据也会影响存储空间、索引和查询。1. 整数类型常见整数类型TINYINT SMALLINT MEDIUMINT INT BIGINT状态字段取值范围比较小可以使用TINYINTstatus TINYINT NOT NULL DEFAULT 1主键 ID 经常使用BIGINTid BIGINT PRIMARY KEY AUTO_INCREMENTINT(11)中的11不是存储范围。整数的存储范围由类型本身决定。2. DECIMAL金额不适合使用FLOAT或DOUBLE一般使用DECIMALbalance DECIMAL(10, 2)这里的10表示总位数2表示小数位数。可以保存的最大正数大致是99999999.99Java 中对应的类型通常是BigDecimal3. CHAR 和 VARCHARCHAR是定长字符串VARCHAR是变长字符串。gender_code CHAR(1) username VARCHAR(50)长度比较固定时可以考虑CHAR大部分普通文本字段使用VARCHAR。身份证号、手机号和订单号虽然看起来是数字但通常不参与数学计算也可能包含前导零所以应该使用字符串类型。phone VARCHAR(20) order_no VARCHAR(32)不应该写成phone BIGINT4. TEXT较长文本可以使用TEXT MEDIUMTEXT LONGTEXT普通标题、名称和描述不应该一律使用TEXT。VARCHAR更容易控制长度也更适合作为部分索引字段。5. 日期类型常用日期类型DATE TIME DATETIME TIMESTAMP只保存生日birthday DATE保存创建时间create_time DATETIMEDATETIME保存日期时间本身范围较大。TIMESTAMP与时区转换和服务器设置关系更紧密。如果业务只关心某一天不要使用字符串保存birthday VARCHAR(20)使用日期类型后比较、排序和日期计算会更方便。五、NULL 和空字符串NULL表示没有值空字符串表示值存在但内容为空。phone IS NULL不能写成phone NULL判断非空phone IS NOT NULL查询空字符串phone 同时排除NULL和空字符串WHERE phone IS NOT NULL AND phone 聚合函数对NULL的处理也需要注意。假设表中有三条数据10 20 NULL执行SELECT COUNT(score) FROM student;结果是2因为COUNT(字段)不统计NULL。SELECT COUNT(*) FROM student;结果是3因为COUNT(*)统计行数。六、约束约束用来保证数据符合基本规则。1. 主键约束id BIGINT PRIMARY KEY主键不能重复也不能为NULL。自动增长id BIGINT PRIMARY KEY AUTO_INCREMENT2. 非空约束username VARCHAR(50) NOT NULL字段必须有值。3. 唯一约束phone VARCHAR(20) UNIQUE也可以单独创建ALTER TABLE sys_user ADD CONSTRAINT uk_user_phone UNIQUE (phone);只在 Java 中查询手机号是否存在不能完全防止重复数据。并发请求可能同时通过查询所以真正要求唯一的字段需要数据库唯一约束。4. 默认值status TINYINT NOT NULL DEFAULT 1插入时不指定状态就使用默认值。5. 外键约束订单表可以引用用户表CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES sys_user(id)外键能保证引用的数据存在但也会增加写入和表结构修改的限制。有些项目使用数据库外键有些项目只保留逻辑关联。无论是否创建外键关联字段一般都需要保持相同的数据类型并考虑索引。七、修改表结构增加字段ALTER TABLE sys_user ADD COLUMN nickname VARCHAR(50) AFTER username;修改字段类型ALTER TABLE sys_user MODIFY COLUMN nickname VARCHAR(100);修改字段名称ALTER TABLE sys_user CHANGE COLUMN nickname nick_name VARCHAR(100);删除字段ALTER TABLE sys_user DROP COLUMN nick_name;修改表名RENAME TABLE sys_user TO user_info;生产环境修改大表结构时不能只考虑语法是否正确。增加索引、修改字段类型等操作可能锁表或消耗较多资源。八、插入数据指定字段插入INSERT INTO sys_user ( username, password, phone, email, gender ) VALUES ( zhangsan, 123456, 13800000001, zhangsanexample.com, 1 );不建议省略字段列表INSERT INTO sys_user VALUES (...);表结构变化后字段数量和顺序可能变化原 SQL 容易失效。批量插入INSERT INTO sys_user ( username, password, phone, gender ) VALUES (zhangsan, 123456, 13800000001, 1), (lisi, 123456, 13800000002, 1), (wangwu, 123456, 13800000003, 2);批量插入通常比循环执行多条单行插入效率高。但一次插入的数据也不能无限增加需要控制 SQL 大小和事务时间。九、修改数据根据主键修改UPDATE sys_user SET username zhangsan_new, update_time NOW() WHERE id 1;多个条件UPDATE sys_user SET status 0 WHERE id 1 AND status 1;第二种写法可以限制数据当前状态。执行后还要检查受影响行数受影响 1 行修改成功 受影响 0 行数据不存在或状态不符合条件执行UPDATE前最好先使用相同条件查询SELECT * FROM sys_user WHERE id 1;确认范围后再执行更新。缺少WHERE会修改整张表UPDATE sys_user SET status 0;语法完全正确但结果通常不是想要的。十、删除数据物理删除DELETE FROM sys_user WHERE id 1;清空整张表DELETE FROM sys_user;TRUNCATE也可以清空表TRUNCATE TABLE sys_user;两者并不完全相同。DELETE属于 DML可以带WHERE并且逐行删除。TRUNCATE属于 DDL用来快速清空整张表不能带条件。很多业务表会使用逻辑删除UPDATE sys_user SET deleted 1 WHERE id 1;查询时加上WHERE deleted 0逻辑删除可以保留历史数据但所有查询都需要考虑删除标记。唯一索引设计也可能受到影响。十一、基础查询查询全部字段SELECT * FROM sys_user;实际代码中更建议明确字段SELECT id, username, phone, status, create_time FROM sys_user;这样可以避免返回不需要的字段也不会因为表新增字段而改变查询结果结构。使用别名SELECT id AS user_id, username AS user_name FROM sys_user;去重SELECT DISTINCT status FROM sys_user;去重作用于查询结果中的整组字段SELECT DISTINCT status, gender FROM sys_user;这里去重的是status和gender的组合。十二、WHERE 查询条件等值查询SELECT * FROM sys_user WHERE status 1;不等于WHERE status 0范围查询WHERE balance 100 AND balance 1000也可以使用WHERE balance BETWEEN 100 AND 1000BETWEEN包含边界值。多个可选值WHERE status IN (1, 2, 3)排除WHERE status NOT IN (0, 3)逻辑条件WHERE status 1 AND gender 1WHERE status 1 OR status 2AND的优先级高于OR。条件比较复杂时应使用括号WHERE status 1 AND (gender 1 OR gender 2)十三、LIKE 模糊查询查询用户名中包含“张”SELECT * FROM sys_user WHERE username LIKE %张%;以“张”开头WHERE username LIKE 张%以“三”结尾WHERE username LIKE %三%表示任意数量字符_表示一个字符WHERE username LIKE 张_前后都有%的查询LIKE %关键字%通常难以使用普通 BTree 索引。数据量大时不能只考虑查询结果是否正确还需要检查执行计划。十四、排序按创建时间倒序SELECT * FROM sys_user ORDER BY create_time DESC;正序ORDER BY create_time ASC;多字段排序ORDER BY status ASC, create_time DESC, id DESC;先按照状态排序状态相同时再按照创建时间排序。分页查询中最好提供稳定排序ORDER BY create_time DESC, id DESC如果多条数据的创建时间相同再按照主键排序可以让结果顺序更稳定。十五、分页查询第一页每页十条SELECT * FROM sys_user ORDER BY id DESC LIMIT 0, 10;查询第二页LIMIT 10, 10偏移量计算offset (page - 1) * pageSize第 5 页每页 20 条offset (5 - 1) * 20 80对应 SQLLIMIT 80, 20查询总数SELECT COUNT(*) FROM sys_user WHERE deleted 0;分页接口通常需要执行两条 SQL查询总记录数查询当前页数据。当偏移量非常大时普通LIMIT会出现深分页问题。这部分放到索引和 SQL 优化中再整理。十六、聚合函数COUNT统计总行数SELECT COUNT(*) FROM sys_user;统计非空手机号数量SELECT COUNT(phone) FROM sys_user;SUM计算余额总和SELECT SUM(balance) FROM sys_user;AVG计算平均余额SELECT AVG(balance) FROM sys_user;MAX 和 MINSELECT MAX(balance), MIN(balance) FROM sys_user;如果没有符合条件的数据SUM、AVG等结果可能为NULL。可以使用SELECT COALESCE(SUM(balance), 0) FROM sys_user WHERE status 99;十七、GROUP BY按照状态统计人数SELECT status, COUNT(*) AS user_count FROM sys_user GROUP BY status;按照状态和性别分组SELECT status, gender, COUNT(*) AS user_count FROM sys_user GROUP BY status, gender;WHERE在分组前过滤数据SELECT status, COUNT(*) AS user_count FROM sys_user WHERE deleted 0 GROUP BY status;HAVING在分组后过滤结果SELECT status, COUNT(*) AS user_count FROM sys_user GROUP BY status HAVING COUNT(*) 10;不能把所有条件都放到HAVING。能在分组前过滤的条件应优先放在WHERE减少参与分组的数据量。十八、表之间的关系常见关系有三种。一对一一个用户对应一份用户详情。sys_user user_profile可以让user_profile.user_id建立唯一约束。一对多一个用户可以有多个订单。sys_user.id rental_order.user_id“多”的一方保存“一”的主键。多对多一个用户可以有多个角色一个角色也可以分配给多个用户。需要中间表sys_user sys_role sys_user_role中间表保存两个外键CREATE TABLE sys_user_role ( user_id BIGINT NOT NULL, role_id BIGINT NOT NULL, PRIMARY KEY (user_id, role_id) );联合主键可以防止同一个用户重复绑定同一角色。十九、内连接准备两张表。用户表CREATE TABLE sys_user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL );订单表CREATE TABLE rental_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL );查询订单和用户名SELECT o.id, o.order_no, u.username, o.total_amount FROM rental_order AS o INNER JOIN sys_user AS u ON u.id o.user_id;INNER JOIN只返回两张表中能够匹配的数据。如果某条订单的user_id在用户表中不存在这条订单不会出现在结果中。二十、左连接和右连接左连接SELECT u.id, u.username, o.order_no FROM sys_user AS u LEFT JOIN rental_order AS o ON o.user_id u.id;左表sys_user中的数据都会保留。没有订单的用户订单字段为NULL。查询没有订单的用户SELECT u.id, u.username FROM sys_user AS u LEFT JOIN rental_order AS o ON o.user_id u.id WHERE o.id IS NULL;右连接保留右表数据SELECT * FROM sys_user AS u RIGHT JOIN rental_order AS o ON o.user_id u.id;右连接通常可以通过调整表顺序改写成左连接。实际代码中左连接更常见一些。二十一、连接条件放在 ON 还是 WHERE下面两条左连接 SQL 的结果可能不同。第一条SELECT u.id, u.username, o.order_no FROM sys_user AS u LEFT JOIN rental_order AS o ON o.user_id u.id AND o.status 1;订单状态条件写在ON中。即使用户没有状态为1的订单用户仍然会保留。第二条SELECT u.id, u.username, o.order_no FROM sys_user AS u LEFT JOIN rental_order AS o ON o.user_id u.id WHERE o.status 1;状态条件放在WHERE后没有匹配订单的用户其o.status是NULL会被过滤掉。结果更接近内连接。写左连接时需要先确认是否希望保留左表中没有匹配记录的数据。二十二、子查询查询余额高于平均余额的用户SELECT id, username, balance FROM sys_user WHERE balance ( SELECT AVG(balance) FROM sys_user );查询有订单的用户SELECT id, username FROM sys_user WHERE id IN ( SELECT user_id FROM rental_order );也可以使用EXISTSSELECT u.id, u.username FROM sys_user AS u WHERE EXISTS ( SELECT 1 FROM rental_order AS o WHERE o.user_id u.id );子查询不一定比连接慢连接也不一定总是更好。最终需要结合执行计划、索引和数据量判断。NOT IN还需要注意NULL。如果子查询结果中包含NULL结果可能与预期不同。排除关联数据时NOT EXISTS通常更容易控制SELECT u.id, u.username FROM sys_user AS u WHERE NOT EXISTS ( SELECT 1 FROM rental_order AS o WHERE o.user_id u.id );二十三、CASE WHEN把状态值转换成文字SELECT id, username, CASE status WHEN 0 THEN 禁用 WHEN 1 THEN 正常 ELSE 未知 END AS status_name FROM sys_user;条件形式SELECT username, balance, CASE WHEN balance 10000 THEN A WHEN balance 5000 THEN B WHEN balance 1000 THEN C ELSE D END AS balance_level FROM sys_user;CASE WHEN适合在查询结果中做简单转换。复杂业务规则不适合全部写进 SQL否则 SQL 会很难维护。二十四、常用字符串函数拼接SELECT CONCAT(username, -, phone) FROM sys_user;字符串长度SELECT CHAR_LENGTH(username) FROM sys_user;截取SELECT SUBSTRING(phone, 1, 3) FROM sys_user;去除两端空格SELECT TRIM(username) FROM sys_user;大小写转换SELECT UPPER(username), LOWER(username) FROM sys_user;替换SELECT REPLACE(phone, 138, ***) FROM sys_user;函数可以方便地处理结果但如果在索引字段上使用函数作为查询条件可能影响索引使用WHERE YEAR(create_time) 2026可以改成范围查询WHERE create_time 2026-01-01 00:00:00 AND create_time 2027-01-01 00:00:00二十五、常用日期函数当前日期SELECT CURDATE();当前日期和时间SELECT NOW();增加日期SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);减少日期SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);计算相差天数SELECT DATEDIFF( 2026-08-20, 2026-08-01 );格式化时间SELECT DATE_FORMAT( create_time, %Y-%m-%d %H:%i:%s ) FROM sys_user;这里%i表示分钟不是%m。%m表示月份。按日期统计SELECT DATE(create_time) AS create_date, COUNT(*) AS user_count FROM sys_user GROUP BY DATE(create_time) ORDER BY create_date;这种写法适合统计结果。如果在WHERE中对索引字段使用DATE(create_time)要留意索引是否还能正常使用。二十六、数据表设计中需要注意的部分1. 表和字段名称保持统一常见命名方式sys_user rental_order create_time update_time避免同一个项目中同时出现createTime create_time created_at create_date数据库通常使用小写字母和下划线。2. 字段长度不要全部写成 255用户名、手机号、订单号的长度不同不需要全部使用VARCHAR(255)可以根据实际数据确定合理长度username VARCHAR(50) phone VARCHAR(20) order_no VARCHAR(32)3. 状态值需要明确status TINYINT NOT NULL DEFAULT 1需要在表注释或代码枚举中写清楚0禁用 1正常 2锁定不要让同一个状态值在不同代码中表示不同含义。4. 金额使用 DECIMALtotal_amount DECIMAL(10, 2)不要使用字符串或浮点数保存金额。5. 时间字段统一常见字段create_time DATETIME NOT NULL update_time DATETIME NOT NULL如果项目包含逻辑删除还可以增加deleted TINYINT NOT NULL DEFAULT 0是否需要create_by、update_by根据系统是否要求记录操作人决定。二十七、目前容易写错的 SQLUPDATE 忘记 WHEREUPDATE sys_user SET status 0;会更新全部数据。DELETE 忘记 WHEREDELETE FROM sys_user;会删除全部数据。NULL 使用等号判断错误WHERE phone NULL正确WHERE phone IS NULLLEFT JOIN 条件写到 WHERE如果需要保留左表数据右表过滤条件要确认应该写在ON还是WHERE。COUNT 字段忽略 NULLCOUNT(phone)只统计手机号非空的数据。如果要统计行数使用COUNT(*)SELECT * 返回无关字段接口查询最好明确列名特别是用户表中包含密码等字段时。字符串数字没有加引号手机号、订单号等字符串字段查询时应使用字符串WHERE phone 13800000001不要写成WHERE phone 13800000001隐式类型转换可能影响结果和索引使用。二十八、这一部分先记住的内容MySQL 基础部分主要包括数据库和表 字段类型 约束 INSERT、UPDATE、DELETE SELECT WHERE ORDER BY LIMIT 聚合和分组 多表连接 子查询 常用函数SQL 能执行只是第一步还需要检查查询结果是否包含重复数据NULL是否处理正确更新和删除范围是否准确左连接是否保留了需要的数据字段类型是否和业务数据匹配唯一数据是否有数据库约束时间、金额和状态字段是否统一。下一篇会继续整理索引、执行计划、事务、锁和 SQL 优化。文章标签MySQL、SQL、数据库、表设计、多表查询、学习笔记