SQL 连接查询

#SQL 连接查询

#连接查询 多表查询
SELECT 
  NAME,
  `boyName` 
FROM
  `beauty`,
  `boys` 
WHERE `beauty`.`boyfriend_id` = `boys`.`id` ;

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

#查询员工名，工种号，工种名,利用别名让语句简洁
SELECT `last_name`,e.`job_id`,`job_title`
FROM `employees` AS e,`jobs` AS j
WHERE e.`job_id` = j.`job_id`;

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

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

#查询每个城市的部门个数
SELECT COUNT(*),`city`
FROM `departments` AS d,`locations` AS l
WHERE d.`location_id` = l.`location_id` 
GROUP BY `city`;

#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT d.`manager_id`,`department_name`,MIN(`salary`)
FROM `employees` AS e,`departments` AS d
WHERE e.`department_id` = d.`department_id`
AND	`commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;

#添加排序
#查询每个工种的工种名和员工个数，并按员工个数降序
SELECT `job_title`,COUNT(*) 员工个数
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` e,`departments` d ,`locations` l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

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

#自连接，自己连接自己
#查询员工名和上级的名字
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` e, `employees` m
WHERE e.`manager_id` = m.`department_id`;

#SQL99语法
#内连接
#查询员工名,部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`;

#查询名字中包含e的员工名和工种名
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id` = j.`job_id`
WHERE `last_name` LIKE '%e%';

#查询部门个数>3的城市名和部门数
SELECT COUNT(*),`department_name`,`city`
FROM `departments` d
INNER JOIN `locations` l
ON d.`location_id` = l.`location_id`
GROUP BY `city`
HAVING COUNT(*) >3;

#查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
SELECT COUNT(*),`department_name`
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id` = e.`department_id`
GROUP BY `department_name`
HAVING COUNT(*) >3
ORDER BY COUNT(*) DESC;

#查询员工名，部门名，工种名，并按部门名排序,三表连接
SELECT `last_name`,`job_title`,`department_name`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id` = j.`job_id`
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
ORDER BY `department_name` DESC;

#查询员工的工资级别
SELECT `salary`,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#查询工资级别的个数>20的个数,并且按工资级别降序
SELECT COUNT(*),`salary`,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY `grade_level`
HAVING COUNT(*) > 20
ORDER BY `grade_level` DESC;


#自连接
#查询员工名字以及他的上级,以及字符包含k
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
INNER JOIN `employees` m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

#外连接 
#查询男朋友不在男生表中的女生名

#左外连接
SELECT b.name,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NOT NULL;

#查询哪个部门没有员工,left 左边为主表

SELECT d.*,e.*
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

#交叉连接,显示笛卡尔乘积效果

SELECT b.*,bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;