Jin_evan 2016-10-20 07:38 采纳率: 100%
浏览 965
已采纳

mysql 数据库小练习 有懂得帮我码下还有没有语法错误

#创建QQDB#
CREATE DATABASE IF NOT EXISTS QQDB;

#使用数据库#
USE QQDB;

#创建QQUser#
CREATE TABLE IF NOT EXISTS QQUser(
QQID BIGINT PRIMARY KEY NOT NULL COMMENT'QQ帐号',
PassWord VARCHAR(20)NOT NULL COMMENT'密码',
LastLogTime DATETIME NOT NULL COMMENT'最后一次登录时间',
Online INT NOT NULL COMMENT'在线状态',
Level INT NOT NULL COMMENT'用户等级'
) COMMENT='用户表';
qquser
#创建BaseInfo#
CREATE TABLE IF NOT EXISTS BaseInfo(
QQID BIGINT PRIMARY KEY NOT NULL COMMENT'QQ帐号',
NickName VARCHAR(10) NOT NULL COMMENT'昵称',
Sex INT NOT NULL COMMENT'性别',
Age INT NOT NULL COMMENT'年龄',
Province VARCHAR(5)NOT NULL COMMENT'省份',
City VARCHAR(10)COMMENT'城市',
Address VARCHAR(20)COMMENT'详细地址',
Phone CHAR(14)COMMENT'联系方式'

)COMMENT='用户表';

#建立关系表#
CREATE TABLE IF NOT EXISTS Relation(
QQID BIGINT NOT NULL COMMENT'QQ号码',
RelationQQID BIGINT NOT NULL COMMENT'关系QQ号',
RelationStatus INT NOT NULL COMMENT'用户关系'
)COMMENT='关系表';

#外键约束#

ALTER TABLE Relation
ADD CONSTRAINT fk_qquser_relation FOREIGN KEY(QQID)REFERENCES QQUser(QQID);

ALTER TABLE Relation
ADD CONSTRAINT fk_baseInfo_relation FOREIGN KEY(QQID)REFERENCES BaseInfo(QQID);

#插入数据QQUser#
INSERT INTO QQUser(QQID,PassWord,LastLogTime,Online,Level)
VALUES(54789625,'add512#&','2013-02-16 17:01:35',2,1);
INSERT INTO QQUser(QQID,PassWord,LastLogTime,Online,Level)
VALUES(88662753,'admin0219','2013-02-19 21:08:50',0,5);
INSERT INTO QQUser(QQID,PassWord,LastLogTime,Online,Level)
VALUES(8855678,'guest0221','2013-02-21 16:28:20',1,6);

#插入BaseInfo#
INSERT INTO BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)
VALUES(54789625,'蝴蝶飞飞',1,16,'北京','朝阳','亚运村',37547388157668);
INSERT INTO BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)
VALUES(88662753,'秋芙蓉',0,20,'河南','南阳','方城博望',88715783657725);
INSERT INTO BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)
VALUES(8855678,'双眼皮@猪',1,38,'北京','海淀','双榆树东里',65794968876143);

#插入数据relation#
INSERT INTO Relation(QQID,RelationQQID,RelationStatus)
VALUES(54789625,88662753,0);
INSERT INTO Relation(QQID,RelationQQID,RelationStatus)
VALUES(88662753,8855678,1);
INSERT INTO Relation(QQID,RelationQQID,RelationStatus)
VALUES(54789625,88662753,0);

DELETE FROM Relation

#导入数据QQUser#
LOAD DATA LOCAL INFILE 'D:\work\4DQL\作业2\QQ数据\User.txt'
INTO TABLE QQUser FIELDS TERMINATED BY '\t';

#导入数据BaseInfo#
LOAD DATA LOCAL INFILE 'D:\work\4DQL\作业2\QQ数据\BaseInfo.txt'
INTO TABLE BaseInfo FIELDS TERMINATED BY '\t';

#导入数据Relation#
LOAD DATA LOCAL INFILE 'D:\work\4DQL\作业2\QQ数据\Relation.txt'
INTO TABLE Relation FIELDS TERMINATED BY '\t';

DELETE FROM Relation
#错误#
#查询QQ88662753#
SELECT RelationQQID,NickName,Age
FROM QQUser AS qq
JOIN BaseInfo AS bi ON qq.QQID=bi.QQID
JOIN Relation AS r ON bi.QQID=r.QQID
WHERE qq.QQID=88662753 AND RelationStatus=0;

#查询在线用户#
SELECT *
FROM QQUser
WHERE Online=0;

#查询北京年龄在18-45岁在线用户信息#
SELECT bi.QQID,NickName,Sex,Age,Province,City,Address,Phone
FROM QQUser AS qq
JOIN BaseInfo AS bi ON qq.QQID=bi.QQID
WHERE qq.Online=0 AND bi.Province ='北京市'AND bi.Age BETWEEN 18 AND 45

#查询昵称为小笨猪的信息#
SELECT *
FROM BaseInfo
WHERE NickName='小笨猪'

#查询54789625的好友的
#分组查询错误:多对一,无意义#
SELECT Province 省份, COUNT(Province) 总人数
FROM BaseInfo AS bi
JOIN Relation AS re ON bi.QQID=re.RelationQQID
WHERE re.QQID=54789625 AND re.RelationStatus=0
GROUP BY Province #desc #分组查询#
ORDER BY COUNT(Province) DESC #排序#

#分组以后需要过滤的信息使用having where 针对于原生的数据进行筛选#

#150天没有登录的#
SELECT bi.QQID,NickName,Sex,Age,Province,City,Address,Phone
FROM BaseInfo AS bi
JOIN QQUser AS qq ON bi.QQID=qq.QQID
WHERE TIMESTAMPDIFF(DAY,LastLogTime,NOW())>=150
ORDER BY LastLogTime DESC
#错误2#
#查询54789625的好友中等级为10级以上的用户信息#
SELECT BaseInfo.QQID,NickName,Sex,Age,Province,City,Address,Phone
FROM QQUser
JOIN BaseInfo ON BaseInfo.QQID = QQUser.QQID
JOIN Relation AS rt ON BaseInfo.QQID=rt.RelationQQID
WHERE Level>10 AND rt.RelationStatus=0 AND rt.QQID=54789625

#错误 连表查询#
#查询54789625的好友隐身用户信息,包括好友QQ号,昵称,年龄#
SELECT DISTINCT qq.QQID,NickName AS 姓名,Age AS 年龄
FROM QQUser AS qq
JOIN BaseInfo AS bi ON qq.QQID=bi.QQID
JOIN Relation AS re ON bi.QQID=re.RelationQQID
WHERE re.RelationStatus=0 AND qq.Online=2 AND re.QQID=54789625

#查询好友超过20个的用户#
SELECT DISTINCT QQID,COUNT(QQID)AS 总数
FROM Relation AS re
WHERE re.RelationStatus=0
GROUP BY QQID
HAVING COUNT(QQID)>20

#被当做黑名单人物次数排名前20#
SELECT re.RelationQQID AS QQ号,COUNT(QQID) AS 黑名单次数
FROM Relation AS re
WHERE re.RelationStatus =1

GROUP BY re.RelationQQID
ORDER BY COUNT(QQID) DESC

#修改数据#
#假设我的QQ为8855678 隐身登录#
UPDATE QQUser AS us SET us.Online=2
WHERE QQID=8855678

#查看#
SELECT *
FROM QQUser
WHERE QQID=8855678

#修改昵称#
UPDATE BaseInfo AS bi SET bi.NickName='被淹死的鱼',Address='解放中路6号院106室'
WHERE QQID=8855678

#查看#
SELECT *
FROM BaseInfo
WHERE QQID=8855678

#假设我的QQ是8855678将好友拖进黑名单#
UPDATE Relation AS r SET r.RelationStatus=1
WHERE QQID=8855678 AND r.RelationQQID=248624066

#查看#
SELECT *
FROM Relation
WHERE QQID=8855678

#把等级少于6级的都提升一级#
UPDATE QQUser SET Level=Level+1
WHERE Level<6

SELECT *
FROM QQUser
WHERE QQUser.Level<6

#将超过365天的QQ锁定等级为-1#
UPDATE QQUser AS us SET us.Level=-1
WHERE TIMESTAMPDIFF(DAY,LastLogTime,NOW())>365

SELECT *
FROM QQUser
WHERE TIMESTAMPDIFF(DAY,LastLogTime,NOW())>365

#将好友数量超过20的用户等级提升1#
UPDATE QQUser SET Level=Level+1
WHERE QQID IN(
SELECT QQID
FROM Relation
WHERE RelationStatus =0
GROUP BY QQID
HAVING COUNT(*)>=20
);

#将286314用户好友为嘟嘟鱼拖进黑名单#
UPDATE Relation AS re SET re.RelationStatus=1
WHERE re.RelationQQID IN(
SELECT QQID
FROM BaseInfo
WHERE NickName='嘟嘟鱼'
)

#删除数据#
#删除QQ号是54789625的用户黑名单的用户#
DELETE FROM BaseInfo
WHERE Relation.QQID IN(
SELECT DISTINCT QQID
FROM Relation
WHERE Relation.RelationStatus=1 AND Relation.QQID=54789625
)
SELECT *
FROM BaseInfo
WHERE QQID=54789625

#删除QQ为622009019的用户#
DELETE FROM BaseInfo
WHERE QQID=622009019;

DELETE FROM Relation
WHERE QQID=622009019 OR RelationQQID=622009019;
DELETE FROM QQUser
WHERE QQID=622009019;

#删除超过1000天没有登录的QQ#
DELETE FROM Relation
WHERE Relation.QQID IN(
SELECT QQID
FROM QQUser
WHERE TIMESTAMPDIFF(DAY,LastLogTime,NOW())>1000
)

DELETE FROM QQUser
WHERE QQUser.QQID IN(
SELECT QQID
FROM Relation
WHERE TIMESTAMPDIFF(DAY,LastLogTime,NOW())>1000

)
SELECT TIMESTAMPDIFF(DAY,'2012-04-02 15:49:00',NOW());

SELECT QQID,LastLogTime
FROM QQUser
WHERE TIMESTAMPDIFF(DAY,LastLogTime,NOW())>1000


  • 写回答

2条回答 默认 最新

  • Jin_evan 2016-11-01 04:00
    关注

    本题自己以解决,可以一起交流mysql

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图的问题
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名