- Published on
MySQL
- Authors
- Name
- MissTree
安装
MySQL 官网
下载选择 根据个人系统选择下载
centos7 安装 MySQL
当前使用的是Debian系统，所以选择使用apt安装，但是在仓库没有指定的版本，所以到 下载页面 页面跳转 归档 ,选择对应的归档文件选项下载
选择第一个版本下载，下载完成后使用命令安装
可以直接到 https://downloads.mysql.com/archives/installer 页面选定windows系统直接MSI文件安装
# 链接可以鼠标右键获取下载链接
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-server_8.0.15-1debian12_amd64.deb-bundle.tar
# 解压到指定目录
tar -xvf mysql-server_8.0.15-1debian12_amd64.deb-bundle.tar -C /usr/local/mysql/
# 进入目录
cd /usr/local/mysql/
# 安装依赖 否则在安装下面的文件会报错
sudo apt-get update
sudo apt-get install libaio1 libmecab2
# 安装下列几个文件 MySQL的密码可以设置为空
sudo dpkg -i mysql-common_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-client-plugins_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-client-core_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-client_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-client_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-server-core_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-server_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-server_8.0.15-1debian12_amd64.deb
# 验证是否安装成功
mysql -V
# 安装完成后会提示输入密码，默认密码为空，直接回车即可
# 进入mysql 输入密码
mysql -u root -p
若是安装失败，可以尝试重新安装，或者查看错误日志，查看错误原因
# 移除已安装的 MySQL 包：
sudo dpkg --purge mysql-community-server-core mysql-community-server mysql-server
# 重新安装所有 .deb 文件：
sudo dpkg -i *.deb
sudo apt-get install -f
账户密码新增修改
# 进入mysql 输入密码
mysql -u root -p
# 输入密码后，进入mysql 命令行 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '你的新密码';
# 查看用户
select user,host from mysql.user;
# 新增用户 new_user-用户名 password-密码
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
# 修改密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
# 刷新权限
flush privileges;
# 退出mysql
exit;
常用命令
常见命令基本和SQL一致，这里只列举一些常用的命令,记得在后面加 ; ,否则MySQL会以为命令还没结束，会一直等待输入，导致命令执行失败。
| 分类 | 全称 | 描述 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
| DML | Data Manipulation Language | 数据指作语言，用来对数据库表中的数据进行增制改 |
| DQL | Data Query Language | 数据询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据空制语言，用来创建数据库用户、控制数据库的访问权限 |
相关命令关联
- DDL
- CREATE：创建数据库对象，如数据库、表、视图等。
- ALTER：修改数据库对象的结构，如修改表的列、添加索引等。
- DROP：删除数据库对象，如删除表、视图等。
- TRUNCATE：清空表中的数据，保留表的结构。
- RENAME：重命名数据库对象，如重命名表、视图等。
- COMMENT：添加注释到数据库对象，如添加表的注释。
- SHOW：显示数据库对象的信息，如显示表的结构、索引等。
- DESCRIBE：显示表的结构，与 SHOW TABLE STATUS 类似。
- EXPLAIN：分析查询语句的执行计划，用于优化查询性能。
- USE：切换当前数据库。
- DML
- INSERT：向表中插入数据。
- UPDATE：更新表中的数据。
- DELETE：删除表中的数据。
- MERGE：将数据插入或更新到表中。
- LOAD DATA：从文件中加载数据到表中。
- CALL：调用存储过程或函数。
- DQL
- SELECT：从表中查询数据。
- FROM：指定查询的表。
- WHERE：指定查询条件。
- GROUP BY：对查询结果进行分组。
- HAVING：对分组后的结果进行过滤。
- ORDER BY：对查询结果进行排序。
- LIMIT：限制查询结果的数量。
- JOIN：连接多个表进行查询。
- UNION：将多个查询结果合并。
- INTERSECT：获取两个查询结果的交集。
- EXCEPT：获取两个查询结果的差集。
- DCL
- GRANT：授予用户权限。
- REVOKE：撤销用户权限。
- CREATE USER：创建新用户。
查看数据库
# 列出 MySQL 服务器上的所有数据库：
SHOW DATABASES;
# 创建数据库
CREATE DATABASE database_name;
# 没有就创建数据库，有就不创建
CREATE DATABASE if not exits database_name;
# 不要创建 utf8，因为utf8是3字节的，有些数据是4字节的，会导致数据丢失
# 可以使用 utf8mb4，utf8mb4是4字节的，兼容utf8
CREATE DATABASE database_name default charset utf8mb4;
# 删除数据库 慎用
DROP DATABASE database_name;
# 切换到某个数据库
USE database_name;
# 查看当前数据库中的所有表
SHOW TABLES;
# 查看表结构
DESCRIBE table_name;
# 查看表数据更详细的命令
SHOW CREATE TABLE table_name;
# 查看数据库大小
SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
# 查看表的大小
# SELECT table_name AS "Table", ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = 'database_name' ORDER BY (data_length + index_length) DESC;
# 查看数据库的字符集和排序规则
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';
# 查看当前连接的数据库
SELECT DATABASE();
# 查看 MySQL 状态
SHOW STATUS;
# 查看 MySQL 变量
SHOW VARIABLES;
# 查看当前连接的用户
SELECT USER();
# 查看当前连接的信息
SHOW PROCESSLIST;
# 查看 MySQL 版本
SELECT VERSION();
创建库表数据
# 创建数据库
CREATE DATABASE blog;
# 创建一个博客评论表
CREATE TABLE comments (
id INT AUTO_INCREMENT PRIMARY KEY, -- 评论 ID，主键
content TEXT NOT NULL, -- 评论内容
user_id INT NOT NULL, -- 评论者用户 ID
username VARCHAR(100) NOT NULL, -- 评论者用户名（冗余字段，避免频繁查询用户表）
post_id INT NOT NULL, -- 评论所属的文章 ID
parent_id INT DEFAULT NULL, -- 父评论 ID（用于支持嵌套评论）
status ENUM('pending', 'approved', 'deleted') DEFAULT 'pending', -- 评论状态
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 评论创建时间
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 评论更新时间
metadata JSON -- 扩展字段（JSON 格式，支持灵活扩展）
);
# 模板
CREATE TABLE 表名 (
字段1 数据类型 [comment '字段1注释'],
字段2 数据类型 [comment '字段2注释'],
...
字段n 数据类型 [comment '字段n注释']
)[comment '表注释'];
# 注意:[..]为可选参数，最后一个字段后面没有逗号
# 查看表字段
DESC 表名;
+------------+--------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra
|
+------------+--------------------------------------+------+-----+-------------------+-----------------------------------------------+
| id | int | NO | PRI | NULL | auto_increment
|
| content | text | NO | | NULL |
|
| user_id | int | NO | | NULL |
|
| username | varchar(100) | NO | | NULL |
|
| post_id | int | NO | | NULL |
|
| parent_id | int | YES | | NULL |
|
| status | enum('pending','approved','deleted') | YES | | pending |
|
| created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED
|
| updated_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| metadata | json | YES | | NULL |
|
+------------+--------------------------------------+------+-----+-------------------+-----------------------------------------------+
# 查询创建表的语句
show create table 表名;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comments | CREATE TABLE `comments` (
`id` int NOT NULL AUTO_INCREMENT,
`content` text NOT NULL,
`user_id` int NOT NULL,
`username` varchar(100) NOT NULL,
`post_id` int NOT NULL,
`parent_id` int DEFAULT NULL,
`status` enum('pending','approved','deleted') DEFAULT 'pending',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`metadata` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# ENGINE=InnoDB 表示使用 InnoDB 存储引擎，AUTO_INCREMENT=3 表示自增主键从 3 开始，CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 表示字符集为 utf8mb4，排序规则为 utf8mb4_0900_ai_ci。
# 直接添加字段
ALTER TABLE comments ADD new_column_name data_type;
ALTER TABLE comments ADD like_count INT DEFAULT 0;
# 修改字段类型
ALTER TABLE comments MODIFY column_name new_data_type;
ALTER TABLE comments MODIFY like_count INT;
# 修改字段名称
ALTER TABLE comments CHANGE old_column_name new_column_name data_type;
ALTER TABLE comments CHANGE like_count likes INT;
# 删除字段
ALTER TABLE comments DROP column_name;
ALTER TABLE comments DROP likes;
# 插入一条顶级评论
INSERT INTO comments (content, user_id, username, post_id, status, metadata)
VALUES ('这是一条评论内容', 1, 'john_doe', 101, 'approved', '{"ip": "192.168.1.1", "device": "iPhone"}');
# 插入一条嵌套评论
INSERT INTO comments (content, user_id, username, post_id, parent_id, status, metadata)
VALUES ('这是对第一条评论的回复', 2, 'jane_doe', 101, 1, 'approved', '{"ip": "192.168.1.2", "device": "Android"}');
# 插入模板
INSERT INTO 表名 (字段1, 字段2, ..., 字段n) VALUES (值1, 值2,..., 值n);
# 注意:字段和值的顺序要对应，最后一个字段后面没有逗号 插入的值要求在字段数据范围中
# 给表中所有字段依次赋值
INSERT INTO 表名 VALUES (值1, 值2,..., 值n);
# 批量插入
INSERT INTO 表名 (字段1, 字段2,..., 字段n) VALUES (值1, 值2,..., 值n), (值1, 值2,..., 值n), ..., (值1, 值2,..., 值n);
INSERT INTO 表名 VALUES (值1, 值2,..., 值n), (值1, 值2,..., 值n), ..., (值1, 值2,..., 值n);
# 更新数据
UPDATE 表名 SET 字段1=值1, 字段2=值2,..., 字段n=值n WHERE 条件;
UPDATE 表名 SET 字段1=值1, 字段2=值2,..., 字段n=值n; # 不添加条件会更新所有数据
# 批量更新
UPDATE 表名 SET 字段1=值1, 字段2=值2,..., 字段n=值n WHERE 条件1 OR 条件2 OR ... OR 条件n;
# 删除数据
DELETE FROM 表名 WHERE 条件;
DELETE FROM 表名; # 不添加条件会删除所有数据
# 查询某篇文章的所有评论
SELECT * FROM comments WHERE post_id = 101;
# 查询某篇文章的顶级评论
SELECT * FROM comments WHERE post_id = 101 AND parent_id IS NULL;
# 查询某条评论的所有回复
SELECT * FROM comments WHERE parent_id = 1;
# 修改表名
ALTER TABLE comments RENAME TO new_table_name;
# 删除表 两种方式
DROP TABLE [ if exits ] comments;
TRUNCATE TABLE comments; # 删除表，重新创建表
# 使用 metadata 字段扩展
# 将新字段存储在 metadata 字段中，避免修改表结构。例如，存储评论的点赞数
UPDATE comments SET metadata = JSON_SET(metadata, '$.like_count', 10) WHERE id = 1;
创建用户
# 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'soaeon'@'localhost' IDENTIFIED BY 'password';
# 查看用户权限 将 username 替换为实际的用户名，host 替换为用户的主机（如 localhost 或 %）
SHOW GRANTS FOR 'username'@'host';
# 为用户授予权限
GRANT CREATE, INSERT, UPDATE, DELETE ON *.* TO 'soaeon'@'localhost';
# CREATE：允许用户创建数据库和表。
# INSERT：允许用户向表中插入数据。
# UPDATE：允许用户更新表中的数据。
# DELETE：允许用户删除表中的数据。
# DROP：允许用户删除数据库和表。
# SELECT：允许用户查询表中的数据。
# *.*：表示这些权限适用于所有数据库和所有表。你也可以指定具体的数据库和表，例如 database_name.* 表示适用于指定数据库中的所有表。
# 追加权限 SELECT-查看选择权限
GRANT SELECT ON *.* TO 'soaeon'@'localhost';
# 刷新权限
FLUSH PRIVILEGES;
# 撤销权限 也要 FLUSH PRIVILEGES; 刷新权限
REVOKE SELECT ON 数据库名.表名 FROM '用户名'@'主机';
REVOKE SELECT DELETE ON *.* FROM 'soaeon'@'localhost';
# 用户的权限也可以授权所有权限
GRANT ALL PRIVILEGES ON *.* TO 'soaeon'@'localhost';
特殊情况，已经给用户授权了所有权限，但是撤销某一数据库权限的时候，会报错
# 网上解决办法：先撤销所有权限，然后再授权 结果还是不行
GRANT ALL PRIVILEGES ON database_name.* TO 'soaeon'@'localhost';
REVOKE ALL PRIVILEGES ON database_name.* FROM'soaeon'@'localhost';
# 最好的是先撤销全部权限，然后再授权 以最小权限为原则
REVOKE ALL PRIVILEGES ON *.* FROM'soaeon'@'localhost';
GRANT SELECT ON database_name1.* TO'soaeon'@'localhost';
GRANT all ON database_name2.* TO'soaeon'@'localhost';
开启远程可视化访问
如果是家用电脑要记得在路由器防火墙开放3306端口
# 开放3306端口
sudo ufw allow 3306
# 查看3306端口是否开放
sudo netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 812/sshd: /usr/sbin
tcp6 0 0 :::3306 :::* LISTEN 1514934/mysqld
tcp6 0 0 :::33060 :::* LISTEN 1514934/mysqld
tcp6 0 0 :::22 :::* LISTEN 812/sshd: /usr/sbin
# 给远程的用户设置权限
# 查看用户权限
SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| soaeon | localhost |
+------------------+-----------+
# 给远程的用户设置权限
UPDATE mysql.user SET host = '%' WHERE user = 'soaeon';
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| soaeon | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
# host为% 表示所有主机都可以访问
# 或者在创建的时候设置外部访问的权限
CREATE USER 'soaeon'@'%' IDENTIFIED BY 'password';
# 刷新权限
FLUSH PRIVILEGES;
# 修改 mysql 的配置文件
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# 开放ipv4和ipv6访问 找到 bind-address 设置
[mysqld]
bind-address = ::
# 使用ipv6的报错 ：IP address '240e:47c:ca9:57d:25da:273a:e0b0:f2d9' could not be resolved: Name or service not known
# 因为使用了IPV6 会用到解析所以还要设置
[mysqld]
....
skip-name-resolve
# 保存退出 然后重启mysql服务
# 重启mysql服务
sudo systemctl restart mysql
sudo systemctl enable mysql
接下来就可以在Navicat Permium中连接了
