3. 表相关的操作

3. 表相关的操作

1. 存储引擎介绍

1.1. 什么是存储引擎

mysql中建立的库===》文件夹

库中建立的表===》文件

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等

数据库中的表也应该又不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎

存储引擎说白了就是如何存储数据，如何存储的数据建立索引和如何更新，查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

在Oracle和SQL Server等数据库只有一种存储引擎，所有数据存储管理机制都是一样的，而MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

SQL解析器、SQL优化器、缓冲池、存储引擎等组件在每个数据库中都存在，但不是每个数据库都有这么多存储引擎。MySQL的插件式存储引擎可以让存储引擎层的开发人员设计他们希望的存储层，例如，有的应用需要满足事物的要求，有的应用不需要对事务有这么强的要求，有的应用则不需要对事务有这么强的要求；有的希望数据能持久存储，有的只希望放在内存中，临时并快速地提供对数据的查询。

存储引擎就是存储数据的方式

1.2. MySQL支持的存储引擎

InnoDB

是MySQL5.5版本及之后默认的存储引擎

存储数据更加的安全

功能：

​ 1.支持事物

​ 2.行级锁

​ 3.外键

MyISAM

是MySQL5.5版本之前默认的存储引擎

速度要比innodb更快 但是我们更加注重的是数据安全

功能：

​ 1.不支持事物

​ 2.表级锁

MEMORY

内存引擎（数据全部存放在内存中）断电数据丢失

BLACKHOLE

无论存什么，都立刻消失（黑洞）

"""
# 查看所有存储引擎
show engines;

# 不同的存储引擎在存储表的时候 异同点
create table t2(id int) engine=innodb;
innodb
t2.frm	表结构
t2.idb   表数据

create table t3(id int) engine=myisam;
myisam	
t.frm	表结构
t.MYD	表数据
t.MYI	索引（index）就类似与书的目录，基于目录查找对应的数据 速度更快

create table t4(id int) engine=memory;
blackhole	
t.frm

create table t5(id int) engine=blackhole;
memory	
t.frm	数据在内存无需文件存储

# 存数据
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
insert into t5 values(1);
"""

2. 表介绍

表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的表题，称为表的字段

id	name
1	egon
2	wyz

id，name，称为字段，其余的，一行内容称为一条记录

3. 创建表

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

# 注意
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的而字段名和类型是必须的
  约束条件写的话，也支持写多个
    字段名1 类型(宽度) 约束条件1 约束条件2
    create table t1(id); 报错
3 最后一行不能有逗号
	 create table t1(id int,name char,);  # 报错
"""补充"""
# 宽度
	一般情况下指的是对存储数据的限制
    create table t1(name char);  # 默认宽度为1
	insert into t1 values('wyzdsb');
    insert into t1 values(null);
    	针对不同的版本会出现不同的效果
        5.6版本默认没有开启严格默认模式，规定只能存一个字符你给了多个字，那么我会自动帮你截取一个字符
        5.7版本以上或者开启了严格模式，那么规定只能存几个，就不能超出范围立刻报错
使用数据库的准则：
	能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力
    
# 约束条件 null, not null（不能存入null）
create table t8(id int,name char not null);
insert into t8 values(1,null);
报错：ERROR 1048 (23000): Column 'name' cannot be null 
    
"""
宽度和约束条件到底是什么关系
	宽度是用来限制数据的存储
	约束条件是在宽度的基础之上增加的额外的约束
"""
    

4. 基本数据类型

数据库存储类型如下图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b5SQgtzQ-1627731764069)(%E6%95%B0%E6%8D%AE%E5%BA%9303%E2%80%94%E2%80%94%E8%A1%A8%E7%9B%B8%E5%85%B3%E6%93%8D%E4%BD%9C.assets/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B1%BB%E5%9E%8B.jpg)]

4.1. 整型

• 分类

  TINYINT， smallint， mediumint， int， bigint

• 作用

  存储年龄，等级，号码等

"""
以tinyint整形默认都是带符号的
	是否有符号
		默认情况下是带符号的
	超出会如何
		超出限制只存最大可接受值
"""
create table t6(id tinyint);
insert into t6 values(-129),(129);

# 约束条件之unsigned 无符号
create table t9(id tinyint unsigned)

# 针对整型 括号内的宽度到底是干嘛的
create table t10(id int(8));
insert into t10 values(123456789);
"""
特例：只有整形括号里面的数字不是表示限制位数
id int(8)
	如果数字没有超过8位，那么默认用空格填充至8位
	如果数字超出了8位，那么有几位就存几位（但是还是遵循最大范围）
"""
# 约束条件之zerofill 用0填充至8位
create table t11(id int(8) unsigned zerofill);

# 总结：
针对整形字段 括号内无需指定宽度，使用默认宽度，因为写了限制宽度也无法限制

4.2. 浮点型

• 分类

  float，double，decimal

• 作用

  存身高，体重，薪资

# 存储限制
float(255,30)  # 总共255位 小数部分30位
double(255,30)  # 总共255位 小数部分30位
decimal(255,30)  # 总共65位 小数部分30位

# 精确度验证
create table t13(id float(255,30));
create table t14(id double(255,30));
create table t15(id double(65,30));

insert into t13 values(1.111111111111111111111111111111);
insert into t14 values(1.111111111111111111111111111111);
insert into t15 values(1.111111111111111111111111111111);

# 精确度
float < double < decimal

4.3. 字符型

• 分类

"""
char
	定长
	char(4)  数据超过四个字符直接报错 不够四个字符空格补全
	变长
	varchar(4)	数据超过四个字符直接报错 不够有几个存几个
	
"""
create table t16(name char(4));
create table t17(name varchar(4));

insert into t16 values('a');
insert into t17 values('a');

# 小方法 char_length统计字段长度
select char_length(name) from t16;
select char_length(name) from t17;
"""
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的 但是在显示的时候MySQL会自动将多余的空格剔除
"""

# 修改sql_mode 让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

char与varchar对比

"""
char
	缺点：浪费空间
	优点: 存取都很简单
		 直接按照固定的字符存取数据即可
		 存按照五个字符存，取也直接按照五个字符取
		  
varchar
	优点：节省空间
	缺点：存取较为麻烦
		1 bytes+jason 1bytes+egon
		 存的时候需要制作报头
		 取的时候也需要先读取报头 之后才能读取真是数据
以前基本上都是用的char 其实现在用varchar的也挺多
"""

补充：
	进来公司之后你完全不需要考虑字段类型和字段名
    因为产品经理给你发的邮件上已经全部指明了

4.4. 时间类型

• 分类

  date:年月日

  datetime：年月日时分秒

  time：时分秒

  Year：年

  create table student(
      id int,
      
  )
  

4.5. 枚举与集合类型

• 分类

"""
枚举(enum)	多选一
集合(set)     多选多
"""

• 具体使用

create table user(
	id int,
    name char(16),
    gender enum('male','female','others')
);
insert into user values(1,'jason','male');
issert into user values(2,'egon','xxxoo');
# 枚举字段 后期在存数据的时候只能从美剧里面选择一个存储

create table teacher(
    id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','DBJ','basketball')
    
);
insert into teacher values(1,'jason','male','read');
insert into teacher values(2,'tank','male','read,DBJ');
insert into teacher values(3,'egon','male','狗屎');
# 集合可以只写一个 但是不能写没有列举的

5. 严格模式

5.6版本默认没有开启严格默认模式
# 查看严格模式
show variables like "%mode";
sql_mode  显示数据库模式
模糊匹配/查询
	关键字 like
    	%:匹配任意多个字符
        _:匹配任意单个字符
# 修改严格模式
	set session   只在当前窗口有效
    set global	  全局有效
    set global sql_mode = 'STRICT_TRANS_TABLES';
    修改完之后，重新进入服务端即可

6. 表完整性约束

6.1. 介绍

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性 主要分为：

primary key		标识该字段为该表的主键，可以唯一的标识记录
foreign key		标识该字段为该表的外键
not null		标识该字段不能为空
unique 			表示该字段的值是唯一的
auto_increment	标识该字段的值自动增长（整数类型，而且为主键）
default		    为该字段设置默认值
unsigned		无符号
zerofill		使用0填充

6.2. 约束条件

# 补充知识点 插入数据的时候可以指定字段
create table t1(
    id int,
    name char(16)
);
insert into t1(name,id) values('jason',1);

unique唯一

# 单列唯一
create table t3(id int unique,name char(10));
insert into t3 values(1,'egon');  # 正确
insert into t3 values(1,'wyz');  # 错误

# 联合唯一
'''
ip和port
单个都可以重复，但是加在一起必须是唯一的

'''
create table t4(
    id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.2',8080);
insert into t4 values(2,'127.0.0.2',8081);
insert into t4 values(2,'127.0.0.2',8081);  # 报错

primary key 主键

'''单单从约束效果上看primary key 等价于not null + unique非空且唯一'''create table t5(id int primary key);insert into t5 values(null);insert into t5 values(1);'''它除了有约束效果之外，它还是Innodb存储引擎组织数据的依据Innodb存储引擎在创建表的时候必须要有primary key因为它类似于书的目录，能够帮组提示查询效率并且也是件标的依据'''# 1. 一张表中有且只有一个主键，如果你没有设置主键，那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键create table t7(    			id int,                name char(16),                age int unsigned not null unique,                addr char(32) not null unique);+-------+------------------+------+-----+---------+-------+| Field | Type             | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id    | int(11)          | YES  |     | NULL    |       || name  | char(16)         | YES  |     | NULL    |       || age   | int(10) unsigned | NO   | PRI | NULL    |       || addr  | char(32)         | NO   | UNI | NULL    |       |+-------+------------------+------+-----+---------+-------+age 自动升为primary key# 2. 如果表中没有主键也没有其他任何非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它，就无法提示查询速度# 3. 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键# 单个主键create table t8(id int primary key);# 联合主键（多个字段联合起来作为表的主键，本质还是一个主键）create table t9(    ip char(16),    port int,    primary key(ip,port));

auto_increment自增

create table t10(    id int primary key auto_increment,    name char(16)    );insert into t10(name) values('jason'),('wyz'),('kevin');# 注意auto_increment只能加在主键上，不能给普通字段加

结论

'''以后在创建表的id(数据的唯一标识id，uid，sid)字段的时候'''

补充

delete from 在删除表中数据的时候，主键的自增不会停止
truncate table t1 清空表数据并且重置主键
binlog  

外键

'''
外键就是用来帮助我们建立表与表之间关系的
foregin key

'''

7. 表关系

'''
一对多关系
多对多关系
一对一关系
没有关系
'''

7.1. 一对多关系

'''
判断表与表之间关系的时候，前期不熟悉的情况下 一定要按照我给的建议换位思考 分别站在两张表的角度考虑

员工表与部门表为例
	先站在员工表
		思考一个员工能否对应多个部门（一条员工数据能否对应多条部门数据） 结论不能
		思考一个部门能否对应多个员工（一个部门数据能否对应多条员工数据） 结论可以
	得出结论
		员工表与部门表是单向的一对多
		所以表关系就是一对多
'''
foreign key
1. 一对多表关系 外键字段建在多的一方
2. 在创建表的时候 一定要先建被关联表
3. 在录入数据的时候 也必须先录入被关联表
# SQL语句建立表关系
create table department(
    id int primary key auto_increment,
    name varchar(20) not null
);
create table employee(
    id int auto_increment primary key,
    name varchar(20) not null,
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references department(id)
);

insert into department(name) values('教育部'),('工程部'),('消防部'),('信息部');
insert into employee(name,dep_id) values('wyz',1),('djf',1),('egon',2),('tom',3),('bob',4);

# 修改department表里面的id字段
update department set id=20 where id=2;
不行，报错
# 删除department表里面的数据
delete from department; 不行

# 1 先删除教学部门对应的员工数据 之后再删除部门
	操作太过繁琐
# 2 真正做到数据之间有关系
	级联更新
    级联删除
create table department(
    id int primary key auto_increment,
    name varchar(20) not null
);
create table employee(
    id int auto_increment primary key,
    name varchar(20) not null,
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references department(id)
    on update cascade
    on delete cascade
);
 

7.2. 多对多

"""
图书与作者
"""
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
);

create table author(
    id int auto_increment primary key,
    name varchar(20) not null
);

create table author2book(
    id int primary key auto_increment,
    author_id int not null,
    book_id int not null,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

insert into book(title,price) values('python',20),('go',20),('linux',20);
+----+--------+-------+
| id | title  | price |
+----+--------+-------+
|  1 | python |    20 |
|  2 | go     |    20 |
|  3 | linux  |    20 |
+----+--------+-------+

insert into author(name) values('egon'),('tank'),('tom');
+----+------+
| id | name |
+----+------+
|  1 | egon |
|  2 | tank |
|  3 | tom  |
+----+------+

insert into author2book(author_id,book_id) values(1,1),(1,2),(1,3),(2,1),(2,3),(3,1);
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         2 |       1 |
|  5 |         2 |       3 |
|  6 |         3 |       1 |
+----+-----------+---------+

7.3 一对一

"""
id,name,age,addr phone hobby email
如果一个表的字段特别多，每次查询又不是所有的字段都能用的到
将表一分为二
	用户表
		id name age
	用户详情表
		addr phone hobby email

"""

8. 修改表

# MySQL不区分大小
'''
1 修改表名
	alter table 表名 rename 新表名;
	
2 增加字段
alter table 表名 add 字段名 字段类型（宽度） 约束条件;
alter table 表名 add 字段名 字段类型（宽度）约束条件 first;
alter table 表名 add 字段名 字段类型（宽度） 约束条件 after 字段名;

3 删除字段
	alter table 表名 drop 字段名;
4 修改字段
	alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
	alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
'''

9. 复制表

sql语句查询的机构其实也是一张虚拟表

create table 表名 select * from 旧表;   不能复制主键 外键