MySql常用SQL命令详解

MySQL 常用 SQL 命令详解

一、数据库操作

1.1 创建数据库

-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

1.2 查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库创建信息
SHOW CREATE DATABASE database_name;

1.3 选择数据库

USE database_name;

1.4 删除数据库

DROP DATABASE database_name;

1.5 修改数据库

ALTER DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

二、表操作

2.1 创建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 查看表

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC table_name;
DESCRIBE table_name;

-- 查看建表语句
SHOW CREATE TABLE table_name;

2.3 修改表

-- 添加列
ALTER TABLE table_name ADD COLUMN column_name datatype;

-- 修改列
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

-- 重命名列
ALTER TABLE table_name CHANGE COLUMN old_name new_name datatype;

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE unique_name (column_name);

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 添加外键
ALTER TABLE orders 
ADD FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
RENAME TABLE old_table_name TO new_table_name;

2.4 删除表

DROP TABLE table_name;

-- 清空表数据（更快，不记录日志）
TRUNCATE TABLE table_name;

三、数据操作（CRUD）

3.1 插入数据

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

-- 插入多行
INSERT INTO table_name (column1, column2) 
VALUES 
    (value1, value2),
    (value3, value4);

-- 插入查询结果
INSERT INTO table_name (column1, column2)
SELECT column1, column2 FROM another_table;

3.2 查询数据

-- 基本查询
SELECT * FROM table_name;

-- 选择特定列
SELECT column1, column2 FROM table_name;

-- 使用别名
SELECT column1 AS alias_name FROM table_name;

-- 去重
SELECT DISTINCT column_name FROM table_name;

-- 条件查询
SELECT * FROM table_name WHERE condition;

-- 常用条件运算符
=, <>, !=, <, >, <=, >=
BETWEEN, IN, LIKE, IS NULL, IS NOT NULL

-- 逻辑运算符
AND, OR, NOT

-- 模糊查询
SELECT * FROM table_name WHERE column_name LIKE 'A%';  -- 以A开头
SELECT * FROM table_name WHERE column_name LIKE '%A%'; -- 包含A
SELECT * FROM table_name WHERE column_name LIKE '_A%'; -- 第二个字符是A

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

-- 限制结果
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 5, 10;  -- 从第6行开始取10行

3.3 更新数据

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

-- 示例：增加年龄
UPDATE users 
SET age = age + 1 
WHERE id = 1;

3.4 删除数据

DELETE FROM table_name WHERE condition;

-- 删除所有数据（危险！）
DELETE FROM table_name;

四、高级查询

4.1 聚合函数

-- 常用聚合函数
COUNT(), SUM(), AVG(), MAX(), MIN()

-- 示例
SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(salary) AS max_salary
FROM users;

4.2 分组查询

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

-- 分组后过滤
SELECT department, COUNT(*) 
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

4.3 连接查询

-- 内连接
SELECT * 
FROM table1 
INNER JOIN table2 ON table1.id = table2.table1_id;

-- 左连接
SELECT * 
FROM table1 
LEFT JOIN table2 ON table1.id = table2.table1_id;

-- 右连接
SELECT * 
FROM table1 
RIGHT JOIN table2 ON table1.id = table2.table1_id;

-- 全外连接（MySQL不支持，可用UNION模拟）
SELECT * FROM table1 LEFT JOIN table2 ON condition
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON condition;

-- 自连接
SELECT e1.name, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

4.4 子查询

-- WHERE子句子查询
SELECT * FROM table1
WHERE column1 IN (SELECT column1 FROM table2);

-- FROM子句子查询
SELECT * FROM (
    SELECT * FROM table1 WHERE condition
) AS subquery;

-- SELECT子句子查询
SELECT 
    column1,
    (SELECT COUNT(*) FROM table2) AS count
FROM table1;

4.5 联合查询

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- UNION ALL（包含重复）
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

五、索引操作

5.1 创建索引

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

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

-- 创建复合索引
CREATE INDEX composite_index ON table_name (col1, col2);

5.2 查看索引

SHOW INDEX FROM table_name;

5.3 删除索引

DROP INDEX index_name ON table_name;

六、视图操作

6.1 创建视图

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

6.2 使用视图

SELECT * FROM view_name;

6.3 修改视图

-- MySQL中修改视图需要删除后重建，或使用CREATE OR REPLACE
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

6.4 删除视图

DROP VIEW view_name;

七、事务控制

7.1 事务基本操作

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

7.2 事务示例

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 如果出现错误
-- ROLLBACK;

COMMIT;

八、用户和权限管理

8.1 用户管理

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- 修改用户密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'username'@'localhost';

8.2 权限管理

-- 授予权限
GRANT permission_type ON database.table TO 'username'@'localhost';

-- 示例
GRANT SELECT, INSERT ON mydb.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'username'@'localhost';

-- 撤销权限
REVOKE permission_type ON database.table FROM 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

8.3 常用权限类型

• SELECT - 查询数据
• INSERT - 插入数据
• UPDATE - 更新数据
• DELETE - 删除数据
• CREATE - 创建表/数据库
• DROP - 删除表/数据库
• ALTER - 修改表结构
• REFERENCES - 创建外键
• INDEX - 创建索引
• ALL PRIVILEGES - 所有权限

九、备份与恢复

9.1 备份数据库

# 使用mysqldump备份
mysqldump -u username -p database_name > backup.sql

# 备份所有数据库
mysqldump -u username -p --all-databases > all_backup.sql

# 备份特定表
mysqldump -u username -p database_name table1 table2 > backup.sql

9.2 恢复数据库

# 使用mysql恢复
mysql -u username -p database_name < backup.sql

9.3 SQL命令备份恢复

-- 导出数据
SELECT * INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

-- 导入数据
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

十、性能优化相关

10.1 查询分析

-- 查看查询执行计划
EXPLAIN SELECT * FROM table_name WHERE condition;

-- 更详细的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM table_name;

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

10.2 锁相关

-- 查看当前锁
SHOW OPEN TABLES WHERE In_use > 0;
SHOW PROCESSLIST;

-- 杀死进程
KILL process_id;

十一、常用函数

11.1 字符串函数

CONCAT(str1, str2)          -- 连接字符串
SUBSTRING(str, start, len)  -- 截取字符串
LENGTH(str)                 -- 字符串长度
UPPER(str), LOWER(str)      -- 大小写转换
TRIM(str)                   -- 去除空格
REPLACE(str, from_str, to_str) -- 替换字符串

11.2 数值函数

ROUND(num, decimals)        -- 四舍五入
CEIL(num), FLOOR(num)       -- 向上/向下取整
ABS(num)                    -- 绝对值
MOD(dividend, divisor)      -- 取模
RAND()                      -- 随机数

11.3 日期时间函数

NOW(), CURDATE(), CURTIME() -- 当前日期时间
DATE_FORMAT(date, format)   -- 格式化日期
DATEDIFF(date1, date2)      -- 日期差
DATE_ADD(date, INTERVAL expr unit) -- 日期加减
YEAR(date), MONTH(date), DAY(date) -- 提取年月日

十二、实用技巧

12.1 批量操作

-- 批量更新（使用CASE）
UPDATE products
SET price = CASE
    WHEN category = 'A' THEN price * 1.1
    WHEN category = 'B' THEN price * 1.2
    ELSE price
END;

12.2 分页查询

-- 传统分页
SELECT * FROM table_name 
LIMIT 10 OFFSET 20;

-- MySQL 8.0+ 分页
SELECT * FROM table_name
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

12.3 存在性检查

-- 使用EXISTS
SELECT * FROM table1 t1
WHERE EXISTS (
    SELECT 1 FROM table2 t2 
    WHERE t1.id = t2.table1_id
);

注意事项

SQL注入防护：使用参数化查询，不要拼接SQL字符串 事务使用：涉及多个操作的业务逻辑要使用事务 索引优化：合理创建索引，避免过多索引影响写入性能 备份策略：定期备份重要数据 性能监控：使用EXPLAIN分析慢查询 字符集统一：建议使用utf8mb4字符集以支持emoji等特殊字符

这些是MySQL中最常用的SQL命令，掌握这些命令可以应对日常开发中的大部分需求。根据具体场景选择合适的命令和优化策略。