SQL的高级查询练习知识点下（day26）

1 学习目标

1. 重点掌握分组查询的语法

2. 重点掌握分页查询的语法

2 分页查询

2.1 语法

SELECT 字段|表达式,...
FROM 表
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 条件]
[ORDER BY 排序的字段]
LIMIT [起始的条目索引,]条目数;

2.2 特点

• 起始条目索引从0开始

• limit子句放在查询语句的最后

• 公式：

  SELECT * FROM 表 LIMIT (page-1) * size , size

  • 每页显示条目数:size
  • 要显示的页数:page

• 例子:

  • limit 10 前10行
  • limit 0,10 第1行开始的10行
  • limit 10,10 第11行开始的10行
  • limit 100,10 第101行开始的10行
  • 数据量大时(千万)，分页数据越往后越慢

2.3 例子

①查询employees表中前10行记录

SELECT employee_id,first_name,last_name
FROM employees LIMIT 10;

②查询employees表中第1行记录开始后的10行记录

SELECT employee_id,first_name,last_name
FROM employees LIMIT 0,10;

③查询employees表中第11行记录开始后的10行记录

SELECT employee_id,first_name,last_name
FROM employees LIMIT 10,10;

3 多表连接查询

3.1 含义

• 两张表，按指定条件连接，连接成一张表

3.2 传统模式连接

3.2.1 语法

SELECT 字段,...
FROM 表1,表2
WHERE 筛选条件;

3.2.2 例子

3.2.2.1 查询员工信息，并附带显示部门名

• 我们可以发现employees有部门id字段，所以是可以关联departments表的

SELECT e.employee_id, e.first_name, e.salary, d.department_id, d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

3.2.2.2 查询部门以及部门所在的城市

• departments表中还包含location_id字段，所以也可以关联区域表，所以查询locations表

SELECT d.department_id, d.department_name, l.city
FROM departments d,locations l
WHERE d.location_id = l.location_id;

3.2.2.3 查询部门以及部门经理

• 发现departments表中还包含manager_id字段，该字段是部门经理的id，部门经理的id其实也是取自于员工表，所以这两个字段也是可以关联的，所以查询manger表

SELECT d.department_id, d.department_name, d.manager_id, e.first_name mgr
FROM departments d,employees e
WHERE d.manager_id = e.employee_id;

3.2.2.4 查询员工和员工的主管名

• 此处需要使用自连接，就是将一张表看做是两张表

• 经过分析可知,员工的信息在employees表，而员工的主管是manger_id字段，该字段的值是取自于employees表的，所以此时可以将employees表即当成员工表，也可以当成主管表

SELECT e1.employee_id, e1.first_name, e1.salary, e2.first_name mgr
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id;

3.3 外连接

3.3.1 概念

• 内连接：只查询符合连接条件的数据
• 外连接：连接条件以外的数据，也要查询出来
  • 左外连接：左侧表中连接条件之外的数据
  • 右外连接：右侧表中连接条件之外的数据
  • 全外连接：双侧表中连接条件之外的数据

3.3.2 语法

• 内连接

SELECT ...
FROM a INNER JOIN b 
ON(连接条件)

• 左外连接

SELECT ...
FROM a LEFT [OUTER] JOIN b 
ON(连接条件)

• 右外连接

SELECT ...
FROM a RIGHT [OUTER] JOIN b 
ON(连接条件)

• 全外连接(mysql不支持)

SELECT ...
FROM a FULL [OUTER] JOIN b 
ON(连接条件)

3.3.3 例子

①查询107个员工并显示部门名

SELECT e.employee_id, e.first_name, e.salary, d.department_name
FROM employees e
LEFT JOIN departments d
ON (e.department_id = d.department_id);

②查询27个部门，有经理显示经理，没有经理显示NULL

SELECT d.department_id, d.department_name, e.first_name mgr
FROM departments d
LEFT JOIN employees e 
ON(d.manager_id=e.employee_id);

③查询107个员工，显示主管名

SELECT e1.employee_id, e1.first_name, e1.salary, e2.first_name mgr
FROM employees e1
LEFT JOIN employees e2 
ON (e1.manager_id = e2.employee_id);

④107个员工，显示部门名，城市

SELECT e.employee_id, e.first_name, e.salary, d.department_name, l.city
FROM employees e
LEFT JOIN departments d ON (e.department_id = d.department_id)
LEFT JOIN locations l ON (d.location_id = l.location_id);

4 总结

• 分页查询的公式是什么？

  SELECT * 
  FROM 表 
  LIMIT (page-1) * sizePerPage , sizePerPage

• 什么是多表连接查询？

  两张表，按指定条件连接，连接成一张表