阶段三：MySQL8基本使用
阶段三：MySQL8基本使用
数据定义语言-库
1、MySQL的组成结构（理解）
注：我们平常说的MySQL，其实主要指的是MySQL数据库管理软件。
一个MySQL DBMS可以同时存放多个数据库，理论上一个项目就对应一个数据库。如博客项目blog数据库、商城项目shop数据库、微信项目wechat数据库。
表：一个数据库包含多张数据表，而数据表才是真正用于存放数据的位置。（类似于Excel表格）。
列：一个数据表包含多个字段，每个字段就是一个属性。比如，一个用户表，需要保存用户账号、密码等；
行：一个数据表除了字段以外，还有很多行，每一行都是一条完整的数据（记录）。
2、数据库的基本操作
编写SQL语句是一个比较细致工作，(初学者)不建议大家直接在终端中输入SQL语句，可以先把你要写的SQL语句写入一个记事本中，然后拷贝执行。
① 查看所有数据库
mysql> show databases; -- 显示 => show；数据库 => database；
mysql> show create database 数据库名称; -- 查看指定数据库的创建语句② 创建数据库
mysql> create database 数据库名称 [设置编码格式]; -- 创建 => create；数据库 => database；
-- 特别说明：
-- 不能创建相同名字的数据库！即，不能重复创建一个数据库
-- 数据库名由字母+数字+下划线组成，以字母开头，不能有中文以及特殊字符
-- 当一条SQL语句编写完毕后，一定要使用分号;进行结尾，否则系统认为这条语句还没有结束。
-- 方式一：创建db_itheima库
mysql> create database db_itheima;
-- 方式二：创建db1库并指定默认字符集
mysql> create database db_itheima default charset gbk;
-- 方式三：如果存在不报错(if not exists)
mysql> create database if not exists db_itheima default character set utf8; ③ 删除数据库
mysql> drop database 数据库名称; -- 删除 => drop 数据库 => database
mysql> drop database db_itheima; -- 比如，删除 db_itheima④ 选择数据库
从数据库列表中查找需要使用的数据库
mysql> use 数据库名称; -- use 使用
mysql> use db_itheima; -- 比如，选中 db_itheima查看正在使用的数据库（8.0以后版本需要基于select查询来获取当前数据库）
mysql> select database();3、关于编码格式（补充）
编码格式，常见的gbk（中国的编码格式）与utf8（国际通用编码格式）
latin1：这是早期的字符编码格式，支持256个字符，适用于基本的西方语言字符集。
gb2312/gbk：由于latin1的字符集无法表示汉字，中国开发了自己的编码格式gb2312，后来升级为gbk，以支持更多汉字和扩展字符集。
big5：中国台湾也开发了一套用于表示繁体中文的字符编码格式，称为big5，适用于繁体中文字符集。
utf8：为了支持多语言环境，国际标准化组织开发了utf8编码，能够表示几乎所有已知语言的字符。由于早期版本的utf8不完全支持某些特殊符号和表情符号，MySQL5.6版本以后推出了utf8mb4，用于支持更多字符（如Emoji表情），每个字符最多使用4个字节（理论值：256\*256\*256\*256）。
小结
查看数据库：SHOW DATABASES; 创建数据库：CREATE DATABASE 数据库名； 删除数据库：DROP DATABASE 数据库名； -- 慎重操作 使用数据库：USE 数据库名；select database(); 将来工作后，上面四大语句的使用频率，大概在10%。
数据定义语言-表
DDL要学4个关键字：create（创建）、drop（删除）、alter（修改）、show（查询）
特别注意：创建数据表必须有一个前提，首先要明确选择某一个数据库。
1、表的基本操作
☆ 查询表
mysql> use 数据库名称; -- use 先切换到具体的库
mysql> show tables; -- 还是 show 命令显示数据表的创建过程（编码格式、字段等信息）
mysql> desc/describe 数据表名称;
mysql> show create table 数据表名称;
☆ 创建表
mysql> create table 数据表名称(
字段1 字段类型 [字段约束],
字段2 字段类型 [字段约束],
...
);
常用字段类型
整数：int（-21亿 ~ 21亿）
浮点：decimal(M,N)，M代表整数长度 + 小数总长度，N代表小数点后保留几位小数，decimal(5,2) => 999.99
字符串类型：固定长度用char(M)，变化长度用varchar(M) => M代表最大字符数
varchar(10) -- VMWare
枚举类型：enum('男','女')，限定要么为男，要么为女案例：创建一个admin管理员表，拥有3个字段（编号、用户名称、用户密码）
mysql> create database db_itheima;
mysql> use db_itheima;
mysql> create table tb_admin(
id tinyint, -- 微整型，范围-128 ~ 127，无符号型，则表示0 ~ 255
username varchar(20), -- varchar代表变化长度的字段，理论值最大65535个字符
password char(32) -- char代表固定长度的字段
) engine=innodb default charset=utf8; use在MySQL中的含义代表选择，use 数据库名称相当于选择指定的数据库。而且use比较特殊，其选择结束后，其尾部可以不加分号；但是强烈建议所有的SQL语句都要加分号，养成一个好习惯。
案例：创建一个article文章表，拥有4个字段（编号、标题、作者、内容）
mysql> use db_itheima;
mysql> create table tb_article(
id int,
title varchar(50),
author varchar(20), -- varchar存储的最大长度，理论值65535个字符
content text -- 文本类型，varchar存储不了的字符串信息，建议用text
) engine=innodb default charset=utf8mb4; -- 相比于utf8，推荐utf8mb4补充
varchar类型，理论值最大65535个字符。但是实际上，有几个字符是用于存放内容的长度的，所以真正可以使用的不足65535个字节，另外varchar类型存储的字符长度还和编码格式有关。1个GBK格式的占用2个字节长度，1个UTF8格式的字符占用3个字节长度。GBK = 65532\~65533/2个字符，UTF8 = 65532\~65533/3个字。
utf8 支持 3 字节字符（如中文），不支持 Emoji；
utf8mb4（UTF-8 Unicode Transformation Format 4-byte）支持 4 字节字符（含 Emoji 表情、生僻字）；
☆ 删除表
mysql> drop table 数据表名称;☆ 修改表名称
rename table 旧名称 to 新名称;☆ 修改表字段（多写）
① 添加字段
mysql> alter table 数据表名称 add 新字段名称 字段类型 first；
mysql> alter table 数据表名称 add 新字段名称 字段类型 after 其他字段名称;
选项说明：
first：把新添加字段放在第一位
after 字段名称：把新添加字段放在指定字段的后面案例：在tb_article文章表中添加一个addtime字段，类型为date(年-月-日)
日期类型有：date（年-月-日）、time（小时:分钟:秒）、datetime（年-月-日 小时:分钟:秒）
mysql> alter table tb_article add addtime date after content;
mysql> desc tb_article;② 删除字段
mysql> alter table tb_article drop 字段名称;
mysql> desc tb_article;③ 修改字段名称、类型
修改字段名称与字段类型（也可以只修改名称）- change
mysql> alter table tb_admin change username name varchar(40);
mysql> desc tb_admin;
说明：
change 比较神奇，既可以修改字段名称，又可以修改字段类型！
change 源字段名称 修改后字段名称 修改后的字段类型（这个也可以不改，但是必须要写）仅修改字段的类型 - modify
mysql> alter table tb_admin modify name varchar(20);
mysql> desc tb_admin;
说明：
modify 字段名称 新字段类型（仅可以修改字段类型）课堂练习
情景：
在企业开发中，小明接到一个需求，要求参与研发一套新的OA系统。其中有一张管理员表，交给了他。
1、创建一张表 tb_admin（ id、name、sex）
2、应前端要求，添加字段address、phone_number；
3、后来发现，地址信息几乎不怎么用，要求把 address 字段删除；
4、后来组员提出疑问，性别用gender（音，巾德儿）更合适。建议把 sex 字段改成 gender；
延申：字段类型没有规定，大家按自己进行设计。int(11) 和 int 有什么区别？
总结
*-- 查看*
show databases;
show tables;
*-- 查看创建信息*
show create database db_itheima;
show create table db_admin;
*-- 查看表*
desc db_admin;
describe db_admin;
2、字段类型详解（重点）
整数int、浮点数decimal、字符串char/varchar/text、枚举类型enum、日期时间类型date、time、datetime
官方链接：https://dev.mysql.com/doc/refman/8.0/en/data-types.html
1 整数类型
| 分类 | 类型名称 | 说明 |
|---|---|---|
| tinyint | 很小的整数（1 Byte） | -128 \~ 127 |
| smallint | 小的整数（2 Bytes） | -32768 \~ 32767 |
| mediumint | 中等大小的整数（3 Bytes） | -8388608 \~ 8388607 |
| int(integer) | 普通大小的整数（4 Bytes） | -2147483648 \~ 2147483647 |
选择整数类型，往往存储范围！
age tinyint unsigned => 0 - 255，只需要记住tinyint和int即可！！！
举个例子：
create table tb_student(
id int,
name varchar(20),
age tinyint unsigned
) default charset=utf8;SQL优化手段：尽量选择满足这个字段范围区间的数据类型！！！
2 小数类型
小数类型，也分为浮点类型（精度失真情况）和定点类型（推荐使用）。
| 分类 | 类型名称 |
|---|---|
| float | 单精度浮点数 |
| double | 双精度浮点数 |
| decimal(m,d) | 定点数，decimal(10,2) |
decimal(10,2) ：10 代表总长度，2代表保留2位小数，因此，整数只剩8位。比如，decimal(5,2) 最大为 999.99
建议：① 精度要求不高（2-4位小数的情况，影响不大），可以使用float/double
② 如果精度要求比较高的情况，可以使用decimal
参考阅读：https://blog.csdn.net/weixin_45729934/article/details/121389109
案例：
create table tb_product(
id int,
name varchar(100),
price decimal(11,2)
) default charset=utf8;1美元 = 7.85人民币。1人民币 = 0.1067美元
3 日期类型
| 份 | 类型名称 |
|---|---|
| year | YYYY 1901\~2155 |
| time | HH:MM:SS -838:59:59\~838:59:59 |
| date | YYYY-MM-DD 1000-01-01\~9999-12-3 |
| datetime | YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00\~ 9999-12-31 23:59:59 |
| timestamp | YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC\~2038-01-19 03:14:07UTC |
选日期类型看格式，用的最多的是 => date、datetime
date：年-月-日
time：小时：分钟：秒数
datetime = date + time合并
案例：
create table tb_news(
id int,
title varchar(100),
addtime datetime
) default charset=utf8;4 字符串（文本）类型
| 类型名称 | 说明 |
|---|---|
| char(m) | m为0\~255之间的整数定长（固定长度），m代表字符长度，255代表不能超过255个字符 |
| varchar(m) | m为0\~65535之间的整数变长（变化长度），65535代表字节长度，和编码有关，utf8最多2万多个汉字 |
| text | |
| mediumtext | 允许长度0\~167772150字节 |
| longtext（扩展） | 允许长度0\~4294967295字节 |
特殊字符串：
枚举类型 enum('值1','值2','值3')，从多个值中选择其中的某一个！适用于订单状态等场景；
选择文本，主要长度，固定char、变化varchar、更长的就是text。
varchar(m)：理论值65535个字符，但是理论上底层最大能存储的字节数也是65535。但是如果是汉字，和编码格式有关，gbk模式下，1个汉字2个字节，理论上大概能3万左右个汉字。utf8模式下，1个汉字3个字节，理论值就是2万多个汉字。
5 二进制类型（了解）
| 类型名称 | 说明 | 适用场景 |
|---|---|---|
| BINARY(N) | 固定长度二进制（如 UUID、哈希值） | 存储加密密钥、定长标识符 |
| VARBINARY(N) | 可变长度二进制（如小文件） | 存储图片缩略图、API 响应数据 |
| BLOB系列（音，布洛勃） | 大文件（如图片、视频） | 权衡性能与便利性，优先存储路径 |
BLOB，是Binary Large Object的缩写，二进制大对象。
注意，大的二进制文件，一般都存储在文件系统当中，很少存放在MySQL当中。
小结
记住以下这些常用的，就可以
类型具体类型场景说明 整数tinyint一些小的范围 int4个字节 小数decimal(m, n)最常用的浮点表示 日期date表示日期（2022-10-20，常见于数据仓库） datetime具体到时间 timestamp时间戳，用的最多，记录的UTC时间 文本 (字符串)varchar最常用（占到80%） text大段的描述 enum枚举类型
UTC (Coordinated Universal Time，协调世界时)，以原子时秒长（纳秒级）为基准。
3、auto_increment（重点）
作用：自动增长，往往用于设置主键，可以让其值自动增长，默认从1开始，依次递增。
drop table users;
create table users(
id int auto_increment primary key,
name varchar(20),
age tinyint unsigned,
gender enum('male', 'female') default 'male'
) default charset=utf8mb4;
-- 设置 auto_increment 后，可以不输入 id 了
insert into users values (null, '张三', 23, 'male');
insert into users values (null, '李四', 24, 'male');
delete from users where id = 2;
insert into users values (null, '王五', 25, 'male');思考：
1、int auto_increment 范围多少，会不会有数值超出边界，存不进去的可能？
2、如果分布式数据库进行数据归并，会不会出现 id 碰撞？
3、根据 id 值，是不是可以推断业务的数据量，造成安全风险？
引申：
UUID（Universally Unique Identifier，通用唯一识别码）也是一种常见的主键生成策略，天然全局唯一。在分布式数据库中作为主键，被广泛使用。
数值边界举例——摩托车的里程表，当行驶到99999时，再开1公里，里程表将会显示00000，导致显示错误。
数据操作语言
1、DML包括哪些SQL语句
insert插入、update更新、delete删除
增删改查，除了查询是Query，其他三个都是操作（Manipulation）
2、数据的增删改（重点）
☆ 数据的增加操作
insert into 数据表名称([字段1,字段2,字段3...]) values (字段1的值,字段2的值,字段3的值...);特别注意：在SQL语句中，除了数字，其他类型的值，都需要使用引号引起来，否则插入时会报错。
第一步：准备一个数据表
use db_itheima;
create table tb_user(
id int,
username varchar(20),
age tinyint unsigned, # 无符号
gender enum('男','女','保密'),
address varchar(255)
) engine=innodb default charset=utf8mb4;
-- 这里，charset=utf8; 等价于character set utf8;unsigned 代表无符号型，只有0到正数。tinyint unsigned无符号型，范围0 \~ 255
enum 枚举类型，多选一。只能从给定的值中选择一个，或者输入 null
第二步：使用insert语句插入数据
insert into tb_user values (1,'刘备',34,'男','广州市天河区');
insert into tb_user(id,username,age) values (2,'关羽',33);
-- 简单引入一下查询命令
select * from tb_user;第三步：批量插入多条数据
insert into tb_user
values (3,'大乔',19,'女','上海市浦东新区'),
(4,'小乔',18,'女','上海市浦东新区'),
(5,'马超',26,'男','北京市昌平区');
-- 简单引入一下查询命令
select * from tb_user;☆ 数据的修改操作（重要）
update 数据表名称 set 字段1=更新后的值,字段2=更新后的值,... [where 更新条件];
where：更新条件，满足条件的才会被更新，如果不写where则默认修改所有记录！特别说明：如果在更新数据时，不指定更新条件，则其会把这个数据表的所有记录全部更新一遍。
案例：修改username='赵飞'这条记录，将其性别更新为男，家庭住址更新为广东省深圳市
select * from tb_user where username='赵飞';
update tb_user set gender='男',address='广东省深圳市' where username='赵飞';案例：今年是2025年，假设到了2026年，现在存储的学员年龄都差1岁，整体进行一次更新
mysql> update tb_user set age=age+1;☆ 数据的删除操作
mysql> delete from 数据表名称 [where 删除条件];案例：删除tb_user表中，id=1的用户信息
mysql> delete from tb_user where id=1;delete from与truncate清空数据表操作
mysql> delete from 数据表;
或
mysql> truncate 数据表;面试题：delete from与truncate区别在哪里？
- delete：删除数据记录
- 数据操作语言（DML）
- 删除大量记录速度慢，只删除数据，主键自增序列不清零，1-100 => 新插入 => 101
- 可以带条件删除
- truncate：删除所有数据记录
- 数据定义语言（DDL）
- 清理大量数据速度快，主键自增序列清零, 1-100 => 新插入 => 1
- 不能带条件删除
小结
DML：数据操作语言，主要涉及增加（INSERT）、修改（UPDATE）、删除（DELETE）
补充：软删除 vs 硬删除
-- 实际企业当中，使用的是 update 进行删除的
update tb_user set status = -1 where username='赵飞';
update tb_user set del_flag = 1 where username='赵飞';
select * from tb_user where username='赵飞' and status <> -1;
select * from tb_user where username='赵飞' and del_flag <> 1;3、字段约束详解
字段约束：在数据类型的基础上，给字段添加约束行为（如不能为空等等）
create table 数据表名称(
字段名称 字段类型 [可选，字段约束]
) default charset=utf8mb4;在MySQL中，字段约束一共有五种约束：①非空约束 ②默认值约束 ③唯一约束 ④主键约束 ⑤外键约束
非、默、唯一、主
☆ 非空约束
强调：非空，这个列不允许出现null空值
not null
案例：创建一个商品表goods，有id主键，name商品名称，price商品价格，name和price都必须为非空！
create table goods(
id int primary key,
name varchar(80) not null,
price decimal(11,2) not null
) default charset=utf8mb4;
-- 插入数据
insert into goods values (1, 'Python从入门到放弃', 99.99);
-- 插入数据（错误演示）
insert into goods values (2, 'Java从入门到放弃', null);
☆ 默认值约束
强调：默认值，插入数据时，如果没有指定对应的值，则其自动使用默认值做为最终结果
default 默认值
案例：创建一个用户表users，id主键、name用户名、age年龄、gender性别，要求gender默认值为male
drop table users;
create table users(
id int primary key,
name varchar(20),
age tinyint unsigned,
gender enum('male', 'female', 'unknown') default 'unknown' -- 性别
) default charset=utf8mb4;
desc users;
-- 插入数据
insert into users values (1, '张三', 18, 'male');
-- 插入数据（触发默认值）
insert into users(id, name, age) values (2, '李四', 18);
-- default
insert into users values (3, '王五', 18, default); -- default在这里将会被导入成默认值
-- 插入数据（不使用默认值）
insert into users values (4, '赵六', 18, 'female');
insert into users values (5, '小明', 18, null);gender，性别（音，今德儿）
☆ 唯一约束
强调：唯一，可以为空；一个表中可以同时拥有多个唯一约束
unique （音，优尼克。意，唯一的）
案例：创建一个用户表，id主键，name，age，code（身份证号码，要求唯一）
create table users(
id int primary key,
name varchar(20),
age tinyint unsigned,
code char(18) unique
) default charset=utf8mb4;
-- 插入数据
insert into users values (1, '张三', 18, '123456789012345678');
-- 插入重复数据（错误演示）
insert into users values (2, '李四', 18, '123456789012345678');
-- 插入数据，但是唯一列为null
insert into users values (2, '王五', 18, null);☆ 主键约束（重点）
强调：非空、唯一、一个表中最多有且仅有1个主键，在MySQL中，往往id字段都是主键（primary key）
article文章表（id主键，title标题，author作者，addtime发布时间）
use db_itheima;
create table article(
id int primary key,
title varchar(80),
author varchar(20),
addtime date
) default charset=utf8mb4;
或
create table article(
id int,
title varchar(80),
author varchar(20),
addtime date,
primary key(id)
) default charset=utf8mb4;案例：
use db_itheima;
create table article(
id int primary key,
title varchar(80),
author varchar(20),
addtime date
) default charset=utf8mb4;
-- 查看数据表
desc article;
-- 向表中插入数据
insert into article values (1, 'MySQL数据库运维', '张三', '2025-02-10');
-- 主键强制为null（错误演示）
insert into article values (null, 'Python运维开发', '李四', '2025-02-10');
-- 主键强制重复（错误演示）
insert into article values (1, 'Python运维开发', '李四', '2025-02-10');
-- 因此，主键不能重复，不能为空主键约束 与 唯一约束区别？
和主键约束相同点：都是唯一的
和主键约束不同点：主键非空、有且仅有1个；唯一可以为null，在一个表中也可以同时拥有多个
☆ 外键约束（了解）
强调：多表，级联操作
外键约束前提：
① 多表
② 数据表引擎必须为InnoDB引擎
小结
MySQL语句中，一共有5种：（非空约束、默认值约束、唯一约束、主键约束、外键约束）