Using MySQL to implement QQ database management

The first step

Create a database

CREATE database name

Step Two

Build table

CREATE TABLE Qquser

(

Qqid INT PRIMARY KEY not NULL,

PASSWORD VARCHAR () not NULL,

Lastlogintime DATETIME not NULL,

Online INT not NULL,

Levle INT not NULL

)

CREATE TABLE Baseinfo (

Qqid INT (a) not NULL PRIMARY KEY,

Nicname VARCHAR () not NULL,

Sex CHAR (20),

Age INT (150),

Province VARCHAR (50),

City VARCHAR (50),

Adress VARCHAR (200),

Phone CHAR (50)
);

CREATE TABLE Relation (
Qqid INT (a) is not NULL,

Relationqqid INT () is not NULL,

Relationstayus INT (20)
);

PS (omitting the process of adding data)

Fourth step: Create a FOREIGN KEY constraint

Syntax: ALTER table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field) REFERENCES associated table name (associated field);

ALTER TABLE ' relation ' ADD CONSTRAINT fk_baseinfo_relation_qqid FOREIGN KEY (' qqid ') REFERENCES ' baseinfo ' (' qqid ');

ALTER TABLE ' relation ' ADD CONSTRAINT fk_baseinfo_relation_retionqqid FOREIGN KEY (' retionqqid ') REFERENCES ' baseinfo ' (' Qqid ');

ALTER TABLE ' baseinfo ' ADD CONSTRAINT fk_qquser_baseinfo_qqid FOREIGN KEY (' qqid ') REFERENCES ' qquser ' (' qqid ');

#01. Search all friends with QQ number 54789625, including QQ number, nickname, age

Select Relationqqid as QQ number, nickname as nickname, age as

From Baseinfo,relation

where baseinfo.qqid=relation.relationqqid

and relation.qqid=54789625

and relationstatus=0 or 1

#02. Query the current online user information (3 points)

SELECT Nickname,province from

Baseinfo,qquser

WHERE Baseinfo.qqid=qquser.qqid and Online=0

#03. Find information on online users in Beijing between the ages of 18-45 years

SELECT Nickname,province from

Baseinfo,qquser

WHERE baseinfo.qqid=qquser.qqid

And baseinfo.province like '% Beijing '

and baseinfo.age between and 45

and online=0

#04. Query user information for Green grass nickname

SELECT nickname,province,city,address

From Baseinfo

WHERE nickname= ' green Grass '

#05. Find the total number of friends in each province of a user with a QQ number of 54789625, and the total number is sorted from big to small.

SELECT Baseinfo.province,count (*) as total number from Relation,baseinfo

WHERE relation.relationqqid=baseinfo.qqid

and relation.relationstatus=0

and relation.qqid=54789625

GROUP by Baseinfo.province

ORDER by total number DESC

#06. Query user information for at least 150 days without login QQ account, including QQ number, last login time, rank, nickname, age, and in descending order by time

SELECT Qquser.qqid,qquser.lastlogtime,qquser.level,baseinfo.nickname,baseinfo.age

From Baseinfo,qquser

WHERE baseinfo.qqid=qquser.qqid

and DATEDIFF (now (), Lastlogtime) >=150

ORDER by DATEDIFF (now (), Lastlogtime) DESC

#07. Query QQ number of 54789625 friends in the level of more than 10 levels of "moon" user information.

SELECT nickname,province

From Qquser,relation,baseinfo

WHERE relation.relationqqid=baseinfo.qqid

and relation.qqid=54789625

and relation.relationstatus=0

and Relation.relationqqid=qquser.qqid

and qquser.level>=10

#08.--Search for the invisible user information in a friend with QQ number 54789625.

SELECT nickname,province

From Relation INNER JOIN baseinfo

On Relation.relationqqid=baseinfo.qqid

and relation.qqid=54789625

INNER JOIN Qquser

On Qquser.qqid=relationqqid

and online=2 #2代表隐身

and Relation.relationstatus=0 #0代表好友

#09.--Search for more than 20 friends ' user information.

SELECT nickname,province

From Baseinfo WHERE qqid in

(SELECT qqid

From Relation

WHERE relationstatus=0

GROUP by Qqid

Having COUNT (*) >20

)

#10. In order to view the reputation, the administrator needs to query the top 3 users who have been blacklisted as the number of people

SELECT Relationqqid,count (*) as Times

From Relation

WHERE Relationstatus=1

GROUP by Relationqqid

ORDER by COUNT (*) DESC

LIMIT 3

#01. Suppose my QQ number is 8855678, today I am incognito login

UPDATE qquser SET Online=2,lastlogtime=now ()

WHERE qqid=8855678

#02. Suppose my QQ number is 8855678, modify my nickname "Drowned Fish", address "Jiefang Road No. 123 Room"

UPDATE baseinfo SET nickname= ' drowned fish ', address= ' center of Liberation Road '

WHERE qqid=8855678

#03. Suppose my QQ number is 54789625, drag my friend "green grass" into the blacklist.

UPDATE Relation SET Relationstatus=1

WHERE qqid=54789625 and relationqqid=88662753

#04. In order to improve the chatting enthusiasm of QQ users, the level of users with level less than 6 level is increased by 1 levels.

Update Qquser Set level=level+1

where level<6

#05. Administrators will be locked out of QQ that has not logged in for more than 365 days (the rating is set to-1).

UPDATE Qquser SET Level=-1

WHERE DATEDIFF (now (), Lastlogtime) >=365

#06. To reward the user, increase the number of friends over 20 to 1 levels.

UPDATE Qquser SET level=level+1

WHERE Qqid in (

SELECT relation.qqid from Relation

WHERE relationstatus=0

GROUP by Relation.qqid

Having COUNT (relation.relationqqid) >=20)

#07. Put the QQ number 54789625 of the user's friend "Doodle Fish" into the blacklist.

UPDATE Relation SET Relationstatus=1

WHERE qqid=54789625 and Relationqqid=

(SELECT qqid from Baseinfo WHERE nickname= ' doodle Fish ')

and relationstatus=0

#1. Remove the user from the blacklist with QQ number 54789625.

DELETE from Relation WHERE qqid=54789625 and Relationstatus=1

#2. QQ Number 54789625 users repeatedly posted illegal information in QQ, causing a very bad impact, so the administrator decided to delete it.

DELETE from Relation WHERE qqid=54789625 OR relationqqid=54789625

DELETE from Baseinfo WHERE qqid=54789625

DELETE from Qquser WHERE qqid=54789625

#3. Admin will delete QQ for more than 1000 days without logging in.

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

WHERE DATEDIFF (now (), Lastlogtime) >=1000

Using MySQL to implement QQ database management