MySQL笔记——多表查询

最新推荐文章于 2023-04-06 19:00:57 发布
最新推荐文章于 2023-04-06 19:00:57 发布
阅读量202 收藏
点赞数
文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/anhanjie/article/details/123025509
版权 
多表查询不能使用 SELECT * from emp, dept; 会产生笛卡尔积。
笛卡尔积，有A，B两个集合，A中有5条信息，B中有4条信息，那么查询结果就是5*4=20条

一、内连接查询

		-- 隐式内连接
		SELECT 字段列表 FROM 表1,表2,… WHERE 条件;
		例：select emp.name,emp.age,dept.dep_name from emp,dept where emp.dep_id = dept.id;
			可以给表起别名将上面的语句转换成下面这种。
			select t1.name,t1.age,t2.dep_name from emp t1,dept t2 where t1.dep_id = t2.id;

在这里插入图片描述

		-- 显示内连接
		SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
		如：select emp.name,dept.dep_name from emp inner join dept on emp.dep_id = dept.id;
		可以给表起别名将上面的语句转换成下面这种。
		select t1.name,t2.dep_name from emp t1 inner join dept t2 on t1.dep_id = t2.id;

在这里插入图片描述

二、外连接查询

		（1）左外连接：相当于查询A表所有数据和交集部分数据
			左外连接：
			SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
		
		select * from emp left join dept on emp.dep_id = dept.id;

在这里插入图片描述

		（2）右外连接：相当于查询B表所有数据和交集部分数据
			右外连接：
				SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
				
		select * from emp right join dept on emp.dep_id = dept.id;

在这里插入图片描述

三、子查询

		（1）子查询概念
			查询中嵌套查询，称嵌套查询为子查询；
			
		（2）子查询根据查询结果不同，作用不同：
			①单行单列：作为条件值，使用 '='  '!='  '>'  '<'等进行条件判断
				SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
				
				查询年龄大于周八的
				select * from emp where age>(select age from emp where name = '周八');

在这里插入图片描述

			②多行单列：作为条件值，使用in关键字进行条件判断
				SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
				
				查询研发和销售的所有员工信息
				select * from emp where dep_id in (select id from dept where dep_name = '研发部' or dep_name = '销售部' );

在这里插入图片描述

			③多行多列：作为虚拟表
				SELECT 字段列表 FROM (子查询) WHERE 条件;
				
				-- 查询入职日期是‘1998-08-01’之后的员工信息和部门信息
				把（SELECT * from emp WHERE join_date>'1998-08-01'）这个多行多列表作为虚拟表查询放到from后面
				SELECT * FROM (SELECT * from emp WHERE join_date>'1998-08-01') t1,dept WHERE t1.dep_id = dept.id;

在这里插入图片描述

四、练习

新建员工表，部门表，职务表，工资等级表
1.查询所有员工信息。查询员工编号id，员工姓名ename，工资salary，职务名称jname，职务描述description
2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
3.查询员工姓名，工资，工资等级
4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
5.查询出部门编号、部门名称、部门位置、部门人数

具体步骤如下：
	首先新建四张表：

-- 部门表
CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, -- 部门id
	dname VARCHAR ( 50 ), -- 部门名称
	loc VARCHAR ( 50 ) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job ( 
	id INT PRIMARY KEY,  -- 职务id
	jname VARCHAR ( 20 ),  -- 职务名称
	description VARCHAR ( 50 ) -- 职务描述
);
-- 员工表
CREATE TABLE emp (
	id INT PRIMARY KEY,-- 员工id
	ename VARCHAR ( 50 ),-- 员工姓名
	job_id INT,-- 工作编号
	mgr INT,-- 上级领导
	joindate DATE,	-- 入职日期
	salary DECIMAL ( 7, 2 ),	-- 工资
	bonus DECIMAL ( 7, 2 ),-- 奖金
	dept_id INT,	-- 所在部门编号
	CONSTRAINT fk_emp_jobid_job_id FOREIGN KEY ( job_id ) REFERENCES job ( id ),
	CONSTRAINT fl_emp_deptid_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id ) 
);
-- 工资等级表
CREATE TABLE salarygrade ( 
	grade INT PRIMARY KEY, -- 界别
	losalary INT, -- 最低工资
	hisalary INT -- 最高工资
);

添加数据：

-- 添加4个部门
INSERT INTO dept ( id, dname, loc )
VALUES
	( 10, '教研部', '北京' ),
	( 20, '学工部', '上海' ),
	( 30, '销售部', '广州' ),
	( 40, '财务部', '深圳' );
-- 添加4个职务
INSERT INTO job ( id, jname, description )
VALUES
	( 1, '董事长', '管理整个公司，接单' ),
	( 2, '经理', '管理部门员工' ),
	( 3, '销售员', '向客人推销产品' ),
	( 4, '文员', '使用办公软件' );
-- 添加员工
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-10', '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-17', '12500.00', 14000.00, 30 ),
	( 1006, '宋江', 2, 1009, '2001-05-17', '28500.00', NULL, 30 ),
	( 1007, '刘备', 2, 1009, '2001-09-17', '24500.00', NULL, 10 ),
	( 1008, '猪八戒', 4, 1004, '2007-12-17', '30000.00', NULL, 20 ),
	( 1009, '罗贯中', 1, NULL, '2000-12-17', '50000.00', NULL, 10 ),
	( 1010, '吴用', 3, 1006, '2001-09-17', '15000.00', '0.00', 30 ),
	( 1011, '沙僧', 4, 1004, '2007-05-17', '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-12', '13000.00', NULL, 10 );
-- 添加工资等级
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES
	( 1, 7000, 12000 ),
	( 2, 12010, 14000 ),
	( 3, 14010, 20000 ),
	( 4, 20010, 30000 ),
	( 5, 30010, 99990 );

1.查询所有员工信息。查询员工编号id，员工姓名ename，工资salary，职务名称jname，职务描述description

-- 1.查询所有员工信息。查询员工编号id，员工姓名ename，工资salary，职务名称jname，职务描述description
/*
分析：
1.员工编号id，员工姓名ename，工资salary 在emp表中
2.职务名称jname，职务描述description，在职务表中
3.职务表和员工表是一对多的关系 e.job_id = j.id
通过内连接可完成
*/
-- 隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp,
	job 
WHERE
	emp.job_id = job.id;
-- 显示内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp
	INNER JOIN job ON emp.job_id = job.id;

2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析：
1.员工编号，员工姓名，工资，在emp表中
2.职务名称，职务描述，在job表中
3.部门名称，部门位置，在dept表中
4.职务表和员工表是一对多的关系 e.job_id = j.id
5.部门表和员工表是一对多的关系 e.dept_id = d.id
*/
-- 隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp,
	job,
	dept 
WHERE
	emp.job_id = job.id 
	AND emp.dept_id = dept.id;
	
-- 显示内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp
	INNER JOIN job ON emp.job_id = job.id
	INNER JOIN dept ON emp.dept_id = dept.id;

3.查询员工姓名，工资，工资等级

-- 3.查询员工姓名，工资，工资等级
/*
	1.员工姓名,工资,emp
	2.工资等级,salarygrade
	3. salarygrade.losalary < emp.salary AND salarygrade.hisalary >  emp.salary
*/

SELECT
	emp.ename,
	emp.salary,
	s.grade 
FROM
	emp,
	salarygrade s 
WHERE
	emp.salary >= s.losalary 
	AND emp.salary <= s.hisalary;


4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
	1.员工姓名，工资,emp
	2.职务名称，职务描述,job
	3.部门名称，部门位置,dept
	4.工资等级,salarygrade
	5.职务表和员工表是一对多的关系 e.job_id = j.id
	6.部门表和员工表是一对多的关系 e.dept_id = d.id
	7.salarygrade.losalary < emp.salary AND salarygrade.hisalary >  emp.salary
*/
-- 隐式内连接
SELECT
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	s.grade 
FROM
	emp,
	job,
	dept,
	salarygrade s 
WHERE
	emp.job_id = job.id 
	AND emp.dept_id = dept.id 
	AND emp.salary >= s.losalary 
	AND emp.salary <= s.hisalary;
	
-- 显式内连接	
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
emp
	INNER JOIN job ON emp.job_id = job.id
	INNER JOIN dept ON emp.dept_id = dept.id
	INNER JOIN salarygrade s ON emp.salary >= s.losalary 
	AND emp.salary <= s.hisalary;


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

-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
	1.部门编号、部门名称、部门位置,dept
	2.部门人数,按照dept_id,进行GROUP BY分组，count(*) 统计人数
	
*/
-- 1.先在emp表中分组并统计人数，2.然后将1中产生的表作为子查询使用
SELECT
	dept.*,
	t1.total 
FROM
	dept,
	(SELECT dept_id, count( * ) total FROM emp GROUP BY dept_id ) t1 
WHERE
	t1.dept_id = dept.id;

-- 第二种方法，先把员工和部门关联起来，然后按照部门id分组，并统计人数
SELECT
	count( * ),
	t.* 
FROM
	( SELECT dept.* FROM emp, dept WHERE emp.dept_id = dept.id ) t 
GROUP BY
	t.id;
小白学编程^_^
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql from多表顺序_MySQL之多表查询
weixin_30516429的博客
01-27 1205
一 介绍本节主题多表连接查询复合条件连接查询子查询首先说一下，我们写项目一般都会建一个数据库，那数据库里面是不是存了好多张表啊，不可能把所有的数据都放到一张表里面，肯定要分表来存数据，这样节省空间，数据的组织结构更清晰，解耦和程度更高，但是这些表本质上是不是还是一个整体啊，是一个项目所有的数据，那既然分表存了，就要涉及到多个表连接查询了，比如说员工信息一张表，部门信息一张表，那如果我想让你帮我查一...
day27 Mysql 连接查询
weixin_46311092的博客
04-27 113
Mysql中的常用方法 年月日 select curdate(); 时分秒 select curtime(); 年月日 时分秒 select now(); 时间戳 select UNIX_TIMESTAMP(); 到指定时间时间戳 select UNIX_TIMESTAMP(2018-08-08 08:00:00); 原点到指定时间秒数 select FROM_UNIXTIME(1) 时间差 select datediff (now() ,'2021-4-1' ); 获取年月日 select day (no
Mysql综合案例一
尽欢的博客
04-06 992
DDL练习、DML练习、DQL练习
MySQL随手练——45道DQL题
卑以自牧，含章可贞；克己慎独，守心明性
02-22 573
MySQL随手练 / DQL篇
MySQL笔记——笔记.md
10-02
MySQL笔记
MySQL学习笔记——叶志镔.pdf
02-07
20200207学习笔记
mysql学习笔记(1)——变量
12-14
mysql学习笔记(1)——变量 文章目录mysql学习笔记(1)——变量一、变量的分类:二、系统变量全局变量（1）概念及分类：（2）常用sql语句：（3）示范：（4）作用域会话变量示范三、自定义变量概念：用户变量(1) 作用域...
mysql from多表顺序_MYSQL-多表查询
weixin_39820158的博客
01-27 557
多表查询**交叉连接 cross join内连接 inner join外连接左连接 left join右连接 right join联合查询 UNION全连接 **1、多表纵向合并纵向合并需要注意的是，两张合并的表查询结果的字段数必须一致，MariaDB [hellodb]> select stuid,name from students-> union-> select tid,...
SQL 语句中 表与条件顺序 与效率
bzq123的博客
03-29 872
FROM多表顺序与效率 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名, 因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下, 你必须选择记录条数最少的表作为基础表. 当ORACLE处理多个表时, 会运用排序及合并的方式连接它们. 首先...
数据库的多表查询
qq_44972352的博客
08-20 440
-- 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。 SELECT d.deptno, d.dname, d.loc, COUNT("renshu") FROM t_dept d, t_employees e WHERE e.deptno=d.deptno /*内连接:*/ /*方言*/ SELECT e.`deptno`,d.`dname`, d.`loc` , COUNT(e.`deptno`) FROM t_dept d, t_employees e WHERE e.`d
Mysql 多表练习
随风
12-14 226
数据库的多表练习 – 添加部门表 create table dept( id int PRIMARY KEY, dname varchar(10), -- 部门名称 loc varchar(10) -- 所在地 ); – 添加四个部门 INSERT into dept(id,dname,loc) values(10,'教研部','北京'); INSERT into dept(id,dname,loc) values(20,'学工部','上海'); INSERT into dept(id,dname
mysql 修改多笔资料_mysql 多表查询(笔计)
weixin_33138295的博客
02-01 54
select r.id as rid,rt.id as rtid,rt.roomname as rtroomname,r.roomPrice as rroomPrice,r.maxNum as rmaxNum,r.roomDescript as rroomDescript,sh.id as shid,sh.subHotelName as shsubHotelName,sh.subHotelAdd...
MySQL实验四数据库的查询_实验四 数据库查询--2
weixin_29247219的博客
01-19 4783
1、 查询“王林”的基本情况和所工作的部门名称；Use ygglSelect name,sex,address,departmentnameFrom employees,departmentsWhere employees.departmentid=departmentname.departmentidAnd name=’王林’;2、 查询财务部、研发部、市场部的员工信息(要求使用三种语法分别...
《MySQL数据库》之练习表数据：emp表与dept表的脚本整理
从明老师的博客
12-08 5323
一、Oracle版本 1、dept表 drop table dept; CREATE TABLE dept(     deptno NUMBER(2),     dname VARCHAR2(14) ,     loc VARCHAR2(13)  ) ; INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'); INSERT INTO d...
Mysql查询进阶
m0_50628119的博客
05-23 1546
Mysql查询（进阶） 一.聚合函数查询 （where后面不能出现聚合函数，一定记住） 准备数据 ： – 创建数据库exam； create database if not exists exam; – 创建表exam_result； create table if not exists exam_result( id int, name varchar(10), math decimal(4,1), Chinese decimal(4,1), English decimal(4,1) ); – 插入数据
mysql慢查询激增
最新发布
08-22
- *1* [MySQL学习笔记5——优化问题（短连接，慢查询，QPS激增）](https://blog.csdn.net/xystrive/article/details/125508577)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值