mysql,day03笔记

今日内容概要

• 创建表的完整语法

• 字段的约束条件

  unsigned
  zerofill
  
  not null
  default
  unique
  == primary key ==
  auto_increment
  

• 外键(创建表与表之间的关系)

  一对多
  多对多
  一对一
  无关系
  

  内容详细

创建表的基本语法

create table 表名(
	字段名1 字段类型(数字) 约束条件 约束条件,
	字段名2 字段类型(数字) 约束条件 约束条件,
     字段名3 字段类型(数字) 约束条件 约束条件
);

'''
1.数字和约束条件可选，而字段名和字段类型是必须的
2.最后一行字段定义不加逗号
'''
create table t1(
	id int unsigned,
    name char(5)
);

约束条件

# not null非空
create table t2(
	id int,
    name varchar(32) not null
);
insert into t2(name) values(null);
# 使得name字段下数据非空



# default 默认值
create table t3(
    id int,
    name varchar(32) default 'Jason'
	gender enum('male','female','others') default 'male'
);
# 在不选择性别的时候默认为男性，这样用户注册会更快



# unique 唯一
# 单列唯一
create table t4(
	id int,
    name varchar(32) unique
);
# 联合唯一
create table t5(
	id int,
    name varchar(32),
    port int,
    unique(host,port)
);
# 举例，如端口号、IP地址唯一，两者数据不能出现重复

# primary key 主键
"""
在约束条件上想等于not null + unique非空且唯一
"""
create table t6(
    id int primary key
);
"""
2.在创建表的时候InnoDB引擎规定了一张表必须有且只有一个主键
	+ 如果没有primary key主键 但是有非空且唯一的字段(not null+unique)
	  它会从上往下将第一个非空且唯一的字段自动升级为主键
	+ 如果你没有任何的主键或其他键字段，那么InnooDB会采用内部隐藏的一个字段作为你的表的主键，但是这个隐藏字段你在查询数据的时候是无法使用的
	
主键可以加快查询数据的速度，类似书的目录
"""
creat table t12(
	id int,
    name char(12),
    age int not null unique,
    addr chat(12) no null unique
);
# age字段自动的升级为主键

# 联合主键（了解
create table t13(
	  ip char(16),
      port int,
      primary key(ip,port)
);
'''总结:主键字段一般都用id字段(nid sid uid)'''

# auto_increment自增
create table t14(
	id int primary key auto_inncrement,
    name varchar(14)
);
'''auto_inncrement不能给普通的整型字段使用 一般都是直接给主键使用的'''
总结:
    以后我们使用SQL语句创建表的时候针对主键字段就是下面的固定写法
    id int primary key auto_increment

主键的补充

1.主键的递增不会因为删除数据而变化
	delete from 
2.如果你真的想让主键从头开始计数
	truncate t1

外键

# 创建一张员工表
id   name   age   dep_name   dep_desc

# 上述表的缺点
1.结构不清晰(重点是员工还是部门)
2.浪费存储空间
3.数据的扩展性极差

# 如何解决上述问题
将表进行拆分
id   name   age

id    dep_name   dep_desc

# 数据之间的对应关系
外键字段
	建立表与表之间的关系

一张普通员工表

拆分过后的表

小技巧

判断表与表之间的关系只需要
"""换位思考"""
站在双方的角度思考问题

一对多

1.先站在员工表
	员工表里面的一条数据能否对应部门表里面的多条数据
    翻译:一个员工能否对应多个部门
  	结论:不可以
2.在站在部门表
	部门表里面的一条数据能否对应员工表里面的多条数据
    翻译:一个部门能否有多个员工
     结论:可以
        
换位思考之后发现结论是:一个可以一个不可以
那么员工表与部门表之间的关系就是"一对多"

# 先定义出基表
# 再添加外键
create table emp(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id) 
);
# 声明emp表里面的dep_id字段是外键字段 并且跟dep表里的id字段关联
create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(255)
);
"""
************************************************
针对一对多的外键关系，外键字段建在多的一方
************************************************
"""
insert into dep(dep_name,dep_desc) values('外交部','搞外交'),
('讲师部','教书育人'),('技术部','技术能力有限部门');
insert into emp(name,age,dep_id) values('jason',18,1);
insert into emp(name,age,dep_id) values('egon',73,4);

外键字段的约束

1.在创建表的时候一定要先创建被关联表
2.在录入数据的时候一定要先保证被关联表里面有数据
3.外键字段的值只能是被关联表字段里面出现过的值

级联更新级联删除

1.修改被关联表的被关联字段(不可以)
	update dep set id=200 where id=1;
2.删除被关联表里面的数据项(不可以)
	delete from dep where id=1;
    
 create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);
# 声明emp表里面的dep_id字段是外键字段 并且跟dep表里的id字段关联
create table dep1(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(255)
);

多对多

以书籍表与作者表为例
1.先站在书籍表
	一本书能否对应多个作者
    	可以
2.再站在作者表
	一个作者能否写多本书
    	可以
结论:如果换位思考之后结论是两个都可以
     那么表关系就是多对多
 
create table book(
	id int primary key auto_increment,
    title varchar(32),
    price float(18,2),
    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),
    phone int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
"""
*********************************************
多对多外键字段需要你重新创建一张表来专门记录表关系
*********************************************
"""

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price float(18,2)
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    phone int
);
create table book2author(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
注意事项
1.两张关系表自身不需要做任何的外键字段
2.在录入数据的时候也不需要考虑先录入后录入的问题
3.在针对第三张关系表的时候需要注意约束