//查出所有宠物的出生月份;
selectname,birth,MONTH(birth)as出生月份frompets;
//查出5月份出生的宠物;
selectname,birth,MONTH(birth)as出生月份frompetswhereMONTH(birth)=5;
==模糊查询
//通配符:
%----匹配零个或更多个字符。
select*frompetswherenamelike'b%';//-业务意义:
查出所有名字以‘b’字符开头的宠物。
//比如:
输入要查找的书名:
java---要找书名含java的书;
select*frombookwherenamelike'%java%';
==【regularexpression正则表达式】;SQL支持直接使用正则表达式;
在SQL中的关键字:
REGEXP
/正则表达式,查找name开始字符为'b'的宠物;
select*frompetswherenameREGEXP'^b';
//查找name开始字符为'bu'的宠物;
select*frompetswherenameREGEXP'^bu';
//查找name结束字符为'r'的宠物;
select*frompetswherenameREGEXP'r$';
//使用正则表达式的特殊标记符‘^’,该标记符匹配字符串开始部位,该功能符号,必须写在表达式的前面;
//'$'---该标记符匹配字符串结束部位,该功能符号,必须写在表达式的结尾处;
select*frompetswherenameREGEXP'w';//查出名字中包含w字符的宠物;
select*frompetswherenamelike'%w%';//模糊查询;
select*frompetswherenameREGEXP'^.....$';//查出名字只有5个字符的宠物记录;
//.---匹配一个单字符;5个.---匹配5个单字符;
select*frompetswherenameREGEXP'.....';//含5个:
'.....'//大于等于5个以上字符的都匹配;
//.{5}---.重复5次;^.{5}$---在开始之后是连续5个随意单字符,然后是字符串结束;
//项目意义:
《网上书店》按输入条件,查书,
select*frombookwherenamelike'%java%';//比如:
输入要查找的书名:
java---要找书名含java的书;
select*fromstudentwherenamelike'%t%'ornamelike'_a%'orderbyid;//查询所有student中,名字里有't'字符的学生,或名字中第二个字符为“a”的学生,结果按id字段值升序显示。
wherenameREGEXP't'ornameREGEXP'^.a''_';//匹配某位置的单个任意字符。
模式中其位置在哪里,该位置匹配任意一个目标字符。
SELECT*FROMpetWHEREnameLIKE'_____';//找出name值为5个字符长度的宠物记录;//5条下划线;
#####
总结:
'%'-----任意长度字符串;>=0;
'_'-----任意单个字符;=1;
==区间查找:
类型I):
范围查找
select*fromstudentwhereid>=andid<=4;//查询id为[2,4]区间的学生
类型II):
枚举查找
select*frompetswherename='tom'orname='cat';//查询名字为“tom”或“cat”的学生。
select*frompetswherespeciesIN('cat','dog');//查找种类中是猫或者是狗的宠物;//IN()在多个值中枚举一个;
//所有记录的species字段值,是否符合'cat'、'dog'的条件,符合的挑出;
==别名:
//可为表、字段指定[别名]。
解释:
在该句内,暂时表示表名、字段名的临时性的名字;//注意:
别名只在该句内临时起作用;
//。
。
要注意使用顺序,按照fromwhereselect的执行顺序,先定义的后面可以用;后面定义的前面不能用;
selects.idasstu_id,s.nameasstu_namefromstudentasswheres.id>=3;.
||||
字段名的别名表名的别名
//显示时,以别名为临时表字段名。
//查询ID号>=3的宠物;
要求:
英文字段名---中文字段名;
selectp.idAS'宠物ID号',p.nameAS'宠物名'frompet1ASpwherep.id>=3;
---------如此别名易于读懂业务意义;----如此别名简化表名书写;
==计算记录行数;count();
业务意义:
BI(BusinessIntelligence)、OA等大量商务软件,都需要对DB中大量业务数据进行统计、分析;
---商业软件的意义所在。
函数COUNT(某个字段名)---统计临时结果表中,在该名字段登记有值的记录个数;
*---------任意字段;自动挑一个字段值最多的;//比如,主键字段,肯定有值;
//∴COUNT(*)-----近似为(记录)总数;
//注意:
null字段值不被count()统计。
selectcount(*)as'宠物总数'frompets;//统计宠物总数;
select*frompets;
selectcount('name')frompets;//统计name字段//业务意义:
有名字的宠物的数量;
selectcount('sex')frompets;//统计sex字段
selectcount(*)frompetswherespecies='dog';//----业务意义:
统计狗的数量;
selectcount(*)frompetswherespecies='dog'orspecies='snake'orspecies='cat';
selectcount('owner')ownerfrompets;//宠物主人字段有几条记录?
selectcount('death')frompets;//宠物death字段有几条记录?
//业务意义:
统计宠物主人有几位?
//重复的主人登记,会重复计算;还需要分组合并重复;
==记录分组(归类):
I)给某个字段分组:
select字段名frompetgroupby字段名;
selectspeciesfrompetsgroupbyspecies;//技术意义:
对species字段进行分组查询//业务意义;列出宠物的种类
II)统计每组内的数量:
COUNT(字段名)+GroupBy
selectspecies,count(species)frompetsgroupbyspecies;//技术意义:
对species字段进行分组数量统计;//业务意义:
每种宠物的数量;
//?
?
如果想列出每种宠物的数量,同时想列出宠物的种类名称;
selectspecies,count(species)frompetswherespeciesIN('cat','dog')groupbyspecies;//业务意义:
统计出猫和狗的各自数量;
selectspecies,COUNT(*)fromordersGROUPBYspecies;//每种家电产品卖了多少件;
selectspecies,COUNT(*)fromsalesGROUPBYspecies;
selectspecies,COUNT(*)fromordersGROUPBYspecies;//每种家电产品卖了多少件;
selectspecies,COUNT(*)fromsalesGROUPBYspecies;
selectsex,COUNT(*)frompetsgroupbysex;//---业务意义:
宠物的性别统计情况;
SELECTowner,COUNT(*)FROMpetsGROUPBYowner;//业务意义:
每个主人各自拥有的宠物数量
selectowner,count(*)as'宠物个数'frompetsgroupbyowner;//同上;
SELECTDISTINCTownerFROMpets;//业务意义:
列出所有养宠物的人;
selectCOUNT(distinctowner)frompets;//列出养宠物的人的个数
其余业务意义:
<学生性别分类统计><人口性别分类统计>
III)统计组数量:
COUNT()+Distinct实现//selectCOUNT(distinct字段名)frompet;
==Distinct//纯字段值种类---用Distinct。
SELECTDISTINCTownerFROMpet;
//Distinct与GroupBy的相同:
针对同一个字段归组时,二者相同;
//Distinct与GroupBy的不同:
组合使用count()、min()、max()等时,distinct的使用与Groupby的使用方式不同;
具体:
组间统计---COUNT(distinct字段名);
组内统计---Count(*)from...groupby字段名;
selectCOUNT(distinctspecies)frompets;//几种宠物
selectspecies,count(species)as'宠物数量'frompetsgroupbyspecies;//--宠物共有几种;
==最值问题。
Max()、Min()orderby+limit1
==求某个字段中的最值:
selectmax(birth)as'最晚的生日'frompets;//最晚的生日
selectmax(month(birth))as'最晚的生日'frompets;//生日中月份最晚的
==Count()、Max()、Min()等,与其它字段组合使用时,两个字段以上必须和Groupby合用。
selectNAME,max(birth)as'最晚的生日'frompets;-----会报错;
//解决方案1:
----ORDERBY+LIMIT技术
selectNAME,birthas'最晚的生日'frompetsorderbybirthdesclimit1;//---limitn;从结果集中,取出最顶部的n个记录;
//解决方案2:
----子查询技术。
selectname,birthfrompetwherebirth=(selectMax(birth)as最晚生日frompet);
selectNAME,max(birth)as'最晚的生日'frompetsgroupbybirth;//列出生日最晚的宠物,及其名字;
selectmax(b.petcount)AS'单人拥有最多宠物数量'from(selectowner,count(*)as'petcount'frompetsgroupbyowner)asb;//单人拥有的最多的宠物数量:
selectowner,count(*)as'petcount'frompetgroupbyownerorderbypetcountdesclimit1;//哪个主人拥有的宠物最多?
---orderby可以对表or临时表的字段进行排序;
==联合字段分组+统计。
特点:
分级别的分组依次统计;
selectspecies,sex,COUNT(*)as'数量'frompetsgroupbyspecies,sex;//---业务意义:
每个种类的每种性别的宠物数量;
selectspecies,sex,COUNT(*)as'数量'frompetswheresexisnotnullgroupbyspecies,sex;//每个种类的每种性别的宠物数量;(所有知道性别的)
selectspecies,sex,COUNT(*)as'数量'frompetswherespeciesIN('cat','dog')groupbyspecies,sex;//猫或狗的每种性别的数量
技术:
1)多字段GROUPBY---实现联合分组
2)再统计最小组内的数量;
COUNT(*)+GROUPBY字段1,字段2,...;
注意:
大小组的级别顺序;
GROUPBYspecies,sex;//先按species,再按sex,联合分组;
GROUPBYsex,species;//先按sex,再按species,联合分组;
练习:
//《网上书店》项目中的count()
分析:
哪张表-----哪些过滤条件-----是否分组-----是否排序-----哪些字段----是否统计。
---某个用户下的订单数count(*),Order表,where用户号条件;
---某个用户购买的每种商品的订单数count(*),Order表,where用户号条件,groupby商品名;
---某个用户购买的每种商品的全年每个月的订单数。
---某种货物卖出的件数;count(*),Order表,where货号条件+已交费条件;
---本月下订单的总用户数;
count(用户字段),Order表,where时间条件;
//wheremonth(订单日期字段)=month(curdate())
--如果一个用户多次只算作一个用户,则需groupby用户名字段;
---本月每种被订购商品的总用户数;
按商品种类字段分组统计;
count(用户字段),Order表,where时间字段月份值;
--每个月每种被订购商品的用户数;
---每个用户购买的每种商品的全年每个月的订单数;//用户名、商品名、订单日期月份三字段的联合分组;
---本月销售的总商品数;//sum(货物数量),Order表,where时间条件;
---本月销售的每种商品的数量;
---全年销售的每种商品的数量;
当年---whereYear(订单日期)=Year(curdate())
每种---groupby商品名字段
数量---sum(货物数量)//加法和;
--另一张表:
《accounts》
selectaccountName,accountCreatedAt,accountBalancefromaccounts;
selectaccountCreatedAtfromaccountsgroupbyaccountCreatedAt;//列出好、各个账户的地点名称;
selectaccountCreatedAt,count(*)fromaccountsgroupbyaccountCreatedAt;//每个地点的账户总数;
==[平均值问题]
==“平均收入”、“平均分数”
selectAVG(accountBalance)as`平均余额`fromaccounts;//所有(记录的)帐户余额平均值
==分组平均值:
技术:
GROUPBY+AVG();
selectaccountCreatedAt,AVG(accountBalance)as`平均余额`fromaccountsgroupbyaccountCreatedAt;//“每个地点(地域组)的帐户的平均余额”
==分组最大值:
技术:
GROUPBY+max();
selectaccountCreatedAt,max(accountBalance)fromaccountsgroupbyaccountCreatedAt;//每个地点的,账户余额最大值;
==分组总和:
技术:
GROUPBY+SUM();
selectaccountCreatedAt,sum(accountBalance)as`总和`fromaccountsgroupbyaccountCreatedAt;//每个地点的,账户总和
【总结】一组一统计分组+函数
==[having子句]常和GROUPBY子句,联合;
对select操作之后的结果集临时表中记录进行条件筛选。
//where是对原表记录,作过滤;
//“ny这个地方的帐户的平均余额”
算法I):
①先按地点分组,统计出每组的余额,得临时表;
②再用Having对临时结果表过滤,过滤出CA这一条临时结果记录;
selectaccountCreatedAt,AVG(accountBalance)as`平均余额`fromaccountsgroupbyaccountCreatedAtHAVINGaccountCreatedAt='ny';
算法II):
where原表过滤条件+avg()