一、条件查询
比较字符:>,<,>=,<=,!=,<>,=
AND OR
LIKE:模糊查询, "_"表示当个字符,“%”表示0或多个字符
IN(.....)和NOT IN(......)
BETWEEN ... AND ... : 查询符合某个值域范围的数据
IS NULL 和 IS NOT NULL : null是特殊的字符,不能使用 “=”进行比较
ANY和ALL
> ANY(...):大于最小
< ANY(...):小于最大
> ALL(...):大于最大
< ALL(...):小于最小
DISTINCT:过滤重复的数据
ORDER BY:根据一定规则进行排序,往往跟ASC和DESC组合使用
二、聚合函数
查询数据时需要一些统计,并且在表当中不能直接体现出来,可以使用聚合函数
MAX,MIN:max(列)最大值,min(列)最小值AVG和SUM:AVG(列)平均值,SUM(列)求和COUNT:COUNT(*)统计记录条数GROUP BY:把查询的数据划分一个个小组HAVING:对分组后近一个月进行限制
三、语句执行顺序
from子句:从右到左,数据量少的放在右边where子句:从右到左,过滤数据多的放在右边group by 子句:从左到右分组,在分组时最好用where将不过滤掉havaing子句:尽量避免使用,原因:会在检索出所有记录后才对结果集进行过滤,需要排序等操作order by子句:从左到右排序,耗资源
四、关联
两个或两个以上的数据表或试图查询叫连接查询连接查询通常建立在互相关系的父子表之间
笛卡尔积
每个表的没一行记录和其他表的每一行记录做组合,如:A1表x条记录,B1表y条记录,执行 select A1.name, B1.name from A1,B1 语句将有x*y条记录
等值连接
通常是存在主外键关联的表之间建立等值查询,是用“=”连接关系表,如: select A1.name, B1.name from A1,B1 where A1.no = B1.no
内连接
表1 JOIN 表2 ON(条件):返回满足条件的所有记录:如 select A1.name, B1.name from A1 JOIN B1 ON(A1.no = B1.no)
外连接
内连接返回满足条件的数据,有些情况下返回不满足条件的数据,外连接可以返回不满足条件的数据select A1.name, B1.name from A1 left | right | full [outer] join B1 on(A1.column1 = B1.column2)table1 left join table2 on(...):以table1为基础查询(table1的数据全部会查询出来,没有找到table2条件的以的列为空)table1 right join table2 on(...):以table1为基础查询(table2 的数据全部会查询出来,没有找到table1 条件的以的列为空)table1 full join table2 on(...):以table1为基础查询(table1,和table2的都会查询出来, 没有找到table1 条件的以的列为空,没有找到table2条件的以的列为空 )
自连接:来源同一张表,通过将表的虚名化,与自身连接,可实现等值或不等值连接如: table1 right join table1 ta1 on(table1.no = ta1.no1 ):以table1为基础查询(table2 的数据全部会查询出来,没有找到table1 条件的以的列为空)
子查询
差用到 in,all,any 等exists : exists后边的子查询至少返回一条数据,则整个条件返回true。
分页查询
rownum:给每一行进行编号(方法:(n-1)*pageSize +1至 n*pageSize)
decode函数
decode用于比较参数的值,如果匹配到哪一个条件,就返回相应的值,可以有多高对应的参数和对应的值,每一匹配到则返回默认值如:select clo1,clo2,clo3,decode(某列,值1,结果1,值2,结果2,值3,结果3,。。。,默认值) 别名列decode类似casse语句,实现类似if-else语句如:select clo1,clo2,clo3,case 某列 when 值1 then 结果1 when 值2 then 结果2 when 值3 then 结果3,。。。else 默认值 end 别名列
排序函数row_number、rank
row_num:row_number() over(paritition by col1 order by col2) 表示根据col1分组,在分组内部根据col2排序该函数的值表示魅族内部排序后的顺序编号,组内连续且唯一rownum是伪劣,row_number功能更强,可以从结果集中获取子集rank:rank()over(paritition by col1 order by col2) 表示根据col1分组,在分组内部根据col2给予等级标识等级标识即排名,相同的数据返回相同的排名,跳跃性。如:并下一个排名则跳跃一个数字,如排名2的两条数据,则吓一条数据则排名第四row_num和rank的区别在于:row_num有重复排名,rank没有重复排名dense_rankdense_rank() over(paritition by col1 order by col2):标识根据col1分组,在分组内根据col2等级标识相同的数据返回相同的排名,连续的排序。如:并下一个排名则跳跃一个数字,如排名2的两条数据,则吓一条数据则排名第三
集合操作
union(去掉重复数据后返回,并排序---重复数据只显示一次)union all(返回所有行,包括重复的行,单不排序--重复数据可能显示多长)intersect(获取两个结果集的交集)minus(返回两个结果集的差集)用法:select col1 FROM table1 union | union all | intersect | minus select col2 FROM table2
高级分组
rollup:group by rollup(a,b,c),从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组,及全表分组,有n个参数就有n+1次分组cube:group by cube(a,b,c):首先对(a,b,c)进行group by,然后依次是(a,b),(a,c),(a),(b,c),(b),(c),最后对全表就那些group by操作,一共是2^8次分组grouping sets:group by grouping sets(a,b,c)只进行(a),(b),(c)进行分组group by grouping sets((a,b,)c)则对(a,b),(c)进行分组,允许其参数重复都是group by的扩展select a,b,c from table1 group by rollup(a,b,c)等价于select a,b,c from table1 group by a,b,c union allselect a,b,null from table1 group by a,b union allselect a,null,null from table1 group by a union allselect null,null,null from table1