19
0
0
数据库基础03-数据库SQL基础
数据库 SQL 基础：库表操作与增删改）
目录
- 进入客户端与基本约定
- 数据库的增删查
- 数据类型
- 建表：CREATE TABLE
- 查看表与表结构
- 修改表：ALTER TABLE
- 删除表：DROP 与 TRUNCATE
- 插入数据：INSERT
- 查询：SELECT 与 WHERE
- 常用条件与运算符
- 排序与分页：ORDER BY / LIMIT
- 更新数据：UPDATE
- 删除数据：DELETE
- 总结
- 参考资料
进入客户端与基本约定
装好服务后，用客户端命令 mysql（或 mariadb）连接数据库服务器。最基础的用法：
# 用 root 连接本机（RHEL 系走密码，Debian 系可用 sudo mysql）
$ mysql -u root -p
# 指定主机与端口连接（走 TCP）
$ mysql -h 192.168.1.21 -P 3306 -u app -p
-u：用户名；-p：提示输入密码（把密码直接跟在-p后也可以，但不安全，不推荐写进命令行）；-h：主机地址（默认 localhost）；-P：端口（默认 3306）。
进入后提示符变成 MariaDB [(none)]>，就可以输入 SQL 语句了。每条 SQL 以分号 ; 结尾回车执行；输入 exit 或 quit 退出。
约定：本文用 mysql> 表示已进入客户端；SQL 关键字不区分大小写（select/SELECT 都行），但表名/库名在 Linux 上默认区分大小写、字段名不区分。
一、数据库的增删查
运维第一步通常是建库。数据库操作就几个命令：
-- 查看所有数据库
SHOW DATABASES;
-- 创建数据库（指定 utf8mb4 字符集，推荐）
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 切换使用某个库（后续语句都在这个库里执行）
USE shop;
-- 删除数据库（危险，会连同其中所有表和数据一起删除）
DROP DATABASE shop;
为什么用 utf8mb4？ MySQL/MariaDB 里 utf8 只是 utf8mb3（最多 3 字节），存不下 emoji 等 4 字节字符；utf8mb4 才是完整的 UTF-8。现代建库一律用 utf8mb4，避免中文/emoji 乱码。
二、数据类型
建表前要决定每个字段的类型。常用三类：
| 类别 | 类型 | 说明 | 示例值 |
|---|---|---|---|
| 数字 | INT |
整数（约 ±21 亿） | 42 |
| 数字 | BIGINT |
大整数（超过 INT 范围时用） | 9999999999 |
| 数字 | DECIMAL(10,2) |
精确小数（金额必须用它，不要用浮点） | 1999.99 |
| 数字 | FLOAT / DOUBLE |
浮点数（有精度损失，不用于金额） | 3.14 |
| 字符串 | VARCHAR(n) |
变长字符串，n 为最大字符数 | 'alice' |
| 字符串 | TEXT |
长文本（如文章正文） | '很长...' |
| 字符串 | CHAR(n) |
定长字符串（长度固定） | 'CN' |
| 日期时间 | DATE |
日期 YYYY-MM-DD |
2024-01-01 |
| 日期时间 | DATETIME |
日期时间 | 2024-01-01 10:30:00 |
| 日期时间 | TIMESTAMP |
时间戳，随 UTC 时区自动换算 | 2024-01-01 10:30:00 |
| 布尔 | BOOLEAN |
本质是 TINYINT(1)，0/1 |
1 |
金额为什么不能用 FLOAT？ 浮点数在计算机里是近似存储，0.1+0.2 可能不等于 0.3，会造成金额误差。涉及钱的字段一律用 DECIMAL，这是金额字段的基本要求。
三、建表：CREATE TABLE
建表就是定义「表有哪些字段、各字段什么类型、有哪些约束」。示例：
USE shop;
CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID，自增主键',
username VARCHAR(50) NOT NULL COMMENT '用户名，非空',
email VARCHAR(100) UNIQUE COMMENT '邮箱，唯一',
age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄，默认0',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
逐行解释几个点：
AUTO_INCREMENT：自增，每次插入自动 +1，常配主键；PRIMARY KEY：主键（唯一且非空）；NOT NULL：不允许为空；UNIQUE：值唯一；DEFAULT 0：不填时用默认值；CURRENT_TIMESTAMP：取当前时间作为默认值；ENGINE=InnoDB：指定存储引擎，InnoDB 是默认且支持事务、行锁，一般都用它；COMMENT：字段注释，便于维护。
四、查看表与表结构
-- 查看当前库有哪些表
SHOW TABLES;
-- 查看某张表的详细结构（列、类型、键）
DESCRIBE user;
-- 或简写
DESC user;
-- 查看建表语句（含引擎、字符集）
SHOW CREATE TABLE user\G
\G 结尾会以纵向、可读的方式显示结果，适合字段多时查看。
五、修改表：ALTER TABLE
表建好后要改结构（加列、改列、删列、加索引等）用 ALTER TABLE：
-- 新增一列
ALTER TABLE user ADD COLUMN phone VARCHAR(20);
-- 修改某列的类型
ALTER TABLE user MODIFY COLUMN username VARCHAR(80);
-- 重命名列
ALTER TABLE user CHANGE COLUMN phone mobile VARCHAR(20);
-- 删除一列
ALTER TABLE user DROP COLUMN mobile;
-- 给字段加索引
ALTER TABLE user ADD INDEX idx_age (age);
注意：ALTER TABLE 会锁表并重建，在数据量很大的表上执行可能耗时较长，生产环境改大表结构要谨慎（评估锁表影响、错峰执行）。
六、删除表：DROP 与 TRUNCATE
-- 删除整张表（连同数据，危险）
DROP TABLE user;
-- 清空表数据但保留表结构
TRUNCATE TABLE user;
DROP 删表，TRUNCATE 清数据但留空表，DELETE 按条件删行（见第十二章）——三者别混用。
七、插入数据：INSERT
数据操作是 SQL 的四大核心动词：增（INSERT）删（DELETE）改（UPDATE）查（SELECT），合称 CRUD。下面以 user 表为例演示。
USE shop;
-- 插入一行（指定列）
INSERT INTO user (username, email, age) VALUES ('alice', 'a@ex.com', 25);
-- 一次插入多行
INSERT INTO user (username, email, age) VALUES
('bob', 'b@ex.com', 30),
('carol', 'c@ex.com', 28);
不指定的列会使用默认值（如 age 用 DEFAULT 0、created_at 自动取当前时间），id 自动递增。
八、查询：SELECT 与 WHERE
SELECT 是使用频率最高的语句，用来取数据：
-- 取所有列所有行
SELECT * FROM user;
-- 只取某些列
SELECT id, username, email FROM user;
-- 加过滤条件（WHERE）
SELECT * FROM user WHERE age >= 28;
-- 精确匹配
SELECT * FROM user WHERE username = 'alice';
-- 别名（AS）：让列名更可读
SELECT username AS 用户名, email AS 邮箱 FROM user;
生产规范：正式环境查询尽量不要 SELECT *，只取需要的列，避免传输无用数据、拖慢查询。
九、常用条件与运算符
WHERE 后面可以组合各种条件：
| 写法 | 含义 | 示例 |
|---|---|---|
= / != / <> |
等于 / 不等于 | WHERE email = 'a@ex.com' |
> < >= <= |
比较大小 | WHERE age >= 18 |
AND / OR |
逻辑与 / 或 | WHERE age > 20 AND age < 30 |
NOT |
取反 | WHERE NOT age = 25 |
IN (...) |
是否在集合内 | WHERE age IN (25, 30) |
BETWEEN a AND b |
闭区间 | WHERE age BETWEEN 20 AND 30 |
LIKE 'x%' |
模糊匹配（% 任意多字符，_ 单字符） |
WHERE username LIKE 'a%' |
IS NULL / IS NOT NULL |
是否为空（不能写 = NULL） |
WHERE email IS NULL |
DISTINCT |
去重 | SELECT DISTINCT age FROM user |
-- 组合示例：年龄 20~30 且用户名以 a 开头的用户
SELECT id, username FROM user
WHERE age BETWEEN 20 AND 30
AND username LIKE 'a%';
易错点：判断空值必须用 IS NULL，写成 WHERE email = NULL 永远查不到结果（NULL 与任何值比较结果都是未知，而不是 true 或 false）。
十、排序与分页：ORDER BY / LIMIT
-- 按年龄升序（默认 ASC 升序）
SELECT * FROM user ORDER BY age;
-- 降序
SELECT * FROM user ORDER BY age DESC;
-- 多列排序：先按年龄降序，再按 id 升序
SELECT * FROM user ORDER BY age DESC, id ASC;
-- 取前 2 条
SELECT * FROM user ORDER BY id LIMIT 2;
-- 分页：跳过 2 条取 2 条（第 2 页）
SELECT * FROM user ORDER BY id LIMIT 2 OFFSET 2;
-- 或简写 LIMIT 偏移量, 条数
SELECT * FROM user ORDER BY id LIMIT 2, 2;
分页公式：第 N 页每页 M 条，则 LIMIT M OFFSET (N-1)*M。分页必须配 ORDER BY，否则顺序不稳定。
十一、更新数据：UPDATE
-- 修改某行某列（务必带 WHERE，否则会改所有行！）
UPDATE user SET age = 26 WHERE username = 'alice';
-- 一次改多个字段
UPDATE user SET age = 26, email = 'alice@ex.com' WHERE id = 1;
UPDATE / DELETE 不带 WHERE 会作用到整张表，这是最危险的误操作。执行修改前先 SELECT 确认 WHERE 条件选中的正是目标行。
十二、删除数据：DELETE
-- 删除满足条件的行
DELETE FROM user WHERE id = 3;
-- 删除所有行（保留表结构）
DELETE FROM user;
-- 想立刻释放空间/重置自增值，用 TRUNCATE（不可按条件）
TRUNCATE TABLE user;
DELETE 支持 WHERE 精确删行，TRUNCATE 只能整表清空且不可回滚（InnoDB 下 DELETE 在事务内可回滚）。生产删数据前务必先备份。
十三、总结
- 建库建表：
CREATE DATABASE（用 utf8mb4）、CREATE TABLE（定类型、约束、主键）；ALTER TABLE改结构，DROP/TRUNCATE删表/清表； - 数据类型：数字（金额用
DECIMAL）、字符串（VARCHAR/TEXT）、日期时间、布尔； - 增删改查：
INSERT增、SELECT查（配WHERE/ORDER BY/LIMIT）、UPDATE改、DELETE删； - 易错：空值用
IS NULL；UPDATE/DELETE不带WHERE会动全表；正式环境避免SELECT *。
SQL 的进阶内容——索引、多表关联、聚合、事务——在《数据库 SQL 进阶》中展开。
参考资料
- MySQL 官方手册（SQL 语句总览）：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
- MySQL 官方手册（数据类型）：https://dev.mysql.com/doc/refman/8.0/en/data-types.html
- MySQL 官方手册（CREATE TABLE）：https://dev.mysql.com/doc/refman/8.0/en/create-table.html
- 菜鸟教程·SQL 教程：https://www.runoob.com/sql/sql-tutorial.html
- 菜鸟教程·SQL SELECT：https://www.runoob.com/sql/sql-select.html
- 菜鸟教程·SQL INSERT：https://www.runoob.com/sql/sql-insert.html
- 菜鸟教程·SQL UPDATE：https://www.runoob.com/sql/sql-update.html
- 菜鸟教程·SQL DELETE：https://www.runoob.com/sql/sql-delete.html
- 菜鸟教程·SQL 建表：https://www.runoob.com/sql/sql-create-table.html
- 菜鸟教程·SQL WHERE：https://www.runoob.com/sql/sql-where.html