SQL學習

小张1995發表於2024-11-20

SQL學習網站:https://sqlmother.yupi.icu/#/levels

基礎語法 - 查詢

查詢-全表查詢

使用 select 全表查詢語句,檢視整個學生表的內容

select * from student;

查詢結果:

學號 姓名 年齡
101 小明 20
102 小紅 22
103 小剛 21
104 小麗 19

查詢 - 選擇查詢

使用"選擇查詢"來獲取所有學生的姓名(name)和年齡(age)資訊

select name,age from student

name age
雞哥 25
魚皮 18
熱dog 40
摸FISH
李阿巴 19
老李 56
李變數 24
王加瓦 23
趙派森 80
孫加加 60

查詢 - 別名

從名為 student 的資料表中選擇出所有學生的姓名(name)和年齡(age)資訊,併為它們取別名為 學生姓名學生年齡

select name as 學生姓名,age 學生年齡 from student;

學生姓名 學生年齡
雞哥 25
魚皮 18
熱dog 40
摸FISH
李阿巴 19
老李 56
李變數 24
王加瓦 23
趙派森 80
孫加加 60

查詢 - 常量和運算

從名為student的資料表中選擇出所有學生的姓名(name)和分數(score),並且額外計算出分數的 2 倍(double_score)

select name,score,score * 2 as double_score from student;
name score double_score
雞哥 2.5 5
魚皮 400 800
熱dog 600 1200
摸FISH 360 720
李阿巴 120 240

條件查詢 - where

從名為student 的資料表中選擇出所有學生的姓名(name)和成績(score),要求學生姓名為 '魚皮'。

select name,score from student where name='魚皮';

name score
魚皮 400

條件查詢 - 運算子

名為 student 的資料表中選擇出所有學生的姓名(name)和年齡(age),要求學生姓名不等於 '熱dog' 。

運算子是 SQL 中用於在條件查詢中進行條件判斷的特殊符號,比如 =!=<> 等。透過使用不同的運算子,我們可以在查詢語句中設定多樣化的條件,從而根據資料的不同屬性進行靈活的篩選和過濾。

select name,age from student where name !='熱dog';

name age
雞哥 25
魚皮 18
摸FISH
李阿巴 19

使用 "!=" 運算子篩選出 name 不是 '小張' 的員工

select name, age, salary from employees where name != '小張';

使用 ">" 運算子篩選出工資高於 5500 的員工:

select name, age, salary from employees where salary > 5500;

使用 "BETWEEN" 運算子篩選出年齡在 25 到 30 之間的員工:

select name, age, salary from employees where age between 25 and 30;

條件查詢 - 空值

從名為 student 的資料表中選擇出所有學生的姓名(name)、年齡(age)和成績(score),要求學生年齡不為空值。

在SQL查詢中,我們可以使用 "IS NULL" 和 "IS NOT NULL" 來判斷欄位是否為空值或非空值。

select name,age,score from student where age is not null;

name age score
雞哥 25 2.5
魚皮 18 400
熱dog 40 600

條件查詢 - 模糊查詢

模糊查詢是一種特殊的條件查詢,它允許我們根據模式匹配來查詢符合特定條件的資料,可以使用 LIKE 關鍵字實現模糊查詢。

在 LIKE 模糊查詢中,我們使用萬用字元來代表零個或多個字元,從而能夠快速地找到匹配的資料。

有如下 2 種萬用字元:

  • 百分號(%):表示任意長度的任意字元序列。

  • 下劃線(_):表示任意單個字元。

    從名為 student 的資料表中選擇出所有學生的姓名(name)和成績(score),要求姓名(name)不包含 "李" 這個字。

select name,score from student where name not like '%李%';

我們使用 LIKE 模糊查詢來找出姓名(name)中包含關鍵字 "張" 的員工資訊:

select name, age, position from employees where name like '%張%';

條件查詢 - 邏輯運算

在邏輯運算中,常用的運算子有:

  • AND:表示邏輯與,要求同時滿足多個條件,才返回 true。
  • OR:表示邏輯或,要求滿足其中任意一個條件,就返回 true。
  • NOT:表示邏輯非,用於否定一個條件(本來是 true,用了 not 後轉為 false)

從名為 student 的資料表中選擇出所有學生的姓名(name)、成績(score),要求學生的姓名包含 "李",或者成績(score)大於 500。

select name,score from student where name like '%李%' or score > 500;

使用邏輯運算來找出姓名中包含關鍵字 "李" 年齡小於 30 歲的員工資訊:

select name, age, salary from employees where name like '%李%' and age < 30;

基礎語法 - 去重

在 SQL 中,可以使用 DISTINCT 關鍵字來實現去重操作。

舉個應用場景:假設你是班長,要統計班級中有哪些不同的學生,而不關心他們重複出現的次數,就可以使用去重。

從名為 student 的資料表中選擇出所有不重複的班級 ID(class_id)和考試編號(exam_num)的組合。

select distinct class_id,exam_num from student;

基礎語法 - 排序

在 SQL 中,可以使用 ORDER BY 關鍵字來實現排序操作。ORDER BY 後面跟上需要排序的欄位,可以選擇升序(ASC)或降序(DESC)排列。

-- SQL 查詢語句 1
select name, age from students order by age asc;

-- SQL 查詢語句 2
select name, score from students order by score desc;

按照年齡升序(從小到大):

name age
張三 18
王五 19
李四 20
趙六 20

按照分數降序(從大到小):

name score
王五 92
張三 90
趙六 88
李四 85

從名為 student 的資料表中選擇出學生姓名(name)、年齡(age)和成績(score),首先按照成績從大到小排序,如果成績相同,則按照年齡從小到大排序。

select name,age,score from student order by score desc,age asc;

基礎語法 - 截斷和偏移

在 SQL 中,使用 LIMIT 關鍵字來實現資料的截斷和偏移。

截斷和偏移的一個典型的應用場景是分頁,即網站內容很多時,使用者可以根據頁號每次只看部分資料

從名為 student 的資料表中選擇學生姓名(name)和年齡(age),按照年齡從小到大排序,從第 2 條資料開始、擷取 3 個學生的資訊

name age
魚皮 18
李阿巴 19
王加瓦 23
- LIMIT 後只跟一個整數,表示要截斷的資料條數(一次獲取幾條)
select task_name, due_date from tasks limit 2;

-- LIMIT 後跟 2 個整數,依次表示從第幾條資料開始、一次獲取幾條
select task_name, due_date from tasks limit 2, 2;

查詢語句 1 結果,只獲取了 2 條資料:

task_name due_date
完成報告 2023-08-05
預約醫生 2023-08-08

查詢語句 2 結果,從下標為 2(第 3 條)資料的位置開始獲取 2 條資料:

task_name due_date
購買禮物 2023-08-10
安排旅行 2023-08-15

基礎語法 - 條件分支

使用 case when 可以在查詢結果中根據特定的條件動態生成新的列或對現有的列進行轉換

將學生按照年齡劃分為三個年齡等級(age_level):60 歲以上為 "老同學",20 歲以上(不包括 60 歲以上)為 "年輕",20 歲及以下、以及沒有年齡資訊為 "小同學"。

返回結果應包含學生的姓名(name)和年齡等級(age_level),並按姓名升序排序。

-- 請在此處輸入 SQL
SELECT name,
  CASE
    WHEN (age > 60) THEN '老同學'
    WHEN (age > 20) THEN '年輕'
    ELSE '小同學'
  END AS age_level
FROM
  student
ORDER BY
  name asc;
name age_level
孫加加 年輕
摸FISH 小同學
李變數 年輕
李阿巴 小同學
熱dog 年輕

使用條件分支 case when ,根據 name 來判斷學生是否會說 RAP,並起別名為 can_rap。

示例 SQL 如下:

SELECT
  name,
  CASE WHEN (name = '雞哥') THEN '會' ELSE '不會' END AS can_rap
FROM
  student;

查詢結果:

name can_rap
小明 不會
雞哥
李華 不會
王五 不會

函式 - 時間函式

常用的時間函式有:

  • DATE:獲取當前日期
  • DATETIME:獲取當前日期時間
  • TIME:獲取當前時間

編寫一個 SQL 查詢,展示所有學生的姓名(name)和當前日期(列名為 "當前日期")。

select name,DATE() as "當前日期" from student;

name 當前日期
雞哥 2024-11-14
魚皮 2024-11-14
熱dog 2024-11-14
摸FISH 2024-11-14
李阿巴 2024-11-14

使用時間函式獲取當前日期、當前日期時間和當前時間:

-- 獲取當前日期
SELECT DATE() AS current_date;

-- 獲取當前日期時間
SELECT DATETIME() AS current_datetime;

-- 獲取當前時間
SELECT TIME() AS current_time;

函式 - 字串處理

SQL 中,字串處理是一類用於處理文字資料的函式。它們允許我們對字串進行各種操作,如轉換大小寫、計算字串長度以及搜尋和替換子字串等。字串處理函式可以幫助我們在資料庫中對字串進行加工和轉換,從而滿足不同的需求。

編寫一個 SQL 查詢,篩選出姓名為 '熱dog' 的學生,展示其學號(id)、姓名(name)及其大寫姓名(upper_name)。

select id,name,UPPER(name) as upper_name from student where name='熱dog'

使用字串處理函式 UPPER 將姓名轉換為大寫:

-- 將姓名轉換為大寫
SELECT name, UPPER(name) AS upper_name
FROM employees;

查詢結果:

name upper_name
小明 小明
熱dog 熱DOG
Fish摸摸 FISH摸摸
雞哥 雞哥

2)使用字串處理函式 LENGTH 計算姓名長度:

-- 計算姓名長度
SELECT name, LENGTH(name) AS name_length
FROM employees;

查詢結果:

name name_length
小明 2
熱dog 4
Fish摸摸 6
雞哥 2

3)使用字串處理函式 LOWER 將姓名轉換為小寫:

-- 將姓名轉換為小寫並進行條件篩選
SELECT name, LOWER(name) AS lower_name
FROM employees;

查詢結果:

name lower_name
小明 小明
熱dog 熱dog
Fish摸摸 fish摸摸
雞哥 雞哥

函式 - 聚合函式

常見的聚合函式包括:

  • COUNT:計算指定列的行數或非空值的數量。

  • SUM:計算指定列的數值之和。

  • AVG:計算指定列的數值平均值。

  • MAX:找出指定列的最大值。

  • MIN:找出指定列的最小值。

    編寫一個 SQL 查詢,彙總學生表中所有學生的總成績(total_score)、平均成績(avg_score)、最高成績(max_score)和最低成績(min_score)

    select
      SUM(score) as total_score,
      AVG(score) as avg_score,
      MAX(score) as max_score,
      MIN(score) as min_score
    from
      student;
    

假設有一個訂單表 orders,包含以下欄位:order_id(訂單號)、customer_id(客戶編號)、amount(訂單金額)。資料如下:

order_id customer_id amount
1 A001 100
2 A002 200
3 A001 150
4 A003 50

1)使用聚合函式 COUNT 計算訂單表中的總訂單數:

SELECT COUNT(*) AS order_num
FROM orders;

查詢結果:

order_num
4

2)使用聚合函式 COUNT(DISTINCT 列名) 計算訂單表中不同客戶的數量:

SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;

查詢結果:

customer_num
3

3)使用聚合函式 SUM 計算總訂單金額:

SELECT SUM(amount) AS total_amount
FROM orders;

查詢結果:

total_amount
500

分組聚合 - 單欄位分組

在 SQL 中,通常使用 GROUP BY 關鍵字對資料進行分組。

編寫一個 SQL 查詢,統計學生表中的班級編號(class_id)和每個班級的平均成績(avg_score)。

-- 請在此處輸入 SQL
select class_id,AVG(score) as avg_score from student group by class_id
class_id avg_score
1 201.25
2 480
3 310
4 330
5 100.5

分組聚合 - 多欄位分組

多欄位分組和單欄位分組的實現方式幾乎一致,使用 GROUP BY 語法即可。

編寫一個 SQL 查詢,統計學生表中的班級編號(class_id),考試次數(exam_num)和每個班級每次考試的總學生人數(total_num)

select
  class_id,
  exam_num,
  count(*) as total_num
from 
  student
group by
  class_id,
  exam_num;
class_id exam_num total_num
1 1 1
1 4 1
2 4 2
3 2 1

查詢使用多欄位分組查詢表中 每個客戶 購買的 每種商品 的總金額,相當於按照客戶編號和商品編號分組:

-- 查詢每個使用者購買的每種商品的總金額,按照客戶編號和商品編號分組
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;

查詢結果:

customer_id product_id total_amount
A001 1 250
A001 2 50
A002 1 200
A003 1 50

分組聚合 - having 子句

在 SQL 中,HAVING 子句用於在分組聚合後對分組進行過濾。它允許我們對分組後的結果進行條件篩選,只保留滿足特定條件的分組

請你編寫一個 SQL 查詢,統計學生表中班級的總成績超過 150 分的班級編號(class_id)和總成績(total_score)。

select
  class_id,
  SUM(score) as total_score
from
  student
group by
  class_id
having
  SUM(score) > 150;

假設有一個訂單表 orders,包含以下欄位:order_id(訂單號)、customer_id(客戶編號)、amount(訂單金額)。資料如下:

order_id customer_id amount
1 A001 100
2 A002 200
3 A001 150
4 A003 50

1)使用 HAVING 子句查詢訂單數超過 1 的客戶:

SELECT customer_id, COUNT(order_id) AS order_num
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

查詢結果:

customer_id order_num
A001 2

2)使用 HAVING 子句查詢訂單總金額超過 100 的客戶:

-- 查詢訂單總金額超過100的客戶
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

查詢結果:

customer_id total_amount
A001 250
A002 200

查詢進階 - 子查詢

子查詢是指在一個查詢語句內部 巢狀 另一個完整的查詢語句,內層查詢被稱為子查詢。子查詢可以用於獲取更復雜的查詢結果或者用於過濾資料。

寫一個 SQL 查詢,使用子查詢的方式來獲取存在對應班級的學生的所有資料,返回學生姓名(name)、分數(score)、班級編號(class_id)欄位。

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

假設我們有以下兩個資料表:orderscustomers,分別包含訂單資訊和客戶資訊。

orders 表:

order_id customer_id order_date total_amount
1 101 2023-01-01 200
2 102 2023-01-05 350
3 101 2023-01-10 120
4 103 2023-01-15 500

customers 表:

customer_id name city
101 Alice New York
102 Bob Los Angeles
103 Charlie Chicago

現在,我們希望查詢出訂單總金額 > 200 的客戶的姓名和他們的訂單總金額,示例 SQL 如下:

-- 主查詢
SELECT name, total_amount
FROM customers
WHERE customer_id IN (
    -- 子查詢
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 200
);

在上述 SQL 中,先透過子查詢從訂單表中過濾查詢出了符合條件的客戶 id,然後再根據客戶 id 到客戶資訊表中查詢客戶資訊,這樣可以少查詢很多客戶資訊資料。

上述語句的查詢結果:

name total_amount
Bob 350
Charlie 500


關注小張的知識雜貨鋪,讓我們一起學習一起進步

相關文章