子查询
1 #进阶7：子查询 2 /* 3 含义： 4 出现在其他语句中的select语句，成为子查询或内查询、 5 外部的查询语句，成为主查询或外查询 6 7 分类： 8 按子查询出现的位置：、 9 select后面： 10 仅仅支持标量子查询 11 FROM后面：、 12 支持表子查询 13 where 或having 后面：*** 14 标量子查询（单行）√ 15 列子查询（多行） √ 16 行子查询 17 exists后面（相关子查询）： 18 表子查询 19 20 按结果集的行列数不同：标量子查询（结果集只有一行一列） 列子查询（结果集只有一列多行） 21 22 行子查询（结果集可以有一行多列） 23 、 24 表子查询（结果集 一般为多行多列） 25 26 27 */ 28 #一、where或haing后面 29 /*1、标量子查询（单行子查询） 30 2、列子 查询（多行子查询） 31 3、行子查询(多列多行） 32 特点： 33 ①子查询放在小括号内 34 ②子查询一般放在条件的右侧 35 ③标量子查询，一般搭配着单行操作符使用 36 > < >= >= = <> 37 38 列子查询，一般搭配着多行操作符使用 39 in、any/some、all 40 41 ④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果 42 */ 43 #1、标量子查询 44 #案例1：谁的工资比Abel高？ 45 ①查询Abel的工资 SELECT 46 salary 47 FROM 48 employees 49 WHERE 50 last_name = 'Abel' ②查询员工的信息，满足salary > ①的结果 SELECT 51 * 52 FROM 53 employees 54 WHERE 55 salary > ( 56 SELECT 57 salary 58 FROM 59 employees 60 WHERE 61 last_name = 'Abel' 62 ); 63 64 #案例2：题目：返回jb_id与141号员工相间， salary比143号员工多的员工姓名，job_id和工资 65 #①查询141号员工的job_id 66 SELECT 67 job_id 68 FROM 69 employees 70 WHERE 71 employee_id = 141 #②查询143号员工的salary 72 SELECT 73 salary 74 FROM 75 employees 76 WHERE 77 employee_id = 143 #③查询员工的姓名，job_id和工资，要求job_id=①并且salary>② 78 SELECT 79 last_name, 80 job_id, 81 salary 82 FROM 83 employees 84 WHERE 85 job_id = ( 86 SELECT 87 job_id 88 FROM 89 employees 90 WHERE 91 employee_id = 141 92 ) 93 AND salary > ( 94 SELECT 95 salary 96 FROM 97 employees 98 WHERE 99 employee_id = 143 100 ); 101 102 #案例3：返回公司工资最少的员工的last_name,job_id和salary 103 SELECT 104 last_name, 105 job_id, 106 salary 107 FROM 108 employees 109 WHERE 110 salary = ( 111 SELECT 112 min(salary) 113 FROM 114 employees 115 ); 116 117 #案例4查询最低工资大于50号部门最低工资的部门id和其最低工资 118 #查询50号部门的最低工资 119 SELECT 120 MIN(salary) 121 FROM 122 employees 123 WHERE 124 department_id = 50 #查询每个部门的最低工资 125 SELECT 126 MIN(salary), 127 department_id 128 FROM 129 employees 130 GROUP BY 131 department_id #在②基础筛选，满足min(salary)>①的结果 132 SELECT 133 MIN(salary), 134 department_id 135 FROM 136 employees 137 GROUP BY 138 department_id 139 HAVING 140 MIN(salary) > ( 141 SELECT 142 MIN(salary) 143 FROM 144 employees 145 WHERE 146 department_id = 50 147 ); 148 149 #非法使用标量子查询 150 SELECT 151 MIN(salary), 152 department_id 153 FROM 154 employees 155 GROUP BY 156 department_id 157 HAVING 158 MIN(salary) > ( 159 SELECT 160 salary 161 FROM 162 employees 163 WHERE 164 department_id = 250 165 ); 166 167 #2、列子查询（多行子查询） 168 #案例1：返回 location id是1400或1700的部门中的所有员工姓名 169 #①查询location_id是1400或1700的部门编号 170 SELECT DISTINCT 171 department_id 172 FROM 173 departments 174 WHERE 175 location_id IN (1400, 1700) #查询员工姓名，要求部门号是①列表中的某一个 176 SELECT 177 last_name 178 FROM 179 employees 180 WHERE 181 department_id IN ( 182 SELECT DISTINCT 183 department_id 184 FROM 185 departments 186 WHERE 187 location_id IN (1400, 1700) 188 ); 189 190 #案例2：返回其它部门中比job_id为 'IT PROG’部门任一工资低的员工的：工号、姓名、 job_id以及 salary 191 #查询job_id为'IT PROG’部门任一工资 192 SELECT DISTINCT 193 salary 194 FROM 195 employees 196 WHERE 197 job_id = 'IT_PROG' #查询工号、姓名、 job_id以及 salary,salary<①的任意个结果 198 SELECT 199 last_name, 200 employee_id, 201 job_id, 202 salary 203 FROM 204 employees 205 WHERE 206 salary < ANY ( 207 SELECT DISTINCT 208 salary 209 FROM 210 employees 211 WHERE 212 job_id = 'IT_PROG' 213 ) 214 AND job_id <> 'IT_PROG'; 215 216 #案例3：返回其它部门中比job_id为' IT PROG'部门所有工资都低的员工的员工号、姓名、job_id以及sa1ary 217 SELECT 218 last_name, 219 employee_id, 220 job_id, 221 salary 222 FROM 223 employees 224 WHERE 225 salary < ALL ( 226 SELECT DISTINCT 227 salary 228 FROM 229 employees 230 WHERE 231 job_id = 'IT_PROG' 232 ) 233 AND job_id <> 'IT_PROG'; 234 235 #3、行子查询（结果集一行多列或多行多列） 236 #案例：查询员工编号最小并且工资最高的员工信息 237 SELECT 238 * 239 FROM 240 employees 241 WHERE 242 (employee_id, salary) = #①查询最小的员工编号 243 SELECT 244 MIN( 245 employee_i 246 247 #①查询最小的员工编号 248 SELECT 249 MIN(employee_id) 250 FROM 251 employeesd) 252 FROM employees; 253 254 #②查询最高工资 255 SELECT 256 MAX(salary) 257 FROM 258 employees #③查询员工信息 259 SELECT 260 * 261 FROM 262 employees 263 WHERE 264 employee_id = ( 265 SELECT 266 MIN(employee_id) 267 FROM 268 employees 269 ) 270 AND salary = #二、select后面 271 /* 272 紧紧支持栍 273 274 #二、select后面 275 /* 276 紧紧支持标量子查询 277 */ 278 #案例1：查询每个部门的员工个数 279 SELECT 280 d.*, ( 281 SELECT 282 COUNT(*) 283 FROM 284 employees e 285 WHERE 286 e.department_id = d.department_id 287 ) 个数 288 FROM 289 departments dȩǏ子查询 290 */ 291 292 #案例1：查询每个部门的员工个数 293 SELECT d.*,( 294 SELECT COUNT(*) 295 FROM employees e 296 WHERE e.department_id=d.department_id 297 ) 个数 298 FROM departments d; 299 300 #案例2：查询员工号=102的部门名 301 SELECT 302 ( 303 SELECT 304 department_name 305 FROM 306 departments d 307 JOIN employees e ON d.department_id = e.department_id 308 WHERE 309 e.employee_id = 102 310 ) 部门名 #三、from后面 311 /* 312 将子查询结果充当一张表，要求必须写别名 313 */ 314 #案例：查询每个部门的平均工资的工资等级 315 #查询每个部门的平均工资 316 SELECT 317 avg(salary), 318 department_id 319 FROM 320 employees 321 GROUP BY 322 department_id; 323 324 #②连接1的结果集合job_grades表，筛选条件平均工资 BETWEEN lowest_sal and highest_sal 325 SELECT 326 ag_dep.*, g.grade_level 327 FROM 328 ( 329 SELECT 330 avg(salary) ag, 331 department_id 332 FROM 333 employees 334 GROUP BY 335 department_id 336 ) ag_dep 337 JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal 338 AND highest_sal; 339 340 #四、exists后面（相关子查询） 341 /* 342 EXISTS(完整的查询语句) 343 结果: 1或0 344 345 */ 346 SELECT 347 EXISTS ( 348 SELECT 349 employee_id 350 FROM 351 employees 352 WHERE 353 salary = 30000 354 ); 355 356 #案例1：查询有员工的部门名 357 #in 358 SELECT 359 department_name 360 FROM 361 departments d 362 WHERE 363 department_id IN ( 364 SELECT 365 department_id 366 FROM 367 employees e 368 ); 369 370 #exists 371 SELECT 372 department_name 373 FROM 374 departments d 375 WHERE 376 EXISTS ( 377 SELECT 378 * 379 FROM 380 employees e 381 WHERE 382 d.department_id = e.department_id 383 ); 384 385 #案例2：查询没有女朋友的男神信息 386 #in 387 SELECT 388 bo.* 389 FROM 390 boys bo 391 WHERE 392 bo.id NOT IN ( 393 SELECT 394 boyfriend_id 395 FROM 396 beauty 397 ); 398 399 #EXISTS 400 SELECT 401 bo.* 402 FROM 403 boys bo 404 WHERE 405 NOT EXISTS ( 406 SELECT 407 boyfriend_id 408 FROM 409 beauty 410 WHERE 411 bo.id = b.boyfriend_id 412 );
版权声明：本文为deyo原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。