欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

DQL---连接查询

程序员文章站 2023-03-08 12:00:21
连接查询含义:涉及多个表字段的查询笛卡尔乘积:表一有m行,表二有n行,结果为m*n行(缺乏连接条件)分类:按年代分类:sq192标准。sq199标准[推荐]按功能分类:内连接(等值/非等值/自连接),外连接(左/右/全),交叉连接----------------------------------------sq192标准-------------------------------------------# 1.1 等值连接SELECT last_name,department......

DQL—连接查询

  1. 连接查询

含义:涉及多个表字段的查询
笛卡尔乘积:表一有m行,表二有n行,结果为m*n行(缺乏连接条件)
分类:
按年代分类:sq192标准。sq199标准[推荐]
按功能分类:内连接(等值/非等值/自连接),外连接(左/右/全),交叉连接

----------------------------------------sq192标准-------------------------------------------
# 1.1 等值连接
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#1.2 为表起别名
/* 如果为表起了别名,那么不能使用原始的表名*/
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

# 1.3 两个表的顺序是否可以调换
SELECT last_name,e.job_id,job_title
FROM jobs AS j,employees AS e
WHERE e.`job_id`=j.`job_id`;

# 1.4 是否可以加筛选
SELECT last_name,department_name,commission_pct
FROM employees AS e ,departments AS d
WHERE commission_pct IS NOT NULL AND e.`department_id`=d.`department_id`;

SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.`location_id`=l.`location_id` AND l.`city` LIKE '_o%';

# 1.5 是否可以加分组
SELECT city,COUNT(*)
FROM locations AS l,departments AS d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;

SELECT department_name,d.manager_id,MIN(salary)
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL
GROUP BY e.department_id;

# 1.6 是否可以加排序
SELECT job_title,COUNT(*)
FROM employees AS e, jobs AS j
WHERE e.`job_id`=j.`job_id`
GROUP BY e.`job_id`
ORDER BY COUNT(*) DESC;

# 1.7 多表连接
SELECT last_name,department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;

# 1.8 非等值连接
## 查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees AS e,job_grades AS j
WHERE e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;

# 1.9自连接

## 查询员工名和上级名称
SELECT e.employee_id 员工编号,e.last_name 员工名称,m.employee_id 领导编号,m.last_name 领导名称
FROM employees AS e,employees AS m
WHERE e.`manager_id`=m.`employee_id`;
---------------------------------------sql199标准(推荐使用)--------------------------------------
/*
语法: select 查询列表 
       from 表1 别名 [连接类型]
       join 表2 别名
       on 连接条件
       【where/group by/having/order by/】
分类: 内连接--> inner
       外连接--> 左外 left
                 右外 right
                 全外 full    
       交叉连接--> cross
*/

# 1. 内连接(inner join)
/*
分类:等值、非等值、自连接(inner可以省略)
*/
# 1.1 等值连接
## 查询员工名与部门名
SELECT last_name,department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`;
## 查询名字中包含e的员工名与工种名
SELECT last_name,job_title
FROM employees AS e
INNER JOIN jobs AS j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';
## 查询部门个数大于3的城市名和部门个数
SELECT city,COUNT(*)
FROM departments AS d
INNER JOIN locations AS l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
## 查询哪个部门的员工个数大于3的部门名和员工个数,并按照个数降序
SELECT department_name,COUNT(*)
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
## 查询员工名、部门名、工种名,并按照部门名降序
SELECT last_name,department_name,job_title
FROM employees AS e
INNER JOIN departments AS d ON e.`department_id`=d.`department_id`
INNER JOIN jobs AS j ON e.`job_id`=j.`job_id`
ORDER BY d.department_name DESC;
# 1.2 非等值连接
## 查询员工工资级别大于20的个数并按照级别降序
SELECT grade_level,COUNT(*)
FROM employees AS e
JOIN job_grades AS jg 
ON e.`salary`>= jg.`lowest_sal` AND e.`salary`<=jg.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
# 1.3 自连接
## 查询姓名中包含K的员工的名字,上级的名字
SELECT e.last_name AS 员工名,m.last_name AS 上级名
FROM employees AS e
JOIN employees AS m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

# 2. 外连接
/*
场景:查询一个表中,而在另一个表中没有的字段
特点:
    1. 外连接中查询结果等于内连接结果加上主表有从表无的结果
    2. 左外连接,left左边为主表
    3. 右外连接,right右边为主表
    4. 左外右外交换两个表顺序,效果不变
    5. 全外连接结果=内连接结果+主表有从表无的结果+从表有主表无的结果
*/
## 查询那个部门没有员工
SELECT d.*,employee_id
FROM departments AS d
LEFT OUTER JOIN employees AS e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

# 3. 交叉连接
/*实现dikaer乘积*/
SELECT b.*,bo.*
FROM beauty AS b
CROSS JOIN boys AS bo;

本文地址:https://blog.csdn.net/G_eraint/article/details/107311899