本文主要是介绍MYSQL DAY02 排序、分页、多表查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
排序数据 :
降序排列:
SELECT
last_name, job_id, department_id, hire_date
FROM
employees
ORDER BY
hire_date
DESC
;
按annsal升序排列:
SELECT
employee_id, last_name, salary*
12
annsal
FROM
employees
ORDER BY
annsal;
多列排序:
SELECT
last_name, department_id, salary
FROM
employees
ORDER BY
department_id, salary
DESC
;
先按department_id升序排列,department_id相同的,按salary降序排列。
2. 分页
![](/images/baidian.png)
--
前
10
条记录:
SELECT
*
FROM
表名
LIMIT
0
,
10
;
或者
SELECT
*
FROM
表名
LIMIT
10
;
--
第
11
至
20
条记录:
SELECT
*
FROM
表名
LIMIT
10
,
10
;
--
第
21
至
30
条记录:
SELECT
*
FROM
表名
LIMIT
20
,
10
;
注*
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”
,意思是获取从第
5
条记录开始后面的
3
条记录,和
“LIMIT 4,3;”返回的结果相同。
拓展 :
![](/images/baidian.png)
子查询:
SELECT
rownum, last_name,salary
FROM
(
SELECT
last_name,salary
FROM
employees
ORDER BY
salary
DESC
)
WHERE
rownum <
10
;
练习:
SELECT `last_name`,`department_id`,`salary` *12 annual
FROM `employees`
ORDER BY annual DESC , `last_name` ;
SELECT `last_name`,`salary`
FROM `employees`
WHERE `salary` NOT BETWEEN 8000 AND 17000
ORDER BY `salary` DESC
LIMIT 20,20;
SELECT `last_name`,`employee_id`,`department_id`,`email`
FROM `employees`
#where `email` like '%e%'
WHERE `email` REGEXP '[e]'
ORDER BY LENGTH(`email`) DESC,`department_id`;
多表查询:
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个 关联字段可能建立了
外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“
部门编号
”
进 行关联。
#
案例:查询员工的姓名及其部门名称
SELECT
last_name, department_name
FROM
employees, departments;
此时产生笛卡尔乘积
SQL92
中,笛卡尔积也称为
交叉连接
,英文是
CROSS JOIN
。在
SQL99
中也是使用
CROSS JOIN
表示交 叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。在MySQL
中如下情况会出现笛卡尔积:
#
查询员工姓名和所在部门名称
SELECT
last_name,department_name
FROM
employees,departments;
SELECT
last_name,department_name
FROM
employees
CROSS JOIN
departments;
SELECT
last_name,department_name
FROM
employees
INNER JOIN
departments;
SELECT
last_name,department_name
FROM
employees
JOIN
departments;
笛卡尔积的错误会在下面条件下产生
:
1.省略多个表的连接条件(或关联条件)
2.连接条件(或关联条件)无效
3.所有表中的所有行互相连接
正确写法:
#
案例:查询员工的姓名及其部门名称
SELECT
last_name, department_name
FROM
employees, departments
WHERE
employees
.department_id
= departments
.department_id
;
多表查询分类
1
:等值连接 、
非等值连接
等值连接
一张表的
主键的值,和另外一张表
外键的值相等,筛选出这个条件下的数据。
SELECT
employees
.employee_id
, employees
.last_name
,
employees
.department_id
, departments
.department_id
,
departments
.location_id
FROM
employees, departments
WHERE
employees
.department_id
= departments
.department_id
;
1
:多个连接条件与
AND
操作符
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND `departments`.`location_id` = 1700;
2
:区分重复的列名
多个表中有相同列时,必须在列名之前加上表名前缀。
在不同表中具有相同列名的列可以用
表名
加以区分。
SELECT
employees
.last_name
, departments
.department_name
,employees
.department_id
FROM
employees, departments
WHERE
employees
.department_id
= departments
.department_id
;
3
:表的别名
使用别名可以简化查询。
列名前使用表名前缀可以提高查询效率。
SELECT
e
.employee_id
, e
.last_name
, e
.department_id
,
d
.department_id
, d
.location_id
FROM
employees e , departments d
WHERE
e
.department_id
= d
.department_id
;
需要注意的是,如果我们使用了表的别名,在查询字段中、过滤条件中就只能使用别名进行代替,
不能使用原有的表名,否则就会报错。
4
:连接多个表
连接
n
个表
,
至少需要
n-1
个连接条件。
比如,连接三个表,至少需要两个连接条件。
练习:查询出公司员工的
last_name,department_name, city
SELECT e.`last_name`,d.`department_name`,l.`city`
FROM `employees` e,`departments` d,`locations` l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
非等值连接
查询员工的工资级别:
SELECT
e
.last_name
, e
.salary
, j
.grade_level
FROM
employees e, job_grades j
WHERE
e
.salary
BETWEEN
j
.lowest_sal
AND
j
.highest_sal
;
筛选出
e
.salary在
j
.lowest_sal
AND
j
.highest_sal
值之间的数据。
2
:自连接 、
非自连接
题目:查询
employees
表,返回
“Xxx works for Xxx”
SELECT
CONCAT(worker
.last_name
,
' works for '
, manager
.last_name
)
FROM
employees worker, employees manager
WHERE
worker
.manager_id
= manager
.employee_id
;
查询出last_name为 ‘Chen’ 的员工的 manager 的信息。
SELECT `last_name`,`manager_id`
FROM `employees`
WHERE `last_name` = 'Chen';
3
:内连接 、
外连接
除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。
*内连接
:
合并具有同一列的两个以上的表的行
,
结果集中不包含一个表与另一个表不匹配的行
*外连接
:
两个表在连接过程中除了返回满足连接条件的行以外
还返回左(或右)表中不满足条件的
行 ,这种连接称为左(或右) 外连接
。没有匹配的行时
,
结果表中相应的列为空
(NULL)
。
*如果是左外连接,则连接条件中左边的表也称为
主表
,右边的表称为
从表
。
*如果是右外连接,则连接条件中右边的表也称为
主表
,左边的表称为
从表
。
SQL92
:使用
(+)
创建连接
#
左外连接
SELECT
last_name,department_name
FROM
employees ,departments
WHERE
employees
.department_id
= departments
.department_id
(+);
#
右外连接
SELECT
last_name,department_name
FROM
employees ,departments
WHERE
employees
.department_id
(+) = departments
.department_id
;
在
SQL92
中,只有左外连接和右外连接,没有满(或全)外连接。
SQL99语法实现多表查询
使用
JOIN...ON
子句创建连接的语法结构:
SELECT
table1
.column
, table2
.column
,table3
.column
FROM
table1
JOIN
table2
ON
table1
和
table2
的连接条件
JOIN
table3
ON
table2
和
table3
的连接条件
SQL99
采用的这种嵌套结构非常清爽、层次性更强、可读性更强,即使再多的表进行连接也都清晰 可见。如果你采用 SQL92
,可读性就会大打折扣。
*可以使用
ON
子句指定额外的连接条件
。
*这个连接条件是与其它条件分开的。
*ON
子句使语句具有更高的易读性
。
*关键字
JOIN
、
INNER JOIN
、
CROSS JOIN
的含义是一样的,都表示内连接
内连接
(INNER JOIN)
的实现
SELECT
字段列表
FROM
A
表
INNER JOIN
B
表
ON
关联条件
WHERE
等其他子句
;
SELECT
e
.employee_id
, e
.last_name
, e
.department_id
,
d
.department_id
, d
.location_id
FROM
employees e
JOIN
departments d
ON
(e
.department_id
= d
.department_id
);
SELECT
employee_id, city, department_name
FROM
employees e
JOIN
departments d
ON
d
.department_id
= e
.department_id
JOIN
locations l
ON
d
.location_id
= l
.location_id
;
外连接(OUTER JOIN)的实现
左外连接
(LEFT OUTER JOIN)
SELECT
e
.last_name
, e
.department_id
, d
.department_name
FROM
employees e
LEFT OUTER JOIN
departments d
ON
(e
.department_id
= d
.department_id
) ;
此时会查询到departmentid和departmentname为空的数据。
右外连接
(RIGHT OUTER JOIN)
SELECT
e
.last_name
, e
.department_id
, d
.department_name
FROM
employees e
RIGHT OUTER JOIN
departments d
ON
(e
.department_id
= d
.department_id
) ;
此时会查询到lastname和departmentid为空的数据。
满外连接
(FULL OUTER JOIN)
*满外连接的结果
=
左右表匹配的数据
+
左表没有匹配到的数据
+
右表没有匹配到的数据。
*SQL99
是支持满外连接的。使用
FULL JOIN
或
FULL OUTER JOIN
来实现。
*需要注意的是,
MySQL
不支持
FULL JOIN
,但是可以用
LEFT JOIN
UNION
RIGHT join
代替。
UNION的使用
合并查询结果
利用
UNION
关键字,可以给出多条
SELECT
语句,并将它们的结果组合成单个结果集。合并 时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT
语句之间使用
UNION
或
UNION ALL关键字分隔。
语法格式:
SELECT column
,...
FROM
table1
UNION
[
ALL
]
SELECT column
,...
FROM
table2
注意:执行
UNION ALL
语句时所需要的资源比
UNION
语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL
语句,以提高数据查询的效率。
举例:查询部门编号
>90
或邮箱包含
a
的员工信息
SELECT
*
FROM
employees
WHERE
email
LIKE
'%a%'
UNION
SELECT
*
FROM
employees
WHERE
department_id>
90
;
等于
SELECT
*
FROM
employees
WHERE
email
LIKE
'%a%'
OR
department_id>
90
;
举例:查询中国用户中男性的信息以及美国用户中年男性的用户信息
SELECT
id,cname
FROM
t_chinamale
WHERE
csex=
'
男
'
UNION ALL
SELECT
id,tname
FROM
t_usmale
WHERE
tGender=
'male'
;
7种SQL JOINS的实现
![](/images/baidian.png)
#
中图:内连接
A
∩
B
SELECT
employee_id,last_name,department_name
FROM
employees e
JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
;
#
左上图:左外连接
SELECT
employee_id,last_name,department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
;
#
右上图:右外连接
SELECT
employee_id,last_name,department_name
FROM
employees e
RIGHT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
;
#
左中图:
A - A
∩
B
SELECT
employee_id,last_name,department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
WHERE
d.
`department_id`
IS
NULL
#
右中图:
B-A
∩
B
SELECT
employee_id,last_name,department_name
FROM
employees e
RIGHT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
WHERE
e.
`department_id`
IS
NULL
#
左下图:满外连接
#
左中图
+
右上图
A
∪
B
SELECT
employee_id,last_name,department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
WHERE
d.
`department_id`
IS
NULL
UNION ALL
#
没有去重操作,效率高
SELECT
employee_id,last_name,department_name
FROM
employees e
RIGHT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
;
#
右下图
#
左中图
+
右中图
A
∪
B- A
∩
B
或者
(A - A
∩
B)
∪ (
B - A
∩
B
)
SELECT
employee_id,last_name,department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
WHERE
d.
`department_id`
IS
NULL
UNION ALL
SELECT
employee_id,last_name,department_name
FROM
employees e
RIGHT JOIN
departments d
ON
e.
`department_id`
= d.
`department_id`
WHERE
e.
`department_id`
IS
NULL
*左中图
#
实现
A - A
∩
B
select
字段列表
from
A
表
left join
B
表
on
关联条件
where
从表关联字段
is
null
and
等其他子句
;
*右中图
#
实现
B - A
∩
B
select
字段列表
from
A
表
right join
B
表
on
关联条件
where
从表关联字段
is
null
and
等其他子句
;
*左下图
#
实现查询结果是
A
∪
B
#
用左外的
A
,
union
右外的
B
select
字段列表
from
A
表
left join
B
表
on
关联条件
where
等其他子句
union
select
字段列表
from
A
表
right join
B
表
on
关联条件
where
等其他子句
;
*右下图
#
实现
A
∪
B - A
∩
B
或
(A - A
∩
B)
∪ (
B - A
∩
B
)
#
使用左外的
(A - A
∩
B) union
右外的(
B - A
∩
B
)
select
字段列表
from
A
表
left join
B
表
on
关联条件
where
从表关联字段
is
null
and
等其他子句
union
select
字段列表
from
A
表
right join
B
表
on
关联条件
where
从表关联字段
is
null
and
等其他子句
SQL99语法新特性
自然连接
SQL99
在
SQL92
的基础上提供了一些特殊语法,比如
NATURAL JOIN
用来表示自然连接。我们可以把自然连接理解为 SQL92
中的等值连接。它会帮你自动查询两张连接表中
所有相同的字段
,然后进行
等值连接
。
![](/images/baidian.png)
USING
连接
章节小结
表连接的约束条件可以有三种方式:
WHERE, ON, USING
*WHERE
:适用于所有关联查询。
*ON
:只能和
JOIN
一起使用,只能写关联条件。虽然关联条件可以并到
WHERE
中和其他条件一起写,但分开写可读性更好。
*USING:
只能和
JOIN
一起使用,而且要求
两个
关联字段在关联表中名称一致,而且只能表示关联字段值相等。
#
关联条件
#
把关联条件写在
where
后面
SELECT
last_name,department_name
FROM
employees,departments
WHERE
employees
.department_id
= departments
.department_id
;
#
把关联条件写在
on
后面,只能和
JOIN
一起使用
SELECT
last_name,department_name
FROM
employees
INNER JOIN
departments
ON
employees
.department_id
= departments
.department_id
;
SELECT
last_name,department_name
FROM
employees
CROSS JOIN
departments
ON
employees
.department_id
= departments
.department_id
;
SELECT
last_name,department_name
FROM
employees
JOIN
departments
ON
employees
.department_id
= departments
.department_id
;
#
把关联字段写在
using()
中,只能和
JOIN
一起使用
#
而且两个表中的关联字段必须名称相同,而且只能表示
=
#
查询员工姓名与基本工资
SELECT
last_name,job_title
FROM
employees
INNER JOIN
jobs
USING
(job_id);
#n
张表关联,需要
n-1
个关联条件
#
查询员工姓名,基本工资,部门名称
SELECT
last_name,job_title,department_name
FROM
employees,departments,jobs
WHERE
employees
.department_id
= departments
.department_id
AND
employees
.job_id
= jobs
.job_id
;
SELECT
last_name,job_title,department_name
FROM
employees
INNER JOIN
departments
INNER JOIN
jobs
ON
employees
.department_id
= departments
.department_id
AND
employees
.job_id
= jobs
.job_id
;
注意:
我们要
控制连接表的数量
。多表连接就相当于嵌套
for
循环一样,非常消耗资源,会让
SQL
查询性能下降得很严重,因此不要连接不必要的表。在许多 DBMS
中,也都会有最大连接表的限制。
这篇关于MYSQL DAY02 排序、分页、多表查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!