MySQL数据库指令大全：从基础到进阶的完整指南

sql1CREATE DATABASE sales_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
2-- 创建支持emoji的数据库
3DROP DATABASE IF EXISTS temp_db; 
4-- 安全删除已存在的数据库
5USE marketing_db; 
6-- 切换当前数据库
7SHOW DATABASES LIKE 'prod_%'; 
8-- 模糊匹配查询数据库
https://m.jixing.net/

sql1CREATE USER 'api_user'@'%' IDENTIFIED BY 'SecurePass123!';
2-- 创建可远程访问的用户
3GRANT SELECT, INSERT ON customer_db.* TO 'api_user'@'%';
4-- 授予特定权限
5REVOKE INSERT ON orders.* FROM 'api_user'@'%'; 
6-- 撤销权限
7FLUSH PRIVILEGES; 
8-- 立即生效权限变更

sql1CREATE TABLE orders (
2    order_id VARCHAR(32) PRIMARY KEY,
3    customer_id VARCHAR(32) NOT NULL,
4    amount DECIMAL(10,2) CHECK (amount > 0),
5    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
6    INDEX idx_customer (customer_id),
7    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
8) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sql1ALTER TABLE products 
2ADD COLUMN discount_rate DECIMAL(3,2) DEFAULT 0.0 AFTER price;
3-- 添加字段并指定位置
4ALTER TABLE users 
5MODIFY COLUMN phone VARCHAR(20) COMMENT '国际电话格式';
6-- 修改字段属性
7ALTER TABLE logs 
8DROP INDEX idx_log_time; 
9-- 删除索引

sql1-- 条件查询与排序
2SELECT product_name, stock 
3FROM inventory 
4WHERE stock > 100 AND category = 'Electronics' 
5ORDER BY stock DESC LIMIT 20;
6
7-- 复杂连接查询
8SELECT o.order_id, c.name, p.product_name 
9FROM orders o
10JOIN customers c ON o.customer_id = c.id
11JOIN order_items oi ON o.id = oi.order_id
12JOIN products p ON oi.product_id = p.id
13WHERE o.order_date > '2025-01-01';

sql1-- 批量更新
2UPDATE products 
3SET price = price * 0.9 
4WHERE category = 'Seasonal' AND stock > 50;
5
6-- 安全删除（带条件）
7DELETE FROM logs 
8WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) 
9LIMIT 10000;

sql1-- 创建复合索引
2CREATE INDEX idx_order_customer_date ON orders(customer_id, order_date);
3
4-- 分析索引使用情况
5EXPLAIN SELECT * FROM orders 
6WHERE customer_id = 'CUST001' AND order_date > '2025-01-01';

sql1OPTIMIZE TABLE orders; 
2-- 重建表减少碎片
3ANALYZE TABLE products; 
4-- 更新统计信息
5SET GLOBAL innodb_buffer_pool_size = 4G; 
6-- 调整缓冲池大小（需权限）

bash1# 备份单个数据库
2mysqldump -u root -p --single-transaction sales_db > sales_backup_20251105.sql
3
4# 备份所有数据库
5mysqldump -u root -p --all-databases > full_backup_20251105.sql

bash1# 创建空数据库
2mysql -u root -p -e "CREATE DATABASE restored_db;"
3
4# 恢复数据
5mysql -u root -p restored_db < sales_backup_20251105.sql

sql1DELIMITER //
2CREATE PROCEDURE transfer_funds(
3    IN from_acc VARCHAR(32),
4    IN to_acc VARCHAR(32),
5    IN amount DECIMAL(10,2)
6)
7BEGIN
8    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
9    START TRANSACTION;
10        UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
11        UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
12    COMMIT;
13END //
14DELIMITER ;

sql1-- 创建视图
2CREATE VIEW v_customer_orders AS
3SELECT c.name, COUNT(o.id) as order_count 
4FROM customers c
5LEFT JOIN orders o ON c.id = o.customer_id
6GROUP BY c.id;
7
8-- 创建分区表
9CREATE TABLE sales_data (
10    id INT AUTO_INCREMENT,
11    sale_date DATE NOT NULL,
12    amount DECIMAL(10,2),
13    PRIMARY KEY (id, sale_date)
14) PARTITION BY RANGE (YEAR(sale_date)) (
15    PARTITION p2023 VALUES LESS THAN (2024),
16    PARTITION p2024 VALUES LESS THAN (2025),
17    PARTITION pmax VALUES LESS THAN MAXVALUE
18);