#案例1 查询女神名和男生名SELECT NAME, boyName
FROM boys,beauty
WHERE beauty.boyfriend_id =boys.id;#案例2 查询员工名和对应的部门名SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=department.`department_id`;#查询员工名,工种号,工种名SELECT employees.last_name,employees.job_id,jobs.job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`= j.`job_id`;
/*
sg199语法
语法:
select 查询列表
from 表1 别名[连接类型]
inner join 表2 别名
on 连接条件
[where 筛选条件]
group by 分组]
having筛选条件
order 排列列表
分类:内连接(h) : inner
外连接
左外(女):left [outer]台外(女): right [outer全外: full [outer]交又连接: cross
*/
#1.等值连接#案例1,查询员工名,部门名SELECT last_name,department_name
FROM employees e
INNERJOIN departments d
ON e.`department_id`= d.`department_id`;#案例2,查询名字中包含e的员工名和工种名(添加筛选)SELECT last_name,job_title
FROM employees e
INNERJOIN jobs j
ON e.`job_id`=j.`job_id`WHERE e.`last_name`LIKE'%e%';#3.查询部门个数>3的城市名和部门个数,(添加分组+筛选)SELECT city,COUNT(*) 部门个数
FROM departments D
INNERJOIN locations L
ON D.`location_id`=L.`location_id`GROUPBY city
HAVINGCOUNT(*)>3;