建表
创建数据库
create database ml1m;
CREATE TABLE movies (
MovieID INTEGER NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
Genres varchar(255) NOT NULL,
PRIMARY KEY (MovieID)
);
CREATE TABLE movie_preferences (
UserID INTEGER NOT NULL,
MovieID INTEGER NOT NULL,
Rating INTEGER NOT NULL DEFAULT 0,
timestamp INTEGER not null default 0,
FOREIGN KEY (movieID) REFERENCES movies(MovieID) ON DELETE CASCADE
);
CREATE TABLE users(
UserID INTEGER NOT NULL AUTO_INCREMENT,
Gender varchar(50) NOT NULL,
Age varchar(255) NOT NULL,
Occupation varchar(255) NOT NULL,
Zipcode varchar(50) NOT NULL,
PRIMARY KEY (UserID)
);
导入数据
在mysql中
set global local_infile=on;
退出数据库
exit;
用以下命令登录数据库
mysql -uroot --local-infile=1 -p
输入密码
使用数据库
use ml1m;
（一）导入第一个表
load data local infile '/root/ml-1m/ratings.dat' into table movie_preferences
CHARACTER SET utf8
FIELDS TERMINATED BY '::'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
（二）导入第二个表
此处再次退出mysql
exit;
mysql -uroot --local-infile=1 -p
输入密码
使用数据库
use ml1m;
导入数据
load data local infile '/root/ml-1m/movies.dat' into table movies
CHARACTER SET utf8
FIELDS TERMINATED BY '::'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
（三）导入第三个表
此处再次退出mysql
exit;
mysql -uroot --local-infile=1 -p
输入密码
使用数据库
use ml1m;
导入数据
load data local infile '/root/ml-1m/users.dat' into table users
CHARACTER SET utf8
FIELDS TERMINATED BY '::'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
完成
创建其他的表
age表
CREATE TABLE `age` (
`age` INT,
`ranges` VARCHAR(25)
);
插入数据
INSERT INTO age (age,ranges) VALUES(1,'Under 18');
INSERT INTO age (age,ranges) VALUES(18,'18-24');
INSERT INTO age (age,ranges) VALUES(25,'25-34');
INSERT INTO age (age,ranges) VALUES(35,'35-44');
INSERT INTO age (age,ranges) VALUES(45,'45-49');
INSERT INTO age (age,ranges) VALUES(50,'50-55');
INSERT INTO age (age,ranges) VALUES(56,'56+');
profession表
CREATE TABLE `profession` (
`num` INT,
`profession` VARCHAR(255)
);
插入数据
INSERT INTO profession (num,profession) VALUES(0,'"other" or not specified');
INSERT INTO profession (num,profession) VALUES(1,"academic/educator");
INSERT INTO profession (num,profession) VALUES(2,"artist");
INSERT INTO profession (num,profession) VALUES(3,"clerical/admin");
INSERT INTO profession (num,profession) VALUES(4,"college/grad student");
INSERT INTO profession (num,profession) VALUES(5,"customer service");
INSERT INTO profession (num,profession) VALUES(6,"doctor/health care");
INSERT INTO profession (num,profession) VALUES(7,"executive/managerial");
INSERT INTO profession (num,profession) VALUES(8,"farmer");
INSERT INTO profession (num,profession) VALUES(9,"homemaker");
INSERT INTO profession (num,profession) VALUES(10,"K-12 student");
INSERT INTO profession (num,profession) VALUES(11,"lawyer");
INSERT INTO profession (num,profession) VALUES(12,"programmer");
INSERT INTO profession (num,profession) VALUES(13,"retired");
INSERT INTO profession (num,profession) VALUES(14,"sales/marketing");
INSERT INTO profession (num,profession) VALUES(15,"scientist");
INSERT INTO profession (num,profession) VALUES(16,"self-employed");
INSERT INTO profession (num,profession) VALUES(17,"technician/engineer");
INSERT INTO profession (num,profession) VALUES(18,"tradesman/craftsman");
INSERT INTO profession (num,profession) VALUES(19,"unemployed");
INSERT INTO profession (num,profession) VALUES(20,"writer");
genres表
CREATE TABLE `genres` (
`Genres` VARCHAR(255)
);
插入数据
INSERT INTO genres (Genres) VALUES('Action');
INSERT INTO genres (Genres) VALUES('Adventure');
INSERT INTO genres (Genres) VALUES('Animation');
INSERT INTO genres (Genres) VALUES("Children's");
INSERT INTO genres (Genres) VALUES('Comedy');
INSERT INTO genres (Genres) VALUES('Crime');
INSERT INTO genres (Genres) VALUES('Documentary');
INSERT INTO genres (Genres) VALUES('Drama');
INSERT INTO genres (Genres) VALUES('Fantasy');
INSERT INTO genres (Genres) VALUES('Film-Noir');
INSERT INTO genres (Genres) VALUES('Horror');
INSERT INTO genres (Genres) VALUES('Musical');
INSERT INTO genres (Genres) VALUES('Mystery');
INSERT INTO genres (Genres) VALUES('Romance');
INSERT INTO genres (Genres) VALUES('Sci-Fi');
INSERT INTO genres (Genres) VALUES('Thriller');
INSERT INTO genres (Genres) VALUES('War');
INSERT INTO genres (Genres) VALUES('Western');补充
set globle local_infile=1; 设置服务器端
mysql --local-infile=1 -u root -p123QWEasd! 客户端登录
load data local infile '/root/ml-1m/users.dat' into table users
CHARACTER SET utf-8
FIELDS TERMINATED BY '::'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(user_id,gender,age,occupation,zipcode)
