MySQL 常用操作语句

新增 (INSERT)

-- 单行插入
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

-- 批量插入
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),
       (value1, value2, value3, ...),
       ...;

删除(DELETE)

DELETE FROM table_name WHERE condition;

修改(UPDATE)

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

查询(SELECT)

-- 基本查询
SELECT column1, column2, ... FROM table_name WHERE condition;

-- 分组和聚合查询
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- 排序
SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC|DESC;

-- 子查询
SELECT column1 FROM table_name WHERE column2 IN (SELECT column2 FROM another_table WHERE condition);

-- 嵌套查询
SELECT column1, (SELECT column2 FROM another_table WHERE condition) AS subquery_result FROM table_name WHERE condition;

分组(GROUP BY)

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

排序(ORDER BY)

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

聚合查询

SELECT COUNT(column1), SUM(column2), AVG(column3) FROM table_name WHERE condition;

子查询

SELECT column1 FROM table_name WHERE column2 IN (SELECT column2 FROM another_table WHERE condition);

嵌套查询

SELECT column1, (SELECT column2 FROM another_table WHERE condition) AS subquery_result FROM table_name WHERE condition;

批量插入

INSERT INTO table_name (column1, column2, column3) VALUES
(value1, value2, value3),
(value1, value2, value3),
...;

批量修改

UPDATE table_name SET column1 = value1 WHERE condition;

异常处理

BEGIN
    -- SQL语句
    -- 若发生异常，执行以下语句
    ROLLBACK; -- 回滚操作
    -- 若无异常，执行以下语句
    COMMIT; -- 提交操作
END;

事务操作

-- 开启事务
START TRANSACTION;

-- SQL语句

-- 若发生异常，回滚事务
ROLLBACK;

-- 若无异常，提交事务
COMMIT;

多表关联查询

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.common_column = table2.common_column
WHERE condition;

闭包查询 (Recursive Queries)

WITH RECURSIVE cte_name AS (
    -- 初始查询部分
    SELECT initial_column FROM table_name WHERE condition
    UNION ALL
    -- 递归部分
    SELECT recursive_column FROM table_name JOIN cte_name ON condition
)
SELECT * FROM cte_name;

触发器 (Triggers)

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
BEGIN
    -- 触发器操作
END;

创建索引 (Indexes)

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 创建全文索引（仅适用于特定存储引擎）
CREATE FULLTEXT INDEX index_name ON table_name (column_name);

备份数据库

mysql -u username -p database_name < backup.sql

视图 (Views)

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

存储过程 (Stored Procedures)

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    -- 存储过程逻辑
END //
DELIMITER ;

用户权限管理 (User Privileges)

-- 创建用户并赋予权限
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

窗口函数 (Window Functions)

SELECT column1, column2, 
       ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_num
FROM table_name;

枚举类型 (ENUM Types)

CREATE TABLE table_name (
    column_name ENUM('value1', 'value2', 'value3')
);

外键约束 (Foreign Key Constraints)

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

联合查询 (UNION and UNION ALL)

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

分页查询 (Pagination)

SELECT column1, column2 FROM table_name LIMIT 10 OFFSET 20;

表别名 (Table Aliases):

SELECT t1.column1, t2.column2
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.id;

数据类型转换 (CAST and CONVERT)

SELECT column1, CAST(column2 AS VARCHAR) AS new_column
FROM table_name;

临时表 (Temporary Tables)

CREATE TEMPORARY TABLE temp_table (
    column1 INT,
    column2 VARCHAR
);

数据导入和导出

可以使用 LOAD DATA INFILE 语句将数据从外部文件导入到数据库中，使用 SELECT ... INTO OUTFILE 将查询结果导出到外部文件

LOAD DATA INFILE 'data.csv' INTO TABLE table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

SELECT column1, column2 INTO OUTFILE 'output.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

联合查询 (JOINs)

联合查询用于将多个表中的数据联合起来，根据特定的关联条件进行连接。常见的联合查询类型包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN。

-- INNER JOIN
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

-- LEFT JOIN
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

自连接 (Self-Joins)

SELECT e.employee_name, m.manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

动态SQL (Dynamic SQL)

SET @query = CONCAT('SELECT column1, column2 FROM ', table_name, ' WHERE condition');
PREPARE dynamic_query FROM @query;
EXECUTE dynamic_query;
DEALLOCATE PREPARE dynamic_query;

批量删除 (Bulk DELETE)

DELETE FROM table_name WHERE condition;

全局临时表 (Global Temporary Tables)

全局临时表是跨会话的临时表，它在创建它的会话关闭后仍然存在，但是在所有会话关闭后会自动删除

CREATE GLOBAL TEMPORARY TABLE temp_table (
    column1 INT,
    column2 VARCHAR
);

JSON数据类型

CREATE TABLE json_table (
    id INT PRIMARY KEY,
    data JSON
);

存储引擎 (Storage Engines)

MySQL支持多种存储引擎，如InnoDB、MyISAM等。不同的存储引擎具有不同的特性，可以根据需求选择合适的引擎

CREATE TABLE table_name (
    column1 INT,
    column2 VARCHAR
) ENGINE = InnoDB;

全文搜索 (Full-Text Search)

SELECT column1 FROM table_name WHERE MATCH(column2) AGAINST('search_query');

分区表 (Partitioned Tables)

分区表将大表分割成多个小表，可以提高查询性能并简化维护。

CREATE TABLE partitioned_table (
    id INT,
    date_column DATE
) PARTITION BY RANGE (YEAR(date_column)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN (2020)
);

← 上一篇「mysql 查询分析」下一篇「mysql 中 ti...」 →