MySQL 查询语句使用进阶
MariaDB > select * fromclasses;+---------+----------------+----------+
| ClassID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai| 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
MariaDB > select * from students,classes where students.ClassID = classes.ClassID;
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| 1 | Shi Zhongyu |22 | M | 2 | 3 | 2 | Emei Pai | 7 |
| 2 | Shi Potian |22 | M | 1 | 7 | 1 | Shaolin Pai | 10 |
| 3 | Xie Yanke |53 | M | 2 | 16 | 2 | Emei Pai | 7 |
| 4 | Ding Dian |32 | M | 4 | 4 | 4 | Wudang Pai | 12 |
| 5 | Yu Yutong |26 | M | 3 | 1 | 3 | QingCheng Pai| 11 |
| 6 | Shi Qing |46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |
| 7 | Xi Ren |19 | F | 3 | NULL | 3 | QingCheng Pai| 11 |
| 8 | Lin Daiyu |17 | F | 7 | NULL | 7 | Ming Jiao | 27 |
| 9 | Ren Yingying|20 | F | 6 | NULL | 6 | Lianshan Pai | 27 |
| 10 | Yue Lingshan|19 | F | 3 | NULL | 3 | QingCheng Pai| 11 |
| 11 | Yuan Chengzhi |23 | M | 6 | NULL | 6 | Lianshan Pai | 27 |
| 12 | Wen Qingqing|19 | F | 1 | NULL | 1 | Shaolin Pai | 10 |
| 13 | Tian Boguang|33 | M | 2 | NULL | 2 | Emei Pai | 7 |
| 14 | Lu Wushuang |17 | F | 3 | NULL | 3 | QingCheng Pai| 11 |
| 15 | Duan Yu |19 | M | 4 | NULL | 4 | Wudang Pai | 12 |
| 16 | Xu Zhu |21 | M | 1 | NULL | 1 | Shaolin Pai | 10 |
| 17 | Lin Chong |25 | M | 4 | NULL | 4 | Wudang Pai | 12 |
| 18 | Hua Rong |23 | M | 7 | NULL | 7 | Ming Jiao | 27 |
| 19 | Xue Baochai |18 | F | 6 | NULL | 6 | Lianshan Pai | 27 |
| 20 | Diao Chan |19 | F | 7 | NULL | 7 | Ming Jiao | 27 |
| 21 | Huang Yueying |22 | F | 6 | NULL | 6 | Lianshan Pai | 27 |
| 22 | Xiao Qiao |20 | F | 1 | NULL | 1 | Shaolin Pai | 10 |
| 23 | Ma Chao |23 | M | 4 | NULL | 4 | Wudang Pai | 12 |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
23 rows in set (0.00 sec)
# 注意，如果两张表做等值连接时如果每个表中的字段唯一，则可以省略表名称，这里select name，Class也正确
MariaDB > select students.name,classes.Class from students,classes where students.ClassID = classes.ClassID;
+---------------+----------------+
| name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QingCheng Pai|
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QingCheng Pai|
| Lin Daiyu | Ming Jiao |
| Ren Yingying| Lianshan Pai |
| Yue Lingshan| QingCheng Pai|
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing| Shaolin Pai |
| Tian Boguang| Emei Pai |
| Lu Wushuang | QingCheng Pai|
| Duan Yu | Wudang Pai |
| Xu Zhu | Shaolin Pai |
| Lin Chong | Wudang Pai |
| Hua Rong | Ming Jiao |
| Xue Baochai | Lianshan Pai |
| Diao Chan | Ming Jiao |
| Huang Yueying | Lianshan Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
+---------------+----------------+
23 rows in set (0.00 sec)
页:
[1]