MySQL 学习笔记#

-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;

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

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN email VARCHAR(200);

-- 删除
DROP TABLE users;
DROP DATABASE shop;

-- 插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@test.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@test.com'), ('carol', 'carol@test.com');

-- 查询
SELECT id, username, email FROM users WHERE age > 18 ORDER BY id DESC LIMIT 10;
SELECT * FROM users WHERE username LIKE 'a%';      -- 以 a 开头
SELECT * FROM users WHERE email IS NOT NULL;        -- 非空
SELECT COUNT(*) FROM users;                          -- 总数

-- 更新
UPDATE users SET email = 'new@test.com' WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;                      -- 条件删除
TRUNCATE TABLE users;                                -- 清空表（比 DELETE 快，不可回滚）

-- 聚合函数
SELECT COUNT(*), AVG(age), MAX(age), MIN(age), SUM(age) FROM users;

-- 分组
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING cnt > 5                    -- 分组后过滤（WHERE 在分组前过滤）
ORDER BY avg_salary DESC;

-- DISTINCT 去重
SELECT DISTINCT department FROM employees;

-- 内连接（交集）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接（左表全保留）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接（右表全保留）
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 标量子查询
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

-- IN 子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 20);

-- EXISTS 子查询
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 联合索引 (a, b, c)
INDEX idx_abc (a, b, c)

-- 能命中索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 不能命中索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

EXPLAIN SELECT * FROM users WHERE username = 'alice';

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 转出
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 转入
COMMIT;    -- 提交（两条同时生效）
-- 或 ROLLBACK;  -- 回滚（两条都不生效）

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;  -- 超过 2 秒记录

-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 主库配置
-- my.cnf: server-id=1, log_bin=mysql-bin, binlog_format=ROW
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 从库配置
-- my.cnf: server-id=2
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.1.100',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='password',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=154;
START REPLICA;

-- 创建用户
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'secure_password';

-- 授权
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'appuser'@'192.168.1.%';
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'appuser'@'192.168.1.%';

-- 撤销权限
REVOKE DELETE ON shop.* FROM 'appuser'@'192.168.1.%';

-- 删除用户
DROP USER 'appuser'@'192.168.1.%';