MySQL 学习小记

安装 MySQL

由于安装并不是本文着重记录的点，故安装过程就简单过一下，个人学习使用推荐使用 Docker 安装，简单快捷且不会有残留文件。

使用 Docker 快速创建本地练习环境

使用 Docker 搭建 MySQL 学习环境无疑是最简单快捷的方式，不挑平台可跳过繁琐冗杂的安装部署过程。若还不会使用 Docker 可参考这篇教程(还没写待更新hhh)。

# 拉取镜像，若网络问题下载失败可使用镜像：docker.ee.tc 或 docekr.tddt.io
docker pull mysql
# 运行 MySQL
docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql

Linux

Ubuntu/Debian 系列

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql
sudo cat /etc/mysql/debian.cnf # 获取默认密码
ALTER USER root@localhost IDENTIFIED  BY '123456'; # 修改 root 密码

CentOS/RHEL 系列

sudo yum update
sudo yum install mysql-server
sudo systemctl start mysqld
sudo grep 'temporary password' /var/log/mysqld.log

Windows

在 Windows 上安装 MySQL 最简单的方式是使用 MySQL Installer（官方提供的图形化安装工具）

1. 下载 MySQL Installer： 前往 MySQL官网 下载适合你系统的版本。

   

2. 运行安装程序：

   • 选择合适的安装类型（开发者默认、服务器等）。
   • 根据提示选择安装的组件（例如 MySQL Server、Workbench 等）。
   • 设置 root 密码并进行基本配置。

3. 完成安装并启动 MySQL 服务。

macOS

使用 Homebrew 安装

Homebrew 是一个流行的包管理器，最初为 macOS 设计，后来也扩展到了 Linux 系统。它的主要目的是简化软件安装过程，推荐 macOS 用户安装。若没有 Homebrew 可以参考这篇教程，

brew install mysql
brew services start mysql

使用 DMG 安装包

1. 前往 MySQL 官网 下载适用于 macOS 的 DMG 文件。

   

2. 安装 DMG 文件：

   • 双击 DMG 文件，按照安装向导进行安装；
   • 配置 MySQL；
   • 安装完成后，可以使用 System Preferences（系统偏好设置）中的 MySQL 控制面板启动或停止 MySQL 服务。

3. 配置密码： 安装完成后，默认情况下 root 用户没有密码。你可以通过命令行或 MySQL Workbench 来设置密码：

   mysql -u root
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

源码安装

本文仅作文 MySQL 入门，不采用源码安装方式，源码安装虽然提供更高的定制性和灵活性，但安装过程复杂、维护和更新困难、依赖管理繁琐，并且可能影响系统稳定性，适合对性能和配置有高度要求的用户。

安装 MySQL 客户端

mysql

这是 MySQL 数据库默认的命令行工具，适用于执行 SQL 查询、管理数据库和与 MySQL 服务器进行交互。它是 MySQL 安装时自带的基本工具，功能相对简单，主要用于传统的 SQL 查询和数据库管理。

若是使用 Docker 安装的 MySQL 服务器，则需要单独安装该客户端，macOS 安装方式为

# 启动方式：
mysql -u username -p -h hostname

MySQL Shell

这是一个更为高级和灵活的命令行工具，支持 SQL、JavaScript 和 Python 脚本，适用于更复杂的数据库管理任务、自动化和开发工作。MySQL Shell 引入了更现代的特性，如对 JSON 数据类型的原生支持、对高级数据库管理操作的增强功能（例如与 MySQL InnoDB 集群和 MySQL 8.0 特性的兼容性），并且在功能上比 mysql 客户端更强大。

下载地址：https://dev.mysql.com/downloads/shell/

# 启动方式
mysqlsh --uri username@hostname

MySQL Workbench

MySQL Workbench 是 MySQL 官方提供的图形化数据库管理工具，集成了数据库设计、查询执行、性能监控、数据建模等功能，适用于开发人员和数据库管理员。它提供直观的界面，方便进行数据库管理和 SQL 调试。

下载地址：https://dev.mysql.com/downloads/workbench/

DBeaver

DBeaver 是一款跨平台的开源数据库管理工具，支持 MySQL 以及多种数据库系统。它提供强大的 SQL 编辑器、查询执行和数据浏览功能，适合开发人员、数据库管理员和数据分析师使用，支持多种数据库和扩展插件。

下载地址：https://dbeaver.io/download/

Navicat for MySQL（收费）

Navicat for MySQL 是一款商业化的数据库管理工具，提供丰富的图形化界面和多种功能，如数据同步、备份恢复、查询优化等，适用于开发人员和数据库管理员。它支持 MySQL 和 MariaDB，并具有强大的可视化管理功能和易用性。

下载地址：https://www.navicat.com/en/products/navicat-for-mysql

MySQL 基础使用

连接服务器

mysql -h 127.0.0.1 -u root -p
# Enter password: 123456

查询数据库：show databases;

切换数据库：use mysql;

查询数据表：show tables;

简单查询数据：select * from tables_priv;

SQL 语句

分类

DDL 数据定义语言

全称：Data Definition Language

语句：CREATE DROP ALERT TRUNCATE

DML 数据操作语言

全称：Data Manipulation Language

语句：INSERT UPDATE DELETE CALL

DQL 数据查询语言

全称：Data Query Laguage

语句：SELECT

DCL 数据控制语言

全称：Data Control Language

语句：GRANT REVOKE

创建数据库

create database game;

删除数据库

drop database game;

创建数据表，同时指定默认值

create table player (
    id INT,
    name VARCHAR(100),
    level INT DEFAULT 1,
    exp INT DEFAULT 0,
    gold DECIMAL(10,2) DEFAULT 0
);

查询表结构

desc player;

创建数据表

修改数据表结构

更改字段数据类型

alter table player modify column name VARCHAR(200);

更改字段名

alter table player rename column name to nick_name;

添加字段

alter table player add column last_login DATETIME;

删除字段

alter table player drop column gold;

删除数据表

drop table player;

插入数据

插入一条数据

insert into player (id, name) values (1, "张三");

插入多条数据

insert into player (id, name) values (2, "李四"), (3, "王五");

更新数据

update player set level=3 where id=1;

update player set gold=9.99 where id=3;

删除数据

delete from player where gold=0;

导出数据

mysqldump -h 127.0.0.1 -u root -p game > game.sql

导入数据

mysql -h 127.0.0.1 -u root -p game < game.sql

更进一步

WHERE 子句

select * from player where level = 1;

select * from player where level > 1;

select * from player where level > 1 and level < 5;

select * from player where level > 1 and level < 5 or exp > 1 and exp <5;

select * from player where level > 1 and (level < 5 or exp > 1) and exp <5;

select * from player where level in (1, 3, 5);

select * from player where level not in (1, 3, 5);

select * from player where level between 1 and 10;

select * from player where level not between 1 and 10;

select * from player where level >= 1 and level <= 10;

select * from player where email is null;

select * from player where email is null or email='';

select * from player where email is not null;

select * from player where name like '王%'; % 匹配任意多个字符

select * from player where name like '%王%';

select * from player where name like '王_';

select * from player where name like '__王';

正则表达式

select * from player where name regexp '^王.$';

select * from player where name regexp '王';

select * from player where name regexp '[王张]';

select * from player where name regexp '王|张';

正则表达式练习：

# 查找邮件地址以zhangsan开头的玩家
SELECT * FROM player WHERE email REGEXP '^zhangsan';
SELECT * FROM player WHERE email LIKE 'zhangsan%';
# 查找邮件地址以a/b/c开头的玩家
SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP '^[a-c]';
# 查找邮件地址以net结尾的玩家
SELECT * FROM player WHERE email REGEXP 'net$';
SELECT * FROM player WHERE email LIKE '%net';

ORDER BY

select * from player order by level; 默认为升序排序

select * from player order by level desc;

select * from player order by 5; 使用列数排序

常用聚合函数

select count(*) from player;

select avg(level) from player;

GROUP BY

# 统计玩家性别比例
select sex, count(*) from player group by sex;
# 不计算 NULL 的数量
select sex, count(sex) from player group by sex;

统计每个等级玩家数量：select level, count(level) from player group by level;

HAVING

select level, count(level) from player group by level having count(level) > 4;

select level, count(level) from player group by level having count(level) > 4 order by count(level) desc;

select substr(name, 1, 1), count(substr(name, 1, 1)) from player
group by substr(name, 1, 1)
having count(substr(name, 1, 1)) >= 5
order by substr(name, 1, 1) desc
limit 3;

select substr(name, 1, 1), count(substr(name, 1, 1)) from player
group by substr(name, 1, 1)
order by substr(name, 1, 1) desc
limit 3, 3;

DISTINCT

select distinct sex from player;

UNION 并集

select * from player where level between 1 and 3 union select * from player where exp between 1 and 3;

不去重：select * from player where level between 1 and 3 union all select * from player where exp between 1 and 3;

INTERSECT 交集

select * from player where level between 1 and 3 intersect select * from player where exp between 1 and 3;

EXCEPT 差集

select * from player where level between 1 and 3 except select * from player where exp between 1 and 3;

子查询

select * from player where level > (select avg(level) from player);

select level, round((select avg(level) from player)) as average,
level - round((select avg(level) from player)) as diff
from player;
# round 函数四舍五入取整

利用子查询建表：create table new_player select * from player where level < 5;

利用子查询向表中插入数据：

insert into new_player select * from player where level between 6 and 10;

EXISTS

select exists (select * from player where level > 100);

select exists (select * from player where level > 10);

联表查询

内连接 INNER JOIN

只返回两个表中都有的数据

select * from player
inner join equip
on player.id = equip.player_id;

# 等效于：
select * from player, equip
where player.id = equip.player_id;

# 别名：
select * from player p, equip e
where p.id = e.player_id;

左连接 LEFT JOIN

返回左表中所有的数据和右表中匹配的数据，右表中没有的数据 NULL 填充

select * from player
left join equip
on player.id = equip.player_id;

右连接 RIGHT JOIN

返回右表中所有的数据和左表中匹配的数据，左表中没有的数据 NULL 填充

select * from player
right join equip
on player.id = equip.player_id;

视图

创建视图

create view top10
as
select * from player order by level desc limit 10;

视图会随原内容更新而更新，如修改原前十名数据，则 top10 视图也会随之发生变化：

更新视图

alter view top10
as
select * from player order by level limit 10;

删除视图

drop view top10;

索引

查询数据量：select count(*) from fast;

查询表结构：DESC fast;

创建索引

create index email_index on fast(email);

# 等效于：
alter table fast add index email_index(email);

查询索引

show index from fast;

在有无索引的表中分别查询数据：

# 有索引表中查询：
SELECT * FROM fast WHERE email LIKE 'user1%' ORDER BY id;
# 无索引表中查询
SELECT * FROM slow WHERE email LIKE 'user1%' ORDER BY id;

删除索引

drop index email_index on fast;

（部分细节内容待更新）

参考

1. 一小时MySQL教程_哔哩哔哩_bilibili ↩
2. GitHub - geekhournet/mysql-course: Sample data and sources of mysql-course. ↩
3. 在Ubuntu 22.04 LTS 上安装 MySQL两种方式 ↩
4. Ubuntu 安装MySQL默认密码是多少 ↩