lyt经典版MySQL基础——进阶7:子查询
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 #一、where或having后面 27 /* 28 1.标量子查询(单行子查询) 29 2.列子查询(多行子查询) 30 3.行子查询(多列多行) 31 32 特点: 33 (1)子查询放在小括号内 34 (2)子查询一般放在条件的右侧 35 (3)标量子查询,一般搭配着单行操作符使用 36 > < >= <= <> 37 38 列子查询,一般搭配着多行操作符使用 39 in、any/some、all 40 (4)子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果 41 */ 42 43 #1.标量子查询 44 #案例1:谁的工资比Abel高? 45 #(1)查询Abel的工资 46 SELECT salary 47 FROM employees 48 WHERE last_name='Abel'; 49 #(2)查询员工的信息,满足salary>(1)的结果 50 SELECT * 51 FROM employees 52 WHERE salary>( 53 SELECT salary 54 FROM employees 55 WHERE last_name='Abel' 56 ); 57 #案例2:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资 58 #(1)查询141号员工的job_id 59 SELECT job_id 60 FROM employees 61 WHERE employee_id='141' 62 #(2)查询143号员工的salary 63 SELECT salary 64 FROM employees 65 WHERE employee_id='143' 66 #(3)查询员工姓名,job_id和工资,要求job_id=(1)并且salary>(2) 67 SELECT last_name,job_id,salary 68 FROM employees WHERE job_id=( 69 SELECT job_id 70 FROM employees 71 WHERE employee_id='141' 72 )AND salary>( 73 SELECT salary 74 FROM employees 75 WHERE employee_id='143' 76 ); 77 #案例3:返回公司工资最少的员工的last_name,job_id和salary 78 #(1)查询公司的最低工资 79 SELECT MIN(salary) 80 FROM employees 81 #(2)查询last_name,job_id和salary,要求salary=(1) 82 SELECT last_name,job_id,salary 83 FROM employees 84 WHERE salary=( 85 SELECT MIN(salary) 86 FROM employees 87 ); 88 #案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资 89 #(1)查询50号部门的最低工资 90 SELECT MIN(salary) 91 FROM employees 92 WHERE department_id='50' 93 #(2)查询每个部门的最低工资 94 SELECT department_id,MIN(salary) '最低工资' 95 FROM employees 96 GROUP BY department_id 97 #(3)在(2)的基础上筛选,满足min(salary)>(1) 98 SELECT department_id,MIN(salary) '最低工资' 99 FROM employees 100 GROUP BY department_id 101 HAVING 最低工资>( 102 SELECT MIN(salary) 103 FROM employees 104 WHERE department_id='50' 105 ); 106 107 #非法使用标量子查询——子查询的结果不是一行一列 108 SELECT department_id,MIN(salary) '最低工资' 109 FROM employees 110 GROUP BY department_id 111 HAVING 最低工资>( 112 SELECT salary 113 FROM employees 114 WHERE department_id='50' 115 ); 116 117 118 #2.列子查询(多行子查询) 119 #案例1:返回location_id是1400或1700的部门中的所有员工姓名 120 #(1)查询location_id是1400或1700的部门编号 121 SELECT DISTINCT department_id 122 FROM departments 123 WHERE location_id IN(1400,1700) 124 #(2)查询员工姓名,要求部门号是(1)列表中的某一个 125 SELECT last_name 126 FROM employees 127 WHERE department_id IN( 128 SELECT DISTINCT department_id 129 FROM departments 130 WHERE location_id IN(1400,1700) 131 ); 132 #或 133 SELECT last_name 134 FROM employees 135 WHERE department_id =ANY( 136 SELECT DISTINCT department_id 137 FROM departments 138 WHERE location_id IN(1400,1700) 139 ); 140 #案例2:返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的 141 #员工号、姓名、job_id以及salary 142 #(1)查询job_id为'IT_PROG'工种任一工资 143 SELECT salary 144 FROM employees 145 WHERE job_id='IT_PROG' 146 #(2)查询员工号、姓名、job_id以及salary,salary<(1)中的任意一个 147 SELECT employee_id,last_name,job_id,salary 148 FROM employees 149 WHERE salary<ANY( 150 SELECT salary 151 FROM employees 152 WHERE job_id='IT_PROG' 153 )AND job_id <>'IT_PROG'; 154 #或 155 SELECT employee_id,last_name,job_id,salary 156 FROM employees 157 WHERE salary<( 158 SELECT MAX(salary) 159 FROM employees 160 WHERE job_id='IT_PROG' 161 )AND job_id <>'IT_PROG'; 162 #案例3:返回其他工种中比job_id为'IT_PROG'工种所有工资都低的员工的 163 #员工号、姓名、job_id以及salary 164 SELECT employee_id,last_name,job_id,salary 165 FROM employees 166 WHERE salary<ALL( 167 SELECT DISTINCT salary 168 FROM employees 169 WHERE job_id='IT_PROG' 170 )AND job_id <>'IT_PROG'; 171 #或 172 SELECT employee_id,last_name,job_id,salary 173 FROM employees 174 WHERE salary<( 175 SELECT DISTINCT MIN(salary) 176 FROM employees 177 WHERE job_id='IT_PROG' 178 )AND job_id <>'IT_PROG'; 179 180 #3.行子查询(结果集一行多列或多行多列) 181 #案例:查询员工编号最小并且工资最高的员工信息 182 #(1)查询最小的员工编号 183 SELECT MIN(employee_id) 184 FROM employees 185 #(2)查询最高工资 186 SELECT MAX(salary) 187 FROM employees 188 #(3)查询员工信息 189 SELECT * FROM employees 190 WHERE employee_id=( 191 SELECT MIN(employee_id) 192 FROM employees 193 ) AND salary=( 194 SELECT MAX(salary) 195 FROM employees 196 ); 197 #或 198 SELECT * FROM employees 199 WHERE (employee_id,salary)=( 200 SELECT MIN(employee_id),MAX(salary) 201 FROM employees 202 ); 203 204 #二、select后面 205 /* 206 仅仅支持标量子查询 207 */ 208 #案例:查询每个部门的员工个数 209 SELECT d.*,( 210 SELECT COUNT(*) 211 FROM employees e 212 WHERE e.department_id=d.department_id 213 ) 个数 214 FROM departments d; 215 #案例2:查询员工号=102的部门名 216 SELECT( 217 SELECT department_name 218 FROM departments d 219 INNER JOIN employees e ON d.department_id=e.department_id 220 WHERE e.employee_id='102' 221 ) 部门名; 222 223 #三、from后面 224 /* 225 将子查询结果充当一张表,要求必须起别名 226 */ 227 #案例:查询每个部门的平均工资的工资等级 228 #(1)查询每个部门的平均工资 229 SELECT AVG(salary),department_id 230 FROM employees 231 GROUP BY department_id 232 #(2)连接(1)的结果集和job_grades表,筛选条件平均工资 233 SELECT ag_dep.*,grade_level 234 FROM ( 235 SELECT AVG(salary) ag,department_id 236 FROM employees 237 GROUP BY department_id 238 ) ag_dep 239 INNER JOIN job_grades ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; 240 241 #四、exists后面(相关子查询) 242 /* 243 语法: 244 exists(完整的查询语句) 245 结果: 246 1或0 247 */ 248 SELECT EXISTS(SELECT employee_id FROM employees WHERE salary='30000'); 249 #案例1:查询有员工的部门名 250 #in 251 SELECT department_name FROM departments 252 WHERE department_id IN( 253 SELECT department_id FROM employees 254 WHERE employee_id IS NOT NULL 255 ); 256 #exists 257 SELECT department_name FROM departments d 258 WHERE EXISTS ( 259 SELECT employee_id FROM employees e 260 WHERE d.department_id=e.department_id 261 ); 262 263 #案例2:查询没有女朋友的男神信息 264 #in 265 SELECT * FROM boys 266 WHERE boys.id NOT IN ( 267 SELECT boyfriend_id FROM beauty 268 ); 269 #exists 270 SELECT * FROM boys bo 271 WHERE NOT EXISTS( 272 SELECT boyfriend_id FROM beauty b 273 WHERE bo.id=b.boyfriend_id 274 );
每天进步一点点,快乐生活多一点。