Mysql阶段性项目——QQ数据库管理

MySql 数据库设计与应用 第七章项目练习

阶段项目——QQ数据库管理

任务概述： 模拟QQ在线聊天系统

  • 后台数据库的创建

  • 基本数据表的创建

  • 表约束、 表间关系的添加

  • 进行数据增加、 删除、 修改、 查询

    一.创建表

    O1T7se.png

    O17Xm4.png

    O1HSt1.png

创建QQUser表

 # 创建数据库
 CREATE DATABASE `mysqlqq`;
 #打开数据库
 USE `mysqlqq`;
 
 #创建QQUser表
  CREATE TABLE `qquser` (
   `QQID` BIGINT(15) NOT NULL COMMENT 'QQ号',
   `passWord` VARCHAR(50) NOT NULL COMMENT '密码',
   `lastLogTime` DATETIME NOT NULL COMMENT '最后一次登录时间',
   `online` INT NOT NULL COMMENT '在线状态',
   `level` INT(50) DEFAULT'0' NOT NULL COMMENT '用户等级',
    PRIMARY KEY (`QQID`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;

创建Baseinfo表

 创建Baseinfo表
 CREATE TABLE `baseinfo` (
  `QQID` BIGINT(15) DEFAULT NULL COMMENT 'QQ号',
  `nickName` VARCHAR(50) NOT NULL COMMENT '昵称',
  `sex` INT DEFAULT NULL COMMENT '性别',
  `age` INT DEFAULT NULL COMMENT '年龄',
  `province` VARCHAR(50) DEFAULT NULL COMMENT '省份',
  `city` VARCHAR(50) DEFAULT NULL COMMENT '城市',
  `address` VARCHAR(50) DEFAULT NULL COMMENT '详细地址',
  `phone` VARCHAR(50) DEFAULT NULL COMMENT '联系方式',
   KEY `QQID` (`QQID`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

创建Relation表

  创建Relation表
 CREATE TABLE `relation` (
  `QQID` BIGINT(15) NOT NULL COMMENT '用户A的QQ号',
  `RelationQQID` BIGINT(15) NOT NULL COMMENT '用户B的QQ号',
  `RelationStatus` INT NOT NULL COMMENT '用户关系:0表示B是A的好友，1表示B是A的黑名单',
  PRIMARY KEY (`QQID`,`RelationQQID`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;

二.添加约束

  • QQ密码不得少于6位

  • 在线状态的值必须为0、 1、 2， 0表示在线， 1表示离线， 2表示隐身

  • 用户关系只能是数字0、 1， 0表示好友， 1表示黑名单人物

  • 性别允许为空值， 如果输入值就必须为0或1，0表示男， 1表示女

  • 用户等级默认值为0

  • 年龄必须是在1~100之间的整数

  •     -- 添加`qquser`表约束
ALTER TABLE `qquser` ADD CONSTRAINT CHECK(`QQID`>=6);
ALTER TABLE `qquser` ADD CONSTRAINT  CHECK(`online`IN(0,1,2));

  -- 添加`baseinfo`表约束
ALTER TABLE `baseinfo` ADD CONSTRAINT CHECK(`QQID`>=6);
ALTER TABLE `baseinfo` ADD CONSTRAINT CHECK(`sex`IN(NULL,0,1));
ALTER TABLE `baseinfo` ADD CONSTRAINT CHECK(`age`>=1 AND `age`<=100);

  -- 添加`relation`表约束
ALTER TABLE `relation` ADD CONSTRAINT CHECK(`RelationStatus`IN(0,1,2)); 
ALTER TABLE `relation` ADD CONSTRAINT CHECK(`QQID`>=6);
ALTER TABLE `relation` ADD CONSTRAINT CHECK(`RelationQQID`>=6);

三.建立表间关系

O367m6.jpg

-- 建立表关系
ALTER TABLE `baseinfo` ADD CONSTRAINT FOREIGN KEY(`QQID`) REFERENCES `qquser` (`QQID`);
ALTER TABLE `relation` ADD CONSTRAINT FOREIGN KEY(`QQID`) REFERENCES `qquser` (`QQID`);
ALTER TABLE `relation` ADD CONSTRAINT FOREIGN KEY(`RelationQQID`) REFERENCES `qquser` (`QQID`);

四.插入测试数据

  • 打开navicat
  • 导入文件
  • 插入成功

O36cwT.jpg

O36oOx.jpg

五.查询数据

  • 查询昵称为“小笨猪” 的用户信息

  • -- 查询昵称为“小笨猪” 的用户信息
SELECT *
FROM `baseinfo`
WHERE `nickName`='小笨猪';

  • 查询QQ号码为88662753的用户的所有好友信息

    显示QQ号码（ QQID） 、 昵称（ NickName） 、 年龄（ Age）

  • -- 查询QQ号码为88662753的用户的所有好友信息 显示QQ号码（ QQID） 、 昵称（ NickName） 、 年龄（ Age）
SELECT r.`QQID` AS 'QQ号', b.`QQID` AS '好友QQ号',b.`nickName` AS '昵称',b.`age` AS '年龄'
FROM `relation` AS r,`baseinfo` AS b
WHERE r.`QQID`='88662753' AND r. `RelationStatus`= 0 AND r.`RelationQQID`= b.`QQID`;

  • 查询当前在线用户信息

  • -- 查询当前在线用户信息
SELECT q.`QQID` AS 'QQ号' , b.`nickName` AS '昵称', b.`sex` AS '性别', b.`age` AS '年龄', b.`province` AS '省份',b.`city` AS '城市'
FROM `qquser` AS q ,`baseinfo` AS b
WHERE `online`=0 AND b.`QQID`=q.`QQID`;

  • 查询北京市、 年龄在18-45岁之间的在线用户的信息

  • -- 查询北京市、 年龄在18-45岁之间的在线用户的信息
SELECT q.`QQID` AS 'QQ号' , b.`nickName` AS '昵称', b.`sex` AS '性别', b.`age` AS '年龄', b.`province` AS '省份',b.`city` AS '城市'
FROM `qquser` AS q,`baseinfo` AS b
WHERE b.`city` LIKE '%北京%' AND (b.`age`BETWEEN 18 AND 45) AND `online`=0 AND b.`QQID`=q.`QQID`;

  • 查询QQ号码为54789625的用户的好友中每个省份的总人数
    按总人数由大到小排序

  • -- 查询QQ号码为54789625的用户的好友中每个省份的总人数 按总人数由大到小排序
SELECT b.`province` AS '省份',COUNT(*) AS '总人数'
FROM`relation` AS r,`baseinfo` AS b
WHERE r.`QQID`='54789625'AND r.`RelationStatus`= 0 AND b.`QQID`=r.`RelationQQID`
GROUP BY b.`province`
ORDER BY COUNT(*) DESC;

  • 查询至少有1000天未登录QQ账号的用户信息
    显示QQ号码、 最后一次登录时间、 等级、 昵称、 年龄
    按时间降序排序

  • -- 查询至少有1000天未登录QQ账号的用户信息 显示QQ号码、 最后一次登录时间、 等级、 昵称、 年龄 按时间降序排序
SELECT b.`QQID` AS 'QQ号码',q.`lastLogTime` AS '最后一次登录时间', q.`level` AS '用户等级',b.`nickName` AS '昵称',b.`age` AS '年龄'
 FROM `baseinfo` AS b,`qquser` AS q 
 WHERE DATEDIFF(NOW(),`lastLogTime`)>1000 AND b.`QQID`= q.`QQID`
 ORDER BY `lastLogTime` DESC ;

  • 查询QQ号码为54789625的好友中等级为10级以上的“月亮” 级
    用户信息

  • -- 查询QQ号码为54789625的好友中等级为10级以上的“月亮” 级 用户信息
SELECT r.`QQID` AS 'QQ号' ,r.`RelationQQID` AS '好友QQ号', b.`nickName` AS '昵称', b.`sex` AS '性别', b.`age` AS '年龄',q.`level` AS '等级'
FROM `baseinfo` AS b
JOIN `qquser` AS q ON b.`QQID` =q.`QQID`
JOIN `relation` AS r ON r.`RelationQQID`=q.`QQID`
WHERE r.`QQID`='54789625' AND `RelationStatus` =0 AND q.`level`>=10 ;

  • 查询QQ号码为54789625的好友中隐身的用户信息
    显示QQ号、 好友QQ号、 昵称、 年龄

  • -- 查询QQ号码为54789625的好友中隐身的用户信息 显示QQ号、 好友QQ号、 昵称、 年龄
SELECT r.`QQID` AS 'QQ号',r.`RelationQQID` AS '好友QQ号',b.`nickName` AS '好友昵称',b.`age` AS '年龄'
  FROM  `relation` AS r
  JOIN `baseinfo` AS b ON b.`QQID` =r.`RelationQQID`
  JOIN `qquser` AS q ON r.`RelationQQID`=q.`QQID`
  WHERE r.`QQID`='54789625' AND r.`RelationStatus`=0 AND q.`online`=2;

  • 查询好友超过20个的用户QQ号码及其好友总数

  • -- 查询好友超过20个的用户QQ号码及其好友总数
  SELECT `QQID` AS 'QQ号码',COUNT(*) AS '好友总数'
  FROM `relation` AS r
  WHERE `RelationStatus` =0
  GROUP BY `QQID`
  HAVING COUNT(*)>20;

  • 查看信誉度统计

  • -- 查询好友超过20个的用户QQ号码及其好友总数
  SELECT `QQID` AS 'QQ号码',COUNT(*) AS '好友总数'
  FROM `relation` AS r
  WHERE `RelationStatus` =0
  GROUP BY `QQID`
  HAVING COUNT(*)>20;

  • 管理员需要查询被当作黑名单人物次数排名前10的用户

  • -- 查看信誉度统计 管理员需要查询被当作黑名单人物次数排名前10的用户
  SELECT `RelationQQID` AS 'QQ号',COUNT(*) AS 黑名单次数
  FROM Relation
  WHERE `RelationStatus` = 1
  GROUP BY RelationQQID
  ORDER BY COUNT(*) DESC
  LIMIT 10 ;

六.修改数据

  • 假设我的QQ号码为8855678， 今天我隐身登录

  • -- 假设我的QQ号码为8855678， 今天我隐身登录
SELECT * 
FROM `qquser`
WHERE `QQID`='8855678';
UPDATE `qquser` SET `online`=2,`lastLogTime`=NOW()
WHERE `QQID`='8855678';

  • 假设我的QQ号码为8855678

    修改我的昵称为“被淹死的鱼” ， 地址为“解放中路6号院106室”

    -- 假设我的QQ号码为8855678 修改我的昵称为“被淹死的鱼” ， 地址为“解放中路6号院106室”
 SELECT *
 FROM `baseinfo`
 WHERE `QQID` ='8855678';
 UPDATE `baseinfo` SET `nickName`='被淹死的鱼',`address`='解放中路6号院106室'
 WHERE `QQID` ='8855678';

  • 假设我的QQ号码为8855678
    将我的好友“248624066” 拖进黑名单

  • -- 假设我的QQ号码为8855678   将我的好友“248624066” 拖进黑名单
SELECT *
FROM `relation`
WHERE `QQID`='8855678' AND `RelationQQID`='248624066';
UPDATE `relation` SET `RelationStatus` =1
WHERE `QQID`='8855678' AND `RelationQQID`='248624066';

  • 为了提高QQ用户的聊天积极性
    把等级小于6级的用户等级都提升1个级别

  • -- 为了提高QQ用户的聊天积极性 把等级小于6级的用户等级都提升1个级别
SELECT *
FROM `qquser`
WHERE `level`<6;
UPDATE `qquser`SET`level`=`level`+1
WHERE`level`<6;

  • 管理员将超过365天没有登录过的QQ锁定
    将等级设定为-1

  • -- 管理员将超过365天没有登录过的QQ锁定 将等级设定为-1
SELECT *
FROM `qquser`
WHERE (NOW()-`lastLogTime`)>=365;
UPDATE `qquser` SET`level`=-1
WHERE  (NOW()-`lastLogTime`)>=365;

  • 为了奖励用户， 将好友数量超过20的用户等级提升1个级别

    -- 为了奖励用户， 将好友数量超过20的用户等级提升1个级别
SELECT *
FROM `qquser`
WHERE `QQID` IN(
SELECT `QQID`
FROM `relation` 
WHERE `RelationStatus`=0
GROUP BY `QQID`
HAVING COUNT(*)>20);

UPDATE `qquser` SET `level`=`level`+1
WHERE`QQID`IN(
SELECT `QQID`
FROM `relation` 
WHERE `RelationStatus`=0
GROUP BY `QQID`
HAVING COUNT(*)>20);

  • 把QQ号码为286314的用户的好友“嘟嘟鱼” 拖进黑名单中

    -- 把QQ号码为286314的用户的好友“嘟嘟鱼” 拖进黑名单中

SELECT *
FROM `baseinfo` AS b ,`relation` AS r
WHERE r.`QQID`='286314' AND `RelationStatus` =0 AND `RelationQQID`IN(SELECT `QQID`
FROM `baseinfo`
WHERE `nickName`='嘟嘟鱼');

UPDATE `relation` SET`RelationStatus` =1
WHERE `RelationQQID`IN(SELECT `QQID`
FROM `baseinfo`
WHERE `nickName`='嘟嘟鱼') AND `RelationStatus` =0;

七.删除数据

  • 把QQ号码为54789625的用户的黑名单中的用户删除

  • -- 把QQ号码为54789625的用户的黑名单中的用户删除
SELECT * 
FROM `relation`
WHERE `QQID`='54789625'AND`RelationStatus`=1;

DELETE FROM `relation`
WHERE `QQID`='54789625'AND`RelationStatus`=1;

  • QQ号码为622009019的用户多次在QQ中发布违法信息
    造成了很坏的影响， 因此管理员决定将其删除

  • -- QQ号码为622009019的用户多次在QQ中发布违法信息造成了很坏的影响， 因此管理员决定将其删除
SELECT *
 FROM `baseinfo`
 WHERE `QQID`='622009019';
 
 
 DELETE FROM `relation`
 WHERE `QQID`='622009019' OR`RelationQQID`='622009019';
 
 DELETE FROM `baseinfo`
 WHERE `QQID`='622009019';
 
  
 DELETE FROM `qquser`
 WHERE `QQID`='622009019';

  • 删除超过1000天没有登录过的QQ用户信息

    -- 删除超过1000天没有登录过的QQ用户信息
SELECT `QQID` 
FROM `qquser`
WHERE DATEDIFF(NOW(),`lastLogTime`)>1000;

DELETE FROM `relation`
WHERE `QQID` IN(SELECT `QQID` 
FROM `qquser`
WHERE DATEDIFF(NOW(),`lastLogTime`)>1000) OR `RelationQQID` IN(SELECT `QQID` 
FROM `qquser`
WHERE DATEDIFF(NOW(),`lastLogTime`)>1000);

DELETE FROM `baseinfo`
WHERE `QQID` IN(SELECT `QQID` 
FROM `qquser`
WHERE DATEDIFF(NOW(),`lastLogTime`)>1000);

DELETE FROM `qquser` 
WHERE DATEDIFF(NOW(),`lastLogTime`)>1000;

SELECT `QQID` 
FROM `qquser`
WHERE DATEDIFF(NOW(),`lastLogTime`)>1000;
posted @   1(*^_^*)1小菜  阅读(2309)  评论(0)  编辑  收藏  举报
相关博文：
·  集合:学生管理系统
·  集合:图书管理系统
·  面向对象课程设计--QQ聊天室
·  Mysql 增删改查语言系列
·  Qt5-C++-GUI-编程秘籍（全）
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~
点击右上角即可分享
微信分享提示
SQL AI 助手