MySQL基础面试题

START TRANSACTION; 
-- 创建用户表
CREATE TABLE `account` (
  `id` int(1) UNSIGNED NOT NULL COMMENT '用户id',
  `point` int(4) NOT NULL COMMENT '总积分',
  `signday` int(4) NOT NULL COMMENT '连续签到天数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `account` ADD PRIMARY KEY (`id`);
-- 创建用户积分签到表
CREATE TABLE `signup` (
  `id` int(11) NOT NULL COMMENT '用户id',
  `signtime` int(11) NOT NULL COMMENT '签到时间',
  `point` int(11) NOT NULL COMMENT '领取积分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `account`
  MODIFY `id` int(1) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id';
COMMIT;

<?php
// 通过分析上面的规律，我们可以看得出，当连续第三天的时候就是前面两天签到的分数相加
// 1.创建数据库连接
$mysqli = new mysqli('127.0.0.1','root','QQadmin001','test');
// 查询数据验证用户最近一次的签到信息
$sql = "select * from signup where id=1 order by signtime desc limit 0,1";
$result = $mysqli->query($sql);
$arr = [];
while($row = mysqli_fetch_assoc($result)){
    $arr['id'] = $row['id'];
    $arr['point'] = $row['point'];
    $arr['signtime'] = $row['signtime'];
}
$insertSql = '';
$updateSql = '';
// 判断是否有数据
if (count($arr) == 0) {
    // 1.如果没数据，则表示用户是第一次登陆,用户是第一次登陆，直接增加一个积分即可
    $insertSql = "insert into signup(`id`,`signtime`,`point`) values(1,".time().",1)";
    $updateSql = "update account set signday = 1 , point=1 where id=1";
}else{
    // 做一天只能登录一次验证
    if (time() - $arr['signtime'] < 24*3600) {
        echo "今天已经登录过了!";
        die();
    }else{
        // 2.如果有数据，则表示用户不是第一次登陆，则进行登录的天数做验证
    // 查询用户连续登录的天数
    $daySql = "select signday from account where id=1";
    $dayRes = $mysqli->query($daySql);
    $day = (mysqli_fetch_assoc($dayRes))['signday'];
    if($day < 2 ) {
        // 还未连续登录到两天
        $updateSql = "UPDATE `account` SET `point`=(point+1),`signday`=(signday+1) WHERE id=1";
        $insertSql = "insert into signup(`id`,`signtime`,`point`) values(1,".time().",1)";
    }elseif (($day> 2 || $day == 2) && (time()-$arr['signtime'] < 24*3600)){
        // 已经连续登录了两次，并且最近登录的时间与现在登录的时间在一天内
        $queryTwoSql = "select * from signup where id=1 order by signtime desc limit 0,2";
        $twoResult = $mysqli->query($queryTwoSql);
        $twoArr = [];
        while($row = mysqli_fetch_assoc($twoResult)) {
            array_push($twoArr, $row['point']);
        }
        // 当前签到的积分
        $nowPoint = array_sum($twoArr);
        $updateSql = "UPDATE `account` SET `point`=(point+$nowPoint),`signday`=(signday+1) WHERE id=1";
        $insertSql = "insert into signup(`id`,`signtime`,`point`) values(1,".time().",$nowPoint)";
    }elseif (($day> 2 || $day == 2) && (time()-$arr['signtime'] > 24*3600)){
        // 已经连续登录了两次，并且最近登录的时间与现在登录的时间超过一天内
        $queryTwoSql = "select * from signup where id=1 order by signtime desc limit 0,2";
        $twoResult = $mysqli->query($queryTwoSql);
        $twoArr = [];
        while($row = mysqli_fetch_assoc($twoResult)) {
            array_push($twoArr, $row['point']);
        }
        // 当前签到的积分
        $insertSql = "insert into signup(`id`,`signtime`,`point`) values(1,".time().",1)";
        $updateSql = "update account set signday = 1 , point=1 where id=1";
    }
    }
}
if($mysqli->query($insertSql)) {
    if($mysqli->query($updateSql)) {
        echo "插入成功!";
    }
}