吖昭的技术笔记《MySQL 数据库基本操作命令大全》
MySQL 作为最流行的关系型数据库之一，其操作命令是后端开发、数据分析的必备技能。从数据库创建到数据增删改查，掌握基础 SQL 语句能让你快速上手数据管理。以下整理了 MySQL 核心操作命令，涵盖数据库设计、表结构定义及 CRUD（增删改查）全流程，附新手易犯错误提示。
一、数据库基础操作
1. 连接与退出 MySQL
-- 连接本地MySQL服务器（默认端口3306）
mysql -u 用户名 -p
-- 示例：以root用户登录
mysql -u root -p
-- 输入密码后回车（密码输入时不显示）
-- 连接远程MySQL服务器
mysql -h 服务器IP -u 用户名 -p 端口号
-- 示例：连接IP为192.168.1.100的服务器，端口3307
mysql -h 192.168.1.100 -u admin -p 3307
-- 退出MySQL
exit; 或 quit;
2. 数据库的创建与管理
-- 查看所有数据库
show databases;
-- 创建数据库（指定字符集为utf8mb4，支持emoji）
create database 数据库名 character set utf8mb4 collate utf8mb4_general_ci;
-- 示例：创建名为blog的数据库
create database blog character set utf8mb4 collate utf8mb4_general_ci;
-- 切换到指定数据库（操作表前必须执行）
use 数据库名;
-- 示例：使用blog数据库
use blog;
-- 查看当前使用的数据库
select database();
-- 删除数据库（危险操作！会删除所有表和数据）
drop database 数据库名;
-- 示例：删除test数据库
drop database test;
易错点：
- 创建数据库时若不指定字符集，默认可能为latin1，导致中文乱码，务必指定utf8mb4。
- drop database无二次确认，删除前需备份重要数据。
二、表结构设计与管理
表是数据库存储数据的基本单位，需先定义表结构（字段名、数据类型、约束等）才能存储数据。
1. 表的创建（CREATE TABLE）
-- 基本语法
create table 表名 (
字段名1 数据类型 约束,
字段名2 数据类型 约束,
...
主键约束/外键约束等
);
-- 示例：创建用户表（users）
create table users (
id int primary key auto_increment, -- 主键，自动增长
username varchar(50) not null unique, -- 用户名，非空且唯一
password varchar(100) not null, -- 密码（加密存储，长度较长）
email varchar(100) unique, -- 邮箱，唯一
age tinyint unsigned, -- 年龄（无符号，0-255）
created_at datetime default current_timestamp -- 创建时间，默认当前时间
) engine=InnoDB default charset=utf8mb4;
常用数据类型：
- int：整数（如 ID、年龄）。
- varchar(n)：可变长度字符串（如用户名、邮箱，n 为最大长度）。
- datetime：日期时间（如2023-10-01 12:30:00）。
- tinyint：小整数（常用于表示状态，如 0/1 表示禁用 / 启用）。
常用约束：
- primary key：主键（唯一标识记录，不重复）。
- not null：字段不能为空。
- unique：字段值唯一（如用户名、邮箱）。
- auto_increment：数值自动增长（常用于主键）。
- default：默认值（如创建时间默认当前时间）。
2. 表结构的查看与修改
-- 查看表结构（字段、类型、约束）
desc 表名; 或 describe 表名;
-- 示例：查看users表结构
desc users;
-- 查看创建表的SQL语句（详细结构）
show create table 表名;
-- 添加字段
alter table 表名 add 字段名 数据类型 约束;
-- 示例：给users表添加phone字段
alter table users add phone varchar(20) unique;
-- 修改字段类型或约束
alter table 表名 modify 字段名 新数据类型 新约束;
-- 示例：将age字段改为允许null
alter table users modify age tinyint unsigned null;
-- 删除字段
alter table 表名 drop 字段名;
-- 示例：删除phone字段
alter table users drop phone;
-- 删除表（危险操作！会删除表中所有数据）
drop table 表名;
-- 示例：删除test_table表
drop table test_table;
易错点：
- 修改或删除字段可能导致数据丢失（如缩短varchar长度可能截断现有数据）。
- 对已有大量数据的表执行alter table可能锁表，建议在低峰期操作。
三、数据增删改查（CRUD）
1. 新增数据（INSERT）
-- 插入完整记录（按表中所有字段顺序）
insert into 表名 values (值1, 值2, ...);
-- 示例：插入用户记录（id自动增长，可省略）
insert into users values (null, 'zhangsan', '123456', 'zhangsan@example.com', 20, null);
-- 插入指定字段（推荐，字段顺序可自定义）
insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...);
-- 示例：只插入用户名、密码和邮箱
insert into users (username, password, email) values ('lisi', '654321', 'lisi@example.com');
-- 批量插入（效率高于多次单条插入）
insert into 表名 (字段1, 字段2) values
(值1, 值2),
(值3, 值4),
(值5, 值6);
-- 示例：批量插入3个用户
insert into users (username, password) values
('wangwu', 'w123'),
('zhaoliu', 'z456'),
('qianqi', 'q789');
易错点：
- 插入的值需与字段类型匹配（如给datetime字段传入字符串需符合格式）。
- 非 null 字段必须提供值，否则会报错。
2. 查询数据（SELECT）
-- 查询表中所有字段的所有记录（不推荐，数据量大时效率低）
select * from 表名;
-- 示例：查询users表所有数据
select * from users;
-- 查询指定字段
select 字段1, 字段2 from 表名;
-- 示例：查询用户名和邮箱
select username, email from users;
-- 带条件查询（where子句）
select 字段 from 表名 where 条件;
-- 示例：查询age>18的用户
select username, age from users where age > 18;
-- 多条件查询（and/or）
select * from users where age > 18 and email is not null; -- 年龄>18且邮箱不为空
select * from users where age < 18 or age > 60; -- 年龄<18或>60
-- 模糊查询（like）
select * from users where username like 'zhang%'; -- 用户名以zhang开头
易错点：
- select *会返回所有字段，生产环境中应明确指定所需字段（减少数据传输）。
- null值比较不能用=，需用is null或is not null（如where email is null）。
3. 更新数据（UPDATE）
-- 示例：将id=5的用户年龄增加5岁
update users set age = age + 5 where id = 5;
危险提示：
- 忘记写where条件会导致全表数据被更新，执行前务必检查条件是否正确。
- 建议先执行select确认符合条件的记录数，再执行update（如select * from users where username = 'zhangsan'）。
4. 删除数据（DELETE）
-- 基本语法（必须加where条件，否则删除全表数据）
delete from 表名 where 条件;
-- 示例：删除id=10的用户
delete from users where id = 10;
-- 示例：删除超过1年未活跃的用户（假设last_login为最后登录时间）
delete from users where last_login < '2022-10-01';
替代方案：
- 若需清空表（保留表结构），用truncate table 表名;，效率高于delete（但无法回滚，且会重置自增主键）。
- 重要数据建议用 “逻辑删除”（添加is_deleted字段标记，而非物理删除）。
四、高级操作：索引与约束
1. 索引（提升查询效率）
-- 创建索引（常用于查询频繁的字段）
create index 索引名 on 表名(字段名);
-- 示例：给users表的username字段创建索引
create index idx_username on users(username);
-- 查看表中的索引
show index from 表名;
-- 删除索引
drop index 索引名 on 表名;
适用场景：
- 频繁出现在where、order by、join条件中的字段。
- 不建议给更新频繁或数据重复率高的字段建索引（如性别字段）。
2. 外键约束（关联多张表）
外键用于关联两个表，确保数据一致性（如订单表关联用户表，保证订单所属用户存在）。
-- 示例：创建订单表（orders），关联users表的id字段
create table orders (
id int primary key auto_increment,
order_no varchar(50) not null unique,
user_id int not null,
total_price decimal(10,2) not null,
-- 外键约束：user_id关联users表的id
foreign key (user_id) references users(id) on delete cascade
);
外键行为：
- on delete cascade：当关联的用户被删除时，该用户的所有订单也自动删除。
- on delete set null：当用户被删除时，订单的user_id设为 null（需user_id允许 null）。
五、常见错误与解决方案
- 中文乱码
原因：数据库或表的字符集不是utf8mb4。
解决：创建时指定字符集，已有表可修改：
alter database 数据库名 character set utf8mb4 collate utf8mb4_general_ci;
alter table 表名 convert to character set utf8mb4 collate utf8mb4_general_ci;
- 主键冲突
错误提示：Duplicate entry '1' for key 'PRIMARY'
原因：插入的主键值已存在，或自增主键序列异常。
解决：重置自增序列：
alter table 表名 auto_increment = 1; -- 从1开始重新增长
- 删除 / 更新受外键约束的记录
错误提示：Cannot delete or update a parent row: a foreign key constraint fails
原因：该记录被其他表的外键关联。
解决：先删除关联表的记录，或修改外键行为为cascade。
总结：MySQL 操作规范
- 命名规范：数据库名、表名、字段名用小写字母，下划线分隔（如user_info），避免使用关键字（如order、table）。
- 安全操作：执行drop、delete、update时先备份数据，务必加where条件。
- 性能优化：查询时避免select *，大表加索引，批量操作优先用insert ... values (...)。
掌握这些基础命令后，可进一步学习join多表查询、group by分组统计等进阶操作，逐步提升 MySQL 使用能力。
你必须 登录 才能发表评论.