注意：
练习前请先下载附件里的文件，并且将文件导入mysql中，导入方法为mysql -u user -h host -p password < hellodb.sql 多表查询一定要多设定约束条件
SELECT查询练习
首先是连接mysql，我这里没设置密码，直接输入mysql就连接进来了。
1.先查询有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.然后选择我们要练习的数据库
mysql> 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
3.看看数据库中有哪些表
mysql> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
4.查看表有哪些属性信息，可以使用如下命令
mysql> 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)
5.查看students表的所有信息(ps:不妨也查看其他的表信息，因为将在下面的练习会用到，最好仔细看看才好)
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 | 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)
6.查询表中年龄大于25且性别为男的同学
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.查询表中年龄大于40且性别为男性的同学
知识点：将查询的到表作为中间表继续其他的查询
mysql> SELECT * FROM (SELECT * FROM students WHERE age>40) AS s where s.Gender='M';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
8.查询students,classes表中ClassID相同的信息
mysql> 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 | 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.01 sec)
9.查询students,classes表中ClassID相同的同学的姓名，班级以及性别
mysql> SELECT Name,class,gender,s.classID FROM students AS s,classes AS c where s.ClassID = c.classID;
+---------------+----------------+--------+---------+
| Name | class | gender | classID |
+---------------+----------------+--------+---------+
| Shi Zhongyu | Emei Pai | M | 2 |
| Shi Potian | Shaolin Pai | M | 1 |
| Xie Yanke | Emei Pai | M | 2 |
| Ding Dian | Wudang Pai | M | 4 |
| Yu Yutong | QingCheng Pai | M | 3 |
| Shi Qing | Riyue Shenjiao | M | 5 |
| Xi Ren | QingCheng Pai | F | 3 |
| Lin Daiyu | Ming Jiao | F | 7 |
| Ren Yingying | Lianshan Pai | F | 6 |
| Yue Lingshan | QingCheng Pai | F | 3 |
| Yuan Chengzhi | Lianshan Pai | M | 6 |
| Wen Qingqing | Shaolin Pai | F | 1 |
| Tian Boguang | Emei Pai | M | 2 |
| Lu Wushuang | QingCheng Pai | F | 3 |
| Duan Yu | Wudang Pai | M | 4 |
| Xu Zhu | Shaolin Pai | M | 1 |
| Lin Chong | Wudang Pai | M | 4 |
| Hua Rong | Ming Jiao | M | 7 |
| Xue Baochai | Lianshan Pai | F | 6 |
| Diao Chan | Ming Jiao | F | 7 |
| Huang Yueying | Lianshan Pai | F | 6 |
| Xiao Qiao | Shaolin Pai | F | 1 |
| Ma Chao | Wudang Pai | M | 4 |
+---------------+----------------+--------+---------+
23 rows in set (0.00 sec)
10.查询表中所有女同学的年龄
mysql> SELECT name,age FROM students WHERE Gender='F';
+---------------+-----+
| name | age |
+---------------+-----+
| Xi Ren | 19 |
| Lin Daiyu | 17 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Wen Qingqing | 19 |
| Lu Wushuang | 17 |
| Xue Baochai | 18 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
+---------------+-----+
10 rows in set (0.00 sec)
11.查询表中的平均年龄
mysql> SELECT avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)
12.查询表中最大的年龄
mysql> SELECT max(age) from teachers;
+----------+
| max(age) |
+----------+
| 94 |
+----------+
1 row in set (0.00 sec)
13.查看表中大于平均年龄的同学的姓名，年龄
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 |
+--------------+-----+
5 rows in set (0.00 sec)
14.查询女同学所属的班级ID
mysql> SELECT DISTINCT classID FROM students WHERE Gender='F';
+---------+
| classID |
+---------+
| 3 |
| 7 |
| 6 |
| 1 |
+---------+
4 rows in set (0.00 sec)
15.查询哪些男同学班级ID和上面的女同学的班级ID相同
mysql> SELECT * FROM students WHERE ClassID IN (SELECT DISTINCT classID FROM students WHERE Gender='F') AND Gender='M';
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
+-------+---------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
16.联合表查询
查询两张表的姓名，年龄并联合显式出来
mysql> SELECT name,age FROM students UNION SELECT name,age FROM teachers;
+---------------+-----+
| 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 |
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
29 rows in set (0.00 sec)
17.查询TeacherID=stuID的表信息，由此可以查询某位同学作为学生的ID以及作为老师的ID
SELECT * FROM students AS s,students AS t WHERE s.TeacherID=t.stuID;
知识点；自连接查询,即将同一张表连接起来
mysql> 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)
18.左外连接
查询students表中ClassID等于classes表中的ClassID的表信息，其中若左侧有ClassID，右侧没有，则右侧为空(null)
mysql> SELECT * FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.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 |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
19.右外连接
查询students表中ClassID等于classes表中的ClassID的表信息，其中若右侧有ClassID，左侧有，则左侧为空(null)
SELECT * FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.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 |
| NULL | NULL | NULL | NULL | NULL | NULL | 8 | Xiaoyao Pai | 15 |
+-------+---------------+------+--------+---------+-----------+---------+----------------+----------+
本文转自 PowerMichael 51CTO博客，原文链接：http://blog.51cto.com/huwho/1944593，如需转载请自行联系原作者
