数据库圣经——第三章CRUD(二)

一.下载参考文档

1.登录Mysql官网

地址：https://www.mysql.com/cn/

2.找到MySQL Reference Manual

3.找到对应的版本，我用的是8.0

4.跳转到该界面

5.左边有个滚轮，往下滑，在最后，找到PDF版本，大约6000+页

二.CRUD

1.创建数据库

创建数据库语法：

{ }： 大括号的关键字必须存在，| 就是DATABASE 与 SCHEMA 二选一，都表示数据库

[ ] : 中括号标表示可有可无，表示可选项。

例如：这里的[IF NOT EXISTS] 表示不存在的话，则创建数据库，存在就不执行。

CHARACTER SET : 是字符集 （8.0默认是utf8mb4）

COLLATE : 排序规则 (8.0是utf8mb4_0900_ai_ci）

ENCRYPTION : 是否加密 Yes or No

db_name : 就是数据库的名称

注：博主强烈建议大家创建数据库的时候，加上明确的字符集

create database database_name character set utf8mb4 collate utf8mb4_0900_ai_ci;

注意： MySQL 不允许直接使用关键字，但允许开发者间接使用关键字，在使用的过程中要加上反引号，用反引号把关键字引起来，反引号在键盘的位置是 Esc 的下面，Tab的上面，1的左边

我们show databases; 检查一下是否创建成功这个库

2.查看当前数据库服务全局的默认字符集

查看当前数据库服务全局的默认字符集

show variables like '%character%';

单字节字符集：

ASCII:

• 最基础的字符集，仅包含 128 个字符（0-127），包括英文字母、数字、标点符号和控制字符（如换行、回车）。
• 每个字符用 1 字节（8 位，最高位为 0）表示，无法表示中文、日文等非英文字符。

多字节字符集（主要用于亚洲语言）:

GBK:

• 对 GB2312 的扩展（“汉字内码扩展规范”），包含 21003 个汉字（涵盖简体、繁体），以及日文、韩文等符号。
• 兼容 GB2312，每个字符用 2 字节表示，是中文 Windows 系统默认的传统编码之一.

Unicode 及相关字符集（全球通用）

Unicode 是国际标准，为全球所有语言的每个字符分配唯一的数字编码（称为 “码点”，如 U+4E2D 表示 “中”），但 Unicode 仅定义码点，不规定存储方式，因此衍生出多种实现方式：

1. UTF-8
   • 目前最流行的 Unicode 实现方式，可变长度编码：
     • 英文字符用 1 字节（兼容 ASCII），汉字用 3 字节，生僻字可能用 4 字节。
     • 支持全球所有语言，节省存储空间，是互联网、编程（如 Java、Python）、数据库（如 MySQL）的首选编码。
2. UTF-16
   • 每个字符用 2 或 4 字节表示：常用字符（如大部分汉字）用 2 字节，生僻字用 4 字节。
   • 是 Windows 系统、Java 内存中的默认编码方式。
3. UTF-32
   • 每个字符固定用 4 字节表示，直接对应 Unicode 码点，编码 / 解码简单但占用空间大，较少用于存储。

输出结果下图：

utf8mb3 : 每个字符最多用 3 字节 存储。但它不支持 emoji 表情,每个字符最多用 3 字节 存储。但它不支持 emoji 表情

utf8mb4 :是 MySQL 对 UTF-8 标准的完整实现，支持 Unicode 中所有码点,每个字符最多用 4 字节 存储。它可以完美支持 emoji、罕见汉字、特殊符号等 utf8mb3 无法覆盖的字符。

cmd中，输入这个语句，client 的字符集是gbk

在属性中他是使用gbk

3.查看警告信息

语句如下：

show warnings;

执行结果如下：

4.选择要操作的数据库

语句：use 数据库名称

use database_name;

MySQL 的这个语句不是很规范，可以不加[ ;], 我们在使用该语句的时，加上分号;

5.查看数据库版本

语句：

select version();

执行结果：

6.查询当前所在的数据库名称

MySQL 数据库的查询语句，用于查询当前所在的数据库名称。

执行语句

select database();

7.删除数据库

执行过程如下：

Query OK; 出现了那说明语句执行完成了。

在服务器中一般都是Linux 系统上删除，没有回收站这一说，删除就没有了

在磁盘中删除文件，并不是真正意义的删除，索引标记替代数据擦除,补数据标记为已删除的状态，但是没有删除，然后有新的数据存进来，就把这个覆盖了(如果说我们覆盖的次数不是很多，我们可以找这个从磁盘的厂家找回，但是这个想找回了肯定是要收费的)

MySQL删除可以通过日志来恢复,数据库备份每天备份(增量备份记录了今天比昨天的新增的数据),每周备份一次（全量备份记录了这一周的所有数据），通过日志可以快速把删除的数据快速执行一遍，完成恢复。

注意：在执行删库的过程中，要把你的领导，同事喊过来一起干这个事情，并把与领导要求你删库的聊天记录保存好

8.\？查看MySQL 的帮助

创建完数据库之后，就要在数据库中创建表，表中存储数据记录，一条记录存储不同的列组成，每个列需要什么样的数据类型

三.常用的数据类型

1.数值类型

1.float 和 double 类型在表示小数的时候，不是特别精准，所以用decimal 类型去表示小数

2.有些系统中表示金额，一般把元为单位换成以分为单位，然后用int 类型去表示金额

3.decimal 类型M表示的是长度，长度M中不计算小数喊口号和符号(一般是负号)，如果D为0，那就说明没有小数点和小数部分(也就是保存的是一个整数)

4.decimal的最大位数(M)就是 65，支持的最大的小数位是 30

不同数据库对其默认值的定义有所差异，以 MySQL 为例：

• M（总有效数字位数）：默认值是 10；
• D（小数位数）：默认值是 0。

扩展资料

• 数值类型可指定为无符号（unsigned），表示不取负数。
• 1 字节（bytes）= 8bit。
• 整型类型的范围：
  1. 有符号范围：-2^(类型字节数8-1) 到 2^(类型字节数8-1) - 1，如 int 是 4 字节，范围是 - 2^31 到 2^31-1
  2. 无符号范围：0 到 2^(类型字节数 * 8) - 1，如 int 范围是 2^32-1
• 尽量不使用 unsigned，对于 int 类型可能存放不下的数据，int unsigned 同样可能存放不下，建议设计时将 int 类型提升为 bigint 类型。
• 这是官网给出来的，大家可以参考一下

2.字符类型

1.varchar(size)最常用的字符串数据类型，可以指定长度为多少，长度是用户设定的字符数量上限，大小是实际存储时的字节消耗，这两点一定要区分，两者因字符集、实际内容的不同而产生差异。

例子：VARCHAR(10) 表示最多存 10 个字符，不管字符是汉字还是英文

定义这一个字段，字段可以放10 或者10 以内的字符，超过10个就报错，之前我们设置了字符集utf8mb4编码集，一个字符最多占4个字节，根据字符的具体情况占1-4个字节，在utf8mb4编码集下最多存储16383个字符

2.TEXT 是文本类型，存储较长文本，如文章摘要、评论，上边还有 MEDIUMTEXT ，LONGTEXT，存储更大的文本等

3.在数据库不建议存储非常大的文件，而是把文件存储到磁盘上，在对应的数据库中保存文件路径，在查询文件的时候，先去查询他的文件路径，通过文件路径在磁盘上加载或者返回真实的文件

4.

1. CHAR 类型

• 特性：固定长度的字符串。
• 长度定义：M 表示以字符为单位的列长度，取值范围 0 ~ 255；若省略 M，长度默认是 1。
• 存储与读取：存储时会用空格向右填充到指定长度，读取时会从尾部删除空格。
• 特殊情况：允许定义 CHAR(0)，此时列的值只能是 NULL 或空字符串，主要用于旧系统兼容（比如表结构必须包含该列，但实际不使用其值）。
• 特性：可变长度的字符串。
• 长度定义：M 表示以字符为单位的最大列长度，取值范围 0 ~ 65535（所有列共享这个长度上限）。有效长度由实际字符数和字符集共同决定。
• 存储机制：用额外的 1~2 个字节记录实际使用的字节数（实际字节数 ≤255 时用 1 字节，＞255 时用 2 字节）。
• 存储与读取：插入数据时会删除超出长度的空格，读取时不会删除尾部空格。

2.VARCHAR 类型

• 特性：可变长度的字符串。
• 长度定义：M 表示以字符为单位的最大列长度，取值范围 0 ~ 65535（所有列共享这个长度上限）。有效长度由实际字符数和字符集共同决定。
• 存储机制：用额外的 1~2 个字节记录实际使用的字节数（实际字节数 ≤255 时用 1 字节，＞255 时用 2 字节）。
• 存储与读取：插入数据时会删除超出长度的空格，读取时不会删除尾部空格

3. 如何选择 CHAR 与 VARCHAR

• 若数据长度固定（如身份证号、MD5 值、学号、邮编等），使用 CHAR 类型。
• 若数据长度不固定（如姓名、地址等），使用 VARCHAR 类型，但需规划好最大长度以确保能存储最长字符串。
• CHAR 类型：较浪费磁盘空间，但效率更高（直接开辟固定存储空间）。
• VARCHAR 类型：节省磁盘空间，但效率较低（在定义长度范围内，按需分配存储空间）。

4 VARCHAR 与 TEXT 的区别

• 容量大小：
  • VARCHAR 最大支持 65535 个字节。
  • TEXT 最大支持 65535 个字节；若超过，会自动转换为 MEDIUMTEXT（最大 16,777,215 字节）；若再超过，转换为 LONGTEXT。
• 存储位置：
  • VARCHAR：实际内容小于 768 字节时，存储在当前行；大于 768 字节时，存储在溢出页，当前行仅保存溢出页地址。
  • TEXT：整体存储在溢出页，当前行仅保存溢出页地址。
• 查询性能：
  • VARCHAR：频繁查询的列可创建普通索引，提升性能。
  • TEXT：无法直接创建普通索引，仅支持 FULLTEXT 索引；因存储位置和索引支持差异，查询性能低于 VARCHAR。

3.整型类型：

4.日期类型

1.TIMESTAMP(timestamp): 记录的是一个时间戳，时间是1970 到 2038 的这个时间节点，MySQL是以毫秒为单位计时的，以四字节的范围就这些，溢出了就会，这个就不要使用了

2.有关的可以从官网进行学习

四.表的操作

需要操作数据库的表的时候，先选择我们要使用的库

use database_name;

1.创建表

create table table_name (
name varchar(8);
--name varchar(8) comment '我要进行一个说明';
--varchar 要记得要写长度
--注意：强烈建议每一张表都要设计一个编号字段，数据类型为bigint
);

加上校验的话就是：create table if not exists table_name

例子：

在编辑器中把SQL编辑好之后，保存为一个本地的.sql文件

source 保存的.sql

注意：命令行客户端工具，只执行一些简单的查询或者命令，如果一两句SQL 可以搞定就在这里面去执行，如果说要编复杂的SQL ，比如表的设计，复杂查询，建议大家还是去可视化工具编写。

执行过程如下：

source 保存.sql文件

• TEMPORARY：表示创建的是一个临时表。
• field：列名。
• datatype：数据类型。
• comment：对列的描述或说明。
• engine：存储引擎，不指定则使用默认存储引擎。
• characterset：字符集，不指定则使用默认字符集。
• collate：排序规则，不指定则使用默认排序规则。

2.查看表

查看数据表里有哪些表，记得要加复数

show tables;

3.查看表的结构

desc 表名;

1.第一列就是字段名，就是列名

2.第二列就是数据类型

3.是不是允许为空

4.default 默认值，什么都不指定的时候就是null

5.Extra 其他的一些扩展内容

• • Field：表中的列名
• • Type：列的数据类型
• • Null：该列的值是否允许为Null
• • Key：该列的索引类型
• • Default：该列的默认值
• • Extra：扩展信息

4.删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tb1_name [, tb1_name...] 

1.[TEMPOARAY] 表示的是用户自己创建的临时表

2.库名.表名 相当于完全路径

5.进入图形化界面

黄色闪电状的是运行键

Schemas 是有哪些库

6.修改表

对表结构进行调整，如添加列、删除列、修改列名 / 数据类型 / 长度等，此时需执行表修改操作。

ALTER TABLE tbl_name [alter_option [, alter_option] ...];

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME [TO | AS] new_tbl_name
}

• tbl_name：要修改的表名

• ADD：向表中添加列

• MODIFY：修改表中现有的列

• DROP：删除表中现有的列

• RENAMECOLUMN：重命名表中现有的列

• RENAME[TO|AS]new_tbl_name：重命名当前的表

6.1向表中添加一列

alter table users ADD assets varchar(100) comment '头像地址' after  birthday;

comment '头像地址'（列注释）

after birthday（列位置指定）

varchar(100) 中的长度 100

6.2 修改某列的长度

alter table users MODIFY assets varchar(255);

6.3重命名某列

alter table users RENAME [COLUMN] assets TO avatar;

6.4删除某个字段

alter table users DROP avatar;

6.5 修改表名

alter table users RENAME TO employee;

7.随手小测试

题目如下：

• 有一个商店的数据，记录客户及购物情况，有以下三个表组成：
  • 商品goods(商品编号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider)
  • 客户customer(客户号customer_id，姓名name，住址address，邮箱email，性别sex，身份证card_id)
  • 购买purchase(购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums)

create database bit_mall character set utf8mb4 collate utf8_0900_ai_ci;

-- 选择数据库
use bit_mall;

-- 创建商品表
drop table if exists goods;
create table goods (
    goods_id bigint,
    goods_name varchar(50),
    unitprice decimal(10, 2),
    category varchar(20),
    provider varchar(50)
);

create table customer (
    customer_id bigint,
    name varchar(20),
    address varchar(255),
    email varchar(255),
    gender bool,
    card_id varchar(18)
);

create table purchase (
    order_id bigint,
    customer_id bigint,
    goods_id bigint,
    nums int
);