案例——连接查询"/>
MySQL案例——连接查询
#回顾:
分组查询 语法:
SELECT 分组函数,列
FROM 表名
【WHERE 筛选条件】
GROUP BY 分组的列表
【HAVING 筛选条件】
【ORDER BY 子句】;
连接查询
> 含义:又称多表查询(多表连接),当查询的字段来自多个表时,就会用到连接查询
> 笛卡尔乘积现象:表1 有m行数据, 表2 有n行数据
> 查询出来的结果有m*n行数据
> 发生原因:没有有效的连接条件
> 分类:
> 按年代分类:
> sql92标准:只支持内连接
> sql99标准【推荐】:支持内连接+外连接
> 按功能分类:
> 内连接:
> 等值连接
> 非等值连接
> 自连接
> 外连接:
> 左外连接
> 右外连接
> 全外连接
> 交叉连接
1、等值连接
①多表等值连接的结果为多表的交集部分
②n个表连接,至少需要n-1个连接条件
③多表的表名顺序没有要求
④一般都要给表起别名(注意:查询的表名起了别名 查询列表的字段就只能通过别名来.到)
⑤可以搭配前面讲过的所有的子句使用 ,比如排序、分组…
#案例:查询员工名对应的部门名
#员工名(last_name)在员工表(employees)中
#部门名(department_name)在部门表(departments)中
SELECT last_name,department_name
FROM employees,departments
WHERE departments.department_id=employees.department_id;
#案例2:查询员工名、工种号、工种名
#员工名(last_name)在员工表(employees)中
#工种号(job_id)、工种名(job_title)、在工种表(jobs)中
SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.job_id = jobs.job_id;
#为表起别名
①提高sql语句的简洁度
②区分多个重名的字段
SELECT last_name,j.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id;
#查询部门名、所在城市 locations表中的city
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id;
#加筛选条件的等值连接查询
#案例: 查询有奖金的员工名和部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE commission_pct IS NOT NULL
AND e.department_id=d.department_id;
#案例:查询城市名称中有字母o和s的(o在s的前面)的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE '%o%s%';
#等值连接查询加分组
#案例1:查询每个城市的部门个数
查询列表: 部门个数
城市 部门跟城市来自两个表 需要表连接
每个城市: 进行分组
SELECT COUNT(*) 部门个数,city 城市
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;
#案例2:查询有奖金的每个部门的部门名(department_name)和
#部门的领导编号(manager_id)和该部门的最低工资
查询列表:department_name、manager_id、salary
查询的表:departments、employees
查询条件:奖金不为空、找等值判断的列
每个部门:进行分组
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d ,employees e
WHERE commission_pct IS NOT NULL
AND d.department_id = e.department_id
GROUP BY d.manager_id,d.department_name;
#等值连接查询加排序
#案例:查询每个工种的工种名和员工的个数,按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#三表连接
#案例:查询员工名、部门名和所在的城市
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;
在数据库myemployees中创建工资等级表job_grades
三个字段 grade_level VARCHAR(3)等级 lowest_sal INT 最低工资highest_sal INT 最高工资
插入六条数据 A,1000,2999B,3000,5999C,6000,9999D,10000,14999E,15000,24999F,25000,40000
CREATE TABLE job_grades(grade_level VARCHAR(3),lowest_sal INT,highest_sal INT
)
INSERT INTO job_grades (grade_level,
lowest_sal,highest_sal) VALUES
('A',1000,2999),('B',3000,5999),('C',6000,9999),
('D',10000,14999),('E',15000,24999),('F',25000,40000);SELECT * FROM job_grades;
#非等值连接查询
#案例1:查询员工的工资和工资级别
#工资级别 job_grades 表中的 grade_level
SELECT salary,employee_id,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal;
#员工表中的salary字段会跟工资等级表中的数据逐行进行比较
#加条件的非等值判断
#案例:查询员工名中第二个字符为 o 的员工工资以及对应的工资级别
SELECT salary,last_name,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal
AND last_name LIKE '_o%';
#自连接
#案例:查询员工名和上级的名称
SELECT e.employee_id 员工id,e.last_name 员工名称,
m.employee_id 领导id,m.last_name 领导名称
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
#sql92语法:
1、等值连接select 查询列表from 表1 别名,表2 别名where 表1.字段 = 表2.字段【and 筛选条件】【group by 分组字段】【having 分组后的筛选】【order by 排序字段】;2、非等值连接select 查询列表from 表1 别名,表2 别名where 非等值的连接条件【and 筛选条件】【group by 分组字段】【having 分组后的筛选】【order by 排序字段】;3、自连接select 查询列表from 表 别名1,表 别名2where 等值的连接条件【and 筛选条件】【group by 分组字段】【having 分组后的筛选】【order by 排序字段】;
#练习答案:
#1. 显示所有员工的姓名,部门号和部门名称。
#等值连接 涉及到员工表和部门表
SELECT last_name,e.department_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
#2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id
#分析:部门员工 涉及到部门表 departments
#location_id涉及到位置表 locations
SELECT job_id,location_id
FROM departments d,employees e
WHERE d.department_id = e.department_id
AND d.department_id = 90;
#3. 选择所有有奖金的员工的
#last_name , department_name , location_id , city
#last_name 属于 employees表
#department_name 属于 departments表
#location_id、city 属于 locations
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 emission_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,city
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 department_name,job_title,MIN(salary)
FROM departments d,jobs j,employees e
WHERE d.department_id = e.department_id
AND j.job_id = e.job_id
GROUP BY d.department_id,job_title;
sql99语法
select 查询列表from 表1 别名 【连接类型】join 表2 别名 on 连接条件【where 筛选条件】【group by 分组字段】【having 分组后的筛选条件】【order by排序列表】分类:内连接:inner 外连接左外连接:left右外连接:right
一、内连接语法:select 查询列表from 表1 别名inner join 表2 别名on 连接条件;分类: 等值连接非等值连接自连接
#等值连接案例:查询员工名、部门名
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id;
#案例:查询名字中包含e字符的员工名(last_name)和工种名(job_title)
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';
#案例3:查询部门个数>3的城市名和部门个数(添加分组+筛选)
#①查询每个城市的部门个数
#②在①的结果上筛选满足条件的
SELECT COUNT(*) 部门个数,city
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*)>3;
#小练习:查询哪个部门的员工个数>3的部门名和员工个数
#departments表department_name employees表
#查询列表 部门名和员工个数
#查询的表 departments、employees
#连接条件 d.department_id = m。department_id
#筛选条件 员工个数>3 哪个部门 要进行分组
#count(*) 表示获取当前表数据的总行数
SELECT department_name,COUNT(*)
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#查询员工名、部门名、工种名,并按部门名降序(三表连接)
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON d.department_id=e.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;
#非等值连接
#查员工的工资级别 job_grades工资等级表
#逐行判断员工的工资在工资等级表里面所在的区间
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON salary BETWEEN g.lowest_sal AND g.highest_sal;
#自连接
#查询员工的名字、上级的名字
SELECT e.last_name 上级名称,m.last_name 员工名称
FROM employees e
INNER JOIN employees m
ON e.employee_id=m.manager_id;
#自连接添加筛选条件
#查员工的名字、上级的名字中包含o字符的
SELECT e.last_name 上级名称,m.last_name 员工名称
FROM employees e
INNER JOIN employees m
ON e.employee_id=m.manager_id
WHERE e.last_name LIKE '%o%';
二、外连接
应用场景:用来查询一个表中有,另一个表中没有的记录
特点:1、外连接的查询结果为主表中的所有数据如果从表中有跟他匹配的值,则显示匹配的值如果从表中没有和他匹配的,则显示null外连接查询的结果 = 内连接的结果 + 主表中有而从表中没有的数据2、左外连接,left join左边的是主表右外连接,right join右边的是主表3、左外连接跟右外连接交换顺序能实现同样的结果
#案例:查询哪个部门没有员工(左外连接)主表 部门表
SELECT d.*,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
(右外连接)
SELECT d.*,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
1.查询哪个城市没有部门
SELECT city,department_id
FROM departments d
RIGHT JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;
2.查询部门名为 SAL 或 IT 的员工信息
SELECT e.*,d.department_name,d.`department_id`
FROM departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');
3.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,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;
4.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;
5.选择指定员工的姓名、员工号、以及他的管理者的姓名和员工号,结果类似于下面的格式
emp_name emp_id mgr_name mgr_idkochhar 101 king 100
SELECT e.last_name emp_name,e.employee_id emp_id,m.last_name mgr_name,m.employee_id mgr_id
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';
更多推荐
MySQL案例——连接查询
发布评论