Oracle学习笔记(一)

一.基本的查询语句，特殊符号||。制定列的别名AS，唯一标示distinct

1.字符连接符“||”与“+”符

oracle：select  列名||'111111111111111' from 表名
sql：select  列名+'111111111111111' from 表名T

2“AS”符号
oracle：select  列名 as 新列名 from 表名 （空格，不能有as）新表名
sql：select  列名 as 新列名 from 表名T as（as可有可无） 新表名

二.数据排序与数据过滤查询与sql相同

in ,like, is null,between ... and ...，and ，or

order by 列名 asc/desc 默认升序

三.单行函数（single-row functions）
 Character字符函数、Number数字函数、Date日期函数、Conversion转换函数、General通用函数

1.字符函数：
Case字符处理函数包括：LOWER、UPPER、INITCAP
lower('SQL')->sql 把大写变小写
upper('sql')->SQL 把小写变大写
initcap('sql course')->Sql Course 首字母大写

Character操作函数

字符拼接
concat('Hello','World')->HelloWorld

字符截取
从左边第一个截取5个字符
substr('HelloWorld',1,5)->Hello

在sql中用left()函数

1、left() LEFT (， ) 返回character_expression 左起 integer_expression 个字符。 列： left('helloworld',5)—>hello

从右边数第五个截取5个字符
substr('HelloWorld',-5,5)->World

在sql中用right()函数

1、right() RIGHT(， ) 返回character_expression 左起 integer_expression 个字符。 列： right('helloworld',5)—>world

 

计算长度
length('HelloWorld')->10

查找字符w的位置
instr('HelloWorld','w')->6

在sql中用charindex()函数

2.charindex()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<'substring_expression'>， )
其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。

取出10个字符不够以字符“*”左边填充
lpad('hello',10,'*')->*****hello

取出10个字符不够以字符“*”右边填充
rpad('hello'10,'*')->hello*****

从字符中删除首个字母
trim('H' from 'HelloWorld')->elloWorld

2.数字函数：round、trunc、mod
round(45.926,2)->45.923

trunc(45.926,2)->45.92

mod(16,3)->1求余数

3.日期函数

获取系统当前时间
sql：select getdate();
oracle: select sysdate from dual;

计算日期差：
months_between(sysdate,to_date('2010-11-29','yyyy-MM-dd'))

加上一个月
add_months(sysdate,1)

下个星期五是几号(plsql中执行)
select next_day(to_date('2011-11-1','yyyy-mm-dd'),'星期五')from dual ->2011-11-04

一月的最后一天：
last_day(to_date('2011-11-1','yyyy-mm-dd'))->2011-11-30

4.转换函数

 varchar2 or char -> number

 varchar2 or char -> date

 number ->varchar2

 date -> varchar2

获取当前星期几
oracle: select to_char(sysdate, 'day')  from dual
sql: select datename(weekday,getdate())

to_char(日期字段,'fmDD Month YYYY')
to_char(number,'format')
to_number(字符字段)
to_date('1-11-2011','dd-MM-yyyy')

sql: 中用CAST 和 CONVERT

将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。

使用 CAST：
CAST ( expression AS data_type )

使用 CONVERT：
CONVERT (data_type[(length)], expression [, style])

函数嵌套 select length(substr('helloworld',1,5))

 

5.通用函数
nvl(parm1,parm2) 如果parm1为空 则返回parm2

nvl2(parm1,parm2,parm3）不为空，返回parm2,否则parm3

　如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。
　如果两个表达式相等，NULLIF 返回空值NULL。
nullif(expression1,expression2)
coalesce(....)一次判断

4.多表查询，表 别名
sql：
1、内联接（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。    
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。  

2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。    
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：    

1）LEFT  JOIN或LEFT OUTER JOIN    
左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。      

2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN    
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。      
3）FULL  JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。  

3、交叉联接  
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。   

FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。    

例子：  

-------------------------------------------------
  a表     id   name     b表     id   job   parent_id  
              1   张3                   1     23     1  
              2   李四                 2     34     2  
              3   王武                 3     34     4      
  a.id同parent_id   存在关系  

--------------------------------------------------   
 1） 内连接  
  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id      
  结果是    
  1   张3                   1     23     1  
  2   李四                  2     34     2  

  2）左连接  
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id      
  结果是    
  1   张3                   1     23     1  
  2   李四                  2     34     2  
  3   王武                  null   

 3） 右连接  
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id      
  结果是    
  1   张3                   1     23     1  
  2   李四                  2     34     2  
  null                       3     34     4  

 4） 完全连接  
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id  

  结果是    
  1   张3                  1     23     1  
  2   李四                 2     34     2  
  null               　　  3     34     4  
  3   王武                 null