mysql第7天之连接查询

目录

一、连接查询含义：

二、笛卡尔积现象：

三、有效连接条件的分类：

        （一）按年代分类：

        （二）按功能分类：

四、sql92标准

（一）、等值连接

        1.特点

        2.为表起别名

        3.加筛选

        4.加分组

        5.加排序

        6.实现三表连接

（二）、非等值连接

（三）、自连接

---

一、连接查询含义：

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

二、笛卡尔积现象：

表1中有m行数据，表2中有n行数据，则输出结果为m*n行数据（表一每一行逐行匹配表二中的每一行）

输出结果如下：表1中的每个行匹配表2中所有行

发生笛卡尔积的原因：没有有效的连接条件

如何避免笛卡尔积：添加有效的连接条件（废话）

三、有效连接条件的分类：

        （一）按年代分类：

                1.sql92标准：仅仅支持内连接

                2.sql99标准：支持内连接+外连接（左外和右外不支持全外）+交叉连接        

        （二）按功能分类

                1.内连接

                        ①等值连接

                        ②非等值连接

                        ③自连接

                2.外连接

                        ①左外连接

                        ②右外连接

                        ③全外连接

                3.交叉连接

四、sql92标准

（一）、等值连接

        1.特点

        （1）多表等值连接的结果为多表的交集部分

        （2）n个表连接至少需要n-1个连接条件

        （3）多表的顺序没有要求【from后的表谁放前放后都没影响】

        （4）表名字长的话尽量起个别名【不然引用这个表中的字段代码很长】

        （5）可以和前面个的子句使用如、排序、分组、筛选

案例1、查询女神名对应的男神名

SELECT beauty.name,boys.boyName FROM beauty,boys

WHERE beauty.`boyfriend_id`=boys.id；

案例2、查询员工名对应的部门名

SELECT last_name,department_name

FROM employees e,departments d

WHERE e.department_id = d.department_id;

        2.为表起别名

优点：提高语句简洁度、区分多个重名的字段、如果为表起了别名，则查询的字段就不能用原表名来限定 表的顺序可以调换

查询员工名、工种号、工种名

SELECT last_name,e.`job_id`,job_title

FROM employees e,jobs j

WHERE e.`job_id`=j.`job_id`;3.

        3.加筛选

案例3、查询有奖金的员工名和部门名

SELECT last_name,department_name

FROM employees e,departments d

WHERE e.`department_id`=d.`department_id` AND e.`commission_pct`IS NOT NULL;

案例4：查询城市名中第二个字符为o的部门名和城市名

SELECT    department_name,city

FROM departments d,locations l

WHERE d.`location_id`=l.`location_id`

AND city LIKE '_o%';

        4.加分组

案例5、查询每个城市的部门个数

SELECT city,COUNT(*)

FROM departments d,locations l

WHERE d.`location_id`=l.`location_id`

GROUP BY city;

案例6、查询有奖金得每个部门得部门名和部门得领导编号和该部门得最低工资（一个部门多个领导）

SELECT department_name,e.manager_id,MIN(salary)

FROM employees e,departments d

WHERE e.`department_id`=d.`department_id`

AND e.commission_pct IS NOT NULL

GROUP BY e.`department_id`,e.`manager_id`;

        5.加排序

案例7、查询每个工种得公众名和员工得个数，并按员工得个数降序

SELECT j.`job_title`,COUNT(*)

FROM employees e,jobs j

WHERE e.`job_id`=j.`job_id`

GROUP BY e.`job_id`

ORDER BY COUNT(*) DESC;

        6.实现三表连接

案例8、查询员工名、部门名和所在得城市

SELECT last_name,department_name,city

FROM employees e,departments d,locations l

WHERE e.`department_id`=d.`department_id`AND d.`location_id`=l.`location_id`;

（二）、非等值连接

案例9、查询员工的工资和工资级别(between and 包含临界值)

SELECT salary,grade_level

FROM employees e,job_grades j

WHERE salary BETWEEN j.`lowest_sal`AND j.`highest_sal`

（三）、自连接

案例10、查询员工名和上级的名称

SELECT e.`employee_id`,e.`last_name`员工名,m.`employee_id`,m.`last_name`上级名

FROM employees e,employees m

WHERE e.`manager_id`=m.`employee_id`;

---

测 试

1. 显示所有员工的姓名，部门号和部门名称。

2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id

3. 选择所有有奖金的员工的

last_name , department_name , location_id , city

4. 选择city在Toronto工作的员工的

last_name , job_id , department_id , department_name

5.查询每个工种、每个部门的部门名、工种名和最低工资

6.查询每个国家下的部门个数大于 2 的国家编号

7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，

其结果类似于下面的格式:

employees      Emp         manager           Mgr

kochhar           101          king                  100

---

 1.

SELECT last_name,e.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;

2.

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`AND e.`department_id`=90;

3.  

SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND e.`commission_pct`IS NOT NULL;

4.

SELECT last_name , e.job_id , e.department_id , d.department_name
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city = 'Toronto';

5.

SELECT job_title,department_name,MIN(salary)
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;

6.

SELECT country_id,COUNT(*)部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY d.location_id
HAVING COUNT(*)>2;

7.

SELECT e.`last_name` employees,e.`employee_id` Emp,m.`last_name` manager,m.`employee_id` Mgr
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;