AI智能
改变未来

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;
赞(0) 打赏
未经允许不得转载:爱站程序员基地 » 07.1sql99语法-内连接-等值连接——MySQL