溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家介紹MySQL的SELECT查詢用法，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL的SELECT查詢用法吧。
注意：
練習前請先下載附件里的文件，并且將文件導入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 |
+-------+---------------+------+--------+---------+-----------+---------+----------------+----------+看完以上關于MySQL的SELECT查詢用法，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
