MySQL常用操作记录【未完待续】MySQL的常用操作，使用的系统为Ubuntu 22，MySQL版本为8.0，未完待续

Ubuntu下的MySQL安装、卸载和图形化工具

Ubuntu下安装MySQL

更新包列表 sudo apt update
查看可使用的安装包 sudo apt search mysql-server

安装MySQL服务器

# 安装最新版本
sudo apt install -y mysql-server
# 安装指定版本
sudo apt install -y mysql-server-8.0

检查MySQL状态 sudo systemctl status mysql
启动MySQL服务 sudo systemctl start mysql
设置MySQL开机自启动(默认自启动) sudo systemctl enable mysql

默认使用auth_socket身份验证，所以会跳过为root设置密码，可以通过ALTER_USER使用密码验证

# 登录mysql
sudo mysql
# 设置密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
# 刷新权限
FLUSH PRIVILEGES;
# 退出mysql
EXIT;

运行 MySQL 安全脚本以增强安全性 sudo mysql_secure_installation
- y setup VALIDATE PASSWORD 使用密码验证
- 0 low 使用低等密码强度(本地开发为方便，密码简单点)
- y Remove anonymous users 移除匿名用户
- y Disallow root login remotely 禁止远程登录root
- y Remove test database and access to it 移除测试数据库及其访问权限
- y Reload privilege tables now 现在重新加载权限表
登录MySQL mysql -u root -p
输入前面设置的密码
验证安装 SELECT VERSION();

创建其他用户

mysql -u root -p
CREATE USER 'my_user'@'%' IDENTIFIED BY 'my_passwordL0';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'%';
FLUSH PRIVILEGES;
EXIT;

Ubuntu下卸载MySQL

停止MySQL服务 sudo systemctl stop mysql
卸载MySQL包 sudo apt-get remove --purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
删除MySQL数据目录注意：此操作将删除所有数据库 sudo rm -rf /etc/mysql /var/lib/mysql
删除MySQL相关的配置文件 sudo rm -rf /var/log/mysql sudo rm -rf /var/run/mysqld
清理无用的依赖包 sudo apt-get autoremove sudo apt-get autoclean
确认卸载干净 dpkg -l | grep mysql

Ubuntu下安装图形化客户端管理工具dbeaver官网

下载wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb
安装：打开文件夹，右键选择Open With Other Application，选择Software Install，输入密码后确认安装
切换中文：Window - Preferences - User Interface - Language - Simplified Chinese

数据库操作

创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;
# 指定编码创建数据库
CREATE DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看数据库

# 查看当前主机下所有数据库
SHOW DATABASES;
# 查看数据库详细信息
SHOW CREATE DATABASE 数据库名;

使用数据库

# 使用数据库
USE 数据库名;
# 查看当前使用的数据库名
SELECT DATABASE();

删除数据库

DROP DATABASE IF EXISTS 数据库名;

数据类型

数据类型	名称	说明	举例	变量范围
TINYINT	小整数	小范围整数	`status TINYINT` 表示状态，如0（关闭）和1（开启）	-128 到 127（无符号为 0 到 255）
SMALLINT	小整数	中小范围整数	`quantity SMALLINT` 存储库存数量，适合小范围值	-32,768 到 32,767（无符号为 0 到 65,535）
MEDIUMINT	中等整数	中等范围整数	`views MEDIUMINT` 存储页面访问量	-8,388,608 到 8,388,607（无符号为 0 到 16,777,215）
INT	整数	整数值	`age INT` 存储人的年龄	-2,147,483,648 到 2,147,483,647
BIGINT	大整数	大范围整数	`total BIGINT` 存储大数据量，如总销售额	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
FLOAT	浮点数	小数值	`price FLOAT` 存储商品价格等非精确数据	-3.402823466E+38 到 3.402823466E+38
DOUBLE	双精度浮点数	更高精度的小数值	`average DOUBLE` 存储高精度数据，如科学计算	-1.7976931348623157E+308 到 1.7976931348623157E+308
DECIMAL(p,s)	精确数	高精度数字	`amount DECIMAL(10,2)` 存储金额，保证精确到小数点后两位	根据p（总位数）和s（小数位数）决定范围
CHAR(n)	固定长度字符串	固定长度字符串	`code CHAR(10)` 存储固定长度的代码，若不足则填充空格	0 到 n 字符（最大255）
VARCHAR(n)	可变长度字符串	不定长度字符串	`username VARCHAR(50)` 存储用户的名称，最大50个字符	0 到 n 字符（最大65535）
TEXT	文本	较小的文本数据	`description TEXT` 存储简短描述	最大65,535 字符
MEDIUMTEXT	中文本	中等大小的文本数据	`content MEDIUMTEXT;` 存储较长内容	最大16,777,215 字符
LONGTEXT	长文本	非常大的文本数据	`article LONGTEXT` 存储长篇文章	最大4,294,967,295 字符
ENUM	枚举	有限选项	`gender ENUM('M', 'F')` 存储性别，限制选项为'M'或'F'	最大65535个值
SET	集合	多个选项	`tags SET('tag1', 'tag2')` 存储多个标签，支持组合选项	最大64个不同的值
TIME	时间	时间	`event_time TIME` 存储事件发生的时间	'-838:59:59' 到 '838:59:59'
DATE	日期	日期	`birthdate DATE` 存储出生日期	'1000-01-01' 到 '9999-12-31'
DATETIME	日期时间	日期和时间	`created_at DATETIME` 存储记录创建的日期和时间	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP	时间戳	事件的时间	`last_login TIMESTAMP` 存储用户最后一次登录的时间	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC
JSON	JSON	JSON格式的数据	`data JSON` 存储结构灵活的数据	无固定范围，受限于存储大小

数据表操作

创建数据表

CREATE TABLE IF NOT EXISTS 数据表名 (
    字段名称 数据类型 [完整性约束条件],
    字段名称 数据类型 [完整性约束条件],
    ...
);

完整性约束条件

名称	描述	示例
NOT NULL	确保列不能有 NULL 值	`username VARCHAR(50) NOT NULL`
DEFAULT	为列指定默认值，当未提供该列的值时使用	`created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`
CHECK	确保列中的值满足特定条件	`age INT CHECK (age >= 18)`
UNIQUE	确保列中的所有值都是唯一的	`email VARCHAR(100) UNIQUE`
AUTO_INCREMENT	使整型列在每次插入新行时自动增加	`id INT AUTO_INCREMENT`
PRIMARY KEY	唯一标识表中的每一行，组合了 NOT NULL 和 UNIQUE 的特性，支持联合主键	`PRIMARY KEY (student_id, course_id)`
FOREIGN KEY	确保一个列的值在另一张表的主键或唯一列中存在	`FOREIGN KEY (user_id) REFERENCES users(id)`

UNIQUE 和 PRIMARY KEY 的区别：

确保列中的所有值都是唯一的，但是 UNIQUE 允许有 NULL 值（每个列可以有多个 NULL）
一个表可以有多个 UNIQUE 约束，但是只能有一个 PRIMARY KEY
UNIQUE 创建唯一索引， PRIMARY KEY 创建主键索引，一个表可以有多个唯一索引，只能有一个主键索引

FOREIGN KEY 的主要使用场景包括

表之间的关联关系

如一个用户可以拥有多个订单，订单表中的 user_id 必须在用户表中存在

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

删除或更新时级联操作
- 外键可以定义 ON DELETE CASCADE 或 ON UPDATE CASCADE，当引用表的数据被删除或更新时，子表中的数据也会同步更新或删除。此功能适用于保持数据同步更新或自动清除无效数据
- 如删除了一个用户，系统自动删除该用户的所有订单
```
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE  # 删除用户时，自动删除关联的订单
);
```

多对多关系

一个学生可以选修多门课程，一门课程也可以有多名学生。可以使用一个中间表来存储学生和课程的对应关系，其中两个外键分别指向 students 表和 courses 表

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)  # 联合主键，防止重复记录
);

层次结构关系

表示员工和上级之间的关系

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)  # 外键指向同一个表
);

查看数据表

# 查看当前数据库下的所有数据表
SHOW TABLES;

# 查看数据表结构
DESC 数据表名;

# 查看数据表创建语句，表的结构和定义
SHOW  CREATE TABLE 数据表名;

# 查看数据表的状态信息，包含有关表的运行时信息和统计数据
SHOW TABLE STATUS LIKE 数据表名;
# 等价于
SELECT * FROM information_schema.tables WHERE table_schema = 数据库名 AND table_name = 数据表名;

删除数据表

DROP TABLE IF EXISTS 数据表名;

增删改字段

增加字段ALTER TABLE 数据表名 ADD 字段名称数据类型及约束条件 [FIRST | AFTER 已有字段名称];
- 例如：
```
ALTER TABLE users ADD age INT NOT NULL FIRST;
ALTER TABLE users ADD age INT AFTER username;
```
删除字段ALTER TABLE 数据表名 DROP COLUMN 字段名称;
- 例如：
```
ALTER TABLE users DROP COLUMN age;
```
修改字段ALTER TABLE 数据表名 MODIFY 字段名称新的数据类型及约束条件 [FIRST | AFTER 已有字段名称];
- 例如：
```
ALTER TABLE users MODIFY username VARCHAR(100);
```
修改字段名称及类型ALTER TABLE 数据表名 CHANGE 旧字段名称新字段名称新的数据类型及约束条件 [FIRST | AFTER 已有字段名称];
- 例如：
```
ALTER TABLE users CHANGE username user_name VARCHAR(150);
```

增删改主键

添加主键ALTER TABLE 数据表名 ADD PRIMARY KEY (字段名称);

例如：

ALTER TABLE users ADD PRIMARY KEY (id);
# 添加复合主键
ALTER TABLE users ADD PRIMARY KEY (id, email);

删除主键ALTER TABLE 数据表名 DROP PRIMARY KEY;
- 有自动增长约束的主键直接删除会失败，需要先去掉自动增长约束后再进行删除
- 例如：
```
ALTER TABLE users DROP PRIMARY KEY;
```
修改主键(不支持直接修改，需要删除后添加)
- 例如：
```
ALTER TABLE users DROP PRIMARY KEY, ADD PRIMARY KEY (email);
```

增删改查唯一索引

添加唯一索引ALTER TABLE 数据表名 ADD UNIQUE (字段名称);
- 例如：
```
ALTER TABLE users ADD UNIQUE (email);
```
删除唯一索引ALTER TABLE 数据表名 DROP INDEX 唯一字段名称;
- 例如：
```
ALTER TABLE users DROP INDEX unique_email;
```

修改唯一索引(不支持直接修改，需要删除后添加)

例如：

ALTER TABLE users DROP INDEX unique_username;
ALTER TABLE users ADD UNIQUE (user_name);

查询唯一索引SHOW INDEX FROM 数据表名;
- 例如：
```
SHOW INDEX FROM users;
```

其他操作

修改数据表名字

ALTER TABLE 旧数据表名 RENAME TO 新数据表名;

修改数据表自增值

ALTER TABLE 数据表名 AUTO_INCREMENT = 新值;
# 查看结果
SHOW TABLE STATUS LIKE 数据表名;

删除数据表的数据

DELETE FROM 数据表名;

数据迁移

小数据量mysqldump迁移

# 导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出文件.sql;
# 导出特定表
mysqldump -u 用户名 -p 数据库名 数据表名 > 导出文件.sql;

# 导入操作
mysql -u 用户名 -p
CREATE DATABASE 数据库名;
mysql -u 用户名 -p 数据库名 < 导出文件.sql;

mysqldump参数

参数	缩写	含义	默认值	示例
--user	-u	用户名		mysqldump -u username
--password	-p	用户密码，可以直接跟密码（不安全），也可以直接输入 -p，然后在提示时输入密码		mysqldump -p
--host	-h	主机地址	localhost	mysqldump -h hostname
--port	-P	端口号	3306	mysqldump -P 3306
--databases	-B	导出多个数据库		mysqldump --databases db1 db2
--no-data	-d	仅导出表结构，不导出数据	off	mysqldump --no-data dbname
--single-transaction		在导出时使用事务，适用于 InnoDB 表	off	mysqldump --single-transaction dbname
--lock-tables		在导出时锁定所有表，确保数据一致性	on	mysqldump --lock-tables dbname
--add-drop-database		在每个创建数据库的语句前添加 DROP DATABASE 语句	off	mysqldump --add-drop-database dbname
--add-drop-table		在每个创建表的语句前添加 DROP TABLE 语句	on	mysqldump --add-drop-table dbname
--set-gtid-purged		控制 GTID 信息的输出	auto	mysqldump --set-gtid-purged=ON
--routines	-R	导出存储过程和函数	off	mysqldump --routines dbname
--triggers		导出触发器	on	mysqldump --triggers dbname
--ignore-table		在导出时忽略特定表		mysqldump --ignore-table=dbname.tablename1 --ignore-table=dbname.tablename2 dbname
--where	-w	仅导出符合特定条件的记录		mysqldump --where="status = 'active' AND age > 30" > 导出文件.sql

大数据量文件迁移

# 导出数据
SELECT * INTO OUTFILE '/var/lib/mysql-files/数据表名.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM 数据表名;

# 重新创建表后，导入数据
LOAD DATA INFILE '/var/lib/mysql-files/数据表名.csv'
INTO TABLE 数据表名
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

指令片段

创建用户列表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    gender ENUM('Male', 'Female') DEFAULT 'Male',
    email varchar(50)
);

INSERT INTO users (name, age, gender, email) VALUES
('Alice', 20, 'Female', 'aa@xx.mail'),
('Bob', 22, 'Male', 'bb@xx.mail'),
('Charlie', 19, 'Male', 'cc@xx.mail');

SELECT * FROM users;

创建25条测试数据

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100),
    gender ENUM('Male', 'Female') DEFAULT 'Male',
    age INT,
    avatar_url VARCHAR(255)
);

INSERT INTO users (username, gender, age, avatar_url)
SELECT
    CONCAT('user', FLOOR(1000 + (RAND() * 9000))),
    IF(RAND() > 0.5, 'Male', 'Female'),
    FLOOR(18 + (RAND() * 50)),
    CONCAT('https://example.com/avatar', FLOOR(RAND() * 1000), '.jpg')
FROM
    (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1,
    (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2;

/*
    INSERT INTO ... SELECT ... 将 SELECT 到的数据插入表中
    SELECT ... FROM ... 从 ... 中 SELECT 数据
    这里的 FROM 是 UNION ALL 生成的虚拟数据集
    UNION ALL 合并多个查询的结果集，且保留重复的行
    所以 t1 和 t2 分别有 5 条数据
    通过笛卡尔积（交叉连接），最终生成 25 行虚拟数据集
    笛卡尔积：虚拟表 t1 中的每一行都与虚拟表 t2 的每一行进行组合，因此最终得到 25 行的输出
*/

创建1000条测试数据

# 创建一个名为 users 的表
create table users (
    id int,
    name varchar(20),
    gender char(6),
    email varchar(50)
);

# 修改 SQL 语句的结束符为 $$，以便在存储过程中使用分号
delimiter $$

# 创建名为 auto_insert 的存储 procedure(过程)
create procedure auto_insert()
BEGIN
    # 声明一个整数变量 i，初始化为 1
    declare i int default 1;

    # 开始一个 while 循环，条件是 i 小于 1000
    while (i < 1000) do
        insert into users values (i, 'shanhe', 'male', concat('shanhe', i, '@helloworld'));
        set i = i + 1;
        # 这个 SELECT 语句的结果可以用于查看当前进度
        select concat('shanhe', i, '_ok');
    end while;
END$$ # 结束存储过程定义，使用 $$ 作为结束符

# 将 SQL 语句的结束符改回默认的分号 ;
delimiter ;

# 显示存储过程 auto_insert 的创建语句，格式化输出
# \G 是一个命令行选项，用于以垂直格式显示查询结果
show create procedure auto_insert\G 

# 调用存储过程 auto_insert，执行插入操作
call auto_insert();

其他

MySQL指令基础语法

注意：sql指令语句后要以 ; 或者 \g 结尾
单行注释使用 -- 或 # ，多行注释使用 /* ... */

存储过程

查看存储过程 SELECT ROUTINE_NAME FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';
删除存储过程 DROP PROCEDURE IF EXISTS 过程名;