您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL on Linux on POWER：資料庫建立實戰、實體關係圖、InnoDB 外鍵、批次執行 SQL 檔案、LOAD DATA 導入、參考完整性驗證

編輯：MySQL 資料庫相關知識

環境背景

POWER5 處理器、64 位、Linux (RHEL/SLES) on Power。
MySQL 隨發行版 RPM 提供，亦可官網下載 POWER 編譯版或原始碼編譯。
管理工具：MySQL Administrator、Query Browser、phpMyAdmin（需 PHP/Apache）。

範例資料庫：IT 專家承包公司

四表 ER 圖：

JOB：職位代碼、名稱、時薪（PK: JOB_CODE）。
EMPLOYEE：員工 SSN、姓名、職位代碼（PK: EMP_SSN，FK→JOB）。
PROJECT：專案編號、名稱、負責人 SSN（PK: PRJ_NUM，FK→EMPLOYEE）。
ORDERFORM：訂單編號、日期、工時、時薪、總額、專案編號、員工 SSN（PK: ORD_NUM，FK→PROJECT/EMPLOYEE）。

關係：JOB 1:M EMPLOYEE；EMPLOYEE 1:M PROJECT；EMPLOYEE N:M PROJECT（透過 ORDERFORM）。

建立資料庫與表（InnoDB 保證參考完整性）


CREATE DATABASE CONTRACTING;
USE CONTRACTING;

CREATE TABLE JOB (
  JOB_CODE INTEGER NOT NULL UNIQUE,
  JOB_NAME LONG VARCHAR NOT NULL,
  JOB_HOUR_CHRG DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (JOB_CODE)
) ENGINE=InnoDB;

CREATE TABLE EMPLOYEE (
  EMP_SSN CHAR(9) NOT NULL UNIQUE,
  EMP_LNAME VARCHAR(50) NOT NULL,
  EMP_FNAME VARCHAR(50) NOT NULL,
  JOB_CODE INTEGER NOT NULL,
  PRIMARY KEY (EMP_SSN),
  FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE PROJECT (
  PRJ_NUM INTEGER NOT NULL UNIQUE,
  PRJ_NAME VARCHAR(50) NOT NULL,
  EMP_SSN CHAR(9) NOT NULL,
  PRIMARY KEY (PRJ_NUM),
  FOREIGN KEY (EMP_SSN) REFERENCES EMPLOYEE(EMP_SSN)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE ORDERFORM (
  ORD_NUM INTEGER NOT NULL UNIQUE,
  ORD_DATE DATE NOT NULL,
  ORD_HOURS SMALLINT NOT NULL,
  ORD_CHRG_HOUR DECIMAL(10,2) NOT NULL,
  ORD_CHARGE DECIMAL(10,2) NOT NULL,
  PRJ_NUM INTEGER NOT NULL,
  EMP_SSN CHAR(9) NOT NULL,
  PRIMARY KEY (ORD_NUM),
  FOREIGN KEY (EMP_SSN) REFERENCES EMPLOYEE(EMP_SSN)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (PRJ_NUM) REFERENCES PROJECT(PRJ_NUM)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

批次執行 SQL 檔


mysql -u username < create_schema.sql

資料導入：INSERT 與 LOAD DATA


-- 單行 INSERT
INSERT INTO JOB (JOB_CODE, JOB_NAME, JOB_HOUR_CHRG) VALUES (200, 'Application Designer', 38.50);

-- 批次 LOAD DATA（製表符分隔檔 job_data_load.txt）
200 Application Programmer 35.48
201 Database Administrator 38.50
202 Technical Support 27.00
203 Database Designer 49.99

LOAD DATA LOCAL INFILE "job_data_load.txt" INTO TABLE JOB;

參考完整性驗證


-- 嘗試刪除被參考的 JOB_CODE=203
DELETE FROM JOB WHERE JOB_CODE=203;
-- ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

-- 更新 JOB_CODE（CASCADE 生效）
UPDATE JOB SET JOB_CODE=207 WHERE JOB_CODE=203;
-- EMPLOYEE 表對應行自動更新為 207