Phase II of the Laboratory Building Competition

subject

There are three csv data files in the folder. The format and description of each data file are as follows:
shiyanlou_user.csv: 1000 user data of the experimental building, including two columns, user ID and user name
shiyanlou_course.csv:Course data for 10 laboratory buildings, including two columns, course ID and course name
shiyanlou_usercourse.csv: 100 user course learning records, including three columns, user ID, course ID and learning time (minutes)

target

The created database needs to meet the following requirements:
MySQL service is running
The new database name is shiyanlou, the readable and writable management user is shiyanlou, and the password is shiyanlou.
shiyanlou database contains three tables: user, course, user, each table contains all the data in a csv data file. The user table contains two columns: ID and name. The coursetable contains two columns: ID and name. The user course table contains four columns: id, user_id, course_id, study_time. Note the relationship between the primary keys of the other two tables.

Create a database (in this case root identity)

CREATE DATABASE `shiyanlou`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

Create users and grant them permissions (in this case root identity)

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";

Set the character set (in this case root identity)

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 ;

Switch to shiyanlou users and create tables

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;

Import csv files into corresponding tables

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);

Conclusion:

The character set utf8 can be specified by creating database and table.
Attention should be paid to the grammatical format when establishing users and granting them permissions.
When importing data, if the number of fields in the data file is different from that in the corresponding table, you need to specify the corresponding field name in the table at the end.

Reference blog:

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

Posted by quintus on Thu, 18 Apr 2019 10:51:33 -0700

Programmer Group