MySQL学习 连接查询

20210711

#进阶6 连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件

分类：
                按年代分类：
                sq192标准：仅支持内连接
                sq199标准【推荐】：除了外连接的全外连接，其他的都支持
                
                按功能分类：
                                            内连接：
                                                            等值连接
                                                            非等值连接
                                                            自连接
                                            外连接：
                                                            左外连接
                                                            右外连接
                                                            全外连接
                                            交叉连接
                                            
                            
*/

SELECT * FROM beauty

SELECT * FROM boys

SELECT name,boyName FROM beauty,boys #有问题，笛卡尔乘积现象

SELECT name,boyName FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id

#一、sq192标准
#1.等值连接
/*
    a.多表等值连接的结果为多表的交集部分
    b.n表连接，至少需要n-1个条件
    c. 多表的顺序没有要求
    d.一般需要给表起别名
    e.可以搭配前面所学所以子句使用，如排序，分组，筛选
*/
#案例1：查询女生名和对应的男生名

SELECT name,boyName FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id

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

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id

#2.为表起别名
#a.提高语句简洁度
#b.区分多个重名的字段

#注意：如果为表起了别名，则查询的字段就不能使用，语句读取顺序是先读FROM

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

SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.job_id = jobs.job_id

SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id

#3.两个表的顺序是否可以调换 可以

#4.是否可以加筛选条件
#案例1：查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL

#案例2城市名中第二个字符为0的部门名和城市名

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

#5.是否可以加分组

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

SELECT COUNT(*) 个数,city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city

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

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

#6.是否可以加排序

#案例： 查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*) 个数
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) desc

#7.三表连接

#案例： 查询员工名，部门名和所在城市

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 
AND city like 's%'

#二、非等值连接

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

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

SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A'

#三、自连接

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

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