MySQL语言基础2（实例运用）

#内连接
select * from 表1 INNER JOIN 表2 on 表的连接条件 where 初始筛选条件

#找出部门名字和对应的员工名字
select 
departments.department_name,employees.first_name,employees.last_name
from departments INNER JOIN employees
on departments.department_id=employees.department_id

#找出没有奖金的员工名和员工部门
select 
e.first_name,e.last_name,d.department_name 
from employees e INNER JOIN departments d
ON d.department_id=e.department_id
WHERE e.commission_pct is NULL

#查询每个城市的部门个数
SELECT
city,count(*)
from locations l inner join departments d
ON l.location_id=d.location_id
GROUP BY l.city

#查询员工名，部门名和所在城市名(三表联合查询)
select first_name,department_name,city
FROM
WHERE
      AND

#自关联
省表
id 
省名 
父级id（null）  

市表 
id
市名
省id（外键）
   
区表
id
区名
市id（外键）

CREATE table areas(
id int UNSIGNED PRIMARY KEY,
aname varchar(200),
pid int
)

select * from areas

#查询所有的省
select * from areas where pid is NULL

#找出浙江省所有的城市  自关联：物理上的一张表，逻辑上的两张表甚至三张表
select * from areas where aname='浙江省'
select * from areas where pid=330000

select * 
from areas as province
INNER JOIN areas as city
ON province.id=city.pid
where province.aname='浙江省'

select * 
from areas as city
INNER JOIN areas as a
ON city.id=a.pid
where city.aname='杭州市'