資料庫設計 - 有效的使用系統資料 - 第五章 SQL 語法

單元 1 Select 撈取資料庫資料的關鍵字

select <欄位名稱>
from <資料庫.table 名稱>
where <條件>;

select *
from orders
where costomerid = 3;

在 workbench 要先指定 orders 資料庫

orders 變成粗體

如果指定到 sys 資料庫會發生 error

補充：

  1. 單純寫 select 就是列印

  2. 資料量大時避免使用 *，要寫出欄位名稱

  3. 搜尋資料時，加入條件增加搜尋效率，條件語法

    1. where

      select * from orders 
where customerid = 10;

      Django 語法

      Orders.objects.get(customer_id = 10)

    2. limit

      select * from orders 
limit 10;

      Django 語法

      returns the first 5 objects (LIMIT 10):

      **>>>** Orders**.**objects**.**all()[:10]

      This returns the sixth through tenth objects (OFFSET 5 LIMIT 10):

      **>>>** Orders**.**objects**.**all()[5:15]

      Generally, slicing a [QuerySet]returns a new [QuerySet]– it doesn’t evaluate the query. An exception is if you use the “step” parameter of Python slice syntax. For example, this would actually execute the query in order to return a list of every second object of the first 10:

      **>>>** Orders**.**objects**.**all()[:10:2]

    3. and

      select * from orders 
where customerid = 10 and employeeid = 4;

      Django 語法

      Orders.objects.filter(customer_id = 10, employee_id = 4)

作業練習

找出 customerid = 87, shipperid = 3 的資料

Django 語法

Orders.objects.filter(customer_id = 87, shipper_id = 3)

單元 2 Join 多個表格連接在一起的做法

關鍵！
利用主鍵與外鍵，把 table 串在一起做搜尋：將兩個 table 結合在一起，在 where 設定結合條件來執行
select * from A, B
where A.id = B.A_id;

Django 語法

B.objects.all().prefetch_related('A_id')

練習

1. Orders & OrderDetails 結合

SELECT * FROM orders, orderdetails
WHERE orders.orderid = orderdetails.orderid;

Django 語法

OrderDetails.objects.all().prefetch_related('orders_id')

2. Orders & Customers 結合

select * from orders, customers 
where orders.CustomerID = customers.CustomerID;

Django 語法

Orders.objects.all().prefetch_related('customer_id')

單元 3 排序

order by

  • asc 小 → 大
  • desc 大 → 小
select * from A
order by column1 desc;

練習

  1. 排序員工的生日

    SELECT * FROM employees
order by birthdate asc;

    Django 語法

    Employees.objects.order_by('-birthdate')

  2. 產品價格 大 → 小 排序

    select * from products 
order by price desc;

    Django 語法

    Products.objects.order_by('-price')

    • price 前的 ‘-’ 代表 descending
    • 沒有 ‘-’ 代表 ascending

  3. 員工生日 小 → 大

    SELECT * FROM employees
order by birthdate asc;

    Django 語法

    Employees.objects.order_by('birthdate')

單元 4 將特定欄位中相同的資料作為分組

Group by

  • 資料分組

  • 當我們搜尋的欄位中，包含函數的運算時，我們就會使用 group by

  • List Table

    item  price
     餅乾  20
     洗髮精  100
     餅乾  20

Django 語法是 values()

sum() 函式

可以將括弧中的欄位加總
如果不用 group by
會將 price 價格加總為一個數字 = 140
select item, sum(price)
from list;

select item, sum(price)
from list
group by item;

試算所有供應商提供的報價總和

SELECT supplierid, sum(price) FROM products
group by SupplierID;

作業練習

找出賣最好的商品

  • 看 orderdetail 的相同 product 總金額最高的
  1. 將 orderdetails 與 products table join
  2. group by productid 查看其 sum(price)
  3. 按照高到低排序 order by sum(price) desc
SELECT orderdetails.ProductID, products.ProductName, sum(price) FROM orderdetails, products
where orderdetails.ProductID = products.ProductID
group by products.ProductID
order by sum(price) desc;

單元 5 資料分組後加入條件

having

過濾條件，控制哪些組可以出現在最後結果

SELECT orderdetails.ProductID, products.ProductName, sum(price) FROM orderdetails, products
where orderdetails.ProductID = products.ProductID
group by products.ProductID
having sum(price) > 500
order by sum(price) desc;

having 要放在最後

但如果有 order by ，要放在最後是因為，撈出來的資料呈現，要按照 order by 排序出最後的結果。

練習 having > 400 or 300

單元 6 欄位名字太長，表單名字太複雜，暫時取暱稱

as

用來指定表格別名或欄位別名

select a.col_1 as name //(結果呈現會用 name 呈現)
from Tabel_1 as a, Table_2 as b
where a.id = b.table1_id

練習

可以不輸入 as 即可使用

  1. 改變 table 名稱

    select * from orderdetails d, orders o
where o.OrderID = d.OrderID;

  2. 改變 column 名稱

    select o.orderid order_id from orderdetails d, orders o
where o.OrderID = d.OrderID;

    orderid → order_id

  3. 數量 * 價格，金額最高的產品是哪一個

    SELECT p.ProductID product_id, p.productName product_name, sum(p.price * od.quantity) Total
FROM products p, orderdetails od
where p.ProductID = od.ProductID
group by product_id
order by Total desc;

單元 7 不曉得完整、精確資料內容，也可以找出大概可能的結果

like

  • %
  • 可以找出相同模式的資料
select * from Table
where name like 'C%';

練習

SELECT * FROM employees
where LastName like '%a%';

SELECT * FROM employees
where LastName like 'D%';

單元 8 搜尋出特定範圍的日期或數字資料

between … and

  • between 可以找出一個範圍內的資料
select * 
from Table
where <欄位名稱> between 1 and 10;

SELECT * from products
where price between 20 and 30;

SELECT * FROM orders
where orderdate between '1996-07-08 00:00:00' and '1996-07-15 00:00:00';

Django 語法

用 range=(start, end)

filter(欄位名稱__range=(start, end))

單元 9 元素比較

in/not in

  • 找出符合 in 元素中的資料
select *
from TABLE
where <欄位名稱> in (5, 8);

練習

  1. 找出 product table 中 product id 為 2 或 4 的資料

    SELECT * FROM orders.products
where productid in (2, 4);

  2. 找出 product table 中 categoryid 不為 2 跟 4 的資料

    SELECT * FROM orders.products
where CategoryID not in (2, 4)
order by CategoryID asc;

單元 10 子查詢

  • 在一個 SQL 查詢敘述中，再放入另一個 SQL 查詢
  • 子查詢要用括弧包起來()
  • 在使用子查詢的時候也可以使用外部 SQL 的 Table 與欄位

  1. 子查詢放在 from

    子查詢的結果為一張 table，所以直接當 table 使用

    select *
from (子查詢) as <名稱>
where <條件>;

  2. 子查詢放在 where 條件

    此處之(子查詢)可以使用 Table_1 欄位的資料

    select *
from Table_1
where <欄位名稱> in (子查詢);

  3. 子查詢放在 select

    查詢出來的結果當成一個欄位

    select *, (子查詢)
from Table_1;

練習

找出包含 productid = 12 的訂單 order

發現有百百種方法

  1. join orders & orderdetails 兩張 table 後，找出 productid = 12

    select * 
from orders o, orderdetails od
where o.orderid = od.OrderID and productid = 12;

  2. 查詢出 orderdetails 中 productid = 12 的結果後，再與 orders Table join 在一起

    SELECT * FROM orders, (SELECT * FROM orderdetails
where productid = 12) od_p
where orders.orderid = od_p.orderid;

  3. （老師版本）子查詢放在 where

    select * 
from orders o
where o.OrderID in (
select o.OrderID 
from orderdetails od
where od.orderid = o.orderid
and od.productid=15
);

作業練習

使用 orderdetails 的資料，查出訂單明細與產品名稱

  • 請在 SQL 中的 SELECT 放入子查詢，查詢產品名稱

原本 join 的方式

select OrderDetailID, ProductName from orderdetails od, products p
where od.ProductID = p.ProductID;

改成老師需求的方式

想法

  1. 把 orderdetails table 加上 一欄 Product Name → 用 orderdetails table

    select *
from orderdetails od;

  2. 從 products table 撈出 productname 欄位

    select productname
from products p;

  3. products 中的 productid 要與 orderdetails 中的 productid 對齊

    select productname
from products p
where p.productid = od.productid;

    這邊的 orderdetail 可以用外部 SQL 語法取的，也就是 od

  4. 把第三步完成的子查詢加入第一步的查詢中，只需顯示 orderdetailid & productname 兩個欄位即可

    select od.OrderDetailID, (select ProductName from products p where od.ProductID = p.ProductID)
from orderdetails od;

單元 11 搜尋資料時用 exists 會更有效率

用來判斷子查詢有沒有查詢結果

回傳 True/False

select *
from Table_1

where exists (子查詢)
如果子查詢有結果，exists 就會回傳 True
True 會執行外面 SQL 結果
如果 False 外面 SQL 就不會執行

練習

在 orders 表單中列出 productid = 12 的 orderid

  • 在 in 章節中教了 in 的用法

    1. order table

      select *
from orders o
where o.orderid in (子查詢)

    2. 子查詢要找出 orderdetails table 中 productid = 12 的 orderid

      select od.orderid
from orderdetails od
where productid = 12;

    3. 將步驟 2 的子查詢放入步驟 1

      select *
from orders o
where o.orderid in 
(select od.orderid
from orderdetails od
where productid = 12);


exists 用法

  1. 在 orders table 中查找

    select *
from orders o
where exists 
(子查詢)

  2. 子查詢為 orderdetail 中 productid=12 的 orderid，其中 order.orderid = orderdetail.orderid

    select od.orderid
from orderdetails od
where o.orderid = od.orderid and od.productid=12

    o 是利用外部的 SQL 語法

  3. 將 1 與 2 的子查詢結合

    select *
from orders o
where exists 
(select od.orderid
from orderdetails od
where od.orderid = o.orderid and od.productid=12);

💡 建議常用 exists 關鍵字做兩張 Table 間的資料搜尋，會比較有效率

單元 12 新增資料

insert into Table(column1) values(1)

將資料塞入資料庫中

  1. 創建全新的資料：values

    insert into TABLE(column1, column2)
values (value1, value2);

  2. 用子查詢的方式將資料塞入到指定的 table 與欄位

    insert into TABLE_A(ta_column1, ta_column2)
select new_id, tb_column1, tb_column2
from TABLE_B
where 條件;

    ex 用 supplierid = 2 的 Name 與 Phone 建立新的 shipper:

    insert into shippers(shipperid, shippername, phone)
select 7, SupplierName, Phone
from suppliers
where SupplierID = 2;

作業

將名字塞入 employee table 中

insert into employees(employeeid, LastName, FirstName, BirthDate, Notes)
values (12, "Tsui", "Seraphine", "2000-01-01", "me");

單元 13 修改資料

update Table set column = where;

更新資料庫資料

update TABLE
set column1 = ""
where id = 3;

ex :

update shippers
set shipperName = "ABC"
where shipperid=5;

單元 14 刪除資料

delete from Table where

將資料庫中的資料刪除

delete from Table
where 條件;

ex 刪除 shipper table 中 shipperid = 4 的資料:

delete from shippers
where shipperid = 4;

Comments

Post a Comment

Popular posts from this blog

《 Imgproxy 使用分析一：圖片下載速度優化分析：Akamai CDN vs Imgproxy 效能比較》

Image
前言 打造了一顆社群 App ，內有一堆圖片在 load 的時候會黑黑的，過一陣子才 load 得出來 目標 解決這個會讓用戶體驗不佳的問題，很快滑過去也不能看到黑黑的！ 方法 網上 Survey (Chat GPT) 了一下， 決定試試壓縮圖片，看能不能讓下載速度更快。 壓縮的方式選用 Imgproxy 架在 AWS Loadbalancer 層級，人如其名，就是把它當成 proxy 使用。 Source Origin 為 S3，前面也加了一層 CDN。 因此拿圖的流程會長這樣 圖1. cdn imgproxy s3 plantUML 接著就是看這樣的設計流程可不可以讓我們拿圖變快囉！ 在開始之前，需要先知道 從 S3 拉圖跟透過 imgproxy 從 S3 拉圖的速度差 才有比較的基準值，畢竟如果 imgproxy 本身就存在下載速度限制的話，再怎麼壓縮，拉圖就是會慢啊！ 實驗設計 假設 Imgroxy 可以幫我加速圖片下載速度！ 方法 比較各種情境的下載速度是否有差異： S3 vs Imgproxy  →  S3 Akamai CDN  →  S3 vs Imgproxy  →  S3 Akamai CDN  →  S3 vs CloudFront CDN  →  Imgproxy  →  S3 第一次緩存建立 CloudFront  CDN  →  Imgproxy  →  S3 與第二三次 緩存建立後 CloudFront  CDN  →  Imgproxy  →  S3，下載速度差 Akamai CDN  →  S3 vs 緩存建立後的 CloudFront  CDN  →  Imgproxy  →  S3 測試環境 星巴克 wifi, AWS S3,  AWS  Loadbalancer (Imgproxy),  AWS  CloudFront,  Akamai CDN 後面的 S3 前面的 CDN...
Read more

《 Akamai + S3 與 CloudFront + Imgproxy + S3 使用分析二：壓縮圖片設計流程：檔案大小 vs 載入時間的權衡》

Image
要透過 Imgproxy 客製化每種圖檔要被壓縮的方法，真的超難啦！ 目標 將圖片壓縮到 50 KB、250 KB、500 KB、1 MB 等不同檔案大小。 對於已小於目標大小的圖片，不進行壓縮以避免檔案變大。 支援多種圖片格式（PNG、JPG、AVIF 等），依據需求選擇最佳壓縮格式。 提供不同解析度的圖片給前端，讓前端可以根據用戶網速自動切換合適檔案大小。 壓縮設計 目的 利用 Imgproxy 壓縮圖檔下載是否可以有效增加下載圖片效率 步驟 1：判斷檔案大小與格式 從 Akamai_S3 下載原始圖片後，先檢查檔案大小與格式。 如果檔案小於欲壓縮格式目標大小 （例如欲壓縮至 250 KB，但檔案只有 70 KB），則該格式 不做處理 ，繼續跳往下一級 (50 KB)。 如果檔案大於目標大小，進行壓縮或格式轉換。 步驟 2：選擇壓縮策略與格式 優先順序： JPG (JPEG) ：適合照片、色彩豐富的圖片。壓縮率高但會有損失。 AVIF / WEBP ：壓縮率更高，幾乎無損，適合支持新格式的瀏覽器。 PNG ：適合需要透明背景的圖片。壓縮率較低但無損壓縮。 選擇策略： PNG → JPG ：如果 PNG 沒有透明背景且圖片過大，轉成 JPG 可以大幅減少檔案大小。 AVIF → AVIF ：AVIF 檔案通常已經很小，不需要轉換，僅調整品質。 JPG → JPG ：降低品質進行壓縮。 GIF → WEBP ：動態 GIF 可轉成 WEBP，通常能縮小 30-50%。 步驟 3：壓縮參數設定 JPG： quality=70-80 是較理想的平衡點， 70 可顯著減少檔案大小，畫質影響有限。 PNG： 使用無損壓縮，或轉換成 JPG 若透明度不重要。 WEBP / AVIF： quality=60-70 仍能保持優異畫質，檔案非常小。 根據以上制定規則，以下為測試參數搭配在不同檔案類型下有最佳效果的測試 png: > 1MB: q:80/rs:fit:1920:1080 500 KB - 1 MB: q:70/rs:fit:1280:720 250 KB - 500 KB: q:60/rs:fit:800:600 50 KB - 250KB: q:50/rs:fit:400:300 jpeg or jpg > 1MB: q:80/mb:1000000...
Read more

PostgreSQL 具有的 NoSQL 特性

Image
了解了 RDBMS 與 NoSQL 的差異之後，我們來了解 PostgreSQL 除了本是 RDBMS 之外，還包含了哪些 NoSQL 的特性： PS: 可依下列步驟先在地端用 docker 啟一個 postgreSQL database 1. pull posgres image and run docker run -d \ --name postgres-db \ -e POSTGRES_USER=postgres \ -e POSTGRES_PASSWORD=password \ -e POSTGRES_DB=postgres \ -p 5432:5432 \ postgres 2. 建立一個 demo database psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE nosql_demo;" 3. 連線 psql -h localhost -p 5432 -U postgres -W -d nosql_demo 4. 輸入密碼 password Password: Document types 1. XML documents 是一種 Hierarchical structured 1. 用 xml text 建立資料 Examples: // create xmltest table CREATE TABLE xmltest ( data xml NOT NULL ); // 插入一筆 xmltest 資料 INSERT INTO xmltest (data) VALUES ( '<attendee><bio> <name>John Doe</name> <birthYear> 1986 </birthYear></bio><languages> <lang level = "5" >php</lang><lang level = "4" >python</lang> <lang level = "2" ...
Read more