单表查询

<article data-content="[{&quot;type&quot;:&quot;block&quot;,&quot;id&quot;:&quot;JzzI-1659751783188&quot;,&quot;name&quot;:&quot;paragraph&quot;,&quot;data&quot;:{},&quot;nodes&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;id&quot;:&quot;2sGZ-1659751783187&quot;,&quot;leaves&quot;:[{&quot;text&quot;:&quot;select * from user； ==》查询所有数据&quot;,&quot;marks&quot;:[{&quot;type&quot;:&quot;fontSize&quot;,&quot;value&quot;:18},{&quot;type&quot;:&quot;fontFamily&quot;,&quot;value&quot;:&quot;Arial Black&quot;},{&quot;type&quot;:&quot;color&quot;,&quot;value&quot;:&quot;#CC0000&quot;}]}]}],&quot;state&quot;:{}},{&quot;type&quot;:&quot;block&quot;,&quot;id&quot;:&quot;5Z6C-1659751857261&quot;,&quot;name&quot;:&quot;paragraph&quot;,&quot;data&quot;:{},&quot;nodes&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;id&quot;:&quot;1Pbw-1659751857254&quot;,&quot;leaves&quot;:[{&quot;text&quot;:&quot; select  name ，class  from user； ==》取多个字段值&quot;,&quot;marks&quot;:[{&quot;type&quot;:&quot;fontSize&quot;,&quot;value&quot;:18},{&quot;type&quot;:&quot;fontFamily&quot;,&quot;value&quot;:&quot;Arial Black&quot;}]}]}],&quot;state&quot;:{}},{&quot;type&quot;:&quot;block&quot;,&quot;id&quot;:&quot;Wd6M-1659752041514&quot;,&quot;name&quot;:&quot;paragraph&quot;,&quot;data&quot;:{},&quot;nodes&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;id&quot;:&quot;FlOa-1659752041512&quot;,&quot;leaves&quot;:[{&quot;text&quot;:&quot;select * from user  where  name=‘dayin’；  ==》查询name等于dayin的所有数据&quot;,&quot;marks&quot;:[{&quot;type&quot;:&quot;fontSize&quot;,&quot;value&quot;:18},{&quot;type&quot;:&quot;fontFamily&quot;,&quot;value&quot;:&quot;Arial Black&quot;}]}]}],&quot;state&quot;:{}},{&quot;type&quot;:&quot;block&quot;,&quot;id&quot;:&quot;ZRSx-1659752151864&quot;,&quot;name&quot;:&quot;paragraph&quot;,&quot;data&quot;:{},&quot;nodes&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;id&quot;:&quot;B93A-1659752151862&quot;,&quot;leaves&quot;:[{&quot;text&quot;:&quot;select * from user  where name  ！=‘dayin’；  ==》查询name不等于dayin的所有数据&quot;,&quot;marks&quot;:[{&quot;type&quot;:&quot;fontSize&quot;,&quot;value&quot;:18},{&quot;type&quot;:&quot;fontFamily&quot;,&quot;value&quot;:&quot;Arial Black&quot;}]}]}],&quot;state&quot;:{}},{&quot;type&quot;:&quot;block&quot;,&quot;id&quot;:&quot;nf2o-1659752231187&quot;,&quot;name&quot;:&quot;paragraph&quot;,&quot;data&quot;:{},&quot;nodes&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;id&quot;:&quot;1yQY-1659752231186&quot;,&quot;leaves&quot;:[{&quot;text&quot;:&quot;select * from user  where  name " dayin';="=">查询name不等于dayin的所有数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"TaHF-1659752312784","name":"paragraph","data":{},"nodes":[{"type":"text","id":"ImmK-1659752312782","leaves":[{"text":"select * from user  where class=1001  and  phone=13311111111；","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"hdIE-1659752389092","name":"paragraph","data":{},"nodes":[{"type":"text","id":"9vr8-1659752389090","leaves":[{"text":"==》查询class为1001且phone为13311111111的所有数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"F3I5-1659752450351","name":"paragraph","data":{},"nodes":[{"type":"text","id":"ziX2-1659752450349","leaves":[{"text":"select * from user where class=1001 or phone=13311111111；","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"N8fz-1659752515034","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Gbnl-1659752515031","leaves":[{"text":"查询class为1001或者phone为13311111111的所有数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"kGTf-1659752573520","name":"paragraph","data":{},"nodes":[{"type":"text","id":"PnN7-1659752573516","leaves":[{"text":"select * from user  where id>4;  ==>查询大于等于4的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"zyzL-1659752676643","name":"paragraph","data":{},"nodes":[{"type":"text","id":"eRWR-1659752676641","leaves":[{"text":"select * from user  where  id>=3  and  id<=6;   ==>查询id大于等于3并且小于等于6的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"X6Fw-1659752780312","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Uapp-1659752780310","leaves":[{"text":"select * from user  where  id  between  3 and  6；  ==》查询id大于等于3并且小于等于6的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"fBj3-1659778787683","name":"paragraph","data":{},"nodes":[{"type":"text","id":"5ejs-1659778787682","leaves":[{"text":"select * from user where class in(1001,1002);  ==>查询class为1001或","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#262626"}]}]}],"state":{}},{"type":"block","id":"ePR7-1659778875259","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Yozx-1659778875257","leaves":[{"text":"1002的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#262626"}]}]}],"state":{}},{"type":"block","id":"WHlW-1659778885464","name":"paragraph","data":{},"nodes":[{"type":"text","id":"MaOn-1659778885463","leaves":[{"text":"select * from user where class=1001 or class=1002；  ==》查询class为1001或1002的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"v7st-1659778974153","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Bt3q-1659778974151","leaves":[{"text":"select * from user where id not in（1，2）；  ==》查询id不为1或2的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"MyMR-1659779050500","name":"paragraph","data":{},"nodes":[{"type":"text","id":"wVOU-1659779050498","leaves":[{"text":"select * from user where class is null；  ==》查询class为空的数据，不能直接用字段=null","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"x4is-1659779141697","name":"paragraph","data":{},"nodes":[{"type":"text","id":"7Ig5-1659779141695","leaves":[{"text":"select * from user where class is not null；  ==》查询class不为空的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"z42Q-1659779231921","name":"paragraph","data":{},"nodes":[{"type":"text","id":"cMs1-1659779231919","leaves":[{"text":"select * from user where name like ‘%li%'  ==>查询name字段所有包含li的数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"LoNl-1659779332365","name":"paragraph","data":{},"nodes":[{"type":"text","id":"brOw-1659779332363","leaves":[{"text":"select * from user where name like '%li',  ==>查询name字段以li结尾的所有数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"0GDa-1659783829343","name":"paragraph","data":{},"nodes":[{"type":"text","id":"zn6k-1659783829341","leaves":[{"text":"select * from user where name like ‘da%',  ==>查询name字段以da开头的是所有数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"kt6y-1659783965765","name":"paragraph","data":{},"nodes":[{"type":"text","id":"2Fem-1659783965763","leaves":[{"text":"select * from user id limit 1，4；  ==》查询2到5行数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"k50N-1659784045011","name":"paragraph","data":{},"nodes":[{"type":"text","id":"WgGp-1659784045009","leaves":[{"text":"select * from user id limit 0，5；  ==》查询前5行数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"7PLb-1659784119802","name":"paragraph","data":{},"nodes":[{"type":"text","id":"RD7r-1659784119800","leaves":[{"text":"limit m，n（m为下标值n为查询行数） 下标值从0开始","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"Eso1-1659784186595","name":"paragraph","data":{},"nodes":[{"type":"text","id":"yhsk-1659784186593","leaves":[{"text":"排序  ","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"5YkK-1659784208585","name":"paragraph","data":{},"nodes":[{"type":"text","id":"xzo9-1659784208584","leaves":[{"text":"从小到大---升序asc","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"wFMG-1659784235936","name":"paragraph","data":{},"nodes":[{"type":"text","id":"56hc-1659784235934","leaves":[{"text":"select * from +表名 order by 字段 asc","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"vhLr-1659784293110","name":"paragraph","data":{},"nodes":[{"type":"text","id":"HZ70-1659784293108","leaves":[{"text":"select * from user order by phone asc;","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"QFuR-1659784342310","name":"paragraph","data":{},"nodes":[{"type":"text","id":"etLO-1659784342308","leaves":[{"text":"从大到小---降序desc","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"82dR-1659784375296","name":"paragraph","data":{},"nodes":[{"type":"text","id":"KltZ-1659784375295","leaves":[{"text":"select * from +表名 order by 字段 desc","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"iQXZ-1659784445298","name":"paragraph","data":{},"nodes":[{"type":"text","id":"eGLx-1659784445297","leaves":[{"text":"select * from user order by phone desc；","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"Vi9c-1659784498798","name":"paragraph","data":{},"nodes":[{"type":"text","id":"KYfQ-1659784498796","leaves":[{"text":"分组","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"wsVW-1659784505739","name":"paragraph","data":{},"nodes":[{"type":"text","id":"dvFz-1659784505737","leaves":[{"text":"select * from user group by class；  ==》通过class字段进行分组","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"yYzu-1659784579702","name":"paragraph","data":{},"nodes":[{"type":"text","id":"24l0-1659784579700","leaves":[{"text":"select class，count（*） from user group by class；  ==>通过class字段进行分组然后求出每组对应的人数","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"zb4s-1659784697091","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Bfh0-1659784697089","leaves":[{"text":"select class，count（*） from user group by class having class is not null；  ==》通过class字段进行分组然后加条件class不为null","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"SaNF-1659784791013","name":"paragraph","data":{},"nodes":[{"type":"text","id":"zWio-1659784791011","leaves":[{"text":"注意：","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"1kWJ-1659784796798","name":"paragraph","data":{},"nodes":[{"type":"text","id":"jF0m-1659784796796","leaves":[{"text":"1.group by分组之后，只能使用having进行条件筛选","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"QxIW-1659784852279","name":"paragraph","data":{},"nodes":[{"type":"text","id":"J2ys-1659784852277","leaves":[{"text":"2.使用group by分组后，仅有分组字段和函数可以放到from前面","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"D4ZA-1659784928354","name":"paragraph","data":{},"nodes":[{"type":"text","id":"yLVC-1659784928352","leaves":[{"text":"聚合函数","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"ToVT-1659784937005","name":"paragraph","data":{},"nodes":[{"type":"text","id":"u5A4-1659784937003","leaves":[{"text":"count（）  ==》统计","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"nVsO-1659784953797","name":"paragraph","data":{},"nodes":[{"type":"text","id":"wNyt-1659784953795","leaves":[{"text":"sum（）  ==》求和","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"8r8U-1659784970639","name":"paragraph","data":{},"nodes":[{"type":"text","id":"JNPL-1659784970637","leaves":[{"text":"avg（）  ==》求平均值","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"cZ0y-1659784987697","name":"paragraph","data":{},"nodes":[{"type":"text","id":"jPqR-1659784987695","leaves":[{"text":"max（）  ==>求最大值","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"bwav-1659785014332","name":"paragraph","data":{},"nodes":[{"type":"text","id":"XMwg-1659785014329","leaves":[{"text":"min（）  ==》求最小值","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"hHi4-1659785030703","name":"paragraph","data":{},"nodes":[{"type":"text","id":"cWNg-1659785030701","leaves":[{"text":"distinct   ==》去重","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"xFAi-1659785056681","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Clfz-1659785056679","leaves":[{"text":"select count(*) from user;  ==>统计user表中数据条数","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"5rg7-1659789552984","name":"paragraph","data":{},"nodes":[{"type":"text","id":"Z5Pn-1659789552982","leaves":[{"text":"select sum（id） from user；  ==》 计算id字段数值之和","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"IC6r-1659785057832","name":"paragraph","data":{},"nodes":[{"type":"text","id":"kvMv-1659785057830","leaves":[{"text":"select avg(id) from user where class=1001; ==》求id的平均值\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"ZmTb-1659789618067","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"jW4G-1659789618068","leaves":[{"text":"select max(id) from user where class=1001;==》求id最大值\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"L8OR-1659789618069","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"rm7P-1659789618070","leaves":[{"text":"select min(id) from user where class=1001;==》求id最小值\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}},{"type":"block","id":"ZC37-1659789618071","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"wuOs-1659789618072","leaves":[{"text":"select distinct(phone) from user;==》对phone字段去掉重复数据","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#000000"}]}]}],"state":{}}]'>select * from user； ==》查询所有数据
select  name ，class  from user； ==》取多个字段值
select * from user  where  name=‘dayin’；  ==》查询name等于dayin的所有数据
select * from user  where name  ！=‘dayin’；  ==》查询name不等于dayin的所有数据
select * from user  where  name <>'dayin';  ==>查询name不等于dayin的所有数据
select * from user  where class=1001  and  phone=13311111111；
==》查询class为1001且phone为13311111111的所有数据
select * from user where class=1001 or phone=13311111111；
查询class为1001或者phone为13311111111的所有数据
select * from user  where id>4;  ==>查询大于等于4的数据
select * from user  where  id>=3  and  id<=6;   ==>查询id大于等于3并且小于等于6的数据
select * from user  where  id  between  3 and  6；  ==》查询id大于等于3并且小于等于6的数据
select * from user where class in(1001,1002);  ==>查询class为1001或
1002的数据
select * from user where class=1001 or class=1002；  ==》查询class为1001或1002的数据
select * from user where id not in（1，2）；  ==》查询id不为1或2的数据
select * from user where class is null；  ==》查询class为空的数据，不能直接用字段=null
select * from user where class is not null；  ==》查询class不为空的数据
select * from user where name like ‘%li%'  ==>查询name字段所有包含li的数据
select * from user where name like '%li',  ==>查询name字段以li结尾的所有数据
select * from user where name like ‘da%',  ==>查询name字段以da开头的是所有数据
select * from user id limit 1，4；  ==》查询2到5行数据
select * from user id limit 0，5；  ==》查询前5行数据
limit m，n（m为下标值n为查询行数） 下标值从0开始
排序  
从小到大---升序asc
select * from +表名 order by 字段 asc
select * from user order by phone asc;
从大到小---降序desc
select * from +表名 order by 字段 desc
select * from user order by phone desc；
分组
select * from user group by class；  ==》通过class字段进行分组
select class，count（*） from user group by class；  ==>通过class字段进行分组然后求出每组对应的人数
select class，count（*） from user group by class having class is not null；  ==》通过class字段进行分组然后加条件class不为null
注意：
1.group by分组之后，只能使用having进行条件筛选
2.使用group by分组后，仅有分组字段和函数可以放到from前面
聚合函数
count（）  ==》统计
sum（）  ==》求和
avg（）  ==》求平均值
max（）  ==>求最大值
min（）  ==》求最小值
distinct   ==》去重
select count(*) from user;  ==>统计user表中数据条数
select sum（id） from user；  ==》 计算id字段数值之和
select avg(id) from user where class=1001; ==》求id的平均值
select max(id) from user where class=1001;==》求id最大值
select min(id) from user where class=1001;==》求id最小值
select distinct(phone) from user;==》对phone字段去掉重复数据