linux_数据库_3 – Learning DevOps

1️⃣ SQL 语言是什么？

SQL（Structured Query Language）就是用来和数据库“说话”的语言。你想让数据库帮你存数据、查数据、改数据，都要通过 SQL 语句。

SQL 语句根据功能分成四大类：

分类	中文名	作用	常用命令	谁常用
DDL	数据定义语言	定义数据库的结构（比如建库、建表）	CREATE, ALTER, DROP	运维、开发
DML	数据操作语言	操作表中的数据（增删改）	INSERT, DELETE, UPDATE	开发
DQL	数据查询语言	查询表中的数据	SELECT	开发
DCL	数据控制语言	控制权限和事务	GRANT, REVOKE, COMMIT, ROLLBACK	运维

简单记：DDL 管结构，DML 管数据，DQL 查数据，DCL 管权限。

2️⃣ 字符编码与校对规则

2.1 为什么会有乱码？

计算机只存 0 和 1，所以每个字符都要对应一个二进制编码。不同国家有自己的编码标准：

ASCII：美国标准，用 1 个字节（256 种组合），只能表示英文字母、数字、符号。
ANSI：各国扩展的 ASCII，比如中国用 GB2312（简体）、GBK（扩展），用 2 个字节表示汉字。
Unicode：统一编码，用 4 个字节表示所有字符，但太浪费空间。
UTF-8：可变长编码，英文 1 字节，中文 3 字节，表情 4 字节，是国际通用编码。

2.2 MySQL 中的字符集

MySQL 中常用的字符集：

utf8：最多 3 字节，支持大部分中文，但不支持 emoji 表情。
utf8mb4：最多 4 字节，支持 emoji，是 MySQL 8.0 的默认字符集。

企业里推荐用 utf8mb4，避免表情符号乱码。

2.3 查看和设置字符集

-- 查看所有可用字符集
SHOW CHARSET;

-- 查看当前默认字符集设置
SHOW VARIABLES LIKE '%character%';

修改字符集的方法：

临时修改当前会话（重启失效）：

  SET NAMES utf8mb4;

永久修改配置文件（my.cnf）：

  [mysqld]
  character-set-server=utf8mb4
  [client]
  default-character-set=utf8mb4

建库时指定：

  CREATE DATABASE mydb CHARSET utf8mb4;

建表时指定：

  CREATE TABLE t1 (id INT) CHARSET utf8mb4;

修改已有表的字符集（只影响后续数据）：

  ALTER TABLE t1 CHARSET utf8mb4;

注意：要完整转换已有数据的字符集，最好用导出导入的方式。

2.4 校对规则（排序规则）

校对规则决定了字符比较时是否区分大小写、重音等。常见后缀：

_ci：不区分大小写（case insensitive）
_cs：区分大小写
_bin：按二进制比较（最严格）

常用校对规则：

utf8mb4_general_ci：速度较快，适合英文
utf8mb4_unicode_ci：更准确，适合多语言
utf8mb4_bin：区分大小写，二进制存储

示例：创建表时指定校对规则

CREATE TABLE t1 (name CHAR(10)) CHARSET utf8mb4 COLLATE utf8mb4_bin;

插入 'a' 和 'A'，用不同校对规则查询结果不同：

若用 _ci，WHERE name='a' 会同时查出 'a' 和 'A'。
若用 _bin，只会查出 'a'。

3️⃣ 数据类型

MySQL 中定义表字段时必须指定数据类型，就像 Excel 的列要选“文本”还是“数字”。

3.1 数值类型

类型	占用字节	范围（有符号）	用途
`TINYINT`	1	-128 ~ 127	年龄、状态码
`INT`	4	-21亿 ~ 21亿	常规整数
`BIGINT`	8	超大	订单号、ID
`FLOAT`	4	单精度	科学计算
`DOUBLE`	8	双精度	科学计算
`DECIMAL(M,D)`	变长	精确小数	金额（如 DECIMAL(10,2)）

注意：金额一定要用 DECIMAL，避免浮点误差。

3.2 字符串类型

类型	说明	最大长度	特点
`CHAR(n)`	定长字符串	255 字符	长度固定，效率高，浪费空间
`VARCHAR(n)`	变长字符串	65535 字节	节省空间，但效率略低
`TEXT`	大文本	64KB	存文章、备注
`ENUM`	枚举	65535 个值	单选，如性别('男','女')
`SET`	集合	64 个值	多选，如爱好('篮球','足球')

选择技巧：长度固定且短用 CHAR，长度变化用 VARCHAR，大段文字用 TEXT。

3.3 日期时间类型

类型	格式	范围	占用
`DATE`	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	3 字节
`TIME`	HH:MM:SS	-838:59:59 ~ 838:59:59	3 字节
`DATETIME`	YYYY-MM-DD HH:MM:SS	同上	8 字节
`TIMESTAMP`	同上	1970-01-01 ~ 2038-01-19	4 字节

注意：TIMESTAMP 受时区影响，适合记录最后修改时间。

3.4 其他类型

二进制：BLOB 存图片、文件等二进制数据。
JSON：MySQL 5.7 起支持，可以直接存 JSON 格式数据。

4️⃣ 约束与属性

约束用来限制字段的值，保证数据正确性。

4.1 常见约束

约束	含义	例子
`PRIMARY KEY`	主键，非空且唯一	`id INT PRIMARY KEY`
`UNIQUE`	唯一，值不能重复	`email VARCHAR(50) UNIQUE`
`NOT NULL`	不能为空	`name VARCHAR(20) NOT NULL`
`FOREIGN KEY`	外键，关联其他表	`FOREIGN KEY (dept_id) REFERENCES dept(id)`

4.2 常见属性

属性	含义	例子
`AUTO_INCREMENT`	自动增长	`id INT AUTO_INCREMENT`
`DEFAULT`	默认值	`status INT DEFAULT 1`
`COMMENT`	注释	`name VARCHAR(20) COMMENT '姓名'`
`UNSIGNED`	无符号（非负）	`age TINYINT UNSIGNED`

5️⃣ 外键（FOREIGN KEY）

5.1 什么是外键？

外键用来建立两张表之间的关联。比如：

班级表（class）：id, name
学生表（student）：id, name, class_id

学生表的 class_id 应该引用班级表的 id，这样就能知道学生属于哪个班。

5.2 创建外键

-- 父表（主表）
CREATE TABLE class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);

-- 子表（从表）
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES class(id)
);

5.3 外键的作用

保护数据完整性：如果插入学生时填了一个不存在的班级ID，会报错。
防止误删：如果班级还被学生引用，就不能直接删除该班级。

6️⃣ SQL 模式（sql_mode）

SQL 模式决定了 MySQL 对 SQL 语句的严格程度。可以通过 SELECT @@sql_mode; 查看当前模式。

常见模式：

模式	作用
`ONLY_FULL_GROUP_BY`	使用 GROUP BY 时，SELECT 的列必须在 GROUP BY 中或使用聚合函数
`STRICT_TRANS_TABLES`	对事务表启用严格模式，数据插入/更新出错则回滚
`NO_ZERO_IN_DATE`	不允许日期中的月或日为 0
`NO_ZERO_DATE`	不允许插入 '0000-00-00'
`ERROR_FOR_DIVISION_BY_ZERO`	除零时报错，否则返回 NULL
`NO_ENGINE_SUBSTITUTION`	指定引擎不可用时抛出错误，不自动替换

生产环境建议保持默认严格模式，避免脏数据。

临时修改当前会话：

SET sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';

永久修改可在配置文件 [mysqld] 下添加 sql_mode=...。

7️⃣ DDL 语句（数据定义语言）

7.1 数据库操作

创建数据库

CREATE DATABASE 库名;
CREATE DATABASE 库名 CHARSET utf8mb4;

查看数据库

SHOW DATABASES;                     -- 所有库
SHOW CREATE DATABASE 库名;           -- 查看建库语句

修改数据库

ALTER DATABASE 库名 CHARSET utf8mb4;

删除数据库

DROP DATABASE 库名;    -- 危险！生产环境慎用

切换数据库

USE 库名;
SELECT DATABASE();     -- 查看当前所在库

7.2 表操作

创建表

CREATE TABLE 表名 (
    字段名1 数据类型 约束 属性,
    字段名2 数据类型 约束 属性,
    ...
);

示例：

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    gender ENUM('男','女') NOT NULL DEFAULT '男'
) CHARSET=utf8mb4;

查看表

SHOW TABLES;                         -- 当前库所有表
DESC 表名;                            -- 查看表结构
SHOW CREATE TABLE 表名;                -- 查看建表语句

修改表

修改表名：

  RENAME TABLE 旧名 TO 新名;
  ALTER TABLE 旧名 RENAME 新名;

添加字段：

  ALTER TABLE 表名 ADD 字段名 类型 约束 [FIRST|AFTER 某字段];

删除字段：

  ALTER TABLE 表名 DROP 字段名;

修改字段类型/属性：

  ALTER TABLE 表名 MODIFY 字段名 新类型 新约束;

修改字段名：

  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新类型 新约束;

删除表

DROP TABLE 表名;        -- 连结构带数据一起删
TRUNCATE TABLE 表名;    -- 只删数据，保留结构（自增重置）

7.3 规范建议

库名、表名、字段名用小写字母+下划线，不要用数字开头。
创建时明确指定字符集，避免继承默认值导致乱码。
每张表都应有主键，最好是自增ID。
字段尽量设为 NOT NULL，并给默认值。
删除操作要严格审核，普通用户不应有DROP权限。
修改表结构尽量在业务低峰期进行，大表修改可用 pt-osc 等工具。

8️⃣ DML 语句（数据操作语言）

8.1 插入数据（INSERT）

-- 全字段插入（按表字段顺序）
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 指定字段插入
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);

-- 插入多行
INSERT INTO 表名 VALUES (值1, 值2), (值3, 值4), ...;

示例：

INSERT INTO student (name, age) VALUES ('张三', 20);

8.2 更新数据（UPDATE）

UPDATE 表名 SET 字段1=新值, 字段2=新值 WHERE 条件;

一定要加 WHERE！ 不加会更新整张表。

安全更新模式：可以开启 sql_safe_updates，防止不带 WHERE 的更新/删除。

SET sql_safe_updates = 1;   -- 启用安全模式

8.3 删除数据（DELETE）

DELETE FROM 表名 WHERE 条件;   -- 逐行删除，可回滚
TRUNCATE TABLE 表名;          -- 清空表，效率高，不可回滚

8.4 伪删除（软删除）

实际业务中，为了防止误删，常用“伪删除”：加一个状态字段。

ALTER TABLE student ADD COLUMN is_deleted TINYINT DEFAULT 0;  -- 0:未删,1:已删
-- “删除”时改为更新状态
UPDATE student SET is_deleted=1 WHERE id=1;
-- 查询时只查未删的
SELECT * FROM student WHERE is_deleted=0;

9️⃣ DQL 查询（数据查询语言）

9.1 基础查询

SELECT * FROM 表名;                    -- 所有列
SELECT 字段1, 字段2 FROM 表名;          -- 指定列
SELECT DISTINCT 字段 FROM 表名;         -- 去重
SELECT 字段 AS 别名 FROM 表名;           -- 列别名

9.2 条件查询（WHERE）

SELECT * FROM 表名 WHERE 条件;

常用条件：

比较：=, >, <, >=, <=, != 或 <>
范围：BETWEEN 小值 AND 大值
集合：IN (值1, 值2)
模糊：LIKE '%张%'（% 代表任意多个字符，_ 代表一个字符）
空值：IS NULL, IS NOT NULL
逻辑：AND, OR, NOT

9.3 聚合函数

函数	作用
`COUNT(*)`	计数
`SUM(字段)`	求和
`AVG(字段)`	平均
`MAX(字段)`	最大值
`MIN(字段)`	最小值
`GROUP_CONCAT(字段)`	将同一组的多个值拼接成一个字符串

9.4 分组查询（GROUP BY）

SELECT 分组字段, 聚合函数 FROM 表名 GROUP BY 分组字段;

示例：统计每个班级的学生人数

SELECT class_id, COUNT(*) FROM student GROUP BY class_id;

9.5 分组后筛选（HAVING）

SELECT 分组字段, 聚合函数 FROM 表名 GROUP BY 分组字段 HAVING 条件;

HAVING 用于过滤分组后的结果，类似 WHERE，但 WHERE 是在分组前过滤。

示例：筛选出学生数大于30的班级

SELECT class_id, COUNT(*) FROM student GROUP BY class_id HAVING COUNT(*) > 30;

9.6 排序（ORDER BY）

SELECT * FROM 表名 ORDER BY 字段 [ASC|DESC];

ASC 升序（默认）
DESC 降序

9.7 分页（LIMIT）

SELECT * FROM 表名 LIMIT 偏移量, 行数;
-- 或
SELECT * FROM 表名 LIMIT 行数 OFFSET 偏移量;

示例：第 1 页（每页 10 条）→ LIMIT 0,10
第 2 页 → LIMIT 10,10

🔟 多表查询

当需要的数据来自多张表时，就要进行多表连接。

10.1 连接方式

笛卡尔积（全连接）

SELECT * FROM 表1, 表2;   -- 结果是两表行数相乘，通常没用

内连接（INNER JOIN）

只返回两表中匹配的行。

SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;
-- 或（老写法）
SELECT * FROM 表1, 表2 WHERE 表1.字段 = 表2.字段;

左外连接（LEFT JOIN）

返回左表全部行，右表没有匹配的则填 NULL。

SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

右外连接（RIGHT JOIN）

返回右表全部行，左表没有匹配的则填 NULL。

SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

10.2 多表查询步骤

确定需要的表。
找到表之间的关联字段。
选择合适的连接方式。
根据需要添加 WHERE、GROUP BY 等。

10.3 示例（学生-课程-成绩）

假设有四张表：

student(sno, sname)
course(cno, cname)
sc(sno, cno, score)
teacher(tno, tname)

查询张三的成绩

SELECT s.sname, c.cname, sc.score
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno
WHERE s.sname = '张三';

查询每位老师所教课程的平均分

SELECT t.tname, c.cname, AVG(sc.score)
FROM teacher t
JOIN course c ON t.tno = c.tno
JOIN sc ON c.cno = sc.cno
GROUP BY t.tno, c.cno;

1️⃣1️⃣ 子查询

子查询就是一个查询嵌套在另一个查询里。

11.1 不相关子查询

子查询独立运行，先执行子查询，再执行外部查询。

-- 查询工资高于平均工资的员工
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

11.2 相关子查询

子查询引用了外部查询的表，对外部查询的每一行都要执行一次子查询。

-- 查询工资高于本部门平均工资的员工
SELECT * FROM emp e1 WHERE sal > (SELECT AVG(sal) FROM emp e2 WHERE e2.deptno = e1.deptno);

11.3 子查询位置

在 WHERE 中：WHERE 字段运算符 (子查询)
在 FROM 中：FROM (子查询) AS 别名
在 SELECT 中：SELECT 字段, (子查询) AS 别名 FROM ...

1️⃣2️⃣ CASE WHEN 条件判断

类似编程中的 if-else。

SELECT
    name,
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS 等级
FROM student;

1️⃣3️⃣ 元数据获取

元数据就是“关于数据的数据”，比如数据库有哪些表、表结构等。

13.1 用 SHOW 命令

SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE 表名;
DESC 表名;
SHOW TABLE STATUS LIKE '表名'\G
SHOW INDEX FROM 表名;
SHOW VARIABLES LIKE '%xx%';
SHOW PROCESSLIST;

13.2 用 information_schema 库

这个库是 MySQL 自带的“数据字典”，里面有很多视图，可以查询各种元数据。

常用视图：

TABLES：所有表的信息
COLUMNS：所有列的信息
STATISTICS：索引信息
KEY_COLUMN_USAGE：约束信息

示例：查询所有非 InnoDB 的表

SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine != 'InnoDB';

示例：生成批量修改表引擎的语句

SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine != 'InnoDB';

1️⃣4️⃣ 常见面试题

14.1 DROP、TRUNCATE、DELETE 区别

命令	删除内容	释放空间	效率	可否回滚	自增影响
DROP	表结构+数据	释放	快	不可	无
TRUNCATE	数据	释放	快	不可	重置
DELETE	数据（逐行）	不释放	慢	可（在事务中）	保留

14.2 如何优化建表语句？

字段类型选合适的、够用的，不要一律 varchar(200)。
每个表都要有主键。
尽量 NOT NULL + DEFAULT。
添加 COMMENT 注释。
引擎用 InnoDB，字符集用 utf8mb4。

🎯 总结

以上是 MySQL 最核心的基础知识，掌握了这些，你就能完成日常的数据库操作了。记住：

DDL 管结构，DML 管数据，DQL 查数据。
字符集用 utf8mb4，避免乱码。
操作数据一定要加 WHERE，防止误操作。
多表查询先找关联，再选连接方式。
元数据用 SHOW 或 information_schema 获取。

如果还有哪里不懂，随时来问！😊

初心忘るべからず