以下详细介绍SQL基础命令，包含连接、数据库操作、表操作、数据操作、查询优化等全流程，仅供参考：
一、数据库连接管理
1. 命令行连接
mysql -u [用户名] -p[密码] -h [主机地址] [数据库名]- 作用：建立客户端与MySQL服务器的通信连接
- 参数：
- -u：指定认证用户名（必需）
- -p：密码输入提示符（交互式更安全）
- -h：远程服务器IP（默认localhost）
- 末尾数据库名：直接进入指定库
- 返回值：
- 成功：显示mysql>命令行提示符
- 失败：返回错误代码（如ERROR 1045密码错误）
安全连接示例：
# 安全连接（回车后输入密码）mysql -u admin -p -h 192.168.1.100# 连接并直接使用shop_db数据库mysql -u root -p shop_db2. 连接工具操作（Navicat）
- 图形界面填写四要素：
- graph LR
A[服务器地址] --> B[端口3306]
C[用户名] --> D[密码] - 连接测试：通过"Test Connection"验证配置
二、数据库层级操作
1. 创建数据库
CREATE DATAbase [IF NOT EXISTS] 数据库名[CHARACTER SET 字符集] [COLLATE 排序规则];- 作用：在数据库系统中划分独立存储空间
- 关键参数：
- IF NOT EXISTS：避免重复创建报错
- utf8mb4：推荐字符集（支持emoji）
- utf8mb4_unicode_ci：通用排序规则
- 物理表现：在data目录创建同名文件夹
示例：
-- 创建电商数据库CREATE DATAbase IF NOT EXISTS shop_dbCHARACTER SET utf8mb4COLLATE utf8mb4_unicode_ci;2. 查看与切换数据库
SHOW DATAbaseS; -- 列出所有数据库USE 数据库名; -- 设置当前工作库- 权限控制：用户只能看到有权限的数据库
- 当前库标识：Database changed提示
操作演示：
-- 查看所有数据库（含系统库）mysql> SHOW DATAbaseS;+--------------------+| Database |+--------------------+| information_schema || shop_db || mysql |+--------------------+-- 进入shop_db库mysql> USE shop_db;Database changed三、数据表操作
1. 创建表结构
CREATE TABLE 表名 ( 列名1 数据类型 [约束条件] [COMMENT '注释'], 列名2 数据类型 [约束条件], ...) [ENGINE=存储引擎] [CHARSET=字符集];核心元素：
元素类型 | 常见值 | 作用说明 |
数据类型 | INT/VARCHAr(255)/DATETIME | 定义列存储格式 |
主键约束 | PRIMARY KEY | 唯一标识行（不可重复） |
自增属性 | AUTO_INCREMENT | 自动生成唯一ID |
唯一约束 | UNIQUE | 禁止重复值（如身份证号） |
枚举类型 | ENUM('男','女') | 限定取值范围 |
默认值 | DEFAULT CURRENT_TIMESTAMP | 自动填充默认值 |
建表示例：
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', username VARCHAr(50) NOT NULL UNIQUE COMMENT '用户名', email VARCHAr(100) NOT NULL COMMENT '邮箱', gender ENUM('男','女') NOT NULL COMMENT '性别', reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间') ENGINE=InnoDB CHARSET=utf8mb4;2. 表结构维护
添加字段：
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [FIRST|AFTER 列名];- 定位参数：
- FIRST：插入到首列
- AFTER 列名：插入到指定列后
修改字段：
-- 修改数据类型ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;-- 重命名字段ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 数据类型;示例操作：
-- 添加手机号字段（在email后）ALTER TABLE users ADD COLUMN mobile CHAr(11) COMMENT '手机号' AFTER email;-- 修改用户名字段长度ALTER TABLE users MODIFY COLUMN username VARCHAr(70);四、数据操作核心命令
1. 插入数据
-- 全字段插入INSERT INTO 表名 VALUES (值1, 值2, ...);-- 指定字段插入INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);-- 批量插入INSERT INTO 表名 (列1, 列2) VALUES (值1a, 值2a), (值1b, 值2b);注意事项：
- 自增字段无需插入
- NOT NULL字段必须提供值
- 枚举值必须从选项中选取
实战示例：
-- 单条插入INSERT INTO users (username, email, gender) VALUES ('jack', 'jack@example.com', '男');-- 批量插入商品数据INSERT INTO products (name, price, stock) VALUES ('iPhone 14', 5999, 100), ('MacBook Pro', 12999, 50), ('AirPods Pro', 1499, 200);2. 查询数据
SELECt [DISTINCT] 列1, 列2 AS 别名 FROM 表名 [WHERe 条件] [GROUP BY 分组列] [HAVINg 分组条件] [ORDER BY 排序列 [ASC|DESC]] [LIMIT 行数];核心子句：
子句 | 作用 | 常用运算符 |
WHERe | 行级过滤 | =, >, <, LIKE, IN, BETWEEN |
GROUP BY | 分组统计 | 常配合COUNT/SUM/AVG使用 |
HAVINg | 分组后过滤 | 类似WHERe但作用于组 |
ORDER BY | 结果排序 | ASC升序/DESC降序 |
LIMIT | 限制返回行数 | 实现分页功能 |
查询示例：
-- 基础查询SELECt id, username, email FROM users;-- 条件过滤SELECt * FROM products WHERe price > 5000 AND stock < 100;-- 模糊搜索SELECt * FROM users WHERe username LIKE '张%';-- 分页查询SELECt * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 20;3. 更新数据
UPDATe 表名 SET 列1=新值1, 列2=新值2 [WHERe 条件] [ORDER BY 列] [LIMIT 行数];关键要点：
- 必须加WHERe条件：避免全表更新
- 原子性保证：单条UPDATE是原子操作
- 性能影响：大表更新建议分批执行
安全更新示例：
-- 修改特定用户邮箱UPDATE users SET email = 'new@example.com' WHERe id = 1001;-- 商品涨价10%（仅库存>50的）UPDATE products SET price = price * 1.1 WHERe stock > 50;4. 删除数据
DELETE FROM 表名 [WHERe 条件] [ORDER BY 列] [LIMIT 行数];与DROp的区别：
命令 | 作用范围 | 是否可恢复 |
DELETE | 删除数据行 | binlog可恢复 |
DROP | 删除整个表结构 | 不可恢复 |
安全删除示例：
-- 删除30天前的日志DELETE FROM access_log WHERe access_time < NOW() - INTERVAL 30 DAY;-- 清空表数据（更高效）TRUNCATE TABLE temp_data;五、高级查询技巧
1. 多表连接查询
连接类型对比：
连接类型 | 语法 | 结果说明 |
内连接 | INNER JOIN / JOIN | 仅返回匹配行 |
左连接 | LEFT JOIN | 左表全量+右表匹配 |
右连接 | RIGHT JOIN | 右表全量+左表匹配 |
全外连接 | MySQL需UNIOn实现 | 返回所有匹配和不匹配行 |
连接查询示例：
-- 查询订单及用户信息SELECt o.order_id, o.amount, u.username, u.emailFROM orders oJOIN users u ON o.user_id = u.idWHERe o.status = 'PAID';-- 左连接保留未分类商品SELECt p.product_name, c.category_nameFROM products pLEFT JOIN categories c ON p.cat_id = c.cat_id;2. 分组聚合统计
SELECt 分组列, COUNT(*) AS 总数, AVG(数值列) AS 平均值, MAX(数值列) AS 最大值FROM 表名GROUP BY 分组列HAVINg 统计条件;执行顺序：
graph LRA[FROM] --> B[WHERe] B --> C[GROUP BY]C --> D[HAVINg] D --> E[SELECt]统计示例：
-- 按性别统计用户数SELECT gender AS 性别, COUNT(*) AS 用户数FROM usersGROUP BY gender;-- 月销售额>10万的月份SELECt DATE_FORMAT(order_date, '%Y-%m') AS 月份, SUM(amount) AS 总销售额FROM ordersGROUP BY 月份HAVINg 总销售额 > 100000;六、权限管理与数据安全
1. 用户权限控制
-- 创建用户CREATE USER '用户名'@'访问来源' IDENTIFIED BY '密码';-- 授权GRANT 权限列表 ON 数据库.表 TO '用户'@'来源';-- 撤销权限REVOKE 权限 ON 数据库.表 FROM '用户'@'来源';-- 删除用户DROp USER '用户名'@'访问来源';权限类型：
- 数据操作：SELECT, INSERT, UPDATE, DELETE
- 结构管理：CREATE, ALTER, DROP
- 管理权限：GRANT OPTION, SUPER
权限配置示例：
-- 创建应用用户（允许内网访问）CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';-- 授予shop_db的读写权限GRANT SELECT, INSERT, UPDATE ON shop_db.* TO 'app_user'@'192.168.1.%';-- 查看用户权限SHOW GRANTS FOR 'app_user'@'192.168.1.%';2. 数据备份恢复
逻辑备份（mysqldump）：
# 全库备份mysqldump -u root -p --databases shop_db > shop_db_full.sql# 单表备份mysqldump -u root -p shop_db products > products.sql物理备份方案：
- 企业级：MySQL Enterprise Backup
- 开源方案：Percona XtraBackup
恢复数据：
# 命令行恢复mysql -u root -p shop_db < backup.sql# 登录后恢复mysql> source /path/to/backup.sql七、实战案例：电商系统操作
1. 数据准备
-- 创建商品表CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAr(100) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, category ENUM('电子','服装','食品'));-- 插入示例数据INSERT INTO products (name, price, category) VALUES ('华为P50', 4999.00, '电子'), ('耐克运动鞋', 899.00, '服装'), ('三只松鼠礼盒', 198.00, '食品');2. 常用操作示例
商品管理：
-- 上架新商品INSERT INTO products (name, price, category)VALUES ('iPad Pro', 6799.00, '电子');-- 调整价格UPDATE products SET price = price * 0.9 WHERe category = '电子';-- 下架无库存商品DELETE FROM products WHERe stock = 0;数据查询：
-- 按类别统计平均价格SELECt category AS 类别, AVG(price) AS 平均价格, COUNT(*) AS 商品数FROM productsGROUP BY category;-- 查询电子类商品（价格降序）SELECt * FROM products WHERe category = '电子'ORDER BY price DESC;八、SQL操作避坑指南
1. 常见错误处理
错误代码 | 原因 | 解决方案 |
1064 | SQL语法错误 | 检查引号/逗号/关键字拼写 |
1146 | 表不存在 | 检查表名大小写 |
1054 | 列不存在 | 检查列名或别名 |
1292 | 数据类型转换错误 | 检查数据格式是否匹配 |
2. 性能优化建议
- 索引策略：-- 低效 ❌
SELECt * FROM users WHERe YEAR(reg_date) = 2023;
-- 高效 ✅
SELECt * FROM users
WHERe reg_date BETWEEN '2023-01-01' AND '2023-12-31'; - 在WHERe/JOIN条件列创建索引
- 避免在索引列使用函数
- 避免全表扫描：
- 使用LIMIT分页
- 避免SELECT *只取必要字段
- 批量操作优化：-- 批量插入优于单条循环
INSERT INTO logs (message) VALUES ('msg1'),('msg2'),('msg3');
-- 批量更新减少连接次数
UPDATE products
SET price = CASE product_id
WHEN 1001 THEN 99.9
WHEN 1002 THEN 129.9
END
WHERe product_id IN (1001,1002);
九、数据类型对照表
类型分类 | MySQL类型 | 存储内容 | 示例值 |
整数 | TINYINT | 小整数（-128~127） | 100 |
INT | 标准整数（±21亿） | 2000000 | |
小数 | DECIMAL(10,2) | 精确小数 | 999999.99 |
FLOAT | 单精度浮点 | 3.1415 | |
字符串 | CHAr(10) | 定长字符串 | 'ABC'（补空格） |
VARCHAr(255) | 变长字符串 | '可变长度文本' | |
日期时间 | DATE | 日期 | '2023-07-05' |
DATETIME | 日期+时间 | '2023-07-05 14:30:00' | |
二进制 | BLOB | 二进制大对象 | 图片/文件 |
特殊类型 | ENUM('Y','N') | 枚举值 | 'Y' |
JSON | JSON文档 | '{"name":"Jack"}' |
总结与学习路径
SQL操作四阶段：
- 基础操作：连接→建库→建表→增删改查
- 结构设计：数据类型选择→约束优化→索引策略
- 高级查询：多表连接→分组聚合→子查询优化
- 管理维护：用户权限→备份恢复→性能调优
推荐学习路径：
- 熟练掌握单表CRUD操作
- 理解JOIN和GROUP BY的工作原理
- 掌握EXPLAIN分析查询执行计划
- 在生产环境前充分测试SQL语句
提示：所有SQL操作建议先在测试环境验证，使用事务包裹关键操作（START TRANSACTION / ROLLBACK），并定期进行数据库备份。
