二、sql99语法
语法：
select 查询列表
from 表1 别名 连接类型
join 表2 别名 on 连接条件
【where 筛选条件】
【group by 筛选条件】
【having 筛选条件】
【order by 排序条件】

内连接：inner
外连接
左外：left【outer】
右外：right【outer】
全外：full【outer】
交叉连接：cross

内连接:

语法：

select 查询列表
from 表1
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

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

1.等值连接
案例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：查询哪个部门的员工个数大于三的部门名和员工个数，并按个数降序(添加排序)
查询每个部门的员工个数
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
INNER 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;

 

2.非等值连接

查询员工的工资级别

SELECT salay,grade_level
FROM employees e
JOIN job_grades g
ON e.salay BETWEEN g.lowest_sal AND g.highest_sal;

查询工资级别的个数>2的个数 并且按照工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.salay BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3.自连接

查询员工名 上级名
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.mannger_id=m.mannger_id;

查询姓名中包含字符k员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.mannger_id=m.mannger_id
WHERE e.last_name LIKE '%K%';

二、外连接

应用场景：用于查询一个表中有，另一个表中没有的记录

特点：
1.外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的 则显示匹配的值
如果从表中没有和它匹配的 则显示null
外连接的查询结果=内连接的查询结果+主表中有而从表中没有的记录

2.左外连接，left join左边的是主表
右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序 可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1没有的

引入：查询男朋友不在男神表的女神名

SELECT * FROM beauty;
SELECT * FROM boys;

左外连接实现
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id =bo.id
WHERE bo.id IS NULL;

右外连接实现
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfriend_id =bo.id
WHERE bo.id IS NULL;

 

UPDATE boys SET usercp = NULL WHERE id=3;

 

 

案例1：查询哪个部门没有员工
左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

全外
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;

交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92和sql99 pk
功能：sql99支持较多
可读性：sql99实现连接条件和筛选条件分离，可读性较高

进阶7：子查询

含义：出现在其他语句中国的select语句 称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询

from后面：
支持表子查询
where和having后面：
标量子查询（单行）
列子查询（多行）

行子查询（较少）
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一行多列）
行子查询（结果集有多行多列）
表子查询（结果集一般为多行多列）

一、where或having后面
1.标量子查询（单行子查询）
2.列子查询（多行子查询）

3.行子查询（多行多列）

特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符进行使用
in、any/some、all

4.子查询的执行都是优先于主查询执行的，主查询的条件用到了子查询的结果

1.标量子查询

案例1：谁的工资比Abel的高？

1.查询Abel的工资
SELECT salay
FROM employees
WHERE last_name='Abel';

2.查询员工的信息，满足salay大于1的结果
SELECT *
FROM employees
WHERE salay(
SELECT salay
FROM employees
WHERE last_name='Abel'
);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名 job_id和工资

1.先查141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id=141;

2.查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id=143;

3.查询员工的姓名，job_id和工资，要求job_id=1 并且salary>2

SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);

案例3：返回公司工资最少的员工的last_name,job_id和salary

1.先查公司的最低工资

SELECT MIN(salary)
FROM employees

2.查询员工的last_name,job_id和salary 要求工资=1

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

案例4：查询最低工资大于50号部门最低工资部门id和其最低工资

1.先查50号部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=50

2.查询每个部门的最低工资

SELECT MIN(salary)
FROM employees
GROUP BY department_id

3.筛选2，满足min(salary)>1的结果
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);

#非法使用标量子查询
1.标量子查询只能是单行子查询

2.子查询的结果必须是一行一列

2.列子查询(多行子查询 单列多行)

案例1：返回location_id是1400或1700的部门中的所有员工姓名

1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

2.查询员工姓名，要求部门号是1列表中的某一个

SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);

案例2：返回其他部门中比job_id为IT_PROG部门任一工资低的员工的员工号，姓名，job_id以及salary

1.查询job_id为IT_PROG部门任一工资

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

2.查询工号，姓名、job_id以及salary，salary<any(1)的任意一个

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

案例3：返回其他部门中比job_id为IT_PROG部门所有工资低的员工的员工号，姓名，job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

#或

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

IN 可以用 =ANY 来提换
NOT IN 用 <>ALL 来替换

3、行子查询（结果集一行多列或为多行多列）

案列：查询员工编号最小且工资最高的员工信息

SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);

1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;

2.查询最高的工资
SELECT MAX(salary)
FROM employees;

3.查询员工信息

SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
) AND salary=(
SELECT MAX(salary)
FROM employees;
);

二、select后面：
案例：查询每个部门的员工个数

SELECT d.*,(
SELECT COUNT(*)
FROM employees
WHERE e.department_id=d.department_id
) 个数
FROM departments d;

案例2：查询员工号=102的部门名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;

三、from后面：

案例：查询每个部门的平均工资的工资等级
1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

SELECT * FROM job_grades;

2.连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

四、放在exists后（相关子查询）

语法：
exists(完整的查询语句)
结果：
1或0

SELECT EXISTS(SELECT employee_id
FROM employees
WHERE salary=30000
);
案例1：查询有员工的部门名
in
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
);

exists
SELECT department_name
FROM departments
WHERE EXISTS(
SELECT *
FROM employees
WHERE d.department_id=e.employee_id
);

案例2：查询没有女朋友的男神信息

in
SELECT bo.*
FROM boys
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
);

exists
SELECT bo.*
FROM boys
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);

进阶8：分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后筛选
order by 排序的字段】
limit offset,size;

offset 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
1.limit语句放在查询语句的最后
2.公式
要显示的页数page，每页的条目数size

select 查询列表
from 表
limit (page-1)*size,size;
size=10;
page
1 0
2 10
3 20

案例1：查询前5条员工信息
SELECT * FROM employees LIMIT 0,5
SELECT * FROM employees LIMIT 5

案例2：查询第11条到第25条
SELECT * FROM employees LIMIT 10,15;

案例3：有奖金的员工信息，并且工资较高的前十名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;