mysql内连接查询练习

mysql内连接查询练习

MySQL连接查询、多表查询、子查询：

连接查询：事先将两张或多张表join，根据join的结果进行查询；

【导入hellodb.sql数据库】，输入密码即可[root@pc0003 home]# mysql -uroot -p mydb

【查看students表】mysql> select * from students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 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 |

+-------+---------------+-----+--------+---------+-----------+

【查看 classes表】mysql> 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 |

+---------+----------------+----------+

【外连接】：

左外连接：只保留出现在左外连接运算之前(左边)的关系中的元组；

left_tb LEFT JOIN right_tb ON 连接条件

右外连接：只保留出现在右外连接运算之后(右边)的关系中的元组；

left_tb RIGHT JOIN right_tb ON 连接条件

全外连接

【交叉连接查询】

cross join: 交叉连接

(a+b)(c+d+e)=

mysql> select * from students,classes;

+-------+---------------+-----+--------+---------+

+-------+---------------+-----+--------+---------+

| 1 | Shi Zhongyu | 22 | M | 2 |

| 2 | Shi Potian | 22 | M | 1 |

………………………此处省略很多行………………………………………………………………………

| 24 | Xu Xian | 27 | M | NULL |

| 25 | Sun Dasheng | 100 | M | NULL |

+-------+---------------+-----+--------+---------+

200 rows in set (0.00 sec)

【自然连接：等值连接】mysql> select * from students,classes where students.ClassID = classes.ClassID;

+-------+---------------+-----+--------+----------------+

+-------+---------------+-----+--------+----------------+

| 1 | Shi Zhongyu | 22 | M | Emei Pai |

| 2 | Shi Potian | 22 | M | Shaolin Pai |

| 3 | Xie Yanke | 53 | M | Emei Pai |

| 4 | Ding Dian | 32 | M | Wudang Pai |

| 5 | Yu Yutong | 26 | M | QingCheng Pai |

| 6 | Shi Qing | 46 | M | Riyue Shenjiao |

| 7 | Xi Ren | 19 | F | QingCheng Pai |

| 8 | Lin Daiyu | 17 | F | Ming Jiao |

| 9 | Ren Yingying | 20 | F | Lianshan Pai |

| 10 | Yue Lingshan | 19 | F | QingCheng Pai |

| 11 | Yuan Chengzhi | 23 | M | Lianshan Pai |

| 12 | Wen Qingqing | 19 | F | Shaolin Pai |

| 13 | Tian Boguang | 33 | M | Emei Pai |

| 14 | Lu Wushuang | 17 | F | QingCheng Pai |

| 15 | Duan Yu | 19 | M | Wudang Pai |

| 16 | Xu Zhu | 21 | M | Shaolin Pai |

| 17 | Lin Chong | 25 | M | Wudang Pai |

| 18 | Hua Rong | 23 | M | Ming Jiao |

| 19 | Xue Baochai | 18 | F | Lianshan Pai |

| 20 | Diao Chan | 19 | F | Ming Jiao |

| 21 | Huang Yueying | 22 | F | Lianshan Pai |

| 22 | Xiao Qiao | 20 | F | Shaolin Pai |

| 23 | Ma Chao | 23 | M | Wudang Pai |

+-------+---------------+-----+--------+----------------+

23 rows in set (0.00 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 | 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.08 sec)

【别名】：

表别名

字段别名mysql> select Name from students;

+---------------+

| Name |

+---------------+

| Shi Zhongyu |

| Shi Potian |

| Xie Yanke |

| Ding Dian |

| Yu Yutong |

| Shi Qing |

| Xi Ren |

| Lin Daiyu |

| Ren Yingying |

| Yue Lingshan |

| Yuan Chengzhi |

| Wen Qingqing |

| Tian Boguang |

| Lu Wushuang |

| Duan Yu |

| Xu Zhu |

| Lin Chong |

| Hua Rong |

| Xue Baochai |

| Diao Chan |

| Huang Yueying |

| Xiao Qiao |

| Ma Chao |

| Xu Xian |

| Sun Dasheng |

+---------------+

25 rows in set (0.00 sec)

【Name as StuNmae】mysql> select Name as StuNmae from students;

+---------------+

| StuNmae |

+---------------+

| Shi Zhongyu |

| Shi Potian |

| Xie Yanke |

| Ding Dian |

| Yu Yutong |

| Shi Qing |

| Xi Ren |

| Lin Daiyu |

| Ren Yingying |

| Yue Lingshan |

| Yuan Chengzhi |

| Wen Qingqing |

| Tian Boguang |

| Lu Wushuang |

| Duan Yu |

| Xu Zhu |

| Lin Chong |

| Hua Rong |

| Xue Baochai |

| Diao Chan |

| Huang Yueying |

| Xiao Qiao |

| Ma Chao |

| Xu Xian |

| Sun Dasheng |

+---------------+

25 rows in set (0.00 sec)

【内连接，等值连接：表别名】调用时也使用别名，否则报错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 | 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)

总结：MySQL的连接查询及子查询

连接：

交叉连接

内连接

外连接

左外

右外

【自连接，StuID 连接 TeacherID 】mysql> select * from students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 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 |

| 26 | Tom | 33 | F | 8 | 11 |

| 27 | Jerry | 25 | M | 9 | 2 |

+-------+---------------+-----+--------+---------+-----------+

27 rows in set (0.00 sec)

【自连接：显示学生对应的老师】左边学生，右边老师mysql> select t.Name,s.Name from students as s,students as t where s.StuID = t.TeacherID;

+-------------+---------------+

| Name | Name |

+-------------+---------------+

| Shi Zhongyu | Xie Yanke |

| Shi Potian | Xi Ren |

| Xie Yanke | Xu Zhu |

| Ding Dian | Ding Dian |

| Yu Yutong | Shi Zhongyu |

| Tom | Yuan Chengzhi |

| Jerry | Shi Potian |

+-------------+---------------+

7 rows in set (0.00 sec)

【【【【外连接】】】】

【左连接，显示每个人学的武功，保留左表人名】 left join ... on...

以左表为准，保留左表的显示，对应的右边有也显示，没有就为nullmysql> 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 | 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 |

| Xu Xian | NULL |

| Sun Dasheng | NULL |

+---------------+----------------+

25 rows in set (0.00 sec)

【右连接：显示每门功夫谁在学，保留右表功夫课程】，以classes表为准，显示全部课程，没有人学的就显示空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 |

| Shi Zhongyu | Emei Pai |

| Xie Yanke | Emei Pai |

| Tian Boguang | Emei Pai |

| Yu Yutong | QingCheng Pai |

| Xi Ren | QingCheng Pai |

| Yue Lingshan | QingCheng Pai |

| Lu Wushuang | QingCheng 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 |

+---------------+----------------+

24 rows in set (0.00 sec)

练习：导入hellodb.sql，完成以下题目：

1、显示前5位同学的姓名、课程及成绩；【内链接练习】

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

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

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

1、显示前5位同学的姓名、课程及成绩；【内链接练习】

【显示姓名，课程】mysql> select Name,Course from students as s,courses as c,coc where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID;

+---------------+----------------+

| Name | Course |

+---------------+----------------+

| Shi Zhongyu | Kuihua Baodian |

| Shi Zhongyu | Weituo Zhang |

| Shi Potian | Kuihua Baodian |

| Shi Potian | Daiyu Zanghua |

| Xie Yanke | Kuihua Baodian |

| Xie Yanke | Weituo Zhang |

| Ding Dian | Daiyu Zanghua |

| Ding Dian | Kuihua Baodian |

| Yu Yutong | Hamo Gong |

| Yu Yutong | Dagou Bangfa |

| Shi Qing | Hamo Gong |

| Xi Ren | Hamo Gong |

| Xi Ren | Dagou Bangfa |

| Lin Daiyu | Taiji Quan |

| Lin Daiyu | Jinshe Jianfa |

| Ren Yingying | Jinshe Jianfa |

| Ren Yingying | Taiji Quan |

| Yue Lingshan | Hamo Gong |

| Yue Lingshan | Dagou Bangfa |

| Yuan Chengzhi | Jinshe Jianfa |

| Yuan Chengzhi | Taiji Quan |

| Wen Qingqing | Kuihua Baodian |

| Wen Qingqing | Daiyu Zanghua |

| Tian Boguang | Kuihua Baodian |

| Tian Boguang | Weituo Zhang |

| Lu Wushuang | Hamo Gong |

| Lu Wushuang | Dagou Bangfa |

| Duan Yu | Daiyu Zanghua |

| Duan Yu | Kuihua Baodian |

| Xu Zhu | Kuihua Baodian |

| Xu Zhu | Daiyu Zanghua |

| Lin Chong | Daiyu Zanghua |

| Lin Chong | Kuihua Baodian |

| Hua Rong | Taiji Quan |

| Hua Rong | Jinshe Jianfa |

| Xue Baochai | Jinshe Jianfa |

| Xue Baochai | Taiji Quan |

| Diao Chan | Taiji Quan |

| Diao Chan | Jinshe Jianfa |

| Huang Yueying | Jinshe Jianfa |

| Huang Yueying | Taiji Quan |

| Xiao Qiao | Kuihua Baodian |

| Xiao Qiao | Daiyu Zanghua |

| Ma Chao | Daiyu Zanghua |

| Ma Chao | Kuihua Baodian |

+---------------+----------------+

45 rows in set (0.00 sec)

【显示前5名姓名，课程】mysql> select Name,Course from students as s,courses as c,coc where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID<=5;

+-------------+----------------+

| Name | Course |

+-------------+----------------+

| Shi Potian | Kuihua Baodian |

| Shi Potian | Daiyu Zanghua |

| Shi Zhongyu | Kuihua Baodian |

| Xie Yanke | Kuihua Baodian |

| Shi Zhongyu | Weituo Zhang |

| Xie Yanke | Weituo Zhang |

| Yu Yutong | Hamo Gong |

| Yu Yutong | Dagou Bangfa |

| Ding Dian | Daiyu Zanghua |

| Ding Dian | Kuihua Baodian |

+-------------+----------------+

10 rows in set (0.02 sec)

【显示这个表】mysql> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| toc |

+-------------------+

7 rows in set (0.03 sec)

【查看成绩表】mysql> select * from scores;

+----+-------+----------+-------+

+----+-------+----------+-------+

| 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.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;

+-------------+----------------+-------+

| 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.00 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.00 sec)

【第一题完成！】

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

【前8位】mysql> select Name,Course from students as s,courses as c,coc where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID<=8;

+-------------+----------------+

| Name | Course |

+-------------+----------------+

| Shi Zhongyu | Kuihua Baodian |

| Shi Zhongyu | Weituo Zhang |

| Shi Potian | Kuihua Baodian |

| Shi Potian | Daiyu Zanghua |

| Xie Yanke | Kuihua Baodian |

| Xie Yanke | Weituo Zhang |

| Ding Dian | Daiyu Zanghua |

| Ding Dian | Kuihua Baodian |

| Yu Yutong | Hamo Gong |

| Yu Yutong | Dagou Bangfa |

| Shi Qing | Hamo Gong |

| Xi Ren | Hamo Gong |

| Xi Ren | Dagou Bangfa |

| Lin Daiyu | Taiji Quan |

| Lin Daiyu | Jinshe Jianfa |

+-------------+----------------+

15 rows in set (0.05 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 <=8 and s.StuID=ss.StuID and coc.CourseID = ss.CourseID group by Name;

+-------------+----------------+-------+

| Name | Course | Score |

+-------------+----------------+-------+

| Ding Dian | Daiyu Zanghua | 71 |

| Lin Daiyu | Taiji Quan | 57 |

| Shi Potian | Kuihua Baodian | 47 |

| Shi Qing | Hamo Gong | 96 |

| Shi Zhongyu | Kuihua Baodian | 77 |

| Xi Ren | Hamo Gong | 86 |

| Xie Yanke | Kuihua Baodian | 88 |

| Yu Yutong | Hamo Gong | 39 |

+-------------+----------------+-------+

8 rows in set (0.05 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.CoureID and s.StuID <=8 and s.StuID=ss.StuID and coc.CourseID = ss.CourseID and Score>=80 group by Name;

+-------------+----------------+-------+

| Name | Course | Score |

+-------------+----------------+-------+

| Ding Dian | Kuihua Baodian | 89 |

| Lin Daiyu | Jinshe Jianfa | 93 |

| Shi Potian | Daiyu Zanghua | 97 |

| Shi Qing | Hamo Gong | 96 |

| Shi Zhongyu | Weituo Zhang | 93 |

| Xi Ren | Hamo Gong | 86 |

| Xie Yanke | Kuihua Baodian | 88 |

+-------------+----------------+-------+

7 rows in set (0.00 sec)

【第二题完成】

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

mysql> select Name,Course,Score,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;

+-------------+----------------+-------+------------+

+-------------+----------------+-------+------------+

| Ding Dian | Daiyu Zanghua | 71 | 80.0000 |

| Lin Daiyu | Taiji Quan | 57 | 75.0000 |

| Shi Potian | Kuihua Baodian | 47 | 72.0000 |

| Shi Qing | Hamo Gong | 96 | 96.0000 |

| Shi Zhongyu | Kuihua Baodian | 77 | 85.0000 |

| Xi Ren | Hamo Gong | 86 | 84.5000 |

| Xie Yanke | Kuihua Baodian | 88 | 81.5000 |

| Yu Yutong | Hamo Gong | 39 | 51.0000 |

+-------------+----------------+-------+------------+

mysql> select Name,Course,Score,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;

+-------------+----------------+-------+------------+

+-------------+----------------+-------+------------+

| Shi Qing | Hamo Gong | 96 | 96.0000 |

| Shi Zhongyu | Kuihua Baodian | 77 | 85.0000 |

| Xi Ren | Hamo Gong | 86 | 84.5000 |

| Xie Yanke | Kuihua Baodian | 88 | 81.5000 |

| Ding Dian | Daiyu Zanghua | 71 | 80.0000 |

| Lin Daiyu | Taiji Quan | 57 | 75.0000 |

| Shi Potian | Kuihua Baodian | 47 | 72.0000 |

| Yu Yutong | Hamo Gong | 39 | 51.0000 |

+-------------+----------------+-------+------------+

8 rows in set (0.00 sec)

【第三题完成】

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

mysql> select * from courses;

+----------+----------------+

| CourseID | Course |

+----------+----------------+

| 1 | Hamo Gong |

| 2 | Kuihua Baodian |

| 3 | Jinshe Jianfa |

| 4 | Taiji Quan |

| 5 | Daiyu Zanghua |

| 6 | Weituo Zhang |

| 7 | Dagou Bangfa |

+----------+----------------+

7 rows in set (0.00 sec)

#################################################################

【子查询：在查询中嵌套的查询】

用于WHERE中的子查询

1、用于比较表达式中的子查询

子查询的返回值只能有一个；

2、用于EXISTS中的子查询

判断存在与否

3、用于IN中的子查询；

判断存在于指定列表中

用于FROM中子查询：

SELECT alias.col,... FROM (SELECT clause) AS alias WHERE condition

MySQL不擅长于子查询：应该避免使用子查询；

子查询：

用于WHERE中的子查询

用于条件比较：子查询只能一个值

用于IN：子查询可以返回多个值

EXISTS：子查询可以返回多个值

用于FROM子句的子查询

MySQL的联合查询：SELECT clauase UNION SELECT clause UNION ...

把两个或多个查询语句的结果合并成一个结果进行输出；

MySQL视图:

存储下来的SELECT语句；

思考：

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

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

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

4、统计各班级中年龄大于全校同学平均年龄的同学。

【子查询】

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

+--------------+-----+

【查询年龄大于20的男性】mysql> select Name,Age,Gender from students where Age >20 ;

+---------------+-----+--------+

| Name | Age | Gender |

+---------------+-----+--------+

| Shi Zhongyu | 22 | M |

| Shi Potian | 22 | M |

| Xie Yanke | 53 | M |

| Ding Dian | 32 | M |

| Yu Yutong | 26 | M |

| Shi Qing | 46 | M |

| Yuan Chengzhi | 23 | M |

| Tian Boguang | 33 | M |

| Xu Zhu | 21 | M |

| Lin Chong | 25 | M |

| Hua Rong | 23 | M |

| Huang Yueying | 22 | F |

| Ma Chao | 23 | M |

| Xu Xian | 27 | M |

| Sun Dasheng | 100 | M |

+---------------+-----+--------+

15 rows in set (0.00 sec)

mysql> select Name,Age,Gender from students where Age >20 and Gender='M';

+---------------+-----+--------+

| Name | Age | Gender |

+---------------+-----+--------+

| Shi Zhongyu | 22 | M |

| Shi Potian | 22 | M |

| Xie Yanke | 53 | M |

| Ding Dian | 32 | M |

| Yu Yutong | 26 | M |

| Shi Qing | 46 | M |

| Yuan Chengzhi | 23 | M |

| Tian Boguang | 33 | M |

| Xu Zhu | 21 | M |

| Lin Chong | 25 | M |

| Hua Rong | 23 | M |

| Ma Chao | 23 | M |

| Xu Xian | 27 | M |

| Sun Dasheng | 100 | M |

+---------------+-----+--------+

14 rows in set (0.00 sec)

【从一个临时的表里面返回的结果查询】(select * from students where Gender='M')

注意:要给临时表一个别名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.03 sec)

【添加两个同学】mysql> insert into students values (26,'Tom',33,'F',8,11),(27,'Jerry',25,'M',9,2);

mysql> select * from students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 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 |

| 26 | Tom | 33 | F | 8 | 11 |

| 27 | Jerry | 25 | M | 9 | 2 |

+-------+---------------+-----+--------+---------+-----------+

27 rows in set (0.00 sec)

【插入9班，课程水波梁山，班级人数22】mysql> insert into classes values (9,'Liangshan',22);

mysql> 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 |

| 9 | Liangshan | 22 |

+---------+----------------+----------+

【显示那些课程没有人在学】from classes left joinmysql> select classes.Class,classes.ClassID,coc.ClassID from classes left join coc on classes.ClassID = coc.ClassID;

+----------------+---------+---------+

| Class | ClassID | ClassID |

+----------------+---------+---------+

| Shaolin Pai | 1 | 1 |

| Emei Pai | 2 | 2 |

| QingCheng Pai | 3 | 3 |

| Wudang Pai | 4 | 4 |

| Riyue Shenjiao | 5 | 5 |

| Lianshan Pai | 6 | 6 |

| Ming Jiao | 7 | 7 |

| Xiaoyao Pai | 8 | NULL |

| Liangshan | 9 | NULL |

+----------------+---------+---------+

16 rows in set (0.00 sec)

【显示了两门课程没有人在学】mysql> select classes.Class,classes.ClassID,coc.ClassID from classes left join coc on classes.ClassID = coc.ClassID where coc.ClassID is NULL;

+-------------+---------+---------+

| Class | ClassID | ClassID |

+-------------+---------+---------+

| Xiaoyao Pai | 8 | NULL |

| Liangshan | 9 | NULL |

+-------------+---------+---------+

2 rows in set (0.00 sec)

【显示没有在学课程的人】mysql> SELECT Name FROM students WHERE ClassID IN (SELECT classes.ClassID from classes left join coc on classes.ClassID = coc.ClassID where coc.ClassID is NULL);

+-------+

| Name |

+-------+

| Tom |

| Jerry |

+-------+

2 rows in set (0.00 sec

#################################################

联合查询 union 合成成一个结果输出mysql> select Name,Age from students;

+---------------+-----+

| Name | Age |

+---------------+-----+

| 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 |

| Tom | 33 |

| Jerry | 25 |

+---------------+-----+

27 rows in set (0.00 sec)

mysql> select Name,Age from teachers;

+---------------+-----+

| Name | Age |

+---------------+-----+

| Song Jiang | 45 |

| Zhang Sanfeng | 94 |

| Miejue Shitai | 77 |

| Lin Chaoying | 93 |

+---------------+-----+

4 rows in set (0.02 sec)

【联合查询，显示合并】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 |

| Tom | 33 |

| Jerry | 25 |

+---------------+-----+

31 rows in set (0.00 sec)

【显示表的索引】mysql> show indexes from students;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+

| students | 0 | PRIMARY | 1 | StuID | A | 27 | NULL |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+

【正常一次查询】mysql> select Name,Age from students where Age > 25;

+--------------+-----+

| Name | Age |

+--------------+-----+

| Xie Yanke | 53 |

| Ding Dian | 32 |

| Yu Yutong | 26 |

| Shi Qing | 46 |

| Tian Boguang | 33 |

| Xu Xian | 27 |

| Sun Dasheng | 100 |

| Tom | 33 |

+--------------+-----+

8 rows in set (0.00 sec)

【纵向 explain 执行语句】mysql> explain select Name,Age from students where Age > 25 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 27

Extra: Using where

1 row in set (0.00 sec)

并没有引用索引

【添加索引】mysql> alter table students add index (Age);

Query OK, 27 rows affected (0.14 sec)

Records: 27 Duplicates: 0 Warnings: 0

【show indexes from students】mysql> show indexes from students;

+----------+------------+----------+------------+

+----------+------------+----------+------------+

| students | 1 | Age | BTREE |

+----------+------------+----------+------------+

2 rows in set (0.00 sec)

显示 BTREE 索引

【纵向 explain 执行语句】mysql> explain select Name,Age from students where Age > 25 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: ALL

possible_keys: Age

key: NULL

key_len: NULL

ref: NULL

rows: 27

Extra: Using where

【mysql视图】

临时执行，临时查询，虚表mysql> help create view

【创建视图】mysql> create view stu as select StuID,Name,Gender from students;

Query OK, 0 rows affected (0.07 sec)

【会发现多了一张表】mysql> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes |

| coc |

| courses |

| scores |

| stu |

| students |

| teachers |

| toc |

+-------------------+

8 rows in set (0.00 sec)

【显示表状态】mysql> show table status \G

*************************** 5. row ***************************

Name: stu

Engine: NULL

Version: NULL

Row_format: NULL

Rows: NULL

Avg_row_length: NULL

Data_length: NULL

Max_data_length: NULL

Index_length: NULL

Data_free: NULL

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: NULL

Checksum: NULL

Create_options: NULL

Comment: VIEW

状态全为空，最后一行显示为view

【可以对它进行查询】mysql> select * from stu;

+-------+---------------+--------+

| StuID | Name | Gender |

+-------+---------------+--------+

| 1 | Shi Zhongyu | M |

| 2 | Shi Potian | M |

| 3 | Xie Yanke | M |

| 4 | Ding Dian | M |

| 5 | Yu Yutong | M |

| 6 | Shi Qing | M |

| 7 | Xi Ren | F |

| 8 | Lin Daiyu | F |

| 9 | Ren Yingying | F |

| 10 | Yue Lingshan | F |

| 11 | Yuan Chengzhi | M |

| 12 | Wen Qingqing | F |

| 13 | Tian Boguang | M |

| 14 | Lu Wushuang | F |

| 15 | Duan Yu | M |

| 16 | Xu Zhu | M |

| 17 | Lin Chong | M |

| 18 | Hua Rong | M |

| 19 | Xue Baochai | F |

| 20 | Diao Chan | F |

| 21 | Huang Yueying | F |

| 22 | Xiao Qiao | F |

| 23 | Ma Chao | M |

| 24 | Xu Xian | M |

| 25 | Sun Dasheng | M |

| 26 | Tom | F |

| 27 | Jerry | M |

+-------+---------------+--------+

27 rows in set (0.03 sec)

【查看视图是如何被创建的】mysql> show create view stu \G

*************************** 1. row ***************************

View: stu

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu` AS select `students`.`StuID` AS `StuID`,`students`.`Name` AS `Name`,`students`.`Gender` AS `Gender` from `students`

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

【删除用户】mysql> drop user 'testuser'@'172.16.%.%';

【授权】mysql> grant all on hellodb.stu to 'test'@'172.16.%.%' identified by 'testpass；

【删除视图】mysql> drop view stu;

。。。。。。。。。。。未完结。。。。。。。。。

更多推荐

mysql内连接查询练习