MySQL 查询语句使用进阶

2321212dd

===============================================================================
概述：

===============================================================================
练习：


练习1
首先导入hellodb.sql的脚本文件，查询其数据库和表如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

[iyunv@centos7 ~]# mysql -p134296 < hellodb.sql # 导入数据库脚本文件； [iyunv@centos7 ~]# mysql -p134296 Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | Syslog             | | hellodb            | # 生成的hellodb数据库 | mydb               | | mysql              | | performance_schema | | test               | | testdb             | | ultrax             | | zabbix             | +--------------------+ 10 rows in set (0.01 sec) MariaDB [(none)]> use hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [hellodb]> show tables; # 查看hellodb数据库中的表如下： +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | teachers          | | toc               | +-------------------+ 7 rows in set (0.00 sec)

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

MariaDB [hellodb]> desc students; +-----------+---------------------+------+-----+---------+----------------+ | Field     | Type                | Null | Key | Default | Extra          | +-----------+---------------------+------+-----+---------+----------------+ | StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | | Name      | varchar(50)         | NO   |     | NULL    |                | | Age       | tinyint(3) unsigned | NO   |     | NULL    |                | | Gender    | enum('F','M')       | NO   |     | NULL    |                | | ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                | | TeacherID | int(10) unsigned    | YES  |     | NULL    |                | +-----------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec) MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name          | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 | |     2 | Shi Potian    |  22 | M      |       1 |         7 | |     3 | Xie Yanke     |  53 | M      |       2 |        16 | |     4 | Ding Dian     |  32 | M      |       4 |         4 | |     5 | Yu Yutong     |  26 | M      |       3 |         1 | |     6 | Shi Qing      |  46 | M      |       5 |      NULL | |     7 | Xi Ren        |  19 | F      |       3 |      NULL | |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | |    15 | Duan Yu       |  19 | M      |       4 |      NULL | |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | |    17 | Lin Chong     |  25 | M      |       4 |      NULL | |    18 | Hua Rong      |  23 | M      |       7 |      NULL | |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | |    20 | Diao Chan     |  19 | F      |       7 |      NULL | |    21 | Huang Yueying |  22 | F      |       6 |      NULL | |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | |    23 | Ma Chao       |  23 | M      |       4 |      NULL | |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]> select Name,Age,Gender from students where Age > 25 and Gender = 'M'; +--------------+-----+--------+ | Name         | Age | Gender | +--------------+-----+--------+ | Xie Yanke    |  53 | M      | | Ding Dian    |  32 | M      | | Yu Yutong    |  26 | M      | | Shi Qing     |  46 | M      | | Tian Boguang |  33 | M      | | Xu Xian      |  27 | M      | | Sun Dasheng  | 100 | M      | +--------------+-----+--------+

(2) 以ClassID为分组依据，显示每组的平均年龄；

1 2 3 4 5 6 7 8 9 10 11 12 13 14

MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID; +---------+---------+ | ClassID | avg_age | +---------+---------+ |    NULL | 63.5000 | |       1 | 20.5000 | |       2 | 36.0000 | |       3 | 20.2500 | |       4 | 24.7500 | |       5 | 46.0000 | |       6 | 20.7500 | |       7 | 19.6667 | +---------+---------+ 8 rows in set (0.01 sec)

(3) 显示第2题中平均年龄大于30的分组及平均年龄；

1 2 3 4 5 6 7 8 9

MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID having avg_age > 30; +---------+---------+ | ClassID | avg_age | +---------+---------+ |    NULL | 63.5000 | |       2 | 36.0000 | |       5 | 46.0000 | +---------+---------+ 3 rows in set (0.02 sec)

(4) 显示以L开头的名字的同学的信息；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

MariaDB [hellodb]> select * from students where name like 'L%';    # LIKE子句匹配 +-------+-------------+-----+--------+---------+-----------+ | StuID | Name        | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | |    14 | Lu Wushuang |  17 | F      |       3 |      NULL | |    17 | Lin Chong   |  25 | M      |       4 |      NULL | +-------+-------------+-----+--------+---------+-----------+ 3 rows in set (0.01 sec) MariaDB [hellodb]> select * from students where name RLIKE '^L.*$';  # 正则表达式匹配 +-------+-------------+-----+--------+---------+-----------+ | StuID | Name        | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | |    14 | Lu Wushuang |  17 | F      |       3 |      NULL | |    17 | Lin Chong   |  25 | M      |       4 |      NULL | +-------+-------------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec)

(5) 显示TeacherID非空的同学的相关信息；

1 2 3 4 5 6 7 8 9 10

MariaDB [hellodb]> select Name,TeacherID from students where TeacherID is not null; +-------------+-----------+ | Name        | TeacherID | +-------------+-----------+ | Shi Zhongyu |         3 | | Shi Potian  |         7 | | Xie Yanke   |        16 | | Ding Dian   |         4 | | Yu Yutong   |         1 | +-------------+-----------+

(6) 以年龄排序后，显示年龄最大的前10位同学的信息；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

MariaDB [hellodb]> select * from students order by Age desc;  # 降序排列 +-------+---------------+-----+--------+---------+-----------+ | StuID | Name          | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | |     3 | Xie Yanke     |  53 | M      |       2 |        16 | |     6 | Shi Qing      |  46 | M      |       5 |      NULL | |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | |     4 | Ding Dian     |  32 | M      |       4 |         4 | |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | |     5 | Yu Yutong     |  26 | M      |       3 |         1 | |    17 | Lin Chong     |  25 | M      |       4 |      NULL | |    23 | Ma Chao       |  23 | M      |       4 |      NULL | |    18 | Hua Rong      |  23 | M      |       7 |      NULL | |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | |    21 | Huang Yueying |  22 | F      |       6 |      NULL | |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 | |     2 | Shi Potian    |  22 | M      |       1 |         7 | |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | |    15 | Duan Yu       |  19 | M      |       4 |      NULL | |     7 | Xi Ren        |  19 | F      |       3 |      NULL | |    20 | Diao Chan     |  19 | F      |       7 |      NULL | |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]> select * from students order by Age desc limit 10; # 增加limit显示数量 +-------+--------------+-----+--------+---------+-----------+ | StuID | Name         | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ |    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL | |     3 | Xie Yanke    |  53 | M      |       2 |        16 | |     6 | Shi Qing     |  46 | M      |       5 |      NULL | |    13 | Tian Boguang |  33 | M      |       2 |      NULL | |     4 | Ding Dian    |  32 | M      |       4 |         4 | |    24 | Xu Xian      |  27 | M      |    NULL |      NULL | |     5 | Yu Yutong    |  26 | M      |       3 |         1 | |    17 | Lin Chong    |  25 | M      |       4 |      NULL | |    23 | Ma Chao      |  23 | M      |       4 |      NULL | |    18 | Hua Rong     |  23 | M      |       7 |      NULL | +-------+--------------+-----+--------+---------+-----------+ 10 rows in set (0.00 sec)

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

MariaDB [hellodb]> select Name,Age from students where Age >= 20 and Age <= 25; +---------------+-----+ | Name          | Age | +---------------+-----+ | Shi Zhongyu   |  22 | | Shi Potian    |  22 | | Ren Yingying  |  20 | | Yuan Chengzhi |  23 | | Xu Zhu        |  21 | | Lin Chong     |  25 | | Hua Rong      |  23 | | Huang Yueying |  22 | | Xiao Qiao     |  20 | | Ma Chao       |  23 | +---------------+-----+ 10 rows in set (0.00 sec) MariaDB [hellodb]> select Name,Age from students where Age between 20 and 25; +---------------+-----+ | Name          | Age | +---------------+-----+ | Shi Zhongyu   |  22 | | Shi Potian    |  22 | | Ren Yingying  |  20 | | Yuan Chengzhi |  23 | | Xu Zhu        |  21 | | Lin Chong     |  25 | | Hua Rong      |  23 | | Huang Yueying |  22 | | Xiao Qiao     |  20 | | Ma Chao       |  23 | +---------------+-----+ 10 rows in set (0.00 sec) MariaDB [hellodb]> select Name,Age from students where Age in (20,21,22,23,2425); +---------------+-----+ | Name          | Age | +---------------+-----+ | Shi Zhongyu   |  22 | | Shi Potian    |  22 | | Ren Yingying  |  20 | | Yuan Chengzhi |  23 | | Xu Zhu        |  21 | | Lin Chong     |  25 | | Hua Rong      |  23 | | Huang Yueying |  22 | | Xiao Qiao     |  20 | | Ma Chao       |  23 | +---------------+-----+ 10 rows in set (0.00 sec)

---

练习2

1、以ClassID分组，显示每班的同学的人数；

1 2 3 4 5 6 7 8 9 10 11 12 13 14

MariaDB [hellodb]> select ClassID,count(StuID) from students group by ClassID; +---------+--------------+ | ClassID | count(StuID) | +---------+--------------+ |    NULL |            2 | |       1 |            4 | |       2 |            3 | |       3 |            4 | |       4 |            4 | |       5 |            1 | |       6 |            4 | |       7 |            3 | +---------+--------------+ 8 rows in set (0.01 sec)

2、以Gender分组，显示其年龄之和；

1 2 3 4 5 6 7 8

MariaDB [hellodb]> select Gender,sum(age) as sum_age from students group by Gender; +--------+---------+ | Gender | sum_age | +--------+---------+ | F      |     190 | | M      |     495 | +--------+---------+ 2 rows in set (0.01 sec)

3、以ClassID分组，显示其平均年龄大于25的班级；

1 2 3 4 5 6 7 8 9

MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID having avg_age > 25; +---------+---------+ | ClassID | avg_age | +---------+---------+ |    NULL | 63.5000 | |       2 | 36.0000 | |       5 | 46.0000 | +---------+---------+ 3 rows in set (0.01 sec)

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

1 2 3 4 5 6 7

MariaDB [hellodb]> select Gender,sum(age) from students where age > 25 group by Gender; +--------+----------+ | Gender | sum(age) | +--------+----------+ | M      |      317 | +--------+----------+ 1 row in set (0.02 sec)

---

---

SELECT：多表查询

★连接操作：

☉交叉连接：笛卡尔乘积；

☉内连接：

• 等值连接：让表之间的字段以等值的方式建立连接；
• 不等值连接：
• 自然连接
• 自连接
  
  
  

☉外连接：

• 左外连接： FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col
• 右外连接： FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col
  
  
  

演示：
  1.等值连接，挑选students表中的ClassID和classes表中的ClassID相等的同学的信息；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

MariaDB [hellodb]> select * from  classes; +---------+----------------+----------+ | 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 [hellodb]> 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 [hellodb]> 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

MariaDB [hellodb]> select * from students,teachers where students.TeacherID = teachers.TID; +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ |     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      | |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      | |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 3 rows in set (0.01 sec) MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name          | Age | Gender | +-----+---------------+-----+--------+ |   1 | Song Jiang    |  45 | M      | |   2 | Zhang Sanfeng |  94 | M      | |   3 | Miejue Shitai |  77 | F      | |   4 | Lin Chaoying  |  93 | F      | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select * from students where TeacherID is not null; +-------+-------------+-----+--------+---------+-----------+ | StuID | Name        | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ |     1 | Shi Zhongyu |  22 | M      |       2 |         3 | |     2 | Shi Potian  |  22 | M      |       1 |         7 | |     3 | Xie Yanke   |  53 | M      |       2 |        16 | |     4 | Ding Dian   |  32 | M      |       4 |         4 | |     5 | Yu Yutong   |  26 | M      |       3 |         1 | +-------+-------------+-----+--------+---------+-----------+ 5 rows in set (0.00 sec) # 注意，这里在students表中的外键TeacherID中是teachers表中的主键，但是students表中的外键TID # 中的数据在teachers表中的主键TID中并不存在，再innoDB存储引擎中是不被允许的（即外键约束），但在 # MyISAM中不支持

2.自连接

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

MariaDB [hellodb]> select * from students as s,students as t where s.TeacherID = t.StuID; +-------+-------------+-----+--------+---------+-----------+-------+-------------+-----+--------+---------+-----------+ | StuID | Name        | Age | Gender | ClassID | TeacherID | StuID | Name        | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+-------+-------------+-----+--------+---------+-----------+ |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |     3 | Xie Yanke   |  53 | M      |       2 |        16 | |     2 | Shi Potian  |  22 | M      |       1 |         7 |     7 | Xi Ren      |  19 | F      |       3 |      NULL | |     3 | Xie Yanke   |  53 | M      |       2 |        16 |    16 | Xu Zhu      |  21 | M      |       1 |      NULL | |     4 | Ding Dian   |  32 | M      |       4 |         4 |     4 | Ding Dian   |  32 | M      |       4 |         4 | |     5 | Yu Yutong   |  26 | M      |       3 |         1 |     1 | Shi Zhongyu |  22 | M      |       2 |         3 | +-------+-------------+-----+--------+---------+-----------+-------+-------------+-----+--------+---------+-----------+ 5 rows in set (0.00 sec) MariaDB [hellodb]> select s.name,t.name from students as s,students as t where s.TeacherID = t.StuID; +-------------+-------------+ | name        | name        | +-------------+-------------+ | Shi Zhongyu | Xie Yanke   | | Shi Potian  | Xi Ren      | | Xie Yanke   | Xu Zhu      | | Ding Dian   | Ding Dian   | | Yu Yutong   | Shi Zhongyu | +-------------+-------------+ 5 rows in set (0.00 sec)

3.如题1，等值连接时，只显示匹配到的的数据，但是没有匹配到的就不予显示，如果，要想显示就需要用到左外连接和右外连接；
  1）左外连接：以左表为准，去连接匹配第二张表对应的数据，如果左表对应的数据不存在，就留空，但要保证左表中的数据都存在显示出来；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

MariaDB [hellodb]> select * from students left join teachers on students.TeacherID=teachers.TID; +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+ | StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender | +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+ |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      | |     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   | |     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   | |     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      | |     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      | |     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   | |     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   | |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   | |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   | |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   | |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   | |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   | |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   | |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   | |    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   | |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   | |    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   | |    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   | |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   | |    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   | |    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   | |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   | |    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   | |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   | |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   | +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+ 25 rows in set (0.00 sec) # 添加指定字段 MariaDB [hellodb]> select students.Name,teachers.Name from students left join teachers on students.TeacherID=teachers.TID; +---------------+---------------+ | Name          | Name          | +---------------+---------------+ | Shi Zhongyu   | Miejue Shitai | | Shi Potian    | NULL          | | Xie Yanke     | NULL          | | Ding Dian     | Lin Chaoying  | | Yu Yutong     | Song Jiang    | | Shi Qing      | NULL          | | Xi Ren        | NULL          | | Lin Daiyu     | NULL          | | Ren Yingying  | NULL          | | Yue Lingshan  | NULL          | | Yuan Chengzhi | NULL          | | Wen Qingqing  | NULL          | | Tian Boguang  | NULL          | | Lu Wushuang   | NULL          | | Duan Yu       | NULL          | | Xu Zhu        | NULL          | | Lin Chong     | NULL          | | Hua Rong      | NULL          | | Xue Baochai   | NULL          | | Diao Chan     | NULL          | | Huang Yueying | NULL          | | Xiao Qiao     | NULL          | | Ma Chao       | NULL          | | Xu Xian       | NULL          | | Sun Dasheng   | NULL          | +---------------+---------------+ 25 rows in set (0.00 sec)

  2）右外连接：以右表为准，右表的每一行都得找一个左表中与之对应的字段，如果左表中没有与之对应的字段，就留空，但要保证右表中的每一行都必须存在；

1 2 3 4 5 6 7 8 9 10

MariaDB [hellodb]> select * from students right join teachers on students.TeacherID=teachers.TID; +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender | +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ |     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      | |     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      | |     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      | |  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      | +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ 4 rows in set (0.01 sec)

--------------------------------------------------------------------------------

select 子查询：

★在查询中嵌套查询

☉用于WHERE子句中的子查询；

• 用于比较表达式中的子查询：子查询仅能返回单个值；
• 用于IN中的子查询：子查询可以返回一个列表值；
• 用于EXISTS中的子查询：
  
  

☉用于FROM子句中的子查询；

• SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;
  
  

注意：

• 所有的连接查询都可以用子查询替换，但在mysql中子查询未经充分优化，所以，能用连接查询，一定不用子查询。
• 在生产环境中一定不能使用不带where子句的select *，因为这是做全表扫描，会对网络I/O和磁盘I/O造成很大影响；
  
  

演示：
1.在students表中显示其年龄大于平均年龄的同学

1 2 3 4 5 6 7 8 9 10 11

MariaDB [hellodb]> select Name,Age from students where age > (select avg(age) from students); +--------------+-----+ | Name         | Age | +--------------+-----+ | Xie Yanke    |  53 | | Ding Dian    |  32 | | Shi Qing     |  46 | | Tian Boguang |  33 | | Sun Dasheng  | 100 | +--------------+-----+ 5 rows in set (0.00 sec)

2.显示同学的id号在老师id号范围内的同学的信息

1 2 3 4 5 6 7 8 9 10

MariaDB [hellodb]> select StuID,Name from students where StuID in (select TID from teachers); +-------+-------------+ | StuID | Name        | +-------+-------------+ |     1 | Shi Zhongyu | |     2 | Shi Potian  | |     3 | Xie Yanke   | |     4 | Ding Dian   | +-------+-------------+ 4 rows in set (0.00 sec)

3.显示年龄大于25且性别为男性的同学

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

MariaDB [hellodb]> select * from students where age>25 and gender='M'； +-------+--------------+-----+--------+---------+-----------+ | StuID | Name         | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ |     3 | Xie Yanke    |  53 | M      |       2 |        16 | |     4 | Ding Dian    |  32 | M      |       4 |         4 | |     5 | Yu Yutong    |  26 | M      |       3 |         1 | |     6 | Shi Qing     |  46 | M      |       5 |      NULL | |    13 | Tian Boguang |  33 | M      |       2 |      NULL | |    24 | Xu Xian      |  27 | M      |    NULL |      NULL | |    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL | +-------+--------------+-----+--------+---------+-----------+ 7 rows in set (0.00 sec) # 使用from子句表示如下: MariaDB [hellodb]> select * from (select * from students where age>25) as s  where s.gender='M'; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name         | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ |     3 | Xie Yanke    |  53 | M      |       2 |        16 | |     4 | Ding Dian    |  32 | M      |       4 |         4 | |     5 | Yu Yutong    |  26 | M      |       3 |         1 | |     6 | Shi Qing     |  46 | M      |       5 |      NULL | |    13 | Tian Boguang |  33 | M      |       2 |      NULL | |    24 | Xu Xian      |  27 | M      |    NULL |      NULL | |    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL | +-------+--------------+-----+--------+---------+-----------+ 7 rows in set (0.00 sec)

--------------------------------------------------------------------------------

select 联合查询：

★将多个查询语句的执行结果相合并；

• UNION
  
  

• SELECT clause UNION SELECT cluase；
  
  
  

演示：
将表students和teachers表中的ID，Name和Age联合起来一同显示，如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

MariaDB [hellodb]> select StuID,Name,Age from students; +-------+---------------+-----+ | StuID | Name          | Age | +-------+---------------+-----+ |     1 | Shi Zhongyu   |  22 | |     2 | Shi Potian    |  22 | |     3 | Xie Yanke     |  53 | |     4 | Ding Dian     |  32 | |     5 | Yu Yutong     |  26 | |     6 | Shi Qing      |  46 | |     7 | Xi Ren        |  19 | |     8 | Lin Daiyu     |  17 | |     9 | Ren Yingying  |  20 | |    10 | Yue Lingshan  |  19 | |    11 | Yuan Chengzhi |  23 | |    12 | Wen Qingqing  |  19 | |    13 | Tian Boguang  |  33 | |    14 | Lu Wushuang   |  17 | |    15 | Duan Yu       |  19 | |    16 | Xu Zhu        |  21 | |    17 | Lin Chong     |  25 | |    18 | Hua Rong      |  23 | |    19 | Xue Baochai   |  18 | |    20 | Diao Chan     |  19 | |    21 | Huang Yueying |  22 | |    22 | Xiao Qiao     |  20 | |    23 | Ma Chao       |  23 | |    24 | Xu Xian       |  27 | |    25 | Sun Dasheng   | 100 | +-------+---------------+-----+ 25 rows in set (0.00 sec) MariaDB [hellodb]> select TID,Name,Age from teachers; +-----+---------------+-----+ | TID | Name          | Age | +-----+---------------+-----+ |   1 | Song Jiang    |  45 | |   2 | Zhang Sanfeng |  94 | |   3 | Miejue Shitai |  77 | |   4 | Lin Chaoying  |  93 | +-----+---------------+-----+ 4 rows in set (0.00 sec) # 联合显示如下： MariaDB [hellodb]> select TID as ID,Name,Age from teachers union select StuID,Name,Age from students; +----+---------------+-----+ | ID | Name          | Age | +----+---------------+-----+ |  1 | Song Jiang    |  45 | |  2 | Zhang Sanfeng |  94 | |  3 | Miejue Shitai |  77 | |  4 | Lin Chaoying  |  93 | |  1 | Shi Zhongyu   |  22 | |  2 | Shi Potian    |  22 | |  3 | Xie Yanke     |  53 | |  4 | Ding Dian     |  32 | |  5 | Yu Yutong     |  26 | |  6 | Shi Qing      |  46 | |  7 | Xi Ren        |  19 | |  8 | Lin Daiyu     |  17 | |  9 | Ren Yingying  |  20 | | 10 | Yue Lingshan  |  19 | | 11 | Yuan Chengzhi |  23 | | 12 | Wen Qingqing  |  19 | | 13 | Tian Boguang  |  33 | | 14 | Lu Wushuang   |  17 | | 15 | Duan Yu       |  19 | | 16 | Xu Zhu        |  21 | | 17 | Lin Chong     |  25 | | 18 | Hua Rong      |  23 | | 19 | Xue Baochai   |  18 | | 20 | Diao Chan     |  19 | | 21 | Huang Yueying |  22 | | 22 | Xiao Qiao     |  20 | | 23 | Ma Chao       |  23 | | 24 | Xu Xian       |  27 | | 25 | Sun Dasheng   | 100 | +----+---------------+-----+ 29 rows in set (0.03 sec)

---

---

练习3：
1.显示前5位同学的姓名、课程及成绩；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

# 首先使用多表连接查看其信息，如下： MariaDB [hellodb]> select * from students,scores,courses where students.StuID = scores.StuID and scores.CourseID = courses.CourseID; +-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+ | StuID | Name        | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score | CourseID | Course         | +-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+ |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  1 |     1 |        2 |    77 |        2 | Kuihua Baodian | |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  2 |     1 |        6 |    93 |        6 | Weituo Zhang   | |     2 | Shi Potian  |  22 | M      |       1 |         7 |  3 |     2 |        2 |    47 |        2 | Kuihua Baodian | |     2 | Shi Potian  |  22 | M      |       1 |         7 |  4 |     2 |        5 |    97 |        5 | Daiyu Zanghua  | |     3 | Xie Yanke   |  53 | M      |       2 |        16 |  5 |     3 |        2 |    88 |        2 | Kuihua Baodian | |     3 | Xie Yanke   |  53 | M      |       2 |        16 |  6 |     3 |        6 |    75 |        6 | Weituo Zhang   | |     4 | Ding Dian   |  32 | M      |       4 |         4 |  7 |     4 |        5 |    71 |        5 | Daiyu Zanghua  | |     4 | Ding Dian   |  32 | M      |       4 |         4 |  8 |     4 |        2 |    89 |        2 | Kuihua Baodian | |     5 | Yu Yutong   |  26 | M      |       3 |         1 |  9 |     5 |        1 |    39 |        1 | Hamo Gong      | |     5 | Yu Yutong   |  26 | M      |       3 |         1 | 10 |     5 |        7 |    63 |        7 | Dagou Bangfa   | |     6 | Shi Qing    |  46 | M      |       5 |      NULL | 11 |     6 |        1 |    96 |        1 | Hamo Gong      | |     7 | Xi Ren      |  19 | F      |       3 |      NULL | 12 |     7 |        1 |    86 |        1 | Hamo Gong      | |     7 | Xi Ren      |  19 | F      |       3 |      NULL | 13 |     7 |        7 |    83 |        7 | Dagou Bangfa   | |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 14 |     8 |        4 |    57 |        4 | Taiji Quan     | |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 15 |     8 |        3 |    93 |        3 | Jinshe Jianfa  | +-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+ 15 rows in set (0.00 sec) # 过滤出指定的字段 MariaDB [hellodb]> select Name,Course,Score from students,scores,courses where students.StuID = scores.StuID and scores.CourseID = courses.CourseID; +-------------+----------------+-------+ | Name        | Course         | Score | +-------------+----------------+-------+ | Shi Zhongyu | Kuihua Baodian |    77 | | Shi Zhongyu | Weituo Zhang   |    93 | | Shi Potian  | Kuihua Baodian |    47 | | Shi Potian  | Daiyu Zanghua  |    97 | | Xie Yanke   | Kuihua Baodian |    88 | | Xie Yanke   | Weituo Zhang   |    75 | | Ding Dian   | Daiyu Zanghua  |    71 | | Ding Dian   | Kuihua Baodian |    89 | | Yu Yutong   | Hamo Gong      |    39 | | Yu Yutong   | Dagou Bangfa   |    63 | | Shi Qing    | Hamo Gong      |    96 | | Xi Ren      | Hamo Gong      |    86 | | Xi Ren      | Dagou Bangfa   |    83 | | Lin Daiyu   | Taiji Quan     |    57 | | Lin Daiyu   | Jinshe Jianfa  |    93 | +-------------+----------------+-------+ 15 rows in set (0.00 sec) # 嵌套一个子查询，显示前5名同学 MariaDB [hellodb]> select Name,Course,Score from (select * from students where StuID<=5) as m,scores,courses where m.StuID = scores.StuID and scores.CourseID = courses.CourseID; +-------------+----------------+-------+ | Name        | Course         | Score | +-------------+----------------+-------+ | Shi Zhongyu | Kuihua Baodian |    77 | | Shi Zhongyu | Weituo Zhang   |    93 | | Shi Potian  | Kuihua Baodian |    47 | | Shi Potian  | Daiyu Zanghua  |    97 | | Xie Yanke   | Kuihua Baodian |    88 | | Xie Yanke   | Weituo Zhang   |    75 | | Ding Dian   | Daiyu Zanghua  |    71 | | Ding Dian   | Kuihua Baodian |    89 | | Yu Yutong   | Hamo Gong      |    39 | | Yu Yutong   | Dagou Bangfa   |    63 | +-------------+----------------+-------+ 10 rows in set (0.00 sec)

2.显示其成绩高于80的同学的名称及课程；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# 只需要在上题的基础上再加一个过滤条件即可 MariaDB [hellodb]> select Name,Course,Score from students,scores,courses where students.StuID = scores.StuID and scores.CourseID = courses.CourseID and Score>80; +-------------+----------------+-------+ | Name        | Course         | Score | +-------------+----------------+-------+ | Shi Zhongyu | Weituo Zhang   |    93 | | Shi Potian  | Daiyu Zanghua  |    97 | | Xie Yanke   | Kuihua Baodian |    88 | | Ding Dian   | Kuihua Baodian |    89 | | Shi Qing    | Hamo Gong      |    96 | | Xi Ren      | Hamo Gong      |    86 | | Xi Ren      | Dagou Bangfa   |    83 | | Lin Daiyu   | Jinshe Jianfa  |    93 | +-------------+----------------+-------+ 8 rows in set (0.00 sec)

3.求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

1 2 3 4 5 6 7 8 9 10 11 12 13 14

MariaDB [hellodb]> select Name,avg(Score) from (select Name,Course,Score from students,scores,courses where students.StuID = scores.StuID and scores.CourseID = courses.CourseID) as m group by Name order by avg(Score) desc; +-------------+------------+ | Name        | avg(Score) | +-------------+------------+ | Shi Qing    |    96.0000 | | Shi Zhongyu |    85.0000 | | Xi Ren      |    84.5000 | | Xie Yanke   |    81.5000 | | Ding Dian   |    80.0000 | | Lin Daiyu   |    75.0000 | | Shi Potian  |    72.0000 | | Yu Yutong   |    51.0000 | +-------------+------------+ 8 rows in set (0.01 sec)

4.显示每门课程课程名称及学习了这门课的同学的个数；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

MariaDB [hellodb]> select * from scores; +----+-------+----------+-------+ | ID | StuID | CourseID | Score | +----+-------+----------+-------+ |  1 |     1 |        2 |    77 | |  2 |     1 |        6 |    93 | |  3 |     2 |        2 |    47 | |  4 |     2 |        5 |    97 | |  5 |     3 |        2 |    88 | |  6 |     3 |        6 |    75 | |  7 |     4 |        5 |    71 | |  8 |     4 |        2 |    89 | |  9 |     5 |        1 |    39 | | 10 |     5 |        7 |    63 | | 11 |     6 |        1 |    96 | | 12 |     7 |        1 |    86 | | 13 |     7 |        7 |    83 | | 14 |     8 |        4 |    57 | | 15 |     8 |        3 |    93 | +----+-------+----------+-------+ 15 rows in set (0.00 sec) # 首先查找出学习这门课（课程编号）有多少同学（同学编号） MariaDB [hellodb]> select CourseID,count(StuID) from scores group by CourseID; +----------+--------------+ | CourseID | count(StuID) | +----------+--------------+ |        1 |            3 | |        2 |            4 | |        3 |            1 | |        4 |            1 | |        5 |            2 | |        6 |            2 | |        7 |            2 | +----------+--------------+ 7 rows in set (0.00 sec) # 然后去用课程编号的表连接课程表，注意这里使用了子查询和别名 MariaDB [hellodb]> select Course,c from (select CourseID,count(StuID) as c from scores group by CourseID) as m,courses where m.CourseID=courses.CourseID; +----------------+---+ | Course         | c | +----------------+---+ | Hamo Gong      | 3 | | Kuihua Baodian | 4 | | Jinshe Jianfa  | 1 | | Taiji Quan     | 1 | | Daiyu Zanghua  | 2 | | Weituo Zhang   | 2 | | Dagou Bangfa   | 2 | +----------------+---+ 7 rows in set (0.00 sec)

---

练习4：
1、如何显示其年龄大于平均年龄的同学的名字？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

MariaDB [hellodb]>  select avg(age) from students; +----------+ | avg(age) | +----------+ |  27.4000 | +----------+ 1 row in set (0.00 sec) MariaDB [hellodb]> select Name,Age from students where age > (select avg(age) from students); +--------------+-----+ | Name         | Age | +--------------+-----+ | Xie Yanke    |  53 | | Ding Dian    |  32 | | Shi Qing     |  46 | | Tian Boguang |  33 | | Sun Dasheng  | 100 | +--------------+-----+ 5 rows in set (0.00 sec)

2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

MariaDB [hellodb]> select * from scores where CourseID in (1,2,4,7); +----+-------+----------+-------+ | ID | StuID | CourseID | Score | +----+-------+----------+-------+ |  1 |     1 |        2 |    77 | |  3 |     2 |        2 |    47 | |  5 |     3 |        2 |    88 | |  8 |     4 |        2 |    89 | |  9 |     5 |        1 |    39 | | 10 |     5 |        7 |    63 | | 11 |     6 |        1 |    96 | | 12 |     7 |        1 |    86 | | 13 |     7 |        7 |    83 | | 14 |     8 |        4 |    57 | +----+-------+----------+-------+ 10 rows in set (0.01 sec) MariaDB [hellodb]> select m.CourseID,students.Name from (select StuID,CourseID from scores where CourseID in (1,2,4,7)) as m,students where m.StuID = students.StuID; +----------+-------------+ | CourseID | Name        | +----------+-------------+ |        2 | Shi Zhongyu | |        2 | Shi Potian  | |        2 | Xie Yanke   | |        2 | Ding Dian   | |        1 | Yu Yutong   | |        7 | Yu Yutong   | |        1 | Shi Qing    | |        1 | Xi Ren      | |        7 | Xi Ren      | |        4 | Lin Daiyu   | +----------+-------------+ 10 rows in set (0.00 sec)