MariaDB 是 MySQL 的一个分支，mysql-server包和mariadb-server包无法同时存在。几乎所有主流编程语言都支持连接MySQL数据库。
MySQL、SQLite、PostgreSQL、SQL Server 、Oracle、达梦均属于关系型数据库（RDBMS），按照固定的结构存储数据(建表)，数据可能依赖内存缓存加速，但最终都存储在硬盘里。遵循国际标准组织制定的 SQL 语法规范（如 SELECT、INSERT、JOIN等）。这保证了基础语法的高度一致性。
非关系型数据库（NoSQL）：没有传统关系型数据库的固定表结构模式。

Redis 是内存数据结构服务器，采用键值模型存储多种数据类型（字符串、列表等），支持持久化与复杂操作；
Memcached 是纯内存键值缓存，仅支持字符串且无持久化；
MongoDB 是磁盘型文档数据库，以 BSON 格式存储结构化数据，支持类 SQL 查询。

三者均脱离 SQL 范式，但数据存储位置与结构灵活性存在显著差异：Redis/Memcached 数据主要驻留内存（其中 Memcached 无持久化），而 MongoDB 数据持久化于磁盘；Memcached 仅支持简单键值，Redis 支持多种数据结构。

1.PHP连接数据库

安装并连接到数据库

装包

1.yum install -y mariadb mariadb-server

2.rpm -qc mariadb-server 查看配置文件目录

3.systemctl enable mariadb --now

4.netstat -tunalp | grep mariadb

链接数据库

-h，--host，用于指定 MySQL 服务所在的主机名或 IP 地址，默认localhost

-p，小写p为交互式输入密码

-P，大写P指定端口，默认3306

-u，用户，直接登录是数据库的root用户，使用show grants；可以看到其拥有所有库所有表的最大权限。且可以把自己的权限复制给他人。

- 退出 exit

- select user(); 查看当前是哪个用户登录了数据库

当直接在命令行输入mysql，等价于：

mysql -u$(whoami) -hlocalhost -P3306 -p

#小写p会提示输入密码

先连接到数据库，执行指定命令，然后自动退出并返回到 Linux命令行（适合用于脚本）

mysql -u用户名 -p密码 -e "SHOW DATABASES;"

注:短格式的选项和参数可以无空格

5.检查php能否连接数据库

yum install php-mysqlnd -y
php -m | grep mysql 列出支持的功能模块
- mysqli
- mysqlnd
- pdo_mysql

步骤

涉及数据库知识在下一标题中整理

1.创建用户

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

作用：创建一个用户名为 admin、密码为 123456 的 MySQL 用户。
- 'admin'@'localhost'：用户 admin 只能从本地（localhost）连接数据库。
- 密码需用双引号包裹（MySQL 8.0+ 也可用单引号）。
- IDENTIFIED BY表示身份验证凭证

2.授予权限

GRANT ALL ON *.* TO 'admin'@'localhost';

作用：授予用户 admin 所有数据库的所有权限（*.* 表示“所有库.所有表”）。
ALL是 ALL PRIVILEGES的简写

3.创建数据库（建库）

MariaDB [(none)]> create database game;

可用show database; 查看已有的库进行确认

4.创建数据表（建表）

MariaDB [(none)]> create table game.user(name char(10), password char(6));

在 game仓库里，建造了一个名为 user的表。这个表的结构被设计为可以存放两种货物：
- name char(10)：用户名，定义为最多10个字符的字符串。
- password char(6)：密码，定义为最多6个字符的字符串。

5.验证表是否创建成功

MariaDB [(none)]> use game; -- 进入（选择）game数据库

MariaDB [game]> show tables; -- 查看当前数据库中的所有表

6.使用php脚本验证是否能够连接并写入数据库，需要关闭SELinux

<?php
// 数据库配置（根据实际配置修改）
$db_host = 'localhost';
$db_user = 'admin';
$db_pass = '123456';
$db_name = 'game';

// 创建数据库连接
$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

// 检测连接
if ($conn->connect_error) {
    die("数据库连接失败: " . $conn->connect_error);
}

// 设置字符集防止中文乱码[2,6](@ref)
$conn->set_charset("utf8");

// 准备测试数据（符合char(10)和char(6)字段限制）
$test_name = "test_user";    // 用户名（10字符以内）
$test_pass = "123abc";       // 密码（6字符以内）

// 使用预处理语句防止SQL注入[1](@ref)
$stmt = $conn->prepare("INSERT INTO user (name, password) VALUES (?, ?)");
$stmt->bind_param("ss", $test_name, $test_pass);

// 执行插入操作
if ($stmt->execute()) {
    echo "数据写入成功！插入记录：";
    echo "用户名：$test_name，密码：$test_pass";
} else {
    echo "数据写入失败: " . $stmt->error;
}

// 关闭连接
$stmt->close();
$conn->close();
?>

补充

1.HCIP例题

86.在LNMP架构中，firewalld 需要在每个节点中都开启并配置。在 Nginx 服务器中，需要放通其对应的业务端口，同时需要放通访问数据库的端口。在数据库服务器中，需要放通对应的业务接口即可。
【错】

    防火墙并非强制开启，应根据节点角色和网络位置决定。例如，数据库节点通常部署在内网隔离区，若已通过安全组或物理防火墙隔离，则无需开启本地防火墙。
    Nginx作为反向代理，不直接连接数据库，而是将动态请求转发至PHP-FPM（如127.0.0.1:9000），由PHP应用连接数据库

2.单引号和反引号

反引号 (`)

引用标识符（表名、列名等），类似SQL Server 中的 []

1.标识符是 MySQL 的保留关键字：如 SELECT * FROM `order`; 因为 order是一个关键字

2.标识符包含特殊字符或空格：如 SELECT `user-name` FROM `user data`;

3.标识符以数字开头：如 CREATE TABLE `2025data` ...

单引号 (')

定义字符串常量

SELECT * FROM users WHERE name = 'Alice';

3.配置文件

服务端配置片段

/etc/my.cnf.d/mariadb-server.cnf 或

/etc/mysql/mysql.conf.d/mysqld.cnf

# MariaDB 服务器主配置文件
# 文件位置: /etc/my.cnf.d/mariadb-server.cnf
# 此文件包含 [mysqld] 节的设置，这些设置会在 MariaDB 服务器进程 (mysqld) 启动时被读取。
# 修改此文件后，需要重启 MariaDB 服务才能生效: `systemctl restart mariadb`

# ---------------------------------------------------------------

[mysqld]
# 定义 MariaDB 所有数据库和表数据的存储根目录。
# 这是最重要的路径之一，包含了你的所有数据文件（如 .ibd, .frm）。
# 默认情况下，初始化和安装脚本会在此目录下创建系统数据库。
# 警告：切勿随意修改此路径，除非你知道如何迁移所有数据文件。
datadir=/var/lib/mysql

# 定义本地连接使用的 Unix Socket 文件路径。
# 当客户端应用（如 mysql命令行）和 MariaDB 服务器位于同一台机器时，
# 它们通过这个 socket 文件进行通信，这比 TCP/IP 连接方式更高效。
# 如果使用 `mysql -uroot -p` 命令本地登录，使用的就是这个socket。
socket=/var/lib/mysql/mysql.sock

# 指定错误日志文件的存放路径。
# 服务器启动、运行、停止过程中的所有错误、警告和重要信息都会记录于此。
# 这是数据库出现任何问题时（如无法启动、运行崩溃）第一个应该查看的地方。
# 可以使用 `tail -f /var/log/mariadb/mariadb.log` 命令实时查看日志。
log-error=/var/log/mariadb/mariadb.log

# 指定进程ID（PID）文件的存放路径。
# MariaDB 服务器启动后，会将自己的进程ID写入这个文件。
# 系统服务管理器（如 systemd）或管理脚本通过读取此文件来找到正确的进程，
# 从而对其进行管理操作（例如：停止、重启服务）。
pid-file=/run/mariadb/mariadb.pid

# ---------------------------------------------------------------
# 以下是一些常用但默认注释的配置
# ---------------------------------------------------------------

# 指定服务器监听的 TCP/IP 端口，默认为 3306。
# 如果端口被占用或想启用多个实例，可以修改此处。
# port=3306

# 设置默认的存储引擎。InnoDB 是支持事务的通用首选引擎。
# default-storage-engine=InnoDB

# 设置服务器默认的字符集。通常建议设置为 utf8mb4 以支持完整的 Unicode（包括表情符号）。
# character-set-server=utf8mb4
# collation-server=utf8mb4_unicode_ci

# 限制服务器允许的最大并发连接数。这有助于防止内存过载。
# 需要根据您的服务器硬件和应用需求进行调整。
# max_connections=151

# 启用慢查询日志，记录执行时间超过 long_query_time（秒）的SQL语句。
# 这对于发现和优化性能低下的查询至关重要。
# slow_query_log=1
# slow_query_log_file=/var/log/mariadb/mariadb-slow.log
# long_query_time=2

2.用户管理

修改密码的三种方式

1.设置管理员密码

mysqladmin -u root -p password

在命令行中执行无需连接数据库
password是固定关键字，表示要执行的操作是“修改密码”
交互式，先输入旧密码再重复输入两次新密码

2.Alter修改用户密码

ALTER可操作表、库、索引、视图、用户等十多种对象，对这些已经建立的对象进行修改
ALTER [对象类型] [对象名称] [操作指令];
alter user root@localhost identified by "123456";

3.执行mysql_secure_installation

MySQL/MariaDB 官方提供的安全初始化脚本

Enter current password for root (enter for none):  ← 初次安装直接回车
Set root password? [Y/n] Y   设置 root 密码
New password: ********
Re-enter new password: ********
Remove anonymous users? [Y/n] Y   移除默认创建的匿名账户
Disallow root login remotely? [Y/n] Y  取消 root 用户的远程访问权限
Remove test database and access to it? [Y/n] Y  移除默认的 test数据库
Reload privilege tables now? [Y/n] Y  刷新权限表使上述修改立即生效
All done!

4.SET命令，在 MySQL 8.0 及更高版本中，SET PASSWORD语法已被弃用，推荐使用Alter

MySQL 5.6 ：SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

MySQL 8.0 ：SET PASSWORD FOR 'root'@'localhost' = '新密码';

创建账号/授权grant

CREATE USER 'webadmin'@'localhost' IDENTIFIED BY '654321';

主机名，规定可以从哪里访问数据库

'localhost'

只能从数据库服务器本机连接。

'192.168.1.100'

只能从指定的 IP 地址 (192.168.1.100) 连接，应用部署在独立的服务器上时使用。

'192.168.1.%'

表示可以从指定的 IP 段 (192.168.1.0/24) 连接。%是通配符，允许一个网段内的多个应用服务器连接。

'%'

表示可以从任何主机连接，非常不安全，通常仅用于测试环境或特殊需求。

在 MySQL 中，'user'@'localhost'和 'user'@'%'被认为是两个完全不同的用户，他们可以有各自的密码和权限。

GRANT SELECT, INSERT, UPDATE, DELETE ON buydb.* TO 'webadmin'@'localhost';

GRANT设置权限

SELECT(查询), INSERT(插入), UPDATE(更新), DELETE(删除)。还可以使用 ALL 或 ALL PRIVILEGES授予所有权限。

权限作用范围ON buydb.*

buydb是数据库名。
.*表示该数据库下的所有表。也可以指定单张表，如 ON buydb.shop。

SHOW GRANTS;

查看当前登录 MySQL 数据库的用户所拥有的权限。

+-----------------------------------------------------------------------+
| Grants for app_user@%                                                 |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'%'                                  |
| GRANT SELECT, INSERT, UPDATE ON `mydb`.* TO 'app_user'@'%'            |
| GRANT SELECT (col1), UPDATE (col1) ON `mydb`.`sometable` TO 'app_user'@'%' |
+-----------------------------------------------------------------------+

全局级：USAGE（基础连接权限）。- USAGE 仅标识用户存在，表示此处未赋予权限。
数据库级：对 mydb数据库下的所有表（mydb.*）拥有 SELECT（查询）、INSERT（插入）、UPDATE（更新）权限。
表/列级：对 mydb数据库下的 sometable表，拥有查询和更新特定列（col1）的权限。

FLUSH PRIVILEGES;

FLUSH是一个多功能命令，用于清除或重新加载各种内部缓存。
PRIVILEGES具体指代的是 MySQL 中存储在系统表里的用户权限信息。

使用 GRANT、REVOKE、CREATE USER、ALTER USER这类标准的权限管理语句后，MySQL 会自动刷新权限，使更改立即生效，无需手动执行 FLUSH PRIVILEGES;

撤销权限revoke

REVOKE 权限 ON 数据库名.表名 FROM 用户名@“客户端地址”;

撤销的权限逗号分隔
REVOKE UPDATE, DROP, ALTER ON bbsdb.* FROM heihei@localhost;
ALL指代所有权限
REVOKE ALL ON bbsdb.* FROM heihei@localhost;

意图是 GRANT(给)，后面就紧跟 TO(给到)。

意图是 REVOKE(收回)，后面就紧跟 FROM(从...那里)。

删除用户drop

drop user 用户名@"客户端地址";

3.查询信息

查看版本/库/切换库

SELECT VERSION();

在命令行中查看 mysql --version

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.6     |
+-----------+
1 row in set (0.00 sec)

select database(); 查看当前所在的库
show databases; 查看所有的库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| halo_42jbew        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.09 sec)

接不接括号的核心依据是：你使用的是SQL关键字（Keyword）还是函数（Function）。

use 库名; 切换到一个库

mysql> use halo_42jbew;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

“正在读取表信息以完成表和列名的自动补全功能”
MySQL 客户端正在预加载您刚切换到的 halo_42jbew数据库中有哪些表、每个表有哪些列。这样当您后续输入 SQL 命令时，按 Tab键它可以自动补全表名和列名，为您提供方便。

“您可以使用 -A选项来关闭此功能以获得更快的启动速度”
这个预加载过程会稍微延长您连接数据库后的初始响应时间。如果您觉得不需要自动补全功能，并希望连接后立即获得命令提示符，可以在登录命令中加入 -A参数（如 mysql -u root -p -A）来禁用此功能。

如何快速判断有无括号：需要的结果是否是“值”？

如果答案是“是” -> 它很可能是函数，需要括号。

例如：当前数据库的名字（DATABASE()）、MySQL的版本号（VERSION()）、当前的日期时间（NOW()）。

如果答案是“否” -> 它很可能是关键字的一部分，不需要括号。

例如：显示数据库列表（SHOW DATABASES）、描述表结构（DESCRIBE table_name）、使用某个数据库（USE database_name）。

/var/lib/mysql

/var/lib/mysql 是 MySQL 服务器进程（mysqld）的工作目录。它被称为 datadir。所有数据库、表的物理文件都存储在这个目录下。

查看表/表头/用户

show tables; 查看数据库中的表
- 查看特定库的表使用SHOW TABLES FROM database_name

mysql> show tables;
+-----------------------+
| Tables_in_halo_42jbew |
+-----------------------+
| extensions            |
+-----------------------+
1 row in set (0.00 sec)

desc 库.表; （选择库时，库名可省略）

DESC 是 DESCRIBE 的简写。可快速查看一张指定表的列结构，包括有哪些字段、字段类型、是否允许为NULL、默认值、键信息等。

mysql> desc halo_42jbew.extensions;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(255) | NO   | PRI | NULL    |       |
| data    | longblob     | YES  |     | NULL    |       |
| version | bigint       | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Null：是否允许为空
Key：键信息,表示该列是否被索引或约束。常见值：
- PRI: 主键 (唯一标识每行)
- UNI: 唯一键 (值不能重复)
- MUL: 普通索引 (可重复)
Default：默认值，如果插入数据时未指定该列的值，将自动填充的值。
Extra：额外信息：提供的其他信息。最常见的是 auto_increment，表示该列值会自动增长。

查看数据库的普通用户

desc mysql.user;

显示所有用户的用户名、允许连接的主机以及密码的哈希值

select user,host,password from mysql.user;

查看正在操作的用户

select user();

4.常见数据类型

类别	MySQL/MariaDB	SQL Server	差异
整数	`INT`	`INT`	完全一样。最常用的整数类型。
小数	`DECIMAL(10,2)`	`DECIMAL(10,2)`	完全一样。用于精确计算（如金额）。`(10,2)`表示总共10位数，含2位小数。
浮点数	`FLOAT`	`FLOAT`	基本一样。用于近似科学计算，可能有微小精度差异。
字符串	`VARCHAR(100)`	`VARCHAR(100)`	基本一样。存储可变长度文本。注意最大长度限制可能不同。
Unicode字符串	`UTF8MB4`字符集的 `VARCHAR`	`NVARCHAR(100)`	重要区别！SQL Server 用 `NVARCHAR`存储国际化文本（如中文）。在 MySQL 中，只需设置好 `utf8mb4`字符集并用 `VARCHAR`即可。
长文本	`TEXT`	`VARCHAR(MAX)`	功能一样，名称不同。SQL Server 的 `TEXT`类型已过时，请用 `VARCHAR(MAX)`。
日期时间	`DATETIME`	`DATETIME`	基本一样。最常用的日期时间类型。但 SQL Server 的 `DATETIME2`精度更高，是新项目的推荐选择。
仅日期	`DATE`	`DATE`	完全一样。只存储年月日。
仅时间	`TIME`	`TIME`	完全一样。只存储时分秒。
二进制数据	`BLOB`	`VARBINARY(MAX)`	功能一样，名称不同。用于存储图片、文件等二进制数据。SQL Server 的 `IMAGE`类型已过时。
布尔值	`TINYINT(1)`	`BIT`	重要区别！MySQL 用 `TINYINT(1)`模拟（1为真，0为假），SQL Server 有原生的 `BIT`类型。

5.库、表管理

5.1创建/删除库

创建库：create database 库名;
- 不能使用空格以及下划线以外的特殊字符
- 不能以数字开头
- 大小写敏感
删除库：drop database 库名;
切换库：use 库名;

5.2创建/删除表

create table 库名.表名(字段名1 数据类型1,字段名2 数据类型2);
- 无法通过单条 CREATE TABLE 语句同时创建数据库和表
- 已经USE选择数据库后库名可省略
drop table 库名.表名;

在进行创建或删除的操作时可以加上存在条件判断
CREATE TABLE IF NOT EXISTS users (......
DROP TABLE IF EXISTS employees;

约束

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

主键约束:
- AUTO_INCREMENT 表示自增，默认初始值1，步长1
- PRIMARY KEY：主键约束，确保该列的值唯一且不为空（NOT NULL）
- 在MySQL中，约束命名和聚集索引默认具备，不用手动设置
NOT NULL约束:
- 强制要求字段必须包含有效值，禁止插入 NULL（空值）
DEFAULT CURRENT_TIMESTAMP：
- 此列的值默认填入当前的时间戳

CREATE TABLE example (
    id INT AUTO_INCREMENT,  -- 自增列
    other_id INT AUTO_INCREMENT,  -- 第二个自增列
    PRIMARY KEY (id)         -- 只有 id 列是主键
);

在上面的表里，id 和 other_id 都是自增列，但只有 id 是主键
一个表上定义多个列作为主键（复合主键），PRIMARY KEY (id, name)
但注意每个表最多只能有一个主键，只能在int这样的类型使用

5.3查看表内数据select

select 字段名 from 库名.表名;
select * from 库名.表名;
select * from 库名.表名 \G;

row：表示每一条记录（行）的分隔
*************************** 1. row ***************************
      id: 1
    name: Alice
     age: 25
   email: alice@example.com
*************************** 2. row ***************************
      id: 2
    name: Bob
     age: 30
   email: bob@example.com

别名as

使用 AS 为查询的列指定别名，只是查询的结果上列名换了名字，且AS可以直接省略。

SELECT column_name AS alias_name
FROM table_name;
或
SELECT column_name alias_name
FROM table_name;

条件子句where

等值查询 (=)

SELECT * FROM t WHERE id = 1;

SELECT * FROM t WHERE id = 'userA';

IN 查询/多值匹配

SELECT * FROM t WHERE id IN (1, 2, 3);

SELECT * FROM t WHERE id IN ('userA', 'userB', 'userC');

LIKE 模糊查询 (%代表“零个或多个字符)

SELECT * FROM t WHERE name LIKE '%John%';

LIKE 模糊查询 (_代表“恰好一个字符”)

SELECT * FROM t WHERE name LIKE 'J_n';

AND/OR 逻辑运算符

SELECT * FROM t WHERE id>1 AND name='John';

NOT 运算符

SELECT * FROM t WHERE id NOT IN (1, 2);

数值比较

select * from [Areas] where [Id]<1000000

不等于用<>或者!=来表示

逻辑操作符 not and or

多个条件的连接

select * from [Areas]
where
[Level]< 4 and [AreaName]= '北京'

--------------

[Level]< 4 and [AreaName] like '%北京%'

--------------

[Level]<4 and [AreaName] like '%北京%'
or [Level] = 4 and [AreaName] like '%天津%'

同时使用and和or。and的优先级较高，会认为是在两个and中间去or。
为了可读性建议使用括号。

([Level]<4 and [AreaName] like '%北京%' )
or ([Level] = 4 and [AreaName] like '%天津%')

not逻辑取反

SELECT * FROM orders WHERE status NOT IN ('completed', 'cancelled')

SELECT * FROM products WHERE name NOT LIKE '%discount%'

SELECT * FROM employees WHERE salary NOT BETWEEN 5000 AND 10000

嵌套查询in

内层查询返回一个值列表，外层查询则使用 IN 运算符来匹配列表中的任何一个值。

SELECT `Id`,`StudentName`
FROM Table_1
WHERE `Id` IN (SELECT StudentId FROM Table_2 WHERE Score > 90)

连接查询join

join通过关联字段的值匹配，将分散在多个表的字段横向拼接为完整业务实体。实现同时select两张表的数据的效果。通常使用连接查询替代嵌套查询。

一旦表通过 JOIN被关联，SELECT子句即可自由选择：

- 主表（如 stu）的任何列（如 stu.Id）；

- 关联表（如 p, c, d, s）的任何列（如 p.AreaName）,p、c、d、s是关联表的别名。

- 下例中Id这个字段是表中数据的唯一标识（主键），所以使用 Id列进行连接。

匹配逻辑：
•若 A.id=1且 B.id=1→ 匹配成功，保留组合；
•若 A.id=1但 B.id=2→ 匹配失败，丢弃组合。

连接的分类:
INNER JOIN仅保留双方匹配的行 【内连接】
LEFT JOIN保留左表所有行 + 右表匹配行 【左外连接】
RIGHT JOIN保留右表所有行 + 左表匹配行 【右外连接】
FULL JOIN保留左右表所有行 【全连接/全外连接】

SELECT 
    主表.列名 AS 别名,
    关联表.列名 AS 别名,
    ...
FROM 主表 AS 主别名

-- 内连接（只取匹配到的数据）
INNER JOIN 关联表 AS 别名
    ON 主别名.列名 = 别名.列名

-- 左外连接（主表全部保留，右表不匹配时为NULL）
LEFT JOIN 关联表 AS 别名
    ON 主别名.列名 = 别名.列名

-- 右外连接（右表全部保留，主表不匹配时为NULL）
RIGHT JOIN 关联表 AS 别名
    ON 主别名.列名 = 别名.列名

-- 全外连接（两边都保留，MySQL需要UNION模拟）
FULL JOIN 关联表 AS 别名
    ON 主别名.列名 = 别名.列名;

主表为Students ，别名stu
子表为Areas，用不同的别名p、c、d、s，用来匹配不同的列

-- 🎯 查询学生信息及其所属省、市、区、街道的名称
SELECT
    stu.Id,                -- 👤 学生唯一标识
    stu.StudentNo,         -- 📝 学号
    stu.StudentName,       -- 👤 姓名
    stu.ProvinceId,        -- 🏞️ 省级ID（外键）
    p.AreaName AS 省,       -- 🌍 省级名称（通过JOIN关联）
    stu.CityId,            -- 🏙️ 市级ID
    c.AreaName AS 市,       -- 🏢 市级名称
    stu.DistrictId,        -- 🏘️ 区/县级ID
    d.AreaName AS 区,       -- 🏡 区/县名称
    stu.StreetId,          -- 🛣️ 街道ID
    s.AreaName AS 街道,     -- 🏘️ 街道名称
    stu.Address            -- 📍 详细地址（门牌号等）
FROM 
    Students AS stu         -- 主表：学生信息表【别名stu】

-- 🔗 连接省级名称（左连接）
LEFT JOIN Areas AS p       -- 省级区域表【别名p】
    ON p.Id = stu.ProvinceId

-- 🔗 连接市级名称（左连接）
LEFT JOIN Areas AS c       -- 市级区域表【别名c】
    ON c.Id = stu.CityId

-- 🔗 连接区级名称（左连接）
LEFT JOIN Areas AS d       -- 区/县级区域表【别名d】
    ON d.Id = stu.DistrictId

-- 🔗 连接街道名称（左连接）
LEFT JOIN Areas AS s       -- 街道表【别名s】
    ON s.Id = stu.StreetId;

Students 表：

Id	StudentName	ProvinceId	CityId	DistrictId	StreetId
1	小明	11	1101	110101	11010101

Areas 表（Id 唯一）：

Id	AreaName
11	北京市
1101	北京市辖区
110101	东城区
11010101	东华门街道

查询结果：

stu.Id	StudentName	Province	City	District	Street
1	小明	北京市	北京市辖区	东城区	东华门街道

join的“嵌套”

这里“嵌套”指的是：一个 JOIN 的条件，依赖于上一个 JOIN 的结果。

p.[Id] = stu.[ProvinceId]

平行连接：逻辑上来说ProvinceId就是省份的区位编码，用这个区位编码直接到另一张表查询省份的名字。

c.[Id] = d.[ParentId]

写之前需要搞清楚一个列。它的逻辑（比如省市镇）的上下级关系。通过查询锚点向上下级查找：通过 ParentId字段，从某个（省→市→区县→街道）层级，查找它的上级下级地区是什么。

SELECT
    d.[Id],                  -- 当前区县ID（怀柔区）
    d.[ParentId],            -- 区县的父级ID（=北京市的ID）
    p.[AreaName] AS 省份,    -- 省（p 通过市的 ParentId 找到）
    c.[AreaName] AS 市,      -- 市（c 通过区县的 ParentId 找到）
    d.[AreaName] AS 区县,    -- 区县（主表 d）
    s.[AreaName] AS 乡村街道,-- 街道（s 通过 d.Id 找到子节点）
    d.[Level]                -- 层级（用于标识当前节点是几级）
FROM [Areas] AS d  -- 🔹 主表：区县（怀柔区）

-- 🔼 向上找父级市
LEFT JOIN [Areas] AS c
    ON c.[Id] = d.[ParentId]

-- 🔼 再向上找父级省
LEFT JOIN [Areas] AS p
    ON p.[Id] = c.[ParentId]

-- 🔽 向下找子级街道
LEFT JOIN [Areas] AS s
    ON s.[ParentId] = d.[Id]

WHERE
    d.[AreaName] = '怀柔区';  -- 只查怀柔区


主表 d = 怀柔区（Id=110116, ParentId=110100）
c 通过 c.Id = d.ParentId → 找到 110100 = 北京市
p 通过 p.Id = c.ParentId → 找到 11 = 北京省份（这里一般还是北京市作为直辖市）
s 通过 s.ParentId = d.Id → 找到所有 “父级=怀柔区” 的街道，比如泉河街道、庙城镇、杨宋镇等

值匹配case

针对列，可结合when做结果上的等值匹配

根据数字或者单个字母在查询结果上显示为不同的中文

CASE <表达式> -- 在这里，表达式就是我们要判断的列
    WHEN <值1> THEN <结果1>
    WHEN <值2> THEN <结果2>
    ...
    ELSE <默认结果>
END

# 例如：

CASE Gender  -- 表达式就是 Gender 列
        WHEN 1 THEN '男'
        WHEN 2 THEN '女'
        ELSE '未知'  -- 推荐加上 ELSE 子句处理未匹配的值
    END AS '性别'  -- 为新生成的列指定别名

# END是 SQL 标准规定的语法闭合符，用于明确 CASE表达式的边界。

统计和排序

SELECT
    聚合函数1(列1) AS 别名1,  -- 例如：COUNT(*), SUM(amount)
    聚合函数2(列2) AS 别名2,  -- 例如：AVG(price), MAX(date)
    ...
FROM
    表名
WHERE
    条件;

COUNT和聚合函数

MySQL中的聚集函数用于对一组数据进行汇总计算，并返回一个单一值。
COUNT 是 SQL 里最常见的统计函数，用来统计行数。

统计表里所有行：SELECT COUNT(*) FROM Students;

统计某列的非空值：SELECT COUNT(StudentName) FROM Students;

除了 COUNT，还有：

SUM(列)：求和
AVG(列)：平均值
MAX(列)：最大值
MIN(列)：最小值

例：

SELECT AVG(Score) AS 平均分, MAX(Score) AS 最高分
FROM Scores;

ORDER

SELECT 列1, 列2 FROM 表名 ORDER BY 排序列 [ASC|DESC]

- 可以order by [as的别名]，不一定是列名
- ASC：升序（默认，小→大）
- DESC：降序（大→小）

单字段排序

SELECT * FROM Students
ORDER BY Score DESC;

多字段排序

SELECT * FROM Students
ORDER BY ProvinceId ASC, Score DESC;

按别名排序

SELECT Name, Score, (Score * 1.1) AS AdjustedScore
FROM Students
ORDER BY AdjustedScore DESC;

5.4修改表alter

格式
- ALTER [对象类型] [对象名称] [具体操作];
- ALTER table 库名.表名具体操作;

添加字段（表头）：add

alter table game.user add sex enum("girl","boy") default "boy";
- add sex 要为表添加的新字段的名称
- enum("girl","boy") 是新字段 sex的数据类型。ENUM是一个枚举类型，值只能从预先定义好的选项中选择
- default "boy" 这是为字段设置的默认值

删除字段：drop

alter table game.user drop sex;

修改字段的数据类型：modify

alter table game.user modify name varchar(15);
- name 是要修改的目标字段/表头的名称
- varchar(15) 是字段 name的新数据类型定义

修改字段名：change

alter table game.user change name 用户名 varchar(15);
- name 是要修改的目标字段/表头的名称
- 用户名即是新的字段名，目前默认字符集支持中文，建议使用反引号包括

修改表名：rename

alter table user rename 用户表;
- 用户表是为表指定的新名称
- 需要先USE库，效果相当于 RENAME TABLE user TO 用户表;

复制/备份表：like

create database bakdb;
- 创建一个名为 bakdb的新数据库。这是为了将复制出来的新表存放在一个独立的、专门用于备份的数据库中，与原始数据分离，便于管理。
使用 LIKE关键字复制表结构，注意只复制一张现有表的表结构和表头，不包括数据。
create table bakdb.user like game.用户表;

如果需要再次填充备份的表数据，还需要INSERT一次
INSERT INTO bakdb.user SELECT * FROM game.用户表;

复制/备份表数据：like

create table bakdb.user like game.用户表;
- 一次性完成了表结构和表数据的备份。

6.管理表记录

insert

INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

INSERT INTO Students
    (StudentNo, StudentName, Gender, Birthday, PhoneNo, ProvinceId, CityId, DistrictId, StreetId, Address)
VALUES
    ('19041315','某某某同学',1,'2001-3-29','18976264312',1,2,3,NULL,'某小区');

省略INTO无影响（MySQL中）
省略列名，则必须为每一列赋值，如果赋值时留空应该使用null

update

UPDATE 表名

SET 列1 = 值1, 列2 = 值2, ...

WHERE 条件

不加where限定行时，操作所有行
SET子句指定需要更新的列（字段）以及这些列应该被设置的新值

delete

表变为空表，但表头、索引、约束等结构完整保留

先检查哪些行会被删除

SELECT * FROM 库名.表名 WHERE 条件;

确认结果无误后，再将 SELECT * 替换为 DELETE

DELETE FROM 库名.表名 WHERE 条件;

7.数据迁移

备份

在源服务器上，将名为 game的数据库完整地导出到一个名为 /root/game.sql的文本文件中。

mysqldump -uroot -p666888 -B game > /root/game.sql

mysqldump：MySQL 官方提供用于备份的命令行工具。将数据库的结构（建表语句）和数据（插入语句）都生成一个标准的 SQL 脚本文件。
-uroot：指定连接数据库的用户名是 root。
-p666888：指定 root用户的密码。注意：这种直接在命令中写密码的方式虽然方便，但不安全（密码会暴露在命令历史中）。更安全的做法是只写 -p，然后交互式输入密码。
-B game：-B参数是--databases的简写。可指定多个要备份的数据库。且在恢复时，工具会自动创建名为 game的数据库（如果不存在）并切换使用它。如果不加 -B，则需要手动先创建数据库。

> /root/game.sql：将 mysqldump工具输出的 SQL 语句重定向到 /root/game.sql文件中。

恢复

mysql -uroot -p654321 < /root/game.sql

可以先通过scp等方式将备份文件拷贝到目标服务器
前半部分为登录目标mysql服务器，-p处生产环境和备份环境的数据库密码通常不同
< /root/game.sql：告诉 MySQL 客户端：不要等待用户手动输入 SQL 命令，而是直接读取 /root/game.sql文件中的内容作为要执行的命令。

8.补充

变量set

SET直接赋值
mysql中不需要declare在赋值前先声明变量。在使用 SET 或 SELECT ... INTO 赋值时，会自动赋予其数据类型。

SET @my_data = 123;

SELECT @my_data; -- 输出 123

SELECT中赋值

SELECT 字段名 INTO @变量名 FROM 表;
1. SELECT COUNT(*) INTO @total_users FROM users;
SELECT 值 INTO @变量名;
SELECT @变量名 := 值;
1. := 是 MySQL 的赋值运算符

year

Year()是一个 SQL 函数，专门用于从日期（DATE）或日期时间（DATETIME）类型的字段中提取年份值。
- ...... WHERE Year(student.[Birthday]) = 2000
类似的还可以用month和day

format

MySQL 的 FORMAT() 主要用于数值格式化，而不是日期格式化。需要使用 DATE_FORMAT() 函数来实现日期格式化。

SELECT
    -- ... 其他列 ...
    DATE_FORMAT(student.Birthday, '%Y年%c月%e日') AS 生日
FROM
    student

MySQL 中的FORMAT(X, D) 函数主要用于将数字四舍五入并以千位分隔符格式化。将提供的数字 X 四舍五入到 D 个小数位。

SELECT FORMAT(1234.56, 2);      -- 结果: '1,234.56'

数据库空间使用情况

实际被使用的有效空间和数据库当前分配的总逻辑空间的比例。表示有效数据占已分配空间的比例。

SELECT 
    table_schema AS `Database Name`,                        -- 数据库名
    SUM(data_length + index_length) / 1024 / 1024 AS `Database Size (MB)`,  -- 库的总大小(数据+索引)，单位 MB
    SUM(data_free) / 1024 / 1024 AS `Free Space (MB)`,      -- 库的空闲碎片空间，单位 MB
    (
        (SUM(data_length + index_length) - SUM(data_free))  -- 已使用空间
        / SUM(data_length + index_length)                  -- 总空间
    ) * 100 AS `Used Space (%)`                            -- 使用率百分比
FROM information_schema.TABLES
GROUP BY table_schema;  -- 按数据库分组，统计每个库的大小、空闲、使用率

简化结构：

SELECT 
    [列定义]
FROM information_schema.TABLES
GROUP BY table_schema;

information_schema.TABLES: MySQL 提供的元数据表，包含所有表的信息
table_schema是 information_schema.TABLES表中的一个列，表示数据库的名称本身
GROUP BY table_schema 按数据库名称分组

执行结果

Database Name | Database Size (MB) | Free Space (MB) | Used Space (%)
------------- | ------------------ | --------------- | -------------
db1          | 250.50             | 12.25           | 95.10
db2          | 180.75             | 8.50            | 92.30
db3          | 320.00             | 25.00           | 92.19

GROUP BY 按照列分组数据

SELECT 列名, 聚合函数(列名)
FROM 表名
GROUP BY 列名;

注意：SELECT 中的非聚合列必须出现在 GROUP BY 中

聚合列：使用了聚合函数的列，如 SUM(sales), COUNT(*), AVG(score)
非聚合列：直接列出的原始列，如 product_name, department

分享

LNMP基础：MySQL/MariaDB篇