當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL 子查询使用方式

發布時間：2023/12/14 数据库 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 查詢每個部門員工人數

SELECTa.*, (SELECTcount(*)FROMemployees bWHEREb.department_id = a.department_id) AS 員工人數 FROMdepartments a;

示例2 查詢員工號 =102 的部門名稱

select (select a.department_namefrom 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) saFROM employees aGROUP BY a.department_id) t1, job_grades t2 WHEREt1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;

where 和 having 后面的子查詢

1.標量子查詢（單行單列行子查詢）

2.列子查詢（單列多行子查詢）

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

一些特點

1 子查詢放在小括號內
2 子查詢一般放在條件的右側
3 標量子查詢，一般搭配著單行單列相關的操作符使用 >、<、>=、<=、<>、!=
4 列子查詢，一般搭配多行操作符使用

in(not in)：列表中的“任意一個”any 或 some (some 是 any 的別名)：和子查詢返回的“某一個值”比較，比如 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 子查詢的執行優先于主查詢執行，因為主查詢的條件用到了子查詢的結果。

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 滿足大于 Abel 工資的結果*/ SELECT* FROMemployees a WHEREa.salary > (SELECTsalaryFROMemployeesWHERElast_name = 'Abel');

多個標量子查詢，返回 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 和工資，要求與 141 號員工的 job_id 相等，比 143 號員工的工資高 */SELECTa.last_name 姓名,a.job_id,a.salary 工資 FROMemployees a WHEREa.job_id = (SELECTjob_idFROMemployeesWHEREemployee_id = 141) AND a.salary > (SELECTsalaryFROMemployeesWHEREemployee_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) > ①

SELECTMIN(a.salary) minsalary,department_id FROMemployees a GROUP BYa.department_id HAVINGMIN(a.salary) > (SELECTMIN(salary)FROMemployeesWHEREdepartment_id = 50)

列子查詢

列子查詢結果集一列多行。

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

為了提升效率，最好去重一下 distinct 關鍵字。

示例1，返回 location_id 是 1400 或 1700 的部門中的所有員工姓名

方式 1

① 先從 depatments 表中查出 location_id 是 1400 或 1700 的部門編號

SELECT DISTINCTdepartment_id FROMdepartments WHERElocation_id IN (1400, 1700);

查詢員工姓名，要求其中員工 department_id 在 ① 中

SELECTa.last_name FROMemployees a WHEREa.department_id IN (SELECT DISTINCTdepartment_idFROMdepartmentsWHERElocation_id IN (1400, 1700))

方式 2：使用 any 實現 (注意符號)

SELECTa.last_name FROMemployees a WHEREa.department_id = ANY (SELECT DISTINCTdepartment_idFROMdepartmentsWHERElocation_id IN (1400, 1700))

<> ALL 等同于 not in

SELECTa.last_name FROMemployees a WHEREa.department_id <> ALL (SELECT DISTINCTdepartment_idFROMdepartmentsWHERElocation_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 中的任意一個。

SELECTlast_name,employee_id,job_id,salary FROMemployees WHEREsalary < ANY (SELECT DISTINCTsalaryFROMemployeesWHEREjob_id = 'IT_PROG')

或者說 salary 小于 1 表 salary 的最大值。

SELECTlast_name,employee_id,job_id,salary FROMemployees WHEREsalary < (SELECTMAX(salary)FROMemployeesWHEREjob_id = 'IT_PROG')

示例3，返回其他工種中比 job_id 為 ‘IT_PROG’ 部門所有工資低的員工的員工號、姓名、job_id、salary

SELECTlast_name,employee_id,job_id,salary FROMemployees WHEREsalary < ALL (SELECT DISTINCTsalaryFROMemployeesWHEREjob_id = 'IT_PROG') AND job_id != 'IT_PROG';

或

SELECTlast_name,employee_id,job_id,salary FROMemployees WHEREsalary < (SELECTMIN(salary)FROMemployeesWHEREjob_id = 'IT_PROG') AND job_id != 'IT_PROG';

查詢結果和上面一樣。

行子查詢

子查詢結果集為一行多列。

示例，查詢員工編號最小并且工資最高的員工信息，3 種方式。

1 先查詢出最小的員工編號

SELECT MIN(employee_id) FROM employees

2 查詢工資最高的員工信息

SELECT MAX(salary)from employees

方法 1

SELECT* FROMemployees a WHEREa.employee_id = (SELECTMIN(employee_id)FROMemployees) AND salary = (SELECTMAX(salary)FROMemployees )

方法 2

SELECT* FROMemployees a WHERE(a.employee_id, a.salary) = (SELECTMIN(employee_id),MAX(salary)FROMemployees)

方法 3

SELECT* FROMemployees a WHERE(a.employee_id, a.salary) IN (SELECTMIN(employee_id),MAX(salary)FROMemployees)

exists 后面（也叫相關子查詢）

exists | not exists

當 exists 在 where 后面時，
用于判斷子查詢的結果集是否為空，
若子查詢的結果集不為空，返回 TRUE，否則返回 FALSE；
若使用關鍵字 NOT，則返回的值正好相反。

1 語法：exists (完整的查詢語句)
2 exists 查詢結果：1 或 0，exists 的結果用來判斷
3 一般來說，能用 exists 的子查詢，絕對都能用 in 代替，所以 exists 用的少

4 和前面的查詢不同，這先執行主查詢，然后主查詢查詢的結果，在根據子查詢進行過濾，子查詢中涉及到主查詢中用到的字段，所以叫相關子查詢。

示例1

SELECTEXISTS (SELECTemployee_idFROMemployeesWHEREsalary = 300000) AS 'exists返回1或者0';

示例2，查詢所有員工的部門名稱

SELECTdepartment_name FROMdepartments a WHEREEXISTS (SELECT1FROMemployees bWHEREa.department_id = b.department_id)

用 in 實現

SELECTdepartment_name FROMdepartments a WHEREa.department_id IN (SELECTdepartment_idFROMemployees);

示例3，查詢沒有員工的部門

exists 實現

SELECT* FROMdepartments a WHERENOT EXISTS (SELECT1FROMemployees bWHEREa.department_id = b.department_idAND b.department_id IS NOT NULL);

in 實現

SELECT* FROMdepartments a WHEREa.department_id NOT IN (SELECTdepartment_idFROMemployees bWHEREb.department_id IS NOT NULL);

注意 null 值

MySQL 提供了 IS NULL 關鍵字，用來判斷字段的值是否為空值（NULL）。

空值不同于 0，也不同于空字符串。

如果字段的值是空值，則滿足查詢條件，該記錄將被查詢出來。
如果字段的值不是空值，則不滿足查詢條件。

注意：

IS NULL 是一個整體，不能將 IS 換成 “=”。
如果將 IS 換成 “=” 將不能查詢出任何結果，數據庫系統會出現 “Empty set(0.00 sec)” 這樣的提示。同理，IS NOT NULL 中的 IS NOT 不能換成 “!=” 或 “<>”。

例如下面的例子，在使用 not in 方式查詢沒有員工的部門時，
因為子查詢的結果中包含了 null 值，導致外查詢的結果為空。

使用 not in 查詢沒有員工的部門

SELECT* FROMdepartments a WHEREa.department_id NOT IN (SELECTdepartment_idFROMemployees 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 的時候，外查詢的結果為空。

小結

在 MySQL 中，NULL 值意味著未知值。
NULL 值不是零或空字符” 值。

NULL 值不等于其自身值。

如果將 NULL 值與另一個 NULL 值或任何其他值進行比較，則結果為 NULL，因為每個NULL 值的值都是未知的。

通常，使用 NULL 值來表示數據丟失，未知或不適用。

當創建表時，可以通過使用 NOT NULL 約束來指定列是否接受 NULL 值。

NULL 在相關排序時類似無窮小的值，所以，不建議使用 NULL 作為列默認值。

總結

以上是生活随笔為你收集整理的MySQL 子查询使用方式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

方式
MySQL

上一篇：抓包抖音充值页面实现微信支付宝充值抖币,
下一篇：管理会计【14】