MySql 知识大汇总

数据库索引

数据库索引是一种数据结构，用于提高数据库查询的速度和效率。索引可以看作是表中一列或多列的值的快速查找方式，类似于书籍的目录。通过创建索引，可以减少数据库的扫描量，加快数据的检索速度。

常见的索引类型

常见的索引类型包括：

1. B-Tree索引：B-Tree（平衡树）是一种常见的索引结构，适用于范围查询和精确查找。它能够快速定位到满足条件的数据块，然后在块内进行查找。B-Tree索引适用于大部分查询场景，是最常用的索引类型。

2. 哈希索引：哈希索引使用哈希函数将索引值映射到一个哈希表中的槽位，然后在槽位中查找数据。哈希索引适用于等值查询，但不适用于范围查询和排序操作。

3. 全文索引：全文索引用于在文本数据中进行全文搜索。它可以快速定位到包含指定关键词的文档或记录。

4. 空间索引：空间索引用于存储和查询空间数据，如地理位置信息。它可以支持空间关系查询，如距离查询、相交查询等。

创建索引时，需要选择合适的列作为索引列，并根据实际情况选择合适的索引类型。一般来说，主键列和经常用于查询的列是常见的索引列选择。但是，过多或不合理的索引可能会导致性能下降，因此需要根据实际情况进行性能测试和调优。

在使用索引时，可以在查询语句中使用关键字来优化查询性能，如WHERE子句中使用等值条件、前缀匹配条件，ORDER BY子句中使用索引列等。

需要注意的是，索引并非万能的，对于小型表或者频繁更新的表，索引的维护成本可能大于查询的性能提升。因此，在创建索引时需要谨慎选择索引列，并根据实际情况进行性能测试和调优。

索引的分类

1. 唯一索引（UNIQUE INDEX）：索引列的值必须唯一，用于加速对唯一值的查询。可以用于主键和唯一约束。

CREATE UNIQUE INDEX index_name ON table_name (column_name);

2. 主键索引（PRIMARY KEY）：主键是一种特殊的唯一索引，用于唯一标识表中的每一行。一个表只能有一个主键。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

3. 外键索引（FOREIGN KEY）：外键是关联两个表的字段，可以用于加速关联查询。

ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table (other_column);

4. 聚簇索引（CLUSTERED INDEX）：聚簇索引按照索引列的顺序对表中的数据进行物理排序。一个表只能有一个聚簇索引。

CREATE CLUSTERED INDEX index_name ON table_name (column_name);

5. 非聚簇索引（NONCLUSTERED INDEX）：非聚簇索引在索引列上创建一个独立的数据结构，用于加速对索引列的查询。

CREATE NONCLUSTERED INDEX index_name ON table_name (column_name);

6. 全文索引（FULLTEXT INDEX）：全文索引用于在文本数据中进行全文搜索。

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

7. 空间索引（SPATIAL INDEX）：空间索引用于存储和查询空间数据，如地理位置信息。

CREATE SPATIAL INDEX index_name ON table_name (column_name);

需要根据实际需求选择合适的索引类型，并根据查询场景进行性能测试和调优。请注意，在创建索引时，需要考虑索引的维护成本和对写操作的影响。

mysql 常用的一些函数

以下是一些常用的MySQL函数及其对应的SQL语句示例：

1. 字符串函数：

• CONCAT()：将多个字符串连接在一起。

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

• SUBSTRING()：截取字符串的一部分。

SELECT SUBSTRING(description, 1, 10) AS short_desc FROM products;

• LENGTH()：返回字符串的长度。

SELECT LENGTH(email) AS email_length FROM customers;

• UPPER()：将字符串转换为大写。

SELECT UPPER(last_name) AS last_name_upper FROM employees;

• LOWER()：将字符串转换为小写。

SELECT LOWER(first_name) AS first_name_lower FROM employees;

• REPLACE()：替换字符串中的某个子串。

SELECT REPLACE(description, 'old_value', 'new_value') AS new_description FROM products;

2. 数值函数：

• ROUND()：对数值进行四舍五入。

SELECT ROUND(price, 2) AS rounded_price FROM products;

• CEIL()：向上取整。

SELECT CEIL(quantity) AS rounded_quantity FROM inventory;

• FLOOR()：向下取整。

SELECT FLOOR(total_amount) AS rounded_amount FROM orders;

• ABS()：返回数值的绝对值。

SELECT ABS(balance) AS absolute_balance FROM accounts;

• MOD()：返回两个数值相除的余数。

SELECT MOD(quantity, 2) AS remainder FROM inventory;

3. 日期和时间函数：

• NOW()：返回当前日期和时间。

SELECT NOW() AS current_datetime;

• CURDATE()：返回当前日期。

SELECT CURDATE() AS current_date;

• CURTIME()：返回当前时间。

SELECT CURTIME() AS current_time;

• DATE_FORMAT()：将日期格式化为指定的格式。

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;

• DATE_ADD()：在日期上添加指定的时间间隔。

SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS new_date FROM orders;

4. 聚合函数：

• COUNT()：计算行数。

SELECT COUNT(*) AS total_rows FROM customers;

• SUM()：计算总和。

SELECT SUM(quantity) AS total_quantity FROM orders;

• AVG()：计算平均值。

SELECT AVG(price) AS average_price FROM products;

• MAX()：找出最大值。

SELECT MAX(salary) AS max_salary FROM employees;

• MIN()：找出最小值。

SELECT MIN(age) AS min_age FROM employees;

5. 条件函数：

• IF()：根据条件返回不同的值。

SELECT IF(quantity > 0, 'In Stock', 'Out of Stock') AS stock_status FROM products;

• CASE WHEN…THEN…ELSE…END：多条件判断。

SELECT
  CASE
    WHEN grade >= 90 THEN 'A'
    WHEN grade >= 80 THEN 'B'
    WHEN grade >= 70 THEN 'C'
    ELSE 'F'
  END AS letter_grade
FROM students;

这些示例SQL语句演示了如何使用常用的MySQL函数来处理和操作数据。您可以根据自己的需求进行调整和扩展。