MySQL的SELECT查询用法
发表于：2025-01-19 作者：千家信息网编辑
千家信息网最后更新 2025年01月19日，本文主要给大家介绍MySQL的SELECT查询用法，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下MySQL的SELECT查询用法吧。注意：练习
千家信息网最后更新 2025年01月19日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 | +-------+---------------+------+--------+---------+-----------+---------+----------------+----------+看完以上关于MySQL的SELECT查询用法，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。
查询
信息
年龄
同学
查询表
班级
相同
右侧
女同
姓名
性别
数据
数据库
知识
文件
知识点
笔者
行业
面的
联合
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
游戏网络安全需求分析
跨数据库查询语句
对象关系数据库模型
问道手游如何看服务器
江苏游戏软件开发定做价格
数据库 文本模糊查询
网络安全报绘画
服务器限制上传文件大小
网络安全路由交换技术
数据库的技术概念
爱如生四库系列数据库
网络安全边界防护不明确
嘀嗒软件开发票
云服务器运用什么技术
跟随网络技术
网络安全中的web和二进制
软件开发相关专业介绍
数据库选择第二高
安丘网络安全局
业务系统数据库
乌克兰sbu数据库
网络安全和雷达有关系吗
网络技术开发需要什么设备
小米平板2做服务器
数据库创建成功怎么查询
熊猫病毒的网络技术安全问题
无线网网络安全认证
上海市第一届网络安全小主播
软件开发技术研究生
业务系统数据库
