MySQL 的基本使用

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都提供一套 API，用来创建、访问、管理、搜索、复制保存的数据。当然数据也可以直接存放在文件，但文件读写速度慢，不适合大规模业务。

SQL（Structured Query Language，结构化查询语言），是访问关系型数据库的标准化语言，分为三大组成部分：

数据定义语言 DDL：定义数据库对象，数据库、表、视图、触发器、存储过程等。
数据操作语言 DML：实现数据更新、查询。
数据控制语言 DCL：授予、回收用户的数据访问权限。

MySQL：业界流行的开源关系型数据库管理系统，Web 开发领域最常用的 RDBMS。

关系型数据库 RDBMS：基于关系模型，以表格组织数据。

数据存放在数据表中
一行代表一条记录
一列代表记录的字段域
多行多列组成一张数据表
多张数据表共同组成数据库

Mac 环境安装 MySQL

检查本机是否已经安装：

which mysql

安装步骤

下载安装包：访问 MySQL 官网 https://dev.mysql.com/downloads/mysql/，Mac 平台优先下载 dmg 格式安装包。
双击 dmg 镜像，运行 .pkg 安装程序。注意：安装结束弹窗会给出 root 用户临时密码，务必记录，遗忘会增加重置密码成本。
启动服务：打开「系统偏好设置」（较新 macOS 为「系统设置」），找到 MySQL 设置面板，启停 MySQL 服务。

终端连接 MySQL

配置环境变量，把 mysql 命令加入 PATH：

PATH="$PATH":/usr/local/mysql/bin

备注：修改后部分终端需要重启终端生效。

连接命令语法：

mysql -h hostname -P port -u username -p [数据库名] [-e "SQL语句"]

参数说明：

-h：数据库服务主机名、IP
-P：端口，MySQL 默认 3306
-u：登录用户名
-p：回车后输入密码
数据库名：登录后直接切换到指定库，可选
-e：执行 SQL 语句之后直接退出终端

mysql -h 127.0.0.1 -u root -p
mysql -h localhost -u root -p
mysql -uroot -p

退出客户端：

exit
quit

Mac 彻底卸载 MySQL

Homebrew 版本：

brew uninstall mysql

dmg 安装包版本执行：

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

额外检查残留目录，存在就删除：

/usr/local/Cellar/[mysql文件]
/usr/local/var/[mysql文件]
/tmp/mysql.sock、mysql.sock.lock、my.cnf
/usr/local/var/mysql/ pid、err日志文件
/usr/local/Library/Cache/Homebrew/ 安装缓存包

brew 用户执行清理：

brew cleanup

修改 root 用户密码

mysqladmin 命令行修改

mysqladmin -u [username] -h [hostname] -p password "newpassword"

注意：password 是关键字，不是旧密码；新密码使用双引号包裹。运行命令之后回车，输入旧密码完成修改。

ALTER USER 修改密码（MySQL 8.0+ 推荐）

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

旧客户端连不上时才指定 mysql_native_password（8.4 起该插件已弃用）：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';

注意：MySQL 8.0 默认认证插件为 caching_sha2_password。5.7 及更早可以使用 SET PASSWORD=PASSWORD("newpassword")；8.0 已删除 PASSWORD() 函数。

忘记 root 密码重置（Mac）

系统偏好设置 → MySQL，关闭数据库服务。
进入 mysql bin 目录
```
cd /usr/local/mysql/bin/
sudo su
```
跳过权限校验启动服务
```
./mysqld_safe --skip-grant-tables &
```

登录修改密码。MySQL 8.0：

mysql -u root mysql
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5.7 及更早：

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;

用户管理

查询用户

SELECT * FROM `mysql`.`user`;
SELECT user FROM `mysql`.`user`;

创建用户

CREATE USER '[username]'@'[host]' IDENTIFIED BY 'password';

username：用户名
host：允许登录主机；localhost 仅本机登录；% 代表允许任意主机访问
password：登录密码。8.0 若开启密码校验插件，空密码会被拒绝。
支持逗号分隔一次性创建多个账号。

CREATE USER 'test1'@'localhost' IDENTIFIED BY '123456';

重命名用户

RENAME USER 'test2'@'localhost' TO 'test3'@'localhost';

权限操作

查看权限

SHOW GRANTS;
SHOW GRANTS FOR 'test1'@'localhost';

授予权限

GRANT priv_type [column_list] ON database.table TO user [WITH with_option];

priv_type：权限类型，多个权限逗号分隔
database.table：指定库.表；*.* 代表全部库全部表
WITH GRANT OPTION：允许该用户把自己的权限再授权给其他人。

GRANT SELECT ON *.* TO 'test1'@'localhost';
GRANT SELECT,INSERT ON *.* TO 'test1'@'localhost','test2'@'localhost';
GRANT SELECT ON *.* TO 'test1'@'localhost' WITH GRANT OPTION;

回收权限

REVOKE priv_type [column_list] ON database.table FROM user;

REVOKE SELECT ON *.* FROM 'test1'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test1'@'localhost';

修改用户密码

ALTER USER 'test1'@'localhost' IDENTIFIED WITH mysql_native_password BY '1111';

删除用户

DROP USER '[username]'@'[host]';
DROP USER 'test3'@'localhost';
-- 不推荐直接 delete 系统表
DELETE FROM mysql.user WHERE Host='localhost' AND User='test3';

数据库管理

列出数据库

SHOW DATABASES;
SHOW DATABASES LIKE '%book%';
SHOW DATABASES LIKE 'book%';
SHOW DATABASES LIKE '%book';

创建数据库

CREATE DATABASE <databaseName>;
CREATE DATABASE IF NOT EXISTS <databaseName>;

删除数据库

DROP DATABASE <databaseName>;
DROP DATABASE IF EXISTS <databaseName>;

切换使用数据库

USE <databaseName>;

存储引擎查看与设置

-- 查看支持的全部引擎
SHOW ENGINES;
-- 查看默认引擎
SHOW VARIABLES LIKE 'default_storage_engine%';
-- 会话级别临时修改默认引擎，重启客户端失效
SET default_storage_engine=InnoDB;

数据表管理

查看当前库全部表

SHOW TABLES;

查看表结构

DESCRIBE <tableName>;
DESC <tableName>;

创建数据表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `author` varchar(30) COLLATE utf8mb4_general_ci NOT NULL,
  `language` int(2) DEFAULT '1',
  `type` int(2) DEFAULT '1',
  `publishing` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `cover` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `details` varchar(10000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `price` decimal(10,2) DEFAULT '0.00',
  `vipprice` decimal(10,2) DEFAULT '0.00',
  `stock` int(10) DEFAULT '0',
  `state` int(2) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

查看建表 DDL：

SHOW CREATE TABLE `test`;

复制表

-- 复制完整表结构，包含主键索引，不复制数据
CREATE TABLE `test` LIKE `book`;

-- 复制表结构+数据，不会复制主键索引
CREATE TABLE `test` SELECT * FROM `book`;
CREATE TABLE `test` SELECT * FROM `book` WHERE id=1;
CREATE TABLE `test` AS (SELECT * FROM `book`);
CREATE TABLE `test` AS (SELECT * FROM `book` WHERE id=1);

修改表 ALTER TABLE

ALTER TABLE <表名> [修改选项]

修改选项：

ADD [COLUMN]：新增字段
CHANGE [COLUMN]：修改字段名+字段类型
ALTER [COLUMN]：修改、删除字段默认值
MODIFY [COLUMN]：只修改字段类型，不改名
DROP [COLUMN]：删除字段
RENAME TO：修改表名
CHARACTER SET、COLLATE：修改字符集与排序规则

ALTER TABLE `test` ADD COLUMN `col_t1` int;
ALTER TABLE `test` CHANGE COLUMN `col_t1` `col_t2` char;
ALTER TABLE `test` ALTER COLUMN `col_t2` SET DEFAULT 'a';
ALTER TABLE `test` ALTER COLUMN `col_t2` DROP DEFAULT;
ALTER TABLE `test` MODIFY COLUMN `col_t2` char(10);
ALTER TABLE `test` DROP COLUMN `col_t2`;
ALTER TABLE `test` RENAME TO `test_2`;
ALTER TABLE `test_2` CHARACTER SET `utf8mb4` DEFAULT COLLATE `utf8mb4_general_ci`;

把查询结果插入已有表：

INSERT INTO `test` SELECT * FROM `book`;
INSERT INTO `test` SELECT * FROM `book` WHERE id=1;

清空表数据

TRUNCATE 删除重建整张表，自增主键重置；DELETE 只删除行，自增计数器继续递增。

TRUNCATE TABLE <tableName>;

删除数据表

DROP TABLE <tableName>;
DROP TABLE IF EXISTS <tableName>;

表数据增删改查

SELECT 查询

SELECT [DISTINCT] *|<字段列名> FROM <表1>,<表2>
[
  [WHERE <表达式>]
  [GROUP BY <字段>,<字段>]
  [HAVING <表达式>]
  [ORDER BY <字段 [ASC|DESC]>, <字段 [ASC|DESC]>]
  [LIMIT [初始位置] <记录数>]
]

关键说明

DISTINCT：去重，写在字段最前面；多字段时，多字段组合全部相同才判定重复。
WHERE：行过滤，分组之前执行，可以使用索引；不能使用聚合函数。
GROUP BY：分组；WITH ROLLUP 增加汇总统计行。
HAVING：分组之后过滤，针对结果集，支持聚合函数，无法使用索引，性能差，优先使用 WHERE。
ORDER BY：排序，ASC 升序，DESC 降序，默认 ASC。
LIMIT offset,count；等价 LIMIT count OFFSET offset。

-- where 条件、like 模糊匹配、regexp 正则
SELECT * FROM `test` WHERE `id`<5;
SELECT * FROM `test` WHERE `name` LIKE '阿%';
SELECT * FROM `test` WHERE `name` REGEXP '^阿';

-- group by 分组+汇总
SELECT `language`, GROUP_CONCAT(name), COUNT(language) FROM test GROUP BY `language`;
SELECT `language`, GROUP_CONCAT(name), COUNT(language) FROM test GROUP BY `language` WITH ROLLUP;

-- having 使用聚合过滤分组
SELECT `language`, GROUP_CONCAT(name), COUNT(language), AVG(price) FROM book GROUP BY `language` HAVING AVG(price)>40;

-- limit 分页
SELECT * FROM `test` LIMIT 3,5;
SELECT * FROM `test` LIMIT 5 OFFSET 3;

INSERT 插入数据

两种写法：INSERT ... VALUES、INSERT ... SET

INSERT INTO `test` (`account`, `password`, `name`, `sex`, `mobile`) VALUES ('a1', 'p1', 'n1', 1, 13011112222);

-- set 语法
INSERT INTO `test` SET `account` = 'a3', `password` = 'p3', `name` = 'n3', `sex` = 2, `mobile` = 13011113333;

-- 查询结果插入表
INSERT INTO `test` SELECT * FROM `user`;

UPDATE 更新数据

UPDATE <表名> SET 字段1=值1 [,字段2=值2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

注意：不写 WHERE，会更新全表所有行！

UPDATE `test` SET `address`="更新地址1";
UPDATE `test` SET `address`="更新地址2" WHERE `id`>15 ORDER BY `id` DESC LIMIT 2;

DELETE 删除行

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

注意：不写 WHERE，删除整张表全部数据！

DELETE FROM test;
DELETE FROM test WHERE id=16;
DELETE FROM test WHERE id>15 ORDER BY id DESC LIMIT 1;

多表连接查询

交叉连接 CROSS JOIN

生成笛卡尔积，数据量大时性能很差，尽量避免。

SELECT * FROM `bookOrder`,`user`;
SELECT * FROM `bookOrder`,`user` WHERE `bookOrder`.userId=`user`.id;

内连接 INNER JOIN

只返回满足连接条件的数据行，标准写法使用 JOIN ... ON。

SELECT * FROM `bookOrder` JOIN `user` ON `bookOrder`.userId=`user`.id;

左外连接 LEFT JOIN

以左表为基准，左表全部行保留；右表匹配不到字段全部填充 NULL。

SELECT * FROM `bookOrder` LEFT JOIN `user` ON `bookOrder`.userId=`user`.id;

右外连接 RIGHT JOIN

以右表为基准，右表全部行保留；左表匹配不到字段全部填充 NULL。

SELECT * FROM `bookOrder` RIGHT JOIN `user` ON `bookOrder`.userId=`user`.id;

子查询

SQL 语句内部嵌套查询，支持多层嵌套；常用于 WHERE 条件中，搭配 IN、EXISTS。

SELECT `bookName`,`author`,`userId`,`userName` FROM `bookOrder` WHERE `userId` in (SELECT `id` from `user` WHERE `id`<7);

MySQL 索引

索引是有序的映射表，存储索引字段值与原表记录指针。类比字典音序表，快速定位行，大幅提升查询速度。

代价：索引会占用存储空间；插入、更新、删除时，索引也要同步维护，会降低 DML 语句性能。

创建索引

CREATE [索引类型] <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

-- 普通索引两种写法等价
CREATE INDEX `idx_name` ON `test` (`name` DESC);
ALTER TABLE `test` ADD INDEX `idx_name` (`name` DESC);

查看索引

SHOW INDEX FROM `test`;

删除索引

DROP INDEX idx_name ON `test`;
ALTER TABLE `test` DROP INDEX `idx_name`;

备注：MySQL 没有直接修改索引的语法，修改索引逻辑：先 DROP，再 CREATE。

常用聚合、字符串函数

avg()：求平均值
count()：统计行数
sum()：求和
min()：最小值
max()：最大值
group_concat()：分组拼接字符串
concat()：拼接多个字符串
instr()：查找子串位置
length()：字节长度；char_length()：字符个数
replace()：字符串替换
substring()：截取子串

SQL 关键字执行顺序

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY

注意：WHERE 在分组前过滤，可以使用索引；HAVING 在分组完成后过滤，操作结果集，无法利用索引。

交叉连接 WHERE 和 INNER JOIN ON 的区别

-- 写法1：交叉连接+where
SELECT * FROM `bookOrder`,`user` WHERE `bookOrder`.userId=`user`.id

-- 写法2：内连接+on
SELECT * FROM `bookOrder` JOIN `user` ON `bookOrder`.userId=`user`.id

交叉连接：先生成完整笛卡尔积大临时表，再用 WHERE 过滤，大表场景内存开销高，性能差。
JOIN ... ON：做行匹配时就应用 ON 条件，不会完整生成全部笛卡尔积，性能更优。

现行优化器常常把两种写法收成相近计划；业务查询仍应优先写 INNER、LEFT、RIGHT JOIN。

参考资料

Mysql 教程（C 语言中文网）

MySQL的基本使用