day38:MySQL数据库之约束&索引&外键&存储引擎

    create table t1(d date,t time,y year,dt datetime);
    insert into t1 values("2020-08-25",08:25:3020202020-08-25 08:25:30")
    insert into t1 values(now(),now(),now())

    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年的某一年

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

 not null      不为空
    create table t4(id int not null,name varchar(255));
    insert into t4 values(1,1)">宋云杰);
    insert into t4 values(null,");  error id不可为空
    insert into t4(name) values('abc');   error id不可为空

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

    create table t6(id int unique,1)">戈隆);
    insert into t6(id) values(1);
    insert into t6(id) values(1);  error id是唯一约束，不能有两个1
    insert into t6(id) values(null); 
    insert into t6(id) values(null); 

    create table t7(id int not null unique,1)">);
    insert into t7 values(1,1)">1122)
    insert into t7 values(null,1)">")  error id不能为空

    create table t8(id int primary key,1)">libolun );
    insert into t8 values(1,1)">ppp")

    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

    create table t11(id int primary key auto_increment,1)">Alan)
    insert into t11 values(1,1)">Wuming)
    insert into t11 values(null,1)">Huahai)
    insert into t11(id) values(null)    
     使用默认值自动插入
    insert into t11 values()
    
     delete 只删除数据,id号保留
    delete from t11 ;
     truncate 删除所有数据 + 重置id
    truncate table t11;

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

    create table t1_server(id int,name varchar(255) not null,unique(ip,port));
    insert into t1_server values(1,1)">aaa192.168.1.1);
    insert into t1_server values(1,3306);  error ip,port为联合唯一索引，不可重复
    insert into t1_server values(1,443); 
    insert into t1_server values(1,1)">192.168.1.255

下图是t1_server表的存储内容

2.联合唯一约束(字段不设置成not null)

    create table t2_server(id int,ip char(15),port int,port));
    insert into t2_server values(1,1)">192.168.65.135);
    insert into t2_server values(1,null,null);  注意点,允许插入多个空值;
    insert into t2_server values(1,null);
    insert into t2_server values(1,null);

下图是t2_server表的详情参数

下图是t2_server表的存储内容

3.联合唯一索引 和 主键 之间是否可以同时存在?
 unique(ip,port) 联合唯一索引
 primary key(ip,port) 联合主键
这两个用法一模一样,区别:前者可以继续添加一个主键,后者不能再额外添加主键
 主键可以是单个字段,也可以是联合主键,设置多个单字段做主键不行的.

    create table t3_server(id int,port));


现在t3_server表的详情参数是这样的

如果现在设置id是主键

    alter table t3_server add primary key(id);

?


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

     创建class1
    create table class1(id int,classname varchar(255))
    
     删除索引
    alter table class1 drop index id

     添加索引
    alter table class1 add unique(id);


创建student表，并且在class1表和student1表中添加数据

     创建student1
    create table student1(
    id int primary key auto_increment,name varchar(255 添加数据
    insert into class1 values(1,1)">python30);
    insert into class1 values(2,1)">python31);    
    insert into class1 values(3,1)">python32);
        
    insert into student1 values(null,1)">yuanweizhuo);
    insert into student1 values(null,1)">lihulingwangwen);
    
     删除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

     创建class2
    create table class2(id int unique,1)">))    ;
        
     创建student2
    create table student2(
    id int primary key auto_increment,foreign key(classid) references class2(id) on delete cascade on update cascade
    );
        
     添加数据
    insert into class2 values(1,1)">);
    insert into class2 values(2,1)">);    
    insert into class2 values(3,1)">);
        
    insert into student2 values(null,1)">);
    insert into student2 values(null,1)">);

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


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


附:day37+day38思维导图
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!