RT!建立测试库的脚本是:CREATE DATABASE BONUS;
USE BONUS; #Create a table to keep track of basketball teams.CREATE DATABASE BONUS;
use BONUS;CREATE TABLE TEAMS
( TEAM_ID INT(2) NOT NULL,
NAME VARCHAR(20) NOT NULL );#Create a table to keep track of basketball players.
CREATE TABLE PLAYERS
( PLAYER_ID INT(2) NOT NULL,
LAST VARCHAR(20) NOT NULL,
FIRST VARCHAR(20) NOT NULL,
TEAM_ID INT(2) NULL,
NUMBER INT(2) NOT NULL );
#Create a table to keep track of players' personal information.CREATE TABLE PLAYER_DATA
( PLAYER_ID INT(2) NOT NULL,
HEIGTH DECIMAL(4,2) NOT NULL,
WEIGHT DECIMAL(5,2) NOT NULL );#Create a table to keep track of games played.CREATE TABLE GAMES
( GAME_ID INT(2) NOT NULL,
GAME_DT DATETIME NOT NULL,
HOME_TEAM_ID INT(2) NOT NULL,
GUEST_TEAM_ID INT(3) NOT NULL );#Create a table to keep track of each team's score for each game.CREATE TABLE SCORES
( GAME_ID INT(2) NOT NULL,
TEAM_ID INT(2) NOT NULL,
SCORE INT(3) NOT NULL,
WIN_LOSE VARCHAR(4) NOT NULL );#View all the tables that you created.
#SHOW TABLES;
#Create records for the basketball teams.INSERT INTO TEAMS VALUES ('1','STRING MUSIC');
INSERT INTO TEAMS VALUES ('2','HACKERS');
INSERT INTO TEAMS VALUES ('3','SHARP SHOOTERS');
INSERT INTO TEAMS VALUES ('4','HAMMER TIME');
#Create records for the players.INSERT INTO PLAYERS VALUES ('1','SMITH','JOHN','1','12');
INSERT INTO PLAYERS VALUES ('2','BOBBIT','BILLY','1','2');
INSERT INTO PLAYERS VALUES ('3','HURTA','WIL','2','32');
INSERT INTO PLAYERS VALUES ('4','OUCHY','TIM','2','22');
INSERT INTO PLAYERS VALUES ('5','BYRD','ERIC','3','6');
INSERT INTO PLAYERS VALUES ('6','JORDAN','RYAN','3','23');
INSERT INTO PLAYERS VALUES ('7','HAMMER','WALLY','4','21');
INSERT INTO PLAYERS VALUES ('8','HAMMER','RON','4','44');
INSERT INTO PLAYERS VALUES ('11','KNOTGOOD','AL',NULL,'0');
#Create records for the players' personal data.INSERT INTO PLAYER_DATA VALUES ('1','71','180');
INSERT INTO PLAYER_DATA VALUES ('2','58','195');
INSERT INTO PLAYER_DATA VALUES ('3','72','200');
INSERT INTO PLAYER_DATA VALUES ('4','74','170');
INSERT INTO PLAYER_DATA VALUES ('5','71','182');
INSERT INTO PLAYER_DATA VALUES ('6','72','289');
INSERT INTO PLAYER_DATA VALUES ('7','79','250');
INSERT INTO PLAYER_DATA VALUES ('8','73','193');
INSERT INTO PLAYER_DATA VALUES ('11','85','310');
#Create records in the GAMES table based on games that have been scheduled.INSERT INTO GAMES VALUES ('1','2002-05-01','1','2');
INSERT INTO GAMES VALUES ('2','2002-05-02','3','4');
INSERT INTO GAMES VALUES ('3','2002-05-03','1','3');
INSERT INTO GAMES VALUES ('4','2002-05-05','2','4');
INSERT INTO GAMES VALUES ('5','2002-05-05','1','2');
INSERT INTO GAMES VALUES ('6','2002-05-09','3','4');
INSERT INTO GAMES VALUES ('7','2002-05-10','2','3');
INSERT INTO GAMES VALUES ('8','2002-05-11','1','4');
INSERT INTO GAMES VALUES ('9','2002-05-12','2','3');
INSERT INTO GAMES VALUES ('10','2002-05-15','1','4');
#Create records in the SCORES table based on games that have been played.INSERT INTO SCORES VALUES ('1','1','66','LOSE');
INSERT INTO SCORES VALUES ('2','3','78','WIN');
INSERT INTO SCORES VALUES ('3','1','45','LOSE');
INSERT INTO SCORES VALUES ('4','2','56','LOSE');
INSERT INTO SCORES VALUES ('5','1','100','WIN');
INSERT INTO SCORES VALUES ('6','3','67','LOSE');
INSERT INTO SCORES VALUES ('7','2','57','LOSE');
INSERT INTO SCORES VALUES ('8','1','98','WIN');
INSERT INTO SCORES VALUES ('9','2','56','LOSE');
INSERT INTO SCORES VALUES ('10','1','46','LOSE');INSERT INTO SCORES VALUES ('1','2','75','WIN');
INSERT INTO SCORES VALUES ('2','4','46','LOSE');
INSERT INTO SCORES VALUES ('3','3','87','WIN');
INSERT INTO SCORES VALUES ('4','4','99','WIN');
INSERT INTO SCORES VALUES ('5','2','88','LOSE');
INSERT INTO SCORES VALUES ('6','4','77','WIN');
INSERT INTO SCORES VALUES ('7','3','87','WIN');
INSERT INTO SCORES VALUES ('8','4','56','LOSE');
INSERT INTO SCORES VALUES ('9','3','87','WIN');
INSERT INTO SCORES VALUES ('10','4','78','WIN')
1、所有球员的平均身高是?
What is the average height of all players?mysql> SELECT AVG(HEIGTH) FROM PLAYER_DATA;
+-------------+
| AVG(HEIGTH) |
+-------------+
| 72.777778 |
+-------------+
1 row in set (0.00 sec)2、所有球员的平均体重是?
What is the average weight of all players?mysql> SELECT AVG(WEIGHT) FROM PLAYER_DATA;
+-------------+
| AVG(WEIGHT) |
+-------------+
| 218.777778 |
+-------------+
1 row in set (0.00 sec)(如果我要求对分析每个球队所有球员的平均身高,体重?)
mysql> SELECT AVG(WEIGHT),TEAMS.NAME FROM PLAYER_DATA,PLAYERS,TEAMS WHERE TEAMS.TEAM_ID=PLAYERS.TEAM_ID AND PLAYER_DATA.PLAYER_ID=PLAYERS.PLAYER_ID;
+-------------+--------------+
| AVG(WEIGHT) | NAME |
+-------------+--------------+
| 207.375000 | STRING MUSIC |
+-------------+--------------+
1 row in set (0.00 sec)怎么只有一支队伍呢?
USE BONUS; #Create a table to keep track of basketball teams.CREATE DATABASE BONUS;
use BONUS;CREATE TABLE TEAMS
( TEAM_ID INT(2) NOT NULL,
NAME VARCHAR(20) NOT NULL );#Create a table to keep track of basketball players.
CREATE TABLE PLAYERS
( PLAYER_ID INT(2) NOT NULL,
LAST VARCHAR(20) NOT NULL,
FIRST VARCHAR(20) NOT NULL,
TEAM_ID INT(2) NULL,
NUMBER INT(2) NOT NULL );
#Create a table to keep track of players' personal information.CREATE TABLE PLAYER_DATA
( PLAYER_ID INT(2) NOT NULL,
HEIGTH DECIMAL(4,2) NOT NULL,
WEIGHT DECIMAL(5,2) NOT NULL );#Create a table to keep track of games played.CREATE TABLE GAMES
( GAME_ID INT(2) NOT NULL,
GAME_DT DATETIME NOT NULL,
HOME_TEAM_ID INT(2) NOT NULL,
GUEST_TEAM_ID INT(3) NOT NULL );#Create a table to keep track of each team's score for each game.CREATE TABLE SCORES
( GAME_ID INT(2) NOT NULL,
TEAM_ID INT(2) NOT NULL,
SCORE INT(3) NOT NULL,
WIN_LOSE VARCHAR(4) NOT NULL );#View all the tables that you created.
#SHOW TABLES;
#Create records for the basketball teams.INSERT INTO TEAMS VALUES ('1','STRING MUSIC');
INSERT INTO TEAMS VALUES ('2','HACKERS');
INSERT INTO TEAMS VALUES ('3','SHARP SHOOTERS');
INSERT INTO TEAMS VALUES ('4','HAMMER TIME');
#Create records for the players.INSERT INTO PLAYERS VALUES ('1','SMITH','JOHN','1','12');
INSERT INTO PLAYERS VALUES ('2','BOBBIT','BILLY','1','2');
INSERT INTO PLAYERS VALUES ('3','HURTA','WIL','2','32');
INSERT INTO PLAYERS VALUES ('4','OUCHY','TIM','2','22');
INSERT INTO PLAYERS VALUES ('5','BYRD','ERIC','3','6');
INSERT INTO PLAYERS VALUES ('6','JORDAN','RYAN','3','23');
INSERT INTO PLAYERS VALUES ('7','HAMMER','WALLY','4','21');
INSERT INTO PLAYERS VALUES ('8','HAMMER','RON','4','44');
INSERT INTO PLAYERS VALUES ('11','KNOTGOOD','AL',NULL,'0');
#Create records for the players' personal data.INSERT INTO PLAYER_DATA VALUES ('1','71','180');
INSERT INTO PLAYER_DATA VALUES ('2','58','195');
INSERT INTO PLAYER_DATA VALUES ('3','72','200');
INSERT INTO PLAYER_DATA VALUES ('4','74','170');
INSERT INTO PLAYER_DATA VALUES ('5','71','182');
INSERT INTO PLAYER_DATA VALUES ('6','72','289');
INSERT INTO PLAYER_DATA VALUES ('7','79','250');
INSERT INTO PLAYER_DATA VALUES ('8','73','193');
INSERT INTO PLAYER_DATA VALUES ('11','85','310');
#Create records in the GAMES table based on games that have been scheduled.INSERT INTO GAMES VALUES ('1','2002-05-01','1','2');
INSERT INTO GAMES VALUES ('2','2002-05-02','3','4');
INSERT INTO GAMES VALUES ('3','2002-05-03','1','3');
INSERT INTO GAMES VALUES ('4','2002-05-05','2','4');
INSERT INTO GAMES VALUES ('5','2002-05-05','1','2');
INSERT INTO GAMES VALUES ('6','2002-05-09','3','4');
INSERT INTO GAMES VALUES ('7','2002-05-10','2','3');
INSERT INTO GAMES VALUES ('8','2002-05-11','1','4');
INSERT INTO GAMES VALUES ('9','2002-05-12','2','3');
INSERT INTO GAMES VALUES ('10','2002-05-15','1','4');
#Create records in the SCORES table based on games that have been played.INSERT INTO SCORES VALUES ('1','1','66','LOSE');
INSERT INTO SCORES VALUES ('2','3','78','WIN');
INSERT INTO SCORES VALUES ('3','1','45','LOSE');
INSERT INTO SCORES VALUES ('4','2','56','LOSE');
INSERT INTO SCORES VALUES ('5','1','100','WIN');
INSERT INTO SCORES VALUES ('6','3','67','LOSE');
INSERT INTO SCORES VALUES ('7','2','57','LOSE');
INSERT INTO SCORES VALUES ('8','1','98','WIN');
INSERT INTO SCORES VALUES ('9','2','56','LOSE');
INSERT INTO SCORES VALUES ('10','1','46','LOSE');INSERT INTO SCORES VALUES ('1','2','75','WIN');
INSERT INTO SCORES VALUES ('2','4','46','LOSE');
INSERT INTO SCORES VALUES ('3','3','87','WIN');
INSERT INTO SCORES VALUES ('4','4','99','WIN');
INSERT INTO SCORES VALUES ('5','2','88','LOSE');
INSERT INTO SCORES VALUES ('6','4','77','WIN');
INSERT INTO SCORES VALUES ('7','3','87','WIN');
INSERT INTO SCORES VALUES ('8','4','56','LOSE');
INSERT INTO SCORES VALUES ('9','3','87','WIN');
INSERT INTO SCORES VALUES ('10','4','78','WIN')
1、所有球员的平均身高是?
What is the average height of all players?mysql> SELECT AVG(HEIGTH) FROM PLAYER_DATA;
+-------------+
| AVG(HEIGTH) |
+-------------+
| 72.777778 |
+-------------+
1 row in set (0.00 sec)2、所有球员的平均体重是?
What is the average weight of all players?mysql> SELECT AVG(WEIGHT) FROM PLAYER_DATA;
+-------------+
| AVG(WEIGHT) |
+-------------+
| 218.777778 |
+-------------+
1 row in set (0.00 sec)(如果我要求对分析每个球队所有球员的平均身高,体重?)
mysql> SELECT AVG(WEIGHT),TEAMS.NAME FROM PLAYER_DATA,PLAYERS,TEAMS WHERE TEAMS.TEAM_ID=PLAYERS.TEAM_ID AND PLAYER_DATA.PLAYER_ID=PLAYERS.PLAYER_ID;
+-------------+--------------+
| AVG(WEIGHT) | NAME |
+-------------+--------------+
| 207.375000 | STRING MUSIC |
+-------------+--------------+
1 row in set (0.00 sec)怎么只有一支队伍呢?
SQL 教材 GROUP BY
TEAM_ID=PLAYERS.TEAM_ID AND PLAYER_DATA.PLAYER_ID=PLAYERS.PLAYER_ID group by TEA
MS.NAME;
+-------------+----------------+
| AVG(WEIGHT) | NAME |
+-------------+----------------+
| 185.000000 | HACKERS |
| 221.500000 | HAMMER TIME |
| 235.500000 | SHARP SHOOTERS |
| 187.500000 | STRING MUSIC |
+-------------+----------------+
4 rows in set (0.08 sec)mysql>