Java学习--- MySQL多表查询与自连接

目录

一、多表查询

二、笛卡尔集

 三、非等值连接

 四、自连接

五、练习

一、多表查询

基于两个和两个以上的表查询，在实际应用中，单表查询不能满足需求。

当在多表查询的条件不能少于表的个数-1，不然会出现笛卡尔集

二、笛卡尔集

-- 多表查询
-- 查询员工名，员工工资和所在部门名字[笛卡尔集]
-- 当需要指定显示某个表的列是：表.列表
SELECT ename,sal,dname,emp.deptno 
       FROM emp,dept WHERE emp.deptno = dept.deptno
-- 查询部门号为3的部门名，员工名和工资
SELECT ename,sal,dname,emp.deptno
       FROM emp,dept 
       WHERE emp.deptno = dept.deptno AND emp.deptno = 3
-- 查询各个员工的姓名，工资，和工资级别
SELECT ename,sal,grade
        FROM emp,salgrade
        WHERE sal BETWEEN losal AND hisal

 三、非等值连接

SELECT e.last_name,e.salary,j.grade_level FROM `employees` e,`job_grades` j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 四、自连接

同一张表的连接查询（可以将一张表看作为两张表）。

-- 自连接
-- 查询公司员工名字和他上级的名字
-- 员工和上级通过emp表的mgr列关联
SELECT worker.ename AS'员工名', boss.ename AS'上级名'
       FROM emp worker,emp boss
       WHERE worker.mgr = boss.empno

自连接特点：1.把一张表当做两张表使用   

                       2.需要给表取别名  表名  表别名 

                      3.列名不明确，要指定列的别名， 列名as列的别名

五、练习

#1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name FROM `employees` e LEFT OUTER JOIN 
`departments` d ON e.`department_id` = d.`department_id`;

# 2.查询90号部门员工的job_id和90号部门的location_id
select e.job_id,d.location_id from employees e join departments d 
on e.`department_id` = d.`department_id` where e.department_id = 90;

      

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
select e.last_name , d.department_name , l.location_id , l.city from employees e 
left join departments d on e.`department_id` = d.`department_id`
left join `locations` l on d.location_id = l.location_id
where e.commission_pct is not null ;

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
    SELECT  e.last_name , e.job_id , e.department_id , d.department_name FROM `employees` e
    JOIN `departments` d ON e.department_id = d.department_id
    JOIN `locations` l ON l.location_id = d.location_id
    WHERE l.city = 'Toronto';

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#employees Emp# manager Mgr#
#kochhar 101 king 100
SELECT emp.last_name "employees",emp.employee_id "emp",mgr.last_name "manager",mgr.employee_id "Mgr"
FROM `employees` emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

# 7.查询哪些部门没有员工
SELECT d.department_id FROM `departments` d 
LEFT JOIN `employees` e ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

# 8. 查询哪个城市没有部门 
SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');