mysql连接查询

/* 
含义：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行 ，表2 有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：加上有效的连接条件

分类：
    
     按年代分类
     sql92标准：仅仅支持内链接
     sql99标准【推荐】：所有的内连接+外连接（左外和右外）+交叉连接
     
     按功能分类：
              内链接：
                     等值连接
                     非等值连接
                     自连接
               外连接：
                     左外连接
                     右外连接
                     全外连接(mysql不支持)
               交叉连接
*/
SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyname FROM boys,beauty
WHERE beauty.boyfriend_id =boys.`id` 

#一、sql92标准
#1、等值连接
/* 
 1、多表等值连接的结果为多表的交集部分
 2、n便连接需要n-1个连接条件
 3、多表的顺序没有要求
 4、一般需要起别名
 5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

# 案例1：查询女神名和对应的男神名
SELECT NAME,boyname 
FROM boys,beauty
WHERE beauty.boyfriend_id =boys.`id` 

# 案例2：查询员工名和对应的部门名

select last_name,department_name
from employees,departments
where employees.`department_id`=departments.`department_id`;

#2、为表起别名
/*
 提高语句的简洁度
 区分多个重名的字段
 
 注意：如果为表起了别名，则查询的字段就不能使用原来的别名去限定
 
 */
# 查询员工名、工种号、工种名
SELECT last_name,A.job_id,job_title
FROM employees A,jobs B
WHERE A.`job_id`=B.`job_id`

# 3、两个表的顺序是否可以调换

# 查询员工名、工种号、工种名
SELECT last_name,A.job_id,job_title
FROM jobs B,employees A
WHERE A.`job_id`=B.`job_id`

# 4、可以加筛选？

#案例1：查询有奖金的员工名和部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id` IS NOT NULL;

# 案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d ,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

# 5、可以加分组？

# 案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city

# 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,manager_id;

# 6、可以加排序

# 案例：查询每个工种的工种名和员工个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7、可以实现3表连接

# 案例：查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM departments d,employees e,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

# 2、非等值连接

# 案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`

# 3、自连接

# 案例：查询员工名以及上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE  e.`manager_id`=m.`employee_id`