MySQL基础-从建库建表到增删改查

发布时间：2026/7/27 5:29:17

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);