SQL mother查詢語句

faf4r發表於2024-03-09
SQL

題目教程網址：SQL之母

基礎查詢

select * from student;

select
    xxx, xxxx as name, ddd,
    xxx*2 as double_xxx
from
    a_table
where
    xx>10 or dd!=1 and kk=9;

別名，常量和運算，where，運算子(=,!=,>,<)，邏輯運算(and,or,not)。

模糊查詢

select something from a_table
where value like '%值%';

模糊查詢用like或not like。
'%值'匹配開頭，'值%'匹配結尾，'%值%'代表包含'值'。

空值

select something from a_table
where value is not null;

空值用null，和Python類似。

去重

select distinct A, B, C from a_table;

去重就是select distinct這樣固定搭配，會按照後面的欄位組合進行去重。

排序

select something from a_table
order by value desc, another_value asc;

排序根據給定的欄位順序進行排序，預設asc是升序，desc是降序。

截斷和偏移

select something from a_table
order by value desc, another_value asc
limit 0, 3;

limit m, n從第m個開始取，取n個結果(索引從0開始)。
limit n 從第0個開始取，取n個結果(索引從0開始)。

條件分支

SELECT
  name,
  CASE
    WHEN (age > 60) THEN '老同學'
    WHEN (age > 20) THEN '年輕'
    ELSE '小同學'
  END AS age_level
FROM
  student;

case ... end，裡面把when當if那樣用，then或else決定值是多少。
整個是決定一個欄位的值。

函式

函式名 功能
date() 返回此刻日期
time() 返回當前時間
datetime() 返回date＋time的組合
upper(field) 將指定列大寫
lower(field) 將指定列小寫
length(field) 求字元長度
max(field) 求該列最大值
min(field) 求該列最小值
sum(field) 求和
avg(field) 求平均
count(...) 計算指定列的行數或非空值的數量。

分組聚合

SELECT
  class_id,
  exam_num,
  COUNT(*) AS total_num
FROM
  student
GROUP BY
  class_id,
  exam_num;

SELECT
  class_id,
  SUM(score) AS total_score
FROM
  student
GROUP BY
  class_id
HAVING
  SUM(score) > 150;

根據group by後面的組合進行分組，整個語句的查詢就是在每一個組裡面單獨執行。
having是類似於where的篩選，但WHERE子句用於在分組之前進行過濾，而HAVING子句用於在分組之後進行過濾。

關聯查詢

-- cross join
select
  s.name student_name,
  s.age student_age,
  s.class_id class_id,
  c.name class_name
from
  student s,
  class c;

-- inner join
select
  s.name student_name,
  s.age student_age,
  s.class_id class_id,
  c.name class_name,
  c.level class_level
from
  student s
  join class c on s.class_id = c.id;

-- outer join
select
  s.name student_name,
  s.age student_age,
  s.class_id class_id,
  c.name class_name,
  c.level class_level
from
  student s
  left join class c on s.class_id = c.id;

將兩個表組合在一起輸出(table.field用於定位具體列，用別名簡化程式碼)
cross join簡單粗暴。
inner join用on來指定兩個表的交集，只輸出交集部分。
outer join加了個left，將完整輸出主表的資料，即使不在交集中。(from後是主表，join後是關聯表，不關聯的部分值為null)

子查詢

select
  name,
  score,
  class_id
from
  student
where
  class_id in (
    select distinct
      id
    from
      class
  );

-- 主查詢
SELECT name, total_amount
FROM customers
WHERE EXISTS (
    -- 子查詢
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

現在子查詢中查出一個表，再根據這個表查詢
in，not in，exist， not exist

組合查詢

-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;

UNION 操作：它用於將兩個或多個查詢的結果集合並， 並去除重複的行 。即如果兩個查詢的結果有相同的行，則只保留一行。
UNION ALL 操作：它也用於將兩個或多個查詢的結果集合並， 但不去除重複的行 。即如果兩個查詢的結果有相同的行，則全部保留。

開窗函式

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
    orders;

用over聚焦到分組，它允許我們在查詢中進行對分組資料進行計算、 同時保留原始行的詳細資訊 。即僅在這個地方用分組，但不影響全域性。
partition by進行分組，order by進行排序，排序後它應該是隻取該組該條record前面順序的。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
    orders;

rank() over 分組排名，相同的值將被賦予相同的排名，當存在並列（相同排序值）時，Rank 會跳過後續排名，並保留相同的排名。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROM
    orders;

Row_Number 開窗函式是 SQL 中的一種用於為查詢結果集中的每一行 分配唯一連續排名 的開窗函式。

SELECT 
    student_id,
    exam_date,
    score,
    LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
    LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    scores;

Lag 函式用於獲取 當前行之前 的某一列的值。它可以幫助我們檢視上一行的資料。
Lead 函式用於獲取 當前行之後 的某一列的值。它可以幫助我們檢視下一行的資料。

LAG(column_name, offset, default_value)。
column_name：要獲取值的列名。
offset：表示要向上偏移的行數。例如，offset為1表示獲取上一行的值，offset為2表示獲取上兩行的值，以此類推。
default_value：可選引數，用於指定當沒有前一行時的預設值。
PARTITION BY和ORDER BY子句可選，用於分組和排序資料。

相關文章