5.查询操作

本章目标

SQL查询语法
聚合函数
连接查询
子查询
分页

本章内容

一、SQL查询语法

查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来。

1、基本语法

 SELECT
     [ALL | DISTINCT | DISTINCTROW ]
     select_expr [, select_expr] ...
     [into_option]
     [FROM table_references]
     [WHERE where_condition]
     [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
     [HAVING where_condition]
     [ORDER BY {col_name | expr | position}
       [ASC | DESC], ... [WITH ROLLUP]]
     [LIMIT {[offset,] row_count | row_count OFFSET offset}]

from子句：指定查询数据的表 where子句：查询数据的过滤条件 group by子句：对匹配where子句的查询结果进行分组 having子句：对分组后的结果进行条件限制 order by子句：对查询结果结果进行排序，后面跟desc降序或asc升序（默认）。　 limit子句：对查询的显示结果限制数目

2、 SELECT子句

使用以下几种方式指定字段列表：

字段列表	说明
*	字段列表为数据源的全部字段
表名.*	多表查询时，指定某个表的全部字段
字段列表	指定所需要显示的列

可以为字段列表中的字段名或表达式指定别名，中间使用as关键字分隔即可（as关键字可以省略）。多表查询时，同名字段前必须添加表名前缀，中间使用“.”分隔。

3、 where子句

数据库中存储着海量数据，数据库用户往往需要的是满足特定条件的记录，where子句可以实现结果集的过滤筛选。 where子句的语法格式：

 where  条件表达式

条件表达式:

关键字	解释
比较运算符	单一条件过滤
逻辑运算符	组合多个条件查询
BETWEEN AND	在两数之间
NOT BETWEEN AND	不在两数之间
IN <值表>	是否在特定的集合里（枚举）
NOT IN <值表>	与上面相反
LIKE	是否匹配于一个模式
IS NULL	为空的
IS NOT NULL	不为空的
Binary	区分大小写

3.1、比较运算符

常用的比较运算符有=（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（不等于）、!=（不等于）

 表达式1  比较运算符  表达式2

说明：“表达式1”和“表达式2”可以是一个字段名、常量、变量、函数甚至是子查询。比较运算符用于比较两个表达式的值，比较的结果是一个布尔值（true或者false）。

如果表达式的结果是数值，则按照数值的大小进行比较；如果表达式的结果是字符串，则需要参考字符序collation的设置进行比较

注意：如果某行比较列值为null时，在做不等于运算符时，该行不会满足的运算条件，不加入的结果集中

3.2、逻辑运算符

where子句中可以包含多个查询条件，使用逻辑运算符可以将多个查询条件组合起来，完成更为复杂的过滤筛选。

常用的逻辑运算符包括逻辑与（and）、逻辑或（or）以及逻辑非（not），其中逻辑非（not）为单目运算符。

逻辑非（not或!）逻辑非（not）为单目运算符
and逻辑运算符使用and逻辑运算符连接两个布尔表达式时，只有两个布尔表达式的值都为true时，整个逻辑表达式的结果才为true。语法格式如下:布尔表达式1 and 布尔表达式2
or逻辑运算符 or逻辑运算符连接两个布尔表达式时，只有两个表达式的值都为false时，整个逻辑表达式的结果才为false。语法格式如下: 布尔表达式1 or 布尔表达式2

3.3、IS NULL运算符

is NULL用于判断表达式的值是否为空值NULL（is not 恰恰相反），is NULL的语法格式如下。

 表达式 is [ not ] NULL

说明：不能将“score is NULL”写成“score = NULL;”，原因是NULL是一个不确定的数，不能使用“=”、“！=”等比较运算符与NULL进行比较。

使用ISNULL(column)也可以判断列是否为空,ISNULL(column)的执行效率更高；

 SELECT emp_name As 姓名, address  AS 地址
 FROM EMPLOYEE WHERE address IS NULL

3.4、BETWEEN AND

把某一字段中内容在特定范围内的记录查询出来

 SELECT emp_name,age FROM EMPLOYEE
 WHERE age BETWEEN 20 AND 30

3.5、IN

把某一字段中内容与所列出的查询内容列表匹配的记录查询出来

 SELECT emp_name AS 员工姓名,contry As 地址 FROM EMPLOYEE
 WHERE contry IN ('北京','广州','上海')

注意：如果某行比较列值为null时，在做not in运算符时，该行不会满足的运算条件，不加入的结果集中

尽量避免in，实在避免不了，也建议将集合元素个数控制在1000个以内；

3.6、模糊查询

like运算符用于判断一个字符串是否与给定的模式相匹配。模式是一种特殊的字符串，特殊之处在于不仅包含普通字符，还包含有通配符。在实际应用中，如果不能对字符串进行精确查询，此时可以使用like运算符与通配符实现模糊查询，like运算符的语法格式如下。字符串表达式 [ not ] like 模式

 SELECT emp_name AS 姓名 FROM EMPLOYEE
 WHERE emp_name LIKE '张%'

通配符	含义
%	指定位置有零个或多个字符
_	指定位置有1个

查询第个字为牛的名称

 SELECT * FROM EMPLOYEE WHERE emp_name  LIKE  '_牛%';

3.7、BINARY运算符

默认情况下，比较是不区分大小写的方式执行的。然而，以前我们注意到，可以添加BINARY关键字让MySQL执行区分大小写的比较。

 SELECT * FROM emp WHERE emp_name  LIKE BINARY '%tom%';

二、聚合函数

工资表中存储了所有员工的工资，我想知道：公司支出的总薪水、员工的平均工资、工资水平在2000块钱以上的总共有多少人?怎么办？

函数	介绍
SUM()	求总和，只适用于数值类型字段，如果是字符串类型不会报错会返回0，会自动过滤空值
AVG()	求平均值，只适用于数值类型字段，字符串类型不会报错会返回0，会自动过滤空值
MAX()	求最大值，适用于数值类型、字符串类型和日期时间类型字段
MIN()	求最小值，适用于数值类型、字符串类型和日期时间类型字段
COUNT()	用于计算查询结果集中的数据共有多少条

1、SUM

 SELECT SUM(sal_disburse) AS '2019年5月公司总薪水支出'
 FROM SALARY WHERE sal_ym='201905'

2、AVG

 SELECT AVG(sal_disburse) AS '2019年6月平均工资'
 FROM SALARY
 WHERE sal_ym='201906'

3、MAX、MIN

 SELECT AVG(sal_disburse) AS '平均工资',
     MAX (sal_disburse) AS '最高工资',
     MIN (sal_disburse) AS '最低工资'
 FROM SALARY WHERE sal_ym ='201911'

4、COUNT

 SELECT COUNT (*)  AS '2000元工资以上的员工数量'
 FROM SALARY
 WHERE sal_disburse>=2000
 AND sal_ym='201907'

count(*)是SQL92标准定义的统计行的语法，与数据库无关，与值无关；

count(*)会统计值为NULL的行，count(column)不会；

5、 ORDER BY

想查看2009年7月份公司工资的排名情况。怎么办？

 SELECT  emp_id,sal_disburse  FROM  SALARY
 WHERE sal_ym='200907'
 ORDER BY sal_disburse  ASC

ASC:升序排列

DESC:降序排列

6、 GROUP BY

6.1、单列分组

如果不是统计公司的总的人数，而是想统计每个部门的总人数或者每个部门的平均年龄。怎么办?

image-20230814175811269

SELECT dep_id AS 部门编号,count(emp_id)AS 部门人数,
    AVG(emp_age)AS 平均年龄
FROM EMPLOYEE
GROUP BY dep_id

6.2、多列分组

如果想统计每个部门不同学历的员工有多少人，该怎么办？

image-20230814180136098

SELECT dep_id AS 部门编号,degree AS 学历,
    count(*) AS 人数
FROM EMPLOYEE
GROUP BY dep_id,degree
ORDER BY dep_id

7、 HAVING

在以上查询的基础上，如果只想看非本科学历的并且人数大于1人的部门，怎么办？

SELECT dep_id AS 部门编号,degree AS 学历,
    count(id) AS 人数
FROM EMPLOYEE
WHERE degree!=‘本科’
GROUP BY dep_id,degree
HAVING count(id)>1
ORDER BY dep_id

WHERE和HAVING的区别？

WHERE子句从数据源中去掉不符合其搜索条件的数据

GROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值

HAVING子句去掉不符合其组搜索条件的各组数据行

8、完整的sql语句

select dept_id, count(*) from employee
where dept_id is not null -- 对所有数据进行筛选，不能使用聚合函数
GROUP BY dept_id   -- 对where筛选后的数据进行分组
having count(*)>2  -- 对分组后的数据进行筛选
order by  dept_id desc; -- 对数据进行排序

三、连接查询

学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据？

语法：

from 表名1  [ 连接类型 ]  join  表名2  on  表1和表2之间的连接条件

1、 SQL连接分类：

mysql暂不支持full join

inner连接（内连接）
outer连接（外连接）
- left（左外连接，简称为左连接）
- right（右外连接，简称为右连接）
cross连接（交叉连接）

2、内连接 Inner join

关键字 ：inner join on

SELECT t1.emp_name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.emp_name = t2.emp_name;

组合两个表中的记录，返回关联字段相符的记录，即返回两个表的交集部分

表的别名前加as，并以t1, t2, t3, …依次命名；

3、左连接查询 left join

关键字 ：left join on / left outer join on

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

#多张表连接
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.id = table3.id;

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

4、右连接 right join

部门表中不是所有部门都有人，想取出每个部门的信息，没有人的为null

关键字：right join on / right outer join on

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

5、交叉连接 CROSS JOIN

在 MySQL 中JOIN，CROSS JOIN、和INNER JOIN是句法是等价（它们可以相互替换）。在标准 SQL 中，它们不是等价的。

select * from employee e CROSS JOIN dept d on e.dept_id=d.id;

等价于

select * from employee e INNER JOIN dept d on e.dept_id=d.id;

等价于

select * from employee e,dept d where e.dept_id=d.id;

四、子查询

官网：https://dev.mysql.com/doc/refman/8.0/en/subqueries.html

1、什么是子查询

子查询：一个查询语句嵌套在另一个查询语句内部

在 MySQL 中，除了在 ORDER BY 列表中以外，在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何可以使用表达式的地方都可以使用子查询来替代。

子查询特征

子查询的 SELECT 查询总是使用圆括号括起来。
子查询可以嵌套在外部 SELECT、INSERT、UPDATE 或 DELETE 语句的 WHERE 或 HAVING 子句内，或者其它子查询中。
根据可用内存和查询中其它表达式的复杂程度不同，嵌套限制也有所不同，嵌套到 32 层。

2、子查询可出现的位置

位置	类型	说明
select后面：	仅仅支持标量子查询	作为一个字段值
from后面：	支持表子查询	作为一个临时表
where或having后面：	支持标量子查询（单行）、列子查询（多行）、行子查询	作为过滤条件
exists后面（也被称为’相关子查询’）	支持表子查询

标量子查询：就是指子查询的结果是“单个值”（一行一列）的查询

3、子查询的分类

从内查询返回结果的条目数

单行子查询	多行子查询
子查询结果只有一个数据	子查询数据返回多个

4、单行子查询

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

示例：取出所有大于平均工资的所有员工信息

普通方式：

select min(salary) from employee ;
select * from employee WHERE salary>6000

子查询方式

select * from employee WHERE salary>(select avg(salary) from employee);
-- 其它需求类同
select * from employee WHERE salary>(select min(salary) from employee);
select * from employee WHERE salary>(select salary from employee where emp_name='张三');

5、多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

5.1、IN

取得等于每个部门最低工资的员工信息

select * from employee where salary in (select min(salary) from employee GROUP BY dept_id)

5.2、ANY

取得大于任意一个部门最低工资的员工信息

select * from employee where salary > ANY (select min(salary) from employee GROUP BY dept_id)

5.3、ALL

取得大于所有部门最低工资的员工信息

select * from employee where salary > ALL (select min(salary) from employee GROUP BY dept_id)

6、相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询 .相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

7、EXISTS子查询

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回 TRUE 或 FALSE 值。

使用 EXISTS 引入的子查询在以下几方面与其它子查询略有不同，EXISTS 关键字前面没有列名、常量或其它表达式。

示例: 取得部门中有员工的部门信息

select * from dept d where EXISTS (select * from employee e where d.id=e.dept_id );
-- 没有员工的部门信息
select * from dept d where NOT EXISTS (select * from employee e where d.id=e.dept_id );

-- 连接子查询， – 首先取得部门表中第一条数据的id， – 然后执行子查询中内容，如果子查询能够有满足条件的记录则返回true – 返回true,则把该部门信息放到结果集中 – 依次读取后面的数据，每条数据都要重新走一遍子查询

8、每个部门最低工资的员工信息

使用in时存在一个问题，当两个部门中有多个相同的工资值时会产生错误的结果,这时我们可以使用关联子查询来实现

select * from employee e ,(select dept_id,min(salary) as minsalry from employee where dept_id is not null group by dept_id) ee
where e.dept_id=ee.dept_id and e.salary=ee.minsalry

五、 MySQL分页

MySQL数据库实现分页比较简单，提供了LIMIT函数。一般只需要直接写到sql语句后面就行了。

1、分页实现

select字段列表
from数据源
limit [start,]length;

start表示从第几行记录开始检索，length表示检索多少行记录。

表中第一行记录的start值为0。

示例：

select * from table WHERE … LIMIT 10; #返回前10行

该SQL语句等效于

select * from table WHERE … LIMIT 0,10; #返回前10行
select * from table WHERE … LIMIT 10,10; #返回第10-20行数据

2、分页优化（扩展）

问题：

对于limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。limit n性能是没问题的，因为只扫描n行。

优化解决办法：

SELECT * FROM message WHERE id>=1000 ORDER BY id ASC LIMIT 20;//当前页
SELECT * FROM message WHERE id>1020 ORDER BY id ASC LIMIT 20;//下一页
SELECT * FROM message WHERE id<1000 ORDER BY id DESC LIMIT 20;//上一页

通过id来减少扫描

思维导图

posted @ 2025-04-10 11:45 icui4cu 阅读(15) 评论(0) 收藏举报

刷新页面返回顶部

icui4cu