Mysql 多表查询和子查询
联结查询：事先将两张或多张表join，根据join的结果查询：
cross join： 交叉联结
(a+b)(c+d+e)=
自然联结：
等值联结：
条件比较
外联结：
左外联结：只保留出现在左外连接运算之前（左边）的关系中的元祖：
left_tb LEFT JOIN right_tb ON 连接条件
右外联结：只保留出现在右外连接运算之后（右边）的关系中的元祖：
left_tb RIGHT JOIN right_tb ON 连接条件
全外联结
自联结
子查询：在查询中嵌套的查询
用于WHERE中的子查询
1、用于比较表达式中的子查询
子查询的返回值只能有一个
2、用于EXISTS中的子查询
判断存在与否
3、用于IN中的子查询
判断存在于指定列表中
用于FROM中子查询：
SELECT alias,col,... FROM (SELECT clause) AS alias WHRER condition
先创建下面的几个表：
mysql> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshen Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| ClassID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QinCheng 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)
mysql> SELECT * FROM coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 6 |
| 5 | 3 | 1 |
| 6 | 3 | 7 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 1 |
| 10 | 5 | 9 |
| 11 | 6 | 3 |
| 12 | 6 | 4 |
| 13 | 7 | 4 |
| 14 | 7 | 3 |
+----+---------+----------+
14 rows in set (0.02 sec)
mysql> 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 | 2 | 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 | 1 | NULL |
| 25 | Sun Dasheng | 100 | M | 2 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
mysql> 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)
mysql> 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)
通过ClassID 使两张表建立关系：
mysql> 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 | QinCheng Pai | 11 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 3 | QinCheng 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 | QinCheng 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 | 2 | NULL | 2 | Emei Pai | 7 |
| 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 |
| 24 | Xu Xian | 27 | M | 1 | NULL | 1 | Shaolin Pai | 10 |
| 25 | Sun Dasheng | 100 | M | 2 | NULL | 2 | Emei Pai | 7 |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
25 rows in set (0.01 sec)
查看哪位同学及其所处的班级
mysql> 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 | QinCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QinCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QinCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | Emei 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 |
| Xu Xian | Shaolin Pai |
| Sun Dasheng | Emei Pai |
+---------------+----------------+
25 rows in set (0.00 sec)
使用AS定义表别名
mysql> SELECT s.Name,c.Class FROM students AS s, classes AS c WHERE s.ClassID = c.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QinCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QinCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QinCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | Emei 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 |
| Xu Xian | Shaolin Pai |
| Sun Dasheng | Emei Pai |
+---------------+----------------+
25 rows in set (0.01 sec)
查询两表中有同样字段信息的学生和班级的信息（使用左联结）
mysql> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QinCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QinCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QinCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | Emei 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 |
| Xu Xian | Shaolin Pai |
| Sun Dasheng | Emei Pai |
+---------------+----------------+
25 rows in set (0.03 sec)
以班级分类，查询某个班级的学生信息
mysql> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Potian | Shaolin Pai |
| Wen Qingqing | Shaolin Pai |
| Xu Zhu | Shaolin Pai |
| Xiao Qiao | Shaolin Pai |
| Xu Xian | Shaolin Pai |
| Shi Zhongyu | Emei Pai |
| Xie Yanke | Emei Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | Emei Pai |
| Sun Dasheng | Emei Pai |
| Yu Yutong | QinCheng Pai |
| Xi Ren | QinCheng Pai |
| Yue Lingshan | QinCheng Pai |
| Ding Dian | Wudang Pai |
| Duan Yu | Wudang Pai |
| Lin Chong | Wudang Pai |
| Ma Chao | Wudang Pai |
| Shi Qing | Riyue Shenjiao |
| Ren Yingying | Lianshan Pai |
| Yuan Chengzhi | Lianshan Pai |
| Xue Baochai | Lianshan Pai |
| Huang Yueying | Lianshan Pai |
| Lin Daiyu | Ming Jiao |
| Hua Rong | Ming Jiao |
| Diao Chan | Ming Jiao |
| NULL | Xiaoyao Pai |
+---------------+----------------+
26 rows in set (0.00 sec)
查看StuID前5名同学所学的课程及其成绩
mysql> SELECT Name,Course,Score FROM students AS s,courses AS c,coc,scores AS ss WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID <= 5 AND s.StuID = ss.StuID;
+-------------+----------------+-------+
| Name | Course | Score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 77 |
| Shi Zhongyu | Kuihua Baodian | 93 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 47 |
| Shi Potian | Kuihua Baodian | 97 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 88 |
| Xie Yanke | Kuihua Baodian | 75 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 71 |
| Ding Dian | Daiyu Zanghua | 89 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 39 |
| Yu Yutong | Hamo Gong | 63 |
| Yu Yutong | Dagou Bangfa | 63 |
+-------------+----------------+-------+
20 rows in set (0.01 sec)
去除重复
mysql> SELECT Name,Course,Score FROM students AS s,courses AS c,coc,scores AS ss WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID <= 5 AND s.StuID = ss.StuID AND coc.CourseID = ss.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.02 sec)
求前8位同学每位同学自己两门课的平均成绩，并按降序排列：
mysql> SELECT Name,AVG(Score) FROM students AS s,courses AS c,coc,scores AS ss WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID <= 8 AND s.StuID = ss.StuID AND coc.CourseID = ss.CourseID 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.03 sec)
找出年龄大于平均年龄的同学（子查询）
mysql> SELECT Name FROM students WHERE Age > (SELECT AVG(Age) FROM students);
+--------------+
| Name |
+--------------+
| Xie Yanke |
| Ding Dian |
| Shi Qing |
| Tian Boguang |
| Sun Dasheng |
+--------------+
5 rows in set (0.00 sec)
查询年龄大于25的男同学
第一种实现方法：
mysql> SELECT Name,Age FROM students WHERE Age > 25 AND Gender = 'M';
+--------------+-----+
| Name | Age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+
7 rows in set (0.01 sec)
第二种实现方法：
mysql> SELECT s.Name,s.Age,s.Gender FROM (SELECT * FROM students WHERE Gender = 'M') AS s WHERE s.Age > 25;
+--------------+-----+--------+
| 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 |
+--------------+-----+--------+
7 rows in set (0.00 sec)
查询属于7班或6班的同学
第一种方法：
mysql> SELECT Name,ClassID FROM students WHERE ClassID = 7 or ClassID = 6;
+---------------+---------+
| Name | ClassID |
+---------------+---------+
| Lin Daiyu | 7 |
| Ren Yingying | 6 |
| Yuan Chengzhi | 6 |
| Hua Rong | 7 |
| Xue Baochai | 6 |
| Diao Chan | 7 |
| Huang Yueying | 6 |
+---------------+---------+
7 rows in set (0.00 sec)
第二种方法：
mysql> SELECT Name,ClassID FROM students WHERE CLassID >= 6;
+---------------+---------+
| Name | ClassID |
+---------------+---------+
| Lin Daiyu | 7 |
| Ren Yingying | 6 |
| Yuan Chengzhi | 6 |
| Hua Rong | 7 |
| Xue Baochai | 6 |
| Diao Chan | 7 |
| Huang Yueying | 6 |
+---------------+---------+
7 rows in set (0.00 sec)
MySQL 的联合查询：
SELECT clause UNION SELECT clause UNION ...
把两张表或两张以上的表联合起来查询
示例：把老师和同学的两个表联合起来
mysql> SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Xi Ren | 19 |
| Lin Daiyu | 17 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing | 19 |
| Tian Boguang | 33 |
| Lu Wushuang | 17 |
| Duan Yu | 19 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Xue Baochai | 18 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
| Ma Chao | 23 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+---------------+-----+
29 rows in set (0.02 sec)
阅读(1371) | 评论(0) | 转发(0) |
