MySQL单表查询实例详解

2023-07-17 09:47:03 185

1、准备数据

以下操作将在该表中进行

createtablestudent(
idintunsignedprimarykeyauto_increment,
namechar(12)notnull,
genderenum("male","famale")default"male",
agetinyintunsignednotnull,
hoc_groupchar(12)notnull,
htmltinyintunsignednotnull,
csstinyintunsignednotnull,
jstinyintunsignednotnull,
sanctionenum("大处分","小处分","无")
);

insertintostudent(name,gender,age,hoc_group,html,css,js,sanction)values
("Yunya","male",18,"first",88,93,76,"无"),
("Jack","male",17,"second",92,81,88,"无"),
("Bella","famale",17,"first",72,68,91,"小处分"),
("Dairis","famale",18,"third",89,54,43,"大处分"),
("Kyle","famale",19,"fifth",31,24,60,"大处分"),
("Alice","famale",16,"second",49,23,58,"无"),
("Ken","male",16,"third",33,62,17,"大处分"),
("Jason","male",21,"fourth",91,92,90,"无"),
("Tom","male",20,"fifth",88,72,91,"无"),
("Fiona","famale",19,"fourth",60,71,45,"无");

2、查询语法

SELECTDISTINCT(字段名1,字段名2...)FROM表名
WHERE条件
GROUPBY字段名
HAVING筛选
ORDERBY字段名asc/desc
LIMIT限制条数;

3、执行顺序

虽然查询的书写语法是上面那样的，但是其内部执行顺序却有些不太一样。

1.通过from找到将要查询的表

2.where规定查询条件，在表记录中逐行进行查询并筛选出符合规则的记录

3.将查到的记录进行字段分组groupby，如果没有进行分组，则默认为一组

4.将分组得到的结果进行having筛选，可使用聚和函数（where时不可使用聚合函数）

5.执行select准备打印

6.执行distinct对打印结果进行去重

7.执行orderyby对结果进行排序

8.执行limit对打印结果的条数进行限制

4、select

select主要复负责打印相关的工作

4.1全部查询

使用select*from表名可拿到该表下全部的数据

以下示例将展示使用全部查询拿到student表中所有记录

select*fromstudent;

+----+--------+--------+-----+-----------+------+-----+----+-----------+
|id|name|gender|age|hoc_group|html|css|js|sanction|
+----+--------+--------+-----+-----------+------+-----+----+-----------+
|1|Yunya|male|18|first|88|93|76|无|
|2|Jack|male|17|second|92|81|88|无|
|3|Bella|famale|17|first|72|68|91|小处分|
|4|Dairis|famale|18|third|89|54|43|大处分|
|5|Kyle|famale|19|fifth|31|24|60|大处分|
|6|Alice|famale|16|second|49|23|58|无|
|7|Ken|male|16|third|33|62|17|大处分|
|8|Jason|male|21|fourth|91|92|90|无|
|9|Tom|male|20|fifth|88|72|91|无|
|10|Fiona|famale|19|fourth|60|71|45|无|
+----+--------+--------+-----+-----------+------+-----+----+-----------+

4.2字段查询

使用select字段名1，字段名2from表名可拿到特定字段下相应的数据

以下示例将展示使用字段查询拿到每个学生的HTML\CSS\JS成绩

selectname,html,css,jsfromstudent;

+--------+------+-----+----+
|name|html|css|js|
+--------+------+-----+----+
|Yunya|88|93|76|
|Jack|92|81|88|
|Bella|72|68|91|
|Dairis|89|54|43|
|Kyle|31|24|60|
|Alice|49|23|58|
|Ken|33|62|17|
|Jason|91|92|90|
|Tom|88|72|91|
|Fiona|60|71|45|
+--------+------+-----+----+

4.3as别名

使用select字段名1as别名1,字段名2as别名2from表名可将查询到的记录字段修改一个别名

以下示例将展示修改name字段为姓名，修改gender字段为性别，修改age字段为年龄的操作

selectnameas"姓名",genderas"性别",ageas"年龄"fromstudent;

+--------+--------+--------+
|姓名|性别|年龄|
+--------+--------+--------+
|Yunya|male|18|
|Jack|male|17|
|Bella|famale|17|
|Dairis|famale|18|
|Kyle|famale|19|
|Alice|famale|16|
|Ken|male|16|
|Jason|male|21|
|Tom|male|20|
|Fiona|famale|19|
+--------+--------+--------+

4.4distinct

使用selectdistinct(字段名1,字段名2)from表名可将查询到的记录做一个取消重复的操作

以下示例将展示使用去重功能来看有多少个小组

selectdistinct(hoc_group)fromstudent;

+-----------+
|hoc_group|
+-----------+
|first|
|second|
|third|
|fifth|
|fourth|
+-----------+

4.5四则运算

查询结果可进行四则运算，以下示例将展示拿到每个同学三科总分的操作

selectname,html+css+jsas总成绩fromstudent;
+--------+-----------+
|name|总成绩|
+--------+-----------+
|Yunya|257|
|Jack|261|
|Bella|231|
|Dairis|186|
|Kyle|115|
|Alice|130|
|Ken|112|
|Jason|273|
|Tom|251|
|Fiona|176|
+--------+-----------+

4.6显示格式

使用concat()可将查询结果与任意字符串进行拼接

使用concat_ws()可指定连接符进行拼接，第一个参数是连接符

selectconcat("姓名->",name,"","性别->",gender)fromstudent;#合并成了一个字符串，注意用的空格分隔开的，不然会黏在一起

+--------------------------------------------------+
|concat("姓名->",name,"","性别->",gender)|
+--------------------------------------------------+
|姓名->Yunya性别->male|
|姓名->Jack性别->male|
|姓名->Bella性别->famale|
|姓名->Dairis性别->famale|
|姓名->Kyle性别->famale|
|姓名->Alice性别->famale|
|姓名->Ken性别->male|
|姓名->Jason性别->male|
|姓名->Tom性别->male|
|姓名->Fiona性别->famale|
+--------------------------------------------------+

selectconcat_ws("|||",name,gender,age)fromstudent;#使用|||为每个字段进行分割

+----------------------------------+
|concat_ws("|||",name,gender,age)|
+----------------------------------+
|Yunya|||male|||18|
|Jack|||male|||17|
|Bella|||famale|||17|
|Dairis|||famale|||18|
|Kyle|||famale|||19|
|Alice|||famale|||16|
|Ken|||male|||16|
|Jason|||male|||21|
|Tom|||male|||20|
|Fiona|||famale|||19|
+----------------------------------+

5、where

where条件是查询的第一道坎，能有效过滤出我们想要的任意数据

5.1、比较运算

使用比较运算符><>=<=!=进行查询

以下示例将展示使用where过滤出js成绩大于80分的同学

selectname,jsfromstudentwherejs>80;

+-------+----+
|name|js|
+-------+----+
|Jack|88|
|Bella|91|
|Jason|90|
|Tom|91|
+-------+----+

5.2、逻辑运算

使用andornot可进行逻辑运算与多条件查询

以下示例将展示使用where多条件查询过滤出各科成绩都大于80分的同学

selectname,html,css,jsfromstudentwherehtml>80andcss>80andjs>80;

+-------+------+-----+----+
|name|html|css|js|
+-------+------+-----+----+
|Jack|92|81|88|
|Jason|91|92|90|
+-------+------+-----+----+

5.3、成员运算

in可以在特定的值中进行获取，如in(80,90,100)则代表只取80或者90或者100的这几条记录。

以下示例将展示只取第一组first以及第二组second学生的个人信息

selectname,gender,age,hoc_groupfromstudentwherehoc_groupin("first","second");

+-------+--------+-----+-----------+
|name|gender|age|hoc_group|
+-------+--------+-----+-----------+
|Yunya|male|18|first|
|Jack|male|17|second|
|Bella|famale|17|first|
|Alice|famale|16|second|
+-------+--------+-----+-----------+

5.4、betweenand

betweenand也是取区间的意思，

以下示例将展示使用betweenand过滤出Js成绩大于等于60并且小于80的同学

selectname,jsfromstudentwherejsbetween60and80;

+-------+----+
|name|js|
+-------+----+
|Yunya|76|
|Kyle|60|
+-------+----+

5.5、like

like是模糊查询，其中%代表任意多个字符（类似于贪婪匹配的通配符.*），_代表任意一个字符（类似于非贪婪匹配的通配符.*?）。

以下示例将展示使用like/%匹配出姓名以k开头的所有同学的名字

selectnamefromstudentwherenamelike"k%";

+------+
|name|
+------+
|Kyle|
|Ken|
+------+

以下示例将展示使用like/_匹配出姓名以k开头并整体长度为3的同学的名字

selectnamefromstudentwherenamelike"k__";

+------+
|name|
+------+
|Ken|
+------+

5.6、正则匹配

使用RegExp可进行正则匹配，以下示例将展示使用正则匹配出名字中带有k的所有同学姓名

selectnamefromstudentwherenameREGEXP"k+";

+------+
|name|
+------+
|Jack|
|Kyle|
|Ken|
+------+

6、groupby

分组行为发生在where条件之后，我们可以将查询到的记录按照某个相同字段进行归类，一般分组都会配合聚合函数进行使用。

需要注意的是select语句是排在groupby条件之后的，因此聚合函数也能在select语句中使用。

6.1、基本使用

以下示例将展示对hoc_group字段进行分组。

我们按照hoc_group字段进行分组，那么select查询的字段只能是hoc_group字段，想要获取组内的其他字段相关信息，需要借助函数来完成

selecthoc_groupfromstudentgroupbyhoc_group;

+-----------+
|hoc_group|
+-----------+
|fifth|
|first|
|fourth|
|second|
|third|
+-----------+

如果不使用分组，则会产生重复的信息

mysql>selecthoc_groupfromstudent;
+-----------+
|hoc_group|
+-----------+
|first|
|second|
|first|
|third|
|fifth|
|second|
|third|
|fourth|
|fifth|
|fourth|
+-----------+

6.2、group_concat

用什么字段名进行分组，在select查询时就只能查那个用于分组的字段，查询别的字段会抛出异常，会提示sql_mode异常。

我们将Js成绩大于80分的同学筛选出来并且按照gender字段进行分组，此外我们还想查看其所有满足条件同学的名字。

以下这样操作会抛出异常。

mysql>selectgender,namefromstudentwherejs>80groupbygender;

ERROR1055(42000):Expression#1ofSELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn'school.student.name'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by

必须借助group_concat()函数来进行操作才能使我们的需求圆满完成。

selectgender,group_concat(name)fromstudentwherejs>80groupbygender;

+--------+--------------------+
|gender|group_concat(name)|
+--------+--------------------+
|male|Jack,Jason,Tom|
|famale|Bella|
+--------+--------------------+

6.3、分组模式

ONLY_FULL_GROUP_BY要求select中的字段是在与groupby中使用的字段

如果groupby是主键或uniquenotnull时可以在select中列出其他字段

#查看MySQL5.7默认的sql_mode如下：
mysql>select@@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql>setglobalsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

6.4、聚合函数

聚合函数可以在where执行后的所有语句中使用，比如having，select等。

聚合函数一般是同分组进行配套使用，以下是常用的聚合函数。

函数名	作用
COUNT()	对组内成员某一字段求个数
MAX()	对组内成员某一字段求最大值
MIN()	对组内成员某一字段求最小值
AVG()	对组内成员某一字段求平均值
SUM()	对组内成员某一字段求和
注意：不使用分组，则默认为一组

以下示例将展示求每组的成绩总和

selecthoc_group,sum(js+html+css)fromstudentgroupbyhoc_group;

+-----------+-------------------+
|hoc_group|sum(js+html+css)|
+-----------+-------------------+
|fifth|366|
|first|488|
|fourth|449|
|second|391|
|third|298|
+-----------+-------------------+

以下示例将展示整个班级的平均成绩及总成绩（round()用于四舍五入操作）

selectround(avg(html+js+css))as平均分,sum(html+js+css)as总分fromstudent;

+-----------+--------+
|平均分|总分|
+-----------+--------+
|199|1992|
+-----------+--------+

以下示例将展示打印出总科成绩最高分数

selectmax(js+css+html)fromstudent;

+------------------+
|max(js+css+html)|
+------------------+
|273|
+------------------+

以下示例将展示查看本班有多少男生，多少女生

selectgender,count(id)fromstudentgroupbygender;

+--------+-----------+
|gender|count(id)|
+--------+-----------+
|male|5|
|famale|5|
+--------+-----------+

7、having

having也可用于过滤操作

7.1、区别差异

执行优先级从高到低：where>groupby>having

where发生在分组groupby之前，因而where中可以有任意字段，但是绝对不能使用聚合函数。

having发生在分组groupby之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数

7.2、示例演示

以下示例将展示使用having过滤取出每组总分数大于400的小组

selecthoc_group,sum(html+css+js)fromstudentgroupbyhoc_grouphavingsum(html+css+js)>400;

+-----------+------------------+
|hoc_group|sum(html+css+js)|
+-----------+------------------+
|first|488|
|fourth|449|
+-----------+------------------+

以下示例将展示使用having过滤取出有处分的同学。（可以使用分组的字段，但不能使用其他字段）

selectsanction,group_concat(name)fromstudentgroupbysanctionhavingsanction!="无";

+-----------+--------------------+
|sanction|group_concat(name)|
+-----------+--------------------+
|大处分|Dairis,Kyle,Ken|
|小处分|Bella|
+-----------+--------------------+

8、orderyby

orderyby用于对查询结果进行排序

默认的排序是按照主键进行排序的

8.1 asc

asc用于升序排列，以下示例将展示按照每位同学的年龄进行升序排列，如果年龄相同则依照总成绩进行升序排列。

selectid,name,age,html+css+jsas总成绩fromstudentorderbyage,html+css+jsasc;

+----+--------+-----+-----------+
|id|name|age|总成绩|
+----+--------+-----+-----------+
|7|Ken|16|112|
|6|Alice|16|130|
|3|Bella|17|231|
|2|Jack|17|261|
|4|Dairis|18|186|
|1|Yunya|18|257|
|5|Kyle|19|115|
|10|Fiona|19|176|
|9|Tom|20|251|
|8|Jason|21|273|
+----+--------+-----+-----------+

8.2、desc

desc用于降序排列，以下示例将展示按照每位同学的年龄进行降序排列。

selectid,name,age,html+css+jsas总成绩fromstudentorderbyagedesc;

+----+--------+-----+-----------+
|id|name|age|总成绩|
+----+--------+-----+-----------+
|8|Jason|21|273|
|9|Tom|20|251|
|5|Kyle|19|115|
|10|Fiona|19|176|
|1|Yunya|18|257|
|4|Dairis|18|186|
|2|Jack|17|261|
|3|Bella|17|231|
|6|Alice|16|130|
|7|Ken|16|112|
+----+--------+-----+-----------+

9、limit

limit用于控制显示的条数

9.1、示例演示

按照总成绩进行降序排序，只打印1-5名。

selectid,name,age,html+css+jsas总成绩fromstudentorderbyhtml+css+jsdesclimit5;

+----+-------+-----+-----------+
|id|name|age|总成绩|
+----+-------+-----+-----------+
|8|Jason|21|273|
|2|Jack|17|261|
|1|Yunya|18|257|
|9|Tom|20|251|
|3|Bella|17|231|
+----+-------+-----+-----------+

按照总成绩进行降序排序，只打印6-8名。

selectid,name,age,html+css+jsas总成绩fromstudentorderbyhtml+css+jsdesclimit5,3;#从第五名开始，打印三条。6，7，8

+----+--------+-----+-----------+
|id|name|age|总成绩|
+----+--------+-----+-----------+
|4|Dairis|18|186|
|10|Fiona|19|176|
|6|Alice|16|130|
+----+--------+-----+-----------+

总结

到此这篇关于MySQL单表查询的文章就介绍到这了,更多相关MySQL单表查询内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL单表查询实例详解

热门推荐

随机推荐