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

MySQL 基础从建库建表到增删改查刚开始学 MySQL 时我最容易混淆的不是某一条语法而是这些语句到底在解决什么问题。CREATE、INSERT、SELECT看起来都在“操作数据库”但它们其实处在完全不同的阶段。后来我把常用 SQL 按下面这条线重新整理了一遍先建数据库、建表确定数据以什么结构保存再插入、修改、删除数据最后按条件查询、排序、分页和统计。这篇文章用一个简单的用户表贯穿示例。环境按 MySQL 8.0 编写代码可以直接复制到客户端执行。一、先分清 DDL、DML 和 DQL分类用途常见关键字DDL定义数据库和表的结构CREATE、ALTER、DROP、TRUNCATEDML写入和修改表中的数据INSERT、UPDATE、DELETEDQL查询数据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))engineInnoDBdefaultcharsetutf8mb4;这里顺便解释几个常用类型类型适用场景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 ... SELECTinsertintovip_users(user_id,username)selectid,usernamefromuserswherebalance300;目标字段数量、顺序和类型要与查询结果对应。2. UPDATE修改数据updateuserssetphone13900000001,balancebalance50whereid1;我现在执行UPDATE前都会先跑一遍相同条件的查询selectid,username,phone,balancefromuserswhereid1;确认命中的确实是目标记录再执行更新。这个习惯比背多少条语法都实用。下面这条 SQL 没有WHERE会修改整张表updateuserssetstatus0;如果需求真的是全表更新最好也先统计行数并在事务或备份可用的情况下操作。3. DELETE删除数据deletefromuserswhereid4;同样先用SELECT检查范围select*fromuserswhereid4;删除手机号为空的用户deletefromuserswherephoneisnull;NULL代表未知或不存在不能写成phone null必须使用IS NULL或IS NOT NULL。另外DELETE删除的是整行不是某个字段。如果只是清空手机号应写updateuserssetphonenullwhereid1;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,balancefromuserswherestatus1andbalance100;AND的优先级高于OR。条件一复杂建议主动加括号select*fromuserswherestatus1and(balance300orbirthdayisnull);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,usernamefromuserswhereid10000orderbyidlimit10;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;先过滤原始行用WHEREselectstatus,count(*)asuser_countfromuserswherebalance100groupbystatus;先分组统计再过滤分组结果用HAVINGselectstatus,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感谢你读到这里这也是我学习路上的一个小小记录。希望以后回头看时能看到自己的成长~