MySQL 数据库

目录

MySQL数据库

1.数据库前戏

1)存储数据的演变史

阶段一：文本文件

文件路径不固定、数据格式不统一

阶段二：软件开发目录规范

规定了数据应该保存在db目录下

但是数据格式仍未统一

阶段三：数据库服务

统一了存取位置，统一了操作方式

降低学习成本，提高开发效率

2)数据库软件应用使

1.单机游戏：数据存储于各个计算机本地，信息无法共享

2.网络游戏：数据存储于网络中，信息可以共享（数据库服务）但是必须联网

"""
远程数据库服务有两个问题：
1.数据安全性问题
2.服务器负载问题
解决方法：
[数据库集群]（让多台服务器运行相同的数据库服务）
即备份了数据又让服务器负载均衡
"""

3)数据库的本质

1.站在底层原理的角度
操作数据的进程（运行在内存中的代码）
2.站在实际应用的角度
可视化操作界面（用于操作进程的界面）


不做特殊说明时提数据库其实都是在指数据库软件
数据库软件本质就是cs架构程序

4)数据库的分类（重要）

(1)关系型数据库

一般用在数据的永久存储

1.数据的存储方式，有固定的表结构（字段名、字段类型）
eg：id name pwd
# 关系型数据库存取方式可以看成是表格

2.表与表之间可以建立数据库层面的关系
eg：用户层  豪华层
# 只要获取用户表中的某条数据，就可以获取豪宅表中对应的信息

# 常用关系型数据库
1.MySQL :开源免费 使用最广 性价比
2.MariaDB: 开源免费 MySQL是同一个作者 用法相似
3.PostgreSQL : 开源免费 支持二次开发 兼容性高
4.Oracle ：收费 安全性高但是维护成本也高，银行等重要机关用
5.sqlite： 小型数据库  主要用于本地测试（django框架自的数据库）
(2)非关系型数据库

一般用在数据的查询

1.数据的存储方式，没固定的表结构
eg：{'name':''}
# 主要采用k：v键值对形式

2.数据之间无法直接建立数据库层面的关系  
# 自己编写代码建立逻辑层面的关系

# 常用非关系型数据库
1. redis : 目前最火、使用频率最高的缓存数据库
    虽然缓存数据库是基于内存做数据存取，但是也拥有持久化的功能
2.mongoDB:文档型数据库，最像关系型数据库的非关系型数据库
    主要用在爬虫、大数据等
3.memcache： 已经被redis淘汰

2.MySQL数据库简介

1)MySQL版本问题

5.6版本：学习阶段使用
5.7版本：使用频率最高
8.0版本：最新版

# mac建议下载8.0版本
# windows哪个版本都可以

2)下载与安装

1.访问官网  https://www.mysql.com/
2.点击下载  DOWNLOADS
3.最下方选择  MySQL Community (GPL) Downloads »
4.点击  MySQL Community Server
5.点击  Archives
6.选择版本 与 本机系统 (学习建议5.6版本 如：5.6.44)
7.选择64位然后download下载
8.解压安装
	上述方式下载的压缩包里含有服务端和客户端 支持本地操作

3)主要目录介绍

bin文件夹
    mysqld.exe服务端    mysql.exe客户端
data文件夹
    存取数据
my-default.ini
    默认配置文件

3.MySQL基本使用

# 建议以管理员身份启动
1.用cmd命令启动服务端
  cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysqld
    (Tab自动补全)
	'可能会报错:拷贝关键信息去百度'
    此时该cmd窗口已经启动了mysqld服务端(如果关闭窗口服务端也会停止)
    
2.再打开一个新的cmd窗口启动客户端
	cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysql
    直接回车会以'游客模式'进入 权限和功能很少
    
3.用管理员登录
    mysql -u用户名 -p密码(eg：mysql -uroot -p密码  直接回车)
    # mysql默认管理员账号 用户名是root 密码是空
    如：
    cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysql -uroot -p
    
4.退出
    exit  或  quit

4.系统服务制作

1.先把bin目录添加到环境变量中
2.将mysql服务端制作成系统服务（随着计算机的开启而启动，关闭而关闭，不需要每次都打开一个服务端在开一个客户端）
	1.以管理员身份打开cmd窗口（搜索cmd右键管理员模式启动）
    2.管理员cmd中执行系统服务命令mysqld--insta11
    （把MySQL添加到系统服务中）
    3.启动服务端
    方式1：右键直接点击启动
    方式2：管理员cmd中执行命令启动net start mysql
    
    
   """
   1.查看系统服务的命令
   services.me
   2.如何卸载重新安装：
   1）关闭mysql服务端
   net stop mysql
   2）移除系统服务
   			1.先确保服务已经关闭
   			2.执行移除命令mysqld--remove
   """

5.密码相关操作

1.修改密码：
	mysqladmin命令
    方式1：# 直接在cmd中写 不要进入mysql
    mysqladmin-u 用户名-p原密码psaaword新密码
    			第一次修改
    mysqladmin-uroot-p123 password 321
    
    方式2：直接修改存储用户数据的表
    
    方式3： 偏门方式（有些版本无法使用）：# 需要先登录
    set password = password('新密码')；

2.忘记密码
    方式1：卸载重装
    方式2：把data目录删除 拷贝别人的目录
    方式3：命令方式修改
        1.关闭正常的服务端：
            net stop mysql
        2.以跳过授权表的方式重启服务端(不校验密码)：
            mysqld --skip-grant-tables
        3.再开一个cmd以管理员身份进入：
            mysql -uroot -p
        4.修改mysql.user表数据
            update mysql.user set password=password('123') where Host='localhost' and User='root';
        5.net stop mysql
        6.net start mysql

6.SQL与NoSQL数据库语句

数据库服务端是可以服务读懂中类型的客户端
客户端可以是自己开发的 也可以是python代码编写的 也可以是java代码编写的

SQL： 操作关系型数据库的语言
有时候也表示关系型数据库

NOSQL： 操作非关系型数据库的语言
有时候也代表非关系型数据库

7.数据库重要概念

# 以下表示并不精准  仅限于小白了解
库 类似于  文件夹
表 类似于   文件夹里的文件
记录  类似于  文件夹里的文件里的每行数据

1.查看所有的数据库
show  databases;
2.查看所有的表
show tables;
3.查看user表里的所有记录
select from mysql.user;

8.基本SQL语句

# 预备知识
1.SQL 语句结束符是分号
    ；
2.取消SQL语句的执行（可以直接让他报错重新输入，反正不影响）
    \C

1)针对库的基本SQL语句

1.增
	create database  库名； # 新建库
2.查
	show databases; # 查看所有库名字
    show create database 库名；#查看指定库的信息
3.改
	alter database 库名 charset = 'gbk';# 修改库的编码
4.删
	drop database 库名；# 删除库

2)针对表的基本SQL语句

'操作表前需先确定库'

select database();  # 查看当前所在的库
use 库名;  # 切换库

1.增
	create table 表名（字段名  字段类型）；# 创建表
    create table t1(id int,name varchar(32));
2.查
	show tables; # 查看库下所有的表名
    show create table 表名； # 查看指定表中的字段
    desc 表名; # 查看指定表中的字段
    """
    跨库查询表：
    desc 库名.表名；
    desc mysql.user;
    """
3.改
	alter table 旧表名rename 新表名；# 改表名
4.删
	drop table 表名； # 删除表

3)针对记录的基本SQL语句

'操作记录前需要先确定库和表'

1.增 
	insert into 表名 values(数据值1，数据值2..)
    insert info t1 values (1,'jason');
    insert info t1 values(2,'torry'),(3,'jack');
2.查
	select from 表名；# 查看表中所有的数据
    '表示查看所有字段'
    select from mysql。user\G；
    '如果表中字段较多出现错乱 结尾可以加\G'
    select name,pwd from t1; # 查看表中name 和 pwd 字段的所有数据
3.改
	update 表名set 字段名=新数据值where筛选条件；# 修改表中某个条件下的字段名改为新数据值
    update t1 set name='torry' where id=1;
4.删
	delete from 表名；# 删除表中所有的数据
    '就会变成一个空表'
    delete from 表名 where 筛选条件 # 按照条件删除数据

9.字符编码与配置文件

\s #查看数据库基本信息(用户、版本号、字符编码、端口号等)
5.6版本字符编码【不统一】 需统一改为utf8
5.6以后的版本不需要 默认就是统一的utf8

1.MySQL文件中 my-default.ini #windows下MySQL默认配置文件，Mac后缀不一样
	再复制一份该文件,重命名为my.ini
2.清空里面内容 添加字符编码相关配置(百度查看)
  # 注：此处清空内容后会埋下一个坑 下面整型严格模式时会发现
    [mysqld]
      character-set-server=utf8mb4
      collation-server=utf8_general_ci
    [client]
      default-character-set=utf8mb4
    [mysql]
      default-character-set=utf8mb4 
   # utf8mb4能存储表情 功能更强大
   # utf8与utf-8不一样,MySQL只有utf8
   # 如果配置文件涉及到mysqld相关的配置修改,那必须重启服务端才能生效
   # 搜索【服务】,找到MySQL重新启动即可
   # 需注意以上修改编码前的之前的数据编码并未修改

"""
偷懒操作：每次登录还要输root 密码 太麻烦,所以可以在配置文件中的[mysql]下写好用户名和密码,这样以后登录的时候直接打mysql就可以登录
    [mysql]
        user='root'
        password='123'
"""

10.数据库存储引擎(重要)

存储引擎：数据库针对数据采取的多种存取方式
show engines; 查看所有的存储引擎

1)需了解的4个存储常见引擎

1.MyISAM
      1)MySQL 5.5及以前版本默认的存储引擎
      2)存取数据的速度快 功能较少 安全性较低

2.InnoDB  ※
      1)MySQL 5.6开始之后版本默认的存储引擎
      2)存取数据的速度没MyISAM快 支持事物、行锁、外键等功能 安全性较高
        
3.Memory
      1)基于内存的存储引擎
      2)存取数据极快 但是重启数据库,数据立刻丢失(仅用于临时表数据存取)
        
4.BlackHole
      1)黑洞 类似垃圾桶
      2)任何写进去的数据都会立刻丢掉

2)不同存储引擎之间底层文件的区别

MySQL中默认忽略大小写 对大小写不敏感

# 1.创建4个表 分别对应4个不同的存储引擎
create table t1(id int) engine = myisam;
create table t2(id int) engine = innodb;
create table t3(id int) engine = memory;
create table t4(id int) engine = blackhole;

# 2.检查MySQL>>data目录>>对应库中的文件发现：各个表中新建的文件不同
1)t1表(MyISAM 存储引擎);
      t1.frm  '表结构'
      t1.MYD  '表数据'
      t1.MYI  '表索引(加快了数据查询)'
2)t2表(InnoDB 存储引擎);
      t2.frm  '表结构'
      t2.ibd  '表数据、表索引(加快了数据查询)'
3)t3表(Memory 存储引擎);
      t3.frm  '表结构'
4)t4表(BlackHole 存储引擎);
      t4.frm  '表结构'
    
# 3.往4个表中插入记录发现：
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

select * from t1; # 正常插入记录
select * from t2; # 正常插入记录
select * from t3; # 重启数据库Memory中的数据丢失
select * from t4; # 无法把数据存入BlackHole中

11.创建表的完整语法

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


 1.字段名和字段类型必须要有(可以不用写多对,但是'至少写一对')
 2.字段类型后的数字 和 约束条件 可以写'也可以不写'
 3.约束条件可以写多个 不过需要'空格隔开'
*4.当多个字段名和字段类型时,最后一对'结尾不能加逗号'！！！

12.MySQL字段基本数据类型

1)整型

类型 所占字节bytes 带负号 不带负号
tinyint(微整型) 1 -128~127 0~255
smallint(小整型) 2 -32768~32767 0~65535
int(整型) 4 -2147483648~2147483647 0~4294967295
bigint(大整型) 8 -9223372036854775808~9223372036854775807 0~18446744073709551615
(1)验证整型默认是否携带正负号

当默认携带正负号时 范围则是负数到正数的值

create table t5(id tinyint);  # 创建t5表 类型为微整型
insert into t5 values(-129),(128);  # 插入两条数据 分别超出带正负号的最大、最小范围
select * from t5;  # 查看t5表中所有记录数据

1)'发现结果为-128 和 127' # 由此可得正负号也默认占一个字节
2)发现哪怕超出范围 MySQL会自动填写为两个边界值，不合理(应该直接报错)
(2)移除负号：解决正负号问题

当移除负号后 范围则是正数的值

unsigned约束条件之一：不带负号

create table t6(id tinyint unsigned);  # 创建t6表 类型为微整型 不带负号
insert into t6(-1),(256);  # 插入两条数据 分别超出不带负号的最大、最小范围
select * from t6;  # 查看t6表中所有记录数据

1)'发现结果为0 和 255' # 由此可得移除负号后 则不能插入负数 只能插入正数,且范围变成了255
2)发现哪怕超出范围 MySQL会自动填写为两个边界值，不合理(应该直接报错)
(3)严格模式：解决自动填写边界值问题

插入的数据当超过数据类型的范围时 应该直接报错,不该自动修改数据！这样会导致我插入的数据失去了本身的意义。

PS: 其实MySQL按理说是会报错的,只是在前面清空配置文件时清掉了

# 方式一：命令临时修改
set session sql_mode = 'strict_trans_tables'; # 在当前客户端有效
set global sql_mode = 'strict_trans_tables'; # 在当前服务端有效

# 方式二：在配置文件中永久修改(建议)
    [mysqld]
        sql_mode='STRICT_TRANS_TABLES'

2)浮点型

float
double
decimal
以上三种'都可以存储浮点型数据' 只是各自的'精确度不一样'

float(20,10)  # 共存储20位数 小数点后面占10位
double(20,10)  # 共存储20位数 小数点后面占10位
decimal(20,10)  # 共存储20位数 小数点后面占10位
(1)验证精确度问题
create table t7(id float(30,20));  # 创建t7表 类型为float 共存储30位数, 小数点后占20位
create table t8(id double(30,20));  # 创建t8表 类型为double 共存储30位数, 小数点后占20位
create table t9(id decimal(30,20));  # 创建t9表 类型为decimal 共存储30位数, 小数点后占20位

insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);

select * from t7;  # 发现小数点后到第7位后精确度就不准确了 1.11111116409301760000
select * from t8;  # 发现小数点后到第15位后精确度就不准确了 1.11111111111111120000
select * from t9;  # 发现小数点后到第20位后精确度还很准确 1.11111111111111111111

"""
结论：
1.精确度排名
    float < double < decimal

2.一般float就够平时使用了，如果想追求完美的精确度可以用字符型来代替(如：手机号等)
"""

3)字符型

1)char 定长
     char(4)  
         # 最多存储4个字符 超出范围报错 
         # 不够4个用空格填充至4个
2)varchar 变长
     varchar(4)  
          # 最多存储4个字符 超出范围报错 
          # 不够4个有几个存几个
(1)验证char与varchar长度问题

char_length(字段名) 获取字段存储的数据长度

create table t10(name char(4)); # 创建t10表 类型为char 最大长度为4
create table t11(name varchar(4)); # 创建t11表 类型为varchar 最大长度为4

insert into t10 values('a');
insert into t11 values('b');

select char_length(name) from t10;  # 获取t10表中name字段数据的长度
select char_length(name) from t11;  # 获取t11表中name字段数据的长度
"""
以上结果：
    t10表中长度为1
    t11表中长度为1
    
1.提问：为什么char(4)不够4个用空格填充至4个 获取长度后还是1？
1.回答：因为默认情况下char在存储的时候针对没有满足固定位数的字符确实会自动在后面填充空格,但是【在读取的时候又会自动将填充的空格移除掉】

2.提问：如何取消该移除空格机制？
2.回答：命令临时修改
  set global sql_mode='strict_trans_tables,pad_char_to_full_length';
"""
# 需注意：上述临时配置命令是替换不是新增，所以要把之前配置的超出范围报错的命令也加上！

该取消移除空格机制命令不用添加到配置文件中 临时用一下就好,因为移除的机制挺好用的
(2)char与varchar哪个好
类型 优势 劣势
char 整存整取 速度快 浪费存储空间
varchar 节省存储空间 存取数据的速度慢于char 
# char与varchar在内存中怎么存数据的：

1)char(4)
    a  bb ccdddd
'占4个位置存放'

2)varchar(4)
1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
'存储数据都是需要操作报头才能让varchar识别每个字符串多少长度分开处理。这就是varchar比char耗时多的原因'

# 结论：两者使用频率都很高根据实际应用决定 现在默认很多时候是varchar

4)数字的含义

数字在大部分情况下都是用来表示限制存储数据的长度

但是！在整型中是控制最少展示的长度！！

zerofill约束条件，不足的用0填充

# zerofill常用在快递单号中
create table t12(id int(3) zerofill);
insert into t12 values(1),(12345);

select * from t12;
"""
结果为：
001     因为最少展示3位 且不足的用0填充
12345   因为够3位了 所以不需要填充
"""
# 总结：以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数(快递单号等)

5)枚举与集合

(1)enum 枚举

多选一

# eg: 性别(男 女 其他)

create table t14(gender enum('male','female','others'));
# 只能选三个中的一个
insert into t14 values('male');
(2)set 集合

多选一 或 多选多

# eg：爱好(唱 跳 rap)
create table t15(hobby set('sing','jump','rap'));
# 只能输三个中的一个或多个
insert into t15 values('sing'); # 多选一
insert into t15 values('sing,jump'); # 多选多

6)日期

date		 年月日
datetime	 年月日时分秒
time		 时分秒
year		 年份

create table t16(date date, datetime datetime, time time,year year);
insert into t16 values('2022-11-23','2022-11-23 12:00:00','12:00:00','2022');
select * from t16;

# 结果：
+------------+---------------------+----------+------+
| date       | datetime            | time     | year |
+------------+---------------------+----------+------+
| 2022-11-23 | 2022-11-23 12:00:00 | 12:00:00 | 2022 |
+------------+---------------------+----------+------+

13.MySQL字段约束条件

约束条件：在字段类型的基础上给字段添加额外的限制

#预备知识：

1.# 空的记录为：NULL

2.# 插入数据
create table t1(id int,name varchar(32));

  1)# 默认按照创建表的字段顺序插入记录
  insert into 表名 values(值1,值2);  
  ——————————————————————————————————
  insert into t1 values(1,'jack');

  2)# 也可以自定义字段顺序插入记录
  insert into 表名(字段1,字段2) values(值1,值2);  
  ——————————————————————————————————
  insert into t1(name) values('lili');
  insert into t1(name,id) values('jason',3);

  select * from t1;

  """
  +------+-------+
  | id   | name  |
  +------+-------+
  |    1 | jack  |
  | NULL | lili  |
  |    3 | jason |
  +------+-------+
  """

1)无负号(符号)

unsigned无负号

# 创t2表 给id字段 且约束为无负号
create table t2(id int unsigned); # int整型无负号的范围为：0~4294967295

insert into t2 values(4294967295); # 插入最大范围的值

select * from t2;
"""
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
"""

2)零填充

zerofill不够范围用0填充

# 创t3表 给id字段 且约束不够范围用0填充
create table t3(id int(5) zerofill);

insert into t3 values(1);

select * from t3;
"""
+-------+
| id    |
+-------+
| 00001 |
+-------+
"""

3)非空

not null该字段必须给一个值,不能为空

# 创t4表 给id 和 name字段 且约束name字段不能为空
create table t4(id int,name varchar(32) not null);

insert into t4 (id) values(1); # 发现会报错,因为没有给name插数据
insert into t4 (id,name) values(1,'jason');  # 可插入数据
insert into t4 (name) values('torry');  # 可插入数据

select * from t4;
"""
+------+-------+
| id   | name  |
+------+-------+
|    1 | jason |
| NULL | torry |
+------+-------+
"""

4)默认值

default不插入数据就用默认值

# 创t5表 给id 和 name字段 并约束两个字段为两个默认值
create table t5(id int default 111, name varchar(32) default 'aaa');

insert into t5 (id) values(1);  # 不给name插数据
insert into t5 (name) values('jason');  # 不给id插数据
insert into t5 values(3,'torry');  # 两个都插数据

select * from t5;
"""
+------+-------+
| id   | name  |
+------+-------+
|    1 | aaa   |
|  111 | jason |
|    3 | torry |
+------+-------+
"""

5)唯一值

unique字段中的数据必须是唯一

"""单例唯一"""
# 创t6表 给id字段 且约束数据单例唯一
create table t6(id int unique);
insert into t6 values(1);
insert into t6 values(1);# 报错,因为必须唯一

"""联合唯一"""
# 创t7表 给id和post字段 且约束两个字段数据联合唯一
create  table t7(ip varchar(32),port int,unique(ip,port));
insert into t7 values('192.168.1.1',8080);
insert into t7 values('192.168.1.1',8080); # 报错,因为必须联合唯一
insert into t7 values('192.168.1.1',8000); # 不报错,因为只有合在一起不唯一才报错
select * from t7;
"""
+-------------+------+
| ip          | port |
+-------------+------+
| 192.168.1.1 | 8000 |
| 192.168.1.1 | 8080 |
+-------------+------+
"""

6)主键

primary key给该字段设置为主键

image-20221124161744485

(1)单从约束层面上看 主键相当于非空 且 唯一（not null + unique
# 创t1表 给id与name字段 且约束id为主键(非空且唯一)
create table t1(id int primary key,name varchar(32));

insert into t1(name) values('jason'); # 报错,因为id不能为空且必须唯一
insert into t1(id) values(1); # 不报错 因为name可以为空

# 查看指定表中的字段等信息
desc t1; 
"""
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
"""
(2)InnoDB存储引擎规定一张表必须有且只有一个主键

主键是组织数据的重要条件，且可以加快数据查询速度

📌①.当表中没有主键 也没有 非空且唯一的字段时：
InnoDB会采用一个隐藏字段作为该表的主键(隐藏意味无法使用)
基于该表的数据查询只能一行一行查询 速度很慢

📌②.当表中没有主键 但是有 非空且唯一的字段时：
InnoDB会自动将从上往下第一个非空且唯一的字段设置成主键

# 验证以上第二点
create table t2(
    id int,
    id1 int not null unique, 
    id2 int not null unique,
    id3 int not null unique
);

# 查看指定表中的字段等信息
desc t2;
"""
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| id1   | int(11) | NO   | PRI | NULL    |       |
| id2   | int(11) | NO   | UNI | NULL    |       |
| id3   | int(11) | NO   | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
"""
(3)每个表都应该有一个id字段,且应该为主键

类似编号等，uid、sid、pid、gid等

7)自增

auth_increment让主键id自增 后续添加数据可以不写主键的数据

该约束条件一张表只能出现一次，主要配合主键一起用

# 需先约束主键 再约束自增

# 创t3表 给id和name字段 且约束id为主键且自增
create table t3(id int primary key auto_increment,name varchar(32));

insert into t3(name) values('jason'); # 不报错是因为主键自动自增

select * from t3;
"""
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
+----+-------+
"""
(1)自增的特性

①.自增不会因为删除单条数据或清空所有数据而回退或重置，永远只会往后自增。
如：现在主键id有1 2 3 4 如果删除4或清空所有数据，再插入新的数据来只会从5开始

# 创t4表 给id和name字段 且约束id为主键且自增
create table t4(id int primary key auto_increment,name varchar(32));

insert into t4(name) values('jason'),('torry'),('tom'),('jsck');

# 删除t4表中 id大于2的所有数据
delete from t4 where id>2;

# 插入一条新数据
insert into t4(name) values('lili');

select * from t4;
"""
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | torry |
|  5 | lili  |
+----+-------+
"""
# 以上可以发现新插入的数据id是从5开始的

②.如果插入了更大的数据，之后的自增会按照更大的数往后自增
如：新插入了更大的id：1000 之后自增就是1001开始

(2)重置表中的主键值：格式化表

truncate 格式化表(清空表数据,且重置主键)

# 格式化t4表
truncate t4;

# 给t4表插入一条新数据
insert into t4(name) values('jason')

# 查看主键是否重置
select * from t4;
"""
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
+----+-------+
"""
# 以上发现表中数据不仅被清空了,主键也重置了

8)外键

(1)外键前戏
# 需要一张员工表:
id   name   age   dep_name    dep_desc
1    张三   18      环安      负责安全
2    李四   20      财务      结算薪资
3    王五   19      环安      负责安全

# 以上表的缺陷：
1.表结构不清晰 到底是员工表还是部门表
2.部分字段数据反复填写 浪费存储空间
3.表的扩展性极差 修改部门时多个员工的部门都要改

# 优化操作：分成两个表
【员工表】
id   name   age

【部门表】
id    dep_name    dep_desc

# 但是两个表之间没有任何关系了。员工表中添加一个部门编号(外键)来填写部门表的主键,让两个表有关系
# 
【员工表】
id    name    age    dep_id

【部门表】
id   dep_name   dep_desc
(2)什么是外键字段

外键字段：就是用来记录表与表之间数据的关系

(3)建立外键字段

foreign key建立外键字段

1.外键字段：
foreign key(外键字段) references 被关联表(被关联表主键)

2.级联更新与级联删除：
# 被关联的数据一但变动 关联的数据同步变动
  级联更新：on update cascade
  级联删除：on delete cascade

'以上三个指令是一段话 中间不用加逗号'

"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
        因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    一般都是自己通过写代码建立逻辑层面的关系 不用外键 
        
   一般在公司表小于100张时属于较少,可以用外键。超过100则自己写SQL
"""
(4)表与表之间数据的四种关系判断
👻一对多
# 针对员工表和部门表判断表数据关系：

    1)先站在员工表的角度
        问:一个员工能否对应多个部门
        答:不可以
    2)再站在部门表的角度
        问:一个部门能否对应多个员工
        答:可以
    结论:一边可以一边不可以 那么表数据关系就是'一对多'
         部门是一 员工是多
        # 针对一对多：外键字段建在多的一方 
        # 【没有多对一】 统一称为【一对多】

1)创建表的时候要先创建'被关联的表'(不放外键字段的)
2)再创建'关联的表'(放外键字段的)
  2.1)先写普通字段
  2.2)再写外键字段
  2.3)添加级联更新、级联删除
    
# 创建从表 给id、dep_name、dep_desc字段 且约束id为主键自增
create table dep1(
        id int primary key auto_increment,
        dep_name varchar(32),
        dep_desc varchar(32)
);
# 创建主表 给id、name、age、dep_id 且约束id为主键自增，给dep_id关联为外键
create table emp1(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int, # 创建一个外键字段
    foreign key(dep_id) references dep1(id) #让dep_id关联dep1表中的id
    on update cascade on delete cascade # 级联更新 级联删除
);
👻多对多
# 针对书籍表和作者表判断表数据关系：

    1)先站在书籍表的角度
        问:一本书能否对应多个作者
        答:可以
    2)再站在作者表的角度
        问:一个作者能否对应多本书
        答:可以
    结论:两边都可以 表数据关系为：'多对多'
         # 针对多对多：外键字段不能建在任意一方,需要单独开设第三张表

1)先把两张表创建出来
2)再创建'第三张表'
  2.1)写上前两张表中要关联的外键字段
  2.2)添加级联更新、级联删除

# 创建书籍表
create table book(
    id int primary key auto_increment, 
    title varchar(32)
);

# 创建作者表
create table author(
    id int primary key auto_increment,
    name varchar(32)
);

# 创建第三张表
create table book2author(
    id int primary key auto_increment, 
    author_id int, # 创建一个外键字段
    foreign key(author_id) references author(id) #让author_id关联author表中的id
    on update cascade on delete cascade,# 级联更新 级联删除
    book_id int, # 创建一个外键字段
    foreign key(book_id) references book(id) #让book_id关联book表中的id
    on update cascade on delete cascade # 级联更新 级联删除
);
👻一对一

一对一 与 一对多 的区别在于 一对一被关联字段增加了约束唯一值

# 针对用户表和用户详情表判断表数据关系：

    1)先站在用户表的角度
        问:一个用户能否对应多个用户详情
        答:不可以
    2)再站在用户详情表的角度
        问:一个用户详情能否对应多个用户
        答:不可以
    结论:两边都不可以 表数据关系可能为：'一对一' 或 '没关系'
         # 针对一对一： 外键字段建在任何一张表都可以 建议建在查询频率较高的表中便于后续查询

1)创建表的时候要先创建'被关联的表'(不放外键字段的)
2)再创建'关联的表'(放外键字段的)
  2.1)先写普通字段
  2.2)再写外键字段并约束唯一值
  2.3)添加级联更新、级联删除
    
# 创建从表
create table userinfo(
    id int primary key auto_increment,
    phone bigint
);

# 创建主表
create table user(
    id int primary key auto_increment,
    name varchar(32),
    userinfo_id int unique, # 创建一个外键字段并约束唯一值
    foreign key(userinfo_id) references userinfo(id) #让userinfo_id关联userinfo表中的id
    on update cascade on delete cascade # 级联更新 级联删除
);
👻没关系​

以上三种都不是的就是没关系

posted @   梅梅小可爱  阅读(234)  评论(0)  编辑  收藏  举报
相关博文：
·  MySQL数据库
·  异常与模块
·  MySQL数据库
·  MySQL数据库
·  python四十期--mysql 配置文件修改字符编码，创建不同存储引擎的表方法，创建表的完整语法中字段名所包含的各种字段类型，严格模式，
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
点击右上角即可分享
微信分享提示
SQL AI 助手