-- 创建数据库 CREATE DATABASE dbname; CREATE DATABASE IF NOT EXISTS dbname CHARACTER SET utf8mb4;
-- 切换数据库 USE dbname;
-- 删除数据库 DROP DATABASE dbname;
二、表操作
1. 创建表
1 2 3 4 5 6 7
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age INT DEFAULT 18, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB;
2. 修改表
1 2 3 4 5 6 7 8 9 10 11
-- 添加列 ALTER TABLE users ADD COLUMN phone VARCHAR(15) AFTER email;
-- 修改列 ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;
-- 删除列 ALTER TABLE users DROP COLUMN phone;
-- 重命名表 RENAME TABLE users TO members;
3. 删除表
1
DROP TABLE IF EXISTS users;
三、数据操作 (CRUD)
1. 插入数据
1 2 3 4 5 6 7
-- 插入单行 INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT COUNT(*) AS total_users, AVG(age) AS avg_age, MAX(created_at) AS latest FROM users;
2. 分组查询
1 2 3
SELECT age, COUNT(*) AS count FROM users GROUP BY age HAVING count > 5;
3. 连接查询
1 2 3 4 5 6 7 8 9
-- 内连接 SELECT u.name, o.order_id FROM users u JOIN orders o ON u.id = o.user_id;
-- 左连接 SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
4. 子查询
1 2 3
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
五、索引管理
1 2 3 4 5 6 7 8
-- 创建索引 CREATE INDEX idx_email ON users(email);
-- 唯一索引 CREATE UNIQUE INDEX uq_email ON users(email);
-- 删除索引 DROP INDEX idx_email ON users;
六、约束
1 2 3 4 5 6 7
-- 主键约束 ALTER TABLE users ADD PRIMARY KEY (id);
-- 外键约束 ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
七、事务控制
1 2 3 4
START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- 或 ROLLBACK;
八、常用函数
1 2 3 4 5 6 7 8 9 10
-- 字符串 SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; SELECT SUBSTRING(email, 1, 5) FROM users;
-- 日期 SELECT NOW(), CURDATE(); SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM users;
-- 条件 SELECT name, IF(age >= 18, 'Adult', 'Minor') AS status FROM users;
九、用户与权限
1 2 3 4 5 6 7 8 9 10 11
-- 创建用户 CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
-- 授权 GRANT SELECT, INSERT ON dbname.* TO 'username'@'localhost';
-- 撤销权限 REVOKE DELETE ON dbname.* FROM 'username'@'localhost';
-- 刷新权限 FLUSH PRIVILEGES;
十、实用技巧
批量导入数据
1 2 3
LOAD DATA INFILE '/path/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
备份与恢复
1 2 3 4 5
# 命令行备份 mysqldump -u root -p dbname > backup.sql
# 恢复 mysql -u root -p dbname < backup.sql
查询优化
1
EXPLAIN SELECT * FROM users WHERE age > 20; -- 分析执行计划
递归查询 (MySQL 8.0+)
1 2 3 4 5 6 7
WITH RECURSIVE cte AS ( SELECT id, name, manager_id FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN cte ON e.manager_id = cte.id ) SELECT * FROM cte;
