Database programming test machine trial QQ

Create a QQ database

#创建数据库CREATE DATABASE qq# Create the table name and add the Column drop table IF EXISTS ' dbo. Baseinfo '; CREATE TABLE ' student ' (qqid INT PRIMARY KEY auto_increment,nickname VARCHAR ( +), Sex int,age INT, province VARCHAR ( the), City VARCHAR ( +), Address VARCHAR ( +), Phone INT) #修改表名ALTER table student RENAME baseinfo# add information data for Baseinfo table QQ table INSERT into student (qqid,nickname,sex,age , Province,city,address,phone) VALUES (8855678,'Lone Ranger',1, -,'Beijing','Haidian District','on the ground',003),(54789625,'Butterfly Fly',1, -,'Beijing','Chaoyang District','Asian Games',001),(88662753,'Green Grass',0, -,'Henan Province','Anyang','Tangyin',002); #创建QQUser表并且添加列CREATE TABLE ' Qquser ' (qqid INT PRIMARY KEY auto_increment,password VARCHAR ( +), 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# Create Qquser table and add column create table ' Relation ' (qqid int, relationqqid int,relationstalus int) # Add information data for the Qquser table insert into Relation (qqid,relationqqid,relationstalus) VALUES (54789625,88662753,0),(88662753,8855678,1),(54789625,8855678,0);

 onSearch all friends with QQ number 54789625, including QQ number, nickname, age select Qqid,nickname,age from Baseinfo WHERE qqid=54789625# Geneva. Querying the current online user's information select*from Qquser WHERE online!=0#Geneva. Find information on online users in Beijing between the ages of 18-45 select*From baseinfo,qquser WHERE baseinfo. Qqid=qquser. Qqid and Baseinfo. Province='Beijing'and Baseinfo. Age between -and $and Qquser. Online>0#Geneva. Query nickname for green Grass user information Select*from Baseinfo WHERE nickname='Green Grass'# toThe total number of friends in each province of a user who has a QQ number of 54789625 is queried, and the total number is sorted from large to small. Select COUNT (province) from Baseinfo where Qqid in (select Qqid from Relation where Relationqqidin (select Relationqqid FRO M relation WHERE qqid='54789625')) GROUP by province# .query user information for at least 150 days without logging in to QQ account, including QQ number, last login time, rank, nickname, age, and select Qquser in descending order by time. 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)>= Max) GROUP by Lastlogtime # -query QQ number of 54789625 friends in the rank of 10 levels above the "moon" level user information. SELECT*from Baseinfo where qqid in (SELECT qqid from Qquser where level>Tenand qqid in (select Qqid from Relation where relationqqid in (select Relationqqid from relation where qqid='54789625'))) # ,.--Check the QQ number for 54789625 of friends in the invisible user information. SELECT*from Baseinfo where qqid in (SELECT qqid from Qquser where online=0and qqid in (select Qqid from Relation where relationqqid in (select Relationqqid from relation where qqid='54789625')))# the.--query more than 20 friends for user information. SELECT*From Baseinfo where qqid in (select Qqid from Relation where relationqqid in (select Relationqqid from relation GROUP B Y qqid having COUNT (relationqqid)> -))#Tento view the reputation, the administrator needs to query the top 3 user select for the number of blacklisted characters*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) LIMIT3# #用例2: Modifying Data # onSuppose my QQ number is 8855678, today I am Incognito login update Qquser SET OnLine='0'WHERE qqid ='8855678'# GenevaSuppose my QQ number is 8855678, modify my nickname for "Drowned Fish", address "Jiefang Road No. 123 Room" UPDATE baseinfo SET nickname='The drowned Fish', Address ='Room 123, Jiefang Middle Road'WHERE qqid='8855678'#GenevaSuppose my QQ number is 54789625, drag my friend "green grass" into the blacklist. UPDATE relation SET Relationstalus=1WHERE qqid ='54789625'#Genevain 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+1WHERE level<6# toThe administrator will be locked out of QQ for more than 365 days without logging in (setting the rank value to-1). UPDATE Qquser SET level=-1WHERE DATEDIFF (now (), Lastlogtime) >=365 # .to reward users, increase the number of friends over 20 to 1 levels. UPDATE Qquser SET level=level+1WHERE (SELECT relationqqid from Relation GROUP by Qqid have COUNT (relationqqid)> -)# -The QQ number for 54789625 of the user's friend "Doodle Fish" dragged into the blacklist. UPDATE relation SET Relationstalus=1WHERE qqid ='54789625'# #用例3: Delete Data #1Delete the user from the blacklist with QQ number 54789625. DELETE from relation WHERE qqid='54789625'#2. QQ number for 54789625 of users repeatedly posted illegal information in QQ, causing a very bad impact, so the administrator decided to delete it. DELETE from Baseinfo WHERE qqid='54789625'#3Admin will delete qq for more than 1000 days without logging in. UPDATE from Qquser WHERE DATEDIFF (now (), Lastlogtime)>= + 

Database programming test machine trial QQ