MYSQL初阶学习笔记——进阶6：连接查询

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

笛卡尔乘积现象：表1：m行，表2：n行，结果m*n行

发生原因；没有有效的连接条件
如何避免：添加有效的连接条件

分类：
    按年代分类；
    sql92标准：仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：等值连接，非等值连接，自连接
        外连接：左外连接，右外连接，全外连接
        交叉连接

*/

#一、sql标准

##1.等值连接

/*
特点总结：
1.多表等值连接的结果为多表的交集部门
2.n表连接，至少需要n-1连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配之前所有子句使用，包括排序，分组，筛选



*/

#案例1：员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#案例2：查询员工名、工种号、工种名
SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE jobs.job_id=employees.job_id;

#2.为表起别名

##若为表起了别名。则查询字段不能使用原表名了。应使用别名

#案例：查询员工名、工种号、工种名（别名类型）
SELECT last_name,e.job_id,job_title
FROM employees e,jobs
WHERE jobs.job_id=e.job_id;


#3.调换表顺序不影响结果

#案例：查询员工名、工种号、工种名（别名类型）
SELECT last_name,e.job_id,job_title
FROM jobs j,employees e
WHERE j.job_id=e.job_id;


#4.可以加筛选条件(Where 后用 and 加条件)

#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id
AND employees.`commission_pct` IS NOT NULL;

#案例2：查询城市名中第二个字符为o 的部门名和城市名
SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

#5.可以加分组？

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

SELECT COUNT(*),city
FROM departments AS d,locations AS 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.`manager_id`=e.`manager_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name;

#6.可以加排序？

#案例1：查询每个工种的工种名和员工个数，并按员工个数降序
SELECT j.`job_id`,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`;

#案例：查询员工名、部门名和所在城市(城市名以s开头，按城市名长度降序排列)
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%'
ORDER BY LENGTH(city) DESC;

##2.非等值连接

#先创建工资等级表job_grades
/*
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`;
    #意思是，将salary处于一个范围内，其grade_leve就会连接g表的级别


##3.自连接（自己连接自己，必须带有一些字段能匹配）
    #只是将一个表用两次进行查询连接 

#案例1：查询 员工名和上级的名称 （manager_id 与 employee_id 代表一个人）

SELECT e.employee_id,e.last_name,e.manager_id,m.employee_id,m.last_name
FROM employees e,employees m  #用别名可完美避免歧义
WHERE e.manager_id=m.employee_id;




#========================Test=============================
#1.显示员工表的最大工资，工资平均值(每个job_id下)
SELECT MAX(salary),AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary);

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

SELECT employee_id,job_id,last_name,salary,department_id
FROM employees
ORDER BY department_id DESC,salary;


#3.查询员工表job_id包含 a 和 e的，且a%e
SELECT job_id,last_name
FROM employees
WHERE job_id LIKE '%a%e%';


#4.
/*
已知表student，里面有id(学号)，name，gradeId（年级编号）
已知表grade,里面有id(年级编号)，name（年级名）
已知表result，里面有id，score，studentNu（学号）
查询 姓名 年级 成绩
*/
SELECT s.name,g.name,r.score
FROM student s,grade g,result r
WHERE s.gradeId=s.id
AND s.id=r.studentNu;

#5.显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM('   111    ') AS exa,LENGTH(TRIM('   111    '));
SELECT SUBSTR('love',3);
SELECT SUBSTR('love',2,2);