MYSQL学习5连接查询

MYSQL学习5连接查询
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

 按功能分类：
 内连接：
         等值连接
         非等值连接
         自连接
 外连接：
          左外连接
          右外连接
          全外连接
 交叉连接         

*/
SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

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

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

#案例2：员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

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

#两个表的顺序可以调换

#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM jobs j,employees e
WHERE e.job_id=j.job_id;

#4.加筛选
#案例：查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE commission_pct IS NOT NULL
AND e.department_id=d.department_id;

#案例2；查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE ‘_o%’;

#加分组

#案例1：查询每个城市的部门个数

SELECT COUNT(*)部门个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY l.city;

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

#6.加排序

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

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

#7.三表连接
#案例：查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id;

#非等值连接
#案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal
AND j.grade_level=‘A’;

#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;

#测试题
#1.显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary)
FROM employees;

#查询员工表，按depatment_id降序，salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary;

#查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT job_id
FROM employees
WHERE job_id LIKE ‘%a%e%’;

#显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM(’’);
SELECT TRIM(字符 FROM’’);
SELECT SUBSTR(str,startIndex,LENGTH);

#练习
#1.显示所有员工的姓名，部门号和部门名
SELECT last_name,e.department_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

#查询90号部门员工的job id和90号部门的location id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.department_id=90;

#3.选择所有有奖金的员工的 last_name,department_id,location_id,city

SELECT last_name,e.department_id,l.location_id,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND commission_pct IS NOT NULL;

#4.选择city在Toronto工作的员工的last_name,job_id,department_id,department_name

SELECT last_name,job_id,e.department_id,department_name
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city =‘Toronto’;

#5.查询每个工种，每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary)
FROM employees e,departments d,jobs j
WHERE e.department_id=d.department_id
AND e.job_id=j.job_id
GROUP BY j.job_title,e.department_id;

#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
HAVING 部门个数>2;

#7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格数
# employees Emp# manager Mgr#
# kochhar 101 king 100****

SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e,employees m
WHERE e.manager_id=m.employee_id
AND e.last_name=‘kochhar’;