题目
文件夹中有三个 csv 数据文件,每个数据文件的格式和描述如下:
shiyanlou_user.csv:1000 名实验楼用户数据,包含两列,用户 ID 和用户名
shiyanlou_course.csv:10 门实验楼课程数据,包含两列,课程 ID 和课程名
shiyanlou_usercourse.csv:100 条用户课程学习记录,包含三列,用户 ID,课程 ID 和学习时间(分钟)
目标
创建后的数据库需要满足以下需求:
MySQL 服务处于运行状态
新的数据库名称为 shiyanlou,设置的可读写的管理用户为 shiyanlou,密码为 shiyanlou。
shiyanlou 数据库包含三个表:user,course,usercourse,每个表包含一个 csv 数据文件中的所有数据。user 表包含两列:id,name。course 表包含两列:id,name。usercourse 表包含四列:id,user_id,course_id,study_time,注意与其他两个表主键之间的关系。
创建数据库(此时是root身份)
CREATE DATABASE `shiyanlou`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
创建用户并授予其权限(此时是root身份)
create user shiyanlou identified by "shiyanlou";
grant create ,drop ,alter, select, update, delete, insert on shiyanlou.* to shiyanlou@localhost identified by "shiyanlou";
grant file on *.* to shiyanlou identified by "shiyanlou";
设置字符集(此时是root身份)
SET character_set_client = utf8 ;
SET character_set_connection = utf8 ;
SET character_set_database = utf8 ;
SET character_set_results = utf8 ;
SET character_set_server = utf8 ;
切换到shiyanlou用户并建立表
create table user
(
id int(10) primary key not null auto_increment,
name varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table course
(
id int(10) primary key not null auto_increment,
name varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table usercourse
(
id int(10) primary key not null auto_increment,
user_id int(10) ,
course_id int(10),
study_time int(10),
foreign key(user_id) references user(id),
foreign key(course_id) references course(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
导入csv文件到对应表中
load data infile '/home/shiyanlou/loudatabase/shiyanlou_user.csv' into table user FIELDS TERMINATED BY ',';
load data infile '/home/shiyanlou/loudatabase/shiyanlou_course.csv' into table course FIELDS TERMINATED BY ',';
load data infile '/home/shiyanlou/loudatabase/shiyanlou_usercourse.csv' into table usercourse FIELDS TERMINATED BY ','
(user_id,course_id,study_time);
总结:
- 创建数据库和表可以指定字符集utf8;
- 建立用户以及授予其权限时的与语法格式要注意;
- 在导入数据时,如果数据文件中的数据字段与对应的表中的字段个数不一样,那么需要在末尾指定表中对应的字段名称
参考博文:
http://www.cnblogs.com/wanghuaijun/p/5802209.html
http://www.pc6.com/infoview/Article_63586.html
http://blog.csdn.net/leili0806/article/details/8573636
http://blog.csdn.net/dliyuedong/article/details/41046419
http://blog.csdn.net/lu8000/article/details/47439849
http://blog.csdn.net/zhangzhikaixinya/article/details/47861255
https://www.shiyanlou.com/contests/lou2/console
http://www.cnblogs.com/discuss/articles/1862248.html