MySQL 子查询使用方式

阅读目录

阐述
- 子查询分类
- 按照返回结果的行列数分类
- 按子查询出现在主查询的位置分类
预备数据
- 部门表 departments
- 员工表 employees
- 职位信息表 jobs
- 位置表 locations
- 薪资等级表 job_grades
查询数据示例
- select 后面的子查询
- - 示例1 查询每个部门员工人数
  - 示例2 查询员工号 =102 的部门名称
- from 后面的子查询
- - 示例1 查询每个部门平均工资的薪资等级
- where 和 having 后面的子查询
标量子查询
- 一般标量子查询，查询谁的工资比 Abel 高
- 多个标量子查询，返回 job_id 与 141 号员工相同，salary 比143 号员工多的员工、姓名、job_id 和工资
- 子查询加分组查询，查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资【having】
列子查询
- 示例1，返回 location_id 是 1400 或 1700 的部门中的所有员工姓名
- - 方式 1
  - 方式 2：使用 any 实现 (注意符号)
  - ALL 等同于 not in
- 示例2，返回其他工种中比 job_id 为 'IT_PROG' 工种任意工资低的员工的员工号、姓名、job_id、salary
- 示例3，返回其他工种中比 job_id 为 'IT_PROG' 部门所有工资低的员工的员工号、姓名、job_id、salary
行子查询
- 方法 1
- 方法 2
- 方法 3
exists 后面（也叫相关子查询）
- 示例1
- 示例2，查询所有员工的部门名称
- 示例3，查询没有员工的部门
注意 null 值
- 小结

阐述

所谓子查询，就是嵌套在其他查询中的查询。

某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。

定义：执行顺序方面先于当前查询执行的，并且是嵌套在当前查询中的查询叫做子查询。

MySQL 在处理上例的 SELECT 语句时，执行流程为：

先执行子查询，再执行父查询。子查询应当始终放在括号内。

子查询的支持是从 4.1 版本引入的。MySQL 的早期版本不支持子查询。

子查询分类

在这里插入图片描述

按照返回结果的行列数分类

1 标量子查询（结果集只有一行一列）
2 列子查询（结果集只有一列多行）
3 行子查询（结果集有一行多列）
4 表子查询（结果集一般为多列多行）

按子查询出现在主查询的位置分类

select 后面：仅支持标量子查询
  from 后面：支持表子查询

where 或者 having 后面：
支持标量子查询（单行单列）、
列子查询（单列多行）、
行子查询（多列多行）

 exists后面（即相关子查询）： 表子查询（多行多列）

预备数据部门表 departments

在这里插入图片描述

/*部门表*/
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT comment '部门id',
  `department_name` varchar(3) DEFAULT NULL comment '部门名称',
  `manager_id` int(6) DEFAULT NULL comment '管理者id',
  `location_id` int(4) DEFAULT NULL comment '部门位置id，来源于表locations中的location_id',
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 comment '部门表';

INSERT INTO `departments` (
	`department_id`,
	`department_name`,
	`manager_id`,
	`location_id`
)
VALUES
	(10, 'Adm', 200, 1700),
	(20, 'Mar', 201, 1800),
	(30, 'Pur', 114, 1700),
	(40, 'Hum', 203, 2400),
	(50, 'Shi', 121, 1500),
	(60, 'IT', 103, 1400),
	(70, 'Pub', 204, 2700),
	(80, 'Sal', 145, 2500),
	(90, 'Exe', 100, 1700),
	(100, 'Fin', 108, 1700),
	(110, 'Acc', 205, 1700),
	(120, 'Tre', NULL, 1700),
	(130, 'Cor', NULL, 1700),
	(140, 'Con', NULL, 1700),
	(150, 'Sha', NULL, 1700),
	(160, 'Ben', NULL, 1700),
	(170, 'Man', NULL, 1700),
	(180, 'Con', NULL, 1700),
	(190, 'Con', NULL, 1700),
	(200, 'Ope', NULL, 1700),
	(210, 'IT ', NULL, 1700),
	(220, 'NOC', NULL, 1700),
	(230, 'IT ', NULL, 1700),
	(240, 'Gov', NULL, 1700),
	(250, 'Ret', NULL, 1700),
	(260, 'Rec', NULL, 1700),
	(270, 'Pay', NULL, 1700);

员工表 employees

在这里插入图片描述

/*员工表*/
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT comment '员工id',
  `first_name` varchar(20) DEFAULT NULL comment '名',
  `last_name` varchar(25) DEFAULT NULL comment '姓',
  `email` varchar(25) DEFAULT NULL comment '电子邮箱',
  `phone_number` varchar(20) DEFAULT NULL comment '手机',
  `job_id` varchar(10) DEFAULT NULL comment '职位id，来源于jobs表中的job_id',
  `salary` double(10,2) DEFAULT NULL comment '薪水',
  `commission_pct` double(4,2) DEFAULT NULL comment '佣金百分比',
  `manager_id` int(6) DEFAULT NULL comment '上级id',
  `department_id` int(4) DEFAULT NULL comment '所属部门id，来源于departments中的department_id',
  `hiredate` datetime DEFAULT NULL comment '入职日期',
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 comment '员工表';

INSERT INTO `employees` (
	`employee_id`,
	`first_name`,
	`last_name`,
	`email`,
	`phone_number`,
	`job_id`,
	`salary`,
	`commission_pct`,
	`manager_id`,
	`department_id`,
	`hiredate`
)
VALUES
	(
		100,
		'Steven',
		'K_ing',
		'SKING',
		'515.123.4567',
		'AD_PRES',
		24000.00,
		NULL,
		NULL,
		90,
		'1992-04-03 00:00:00'
	),
	(
		101,
		'Neena',
		'Kochhar',
		'NKOCHHAR',
		'515.123.4568',
		'AD_VP',
		17000.00,
		NULL,
		100,
		90,
		'1992-04-03 00:00:00'
	),
	(
		102,
		'Lex',
		'De Haan',
		'LDEHAAN',
		'515.123.4569',
		'AD_VP',
		17000.00,
		NULL,
		100,
		90,
		'1992-04-03 00:00:00'
	),
	(
		103,
		'Alexander',
		'Hunold',
		'AHUNOLD',
		'590.423.4567',
		'IT_PROG',
		9000.00,
		NULL,
		102,
		60,
		'1992-04-03 00:00:00'
	),
	(
		104,
		'Bruce',
		'Ernst',
		'BERNST',
		'590.423.4568',
		'IT_PROG',
		6000.00,
		NULL,
		103,
		60,
		'1992-04-03 00:00:00'
	),
	(
		105,
		'David',
		'Austin',
		'DAUSTIN',
		'590.423.4569',
		'IT_PROG',
		4800.00,
		NULL,
		103,
		60,
		'1998-03-03 00:00:00'
	),
	(
		106,
		'Valli',
		'Pataballa',
		'VPATABAL',
		'590.423.4560',
		'IT_PROG',
		4800.00,
		NULL,
		103,
		60,
		'1998-03-03 00:00:00'
	),
	(
		107,
		'Diana',
		'Lorentz',
		'DLORENTZ',
		'590.423.5567',
		'IT_PROG',
		4200.00,
		NULL,
		103,
		60,
		'1998-03-03 00:00:00'
	),
	(
		108,
		'Nancy',
		'Greenberg',
		'NGREENBE',
		'515.124.4569',
		'FI_MGR',
		12000.00,
		NULL,
		101,
		100,
		'1998-03-03 00:00:00'
	),
	(
		109,
		'Daniel',
		'Faviet',
		'DFAVIET',
		'515.124.4169',
		'FI_ACCOUNT',
		9000.00,
		NULL,
		108,
		100,
		'1998-03-03 00:00:00'
	),
	(
		110,
		'John',
		'Chen',
		'JCHEN',
		'515.124.4269',
		'FI_ACCOUNT',
		8200.00,
		NULL,
		108,
		100,
		'2000-09-09 00:00:00'
	),
	(
		111,
		'Ismael',
		'Sciarra',
		'ISCIARRA',
		'515.124.4369',
		'FI_ACCOUNT',
		7700.00,
		NULL,
		108,
		100,
		'2000-09-09 00:00:00'
	),
	(
		112,
		'Jose Manuel',
		'Urman',
		'JMURMAN',
		'515.124.4469',
		'FI_ACCOUNT',
		7800.00,
		NULL,
		108,
		100,
		'2000-09-09 00:00:00'
	),
	(
		113,
		'Luis',
		'Popp',
		'LPOPP',
		'515.124.4567',
		'FI_ACCOUNT',
		6900.00,
		NULL,
		108,
		100,
		'2000-09-09 00:00:00'
	),
	(
		114,
		'Den',
		'Raphaely',
		'DRAPHEAL',
		'515.127.4561',
		'PU_MAN',
		11000.00,
		NULL,
		100,
		30,
		'2000-09-09 00:00:00'
	),
	(
		115,
		'Alexander',
		'Khoo',
		'AKHOO',
		'515.127.4562',
		'PU_CLERK',
		3100.00,
		NULL,
		114,
		30,
		'2000-09-09 00:00:00'
	),
	(
		116,
		'Shelli',
		'Baida',
		'SBAIDA',
		'515.127.4563',
		'PU_CLERK',
		2900.00,
		NULL,
		114,
		30,
		'2000-09-09 00:00:00'
	),
	(
		117,
		'Sigal',
		'Tobias',
		'STOBIAS',
		'515.127.4564',
		'PU_CLERK',
		2800.00,
		NULL,
		114,
		30,
		'2000-09-09 00:00:00'
	),
	(
		118,
		'Guy',
		'Himuro',
		'GHIMURO',
		'515.127.4565',
		'PU_CLERK',
		2600.00,
		NULL,
		114,
		30,
		'2000-09-09 00:00:00'
	),
	(
		119,
		'Karen',
		'Colmenares',
		'KCOLMENA',
		'515.127.4566',
		'PU_CLERK',
		2500.00,
		NULL,
		114,
		30,
		'2000-09-09 00:00:00'
	),
	(
		120,
		'Matthew',
		'Weiss',
		'MWEISS',
		'650.123.1234',
		'ST_MAN',
		8000.00,
		NULL,
		100,
		50,
		'2004-02-06 00:00:00'
	),
	(
		121,
		'Adam',
		'Fripp',
		'AFRIPP',
		'650.123.2234',
		'ST_MAN',
		8200.00,
		NULL,
		100,
		50,
		'2004-02-06 00:00:00'
	),
	(
		122,
		'Payam',
		'Kaufling',
		'PKAUFLIN',
		'650.123.3234',
		'ST_MAN',
		7900.00,
		NULL,
		100,
		50,
		'2004-02-06 00:00:00'
	),
	(
		123,
		'Shanta',
		'Vollman',
		'SVOLLMAN',
		'650.123.4234',
		'ST_MAN',
		6500.00,
		NULL,
		100,
		50,
		'2004-02-06 00:00:00'
	),
	(
		124,
		'Kevin',
		'Mourgos',
		'KMOURGOS',
		'650.123.5234',
		'ST_MAN',
		5800.00,
		NULL,
		100,
		50,
		'2004-02-06 00:00:00'
	),
	(
		125,
		'Julia',
		'Nayer',
		'JNAYER',
		'650.124.1214',
		'ST_CLERK',
		3200.00,
		NULL,
		120,
		50,
		'2004-02-06 00:00:00'
	),
	(
		126,
		'Irene',
		'Mikkilineni',
		'IMIKKILI',
		'650.124.1224',
		'ST_CLERK',
		2700.00,
		NULL,
		120,
		50,
		'2004-02-06 00:00:00'
	),
	(
		127,
		'James',
		'Landry',
		'JLANDRY',
		'650.124.1334',
		'ST_CLERK',
		2400.00,
		NULL,
		120,
		50,
		'2004-02-06 00:00:00'
	),
	(
		128,
		'Steven',
		'Markle',
		'SMARKLE',
		'650.124.1434',
		'ST_CLERK',
		2200.00,
		NULL,
		120,
		50,
		'2004-02-06 00:00:00'
	),
	(
		129,
		'Laura',
		'Bissot',
		'LBISSOT',
		'650.124.5234',
		'ST_CLERK',
		3300.00,
		NULL,
		121,
		50,
		'2004-02-06 00:00:00'
	),
	(
		130,
		'Mozhe',
		'Atkinson',
		'MATKINSO',
		'650.124.6234',
		'ST_CLERK',
		2800.00,
		NULL,
		121,
		50,
		'2004-02-06 00:00:00'
	),
	(
		131,
		'James',
		'Marlow',
		'JAMRLOW',
		'650.124.7234',
		'ST_CLERK',
		2500.00,
		NULL,
		121,
		50,
		'2004-02-06 00:00:00'
	),
	(
		132,
		'TJ',
		'Olson',
		'TJOLSON',
		'650.124.8234',
		'ST_CLERK',
		2100.00,
		NULL,
		121,
		50,
		'2004-02-06 00:00:00'
	),
	(
		133,
		'Jason',
		'Mallin',
		'JMALLIN',
		'650.127.1934',
		'ST_CLERK',
		3300.00,
		NULL,
		122,
		50,
		'2004-02-06 00:00:00'
	),
	(
		134,
		'Michael',
		'Rogers',
		'MROGERS',
		'650.127.1834',
		'ST_CLERK',
		2900.00,
		NULL,
		122,
		50,
		'2002-12-23 00:00:00'
	),
	(
		135,
		'Ki',
		'Gee',
		'KGEE',
		'650.127.1734',
		'ST_CLERK',
		2400.00,
		NULL,
		122,
		50,
		'2002-12-23 00:00:00'
	),
	(
		136,
		'Hazel',
		'Philtanker',
		'HPHILTAN',
		'650.127.1634',
		'ST_CLERK',
		2200.00,
		NULL,
		122,
		50,
		'2002-12-23 00:00:00'
	),
	(
		137,
		'Renske',
		'Ladwig',
		'RLADWIG',
		'650.121.1234',
		'ST_CLERK',
		3600.00,
		NULL,
		123,
		50,
		'2002-12-23 00:00:00'
	),
	(
		138,
		'Stephen',
		'Stiles',
		'SSTILES',
		'650.121.2034',
		'ST_CLERK',
		3200.00,
		NULL,
		123,
		50,
		'2002-12-23 00:00:00'
	),
	(
		139,
		'John',
		'Seo',
		'JSEO',
		'650.121.2019',
		'ST_CLERK',
		2700.00,
		NULL,
		123,
		50,
		'2002-12-23 00:00:00'
	),
	(
		140,
		'Joshua',
		'Patel',
		'JPATEL',
		'650.121.1834',
		'ST_CLERK',
		2500.00,
		NULL,
		123,
		50,
		'2002-12-23 00:00:00'
	),
	(
		141,
		'Trenna',
		'Rajs',
		'TRAJS',
		'650.121.8009',
		'ST_CLERK',
		3500.00,
		NULL,
		124,
		50,
		'2002-12-23 00:00:00'
	),
	(
		142,
		'Curtis',
		'Davies',
		'CDAVIES',
		'650.121.2994',
		'ST_CLERK',
		3100.00,
		NULL,
		124,
		50,
		'2002-12-23 00:00:00'
	),
	(
		143,
		'Randall',
		'Matos',
		'RMATOS',
		'650.121.2874',
		'ST_CLERK',
		2600.00,
		NULL,
		124,
		50,
		'2002-12-23 00:00:00'
	),
	(
		144,
		'Peter',
		'Vargas',
		'PVARGAS',
		'650.121.2004',
		'ST_CLERK',
		2500.00,
		NULL,
		124,
		50,
		'2002-12-23 00:00:00'
	),
	(
		145,
		'John',
		'Russell',
		'JRUSSEL',
		'011.44.1344.429268',
		'SA_MAN',
		14000.00,
		0.40,
		100,
		80,
		'2002-12-23 00:00:00'
	),
	(
		146,
		'Karen',
		'Partners',
		'KPARTNER',
		'011.44.1344.467268',
		'SA_MAN',
		13500.00,
		0.30,
		100,
		80,
		'2002-12-23 00:00:00'
	),
	(
		147,
		'Alberto',
		'Errazuriz',
		'AERRAZUR',
		'011.44.1344.429278',
		'SA_MAN',
		12000.00,
		0.30,
		100,
		80,
		'2002-12-23 00:00:00'
	),
	(
		148,
		'Gerald',
		'Cambrault',
		'GCAMBRAU',
		'011.44.1344.619268',
		'SA_MAN',
		11000.00,
		0.30,
		100,
		80,
		'2002-12-23 00:00:00'
	),
	(
		149,
		'Eleni',
		'Zlotkey',
		'EZLOTKEY',
		'011.44.1344.429018',
		'SA_MAN',
		10500.00,
		0.20,
		100,
		80,
		'2002-12-23 00:00:00'
	),
	(
		150,
		'Peter',
		'Tucker',
		'PTUCKER',
		'011.44.1344.129268',
		'SA_REP',
		10000.00,
		0.30,
		145,
		80,
		'2014-03-05 00:00:00'
	),
	(
		151,
		'David',
		'Bernstein',
		'DBERNSTE',
		'011.44.1344.345268',
		'SA_REP',
		9500.00,
		0.25,
		145,
		80,
		'2014-03-05 00:00:00'
	),
	(
		152,
		'Peter',
		'Hall',
		'PHALL',
		'011.44.1344.478968',
		'SA_REP',
		9000.00,
		0.25,
		145,
		80,
		'2014-03-05 00:00:00'
	),
	(
		153,
		'Christopher',
		'Olsen',
		'COLSEN',
		'011.44.1344.498718',
		'SA_REP',
		8000.00,
		0.20,
		145,
		80,
		'2014-03-05 00:00:00'
	),
	(
		154,
		'Nanette',
		'Cambrault',
		'NCAMBRAU',
		'011.44.1344.987668',
		'SA_REP',
		7500.00,
		0.20,
		145,
		80,
		'2014-03-05 00:00:00'
	),
	(
		155,
		'Oliver',
		'Tuvault',
		'OTUVAULT',
		'011.44.1344.486508',
		'SA_REP',
		7000.00,
		0.15,
		145,
		80,
		'2014-03-05 00:00:00'
	),
	(
		156,
		'Janette',
		'K_ing',
		'JKING',
		'011.44.1345.429268',
		'SA_REP',
		10000.00,
		0.35,
		146,
		80,
		'2014-03-05 00:00:00'
	),
	(
		157,
		'Patrick',
		'Sully',
		'PSULLY',
		'011.44.1345.929268',
		'SA_REP',
		9500.00,
		0.35,
		146,
		80,
		'2014-03-05 00:00:00'
	),
	(
		158,
		'Allan',
		'McEwen',
		'AMCEWEN',
		'011.44.1345.829268',
		'SA_REP',
		9000.00,
		0.35,
		146,
		80,
		'2014-03-05 00:00:00'
	),
	(
		159,
		'Lindsey',
		'Smith',
		'LSMITH',
		'011.44.1345.729268',
		'SA_REP',
		8000.00,
		0.30,
		146,
		80,
		'2014-03-05 00:00:00'
	),
	(
		160,
		'Louise',
		'Doran',
		'LDORAN',
		'011.44.1345.629268',
		'SA_REP',
		7500.00,
		0.30,
		146,
		80,
		'2014-03-05 00:00:00'
	),
	(
		161,
		'Sarath',
		'Sewall',
		'SSEWALL',
		'011.44.1345.529268',
		'SA_REP',
		7000.00,
		0.25,
		146,
		80,
		'2014-03-05 00:00:00'
	),
	(
		162,
		'Clara',
		'Vishney',
		'CVISHNEY',
		'011.44.1346.129268',
		'SA_REP',
		10500.00,
		0.25,
		147,
		80,
		'2014-03-05 00:00:00'
	),
	(
		163,
		'Danielle',
		'Greene',
		'DGREENE',
		'011.44.1346.229268',
		'SA_REP',
		9500.00,
		0.15,
		147,
		80,
		'2014-03-05 00:00:00'
	),
	(
		164,
		'Mattea',
		'Marvins',
		'MMARVINS',
		'011.44.1346.329268',
		'SA_REP',
		7200.00,
		0.10,
		147,
		80,
		'2014-03-05 00:00:00'
	),
	(
		165,
		'David',
		'Lee',
		'DLEE',
		'011.44.1346.529268',
		'SA_REP',
		6800.00,
		0.10,
		147,
		80,
		'2014-03-05 00:00:00'
	),
	(
		166,
		'Sundar',
		'Ande',
		'SANDE',
		'011.44.1346.629268',
		'SA_REP',
		6400.00,
		0.10,
		147,
		80,
		'2014-03-05 00:00:00'
	),
	(
		167,
		'Amit',
		'Banda',
		'ABANDA',
		'011.44.1346.729268',
		'SA_REP',
		6200.00,
		0.10,
		147,
		80,
		'2014-03-05 00:00:00'
	),
	(
		168,
		'Lisa',
		'Ozer',
		'LOZER',
		'011.44.1343.929268',
		'SA_REP',
		11500.00,
		0.25,
		148,
		80,
		'2014-03-05 00:00:00'
	),
	(
		169,
		'Harrison',
		'Bloom',
		'HBLOOM',
		'011.44.1343.829268',
		'SA_REP',
		10000.00,
		0.20,
		148,
		80,
		'2014-03-05 00:00:00'
	),
	(
		170,
		'Tayler',
		'Fox',
		'TFOX',
		'011.44.1343.729268',
		'SA_REP',
		9600.00,
		0.20,
		148,
		80,
		'2014-03-05 00:00:00'
	),
	(
		171,
		'William',
		'Smith',
		'WSMITH',
		'011.44.1343.629268',
		'SA_REP',
		7400.00,
		0.15,
		148,
		80,
		'2014-03-05 00:00:00'
	),
	(
		172,
		'Elizabeth',
		'Bates',
		'EBATES',
		'011.44.1343.529268',
		'SA_REP',
		7300.00,
		0.15,
		148,
		80,
		'2014-03-05 00:00:00'
	),
	(
		173,
		'Sundita',
		'Kumar',
		'SKUMAR',
		'011.44.1343.329268',
		'SA_REP',
		6100.00,
		0.10,
		148,
		80,
		'2014-03-05 00:00:00'
	),
	(
		174,
		'Ellen',
		'Abel',
		'EABEL',
		'011.44.1644.429267',
		'SA_REP',
		11000.00,
		0.30,
		149,
		80,
		'2014-03-05 00:00:00'
	),
	(
		175,
		'Alyssa',
		'Hutton',
		'AHUTTON',
		'011.44.1644.429266',
		'SA_REP',
		8800.00,
		0.25,
		149,
		80,
		'2014-03-05 00:00:00'
	),
	(
		176,
		'Jonathon',
		'Taylor',
		'JTAYLOR',
		'011.44.1644.429265',
		'SA_REP',
		8600.00,
		0.20,
		149,
		80,
		'2014-03-05 00:00:00'
	),
	(
		177,
		'Jack',
		'Livingston',
		'JLIVINGS',
		'011.44.1644.429264',
		'SA_REP',
		8400.00,
		0.20,
		149,
		80,
		'2014-03-05 00:00:00'
	),
	(
		178,
		'Kimberely',
		'Grant',
		'KGRANT',
		'011.44.1644.429263',
		'SA_REP',
		7000.00,
		0.15,
		149,
		NULL,
		'2014-03-05 00:00:00'
	),
	(
		179,
		'Charles',
		'Johnson',
		'CJOHNSON',
		'011.44.1644.429262',
		'SA_REP',
		6200.00,
		0.10,
		149,
		80,
		'2014-03-05 00:00:00'
	),
	(
		180,
		'Winston',
		'Taylor',
		'WTAYLOR',
		'650.507.9876',
		'SH_CLERK',
		3200.00,
		NULL,
		120,
		50,
		'2014-03-05 00:00:00'
	),
	(
		181,
		'Jean',
		'Fleaur',
		'JFLEAUR',
		'650.507.9877',
		'SH_CLERK',
		3100.00,
		NULL,
		120,
		50,
		'2014-03-05 00:00:00'
	),
	(
		182,
		'Martha',
		'Sullivan',
		'MSULLIVA',
		'650.507.9878',
		'SH_CLERK',
		2500.00,
		NULL,
		120,
		50,
		'2014-03-05 00:00:00'
	),
	(
		183,
		'Girard',
		'Geoni',
		'GGEONI',
		'650.507.9879',
		'SH_CLERK',
		2800.00,
		NULL,
		120,
		50,
		'2014-03-05 00:00:00'
	),
	(
		184,
		'Nandita',
		'Sarchand',
		'NSARCHAN',
		'650.509.1876',
		'SH_CLERK',
		4200.00,
		NULL,
		121,
		50,
		'2014-03-05 00:00:00'
	),
	(
		185,
		'Alexis',
		'Bull',
		'ABULL',
		'650.509.2876',
		'SH_CLERK',
		4100.00,
		NULL,
		121,
		50,
		'2014-03-05 00:00:00'
	),
	(
		186,
		'Julia',
		'Dellinger',
		'JDELLING',
		'650.509.3876',
		'SH_CLERK',
		3400.00,
		NULL,
		121,
		50,
		'2014-03-05 00:00:00'
	),
	(
		187,
		'Anthony',
		'Cabrio',
		'ACABRIO',
		'650.509.4876',
		'SH_CLERK',
		3000.00,
		NULL,
		121,
		50,
		'2014-03-05 00:00:00'
	),
	(
		188,
		'Kelly',
		'Chung',
		'KCHUNG',
		'650.505.1876',
		'SH_CLERK',
		3800.00,
		NULL,
		122,
		50,
		'2014-03-05 00:00:00'
	),
	(
		189,
		'Jennifer',
		'Dilly',
		'JDILLY',
		'650.505.2876',
		'SH_CLERK',
		3600.00,
		NULL,
		122,
		50,
		'2014-03-05 00:00:00'
	),
	(
		190,
		'Timothy',
		'Gates',
		'TGATES',
		'650.505.3876',
		'SH_CLERK',
		2900.00,
		NULL,
		122,
		50,
		'2014-03-05 00:00:00'
	),
	(
		191,
		'Randall',
		'Perkins',
		'RPERKINS',
		'650.505.4876',
		'SH_CLERK',
		2500.00,
		NULL,
		122,
		50,
		'2014-03-05 00:00:00'
	),
	(
		192,
		'Sarah',
		'Bell',
		'SBELL',
		'650.501.1876',
		'SH_CLERK',
		4000.00,
		NULL,
		123,
		50,
		'2014-03-05 00:00:00'
	),
	(
		193,
		'Britney',
		'Everett',
		'BEVERETT',
		'650.501.2876',
		'SH_CLERK',
		3900.00,
		NULL,
		123,
		50,
		'2014-03-05 00:00:00'
	),
	(
		194,
		'Samuel',
		'McCain',
		'SMCCAIN',
		'650.501.3876',
		'SH_CLERK',
		3200.00,
		NULL,
		123,
		50,
		'2014-03-05 00:00:00'
	),
	(
		195,
		'Vance',
		'Jones',
		'VJONES',
		'650.501.4876',
		'SH_CLERK',
		2800.00,
		NULL,
		123,
		50,
		'2014-03-05 00:00:00'
	),
	(
		196,
		'Alana',
		'Walsh',
		'AWALSH',
		'650.507.9811',
		'SH_CLERK',
		3100.00,
		NULL,
		124,
		50,
		'2014-03-05 00:00:00'
	),
	(
		197,
		'Kevin',
		'Feeney',
		'KFEENEY',
		'650.507.9822',
		'SH_CLERK',
		3000.00,
		NULL,
		124,
		50,
		'2014-03-05 00:00:00'
	),
	(
		198,
		'Donald',
		'OConnell',
		'DOCONNEL',
		'650.507.9833',
		'SH_CLERK',
		2600.00,
		NULL,
		124,
		50,
		'2014-03-05 00:00:00'
	),
	(
		199,
		'Douglas',
		'Grant',
		'DGRANT',
		'650.507.9844',
		'SH_CLERK',
		2600.00,
		NULL,
		124,
		50,
		'2014-03-05 00:00:00'
	),
	(
		200,
		'Jennifer',
		'Whalen',
		'JWHALEN',
		'515.123.4444',
		'AD_ASST',
		4400.00,
		NULL,
		101,
		10,
		'2016-03-03 00:00:00'
	),
	(
		201,
		'Michael',
		'Hartstein',
		'MHARTSTE',
		'515.123.5555',
		'MK_MAN',
		13000.00,
		NULL,
		100,
		20,
		'2016-03-03 00:00:00'
	),
	(
		202,
		'Pat',
		'Fay',
		'PFAY',
		'603.123.6666',
		'MK_REP',
		6000.00,
		NULL,
		201,
		20,
		'2016-03-03 00:00:00'
	),
	(
		203,
		'Susan',
		'Mavris',
		'SMAVRIS',
		'515.123.7777',
		'HR_REP',
		6500.00,
		NULL,
		101,
		40,
		'2016-03-03 00:00:00'
	),
	(
		204,
		'Hermann',
		'Baer',
		'HBAER',
		'515.123.8888',
		'PR_REP',
		10000.00,
		NULL,
		101,
		70,
		'2016-03-03 00:00:00'
	),
	(
		205,
		'Shelley',
		'Higgins',
		'SHIGGINS',
		'515.123.8080',
		'AC_MGR',
		12000.00,
		NULL,
		101,
		110,
		'2016-03-03 00:00:00'
	),
	(
		206,
		'William',
		'Gietz',
		'WGIETZ',
		'515.123.8181',
		'AC_ACCOUNT',
		8300.00,
		NULL,
		205,
		110,
		'2016-03-03 00:00:00'
	);

职位信息表 jobs

在这里插入图片描述

/*职位信息表*/
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL comment '职位id',
  `job_title` varchar(35) DEFAULT NULL comment '职位名称',
  `min_salary` int(6) DEFAULT NULL comment '薪资范围最小值',
  `max_salary` int(6) DEFAULT NULL comment '薪资范围最大值',
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB comment '职位id';

INSERT INTO `jobs` (
	`job_id`,
	`job_title`,
	`min_salary`,
	`max_salary`
)
VALUES
	(
		'AC_ACCOUNT',
		'Public Accountant',
		4200,
		9000
	),
	(
		'AC_MGR',
		'Accounting Manager',
		8200,
		16000
	),
	(
		'AD_ASST',
		'Administration Assistant',
		3000,
		6000
	),
	(
		'AD_PRES',
		'President',
		20000,
		40000
	),
	(
		'AD_VP',
		'Administration Vice President',
		15000,
		30000
	),
	(
		'FI_ACCOUNT',
		'Accountant',
		4200,
		9000
	),
	(
		'FI_MGR',
		'Finance Manager',
		8200,
		16000
	),
	(
		'HR_REP',
		'Human Resources Representative',
		4000,
		9000
	),
	(
		'IT_PROG',
		'Programmer',
		4000,
		10000
	),
	(
		'MK_MAN',
		'Marketing Manager',
		9000,
		15000
	),
	(
		'MK_REP',
		'Marketing Representative',
		4000,
		9000
	),
	(
		'PR_REP',
		'Public Relations Representative',
		4500,
		10500
	),
	(
		'PU_CLERK',
		'Purchasing Clerk',
		2500,
		5500
	),
	(
		'PU_MAN',
		'Purchasing Manager',
		8000,
		15000
	),
	(
		'SA_MAN',
		'Sales Manager',
		10000,
		20000
	),
	(
		'SA_REP',
		'Sales Representative',
		6000,
		12000
	),
	(
		'SH_CLERK',
		'Shipping Clerk',
		2500,
		5500
	),
	(
		'ST_CLERK',
		'Stock Clerk',
		2000,
		5000
	),
	(
		'ST_MAN',
		'Stock Manager',
		5500,
		8500
	);

位置表 locations

在这里插入图片描述

/*位置表*/
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT comment '位置id',
  `street_address` varchar(40) DEFAULT NULL comment '街道地址',
  `postal_code` varchar(12) DEFAULT NULL comment '邮编',
  `city` varchar(30) DEFAULT NULL comment '城市名称',
  `state_province` varchar(25) DEFAULT NULL comment '省',
  `country_id` varchar(2) DEFAULT NULL comment '国家编号',
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 comment '位置表';

INSERT INTO `locations` (
	`location_id`,
	`street_address`,
	`postal_code`,
	`city`,
	`state_province`,
	`country_id`
)
VALUES
	(
		1000,
		'1297 Via Cola di Rie',
		'00989',
		'Roma',
		NULL,
		'IT'
	),
	(
		1100,
		'93091 Calle della Testa',
		'10934',
		'Venice',
		NULL,
		'IT'
	),
	(
		1200,
		'2017 Shinjuku-ku',
		'1689',
		'Tokyo',
		'Tokyo Prefecture',
		'JP'
	),
	(
		1300,
		'9450 Kamiya-cho',
		'6823',
		'Hiroshima',
		NULL,
		'JP'
	),
	(
		1400,
		'2014 Jabberwocky Rd',
		'26192',
		'Southlake',
		'Texas',
		'US'
	),
	(
		1500,
		'2011 Interiors Blvd',
		'99236',
		'South San Francisco',
		'California',
		'US'
	),
	(
		1600,
		'2007 Zagora St',
		'50090',
		'South Brunswick',
		'New Jersey',
		'US'
	),
	(
		1700,
		'2004 Charade Rd',
		'98199',
		'Seattle',
		'Washington',
		'US'
	),
	(
		1800,
		'147 Spadina Ave',
		'M5V 2L7',
		'Toronto',
		'Ontario',
		'CA'
	),
	(
		1900,
		'6092 Boxwood St',
		'YSW 9T2',
		'Whitehorse',
		'Yukon',
		'CA'
	),
	(
		2000,
		'40-5-12 Laogianggen',
		'190518',
		'Beijing',
		NULL,
		'CN'
	),
	(
		2100,
		'1298 Vileparle (E)',
		'490231',
		'Bombay',
		'Maharashtra',
		'IN'
	),
	(
		2200,
		'12-98 Victoria Street',
		'2901',
		'Sydney',
		'New South Wales',
		'AU'
	),
	(
		2300,
		'198 Clementi North',
		'540198',
		'Singapore',
		NULL,
		'SG'
	),
	(
		2400,
		'8204 Arthur St',
		NULL,
		'London',
		NULL,
		'UK'
	),
	(
		2500,
		'Magdalen Centre, The Oxford Science Park',
		'OX9 9ZB',
		'Oxford',
		'Oxford',
		'UK'
	),
	(
		2600,
		'9702 Chester Road',
		'09629850293',
		'Stretford',
		'Manchester',
		'UK'
	),
	(
		2700,
		'Schwanthalerstr. 7031',
		'80925',
		'Munich',
		'Bavaria',
		'DE'
	),
	(
		2800,
		'Rua Frei Caneca 1360 ',
		'01307-002',
		'Sao Paulo',
		'Sao Paulo',
		'BR'
	),
	(
		2900,
		'20 Rue des Corps-Saints',
		'1730',
		'Geneva',
		'Geneve',
		'CH'
	),
	(
		3000,
		'Murtenstrasse 921',
		'3095',
		'Bern',
		'BE',
		'CH'
	),
	(
		3100,
		'Pieter Breughelstraat 837',
		'3029SK',
		'Utrecht',
		'Utrecht',
		'NL'
	),
	(
		3200,
		'Mariano Escobedo 9991',
		'11932',
		'Mexico City',
		'Distrito Federal,',
		'MX'
	);

薪资等级表 job_grades

在这里插入图片描述

/*薪资等级表*/
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades`(
  `grade_level` varchar(3) comment '等级',
  `lowest_sal`  int comment '薪资最低值',
  `highest_sal` int comment '薪资最高值',
  PRIMARY KEY (`grade_level`)
) comment '薪资等级表';

INSERT INTO job_grades
VALUES
	('A', 1000, 2999),
	('B', 3000, 5999),
	('C', 6000, 9999),
	('D', 10000, 14999),
	('E', 15000, 24999),
	('F', 25000, 40000);

查询数据示例

1 标量子查询（结果集只有一行一列）
2 列子查询（结果集只有一列多行）
3 行子查询（结果集有一行多列）
4 表子查询（结果集一般为多列多行）

select 后面：仅支持标量子查询
  from 后面：支持表子查询

where 或者 having 后面：
支持标量子查询（单行单列）、
列子查询（单列多行）、
行子查询（多列多行）

 exists后面（即相关子查询）： 表子查询（多行多列）

select 后面的子查询

子查询位于 select 后面的，仅仅支持标量子查询。

示例1 查询每个部门员工人数

SELECT
	a.*, (
		SELECT
			count(*)
		FROM
			employees b
		WHERE
			b.department_id = a.department_id
	) AS 员工人数
FROM
	departments a;

在这里插入图片描述

示例2 查询员工号 =102 的部门名称

select (
	select a.department_name
	from departments a,employees b 
	where a.department_id = b.department_id and b.employee_id = 102
	)
as 部门名称

在这里插入图片描述

from 后面的子查询

将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到。

然后将真实的表和子查询结果表进行连接查询。

示例1 查询每个部门平均工资的薪资等级

-- 查询每个部门的平均工资
SELECT department_id,AVG(a.salary)
FROM employees a
GROUP BY a.department_id


-- 薪资等级表
SELECT * from job_grades;

-- 将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal;

SELECT 
	t1.department_id,
	sa AS '平均工资',
	t2.grade_level
FROM (SELECT department_id,AVG(a.salary) sa
			FROM employees a
			GROUP BY a.department_id
			) t1, job_grades t2
WHERE
	t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;

在这里插入图片描述

where 和 having 后面的子查询

1.标量子查询（单行单列行子查询）

2.列子查询（单列多行子查询）

3.行子查询（一行多列）

一些特点

1 子查询放在小括号内 2 子查询一般放在条件的右侧 3 标量子查询，一般搭配着单行单列相关的操作符使用 >、=、some(10,20,30)， a 大于子查询中任意一个即可，a 大于子查询中最小值即可，等同于 a > min(10,20,30) all：和子查询返回的“所有值”比较，比如 a > all(10,20,30)， a 大于子查询中所有值，换句话说，a 大于子查询中最大值即可满足查询条件，等同于 a > max(10,20,30);

5 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

in、any、some、all

in，any，some，all 分别是子查询关键词之一。

in：in 常用于 where 表达式中，其作用是查询某个范围内的数据。

any 和 some 一样： 
可以与  =、>、>=、=、 (
	SELECT
		salary
	FROM
		employees
	WHERE
		employee_id = 143
);

在这里插入图片描述

子查询加分组查询，查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资【having】

查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资【having】

① 查询 50 号部门的最低工资

SELECT MIN(salary) FROM employees where department_id = 50

在这里插入图片描述 ② 查询每个部门的最低工资

SELECT MIN(salary),department_id FROM employees GROUP BY department_id

在这里插入图片描述 ③ 在 ② 的基础上筛选，满足 min(salary) > ①

SELECT
	MIN(a.salary) minsalary,
	department_id
FROM
	employees a
GROUP BY
	a.department_id
HAVING
	MIN(a.salary) > (
		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			department_id = 50
	)

在这里插入图片描述

列子查询

列子查询结果集一列多行。

列子查询需要搭配多行操作符使用：in(not in)、any/some、all

为了提升效率，最好去重一下 distinct 关键字。

示例1，返回 location_id 是 1400 或 1700 的部门中的所有员工姓名方式 1

① 先从 depatments 表中查出 location_id 是 1400 或 1700 的部门编号

SELECT DISTINCT
	department_id
FROM
	departments
WHERE
	location_id IN (1400, 1700);

在这里插入图片描述查询员工姓名，要求其中员工 department_id 在 ① 中

SELECT
	a.last_name
FROM
	employees a
WHERE
	a.department_id IN (
		SELECT DISTINCT
			department_id
		FROM
			departments
		WHERE
			location_id IN (1400, 1700)
	)

在这里插入图片描述

方式 2：使用 any 实现 (注意符号)

SELECT
	a.last_name
FROM
	employees a
WHERE
	a.department_id = ANY (
		SELECT DISTINCT
			department_id
		FROM
			departments
		WHERE
			location_id IN (1400, 1700)
	)

在这里插入图片描述

ALL 等同于 not in

SELECT
	a.last_name
FROM
	employees a
WHERE
	a.department_id  ALL (
		SELECT DISTINCT
			department_id
		FROM
			departments
		WHERE
			location_id IN (1400, 1700)
	)

在这里插入图片描述

示例2，返回其他工种中比 job_id 为 ‘IT_PROG’ 工种任意工资低的员工的员工号、姓名、job_id、salary

1 查询出 job_id 为 IT_ROG 工种的所有工资

SELECT salary FROM employees WHERE job_id = 'IT_PROG'

在这里插入图片描述也就是说要小于上面查询出的工资列表中的任意一个。

查询员工的员工号、姓名、job_id、salary，且 salary 小于 1 中的任意一个。

SELECT
	last_name,
	employee_id,
	job_id,
	salary
FROM
	employees
WHERE
	salary

MySQL 子查询使用方式

最近更新

热门博客

[ 申请 ]友情链接：