SQL文件见第三天和第一天
/*
语法:
select 查询列表
from 表 别名
jion 表2 别名
on 连接条件
where 筛选条件
gruop by 分组
having 筛选条件
order by 排序列表
内连接 :inner
外连接
左外 :left (outer)
右外 :right (outer)
全外 :full (outer)
交叉连接 :cross
*/
#一,内连接
/*
语法:
select 查询列表
from 表 别名
inner join 表 别名
on 连接条件
分类:
等值
非等值
自连接
特点:
添加排序1,分组,筛选
inner可以省略
筛选条件放在where后面,连接条件放在on后面
inner join连接和sql92语法中的等值连接效果一样,都是查询多表的交集
*/
# 等值连接
#查询员工名和部门名
SELECT `department_name`,`last_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 e.`last_name` LIKE "%e%"
#查询部门个数大于三的城市名和部门个数
#先查每个城市的部门个数再筛选
SELECT `city`,COUNT(*)
FROM `locations` l
INNER JOIN `departments` d
ON d.`location_id`=l.`location_id`
GROUP BY `city`
HAVING COUNT(*)>3
#查询哪个部门的员工数》3的部门名和员工个数,并按个数降序
SELECT `department_name`,COUNT(*)
FROM `departments` d
INNER JOIN `employees` e
ON e.`department_id`=d.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC
#查询员工名,部门名,工种名,并按部门名降序
SELECT `last_name`,`department_name`,`job_title`
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
JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#自连接
#查询员工名字和上级名字
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
JOIN `employees` m
ON e.`manager_id`=m.`employee_id`
#外连接 用于查询一个表中有另一个表中没有
/*
特点:外连接查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
没有显示null
左外链接: left join 左边的是主表
右外连接: right join 右边的是主表
左外和右外交换两个表的顺序,可以实现同样的效果
*/
# 查询男朋友不在男神表的女神名
#左外连接
SELECT `name`
FROM `beauty`
LEFT OUTER JOIN `boys`
ON `beauty`.`boyfriend_id`=boys.`id`
WHERE `boys`.`id` IS NULL
#右外链接
SELECT `name`
FROM `boys`
RIGHT OUTER JOIN `beauty`
ON `beauty`.`boyfriend_id`=boys.`id`
WHERE `boys`.`id` IS NULL
#查询哪个部门没有员工
SELECT `department_name`
FROM `departments`
LEFT OUTER JOIN `employees`
ON `departments`.`department_id`=`employees`.`department_id`
WHERE `employees`.`employee_id`IS NULL
#交叉连接 笛卡尔积形式
SELECT `boys`.*,`beauty`.*
FROM `beauty`
CROSS JOIN `boys`
测试练习
SELECT be.`id`,be.`name`,bo.*
FROM `beauty` be
LEFT OUTER JOIN `boys` bo
ON be.`boyfriend_id`=bo.`id`
WHERE be.`id`>3
SELECT `city`,d.*
FROM `locations` l
LEFT OUTER JOIN `departments` d
ON l.`location_id`=d.`location_id`
HAVING d.`department_id` IS NULL
SELECT `employees`.*
FROM `employees`
JOIN `departments`
ON `departments`.`department_id`=`employees`.`department_id`
WHERE `departments`.`department_name`='SAL' OR `departments`.`department_name`='IT'