MySQL管理操作

┌──────────────┐  SQL   ┌──────────────┐
│ MySQL Client │───────>│ MySQL Server │
└──────────────┘  TCP   └──────────────┘

# 连接本地 MySQL
mysql -u root -p

# 连接远程 MySQL
mysql -h 10.0.1.99 -P 3306 -u root -p

# 指定数据库连接
mysql -u root -p -D test

-- 列出所有数据库
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-- 创建数据库
CREATE DATABASE test;

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

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS test;

-- 删除数据库
DROP DATABASE test;

-- 如果存在则删除
DROP DATABASE IF EXISTS test;

-- 切换到指定数据库
USE test;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 列出当前数据库的所有表
SHOW TABLES;

-- 查看表结构
DESC students;

-- 查看创建表的 SQL 语句
SHOW CREATE TABLE students;

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint       | NO   | PRI | NULL    | auto_increment |
| class_id | bigint       | NO   |     | NULL    |                |
| name     | varchar(100) | NO   |     | NULL    |                |
| gender   | varchar(1)   | NO   |     | NULL    |                |
| score    | int          | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 添加列
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

-- 修改列名和类型
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

-- 仅修改类型
ALTER TABLE students MODIFY COLUMN birthday DATE NOT NULL;

-- 删除列
ALTER TABLE students DROP COLUMN birthday;

-- 添加索引
ALTER TABLE students ADD INDEX idx_score (score);

-- 添加唯一索引
ALTER TABLE students ADD UNIQUE INDEX uni_name (name);

-- 删除索引
ALTER TABLE students DROP INDEX idx_score;

-- 删除表
DROP TABLE students;

-- 如果存在则删除
DROP TABLE IF EXISTS students;

-- 清空表数据（保留表结构）
TRUNCATE TABLE students;

-- 如果 id=1 存在，先删除再插入；不存在则直接插入
REPLACE INTO students (id, class_id, name, gender, score) 
VALUES (1, 1, '小明', 'M', 99);

INSERT INTO students (id, class_id, name, gender, score) 
VALUES (1, 1, '小明', 'M', 99) 
ON DUPLICATE KEY UPDATE 
    name = '小明', 
    gender = 'M', 
    score = 99;

INSERT IGNORE INTO students (id, class_id, name, gender, score) 
VALUES (1, 1, '小明', 'M', 99);

-- 复制整个表
CREATE TABLE students_backup SELECT * FROM students;

-- 复制部分数据
CREATE TABLE students_of_class1 
SELECT * FROM students WHERE class_id = 1;

-- 仅复制表结构
CREATE TABLE students_copy LIKE students;

-- 创建统计表
CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

-- 将查询结果写入统计表
INSERT INTO statistics (class_id, average) 
SELECT class_id, AVG(score) 
FROM students 
GROUP BY class_id;

+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |         86.5 |
|  2 |        2 | 73.666666666 |
|  3 |        3 | 88.333333333 |
+----+----------+--------------+

-- 强制使用 idx_class_id 索引
SELECT * FROM students 
FORCE INDEX (idx_class_id) 
WHERE class_id = 1 
ORDER BY id DESC;

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

-- 创建允许远程访问的用户
CREATE USER 'user2'@'%' IDENTIFIED BY 'password123';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';

-- 授予特定数据库的所有权限
GRANT ALL PRIVILEGES ON test.* TO 'user1'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON test.* TO 'user1'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'user1'@'localhost';

-- 撤销权限
REVOKE INSERT, UPDATE ON test.* FROM 'user1'@'localhost';

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

-- MySQL 5.7.6+（8.0 推荐写法）
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password';

-- MySQL 5.7.5 及以下（PASSWORD() 函数在 8.0 中已被移除）
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('new_password');

-- 查看服务器状态
SHOW STATUS;

-- 查看特定状态
SHOW STATUS LIKE 'Threads%';

-- 查看变量
SHOW VARIABLES;

-- 查看特定变量
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看完整信息
SHOW FULL PROCESSLIST;

+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  5 | root | localhost       | test | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+----------+------------------+

-- 终止指定连接
KILL 5;

-- 终止查询但保持连接
KILL QUERY 5;

https://dev.mysql.com/downloads/workbench/

-- 方式一
EXIT;

-- 方式二
QUIT;

-- 方式三
\q