-- 数据库操作
-- 创建数据库
CREATE DATABASE database_name;
-- 创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 查看所有数据库
SHOW DATABASES;
-- 选择数据库
USE database_name;
-- 删除数据库
DROP DATABASE database_name;
-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;
-- 修改数据库字符集
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 表操作
-- 创建表
CREATE TABLE table_name (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 创建表时指定存储引擎和字符集
CREATE TABLE table_name (
-- 列定义
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 查看所有表
SHOW TABLES;
-- 查看表结构
DESCRIBE table_name;
DESC table_name; -- 简写
-- 查看表创建语句
SHOW CREATE TABLE table_name;
-- 删除表
DROP TABLE table_name;
-- 重命名表
RENAME TABLE old_name TO new_name;
-- 清空表数据（保留表结构）
TRUNCATE TABLE table_name;
-- 修改表名
ALTER TABLE table_name RENAME TO new_table_name;
-- 添加列
ALTER TABLE table_name ADD COLUMN column_name data_type;
-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
-- 修改列定义
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;
-- 重命名列
ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;
-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
CREATE INDEX index_name ON table_name (column_name);
-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE index_name (column_name);
CREATE UNIQUE INDEX index_name ON table_name (column_name);
-- 删除索引
ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;
-- 添加外键
ALTER TABLE table_name ADD CONSTRAINT fk_name
FOREIGN KEY (column_name) REFERENCES other_table(other_column);
-- 删除外键
ALTER TABLE table_name DROP FOREIGN KEY fk_name;
-- 数据操作
-- 插入数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 插入多行数据
INSERT INTO table_name (column1, column2)
VALUES (value1, value2), (value3, value4), (value5, value6);
-- 插入查询结果
INSERT INTO table_name (column1, column2)
SELECT column1, column2 FROM other_table WHERE condition;
-- 更新数据
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
-- 删除数据
DELETE FROM table_name WHERE condition;
-- 查询所有数据
SELECT * FROM table_name;
-- 查询特定列
SELECT column1, column2 FROM table_name;
-- 带条件的查询
SELECT * FROM table_name WHERE column_name = value;
-- 排序查询结果
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
-- 分组查询
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
-- 分组后筛选
SELECT column_name, COUNT(*) FROM table_name
GROUP BY column_name HAVING COUNT(*) > 1;
-- 限制返回行数
SELECT * FROM table_name LIMIT 10;
-- 分页查询
SELECT * FROM table_name LIMIT offset, count;
SELECT * FROM table_name LIMIT count OFFSET offset;
-- 连接查询（内连接）
SELECT a.*, b.* FROM table_a a INNER JOIN table_b b ON a.id = b.a_id;
-- 左外连接
SELECT a.*, b.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id;
-- 右外连接
SELECT a.*, b.* FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id;
-- 全外连接（MySQL不支持FULL OUTER JOIN,需要使用UNION模拟）
SELECT a.*, b.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id
UNION
SELECT a.*, b.* FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id WHERE a.id IS NULL;
-- 子查询
SELECT * FROM table_name WHERE column_name
IN (SELECT column_name FROM other_table WHERE condition);
-- 联合查询
SELECT column_name FROM table1 UNION SELECT column_name FROM table2;
SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2;
-- 存在性检查
SELECT * FROM table_name WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);
-- 计算字段
SELECT column1, column2, column1 + column2 AS sum FROM table_name;
-- 去除重复行
SELECT DISTINCT column_name FROM table_name;
-- 聚合函数
SELECT COUNT(*), AVG(column_name), MAX(column_name), MIN(column_name), SUM(column_name)
FROM table_name;
-- 字符串函数
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
UPPER(column_name),
LOWER(column_name),
SUBSTRING(column_name, start, length),
LENGTH(column_name),
TRIM(column_name),
REPLACE(column_name, 'old', 'new')
FROM table_name;
-- 日期函数
SELECT NOW(), CURDATE(), CURTIME(),
DATE_FORMAT(date_column, '%Y-%m-%d'),
DAY(date_column), MONTH(date_column), YEAR(date_column),
DATEDIFF(date1, date2),
DATE_ADD(date_column, INTERVAL 1 DAY),
DATE_SUB(date_column, INTERVAL 1 MONTH)
FROM table_name;
-- 数学函数
SELECT ABS(column_name), ROUND(column_name, decimals),
CEILING(column_name), FLOOR(column_name),
RAND(), MOD(column1, column2)
FROM table_name;
-- 条件函数
SELECT IF(condition, true_value, false_value),
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
FROM table_name;
-- 用户和权限管理
-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- 修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');
-- 重命名用户
RENAME USER 'old_username'@'host' TO 'new_username'@'host';
-- 删除用户
DROP USER 'username'@'host';
-- 授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'host';
-- 撤销权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
REVOKE SELECT, INSERT ON database_name.table_name FROM 'username'@'host';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';
-- 创建角色
CREATE ROLE 'role_name';
-- 授予角色权限
GRANT SELECT, INSERT ON database_name.* TO 'role_name';
-- 将角色授予用户
GRANT 'role_name' TO 'username'@'host';
-- 设置活动角色
SET ROLE 'role_name';
-- 事务控制
-- 开始事务
START TRANSACTION;
-- 或者
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 设置保存点
SAVEPOINT savepoint_name;
-- 回滚到保存点
ROLLBACK TO savepoint_name;
-- 释放保存点
RELEASE SAVEPOINT savepoint_name;
-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 查看当前事务隔离级别
SELECT @@transaction_isolation;
-- 存储过程和函数
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(255))
BEGIN
-- 存储过程逻辑
SELECT column_name INTO param2 FROM table_name WHERE id = param1;
END //
DELIMITER ;
-- 调用存储过程
CALL procedure_name(1, @output_param);
SELECT @output_param;
-- 创建函数
DELIMITER //
CREATE FUNCTION function_name(param1 INT) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT;
-- 函数逻辑
SELECT COUNT(*) INTO result FROM table_name WHERE column_name = param1;
RETURN result;
END //
DELIMITER ;
-- 调用函数
SELECT function_name(1);
-- 查看存储过程和函数
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
-- 查看存储过程/函数定义
SHOW CREATE PROCEDURE procedure_name;
SHOW CREATE FUNCTION function_name;
-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;
-- 删除函数
DROP FUNCTION IF EXISTS function_name;
-- 触发器
-- 创建触发器
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
-- 触发器逻辑
IF NEW.column_name < 0 THEN
SET NEW.column_name = 0;
END IF;
END //
DELIMITER ;
-- 查看触发器
SHOW TRIGGERS;
-- 查看触发器定义
SHOW CREATE TRIGGER trigger_name;
-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;
-- 事件
-- 创建事件
CREATE EVENT event_name
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
-- 事件逻辑
UPDATE table_name SET column_name = column_name + 1;
END;
-- 查看事件
SHOW EVENTS;
-- 查看事件定义
SHOW CREATE EVENT event_name;
-- 修改事件
ALTER EVENT event_name
ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY;
-- 删除事件
DROP EVENT IF EXISTS event_name;
-- 视图
-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;
-- 创建或替换视图
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;
-- 查看视图
SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';
-- 查看视图定义
SHOW CREATE VIEW view_name;
-- 删除视图
DROP VIEW IF EXISTS view_name;
--实用命令
-- 查看MySQL版本
SELECT VERSION();
-- 查看当前用户
SELECT USER();
-- 查看当前数据库
SELECT DATABASE();
-- 查看服务器状态
SHOW STATUS;
-- 查看服务器变量
SHOW VARIABLES;
-- 查看进程列表
SHOW PROCESSLIST;
-- 杀死进程
KILL process_id;
-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';
-- 查看索引统计信息
ANALYZE TABLE table_name;
-- 优化表
OPTIMIZE TABLE table_name;
-- 修复表
REPAIR TABLE table_name;
-- 检查表
CHECK TABLE table_name;
-- 导出查询结果到文件
SELECT * INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name WHERE condition;
-- 从文件导入数据
LOAD DATA INFILE '/tmp/data.txt' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, column2, column3);
--性能优化
-- 解释查询执行计划
EXPLAIN SELECT * FROM table_name WHERE condition;
-- 开启查询分析
SET profiling = 1;
-- 执行查询
SELECT * FROM large_table WHERE condition;
-- 查看查询分析结果
SHOW PROFILES;
-- 查看特定查询的详细信息
SHOW PROFILE FOR QUERY 1;
-- 创建性能模式表（需要启用performance_schema）
-- 这些表提供了服务器性能的详细信息
SELECT * FROM performance_schema.events_statements_summary_by_digest LIMIT 5;
-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;
-- 查看当前连接
SELECT * FROM information_schema.processlist;
-- 查看表大小
SELECT
table_name AS "表名",
round(data_length/1024/1024, 2) AS "数据大小(MB)",
round(index_length/1024/1024, 2) AS "索引大小(MB)",
round((data_length+index_length)/1024/1024, 2) AS "总大小(MB)",
table_rows AS "行数"
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length+index_length) DESC;
-- 备份与恢复
-- 使用mysqldump命令行工具备份数据库（非SQL指令）
-- mysqldump -u username -p database_name > backup.sql
-- 使用mysql命令行工具恢复数据库（非SQL指令）
-- mysql -u username -p database_name < backup.sql
-- 在MySQL中导出查询结果到文件
SELECT * INTO OUTFILE '/tmp/backup.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;
-- 从文件导入数据到表中
LOAD DATA INFILE '/tmp/backup.csv' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';