MySQL 基础概念与核心语法详解

mysql -u root

MySQL 数学运算语句

1. 加法： 使用 + 运算符执行加法运算。

   SELECT column1 + column2 AS sum FROM 表名;  -- 执行加法运算后的结果列指定别名为 sum
   SELECT column1 + 10 FROM 表名;              -- 也可以不指定别名
   

2. 减法： 使用 - 运算符执行减法运算。

   SELECT column1 - column2 AS diff FROM 表名;
   

3. 乘法： 使用 * 运算符执行乘法运算。

   SELECT column1 * column2 AS product FROM 表名;
   SELECT column1 * 10 AS product FROM 表名;   -- 数值扩大十倍
   

4. 除法： 使用 / 运算符计算除法。

   SELECT column1 / column2 AS division FROM 表名;
   

5. 取余： 使用 % 运算符计算两个数的余数。

   SELECT column1 % column2 AS remainder FROM 表名;
   

6. 其它运算： POWER() 函数用于计算幂次方，SQRT() 函数用于计算平方根，ABS() 函数用于获取绝对值等等。

MySQL 条件查询

MySQL 中的条件查询是通过使用 WHERE 子句来筛选满足特定条件的数据。

基本语法：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

常见运算符示例：

1. 等于 (=) 运算符：

   SELECT * FROM table_name WHERE column_name = value;
   

2. 不等于 (<> 或 !=) 运算符：

   SELECT * FROM table_name WHERE column_name <> value;
   

3. 大于 (>) 运算符：

   SELECT * FROM table_name WHERE column_name > value;
   

4. 小于 (<) 运算符：

   SELECT * FROM table_name WHERE column_name < value;
   

5. 大于等于 (>=) 运算符：

   SELECT * FROM table_name WHERE column_name >= value;
   

6. 小于等于 (<=) 运算符：

   SELECT * FROM table_name WHERE column_name <= value;
   

7. BETWEEN 运算符：

   -- 第一种方式
   SELECT * FROM table_name WHERE column_name >= value1 AND column_name <= value2;
   -- 第二种方式
   SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
   -- value1 必须小于 value2
   

8. 模糊匹配 (LIKE) 运算符：

   SELECT * FROM table_name WHERE column_name LIKE pattern;
   

   条件查询还可以通过逻辑运算符（例如 AND, OR, NOT）组合多个条件。

9. NULL 运算符：

   SELECT * FROM table_name WHERE column_name IS NULL;
   

   这将返回所有 column_name 列中值为 NULL 的行。

10. IN 运算符： IN 运算符用于在查询中匹配多个值。它允许您指定一个值列表，并检查某个列是否与该列表中的任何值匹配。

    SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
    

    例如：

    SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');
    

    注意：IN 后跟的是具体的值，并不是一个区间。

11. 逻辑运算符优先级： 在 MySQL 中，AND 运算符的优先级高于 OR 运算符。这意味着当查询中同时存在 AND 和 OR 运算符时，AND 运算符的条件会首先进行计算，然后再计算 OR 运算符的条件。

    SELECT * FROM table_name WHERE condition1 AND condition2 OR condition3;
    -- 等价于
    SELECT * FROM table_name WHERE (condition1 AND condition2) OR condition3;
    

    可以使用括号来明确指定优先级。

12. 通配符：

    • % 表示零个或多个字符的任意序列。
      • 以特定字符开头：pattern%
      • 以特定字符结尾：%pattern
      • 包含特定字符：%pattern%
    • _ 表示匹配任意单个字符的位置。

MySQL 排序

在 MySQL 中使用 ORDER BY 子句来对结果进行排序。ORDER BY 子句允许指定一个或多个列作为排序依据，并指定升序（ASC）或降序（DESC）进行排序。

1. 单列排序：

   SELECT * FROM table_name ORDER BY column_name ASC;
   

2. 多列排序：

   SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;
   

3. 使用别名排序：

   SELECT column_name AS alias_name FROM table_name ORDER BY alias_name ASC;
   

4. 数字排序：

   SELECT * FROM table_name ORDER BY 1;
   

   排序时将结果集中在第一个字段。

注意，ORDER BY 子句应该位于查询语句的末尾。如果查询中使用了 LIMIT 子句，则结果将首先根据排序顺序进行排序，然后再进行限制。

MySQL 数据处理函数

（1）单行处理函数

指在 SQL 中用于处理单个值的函数。它们接受一个或多个输入参数，并返回一个单一的结果。

• UPPER()：将字符串转换为大写。
• LOWER()：将字符串转换为小写。
• LENGTH()：返回字符串的长度。
• SUBSTRING()：提取字符串的一个子串。
• CONCAT()：将多个字符串连接起来。
• TRIM()：去除字符串前后的空格。
• ROUND()：对数值进行四舍五入。
• ABS()：返回数值的绝对值。
• COALESCE()：返回参数列表中第一个非空的值。
• NOW()：返回当前日期和时间。

（2）多行处理函数

指在 SQL 中用于处理多个值的函数。它们接受多个输入参数，并返回一个结果集。

• COUNT()：计算满足条件的行数。
• SUM()：计算数值列的总和。
• AVG()：计算数值列的平均值。
• MIN() 和 MAX()：分别返回数值列的最小值和最大值。
• GROUP_CONCAT()：将多行值连接为一个字符串。
• GROUP BY 子句：按照指定的列对结果进行分组。
• HAVING 子句：在 GROUP BY 分组结果上进行过滤。

MySQL 分组查询

分组查询是一种在 SQL 中使用 GROUP BY 子句对结果进行分组的操作。它结合了聚合函数，可以对数据集按照一个或多个列进行分组，并对每个分组应用聚合函数计算。

语法：

SELECT 列 1, 列 2, ..., 聚合函数 (column)
FROM 表名
GROUP BY 列 1, 列 2, ...

执行顺序：

1. FROM 和 WHERE 子句选择行。
2. GROUP BY 分组。
3. 聚合计算。
4. SELECT 选择列。
5. ORDER BY 排序。

HAVING 子句： HAVING 子句用于在 GROUP BY 子句之后对分组进行条件过滤。与 WHERE 不同，WHERE 在分组前过滤，HAVING 在分组后过滤。

MySQL 查询结果去重

DISTINCT 关键字用于从结果集中选择唯一的记录，并去除重复。

SELECT DISTINCT email
FROM customers;

如果在 SELECT 语句中选择多列，并且希望根据多列进行唯一性判断和去重，只需将多列包含在 SELECT DISTINCT 子句中。

MySQL 连接查询

连接查询用于在多个表之间建立关系，并检索满足特定条件的数据。

1. 内连接（INNER JOIN）： 返回两个表中满足连接条件的匹配行。
2. 等值连接（Equi Join）： 基于相等条件进行连接。
3. 非等值连接（Non-Equi Join）： 基于不相等条件进行连接。
4. 自连接（Self Join）： 在同一个表内进行连接操作。
5. 左连接（LEFT JOIN）： 返回左表的所有行，以及与右表满足连接条件的匹配行。
6. 右连接（RIGHT JOIN）： 返回右表的所有行，以及与左表满足连接条件的匹配行。
7. 全连接（FULL JOIN）： 返回左表和右表的所有行（MySQL 原生不支持 FULL JOIN，通常通过 UNION LEFT/RIGHT JOIN 模拟）。

内连接与外连接的区别：

• 匹配方式不同。
• 结果集不同。
• 表的顺序影响结果（外连接）。
• 使用场景不同。

MySQL 子查询

子查询（Subquery）是指在一个查询语句中嵌套另一个完整的查询语句。

1. WHERE 子句中的子查询： 用于过滤主查询结果。
2. FROM 子句中的子查询： 将子查询作为临时表或视图处理。
3. SELECT 列表中的子查询： 检索特定数据并作为新列返回。

MySQL 部分操作符

1. UNION： 合并两个或多个 SELECT 语句结果集，自动去除重复行。如需保留重复行，使用 UNION ALL。
2. LIMIT： 限制 SQL 查询返回的结果行数量。可与 OFFSET 配合跳过前几行。

SQL 查询语句模板：

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

MySQL 之增删改查

表的创建

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    PRIMARY KEY (column_name)
);

常见数据类型

• INT（整数）
• FLOAT（浮点数）
• DECIMAL（高精度小数）
• VARCHAR（可变长度字符串）
• CHAR（固定长度字符串）
• BOOLEAN（布尔值）
• DATE/TIME/DATETIME（日期时间）
• BLOB（二进制大对象）

表的删除

DROP TABLE table_name;

INSERT 插入数据

INSERT INTO table_name (column1, ...) VALUES (value1, ...);

UPDATE 更新语句

UPDATE 表名 SET 列名 1 = 新值 1 WHERE 条件;

DELETE 删除语句

DELETE FROM 表名 WHERE 条件;

MySQL 约束

约束用于定义和强制表中数据的完整性规则。

1. 主键约束（Primary Key）： 唯一标识每一行，非空且唯一。
2. 唯一约束（Unique）： 确保列值唯一，允许空值。
3. 外键约束（Foreign Key）： 定义表间关系。
4. 非空约束（Not Null）： 列不能为空。
5. 默认约束（Default）： 未提供值时使用默认值。
6. 检查约束（Check）： 限制值范围。

MySQL 事务

事务是数据库管理系统中的一种机制，用于确保数据库操作的一致性和完整性。它将一系列操作作为一个单元，要么全部成功，要么全部回滚。

ACID 特性：

1. 原子性（Atomicity）： 不可分割。
2. 一致性（Consistency）： 状态一致。
3. 隔离性（Isolation）： 互不干扰。
4. 持久性（Durability）： 永久保存。

事务隔离级别：

1. 读未提交（Read Uncommitted）： 可能脏读。
2. 读已提交（Read Committed）： 避免脏读，可能有不可重复读。
3. 可重复读（Repeatable Read）： MySQL 默认，避免不可重复读，可能有幻读。
4. 串行化（Serializable）： 最高隔离，性能最低。

MySQL 索引

索引是用于提高数据检索效率的数据结构，类似于书籍目录。

常见类型：

• B 树索引（B-Tree Index）： 适用于等值和范围查找。
• 哈希索引（Hash Index）： 仅适用于等值查找。
• 全文索引（Full-Text Index）： 针对文本搜索。
• 空间索引（Spatial Index）： 针对地理位置数据。

索引失效场景：

1. 不满足索引字段的顺序（最左前缀原则）。
2. 对索引字段使用函数或表达式。
3. 模糊查询以通配符开头（如 LIKE '%abc'）。

MySQL 视图

视图是在数据库中基于查询结果定义的虚拟表，不直接存储数据。

好处：

• 简化复杂查询。
• 提高数据安全性。
• 重用和模块化。
• 数据逻辑独立性。

创建与删除：

CREATE VIEW view_name AS SELECT ...;
DROP VIEW [IF EXISTS] view_name;

MySQL 设计的三个范式

1. 第一范式（1NF）： 确保每个列都具有原子性值，不可再分割。
2. 第二范式（2NF）： 确保非主键列完全依赖于主键而不是部分依赖。
3. 第三范式（3NF）： 确保非主键列之间没有传递依赖关系。

最佳实践建议

1. 索引优化： 仅在经常用于查询、排序或连接的列上创建索引。避免过度索引影响写入性能。
2. 事务控制： 尽量缩短事务持有时间，减少锁竞争。
3. SQL 规范： 使用预编译语句防止 SQL 注入，保持代码可读性。
4. 备份策略： 定期使用 mysqldump 导出数据库，确保数据安全。
5. 监控与维护： 定期检查慢查询日志，分析并优化低效 SQL 语句。