mysql排序分页多表查询_mysql多表查询分页

排序分页

# 默认是升序(asc)
select employee_id,last_name,salary from employees order by salary;
select employee_id,last_name,salary from employees order by salary desc;
# 可以使用列的别名进行排序
select employee_id,salary,salary * 12 as "annual_sal" from employees order by annual_sal;
# 列的别名只能在order by中,不能在where中使用
# 如下会报错
select employee_id,salary,salary * 12 as "annual_sal" from employees where annual_sal > 8000;
# where需要写在from后,order by前
# 二级排序
select employee_id,salary from employees order by department_id desc,salary asc;
1
2
3
4
5
6
7
8
9
10
11

# sql执行顺序
from(如果有多表查询,先cross join,然后on过滤掉不符合的行,并且配合上left/right join)
where
group by
having
select
order by
1
2
3
4
5
6
7

# 分页
# 返回[0-20)条记录
select employee_id,last_name from employees limit 0,20;
# 返回[20-40)条记录
select employee_id,last_name from employees limit 20,20;
1
2
3
4
5

多表查询

# 查到的结果是employees x departments笛卡尔积,没有实际意义
select employee_id,department_name
from employees,departments;
# 加上连接筛选条件
select employee_id,department_name
from employees,departments
where employees.department_id = departments.department_id;
# SQL99语法
# inner可以省略
select employee_id,department_name
from employees
inner join departments on employees.department_id = departments.department_id;
# 连接多个表
select employee_id,department_name,city
from employees
inner join departments on employees.department_id = departments.department_id
inner join locations on departments.location_id = locations.location_id;
# 建议每个字段都指明所在的表
select employees.employee_id,departments.department_name
from employees,departments
where employees.department_id = departments.department_id;
# 可以给表起别名
select t1.employee_id,t2.department_name
from employees t1,departments t2
where t1.department_id = t2.department_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 自连接
# 员工id,姓名及其管理者的id和姓名
select emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
from employees emp,employees mgr
where emp.manager_id = mgr.employee_id;
1
2
3
4
5

# 上面的都是内连接(也称为自然连接,只有两个表相匹配的行才在结果集中出现)
# 外连接
# 左外连接(左表全部列出,如果左表有的行在右表中没有与之相匹配的,对应的右表中的字段值为null)
select last_name,department_name
from employees
left join departments on employees.department_id = departments.department_id;
# 右外连接(右表全部列出,...)
select last_name,department_name
from employees
right join departments on employees.department_id = departments.department_id;
# 满外连接
select employee_id,department_name
from employees
left join departments on employees.department_id = departments.department_id

union all

select employee_id,department_name
from employees
right join departments on employees.department_id = departments.department_id
where employees.department_id is null;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 返回两个查询的结果集的并集,并去除重复记录
union
# 对于重复部分, 不去重
union all
1
2
3
4

外连接的执行过程
sql语句的执行顺序以及流程