创建 QQ 数据库
- # 创建数据库
- CREATE DATABASE QQ
- # 创建表名 并且添加列
- DROP TABLE IF EXISTS `dbo.BaseInfo`;
- CREATE TABLE `student`(
- QQID INT PRIMARY KEY AUTO_INCREMENT,
- NickName VARCHAR(32),
- Sex INT,
- Age INT ,
- Province VARCHAR(256),
- City VARCHAR(32),
- Address VARCHAR(32),
- Phone INT
- )
- # 修改表名
- ALTER TABLE student RENAME BaseInfo
- # 添加 BaseInfo 表 qq 表的信息数据
- INSERT INTO student (QQID,NickName,Sex,Age,Province,City,Address,Phone)VALUES
- (8855678, 独行侠, 1,38, 北京, 海淀区, 上地, 003),
- (54789625, 蝴蝶飞飞, 1,16, 北京, 朝阳区, 亚运村, 001),
- (88662753, 青青草, 0,20, 河南省, 安阳, 汤阴, 002);
- # 创建 QQUser 表并且添加列
- CREATE TABLE `QQUser`(
- QQID INT PRIMARY KEY AUTO_INCREMENT,
- PASSWORD VARCHAR(32),
- LastLogTime DATETIME,
- OnLine INT ,
- LEVEL INT
- )
- # 添加 QQUser 表的信息数据
- INSERT INTO QQUser (QQID,PASSWORD,LastLogTime,OnLine,LEVEL)VALUES
- (8855678,guest0221,2008-02-21 16:28:20:000,1,6),
- (54789625,add521#&,2008-02-16 17:01:35:000,2,1),
- (88662753,admin0219,2008-02-19 21:08:35:000,0,5);
- # 删除表名
- DROP TABLE QQUser
- # 创建 QQUser 表并且添加列
- CREATE TABLE `Relation`(
- QQID INT ,
- RelationQQID INT,
- RelationStalus INT
- )
- # 添加 QQUser 表的信息数据
- INSERT INTO Relation (QQID,RelationQQID,RelationStalus)VALUES
- (54789625,88662753,0),
- (88662753,8855678,1),
- (54789625,8855678,0);
01. 查询 QQ 号码为 54789625 的所有好友信息, 包括 QQ 号码, 昵称, 年龄
- SELECT QQID,NickName,Age FROM baseinfo WHERE QQID =54789625
- #02. 查询当前在线用户的信息
- SELECT *FROM qquser WHERE OnLine!=0
- #03. 查询北京的年龄在 18 至 45 岁之间的在线用户的信息
- SELECT *FROM baseinfo,qquser
WHERE baseinfo.QQID =qquser.QQID AND baseinfo.Province = 北京 AND baseinfo.Age BETWEEN 18 AND 45 AND qquser.OnLine>0
#04. 查询昵称为青青草的用户信息
SELECT *FROM baseinfo WHERE NickName = 青青草
- #05. 查询 QQ 号码为 54789625 的用户的好友中每个省份的总人数, 并且总人数按由大到小排序
- SELECT COUNT(Province) FROM baseinfo WHERE QQID IN (
- SELECT QQID FROM relation WHERE RelationQQID
- IN(
- SELECT RelationQQID FROM relation WHERE QQID =54789625 )
- )
- GROUP BY Province
- #06. 查询至少有 150 天未登录 QQ 账号的用户信息, 包括 QQ 号码, 最后一次登录时间等级昵称年龄, 并按时间的降序排列
- SELECT qquser.QQID, LastLogTime,LEVEL,NickName,Age FROM baseinfo,qquser WHERE baseinfo.QQID=qquser.QQID AND baseinfo.QQID IN(
- SELECT QQID FROM qquser WHERE DATEDIFF(NOW(),LastLogTime ) >=150
- )
- GROUP BY LastLogTime
- #07. 查询 QQ 号码为 54789625 的好友中等级为 10 级以上的月亮级用户信息
- SELECT *FROM baseinfo WHERE QQID IN(SELECT QQID FROM qquser WHERE LEVEL>10 AND QQID IN(
- SELECT QQID FROM relation WHERE RelationQQID IN (
- SELECT RelationQQID FROM relation WHERE QQID =54789625
- )))
- #08.-- 查询 QQ 号码为 54789625 的好友中隐身的用户信息
- SELECT *FROM baseinfo WHERE QQID IN (SELECT QQID FROM qquser WHERE OnLine=0 AND QQID IN (
- SELECT QQID FROM relation WHERE RelationQQID IN (
- SELECT RelationQQID FROM relation WHERE QQID =54789625
- )))
- #09.-- 查询好友超过 20 个的用户信息
- SELECT *FROM baseinfo WHERE QQID IN (
- SELECT QQID FROM relation WHERE RelationQQID IN(
- SELECT RelationQQID FROM relation GROUP BY QQID HAVING COUNT(RelationQQID)>20)
- )
- #10. 为了查看信誉度, 管理员需要查询被当做黑名单人物次数排名前 3 的用户
- SELECT * FROM baseinfo WHERE baseinfo.QQID IN(
- SELECT qquser.QQID FROM qquser WHERE baseinfo.QQID=qquser.QQID AND qquser.QQID IN(
- SELECT relation.QQID FROM relation WHERE relation.RelationStalus=1
- )
- ORDER BY qquser.Level DESC
- )
- LIMIT 3
- ## 用例 2: 修改数据
- #01. 假设我的 QQ 号码为 8855678, 今天我隐身登录
- UPDATE qquser SET OnLine=0 WHERE QQID =8855678
- #02. 假设我的 QQ 号码为 8855678, 修改我的昵称为被淹死的鱼, 地址为解放中路号院 123 室
UPDATE baseinfo SET NickName = 被淹死的鱼, Address = 解放中路号院 123 室 WHERE QQID=8855678
- #03. 假设我的 QQ 号码为 54789625, 将我的好友青青草拖进黑名单
- UPDATE relation SET RelationStalus =1 WHERE QQID =54789625
- #04. 为了提高 QQ 用户的聊天积极性, 把等级小于 6 级的用户的等级都提升 1 个级别
- UPDATE qquser SET LEVEL =LEVEL+1 WHERE LEVEL<6
- #05. 管理员将超过 365 天没有登录过的 QQ 锁定 (即将等级值设定为 - 1)
- UPDATE qquser SET LEVEL =-1 WHERE DATEDIFF(NOW(),LastLogTime ) >=365
- #06. 为了奖励用户, 将好友数量超过 20 的用户等级提升 1 个级别
- UPDATE qquser SET LEVEL=LEVEL+1 WHERE (
- SELECT RelationQQID FROM relation GROUP BY QQID HAVING COUNT(RelationQQID)>20)
- #07. 把 QQ 号码为 54789625 的用户的好友嘟嘟鱼拖进黑名单中
- UPDATE relation SET RelationStalus =1 WHERE QQID =54789625
- ## 用例 3: 删除数据
- #1. 把 QQ 号码为 54789625 的用户黑名单中的用户删除
- DELETE FROM relation WHERE QQID=54789625
- #2.QQ 号码为 54789625 的用户多次在 QQ 中发布违法信息, 造成了很坏的影响, 因此管理员决定将其删除
- DELETE FROM baseinfo WHERE QQID =54789625
- #3. 管理员将超过 1000 天没有登录过的 QQ 删除
- UPDATE FROM qquser WHERE DATEDIFF(NOW(),LastLogTime ) >=1000
来源: http://www.bubuko.com/infodetail-2513135.html