oracle回顾--基础篇（二）

一、模糊查询like通配符及说明

%               包含零个或多个字符串的任意字符串，包括字符串like 's%'

_                 任何单个字符，不包括空字符

[ ]                指定范围或集合中的任何单个字符，like '[1-5]abc'

[^]                不属于指定范围或集合中的任何单个字符，like '[^1]abc'

二、order by 子句

用order by 子句 排序时，默认为升序asc,数值类按从小到大的顺序显示，日期值按由远及近的顺序显示，字符值按字母顺序显示，可改为降序desc，null值认为是无穷大的，降序时指定nulls last可以将null放在最后，order by子句中的排序列是列名。

三、group by 子句

group by 子句常与分组函数（count,max,min,sum,avg)函数一起使用，在使用group by 子句时，select查询出的字段要么使用了分组函数，要么是分组的列（被group by子句使用）

四、having子句

having 子句通常与group by 子句一起使用，用来限定搜索条件，having子句与组有关，不与单个值有关，它会作用于group by创建的组，只能配合group by子句使用；

having子句中的关系表达式必须使用分组函数，可以是select语句中已出现的分组函数，也可以是没有出现的函数。having 子句通常用于在检索出的结果中过滤结果，所以效率会比较低，where在查询时就限定结果的范围。

     eg: select sales_rep_id,sum(order_tatal) from orders

            group by sales_rep_id

            having sum(order_total)>15000

虽然having子句和where子句都是用来设置条件的，但是，where子句设置的条件是在查询时起作用，它决定查询什么样的数据，若要进行统计，这样的条件在统计之前就已经起作用了，而having子句设置的条件只有在进行统计后才起作用，它决定了对于统计产生的数据哪些能够显示给用户。

先执行group by语句 再执行order by语句

五、表的连接查询

1、自然连接natural join

2、内连接 inner join   （oracle默认连接为这种连接）

3、外连接（左外连接left join、右外连接right join、全外连接full join）

在oracle中，提供了外连接的简略写法，即在where条件中将附属（从）数据源（表）的列使用（+）进行标识，从而省略left join/right join以及on 关键字    （从表上加+）

eg:       

           left join

            select * from product_information p,categories ca

            where p.categories_id=ca.categories_id(+);

         right join

            select * from product_information p,categories ca

            where ca.categories_id=p.categories_id(+);

六、单表中的联合查询

      union 查询                                取得两个查询结果集的并集，会取消重复行

      union all查询                           与union相似，但不会取消重复行，不会排序

      intersect查询                           用于两个查询结果的交集

      minus查询                               查询两个给定集合之间的差集

七、子查询运算符用用法

exists                                                               如果集合S（子查询返回的结果）不为空，则条件表达式为真，否则为假

in                            表达式 in  S                     如果表达式的值在集合S中，则条件表达式为真，否则为假

=                             表达式  = S                       如果表达式的值与集合S中的唯一一个元素相等，则条件表达式为真，否则为假，集合S中最多只有一个元素

<>、<=、>=          表达式  <= S                   进行相应的关系运算

any                        用在集合之前                   指定要与集合中的任一个元素进行比较

all                         用在集合之前                    指定要与集合中的所有元素进行比较

八、查询语句的优化

1、在查询中尽量不使用"*",不需要所有字段时，用指定列名查询

2、多表查询中尽量使用表别名

3、条件查询多使用where

4、指定查询范围多使用in，or要比较每个每个条件速度比in慢

5、子查询中多使用exists语句判断条件

九、常用的聚合函数

avg() ,count(),max(),min(),sum(),stddev()（求标准偏差）,stddev_pop()（求总体标准偏差）

聚合函数经常与select语句的group by语句一起使用，除count()外，其他聚合函数都会忽略null值，聚合函数只能在select语句的选择列表（子查询或外部查询）、having子句的位置作为表达式使用。

sum ([distinct | all ] 列名)

十、字符串函数

ltrim()                     去除字符串前面的所有空格

rtrim()                    去除一个字符串尾部的所有空格

trim()                     去除一个字符串两侧的所有空格

eg： select customer_id,ltrim(cust_first_name) from customer;

可利用length()函数得到指定字符串的长度，并返回数值型，若字符串为null，则返回null值，

对于length()函数来说，无论是单字节字符还是双字节字符都将被视为一个字符进行计算，eg: length('历史记录')   4

length()函数的参数不仅可以为字符串，还可以为其他数据类型                  eg: length(1265.39)              7

十一、字符转换函数

ASCII（）函数                           返回表达式第一个字符的ASCII码值

chr(n)函数                                  返回n对应的ASCII字符，n是一个数字

lower() 函数                               将字符串全部转换为小写

upper() 函数                              将字符串全部转为大写

initcap()函数                             单词首字符大写

concat()函数                             连接字符串，只有两个参数，若连接多个字符中，则将concat()嵌套

instr()函数                                 获得字符串出现 的位置

substr()函数                             截取字符串

replace()函数                           替换字符串

lpad()/rpad()函数                     向左/右补全字符串             eg:lpad(category_id,4,'0')       0010  原始字符串长度大于预期时，截取字符串

十二、日期函数

sysdate                                    系统日期

systimestamp                        系统时间

current_date()                         返回会话所在时区当前日期函数

extract()                                    返回日期的某个域（年、月、日、时、分、秒等），  extract(域    from  日期)

trunc()                                      将日期截取到某一种格式

last_day()                               返回特定日期所在用的最后一天

add_month()                         月份增加                      add_month(order_date ,1)

months_between()              两个日期之间的月份

next_day()                              特定日期之后的一周之内的日期                     next_day(sysdate,2）获取下一个周一对应的日期

十三、转换函数

cast()                                   把内置的数据类型或集合类型值转换成其他的数据类型

                                          cast(exp as type_name)

                                          cast(sysdate as varchar2(20))

                                          cast(950 as varchar2(20))

                                          cast('951' as number)

to_char()                          将数值或日期转换为字符串

to_number()                   将字符串转换为数值  to_number('11.09','99,99')     11.09

to_date()                         字符转日期

十四、null函数

nvl () :                       可以替换数据中的null值，nvl(state,'UN'）