mysql 小知识点

MySQL索引

索引是用于提高查询效率的数据结构，相当于一本书的目录，能加快数据查找的速度。MySQL持B+树索引（最常见）、哈希索引、全文索引等。常见的索引类型包括：

• 普通索引（INDEX）：加速查询但无唯一性约束。
• 唯一索引（UNIQUE）：确保列的值唯一。
• 主键索引（PRIMARY KEY）：主键默认就是唯一索引，不可为空。
• 全文索引（FULLTEXT）：用于全文搜索，适用于大文本字段。
• 组合索引（Composite Index）：多个列组合成一个索引，提高多列查询效率。

创建索引的基本语法：

CREATE INDEX idx_name ON table_name(column_name);

但要注意，索引会加快查询，但会降低插入、更新、删除的效率，因为每次修改数据时，索引也需要更新。

---

MySQL外键（FOREIGN KEY）

外键用于建立表与表之间的关系，确保数据的完整性和一致性。它保证子表中的数据必须在父表中存在，否则无法插入数据。

示例：

CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );

其中 ON DELETE CASCADE 表示如果 users 表中的某个用户被删除，相关的 orders 记录也会自动删除。

但需要注意的是，MySQL的InnoDB存储引擎支持外键，而MyISAM不支持外键。此外，外键可能会影响插入和删除的性能，尤其是有大量关联数据时。

---

MySQL的执行顺序

SQL查询的执行顺序与书写顺序不同，MySQL在执行 SELECT 语句时通常遵循以下顺序：

1. FROM（从哪个表查询数据）
2. JOIN（如果有表连接，则先执行连接操作）
3. WHERE（筛选符合条件的数据）
4. GROUP BY（对数据进行分组）
5. HAVING（对分组后的数据进行筛选）
6. SELECT（选择要返回的列）
7. ORDER BY（对结果进行排序）
8. LIMIT（限制返回的行数）

例如：

SELECT name, COUNT(*) FROM employees WHERE department = 'IT' GROUP BY name HAVING COUNT(*) > 1 ORDER BY name LIMIT 10;

这里，虽然 SELECT 语句在 SQL 代码中是最先写的，但它实际上是在最后才执行的。

---

写SQL语句时的顺序（最佳实践）

写SQL时建议按逻辑顺序书写，保持清晰的结构：

1. 先写 SELECT，明确需要查询的字段。
2. 决定 FROM 目标表，并考虑是否需要 JOIN 其他表。
3. 用 WHERE 过滤数据，减少不必要的计算。
4. 如果涉及分组和聚合，添加 GROUP BY 和 HAVING。
5. 确定排序方式，使用 ORDER BY。
6. 适当使用 LIMIT 限制返回行数，提高效率。

---

Oracle函数

Oracle 提供了丰富的函数，主要分为单行函数和多行（聚合）函数。

• 单行函数：对每一行数据进行计算，如 LENGTH()、UPPER()、TO_DATE() 等。
• 多行（聚合）函数：对多行数据进行计算，如 SUM()、AVG()、COUNT()、MAX()、MIN() 等。

示例：

SELECT UPPER(name), LENGTH(name) FROM employees;

这里 UPPER(name) 将 name 转换为大写，LENGTH(name) 计算 name 的长度。

---

MySQL函数

MySQL 也提供了类似的函数，包括数学函数、字符串函数、日期函数等。例如：

• 数学函数：ABS() 取绝对值，ROUND() 取整，CEIL() 向上取整。
• 字符串函数：CONCAT() 连接字符串，LENGTH() 计算长度，SUBSTRING() 截取字符串。
• 日期函数：NOW() 返回当前时间，DATE_FORMAT() 格式化日期，DATEDIFF() 计算日期间隔。

示例：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, DATEDIFF(NOW(), birth_date) AS age_in_days FROM users;

这里 CONCAT() 拼接姓名，DATEDIFF() 计算出生到现在的天数。

---

字符函数（字符串函数）

字符函数主要用于处理字符串数据，常见的有：

• LENGTH(str)：返回字符串长度（字节数）。
• CHAR_LENGTH(str)：返回字符串的字符数（与 LENGTH 区别在于支持多字节字符）。
• UPPER(str) / LOWER(str)：转换为大写/小写。
• SUBSTRING(str, pos, len)：从 str 中截取从 pos 开始、长度为 len 的子串。
• TRIM(str)：去除前后空格。
• REPLACE(str, from_str, to_str)：替换字符串中的部分内容。
• CONCAT(str1, str2, ...)：拼接多个字符串。

示例：

SELECT UPPER(name), SUBSTRING(email, 1, 5), REPLACE(phone, '-', '') FROM users;

这里：

• UPPER(name) 转换为大写。
• SUBSTRING(email, 1, 5) 取 email 的前 5 个字符。
• REPLACE(phone, '-', '') 移除电话号码中的 -。

---

总结

1. 索引 用于优化查询，提高性能，但会增加写操作的成本。
2. 外键 用于维护表之间的关系，确保数据完整性，但可能影响性能。
3. SQL 执行顺序 与写的顺序不同，查询时 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT。
4. Oracle 和 MySQL 都提供了丰富的函数，包括数学函数、日期函数、字符串函数等，帮助处理数据。
5. 字符函数 主要用于处理字符串，如 UPPER() 转大写、SUBSTRING() 截取子串、REPLACE() 替换字符串内容。