Python进阶----表与表之间的关系(一对一,一对多,多对多),增删改查操作,单表查询,多表查询
一丶表与表之间的关系
背景:
由于如果只使用一张表存储所有的数据,就会操作数据冗余,也会操作数据库查询效率低下等问题,所以会把一张表分成多个表. 但是表与表之间的关系就需要被,否则在创建数据库表时,思维混乱,导致项目崩溃.
表与表之间存在三种关系:
1.一对一
2.一对多
3.多对多
如何找出表与表之间关系:
分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
一对一:
含义:
1.将一对一的情况,当作是一对多情况处理,在任意一张表添加一个外键,并且这个外键要唯一,指向另外一张表主键.
2.直接将两张表合并成一张表将两张表的主键建立起连接,
3.让两张表里面主键相等
关联方式：foreign key+unique
案例:
学生和客户,班级和班长, 公民和身份号码,国家和国旗都是一对一的关系
# 1. 创建表
create table customer0(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);
create table student0(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer0(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
# 查看student表的详细信息
#增加客户
mysql> insert into customer0(name,qq,phone) values
('韩蕾','31811231',13811341220),
('杨澜','123123123',15213146809),
('翁惠天','283818181',1867141331),
('杨宗河','283818181',1851143312),
('袁承明','888818181',1861243314),
('袁清','112312312',18811431230);
mysql> #增加学生
mysql> insert into student0(class_name,customer_id) values
('脱产1班',3),
('周末1期',4),
('周末1期',5);
一对多***:
含义:
1.A表中的某条数,可以被B表关联N条
2.在多的一方添加一个外键,指向一的一方的主键
案例:一个出版社可以出版多本书
关联方式：foreign key
# 1 . 创建出版社表
create table press(
id int primary key auto_increment,
name varchar(20)
);
# 2. 创建图书表
create table books1(
bid int primary key auto_increment,
name char(10),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade on update cascade
);
# 3. 查看books表详细结构
mysql> show create table books;
`name` char(10) DEFAULT NULL,
`press_id` int(11) NOT NULL,
PRIMARY KEY (`bid`),
KEY `press_id` (`press_id`),
CONSTRAINT `books_ibfk_1` FOREIGN KEY (`press_id`) REFERENCES `press` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
# 4. 增加数据
# press表
mysql> insert into press(name) values('北京工业书出版社'),('人民出版社');
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
# books表
mysql> insert into books(name,press_id) values('金美瓶梅美',1),('精益求精',2);
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
多对多***:
含义:
1.引入第三张的概念,
2. 建立一张中间表,将多对多的关系,拆分成一对多的关系,中间表至少要有两个外键,分别指向原来的那两张表
关联方式：
foreign key + 一张新的表
# 1. 创建图书表
create table books1(
bid int primary key auto_increment,
name char(10)
);
# 2. 创建作者表
create table author(
aid int primary key auto_increment,
name varchar(20)
);
# 3. 第三张表
create table au_bo(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
# 给外键起名 ,fk_book ,
constraint fk_book foreign key(book_id) references books1(bid)
on update cascade
on delete cascade,
# 给外键起名 ,fk_auto ,
constraint fk_auto foreign key(author_id) references author(aid)
on update cascade
on delete cascade,
primary key(author_id,book_id)
);
# 4. 查看au_bo表结构
mysql> show create table au_bo;
| au_bo | CREATE TABLE `au_bo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`author_id` int(11) NOT NULL,
`book_id` int(11) NOT NULL,
PRIMARY KEY (`author_id`,`book_id`),
UNIQUE KEY `id` (`id`),
KEY `fk_book` (`book_id`),
CONSTRAINT `fk_auto` FOREIGN KEY (`author_id`) REFERENCES `author` (`aid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_book` FOREIGN KEY (`book_id`) REFERENCES `books1` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
# 5. 增加数据
mysql> insert into au_bo(author_id,book_id) values(1,1),(1,2),(2,1),(2,2),(3,2),(3,4);
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 3 | 2 |
| 7 | 3 | 4 |
+----+-----------+---------+
二丶增删改查操作
表操作
1.创建表 create table 表名
2.删除表 drop table 表名
3.查看表结构 desc 表名 / show create table 表名
4.修改表如下