【Java从零到架构师第二季】【06】DQL语句主键外键+多表查询

持续学习&持续更新中…

学习态度：守破离

DQL语句主键外键+多表查询

- DQL语句
- - - 什么是DQL语句
    - 聚合函数
    - 常见的WHERe子句
    - - 比较运算
        逻辑运算
        模糊查询
- UNIQUE KEY（索引）
- 主键（PRIMARY KEY）
- - - 什么是主键
    - 什么是复合主键
- 外键（FOREIGN KEY）
- - - 什么是外键
    - 为什么需要外键
    - 创建客户表和公司表
- 级联
- 多表查询
- - - 什么是多表查询
    - 例子讲解
    - - 建表
        查询结果说明
    - SQL JOINS
    - 外连接
    - - LEFT JOIN
        RIGHT JOIN
- 参考

DQL语句什么是DQL语句

聚合函数

常见的WHERe子句比较运算

逻辑运算

模糊查询

UNIQUE KEY（索引）

主键（PRIMARY KEY）什么是主键

什么是复合主键外键（FOREIGN KEY）什么是外键

为什么需要外键

请比较下面两种建表方式。很明显，第二种方式建表更好一点。

建表方式一：

建表方式二：

创建客户表和公司表

注意：

上面的建表语句表示：如果customer表中的company_id有值的话，它的值必须来自company表中的id字段。
当然，company_id是可以为空的，表示这个客户没有公司。
如果想要该客户必须设置公司的话，那么你也可以给customer表中的company_id后面加上NOT NULL（这样就能保证customer中的客户一定有company_id并且一定都是来自company表中）

级联

注意：

如果不写ON UPDATe CASCADE的话，那么就更新不了company下的id（如果该id已经被引用的话）
如果不写ON DELETE CASCADE的话，那么就删除不了company下的id（如果该id已经被引用的话）

测试代码：

DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS company;

CREATE TABLE company(
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20) NOT NULL UNIQUE,
	address VARCHAR(20)
);

CREATE TABLE customer(
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	age INT,
	company_id INT,
	FOREIGN KEY (company_id) REFERENCES company(id) # ON UPDATE CASCADE
);

INSERT INTO company(name, address) VALUES('阿里巴巴', '杭州');
INSERT INTO company(name, address) VALUES('腾讯', '深圳');

INSERT INTO customer(name, age, company_id) VALUES('张三', 22, 1);
INSERT INTO customer(name, age, company_id) VALUES('李四', 12, 2);
INSERT INTO customer(name, age, company_id) VALUES('王五', 23, 1);
INSERT INTO customer(name, age, company_id) VALUES('赵六', 33, 2);
INSERT INTO customer(name, age, company_id) VALUES('田七', 29, 1);

UPDATE company SET id = 11 WHERe id = 1;

多表查询什么是多表查询

例子讲解建表

customer表（100条数据）：

DROP TABLE IF EXISTS customer;

CREATE TABLE customer(
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(10) NOT NULL,
	phone VARCHAR(20),
	company_id INT,
	FOREIGN KEY (company_id) REFERENCES company(id)
);

INSERT INTO customer(name, phone, company_id) VALUES ('刘旺', '196915872', 9);
INSERT INTO customer(name, phone, company_id) VALUES ('欧阳瓜娃', '127214969', 6);
INSERT INTO customer(name, phone, company_id) VALUES ('欧阳瓜娃', '140275383', 3);
INSERT INTO customer(name, phone, company_id) VALUES ('杨庆', '116561749', 16);
INSERT INTO customer(name, phone, company_id) VALUES ('关达', '150398197', 11);
INSERT INTO customer(name, phone, company_id) VALUES ('西门瓜娃', '145177519', 16);
INSERT INTO customer(name, phone, company_id) VALUES ('邓峰', '160069241', 6);
INSERT INTO customer(name, phone, company_id) VALUES ('钱凡', '126430190', 7);
INSERT INTO customer(name, phone, company_id) VALUES ('陈吹雪', '188950363', 13);
INSERT INTO customer(name, phone, company_id) VALUES ('刘兴', '100753337', 13);
INSERT INTO customer(name, phone, company_id) VALUES ('周庆', '141067078', 4);
INSERT INTO customer(name, phone, company_id) VALUES ('钱杰', '175176269', 6);
INSERT INTO customer(name, phone, company_id) VALUES ('孙凡', '134182337', 7);
INSERT INTO customer(name, phone, company_id) VALUES ('关杰', '131844976', 11);
INSERT INTO customer(name, phone) VALUES ('赵然', '100205537');
INSERT INTO customer(name, phone, company_id) VALUES ('陈达', '187867890', 18);
INSERT INTO customer(name, phone) VALUES ('欧阳旺', '198629680');
INSERT INTO customer(name, phone, company_id) VALUES ('孙兴', '163652349', 19);
INSERT INTO customer(name, phone) VALUES ('西门凡', '199596501');
INSERT INTO customer(name, phone, company_id) VALUES ('陈凡', '144903593', 5);
INSERT INTO customer(name, phone, company_id) VALUES ('邓杰', '112388911', 18);
INSERT INTO customer(name, phone) VALUES ('陈娃', '154802032');
INSERT INTO customer(name, phone, company_id) VALUES ('欧阳兴', '165260030', 18);
INSERT INTO customer(name, phone, company_id) VALUES ('徐平', '193551217', 4);
INSERT INTO customer(name, phone, company_id) VALUES ('李吹雪', '154825175', 18);
INSERT INTO customer(name, phone, company_id) VALUES ('关凡', '161415438', 1);
INSERT INTO customer(name, phone, company_id) VALUES ('欧阳裕', '173090309', 18);
INSERT INTO customer(name, phone, company_id) VALUES ('徐峰', '134086863', 14);
INSERT INTO customer(name, phone, company_id) VALUES ('黄合', '139680865', 4);
INSERT INTO customer(name, phone, company_id) VALUES ('陈总', '175502537', 8);
INSERT INTO customer(name, phone) VALUES ('张凡', '149616928');
INSERT INTO customer(name, phone, company_id) VALUES ('独孤杰', '123451434', 1);
INSERT INTO customer(name, phone) VALUES ('李杰', '130744727');
INSERT INTO customer(name, phone, company_id) VALUES ('王杰', '177098557', 9);
INSERT INTO customer(name, phone, company_id) VALUES ('独孤然', '170075168', 3);
INSERT INTO customer(name, phone) VALUES ('周凡', '143658812');
INSERT INTO customer(name, phone) VALUES ('李达', '118996803');
INSERT INTO customer(name, phone, company_id) VALUES ('李总', '183703874', 19);
INSERT INTO customer(name, phone, company_id) VALUES ('周兴', '127734528', 16);
INSERT INTO customer(name, phone, company_id) VALUES ('王平', '122968624', 14);
INSERT INTO customer(name, phone, company_id) VALUES ('孙憨批', '195287678', 4);
INSERT INTO customer(name, phone, company_id) VALUES ('张旺', '197393111', 11);
INSERT INTO customer(name, phone, company_id) VALUES ('周杰', '140523083', 18);
INSERT INTO customer(name, phone, company_id) VALUES ('陈土豪', '162893107', 5);
INSERT INTO customer(name, phone, company_id) VALUES ('陈平', '111624220', 7);
INSERT INTO customer(name, phone, company_id) VALUES ('张杰', '115991505', 1);
INSERT INTO customer(name, phone, company_id) VALUES ('周旺', '169299785', 8);
INSERT INTO customer(name, phone, company_id) VALUES ('司徒凡', '143266660', 14);
INSERT INTO customer(name, phone, company_id) VALUES ('邓杰', '100225342', 11);
INSERT INTO customer(name, phone, company_id) VALUES ('周蛋', '191294463', 5);
INSERT INTO customer(name, phone) VALUES ('杨达', '101123338');
INSERT INTO customer(name, phone, company_id) VALUES ('西门杰', '180978738', 3);
INSERT INTO customer(name, phone, company_id) VALUES ('赵达', '168166826', 6);
INSERT INTO customer(name, phone, company_id) VALUES ('刘然', '138203793', 1);
INSERT INTO customer(name, phone, company_id) VALUES ('黄娃', '110920045', 14);
INSERT INTO customer(name, phone, company_id) VALUES ('孙合', '112739754', 7);
INSERT INTO customer(name, phone, company_id) VALUES ('关土豪', '139810202', 15);
INSERT INTO customer(name, phone, company_id) VALUES ('西门峰', '114702902', 1);
INSERT INTO customer(name, phone) VALUES ('刘旺', '155474310');
INSERT INTO customer(name, phone, company_id) VALUES ('徐瓜娃', '172674046', 20);
INSERT INTO customer(name, phone) VALUES ('刘瓜娃', '169617276');
INSERT INTO customer(name, phone) VALUES ('钱庆', '169018269');
INSERT INTO customer(name, phone, company_id) VALUES ('李憨批', '137807714', 19);
INSERT INTO customer(name, phone) VALUES ('刘总', '185691683');
INSERT INTO customer(name, phone, company_id) VALUES ('张总', '127157599', 3);
INSERT INTO customer(name, phone, company_id) VALUES ('邓吹雪', '165322531', 19);
INSERT INTO customer(name, phone, company_id) VALUES ('西门吹雪', '153404351', 19);
INSERT INTO customer(name, phone, company_id) VALUES ('西门旺', '120073784', 7);
INSERT INTO customer(name, phone) VALUES ('邓憨批', '110819661');
INSERT INTO customer(name, phone, company_id) VALUES ('陈娃', '155934763', 11);
INSERT INTO customer(name, phone, company_id) VALUES ('司徒土豪', '173121828', 3);
INSERT INTO customer(name, phone) VALUES ('陈庆', '198736733');
INSERT INTO customer(name, phone, company_id) VALUES ('刘然', '165717124', 8);
INSERT INTO customer(name, phone, company_id) VALUES ('关裕', '120267477', 20);
INSERT INTO customer(name, phone, company_id) VALUES ('杨平', '131777484', 17);
INSERT INTO customer(name, phone) VALUES ('关庆', '185248556');
INSERT INTO customer(name, phone) VALUES ('吴达', '182441220');
INSERT INTO customer(name, phone) VALUES ('杨杰', '188646264');
INSERT INTO customer(name, phone) VALUES ('欧阳憨批', '178924367');
INSERT INTO customer(name, phone, company_id) VALUES ('独孤总', '182183221', 2);
INSERT INTO customer(name, phone) VALUES ('周合', '144871705');
INSERT INTO customer(name, phone) VALUES ('西门庆', '161083558');
INSERT INTO customer(name, phone, company_id) VALUES ('王平', '118765659', 11);
INSERT INTO customer(name, phone, company_id) VALUES ('杨峰', '190696838', 13);
INSERT INTO customer(name, phone, company_id) VALUES ('张吹雪', '177472689', 13);
INSERT INTO customer(name, phone, company_id) VALUES ('周旺', '103478393', 5);
INSERT INTO customer(name, phone, company_id) VALUES ('李达', '185302470', 16);
INSERT INTO customer(name, phone, company_id) VALUES ('王蛋', '154331787', 9);
INSERT INTO customer(name, phone, company_id) VALUES ('吴旺', '100914061', 14);
INSERT INTO customer(name, phone, company_id) VALUES ('吴然', '187178166', 7);
INSERT INTO customer(name, phone, company_id) VALUES ('关吹雪', '111382438', 19);
INSERT INTO customer(name, phone, company_id) VALUES ('邓庆', '198483480', 17);
INSERT INTO customer(name, phone) VALUES ('赵兴', '104243320');
INSERT INTO customer(name, phone, company_id) VALUES ('杨总', '192928683', 13);
INSERT INTO customer(name, phone, company_id) VALUES ('邓旺', '176753360', 19);
INSERT INTO customer(name, phone, company_id) VALUES ('张蛋', '165074918', 17);
INSERT INTO customer(name, phone) VALUES ('独孤兴', '123976698');
INSERT INTO customer(name, phone, company_id) VALUES ('孙总', '155054057', 1);
INSERT INTO customer(name, phone) VALUES ('黄瓜娃', '126691432');
INSERT INTO customer(name, phone, company_id) VALUES ('欧阳吹雪', '188604280', 17);

company表（20条数据）：

DROP TABLE IF EXISTS company;

CREATE TABLE company(
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(10) NOT NULL,
	address VARCHAR(100)
);

INSERT INTO company(name, address) VALUES ('网易音乐', '山东上海徐汇区');
INSERT INTO company(name, address) VALUES ('天朝石油', '浙江上海锦江区');
INSERT INTO company(name, address) VALUES ('字节移动', '湖南上海海珠区');
INSERT INTO company(name, address) VALUES ('中国石油', '浙江上海金牛区');
INSERT INTO company(name, address) VALUES ('阿里石油', '四川深圳徐汇区');
INSERT INTO company(name, address) VALUES ('中国音乐', '浙江成都锦江区');
INSERT INTO company(name, address) VALUES ('腾讯妈妈', '山东广州锦江区');
INSERT INTO company(name, address) VALUES ('腾讯妈妈', '广东上海锦江区');
INSERT INTO company(name, address) VALUES ('腾讯移不动', '山东深圳锦江区');
INSERT INTO company(name, address) VALUES ('百度巴巴', '广东广州徐汇区');
INSERT INTO company(name, address) VALUES ('百度巴巴', '山东杭州天河区');
INSERT INTO company(name, address) VALUES ('阿里石油', '山东上海海珠区');
INSERT INTO company(name, address) VALUES ('网易跳动', '四川上海徐汇区');
INSERT INTO company(name, address) VALUES ('阿里巴巴', '湖南广州海珠区');
INSERT INTO company(name, address) VALUES ('网易巴巴', '广东上海天河区');
INSERT INTO company(name, address) VALUES ('天朝跳不动', '四川成都锦江区');
INSERT INTO company(name, address) VALUES ('中国音乐', '广东广州金牛区');
INSERT INTO company(name, address) VALUES ('天朝跳动', '湖南广州锦江区');
INSERT INTO company(name, address) VALUES ('网易移动', '广东上海锦江区');
INSERT INTO company(name, address) VALUES ('百度联不通', '浙江成都徐汇区');

查询结果说明

① 这样查询出来的结果是两张表的笛卡尔积，会有2000（20 * 100）条结果。

SELECT * FROM company, customer;

② company_id不为NULL的客户有76个。

SELECt
 * 
FROM 
customer
WHERe
company_id IS NOT NULL;

或者这样写：

SELECt
 * 
FROM 
customer, company
WHERe
customer.company_id = company.id;

③ company_id为NULL的客户有24条。

SELECt
 * 
FROM 
customer
WHERe
company_id IS NULL;

④ 有2个公司下面没有任何客户

SQL JOINS

说明：

A、B代表两张表。A圈中是A的数据，B圈中是B的数据。
交叉区域：A表和B表之间有联系的数据。
蓝色的区域代表能够查出来的数据。
蓝色区域大小不能说明数据多少，只是用来表示有这样的数据。（交集看起来比较小，但是有可能数据多。其他蓝色区域同理）

外连接

注意：要想使用JOIN来连接两张表，那么就必须使用ON来写清楚连接两张表的条件。（ON的意思就是：在什么条件的基础上进行两张表的连接）

LEFT JOIN

左外连接：以左边的表为主。

# 100 = 76 + 24
SELECt
 * 
FROM 
# 以左边（customer）为主
customer a LEFT JOIN company b
ON
a.company_id = b.id;

蓝色区域中：A和B的交集部分是A表和B表之间有联系的数据（76）；A减去交集的部分是只有A表有的数据（24）。

RIGHT JOIN

右外连接：以右边的表为主。

# 78 = （76 + 2）
SELECt
 * 
FROM 
customer a RIGHT JOIN company b
ON
a.company_id = b.id;

蓝色区域中：A和B的交集部分是A表和B表之间有联系的数据（76）；B减去交集的部分是只有B表有的数据（2）。

参考

李明杰: Java从0到架构师②JavaEE技术基石.

本文完，感谢您的关注支持！

【Java从零到架构师第二季】【06】DQL语句主键外键+多表查询

Java相关栏目本月热门文章