Skip to main content

SQL 基礎語法-1

info

相關文章：

  1. py4e - Using Databases

SQL 練習與教學網站​

  1. MySQL Online
  2. SQLZoo
  3. SQL語法教學

五大鍵​

  1. Super Key：一個或多個欄位的組合，可以唯一識別一筆資料。
  2. Candidate Key：最小的 Super Key，也就是最少欄位的組合，可以唯一識別一筆資料。
  3. Primary Key：Candidate Key 中選擇一個當作主鍵，並且不可重複。
  4. Alternate Key：除了 Primary Key 以外的 Candidate Key。
  5. Foreign Key：另一個資料表的 Primary Key，用來建立資料表之間的關聯。

建立資料庫​

  1. 建立資料庫，關鍵字是 CREATE。
  2. 設定字元集。
  3. 定序，即決定了字元該如何被排序。
CREATE DATABASE myDatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
info

刪除資料庫：

DROP DATABASE myDatabase;

建立資料表​

建立一個資料表，必須要規範：

  1. 欄位名稱。
  2. 資料型態。
  3. 必填與否。
  4. 預設值。
  5. 哪個欄位要做為主鍵。
Sample
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])
);
tip

可以用 Online SQL Generator 來產生 code

先簡單創一個飲料表單：

drinks
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`)
);
info
資料型態說明
int整數
float浮點數
char固定長度字串
varchar可變長度字串
text長字串
date日期
time時間
datetime日期時間
timestamp時間戳記

其他如 unsigned、not null、auto_increment 等是資料型態的修飾詞，分別代表非負整數、不可為空、自動增加。

資料表的調整​

若要在建立好的表格上做調整，可以使用 ALTER TABLE。
舉例來說，我們要新增一個欄位 category 來記錄飲料的類別：

ALTER TABLE drinks ADD (category varchar(20) NOT NULL);

反之若要調整刪除欄位，則使用 DROP：

ALTER TABLE drinks DROP category;

SQL 的 CRUD​

新增資料​

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

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

查詢資料​

查詢資料是用 SELECT 撈資料。

SELECT * FROM `drinks`;

修改資料​

使用 UPDATE 來更新資料，並透過 WHERE 來限制指令的作用對象。

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

刪除資料​

info

刪除表格中所有內容，使用 TRUNCATE：

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

SQL 運算子​

前置準備，先準備一組 SQL 資料​

customers
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`;
orders
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`;

運算子有哪些？​

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

運算子實戰​

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

ver.update
UPDATE `drinks`
SET `price` = `price` * 0.8;

但這樣事直接對資料做更改，並不是最佳解。最好的選擇是多創一個欄位來記錄折扣價格：

best solution
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 '%茶%';
tip

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

SQL 內建的函式​

SUM​

今天一共賣了幾杯飲料？

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

AVG​

一單平均賣幾杯？

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

GROUP BY​

每個飲料品項總共賣多少杯？

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

COUNT​

今天各種飲料項目的熱門程度 (出現次數)？

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

HAVING​

銷售量超過10的的飲料項目？

danger

直覺會使用 WHERE，但儲存總量的 total_sold 欄位是用函式建的， WHERE 無法篩選

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

參考資料​

  1. Alpha Camp
  2. Meta Back-End Developer Professional Certificate