常见SQL语句及其函数集锦

1、select DISTINCT amout from checks;表示查询checks表中amout字段不重复的数据。关键字DISTINCT会强制性的要求返回的结果中不能有重复数据。
2、select item,wholesale,(wholesale+0.15) retail from price;学会使用别名

减号(-)的用法：
1、作为负号来使用
2、作为减号使用

除法/可以取小数
取模运算符%取余数

在一些SQL解释器中取模运算符为MOD，例如MOD(numberator,denominator)

由于Unknow是一种不正常的状态，所以大多数SQL都会置其为无效并提供一种叫IS NULL的操作来测试Null的存在。

条件语句中判断某个字段为空应该使用where wholesale is null，而不应该使用where wholesale = null。第二种用法比第一种用法更靠谱。

在字符型字段中空字段和空白字段是不同的。

尽管SQL对大小写是不敏感的，但是数据库中的数据对大小写却是敏感的，大多数公司在存储数据时采用大些以保证数据的一致性，所以你应该采用大些或小写来存储数据，大小写的混合使用会对你精确地查找数据造成障碍。

对于数字型字段是不需要加引号的。

不等号<>或!=，不同解释器使用的符号可能不同。

在like表达式中，%是一种通配符，表示可能出现的其他信息。重点：like语句对大小写是敏感的。

如果你想查找在某一确定的位置上有字符的数据时你应该如何去做呢？你可以使用另一个通配符_下划线。

例如：select * from friends where state like 'C_';表示查找state字段以C开头并且只有两个字符的记录。

select * from friends where firstname like '_L%';找出所有的第二个字母为L的记录

||可以将两个字符串连接起来。有一些解释器也采用加好来连接字符串，这需要自己来检查自己使用的解释器。

and且

or对几个条件进行合并，当其中的任一个条件为真时，其结果就会为真。

not，它对条件取反，条件为假时结果为真，条件为真时结果为假。

select * from price where wholesale is not null;

select * from vacation where lastname not like 'B%';

union将返回两个查询的结果并去除其中的重复部分。

select name from softball union select name from football;返回两个表中name不同的所有name名称

select name from softball union all select name from football;返回两个表中所有的name，包括重复的

union all将返回两个表合并之后所有的记录(不去重)

intersect(相交，返回两个表中相同的都存在的行)

select name from football intersect select name from softball;返回两个表中都存在的名称记录

minus(相减，返回的记录是存在于第一个表中但不存在于第二个表中的记录)：

例如：select * from football numus select * from softball;检索出不在垒球队中的足球队员

从属运算(in and between),而且between操作将包括边界值:
select * from friends where state in('CA','CO','LA');
select * from friends where areacode in(100,381,204);

select * from price where wholesale between 0.25 and 0.75;




下面是汇总函数：
count函数：统计记录数
sum函数：返回某一列所有数值的和
avg返回某一列的平均值
这几个函数只能对数字进行计算

求平均值(求各种率)，sum与avg的区别：
sum(hits)/sum(ab)
avg(hits/ab)
avg(hits)/avg(ab)
这三种计算方法里面第一种和第三种是正确的，第二种是错误的。

max函数：得到某一列中的最大值
注意：汇总函数在where子句中无法使用。
注意：max函数既可以处理数值也可以处理字符串(处理字符串时以首字母为基准，最大的是z)

variance函数(也是只应用于数值对象的函数)：方差函数
stddev函数(也是只应用于数值对象的函数)：标准差函数

下面是日期函数：
add_months：将给定的日期增加指定月数。
例如：select task tasks_shorter_than_one_month from project where add_months(startdate,1)>enddate;
last_day：返回指定月份的最后一天。
例如：select enddate,last_day(enddate) from project;
months_between：得到在给定的两个日期中有多少个月。
例如：select task,startdate,enddate,months_between(startdate,enddate) duration from project;
new_time：把时间调整到你所在的时区。
next_day：将返回与指定日期在同一个星期或之后一个星期之内的，你所要求的星期几的确切日期。
例如：select startdate,next_day(startdate,'friday') from project;查询指定日期的最近的星期五是几号。
sysdate：返回系统的日期和时间

下面是数学函数：
abs函数：返回给定数值的绝对值
geil函数：返回与给定参数相等或比给定参数大的最小整数
floor函数：返回与给定参数相等或比给定参数小的最大整数
cos、sin、tan函数：返回给定参数的三角函数值，默认的参数认定为弧度制
exp函数：返回以给定的参数为指数，以e为底数的幂值
ln and log函数：ln返回给定参数的自然对数。
mod函数：在一些解释器中为取模函数(多数解释器中取模是用%运算符)
power函数：该函数可以返回某一个数对另一个数的幂，在使用幂函数时，第一个参数为底数，第二个为指数
sign函数：如果参数的值为负数，那么sign返回-1，如果参数的值为正数，那么sign返回1，如果参数为0，那么sign也返回0。
sqrt函数：该函数返回参数的平方根，由于负数时不能开平方的，所以我们不能将该函数应用于负数


下面是字符函数：
chr函数：返回与所给数值参数等当的字符，返回的字符取决于数据库所依赖的字符集。
concat函数：将两个字符串连接起来(||符号也是完成这个功能的)
例如：select concat(firstname,lastname) name from characters;
注意：当多个词来做别名时需对它们使用引号。
initcap函数：该函数将参数的第一个字母变为大写，此外其它的字母则转换为小写。
lower和upper函数：lower将参数转换为全部小写字母，而upper则把参数全部转换成大写字母
lpad与rpad函数：这两个函数最少需要两个参数，最多需要三个参数。每一个参数是需要处理的字符串，第二个参数是需要将字符串扩充的宽度，第三个参数表示加宽部分用什么字符来做填补，第三个参数的默认值为空格，但也可以是单个的字符或字符串，下面的句子中向字段中加入了五个字符
ltrim与rtrim函数：ltrim和rtim至少需要一个参数，最多允许两个参数。第一个参数与lpad和rpad类似，是一个字符串，第二个参数也是一个字符或字符串，默认则是空格。如果第二个参数不是空格的话，那么该函数将会像剪除空格那样剪除多指定的字符。
replace函数：它的工作就像它的名字所说的那样，该函数需要三个参数，第一个参数是需要搜索的字符串，第二个参数是搜索的内容，第三个参数则是需要替换成的字符串，如果第三个参数省略或是null，那么将只执行搜索操作而不会替换任何内容。
substr函数：这个函数有三个参数，允许你将目标字符串的一部分输出。第一个参数为目标字符串，第二个字符串是将要输出的子串的起点，第三个参数是将要输出的子串的长度。如果没有第三个参数，将会输出字符串余下的部分。
translate函数：这一函数有三个参数，目标字符串、源字符串和目的字符串，在目标字符串与源字符串中均出现的字符将会被替换成对应的目的字符串的字符。
instr函数：如果需要知道在一个字符串中满足特定的内容的位置可以使用instr，它的第一个参数是目标字符串，第二个参数是匹配的内容，第三个和第四个参数是数字，用以指定开始搜索的起点以及指出第几个满足条件的将会被返回。下面的例子将从字符串的第二个字符开始，搜索并返回第一个以o开头的字符的位置：
select lastname,instr(lastname,'o',2,1) from characters;
length函数：将返回指定字符串的长度


下面是转换函数：
to_char函数：该函数的最初功能是将一个数字转换为字符型，不同的解释器可能会使用它来转换其它的数据类型，例如日期型转换为字符型或是拥有更多的参数。
注意：如果对数字使用length函数将会返回错误，to_char与先前见过的chr不同，chr返回字符集中给定数字位置的一个字符或符号。
to_number函数：该函数与to_char函数想对应，显而易见，它是将一个字符串型数字转换为数值型。
greatest与least函数：这两个函数将返回几个表达式中最大的和最小的。
user函数：该函数将返回当前使用数据库的用户的名字。

以下是子句的学习：
starting with子句：starting with字句附加于where子句上，它的作用与like(exp%)相似。
order by子句：order by子句为你提供了对输出的结果进行排序的方法。desc表示降序，asc(默认)表示升序。(order by可以使用多个字段，order by后面跟的多个字段的顺序不同排序结果也是完全不同的)
技巧：假如你已经知道了你想要进行排序的列是表中的第一列的话，那么你可以用order by 1来代替输入列的名字。
group by函数：SQL无法把正常的列和汇总函数结合在一起，这时就需要group by子句，它可以对select的结果进行分组后再用汇总函数。
注意：如果我们在分组时指定的列名与select中所指定的列名不相同时会报错，规则是当要求分组结果返回多个数值时不能再在select子句中使用除分组列以外的列，这将会导致多雾的返回值。你可以使用在select中未列出的列进行分组。
having子句：having子句允许你将汇总函数作为条件(汇总函数在where子句中无法使用)，having子句也允许使用多个条件，你也可以在having中使用在select中没有指出的字段进行汇总。其他操作符如avg,min,in等都可以在having子句中使用。
例如：select team,avg(salary) from orgchart group by team having avg(salary)<3800;
注意：无论在什么情况下分组，select语句中出现的字段只能是在group by中出现过的才可以，除非你在select子句中不指定任何字段。

下面讲解联合查询：
在使用联合查询的时候经常会为每一个表起一个别名，然后在前每个需要查询的字段前面都使用这个别名，可以避免当两个表出现相同的列名时不知道查询的是哪个表的错误。
等值联合：
例如select o.orderdon,o.name,o.partnum,p.partnum,p.description from orders o,part p where o.partnum = p.partnum;
利用在两个表中都存在的partnum列，我们得到了存储在orders表中的信息以及在parts中的与orders相关的信息，它表明了你已经决定出的零部件数量。
通常情况下根据给定的条件返回行数最少的表会作为驱动表-也就是基表。在查询中除基表以外的其它表通常是向基表联合以便更有效地获得数据。
例如：select c.name,c.address,o.quantity * p.price total from orders o,part p,customer c where o.partnum = p.partnum and o.name = c.name order by c.name;[orders表示基表]
注:如果你所使用的平台足够快，那么多表联合可能对系统的性能没有影响。可是如果你工作在一个比较慢的平台上，联合可能会导致死机。
不等值联合则是在where子句中使用除了符号以外的其它比较运算符。
select p.partnum,p.description,p.price,o.name,o.partnum from part p join orders o on orders.partnum = 54;
不要对内部联合和外部联合操太多的心，大多数的SQL产品会自动判断在你的查询中应该使用哪一种联合。
在一些解释器中使用+号来代替外部联合，+号的意思就是显示我的全部内容包括不匹配的内容。
例如：select e.name,e.employee_id,ep.salary,ep.marital_status from e,ployee_tbl e,employee_pay_tbl ep where e.employee_id =
ep.employee_id(+) and e.name like '%MITH';
这条语句将会联合两个表，标有+号的employee_id将会全部显示，包括不满足条件的记录。
表的自连接：
例如：select f.partnum,f.description,s.partnum,s.description from part f,part,s where f.partnum = s.partnum and f.description <> s.description
将会检查到表里字段partnum重复的记录。


以下讲解子查询：
子查询时一种把查询的结果作为参数返回给另一种查询的一种查询，子查询可以让你将多个查询绑定在一起。
例如：select o.orderedon,o.partnum,p.description,o.quantity,o.remarks from orders o,part p where o.partnum = p.partnum and o.partnum = (select partnum from part where description like 'ROAD%');
分析上面一条语句：如果子查询查出来的partnum就一个的话此条sql语句能顺利执行，而如果不止一个的话执行这条sql语句就会报错，因为多个partnum无法赋给o.partnum=，就是这一步导致的错误，partnum不能同时匹配多个值。所以在这里需要作出提醒，在使用子查询时候，如果想要使用比较运算符如>、<和=时，你必须确保你的子查询结果是唯一的。针对上面的例子，更靠谱的写法如下：
select o.orderedon,o.partnum,p.description,o.quantity,o.remarks from orders o,part p where o.partnum = p.partnum and o.partnum = (select partnum from part where description ='ROAD BIKE');
当你需要唯一的结果时如何才能避免子查询返回多个结果呢：首先是不要使用like，再就是设计表的时候就要保证你要搜索的字段内容是唯一的，你也可以使用表自我归并的方法来检查给定字段的内容是否是唯一的。
例子：select o.name,o.orderedon,o.quantity * p.price total from orders o,part p where o.partnum = p.partnum and o.quantity * p.price>(select avg(o.quantity*p.price) from orders o,part p where o.partnum = p.partnum);查询都有哪些订单的金额高于平均值。
事实上，大多数子查询都是作为独立查询经过测试确定其只返回一个值以后才作为子查询使用的。
子查询的嵌套就是将一个子查询嵌入到另一个子查询中去。
例：select all c.name,c.address,c.state,c.zip from customer c where c.name in(select o.name from orders o,part p where o.partnum = p.partnum and o.quantity * p.price >(select avg(o.quantity * p.price from orders o,part p where o.partnum = p.partnum)));给那些花费了平均价格的客户发一个特别通知。
注：在子查询中使用关键字in是非常普遍的，因为in可以与一组数据进行对比，而且它不会是sql的引擎检查出其中有冲突或是不适合的地方。
子查询也可以使用group by和having子句，例如下：
select name,avg(quantity) from orders group by name having avg(quantity) > (select avg(quantity) from orders);
相关查询的执行情况与归并非常相似。下面是例子。
例如：select * from orders o where 'ROAD BIKE'=(select description from part p where p.partnum = o.partnum);这条语句中如果子查询查出来的description不止一个，那么执行语句就会报错
注意：当在相关查询中使用group by和having子句时，在having子句中的列必须在select或group by子句中存在，否则你将会收到一行非法引用的信息，因为这时与子查询对应的是每一组而不是每一行，对于组你无法进行比较操作。
exists、any、all的使用：如果子查询返回的内容为非空时exists返回true，否则返回false。
例如：select name,orderedon from orders where exists (select * from orders where name='TRUE WHEEL');在这个例子中如果子查询能查出记录来，不管多少条记录，exists都会返回true，然后select 就会查询出orders表中的多有记录，而如果子查询一条记录也查不出来，那么exists返回false，select查询将不会得到任何结果。exists的子查询不会关心查询出多少条记录，它只关心能不能查询出记录来，该返回true还是alsefalse。
你可以使用exist来检查查询是否确实存在输出，从而实现对查询确实有结果才输出的控制。
any与子查询中的每一行与主查询进行比较，并对子查询中的每一行返回一个true值。
例如：select name,orderedon from orders where name=any(select name from orders where name='TRUE WHEEL');
any和some的用法相同。与in的用法特别类似。
例如：select name,orderedon from orders where name in(select name from orders where name='TRUE WHEEL');这条语句和上一条语句执行结果相同。in的执行结果和some或any是相同的。
但是any或some也有它的独到用处：
例如select name,orderedon from orders where name > (select name from orders where name='JACKS BIKE');
in相当于多个等号的作用，而any和some则可以使用其他的比较运算符如大于或小于，它是一个新增工具
all关键字的作用在于子查询中的所有结果均满足条件是它才会返回true，all常起双重否定的作用。
相关查询会在查询和子查询之间建立一个关系，并对子查询中的每一个实例加以关系限制，不要为查询的长度所制约，你可以马上从它们中把子查询找出来。


以下是新增内容：
使用intersect，切记，intersect将返回两个查询中的公共行
例如：select partno from part1 intersect select partno from part2;
group by必须在order by之前
排序时要排序的列不必一定要在select语句中出现