MySQL基础-从建库建表到增删改查
MySQL 基础：从建库建表到增删改查
刚开始学 MySQL 时，我最容易混淆的不是某一条语法，而是这些语句到底在解决什么问题。CREATE、INSERT、SELECT看起来都在“操作数据库”，但它们其实处在完全不同的阶段。
后来我把常用 SQL 按下面这条线重新整理了一遍：
- 先建数据库、建表，确定数据以什么结构保存；
- 再插入、修改、删除数据；
- 最后按条件查询、排序、分页和统计。
这篇文章用一个简单的用户表贯穿示例。环境按 MySQL 8.0 编写，代码可以直接复制到客户端执行。
一、先分清 DDL、DML 和 DQL
| 分类 | 用途 | 常见关键字 |
|---|---|---|
| DDL | 定义数据库和表的结构 | CREATE、ALTER、DROP、TRUNCATE |
| DML | 写入和修改表中的数据 | INSERT、UPDATE、DELETE |
| DQL | 查询数据 | SELECT |
可以把数据库想成一个仓库：
- DDL 决定仓库有几个房间、每个货架放什么；
- DML 负责把货物搬进来、换位置或清出去；
- DQL 负责按条件找到需要的货物。
这个区分看似基础，后面排查 SQL 问题时却很有用。比如删错一列应该找ALTER TABLE，删错一行才是DELETE。
二、准备一个练习数据库
1. 创建并进入数据库
createdatabaseifnotexistsmysql_practicedefaultcharactersetutf8mb4collateutf8mb4_0900_ai_ci;usemysql_practice;utf8mb4可以完整保存中文和 emoji，实际项目里一般比旧的utf8更稳妥。
常用的数据库查看命令：
showdatabases;selectdatabase();showcreatedatabasemysql_practice;2. 创建用户表
createtableusers(idbigintunsignedprimarykeyauto_increment,usernamevarchar(30)notnull,phonechar(11),birthdaydate,statustinyintnotnulldefault1,balancedecimal(10,2)notnulldefault0.00,created_atdatetimenotnulldefaultcurrent_timestamp,uniquekeyuk_users_username(username),uniquekeyuk_users_phone(phone))engine=InnoDBdefaultcharset=utf8mb4;这里顺便解释几个常用类型：
| 类型 | 适用场景 |
|---|---|
bigint | 主键、数量较大的整数 |
varchar(n) | 长度不固定的字符串，例如用户名 |
char(n) | 长度基本固定的字符串，例如手机号、状态码 |
decimal(m, d) | 金额等需要精确计算的小数 |
date | 只保存日期 |
datetime | 保存日期和时间 |
金额不建议用float或double。浮点数适合科学计算，但会有精度误差；订单金额、余额一类字段通常使用decimal。
查看表是否创建成功：
showtables;descusers;showcreatetableusers;三、DDL：修改表结构
需求变化后，经常要给已有表加字段或调整字段定义，这时用ALTER TABLE。
1. 添加字段
altertableusersaddcolumnemailvarchar(100)afterphone;2. 修改字段类型
altertableusersmodifycolumnusernamevarchar(50)notnull;3. 修改字段名和类型
altertableusers changecolumnstatusaccount_statustinyintnotnulldefault1;MySQL 8.0 还可以只重命名字段：
altertableusersrenamecolumnaccount_statustostatus;4. 删除字段
altertableusersdropcolumnemail;这类操作会改变表结构。生产环境执行前，除了备份，还要确认应用代码、接口和报表是否仍在使用该字段。
5.DELETE、TRUNCATE和DROP的区别
| 写法 | 实际效果 |
|---|---|
delete from users; | 删除全部行，保留表结构 |
truncate table users; | 快速清空表，通常会重置自增计数 |
drop table users; | 连表结构一起删除 |
它们都很危险，但危险的层级不同。DROP TABLE之后，字段、索引和数据都会消失。
四、DML：新增、修改和删除数据
1. INSERT：插入数据
日常开发更推荐指定字段名：
insertintousers(username,phone,birthday,balance)values('张三','13800000001','2001-02-03',100.00);这样即使表后来增加了字段，原来的 SQL 也不容易受到影响。
一次插入多行：
insertintousers(username,phone,birthday,balance)values('李四','13800000002','2000-06-18',55.50),('王五',null,'1999-11-20',320.00),('赵六','13800000004',null,0.00);需要复制查询结果时，可以使用INSERT ... SELECT：
insertintovip_users(user_id,username)selectid,usernamefromuserswherebalance>=300;目标字段数量、顺序和类型要与查询结果对应。
2. UPDATE：修改数据
updateuserssetphone='13900000001',balance=balance+50whereid=1;我现在执行UPDATE前都会先跑一遍相同条件的查询：
selectid,username,phone,balancefromuserswhereid=1;确认命中的确实是目标记录，再执行更新。这个习惯比背多少条语法都实用。
下面这条 SQL 没有WHERE，会修改整张表：
updateuserssetstatus=0;如果需求真的是全表更新，最好也先统计行数，并在事务或备份可用的情况下操作。
3. DELETE：删除数据
deletefromuserswhereid=4;同样先用SELECT检查范围：
select*fromuserswhereid=4;删除手机号为空的用户：
deletefromuserswherephoneisnull;NULL代表未知或不存在，不能写成phone = null，必须使用IS NULL或IS NOT NULL。
另外，DELETE删除的是整行，不是某个字段。如果只是清空手机号，应写：
updateuserssetphone=nullwhereid=1;NULL和空字符串''也不是一回事：前者表示没有值，后者是一个长度为 0 的字符串。
五、DQL：把数据查出来
一条完整查询通常按下面的顺序书写：
select字段列表from表名where行过滤条件groupby分组字段having分组后的过滤条件orderby排序字段limit起始位置,返回行数;逻辑执行顺序可以先记成：
FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → ORDER BY → LIMIT这能解释两个常见问题：
WHERE阶段还没完成分组，所以不能直接使用聚合函数；HAVING在聚合之后执行，因此可以写HAVING AVG(score) >= 80。
1. 查询需要的字段
selectid,username,phonefromusers;练习时用SELECT *很方便，但业务代码最好明确列名。这样能减少无用数据传输，也不会因为表结构变化而突然多返回敏感字段。
字段可以使用别名和表达式：
selectusernameas用户名,balanceas余额,year(curdate())-year(birthday)as大致年龄fromusers;去重查询：
selectdistinctstatusfromusers;如果DISTINCT后面有多列，MySQL 判断的是这一组列的组合是否重复。
2. WHERE 条件查询
常用条件可以分成四类：
| 类型 | 示例 |
|---|---|
| 比较 | balance >= 100、status <> 0 |
| 范围 | birthday between '2000-01-01' and '2005-12-31' |
| 集合 | status in (1, 2) |
| 模糊匹配 | username like '张%' |
组合多个条件：
selectid,username,balancefromuserswherestatus=1andbalance>=100;AND的优先级高于OR。条件一复杂，建议主动加括号：
select*fromuserswherestatus=1and(balance>=300orbirthdayisnull);LIKE有两个常用通配符：
%：匹配任意长度的字符，包括 0 个字符；_：只匹配一个字符。
-- 姓张select*fromuserswhereusernamelike'张%';-- 名字正好两个字符select*fromuserswhereusernamelike'__';前面带%的查询，例如LIKE '%三'，普通 B+ 树索引通常很难有效利用，数据量大时要留意执行计划。
3. ORDER BY 排序
selectid,username,balancefromusersorderbybalancedesc,idasc;先按余额降序；余额相同时，再按id升序。多加一个稳定排序字段，也能避免分页时同分记录顺序来回变化。
4. LIMIT 分页
selectid,username,balancefromusersorderbyidlimit0,10;第n页、每页page_size条时：
offset = (n - 1) × page_size例如第 3 页、每页 10 条：
selectid,usernamefromusersorderbyidlimit20,10;也可以写成：
limit10offset20;当页码非常靠后时，OFFSET会扫描并丢弃大量记录。实际项目常用上一页最后一个id做游标：
selectid,usernamefromuserswhereid>10000orderbyidlimit10;5. 聚合函数与 GROUP BY
常见聚合函数：
| 函数 | 用途 |
|---|---|
count() | 统计数量 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
selectcount(*)asuser_count,round(avg(balance),2)asavg_balance,max(balance)asmax_balancefromusers;COUNT(*)统计行数；COUNT(phone)只统计phone不为NULL的行。
按状态分组：
selectstatus,count(*)asuser_count,round(avg(balance),2)asavg_balancefromusersgroupbystatus;先过滤原始行，用WHERE：
selectstatus,count(*)asuser_countfromuserswherebalance>=100groupbystatus;先分组统计，再过滤分组结果，用HAVING：
selectstatus,count(*)asuser_countfromusersgroupbystatushavingcount(*)>=2;6. 几个常用内置函数
-- 日期时间selectcurdate(),now();selectdate_add(curdate(),interval7day);selectdatediff('2026-08-01','2026-07-26');-- 字符串selectconcat(username,'：',phone)fromusers;selectchar_length('你好 MySQL');selecttrim(' MySQL ');-- 数学selectround(12.3456,2);selectfloor(rand()*1000000);中文场景下，CHAR_LENGTH()统计字符数，LENGTH()统计字节数，两者不要混用。
以上是我关于MySQL的笔记分享，也可以关注关注我的Sirens-Blog🥰
感谢你读到这里，这也是我学习路上的一个小小记录。希望以后回头看时，能看到自己的成长~