mysql 约束

约束

1. unsigned  无符号
2. not null   不为空
3. 设置默认值
4. 唯一约束
5. primary key   主键
6. auto_increment  自增加1
7. zerofill   零填充
8. !!!  foreign key    外键
9. 表与表之间的关系
10. 联合索引
11. 关于约束的添加和删除
12. mysql  存储引擎

unsigned  无符号

# unsigned      无符号
create table t3(id int unsigned);
insert into t3 values(100);
insert into t3 values(-100); error

not null   不为空

# 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('abc');   error

设置默认值

# default       设置默认值
create table t5(id int not null,name varchar(255) default '高雪峰');
insert into t5 values(1,null);
insert into t5(id) values(2);

唯一约束

数据唯一不重复(可以重复出现 空  null)

索引:相当于字典的目录,通过索引可以加快查询的速度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

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

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;

zerofill   零填充

(配合整型int使用) int(11) , 位数不够11位,拿0补充

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

!!!  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,"yuanweizhuo",88,2);
insert into student1 values(null,"lihuling",99,2);
insert into student1 values(null,"wangwen",18,3);
 
# 删除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,"yuanweizhuo",88,2);
insert into student2 values(null,"lihuling",99,2);
insert into student2 values(null,"wangwen",18,3);
# 联级删除
delete from class2 where id = 2
# 联级更新
update class2 set id = 100  where classname = "python32";

表与表之间的关系

(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

联合索引

主键索引 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); # 注意点,允许插入多个空值;
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) 联合主键
这两个用法一模一样,区别:前者可以继续添加一个主键,后者不能再额外添加主键
主键可以是单个字段,也可以是联合主键,设置多个单字段做主键不行的. 
"""

关于约束的添加和删除

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

mysql  存储引擎

存储引擎 : 存储数据的一种结构方法 　　show engines; 查看所有的存储引擎

 

# 概念:
(1)表级锁: 如果有人修改当前这个表,会直接上锁,其他用户无法进行修改,不能进行高并发.

(2)行级锁: 如果有人修改当前这个表中的一条记录,当前这条数据会被锁定,其他数据仍然可以被修改,速度快,允许高并发

(3)事务处理: 执行sql语句是哦,必须所有的操作全部成功,最终提交数据,否则数据回滚,回到刚开始没操作的那个状态.

(4)begin : 开启事务

(5)commit: 提交数据

(6)rollback: 回滚数据

(7)MyISAM : 支持表级锁(5.6版本前默认存储引擎)

(8)InnoDB : 事务处理,行级锁,外键(5.6版本后默认存储引擎)

(9)MEMORY : 把数据放在内存中,做一个临时的缓存

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

 

返回顶部