目錄

• 目錄
  • 名詞解釋
    • 關於資料庫
    • 關於動作
    • 關於語句 \& function
  • 建立table
  • 新增資料
  • 更新資料
  • 撈資料
    • select
    • order by
    • distinct
    • where
    • group by + where
    • group by + having
    • join
  • 子查詢

名詞解釋

關於資料庫

關於動作

關於語句 & function

建立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的進行操作的函數

可以搭配以下運算子使用

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只差在以右側的表為主

    

    

  • (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運算子常會和關聯子查詢一起用，它用於得知子查詢結果有無資料