数据处理之查询

精选原创

Anabelle 2024-05-27 10:23:00 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者Anabelle的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、基本的SELECT语句

预先创建表departments：

CREATE TABLE departments (
    DEPARTMENT_ID INT PRIMARY KEY,
    DEPARTMENT_NAME VARCHAR(255),
    MANAGER_ID INT,
    LOCATION_ID INT
);

INSERT INTO departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES 
(10, 'Administration', 200, 1700),
(20, 'Marketing', 201, 1800),
(50, 'Shipping', 124, 1500),
(60, 'IT', 103, 1400),
(80, 'Sales', 149, 2500),
(90, 'Executive', 100, 1700),
(110, 'Accounting', 205, 1700),
(190, 'Contracting', NULL, 1700);

1.1.基本查询语法说明

数据处理之查询_数据

语法说明：

SELECT 标识选择哪些列。
FROM 标识从哪个表中选择。

1.2.选择全部列

数据处理之查询_数据_02

数据处理之查询_字段_03

1.3.选择特定列

数据处理之查询_ci_04

数据处理之查询_字段_05

1.4.SQL编写需要注意的地方

注意点有：

SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。

1.5.列的别名

列的别名:

重命名一个列。
便于计算。
紧跟列名，也可以在列名和别名之间加入关键字 ‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

使用别名语法：

SELECT last_name AS name, commission_pct comm FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;

预览如下：

1.6.字符串

字符串可以是SELECT 列表中的一个字符,数字,日期。
日期和字符只能在单引号中出现。
每当返回一行时，字符串被输出一次。

如下

数据处理之查询_字段_06

1.7.显示表结构

使用DESCRIBE 命令，表示表结构

数据处理之查询_ci_07

数据处理之查询_ci_08

二、过滤和排序数据

2.1.过滤

使用WHERE 子句，将不满足条件的行过滤掉

数据处理之查询_字段_09

注意：WHERE 子句紧随 FROM 子句。

例如：返回在90号部门工作的所有员工的信息

SELECT * FROM employees WHERE department_id = 90

效果如下：

数据处理之查询_字段_10

2.2.比较运算

赋值使用:= 符号，其余的如下：

数据处理之查询_数据_11

比较运算：查询薪水小于等于3000的员工信息

SELECT last_name, salary FROM employees WHERE salary <= 3000;

如下图：

数据处理之查询_数据_12

2.3.其它比较运算

比较运算如下：

数据处理之查询_ci_13

⑴.BETWEEN

使用 BETWEEN 运算来显示在一个区间内的值

数据处理之查询_ci_14

如下图：

数据处理之查询_数据_15

⑵.IN

使用 IN运算显示列表中的值。

查询部门经理ID是100、101、114的员工信息

SELECT employee_id, last_name, salary, manager_id FROM employees WHERE  manager_id IN (100, 101, 114);

⑶.LIKE

使用 LIKE 运算选择类似的值，选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符，不会区分大小写)。
_ 代表一个字符。

①.查询员工名字是字母a开头的员工信息

SELECT first_name FROM employees WHERE first_name LIKE 'a%';

如下图：

数据处理之查询_字段_16

②.‘%’和‘-’可以同时使用。

查询姓氏last_name中在第二个字符为字母o的员工姓氏

SELECT last_name FROM employees WHERE last_name LIKE '_o%';

如下图：

数据处理之查询_字段_17

⑷.NULL

使用 IS (NOT) NULL 判断空值。

案例：查询部门经理ID为空的员工姓氏和部门经理ID

SELECT last_name,manager_id FROM employees WHERE manager_id IS NULL;

如下：

数据处理之查询_字段_18

2.4.逻辑运算

逻辑运算符说明：

数据处理之查询_ci_19

⑴.AND

AND 要求并的关系为真。

例如：求薪资大于等于10000，同时职位工作id中包含MAN的员工信息

SELECT * FROM employees WHERE salary>=10000 AND job_id LIKE '%MAN%';

预览效果如下：

数据处理之查询_字段_20

⑵.OR

OR 要求或关系为真。

例如：求薪资大于等于10000，或者职位工作id中包含MAN的员工信息

SELECT * FROM employees WHERE salary>=10000 OR job_id LIKE '%MAN%';

⑶.NOT

not取反，例如：查询职位工作id不是'ST_MAN','PU_MAN','SA_REP'的员工信息

SELECT * FROM employees WHERE job_id NOT IN ('ST_MAN','PU_MAN','SA_REP')

2.5.排序

⑴.ORDER BY子句

使用ORDER BY 子句排序

ASC（ascend）: 升序(默认就是升序)
DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾。

例如：通过薪资将员工信息进行升序排列

SELECT last_name, job_id, department_id, salary FROM employees ORDER BY salary

如下：

数据处理之查询_字段_21

⑵.降序排序

SELECT last_name, job_id, department_id, salary FROM employees ORDER BY salary DESC

数据处理之查询_字段_22

⑶.按别名排序

员工信息乘以12，求收入总和，然后根据收入总和降序排列

SELECT employee_id, last_name, salary*12 收入总和 FROM employees ORDER BY 收入总和 DESC

如下图：

数据处理之查询_字段_23

⑷.多个列排序

ORDER BY 同时按多个列排序，通过用逗号分隔的方式列出多个列，可以实现多列排序：

SELECT * FROM your_table ORDER BY column1 ASC, column2 DESC;

这将首先按照 column1 升序排序，然后再按照 column2 降序排序。

案例：先根据部门ID排序，然后再根据薪资降序排列

SELECT last_name,department_id,salary FROM employees ORDER BY department_id,salary DESC;

数据处理之查询_ci_24

三、分组函数

3.1.什么是分组函数？

在MySQL中，分组函数（也称聚合函数）是用于对一组数据进行计算并返回单个值的函数。这些函数通常用于与GROUP BY子句一起使用，以便对数据进行分组并对每个组应用函数进行计算。常见的MySQL分组函数包括：

COUNT()：用于计算指定列的行数，可以用于计算某个列的非NULL值的数量。
SUM()：用于计算指定列的总和，通常用于对数值型列进行求和计算。
AVG()：用于计算指定列的平均值，通常用于对数值型列进行平均值计算。
MAX()：用于返回指定列的最大值，通常用于获取某列的最大数值或最大日期等。
MIN()：用于返回指定列的最小值，通常用于获取某列的最小数值或最小日期等。

这些分组函数通常与GROUP BY子句一起使用，以便对数据按照指定的列进行分组，并对每个组应用相应的函数进行计算。例如，可以使用GROUP BY对某个列进行分组，然后使用COUNT()函数计算每个组中的行数，或者使用SUM()函数计算每个组中某个数值列的总和。

3.2.组函数语法

分组函数一般用于select和from之间，需要结合某个列字段使用

数据处理之查询_ci_25

3.3.AVG（平均值）和SUM （合计）函数

可以对数值型数据使用AVG 和SUM 函数：

数据处理之查询_ci_26

3.4.MIN（最小值）和 MAX（最大值）函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数

数据处理之查询_ci_27

3.5.COUNT（计数）

函数 COUNT(*) 返回表中记录总数,适用于任意数据类型。

数据处理之查询_字段_28

COUNT(expr) 返回expr不为空的记录总数。

数据处理之查询_ci_29

四、分组查询

4.1.分组数据

⑴.分组数据说明：

数据处理之查询_ci_30

⑵.分组数据: GROUP BY 子句语法

可以使用GROUP BY子句将表中的数据分成若干组

数据处理之查询_数据_31

注意：WHERE一定放在FROM后面

在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。

数据处理之查询_数据_32

包含在GROUP BY 子句中的列不必包含在SELECT 列表中

数据处理之查询_ci_33

数据处理之查询_字段_34

⑶.使用多个列分组

数据处理之查询_ci_35

在GROUP BY子句中包含多个列

在 MySQL 中使用 GROUP BY 对表中的数据进行分组时，GROUP BY X 意味着将所有具有相同 X 字段值的记录放到一个分组里，GROUP BY X, Y 意味着将所有具有相同 X 字段值和 Y 字段值的记录放到一个分组里。

SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id,job_id;

数据处理之查询_ci_36

⑷.非法使用组函数

不能在WHERE 子句中使用组函数。
可以在HAVING 子句中使用组函数。

数据处理之查询_字段_37

注意：WHERE 子句中不能使用组函数

4.2.分组过滤

⑴.分组过滤

案例：统计部门最高工资比10000高的部门编号，需要使用HAVING子句；

数据处理之查询_数据_38

⑵.过滤分组：HAVING 子句

使用HAVING 过滤分组:

行已经被分组。
使用了组函数。
满足HAVING 子句中条件的分组将被显示

数据处理之查询_字段_39

⑶.案例

统计部门最高工资比10000高的部门

SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000;

如下：

数据处理之查询_ci_40

五、多表查询

5.1.多表问题引入

创建两张表：beauty表和boys表

DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys`  (
  `id` int(11) NOT NULL,
  `boyName` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `userCP` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
)

INSERT INTO `boys` VALUES (1, '张无忌', 100);
INSERT INTO `boys` VALUES (2, '鹿晗', 800);
INSERT INTO `boys` VALUES (3, '黄晓明', 50);
INSERT INTO `boys` VALUES (4, '段誉', 300);

DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `boyfriend_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
)

INSERT INTO `beauty` VALUES (1, '柳岩', '女', NULL);
INSERT INTO `beauty` VALUES (2, '范冰冰', '女', NULL);
INSERT INTO `beauty` VALUES (3, 'Angelababy', '女', 3);
INSERT INTO `beauty` VALUES (4, '关晓彤', '女', 2);
INSERT INTO `beauty` VALUES (5, '赵丽颖', '女', NULL);
INSERT INTO `beauty` VALUES (6, '周冬雨', '女', 1);
INSERT INTO `beauty` VALUES (7, '白百何', '女', NULL);
INSERT INTO `beauty` VALUES (8, '赵敏', '女', 1);
INSERT INTO `beauty` VALUES (9, '倪妮', '女', NULL);
INSERT INTO `beauty` VALUES (10, '王语嫣', '女', 4);
INSERT INTO `beauty` VALUES (11, '马苏', '女', NULL);
INSERT INTO `beauty` VALUES (12, '杨幂', '女', 1);

思考问题：如果想查询女神的名称和对应的男神名称，该如何实现？

语法：

SELECT name,boyName FROM beauty,boys;

数据处理之查询_数据_41

笛卡尔集的错误情况：

select count(*) from beauty;
假设输出12行

select count(*)from boys;
假设输出4行 boys表

最终结果：12*4=48行

5.2.笛卡尔集

笛卡尔集会在下面条件下产生:

省略连接条件
连接条件无效
所有表中的所有行互相连接

为了避免笛卡尔集，可以在 WHERE 加入有效的连接条件

笛卡尔积（也称为直积）是集合论中的一个基本概念，由法国数学家笛卡尔（René Descartes）首次引入。它描述了两个集合之间所有可能的有序对的集合。在笛卡尔积中，每个元素都与另一个集合中的每个元素形成一对，这样就生成了所有可能的组合。

给定集合 $A 和集合 B，笛卡尔积 A\timesB 表示将集合 A 中的每个元素与集合 B 中的每个元素进行组合，形成一个新的集合。例如，如果集合 A 是13个元素的点数集合，而集合 B 是4个元素的花色集合（♠, ♥, ♦, ♣），则这两个集合的笛卡尔积是有52个元素的标准扑克牌的集合（♠♠, ♠♥, ♠♦, ♠♣, ♥♠, ♥♥, ♥♦, ♥♣, ♦♠, ♦♥, ♦♦, ♦♣, ♣♠, ♣♥, ♣♦, ♣♣）。$

笛卡尔积得名于笛卡尔，因为这个概念是由他建立的解析几何引申出来的。它在数学、计算机科学和其他领域中都有广泛的应用。

一些笛卡尔积的性质包括：

对于任意集合 $A，有 A\times\emptyset=\emptyset\timesA=\emptyset，其中 \emptyset 表示空集。$
笛卡尔积不满足交换律和结合律。
笛卡尔积对集合的并和交满足分配律。

5.3.Mysql 连接

使用连接在多个表中查询数据。

数据处理之查询_字段_42

说明：

在 WHERE 子句中写入连接条件。
在表中有相同列时，在列名之前加上表名前缀

通过id字段连接。这样就能避免笛卡尔积

数据处理之查询_ci_43

5.4.等值连接

MySQL等值连接是一种数据库查询操作，它允许我们将两个表中的行基于相等的条件进行匹配。在日常的数据库操作中，我们经常会使用等值连接。通过在两个表之间匹配相等的列值来组合记录。语法如下：

数据处理之查询_字段_44

5.5.区分重复的列名

在多表连接查询的时候，会存在多个表中字段名重复，就需要使用表名前缀在多个表中区分相同的列。

在不同表中具有相同列名的列可以用表的别名加以区分。
如果使用了表别名，则在select语句中需要使用表别名代替表名
表别名最多支持32个字符长度，但建议越少越好

5.6.表的别名

使用别名可以简化查询。
使用表名前缀可以提高执行效率。

数据处理之查询_ci_45

5.7.连接多个表

数据处理之查询_字段_46

注意：连接 n个表,至少需要 n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

练习：查询出公司员工的last_name, department_name, city

SELECT last_name,department_name,city FROM employees,departments,locations 
WHERE employees.department_id = departments.department_id AND departments.location_id = locations.location_id

执行如下图：

数据处理之查询_ci_47

5.8.SQL多表查询分类

5.8.1.分类：

⑴.按年代分类：

sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

⑵.按功能分类：

内连接：

等值连接
非等值连接
自连接

外连接：

左外连接
右外连接
全外连接（MySQL不支持）

交叉连接

5.8.2.内连接

# SQL92标准，内连接分类

/*
1、等值连接
① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

*/

#案例1：查询女神名和对应的男神名
SELECT name,boyName FROM boys,beauty WHERE beauty.boyfriend_id = boys.id;

#案例2：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.department_id = departments.department_id

/*
2、为表起别名
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/

#查询员工名、工种号、工种名
SELECT e.last_name,j.job_id,j.job_title FROM employees e,jobs j WHERE e.job_id = j.job_id

#3、两个表的顺序是可以调换
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

#4、可以加筛选
#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct 
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT d.department_name, l.city FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE "_o%"

#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT city,COUNT(*) 部门个数 FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY l.city

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
/*
由于默认的 MySQL 配置中 sql_mode 配置了 only_full_group_by，需要 GROUP BY 中包含所有 在 SELECT 中出现的字段。
only_full_group_by：使用这个就是使用和oracle一样的group 规则, 出现在select后的字段（除了聚合的字段以外SUM,AVG,MAX,MIN）都必须要在group中。
可以在命令行模式下使用 SQL 语句可以查询这个配置
SELECT @@sql_mode;
*/
SELECT d.department_name,d.manager_id,MIN(e.salary) FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY d.department_name,d.manager_id

#2、非等值连接
/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);
*/

#案例1：查询员工的工资和工资级别
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

#案例2：查询工资级别为A的员工工资和级别
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
AND j.grade_level = "A"

/*
#3、自连接
自连接是指在同一张表中使用连接操作，将表中的数据进行联合查询。它允许我们在一个查询中引用同一张表的两个不同实例，并根据某种关联条件来关联这两个实例。
*/
#案例1：查询 员工名和上级的名称 （注意：员工名和对应的上级领导名在一个）
SELECT e.last_name 员工,m.last_name 上级
FROM employees e,employees m
WHERE e.manager_id = m.employee_id

# 案例2：查询 姓名中包含字母h的员工名和上级的名称 （注意：员工名和对应的上级领导名在一个）
SELECT e.last_name 员工,m.last_name 上级
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name LIKE "%h%"

5.8.3.外连接

/*
外连接
应用场景：用于查询一个表中有，另一个表没有的记录
 
特点：
①.外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
②.左外连接，left join左边的是主表
  右外连接，right join右边的是主表
③.左外和右外交换两个表的顺序，可以实现同样的效果 
④.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
*/

#引入：查询男朋友 不在男神表的的女神名
# 左外连接
SELECT be.*,bo.* FROM beauty be LEFT JOIN boys bo ON be.boyfriend_id = bo.id
WHERE bo.id IS NULL

# 右外连接 实现
SELECT be.*,bo.* FROM boys bo RIGHT JOIN beauty be ON be.boyfriend_id = bo.id
WHERE bo.id IS NULL

#案例1：查询哪个部门没有员工-左外实现
# WHERE e.employee_id IS NULL 表示主表department中如果的信息如果和从表employees中如果没有匹配上则显示出来，表示没有员工
SELECT d.*,e.employee_id
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL

# -右外实现
SELECT d.*,e.employee_id
FROM employees e RIGHT JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL

#全外(MySQL不支持)
/*
全外连接返回两个表中的所有行。
如果某个表中没有匹配的行，则结果集中对应的列将包含NULL值。
全外连接的结果集是左外连接和右外连接结果的组合。
语法：SELECT ... FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
全外连接在某些特定场景下非常有用。例如，当您需要从两个相关表中获取所有数据，而不关心是否存在匹配的记录时，可以使用全外连接。此外，全外连接还可以用于合并来自不同数据源的数据，即使这些数据源之间没有明确的关联关系
*/
SELECT b.*,bo.* 
FROM beauty b
FULL JOIN boys bo;
ON b.boyfriend_id = bo.id

5.8.4.交叉连接

交叉连接（也称为笛卡尔积）是SQL中一种特殊的连接类型，用于生成两个表之间的所有可能组合，交叉连接的语法：

使用CROSS JOIN关键字可以轻松地执行交叉连接操作。

#交叉连接
SELECT b.*,bo.* 
FROM beauty b
CROSS JOIN boys bo;

5.8.5.练习

#查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT bo.*,be.`name` FROM 
boys bo LEFT JOIN beauty be 
ON bo.id = be.boyfriend_id
WHERE be.id > 3

#查询哪个城市没有部门
SELECT l.city FROM 
locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.department_id IS NULL

#查询部门名为SAL或IT的员工信息
SELECT d.department_name,e.* FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'SAL' OR d.department_name = 'IT'

5.9.SQL99：使用ON 子句创建连接

自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。

⑴.Join连接

分类：

– 内连接 [inner] join on

– 外连接

左外连接left [outer] join on
右外连接right [outer] join on

⑵.ON 子句

数据处理之查询_数据_48

效果如下：

数据处理之查询_数据_49

⑶.使用 ON 子句创建多表连接

数据处理之查询_ci_50

SQL如下：

SELECT last_name,department_name,city FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id

⑷.外连接

左外连接SQL如下：

SELECT beauty.id,beauty.name,boys.boyName FROM beauty LEFT JOIN boys ON beauty.boyfriend_id = boys.id

执行后如下：

数据处理之查询_ci_51

⑸.连接总结

数据处理之查询_ci_52

外连接排除内连部分说明：

数据处理之查询_ci_53

六、子查询

6.1.子查询

概念：出现在其他语句内部的select语句，称为子查询或内查询内部嵌套其他select语句的查询，称为外查询或主查询

# 查询location_id=1700的部门中的员工的名字
SELECT first_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id=1700)

注意：

子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询，多行操作符对应多行子查询

6.2.子查询类型

⑴.单行子查询

数据处理之查询_字段_54

⑵.多行子查询

数据处理之查询_ci_55

6.3.单行子查询

单行子查询只返回一行，需要使用单行比较操作符：

数据处理之查询_ci_56

6.4.子查询语法

数据处理之查询_字段_57

说明：

子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用。

6.5.使用子查询解决问题

案例一：查询工资比Abel 高的员工名

# 查询工资比Abel 高的员工名
SELECT first_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name='Abel')

案例二：返回job_id与141号员工相同，salary比143号员工多的员工名，job_id 和工资

# 返回job_id与141号员工相同，salary比143号员工多的员工名，job_id 和工资
SELECT first_name,job_id,salary FROM employees 
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id=141)
AND salary > (SELECT salary FROM employees WHERE employee_id=143)

6.6.在子查询中使用组函数

案例：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees)

数据处理之查询_数据_58

6.7.子查询中的HAVING 子句

• 首先执行子查询。

• 向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

题目：查询最低工资 大于 50号部门最低工资的 部门id和 其最低工资  2100
SELECT department_id, MIN(salary) FROM employees 
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50)

数据处理之查询_ci_59

6.8.非法使用子查询

数据处理之查询_数据_60

多行子查询使用单行比较符等号，但是后面是根据部门ID分组求最低薪资，这时候多个部门有多个值给等号就会报错，将等号换成in则可以解决这个问题

SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);

报错

数据处理之查询_ci_61

6.9.子查询中的空值问题

如果子查询括号中的内容返回为空，那么子查询不会返回任何行，

数据处理之查询_字段_62

7.10.多行子查询

多行子查询会返回多行数据。但是需要使用多行比较操作符：

数据处理之查询_数据_63

⑴.使用in操作符

# 案例：返回location_id是1400或1700的部门中的所有员工姓名
SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1400 OR location_id=1700)

⑵.在多行子查询中使用 ANY 操作符

案例：：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary

数据处理之查询_ci_64

# 案例：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,salary,job_id 
FROM employees 
WHERE salary < ANY(SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'         # 排除职业编号为IT_PROG

⑶.在多行子查询中使用 ALL 操作符

案例：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

数据处理之查询_ci_65

# 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary在多行子查询中使用 ALL 操作符 题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,salary,job_id 
FROM employees 
WHERE salary < ALL(SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'

7.11.多行子查询空值问题

子查询中的空值问题，如果子查询括号中查询出来的结果有NULL值，则整个子查询返回为空

SELECT last_name FROM employees 
WHERE employee_id NOT IN (SELECT manager_id FROM employees)

如下：因为为NULL值所以子查询为空

数据处理之查询_数据_66

要解决上面的问题，只需要去掉子查询中的NULL即可

SELECT last_name FROM employees 
WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL)

执行后查询如下：

数据处理之查询_数据_67

七、分页查询

7.1.MySQL中使用limit实现分页

背景

查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

分页原理

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件

数据处理之查询_数据_68

7.2.limit实现分页

MySQL中使用limit实现分页怎么分段，当前在第几段（每页有几条，当前在第几页）

前10条记录：SELECT * FROM table LIMIT 0,10;
第11至20条记录：SELECT * FROM table LIMIT 10,10;
第21至30条记录： SELECT * FROM table LIMIT 20,10;

limit后面参数表示从第几行开始显示几条数据

公式：

（当前页数-1）*每页条数，每页条数

SQL语句

SELECT * FROM table LIMIT(PageNo- 1)*PageSize,PageSize;

注意： limit子句必须放在整个查询语句的最后

案例：将员工信息表，每页显示5行，分页查询显示（下面的SQL之间计算会出错）

SELECT * FROM employees LIMIT 0,5
SELECT * FROM employees LIMIT (2-1)*5,5
SELECT * FROM employees LIMIT (3-1)*5,5
SELECT * FROM employees LIMIT (4-1)*5,5
SELECT * FROM employees LIMIT (5-1)*5,5
SELECT * FROM employees LIMIT (6-1)*5,5

八、联合查询

8.1.什么是联合查询？

说明：当查询结果来自于多张表，但是多张表之间没有关联关系，这个时候就需要使用联合查询，也称之为union查询

8.2.语法

select 查询列表 from 表1 where 筛选条件
union
select 查询列表 from 表2 where 筛选条件

8.3.特点

多条待联合的查询语句的查询列出必须一致，否则会报错，查询类型和字段含义建议一致
union会实现去重查询，union all如果数据重复则会保留，不会去重

准备测试数据：

# 测试部门人员表
CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
# Java部门人员表
CREATE TABLE java (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

插入数据的SQL：

-- 向table1插入数据
INSERT INTO test (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO test (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO test (id, name, age) VALUES (3, 'Charlie', 28);

-- 向table2插入数据
INSERT INTO java (id, name, age) VALUES (4, 'David', 35);
INSERT INTO java (id, name, age) VALUES (5, 'Eva', 27);
INSERT INTO java (id, name, age) VALUES (6, 'Frank', 32);

⑴.多条待联合的查询语句的查询列出必须一致，否则会报错，查询类型和字段含义建议一致

SELECT name,age FROM test 
UNION
SELECT name,age FROM java

这样进行联合不会出错，列出是一致的

数据处理之查询_数据_69

下面列出不一致会报错

数据处理之查询_字段_70

⑵.union会实现去重查询，union all如果数据重复则会保留，不会去重

数据处理之查询_数据_71

union all不会去重

数据处理之查询_ci_72

上一篇：MySQL概述和安装使用

下一篇：Numpy模块详解

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯