MySQL 笔记（三）由 txt 文件导入数据

改编自学校实验，涉及一些字符集相关的问题。

索引

建库
导入数据
最终脚本

下载数据点击这里

建库

create.sql

DROP DATABASE IF EXISTS orderdb;
CREATE DATABASE orderdb;
USE orderdb;

CREATE TABLE employee (
        employee_no VARCHAR(8),
        employee_name VARCHAR(10),
        sex CHAR(1),
        birthday DATE,
        address VARCHAR(50),
        telephone VARCHAR(20),
        hiredate DATE COMMENT '聘用日期',
        department VARCHAR(30),
        headship VARCHAR(10) COMMENT '职务',
        salary DECIMAL(8,2),

        PRIMARY KEY(employee_no)

)       ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE customer (
        customer_no VARCHAR(9),
        customer_name VARCHAR(40),
        telephone VARCHAR(20),
        address VARCHAR(40),
        zip VARCHAR(6),

        PRIMARY KEY(customer_no)

)       ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE product (
        product_no VARCHAR(9),
        product_name VARCHAR(40),
        product_class VARCHAR(20),
        product_price DECIMAL(7,2),

)       ENGINE=INNODB DEFAULT CHARSET=utf8;

        order_no VARCHAR(12),
        customer_no VARCHAR(9),
        saler_no VARCHAR(8),
        order_date DATE,
        order_sum DECIMAL(9,2),
        invoiceno CHAR(10) COMMENT '发票号码',

        PRIMARY KEY (order_no),

        FOREIGN KEY (customer_no)
                REFERENCES customer(customer_no)

)       ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE order_detail (
        order_no VARCHAR(12),
        product_no VARCHAR(9),
        quantity INT,
        price DECIMAL(7,2),

        PRIMARY KEY (order_no, product_no),

        FOREIGN KEY (order_no)
                REFERENCES order_master(order_no),
        FOREIGN KEY (product_no)
                REFERENCES product(product_no)

)       ENGINE=INNODB DEFAULT CHARSET=utf8;

索引

建库

更多相关文章

随机推荐