MySQL学习-SQL92连接查询，SQL99连接查询

MySQL学习-简单查询、条件查询、排序查询、分组查询

MySQL学习-子查询，分页查询，联合查询

SQL92连接查询

    连接查询，又称多表查询。
    SQL92支持等值，非等值，自连接，部分外连接。

---

#一、等值连接 多表的顺序可以调换

#查询员工名和对应的部门名

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

---

#为表起别名 如果为表起了别名，则查询的字段就不能使用原来的表名
#查询员工名、工种号、工种名

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

---

#查询有奖金的员工名、部门名

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

---

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

SELECT city,department_name FROM departments,locations
	WHERE locations.city LIKE '_o%' AND departments.location_id=locations.location_id;

---

#查询每个城市的部门个数

SELECT city,COUNT(*) FROM locations,departments
	WHERE locations.location_id=departments.location_id
		GROUP BY city;

---

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

SELECT job_title,COUNT(*) AS number FROM employees,jobs
		WHERE jobs.job_id=employees.job_id
				GROUP BY job_title ORDER BY number DESC;

---

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

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;

---

#查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) FROM departments,locations
		WHERE departments.location_id=locations.location_id
				GROUP BY country_id HAVING COUNT(*)>2;

---

#二、非等值连接

#查询员工的工资和工资级别并按工资级别升序

SELECT last_name,salary,grade_level FROM employees,job_grades
		WHERE salary BETWEEN lowest_sal AND highest_sal ORDER BY grade_level,salary;

---

#三、自连接 自己查询自己

#查询员工的名字及其领导的名字

SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
		FROM employees AS e,employees AS m
				WHERE e.manager_id=m.employee_id;

---

#四、交叉连接 笛卡尔乘积

SELECT locations.*,departments.* FROM locations,departments;

SQL99连接查询

    sql99语法
        SELECT 查询列表
        FROM 表1 别名
        [连接类型] JOIN 表2 别名
        ON 连接条件
        [WHERE 筛选条件]
        [GROUP BY 分组]
        [HAVING 筛选条件]
        [ORDER BY 排序]

    分类：内连接：INNER（可以省略）
            左外连接：LEFT [OUTER]
            右外连接：RIGHT [OUTER]
            全外连接：FULL [OUTER]
            交叉连接：CROSS

    内连接的结果=多表的交集
    1.外连接应用场景：用于查询一个表中有而另一个表没有的记录
    2.外连接特点：
            外连接的查询结果为主表中的所有记录
            如果从表中有和它匹配的，则显示匹配的值
            如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有同时从表中没有的记录

    3.左外连接：LEFT JOIN左边的是主表
       右外连接：RIGHT JOIN右边的是主表
    4.左外右外交换两个表的顺序可以实现同样的效果
    5.全外连接=内连接结果+表1中有表2中没有的记录+表2中有表1中没有的记录

#SQL99：筛选条件放在WHERE后面，连接条件放在ON后面，提高了分离性，便于阅读

---

#一、等值连接

#查询员工名、部门名

SELECT last_name,department_name FROM employees INNER JOIN departments
		ON employees.department_id=departments.department_id;

---

#查询名字中包含e的员工的名称和工种名（筛选）

SELECT last_name,job_title FROM employees INNER JOIN jobs
		ON employees.job_id=jobs.job_id WHERE last_name LIKE '%e%';

---

#查询部门个数大于3的城市名和部门个数（分组+筛选）

SELECT city,COUNT(*) FROM departments INNER JOIN locations
		ON departments.location_id=locations.location_id GROUP BY city HAVING COUNT(*)>3;

---

#查询哪个部门的部门员工个数大于3的部门名和员工个数，并按个数降序（排序）

SELECT department_name,COUNT(*) FROM departments INNER JOIN employees
		ON departments.department_id=employees.department_id
				GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;

---

#查询员工名、部门名、工种名、并按部门名排序（三表查询）

SELECT last_name,department_name,job_title FROM employees AS e
		INNER JOIN departments AS d ON e.department_id=d.department_id
				INNER JOIN jobs AS j ON e.job_id=j.job_id
						ORDER BY department_name DESC;

---

#查询部门名为SAL或IT的员工信息

SELECT department_name,employees.* FROM employees INNER JOIN departments
		ON employees.department_id=departments.department_id
				WHERE department_name='SAL' OR department_name='IT';

---

#二、非等值连接

#查询员工的工资和工资级别并按工资级别升序

SELECT salary,grade_level FROM employees INNER JOIN job_grades
		ON salary BETWEEN lowest_sal AND highest_sal
				ORDER BY grade_level;

---

#查询工资级别大于20的员工个数，并按工资级别升序

SELECT grade_level,COUNT(*) FROM employees INNER JOIN job_grades
		ON salary BETWEEN lowest_sal AND highest_sal
				GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level;

---

#三、自连接

#查询员工的名字及其领导的名字

SELECT e.last_name AS employee,m.last_name AS manager
		FROM employees AS e INNER JOIN employees AS m
				ON e.manager_id=m.employee_id;

---

#四、左外连接

#查询哪个部门没有员工

SELECT department_name,employee_id FROM departments LEFT JOIN employees
		ON departments.department_id=employees.department_id
				WHERE employee_id IS NULL;

---

##查询哪个城市没有部门

SELECT city,department_id FROM locations LEFT JOIN departments
		ON locations.location_id=departments.location_id
				WHERE department_id IS NULL;

---

#五、右外连接
#查询哪个部门没有员工

SELECT department_name,employee_id FROM employees RIGHT JOIN departments
		ON departments.department_id=employees.department_id
				WHERE employee_id IS NULL;

---

#六、交叉连接 笛卡尔乘积（92语法是，）

SELECT locations.*,departments.* FROM locations CROSS JOIN departments;

---

#七、全外连接（union默认去重）

SELECT department_name,employee_id FROM departments LEFT JOIN employees
ON departments.department_id=employees.department_id
UNION
SELECT department_name,employee_id FROM departments RIGHT JOIN employees
ON departments.department_id=employees.department_id;

    有 A，B 两张表

1. 内连接（中间）：A ∩ B = { x∣x ∈ A ∧ x ∈ B } 。AB共有的数据。
2. 左外连接（左上角）：A = A∪(A ∩ B) 。A独有的数据 + AB共有的数据。
3. 左外连接（左边）：A − B = A − A ∩ B = { x∣x ∈ A ∧ x ∉ B} 。A独有的数据。
4. 右外连接（右上角）：B = B∪(A ∩ B) 。B独有的数据 + AB共有的数据。
5. 右外连接（右边）：B − A = B − A ∩ B = { x∣x ∈ B ∧ x ∉ A} 。B独有的数据。
6. 全连接（左下角）：A∪B = { x ∣ x ∈ A ∨ x ∈ B } 。A独有的数据 + AB共有的数据 + B独有的数据。
7. 全连接（右下角）：A ⊕ B = ( A ∪ B ) − ( A ∩ B ) 。A独有的数据 + B独有的数据。
   ​