数据库第三天

二、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;

上一篇:【2019年8月】OCP 071认证考试最新版本的考试原题-第32题


下一篇:微人事项目-mybatis-持久层