【数据库】数据库学习（MySQL,Oracle,PostgreSql）

数据库语句学习

摘要：文章主要内容是数据库语句的基本操作，以及一些基本的数据库标准库函数

重点：SQL语句对大小写不敏感

数据库操作语句

• SELECT - 从数据库表中获取数据
• UPDATE - 更新数据库表中的数据
• DELETE - 从数据库表中删除数据
• INSERT INTO - 向数据库表中插入数据

字句与附加内容

SELECT list1,list2 FROM table;//输出某几个表

SELECT * FROM table;//输出表中全部内容

SELECT DISTINCT list1 FROM table;//输出列中内容，但每个值只出现一次

SELECT * FROM table WHERE id=2;//查找id为2的那一行，注意等号为单个，并且如果查找内容非数值，需要加单引号eg:WHERE name='alice'

SELECT * FROM table WHERE id=2 AND name='alice';//他的意思和你想的一样，同样的用法还有OR

SELECT * FROM table ORDER by id;//查找以后的结果按照id正序输出

INSERT INTO table(list1,list2) VALUES('alice','bob')

UPDATE table SET list1='alice',list2='bob' WHERE id=1;

DELETE FROM table WHERE id=1;
DELETE * FROM table;

数据库定义语句

• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

oracle数据库标准库函数（math库）（DML库-操作游标 json）

1. 单行函数

字符函数

• CONCAT('Hello', 'World'): 结果为 'HelloWorld'。
• SUBSTR('HelloWorld', 6, 5): 从第6位开始取5个字符，结果为 'World'。
• LENGTH('Hello'): 返回字符串 'Hello' 的长度，结果为 5。
• TRIM('H' FROM 'HHHelloHH'): 移除字符串两侧的 ‘H’，结果为 'Hello'。
• LOWER('HELLO'): 将字符串转换为小写，结果为 'hello'。
• UPPER('hello'): 将字符串转换为大写，结果为 'HELLO'。
• REPLACE('2024 Happy New Year', '2024', '2025'): 将字符串中的 2024 替换为 2025，结果为 '2025 Happy New Year'。

数值函数

• ROUND(123.4567, 2): 将 123.4567 四舍五入到小数点后2位，结果为 123.46。
• TRUNC(123.4567, 2): 将 123.4567 截断到小数点后2位，结果为 123.45。
• ABS(-123.45): 返回 -123.45 的绝对值，结果为 123.45。
• CEIL(123.45): 返回大于等于 123.45 的最小整数，结果为 124。
• FLOOR(123.45): 返回小于等于 123.45 的最大整数，结果为 123。

日期函数

• SYSDATE: 返回当前系统日期和时间，结果取决于系统时刻。
• ADD_MONTHS('01-JAN-2024', 1): 在日期 01-JAN-2024 上加1个月，结果为 01-FEB-2024。
• LAST_DAY('15-JAN-2024'): 返回 JAN-2024 的最后一天，结果为 31-JAN-2024。
• MONTHS_BETWEEN('01-JAN-2024', '01-MAR-2024'): 计算从 01-MAR-2024 到 01-JAN-2024 之间的月份差异，结果为 -2。
• NEXT_DAY('01-JAN-2024', 'FRIDAY'): 返回 01-JAN-2024 后的第一个星期五，日期取决于当年日历。

2. 聚合函数（详细）（三个不同）

• SUM(column): 计算某列的总和。例如，在收入列上使用，可以计算总收入。
• AVG(column): 计算某列的平均值。例如，计算所有员工的平均薪资。
• MAX(column): 找出某列的最大值。例如，找出最高的订单金额。
• MIN(column): 找出某列的最小值。例如，找出最低的库存量。
• COUNT(column): 计算某列的非空值数量。例如，计算有多少员工有邮箱地址。

3. 转换函数

• TO_CHAR(SYSDATE, 'YYYY-MM-DD'): 将当前日期转换为 'YYYY-MM-DD' 格式的字符串。
• TO_NUMBER('123.45', '999.99'): 将字符串 '123.45' 转换为数字。
• TO_DATE('01-JAN-2024', 'DD-MON-YYYY'): 将字符串 '01-JAN-2024' 转换为日期类型。

4. 分析函数

• ROW_NUMBER() OVER (ORDER BY salary DESC): 对所有员工按薪资降序排列，并给每个员工分配一个唯一的行号。
• RANK() OVER (ORDER BY score DESC): 在成绩表中，对学生的分数进行排名，相同分数的学生排名相同。
• DENSE_RANK() OVER (ORDER BY score DESC): 类似 RANK，但排名将连续不留空。

5. 空值处理函数（详细）

• NVL(NULL, 'default'): 如果第一个表达式是 NULL，返回 'default'。
• COALESCE(NULL, NULL, 'first_non_null'): 返回参数列表中的第一个非空表达式，结果为 'first_non_null'。
• NULLIF('A', 'B'): 如果两个表达式不相等，返回第一个表达式，结果为 'A'；如果相等，则返回 NULL。

MySQL数据库函数

1. 字符串函数

• CONCAT(str1, str2, ...): 连接两个或多个字符串。例如：CONCAT('Hello', ' ', 'World') 返回 'Hello World'。
• LENGTH(str): 返回字符串的长度。例如：LENGTH('Hello') 返回 5。
• LOWER(str): 将字符串转换为小写。例如：LOWER('HELLO') 返回 'hello'。
• UPPER(str): 将字符串转换为大写。例如：UPPER('hello') 返回 'HELLO'。
• REPLACE(str, from_str, to_str): 在字符串中替换子串。例如：REPLACE('Hello World', 'World', 'MySQL') 返回 'Hello MySQL'。
• SUBSTRING(str, pos, len): 从字符串中提取子串。例如：SUBSTRING('Hello World', 7, 5) 返回 'World'。
• TRIM(str): 去除字符串两侧的空格。例如：TRIM(' Hello ') 返回 'Hello'。

2. 数值函数

• ROUND(number, decimals): 对数值进行四舍五入。例如：ROUND(123.4567, 2) 返回 123.46。
• FLOOR(number): 返回不大于数值的最大整数。例如：FLOOR(123.45) 返回 123。
• CEIL(number): 返回不小于数值的最小整数。例如：CEIL(123.45) 返回 124。
• ABS(number): 返回数值的绝对值。例如：ABS(-123) 返回 123。

3. 日期和时间函数

• CURDATE(): 返回当前日期。例如，如果今天是 2024-04-10，则 CURDATE() 返回 2024-04-10。
• NOW(): 返回当前日期和时间。例如，如果现在是 2024-04-10 12:34:56，则 NOW() 返回 2024-04-10 12:34:56。
• DATEDIFF(date1, date2): 返回两个日期之间的天数。例如：DATEDIFF('2024-04-10', '2024-04-01') 返回 9。
• DATE_ADD(date, INTERVAL expr type): 给定日期加上一个时间间隔。例如：DATE_ADD('2024-01-01', INTERVAL 1 MONTH) 返回 2024-02-01。

4. 条件表达式

• IF(expr1, expr2, expr3): 如果 expr1 为真，则返回 expr2；否则返回 expr3。例如：IF(1>0, 'true', 'false') 返回 'true'。
• CASE WHEN condition THEN result [WHEN ...] [ELSE result] END: 条件选择语句。例如：CASE WHEN 1>0 THEN 'true' ELSE 'false' END 返回 'true'。

5. 聚合函数

• SUM(column): 计算数值列的总和。例如，在销售表中，SUM(price) 可以计算总销售额。
• AVG(column): 计算数值列的平均值。例如：AVG(price) 可以计算平均销售价格。
• MAX(column): 返回列中的最大值。例如：MAX(price) 可以找到最高的销售价格。
• MIN(column): 返回列中的最小值。例如：MIN(price) 可以找到最低的销售价格。
• COUNT(column): 返回列中非NULL值的数量。例如：COUNT(*) 可以计算表中的行数。

6. 其他有用的函数

• COALESCE(value1, value2, ...): 返回参数列表中的第一个非NULL值。例如：COALESCE(NULL, NULL, 'first_non_null', NULL) 返回 'first_non_null'。
• NULLIF(value1, value2): 如果两个值相等则返回NULL，否则返回第一个值。例如：NULLIF('A', 'B') 返回 'A'；NULLIF('A', 'A') 返回 NULL。

PostgreSQL数据库函数

1. 字符串函数

• CONCAT(str1, str2, ...): 连接两个或多个字符串。例如：CONCAT('Hello', ' ', 'World') 返回 'Hello World'。
• LENGTH(str): 返回字符串的长度。例如：LENGTH('Hello') 返回 5。
• LOWER(str): 将字符串转换为小写。例如：LOWER('HELLO') 返回 'hello'。
• UPPER(str): 将字符串转换为大写。例如：UPPER('hello') 返回 'HELLO'。
• REPLACE(str, from_str, to_str): 在字符串中替换子串。例如：REPLACE('Hello World', 'World', 'PostgreSQL') 返回 'Hello PostgreSQL'。
• SUBSTRING(str FROM start FOR length): 从字符串中提取子串。例如：SUBSTRING('Hello World' FROM 7 FOR 5) 返回 'World'。
• TRIM([LEADING | TRAILING | BOTH] [characters] FROM str): 去除字符串两侧的指定字符。例如：TRIM(' Hello ') 返回 'Hello'。

2. 数值函数

• ROUND(value, precision): 对数值进行四舍五入到指定的小数位数。例如：ROUND(123.4567, 2) 返回 123.46。
• FLOOR(value): 返回不大于数值的最大整数。例如：FLOOR(123.45) 返回 123。
• CEILING(value): 返回不小于数值的最小整数。例如：CEILING(123.45) 返回 124。
• ABS(value): 返回数值的绝对值。例如：ABS(-123) 返回 123。

3. 日期和时间函数

• CURRENT_DATE: 返回当前日期。例如：CURRENT_DATE。
• CURRENT_TIMESTAMP: 返回当前日期和时间。例如：CURRENT_TIMESTAMP。
• AGE(timestamp1, timestamp2): 返回两个日期之间的间隔。例如：AGE(TIMESTAMP '2024-04-10', TIMESTAMP '2024-01-01') 返回 '3 mons 9 days'。
• DATE_PART('field', timestamp): 提取日期的指定部分。例如：DATE_PART('year', TIMESTAMP '2024-04-10') 返回 2024。

4. 条件表达式

• CASE WHEN condition THEN result [WHEN ...] [ELSE result] END: 条件选择语句。例如：CASE WHEN 1>0 THEN 'true' ELSE 'false' END 返回 'true'。
• COALESCE(value1, value2, ...): 返回参数列表中的第一个非NULL值。例如：COALESCE(NULL, NULL, 'first_non_null', NULL) 返回 'first_non_null'。
• NULLIF(value1, value2): 如果两个值相等则返回NULL，否则返回第一个值。例如：NULLIF('A', 'B') 返回 'A'；NULLIF('A', 'A') 返回 NULL。

5. 聚合函数

• SUM(column): 计算数值列的总和。例如，SUM(price) 可以计算总销售额。
• AVG(column): 计算数值列的平均值。例如：AVG(price) 可以计算平均销售价格。
• MAX(column): 返回列中的最大值。例如：MAX(price) 可以找到最高的销售价格。
• MIN(column): 返回列中的最小值。例如：MIN(price) 可以找到最低的销售价格。
• COUNT(column): 返回列中非NULL值的数量。例如：COUNT(*) 可以计算表中的行数。

6. 数组函数

• ARRAY_LENGTH(array, dimension): 返回数组在指定维度的长度。例如：ARRAY_LENGTH(ARRAY[1,2,3,4], 1) 返回 4。
• ARRAY_APPEND(array, element): 向数组添加元素。例如：ARRAY_APPEND(ARRAY[1,2,3], 4) 返回 {1,2,3,4}。

7. 地理空间函数（GIS）

• ST_Area(geom): 计算地理对象的面积。例如：ST_Area(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')) 返回 1.0。