连接查询
又称多表查询，当我们查询的字段来自于多个表时，会用到连接查询
笛卡尔集错误情况：表1有m行，表2有n行，结果是m*n行
如何解决：添加有效的连接
分类：
1.按年代分类
sql92标准:仅仅支持内连接
sql99标准/推荐：支持内连接、外连接（不支持全外连接）、交叉连接
2.按功能分类：
a.内连接：等值连接、非等值连接、自连接
b.外连接：左外连接、右外连接、全外连接
c.交叉连接
案例：
SELECT * FROM beauty
SELECT * FROM boys
SELECT name,boyName FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id
一、SQL 92标准
1.等值连接
案例一、查询女生名对应的男生名
SELECT name,boyName FROM beauty,boys
WHERE beauty.boyfriend_id =boys.id
案例二、查询员工名对应的部门名
SELECT name department_name FROM mylist,departments
WHERE mylist.department_id =departments.department_id
案例三、查询员工名，工种号，工种名(为表起别名)
SELECT last_name,job_title, e.job_id
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
两个表的顺序可以调换
案例四、查询有奖金的员工名，部门名(可以加筛选)
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND commission_pct IS NOT NULL
案例五、查询城市名中第二个字符为o的部门
SELECT city ,department_name FROM departments d ,locations l
WHERE d.location_id=l.location_id
AND city like'_o%'
案例六、查询每个城市的部门个数（可以加分组）
SELECT count(*),city FROM departments d ,locations l
WHERE d.location_id =l.location_id
GROUP BY city
案例七、查询有奖金的每个部门的部门名，和部门领导编号，和该部门的最低工资
SELECT MIN(salary),d.`manager_id`,department_name
FROM departments d,employees e
WHERE e.commission_pct IS NOT NULL
AND d.department_id =e.department_id
GROUP BY department_name,d.`manager_id`
案例八，查询每个工种的工种名和员工的个数，并按照员工个数降序（可以加排序）
SELECT j.job_title , count(*)FROM employees e, jobs j
WHERE e.`job_id` =j.`job_id`
GROUP BY j.`job_title`
ORDER BY count(*) DESC
案例九：查询员工名、部门名和所在的城市
SELECT e.last_name,d.department_name,l.city
FROM employees e ,departments d ,locations l
WHERE l.location_id =d.location_id
AND e.department_id =d.department_id
AND city LIKE'S%'
ORDER BY e.last_name desc
2.非等值连接
查询员工工资和员工级别, 并将员工工资从低到高排序
SELECT salary,grade_level FROM employees e ,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.grade_level ='A'
ORDER BY salary ASC
3.自连接
查询上级对应的员工的名称，上级名称
SELECT e.last_name ,e.employee_id, m.manager_id,m.last_name
FROM employees e ,employees m
WHERE e.employee_id = m.manager_id
GROUP BY manager_id
SELECT * FROM employees e
测试题目
1.显示员工表的最大工资，工资平均值
SELECT max(salary),min(salary) FROM employees e
2.查询员工表employee_id,job_id,last_name,按照department_id降序，按照salary升序
SELECT employee_id,job_id,last_name,department_id,salary FROM employees e
ORDER BY department_id DESC,salary ASC
3.查询员工表的job_id中包含a和e,并且a在e的前面
SELECT job_id FROM employees e
WHERE job_id LIKE '%a%e%'
4.已知表student 里面有id(学号) ,name,gradeId（年级编号）
已知表grade 里面有id（年级编号） ,name（年级名）
已知表result里面有id ,score,studentNo(学号)
查姓名，年级名，成绩
SELECT s.name,g.name,r.score
FROM student s,RESULT r,grade g
WHERE s.gradeId=g.id AND s.id=r.studentNo
5.显示当前日期，以及去前后空格，截取字符串的函数
SELECT substr(trim(now()),1,10 )
