表与表建立关系
表与表关系
分析步骤
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
一对一
# 学生表 客户表 一对一
# 一个客户对应一个学生
# 学生表gid foreign key 关联客户表id主键
# 并且gid还要设置为unique
实列
#一对一 先键多的（客户） 在键少的且约束 唯一（学生）
两张表：学生表和客户表
一对一：一个学生是一个客户
关联方式：foreign key+unique
键好表如果删除被关联的会报错 除非设置级联更新 删除
列
delete from customer where id=1;
drop database day01;
create database day01;
use day01;
#创建客户表
create table customer(
id int primary key auto_increment,
name varchar(12) not null,
qq varchar(11) not null,
phone varchar(16) not null
);
#创建学生表
create table student(
id int primary key auto_increment,
class_name varchar(12) not null,
customer_id int unique,#该字段必须唯一
foreign key(customer_id) references customer(id)
);
#增加客户
insert into customer(name,qq,phone) values
('小天','31811231',13811341220),
('小白','123123123',15213146809),
('小久','283818181',1867141331),
('元歌','283818181',1851143312),
('太白','888818181',1861243314),
('关羽','112312312',18811431230);
#增加学生
insert into student(class_name,customer_id) values
('脱产1班',3),
('周末1期',4),
('周末1期',5);
select * from customer;
select * from student;
实列
证明不可以删除被关联的
mysql> delete from customer where id=1;
mysql> delete from customer where id=3;
一对多
# 校区表 一个校区可以有多个班级 一对多
# 班级表 一个班级可不可以对应多个校区
# 校区表 校区id 校区名称 校区城市 校区地址
# 班级表 班级id 班级名称 开班日期 班主任 校区id
# 多(foreign key)关联一这张表
# 班级表创建foreign key关联校区表的校区id字段
先创建1在创建多
不可以删1 只可以删多
多 是1的子集
实列
级和学生
一个班级可以对应多个学生，但一个学生只能对应一个班级
主机和机房
一个机房可以有多台主机，但是一个主机只能属于一个机房
一个出版社可以出版多本书
show databases;
drop database day01;
create database day01;
use day01;
create table press(
id int primary key auto_increment,
name varchar(12) not null
);
insert into press(name) values
('小可爱出版社'),
('爱你呦出版社'),
('么么哒出版社')
;
mysql> select * from press;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | 小可爱出版社 |
| 2 | 爱你呦出版社 |
| 3 | 么么哒出版社 |
+----+--------------------+
create table book(
id int primary key auto_increment,
name varchar(12) not null,
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into book(name,press_id) values
('小黄书',1),
('旧时光',2),
('电影简史',2),
('意林',3),
('知音漫客',2),
('故事会',3)
;
mysql> select * from book;
+----+--------------+----------+
| id | name | press_id |
+----+--------------+----------+
| 1 | 小黄书 | 1 |
| 2 | 旧时光 | 2 |
| 3 | 电影简史 | 2 |
| 4 | 意林 | 3 |
| 5 | 知音漫客 | 2 |
| 6 | 故事会 | 3 |
+----+--------------+----------+
多对多
# 学生表 班级表 多对多
# 站在学生的角度上 一个学生属于多个班级 一对多
# 站在班级的角度上 一个班级可以有多个学生么 多对一
# 学生表 学生id 学生姓名 ...
# 班级表 班级id 班级名称 ...
# 产生第三张表
# 一个字段是外键关联另一个表的主键
# 另一个字段外键关联另一张表的主键
实列
#学生表
create table student(
sid int not null unique auto_increment,
sname varchar(12) not null,
gender enum('男','女')
);
#插入数据
insert into student(sname,gender) values
('小强','男'),
('小艾','女'),
('小九','男');
mysql> select * from student;
+-----+--------+--------+
| sid | sname | gender |
+-----+--------+--------+
| 1 | 小强 | 男 |
| 2 | 小艾 | 女 |
| 3 | 小九 | 男 |
+-----+--------+--------+
#科目表
create table course(
cid int not null unique auto_increment,
cname varchar(12) not null
);
#插入数据
insert into course(cname) values
('生物'),
('体育'),
('物理');
mysql> select * from course;
+-----+--------+
| cid | cname |
+-----+--------+
| 1 | 生物 |
| 2 | 体育 |
| 3 | 物理 |
+-----+--------+
#关联表
create table relevance(
sid int not null unique auto_increment,
student_id int not null,
course_id int not null,
foreign key(student_id) references student(sid),
foreign key(course_id) references course(cid)
);
insert into relevance(student_id,course_id) values
(1,1),
(1,2),
(2,2);
mysql> select * from relevance;
+-----+------------+-----------+
| sid | student_id | course_id |
+-----+------------+-----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
+-----+------------+-----------+
额外知识点
导入其他表数据
insert into class2 select * from class;
课后开胃菜
看图建表
做这个题目前先分析表与表关系
成绩表关联与 课程表 学生表
学生表 关联与班级表
课程表 关联与老师表
所有建表顺序 班级表 老师表 学生表 课程表 成绩表
键表了
#建立一个库
create database cs;
use cs;
#班级表
create table class(
cid int primary key auto_increment,
caption varchar(12) not null unique
);
#插入数据
insert into class(caption) values('三年二班'),('一年三班'),('三年一班');
#查询表
select * from class;
desc class;
show create table class;
#老师表
create table teacher(
tid int not null unique auto_increment,
tname varchar(12) not null
);
#插入数据
insert into teacher(tname) values('太白'),('骚强'),('小江');
select * from teacher;
desc teacher;
#学生表
create table student(
sid int not null unique auto_increment,
sname varchar(12) not null,
gender enum('男','女'),
class_id int not null,
foreign key(class_id) references class(cid)
on delete cascade
on update cascade
);
#插入数据
insert into student(sname,gender,class_id) values
('小强','男',1),
('小艾','女',1),
('小九','男',1);
select * from student;
desc student;
#课程表
create table course(
cid int not null unique auto_increment,
cname varchar(12) not null,
tearch_id int not null,
foreign key(tearch_id) references teacher(tid)
on delete cascade
on update cascade
);
#插入数据
insert into course(cname,tearch_id) values
('生物',1),
('体育',1),
('物理',2);
select * from course;
#成绩表
create table score(
sid int not null unique auto_increment,
student_id int ,
course_id int ,
number int not null,
foreign key(student_id) references student(sid),
foreign key(course_id) references course(cid)
);
insert into score(student_id,course_id,number) values
(1,1,60),
(1,2,59),
(2,2,100);
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 2 | 59 |
| 3 | 2 | 2 | 100 |
+-----+------------+-----------+--------+
建一个 带全部 数据类型 与约束的表 表自定义
create table figure(
id int primary key auto_increment,
name varchar(12) not null unique,
tim time,
sex enum('男','女') default '男',
hobby set('玩','乐'),
payroll float(8,2)
);
insert into figure(name,tim,sex,hobby,payroll) values
('小z','2018-07-05 11:22:22','男','玩,乐',88888.66),
('zz','2018-07-05 11:22:22','男','玩,乐',88888.66);
mysql> select * from d1;
+----+------+----------+------+---------+----------+
| id | name | tim | sex | hobby | payroll |
+----+------+----------+------+---------+----------+
| 1 | 小z | 11:22:22 | 男 | 玩,乐 | 88888.66 |
| 2 | zz | 11:22:22 | 男 | 玩,乐 | 88888.66 |
+----+------+----------+------+---------+----------+