MySQL:连接查询

#进阶6:连接查询
/*
含义:又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表二有n行，结果有m * n行
发生的原因是没有有效的连接条件
分类
    
     按年代分类
     sql92
     sql99
     
     按功能分类
          内连接:
               等值连接
               1,多表查询值得结果为多表的交集部分
               2,n表连接，至少需要n - 1个连接条件
               3，多表的顺序没有要求
               4，一般为表别名
        
               非等值连接
               自然连接
          外连接
               左外连接
               右外连接
               全外连接
               
          交叉连接
*/
SELECT  *
FROM    boys;#男生表

#----------------
SELECT  *
FROM    beauty;#女生表

#------------------
#根据男女生·表来配对

SELECT  NAME,boyName
FROM    beauty,boys;#这样会出现错误被称为笛卡尔成积的错误

#改正
SELECT  NAME,boyName
FROM    beauty,boys
WHERE   beauty.`boyfriend_id` = boys.`id`;
#--------------------------------
#一,sql92标准
#1,等值连接
#查询女生名和对应的男生名
SELECT beauty.`name`,boys.`boyName`

FROM beauty,boys

WHERE
     beauty.`boyfriend_id` = boys.`id`;

#2,查询员工名，和对应的部门名
SELECT last_name,department_name

FROM employees AS e,departments AS d

WHERE e.`department_id` = d.`department_id`;

#3,查询员工名，工种号，工种名
SELECT  last_name,e.job_id,job_title

FROM employees AS e,jobs AS j#为表起别名,如果为表起别名，则查询的字段就不能使用原来的表名去限定

WHERE e.`job_id` = j.`job_id`;


#添加筛选
#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`;


#5,查询城市名中第二个字符为o的部门名和城市名

SELECT  department_name,city

FROM departments AS d,locations AS l

WHERE city LIKE '_o%'

AND d.`location_id` = l.`location_id`;


#加分组

#6,查询每一个城市的部门个数
SELECT  COUNT(*),city

FROM departments AS d,locations AS l

WHERE d.`location_id` = l.`location_id`

GROUP BY city;

#7,查询有奖金的每一个部门的部门名和部门领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)

FROM departments AS d,employees AS e

WHERE commission_pct IS NOT NULL
AND  d.`department_id` = e.`department_id`

GROUP BY department_name;
 
#加排序
#查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT COUNT(*),job_title

FROM employees AS e,jobs AS j

WHERE e.`job_id` = j.`job_id`

GROUP BY job_title

ORDER BY COUNT(*)
DESC;

#实现三表连接
#查询员工名，部门名，所在城市
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`;


#非等值连接
#查询员工的工资和工资等级
SELECT salary,grade_level

FROM  employees AS e,job_grades AS j

WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
ORDER BY salary;
     
#select salary,employee_id from employees;

#select * from 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);
*/
#自连接(查询时同一个表用两次)
#查询员工名，和上级名
SELECT e1.manager_id,e1.`last_name`,e2.`employee_id`,e2.`last_name`

FROM employees AS e1,employees AS e2

WHERE e1.manager_id = e2.employee_id;

#----------练习
#显示员工表的最大工资，工资的平均值
SELECT  MAX(salary),AVG(salary)

FROM  employees;

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

FROM employees

ORDER BY department_id DESC,salary;

#查询员工表的job_id中包含a和e的并且a在e的前面
SELECT  job_id

FROM employees

WHERE job_id LIKE '%a%e%';


#查询当前时间
SELECT 
 NOW();
 
#-------------------------案例
#1，查询所有员工的姓名，部门号和部门名称
SELECT last_name,e.department_id,department_name

FROM employees AS e,departments AS d

WHERE e.`department_id` = d.`department_id`;

#2,查询90号部门员工的job_id,和90号部门的location_id
SELECT job_id,location_id

FROM employees AS e,departments AS d

WHERE e.`department_id` = 90
AND e.`department_id` = d.`department_id`;

#3,查询所有有奖金的员工的last_name,department_id,deparment_name

SELECT last_name,e.department_id,d.`department_name`

FROM employees AS e,departments AS d

WHERE commission_pct IS NOT NULL;

#4,查询city在Toronto工作的员工的last_name,job_id,deparment_id,deparment_name
SELECT last_name,job_id,e.department_id,department_name,l.`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` 

AND  l.`city` = 'Toronto' ;


#5,查询每一个工种，每一个部门的部门名，工种名和最低工资
SELECT department_name,job_title,MIN(salary)

FROM employees AS e,departments AS d,jobs AS j

WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`

GROUP BY j.`job_title`,d.`department_name`;

#6,查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*)

FROM departments AS d,locations AS l

WHERE d.`location_id` = l.`location_id` 


GROUP BY country_id

HAVING COUNT(*) > 2;