本笔记中包含了单行函数和多表查询的内容。
一、单行函数
特点:操作数据对象,接受参数返回一个结果,只对一行进行变换,每行返回一个结果
可以转换数据类型,可以嵌套参数,可以是一列或一个值
主要分为五个类比:字符函数、数字函数、日期函数、转换函数、通用函数
1)字符函数
—大小写控制函数
UPPER(字符串 | 列):将输入的字符串变为大写返回;
LOWER(字符串 | 列):将输入的字符串变为小写返回;INITCAP(字符串 | 列):开头首字母大写;
eg:Select column_1, column_name
From table_1
Where Lower(column_name) = 'test_name';
—字符控制函
LENGTH(字符串 | 列):求出字符串的长度;
REPLACE(字符串 | 列):进行替换;
SUBSTR(字符串 | 列,开始点 [,结束点]):字符串截取;
当使用SUBSTR时,一般有两种语法:
语法一:SUBSTR(字符串 | 列,开始点),表示从开始点一直截取到结尾,当设置开始点为负数时,表示从后开始;
语法二:SUBSTR(字符串 | 列,开始点,结束点),表示从开始点截取到结束点,截取部分内容;2)数字函数
ROUND(数字 | 列 [,保留小数的位数]):四舍五入的操作;
eg: ROUND(45.926, 2) -> 45.93, ROUND(45.926, 0) -> 46, ROUND(45.926, -1) -> 50
TRUNC(数字 | 列 [,保留小数的位数]):舍弃指定位置的内容;
eg: TRUNC(45.926, 2) -> 45.92, TRUNC(45.926) -> 45, TRUNC(45.926, -1) -> 40
MOD(数字 1,数字2):取模,取余数;
eg: MOD(10,3) -> 1
3)日期函数
MONTHS_BETWEEN(日期1,日期2):求出两个日期之间所经历的月份;
NEXT_DAY(日期,星期数):求出下一个指定星期X的日期;
4)转换函数
TO_CHAR(字符串 | 列,格式字符串):将日期或者是数字变为字符串显示;
eg:……where to_char(xxx_date,'yyy-mm-dd') = '1997-06-15'
Select to_char(column_1,'L999,999.00') column_1 From ……
TO_DATE(字符串,格式字符串):将字符串变为DATE数据显示;
eg: Select to_date(xxx_str,'yyyy-mm-dd') From ……
Select to
TO_NUMBER(字符串):将字符串变为数字显示——基本上不用了
5)通用函数
通用函数主要有两个:NVL()、DECODE(),这两个函数算是Oracle自己的特色函数了;
NVL(),将空值转换成一个已知的值,可以使用的数据类型有日期、字符、数字。函数的一般形式:
NVL(commission_pct,0)
NVL(date_x,'01-JAN-97')
NVL(id_x,'No Job Yet')
NVL2 (expr1, expr2, expr3) : expr1不为NULL,返回expr2;为NULL,返回expr3。
DECODE(数值 | 列 ,判断值1,显示值1,判断值2,显示值2,判断值3,显示值3,…)
DECODE()函数非常类似于程序中的if…else…语句,唯一不同的是DECODE()函数判断的是数值,而不是逻辑条件。
eg:Select column_1 Decode(column_2, expr1, value1, expr2, value2 ……) From ……
二、多表查询
查询的基本语法是:
Select [Distinct] * | 字段[别名] [,字段 [别名], ……]
From 表名 [别名],[表名[别名],……]
[Where 条件[s]]
[Order By 排序字段 [ASC|DESC] [, 排序字段 [ASC|DESC], ……]]
在进行多表查询的时候,若1)省略连接条件, 2)连接条件无效, 3)所有表中的所有行互相连接,将会产生笛卡尔积,
为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件。
1)等值连接——查询的表之间存在相等的关系
eg:Select tab1.column_1, tab1.column_2,……, tab2.conlmn_1, tab2.column_2, ……
From table_1 tab1, table_2 tab2
Where tab1.column_x = tab2.column_y AND ……
2)非等值连接——查询的表之间存在某种不相等的特定联系
eg:Select tab1.column_1, tab1.column_2,……, tab2.conlmn_1, tab2.column_2, ……
From table_1 tab1, table_2 tab2
Where tab1.column_z Between tab1.column_x And tab2.column_y AND ……3)自连接——在查询的时候,使用该表自身与自身进行对比
eg:Select tab1.column_1, tab1.column_2,……, tab2.conlmn_1, tab2.column_2, ……
From table_1 tab1, table_1 tab2
Where tab1.column_x = tab2.column_y AND ……
4)内连接——合并具有同一列的两个以上的表的行, 结果集中只包含一个表与另一个表匹配的行
5)外连接——除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,即称为左(或右) 外连接。
左、右连接指的是查询判断条件的参考方向
没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接,
但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+).
(+)=:放在了等号的左边,表示的是右连接; =(+):放在了等号的右边,表示的是左连接;
注意:这种符号是Oracle数据库自己所独有的,其他数据库不能使用。
6)SQL:1999语法
标准格式:Select tab1.column_1, ……, tab2.column_1
From table1 tab1
[Cross Join table2 tab2] | [Natural Jion ] |
[Jion table2 tab2 Using(column_x)] |
[Left | Right | Full Outer Join table2 tab2 On (tab1.column_x = tab2.column_y)]
交叉连接(Cross Jion):用于产生笛卡尔积
自然连接(Natural Jion):自动找到匹配的关联字段,消除笛卡尔积
Jion……Using:用户指定一个消除笛卡尔积的关联字段
Join……On:用户自己指定一个消除笛卡尔积的关联条件
改变连接方向:
左(外)连接:Left Outer Join …… On
右(外)连接:Right Outer Join …… On
全(外)连接:Full Outer Join …… On ---> 将两张表中没有的数据都显示