MySQL-约束条件_记录相关操作_表与表之间的三种关系

MySQL

1. 约束

对要编辑的数据进行类型上的限制 , 不满足限制条件的数据不允许操作

unsigned         无符号
not null         不为空
default          设置默认值
unique           唯一约束,数据唯一不重复
primary key      主键,标记数据的唯一特征(唯一且不为空)
auto_increment   自增加一(一般配合主键使用,或 unique 进行自增)
zerofill         零填充(配合整型int使用) int(11) 位数不够11位,拿0补全
foreign key      外键,把多张表通过一个关联字段联合在一起,这个字段可以加外键,可设置成联级更新和联级删除 

1. unsigned

create table t3(id int unsigned);
insert into t3 values(100);
insert into t3 values(-100); error

2. not null

create table t4(id int not null , name varchar(255));
insert into t4 values(1,'小明');
insert into t4 values(null,'小明'); error
insert into t4(name) values('小花'); error

3. default

create table t5(id int not null , name varchar(255) default '朱厚照');
insert into t5 values(1,null);
insert into t5(id) values(2);

4. unique

'''
索引: 相当于字典的目录,通过索引可以加快查询的速度
UNI 唯一索引,允许插入NULL空值
'''

create table t6(id int unique , name varchar(255) default '沈腾');
insert into t6(id) values(1);
insert into t6(id) values(1); error
insert into t6(id) values(null); ok
insert into t6(id) values(null); ok

5. primary key

# PRI 主键 非空且唯一 在一个表里只能有一个主键
create table t7(id int not null unique , name varchar(255) default '马丽');
insert into t7 values(1,'2222');
insert into t7 values(null,'2222'); error

# primary key 创建主键
create table t8(id int primary key , name varchar(255) default '张飞');
insert into t8 values(1,'ppp');

# 两者同时存在 (优先显示primary key 作为主键,另一个设置成UNI 唯一索引)
create table t9(id int primary key , name char(3) not null unique);

# 一个表里只能有一个主键
create table t10(id int primary key , name char(3) primary key); error

6. auto_increment

create table t11(id int primary key auto_increment , name varchar(255) default '孙志和');
insert into t11 values(1,'李柏林');
insert into t11 values(null,'李亚');
insert into t11(id) values(null);
# 使用默认值自动插入
insert into t11 values();

# delete 只删除数据 , id保留
delete from t11;
# truncate 删除所有数据 + 重置id
truncate table t11;

7. zerofill

create table t12(id int(8) zerofill);
insert into t12 values(2);
insert into t12 values(123456789);

8. foreign key

# 外键所关联的其他字段必须具有唯一属性 unique 或者 primary key

	student1:
		id   name          age   address   classid
		 1   yuanweizhuo   88    天堂      2
		 2   lihuling      99    地府      2
		 3   wangwen       18    包头      3
		
	class1:
		id  classname        datetime
		1   python30   2020-4-28 10:10:10
		2   python31   2020-7-9  10:10:10
		3   python32   2020-9-5  10:10:10
                
    # 创建class1
    create table class1(id int , classname varchar(255));
    # 删除索引
    alter table class1 drop index id;
    # 添加索引
    alter table class1 add unique(id);
    
    # 创建student1
    create table student1(
    id int primary key auto_increment,
    name varchar(255),
    age int,
    classid int,
    foreign key(classid) references class1(id)    
    );
    # 添加数据
    insert into class1 values(1,'python30');
    insert into class1 values(2,'python31');
    insert into class1 values(3,'python32'); 
    
    insert into student1 values(null,'xiaoming',18,2);
    insert into student1 values(null,'xiaohong',21,2);
    insert into student1 values(null,'xiaotian',25,2);
    
    # 删除class1里面的python31这个班级 (报错删不掉,因为有其他数据关联该班级)
    delete from class1 where id = 2;
    # 需要先把关联的其他数据都删掉之后再删,才能成功
    delete from student1 where id = 1;
    delete from student1 where id = 2;
    
    # 联级删除 联级更新
    '''
    联级删除 on delete cascade
    联级更新 on update cascade
    '''
    # 创建class2
    create table class2(id int unique , classname varchar(255));
    
    # 创建student2
    create table student2(
    id int primary key auto_increment,
    name varchar(255),
    age int,
    classid int,
    foreign key(classid) references class2(id) on delete cascade on update cascade    
    );
    
    # 添加数据
    insert into class2 values(1,'python30');
    insert into class2 values(2,'python31');
    insert into class2 values(3,'python32'); 
    
    insert into student2 values(null,'xiaoming',18,2);
    insert into student2 values(null,'xiaohong',21,2);
    insert into student2 values(null,'xiaotian',25,2);
    
    # 联级删除
    delete from class2 where id = 2;
    # 联级更新
    update class2 set id = 100 where classname = 'python32';

9. 额外补充

# 关于约束的添加和删除
# 1 添加/删除 约束 not null
	#alter table 表名 modify 字段名 类型
	alter table t1 modify id int not null
	alter table t1 modify id int

# 2 添加/删除 unique 唯一索引
	# alter table 表名 add unique(id)
	alter table t1 add unique(id)
	alter table t1 drop index id
	
# 3 添加/删除 primary key
	# alter table 表名 add primary key(id);
	alter table t1 add primary key(id);
	alter table t1 drop primary key;
	
# 4 添加/删除 foreign key 外键 (show create table student1 找到外键名字,然后再删)
	alter table student1 drop foreign key student1_ibfk_1; #删除
	alter table student1 add foreign key(classid) references class1(id) #添加

2. 联合唯一约束

1. not null + unique

​ 联合唯一约束 : 字段都设置成 not null + unique 显示 PRI , 联合在一起表达一种唯一性

​ unique(字段1 , 字段2 , 字段3 … ) 把多个字段拼在一起表达唯一的数据

create table t1_server(id int , server_name varchar(255) not null , ip char(15) not null,port int not null , unique(ip,port));
insert into t1_server values(1,'aaa','192.168.65.135',3306);
insert into t1_server values(1,'aaa','192.168.65.135',3306); error
insert into t1_server values(1,'aaa','192.168.65.135',3300);
insert into t1_server values(1,'aaa','192.168.65.130',3300);

2. 字段不设置 not null

create table t2_server(id int , server_name varchar(255) not null , ip char(15),port int, unique(ip,port));
insert into t2_server values(1,'aaa','192.168.65.135',3306);
insert into t1_server values(1,'aaa',null,null); # 注意点,允许插入多个空值

mysql> select * from t2_server;
	+------+-------------+----------------+------+
	| id   | server_name | ip             | port |
	+------+-------------+----------------+------+
	|    1 | aaa         | 192.168.65.135 | 3306 |
	|    1 | aaa         | NULL           | NULL |
	|    1 | aaa         | NULL           | NULL |
	|    1 | aaa         | NULL           | NULL |
	|    1 | aaa         | NULL           | NULL |
	+------+-------------+----------------+------+

3. 联合唯一索引和主键

create table t3_server(id int , server_name varchar(255) not null , ip char(15) not null,port int not null , unique(ip,port));
alter table t3_server add primary key(id);
'''
unique(ip,port)      联合唯一索引
primary key(ip,port) 联合主键
这两个用法一模一样
	区别:
		前者可以继续添加一个主键,后者不能再额外添加主键
		主键可以是单个字段,也可以是联合主键,设置多个单字段做主键不行
'''

3. 表与表之间的关系

​ 1. 一对一 : 表1 id z1 z2 z3 … 表2 id z4 z5 z6 (可设置z3为关联字段且唯一 , 关联表2中的唯一一个id)

​ 2. 一对多 或者 多对一 : 一个班级里面可以有多个学生 , 在学生表中创建一个关联字段 , 关联班级 , 把关联字段设置成外键 , 去存储班级id

​ 3. 多对多 : 一个学生可以学习多个学科 , 一个学科也可以被多个学生学习

​ 一本书可以被多个作者共同撰写 , 一个作者也可以写多本书

xueke (表1)
id   name
1    math
2    english
3    wuli

student (表2)
id   name
1    wangwen
2    songyunjie
3    gaoxuefeng

# 表达多对多关系时,需要第三张关系表
relation (表3) 把xid 和 sid 这两个关联字段设置成外键,xid 关联xueke的id , sid关联student 的id

xid  sid
1    1
1    2
1    3
2    1
2    2
2    3
3    1
3    2
3    3

4. 存储引擎

​ 存储数据的一种结构方式

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

# 概念
表级锁: 如果有人修改当前这个表,会直接上锁,其他用户无法进行修改,不能进行高并发.
行级锁: 如果有人修改当前这个表中的一条记录,当前这条数据会被锁定,其他数据仍然可以被修改,速度快,允许高并发
事务处理: 执行sql语句时,必须所有的操作全部成功,最终提交数据,否则数据回滚,回到刚开始没操作的那个状态.
begin : 开启事务
commit: 提交数据
rollback: 回滚数据

MyISAM : 支持表级锁(5.6版本前默认存储引擎)
InnoDB : 事务处理,行级锁,外键(5.6版本后默认存储引擎)
MEMORY : 把数据放在内存中,做一个临时的缓存
BLACKHOLE : 黑洞,产生binlog日志,不产生真实数据
		   用来同步主从数据库中的数据,场景发生在多服务器集群中 (一主一从,一主多从,主数据库:增删改,从数据库:查)

create table myisam1(id int , name varchar(255)) engine = MyISAM;

myisam1.frm  表结构
myisam1.MYD  表数据
myisam1.MYI  表索引

create table innodb1(id int , name varchar(255)) engine = InnoDB;
innodb1.frm  表结构
innodb1.ibd  表数据 + 表索引

create table memory1(id int , name varchar(255)) engine = MEMORY;
memory1.frm 表结构
没有数据文件的,因为所有的数据都临时存储在内存之中

create table blackhole1(id int , name varchar(255)) engine = BLACKHOLE;
blackhole1.frm 表结构
内存中不存储任何值

InnoDB
用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

MyISAM
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

Memory
将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

#查看当前的默认存储引擎:
show variables like "default_storage_engine";
#更改表的存储引擎
alter table t1 engine = innodb;

5. 索引原理

# innodb和myisam的索引的存储方式是不同的
    innodb 存储引擎 索引和数据都存在ibd文件中
    myisam 把所有的索引全部单独存储,就是那个MYI文件

# 索引的数据结构使用的是b+树
    一个叶子节点也叫一个数据页,可以存多条数据,大概16k
    单条数据量越小,叶子节点存的数据量就会越大,需要的叶子节点就越少,
    这样的话,树的高度相对降低一些,查询的速度越快.

6. 联合索引

# 联合索引: 经常查询时候,几个字段要放在一起查,比如找名字:姓+名
name = 'xxx' and email = 'xxx'
create index 索引名 on 表名(字段1,字段2 ... )
create index name_email on t1(name,email);

7. sql 语句优化

(1) 避免使用select *,
(2) 不确定表大小时候,先用count(*)查下数据.
(3) 创建表时尽量使用 char 代替 varchar
(4) 定长的字段放前面,变长的字段放后面.(尽可能小的改变树状结构高度)
(5) 组合索引代替多个单列索引
   （由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
(6) 尽量使用短索引(小数据值)
(7) 重复少的字段值不适合做索引，例：性别不适合
(8) 使用连接（JOIN）来代替子查询(Sub-Queries)

8. 导出数据库

# 导出数据库: 退出mysql时再操作
mysqldump -uroot -p123456 db1 > db1.sql
mysqldump -uroot -p123456 db1 表1 表2 > ceshi001.sql

9. 导入数据库

# 导入数据库: 登入mysql时再操作
source ~/ceshi001.sql