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'
  1. count(*)是SQL92标准定义的统计行的语法,与数据库无关,与值无关;

  2. 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的区别?

  1. WHERE子句从数据源中去掉不符合其搜索条件的数据
  2. GROUP BY子句搜集数据行到各个组中,统计函数为各个组计算统计值
  3. 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 中JOINCROSS 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来减少扫描

思维导图

image

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