MySQL-DQL-连接查询sql92

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

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
	按年代分类：
	sql92标准：仅仅支持内连接
	sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
	
	按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		有外连接
		全外连接
	交叉连接

*/

SELECT * FROM beauty;
SELECT * FROM boys;

#笛卡尔乘积
SELECT NAME,boyName FROM boys,beauty;

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

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

#案例1：查询女神名和对应的男神名
USE girls;

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

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

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#2.为表起别名
/*
好处：
	1.提高语句的简洁度
	2.区分多个重名的字段

注意：
	1.如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/

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

SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id=j.job_id

#3.两个表的顺序可以调换
SELECT last_name,e.job_id,job_title
FROM jobs AS j,employees AS e
WHERE e.job_id=j.job_id

#4.可以加筛选
#案例1：查询有奖金的员工名和部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.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 l.city LIKE '_o%';

#5.可以加分组
#案例1：查询每个城市的部门个数
SELECT city,COUNT(department_id)
FROM locations l,departments d
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;

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,d.manager_id;

#6.可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(employee_id)
FROM jobs j, employees e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY COUNT(employee_id) DESC;

#7.可以实现三表连接
#案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND l.location_id=d.location_id;

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

#2.非等值连接
#案例1：查询出员工的工资和工资级别
/*生成job_grades表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES ('C', 6000, 9999);

INSERT INTO job_grades
VALUES ('D', 10000, 14999);

INSERT INTO job_grades
VALUES ('E', 15000, 24999);

INSERT INTO job_grades
VALUES ('F', 25000, 40000);

select * from job_grades;
*/
SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

#3.自连接
#案例：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.manager_id,m.last_name
FROM employees e, employees m
WHERE e.manager_id=m.employee_id;

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

#测试2.查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT employee_id,job_id,last_name,department_id,salary
FROM employees
ORDER BY department_id DESC,salary ASC;

#测试3.查询员工表的job_id中包含a和e的，并且a在e的前面(疑问：eae是不是也会匹配，但是不符合题目要求呀？？？)
SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';

#测试4.已知表student，里面有id(学号),name,gradeId(年级编号)
#已知表grade,里面有id(年级编号),name(年级名)
#已知表result,里面有id,score,studentNo(学号)
#要求查询姓名、年级名、成绩
SELECT s.name,g.name,score
FROM student s,grade g,result r
WHERE s.gradeId=g.id
AND s.id=r.studentNo;

#测试5.显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT CURDATE();
SELECT TRIM()
SELECT SUBSTR(str,INDEX,len)

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

#作业2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.department_id,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_name,location_id,city
SELECT last_name,department_name,l.location_id,city,commission_pct
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,d.department_id,department_name,city
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 e.job_id,job_title,department_name,MIN(salary)
FROM employees e,jobs j,departments d
WHERE e.job_id=j.job_id
AND e.department_id=d.department_id
GROUP BY job_title;

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

#作业7.选择制定员工的姓名，原公告，以及他的管理者的姓名和员工号，结果类似于下面的格式
/*
employees    Emp#    manager    Mgr#
kochhar      101     King       100
*/
SELECT
	e.last_name employees,
	e.employee_id 'Emp#',
        m.last_name manager,
        m.employee_id 'Mgr#'
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;