数据分析师Mysql复习模拟自考

数据分析师Mysql模拟自考

---

一、数据准备背景：

最近一直在学mysql，为了检测所学的掌握程度，特有此mysql自考一文;

二、数据准备建表语句：

场景：每个用户在登陆某个系统的时候，会有登陆的ip记录，登陆的当前时间记录等等，以及最重要的用户的uid，即用户的唯一标识；

CREATE TABLE `user_login_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) DEFAULT NULL COMMENT '用户id',
  `curr_day` date DEFAULT NULL COMMENT '当天时间，yyyyMMdd',
  `login_ip` varchar(32) DEFAULT '0' COMMENT '登录的ip',
  `login_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '登录时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=51505 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

三、造数准备SQL脚本

大约造数5万多条，用来进行模拟真实环境数据

DROP PROCEDURE IF EXISTS user_login_time;--如果存在此存储过程则删掉
DELIMITER $
CREATE PROCEDURE user_login_time()
BEGIN
    DECLARE i INT DEFAULT 1;
		
    WHILE i<=51000 DO
        INSERT INTO `user_login_time`(`uid`, `curr_day`, `login_ip`, `login_date`) VALUES (FLOOR(RAND()*50000 + 500), NOW(), "127.0.0.1", NOW());
        SET i = i+1;
    END WHILE;
END $
CALL user_login_time();

四、自考题目汇总以及参考答案：

-- 统计用户
select count(*) from user_login_time;

-- 统计这张表，一共有多少个用户登录过
答案：select count(DISTINCT uid) from user_login_time ;

-- 统计指定某天的用户总的登录记录：
select count(curr_day) from user_login_time where curr_day="2020-11-07";
select * from user_login_time where curr_day!="2020-11-07";

-- 找出2020-11-07这一天中最先登录的用户
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date asc limit 1;

-- 找出2020-11-07这一天中最后登录的用户
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date desc limit 1;

-- 找出2020-11-07这一天中前十个登录的用户
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date limit 10; -- 错误没有考虑重复uid的情况；
select distinct uid,login_date from user_login_time where curr_day="2020-11-07" order by login_date limit 0,10;

-- 找出2020-11-07这一天中最后十个登录的用户
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date desc limit 10;-- 错误
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" and uid is not null order by login_date desc limit 10;-- 正确：
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" and uid is not null and uid!=''  order by login_date desc limit 10;-- 增强版，去掉空字符串情况

-- 需要找出整个表中，登录次数大于一次的所有用户；2， 3， ，4次都有可能：考察group by 
select uid,curr_day from user_login_time group by curr_day having count(curr_day)>1; -- 错误
select uid,curr_day from user_login_time group by uid having count(curr_day)>1; -- 凑巧
select uid,curr_day from user_login_time group by uid having count(uid)>1; -- 正确

五、结尾：

由于本人初次写作，若在本文中发现问题，谢谢指出，不胜感激！！！​