11/30/2021--数据库 6 综合实验

1、数据导入及数据清洗

（1）将原始数据文件（txt.）导入到Mysql，表结构自定                                                         

        导入的方式：1、通过mysql的可视化工具进行导入（navicat等）；2、通过代码实现导入（python等）

Navicat怎样导入Excel表格和txt文本的数据https://blog.csdn.net/qq_39135287/article/details/80881746https://blog.csdn.net/qq_39135287/article/details/80881746

（2）清除标题或者内容是404的数据

SET SQL_SAFE_UPDATES = 0;            //如出现安全权限问题可以如左设置
delete from train_data
WHERE title="404" or content=null;
SET SQL_SAFE_UPDATES = 1;

（3）清除内容是null或者是字符串null的数据

delete from train_data
WHERE publishtime is null;

2、数据统计

（1）计算该网站每天的pv、uv，按各自的大小从高到低排序。

- data中的clicktime为unix时间戳，须进行转化，并修改更新时间格式。

  UNIX时间戳转化https://blog.csdn.net/weixin_34417635/article/details/93218915?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163826210816780271912792%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163826210816780271912792&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-2-93218915.first_rank_v2_pc_rank_v29&utm_term=unix%E6%97%B6%E9%97%B4%E6%88%B3%E8%BD%AC%E6%8D%A2%E6%88%90%E6%97%B6%E9%97%B4+MySQL&spm=1018.2226.3001.4187https://blog.csdn.net/weixin_34417635/article/details/93218915?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163826210816780271912792%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163826210816780271912792&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-2-93218915.first_rank_v2_pc_rank_v29&utm_term=unix%E6%97%B6%E9%97%B4%E6%88%B3%E8%BD%AC%E6%8D%A2%E6%88%90%E6%97%B6%E9%97%B4+MySQL&spm=1018.2226.3001.4187- 转化后可在原列表进行更新，也可以重新增加一列并加入数据。

update train_data set clicktime = from_unixtime(clicktime,'%Y-%m-%d');

alter table train_data add column clickt varchar(255) not null;
update train_data set clickt=from_unixtime(clicktime);

- 最后统计pv, uv

//pv：每天各页面的浏览量
select count(date(clickt)),date(clickt)
from train_data
group by date(clickt)
order by count(date(clickt)) DESC;  

//uv：电脑的IP的访问总量
select date(clickt),count(distinct userid)
from train_data
group by date(clickt)
order by count(distinct userid) DESC;

（2）查询3月份十大热点新闻，列出ID及标题

        注：热点新闻：访问用户最多（非访问量）的新闻。

select newsid,title,count(newsid)
from train_data
group by newsid
order by count(newsid) desc;

（3）查询3月份的排名前三的忠实用户  

         注：忠实用户：每天都访问网站的用户

select userid,count(userid)
from 
(select distinct date(clickt),userid
from train_data) as loyaluser
group by userid
order by count(userid) desc;

（4）请根据3月份的数据计算24小时内各时段（以小时为单位）的总访问量（自己定义什么是访问量），给出该网站的用户访问习惯结论

select time_format(time(clickt), '%H:00:00')as time,count(time_format(time(clickt), '%H:00:00'))as view
from 
(select time_format(time(clickt), '%H:00:00'),clickt
from train_data)as Houradd
group by time_format(time(clickt), '%H:00:00')
order by time_format(time(clickt), '%H:00:00');

（5）附加题：同时查询具有特定词语的新闻如：“存款”和“余额宝”，“中国”和“工行”，“马航”和“飞机”等等，有什么样的实现方式（SQL、优化等等）。因为数据量不足原因，理论上的优化也可以。（个人拙见，不保证正确！）

~ 使用locate或position函数

LOCATE和POSITION函数使用方法https://blog.csdn.net/dy357978912/article/details/9269053?ops_request_misc=&request_id=&biz_id=102&utm_term=MYSQL%20POSITION%E5%87%BD%E6%95%B0%E4%B8%8D%E5%8F%AF%E7%94%A8&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-2-9269053.first_rank_v2_pc_rank_v29&spm=1018.2226.3001.4187https://blog.csdn.net/dy357978912/article/details/9269053?ops_request_misc=&request_id=&biz_id=102&utm_term=MYSQL%20POSITION%E5%87%BD%E6%95%B0%E4%B8%8D%E5%8F%AF%E7%94%A8&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-2-9269053.first_rank_v2_pc_rank_v29&spm=1018.2226.3001.4187

select * 
from train_data
where locate('飞机',title)>0;

~ 字符串函数 find_in_set(str,strlist)不可用，此函数针对以逗号分隔的字符串列

SELECT content FROM train_data WHERE find_in_set('飞机', ‘飞机,巴士,汽车');