"Learning Notes" July 18, 2017 MySQL test: Simulation QQ Database

Simulation test:

QQ Database Management

Create a database and add relationships and test data

1 # #创建QQ数据库, complete a simple test2 3 #创建数据库4 DROP DATABASE IF EXISTSmyqq;5 CREATE DATABASEmyqq;6  Usemyqq;7 8 #在数据库创建基本数据表9 Ten #创建User表 One DROP TABLE IF EXISTS`User`; A CREATE TABLE`User`( -QqidINT( One) not NULL PRIMARY KEYCOMMENT'QQ Number', -' PassWord 'VARCHAR( -) not NULLCOMMENT'QQ Password', theLastlogtimeDATETIME DEFAULTNow () COMMENT'Last logon time', -OnLineINT(4)DEFAULT 0COMMENT'Login Status: 0 offline 1 Online 2 stealth', -` Level`INT(4)DEFAULT 1COMMENT'QQ Grade' -) COMMENT="QQ user table"; +  - #创建用户信息表 + DROP TABLE IF EXISTSBaseinfo; A CREATE TABLEBaseinfo ( atQqidINT( One) not NULL PRIMARY KEYCOMMENT'QQ Number', -NicknameVARCHAR( -) COMMENT'Nickname', -SexINT(4)DEFAULT 0COMMENT'Gender: 0 Female 1 male', -AgeINT(4) COMMENT'Age', -ProvinceVARCHAR( -)DEFAULT 'Beijing'COMMENT'Provinces', -CityVARCHAR( -) COMMENT'City', inAddressVARCHAR(255) COMMENT'Address', -PhoneVARCHAR( One) COMMENT'Contact Phone' to) COMMENT="User Information Form"; +  - #创建QQ好友关系表 the DROP TABLE IF EXISTSRelation; * CREATE TABLERelation ( $QqidINT( One) not NULLCOMMENT'QQ Number',Panax NotoginsengRelationqqidINT( One) not NULLCOMMENT'QQ Friends', -RelationstatusINT(4)DEFAULT 0  not NULLCOMMENT'friend Status: 0 Friends 1 blacklist' the) COMMENT="QQ friends"; +  A # #创建表关系 (foreign key) the  + ALTER TABLEBaseinfo - ADD CONSTRAINTFk_baseinfo_userFOREIGN KEY(qqid) $ REFERENCES`User' (qqid); $  - ALTER TABLERelation - ADD CONSTRAINTFk_qqid_userFOREIGN KEY(qqid) the REFERENCES`User' (qqid); - Wuyi ALTER TABLERelation the ADD CONSTRAINTFk_reid_userFOREIGN KEY(relationqqid) - REFERENCES`User' (qqid); Wu  - # #添加测试数据 About  $ #User表数据 - INSERT  into USER(Qqid,password,lastlogtime,online, ' Level`) - VALUES(8855678,'guest0221','2008-02-21 16:28:20',1,6), -(54789625,'add512#&','2008-01-16 17:01:35',2, One), A(88662753,'admin0219','2017-07-01 21:08:35',0,5); +  the #BaseInfo表数据 - INSERT  intobaseinfo (Qqid,nickname,sex,age,province,city,address,phone) $ VALUES(8855678,'Lone Ranger',1, -,'Beijing','Haidian District','on the ground','13609887788'), the(54789625,'Butterfly Fly',1, -,'Beijing','Chaoyang District','Asian Games','15665788768'), the(88662753,'Green Grass',0, -,'Henan Province','Anyang','Tangyin','13268789432'); the  the #Relation表数据 - INSERT  intoRelation (qqid,relationqqid,relationstatus) in VALUES(54789625,88662753,0), the(88662753,8855678,1), the(54789625,8855678,0);

Second, the implementation of query, modification, deletion and other operations

"Learning Notes" July 18, 2017 MySQL test: Simulation QQ Database