MySQL详解

Honkers · 2026-5-7 09:06:43

一、数据库概述

1、什么是数据库？

- **定义**：数据库（Database, DB）是按照数据结构来组织、存储和管理数据的仓库，用于高效存储、检索和管理大量结构化或非结构化数据。
- **核心目标**：解决数据冗余、数据不一致性问题，确保数据的安全性、完整性和可访问性

2、数据库管理系统(DBMS)

- **定义**：数据库管理系统是用于管理数据库的软件系统，负责数据的存储、查询、更新、删除等操作，并提供事务管理、权限控制、备份恢复等功能。
- 常见 DBMS：
- **关系型数据库**：MySQL、PostgreSQL、Oracle、SQL Server。
- **非关系型数据库（NoSQL）**：MongoDB、Redis、Cassandra。

3、数据库分类

数据库主要分为**关系型数据库**和 ** 非关系型数据库（NoSQL）** 两大类，两者在数据模型、适用场景上有显著差异。

```
姓名年纪地址性别出生
张三 18 china man 1990
张三

```

**关系型数据库（SQL）**

- **数据模型**：基于二维表格（关系表），通过行（记录）和列（字段）存储数据，表间通过主键、外键建立关联。
- 特点：
- 结构化强，数据一致性高。
- 支持复杂查询（如 JOIN、子查询），适合事务性场景（如金融交易、订单系统）。
- 遵循 ACID 特性（原子性、一致性、隔离性、持久性）。
- **典型应用**：企业 ERP 系统、银行核心系统、电商订单管理。

** 非关系型数据库（NoSQL）**

- **数据模型**：采用键值对、文档、列族、图结构等非表格模型存储数据。
- 特点：
- 灵活可扩展，适合处理海量非结构化数据（如日志、图片、JSON 文档）。
- 读写性能高，支持分布式架构。
- 弱化 ACID，强调 BASE 特性（基本可用、软状态、最终一致性）。
- 细分类型及示例：
- **键值存储**：Redis（用于缓存、会话管理）。
- **文档存储**：MongoDB（用于内容管理、用户数据存储）。
- **列族存储**：HBase（用于海量数据实时查询，如日志分析）。
- **图存储**：Neo4j（用于社交网络、推荐系统中的关系分析）。
- **典型应用**：互联网用户行为分析、实时推荐系统、物联网设备数据存储。

###### 4、结构化查询语言(SQL)

结构化查询语言（SQL）是用于管理和操作关系型数据库的标准编程语言，广泛应用于数据存储、查询、更新和管理。以下是对 SQL 的详细解析：

**. 定义与作用**

- **SQL（Structured Query Language）**：是一种专门设计用于与数据库通信的编程语言，用于执行数据操作（如查询、插入、更新）和数据库管理（如创建表、修改结构、权限控制）。

- 核心功能：

- 数据查询（SELECT）：从数据库中检索数据。
- 数据操作（INSERT、UPDATE、DELETE）：增删改数据。
- 数据定义（CREATE、ALTER、DROP）：创建、修改、删除数据库对象（如表、索引）。
- 数据控制（GRANT、REVOKE）：管理用户权限。

**SQL 与数据库的关系**

- SQL 是一种语言标准，而数据库（如 MySQL、PostgreSQL、Oracle）是实现 SQL 标准的软件系统。不同数据库对 SQL 的支持略有差异，但核心语法一致。

### 二、MySQL入门

```
MySQL是一个c/s架构的软件

c/s: client (客户端)------> server 服务端代表应用：QQ 迅雷企业微信
b/s: browser (浏览器) ----> server 服务端

密码：qwe123 输入的时候要是英文状态，然后必须是小写。

```

MySQL 是全球最流行的开源关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，后被 Oracle 收购。它以高性能、可靠性和易用性著称，广泛应用于 Web 应用、企业系统和中小型数据存储场景。以下是对 MySQL 的深度解析：

###### 1、核心特性

开源与跨平台

- **开源许可证**：采用 GPL（通用公共许可证），允许免费使用和修改，但商业应用需购买商业许可证。
- **跨平台支持**：支持 Linux、Windows、macOS、Solaris 等主流操作系统。

** 存储引擎**

MySQL 支持多种存储引擎，每个引擎有不同特性，最常用的是：

- **InnoDB**（默认）：支持事务、外键、行级锁和崩溃恢复，适合高并发和写密集型场景。
- **MyISAM**：不支持事务和外键，读写性能高，但不适合高并发事务场景。
- **Memory**：数据存储在内存中，查询极快，适合临时数据或缓存。
- **Archive**：只支持 INSERT 和 SELECT，压缩率高，适合历史数据归档。

**高性能与扩展性**

- **索引优化**：支持 B-Tree、哈希索引，优化查询效率。
- **复制（Replication）**：主从复制、主主复制，实现读写分离和高可用性。
- **分区（Partitioning）**：水平分割大表，提升查询性能。

**安全与权限管理**

- **用户认证**：基于用户名、主机和密码的访问控制。
- **细粒度权限**：支持 GRANT/REVOKE 对数据库、表、列级别的权限控制。
- **加密功能**：支持 SSL/TLS 加密传输，以及数据静态加密（TDE）。

###### 2、MySQL链接

安装 MySQL 后，你可以通过以下几种方式连接到 MySQL 服务端：

- 1、使用命令行客户端连接
- 2、使用图形化工具连接

常用 MySQL 图形化管理工具：

- MySQL Workbench（官方工具）：https://www.mysql.com/cn/products/workbench/
- Navicat（付费）：https://www.navicat.com/
- DBeaver：https://dbeaver.io/
- phpMyAdmin（基于Web）：https://www.phpmyadmin.net/

使用MySQL命令行链接

```
mysql -u your_username -p
```

**参数说明：**

- `-u` 参数用于指定用户名。
- `-p` 参数表示需要输入密码。

指定主机和端口连接（适用于远程连接）:

```
mysql -h 主机名或IP地址 -P 端口号 -u 用户名 -p
```

例如：

```
mysql -h 127.0.0.1 -P 3306 -u root -p
```

###### 3、简单的SQL语句

列出所有可用的数据库：

```
SHOW DATABASES;
```

选择要使用的数据库：

```
USE your_database;
```

列出所选数据库中的所有表：

```
SHOW TABLES;
```

退出 **mysql>** 命令提示窗口可以使用 **exit** 命令，如下所示：

```
mysql> EXIT;
Bye
```

或者使用：

```
mysql> QUIT;
```

或者按下 **Ctrl + D**（在 Unix/Linux 系统中）。

###### 4、创建数据库

我们可以在登陆 MySQL 服务后，使用 **create** 命令创建数据库，语法如下:

```
CREATE DATABASE 数据库名;
```

以下命令简单的演示了创建数据库的过程，数据名为 helloworld:

```
[root@host]# mysql -u root -p
Enter password:****** # 登录后进入终端

mysql> create DATABASE helloworld;
```

建数据库的基本语法如下：

```
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];
```

如果你希望在创建数据库时指定一些选项，可以使用 CREATE DATABASE 语句的其他参数，例如，你可以指定字符集和排序规则：

```
CREATE DATABASE mydatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
```

如果数据库已经存在，执行 CREATE DATABASE 将导致错误。

为了避免这种情况，你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句：

```
CREATE DATABASE IF NOT EXISTS mydatabase;
```

###### 5、删除数据库

使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

**drop 命令删除数据库**

drop 命令格式：

```
DROP DATABASE ; -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] ;
```

**参数说明：**

- `IF EXISTS` 是一个可选的子句，表示如果数据库存在才执行删除操作，避免因为数据库不存在而引发错误。
- `database_name` 是你要删除的数据库的名称。

例如删除名为 helloworld 的数据库：

```
-- 直接删除数据库，不检查是否存在
mysql> DROP DATABASE helloworld;

-- 删除数据库，如果存在的话
DROP DATABASE IF EXISTS helloworld;
```

**注意：** 在执行删除数据库操作前，请确保你确实想要删除数据库及其所有数据，因为该操作是不可逆的。为了避免误操作，通常建议在执行删除之前备份数据库。

###### **6、创建数据表**

**语法**

以下为创建 MySQL 数据表的 SQL 通用语法：

```
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);

create table goods(
   name varchar(50),
   price decimal(10, 2),
   description varchar(500),
   address varchar(100)
)charset=utf8;

create table goods()charset=utf8;

添加数据：
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

insert into goods(name,price,description,address) values("小米 suv7", 10000.01, "are you ok", "beijing");

insert into goods(name,price,description,address) values("小米 Ultra", 100000.01, "are you ok", "beijing");
```

**参数说明：**

- `table_name` 是你要创建的表的名称。
- `column1`, `column2`, ... 是表中的列名。
- `datatype` 是每个列的数据类型。

以下是一个具体的实例，创建一个用户表 **users**：

```
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
birthdate DATE,
is_active BOOLEAN DEFAULT TRUE
);
```

实例解析：

- `id`: 用户 id，整数类型，自增长，作为主键。
- `username`: 用户名，变长字符串，不允许为空。
- `email`: 用户邮箱，变长字符串，不允许为空。
- `birthdate`: 用户的生日，日期类型。
- `is_active`: 用户是否已经激活，布尔类型，默认值为 true。

###### 7、删除数据表

语法

以下为删除 MySQL 数据表的通用语法：

```
DROP TABLE table_name; -- 直接删除表，不检查是否存在
```

或

```
DROP TABLE [IF EXISTS] table_name; -- 会检查是否存在，如果存在则删除
```

**参数说明：**

- `table_name` 是要删除的表的名称。
- `IF EXISTS` 是一个可选的子句，表示如果表存在才执行删除操作，避免因为表不存在而引发错误。

```
-- 删除表，如果存在的话
DROP TABLE IF EXISTS mytable;

-- 直接删除表，不检查是否存在
DROP TABLE mytable;
```

###### 8、插入数据

语法

以下为向MySQL数据表插入数据通用的 **INSERT INTO** SQL语法：

```
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
```

**参数说明：**

- `table_name` 是你要插入数据的表的名称。
- `column1`, `column2`, `column3`, ... 是表中的列名。
- `value1`, `value2`, `value3`, ... 是要插入的具体数值。

如果数据是字符型，必须使用单引号 **'** 或者双引号 **"**，如： 'value1', "value1"。

一个简单的实例，插入了一行数据到名为 users 的表中：

```
INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', 'test@google.com', '1990-01-01', true);
```

- `username`: 用户名，字符串类型。
- `email`: 邮箱地址，字符串类型。
- `birthdate`: 用户生日，日期类型。
- `is_active`: 是否已激活，布尔类型。

如果你要插入所有列的数据，可以省略列名：

```
INSERT INTO users
VALUES (NULL,'test', 'test@google.com', '1990-01-01', true);
```

这里，**NULL** 是用于自增长列的占位符，表示系统将为 **id** 列生成一个唯一的值。

如果你要插入多行数据，可以在 VALUES 子句中指定多组数值：

```
INSERT INTO users (username, email, birthdate, is_active)
VALUES
('test1', 'test1@google.com', '1985-07-10', true),
('test2', 'test2@google.com', '1988-11-25', false),
('test3', 'test3@google.com', '1993-05-03', true);
```

以上代码将在 users 表中插入三行数据。

查询数据：

语法：

```
SELECT COLUMN1,COLUMN2...... FROM TABLE_NAME; 显示指定字段
SELECT * FROM TABLE_NAME; 显示所有字段
```

修改数据：

语法：

```
UPDATE TABLE_NAME SET COLUMN=VALUE; 将某一列里面的所有数据都更新
UPDATE TABLE_NAME SET COLUMN=VALUE WHERE 条件;
```

###### 9、表结构修改

1. 添加列

```
ALTER TABLE 表名
ADD COLUMN 列名数据类型 [约束条件] [AFTER 现有列名|FIRST];
```

示例：

```
-- 在users表的email列后添加age列
ALTER TABLE users
ADD COLUMN age INT NOT NULL DEFAULT 0 AFTER email;

-- 添加多列
ALTER TABLE users
ADD COLUMN phone VARCHAR(20),
ADD COLUMN address VARCHAR(100);
```

2. 删除列

```
ALTER TABLE 表名
DROP COLUMN 列名;
```

示例：

```
ALTER TABLE users
DROP COLUMN age;
```

3. 修改列定义

```
ALTER TABLE 表名
MODIFY COLUMN 列名新数据类型 [新约束条件];
```

示例：

```
-- 修改username长度并添加唯一约束
ALTER TABLE users
MODIFY COLUMN username VARCHAR(50) UNIQUE;

-- 修改列默认值
ALTER TABLE users
MODIFY COLUMN status TINYINT DEFAULT 1;
```

4. 重命名列

```
ALTER TABLE 表名
CHANGE COLUMN 旧列名新列名数据类型 [约束条件];
```

示例：

```
ALTER TABLE users
CHANGE COLUMN reg_date register_date DATETIME;
```

### 三、MySQL数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

###### 1、数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
| :----------- | :--------------------------------------- | :----------------------------------------------------------- | :----------------------------------------------------------- | :-------------- |
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |

```

create table demo1(
name varchar(50),
age tinyint unsigned
);

create table demo2(
name varchar(50),
age int
);

create table demo3(
goods_name varchar(50),
price decimal(10, 2)
);

```

###### 2、日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
| :-------- | :------------ | :----------------------------------------------------------- | :------------------ | :----------------------- |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
| TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 **2147483647** 秒，北京时间 **2038-1-19 11:14:07**，格林尼治时间 2038年1月19日凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |

```
create table demo4(
title varchar(100),
add_time DATE
);

create table demo5(
title varchar(100),
add_time year
);

```

###### 3、字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

**注意**：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

```

create table demo1(
name char(10)
);

create table demo2(
title varchar(100)
);

create table demo3(
content LONGTEXT
);

```

### 四、字段约束

在数据库中，字段约束（也称为列约束）是一种规则，用于限制可以存储在表字段中的数据类型、范围或格式。它们确保数据的完整性和一致性，防止无效或不符合业务规则的数据进入数据库。

###### 1、非空约束(not null)

**作用**：字段必须包含值，插入 `NULL` 会报错。

示例：

```
CREATE TABLE users (
name VARCHAR(50) NOT NULL, -- 姓名不能为空
email VARCHAR(50),
);
```

###### 2、唯一约束(**UNIQUE**)

作用：字段值必须唯一，但可以为 `NULL`（多个 `NULL` 被视为相同）。

示例：

```
CREATE TABLE users2 (
email VARCHAR(50) UNIQUE -- 邮箱不能重复
);
```

###### 3、主键约束(**PRIMARY KEY**)

作用：唯一标识表中的记录，隐含 `NOT NULL + UNIQUE`。

示例：

```
CREATE TABLE products (
product_id INT PRIMARY KEY, -- 单字段主键
name VARCHAR(50)
);

-- 复合主键（多个字段组合唯一）
CREATE TABLE order_items (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);

create table wenzhang2(
   id int AUTO_INCREMENT primary key,
   title varchar(200) UNIQUE not null,
   author varchar(50) not null,
   address varchar(100) not null,
   add_time datetime not null,
   content longtext not null
);

insert into wenzhang(id,title, author,address,add_time,content)
values(1, "python 从入门到精通", "zhangsan", "beijing", "2025-5-23 20:59:01", "python nice!")

insert into wenzhang2(title,author,address,add_time,content)
values("python 从入门到精通", "zhangsan", "beijing", "2025-5-23 20:59:01", "python nice!");
```

###### 4、默认约束(**DEFAULT**)

作用：当插入数据未提供值时，自动使用默认值。

```
CREATE TABLE logs (
id INT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默认当前时间
);

create table demo5(
name varchar(50) not null,
age int not null
);
```

###### 5、自动增长(AUTO_INCREMENT)

在 MySQL 中，`AUTO_INCREMENT` 是一种特殊的字段属性，用于为表中的记录生成唯一的自增数值。它通常与主键（PRIMARY KEY）一起使用，确保每条记录都有一个唯一标识.

**创建表时指定 `AUTO_INCREMENT`**

```
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE
);
```

- 无需手动插入 `id` 值，MySQL 会自动生成下一个可用的整数。
- 初始值默认为 `1`，每次插入新记录时递增 `1`。

**起始值与步长**

- **起始值**：默认从 `1` 开始，可通过 `ALTER TABLE` 修改：

```
ALTER TABLE users AUTO_INCREMENT = 100; -- 下一个插入的id为100
```

- **步长**：通常为 `1`，不支持直接设置步长（由 MySQL 内部控制）。

**处理删除操作**

若删除某条记录，自增序列不会回退。例如：

```
DELETE FROM users WHERE id = 2; -- 删除id=2的记录
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');
-- 新记录的id=3（而非2）
```

**手动插入自增字段**

可以手动指定 `id` 值，但需确保不与现有值冲突：

```
INSERT INTO users (id, name, email) VALUES (10, 'David', 'david@example.com');
-- 强制使用id=10，下一个自动生成的id为11
```

###### 6、注释(comment)

在 MySQL 中，注释（**COMMENT**）是一种用于为表、列（字段）、存储过程等数据库对象添加说明文字的机制。它不会影响数据库的功能，但能帮助开发者理解表结构和数据含义。

**列（字段）注释**

为表中的字段添加描述性信息，便于后续维护。

```
CREATE TABLE usersss (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，自增主键',
name VARCHAR(50) NOT NULL COMMENT '用户姓名',
email VARCHAR(50) UNIQUE COMMENT '用户邮箱，唯一标识'
);
```

查看列注释：

```
SHOW FULL COLUMNS FROM users;
```

**表注释**

为整个表添加说明，解释其用途和数据范围。

```
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50)
) COMMENT = '存储所有商品的基础信息，包括名称、价格等';
```

查看表注释：

```
SHOW CREATE TABLE products;
```

### 五、数据表关系

在 MySQL 中，表关系（Table Relationships）用于建立不同数据表之间的关联，使数据能够以结构化方式组织和查询。常见的表关系包括**一对一**、**一对多**和**多对多**，这些关系通过外键实现。

###### 1、 **一对一关系（One-to-One）**

**特点**

- **一个记录仅对应另一个表中的一个记录**：例如，一个用户（User）只有一个详细资料（Profile）。
- **实现方式**：在任意一方添加唯一外键，指向另一方的主键。

```sql
-- 用户表
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50)
);

-- 资料表
CREATE TABLE profiles (
profile_id INT PRIMARY KEY,
address VARCHAR(100),
user_id INT UNIQUE, -- 唯一外键，关联users表的user_id
);

create table student(
id int unsigned auto_increment comment "主键",
name varchar(50) not null comment "学生的名字",
age tinyint unsigned not null comment "学生的年纪",
sex tinyint unsigned not null default 1 comment "1代表男,0代表女",
primary key(id)
);

create table student_profile(
height tinyint unsigned not null comment "学生的身高",
id_num char(18) unique not null comment "学生的身份证号码",
student_id int unsigned not null comment "存储学生的id"
);

张三 18 1 182 23412341234123412341234

```

###### 2、**一对多关系（One-to-Many）**

**特点**

- **一个记录对应多个记录**：例如，一个部门（Department）可以有多个员工（Employee）。

- **实现方式**：在 “多” 的一方表中添加外键，指向 “一” 的一方的主键。

```sql
-- 部门表（一）
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

-- 员工表（多）
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT, -- 外键，关联departments表的dept_id
);



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

create table department(
   id int unsigned auto_increment comment "主键",
   dept_name varchar(50) not null comment "部门的名称",
leader varchar(50) not null comment "部门领导",
information text not null comment "部门介绍",
primary key(id)
);

insert into department(dept_name, leader, information) values("人事部", "zhangsan", "adfadsadfadsfsaddf");
insert into department(dept_name, leader, information) values("财务部", "lisi", "adfadsadfadsfsaddf");



create table employees(
   id int unsigned auto_increment comment "主键",
name varchar(50) not null comment "员工的名称",
salary int unsigned not null comment "员工的工资",
department_id int unsigned not null comment "部门的id",
   primary key(id)
);


insert into employees(name, salary, department_id) values("小红", 8000, 1);
insert into employees(name, salary, department_id) values("小花", 9000, 1);

```

###### 3、**多对多关系（Many-to-Many）**

**特点**

- **多个记录对应多个记录**：例如，一个学生（Student）可以选修多门课程（Course），一门课程也可以被多个学生选修。
- **实现方式**：通过创建中间表（关联表），将多对多拆分为两个一对多关系。

```sql
-- 学生表
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50)
);

-- 课程表
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);

-- 中间表（关联表）
CREATE TABLE student_courses (
student_id INT,
course_id INT,
);

学生管理系统：

create table student2(
id int unsigned auto_increment comment "主键",
name varchar(50) not null comment "学生的名称",
age tinyint unsigned not null comment "学生的年纪",
sex tinyint unsigned not null default 1 comment "1代表男,0代表女",
courses varchar(500) not null comment "学习选择的课程",
primary key(id)
);

insert into student2(name,age,sex, courses) values(
"小红", 18, 1, "1|2|3"
);

================================== 优化版 =======================================

create table student3(
id int unsigned auto_increment comment "主键",
name varchar(50) not null comment "学生的名称",
age tinyint unsigned not null comment "学生的年纪",
sex tinyint unsigned not null default 1 comment "1代表男,0代表女",
primary key(id)
);

insert into student3(name,age,sex, courses) values(
"小红", 18, 1
);

insert into student3(name,age,sex, courses) values(
"小花", 18, 0
);
小红id: 1
小花id: 2

create table student_course(
student_id int unsigned not null comment "学生id",
course_id int unsigned not null comment "课程id",
);

insert into student_course(student_id, course_id) values(1, 1); 小红计算机组成原理
insert into student_course(student_id, course_id) values(1, 2); 小红操作系统
insert into student_course(student_id, course_id) values(1, 3); 小红数据库理论

insert into student_course(student_id, course_id) values(2, 2); 小花操作系统
insert into student_course(student_id, course_id) values(2, 3); 小花数据库理论

create table course(
id int unsigned auto_increment comment "主键",
course_name varchar(50) not null comment "课程的名称",
course_count int unsigned not null comment "课时",
primary key(id)
);

insert into course(course_name, course_count) values("计算机组成原理", 150); 1
insert into course(course_name, course_count) values("操作系统", 140); 2
insert into course(course_name, course_count) values("数据库理论", 30); 3

```

### 六、查询

###### 1. 条件

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

- 语法如下：

```sql
select * from 表名 where 条件;
例：
select * from students where id=1;

create table student(
id int unsigned auto_increment comment "主键",
name varchar(50) not null comment "学生的名称",
age tinyint unsigned not null comment "学生的年纪",
gender tinyint unsigned not null default 1 comment "学生的性别,1代表男,0代表女",
height tinyint unsigned not null comment "学生的身高",
address varchar(500) not null comment "地址",
primary key(id)
);

insert into student(name,age,gender,height,address) values("小红", 18, 0, 182, "北京");
insert into student(name,age,gender,height,address) values("小花", 19, 0, 170, "长沙");
insert into student(name,age,gender,height,address) values("小绿", 20, 1, 160, "深圳");
insert into student(name,age,gender,height,address) values("小明", 30, 0, 182, "上海");
insert into student(name,age,gender,height,address) values("大明", 40, 1, 190, "新疆");
insert into student(name,age,gender,height,address) values("二明", 35, 1, 187, "西藏");
insert into student(name,age,gender,height,address) values("小蓝", 16, 0, 150, "长沙");
insert into student(name,age,gender,height,address) values("小紫", 16, 0, 150, "长沙");
insert into student(name,age,gender,height,address) values("大牛", 16, 1, 150, "上海");

insert into student(name,age,gender,height,address) values("如花", 16, 3, 150, "上海");

insert into student(name,age,gender,height,address) values("凤姐", 25, 0, 150, "上海");

```

- where后面支持多种运算符，进行条件的处理
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断

比较运算符

- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>

例1：查询编号大于3的学生

```sql
select * from students where id > 3;
```

例2：查询编号不大于4的学生

```sql
select * from students where id <= 4;
```

例3：查询姓名不是“黄蓉”的学生

```sql
select * from students where name != '黄蓉';
```

例4：查询没被删除的学生

```sql
select * from students where is_delete=0;
```

逻辑运算符

- and
- or
- not

例5：查询编号大于3的女同学

```sql
select * from students where id > 3 and gender=0;
```

例6：查询编号小于4或没被删除的学生

```sql
select * from students where id < 4 or is_delete=0;
```

模糊查询

- like
- %表示任意多个任意字符
- %
- _表示一个任意字符

例7：查询姓黄的学生

```sql
select * from students where name like '黄%';
```

例8：查询姓黄并且“名”是一个字的学生

```sql
select * from students where name like '黄_';
```

例9：查询姓黄或叫靖的学生

```sql
select * from students where name like '黄%' or name like '%靖';
```

范围查询

- in表示在一个非连续的范围内

例10：查询编号是1或3或8的学生

```sql
select * from students where id in(1,3,8);
```

- between ... and ...表示在一个连续的范围内

例11：查询编号为3至8的学生

```sql
select * from students where id between 3 and 8;
```

例12：查询编号是3至8的男生

```sql
select * from students where (id between 3 and 8) and gender=1;
```

空判断

- 注意：null与''是不同的
- 判空is null

例13：查询没有填写身高的学生

```sql
select * from students where height is null;
```

- 判非空is not null

例14：查询填写了身高的学生

```sql
select * from students where height is not null;
```

例15：查询填写了身高的男生

```sql
select * from students where height is not null and gender=1;
```

优先级

- 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
- and比or先运算，如果同时出现并希望先算or，需要结合()使用

###### 2. 排序

为了方便查看数据，可以对数据进行排序

语法：

```sql
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
```

说明

- 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
- 默认按照列值从小到大排列（asc）
- asc从小到大排列，即升序
- desc从大到小排序，即降序

例1：查询未删除男生信息，按学号降序

```sql
select * from students where gender=1 and is_delete=0 order by id desc;
```

例2：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时按照身高从高-->矮排序

```sql
select * from students order by age desc,height desc;
```

###### 3. 聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

总数

- count(*)表示计算总行数，括号中写星与列名，结果是相同的

例1：查询学生总数

```sql
select count(*) from students;
```

最大值

- max(列)表示求此列的最大值

例2：查询女生的编号最大值

```sql
select max(id) from students where gender=2;
```

最小值

- min(列)表示求此列的最小值

例3：查询未删除的学生最小编号

```sql
select min(id) from students where is_delete=0;
```

求和

- sum(列)表示求此列的和

例4：查询男生的总年龄

```sql
select sum(age) from students where gender=1;

-- 平均年龄
select sum(age)/count(*) from students where gender=1;
```

平均值

- avg(列)表示求此列的平均值

例5：查询未删除女生的编号平均值

```sql
select avg(id) from students where is_delete=0 and gender=2;
```

###### 4. 分组

group by

1. group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
2. group by可用于单个字段分组，也可用于多个字段分组

```

gender count members
1 20 小红,小花,小紫....
0 30 大明,二明
```

group by + group_concat()

1. group_concat(字段名)可以作为一个输出字段来使用，
2. 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

```sql
select gender from students group by gender;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 中性 |
| 保密 |
+--------+

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name) |
+--------+-----------------------------------------------------------+
| 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 |
| 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 |
| 中性 | 金星 |
| 保密 | 凤姐 |
+--------+-----------------------------------------------------------+

select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男 | 3,4,8,9,14 |
| 女 | 1,2,5,7,10,12,13 |
| 中性 | 11 |
| 保密 | 6 |
+--------+------------------+
```

group by + 集合函数

1. 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个`值的集合`做一些操作

```sql
select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age) |
+--------+----------------------+
| 男 | 29,59,36,27,12 |
| 女 | 18,18,38,18,25,12,34 |
| 中性 | 33 |
| 保密 | 28 |
+--------+----------------------+

分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男 | 32.6000 |
| 女 | 23.2857 |
| 中性 | 33.0000 |
| 保密 | 28.0000 |
+--------+----------+

分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+

gender avg_age
1 20
0 18
```

group by + having

1. having 条件表达式：用来分组查询后指定一些条件来输出查询结果
2. having作用和where一样，但having只能用于group by

```sql
select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
+--------+----------+
```

###### 5. 获取部分行

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

语法

```sql
select * from 表名 limit start,count
```

说明

- 从start开始，获取count条数据

例1：查询前3行男生信息

```sql
select * from students where gender=1 limit 0,3;
```

示例：分页

- 已知：每页显示m条数据，当前显示第n页
- 求总页数：此段逻辑后面会在python中实现
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总数页
- 如果不整除则p2+1为总页数
- 求第n页的数据

```sql
select * from students where is_delete=0 limit (n-1)*m,m
```

###### 6. 连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

- 内连接查询：查询的结果为两个表匹配到的数据

- 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

- 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

```
需求：查询数据库获取小红的工资和部门信息。
select * from employees where id = 1;

select employees.id,employees.name,employees.salary,department.dept_name from employees
join department
on department.id = employees.department_id
where employees.id = 1;

select employees.id,employees.name,employees.salary,department.dept_name from employees
right join department
on department.id = employees.department_id
where employees.id = 1;

select employees.id,employees.name,employees.salary,department.dept_name from employees
left join department
on department.id = employees.department_id
where employees.id = 1;

select employees.id,employees.name,employees.salary,department.dept_name from employees join department
on department.id = employees.department_id
where employees.id = 3;

```

语法

```sql
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
```

例1：使用内连接查询班级表与学生表

```sql
select * from students inner join classes on students.cls_id = classes.id;
```

例2：使用左连接查询班级表与学生表

- 此处使用了as为表起别名，目的是编写简单

```sql
select * from students as s left join classes as c on s.cls_id = c.id;
```

例3：使用右连接查询班级表与学生表

```sql
select * from students as s right join classes as c on s.cls_id = c.id;
```

例4：查询学生姓名及班级名称

```sql
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
```

### 七、Python操作MySQL

###### 1. 安装 pymysql

```bash
pip install pymysql
```

###### 2. 连接数据库

```python
import pymysql

# 连接数据库
conn = pymysql.connect(
host='localhost', # 主机名
user='your_username', # 用户名
password='your_password', # 密码
database='your_database', # 数据库名
charset='utf8mb4', # 字符集
cursorclass=pymysql.cursors.DictCursor # 返回字典格式的结果
)

try:
# 创建游标对象
with conn.cursor() as cursor:
# 执行 SQL 查询
sql = "SELECT VERSION()"
cursor.execute(sql)
# 获取单条数据
result = cursor.fetchone()
print(f"数据库版本: {result}")
finally:
# 关闭数据库连接
conn.close()
```

###### 3. 创建表

```python
try:
with conn.cursor() as cursor:
# 创建 users 表
sql = """
CREATE TABLE IF NOT EXISTS users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(100) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
cursor.execute(sql)
# 提交事务
conn.commit()
print("表创建成功")
except Exception as e:
print(f"表创建失败: {e}")
# 回滚事务
conn.rollback()
```

###### 4. 插入数据

```python
try:
with conn.cursor() as cursor:
# 插入单条数据
sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
cursor.execute(sql, ('Alice', 25, 'alice@example.com'))

# 插入多条数据
users = [
('Bob', 30, 'bob@example.com'),
('Charlie', 22, 'charlie@example.com')
]
sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
cursor.executemany(sql, users)

# 提交事务
conn.commit()
print(f"成功插入 {cursor.rowcount} 条数据")
except Exception as e:
print(f"数据插入失败: {e}")
conn.rollback()
```

###### 5. 查询数据

```python
# 查询所有数据
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM users"
cursor.execute(sql)
results = cursor.fetchall() # 获取所有记录
for row in results:
print(f"ID: {row['id']}, 姓名: {row['name']}, 年龄: {row['age']}, 邮箱: {row['email']}")
except Exception as e:
print(f"查询失败: {e}")

# 查询单条数据
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM users WHERE id = %s"
cursor.execute(sql, (1,))
result = cursor.fetchone() # 获取单条记录
print(f"ID为1的用户: {result}")
except Exception as e:
print(f"查询失败: {e}")
```

###### 6. 更新数据

```python
try:
with conn.cursor() as cursor:
sql = "UPDATE users SET age = age + 1 WHERE name = %s"
cursor.execute(sql, ('Alice',))

# 提交事务
conn.commit()
print(f"成功更新 {cursor.rowcount} 条数据")
except Exception as e:
print(f"数据更新失败: {e}")
conn.rollback()
```

###### 7. 删除数据

```python
try:
with conn.cursor() as cursor:
sql = "DELETE FROM users WHERE age < %s"
cursor.execute(sql, (25,))

# 提交事务
conn.commit()
print(f"成功删除 {cursor.rowcount} 条数据")
except Exception as e:
print(f"数据删除失败: {e}")
conn.rollback()
```

###### 8. 使用上下文管理器管理连接

为了简化资源管理，可以将数据库连接封装成上下文管理器：

```python
class MySQLConnection:
def __init__(self, host, user, password, database):
self.host = host
self.user = user
self.password = password
self.database = database
self.conn = None

def __enter__(self):
self.conn = pymysql.connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
cursorclass=pymysql.cursors.DictCursor
)
return self.conn

def __exit__(self, exc_type, exc_val, exc_tb):
if self.conn:
self.conn.close()

# 使用示例
with MySQLConnection('localhost', 'user', 'password', 'dbname') as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
print(row)
```

###### 9. 处理数据库事务

```python
try:
with conn.cursor() as cursor:
# 开始事务
conn.begin()

# 执行多个操作
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('David', 28))
cursor.execute("UPDATE users SET age = age + 1 WHERE name = 'Bob'")

# 提交事务
conn.commit()
print("事务提交成功")
except Exception as e:
# 回滚事务
conn.rollback()
print(f"事务回滚: {e}")
```

```

create table blog(
   id int unsigned not null auto_increment comment "主键",
   title varchar(200) not null comment "文章标题",
   authoer varchar(50) not null comment "文章作者",
   description varchar(500) not null comment "文章摘要",
   content text not null comment "内容",
   primary key(id)
)charset=utf8;

```

[数据库] MySQL详解

一、数据库概述

1、什么是数据库？

2、数据库管理系统(DBMS)

3、数据库分类

新人须知

常见问题

关于我们