MySql--基础--2一. 创建数据库和删除数据库二：常用的数据类型和常用的键约束三：创建表，更改表（更新表），删

一. 创建数据库和删除数据库

创建数据库

CREATE DATABASE <数据库名字> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE DATABASE <数据库名字>：创建数据库
CHARACTER SET utf8mb4：设置字符集
utf8mb4是默认字符集----我们设置一般不需要
COLLATE utf8mb4_general_ci：设置排序规则
utf8mb4_0900_ai_ci：是默认排序规则----我们设置一般不需要

通常我们使用`CREATE DATABASE IF NOT EXISTS <数据库名字>;`

使用IF NOT EXISTS，如果表不存在才会创建，更加安全

删除数据库

DROP DATABASE <数据库名字>;

执行删除命令后，数据库将被永久删除。
您可以使用数据库客户端或命令行工具验证数据库是否已成功删除。

二. 数据库和表

1. 数据库：数据的容器

数据库是一个组织化、结构化的数据集合
它可以包含一个或多个表，以及其他数据库对象，如视图、索引、存储过程等。
数据库的主要作用是存储、管理和检索数据。

2. 表：数据的基本单元

表是数据库中存储数据的基本单元。
它由行和列组成，类似于电子表格。
每一行代表一个记录，每一列代表一个属性。
表用于存储特定类型的数据，例如客户信息、产品信息、订单信息等。

3. 数据库与表的关系

数据库是表的容器，表是数据库的组成部分。
一个数据库可以包含多个表，这些表之间可以通过关系相互关联。
表之间的关系可以通过主键和外键来建立，从而实现数据的关联和查询。
通过表之间的关系，可以有效地组织和管理复杂的数据，避免数据冗余和不一致性。

三. 数据类型和列约束

MySQL常用的数据类型有四类（个人认为，不专业）：

1.TEXT: CHAR(X) VARCHAR(X) TEXT LOGNTEXT ENUM
2.NUM: INT SMALLINT DECIMAL NUMERIC FLOAT REAL
3.Data: DATE DATETIME TIMESTAMP
4.Other: BOOLEAN JSON SERIAL

1.TEXT:

`CHAR(<数字>)`:

定长字符串

`VARCHAR(<数字>)`:

变长字符串

`TEXT,LONGTEXT`:

长的文本字符串，非常长的文本字符串

`ENUM`:

枚举类型，允许从预定义的列表中选择值

2.NUM:

`INT, SMALLINT`

普通大小的整数, 小整数

`DECIMAL, NUMERIC`

用于存储精确的数值,例如货币数据

`FLOAT, REAL`

单精度浮点数, 双精度浮点数

3.Data:

`DATE`

日期（YYYY-MM-DD）

`DATETIME,TIMESTAMP`

日期和时间（YYYY-MM-DD HH:MM:SS）,时间戳，通常用于记录数据的修改时间

4.Other:

`BOOLEAN`：

布尔类型，存储 TRUE 或 FALSE 值

`JSON`

JSON 数据类型，用于存储 JSON 格式的数据。

`SERIAL`PostgreSQL中

“serial”通常指的是一种数据类型或属性，用于自动生成唯一的、顺序递增的整数值。它主要用于为表中的记录生成唯一标识符，例如主键

列约束

1. NOT NULL（非空约束）

作用： 确保列中的值不能为 NULL。
用法： 在列定义中添加 NOT NULL。
示例： name VARCHAR(255) NOT NULL

2. UNIQUE（唯一约束）

作用： 确保列中的所有值都是唯一的。
用法： 在列定义中添加 UNIQUE。
示例： email VARCHAR(255) UNIQUE
注意： 允许存在多个 NULL 值。

3. PRIMARY KEY（主键约束）

作用： 唯一标识表中的每一行。
用法： 在列定义中添加 PRIMARY KEY。
特点：
- 主键列必须包含唯一的值。
- 主键列不能包含 NULL 值。
- 一个表只能有一个主键。
示例： id INT PRIMARY KEY

4. FOREIGN KEY（外键约束）

作用： 定义与其他表的关系。
用法： 在列定义中添加 FOREIGN KEY，并指定引用的表和列。
特点：
- 外键列的值必须在引用的表的主键列中存在，或者为 NULL。
- 用于维护表之间的引用完整性。
示例： FOREIGN KEY (order_id) REFERENCES orders(id)

5. CHECK（检查约束）

作用： 限制列中的值必须满足指定的条件。
用法： 在列定义中添加 CHECK，并指定条件。
示例： age INT CHECK (age >= 18)
注意： 不同数据库系统对 CHECK 约束的支持程度可能不同。

6. DEFAULT（默认约束）

作用： 为列指定默认值。
用法： 在列定义中添加 DEFAULT，并指定默认值。
示例： created_at DATETIME DEFAULT CURRENT_TIMESTAMP

7. AUTO_INCREMENT（自动递增约束）

作用： 自动生成递增的整数值。
用法： 在整数列定义中添加 AUTO_INCREMENT。
特点：
- 通常用于主键列。
- MySQL 特有。
示例： id INT PRIMARY KEY AUTO_INCREMENT

四. 创建表

--表1
CREATE TABLE User (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- 主键，自增
    username VARCHAR(50) NOT NULL UNIQUE,  -- 唯一约束
    email VARCHAR(100) NOT NULL UNIQUE,   -- 唯一约束
    bio TEXT,  -- 长文本
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 默认值
);

--表2
CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,  
    user_id INT NOT NULL,  -- 关联 User 表
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),  -- CHECK 约束，金额必须大于 0
    tax_rate FLOAT DEFAULT 0.07,  
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE  -- 外键，级联删除
);

--表3
CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),  
    in_stock BOOLEAN DEFAULT TRUE,  
    category ENUM('Electronics', 'Clothing', 'Books', 'Food') NOT NULL
);

--表4
CREATE TABLE Settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    preferences JSON NOT NULL,  -- 存储用户配置 JSON 数据
    FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE
);

--表5
CREATE TABLE Logs (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    log_data BLOB NOT NULL,  -- 存储二进制数据
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE
);

五. 更新表

更改表的结构

ALTER TABLE 语句
更新表中的数据（UPDATE 语句）属于增删改查（这里我们不讨论，后面讨论）

常用的方法

在 MySQL 中，可以使用 ALTER TABLE 语句来修改表的结构，比如 添加、删除、修改列 或 更改约束。下面是一些常见的 ALTER TABLE 操作示例。

1. 添加新列

给 User 表添加 phone_number 列：

ALTER TABLE User ADD COLUMN phone_number VARCHAR(20) UNIQUE;

这会在 User 表中增加一个 电话号码 字段，并确保其值是唯一的。

2. 删除列

如果不再需要 bio 字段，可以删除：

ALTER TABLE User DROP COLUMN bio;

3. 修改列的数据类型

将 username 字段扩展为 VARCHAR(100)：

ALTER TABLE User MODIFY COLUMN username VARCHAR(100) NOT NULL UNIQUE;

4. 重命名列

将 email 列重命名为 user_email：

ALTER TABLE User CHANGE COLUMN email user_email VARCHAR(100) NOT NULL UNIQUE;

5. 修改默认值

将 Orders 表的 tax_rate 默认值改为 0.05：

ALTER TABLE Orders ALTER COLUMN tax_rate SET DEFAULT 0.05;

6. 添加外键

如果 Orders 表的 user_id 还没有外键，我们可以这样添加：

ALTER TABLE Orders ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE;

7. 删除外键

如果 Orders 表已经有外键 fk_orders_user，但你想删除它：

ALTER TABLE Orders DROP FOREIGN KEY fk_orders_user;

8. 添加 CHECK 约束

如果想确保 price 必须大于 0，可以这样修改 Products 表：

ALTER TABLE Products ADD CONSTRAINT chk_price CHECK (price > 0);

9. 删除 CHECK 约束

如果需要移除 chk_price 约束：

ALTER TABLE Products DROP CONSTRAINT chk_price;

删除表

基本语法：

DROP TABLE table_name;

table_name: 要删除的表的名称。

删除多个表：

可以使用逗号分隔多个表名，一次性删除多个表：

DROP TABLE table1, table2, table3;

IF EXISTS 子句：

为了防止在表不存在时出现错误，可以使用 IF EXISTS 子句：

DROP TABLE IF EXISTS table_name;

如果表存在，则删除该表。
如果表不存在，则不会报错，只会发出警告

重要注意事项：

数据丢失：
- DROP TABLE 语句会永久删除表及其所有数据。
- 在执行此操作之前，务必备份重要数据。
权限：
- 您需要具有删除表的权限才能执行 DROP TABLE 语句。
- 一般我们自己创建的表是root（最高权限）
外键约束：
- 如果其他表具有引用您要删除的表的外键约束，则删除操作可能会失败。
- 在这种情况下，您需要先删除外键约束，或者使用 CASCADE 选项（如果支持）来同时删除相关表。
谨慎操作：
- 删除表是一个不可逆的操作，请务必谨慎操作。
- 在生产环境中执行此操作之前，请务必进行测试。

MySql--基础--2