1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| /* 用法: SELECT name, boyName FROM boys, bueaty WHERE bueaty.boyfriend_id = boys.id; */
# 查询员工名,工种号,工种名 SELECT last_name, employees.job_id, job_title FROM employees, jobs WHERE employees.'job_id' = jobs.'job_id';
# 为表起别名 SELECT e.last_name, e.job_id, j.job_title FROM employees e, jobs j WHERE e.'job_id' = j.'job_id';
# 加筛选 # 查询有将近的员工名和部门名 SELECT last_name, department_name, commission_pct FROM employees e, departments d WHERE e.'department_id' = d.'department_id' AND e.'commission_pct' IS NOT NULL;
# 加分组 # 查询每个城市的部门数量 SELECT COUNT(*) 个数, city FROM departments d, locations l WHERE d.'location_id' = l.'location_id' GROUP BY city;
# 查询有奖金的部门名和部门领导编号和最低工资 SELECT department_name, manager_id, MIN(salary) FROM department d, employees e WHERE d.'department_id' = e.'department_id' AND commission_pct IS NOT NULL GROUP BY department_name, d.manager_id;
# 加排序 # 查询每个工种的工种名,员工个数,并且按照员工个数降序 SELECT job_titile, COUNT(*) FROM employees e, jobs j WHERE e.'job_id' = j.'job_id' GROUP BY job_title ORDER BY COUNT(*) DESC;
# 三表连接 SELECT last_name, department_name,city FROM emplyees e, departments d, locations l WHERE e.'departemnt_id' = d.'department_id' AND d.'location_id' = l.'location_id';
|