mysql-3笔记

约束条件

default默认值

create table t1(
    id int,
    gender enum('male','female','others') default 'male'
);
 #插入一条语句，不填gender值，默认为male
   insert into t1(id) values(1);

unique唯一

#单列唯一
create table t3(
    id int unique,
    name char(16)
);
insert into t3 values('1','jason'),('1','egon'); #报错
insert into t3 values('1','jason'),('2','egon'); #正常
#联合唯一
"""
例如ip和port
单个都可以重复，但加载在一起必须是唯一的
"""
create table t4(
    id int,
    ip char(16)
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080);  #报错

primary key 主键

"""
1. 但从约束效果上来看，primary key等价于 not null + unique
非空且唯一
"""
create table t5(id int primary key);
  insert into t5 values(unll);  #报错
  insert into t5 values(1),(1);  #报错
  insert into t5 values(1)(2); #正常
"""
2. 它除了有约束效果之外，它还是innodb存储引擎组织数据的依据
 innodb存储引擎在创建表的时候必须要有primary key
 因为它类似于书的目录， 能够帮助提示查询效率并且也是建表的依据
"""
  #（1） 一张表中有且只有一个主键， 如果你没有设置主键，那么会从上往下搜索知道遇到一个非空且唯一的字段，它将自动升级为主键
 create table t6(
     id int,
     name char(16),
     age int not null unique,
     addr char(32) not null unique
 );
  #（2） 如果表中没有主键，也没有其他任何的非空唯一字段，那么innodb会采用自己内部提供的一个隐藏的字段作为主键；隐藏意味着无法使用到它，就无法提升查询速度
  #(3) 一张表中通常都应该有一个主键字段， 并且通常将id(uid/sid)字段作为主键
  create table t5(
      id int primary key
    name char(16)
  );
  #联合主键(多个字段联合起来作为表的主键，本质还是一个主键)
  create table t6(
      ip char(16),
    port int,
    primary key(ip,port)
  );
  """
  也就意味着  以后在创建表的时候，id字段一定要加primary key
  """

auto_increment 自增

#当编号特别多的时候，人为的去维护太麻烦
create table t8(
    id int primary key  auto_increment,
    name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');
 #注意auto_increment 通常都是加在主键上的，不能给普通字段加

结论

"""
以后再创建表的id（数据的唯一标识id/uid/sid)字段的时候
 id int primary key  auto_increment 
"""

补充

delete from 指令 在删除表中数据的时候，主键的自增不会停止
truncate t1 #清空t1表数据并且重置主键

表之间建关系

外键

#用来帮助我们建立表与表之间关系的
  foregin key

表关系

"""
表与表之间最多只有四种关系
    一对多关系
    一对一关系
    多对多关系
    没有关系
"""

一对多关系

foreign key
  1. 一对多表关系，外键字段建在'一对多'中的'多'一方
  2. 在创建表的时候，一定要先建被关联表  
  3. 在录入数据的时候，也必须先录入被关联表
#建部门表
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
#建立员工表
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int，
    foreign key(dep_id) references dep(id) #解释：dep_id是外键，跟dep表的id字段关联
    on update cascade #同步更新
    on delete cascade #同步删除
);
#插入数据
insert into dep(dep_name,dep_desc) values('sb教学部'，'教书育人')，('外交部','多人外交'),('nb技术部','技术能力有限部门')；
insert into emp(name,dep_id) values('jason',2),('egon','1'),('tank','1'),('kevin',3);
#修改emp表中的dep_id或者dep表中的id
update dep set id=200 where id=2;  #不行
#删除dep表里面的数据
delete from dep；  #不行
#修改方式1：先删除教学部对应的员工数据，之后再删除部门
  操作太过繁琐
#修改方式2：真正做到数据之间有关系
  更新就同步更新，删除就同步删除
  """
  级联更新
      on update cascade #同步更新
  级联删除
      on delete cascade #同步删除
  """

多对多关系

#新建图书表
create table book(
    id int primary key auto_increment,
    title varchar(32),  #标题
    price int,  #价格
  """
    author_id int,
    foreign key(author_id) references author(id) #设置外键
    on update cascade
    on delete cascade  
  """
);
#建立作者表
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,  #年龄
 """    
    book_id int,
    foreign key(book_id) references book(id) #设置外键
    on update cascade
    on delete cascade 
 """ 
);
"""
按照上述方式直接创建，两张表都不能成功
针对多对多字段关系 不能在两张原有的表中创建外键
需要单独再开设一张表，专门用来存储两张表数据之间的关系
"""
#建立中间表
create table book2author(
    id int priamary key auto_increment,
    book_id int,
    author_id int，
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade,
    foreign key(author_id) references author(id)  #设置外键
    on update cascade  #同步更新
    on delete cascade  #同步删除
);

一对一关系

"""
如果一个表的字段特别多，每次查询又不是所有的字段都能用得到
将表一分为二
    用户表
        用户表  id  name  age
        用户详情表  id  addr  phone  email。。。。
"""
一对一外键建在任意一方都可以，但建议建在查询频率较高的表中
#新建作者详细信息表
create table authordetail(
    id int primary key auto_increment,
    phone int,
    addr char(32)
);
#建立作者比表
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,  #详细表id列，设置'唯一'约束条件
    foreign key(authordetail_id) references authordetail(id)
    on update cascade
    on delete cascade
);

修改表

#mysql对大小写不敏感
1.修改表名
    alter table 表名 rename 新表名；
2.增加字段
    alter table 表名 add 字段名 字段类型(宽度) 约束条件；
    alter table 表名 add 字段名 字段类型(宽度) 约束条件 first； 
    alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名；
3.删除字段
    alter table 表名 drop 字段名;
4.修改字段
    #修改字段类型
    alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
    #修改字段名称
    alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件；

复制表

"""
我们sql语句查询的结果其实也是一张虚拟表
"""
create table 新表名 select * from 旧表;  #只能复制数据，不能复制主键、外键等key
例：
  create table new——dep2 select * from dep where id>3;