mysql数据库操作（六）

如需用我的练习表，发消息qq:2383241998@qq.com

一、sql92语法：

select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]
特点：
1.一般为表起别名
2.多表的顺序可以调换
3.n表连接至少需要n-1个连接条件
4.等值连接的结果是多表的交集部分

sql99语法(推荐)：

select 查询列表
from 表1 别名	[连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]

分类：

内连接： inner
外连接：
	左外连接 left [outer]
	右外连接  right [outer] 
	全外连接  full [outer]
交叉连接:  cross

内连接

分类：等值连接 非等值连接 自连接
特点：1.可以添加排序、分组、筛选
	 2.inner可以省略
	 3.筛选条件放在where后面，连接条件放在on 后面，提高分离性，便于阅读
	 4.inner jion 连接和sql92语法中的等值连接效果是一样的都是查询多表的交集。

外连接

应用场景：用于查询一个表中有，另一个表中没有记录
特点：1.外连接的查询结果为主表中的所有记录
	   如果从表中又和它匹配的，则显示匹配的值
	   如果从表中没有和它匹配的，则是显示null
2.外连接的查询结果：内连接结果+主表中有而从表中没有的记录
	左外连接：left join 左边的是主表
	右外连接: right join 右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的（mysql中不支持）

交叉连接

cross
笛卡尔乘积连接：
表1有a条数据，表2有b条数据    交叉连接后有ab条数据

USE myemployees;

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

#2.查询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_name,location_id , city
SELECT last_name , department_name , 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 e.commission_pct IS NOT NULL;

#4.选择city在Toronto工作的员工的last_name , job_id , depaetment_id , depaetment_name

SELECT last_name , job_id ,d.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 department_name,job_title

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

#案例1.查询员工名、部门名

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

#案例2.查询名字中包含e的员工名和公众名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE ‘%e%’;

#3。查询部门个数>3的城市名和部门个数，（添加分组和筛选）

SELECT city,COUNT() 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT()>3;

#4.查询哪个部门的员工个数>3的部门名考和员工个数，并排序

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

#5.查询员工名、部门名、工种名，并按部门名降序(添加排序)

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

#二、外连接