MySQL data query exercises one

Design a system to save the table tennis tournament information!

Requirements: Output The following information on the MySQL command line:

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/76/D3/wKioL1ZdQR3TtEpeAABRgoDZ2u0976.jpg "title=" 01.jpg "alt=" Wkiol1zdqr3ttepeaabrgodz2u0976.jpg "/>

1. Create a database (this example takes the GINVIP database as an example)

Create Database Ginvip;

2. Create a Team information table

CREATE TABLE player (ID int unsigned primary key auto_increment,player_name varchar (default "), Gender enum (' Male ', ' fem Ale ', ' secret '));

Insert the player information data in the player table:

INSERT into player values (1, ' Cameron Poe ', ' Male '), (2, ' Casey Poe ', ' Female '), (3, ' John j.rambo ', ' Male '), (4, ' Jason Bourne ', ' Male '), (5, ' Marie Kreutz ', ' Female '), (6, ' James Bond ', ' Male ');

this gets the following player information:

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/76/D3/wKioL1ZdRYOQwwITAAA6-Yy00WE400.jpg "title=" 01.jpg "alt=" Wkiol1zdryoqwwitaaa6-yy00we400.jpg "/>

Add more field information (feel free to add, just to familiarize yourself with MySQL syntax):

ALTER TABLE player add column ' Money ' decimal (10,2) default 2536.02;

ALTER TABLE player add column ' height ' float (5,2) default 175.00;

This gives you the following information about the players:

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/76/D4/wKiom1ZdRkHxdJhqAABdBIs3fDg793.jpg "title=" 01.jpg "alt=" Wkiom1zdrkhxdjhqaabdbis3fdg793.jpg "/>

Modify the Money, height field information:

Update player set Money=money+rand () *1000;

Update player set Height=165+rand () *20;

The information after the modification is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/76/D3/wKioL1ZdR1TxzkZrAABny3rIheA592.jpg "title=" 01.jpg "alt=" Wkiol1zdr1txzkzraabny3rihea592.jpg "/>

3. Create a Match information table

CREATE TABLE Player_match (match_id int primary key auto_increment,player_1 int unsigned comment ' Player 1 id ', player_2 int unsi gned comment ' Player 2 id ', Match_time datetime,match_result char (3), match_address varchar (TEN) Default ' London ');

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/76/D3/wKioL1ZdSJ3hmqyyAABvlEsPxpw477.jpg "title=" 01.jpg "alt=" Wkiol1zdsj3hmqyyaabvlespxpw477.jpg "/>

Insert match data:

INSERT into Player_match values (null,2,5, ' 2015-11-1 18:00:00 ', ' 5:3 ', ' Barcelona '), (null,1,3, ' 2015-10-19 19:00:00 ', ' 3:5 ', ' real Mardrid '), (null,2,4, ' 2015-10-25 20:00:00 ', ' 2:6 ', ' Beijing '), (null,3,6, ' 2015-12-1 19:30:00 ', ' 8:0 ', ' London ');

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/76/D7/wKiom1ZdaEHQEKYcAABm55hAroc406.jpg "title=" 01.jpg "alt=" Wkiom1zdaehqekycaabm55haroc406.jpg "/>

Inquire:

Select match_time,player_1,match_result,player_2 from Player_match;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/76/D6/wKioL1ZdaNLTDZLOAABRzmgLBIE556.jpg "title=" 01.jpg "alt=" Wkiol1zdanltdzloaabrzmglbie556.jpg "/>

Replace the player_1 field with the Player_name field to display the player's name

Select match_time,player_name,match_result,player_2 from Player_match left join player on player_match.player_1= Player.id;

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/76/D6/wKioL1ZdaTvDwzl3AABtMuqbXmw021.jpg "title=" 01.jpg "alt=" Wkiol1zdatvdwzl3aabtmuqbxmw021.jpg "/>

At this point, the contestant table can be connected again, output player_2 name of the player

Select Match_time,player_name,match_result,player_name from Player_match left join player on player_match.player_1= Player.id left join player on Player_match.player_2=player.id;

As can be seen from the above statement, a table appears in a single query, is used multiple times! Note that there is no ambiguity when using the guarantee!

Alias a table that produces ambiguity! So the above statement can be changed to the following statement, the final output:

Select Match_time,p1.player_name,match_result,p2.player_name from Player_match left join player as P1 on Player_ Match.player_1=p1.id left join player as P2 on player_match.player_2=p2.id;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/76/D7/wKiom1Zdbb-Rk31vAACT1kXEHZs315.jpg "title=" 01.jpg "alt=" Wkiom1zdbb-rk31vaact1kxehzs315.jpg "/>

MySQL data query exercises one