MySQL介绍

一、数据库的介绍，什么是数据库

• 数据库其实就是一块基于网络通信的应用程序
• 每个人都有开发一块数据库的能力

【1】关系型数据库

• MySQL
• Oracle
• db2
• access
• sql server

这些数据库都采用关系模型来组织数据，并且支持SQL查询语言。

【2】非关系型数据库

• Redis
• MongoDB
• Memcached

这些数据库不采用关系模型来组织数据，而是采用了其他的模型，例如键值对模型、文档模型、图形模型等。

【3】关系型（存储数据）

• 彼此之间有关联
• 存储数据的表型形式通常以表格形式存储
• 每个字段限制每个字段下的存储数据的格式例如字符串、数字、日期等。
• 同时，关系型数据库还支持各种数据操作，例如插入、删除、更新和查询等。

【4】非关系型（缓存数据）

• 存储数据通常是K:V形式存储数据
• 非关系型数据库通常用于存储临时性的、高速访问的数据，这些数据通常以键值对的形式存储，其中“键”表示数据的唯一标识，“值”表示实际的数据内容。
• 由于非关系型数据库通常不提供事务支持和复杂的查询功能，因此不适合存储长期保存的历史数据。

【5】数据库应用场景

【1】需求

• 假设现在你已经是某大型互联网公司的高级程序员，让你写一个火车票购票系统，来hold住十一期间全国的购票需求，你怎么写？
• 在同一时段抢票的人数如果太多，那么你的程序不可能运行在一台机器上，应该是多台机器一起分担用户的购票请求。

【2】实现需求

• 那么问题就来了，票务信息的数据存在哪里？存在文件里么？
• 如果存储在文件里，那么存储在哪一台机器上呢？是每台机器上都存储一份么？
  • 首先，如果其中一台机器上卖出的票另外两台机器是感知不到的，
  • 其次，如果我们将数据和程序放在同一个机器上，如果程序和数据有一个出了问题都会导致整个服务不可用。
  • 最后，操作或修改文件中的内容对python代码来说是一件很麻烦的事。
• 基于上面这些问题，单纯的将数据存储在和程序同一台机器上的文件中是非常不明智的。
• 根据上面的例子，我们可以知道，将文件和程序存在一台机器上是很不合理的，同时，操作文件是一件很麻烦的事，所以我们可以使用数据库来存储数据。

二、安装、配置、启动

【1】下载

• 打开官网网址，https://www.mysql.com
• 选择下载

• 之前安装了一个8.0版本的这次安装一个5.7版本的，在实现一下多版本共存

• 下载的解压包解压到本地目录里
• mysql解压后其实就安装好了，开始进行配置
• mysql5.7是我自己起的名字，避免跟另一个重复
• 需要手动创建data文件夹

【2】配置

• 先配置环境变量

• 创建配置文件my.ini默认是没有的，我们需要手动创建。

• 配置文件内容如下

  • 注意：basedir和datadir的路径要根据自己的路径如实填写

  [mysqld]
  ; 这一条是跳过密码验证登录，后续会用到先不添加
  skip-grant-tables=1
  ; 设置3306端口
  port=3307
  ; 设置mysql的安装目录
  basedir="E:\Mysql5.7"
  ; 设置mysql数据库的数据的存放目录，就是前面手动创建的data目录
  datadir="E:\Mysql5.7\data"
  ; 允许最大连接数
  max_connections=200
  ; 允许连接失败的次数。
  max_connect_errors=10
  ; 服务端使用的字符集默认为utf8mb4
  character-set-server=utf8mb4
  ; 创建新表时将使用的默认存储引擎
  default-storage-engine=INNODB
  ; 默认使用“mysql_native_password”插件认证, mysql_native_password
  default_authentication_plugin=mysql_native_password
  [mysql]
  ; 设置mysql网络通信的默认字符集
  default-character-set=utf8mb4
  [client]
  ; 设置mysql客户端连接服务端时默认使用的端口
  port=3307
  ; 设置mysql客户端的默认字符集
  default-character-set=utf8mb4
  

  

• 初始化数据库打开cmd窗口

mysqld --initialize --console
多版本共存的话需要添加绝对路径
E:\Mysql5.7\bin\mysqld --initialize --console
初始化完成后最后一行会显示随机密码需要保存下，后续会用

• 注册系统服务

• 把mysql注册到操作系统作为系统服务，保证将来电脑重启了就可以开机自启了

• 以管理员身份运行cmd窗口

mysqld --install
E:\Mysql5.7\bin\mysqld --install
显示注册成功后mysql才会添加成功，会多出一个mysql服务

• 注销服务，用于卸载mysql的，别乱用。

mysqld --remove

【3】启动

• 直接在上述服务窗口右键启动

• 或者通过命令启动/停止

net start MySQL
net stop MySQL

【4】启动后的配置

• 登录是通过以下命令按回车键，接着输入上面初始化的登陆密码，就可以登陆MySQL交互终端了。
• 注意：mysql与linux一样，在安装成功以后默认就存在了一个上帝一般的用户，叫root。由于多版本共存将上述mysql配置文件的端口改成了3307
• 退出

mysql -uroot -p:IUFjXkgv7l=
mysql -h 127.0.0.1 -P 3307 -uroot -p
exit

• 修改用户名密码
• 前提是能正常进入到客户端内部
• 刷新权限数据

alter user 'root'@'localhost' identified by '123456';
flush privileges;

• 参数介绍
  • 'root' ：就是要修改密码的用户名
  • 'localhost' ：表示允许用户在什么地址下可以使用密码登陆到数据库服务器，localhost表示本地登陆
  • '123456' ：就是新的密码了，注意，不要设置空密码！以后公司里面的密码一定要非常难记的才最好。

• 跳过授权表重置密码就是上述添加到配置文件里的那条(非临时跳过)

skip-grant-tables=1

• 如果想修改密码，需要先停止服务，添加配置文件启动服务后进行修改
• 刷新权限数据

alter user 'root'@'localhost' identified by '1314521';
flush privileges;

• 还有一种方法

default-character-set=utf8

三、SQL语句

【1】socket通信

• 任何基于网络通信的应用程序底层用的都是scoket
  • 服务端
    • 基于socket通信
    • 收发消息
  • 客户端
    • 基于socket通信
    • 收发消息

【2】SQL语句的由来

• 为了使 MySQL 不仅能支持 MySQL 自己的客户端应用程序，还能支持其他编程语言
• MySQL 采用了 SQL 作为统一的语言来进行数据操作。
• 这样就可以让服务器端兼容所有语言，因为所有的客户端应用程序都可以通过执行相同的 SQL 语句来进行数据操作。

【3】库（Database）

• 库是指在数据库管理系统中用于存储和组织数据的容器。
• 它可以视为一个文件夹，用于存放相关的数据表。 数据库中可以包含多个库，每个库可以包含多个表。

【4】表（Table）

• 表是数据库中的一个基本组成单位，用于存储和展示数据。
• 表由行（记录）和列（字段）组成，每一行表示一个记录，每一列表示一个字段。
• 表可以看作是一个二维数据结构，类似于电子表格。

【5】记录（Record）

• 记录也称作行，是表中的一个数据项或实体。
• 每一行都代表了一个完整的数据记录，其中包含了各个字段的具体数值或信息。
• 例如，在一个学生信息表中，每一行代表一个学生的具体信息。

【6】表头（Header）

• 表头是表中的第一行，用于描述每个列字段的含义或名称。
• 表头通常包含了列的标签或标题，通过表头可以了解每个列字段所代表的意义，方便数据的理解和查询。

【7】表单（Form）

• 表单是一种用来收集和展示数据的界面形式，常见于数据输入和显示的场景中。
• 表单通常包含了各种输入字段（例如文本框、下拉列表等）和相关的操作按钮，用户可以通过表单进行数据的录入、编辑和提交等操作。
• 表单的数据可以被存储到相应的表中，用于后续的数据处理和分析。

【8】小结

• 库：
  • 相当于我们的文件夹
• 表：
  • 相当于我们的文件
• 记录：
  • 相当于我们一行行的数据
• 表头：
  • 表格的第一行字段
• 表单：
  • 表头对应的每一条数据

四、数据库常用命令

安装上数据库之后，就需要开始学习指令了，通过指令让MySQL去做出一些操作。

【1】数据库的操作（增删改查）

（1）查询数据库

show databases;   -- 查看所有数据库
show databases like '%db%'; --模糊查询，查看名字内包含db的数据库
show create database db2; --查看数据库的建库sql语句

（2）创建库

• 注意：mysql中的编码字符集中utf-8，要换成utf8mb4。SQL语句中的中括号部分表示可选。

create database db1;
create database db2 charset='utf8';

（3）修改库

alter database db1 charset='utf8';

（4）删除库

drop database db2;

【2】数据表的操作（增删改查）

（1）查看当前所在库

select database();  --  查看当前使用的数据库

（2）进入库

use database db1;

注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换

（3）创建表

create table 表名(
    字段名	数据类型，
	字段名	数据类型，
    字段名	数据类型
) default charset=utf8;

• 注意：
  • 上面SQL语句中，小括号中的定义字段语句后面必须以英文逗号结尾，而最后一个字段的定义语句不能有英文逗号出现，否则报错。
  • 在同一张数据表中，字段名是不能相同，否则报错！
  • 创建数据表的SQL语句中，存储空间和字段约束是选填的，而字段名和数据类型则是必须填写的。

CREATE TABLE tb1 (
    id INT,
    username VARCHAR(16),
    age INT
) DEFAULT CHARSET=utf8;

create table tb2 (
    id int,
    username varchar(16),
	age int
) default charset=utf8;

• 主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。

create table tb3(
    -- auto_increment表示当前字段值在每次新增数据时自动+1作为值保存
    -- primary key，mysql中叫主键，表示用于区分一个数据表中不同行的数据的唯一性，同时还具备加快查询速度的作用
	id int not null auto_increment primary key,-- 不允许为空 & 主键 & 自增
    name varchar(16) not null, 		-- 不允许为空
    age int, 
    hobby varchar(32),
    address varchar(100) null     	-- 允许为空
) default charset=utf8;

（4）查看表

show tables;    -- 查看所有的表
desc tb3; describe tb3;      -- 查看表的详细信息
show create table tb3; -- 查看指定的建表语句

（5）修改表

				-- 用于修改表结构
alter table tb1 modify username varchar(32);

（6）删除表

drop table tb1;

（7）插入数据

insert into tb1 values(1,'zhangsan',18);
mysql> select * from tb1;     -- 表里数据查看
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
+------+----------+------+
1 row in set (0.00 sec)

（8）重置表信息

• 保留数据表结构，但是把数据表存储的数据以及数据表的状态回滚，
• 相当于删除原表，并新建一张一模一样的空数据表。

TRUNCATE table 表名;

（9）删除数据

delete from tb1 where username='zhangsan';

【3】数据行的操作（增删改查）

• 新增数据

  insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);
  

  insert into tb1(name,password) values('武沛齐','123123');
  insert into tb1(name,password) values('武沛齐','123123'),('alex','123');
  
  insert into tb1 values('武沛齐','123123'),('alex','123'); -- 如果表中只有2列
  

• 删除数据

  delete from 表名;
  delete from 表名 where 条件;
  

  delete from tb1;
  delete from tb1 where name="wupeiqi";
  delete from tb1 where name="wupeiqi" and password="123";
  delete from tb1 where id>9;
  

• 修改数据

  update 表名 set 列名=值;
  update 表名 set 列名=值 where 条件;
  

  update tb1 set name="wupeiqi";
  update tb1 set name="wupeiqi" where id=1;
  
  update tb1 set age=age+1;  -- 整型
  update tb1 set age=age+1 where id=2;
  
  update L3 set name=concat(name,"db");
  update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数，可以拼接字符串
  

• 查询数据

  select * from 表名;
  select 列名,列名,列名 from 表名;
  select 列名,列名 as 别名,列名 from 表名;
  select * from 表名 where 条件;
  

  select * from tb1;
  select id,name,age from tb1;
  select id,name as N,age, from tb1;
  select id,name as N,age, 111 from tb1;
  
  select * from tb1 where id = 1;
  select * from tb1 where id > 1;
  select * from tb1 where id != 1;
  select * from tb1 where name="wupeiqi" and password="123";
  

演示：

• 创建学生表和课程表

create table student(
    id int auto_increment,   -- 字段名：id，数据类型：int整型，auto_increment整数自动增长+1
    name varchar(10),   -- 字段名：name， 数据类型：varchar字符串（长度限制最多10个字符）
    sex int default 1,  -- 字段名：sex，数据类型：int整型，默认值（default）：1 相当于True 
    classes int,         -- 字段名：classes， 数据类型：int整型，
    age int,             -- 字段名：age，数据类型：int整数,
    description text,  -- 字段名：description，数据类型：text文本
    primary key (id)  -- 设置主键(id) 每个表必须都有主键，用以区分不同行的数据
);

• 假设现在我们有一个课程表(courses)，
• 里面需要保存课程编号（id），课程名（cource），授课老师（lecturer），教室（address）。

create table courses (
  id int auto_increment primary key comment "课程编号",
  course varchar(50) comment "课程名称",
  lecturer int comment "讲师编号",
  address int comment "教室编号"
);

• 批量插入数据

CREATE TABLE emp(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20),
    gender   ENUM("male","female","other"),
    age      TINYINT,
    dep      VARCHAR(20),
    city     VARCHAR(20),
   salary    DOUBLE(7,2)
)character set=utf8;

insert into user(name, age, selary) values('lisi', 18, 100);
insert into user(name, age, selary) values('lisi', 18, 100), ('wan', 18, 100), ('wu', 18, 100);
INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
                ("yuan","male",24,"教学部","河北省",8000),
                ("eric","male",34,"销售部","山东省",8000),
                ("rain","male",28,"销售部","山东省",10000),
                ("alvin","female",22,"教学部","北京",9000),
                ("George", "male",24,"教学部","河北省",6000),
                ("danae", "male",32,"运营部","北京",12000),
                ("Sera", "male",38,"运营部","河北省",7000),
                ("Echo", "male",19,"运营部","河北省",9000),
                ("Abel", "female",24,"销售部","北京",9000);

create table student(
	id int(4) not null auto_increment primary key, 
    name varchar(16),
    class varchar(32),
    age int(8),
    sex enum('boy','girl','trans person'),
    hobby set('read books','listen music','play games'),
    savemoney float(10,3)
);


create table courses(
	id int(4) not null auto_increment primary key comment "课程",
    '课程名称'cource varchar(10) comment "课程名称",
    lecturer int(10) comment "讲师",
    address int(8) comment "教师编号"  
);

insert into courses(cource,lecturer,address) values 
('Axure原型策划','1','301'),
('Javascript','2','305'),    
('AJAX','3','302'),
('Python','4','304'), 
('API接口','5','309'),
('SEO','6','308'), 
('Vue','7','306'),
('Css','8','303'), 
('Django基础','9','307');

五、数据类型

创建表的完整语法

create table 表名(
	字段名1 类型(宽度) 约束条件,
    字段名2 类型(宽度) 约束条件,
    字段名3 类型(宽度) 约束条件,
);

• 同一张表中字段名不能重复
• 宽度和约束条件是可选的（可写可不写）
• 约束条件可以写多个

【一】什么是宽度

【1】默认宽度

• 一般情况下指的是对存储数据的限制
  • 默认宽度为 1（只能存一个字符）
• 创建表

mysql> create table t7(name char);
Query OK, 0 rows affected (0.03 sec)

mysql> desc t7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

• 默认宽度为 1（只能存一个字符）

【2】宽度

mysql> insert t7 values('zhang');
ERROR 1406 (22001): Data too long for column 'name' at row 1

• 5.6 版本默认没有开启严格模式，规定只能存一个字符，给多了字符就会自动截取一个字符
• 5.7 版本及以上开启了严格模式，那么规定存几个就只能存几个，不能超出，一旦超出就会报上面的错误

【二】null 和 not null

【1】not null

mysql> create table t8(id int,name char not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(1) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

insert t8 values(1,'d');
mysql> desc t8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(1) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

【2】null

• 插入数据

insert t7 values(null);

# Query OK, 1 row affected (0.11 sec)

• 查看数据

desc t7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

• 默认允许插入 空
• 宽度是用来限制数据的存储
• 约束条件是在宽度的基础上增加的额外的约束

【三】严格模式

【1】如何查看严格模式

show variables like "%mode";
mysql> show variables like "%mode";
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value
                                               |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb
                                               |
| gtid_mode                | OFF
                                               |
| innodb_autoinc_lock_mode | 1
                                               |
| innodb_strict_mode       | ON
                                               |
| offline_mode             | OFF
                                               |
| pseudo_slave_mode        | OFF
                                               |
| rbr_exec_mode            | STRICT
                                               |
| slave_exec_mode          | STRICT
                                               |
| sql_mode                 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.01 sec)

• sql_mode(严格模式)

【2】模糊匹配/查询like "%mode"

• 关键字 ：
  • like "%mode"匹配任意多个字符
  • like "_mode"只能匹配单个字符

【3】修改严格模式

• 只在当前窗口有效

set session;

• 全局有效

set global

• 语法(严格模式)

set global sql_mode = 'STRICT_TRANS_TABLES'

• 修改完成后，重启服务端，即可生效

【4】严格模式到底开启还是不开启

• 5.7 之后的版本默认都是开启严格模式的
• 尽量减轻数据库的负担，不给数据库增加额外的压力

【四】数据类型

【1】浮点型

• FLOAT DOUBLE DECIMAL

数据类型	字节数	取值范围
FLOAT	4	-2^1282 ^128，即-3.40E+38+3.40E+38
DOUBLE	8	-2^1024~ 2^1024，即-1.79E+308~1.79E+308
DECIMAL	设置位数和精度。	65 ~ 30

float(255,30)
# 总共255位 ， 小数部分占 30 位

double(255,30)
# 总共255位 ， 小数部分占 30 位

decimal(65,30)
# 总共65位 ， 小数部分占 30 位

• 创建表

mysql> create table t15(id float(255,30));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t16(id double(255,30));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t17(id decimal(65,30));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t15;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | float(255,30) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc t16;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | double(255,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc t17;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | decimal(65,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

• 插入数据

mysql> insert into t15 values(1.1111111111111111111111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t16 values(1.1111111111111111111111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t17 values(1.1111111111111111111111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t15;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t16;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t17;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111111111100000000 |
+----------------------------------+
1 row in set (0.00 sec)

可以很明显的发现每一个类型的精确度有明显的区别

float < double < decimal

虽然每个类型的精确度不一，但是可以根据实际情况选择对应的数据类型

【2】字符类型

• CHAR

  • 定长

  char(4)
  #（超过四个字符直接报错，不够,四个字符空格补全）
  

• VARCHAR

  • 变长

  varchar(4)
  #（超过四个字符直接报错，不够,有几个接存几个）
  

• 创建表

mysql> create table t18(name char(4));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t19(name varchar(4));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t18
    -> ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc t19;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

• 插入数据

mysql> insert into t18 values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t19 values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t18 values('aaaaaa');
ERROR 1406 (22001): Data too long for column 'name' at row 1

• char_length

mysql> select char_length(name) from t18;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select char_length(name) from t19;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

char 与 varchar 的比较

（1）char

• 缺点：浪费空间
• 优点：存储简单
  • 直接按照固定的字符存储数据即可

（2）varchar

• 优点：节省空间
• 缺点：存储麻烦
  • 存的时候，在真正数据的前面加报头（表示数据真正大小）
  • 取的时候，需要先读取报头，才能读取真实的数据

以前用 char 现在 varchar 使用较多

【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:00/2037年）
  

  对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储，查询时，将其又转化为客户端当前时区进行返回。
  
  对于DATETIME，不做任何改变，原样输入和输出。
  

  mysql> create table L5(
      -> id int not null primary key auto_increment,
      -> dt datetime,
      -> tt timestamp
      -> )default charset=utf8;
  Query OK, 0 rows affected (0.03 sec)
  
  mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");
  
  mysql> select * from L5;
  +----+---------------------+---------------------+
  | id | dt                  | tt                  |
  +----+---------------------+---------------------+
  |  1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 |
  +----+---------------------+---------------------+
  1 row in set (0.00 sec)
  
  mysql> show variables like '%time_zone%';
  +------------------+--------+
  | Variable_name    | Value  |
  +------------------+--------+
  | system_time_zone | CST    | 
  | time_zone        | SYSTEM |
  +------------------+--------+
  2 rows in set (0.00 sec)
  -- “CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00
  
  mysql> set time_zone='+0:00';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show variables like '%time_zone%';
  +------------------+--------+
  | Variable_name    | Value  |
  +------------------+--------+
  | system_time_zone | CST    |
  | time_zone        | +00:00 |
  +------------------+--------+
  2 rows in set (0.01 sec)
  
  mysql> select * from L5;
  +----+---------------------+---------------------+
  | id | dt                  | tt                  |
  +----+---------------------+---------------------+
  |  1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
  +----+---------------------+---------------------+
  1 row in set (0.00 sec)
  

  create table student(
  	id int,
      name varchar(16),
      born_year year,
      birth date,
      study_time time,
      reg_time datetime
  );
  
  # Query OK, 0 rows affected (0.45 sec)
  insert into student values(
  		 1,
      'dream',
      '2001',
      '2001-1-31',
      '11:11:11',
      '2023-6-30 11:11:11'
  );
  
  # Query OK, 1 row affected (0.09 sec)
  desc student;
  +------------+-------------+------+-----+---------+-------+
  | Field      | Type        | Null | Key | Default | Extra |
  +------------+-------------+------+-----+---------+-------+
  | id         | int(11)     | YES  |     | NULL    |       |
  | name       | varchar(16) | YES  |     | NULL    |       |
  | born_year  | year(4)     | YES  |     | NULL    |       |
  | birth      | date        | YES  |     | NULL    |       |
  | study_time | time        | YES  |     | NULL    |       |
  | reg_time   | datetime    | YES  |     | NULL    |       |
  +------------+-------------+------+-----+---------+-------+
  6 rows in set (0.00 sec)
  select * from student; 
  +------+-------+-----------+------------+------------+---------------------+
  | id   | name  | born_year | birth      | study_time | reg_time            |
  +------+-------+-----------+------------+------------+---------------------+
  |    1 | dream |      2001 | 2001-01-31 | 11:11:11   | 2023-06-30 11:11:11 |
  +------+-------+-----------+------------+------------+---------------------+
  1 row in set (0.00 sec)
  

【4】枚举类型与集合类型

（1）枚举(enum)：多选一

• 枚举类型存储数据只能从候选项中选取一个才行

• 创建表

mysql> create table user(
    ->  id int,
    ->     name char(16),
    ->     gender enum('male','female','others')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql> desc user;
+--------+--------------------------------+------+-----+---------+-------+
| Field  | Type                           | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id     | int(11)                        | YES  |     | NULL    |       |
| name   | char(16)                       | YES  |     | NULL    |       |
| gender | enum('male','female','others') | YES  |     | NULL    |       |
+--------+--------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into user values(1,'dream','male');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    1 | dream | male   |
+------+-------+--------+
1 row in set (0.00 sec)

mysql> insert into user values(2,'chimeng','x');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

枚举类型存储数据只能从候选项中选取一个才行

（2）集合(set)：多选多

• 创建表

mysql> create table teacher(
    ->  id int,
    ->     name varchar(16),
    ->     gender enum('male','female','others'),
    ->     hobby set('read books','listen music','play games')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc teacher
    -> ;
+--------+-----------------------------------------------+------+-----+---------+-------+
| Field  | Type                                          | Null | Key | Default | Extra |
+--------+-----------------------------------------------+------+-----+---------+-------+
| id     | int(11)                                       | YES  |     | NULL    |       |
| name   | varchar(16)                                   | YES  |     | NULL    |       |
| gender | enum('male','female','others')                | YES  |     | NULL    |       |
| hobby  | set('read books','listen music','play games') | YES  |     | NULL    |       |
+--------+-----------------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into teacher values(
    ->  1,
    ->     'dream',
    ->     'male',
    ->     'read books'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+------+-------+--------+------------+
| id   | name  | gender | hobby      |
+------+-------+--------+------------+
|    1 | dream | male   | read books |
+------+-------+--------+------------+
1 row in set (0.00 sec)

mysql> insert into teacher values(
    ->  2,
    ->     'chimeng',
    ->     'female',
    ->     'read books,listen music'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> desc teacher;
+--------+-----------------------------------------------+------+-----+---------+-------+
| Field  | Type                                          | Null | Key | Default | Extra |
+--------+-----------------------------------------------+------+-----+---------+-------+
| id     | int(11)                                       | YES  |     | NULL    |       |
| name   | varchar(16)                                   | YES  |     | NULL    |       |
| gender | enum('male','female','others')                | YES  |     | NULL    |       |
| hobby  | set('read books','listen music','play games') | YES  |     | NULL    |       |
+--------+-----------------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into teacher values(
    ->  3,
    ->     'bud',
    ->     'others'
    -> );
ERROR 1136 (21S01): Column count doesn't match value count at row 1

集合类型

可以多选

不允许不选，错选

【小结】

【1】字段类型

（1）数值类型

• INT：整数类型，通常用于存储整数值。
• BIGINT：大整数类型，可以存储更大范围的整数值。
• DECIMAL：高精度浮点数类型，用于存储具有精确小数位的数值。

（2）字符串类型

• CHAR：定长字符串类型，存储固定长度的字符。
• VARCHAR：变长字符串类型，存储可变长度的字符。
• TEXT：用于存储较长的文本内容。

（3）日期和时间类型

• DATE：仅存储日期。
• TIME：仅存储时间。
• DATETIME：存储日期和时间。
• TIMESTAMP：存储日期和时间，并自动记录修改时间戳。

（4）枚举和集合类型

• ENUM：用于存储预定义的枚举值。
• SET：用于存储多个预定义值的集合。

【2】严格模式

• 严格模式（Strict Mode）是MySQL数据库的一种运行模式，它对于数据的完整性和准确性有更严格的要求。
• 使用严格模式可以帮助开发人员在数据操作中发现和修复潜在的问题，提高数据的一致性和可靠性。

（1）在严格模式下，MySQL会进行以下检查

1. 严格的数据类型检查：MySQL会根据表定义的字段类型和长度进行严格的数据验证，不允许非法的数据插入。比如，如果一个字段定义为整数类型，但插入了一个字符串，则会产生错误。
2. 禁止隐式转换：MySQL不会自动将一种数据类型隐式转换为另一种数据类型，从而避免因为隐式转换引起的数据不准确或截断的问题。
3. 非空字段检查：MySQL会检查非空字段是否为空，如果为空则会报错。
4. 严格模式下的零值插入：在严格模式下，MySQL对于插入零值（如或''）会更加严格，例如不允许将一个非零值插入到定义为无符号整数的字段中。

（2）启用严格模式可以通过以下方式之一实现

1. 在连接数据库时指定参数：可以在连接字符串中加上STRICT_TRANS_TABLES或STRICT_ALL_TABLES参数来开启严格模式。
2. 修改配置文件：可以在MySQL的配置文件（通常是my.cnf或my.ini）中设置sql_mode参数，添加或修改相关模式来启用或禁用严格模式。

需要注意的是，严格模式可能导致一些旧有的应用程序出现问题，因为它要求更加严格的数据验证和操作。

因此，在启用严格模式之前，应该进行充分的测试和评估，确保应用程序能够适应这种模式下的变化。

【3】约束条件

（1）not null

• not null约束条件用于指定某个字段不能为空。
• 这意味着在插入或更新数据时，该字段必须包含有效的值，而不能为NULL。
• 通过添加not null约束，可以防止插入或更新操作中出现空值，确保数据的完整性。

（2）zerofill

• zerofill约束条件用于指定某个字段的数据以零填充。

• 当使用

  zerofill
  

  约束时，如果字段被赋予较短的值，MySQL将使用来填充该字段的剩余空间。

  • 例如，如果一个字段定义为INT(5) ZEROFILL
  • 并且赋值为123
  • 则存储在数据库中的值将是00123。
  • 这样做可以确保字段的固定长度，方便进行比较和处理。

（3）unsigned

• unsigned约束条件用于指定某个字段只能包含非负数（正整数或零）。
• 当将字段定义为unsigned时，它会限制该字段的取值范围为大于等于零的整数。
• 这对于表示计数器、年龄、数量等非负数值的字段非常有用。

UTF-8

六、存储引擎

【一】什么是存储引擎

• 日常生活中文件格式有很多，并且针对不同的文件格式会有对应不同的存储方式和处理机制
• 针对不同的数据应该有对应的不同的处理机制

存储引擎就是不同的处理机制

【二】MySQL主要的存储引擎

【1】Innodb

• 是MySQL5.5版本之后的默认存储引擎
• 支持事务/行锁/外键

【2】myisam

• 是MySQL5.5版本之前的默认存储引擎
• 速度比innodb快，但是数据安全较弱

【3】memory

• 内存引擎
• 数据全部存放在内存中
• 存储速度快，但是断电数据丢失

【4】blackhole

• 黑洞引擎
• 无论存什么都会立刻消失

【5】查看所有的数据库存储引擎

show engines;

【三】不同的存储引擎存储数据的异同点

【1】建表

create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;

【2】文件说明

（1）Innodb

• t1.frm
  • 表结构
• t1.ibd
  • 表结构

Innodb：默认的引擎

（2）myisam

• t2.frm
  • 表结构
• t2.MYD
  • 表数据
• t2.MYI
  • 索引（index），类似于书的目录，基于目录查找数据的速度会很快

myisam:数据具有索引，读取数据快

（3）blackhole

• t3.frm
  • 表结构

blackhole:黑洞引擎，存什么数据都会消失

（4）memory

• t4.frm
  • 表结构

memory:数据缓存到内存

七、约束条件

【一】什么是约束条件

• 约束条件：限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！
• 约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件

【二】概览

• 非空约束（not null）
• 唯一性约束（unique）
• 组合使用 not null 和 unique
• 主键约束PK（primary key）
• 外键约束FK（foreign key）
• 级联更新与级联删除

ps:创建表结构时，约束条件直接跟在字段后面的为列级约束，若约束条件是用括号时为表级约束。

使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复。

【三】非空约束（not null）

【1】作用

not null约束的字段不能为 NULL 值，必须赋具体数据；

• 创建t_user 表（id，name,email），name不为空

create table t_user(
  id int(10) ,
  name varchar(32) not null,
  email varchar(128)
);

【四】唯一性约束（unique）

【1】作用

• unique约束的字段具有唯一性，不可重复，但是可以为空（null）。
• 修改之前的t_user表结构，把email设置为唯一性
• 确保 email 列的值在整个表中是唯一的，即不允许有重复的邮箱地址。这通常用于确保表中的某一列不包含相同的值，以满足唯一性约束。

alter table t_user modify email varchar(128) unique;
mysql> desc t_user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(32)  | NO   |     | NULL    |       |
| email | varchar(128) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

【五】组合使用

【1】not null 和 unique 单独使用(表级约束）

• 使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复，但是名字和邮箱字段可以单独重复。
• 可以给表级约束起名字，这样可以便于操作这个约束（如，删除，修改等）

【2】not null 和unique同时使用(列级约束)

• 被 not null 和 unique 约束的字段，该字段即不能为 NULL 也不能重复；
• 创建表

create table t_user(
  id int(10) ,
  name varchar(32) not null unique
);

• 查看表数据结构

desc t_user;
mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | YES  |     | NULL    |       |
| name  | varchar(32) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 插入数据

mysql> insert into t_user(id) value(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value	-- 由于name字段不能为空，所以会报错

mysql> insert into t_user(id,name) value(1,'zhang');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name) value(2,'zhang');
ERROR 1062 (23000): Duplicate entry 'zhang' for key 'name'
-- 由于name字段唯一，所以重复数据会报错

【六】如何查看当前表的约束条件

• 给约束条件添加名字

show databases;
use information_schema;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_FT_CONFIG                      |
| INNODB_SYS_VIRTUAL                    |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCK_WAITS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)

• table_constraints 该表专门存储约束信息

mysql> desc table_constraints;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

• 查看某张表存在哪些约束条件？

select constraint_name from table_constraints where table_name='表名';

【七】主键约束PK（primary key）

【1】主键涉及到的术语

• 主键约束
• 主键字段
• 主键值

【2】主键约束、主键字段、主键值三者之间关系

• 表中某个字段添加主键约束之后，该字段被称为主键字段
• 主键字段中出现的每一个数据都被称为主键值；

ps：主键值必须不能为空值，具有唯一性，会自动添加索引，且具有该行数据的唯一标识。一张表只能有一个主键约束。

【3】主键的作用

• 添加主键primary key的字段即不能重复也不能为空，效果与“not null nuique”相同，但本质是不同的，添加主键约束之后，主键不仅会有“not null unique”作用，而且主键字段还会自动添加“索引 — index”；
• 一张表应该有主键，若没有，表示这张表是无效的。“主键值”是当前行 数据的唯一标识，“主键值”可以是当前行数据的身份证号；（即使表中两行数据完全相同，但是由于主键不同，我们也认为这是两行完全不同的数据）

【4】主键根据个数分类

• 单一主键、复合主键
• 给一个字段添加一个主键约束，被称为单一主键
• 给多个字段联合添加一个主键约束，被称为复合主键

ps:不论时单一主键还是复合主键，一张表只能有一个主键约束。

（1）单一主键：列级约束

drop table if exists t_user;

create table t_user(
	id int(10) primary key,
    name varchar(16)
);

（2）单一主键：表级约束

drop table if exists t_user;

create table t_user(
	id int(10),
    name varchar(32),
    primary key(id)
);

• 给主键重命名

-- 步骤 1: 删除原主键约束
ALTER TABLE t_user
DROP PRIMARY KEY;

-- 步骤 2: 修改列名
ALTER TABLE t_user
CHANGE COLUMN id new_id INT;

-- 步骤 3: 添加新的主键约束
ALTER TABLE t_user
ADD PRIMARY KEY (new_id);

（3）复合主键：只能为表级约束

drop table if exists t_user;
create table t_user(
	id int(10),
  name varchar(32),
  email varchar(32),
  constraint t_user_id_name_pk primary key(id,name)
);

【5】主键根据性质分类

• 自然主键：主键值若是一个自然数，并且这个自然数与业务没有任何关系，这种主键称为自然主键；
• 业务主键：主键值若和当前表中的业务紧密相关，那么这种主键值被业务主键；如果业务发生改变时，业务主键往往会受到影响，所以业务主键使用较少，大多情况使用自然主键。

【6】自动生成主键

• MySQL中自动生成主键值(使用自增字段auto_increment)
• MySQL数据库管理系统中提供了一个自增数字，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户生成，MySQL会自动生成。
• 自增数字默认从1开始，以1递增：1、2、3、

drop table if exists t_user;

create table t_user(
  id int(10) primary key auto_increment,
  name varchar(32)
);

mysql> insert into t_user(name) values('zhangsan'),('hope'),('opp');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | hope     |
|  3 | opp      |
+----+----------+
3 rows in set (0.00 sec)

Ps:自动生成后，一个主键自然数只能出现一次，若删除该行纪录，重新递增纪录时，主键自然数会跳过直接+1

【八】外键约束FK（foreign key）

【1】外键涉及到的术语

• 外键约束，外键字段，外键值

【2】外键约束，外键字段，外键值之间的关系？

• 某个字段添加外键约束之后，该字段为外键字段，外键字段中的每一个数据都是外键值。

【3】外键根据个数分类

• 单一外键【给一个字段添加外键约束】和复合外键【给多个字段联合添加成一个外键约束】

【4】补充

• 注：一张表中可以有多个外键字段。
• 注：外键值可以为空值NULL。
• 注：外键字段引用一张表的某个字段时，被引用的字段必须具有unique约束。
• 注：有了外键引用之后，表分为父表和子表。一对多关系中，一为父表，多为子表。创建表时，先创建父表，再创建子表；插入数据时先插入父表中的数据，再插入子表中的数据；删除数据时，需先删除子表的数据，再删除父表的数据。
• 注：外键约束没有列级约束，只有表级约束。

【5】外键演示

• 先创建表1
• 先有了需要被建立关系的表才能让建立关系的表去和他建立关系

create table 表名1(
	字段名1 字段类型1 约束条件1 comment 注释1,
    字段名2 字段类型2 约束条件2 comment 注释2,
)

create table 表名2(
	字段名1 字段类型1 约束条件1 comment 注释1,
	字段名2 字段类型2 约束条件2 comment 注释2,
  foreign key(外键字段名称) references 表名1(在表名1内部建立联系的字段名)
);

• 创建表

# 创建被约束的表
create table dep(
	id int(4) primary key auto_increment comment "部门表id",
    dep_name varchar(32) comment "员工名字",
    dep_desc text comment "部门介绍"
);

# 创建外键约束的表，前提是被建立连接的表先创建
create table emp(
	id int(4) primary key auto_increment comment "员工id",
    name varchar(32) comment "员工名字",
    age int(4) comment "员工年龄",
    dep_id int(4) comment "外键关联部门id",
    	foreign key(dep_id) references dep(id)
);

• 创建顺序
  • 先创建被关联的表
  • 再创建需要添加外键字段的表
• 删除顺序
  • 先删除添加外键字段的表
  • 再删除被关联的表

（1）一对一关系

在关系型数据库中，一对一关系通常需要在其中一个表中设置一个外键，该外键指向另一个表的主键。以下是创建一个简单的一对一关系的例子，假设有两个实体表 Person 和 Passport，它们之间是一对一关系：

sql
Copy code
CREATE TABLE Person (
    person_id INT PRIMARY KEY,
    person_name VARCHAR(255),
    passport_id INT UNIQUE,
    FOREIGN KEY (passport_id) REFERENCES Passport(passport_id)
);

CREATE TABLE Passport (
    passport_id INT PRIMARY KEY,
    passport_number VARCHAR(255)
);
在上述例子中：

Person 表包含个人信息，其中 person_id 是主键，passport_id 是外键，它指向 Passport 表的主键。
Passport 表包含护照信息，其中 passport_id 是主键。
在这个例子中，Person 表中的 passport_id 列是一个外键，通过这个外键，可以与 Passport 表建立一对一的关系。确保 passport_id 在 Person 表中设置为 UNIQUE，以确保每个人只有一个护照。

你可以插入数据来建立一对一关系：

sql
Copy code
INSERT INTO Passport (passport_id, passport_number) VALUES
(1, 'AB123456');

INSERT INTO Person (person_id, person_name, passport_id) VALUES
(101, 'John Doe', 1);
在这个例子中，通过 passport_id 的外键关系，Person 表中的每个人都与 Passport 表中的一个护照建立了一对一的关系。

（2）一对多关系

• 以员工表和部门表来说

  • 员工可不可以只对应一个部门？
    • 默认是可以的
  • 部门是不是可以有多个员工？
    • 部门可以有多个员工

• 总结

  • 部门是一
  • 员工是多

• 一对多关系将外键建立在哪一方？

  • 将外键建在多的这一方

• 如果表中有外键字段 那么建议你先编写普通字段 最后再考虑外键字段

• 创建部门表

# 创建被约束的表
create table dep(
	id int(4) primary key auto_increment comment "部门表id",
    dep_name varchar(32) comment "部门名称",
    dep_desc text comment "部门介绍"
);

# 创建外键约束的表，前提是被建立连接的表先创建
create table emp(
	id int(4) primary key auto_increment comment "员工id",
    name varchar(32) comment "员工名字",
    age int(4) comment "员工年龄",
    dep_id int(4) comment "外键关联部门id",
    foreign key(dep_id) references dep(id)
);

-- 向部门表中插入数据
insert into dep(dep_name,dep_desc) values("cook","后厨部门");
insert into dep(dep_name,dep_desc) values("hospital","医院部门");

-- 向员工表中插入数据,不带外键字段
insert into emp(name,age) values('zhangsan',18);

-- 向员工表插入数据，携带部门表中存在的部门ID
insert into emp(name,age,dep_id) values("hope",38,1);

（3）多对多关系

• 针对多对多表关系
• 两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

mysql> CREATE TABLE Students (
    ->     student_id INT PRIMARY KEY,
    ->     student_name VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE Courses (
    ->     course_id INT PRIMARY KEY,
    ->     course_name VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE StudentCourses (
    ->     id INT PRIMARY KEY,
    ->     student_id INT,
    ->     course_id INT,
    ->     FOREIGN KEY (student_id) REFERENCES Students(student_id),
    ->     FOREIGN KEY (course_id) REFERENCES Courses(course_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

• 级联更新和级联删除

• 在实际工作中外键也可能不会使用 因为外键会消耗额外的资源并且会增加表的复杂度

• 表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系

在数据库中，级联更新（Cascade Update）和级联删除（Cascade Delete）是外键约束的两个常见选项，用于指定在主表（父表）的记录更新或删除时，如何处理与其关联的从表（子表）的记录。

级联更新（Cascade Update）：

当主表中的主键值被更新时，级联更新会自动更新与之关联的从表中的外键值。

sql
Copy code
CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(255)
);

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(255),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON UPDATE CASCADE
);
在上述例子中，如果你更新了 department 表中的 dept_id，与之关联的 employee 表中的所有对应的 dept_id 也会被更新。

级联删除（Cascade Delete）：

当主表中的主键值被删除时，级联删除会自动删除与之关联的从表中的记录。

sql
Copy code
CREATE TABLE author (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

CREATE TABLE book (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES author(author_id) ON DELETE CASCADE
);
在上述例子中，如果你删除了 author 表中的某个作者，与之关联的 book 表中所有对应的书籍也会被删除。

注意：使用级联更新和级联删除时需要谨慎，确保操作符合业务逻辑，以免意外删除或更新数据。

__EOF__

本文作者：Fredette
本文链接：https://www.cnblogs.com/Fredette/p/18002000.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！