数据库

sudo service mysql start
mysql -u root
CREATE DATABASE shiyanlou;
use mysql;
create user shiyanlou IDENTIFIED by 'shiyanlou';
#select host,user,password from user;
grant ALL privileges on shiyanlou.* to shiyanlou identified by 'shiyanlou';
flush privileges;
#show grants for shiyanlou;

中文csv导入数据库

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

use shiyanlou;

#show character set;

/*********table course********/
create table course (  
    id  integer not null,  
    name varchar(64) not null,  
    primary key (id)  
) default character set utf8mb4;  

load data infile '/home/shiyanlou/loudatabase/shiyanlou_course.csv'  
into table course character set utf8mb4
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by '\n';

#select * from course;

/*********table user********/
create table user (  
    id  integer not null,  
    name varchar(64) not null,  
    primary key (id)  
) default character set utf8mb4;  

load data infile '/home/shiyanlou/loudatabase/shiyanlou_user.csv'  
into table user character set utf8mb4
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by '\n';

/********table usercourse*********/
create table usercourse (  
    user_id  integer not null,  
    course_id  integer not null,  
    study_time integer not null,
    foreign key(user_id) references user(id), 
    foreign key(course_id) references course(id)
) default character set utf8mb4;  

load data infile '/home/shiyanlou/loudatabase/shiyanlou_usercourse.csv'  
into table usercourse character set utf8mb4
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by '\n';
 # 在第一列增加一列自动增长的id列
alter table usercourse add COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY first;

---

学习时间最长

#select user.name,course.name,study_time from usercourse,course,user where usercourse.course_id = course.id and user.id = usercourse.user_id group by user_id group by user.name,course.name;


create table favorite select user.name as user_name ,course.name as course_name,max(study_time) as study_time from usercourse,course,user where usercourse.course_id = course.id and user.id = usercourse.user_id group by user.name;

alter table favorite add COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY first;

---

查询慢

alter table usercourse add index user_Index(user_id);

alter table usercourse add index studytime_Index(study_time);

---

备份和恢复

/*************************/
crontab -u shiyanlou –e
#每个星期一凌晨3:00完全备份
0 3 * * 1 /usr/bin/mysqldump --opt --force -uroot shiyanlou> /usr/lib/mysql/"mysql-`date +'%Y%m%d-%H:%M:%S'`".sql
#周二到周天凌晨3:00增量备份
0 3 * * 2-7 mysqladmin -uroot flush-logs

mysqlbackup.sh

# /bin/bash  
DB_NAME="****"  
DB_USER="****"  
DB_PASS="****"  
BIN_DIR="/usr/bin"  
BACK_DIR="/data/backdata"  
DATE="mysql-`date +'%Y%m%d-%H:%M:%S'`"  
LogFile="$BACK_DIR"/dbbakup.log #日志记录保存的目录  
BackNewFile=$DATE.sql  
$BIN_DIR/mysqldump --opt --force -u$DB_USER  -p$DB_PASS $DB_NAME > $BACK_DIR/$DATE.sql  
echo ----------"$(date +"%y-%m-%d %H:%M:%S")"------------ >> $LogFile  
echo  createFile:"$BackNewFile" >> $LogFile  
#find "/data/backdata/" -cmin +1 -type f -name "*.sql" -print > deleted.txt  
find "/data/backdata/" -ctime +7 -type f -name "*.sql" -print > deleted.txt  
echo -e "delete files:\n" >> $LogFile  
#循环删除匹配到的文件  
cat deleted.txt | while read LINE  
do  
rm -rf $LINE  
echo $LINE>> $LogFile  
done  
echo "---------------------------------------------------------------" >> $LogFile