MySQL的SELECT查找用法
发布时间：2023-10-26 15:57:08 所属栏目：MySql教程 来源：
导读：本文主要给大家介绍MySQL的SELECT查询用法，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下MySQL的SELECT查询用法吧。
注意：
练习前请先下载附
注意：
练习前请先下载附
|
本文主要给大家介绍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 | +-------+---------------+------+--------+---------+-----------+---------+----------------+----------+ （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
