MySQL 查询语句使用进阶
===============================================================================概述：
===============================================================================
练习：
练习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
# mysql -p134296 < hellodb.sql # 导入数据库脚本文件；
# 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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
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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > select * from (select * from students where age>25) as swhere 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 >select avg(age) from students;
+----------+
| avg(age) |
+----------+
|27.4000 |
+----------+
1 row in set (0.00 sec)
MariaDB > 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 > 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 > 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)
页:
[1]