MySQL 子查询

时间：2022-04-25 01:22:17


阅读目录

  • MySQL子查询
  • 表
  • emp 员工信息表
  • salgrade 薪水等级信息表
  • dept 部门信息表
  • 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
  • 1、首先取得管理者的编号，去除重复的
  • 2、查询员工编号包含管理者编号的
  • 查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水实现思路
  • 1、取得平均薪水
  • 2、取得大于平均薪水的员工
  • 在 from 语句中使用子查询，可以将该子查询看做一张表
  • 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
  • 1、首先取得管理者的编号，去除重复的
  • 2、将以上查询作为一张表，放到 from 语句的后面
  • 查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号
  • 1、首先取得各个部门的平均薪水
  • 2、将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级
  • 在 select 语句中使用子查询
  • 查询员工信息，并显示出员工所属的部门名称
  • 示例数据库
  • MySQL子查询编写复杂的查询语句并解释相关的子查询概念
  • MySQL子查询在WHERE子句中
  • MySQL子查询与比较运算符
  • 具有 IN 和 NOT IN 运算符的 MySQL 子查询
  • FROM 子句中的 MySQL 子查询
  • MySQL子查询使用外部查询中的数据的子查询
  • MySQL子查询与 EXISTS 和 NOT EXISTS
  • MySQL中的联表查询与子查询
  • 0.准备数据
  • blog 记录文章名与文章类型表
  • blog_type 记录文章类型表
  • 1.内连接：INNER JOIN
  • 2.左连接：LEFT JOIN
  • 3.右连接：RIGHT JOIN
  • 4.USING子句
  • join 中改善性能的一些注意点：
  • 扩展知识点
  • 1.子查询
  • 举栗：想要从数据库中获取文章类型是Python的文章列表

MySQL子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表。

在where语句中使用子查询，也就是在where语句中加入select语句。

表

emp 员工信息表

表名称：emp

描述：员工信息表

英文字段名称

中文描述

类型

EMPNO

员工编号

INT (4)

ENAME

员工姓名

VARCHAR(10)

JOB

工作岗位

VARCHAR(9)

MGR

上级领导

INT (4)

HIREDATE

入职日期

DATE

SAL

薪水

DOUBLE(7,2)

COMM

津贴

DOUBLE (7,2)

DEPTNO

部门编号

INT(2)

 
CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', NULL, '20');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', NULL, '20');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', NULL, '30');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', NULL, '10');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', NULL, '20');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000.00', NULL, '10');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100.00', NULL, '20');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', NULL, '30');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', NULL, '20');
INSERT INTO `bjpowernode`.`emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', NULL, '10');

MySQL 子查询

salgrade 薪水等级信息表

表名称：salgrade

描述：薪水等级信息表

英文字段名称

中文描述

类型

GRADE

等级

INT

LOSAL

最低薪水

INT

HISAL

最高薪水

INT

 
CREATE TABLE `salgrade` (
  `GRADE` int(11) DEFAULT NULL,
  `LOSAL` int(11) DEFAULT NULL,
  `HISAL` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `bjpowernode`.`salgrade` (`GRADE`, `LOSAL`, `HISAL`) VALUES ('1', '700', '1200');
INSERT INTO `bjpowernode`.`salgrade` (`GRADE`, `LOSAL`, `HISAL`) VALUES ('2', '1201', '1400');
INSERT INTO `bjpowernode`.`salgrade` (`GRADE`, `LOSAL`, `HISAL`) VALUES ('3', '1401', '2000');
INSERT INTO `bjpowernode`.`salgrade` (`GRADE`, `LOSAL`, `HISAL`) VALUES ('4', '2001', '3000');
INSERT INTO `bjpowernode`.`salgrade` (`GRADE`, `LOSAL`, `HISAL`) VALUES ('5', '3001', '9999');

dept 部门信息表

表名称：dept

描述：部门信息表

英文字段名称

中文描述

类型

DEPTNO

部门编号

INT(2)

DNAME

部门名称

VARCHAR(14)

LOC

位置

VARCHAR(13)

 
CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `bjpowernode`.`dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `bjpowernode`.`dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `bjpowernode`.`dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `bjpowernode`.`dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('40', 'OPERATIONS', 'BOSTON');

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

实现思路：

1、首先取得管理者的编号，去除重复的

distinct 去除重复行

select distinct mgr from emp where mgr is not null;

2、查询员工编号包含管理者编号的

select empno, ename from emp 
where empno in(select mgr from emp where mgr is not null);

MySQL 子查询

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水实现思路

1、取得平均薪水

select avg(sal) from emp;

MySQL 子查询

2、取得大于平均薪水的员工

select empno, ename, sal from emp where sal > (select avg(sal) from emp);

MySQL 子查询

在 from 语句中使用子查询，可以将该子查询看做一张表

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

1、首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;

MySQL 子查询

2、将以上查询作为一张表，放到 from 语句的后面

第一种语法

SELECT e.empno, e.ename
FROM emp e, (
		SELECT DISTINCT mgr
		FROM emp
		WHERE mgr IS NOT NULL
	) m
WHERE e.empno = m.mgr;

MySQL 子查询

第二种语法

SELECT e.empno, e.ename
FROM emp e
	JOIN (
		SELECT DISTINCT mgr
		FROM emp
		WHERE mgr IS NOT NULL
	) m
	ON e.empno = m.mgr;

MySQL 子查询

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

1、首先取得各个部门的平均薪水
select deptno, avg(sal) avg_sal from emp group by deptno;

MySQL 子查询

2、将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级
select deptno,avg(sal) avg_sal from emp group by deptno;
select * from salgrade;
SELECT a.deptno, a.avg_sal, g.grade
FROM (
	SELECT deptno, avg(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
) a
	JOIN salgrade g ON a.avg_sal BETWEEN g.losal AND hisal;

MySQL 子查询

在 select 语句中使用子查询

查询员工信息，并显示出员工所属的部门名称

第一种做法，将员工表和部门表连接

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno

MySQL 子查询


第二种做法，在 select 语句中再次嵌套 select 语句完成部分名称的查询

SELECT e.ename, (
		SELECT d.dname
		FROM dept d
		WHERE e.deptno = d.deptno
	) AS dname
FROM emp e

MySQL 子查询

示例数据库


MySQL子查询编写复杂的查询语句并解释相关的子查询概念

MySQL子查询是嵌套在另一个查询 (如 SELECT，INSERT，UPDATE 或 DELETE) 中的查询。 另外，MySQL子查询可以嵌套在另一个子查询中。

MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。 子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。

以下查询返回在位于美国(USA)的办公室工作的员工。

SELECT lastName, firstName
FROM employees
WHERE officeCode IN (
	SELECT officeCode
	FROM offices
	WHERE country = 'USA'
);

MySQL 子查询

在这个例子中：

子查询返回位于美国的办公室的所有办公室代码。

外部查询选择在办公室代码在子查询返回的结果集中的办公室中工作的员工的姓氏和名字。

MySQL 子查询


MySQL 子查询

MySQL子查询在WHERE子句中

我们将使用示例 payments 表进行演示。

payments 表的表结构如下

MySQL 子查询

MySQL子查询与比较运算符

可以使用比较运算符，例如 =，>，< 等将子查询返回的单个值与 WHERE 子句中的表达式进行比较。

例如，以下查询返回最大付款额的客户。

SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount = (
	SELECT MAX(amount)
	FROM payments
);

MySQL 子查询


除等式运算符之外，还可以使用大于 (>)，小于(<) 等的其他比较运算符。

例如，可以使用子查询找到其付款大于平均付款的客户。 首先，使用子查询来计算使用AVG聚合函数的平均付款。 然后，在外部查询中，查询大于子查询返回的平均付款的付款。参考以下查询语句的写法

SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount > (
	SELECT AVG(amount)
	FROM payments
);

MySQL 子查询

具有 IN 和 NOT IN 运算符的 MySQL 子查询

如果子查询返回多个值，则可以在 WHERE 子句中使用 IN 或 NOT IN 运算符等其他运算符。

查看以下客户和订单表的ER结构图

MySQL 子查询


例如，可以使用带有NOT IN运算符的子查询来查找没有下过任何订单的客户，如下所示：

SELECT customerName
FROM customers
WHERE customerNumber NOT IN (
	SELECT DISTINCT customerNumber
	FROM orders
);

MySQL 子查询

FROM 子句中的 MySQL 子查询

在 FROM 子句中使用子查询时，从子查询返回的结果集将用作临时表。 该表称为派生表或物化子查询。

以下子查询将查找订单表中的最大，最小和平均数：

SELECT 
	MAX(items), 
	MIN(items), 
	FLOOR(AVG(items))
FROM (
	SELECT orderNumber, COUNT(orderNumber) AS items
	FROM orderdetails
	GROUP BY orderNumber
) lineitems;

MySQL 子查询


MySQL 子查询

MySQL子查询使用外部查询中的数据的子查询

在前面的例子中，注意到一个子查询是独立的。 这意味着您可以将子查询作为独立查询执行，例如：

SELECT 
    orderNumber, 
    COUNT(orderNumber) AS items
FROM
    orderdetails
GROUP BY orderNumber;

MySQL 子查询

与独立子查询不同，相关子查询是使用外部查询中的数据的子查询。
换句话说，相关的子查询取决于外部查询。
对外部查询中的每一行对相关子查询进行一次评估。

在以下查询中，我们查询选择购买价格高于每个产品线中的产品的平均购买价格的产品。

MySQL 子查询

MySQL 子查询

SELECT productname, buyprice
FROM products p1
WHERE buyprice > (
	SELECT AVG(buyprice)
	FROM products
	WHERE productline = p1.productline
);

MySQL 子查询


对于变化的每一行产品线，每个产品线都会执行内部查询。

因此，平均购买价格也会改变。 外部查询仅筛选购买价格大于子查询中每个产品线的平均购买价格的产品。

MySQL子查询与 EXISTS 和 NOT EXISTS

当子查询与 EXISTS 或 NOT EXISTS 运算符一起使用时，子查询返回一个布尔值为 TRUE或 FALSE 的值。

以下查询说明了与 EXISTS 运算符一起使用的子查询：

SELECT 
    *
FROM
    table_name
WHERE EXISTS(subquery);

在上面的查询中，如果子查询 (subquery) 有返回任何行，则 EXISTS 子查询返回 TRUE，否则返回 FALSE。

通常在相关子查询中使用 EXISTS 和 NOT EXISTS。

下面我们来看看示例 orders 和 orderDetails 表：

MySQL 子查询


以下查询选择总额大于 60000 的销售订单。

SELECT orderNumber, SUM(priceEach * quantityOrdered) AS total
FROM orderdetails
	INNER JOIN orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;

MySQL 子查询


MySQL 子查询

MySQL 子查询


如上面所示，返回 3 行数据，这意味着有 3 个销售订单的总额大于 60000。

可以使用上面的查询作为相关子查询，通过使用 EXISTS 运算符来查找至少有一个总额大于60000 的销售订单的客户信息：

SELECT customerNumber, customerName
FROM customers
WHERE EXISTS (
	SELECT orderNumber, SUM(priceEach * quantityOrdered)
	FROM orderdetails
		INNER JOIN orders USING (orderNumber)
	WHERE customerNumber = customers.customerNumber
	GROUP BY orderNumber
	HAVING SUM(priceEach * quantityOrdered) > 60000
);

执行上面查询，得到以下结果 :

MySQL 子查询

MySQL中的联表查询与子查询

0.准备数据
1.内连接：INNER JOIN
2.左连接：LEFT JOIN
3.右连接：RIGHT JOIN
4.USING子句

扩展知识点：

0、表别名的使用
1、group by 的用法
2、子查询
      1）不相关子查询
      2）相关子查询

JOIN 的含义就如英文单词 “join” 一样，连接两张表，语法如下所示：

SELECT * FROM A INNER|LEFT|RIGHT JOIN B ON condition

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
LEFT JOIN（左连接）：取得左表（A）完全记录，即是右表（B）并无对应匹配记录。
RIGHT JOIN（右连接）：取得右表（B）完全记录，即是左表（A）并无匹配对应记录。

注意：MySQL 不支持 Full join 不过可以通过 UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN 来模拟 FULL join。

0.准备数据

blog 记录文章名与文章类型表

CREATE TABLE blog (
	id INT PRIMARY KEY AUTO_INCREMENT,
	title_name varchar(40),
	title_type int
);

INSERT INTO blog
VALUES 
    (0, 'aa', 1),
	(0, 'bb', 2),
	(0, 'cc', 3),
	(0, 'dd', 4),
	(0, 'ee', 3),
	(0, 'ff', 2),
	(0, 'gg', DEFAULT),
	(0, 'hh', 6);

MySQL 子查询

blog_type 记录文章类型表

CREATE TABLE blog_type (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name varchar(40)
);

INSERT INTO blog_type
VALUES 
    (0, 'C'),
	(0, 'PYTHON'),
	(0, 'JAVA'),
	(0, 'HTML'),
	(0, 'C++');

MySQL 子查询

1.内连接：INNER JOIN

内连接 INNER JOIN 是最常用的连接操作。
从数学的角度讲就是求两个表的交集:

SELECT * FROM blog INNER JOIN blog_type ON blog.title_type = blog_type.id;
select * from blog join blog_type on blog.title_type=blog_type.id;
select * from blog,blog_type where blog.title_type=blog_type.id;

MySQL 子查询

2.左连接：LEFT JOIN

左连接 LEFT JOIN 的含义就是求两个表的交集外加左表剩下的数据，左连接从左表 (A) 产生一套完整的记录，与匹配的记录 (右表(B))，如果没有匹配,右侧将包含 null。

select * from blog left join blog_type on blog.title_type=blog_type.id;

MySQL 子查询


如果想只从左表 (A) 中产生一条记录，但不包含右表 (B) 的记录，可以通过设置 where 语句来执行，如下：

SELECT *
FROM blog
	LEFT JOIN blog_type ON blog.title_type = blog_type.id
WHERE blog_type.id IS NULL;

MySQL 子查询

3.右连接：RIGHT JOIN

同理右连接 RIGHT JOIN 就是求两个表的交集外加右表剩下的数据。

select * from blog right join blog_type on blog.title_type=blog_type.id;

MySQL 子查询

4.USING子句

MySQL中连接SQL语句中，ON子句的语法格式为：
table1.column_name = table2.column_name。

当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)。

所以，USING 的功能相当于ON，区别在于USING指定一个属性名用于连接两个表，而 ON指定一个条件。另外，SELECT * 时，USING会去除 USING 指定的列，而 ON 不会。

实例如下:

create table blog_type_1 as select * from blog_type;

MySQL 子查询

alter table blog_type drop id;

MySQL 子查询

ALTER TABLE blog_type
	ADD COLUMN title_type int NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

MySQL 子查询

SELECT *
FROM blog
	INNER JOIN blog_type ON blog.title_type = blog_type.title_type;

MySQL 子查询

select * from blog join blog_type using(title_type);

USING会去除USING指定的列

MySQL 子查询

join 中改善性能的一些注意点：

1、小表驱动大表能够减少内循环的次数从而提高连接效率。
2、在被驱动表建立索引能够提高连接效率
3、优先选择驱动表的属性进行排序能够提高连接效率

扩展知识点

1.子查询

嵌套在其它查询中的查询称之为子查询或内部查询，包含子查询的查询称之为主查询或外部查询。

举栗：想要从数据库中获取文章类型是Python的文章列表

SELECT title_name
FROM blog
WHERE title_type = (
	SELECT id
	FROM blog_type_1
	WHERE name = 'PYTHON'
);

MySQL 子查询

SELECT title_name
FROM blog A
	JOIN blog_type_1 B ON A.title_type = B.id
WHERE B.name = 'PYTHON';

MySQL 子查询


分步执行：

获取id: 
select id from blog_type_1 where name='PYTHON';---->id=2

获取文章列表：
select title_name from blog where title_type=2;-→title_name=(bb,ff)

联合查询：

子查询的方式

SELECT title_name
FROM blog
WHERE title_type = (
	SELECT id
	FROM blog_type_1
	WHERE name = 'PYTHON'
);

联表查询的方式

SELECT title_name
FROM blog A
	JOIN blog_type_1 B ON A.title_type = B.id
WHERE B.name = 'PYTHON';


标签：

相关文章