数据库相关知识点（整理中）

常用的聚合函数有哪五种？

count()、sum()、avg()、min()、max()

count()：求表的行数或者指定表中某个列的列值行数，null值被忽略

select count(字段) from 表名 --得到该列值的非null值的行数

select count(*) from 表名  --用于统计整个表的行数。任何行，只要有一行非null，则整个表的行数就会被统计上。全为null（不会出现该情况）则不被统计

sum()：返回指定数据的和，只能用于数字列，null值被忽略

select sum(salary) from emp   --求所有salary的总和

max()：返回一列中的最大值，null值被忽略

select max(column_name) from table_name

min()：返回一列中的最小值，null值被忽略

select max(column_name) from table_name

avg()：返回数值列的平均值，null值被忽略

select avg(salary) as avg_sal from emp

注意事项

1. 聚合函数可用于任何有效的表达式
2. 聚合函数会忽略空值
3. distinct关键字可以与聚合函数一起使用，这样可以在聚合函数的计算中排除重复项

聚合函数

聚合函数	描述	实例	MySQL	SQL Server
AVG()	返回某列的平均值	SELECT AVG(column_name) FROM table_name;	支持	支持
COUNT()	返回某列中的值的数量	SELECT COUNT(column_name) FROM table_name;	支持	支持 COUNT() 函数，并且提供了额外的选项，如 COUNT_BIG() 用于处理大量数据
MAX()	返回某列中的最大值	SELECT MAX(column_name) FROM table_name;	支持	支持
MIN()	返回某列中的最小值	SELECT MIN(column_name) FROM table_name;	支持	支持
SUM()	返回某列值的总和	SELECT SUM(column_name) FROM table_name;	支持	支持
GROUP_CONCAT()	在某些数据库系统（如 MySQL）中，将多个行的值组合成一个由分隔符分隔的字符串	SELECT GROUP_CONCAT(column_name SEPARATOR ‘, ‘) FROM table_name;	将多个行的值组合成一个字符串	没有直接的 GROUP_CONCAT() 函数，可以使用 FOR XML PATH 来实现类似的功能。
STD() / STDDEV() / STDDEV_POP() / STDDEV_SAMP()	返回某列的总体或样本标准偏差	SELECT STD(column_name) FROM table_name;	STD() 和 STDDEV() 函数（它们是等价的）	STDDEVP()（总体标准偏差）和 STDDEV()（样本标准偏差）
VAR_POP() / VARIANCE_POP()	返回某列的总体方差	SELECT VAR_POP(column_name) FROM table_name;	VAR_POP()	VARP()（总体方差）
VAR_SAMP() / VARIANCE_SAMP()	返回某列的样本方差	SELECT VAR_SAMP(column_name) FROM table_name	VAR_SAMP()	VAR()（样本方差）
COVAR_POP() / COVAR_SAMP()	返回两个列的协方差	SELECT COVAR_POP(column1, column2) FROM table_name;	不直接支持	支持
CORRELATION()	返回两个列之间的相关系数	SELECT CORRELATION(column1, column2) FROM table_name;	不直接支持	不直接支持
PERCENT_RANK()	返回相对于其他行的行的百分比排名	SELECT PERCENT_RANK(column_name) OVER (ORDER BY column_name) FROM table_name;	不直接支持	支持
CUME_DIST()	返回相对位置的累积分布	SELECT CUME_DIST(column_name) OVER (ORDER BY column_name) FROM table_name;	不直接支持	支持
NTILE()	将有序分区中的结果行分成指定数量的近似相等的排名组	SELECT NTILE(4) OVER (ORDER BY column_name) FROM table_name;	不直接支持	支持
LEAD() / LAG()	返回指定偏移量的行的值，用于分析数据中的趋势	SELECT LEAD(column_name, 1) OVER (ORDER BY column_name) FROM table_name; SELECT LAG(column_name, 1) OVER (ORDER BY column_name) FROM table_name;	从8.0 版本开始支持	支持
FIRST_VALUE() / LAST_VALUE()	返回指定窗口帧中的第一个或最后一个值	SELECT FIRST_VALUE(column_name) OVER (ORDER BY column_name) FROM table_name; SELECT LAST_VALUE(column_name) OVER (ORDER BY column_name) FROM table_name;	从 8.0 版本开始支持 FIRST_VALUE() 和 LAST_VALUE() 函数	支持
GREATEST() / LEAST()	返回一组值中的最大值或最小值	SELECT GREATEST(value1, value2, …) FROM table_name; SELECT LEAST(value1, value2, …) FROM table_name;	支持	支持

修改某表某字段，不允许空值

ALTER TABLE 表名
ALTER COLUMN 修改字段名 字段数据类型 NOT NULL

ORDER BY，GROUP BY

Oracle SQL语句组成：

数据定义语言（DDL）	数据操作语言（DML）	数据查询语言（DQL）	事物控制语言（TCL）	数据控制语言（DCL）
CREATE（创建命令）、ALTER（修改）命令、DROP（删除）命令等	INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT…FOR UPDATE（查询）等	基本查询语句、Order By子句、Group By子句等	COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令	GRANT（授权）命令、REVOKE（撤销）命令

WHERE不能接聚合函数（MAX、MIN、COUNT、SUM、AVG等）;HAVING后可以接聚合函数；

WHERE用在GROUP BY前，先过滤后分组；

HAVING用在GROUP BY之后，先分组后过滤，且使用HAVING一定要用到GRUOP BY，但用到GROUP BY 不一定有HAVING。

查询表的总行数，并命名为cnt

select count(*) (as) cnt from 表名（as可加可不加）

select count(主键或其他不为空的字段) (as) cnt from 表名（as可加可不加）

distinct 搜索去重：select distinct [列名1，列名2，…] from [表名]

检索所有列

select * from 表名

插入记录的方式汇总：

• 普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
• 普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
• 多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
• 从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
• 带更新的插入：REPLACE INTO table_name VALUES (value1, value2, …) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

修改记录的方式汇总：

• 设置为新值：UPDATE table_name SET column_name=new_value [column_name2=new_value2][WHERE column_name3=value3]
• 根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options][ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数
    关键词：substing,concat,upper

用法：

字符串的截取：substring(字符串，起始位置，截取字符数）
字符串的拼接：concat(字符串1，字符串2，字符串3,…)
字母大写：upper(字符串）

SQL语句书写顺序

select->distinct->from->join->on->where->group by->having->order by->limit

SQL语句执行顺序

from->on->join->where->group by（开始使用select中的别名，后面的语句中都可以使用别名）->sum、count、max、min、avg->having->select->distinct->order by->limit

DATEDIFF() 是 SQL 中的一个函数，用于计算两个日期之间的差异

DISTINCT关键字

当查询某个列时，使用 DISTINCT 可以确保结果集中该列的值是唯一的；也可以在查询多个列时使用 DISTINCT，此时 DISTINCT 会确保这些列的组合是唯一的。

注意事项

1. 性能影响：使用 DISTINCT 可能会对查询性能产生影响，特别是在处理大型数据集时。因为数据库需要额外的工作来识别和消除重复的行。
2. NULL 值：DISTINCT 会将 NULL 值视为不同的值。也就是说，如果列中有多个 NULL 值，DISTINCT 会将它们全部返回。
3. 与其他 SQL 语句结合：DISTINCT 可以与其他 SQL 语句（如 ORDER BY、WHERE 等）结合使用，以进一步定制查询结果。
4. 不应用于聚合函数：虽然 DISTINCT 用于消除重复行，但它不应该与聚合函数（如 SUM(), COUNT(), AVG() 等）一起用于单个列，因为这会导致错误或不可预测的结果。

SUBSTRING(column_name, start, length)：这将从列的值中提取一个子字符串，从指定的起始位置开始，直到指定的长度。

UPPER(expression)：这会将字符串表达式转换为大写。

LOWER(expression)：这会将字符串表达式转换为小写。

CONCAT(string1, string2, …)：这会将两个或多个字符串连接成一个字符串。

SQL 语句主要区别

	MySQL	SQL Server	Oracle
字符串连接	使用 CONCAT() 函数	使用 + 运算符或 CONCAT() 函数	使用 `
自增字段	使用 AUTO_INCREMENT 关键字	使用 IDENTITY 关键字	使用序列（sequence）和触发器（trigger）
分页查询	使用 LIMIT 和 OFFSET	使用 OFFSET 和 FETCH	使用 ROWNUM 或 FETCH 和 OFFSET（在12c及更高版本中）
日期函数	使用 DATE_FORMAT(), NOW(), DATEDIFF() 等	使用 FORMAT(), GETDATE(), DATEDIFF() 等	使用 TO_DATE(), SYSDATE, MONTHS_BETWEEN() 等
变量	使用 SET 或 SELECT ... INTO 来定义变量	使用 DECLARE 来定义变量，并使用 SET 或 SELECT 来赋值	使用 DECLARE 来定义变量，并使用 SELECT INTO 来赋值
LIMIT 返回的行数	默认允许使用 LIMIT 返回大量行数	在较早的版本中，TOP 有一个默认的行数限制（如 1000 行），但在较新的版本中这个限制已被移除	没有内置的行数限制，但分页查询可能需要额外的逻辑
数据类型	使用 TINYINT, MEDIUMINT, LONGTEXT 等	使用 TINYINT, INT, VARCHAR(MAX) 等	使用 NUMBER, VARCHAR2, CLOB
存储过程和函数	存储过程和函数的语法和调用方式在每种数据库系统中也有所不同	存储过程和函数的语法和调用方式在每种数据库系统中也有所不同	存储过程和函数的语法和调用方式在每种数据库系统中也有所不同

数据库的三个范式

1. 第一范式（1NF）：要求数据库表中的每个字段都是原子性的，即不可再分。这意味着每个字段中不能包含多个值或重复的值。如果一个字段包含多个值，应该将其拆分为多个独立的字段。第一范式确保了数据的原子性，消除了数据的重复和冗余。
2. 第二范式（2NF）：要求数据库表中的非主键字段必须完全依赖于主键。换句话说，非主键字段必须与主键直接相关，而不是间接相关。如果一个表中存在部分依赖关系，即某些字段只依赖于主键的一部分，就需要将这些字段拆分到另一个表中，以确保每个表都只包含相关的数据。第二范式消除了部分依赖，进一步减少了数据的冗余性。
3. 第三范式（3NF）：要求数据库表中的非主键字段之间不能存在传递依赖关系。传递依赖是指非主键字段依赖于其他非主键字段。如果存在传递依赖，就需要将这些字段拆分到另一个表中，以消除冗余和数据不一致性。第三范式消除了传递依赖，使得数据结构更加清晰和稳定。

这三个范式的设计目的是确保数据库的简洁性、结构明晰性以及数据的一致性。遵循这些范式可以提高数据库的性能和可维护性，减少数据的冗余和不一致性。然而，需要注意的是，在某些特定场景下，为了优化查询性能或其他原因，可能会选择违反这些范式。因此，在实际应用中，需要根据具体业务需求和数据特点来权衡和选择合适的设计方案。