資料庫設計 - 有效的使用系統資料 - 第三章 資料庫基本架構

單元 1 資料庫基本概念說明

Schemas

資料庫描述的檔案

sys 為資料庫預設的資料庫，跟系統有關的

未來新的資料庫會在 sys 下做排列

此次會使用訂單管理系統做教學

SQL 檔案資料的匯入方式後面會介紹

資料庫架構

一個系統 sys 會對應到 1 或多個資料庫，這邊會以訂單系統為例

資料庫有哪些東西：

• Tables 表單，很多表單
• Views
• Stored Procedures
• Function

主要先介紹 table，table 包含

• Columns 欄位
• Indexes
• Foreign Keys
• Triggers

建立資料庫的兩種方式

1. 指令

   1. 創建資料庫 create database

      create database if not exists orders;
      

      create database 是基本語法

      要創建一定要這麼下 create database

      if not exists <名稱> 是選則性的

      1. 
         按一下閃電執行程式碼
         
         
         

      2. 
         按一下 SCHEMAS 右邊的 refresh 箭頭

      orders 就會跑出來了！

      

   2. 刪除資料庫 drop database

      drop database orders;
      

   

2. 透過 Workbench 的功能來新建

   1. 
      點選 create a new schema in the connected server
      
      
      

   2. 設定名稱, collation 點選右下 apply

      

單元 2 儲存資料的表格

建立 table

建立 table 時，會有以下產生

• Columns

  須建立至少一個欄位

• Indexes 索引

  快速搜尋，增加效能

• Foreign Keys

  與其他 table 關係的連結

• Triggers

  在資料異動時，同時做一些動作

  • before insert 塞資料前先做
  • after insert 塞資料後做
  • before update
  • after update
  • before delete
  • after delete

• Partitioning

  資料量大時做切割

  • Options

    最大、最小筆數

會在之後章節係講，但建立 table 一定要至少有一個欄位 Column，所以這邊會先提到建立 column 的方式。

建立方式

1. Workbench

   1. 點選 Tables 右鍵，Create Table…

      

   2. 設定 Table 名稱: customers

      1. 建立一個欄位, 名稱為 customers_id, 選 type int, 勾選 PK NN

         💡 PK: primary key 主鍵/主 key
              NN: not null 不可為空 int: 整數型態

         

      2. 按右下 apply

      3. 會出現以下語法

         CREATE TABLE `orders`.`customers` (
           `customers_id` INT NOT NULL,
           PRIMARY KEY (`customers_id`));
         

2. 指令

   與上述指令相同，直接執行

   CREATE TABLE `orders`.`customers` (
     `customers_id` INT NOT NULL,
     PRIMARY KEY (`customers_id`));
   

執行結果

右邊 Tables 欄位會出現 customers，下拉會有 Columns, Indexes, Foreign Keys, Triggers 等欄位

刪除 Table 表單

DROP TABLE `orders`.`customers`;

新增 Column 欄位

在 orders schema, customers table 新增欄位 customers_name

ALTER TABLE `orders`.`customers`
ADD COLUMN customers_name VARCHAR(45) NOT NULL AFTER `customers_id`;

刪除欄位

在 orders schema, customers table 刪除欄位 customers_name

ALTER TABLE `orders`.`customers`
DROP COLUMN `customers_name`;

刪除資料

在 orders schema, customers table 清除所有資料

truncate `orders`.`customers`;

刪除

1. DROP

   刪除 table，以及其中的資料

2. truncate

   刪除 table 中的資料，table 保留

💡 要特別小心 DROP 與 truncate 的使用 兩者都是刪除資料的語法

筆記 SQL 程式碼

CREATE TABLE `orders`.`customers` (
  `customers_id` INT NOT NULL,
  PRIMARY KEY (`customers_id`));

DROP TABLE `orders`.`customers`;

ALTER TABLE `orders`.`customers`
ADD COLUMN customers_name VARCHAR(45) NOT NULL AFTER `customers_id`;

ALTER TABLE `orders`.`customers`
DROP COLUMN `customers_name`;

truncate `orders`.`customers`;

單元 3 欄位

點選 Columns 右邊的第二個按鈕

會有設定 table 的工作區塊

最底下會有 Columns, Indexes, Foreign Keys 等頁籤

先選 Columns 欄位

欄位

型態定義

Datatype → 對應到程式裡的物件

• INT()：整數，括弧裡面可以設定長度 () → integer 物件
• VARCHAR()：字串、文字 → string 物件
• DECIMAL()：浮點數，小數點
• DATETIME：Date
• BLOB：二進位資料型態，資料欄位存放如圖片，音樂黨，可以轉成二進位，會是比較大的資料來源

這邊的 customers_id 用數字 INT；customers_name 用文字 VARCHAR

設定

• PK：關聯式資料庫的核心，串接 table，每個 table 都一定要有
  • [x] 一定要有 NN 設定
• NN： not null ，可不可以為空
• UQ：unique 唯一索引值，有時有多個 key，設定這個就只會有唯一
• BIN：binary 二進位制的資料
• UN：unsigned integer 沒有符號 → 非負值
• ZF：zero fill 填充 0 例如欄位資料是 1 int(4) 則內容顯示為 0001
• AI：auto increment 自動增加
• G：由其他列計算出來的，並非本身塞入的資料

Comments:

寫註解，此欄位當初設計時是要塞什麼樣的資料

所有更改都要按 apply 才會作用，反悔可以按 revert

ALTER TABLE `orders`.`customers` 
CHANGE COLUMN `customers_id` `customers_id` INT NOT NULL COMMENT '客戶 ID' ;

註解

-- 單行註解
-- 每次註解都要打 '--'

/*
多行註解
只要用 slash & 星號
把須註解的字
包起來即可
*/

插入

1. 直接在工作欄位輸入後，按右下 apply

   

2. 指令

   INSERT INTO `orders`.`customers` (`customers_id`, `customers_name`) VALUES ('1', 'Amy');
   

刪除

1. 右鍵 delete rows

2. 指令

   DELETE FROM `orders`.`customers` WHERE (`customers_id` = '4');
   

更新

1. 直接編輯欄位並按下 apply

   

2. 指令

   UPDATE `orders`.`customers` SET `city` = '高雄' WHERE (`customers_id` = '4');
   

單元 4 課程資料匯入與匯出

Data Export

1. 點選右上 Data Export
2. 勾選 Schema
3. 勾選 Table
4. 選擇 Export 檔案路徑
5. 點選右下 Start Export

export 完成後會出現以下檔案

-- MySQL dump 10.13  Distrib 8.0.31, for macos12 (x86_64)
--
-- Host: localhost    Database: orders
-- ------------------------------------------------------
-- Server version	8.0.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `customers`
--

DROP TABLE IF EXISTS `customers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `customers` (
  `customers_id` int NOT NULL COMMENT '客戶 ID',
  `customers_name` varchar(45) NOT NULL,
  `city` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customers_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `customers`
--

LOCK TABLES `customers` WRITE;
/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
INSERT INTO `customers` VALUES (1,'Amy','Taipie'),(2,'Seraphine','Taipei'),(3,'John','Taipei'),(4,'Angle','高雄');
/*!40000 ALTER TABLE `customers` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-11-07 16:29:34

Data Import

先將 customers table 刪除

1. 點選右邊的 data import

2. 選擇資料夾途徑

   

3. 選擇 Schema

4. 右下 Start Import

5. 完成

   可以看到 customers_id & customers_name 又出現了
   

   

課程資料匯入

老師有提供以下訂單 orders 資料，匯入後於下次上課使用