sql92： mysql 内连接

#笛卡尔积
/*
 表1：m行
 表2：n行
 结果;m*n行
 # 原因在于没有有效的连接条件
*/

SELECT COUNT(*) FROM departments; // 27
SELECT COUNT(*) FROM employees;//23
SELECT department_name, street_address,COUNT(*)
FROM departments, locations;

SELECT 23*27; //621

#解决：添加条件
SELECT department_name, street_address
FROM departments d, locations l
WHERE d.location_id = l.location_id;

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

*/

/*
等值连接：
多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配 排序 分组 筛选 子句使用
*/


#查询员工名和对应的部门名
SELECT last_name,department_name
FROM departments,employees
WHERE departments.`department_id` = employees.`department_id`;

#可以为表起别名
#如果使用了别名，则查询字段就不能使用原来的表名去限定
#查询员工名 工种号 工种名
SELECT e.last_name,j.job_id,j.job_title
FROM employees AS e, jobs AS j
WHERE e.job_id = j.`job_id`;

# 可以加筛选
# 查询有奖金的员工名 部门名
SELECT e.`last_name`, d.`department_name`,e.`commission_pct`
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL;

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

#可以加分组
#查询每个城市的部门个数
SELECT l.`city`,COUNT(d.`department_name`)
FROM departments d, locations l 
WHERE d.`location_id` = l.`location_id`
GROUP BY l.city;

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

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

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

#可以添加排序
# 查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT j.`job_id`,j.`job_title`,COUNT(*) c
FROM jobs j, employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY j.`job_id`
ORDER BY c DESC;

# 三表连接
# 查询员工名，部门名和所在的城市
SELECT e.`last_name`,d.`department_name`,l.`city`
FROM locations l, departments d, employees e
WHERE l.`location_id`=d.`location_id` AND d.`department_id`=e.`department_id`

#非等值连接
/*
*/

/*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 e.`last_name`, e.`salary`,g.`grade_level`
FROM employees e, job_grades g
WHERE e.`salary` > g.`lowest_sal` AND e.`salary` < g.`highest_sal`;

SELECT e.`last_name`, e.`salary`,g.`grade_level`
FROM employees e, job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#自连接
#员工名及上级的名字
SELECT e.`last_name`,r.m
FROM employees e,(SELECT e.`employee_id` mm,e.`last_name` m FROM employees) AS r
WHERE e.`manager_id` = r.mm;

SELECT e.`last_name`, r.`last_name`
FROM employees e, employees r
WHERE e.`manager_id` = r.`employee_id`;


SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';