SQL常用查询语句.docx
《SQL常用查询语句.docx》由会员分享，可在线阅读，更多相关《SQL常用查询语句.docx（21页珍藏版）》请在冰点文库上搜索。
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,,fromstudents;
--column列运算
select（age+id）colfromstudent;
select+'-'+fromclassesc,studentswhere=;
--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
）twhere>2;
上面括号中的语句，就是子查询语句（内部查询）。
在外面的是外部查询，其中外部查询可以包含以下语句：
1、包含常规选择列表组件的常规select查询
2、包含一个或多个表或视图名称的常规from语句
3、可选的where子句
4、可选的groupby子句
5、可选的having子句
#示例
查询班级信息，统计班级学生人生
select*,（selectcount（*）fromstudentwherecid=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=andid=3
）;
查询没有分配班级的学生信息
select*fromstudentwherenotexists（
select*fromclasseswhereid=
）;
exists和notexists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。
如：
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函数
根据排序子句给出递增连续序号
按照名称排序的顺序递增
select,,cid,,row_number（）over（orderbyasnumber
fromstudents,classescwherecid=;
#rank函数函数
根据排序子句给出递增的序号，但是存在并列并且跳空
顺序递增
selectid,name,rank（）over（orderbycid）asrankfromstudent;
跳过相同递增
select,,cid,,rank（）over（orderbyasrank
fromstudents,classescwherecid=;
#dense_rank函数
根据排序子句给出递增的序号，但是存在并列不跳空
不跳过，直接递增
select,,cid,,dense_rank（）over（orderbyasdense
fromstudents,classescwherecid=;
#partitionby分组子句
可以完成对分组的数据进行增加排序，partitionby可以与以上三个函数联合使用。
利用partitionby按照班级名称分组，学生id排序
select,,cid,,row_number（）over（partitionbyorderbyasrank
fromstudents,classescwherecid=;
select,,cid,,rank（）over（partitionbyorderbyasrank
fromstudents,classescwherecid=;
select,,cid,,dense_rank（）over（partitionbyorderbyasrank
fromstudents,classescwherecid=;
#ntile平均排序函数
将要排序的数据进行平分，然后按照等分排序。
ntile中的参数代表分成多少等分。
select,,cid,,
ntile（5）over（orderbyasntile
fromstudents,classescwherecid=;
Ø集合运算
操作两组查询结果，进行交集、并集、减集运算
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、简化连接查询
--简化联接查询
select,,,fromstudents,classescwhere=;
2、leftjoin左连接
--左连接
select,,,fromstudentsleftjoinclassescon=;
3、rightjoin右连接
--右连接
select,,,fromstudentsrightjoinclassescon=;
4、innerjoin内连接
--内连接
select,,,fromstudentsinnerjoinclassescon=;
--inner可以省略
select,,,fromstudentsjoinclassescon=;
5、crossjoin交叉连接
--交叉联接查询，结果是一个笛卡儿乘积
select,,,fromstudentscrossjoinclassesc
--where=;
6、自连接（同一张表进行连接查询）
--自连接
selectdistincts.*fromstudents,students1where<>and=;
Ø函数
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函数
selectrand（100）,rand（50）,rand（）,rand（）;--随机数
selectround（rand（）,3）,round（rand（100）,5）;--精确小数位
--精确位数，负数表示小数点前
selectround,2）,round,-2）;
selectround,1,2）;
4、元数据
selectcol_name（object_id（'student'）,1）;--返回列名
selectcol_name（object_id（'student'）,2）;
--该列数据类型长度
selectcol_length（'student',col_name（object_id（'student'）,2））;
--该列数据类型长度
selectcol_length（'student',col_name（object_id（'student'）,1））;
--返回类型名称、类型id
selecttype_name（type_id（'varchar'））,type_i
升级会员 