SQL知识与练习

目录

• SQL知识与练习
  • 日期
  • ROW_NUMBER、SUBSTR、SUBSTR和COALESCE方法
  • LIKE、INNER JOIN、LEFT OUTER JOIN
  • postgresql

SQL知识与练习

日期

-- 查询当年 当月 当天 过去七天  某个日期后的 30 天
-- SELECT * FROM ods_bctulmarket WHERE businessdate BETWEEN '2024-01-01' AND '2024-12-31';
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= DATE_TRUNC('year',CURRENT_DATE);
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= DATE_TRUNC('month',CURRENT_DATE);
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= NOW(); 
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= NOW()- INTERVAL '7 days';  
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= NOW() - INTERVAL '300 days';
--
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= Date '2024-01-01' + INTERVAL '29 days';
-- 
-- SELECT * FROM ods_bctulmarket WHERE EXTRACT(YEAR FROM businessdate) = 2023;
-- SELECT * FROM ods_bctulmarket WHERE EXTRACT(MONTH FROM businessdate) = 2;

ROW_NUMBER、SUBSTR、SUBSTR和COALESCE方法

--解读  ROW_NUMBER() 为 document_id 生成序号时，通常需要一个排序依据
	ods_document_tmp as ( -- 生成凭证号
	 SELECT instruction_no, ROW_NUMBER ( ) OVER ( ) AS "document_id" FROM order_1 GROUP BY instruction_no
),
   ROW_NUMBER() OVER (ORDER BY instruction_no)：在每个唯一的 instruction_no 组内为每行生成唯一的序号，且按 instruction_no 排序。 
   
--解读
    SUBSTR( REPLACE ( cast (bill_date as text), '-', '' ), 1, 6 ) || odt.document_id AS "document_id",
    cast (bill_date as text) 将时间转为文本
    REPLACE(..., '-', '')：去除 bill_date 中的所有 - 符号，例如将 2024-10-24 转换为 20241024
    SUBSTR(..., 1, 6)：截取转换后的字符串前 6 个字符,如202410
    || odt.document_id：将截取的年月字符串与 odt.document_id 进行字符串拼接 如20241000007  
    odt就是ods_document_tmp
    
    --保留两位小数
    COALESCE(round( "origin_currency", 2 ),0) AS "origin_currency",
    COALESCE(..., 0)：如果 origin_currency 是 NULL，则返回 0，否则返回经过 ROUND 处理的 origin_currency 值。
     
  ---sql不区分大小写，需要”“ 保证大小写

LIKE、INNER JOIN、LEFT OUTER JOIN

SELECT * FROM table_test WHERE quantity > 10000 and trade_date >'2024-11-1';

SELECT * FROM table_test WHERE trade_date  BETWEEN '2024-10-30' AND '2024-11-4';

SELECT * FROM "Pro_Product" WHERE "number" LIKE 'OCO%' -- 以OCO开头的数据

SELECT * FROM "Pro_Product"  WHERE "number" LIKE '%TAC7'  -- 以TAC7结尾的数据

SELECT * FROM "Pro_Product"  WHERE "number" LIKE '_C%17'  -- C在第二个位置并且以17结尾的数据
 
SELECT * FROM table_test WHERE quantity::TEXT LIKE '527%'  ---数量以527开头的数据

SELECT * FROM table_test WHERE account  NOT IN ('41183','53378')

SELECT * FROM table_test WHERE quantity IS NOT NULL

SELECT quantity FROM table_test WHERE quantity>300000
 
SELECT * FROM table_test LIMIT 5 OFFSET 2  -- 第三位开始提取 3 个记录

SELECT * FROM table_test ORDER BY quantity,account ASC  -- 升序排列  降序 DESC

SELECT  account, "sum"(quantity) AS total FROM table_test GROUP BY account
SELECT  account, SUM(quantity) AS total FROM table_test GROUP BY account  --   GROUP 必须和聚合函数一起使用 sum AVG COUNT
SELECT  account, SUM(quantity) AS total FROM table_test GROUP BY account ORDER BY account ASC 
SELECT  account FROM table_test GROUP BY account HAVING COUNT(account) >1; --account 字段值进行分组，并且名称的计数大于 1 数据

SELECT DISTINCT account FROM table_test


SELECT account FROM table_test 
WHERE account IN  (SELECT account,quantity FROM table_test WHERE quantity >300000);

--CROSS JOIN ：交叉连接     -- 第一个表的每一行与第二个表的每一行进行匹配
--INNER JOIN：内连接     --连接谓词结合两个表的列值来创建一个新的结果表
--LEFT OUTER JOIN：左外连接  --执行内部连接。连接的表在 T1 中每一行至少有一行 ,不满足条件有null也会添加一个连接行
--RIGHT OUTER JOIN：右外连接  --执行内部连接。对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行
--FULL OUTER JOIN：全外连接   -- 执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

SELECT * FROM table_test 
INNER JOIN "Account_Maping" ON table_test.account = "Account_Maping"."ExternalAccountNumber";

SELECT  * FROM table_test 
LEFT OUTER JOIN  "Account_Maping" ON table_test.account = "Account_Maping"."ExternalAccountNumber"


SELECT * FROM  "Account_Maping" LIMIT 5;

 SELECT  COALESCE ( acct."AccountId", '待确认' ) AS tradingaccount,...FROM -- 将acct表中的AccountId 重新命名为tradingaccount，如果没有找到就会返回待确认

postgresql

 ps aux |grep port=5432

# 更新test表中的age字段数据类型由bit ->integer
ALTER TABLE test
ALTER COLUMN age TYPE INTEGER USING age::integer;

# 删除表中的数据--》sql工具
delete from test where name='k'; 删除name为k的记录 

# TRUNCATE 命令来清空表：
TRUNCATE table_name;

# 下面实例将找出 AGE 以 2 开头的数据：
 SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
            
# 将找出 address 字段中含有 - 字符的数据：
 SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';
    
# 找出限定的数量的数据，即读取 4 条数据：
 SELECT * FROM COMPANY LIMIT 4;
  
# ASC 表示升序，DESC 表示降序
	AGE 字段值进行升序排列：
	SELECT * FROM COMPANY ORDER BY AGE ASC;