07.1sql99语法-内连接-等值连接——MySQL

/*sql99语法：SELECT 查询列表from 表1 别名【连接类型】join 表2 别名on 连接条件【where 连接条件】【group by 分组】【having 筛选条件】【order by 排序条件】内连接(最重要的) ：连接类型：inner外连接左外连接类型：left 【outer】右外连接类型：right 【outer】全外连接类型：full 【outer】交叉连接连接类型：crose*/-- 1.等值连接-- 案例1：查询员工名，部门名（调换位置）SELECT last_name,department_nameFROM employees eINNER JOIN departments dON e.department_id=d.department_id;-- 案例2：查询员工名中包含e的员工名和工种名SELECT last_name job_titleFROM employees eINNER JOIN jobs jON e.job_id=j.job_idWHERE last_name LIKE \"%e%\";-- 案例3：查询部门个数>3的城市名和部门个数（分组+筛选）SELECT city,COUNT(*) 部门个数FROM departments dJOIN locations l  -- inner 可以省略ON l.location_id=d.location_idGROUP BY cityHAVING count(*)>3;-- 案例4：查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序 （排序）SELECT department_name,COUNT(employee_id)FROM departments dINNER JOIN employees eon d.department_id=e.department_idGROUP BY department_nameHAVING COUNT(employee_id)>3ORDER BY COUNT(employee_id);-- 案例5：查询员工名、部门名、工种名，并按部门名降序SELECT last_name,department_name,job_titleFROM employees eINNER JOIN departments dON e.department_id=d.department_idINNER JOIN jobs jON e.job_id=j.job_idORDER BY department_name desc;