mysql创表的工种_[MySQL基础]七、连接查询

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

笛卡尔乘积现象

表1 有m行，表2有n行，结果为m*n行

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

如何避免：添加有效的连接条件。

案例：在boys表中匹配beauty表中女生的男朋友

以下的方法错误，会出现笛卡尔乘积现象。

正确解法：

SELECT

NAME,

boyName

FROM

boys,

beauty

WHERE beauty.`boyfriend_id` = boys.`id` ;

结果：

6.1 连接查询的分类

按年代分类：SQL192标准：仅仅支持内连接

SQL199标准【推荐】：支持内连接+外连接(左外和右外)+交叉连接

按功能分类：

1.内连接：等值连接

非等值连接

自连接

2.外连接：左外连接

右外连接

全外连接

交叉连接

6.2 SQL192连接

1、等值连接

语法：①多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

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

SELECT

NAME,

boyName

FROM

boys,

beauty

WHERE beauty.`boyfriend_id` = boys.`id` ;

案例2：查询部门名和对应的部门名

SELECT

last_name,

department_name

FROM

employees,

departments

WHERE employees.`department_id` = `departments`.`department_id` ;

(1) 为表起别名提高语句的简洁度

区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例：查询员工名、工种号、工种名

SELECT

e.last_name,

e.job_id,

j.job_title

FROM

employees e,

jobs j

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

两个表的顺序可以调换：

下面代码和上面功能一样

SELECT

e.last_name,

e.job_id,

j.job_title

FROM

jobs j,

employees e

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

(2)可以加筛选

案例1：查询有奖金的员工名、部门名

SELECT

last_name,

department_name

FROM

employees e,

departments d

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

AND e.`commission_pct` IS NOT NULL ;

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

SELECT

department_name,

city

FROM

departments d,

locations l

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

AND city LIKE '_o%' ;

结果：

(3)可以加分组

案例1：查询每个城市的部门个数

SELECT

COUNT(*) AS "个数",

city

FROM

departments d,

locations l

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

GROUP BY city ;

案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT

department_name,

d.manager_id,

MIN(salary)

FROM

departments d,

employees e

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

AND commission_pct IS NOT NULL

GROUP BY department_name,d.`manager_id` ;

(4)可以加排序

案例：查询每个工种的工种名和员工的个数

SELECT

job_title,

COUNT(*)

FROM

employees e,

jobs j

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

GROUP BY job_title

ORDER BY COUNT(*) DESC ;

(5)可以实现三表连接

案例：查询员工名、部门名和所在的城市

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` ;

2、非等值连接

语法：

案例1：查询员工的工资和工资级别

SELECT

salary,

grade_level

FROM

employees e,

job_grades g

WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;

工作原理：

用employees表中的salary去和job_grades对比，比如查询24000的所在的等级，先用24000和1000及2999对比，如果介于这之间，则为等级A，依次分析，最终得到24000的等级为E。

3、自连接

语法：

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

同一张表查两次：比如查找Neena的上级是谁。

对上面分析的进一步拆分：

两次查询相当于把同一张表分别看着员工表和领导表，然后通过manager_id等于employee_id来查询。

本题解答：

SELECT

e.employee_id,

e.last_name AS "员工",

m.employee_id,

m.last_name AS "上级"

FROM

employees e,

employees m

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

小测试

1.显示员工的最大工资，工资平均值

SELECT MAX(salary),AVG(salary) FROM employees;

2.查询员工表的employees_id,job_id,last_name,按department_id降序，salary升序

SELECT

employee_id,

job_id,

last_name

FROM

employees

ORDER BY department_id DESC,salary ASC ;

3.查询员工表的job_id中包含a和e的，并且a在e的前面

SELECT

job_id

FROM

employees

WHERE job_id LIKE '%a%e%' ;

4.

SELECT

s.name,

g.name,

r.score

FROM

student s,

grade g,

result r

WHERE s.id = r.studentNo AND g.id = s.gradeid ;

5.显示当前日期，以及去前后空格，截取子字符串的函数

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

SELECT

last_name,

d.department_id,

department_name

FROM

employees e,

departments d

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

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

SELECT

job_id,

location_id

FROM

employees e,

departments d

WHERE e.`department_id` = d.`department_id` AND e.`department_id` = 90 ;

8.选择所有有奖金的员工的last_name ,department_id ,location_id ,city

SELECT

last_name,

department_name,

l.location_id,

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 ;

9.选择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 l

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

AND d.`location_id` = l.`location_id`

AND city = 'Toronto' ;

10.查询每个工种、每个部门的部门号、工种号和最低工资

SELECT

department_name,

job_title,

MIN(salary) AS "最低工资"

FROM

employees e,

departments d,

jobs j

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

AND j.`job_id` = e.`job_id`

GROUP BY department_name,job_title ;

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

SELECT

country_id,

COUNT(*) 部门个数

FROM

departments d,

locations l

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

GROUP BY country_id

HAVING COUNT(*) > 2 ;

12.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

SELECT

e.`last_name` employees,

e.`employee_id` "Emp#",

m.`last_name`,

m.`employee_id` = "Mgr#"

FROM

employees e,

employees m

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

AND e.`last_name` = "kochhar" ;