MySQL数据库基础3-基础查询

基础查询(单表)

创建表、单行插入、多行插入就不重复介绍了。

替换

当我们的程序每天都会产生大量的数据，而这些数据都是前一天或者再之前的数据更新产生，我们也只需要保存其中一条，此时就考虑用新的数据替换老的数据。

语法：
replace into tablename values();

如果主键或者唯一键不冲突，则直接插入数据
如果主键或唯一键冲入，则删除主键或唯一键重复的行再插入

查询

• 全表全列查询：select * from [表名]
• 全表指定列查询：select [列名] [列名] … from [表名]
• 查询字段为表达式，此时要注意区分表达式的内容是否包含字段
• 为查询结果指定别名

SELECT column [AS] alias_name […] FROM table_name;

• 结果去重

SELECT DISTINCT [列名] FROM table_name;

• WHERE，条件查询，指定查询行的内容。在where后加上条件

比较运算符：

运算符	说明
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0）

结果排序

order by [字段名] ：将查询结果按照order by后的字段进行排序

语法：
SELECT … FROM table_name [WHERE …] ORDER BY column [ASC|DESC], […];
ASC 为升序（默认是升序）
DESC为降序

在使用 ORDER BY 进行排序时，不能依赖排序结果的具体顺序，尤其是当排序字段中存在相同的值时。

不同的数据库管理系统可能采用不同的算法和策略来处理排序，因此排序结果可能会有所不同。如果需要确保特定的排序结果，可以通过在 ORDER BY 子句中指定多个排序条件，以便在有相同值的情况下进行更精确的排序。例如，可以使用其他字段作为次要排序条件来进一步确定排序顺序。也就是按照多列进行排序。order by 字段1，字段2，先按照字段1进行排序，再将字段1的结果中相同字段值的部分，按照字段2进行排序

order by中可以使用别名，与where不同，where中不允许使用别名

筛选分页结果

筛选分页结果是一种数据查询和展示技术，用于在大数据集中按需获取特定页的数据。通常情况下，数据库中的数据量可能非常庞大，而且一次性检索和显示所有数据可能会导致性能问题和用户体验下降。通过筛选分页结果，可以按照用户的需求和界面的设计，以分页的方式逐步加载和展示数据，提供更好的用户体验并减少数据库和网络资源的负载。

需要注意：筛选分页结果的起始下标是从0开始的。

• 从 0 开始，筛选 n 条结果

SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;

• 从 s 开始，筛选 n 条结果

SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;

• 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;

更新表

语法：
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
注意：如果没有condition，则会将所有的行对应的列进行更新

在执行UPDATE语句时，请务必谨慎，确保更新的条件和值符合预期，以免误操作导致数据不正确或不可恢复的问题。在更新表之前，最好先进行备份或测试，以确保安全性和正确性。

删除数据

语法：
DELETE FROM table_name WHERE condition;
如果没有condition，那么删除的是整张表。

如果把整张表的数据都删除了，但是表依旧在，原先的表结构不会改变。 AUTO_INCREMENT会在之前的基础上增加。

需要注意的是，执行DELETE语句会永久删除数据，因此在删除数据之前，请确保确认操作并做好备份。

截断(清空)表

语法：
TRUNCATE [TABLE] table_name
截断表将删除表中的所有行，但保留表的定义、索引、约束等信息

这个操作只能对整张表进行操作，不能像 DELETE 一样针对部分数据操作； 会重置 AUTO_INCREMENT 项

聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

分组查询

分组查询是一种在数据库中进行数据分组和聚合的操作。它允许我们按照指定的列或表达式对数据进行分组，并在每个组上执行聚合函数来计算汇总值。

在select中使用group by 子句对指定列进行分组查询：

select column1, column2, … from table group by column;

我们以雇员信息表为例（来自oracle 9i的经典测试表）

• 如何显示每个部门的平均工资和最高工资

select deptno,avg(sal),max(sal) from emp group by deptno;

执行结果：

• 显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),job,deptno from emp group by deptno,job;

执行结果：

• 显示平均工资低于2000的部门和它的平均工资，首先统计各个部门的平均工资，再配合having进行过滤

统计各个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

执行结果：

对结果进行过滤，得到平均工资低于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;

执行结果：

分组查询可以帮助我们对数据进行更细粒度的统计和分析。我们可以根据需要对多个列进行分组，并在每个组上执行多个聚合函数。此外，还可以通过使用HAVING子句对分组结果进行过滤。

分组查询是 SQL 中常用的功能之一，可以用于处理统计报表、数据分析和生成汇总结果等场景。