MySQL多表查询---Java学习之数据库

/*
-- 创建部门表
create table tb_dept(
id int primary key auto_increment,
name varchar(20)
);

insert into tb_dept (name) values ('开发部'),('市场部'),('财务部');
--创建员工表
create table tb_emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references tb_dept(id) -- 外键，关联部门表(部门表的主键)
);

insert into tb_emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

###############################多表查询的操作########################

1.多表查询的经典问题：笛卡儿积现象

CREATE DATABASE dlei;
直接进行两张表的相乘组合。
就是笛卡尔积现象，应该被避免，因为它有很多数据是无效的
SELECT * FROM tb_emp , tb_dept;

2.内连接可以解决笛卡尔积现象，得到准确的有效的结果数据

内连接分为：显示内连接，隐式内链接，功能是一样的，只是写法不同而已。

a.隐式内连接

SELECT * FROM tb_emp e , tb_dept d WHERE e.dept_id = d.id;

b.显示内连接 INNER JOIN ... ON 连表的条件。

SELECT * FROM tb_emp e INNER JOIN tb_dept d ON e.dept_id = d.id;

INNER可以省略不写(这种可以采用)

SELECT * FROM tb_emp e JOIN tb_dept d ON e.dept_id = d.id;

1.查询出唐僧的部门名称

SELECT d.name 部门名称 , e.name员工名称
FROM tb_emp e JOIN tb_dept d ON e.dept_id= d.id WHERE e.name = '唐僧';

#######################外连接的操作###################################

外连接：分为左外连接（左连接），右外连接（右连接）

1.左连接：在内连接的基础之上，保全左表的全部数据,右表没有对应的数据默认为null
格式：LEFT OUTER JOIN 右表 ON 左表外键=右表主键
OUTER可以省略不写
SELECT * FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id= d.id;

2.右连接：在内连接的基础之上，保全右表的全部数据,左表没有对应的数据默认为null
格式：RIGHT OUTER JOIN 右表 ON 左表外键=右表主键
OUTER可以省略不写
SELECT * FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id= d.id;

在内链接的基础上保留左右表的全部数据

SELECT * FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id = d.id
UNION
SELECT * FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id = d.id

############################子查询的操作####################################

子查询：是多表查询方案中的一种（表连接，子查询）

子查询的含义是：一条SQL语句中又包含了一条select语句。
1.需求:查询出工资最高的那个人的名字。
a.查询出最高工资
SELECT MAX(salary) FROM tb_emp ;
b.查询出最高工资的那个人的名字(以下SQL语句就是子查询)
第一种情况：子查询的结果是单列情况，只能用=这样的判断（有一个值）
SELECT NAME FROM tb_emp WHERE salary = (SELECT MAX(salary) FROM tb_emp)
--查询工资小于平均工资的员工有哪些？
SELECT * FROM tb_emp WHERE salary < (SELECT AVG(salary) FROM tb_emp )

第二种情况：子查询的结果是多行单列数据（有多个值）：必须用IN来实现判断

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
2.需求：查询工资大于4000的员工，来自于哪些部门的名字
a.查询工资大于4000的员工的部门编号
SELECT dept_id FROM tb_emp WHERE salary > 4000;
b.再查询出他们所属的部门信息
SELECT NAME FROM tb_dept WHERE id IN(SELECT dept_id FROM tb_emp WHERE salary > 4000)

3.需求：查询开发部与财务部所有的员工信息
a.查询出开发部和财务部的编号
SELECT id FROM tb_dept WHERE NAME = '开发部' || NAME = '财务部';
b.再查询出这个部门下的员工信息
SELECT * FROM tb_emp WHERE dept_id IN (SELECT id FROM tb_dept WHERE NAME = '开发部' || NAME = '财务部');

第三种情况：子查询的结果是多行多列（查询出来就是一个新表（虚表），直接当表使用）
4.需求查询出2011年以后入职的员工信息，包括部门名称
a.查询出2011年之后的员工信息
SELECT * FROM tb_emp WHERE join_date >= '2011-01-01'
b.把新查询出的员工表（2011年之后的员工）与部门表左外连接
SELECT * FROM (SELECT * FROM tb_emp WHERE join_date >= '2011-01-01') new_emp LEFT JOIN tb_dept d
ON new_emp.dept_id = d.id;

多表查询练习题

-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY,
losalary INT,
hisalary INT
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

1.查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

-- a.明确表：员工表，职务表
-- b.连接表：左连接
SELECT e.id , e.ename , e.salary, j.jname , j.description
FROM emp e LEFT JOIN job j ON e.job_id = j.id;

2.查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

-- a.明确表：员工表，职务表,部门表
-- b.连接表：左连接
SELECT e.id, e.ename , e.salary , j.jname, j.description , d.dname, d.loc
FROM emp e LEFT JOIN job j ON e.job_id= j.id LEFT JOIN dept d
ON e.dept_id= d.id

不考虑员工没有部门和职位的人！

--SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d ON e.job_id=j.id AND e.dept_id=d.id;

3.查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

-- a.明确表：员工表，职务表, 部门表 ,工资等级表
-- b.连接表：左连接
-- 最通用做法：

SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc, sg.grade , sg.losalary,sg.hisalary FROM emp e LEFT JOIN job j ON e.job_id=j.id
LEFT JOIN dept d ON e.dept_id= d.id
JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary

4.查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT * FROM emp e JOIN JOB j ON e.job_id = j.id AND j.jname='经理'
JOIN dept d ON e.dept_id= d.id
JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary

5.查询出部门编号、部门名称、部门位置、部门人数

-- a.简单粗暴连接表：dept , emp
-- 1.直接到员工表根据部门id分组查询
SELECT COUNT() , dept_id FROM emp GROUP BY dept_id;
-- 2.拿着部门员工的信息数据与部门表实现右连接
SELECT * FROM (SELECT COUNT() , dept_id FROM emp GROUP BY dept_id) new_emp_nums RIGHT JOIN
dept d ON new_emp_nums.dept_id = d.id;

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,080评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,422评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,630评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,554评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,662评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,856评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,014评论 3赞 408
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,752评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,212评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,541评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,687评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,347评论 4赞 331
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,973评论 3赞 315
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,777评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,006评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,406评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,576评论 2赞 349