MySQL练习题及答案

[email protected] 10:24 mysql>SELECT PLAYERNO,TOWN,SEX FROM PLAYERS WHERE SEX = (SELECT SEX FROM PLAYERS WHERE PLAYERNO = 44) AND TOWN = (SELECT TOWN FROM PLAYERS WHERE PLAYERNO = 7);
±---------±----------±----+
| PLAYERNO | TOWN | SEX |
±---------±----------±----+
| 2 | Stratford | M |
| 6 | Stratford | M |
| 7 | Stratford | M |
| 39 | Stratford | M |
| 57 | Stratford | M |
| 83 | Stratford | M |
| 100 | Stratford | M |
±---------±----------±----+
7 rows in set (0.00 sec)

[email protected] 10:31 mysql>

例1： 得到1号球队的队长的编号和姓名

表： PLAYERS TEAMS
字段： 队长的编号(PLAERNO)和姓名(NAME)
条件：1号球队的队长的编号
子查询
SELECT PLAYERNO FROM TEAMS WHERE TEAMNO=1

SELECT PLAYERNO,NAME FROM PLAYERS
WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO=1)

例 2：对于编号小于60的球员，得到他们加入俱乐部的年份和104号球员加入俱乐部的年份之间的差值

需求分析
表： PLAYERS
字段： PLAYERNO,JOINED-（104号球员的加入俱乐部的年份）
条件：编号小于60
子查询：104号球员的加入俱乐部的年份

select playerno,joined-(select joined from PLAYERS WHERE playerno=104) sub from PLAYERS where playerno<60;

SELECT PLAYERNO,NAME,JOINED-(SELECT JOINED FROM PLAYERS WHERE PLAYERNO=104) FROM PLAYERS WHERE PLAYERNO<60;

SELECT playerno,JOINED - (SELECT JOINED
FROM PLAYERS
WHERE PLAYERNO=104) AS YEARED
FROM PLAYERS
WHERE PLAYERNO<60

索引

1. 什么是索引？ 从表中找到所需的数据 方便查找