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

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

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

后台数据库的创建
基本数据表的创建
表约束、表间关系的添加
进行数据增加、删除、修改、查询

一.创建表

创建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);

三.建立表间关系

-- 建立表关系

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
导入文件
插入成功

五.查询数据

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

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

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;

巴特西

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

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

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

一.创建表

创建QQUser表

创建Baseinfo表

创建Relation表

二.添加约束

三.建立表间关系

四.插入测试数据

五.查询数据

六.修改数据

七.删除数据

最新文章

热门文章

巴特西

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

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

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

一.创建表

创建QQUser表

创建Baseinfo表

创建Relation表

二.添加约束

三.建立表间关系

四.插入测试数据

五.查询数据

六.修改数据

七.删除数据

最新文章

热门文章

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