表的关系对应

回顾
字段类型
约束条件
创建表的完整语法
create table 表名（
字段名 字段类型[(宽度)约束条件]，
字段名 字段类型[(宽度)约束条件]，
字段名 字段类型[(宽度)约束条件]，
字段名 字段类型[(宽度)约束条件]，
字段名 字段类型[(宽度)约束条件]
）；

注意点

1.同一张表内，字段名不可以重复
2.字段名和字段类型是必须的，宽度和约束条件是可选的
3.最后一个字段不能加逗号

宽度：对存储数据的限制
char(1) 只存1

约束条件与字段类型
字段类型：用来约束该字段存储的数据的类型
约束条件：基于字段类型之上附加的额外的限制条件

整型：tinyint smallint int bigint
默认都是有正负号的

int(8)
唯独整型字段宽度限制的不是存储宽度，而是显示宽度
id int(8) zerofill

浮点型：float double decimal
能够存储的范围
float(255,30)
double(255,30)
decimal(65,30)
相同点都能存小数，但是精度不一样
精度从下往上依次增加
float
double
decimal

补充：可以用字符串来存整型，浮点型相关类型的数据，保证精度准确

字符类型*****
char() :定长
varchar() ：变长

都是用来存储字符串的
char(4):如果超过了4位直接报错，不超过4位，也按照4位存储，默认空格填充
varchar(4):如果超过了4位直接报错，不超过4位，按照真实长度存储

varchar 与 char 的区别
char
    优点：存取速度都快，存取简单暴力，都是固定存取
    缺点：浪费硬盘空间
varchar
    报头是必须的，用来描述后面存储数据的真实长度
    优点：节省空间
    缺点：存取速度都慢

日期类型
date * 2019-05-14
datetime * 2019-05-14 11:11:11
time
year

枚举与集合
枚举：多选一
例如获取用户的性别
gender enum('male','female','others')

集合：多选多但是也可以就选一个
hobby set('read','sing','dj')

约束条件
not null
default

primary key 主键
限制条件上 ==》not null+unique
auto_increment  #自增  只能记在被设置位key的字段



#以后创建主键的时候，固定写法
id int primary key auto_increment
insert into t1(name,password) values('egon','123')

联合主键
primary key(name,password)

unique
单列唯一
联合唯一：服务器的IP+port
host int,
port int,
unique(host,port)

删除表的数据
delete table

清空表
truncate 表名

外键

前戏之一对多关系

# 定义一张部门员工表
id		name		gender		dep_name		dep_desc
1			jason			male			教学部				教书育人
2			egon			male			外交部				漂泊游荡
3			tank			male			教学部				教书育人
4			kevin			male			教学部				教书育人
5			owen			female		技术部				技术能力有限部门
"""
把所有数据都存放于一张表的弊端
1.组织结构不清晰
2.浪费硬盘空间
3.扩展性极差
"""
# 上述的弊端产生原因类似于把代码全部写在一个py文件中，你应该怎么做？>>>解耦合！将上述一张表拆成员工和部门两张表！
# 类似的表关系学生与班级，也是如此，一张学生表和一张班级表

# 分析表数据之间的关系：多个用户对应一个部门，一个部门对应多个用户。禁止一个用户对应多个部门这种情况是另外一张表关系


# 如何查找表与表之间的关系
"""
老师与课程表
1.站在老师表的角度：一名老师能否教授多门课程(限制死，不能，一名老师只能教python，不能同时教python和linux)
2.站在课程表的角度：一门课程能否可以被多个老师教,完全可以！
那就是课程表多对一老师表，如何表示这种关系？在课程表中创建一个字段(tea_id)指向老师表的id字段

学生与班级表
1.站在学生表的角度：？？？
2.站在班级表的角度：？？？
那就是学生表多对一班级表，如何表示这种关系？在学生表中创建一个字段(class_id)指向班级表的id字段
"""

# 再回过头来看员工与部门表，我员工表里面的dep_id我可以随意更改，但是应该有一个强制限制，限制dep_id字段必须只是部门表已有的id字段才合理

一对多(Foreign Key)

# foreign key会带来什么样的效果？
# 1、在创建表时，先建被关联的表dep，才能建关联表emp
create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
# 2、在插入记录时，必须先插被关联的表dep，才能插关联表emp
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');

insert into emp(name,gender,dep_id)  values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);

# 当我想修改emp里的dep_id或dep里面的id时返现都无法成功
# 当我想删除dep表的教学部的时候，也无法删除
# 方式1:先删除教学部对应的所有的员工，再删除教学部
# 方式2:受限于外键约束，导致操作数据变得非常复杂，能否有一张简单的方式，让我不需要考虑在操作目标表的时候还需要考虑关联表的情况，比如我删除部门，那么这个部门对应的员工就应该跟着立即清空

# 先把之前创建的表删除，先删员工表，再删部门表，最后按章下面的方式重新创建表关系
# 3.更新于删除都需要考虑到关联与被关联的关系>>>同步更新与同步删除
create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade
    on delete cascade
);
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');

insert into emp(name,gender,dep_id)  values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);

# 删除部门后，对应的部门里面的员工表数据对应删除
# 更新部门后，对应员工表中的标示部门的字段同步更新

多对多

# 图书表与作者表之间的关系
"""
仍然站在两张表的角度：
1.站在图书表：一本书可不可以有多个作者，可以！那就是书多对一作者
2.站在作者表：一个作者可不可以写多本书，可以！那就是作者多对一书
双方都能一条数据对应对方多条记录，这种关系就是多对多！
"""
# 先来想如何创建表？图书表需要有一个外键关联作者，作者也需要有一个外键字段关联图书。问题来了，先创建谁都不合适！如何解决？
# 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id
create table author(
    id int primary key auto_increment,
    name char(16)
);

create table book(
    id int primary key auto_increment,
    bname char(16),
    price int
);

insert into author(name) values
('egon'),
('alex'),
('wxx')
;
insert into book(bname,price) values
('python从入门到入土',200),
('葵花宝典切割到精通',800),
('九阴真经',500),
('九阳神功',100)
;

create table author2book(
    id int primary key auto_increment,
    author_id int,
    book_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
);

insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4);

一对一

客户表和学生表(老男孩的客户与学生之间,报名之前都是客户，只有报了名的才能是学生)

# 左表的一条记录唯一对应右表的一条记录，反之也一样

create table customer(
    id int primary key auto_increment,
    name char(20) not null,
    qq char(10) not null,
    phone char(16) not null
);

create table student(
    id int primary key auto_increment,
    class_name char(20) not null,
    customer_id int unique, #该字段一定要是唯一的
    foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
    on delete cascade
    on update cascade
);
# 三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制即可做出三种关系

修改表

# mysql对大小写不敏感！！！
语法：
1. 修改表名  
      ALTER TABLE 表名 
                          RENAME 新表名;
2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;                       
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;
4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

# 查询语句执行的结果也是一张表，可以看成虚拟表

# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from service;

# 只复制表结构
select * from service where 1=2;        //条件为假，查不到任何记录

create table new1_service select * from service where 1=2;  

create table t4 like employees;

作业布置

练习：账号信息表，用户组，主机表，主机组

#用户表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);

#用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);

#主机表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);

#业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);

#建关系：user与usergroup

create table user2usergroup(
id int not null unique auto_increment,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);

#建关系：host与business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);

#建关系：user与host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);

练习:

# 班级表
cid	caption
# 学生表
sid sname gender class_id
# 老师表
tid	tname
# 课程表
cid	cname	teacher_id
# 成绩表
sid	student_id course_id number