MySQL 查询语句使用进阶

===============================================================================

概述:

 本章为上一章内容的巩固和补充,具体内容如下:

  • SELECT单表查询语句的练习巩固;

  • SELECT多表查询;

     ·交叉连接

     ·内连接

     ·外链接

  • SELECT子查询:

     ·用于WHERE子句中的子查询;

     ·用于FROM子句中的子查询

  • SELECT联合查询:

     ·UNION

===============================================================================

练习:

wKioL1g4HV2SL5bWAAEumj-nvXc463.png


练习1

 首先导入hellodb.sql的脚本文件,查询其数据库和表如下:

[root@centos7 ~]# mysql -p134296 < hellodb.sql # 导入数据库脚本文件;
[root@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岁,且为男性的同学的名字和年龄;

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为分组依据,显示每组的平均年龄;

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的分组及平均年龄;

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开头的名字的同学的信息;

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非空的同学的相关信息;

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位同学的信息;

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岁的同学的信息;用三种方法;

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分组,显示每班的同学的人数;

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分组,显示其年龄之和;

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的班级;

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的学员的年龄之和;

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相等的同学的信息;

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)
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.自连接

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)左外连接:以左表为准,去连接匹配第二张表对应的数据,如果左表对应的数据不存在,就留空,但要保证左表中的数据都存在显示出来;

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)右外连接:以右表为准,右表的每一行都得找一个左表中与之对应的字段,如果左表中没有与之对应的字段,就留空,但要保证右表中的每一行都必须存在;

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表中显示其年龄大于平均年龄的同学

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号范围内的同学的信息

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且性别为男性的同学

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联合起来一同显示,如下:

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位同学的姓名、课程及成绩;

# 首先使用多表连接查看其信息,如下:
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的同学的名称及课程;

# 只需要在上题的基础上再加一个过滤条件即可
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位同学每位同学自己两门课的平均成绩,并按降序排列;

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.显示每门课程课程名称及学习了这门课的同学的个数;

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、如何显示其年龄大于平均年龄的同学的名字?

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门课的同学的名字?

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)