SQL | SQL 語法基礎篇 (1)

為了上次的 AWS 部署囫圇吞棗地把 MySQL 給裝到腦海裡，但總歸根基不穩，所以現在要來還債了，好好地再複習一下 SQL 語法並做紀錄。

在這裡先推兩個網站，非常適合練習 SQL 操作：

1. MySQL Online
2. SQLZoo

建立資料庫

CREATE DATABASE myDatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

上述 SQL 程式碼的意義如下：

1. 建立資料庫，關鍵字是 CREATE。
2. 設定字元集。
3. 定序，即決定了字元該如何被排序。

建立資料表

CREATE TABLE [table_name](
  [column1_name] [data_type] [not_null] [default],
  [column2_name] [data_type] [not_null] [default],
  [column3_name] [data_type] [not_null] [default],
  PRIMARY KEY ([column_name])
);

簡單來說，建立一個資料表，必須要規範：

1. 欄位名稱。
2. 資料型態。
3. 必填與否。
4. 預設值。
5. 哪個欄位要做為主鍵。

現在，讓我們在 MySQL Online 上建立一個飲料資料表 (可以偷偷用 Online SQL Generator 來產生 code~)：

CREATE TABLE `drinks` (
  `id`    int(11)      unsigned NOT NULL AUTO_INCREMENT,
  `name`  varchar(20)           NOT NULL,
  `price` int(11)      unsigned NOT NULL,
  `cost`  int(11)      unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

解釋一下，int 指的是存入整數，varchar 是存入字母、數字、特殊符號等文本數據，unsigned 是指只能存入非負整數，NOT NULL 表示必填。

SQL 的 CRUD

有了資料庫和資料表，接下來該練練資料操作了，來看看 SQL 的 CRUD 怎麼運作。

新增資料

INSERT INTO `drinks` (`name`, `price`, `cost`)
VALUES ('阿華田', 65, 20),
('百香紅茶', 45, 10),
('四季春茶', 25, 5),
('愛玉冰茶', 50, 7),
('冰咖啡', 70, 30);

新增資料會用到INSERT INTO指定要新增資料的資料表及欄位，並透過VALUES賦值。

現在先新增五筆飲料資料，包含名稱、價格、成本到我們的飲料資料表中。

查詢資料

SELECT * FROM `drinks`;

查詢資料是用SELECT撈資料。現在可以看到如下圖，顯示出我們剛剛新增的飲料資料。

修改資料

UPDATE `drinks`
SET `price` = 90
WHERE `name` = '冰咖啡';

假設今天我們要修改冰咖啡的價格，我們就會需要用到UPDATE來更新資料，並透過WHERE來限制指令的作用對象。

用SELECT再查詢一次，現在我們看到冰咖啡價格從70變90了：

刪除資料

DELETE FROM `drinks`
WHERE `name` = '阿華田';

使用DELETE的方法叫做硬刪除，是真的直接把資料從資料表中抹去，所以比較重要的資料會使用軟刪除，但軟刪除這裡就不介紹了。

現在可以看到阿華田已經從資料表上消失了：

進入下一段前

先來建立更多資料表與資料吧！

客人資料：

CREATE TABLE `customers` (
	`id` INT unsigned NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`phone` VARCHAR(20) NOT NULL,
	`birth` DATE NOT NULL,
	PRIMARY KEY (`id`)
);

INSERT INTO `customers`(`name`, `phone`, `birth`)
VALUES ('Johny', '0980123123', '1997-04-22'),
('Wendy', '0919456456', '1999-07-07');

SELECT * FROM `customers`;

點單資料：

CREATE TABLE `orders` (
    `id` INT unsigned NOT NULL AUTO_INCREMENT,
    `drinks_id` INT(11) unsigned NOT NULL,
    `customers_id` INT(11) unsigned NOT NULL,
    `amounts` INT(11) unsigned NOT NULL,
    `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

INSERT INTO `orders`(`drinks_id`, `customers_id`, `amounts`)
VALUES (1, 2, 4),
(2, 2, 3),
(3, 2, 9),
(3, 1, 2);

SELECT * FROM `orders`;

SQL 運算子

運算子有哪些？

1. 計算運算子：+, -, *, /, %
2. 比較運算子：=, <> (不等於), >, <, >=, <=
3. 邏輯運算子： AND, OR, BETWEEN, LIKE

運算子實戰

假設現在飲料店要因應活動，全體品項要打8折，第一個直覺應該是用UPDATE把價格全部改掉：

UPDATE `drinks`
SET `price` = `price` * 0.8;

但是！周年慶後還要改回來很麻煩，所以最好的選擇是多創一個欄位來記錄折扣價格：

SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`;

運算子實戰 - 條件限制

今天老闆說，只有單價30以上的才可以打8折，我們又該如何做呢？這時就要請出前面提過的WHERE配合運算子啦：

SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `price` >= 30;

然後今天老闆很摳，他說50以上不打折，想多賺一點，那我們可以這樣寫：

// 寫法1
SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `price` >= 30 AND `price` <= 50;

// 寫法2
SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `price` BETWEEN 30 AND 50;​

運算子實戰 - 關鍵字搜尋

老闆很煩，他後來決定名字帶 "茶" 的飲料才打8折，這時我們就一樣請出WHERE來配合LIKE運算子來打組合技啦：

SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `name` LIKE '%茶%';

%茶%表示要找前面有任意字元的茶和後面有任意字元的茶。同理，我們要找以茶開頭的飲料可以寫茶%。

SQL 內建的函式

SQL 有一些內建的函式可以幫忙我們快速做資料處理。

SUM

回到剛剛創建的 orders 資料表，忙完一天的工作老闆問說：我們今天一共賣了幾杯飲料？這時聰明的我們可以立馬對資料表做下列操作：

SELECT SUM(`amounts`) AS `total_sold`
FROM `orders`;

你很快地得到今天賣了18杯：

AVG

當我們正得意洋洋地報告給老闆我們的結果後，老闆幽幽地說：所以一單平均賣幾杯？還是一樣從明的我們想到用AVG來解決老闆的問題：

SELECT AVG(`amounts`) AS `average_sold`
FROM `orders`;

很快，我們知道一單平均賣4.5杯：

GROUP BY

現在我們大概知道老闆接下來要問：每個飲料品項總共賣多少杯？所以我們乾脆先算好給他：

SELECT `drinks_id`, SUM(`amounts`) as total_sold
FROM `orders`
GROUP BY `drinks_id`;

COUNT

老闆很讚嘆，我們竟然會搶答了！但是他說他要問的不是這個，他想問的是今天各種飲料項目的熱門程度，這時我們默默掏出COUNT來幫忙統計資料的出現次數：

SELECT `drinks_id`, COUNT(`amounts`) as num_of_orders
FROM `orders`
GROUP BY `drinks_id`;

現在我們知道 drinks_id = 3 的飲料最熱門，有兩單的資料都有它：

HAVING

最後老闆說，他想知道銷售量超過10的的飲料項目，聰明的我們直覺要用WHERE，可是突然靈光一閃，儲存總量的 total_sold 欄位是用函式建的，WHERE無法篩選誒...還好我們有HAVING！

SELECT `drinks_id`, SUM(`amounts`) as total_sold
FROM `orders`
GROUP BY `drinks_id`
HAVING `total_sold` > 10;

然後我們愉快地跟老闆說：超過10的只有 drinks_id = 3 的飲料喔！然後帥氣下班，留下呆滯的老闆：

好了，夜已深，退朝！明天來寫 SQL JOIN~