爱站程序员基地目录
part1:数据类型
part2:约束
part3:主键索引 PRI &唯一索引 UNI &普通索引 MUL
part4:外键:foreign key
part5:在外键中设置联级删除和联级更新
part6:表和表之间的关系
part7:存储引擎
part8:关于约束的添加和删除
附:day37+day38思维导图
part1:数据类型
date YYYY-MM-DD 年月日 (结婚纪念日,节假日)
time HH:MM:SS 时分秒 (体育竞赛)
year YYYY 年份值 (历史,酒的年份)
datetime YYYY-MM-DD HH:MM:SS 年月日 时分秒 (用户登录时间,下单时间)
create table t1(d date , t time , y year , dt datetime);insert into t1 values(\"2020-08-25\",\"08:25:30\",\"2020\",\"2020-08-25 08:25:30\")insert into t1 values(now(),now(),now(),now())
t1表中内容如下所示
timestamp YYYYMMDDHHMMSS(时间戳) 自动更新时间(不需要手动,系统自动更新时间) 数据上一次的修改时间
create table t2(dt datetime , ts timestamp);insert into t2 values(null,null);insert into t2 values(20200825082530,20200825082530);insert into t2 values(20200825082530,20380825082530); # error 不能超过2038年的某一年
t2表中内容如下所示
part2:约束
unsigned 无符号
not null 不为空
default 设置默认值
unique 唯一约束,数据唯一不重复
primary key 主键,标记数据的唯一特征(唯一且不为空)
auto_increment 自增加1(一般配合主键使用, 或 unique进行自增)
zerofill 零填充(配合整型int使用) int(11) , 位数不够11位,拿0补充
foreign key 外键,把多张表通过一个关联字段联合在一起,(这个字段可以加外键)
1.unsigned 无符号
# unsigned 无符号create table t3(id int unsigned);insert into t3 values(100);insert into t3 values(-100); error
下图是t3表的详情参数
2.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 id不可为空insert into t4(name) values(\'abc\'); # error id不可为空
下图是t4表的详情参数
3.default 设置默认值
# default 设置默认值create table t5(id int not null,name varchar(255) default \'高雪峰\');insert into t5 values(1,null);insert into t5(id) values(2);
下图是t5表的详情参数
下图是t5表的存储内容
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 id是唯一约束，不能有两个1insert into t6(id) values(null);insert into t6(id) values(null);
下图是t6表的详情参数
5.primary key 主键,标记数据的唯一特征(唯一且不为空)
1.PRI 主键 非空且唯一 在一个表里只能有一个主键
not null unqiue约等于primary key
create table t7(id int not null unique , name varchar(255) default \'戈隆\');insert into t7 values(1,\"1122\")insert into t7 values(null,\"1122\") # error id不能为空
下图是t7表的详情参数
2.primary key 创建主键
create table t8(id int primary key , name varchar(255) default \'libolun\' );insert into t8 values(1,\"ppp\")
下图是t8表的详情参数
3.两者同时存在 (优先显示primary key 作为主键,另一个设置成UNI 唯一索引)
create table t9(id int primary key , name char(3) not null unique);
下图是t9表的详情参数
4.一个表里只能有一个主键
create table t10(id int primary key , name char(3) primary key); error
当创建表中有多个主键，会出现如下错误提示
6.auto_increment 自增加1(一般配合主键使用, 或 unique进行自增)
create table t11(id int primary key auto_increment , name varchar(255) default \'Alan\')insert into t11 values(1,\"ad8Wuming\")insert into t11 values(null,\"Huahai\")insert into t11(id) values(null)# 使用默认值自动插入insert into t11 values()# delete 只删除数据,id号保留delete from t11 ;# truncate 删除所有数据 + 重置idtruncate table t11;
下图是t11表的详情参数
下图是t11表的存储内容
7.zerofill 零填充(配合整型int使用) int(11) , 位数不够11位,拿0补充
create table t12(id int(8) zerofill);insert into t12 values(2)insert into t12 values(123456789)
下图是t12表的详情参数
下图是t12表中的存储内容
part3:主键索引 PRI &唯一索引 UNI &普通索引 MUL
1.联合唯一约束(字段都设置成not null + unique 显示PRI , 联合在一起表达一种唯一性)
格式：unique(字段1,字段2,字段3 … ) 把多个字段拼在一起表达唯一的数据
create table t1_server(id int , name varchar(255) not null,ip char(15) not null,port int not null , unique(ip,port));insert into t1_server values(1,\"564aaa\",\"192.168.1.1\",3306);insert into t1_server values(1,\"aaa\",\"192.168.1.1\",3306); # error ip,port为联合唯一索引，不可重复insert into t1_server values(1,\"aaa\",\"192.168.1.1\",443);insert into t1_server values(1,\"aaa\",\"192.168.1.255\",443);
下图是t1_server表的详情参数
下图是t1_server表的存储内容
2.联合唯一约束(字段不设置成not null)
create table t2_server(id int ,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); # 注意点,允许插入多个空值;insert into t2_server values(1,\"aaa\",null,null);insert into t2ad8_server values(1,\"aaa\",null,null);insert into t2_server values(1,\"aaa\",null,null);insert into t2_server values(1,\"aaa\",null,null);
下图是t2_server表的详情参数
下图是t2_server表的存储内容
3.联合唯一索引 和 主键 之间是否可以同时存在?
unique(ip,port) 联合唯一索引
primary key(ip,port) 联合主键
这两个用法一模一样,区别:前者可以继续添加一个主键,后者不能再额外添加主键
主键可以是单个字段,也可以是联合主键,设置多个单字段做主键不行的.
create table t3_server(id int , name varchar(255) not null,ip char(15) not null,port int not null , unique(ip,port));
现在t3_server表的详情参数是这样的
如果现在设置id是主键
alter table t3_server add primary key(id);
part4:外键:foreign key
外键:把多张表通过一个关联字段联合在一起,(这个字段可以加外键) [可设置成联级更新和删除]
要注意的是:外键所关联的其他字段必须具有唯一属性 unique 或者 primary key
创建class表:
# 创建class1create table class1(id int , classname varchar(255))# 删除索引alter table class1 drop index id# 添加索引alter table class1 add unique(id);
创建student表，并且在class1表和student1表中添加数据
# 创建student1create 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;
part5:在外键中设置联级删除和联级更新
联级删除 on delete cascade
联级更新 on update cascade
# 创建class2create table class2(id int unique , classname varchar(255)) ;# 创建student2create 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\";
part6:表和表之间的关系
1.一对一 : 表1 id z1 z2 z3 .. 表2 id z4 z5 z6 (可以设置z3为关联字段且唯一 , 关联表2中的唯一一个id)
2.一对多 或者 多对一 : 一个班级里面可以由多个学生,在学生表中创建一个关联字段,关联班级,把关联字段设置成外键,去存储班级的id
3.多对多 : 一个学生可以学习多个学科,一个学科也可以被多个学生学习。一本书可以被多个作者共同撰写,一个作者也可以写多本书.
如果表和表是多对多的话，需要注意设置第三张关系表
part7:存储引擎
show engines：查看所有的存储引擎
一些相关的概念：
1.表级锁: 如果有人修改当前这个表,会直接上锁,其他用户无法进行修改,不能进行高并发.
2.行级锁: 如果有人修改当前这个表中的一条记录,当前这条数据会被锁定,其他数据仍然可以被修改,速度快,允许高并发
3.事务处理: 执行sql语句时,必须所有的操作全部成功,最终提交数据,否则数据回滚,回到刚开始没操作的那个状态.
begin : 开启事务
commit: 提交数据
rollback: 回滚数据
常见的四种存储引擎：
1.MyISAM : 支持表级锁(5.6版本前默认存储引擎)
2.InnoDB : 事务处理,行级锁,外键(5.6版本后默认存储引擎)
3.MEMORY : 把数据放在内存中,做一个临时的缓存
4.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 表结构内存中不存储任何值
part8:关于约束的添加和删除
# 关于约束的添加和删除# 1 添加/删除 约束 not null#alter table 表名 modify 字段名 类型alter table t1 modify id int not nullalter 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) #添加
附:day37+day38思维导图
