07链接查询

  1 #连接查询 
  2 /*
  3 含义：又称多表表查询，当查询的字段来自多表时，就会用到连接查询
  4 
  5 笛卡尔乘积现象：表1 有m行，表二有n行，结果=n*m行
  6 
  7 发生原因：没有有效的连接条件
  8 如何避免：添加有效的连接条件
  9 
 10 分类 ：
 11     按年份:    
 12         aq192标准 :仅仅支持内链接
 13         aq199标准【推荐】：支持内链接 + 外连接（左外 + 右外） + 交叉连接
 14     按功能分类：
 15         内链接：
 16             等值连接
 17             费等值连接
 18             自连接
 19         外连接：
 20             左外连接
 21             右外连接
 22             全外连接
 23         叫擦连接
 24                 
 25 */
 26 
 27 SELECT    NAME ,boyName FROM boys,beauty
 28 
 29 WHERE beauty.boyfriend_id = boys.id;
 30 
 31 #一.sq92标准
 32 
 33 #1.等值连接
 34 /*
 35 1).夺标等值连接的结果为多表的交集部分
 36 2).n表连接至少需要n-1个连接条件
 37 3).多表的顺序没有要求
 38 4).一般需要为表其别名
 39 5).可以搭配前面介绍的字句使用 排序 分组 筛选
 40 */
 41 
 42 #案例1：查询女神对应的男神名
 43 SELECT NAME,boyName
 44 FROM boys,beauty
 45 WHERE beauty.boyfriend_id = boys.id;
 46 
 47 #案例2.查询员工名对应的部门名
 48 SELECT last_name,department_name 
 49 FROM employees,departments
 50 WHERE employees.department_id = departments.department_id;
 51 
 52 #2.为表其别名
 53 /*
 54 1).提高语句的简洁度
 55 2).区分多个重名字段
 56 注意：如果为表起了别名，则查询的字段就不能使用原来的字段
 57 */
 58 #查询员工名 工种名 工种号
 59 
 60 SELECT e.last_name,e.job_id,j.job_title
 61 FROM employees e,jobs j
 62 WHERE e.job_id = j.job_id;
 63 
 64 #3.加筛选
 65 
 66 #案例1：查询有奖金的员工名，部门名
 67 SELECT last_name,separtment_name,commission_pct
 68 FROM employees e,departments d
 69 WHERE e.department_id = d.department_id
 70 AND e.commission_pct IS NOT NULL;
 71 
 72 #案例2：查询城市名中第二个字符为o的 部门名和城市名
 73 
 74 SELECT department_name,city
 75 FROM departments d ,locatons l
 76 WHERE d.location_id = l.location_id
 77 AND city LIKE ‘_o%‘;
 78 
 79 #4.加分组
 80 
 81 #案例1：查询每个城市的部门个数
 82 
 83 SELECT COUNT(*) 个数,city
 84 FROM department d,location l
 85 WHERE d.location_id = l.location_id 
 86 GROUP BY city;
 87 
 88 #案例2：查询有奖金的每个部门的部门名和部门领导编号和该部门最低工资
 89 
 90 SELECT department_name,d.manager_id,MIN(salary)
 91 FROM departmens d,employees e
 92 WHERE d.department_id=e.department_id
 93 AND commission_pct IS NOT NULL
 94 GROUP BY department_name,manager_id;
 95 
 96 #5.加排序
 97 
 98 #案例1：查询每个工种的工种名和员工个数，并且按员工个数降序
 99 
100 SELECT job_title,COUNT(*)
101 FROM employees e,jobs j
102 WHERE e.job_id = j.job_id
103 GROUP BY job_title
104 ORDER BY COUNT(*) DESC;
105 
106 #7.三表连接
107 
108 #案例：查询员工名 部门名和所在的城市
109 
110 SELECT last_name,deparment_name,city 
111 FROM employees e,department d ,locations l
112 WHERE e.department_id = d.department_id
113 AND d.location_id = l.location_id 
114 AND city LIKE ‘s%‘
115 ORDER BY department_name DESC;
116 
117 #2.非等值连接
118 
119 #案例：查询员工工资和工资等级
120 
121 SELECT salary,grade_level 
122 FROM employees e,job_grades g
123 WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
124 AND g.grade_level = ‘A‘;
125 
126 #3.自连接 
127 
128 #案例：查询员工名和上级的名称
129 SELECT e.employees_id,e.last_name,m.employee_id,m.last_name
130 FROM employees e,employees m
131 WHERE e.,amager_id = employee_id;
132 
133 #二. sql99语法
134 /*
135 语法：
136     select 查询列表
137     from 表1 别名 【链接类型】
138     join 表2 别名
139     on 连接条件
140     【where 筛选条件】
141     【group by 分组】
142     【having 筛选条件】
143     【order by 排序列表】
144     
145 分类：
146     内联（☆）：inner
147     外联
148         左外（☆）：left 【outer】
149         左外（☆）：right 【outer】
150         全外：full 【outer】
151 交叉链接;cross
152 */
153 
154 #1.内连接
155 /*
156 语法：
157     select 查询列表
158     from 表1 别名
159     inner join 表2 别名
160     on 链接条件;
161 分类：
162     等值链接
163     非等值链接
164     自连接
165 特点：
166     1）可以添加排序 分组 筛选
167     2）inner 可以省略
168     3）筛选条件放在where后面 链接条件放在on后面 提高分离性，便于阅读
169     4）inner join 链接和sql92语法中的等值链接效果是一样的，都是查询交集
170 */
171 
172 #1.等值连接
173 
174 #案例1.查询员工名 和部门名
175 
176 SELECT last_name,department_name
177 FROM employees e
178 INNER JOIN departments d
179 ON e.department_id = d.department_id;
180 
181 #案例2.查询名字中包含e员工名和工种名（添加筛选）
182 
183 SELECT last_name,job_title
184 FROM employees e
185 INNER JOIN jobs j
186 ON e.job_id = j.job_id
187 WHERE e.last_name LIKE ‘%e%‘;
188 
189 #案例3 查看部门个数>3的城市名和部门个数，（添加分组+ 筛选）
190 
191 SELECT city ,COUNT(*) 部门个数
192 FROM departments d
193 INNER JOIN locations l
194 ON d.location_id = l.location_id
195 GROUP BY city 
196 HAVING COUNT(*)>3;
197 
198 #案例4：查询那个部门的员工个数大于3的部门名和员工个数，并降序（添加排序）
199 #1）查询每个部门员工个数
200 
201 SELECT COUNT(*),department_name
202 FROM employees e 
203 INNER JOIN departments d
204 HAVING COUNT(*)>3
205 ORDER BY COUNT(*) DESC;
206 
207 
208 #案例5：查询员工名 部门名 工种名 并按照部门名降序
209 
210 SELECT last_name ,department_name,job_title
211 FROM employees e
212 INNER JOIN department d ON e.department_id = d.department_id
213 INNER JOIN jobs j ON e.job_id = j.job_id
214 ORDER BY department_name DESC;
215 
216 #二.非等值连接
217 
218 #查询员工工资级别
219 
220 SELECT salary,grade_level
221 FROM employees e
222 JOIN job_grades g
223 ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
224 GROUP BY grade_level
225 HAVING COUNT(*)>20
226 ORDER BY grade_level DESC;
227 
228 
229 #三.自连接
230 
231 #案例：查询员工姓名中包含字符k的员工名字 、上级名字
232 
233 SELECT e.last_name,m.last_name
234 FROM employees e
235 JOIN employees m
236 ON e.manager_id = m.employee_id
237 WHERE e.last_name LIKE ‘%k%‘;
238 
239 
240 #外连接
241 
242 /*
243 应用：
244     用于查询一个表有 另一个表没有的记录
245 特点：
246     1.外连接的查询结果为主表中的所有记录
247         如果从表中又和他匹配的，则显示匹配的值
248         如果从表中没有和它匹配的 则显示NULL
249         外连接的查询结果 = 内连接结果 + 主表中有而从表中没有的记录
250     2.左外连接，left  join 左边是主表
251        右外连接，right join 右边是主表
252     3.左外和右外交换两个表的顺序 可以实现同样的效果
253     4.全外连接=内连接的结果+表1中有但表2中没有+表2中有但表1中没有
254 */
255 
256 #引入
257 
258 SELECT b.name,bo.*
259 FROM beauty b
260 LEFT OUTER JOIN boys bo
261 ON b.boyfriend_id = bo.id
262 WHERE bo.id IS NULL;
263 
264 
265 #案例1：查询那个部门没有员工
266 #左外
267 
268 SELECT d.*,e.employee_id
269 FROM departments d
270 LEFT OUTER JOIN employees e
271 ON d.department_id = e.department_id 
272 WHERE e.employee_id IS NULL;
273 
274 
275 #右外 
276 
277 SELECT d.*,e.employee_id
278 FROM employees e
279 LEFT OUTER JOIN departments d
280 ON d.department_id = e.department_id 
281 WHERE e.employee_id IS NULL;
282 
283 #全外 （不支持）
284 
285 
286 #交叉连接
287 
288 SELECT b.*,bo.*
289 FROM beauty b
290 CROSS JOIN boys bo;