数据清洗

数据清洗即是将爬下来未处理的数据文件（txt、json等格式），转化为csv文件，一般需要通过sed把里面不必要的字符删除，再以通过awk以一定规律分割，最后将脚本的处理结果重定向到csv文件中。

一般处理思路：
sed掐头-去尾-换行-置空值-删符号-删元素（列名）-awk分隔、添加列名 > 重定向到csv

操作类型 sed/awk 实现方案 示例
去除结构标记 删除JSON头尾 sed 's/^{"data":\[//; s/\]}$//'
字段分隔 替换分隔符为换行符 sed 's/},{/\n/g'
特殊字符处理 移除转义字符 sed 's/\\//g'
空值标准化 统一空值表示 sed 's/""/null/g'
引号处理 删除所有引号 tr -d '"'
字段提取 按位置重组字段 awk '{print $2,$1,$3}'

导出的csv文件用excel打开可能中文显示会乱码（wps不会），需正常显示可如下操作：

  • 右键csv文件以txt方式打开-另存为-设置编码为ANSI-确定保存
    此后用excel打开此文件即可正常显示中文

通过mysql对数据库服务器操作

1
mysql -h127.0.0.1 -P3306 -uroot -proot123456 test -e "select * from student"

-h：MySQL服务器的ip地址或主机名；
-u：连接MySQL服务器的用户名；
-e：执行mysql内部命令；
-p：连接MySQL服务器的密码。
-P：连接MySQL服务器的端口

mysqldump 备份命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#1.导出education数据库里面的users表的表数据和表结构(下面以users表为例)
mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] 数据库名 表名 > 导出的文件名.sql
mysqldump -uroot -h127.0.0.1 -proot -P3306 education users > d:/user.sql

#导出多张表:
mysqldump -uroot -proot --databases test --tables t1 t2 > two.sql

#2.只导出表结构不导表数据，添加“-d“命令参数
mysqldump -uroot -h127.0.0.1 -proot -p3306 -d education users > d:/user.sql

#3.只导出表数据不导表结构，添加“-t"命令参数
mysqldump -uroot -h127.0.0.1 -proot -p3306 -t education users > d:/user.sql

#4.导出数据库的表结构和表数据(导入的时候需要指定数据库，保证指定的数据库存在)
#导出命令
mysqldump -h127.0.0.1 -P3306 -uroot -proot education  > d:/database.sql
#导入命令
mysql -uroot -proot -h127.0.0.1 -P3306 education < d:/database.sql

#5.导出特定数据库的所有表格的表结构及其数据，添加"--databases“命令参数(导入的时候直接执行导出sql就行)
#导出命令
mysqldump -h127.0.0.1 -P3306 -uroot -proot education --databases education > database.sql
#导入命令:
mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all database.sql

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#1.清洗数据《infotest.txt》保存成《infotest.csv》 
sed 's/\[{//g' /root/sh/infotest.txt |  #掐头
sed 's/}\]//g' |  #去尾
sed 's/""/null/g' |  #置空值
sed 's/"//g' |  #删引号
#删除元素说明
sed 's/catchTime://g' |
sed 's/commentCount://g' |
sed 's/content://g' |
sed 's/createTime://g' |
sed 's/pic_list://g' |
sed 's/praiseCount://g' |
sed 's/reportCount://g' |
sed 's/source://g' |
#以逗号分割，添加列名，保存到csv
awk -v FS="," -v OFS="," 'BEGIN{print "catchTime,commentCount,content,createTime,pic_list,praiseCount,reportCount,source"}{print $1,$2,$3,$4,$5,$6,$7,$8}' \
> /root/sh/infotest.csv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#2.清洗数据《douban.txt》保存成《douban.csv》
sed 's/{"subjects":\[{//g' /root/sh/douban.txt |  #掐头
sed 's/}\]}//g' |  #去尾
sed 's/},{/\n/g' |  #换行
sed 's/\\//g' |  #删\号
sed 's/""/null/g' |  #置空值
sed 's/"//g' |  #删引号
#删除元素说明
sed 's/id://g' |
sed 's/title://g' |
sed 's/episodes_info://g' |
sed 's/rate://g' |
sed 's/cover_x://g' |
sed 's/url://g' |
sed 's/playable://g' |
sed 's/cover://g' |
sed 's/cover_y://g' |
sed 's/is_new://g' |
#以逗号分割，添加列名，保存到csv
awk -v FS="," -v OFS="," 'BEGIN{print "id,title,episodes_info,rate,cover_x,url,playable,cover,cover_y,is_new"}{print $8,$4,$1,$2,$3,$5,$6,$7,$9,$10}' \
>/root/sh/douban.csv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#编写shell脚本/root/shell/mysqlcsv.sh 实现：
#1.如果douban表存在则删除 
#2.如果douban表不存在则新建 
#3.导入douban.csv数据到douban表中,建表过程为根据导入的csv文件自动创建表 
#4.查询douban表验证结果

#设置mysql连接参数
dbhost="127.0.0.1"  #主机名
dbprot=3306  #端口
dbuser="root"  #用户名
dbpass="root123456"  #密码
db="test"  #表空间

#如果douban表存在则删除 
sql1="drop table if exists douban"
mysql -h$dbhost -P$dbprot -u$dbuser -p$dbpass $db -e  "$sql1"

#获取第一行（列名）
names=`head -n +1 /root/sh/douban.csv | sed 's/,/ /g'`
#找出最后一个列名
lastname=`echo "$names" | awk '{print $NF}'`

#如果douban表不存在则新建 
sql2="create table if not exists douban("
 for i in $names
    do
        if [ $i == "$lastname" ]
        then 
            sql2="$sql2$i varchar(200))"
        else
            sql2="$sql2$i varchar(200),"
        fi
        
    done
mysql -h$dbhost -P$dbprot -u$dbuser -p$dbpass $db -e  "$sql2"

#导入数据
sql3="LOAD DATA INFILE '/usr/local/mysql/data/douban.csv' INTO TABLE douban
CHARACTER SET utf8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES"
mysql -h$dbhost -P$dbprot -u$dbuser -p$dbpass $db -e  "$sql3"

#查看表和数据
sql4="select * from douban"
mysql -h$dbhost -P$dbprot -u$dbuser -p$dbpass $db -e  "$sql4"

练习：实现每天凌晨3点10分备份数据库test到/root/backup/db

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#一、编写shell脚本为 /root/shell/backdb.sh 
#1.判断路径/root/backup/db是否存在，不存在创建，存在开始备份 
#2.备份后的文件以时间命名，如20230401110101.sql 
#3.在备份的同时，检查是否有10天前的备份数据库文件，有则删除之 

# 设置mysql连接参数
dbhost="127.0.0.1"  #主机名
dbport=3306  #端口
dbuser="root"  #用户名
dbpwd="root123456"  #密码
db="test"  #表空间

# 设置备份目录
backup_dir="/root/backup/db"

# 1. 检查并创建备份目录
if [ ! -d "$backup_dir" ]; then
    mkdir -p "$backup_dir"
    echo "创建备份目录: $backup_dir"
else
    echo "备份目录存在: $backup_dir"
fi

# 2. 生成带时间戳的备份文件名
backup_file="$backup_dir/$(date +'%Y%m%d%H%M%S').sql"

# 执行数据库备份
echo "开始备份数据库 $db 到 $backup_file"
mysqldump -h$dbhost -P$dbport -u$dbuser -p$dbpwd $db > $backup_file

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "数据库备份成功!"
else
    echo "数据库备份失败!" >&2
    exit 1
fi

# 3. 删除10天前的备份文件
find "$backup_dir" -name "*.sql" -mtime +10 -exec rm -f {} \;
echo "已清理10天前的备份文件"

# 显示当前备份文件
echo "当前备份文件:"
ls -lh "$backup_dir"/*.sql

#二、编写定时任务，任务是执行上面的shell脚本
sudo vim /etc/crontab
#每天凌晨3点10分备份数据库
10 3 * * * root /root/sh/backdb.sh