菜比八寫後端(2) - MySQL語法

#MySQL

Posted by TempuraEngineer on 2023-09-18

目錄


名詞解釋

關於資料庫

名詞 解釋 其他
DB server(database server) 安裝且啟用了DBMS的伺服器(裝了DBMS且啟用的電腦也算)
DB(database) table和資料的集合體
DBMS(database management system) 管理資料庫的系統，安裝在資料庫伺服器上 ex: MySQL
schema 資料庫的藍圖，描述一資料庫由哪些物件組成 MySQL的Schema等於DB
table 儲存資料的地方 view
column 定義table儲存什麼屬性的資料
row 儲存一筆資料的地方


關於動作

名詞 解釋 其他
transaction(交易) 一系列的操作DB的動作，組合起來的可以獨立運作的單位，常控制成: 要就全部執行成功，否則就全部執行失敗 使用START TRANSACTION啟動transaction，最後用COMMIT敘述讓因為transaction產生的table的改變確實生效
commit 交易成功時讓table改變生效的動作
rollback 交易失敗時把剛剛動作復原的動作


關於語句 & function

名詞 解釋 其他
子語言 分為DML、TCL、DQL、DDL、DCL 5種
敘述(statement) 子語言底下又各自分成幾種敘述
子句(cause) 敘述又可以拆成幾個子句
DDL 資料定義語言，用於建立資料庫，對庫表的結構進行刪除和修改等操作 create table、alter table、drop table、truncate table
DML 資料操作語言，用於新增、刪除、修改Table內的資料
insert into、delete from、update
single row function(單一資料列函數) 單一資料列函數會對每一個row的進行操作 ex: CONCAT()、UPPER()、LENGTH()、DATE_FORMAT()
aggregate functions(多重資料列函數、聚合函數) 用於計算的function ex: COUNT()、MIN()、AVG()、SUM()


建立table

CREATE TABLE `books` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `price` INT UNSIGNED NULL,
  `publishedDate` INT NOT NULL,
  `authorId` INT NOT NULL,
  `language` VARCHAR(45) NOT NULL DEFAULT 'zh-tw',
  `translatorId` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));


新增資料

INSERT INTO books
    (name, price, publishedDate, authorId, publisherId, language)
    VALUES
    ('Clean Code: A Handbook of Agile Software Craftsmanship', 1000, now(), 1, 1, 'en');

如果想要自訂時間也可以把now換成Date('2011/11/01')


更新資料

全部都設成某個值

UPDATE 
    libraries 
SET 
    serviceTimes = '{\"MON\":\"08:00~17:00\", \"TUE\":\"08:00~22:00\",\"WED\":\"08:00~22:00\", \"THUR\":\"08:00~22:00\", \"FRI\":\"08:00~22:00\", \"SAT\":\"08:00~22:00\", \"SUN\":\"00:00~00:00\"}' WHERE (`id` = '1');

根據條件把某幾個資料欄位設成某個值

UPDATE
    books 
SET 
    libraryId = 4 
Where id IN (14,21);


撈資料

select

select敘述是撈資料的核心

select * from books;


select name from books;


order by

DESC為遞減
ASC則為遞增

null值排序會被視為最小

SELECT DISTINCT
    *
FROM
    books
ORDER BY
    publishedDate DESC;


distinct

disctinct只回傳每種資料一次，另一個同樣用途的關鍵字是distinctrow

例如有4筆資料，個別的publisherId為1、1、2、3，就只會回傳1、2、3

SELECT DISTINCT
    publisherId
FROM
    books;


where

where是篩選的條件，只能和單一資料列函數一起用(但也可以不使用)，單一資料列函數是對每一個row的進行操作的函數

可以搭配以下運算子使用

運算子 用途
between 相當於 >= N AND <= M
in 陣列中任一個
like 模糊比對，'%w%'是含有w，'w%'是w開頭
is null 是null值的
rlike 使用正規表達式對比 

SELECT 
    *
FROM
    books
WHERE
    name = 'Clean Code: A Handbook of Agile Software Craftsmanship'
;


SELECT 
    *
FROM
    books
WHERE
    LENGTH(books.name) > 20
;


SELECT
    name, JSON_EXTRACT(serviceTimes, '$.FRI') as fridayServiceTime
FROM
    libraries
WHERE
    city = '高雄市';


group by + where

SELECT 
    publisherId,
    COUNT(*) as longNameBookNumber

FROM
    books
WHERE
    LENGTH(books.name) > 50
GROUP BY
    publisherId
;


group by + having

group by+having的概念和where類似，但只能跟聚合資料列函數一起用

SELECT 
    publisherId, 
    COUNT(*) AS bookNumbers
FROM
    books
GROUP BY publisherId
HAVING bookNumbers > 2;

但where和having是可以同時用的

例如下面這個例子

publisherId並沒有經過聚合函數，所以用where

bookNumber經過聚合函數，所以用having

SELECT 
    publisherId,
    COUNT(*) AS bookNumber
FROM
    books
WHERE
    books.publisherId != 1
GROUP BY publisherId
HAVING bookNumber > 1;


join

join用於連結table，分為以下幾種

  • cross join
    無條件連結，最無腦的join
    如果表A有2筆資料，表B有3筆，最後會無腦得到6筆

    SELECT 
  COUNT(*)
  FROM
  books
      JOIN
  authors;

  • inner join

    • natural join
      依2個table的 同名欄位連結
      如果沒有同名欄位的話得到的資料會是空的

      SELECT 
*
FROM
books
 NATURAL JOIN
authors;

    • equal join
      on語法後有等於運算子

      SELECT 
*
FROM
books
  JOIN
translators ON books.translatorId = translators.id;


      SELECT 
books.*,
authors.name AS authorName,
translators.name AS translatorName
FROM
books
  JOIN
authors ON authors.id = authorId
  JOIN
translators ON translators.id = translatorId;

    • non-equal join
      無腦join，on語法後無等於運算子

      SELECT 
  *
FROM
books
 join 
 translators on books.price between 100 and 2000;

  • outer join

    • left join
      以左側的表為主

        SELECT 
  *
  FROM
  books
  LEFT JOIN
  translators ON books.translatorId = translators.id;

      也可以同時使用複數種join

        SELECT 
  books.*,
  authors.name as authorName,
  translators.name as translatorName
  FROM
      books
          JOIN
      authors ON authors.id = authorId
          LEFT JOIN
      translators ON translators.id = translatorId
  WHERE
      authors.name = '三毛'
          OR translators.name = '三毛'

    • right join
      和left join只差在以右側的表為主

      right join

      right join

    • (full join)
      MySQL不支援

  • self join
    table1和table2是同一個物件，或子查詢
    但實際上很少會需要同個table做self join


子查詢

注意看

單欄的話where的條件欄位只有一個，多欄則會有多個
單筆的話where條件運算子只會是=，多筆則會是IN或者LIKE之類的

  • 自主子查詢
    可獨立執行，不受主查詢影響

    • 單欄單筆(Scalar)

    select * from publishers where (id) = (select publisherId from books  group by publisherId having count(*) > 3)

    • 單欄多筆(Multi-valued)

      SELECT 
*
FROM
authors
WHERE
id IN (SELECT 
        authorId
    FROM
        books
    WHERE
        language = 'en' AND price < 1000);

    • 多欄單筆

    SELECT 
  *
FROM
  books
WHERE
  (authorId, publisherId) = (SELECT 
  authors.id  as authorId,
  publishers.id as publisherId
FROM
  books
  JOIN authors
      ON authors.id = authorId
  JOIN publishers
      ON publishers.id = publisherId
  where
      authors.name = '三毛');

    • 多欄多筆

    SELECT 
  *
FROM
  books
WHERE
  (authorId , publisherId) IN (SELECT 
          authorId, publisherId
      FROM
          books
      GROUP BY authorId , publisherId
      HAVING COUNT(*) > 1)

  • 關聯子查詢
    需依賴主查詢的資料，效率極差，應盡量改用join取代

    exist運算子常會和關聯子查詢一起用，它用於得知子查詢結果有無資料


#MySQL

TempuraEngineer
一根油滋滋的天婦羅




Related Posts

遞迴（Recursion）

遞迴（Recursion）

 ralphhong5465
JavaScript 的判斷式

JavaScript 的判斷式

 saffran
漫談傳輸介面-PATA

漫談傳輸介面-PATA

 bcewang

Comments