MySQL基础操作

一、数据库操作

1. 创建数据库

1CREATE DATABASE database_name;
2CREATE DATABASE IF NOT EXISTS database_name;  -- 如果不存在则创建
3CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  -- 指定字符集

2. 选择数据库

1USE database_name;

3. 查看所有数据库

1SHOW DATABASES;

4. 删除数据库

1DROP DATABASE database_name;
2DROP DATABASE IF EXISTS database_name;  -- 安全删除

5. 查看数据库信息

1SHOW CREATE DATABASE database_name;  -- 查看创建语句

二、表操作

1. 创建表

1CREATE TABLE users (
2    id INT PRIMARY KEY AUTO_INCREMENT,
3    username VARCHAR(50) NOT NULL UNIQUE,
4    email VARCHAR(100) NOT NULL UNIQUE,
5    age INT DEFAULT 0,
6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
8);
9
10-- 创建带索引的表
11CREATE TABLE products (
12    id INT PRIMARY KEY AUTO_INCREMENT,
13    name VARCHAR(100) NOT NULL,
14    price DECIMAL(10,2) NOT NULL,
15    category VARCHAR(50),
16    stock INT DEFAULT 0,
17    INDEX idx_category (category),
18    INDEX idx_price (price)
19);
20
21-- 创建带外键的表
22CREATE TABLE orders (
23    id INT PRIMARY KEY AUTO_INCREMENT,
24    user_id INT NOT NULL,
25    total_amount DECIMAL(10,2) NOT NULL,
26    status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
27    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
28    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
29);

2. 查看表

1SHOW TABLES;  -- 查看所有表
2SHOW TABLES LIKE 'user%';  -- 模糊查询表名
3DESCRIBE users;  -- 查看表结构
4DESC users;  -- 简写
5SHOW CREATE TABLE users;  -- 查看创建表的SQL语句
6SHOW FULL COLUMNS FROM users;  -- 查看表的完整列信息

3. 修改表结构

1-- 添加列
2ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
3
4-- 修改列
5ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;
6
7-- 重命名列
8ALTER TABLE users CHANGE COLUMN age user_age INT;
9
10-- 删除列
11ALTER TABLE users DROP COLUMN phone;
12
13-- 添加索引
14ALTER TABLE users ADD INDEX idx_username (username);
15ALTER TABLE users ADD UNIQUE INDEX unique_email (email);
16
17-- 添加主键
18ALTER TABLE users ADD PRIMARY KEY (id);
19
20-- 添加外键
21ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
22
23-- 重命名表
24ALTER TABLE users RENAME TO customers;
25RENAME TABLE customers TO users;
26
27-- 修改表字符集
28ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 删除表

1DROP TABLE users;
2DROP TABLE IF EXISTS users;  -- 安全删除
3TRUNCATE TABLE users;  -- 清空表数据，保留表结构

三、数据操作（CRUD）

1. 插入数据 (INSERT)

1-- 插入单条数据
2INSERT INTO users (username, email, age) VALUES ('张三', 'zhangsan@example.com', 25);
3
4-- 插入多条数据
5INSERT INTO users (username, email, age) VALUES 
6    ('李四', 'lisi@example.com', 30),
7    ('王五', 'wangwu@example.com', 28),
8    ('赵六', 'zhaoliu@example.com', 35);
9
10-- 插入忽略重复（如果存在唯一约束冲突则忽略）
11INSERT IGNORE INTO users (username, email, age) VALUES ('张三', 'zhangsan@example.com', 26);
12
13-- 插入或替换（如果存在则替换）
14REPLACE INTO users (id, username, email, age) VALUES (1, '张三', 'zhangsan_new@example.com', 26);
15
16-- 插入或更新（ON DUPLICATE KEY UPDATE）
17INSERT INTO users (username, email, age) 
18VALUES ('张三', 'zhangsan@example.com', 26)
19ON DUPLICATE KEY UPDATE 
20    email = VALUES(email),
21    age = VALUES(age),
22    updated_at = CURRENT_TIMESTAMP;

2. 查询数据 (SELECT)

1-- 查询所有列
2SELECT * FROM users;
3
4-- 查询指定列
5SELECT id, username, email FROM users;
6
7-- 条件查询
8SELECT * FROM users WHERE age > 25;
9SELECT * FROM users WHERE age BETWEEN 20 AND 30;
10SELECT * FROM users WHERE username LIKE '张%';  -- 张开头
11SELECT * FROM users WHERE username LIKE '%三';  -- 三结尾
12SELECT * FROM users WHERE username LIKE '%张%';  -- 包含张
13SELECT * FROM users WHERE username NOT LIKE '张%';  -- 不是张开头
14
15-- IN 操作符
16SELECT * FROM users WHERE age IN (25, 30, 35);
17SELECT * FROM users WHERE age NOT IN (25, 30, 35);
18
19-- NULL 值判断
20SELECT * FROM users WHERE phone IS NULL;
21SELECT * FROM users WHERE phone IS NOT NULL;
22
23-- 多重条件
24SELECT * FROM users WHERE age > 25 AND email LIKE '%@example.com';
25SELECT * FROM users WHERE age < 20 OR age > 40;
26
27-- 排序
28SELECT * FROM users ORDER BY age ASC;  -- 升序
29SELECT * FROM users ORDER BY age DESC;  -- 降序
30SELECT * FROM users ORDER BY age DESC, username ASC;  -- 多重排序
31
32-- 限制结果
33SELECT * FROM users LIMIT 10;  -- 前10条
34SELECT * FROM users LIMIT 5, 10;  -- 从第6条开始，取10条（跳过前5条）
35SELECT * FROM users LIMIT 10 OFFSET 5;  -- 同上
36
37-- 去重
38SELECT DISTINCT age FROM users;
39
40-- 聚合函数
41SELECT COUNT(*) FROM users;  -- 总行数
42SELECT COUNT(DISTINCT age) FROM users;  -- 不同年龄数量
43SELECT AVG(age) FROM users;  -- 平均年龄
44SELECT SUM(age) FROM users;  -- 年龄总和
45SELECT MAX(age) FROM users;  -- 最大年龄
46SELECT MIN(age) FROM users;  -- 最小年龄
47
48-- 分组查询
49SELECT age, COUNT(*) as count FROM users GROUP BY age;
50SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 1;
51
52-- 别名
53SELECT u.username AS name, u.email AS mail FROM users AS u;
54
55-- 连接查询
56-- 内连接
57SELECT u.username, o.order_date, o.total_amount 
58FROM users u 
59INNER JOIN orders o ON u.id = o.user_id;
60
61-- 左连接
62SELECT u.username, o.order_date, o.total_amount 
63FROM users u 
64LEFT JOIN orders o ON u.id = o.user_id;
65
66-- 右连接
67SELECT u.username, o.order_date, o.total_amount 
68FROM users u 
69RIGHT JOIN orders o ON u.id = o.user_id;
70
71-- 子查询
72SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
73SELECT username FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders);
74
75-- EXISTS
76SELECT * FROM users u WHERE EXISTS (
77    SELECT 1 FROM orders o WHERE o.user_id = u.id
78);
79
80-- UNION
81SELECT username FROM users WHERE age < 20
82UNION
83SELECT username FROM users WHERE age > 40;
84
85-- CASE WHEN
86SELECT 
87    username,
88    age,
89    CASE 
90        WHEN age < 20 THEN '少年'
91        WHEN age BETWEEN 20 AND 30 THEN '青年'
92        WHEN age BETWEEN 31 AND 50 THEN '中年'
93        ELSE '老年'
94    END AS age_group
95FROM users;

3. 更新数据 (UPDATE)

1-- 更新所有行
2UPDATE users SET updated_at = CURRENT_TIMESTAMP;
3
4-- 条件更新
5UPDATE users SET age = 26 WHERE username = '张三';
6
7-- 更新多个字段
8UPDATE users SET 
9    age = age + 1,
10    updated_at = CURRENT_TIMESTAMP 
11WHERE username = '张三';
12
13-- 使用子查询更新
14UPDATE users u
15JOIN (
16    SELECT user_id, COUNT(*) as order_count 
17    FROM orders 
18    GROUP BY user_id
19) o ON u.id = o.user_id
20SET u.order_count = o.order_count
21WHERE u.id = 1;
22
23-- 限制更新数量
24UPDATE users SET status = 'active' WHERE status = 'inactive' LIMIT 10;

4. 删除数据 (DELETE)

1-- 删除所有数据（慎用！）
2DELETE FROM users;
3
4-- 条件删除
5DELETE FROM users WHERE age > 100;
6
7-- 限制删除数量
8DELETE FROM users WHERE status = 'inactive' LIMIT 100;
9
10-- 删除重复数据
11DELETE u1 FROM users u1
12INNER JOIN users u2 
13WHERE 
14    u1.id > u2.id AND 
15    u1.email = u2.email;  -- 保留id最小的

四、索引操作

1. 创建索引

1-- 创建普通索引
2CREATE INDEX idx_age ON users(age);
3
4-- 创建唯一索引
5CREATE UNIQUE INDEX idx_email ON users(email);
6
7-- 创建复合索引
8CREATE INDEX idx_name_age ON users(username, age);
9
10-- 创建全文索引（适用于文本搜索）
11CREATE FULLTEXT INDEX idx_content ON articles(content);
12
13-- 创建空间索引
14CREATE SPATIAL INDEX idx_location ON places(location);

2. 查看索引

1SHOW INDEX FROM users;
2SHOW INDEXES FROM users;

3. 删除索引

1DROP INDEX idx_age ON users;
2ALTER TABLE users DROP INDEX idx_email;

五、事务操作

1-- 开始事务
2START TRANSACTION;
3-- 或
4BEGIN;
5
6-- 执行SQL操作
7UPDATE accounts SET balance = balance - 100 WHERE id = 1;
8UPDATE accounts SET balance = balance + 100 WHERE id = 2;
9
10-- 提交事务
11COMMIT;
12
13-- 回滚事务
14ROLLBACK;
15
16-- 设置保存点
17SAVEPOINT sp1;
18-- 回滚到保存点
19ROLLBACK TO SAVEPOINT sp1;
20-- 释放保存点
21RELEASE SAVEPOINT sp1;

六、视图操作

1-- 创建视图
2CREATE VIEW user_orders_view AS
3SELECT u.username, o.order_date, o.total_amount
4FROM users u
5JOIN orders o ON u.id = o.user_id;
6
7-- 查看视图
8SELECT * FROM user_orders_view;
9
10-- 创建或替换视图
11CREATE OR REPLACE VIEW user_orders_view AS
12SELECT u.username, u.email, o.order_date, o.total_amount
13FROM users u
14JOIN orders o ON u.id = o.user_id;
15
16-- 查看视图定义
17SHOW CREATE VIEW user_orders_view;
18
19-- 删除视图
20DROP VIEW user_orders_view;

七、存储过程和函数

1. 创建存储过程

1DELIMITER //
2
3CREATE PROCEDURE GetUserByAge(IN min_age INT, IN max_age INT)
4BEGIN
5    SELECT * FROM users 
6    WHERE age BETWEEN min_age AND max_age
7    ORDER BY age ASC;
8END //
9
10DELIMITER ;
11
12-- 调用存储过程
13CALL GetUserByAge(20, 30);

2. 创建函数

1DELIMITER //
2
3CREATE FUNCTION GetUserCountByAgeRange(min_age INT, max_age INT) 
4RETURNS INT
5DETERMINISTIC
6BEGIN
7    DECLARE user_count INT;
8    SELECT COUNT(*) INTO user_count 
9    FROM users 
10    WHERE age BETWEEN min_age AND max_age;
11    RETURN user_count;
12END //
13
14DELIMITER ;
15
16-- 使用函数
17SELECT GetUserCountByAgeRange(20, 30);

八、用户和权限管理

1-- 创建用户
2CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
3CREATE USER 'username'@'%' IDENTIFIED BY 'password';  -- 允许任何主机
4
5-- 修改密码
6ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
7
8-- 授予权限
9GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';
10GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
11
12-- 授予创建用户的权限
13GRANT CREATE USER ON *.* TO 'admin'@'localhost';
14
15-- 查看权限
16SHOW GRANTS FOR 'username'@'localhost';
17
18-- 撤销权限
19REVOKE INSERT ON database_name.* FROM 'username'@'localhost';
20
21-- 删除用户
22DROP USER 'username'@'localhost';

九、备份和恢复

1-- 导出数据库（命令行）
2mysqldump -u username -p database_name > backup.sql
3
4-- 导出特定表
5mysqldump -u username -p database_name table1 table2 > backup.sql
6
7-- 导入数据库
8mysql -u username -p database_name < backup.sql
9
10-- 导出数据到CSV
11SELECT * INTO OUTFILE '/tmp/users.csv'
12FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
13LINES TERMINATED BY '\n'
14FROM users;
15
16-- 从CSV导入数据
17LOAD DATA INFILE '/tmp/users.csv'
18INTO TABLE users
19FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
20LINES TERMINATED BY '\n'
21IGNORE 1 ROWS;  -- 忽略标题行

十、实用技巧和最佳实践

1. 性能优化

1-- 使用EXPLAIN分析查询
2EXPLAIN SELECT * FROM users WHERE age > 25;
3
4-- 强制使用索引
5SELECT * FROM users FORCE INDEX (idx_age) WHERE age > 25;
6
7-- 优化表
8OPTIMIZE TABLE users;
9
10-- 分析表
11ANALYZE TABLE users;
12
13-- 检查表
14CHECK TABLE users;
15
16-- 修复表
17REPAIR TABLE users;

2. 系统信息查询

1-- 查看MySQL版本
2SELECT VERSION();
3
4-- 查看当前用户
5SELECT USER();
6
7-- 查看当前数据库
8SELECT DATABASE();
9
10-- 查看服务器状态
11SHOW STATUS;
12
13-- 查看系统变量
14SHOW VARIABLES LIKE 'max_connections';
15SHOW VARIABLES LIKE '%timeout%';

3. 时间处理

1-- 当前时间
2SELECT NOW();      -- 日期时间
3SELECT CURDATE();  -- 日期
4SELECT CURTIME();  -- 时间
5
6-- 日期格式化
7SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
8SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');
9
10-- 日期计算
11SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
12SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
13SELECT DATEDIFF('2024-12-31', '2024-01-01');
14
15-- 提取日期部分
16SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
17SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

这些是 MySQL 的基础操作，涵盖了数据库管理的各个方面。建议在实际使用时根据具体需求选择合适的方法，并注意数据安全。