SQL常用查询语句.docx
《SQL常用查询语句.docx》由会员分享,可在线阅读,更多相关《SQL常用查询语句.docx(15页珍藏版)》请在冰豆网上搜索。
SQL常用查询语句
SQL根本常用查询语句
--select
select*fromstudent;
--all查询所有
selectallsexfromstudent;
--distinct过滤重复
selectdistinctsexfromstudent;
--count统计
selectcount(*)fromstudent;
selectcount(sex)fromstudent;
selectcount(distinctsex)fromstudent;
--top取前N条记录
selecttop3*fromstudent;
--aliascolumnname列重命名
selectidas编号,name'名称',sex性别fromstudent;
--aliastablename表重命名
selectid,name,s.id,s.namefromstudents;
--column列运算
select(age+id)colfromstudent;
selects.name+'-'+c.namefromclassesc,studentswheres.cid=c.id;
--where条件
select*fromstudentwhereid=2;
select*fromstudentwhereid>7;
select*fromstudentwhereid<3;
select*fromstudentwhereid<>3;
select*fromstudentwhereid>=3;
select*fromstudentwhereid<=5;
select*fromstudentwhereid!
>3;
select*fromstudentwhereid!
<5;
--and并且
select*fromstudentwhereid>2andsex=1;
--or或者
select*fromstudentwhereid=2orsex=1;
--between...and...相当于并且
select*fromstudentwhereidbetween2and5;
select*fromstudentwhereidnotbetween2and5;
--like模糊查询
select*fromstudentwherenamelike'%a%';
select*fromstudentwherenamelike'%[a][o]%';
select*fromstudentwherenamenotlike'%a%';
select*fromstudentwherenamelike'ja%';
select*fromstudentwherenamenotlike'%[j,n]%';
select*fromstudentwherenamelike'%[j,n,a]%';
select*fromstudentwherenamelike'%[^ja,as,on]%';
select*fromstudentwherenamelike'%[ja_on]%';
--in子查询
select*fromstudentwhereidin(1,2);
--notin不在其中
select*fromstudentwhereidnotin(1,2);
--isnull是空
select*fromstudentwhereageisnull;
--isnotnull不为空
select*fromstudentwhereageisnotnull;
--orderby排序
select*fromstudentorderbyname;
select*fromstudentorderbynamedesc;
select*fromstudentorderbynameasc;
--groupby分组
按照年龄进展分组统计
selectcount(age),agefromstudentgroupbyage;
按照性别进展分组统计
selectcount(*),sexfromstudentgroupbysex;
按照年龄和性别组合分组统计,并排序
selectcount(*),sexfromstudentgroupbysex,ageorderbyage;
按照性别分组,并且是id大于2的记录最后按照性别排序
selectcount(*),sexfromstudentwhereid>2groupbysexorderbysex;
查询id大于2的数据,并完成运算后的结果进展分组和排序
selectcount(*),(sex*id)newfromstudentwhereid>2groupbysex*idorderbysex*id;
--groupbyall所有分组
按照年龄分组,是所有的年龄
selectcount(*),agefromstudentgroupbyallage;
--having分组过滤条件
按照年龄分组,过滤年龄为空的数据,并且统计分组的条数和现实年龄信息
selectcount(*),agefromstudentgroupbyagehavingageisnotnull;
按照年龄和cid组合分组,过滤条件是cid大于1的记录
selectcount(*),cid,sexfromstudentgroupbycid,sexhavingcid>1;
按照年龄分组,过滤条件是分组后的记录条数大于等于2
selectcount(*),agefromstudentgroupbyagehavingcount(age)>=2;
按照cid和性别组合分组,过滤条件是cid大于1,cid的最大值大于2
selectcount(*),cid,sexfromstudentgroupbycid,sexhavingcid>1andmax(cid)>2;
Ø嵌套子查询
子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。
任何允许使用表达式的地方都可以使用子查询。
子查询也称为部查询或部选择,而包含子查询的语句也成为外部查询或外部选择。
#from(select…table)示例
将一个table的查询结果当做一个新表进展查询
select*from(
selectid,namefromstudentwheresex=1
)twheret.id>2;
上面括号中的语句,就是子查询语句〔部查询〕。
在外面的是外部查询,其中外部查询可以包含以下语句:
1、包含常规选择列表组件的常规select查询
2、包含一个或多个表或视图名称的常规from语句
3、可选的where子句
4、可选的groupby子句
5、可选的having子句
#示例
查询班级信息,统计班级学生人生
select*,(selectcount(*)fromstudentwherecid=classes.id)asnum
fromclassesorderbynum;
#in,notin子句查询示例
查询班级id大于小于的这些班级的学生信息
select*fromstudentwherecidin(
selectidfromclasseswhereid>2andid<4
);
查询不是班的学生信息
select*fromstudentwherecidnotin(
selectidfromclasseswherename='2班'
)
in、notin后面的子句返回的结果必须是一列,这一列的结果将会作为查询条件对应前面的条件。
如cid对应子句的id;
#exists和notexists子句查询示例
查询存在班级id为的学生信息
select*fromstudentwhereexists(
select*fromclasseswhereid=student.cidandid=3
);
查询没有分配班级的学生信息
select*fromstudentwherenotexists(
select*fromclasseswhereid=student.cid
);
exists和notexists查询需要部查询和外部查询进展一个关联的条件,如果没有这个条件将是查询到的所有信息。
如:
id等于student.id;
#some、any、all子句查询示例
查询班级的学生年龄大于班级的学生的年龄的信息
select*fromstudentwherecid=5andage>all(
selectagefromstudentwherecid=3
);
select*fromstudentwherecid=5andage>any(
selectagefromstudentwherecid=3
);
select*fromstudentwherecid=5andage>some(
selectagefromstudentwherecid=3
);
Ø聚合查询
1、distinct去掉重复数据
selectdistinctsexfromstudent;
selectcount(sex),count(distinctsex)fromstudent;
2、compute和computeby汇总查询
对年龄大于的进展汇总
selectagefromstudent
whereage>20orderbyagecomputesum(age)byage;
对年龄大于的按照性别进展分组汇总年龄信息
selectid,sex,agefromstudent
whereage>20orderbysex,agecomputesum(age)bysex;
按照年龄分组汇总
selectagefromstudent
whereage>20orderbyage,idcomputesum(age);
按照年龄分组,年龄汇总,id找最大值
selectid,agefromstudent
whereage>20orderbyagecomputesum(age),max(id);
compute进展汇总前面是查询的结果,后面一条结果集就是汇总的信息。
compute子句中可以添加多个汇总表达式,可以添加的信息如下:
a、可选by关键字。
它是每一列计算指定的行聚合
b、行聚合函数名称。
包括sum、avg、min、max、count等
c、要对其执行聚合函数的列
computeby适合做先分组后汇总的业务。
computeby后面的列一定要是orderby中出现的列。
3、cube汇总
cube汇总和compute效果类似,但语法较简洁,而且返回的是一个结果集。
selectcount(*),sexfromstudentgroupbysexwithcube;
selectcount(*),age,sum(age)fromstudentwhereageisnotnullgroupbyagewithcube;
cube要结合groupby语句完成分组汇总
Ø排序函数
排序在很多地方需要用到,需要对查询结果进展排序并且给出序号。
比如:
1、对某表进展排序,序号需要递增不重复的
2、对学生的成绩进展排序,得知名次,名次可以并列,但名次的序号是连续递增的
3、在某些排序的情况下,需要跳空序号,虽然是并列
根本语法
排序函数over([分组语句]排序子句[desc][asc])
排序子句orderby列名,列名
分组子句partitionby分组列,分组列
#row_number函数
根据排序子句给出递增连续序号
按照名称排序的顺序递增
selects.id,s.name,cid,c.name,row_number()over(orderbyc.name)asnumber
fromstudents,classescwherecid=c.id;
#rank函数函数
根据排序子句给出递增的序号,但是存在并列并且跳空
顺序递增
selectid,name,rank()over(orderbycid)asrankfromstudent;
跳过一样递增
selects.id,s.name,cid,c.name,rank()over(orderbyc.name)asrank
fromstudents,classescwherecid=c.id;
#dense_rank函数
根据排序子句给出递增的序号,但是存在并列不跳空
不跳过,直接递增
selects.id,s.name,cid,c.name,dense_rank()over(orderbyc.name)asdense
fromstudents,classescwherecid=c.id;
#partitionby分组子句
可以完成对分组的数据进展增加排序,partitionby可以与以上三个函数联合使用。
利用partitionby按照班级名称分组,学生id排序
selects.id,s.name,cid,c.name,row_number()over(partitionbyc.nameorderbys.id)asrank
fromstudents,classescwherecid=c.id;
selects.id,s.name,cid,c.name,rank()over(partitionbyc.nameorderbys.id)asrank
fromstudents,classescwherecid=c.id;
selects.id,s.name,cid,c.name,dense_rank()over(partitionbyc.nameorderbys.id)asrank
fromstudents,classescwherecid=c.id;
#ntile平均排序函数
将要排序的数据进展平分,然后按照等分排序。
ntile中的参数代表分成多少等分。
selects.id,s.name,cid,c.name,
ntile(5)over(orderbyc.name)asntile
fromstudents,classescwherecid=c.id;
Ø集合运算
操作两组查询结果,进展交集、并集、减集运算
1、union和unionall进展并集运算
--union并集、不重复
selectid,namefromstudentwherenamelike'ja%'
union
selectid,namefromstudentwhereid=4;
--并集、重复
select*fromstudentwherenamelike'ja%'
unionall
select*fromstudent;
2、intersect进展交集运算
--交集〔一样局部〕
select*fromstudentwherenamelike'ja%'
intersect
select*fromstudent;
3、except进展减集运算
--减集〔除一样局部〕
select*fromstudentwherenamelike'ja%'
except
select*fromstudentwherenamelike'jas%';
Ø公式表表达式
查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性低,不利于理解。
那么公式表表达式可以解决这个问题。
我们可以将公式表表达式〔CET〕视为临时结果集,在select、insert、update、delete或是createview语句的执行围进展定义。
--表达式
withstatNum(id,num)as
(
selectcid,count(*)
fromstudent
whereid>0
groupbycid
)
selectid,numfromstatNumorderbyid;
withstatNum(id,num)as
(
selectcid,count(*)
fromstudent
whereid>0
groupbycid
)
selectmax(id),avg(num)fromstatNum;
Ø连接查询
1、简化连接查询
--简化联接查询
selects.id,s.name,c.id,c.namefromstudents,classescwheres.cid=c.id;
2、leftjoin左连接
--左连接
selects.id,s.name,c.id,c.namefromstudentsleftjoinclassescons.cid=c.id;
3、rightjoin右连接
--右连接
selects.id,s.name,c.id,c.namefromstudentsrightjoinclassescons.cid=c.id;
4、innerjoin连接
--连接
selects.id,s.name,c.id,c.namefromstudentsinnerjoinclassescons.cid=c.id;
--inner可以省略
selects.id,s.name,c.id,c.namefromstudentsjoinclassescons.cid=c.id;
5、crossjoin交叉连接
--交叉联接查询,结果是一个笛卡儿乘积
selects.id,s.name,c.id,c.namefromstudentscrossjoinclassesc
--wheres.cid=c.id;
6、自连接〔同一表进展连接查询〕
--自连接
selectdistincts.*fromstudents,students1wheres.id<>s1.idands.sex=s1.sex;
Ø函数
1、聚合函数
max最大值、min最小值、count统计、avg平均值、sum求和、var求方差
select
max(age)max_age,
min(age)min_age,
count(age)count_age,
avg(age)avg_age,
sum(age)sum_age,
var(age)var_age
fromstudent;
2、日期时间函数
selectdateAdd(day,3,getDate());--加天
selectdateAdd(year,3,getDate());--加年
selectdateAdd(hour,3,getDate());--加小时
--返回跨两个指定日期的日期边界数和时间边界数
selectdateDiff(day,'2011-06-20',getDate());
--相差秒数
selectdateDiff(second,'2011-06-2211:
00:
00',getDate());
--相差小时数
selectdateDiff(hour,'2011-06-2210:
00:
00',getDate());
selectdateName(month,getDate());--当前月份
selectdateName(minute,getDate());--当前分钟
selectdateName(weekday,getDate());--当前星期
selectdatePart(month,getDate());--当前月份
selectdatePart(weekday,getDate());--当前星期
selectdatePart(second,getDate());--当前秒数
selectday(getDate());--返回当前日期天数
selectday('2011-06-30');--返回当前日期天数
selectmonth(getDate());--返回当前日期月份
selectmonth('2011-11-10');
selectyear(getDate());--返回当前日期年份
selectyear('2010-11-10');
selectgetDate();--当前系统日期
selectgetUTCDate();--utc日期
3、数学函数
selectpi();--PI函数
select