mysql将一个表的数据更新到另一个表_数据分析Day06

# 数据库day06

## 昨日回顾

```mysql
# 外键: 用于将一张表与领一张表绑定关系的，关联表

# 所有数据存放在一张表中，会有什么问题？
1.浪费空间
2.扩展性差
3.结构不清晰

# 将一张表拆分成多张表（可以是两张也可以是三张）

# 拆分表的关系

# 一对多
1.员工表与部门表，我们应该站在两张表的角度去思考
	- 员工表: 一个员工是否可以同时在多个部门？不可以，一个员工只能在一个部门;      一
    - 部门表: 一个部门是否可以有多个员工？可以;     多
        
    员工表与部门表是  "一对多" 的关系
    
2.学生表与班级表，同上;
	- 学生表:  一个学生能够在多个班级中？ 不可以;     一
        
        # 关联表
        - 来了一位同学，插入一个学生的记录(数据)，绑定一个班级foreign key;
        
    - 班级表:  一个班级能够有多个学生？ 可以;      多
        
        # 被关联表
       	- 事先创建好班级
        
    学生表与班级表的关系是 "一对多";
    
    
# 注意: ; 号是结束符号
3.foreign key：外键
	1) 确保mysql服务端是正常运行
    2) 要连接mysql客户端;
    	# 语法
    	'''
    	cmd--> mysql -u 用户名 -p 回车
        输入密码: 密码
    	'''
        mysql -uroot -p
        123
     3) 创建库（类似于创建文件夹）
    	# create database 库名 charset='utf8';
        create database day06_db charset='utf8';
     
     4) 切换到指定的库
        use 库名;
	 
     5) 创建表
        - 先创建被关联表（班表）
        	create table class_table(
                id int primary key auto_increment,
                name varchar(16) not null
            );
        
        
        - 再创建关联表（学生表）
        	create table student(
            	id int primary key auto_increment,
                name varchar(16) not null,
                gender enum('male', 'female') default 'male',
                age int,
                # 外键
                c_id int,
                # 将当前student表的中c_id字段，设置为外键，关联给另一张表中的字段id
                # 语法: foreign key(c_id) references 表名(关联的字段id)
                foreign key(c_id) references class_table(id)
            );
         
    - 插入数据
    
    	- 先插入被关联表数据
        	insert into class_table(name) values('数据分析班'),('python15期');
        
        - 再插入关联表数据
        	insert into student(name, gender, age, c_id) values('车向林', 'male', 95, 1);

# 多对多

# 一对一
```





## 今日内容

```python
# 一对多
	- 级联更新
    - 级联删除

# 多对多

# 一对一
```


```mysql

# 当我想删除dep表的外交部的时候，也无法删除
# 删除dep表中id为1的记录
delete from dep where id=2;  # 无法删除，报错

# 方式1:先删除员工表中外交部对应的所有的员工，再删除外交部
delete from emp where id=7;
delete from dep where id=2;


# 方式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);

# 查看两张表数据
select * from emp;
select * from dep;

# 删除部门后，对应的部门里面的员工表数据对应删除
delete from dep where id=3;

# 更新部门后，对应员工表中的标示部门的字段同步更新
update dep set id=3 where id=2;

```

## 多对多

```mysql
# 图书表与作者表之间的关系
"""
仍然站在两张表的角度：
1.站在图书表：一本书可不可以有多个作者，可以！那就是书多对一作者
	- 多
	
	1 围城  ---> foreign key(a_id) 1
	2 墨菲定律 
	
2.站在作者表：一个作者可不可以写多本书，可以！那就是作者多对一书
双方都能一条数据对应对方多条记录，这种关系就是多对多！
	- 多
	
	1  tank  ---> foreign key(book_id) 2
	
图书表与作者表是 多对多 的关系;
"""
# 先来想如何创建表？图书表需要有一个外键关联作者，作者也需要有一个外键字段关联图书。问题来了，先创建谁都不合适！如何解决？

# 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id

# 1) 先创建作者表
create table author(
    id int primary key auto_increment,
    name char(16)
);

# 2) 创建图书表
create table book(
    id int primary key auto_increment,
    bname char(16),
    price int
);

# 给各自表插入数据
insert into author(name) values
('tank'),
('jason'),
('egon');

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

# 3) 创建第三张关联表
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);
```

## 一对一

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

```python
# 左表的一条记录唯一对应右表的一条记录，反之也一样
- 客户表
# 创建客户表
# 被关联表
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
);

insert into customer(name, qq, phone) values('学生1', '12145124', '12412421421');

insert into student(class_name, customer_id) values('数据分析班', 1);

# 报错
insert into student(class_name, customer_id) values('python10期', 1);


# 三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制即可做出三种关系
```

## 修改表  了解

```mysql
# 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 旧字段名 新字段名 新数据类型 [完整性约束条件…];
```

## 复制表

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

# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table 新的表名 select * from 已存在的表名;

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

# 今日作业: 作业布置

1）课上例子3遍以上

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


# 答案
```python
#用户表： id ，username，password，
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)
);
```

3）做练习:

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



4）总结并发布知乎文章