目录
一、外键
在我们创建使用表的时候,有一些缺陷:
1. 表的重点不清晰 ----》 可以忽略
到底是员工表还是部门表
2. 表中相关字段一直在重复存储 ----》 可以忽略
浪费存储空间
3. 表的扩展性极差,牵一发而动全身 ----》 不能忽略
解决方案:将上述一张表拆分成两张表 -----》employee与department
上述三个缺陷全部解决
然而带来了一个小问题,表与表之间的数据没有对应关系了。
外键字段:就是用来标识表与表之间的数据关系,如部门编号
简单的理解为该字段可以让你去到其他表中查找数据
二、表与表之间的关系
一对多
如何判断表关系:换位思考法
以员工表和部门表为例
先站在员工表
问:一个员工能否有多个部门?
答:不能
再站在部门表
问:一个部门能否有多个员工?
答:可以
结论:一个可以,一个不可以,表关系就是:一对多,表关系中没有多对一。
针对于一对多,外键字段要建在多的一方!
如何在SQL层面建立一对多的关系:
关联方式:foreign key
先把基础表的中基础字段建立出来,然后再考虑外键字段
创建表
'员工表'
create table employee(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id) # 让两张表建立了外键关系
on update cascade # 级联更新
on delete cascade # 级联删除
);
'部门表'
create table department(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
往表中录入数据
insert into employee(name, age, dep_id) values('kevin', 20, 1);
insert into department(dep_name,dep_desc) values('人事部', '管理人才');
多对多
以图书表和作者表为例
我们站在图书表的角度
问:一本图书能不能有多个作者?
答:可以
我们再站在作者表的角度
问:一个作者能不能写多本书
答:可以
得出结论:如果两个都可以,那么表关系就是'多对多'
针对于多对多的表关系,外键字段建在第三张表中
在SQL层面建立多对多的表关系
关联方式:foreign key + 一张新的表
创建表
'图书表'
create table book(
id int primary key auto_increment,
title varchar(32),
price decimal(8,2)
);
'作者表'
create table author(
id int primary key auto_increment,
name varchar(32),
addr varchar(32)
);
'图书与作者建立联系表'
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references author(id) # 让两张表建立了外键关系
on update cascade # 级联更新
on delete cascade, # 级联删除
foreign key(author_id) references book(id) # 让两张表建立了外键关系
on update cascade # 级联更新
on delete cascade # 级联删除
);
录入数据
insert into book(title, price) values('水浒传', 1000);
insert into book(title, price) values('西游记', 2000);
insert into author(name, addr) values('张三', '北京');
insert into author(name, addr) values('李四', '上海');
insert into book2author(book_id, author_id) values(1, 1);
insert into book2author(book_id, author_id) values(1, 2);
insert into book2author(book_id, author_id) values(2, 1);
insert into book2author(book_id, author_id) values(2, 2);
一对一
两张表:学生表和客户表
一对一:一个学生是一个客户,一个客户有可能变成一个学校,即一对一的关系。
外键关系建在哪里?
两张表都可以,但是,推荐建在查询频率较高的一张表
在SQL层建立一对一的关系
关联方式:foreign key + unique
'创建客户表'
create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);
'创建学生表'
create table student(
id int primary key auto_increment,
class_name varchar(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
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;
# 增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;
注意事项
1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
如果想要数据之间自动修改和删除需要添加额外的配置
三、多表查询
数据准备
建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
"""如果两张表没有建立强制的约束关系,就使用逻辑意义上的关联"""
插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;
insert into employee(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
| 205 | 保洁 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 38 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+------------+--------+------+--------+
子查询
子查询就是:一条SQL的执行结果就是另外一条SQL的执行条件!即分步操作。
查询kevin的部门名称
1. 应该先查询kevin 的部门编号(部门表的id)
select dep_id from emp where name='kevin';
2. 然后拿着查询出来的部门id去dep表中查询部门名称
select *from dep where id = (select dep_id from emp where name='kevin';);
连表查询(重要)
把多张有关系的表链接成一张大的虚拟表,连接出来的虚拟表不是实际存在的,它是在内存中存储,然后按照单表查询。
专业的连标语法:
- inner join ----> 内连接,查询的是两张表中都有的数据
- left join ----> 左连接,以左表为基准,查询左表中所有的数据,右表没有的数据,使用NULL填充
select * from emp left join dep on emp.dep_id=dep.id;
- right join ----> 右连接,以右表为基准,查询右表中所有的数据,右表没有的数据,使用NULL填充
select * from emp right join dep on emp.dep_id=dep.id;
- union ----> 连接两个SQL语句的结果
- 连表可以连很多张表,不只是两张,大多数都是两张
select * from emp left join dep on emp.dep_id=dep.id inner join A on A.id=dep.A_id where ...;
四、Navicat可视化软件
官网下载:https://www.navicat.com.cn/products/
- Windows版本
- Mac版本
它不是免费的,收费的,所以:
- 花钱去买
- 白嫖
- 免费试用14天
连接mysql命令:mysql -h 127.0.0.1 -P 3306 -u root -p 123456
自己电脑:mysql -u root -p
使用Navicat基本上是不用写SQL语句。