MySQL学习第四天天 sql99语法 连接查询总结

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'