数据库系统概论实验准备

记录自己数据库实验的数据准备问题
因为实验的数据为gbk的文件,而在mysql中导入gbk中文一直有问题,最终使用utf-8的编码格式解决问题,希望能够解决一部分人的问题

数据包下载
因为原数据为gbk,我将其改为了utf-8,所以资源是utf-8的编码
首先创建数据库

CREATE DATABASE sales DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

然后创建表

CREATE TABLE region(
	regionkey INT PRIMARY KEY,
	name CHAR(25),
	comment VARCHAR(150)
);
CREATE TABLE nation(
	nationkey INT PRIMARY KEY,
	name CHAR(25),
	regionkey INT,
	comment VARCHAR(150),
	FOREIGN KEY(regionkey) REFERENCES region(regionkey)
);
CREATE TABLE supplier(
	suppkey INT PRIMARY KEY,
	name CHAR(100),
	address VARCHAR(100),
	nationkey INT,
	phone CHAR(30),
	acctbal NUMERIC(12,2),
	comment VARCHAR(100),
	FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);
CREATE TABLE part(
	partkey INT PRIMARY KEY,
	name VARCHAR(100),
	mfgr CHAR(50),
	brand CHAR(50),
	type VARCHAR(25),
	size INT,
	container CHAR(10),
	retailprice NUMERIC(8,2),
	comment VARCHAR(20)
);
CREATE TABLE partsupp(
	partkey INT,
	suppkey INT,
	availqty INT,
	supplycost NUMERIC(10,2),
	comment VARCHAR(200),
	PRIMARY KEY(partkey,suppkey),
	FOREIGN KEY(partkey) REFERENCES part(partkey),
	FOREIGN KEY(suppkey) REFERENCES supplier(suppkey)
);
CREATE TABLE customer(
	custkey INT PRIMARY KEY,
	name VARCHAR(25),
	address VARCHAR(40),
	nationkey INT,
	phone CHAR(30),
	acctbal NUMERIC(12,2),
	mktsegment CHAR(10),
	comment VARCHAR(100),
	FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);
CREATE TABLE orders(
	orderkey INT PRIMARY KEY,
	custkey INT,
	orderstatus CHAR(1),
	totalprice NUMERIC(10,2),
	orderdate DATE,
	orderpriority CHAR(15),
	clerk CHAR(16),
	shippriority CHAR(1),
	comment VARCHAR(60),
	FOREIGN KEY(custkey) REFERENCES customer(custkey)
);
CREATE TABLE lineitem(
	orderkey INT,
	partkey INT,
	suppkey INT,
	linenumber INT,
	quantity INT,
	extendedprice NUMERIC(8,2),
	discount NUMERIC(3,2),
	tax NUMERIC(3,2),
	returnflag CHAR(1),
	linestatus CHAR(1),
	shipdate DATE,
	commitdate DATE,
	receiptdate DATE,
	shipinstruct CHAR(25),
	shipmode CHAR(10),
	comment VARCHAR(40),
	PRIMARY KEY(orderkey,linenumber),
	FOREIGN KEY(partkey,suppkey) REFERENCES partsupp(partkey,suppkey)
);

然后导入数据,可以使用navicate,这个软件强烈推荐
创建后,要按创建的顺序导入数据, 按region,nation,supplier的顺序

点击csv

选择的数据和表相同,这里使用utf-8

然后就一直下一步,导入月份有错误,在这里改为年月日

然后就可以点开始导入数据,最终数据都成功导入