section_2.约束\索引\存储引擎

一、约束

"""
unsigned       无符号
not null       不为空
default        设置默认值
unique         唯一约束,数据唯一不重复
primary key    主键,标记数据的唯一特征(唯一且不为空)
auto_increment 自增加1(一般配合主键使用, 或 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,"aaa");
insert into t4 values(null,"aaa"); error
insert into t4(name) values('abc');   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,"1122")
insert into t7 values(null,"1122")

# 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

5.1、auto_increment 自增加1(一般配合主键使用, 或 unique进行自增)

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;

6、zerofill 零填充(配合整型int使用) int(11) , 位数不够11位,拿0补充

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

7、关于约束的添加和删除

-- 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) -- 添加

 

二、索引

"""
主键索引 PRI  唯一索引 UNI  普通索引 MUL
"""

1、联合唯一索引(字段都设置成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",443); 
insert into t1_server values(1,"aaa","192.168.65.130",443); 

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 t2_server values(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) 联合主键
这两个用法一模一样
区别:前者（unique）可以继续添加一个主键,后者（primary key）不能再额外添加主键
主键可以是单个字段,也可以是联合主键,设置多个 单字段 做主键不行的.    
"""

4、foreign key

"""
外键,把多张表通过一个关联字段联合在一起,(这个字段可以加外键) [可设置成联级更新和删除]
外键所关联的其他字段必须具有唯一属性 unique 或者 primary key
"""

4.1、建立一个班级与学生的关联关系

4.1.1、建立班级表

-- 创建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)
);

4.1.2、建立学生表

-- 创建student1
create table student1(
id int primary key auto_increment, 
name varchar(255), 
age int , 
classid int,
foreign key(classid) references class1(id)
);

4.1.3、向班级表插入数据

插入数据：

insert into class1 values(1,"python1");
insert into class1 values(2,"python2");    
insert into class1 values(3,"python3");

4.1.4、向学生表插入数据

插入数据：

insert into student1 values(null,"ywz",88,2);
insert into student1 values(null,"lhl",99,2);
insert into student1 values(null,"ww",18,3);

4.2、删除数据

-- 删除class1里面的python31这个班级  (报错删不掉,因为有其他数据关联该班级)
delete from class1 where id = 2;
-- 需要先把关联的其他数据都删掉之后再删,才能成功
delete from student1 where id = 1;
delete from student1 where id = 2;

4.3、联级删除、联级更新

"""
联级删除 on delete cascade
联级更新 on update cascade
"""

delete from class2 where id = 2
-- 联级更新
update class2 set id = 100  where classname = "python3";

-- cascade 级联方式
-- cascade 方式 在父表上update/delete 级联是，同步update/delete掉子表的匹配
-- 记录外键的级联删除；如果父表中的记录被删除，则字表中对应的记录自动删除
FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
    ON DELETE CASCADE

-- set null 方式
-- set null 在父表上update/delete 记录是，将子表上匹配的记录设为null
-- 例如 删除父表的一个教师，不应该把子表中该教师对应的学生都删除。
FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
    ON DELETE SET NULL

5、测试索引

--创建表
create table t1(id int,name varchar(20));

--存储过程
--指定结束符以什么结尾
delimiter $$
-- procedure 关键字 存储过程 autoinsert定义函数
BEGIN
declare i int default 1;
while(i<500000)do
insert into t1 values(i,'kxq');
set i=i+1;
end while;
END$$

delimiter ;
call autoinsert();

-- 添加索引
create index index_name on t1(id);

三、存储引擎：存储数据的一种结构方式

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 表结构
-- 内存中不存储任何值