MySQL Study (MySQL從入門到精通)

標籤：io   os   ar   for   sp   資料   on   ad   ef   

 建立一個簡單的書籍出版追蹤資料庫（UI工具 Navicat for MySQL）　

 

CREATE DATABASE publication_db;
USE publication_db;

CREATE TABLE poet(poet_code INT NOT NULL,first_name VARCHAR(30),surname VARCHAR(40),address VARCHAR(100),postcode VARCHAR(20),
telephone_number VARCHAR(30),PRIMARY KEY(poet_code));

CREATE TABLE poem(poem_Code INT NOT NULL,title VARCHAR(50),content TEXT,poet_Code INT NOT NULL,PRIMARY KEY(poem_Code),
INDEX(poet_Code),FOREIGN KEY(poet_Code) REFERENCES poet(poet_Code));

CREATE TABLE publication(publication_code INT NOT NULL,title VARCHAR(100),price MEDIUMINT UNSIGNED,PRIMARY KEY(publication_code));

CREATE TABLE poem_publication(poem_code INT NOT NULL,publication_code INT NOT NULL,PRIMARY KEY(poem_code,publication_code),
FOREIGN KEY(poem_code) REFERENCES poem(poem_code),FOREIGN KEY(publication_code) REFERENCES publication(publication_code));

CREATE TABLE sales_publication(sales_code INT NOT NULL,publication_code INT NOT NULL,PRIMARY KEY(sales_code,publication_code));

CREATE TABLE customer(customer_code INT NOT NULL,first_name VARCHAR(30),surname VARCHAR(30),address VARCHAR(100),postcode VARCHAR(20),
telephone_number VARCHAR(30),PRIMARY KEY(customer_code));

CREATE TABLE sale(sale_code INT NOT NULL,sale_date DATE,amount INT UNSIGNED,customer_code INT NOT NULL,PRIMARY KEY(sale_code),
INDEX(customer_code),FOREIGN KEY(customer_code) REFERENCES customer(customer_code));

　

MySQL Study (MySQL從入門到精通)