在MySQL中，有许多实用的小技巧和语句可以帮助提高工作效率和简化数据库操作。
以下是一些常用的技巧和语句：
1. 使用别名(Alias):
SELECT column_name AS alias_name FROM table_name;
为列名或表名指定别名，使查询结果更清晰。
2. 查询去重(Distinct):
SELECT DISTINCT column_name FROM table_name;
返回唯一不同的值。
3. 字符串拼接(Concatenation):
SELECT CONCAT(column1, ' ', column2) FROM table_name;
将多个列的值拼接成一个字符串。
4. 条件筛选(Where):
SELECT * FROM table_name WHERE condition;
根据条件筛选查询结果。
5. 排序(Order By):
SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
根据列的值对结果进行排序。
6. 分组(Group By)与聚合函数:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
按照某列的值进行分组，并使用聚合函数（如COUNT, SUM, AVG等）计算每组的统计数据。
7. 连接(Join):
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
结合两个或多个表中有关联的行。
8. 子查询(Subquery):
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM other_table);
在查询中嵌套另一个查询。
9. 插入多行(Insert Multiple Rows):
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;
一次性插入多行数据。
10. 更新(Update)与限制(Limit):
UPDATE table_name SET column_name = value WHERE condition LIMIT 1;
更新表中的数据，并使用LIMIT来限制更新的行数。
11. 删除(Delete)与限制(Limit):
DELETE FROM table_name WHERE condition LIMIT 1;
删除表中的数据，并使用LIMIT来限制删除的行数。
12. 使用临时表(Temporary Table):
CREATE TEMPORARY TABLE temp_table_name AS SELECT * FROM table_name WHERE condition;
创建临时表来存储临时数据。
13. 索引(Indexes)优化查询:
CREATE INDEX index_name ON table_name (column_name);
为表中的列创建索引，加快查询速度。
14. 使用CASE语句处理条件逻辑:
SELECT column_name, CASE WHEN condition THEN result1 ELSE result2 END AS alias_name FROM table_name;
根据条件返回不同的结果。
15. 使用变量(Variables):
SET @myVariable := value;
SELECT @myVariable;
设置并使用变量来存储临时数据。
这些是一些基本的My技巧和语句，可以帮助你更有效地操作和管理数据库。当然，根据实际需求，可能还会有更多高级技巧和最佳实践。
16. 批量更新(Update)数据:
UPDATE table_name SET column_name = CASE id
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END
WHERE id IN (1,2,3);
根据不同的条件批量更新数据。
17. 使用正则表达式(Regular Expressions):
SELECT * FROM table_name WHERE column_name REGEXP pattern;
利用正则表达式进行复杂的字符串匹配。
18. 日期和时间的操作:
SELECT CURRENT_DATE(), CURRENT_TIME(), NOW();
获取当前的日期、时间。
19. 日期格式化(Date Formatting):
SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:%i:%s') FROM table_name;
将日期时间格式化为指定的格式。
20. 使用IFNULL来处理NULL值:
SELECT IFNULL(column_name, 'default_value') FROM table_name;
如果列值为NULL，返回默认值。
21. 使用COALESCE返回第一个非NULL值:
SELECT COALESCE(column1, column2, 'default_value') FROM table_name;
从一系列值中返回第一个非NULL值。
22. 使用CAST转换数据类型:
SELECT CAST(column_name AS CHAR) FROM table_name;
将列的数据类型转换为另一种类型。
23. 使用EXPLAIN分析查询:
EXPLAIN SELECT * FROM table_name;
分析查询语句的执行计划，帮助优化查询。
24. 锁定表(Lock Table)以确保数据一致性:
LOCK TABLES table_name READ;
对表进行锁定，保证读取的数据在使用时不会被其他操作修改。
25. 解锁表(Unlock Tables):
UNLOCK TABLES;
完成操作后解锁表。
26. 使用LIMIT和OFFSET进行分页查询:
SELECT * FROM table_name LIMIT 10 OFFSET 20;
获取从第21条开始的10条记录。
27. 创建视图(Views)简化复杂查询:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
创建视图以简化频繁和复杂的查询。
28. 使用TRIGGER触发器自动化操作:
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW SET NEW.column_name = value;
创建触发器以自动执行插入前的操作。
29. 使用事务(Transactions)保持数据完整性:
START TRANSACTION;
INSERT INTO table_name (column1) VALUES (value1);
UPDATE another_table SET column2 = value2 WHERE condition;
COMMIT;
使用事务来确保一系列操作要么完全执行，要么完全不执行。
30. 导出(Export)和导入(Import)数据:
- 导出:
SELECT * INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;
将查询结果导出到CSV文件。
- 导入:
LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
从CSV文件导入数据到表中。
31、使用REPLACE INTO来插入或替换数据，避免先删除再插入的操作。
REPLACE INTO table_name (id, name) VALUES (1, 'John');
注释：如果id为1的记录已存在，则该记录将被更新；否则，将插入一条新记录。这种方法避免了先删除再插入的过程。
32、通过SET GLOBAL sql_mode=''来临时禁用SQL严格模式。
SET GLOBAL sql_mode='';
注释：这将在当前会话中禁用SQL严格模式，使得一些非标准的SQL语句能够执行。
33、利用EXPLAIN语句来分析查询性能，找出潜在的性能瓶颈。
EXPLAIN SELECT * FROM table_name WHERE id = 1;
注释：EXPLAIN语句可帮助你了解查询的执行计划，帮助优化查询性能。
34、使用LOAD DATA INFILE快速导入大量数据。
LOAD DATA INFILE 'file_path' INTO TABLE table_name;
注释：通过LOAD DATA INFILE可以快速地将文件中的数据导入数据库表中，适用于大量数据的导入操作。
35、利用分区表来优化大表的查询性能。
CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
注释：通过分区表可以将大表拆分为更小的部分，提高查询效率，尤其是针对时间范围查询时。
36、使用批量插入来提高插入数据的效率。
INSERT INTO table_name (id, name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');
注释：一次性插入多行数据可以减少插入操作的开销，提高效率。
37、利用用户自定义变量简化复杂查询。
SET @min_salary = 50000;
SELECT name, salary FROM employees WHERE salary > @min_salary;
注释：通过自定义变量可以在查询中使用变量，简化复杂查询的编写和维护。
38、通过分析表的索引使用情况来优化查询性能。
SHOW INDEX FROM table_name;
注释：查看表的索引情况可以帮助你评估哪些索引被查询使用，哪些可以优化或重建。
39、使用MySQL事件调度器来执行定时任务。
CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
UPDATE table_name SET status = 'expired'
WHERE expiration_date < NOW();
注释：通过事件调度器可以定时执行MySQL语句，执行定时任务如数据清理、自动化操作等。
40、利用全文索引提高搜索效率。
CREATE FULLTEXT INDEX idx_title ON articles(title);
SELECT * FROM articles
WHERE MATCH(title) AGAINST('search keyword');
注释：全文索引可加速对文本内容的搜索，提高检索效率。
41、使用ON DUPLICATE KEY UPDATE来插入新数据或更新现有数据。
INSERT INTO table_name (id, name)
VALUES (1, '张三') ON DUPLICATE KEY UPDATE name = '张三';
注释：如果插入数据的主键已存在，将会执行更新操作而不是插入新数据。
42、利用VALUES()函数来在INSERT语句中引用插入的值。
INSERT INTO table_name (id, name)
VALUES (1, 'Alice'), (2, VALUES(name));
注释：VALUES()函数可以引用正在插入的值，方便实现一次插入多个记录且其中某些值相同的需求。
43、使用SHOW PROFILE来查看查询的性能分析结果。
SET PROFILING = 1;
SELECT * FROM table_name;
SHOW PROFILES;
注释：SHOW PROFILE可以显示查询执行的详细性能信息，帮助优化查询。
44、利用TRIGGERS来触发特定操作。
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SET NEW.date_created = NOW();
END;
注释：TRIGGERS可用于在特定事件发生时自动执行一些操作，如更新其他列的值等。
45、使用MEMORY引擎来创建内存表，加快某些小型数据的访问速度。
CREATE TABLE memory_table ENGINE=MEMORY
AS
SELECT * FROM table_name;
注释：MEMORY引擎将表保存在内存中，适用于缓存或临时性数据的查询。
46、使用ROW_NUMBER()函数模拟MySQL中的行号（Row_Number）功能。
SELECT
@row_number := @row_number + 1 AS row_number,
col1, col2
FROM
table_name, (SELECT @row_number := 0) AS t;
注释：通过设置变量并自增来模拟行号功能，可以在结果集中为每行分配唯一标识符。
47、利用IGNORE选项忽略插入数据中的重复值。
INSERT IGNORE INTO table_name (id, name)
VALUES (1, '张三'), (1, '李四');
注释：当插入数据中有重复值时，使用IGNORE选项可以忽略重复值而继续插入其他数据。
48、使用BIN()函数对字段进行二进制字符串转换。
SELECT BIN(10); -- 输出 '1010'
注释：BIN()函数可以将数字转换为二进制字符串表示形式。
49、利用GROUP_CONCAT()函数将多行数据合并成一行并用特定分隔符分隔。
SELECT id, GROUP_CONCAT(name SEPARATOR ', ') AS names
FROM table_name GROUP BY id;
注释：GROUP_CONCAT()函数可以将多行数据合并成一行，并通过指定的分隔符进行分隔。
50、使用JSON数据类型来存储和查询JSON格式的数据。
CREATE TABLE json_data (
id INT PRIMARY KEY,
data JSON
);
INSERT INTO json_data (id, data)
VALUES (1, '{"name": "张三", "age": 30}');
SELECT data->'$.name' AS name
FROM json_data WHERE id = 1;
注释：JSON数据类型适用于存储和处理具有结构化格式的数据，提供了方便的JSON操作功能。
51、使用CHECK约束实现数据完整性验证。
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT,
CHECK (age >= 18)
);
注释：CHECK约束可确保数据符合特定条件，保证数据完整性。
52、利用EXISTS子查询来判断子查询是否返回结果。
SELECT * FROM table_name t
WHERE EXISTS
(SELECT 1 FROM other_table o
WHERE o.id = t.id);
注释：利用EXISTS子查询可以根据子查询是否返回结果筛选主查询的结果。
53、使用RAND()函数生成随机数。
SELECT RAND(); -- 返回一个0到1之间的随机数
注释：RAND()函数可用于生成随机数，适用于需要随机值的场景。
54、利用ENUM数据类型限制列的取值范围。
CREATE TABLE students (
id INT PRIMARY KEY,
gender ENUM('Male', 'Female')
);
注释：ENUM数据类型可以定义列的可选取值，限制输入的数据在预定义的范围内。
55、使用DATE_FORMAT()函数将日期格式化为指定的字符串形式。
SELECT DATE_FORMAT('2024-04-19 12:00:00', '%Y-%m-%d'); -- 输出 '2024-04-19'
注释：DATE_FORMAT()函数可以将日期格式化为指定的字符串形式，方便日期显示和处理。
56、利用SUBSTRING_INDEX函数提取字符串中的子串。
SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 2);
-- 输出 'apple,banana'
注释：SUBSTRING_INDEX函数可以根据指定的分隔符提取字符串中的子串。
57、使用CONVERT()函数进行数据类型转换。
SELECT CONVERT('123', SIGNED);
-- 将字符串'123'转换为有符号整数
注释：CONVERT()函数可用于将数据从一种类型转换为另一种类型。
58、使用CASE语句实现条件逻辑。
SELECT
CASE
WHEN marks >= 90 THEN 'A'
WHEN marks >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM student_marks;
注释：CASE语句可根据条件执行不同的逻辑，类似于编程语言中的条件语句。
59、利用HAVING子句过滤聚合函数结果。
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;
注释：HAVING子句用于在执行GROUP BY后过滤聚合函数结果。
60、使用LIMIT和OFFSET分页查询结果集。
SELECT * FROM products LIMIT 10 OFFSET 20;
注释：LIMIT用于限制结果集返回的行数，OFFSET用于指定起始位置。
61、使用TRANSACTION来确保一组SQL操作要么全部执行成功，要么都不执行。
START TRANSACTION;
INSERT INTO table1 (col1, col2) VALUES (val1, val2);
UPDATE table2 SET col3 = val3 WHERE col4 = val4;
COMMIT;
注释：TRANSACTION可以保证一组SQL操作的原子性，如果其中任何一个操作失败，则整个操作将回滚。
62、使用临时表（Temporary Table）临时存储中间结果。
CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM orders
WHERE order_date = '2024-01-01';
注释：临时表在当前会话中存在，会话结束时自动删除，适合存储临时数据或中间计算结果。
63、使用存储点（Savepoint）在事务中实现部分回滚。
START TRANSACTION;
INSERT INTO table1 VALUES (1, 'A');
SAVEPOINT sp1;
INSERT INTO table2 VALUES (2, 'B');
ROLLBACK TO sp1;
COMMIT;
注释：存储点可以将事务分段处理，部分回滚受影响的操作
64、使用MySQL的自动递增主键（Auto Increment Primary Key）来自动生成唯一标识符：
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50)
);
注释：自动递增主键可确保每行数据都有唯一的标识符，方便管理和检索。
65、使用备份和恢复策略来保障数据的安全性和可用性，定期进行备份并测试恢复流程：
mysqldump -u [username] -p [database_name] > backup.sql
mysql -u [username] -p [database_name] < backup.sql
注释：备份与恢复策略是防止数据丢失或损坏的重要措施，保障数据的可持续运营。
66、监控数据库的系统资源利用情况，包括CPU、内存、磁盘等，及时调整配置以应对系统负载
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW ENGINE INNODB STATUS;
这些技巧覆盖了数据查询、更新、格式化、性能优化、数据导入导出等多个方面，掌握这些可以在实际工作中提高效率。
AIGC ChatGPT 职场案例
AI 绘画 与 短视频制作
PowerBI 商业智能 68集
Mysql 8.0 54集
Oracle 21C 142集
Office 2021实战应用
Python 数据分析实战，
ETL Informatica 数据仓库案例实战 51集
Excel 2021实操 100集，
Excel 2021函数大全 80集
Excel 2021高级图表应用 89集，
Excel 2021大屏可视化制作 56集
Excel 2021实用技巧 300集
PPT 2021 商业汇报实战应用 69集
Tableau 数据分析 80集
FineReport 帆软大屏可视化 50集
送你各类文档模板PPT，表格，大屏可视化 超过5000+模板
以上案例实战获取： https://edu.csdn.net/combo/detail/2552
1970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
