lyt经典版MySQL基础——进阶6：连接查询-sql92语法-内连接

  1 #进阶6：连接查询
  2 /*
  3 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
  4 
  5 笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
  6 
  7 发生原因：没有有效的连接条件
  8 如何避免：添加有效的连接条件
  9 
 10 分类：
 11     按年代分类：
 12     sql92标准：仅仅支持内连接
 13     sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
 14     
 15     按功能分类：
 16         内连接：
 17             等值连接
 18             非等值连接
 19             自连接
 20         外连接：
 21             左外连接
 22             右外连接
 23             全外连接
 24         交叉连接
 25 */
 26 
 27 SELECT * FROM beauty;
 28 SELECT * FROM boys;
 29 
 30 SELECT NAME,boyName FROM boys,beauty
 31 WHERE beauty.boyfriend_id=boys.id;
 32 
 33 #一、sql92标准
 34 #1.等值连接
 35 /*
 36 (1)多表等值连接的结果为多表的交集部分
 37 (2)n表连接，至少需要n-1个链接条件
 38 (3)多表的顺序没有要求
 39 (4)一般需要为表起别名
 40 (5)可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
 41 */
 42 
 43 #案例1：查询女神名和对应的男神名
 44 SELECT NAME,boyName FROM boys,beauty
 45 WHERE beauty.boyfriend_id=boys.id;
 46 #案例2：查询员工名和对应的部门名
 47 SELECT last_name,department_name
 48 FROM employees,departments
 49 WHERE departments.department_id=employees.department_id;
 50 
 51 #2.为表起别名
 52 /*
 53 (1)提高语句的简洁度
 54 (2)区分多个重名的字段
 55 
 56 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
 57 因为执行顺序是 from --> where --> select
 58 */
 59 #查询员工名、工种号、工种名
 60 SELECT last_name,e.job_id,job_title
 61 FROM employees AS e,jobs
 62 WHERE e.job_id=jobs.job_id;
 63 
 64 #3.两个表的顺序可以调换
 65 #查询员工名、工种号、工种名
 66 SELECT last_name,e.job_id,job_title
 67 FROM jobs,employees AS e
 68 WHERE e.job_id=jobs.job_id;
 69 
 70 #4.可以加筛选
 71 #案例：查询有奖金的员工名、部门名
 72 SELECT last_name,department_name
 73 FROM departments,employees AS e
 74 WHERE departments.department_id=e.department_id
 75 AND e.commission_pct IS NOT NULL;
 76 #案例2：查询城市名中第二个字符为o的部门名和城市名
 77 SELECT department_name,city
 78 FROM departments,locations l
 79 WHERE departments.location_id=l.location_id
 80 AND city LIKE '_o%';
 81 
 82 #5.可以加分组
 83 #案例1：查询每个城市的部门个数
 84 SELECT city,COUNT(department_name) 个数
 85 FROM locations l,departments d
 86 WHERE l.location_id=d.location_id
 87 GROUP BY city;
 88 #案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
 89 SELECT department_name,d.manager_id,MIN(salary)
 90 FROM departments d, employees e
 91 WHERE d.department_id=e.department_id
 92 AND e.commission_pct IS NOT NULL
 93 GROUP BY d.department_id;
 94 
 95 #6.可以加排序
 96 #案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
 97 SELECT j.job_title,COUNT(*) FROM employees e,jobs j
 98 WHERE j.job_id=e.job_id
 99 GROUP BY j.job_title
100 ORDER BY COUNT(*) DESC;
101 
102 #7.可以实现三表连接
103 #案例：查询员工名、部门名和所在的城市
104 SELECT last_name,department_name,city
105 FROM employees e,departments d,locations l
106 WHERE d.department_id=e.department_id 
107 AND d.location_id=l.location_id;
108 
109 
110 #2.非等值连接
111 #案例1：查询员工的工资和工资级别
112 SELECT salary,grade_level
113 FROM employees e,job_grades g
114 WHERE salary BETWEEN lowest_sal AND highest_sal;
115 
116 #3.自连接
117 #案例：查询员工名和上级的名称
118 SELECT e.employee_id '员工id',e.last_name '员工名',
119 m.employee_id '上级id',m.last_name '上级名'
120 FROM employees e,employees m
121 WHERE e.manager_id=m.employee_id;