【MySQL】子查询(本篇文章非常重要,高手必备)

·  阅读 23

本文已参与「新人创作礼」活动,一起开启掘金创作之路。

这是Mysql系列第12篇。

环境:mysql5.7.25,cmd命令中进行演示。

本章节非常重要。

子查询

出现在select语句中的select语句,称为子查询或内查询。

外部的select查询语句,称为主查询或外查询。

子查询分类

按结果集的行列数不同分为4种

  • 标量子查询(结果集只有一行一列)
  • 列子查询(结果集只有一列多行)
  • 行子查询(结果集有一行多列)
  • 表子查询(结果集一般为多行多列)

按子查询出现在主查询中的不同位置分

  • select后面:仅仅支持标量子查询。
  • from后面:支持表子查询。
  • where或having后面:支持标量子查询(单列单行)、列子查询(单列多行)、行子查询(多列多行)
  • exists后面(即相关子查询) :表子查询(多行、多列)

准备测试数据

测试数据,有点多

drop database if exists `tengteng`;
create database `tengteng`;
USE `tengteng`;

/*部门表*/
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);

/*员工表*/
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');

/*职位信息表*/
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);

/*位置表*/
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');

/*薪资等级表*/
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);
复制代码

mysql中执行里面的tengteng_employees库部分的脚本。

成功创建tengteng_employees库及5张表,如下:

表名描述
departments部门表
employees员工信息表
jobs职位信息表
locations位置表(部门表中会用到)
job_grades薪资等级表

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;
复制代码

运行最后一条结果如下:

mysql> 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;
+---------------+--------------+-------------+
| department_id | 平均工资     | grade_level |
+---------------+--------------+-------------+
|          NULL |  7000.000000 | C           |
|            10 |  4400.000000 | B           |
|            20 |  9500.000000 | C           |
|            30 |  4150.000000 | B           |
|            40 |  6500.000000 | C           |
|            50 |  3475.555556 | B           |
|            60 |  5760.000000 | B           |
|            70 | 10000.000000 | D           |
|            80 |  8955.882353 | C           |
|            90 | 19333.333333 | E           |
|           100 |  8600.000000 | C           |
|           110 | 10150.000000 | D           |
+---------------+--------------+-------------+
12 rows in set (0.00 sec)
复制代码

where和having后面的子查询

where或having后面,可以使用

  1. 标量子查询(单行单列行子查询)
  2. 列子查询(单列多行子查询)
  3. 行子查询(一行多列)

特点

  1. 子查询放在小括号内。

  2. 子查询一般放在条件的右侧。

  3. 标量子查询,一般搭配着单行单列操作符使用  >、<、>=、<=、=、<>、!=

  4. 列子查询,一般搭配着多行操作符使用

    in(not in):列表中的“任意一个”

    any或者some:和子查询返回的“某一个值”比较,比如a>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. 子查询的执行优先于主查询执行,因为主查询的条件用到了子查询的结果。

mysql中的in、any、some、all

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

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

any和some一样: 可以与=、>、>=、<、<=、<>结合起来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all:可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

下文中会经常用到这些关键字。

标量子查询

一般标量子查询,示例

查询谁的工资比Abel的高?

/*①查询abel的工资【改查询是标量子查询】*/
SELECT salary FROM employees WHERE last_name = 'Abel';

/*②查询员工信息,满足salary>①的结果*/
SELECT *FROM employees aWHERE a.salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
复制代码

多个标量子查询,示例

返回job_id与141号员工相同,salary比143号员工多的员工、姓名、job_id和工资

/*返回job_id与141号员工相同,salary比143号员工多的员工、姓名、job_id和工资*/
/*①查询141号员工的job_id*/
SELECT job_id FROM employees WHERE employee_id = 141;

/*②查询143好员工的salary*/
SELECT salary FROM employees WHERE employee_id = 143;

/*③查询员工的姓名、job_id、工资,要求job_id=① and salary>②*/
SELECT 
    a.last_name 姓名,  a.job_id,  a.salary  工资
FROM 
    employees a
WHERE 
    a.job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND  a.salary > 
    (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_idFROM employeesGROUP 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);
复制代码

错误的标量子查询,示例

将上面的示例③中子查询语句中的min(salary)改为salary,执行效果如下:

mysql> SELECT         
            min(a.salary) minsalary, department_id 
       FROM  
            employees a 
       GROUP BY 
            a.department_id  
       HAVING 
           min(a.salary) > (SELECT salary  FROM employees  WHERE department_id = 500000);
ERROR 1242 (21000): Subquery returns more than 1 row
复制代码

错误提示:子查询返回的结果超过了1行记录。

说明:上面的子查询只支持最多一列一行记录

列子查询(子查询结果集一列多行)

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

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

示例1

返回location_id是1400或1700的部门中的所有员工姓名

/*返回location_id是1400或1700的部门中的所有员工姓名*/
/*方式1*/
/*①查询location_id是1400或1700的部门编号*/
SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700);

/*②查询员工姓名,要求部门是①列表中的某一个*/
SELECT a.last_name FROM employees aWHERE 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));

/*拓展,下面与not in等价*/
SELECT a.last_nameFROM employees aWHERE a.department_id <> ALL (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700));
复制代码

示例2

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

/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
/*①查询job_id为'IT_PROG'部门任-工资*/
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG';

/*②查询员工号、姓名、job_id、salary,slary<①的任意一个*/
SELECT  last_name,  employee_id,  job_id,  salary FROM employeesWHERE salary < ANY 
(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

/*或者*/
SELECT  last_name,  employee_id,  job_id,  salary FROM employeesWHERE salary < 
(SELECT max(salary) FROM employees  WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
复制代码

示例3

返回其他工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id、salary

/*返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary*/
SELECT  last_name,  employee_id,  job_id,  salary FROM employees WHERE salary < ALL
(SELECT DISTINCT salary  FROM employees  WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

/*或者*/
SELECT  last_name,  employee_id,  job_id,  salaryFROM employeesWHERE salary < 
(SELECT min(salary) FROM employees  WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
复制代码

行子查询(子查询结果集一行多列)

示例

查询员工编号最小并且工资最高的员工信息,3种方式。

/*查询员工编号最小并且工资最高的员工信息*/
/*①查询最小的员工编号*/
SELECT min(employee_id) FROM employees;

/*②查询最高工资*/
SELECT max(salary) FROM employees;

/*③方式1:查询员工信息*/
SELECT * FROM employees a WHERE a.employee_id = 
(SELECT min(employee_id) FROM employees) AND salary = (SELECT max(salary) FROM employees);

/*方式2*/
SELECT * FROM employees a WHERE (a.employee_id, a.salary) = 
(SELECT  min(employee_id),  max(salary) FROM employees);

/*方式3*/
SELECT * FROM employees a WHERE (a.employee_id, a.salary) in 
(SELECT  min(employee_id),   max(salary)  FROM employees);
复制代码

方式1比较常见,方式2、3更简洁。

exists后面(也叫做相关子查询)

  1. 语法:exists(完整的查询语句)。
  2. exists查询结果:1或0,exists查询的结果用来判断子查询的结果集中是否有值。
  3. 一般来说,能用exists的子查询,绝对都能用in代替,所以exists用的少。
  4. 和前面的查询不同,这先执行主查询,然后主查询查询的结果,在根据子查询进行过滤,子查询中涉及到主查询中用到的字段,所以叫相关子查询。

示例1

简单示例

mysql> SELECT
            exists(SELECT employee_id     
       FROM 
            employees 
       WHERE 
           salary = 300000) AS 'exists返回1或者0';
+----------------------+
| exists返回1或者0     |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)
复制代码

示例2

查询所有员工的部门名称

/*exists入门案例*/
SELECT exists(SELECT employee_id FROM employees WHERE salary = 300000) AS 'exists返回1或者0';


/*查询所有员工部门名*/

SELECT department_nameFROM departments a WHERE exists(SELECT 1  FROM employees b WHERE a.department_id = b.department_id);


/*使用in实现*/
SELECT department_name FROM departments a WHERE a.department_id IN  (SELECT department_id  FROM employees);
                                     
复制代码

示例3

查询没有员工的部门

/*查询没有员工的部门*/
/*exists实现*/
SELECT * FROM departments a WHERE NOT exists
(SELECT 1 FROM employees b WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);

/*in的方式*/
SELECT * FROM departments a WHERE a.department_id NOT IN (SELECT department_id FROM employees b  WHERE b.department_id IS NOT NULL);
复制代码

上面脚本中有b.department_id IS NOT NULL,为什么,有大坑,向下看。

NULL的大坑

示例1

使用not in的方式查询没有员工的部门,如下:

SELECT * FROM departments a WHERE a.department_id NOT IN (SELECT department_id   FROM employees b);                           
复制代码

运行结果:

mysql> SELECT * 
-> FROM departments a   
-> WHERE a.department_id NOT IN (SELECT department_id  
-> FROM employees b);
Empty set (0.00 sec)
复制代码

not in的情况下,子查询中列的值为NULL的时候,外查询的结果为空。

建议:建表是,列不允许为空。

总结

  1. 本文中讲解了常见的子查询,请大家务必多练习
  2. 注意in、any、some、all的用法
  3. 字段值为NULL的时候,not in查询有大坑,这个要注意
  4. 建议创建表的时候,列不允许为空
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改