MySQL 基本語法 (六) 創建公司資料庫

依照上圖的資料表創建出公司的資料庫

1. Employee

CREATE TABLE `employee`(
	`emp_id` INT PRIMARY KEY,
  `name` VARCHAR(20),
	`birth_date` DATE,
	`sex`VARCHAR(1),
  `salary` INT,
  `branch_id` INT,
  `sup_id` INT
);

2. Branch

CREATE TABLE `branch`(
	`branch_id` INT PRIMARY KEY,
	`branch_name` VARCHAR(20),
  `manager_id` INT,
  FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL
);

新增完之後才可以增加employee table中的FORIEGN KEY

ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`)
REFERENCES `branch`(`branch_id`)
ON DELETE SET NULL;

ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`)
REFERENCES `employee`(`emp_id`)
ON DELETE SET NULL;

3. Client

CREATE TABLE `client`(
	`client_id` INT PRIMARY KEY,
	`client_name` VARCHAR(20),
    `phone` VARCHAR(20)
);

4. Work_with

CREATE TABLE `works_with`(
	`emp_id` INT ,
	`client_id` INT ,
    `total_sales` INT,
    PRIMARY KEY(`emp_id`,`client_id`),
    FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
    FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);

開始新增資料

1. 先新增部門branch的資料，把manager_id設成NULL

INSERT INTO `branch` VALUES( 1, '研發', NULL);
INSERT INTO `branch` VALUES( 2, '行政', NULL);
INSERT INTO `branch` VALUES( 3, '資訊', NULL);

2. 新增員工資料

INSERT INTO `employee` VALUES(206, '小黃', '1998-10-08', 'F', 50000, 1, NULL);
INSERT INTO `employee` VALUES(207, '小綠', '1985-09-16', 'M', 29000, 2, 206);
INSERT INTO `employee` VALUES(208, '小黑', '2000-12-19', 'M', 35000, 3, 206);
INSERT INTO `employee` VALUES(209, '小白', '1997-01-22', 'F', 39000, 3, 207);
INSERT INTO `employee` VALUES(210, '小藍', '1925-11-10', 'F', 84000, 1, 207);

3. 修改branch中的manager_id

UPDATE `branch`
SET `manager_id` = 206
WHERE `branch_id` = 1;

UPDATE `branch`
SET `manager_id` = 207
WHERE `branch_id` = 2;

UPDATE `branch`
SET `manager_id` = 208
WHERE `branch_id` = 3;

4. 新增顧客資料`client`

INSERT INTO `client` VALUES(400, '阿狗', '254354335');
INSERT INTO `client` VALUES(401, '阿貓', '25633899');
INSERT INTO `client` VALUES(402, '旺來', '45354345');
INSERT INTO `client` VALUES(403, '露西', '54354365');
INSERT INTO `client` VALUES(404, '艾瑞克', '18783783');

5. 新增員工和顧客關西`work_with`

INSERT INTO `works_with` VALUES(210, 400, '70000');
INSERT INTO `works_with` VALUES(210, 401, '24000');
INSERT INTO `works_with` VALUES(210, 402, '9800');
INSERT INTO `works_with` VALUES(210, 403, '24000');
INSERT INTO `works_with` VALUES(210, 404, '87940');