本文共 4678 字，大约阅读时间需要 15 分钟。

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件

分类：

• sql92标准:仅仅支持内连接
• sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：

• 内连接：

  等值连接
  非等值连接
  自连接

• 外连接：

  左外连接
  右外连接
  全外连接

• 交叉连接

等值连接：

#一、sql92标准#1、等值连接/*① 多表等值连接的结果为多表的交集部分②n表连接，至少需要n-1个连接条件③ 多表的顺序没有要求④一般需要为表起别名⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选*/#案例1：查询女神名和对应的男神名SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id= boys.id;#案例2：查询员工名和对应的部门名SELECT last_name,department_nameFROM employees,departmentsWHERE employees.`department_id`=departments.`department_id`;#2、为表起别名/*①提高语句的简洁度②区分多个重名的字段注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定*/#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM employees  e,jobs jWHERE e.`job_id`=j.`job_id`;#3、两个表的顺序是否可以调换#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM jobs j,employees eWHERE e.`job_id`=j.`job_id`;#4、可以加筛选#案例：查询有奖金的员工名、部门名SELECT last_name,department_name,commission_pctFROM employees e,departments dWHERE e.`department_id`=d.`department_id`AND e.`commission_pct` IS NOT NULL;#案例2：查询城市名中第二个字符为o的部门名和城市名SELECT department_name,cityFROM departments d,locations lWHERE d.`location_id` = l.`location_id`AND city LIKE '_o%';#5、可以加分组#案例1：查询每个城市的部门个数SELECT COUNT(*) 个数,cityFROM departments d,locations lWHERE d.`location_id`=l.`location_id`GROUP BY city;#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资SELECT department_name,d.`manager_id`,MIN(salary)FROM departments d,employees eWHERE d.`department_id`=e.`department_id`AND commission_pct IS NOT NULLGROUP BY department_name,d.`manager_id`;#6、可以加排序#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序SELECT job_title,COUNT(*)FROM employees e,jobs jWHERE e.`job_id`=j.`job_id`GROUP BY job_titleORDER BY COUNT(*) DESC;#7、可以实现三表连接？#案例：查询员工名、部门名和所在的城市SELECT last_name,department_name,cityFROM employees e,departments d,locations lWHERE e.`department_id`=d.`department_id`AND d.`location_id`=l.`location_id`AND city LIKE 's%'ORDER BY department_name DESC;

非等值连接

#2、非等值连接#案例1：查询员工的工资和工资级别SELECT salary,grade_levelFROM employees e,job_grades gWHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`/*select salary,employee_id from employees;select * from job_grades;CREATE TABLE job_grades(grade_level VARCHAR(3), lowest_sal  int, highest_sal int);INSERT INTO job_gradesVALUES ('A', 1000, 2999);INSERT INTO job_gradesVALUES ('B', 3000, 5999);INSERT INTO job_gradesVALUES('C', 6000, 9999);INSERT INTO job_gradesVALUES('D', 10000, 14999);INSERT INTO job_gradesVALUES('E', 15000, 24999);INSERT INTO job_gradesVALUES('F', 25000, 40000);*/

自连接：

3、自连接#案例：查询 员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM employees e,employees mWHERE e.`manager_id`=m.`employee_id`;

示例：

#显示员工表的最大工资，工资平均值SELECT MAX(salary), AVG(salary) FROM employees;#查询员工表的employee_id，job_id,last_name,按department_id降序，salary升序SELECT employee_id,job_id,last_nameFROM employees ORDER BY department_id DESC, salary ASC;#查询员工表的job_id中包含a和e的，并且a在e前面SELECT job_id FROM employeesWHERE job_id LIKE '%a%e%';#已知表student,里面有id（学号），name，gradeId（年级号）#已知表grade，里面有id（年纪号），name（年纪名）#已知表result，里面有id，score，studentNo（学号）#要求查询姓名、年级名和成绩SELECT s.name,g.name,r.scroeFROM student s, grade g,result rWHERE s.id=r.studentNo AND g.id=s.gradeid;#显示当前日期，以及去前后空格，截取字符串的函数SELECT TRIM(NOW());

#1.显示所有员工的姓名，部门号和部门名称。USE myemployees;SELECT last_name,d.department_id,department_nameFROM employees e,departments dWHERE e.`department_id` = d.`department_id`;#2.查询90号部门员工的job_id和90号部门的location_idSELECT job_id,location_idFROM employees e,departments dWHERE e.`department_id`=d.`department_id`AND e.`department_id`=90;#3.	选择所有有奖金的员工的last_name , department_name , location_id , citySELECT last_name , department_name , l.location_id , cityFROM employees e,departments d,locations lWHERE e.department_id = d.department_idAND d.location_id=l.location_idAND e.commission_pct IS NOT NULL;#4.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name SELECT last_name , job_id , d.department_id , department_name FROM employees e,departments d ,locations lWHERE e.department_id = d.department_idAND d.location_id=l.location_idAND city = 'Toronto';#5.查询每个工种、每个部门的部门名、工种名和最低工资SELECT department_name,job_title,MIN(salary) 最低工资FROM employees e,departments d,jobs jWHERE e.`department_id`=d.`department_id`AND e.`job_id`=j.`job_id`GROUP BY department_name,job_title;#6.查询每个国家下的部门个数大于2的国家编号SELECT country_id,COUNT(*) 部门个数FROM departments d,locations lWHERE d.`location_id`=l.`location_id`GROUP BY country_idHAVING 部门个数>2;#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees	Emp#	manager	Mgr#kochhar		101	king	100SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"FROM employees e,employees mWHERE e.manager_id = m.employee_idAND e.last_name='kochhar';

转载地址：http://uhsqb.baihongyu.com/