ORACLE学习（下）

ORACLE学习（下）
Select语句 Select语句功能
查询语句语法
Select子句
From子句
Where子句

字符串操作函数 CHAR和VARCHAR2类型
LONG和CLOB类型
LENGTH
CONCAT和“||”
UPPER、LOWER和INITCAP
TRIM、LTRIM、RTRIM
LPAD、RPAD
SUBSTR
INSTR

数值操作 NUMBER§表示整数
NUMBER(P,S)表示浮点数
ROUND
TRUNC
MOD
CEIL和FLOOR

日期常用操作 DATE
TIMESTAMP
SYSDATE
SYSTIMESTAMP
TO_DATE
日期格式
TO_CHAR
ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
NEXT_DAY
LEAST和GREATEST
EXTRACT
日期相减

一、Select语句
1.Select语句功能
SQL查询语句是使用最频繁的语句，Select语句的作用是通过from子句从某个或某些表中，通过select子句投影出某些列的数据，并通过where子句过滤选择出某些记录。

.
2.Select语句基本语法
查询语句语法select [distinct] {*,列名 [列别名],…} from 表名

select子句用于指定要查询的列，如果要查询所有的列，可以在select后面使用*号，如果只查特定的列，
可以在select后面指定列名，列名之间用逗号隔开

–查询employee_xxx表下的所有记录
select * from employee_xxx;

如果只查询表的部分列，需要在SELECT后指定列名，例如：
–查询employee_xxx下的所有员工号和员工名字
select empno,ename from employee_xxx;

使用别名
在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义，使用语法是列的别名跟在列名后，中间可以加或不加一个“as”关键字。
注意：Oracle不区分大小写，如果要让别名区分大小写要使用"别名"的方式
例如：
SELECT empno AS ID , ename empname,deptno “Dept” FROM employee_xxx;

3.From子句
from用于指定从哪张表中查询

如：
select * from dual;
dual 是用来帮助测试函数的,SYS 用户的一个表，共享给当前数据库中所有用户，
其中 SYS 是数据库中管理员用户

4.Where子句
在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果，是可选的。

当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起。

例如查询部门20下的员工信息：
SELECT * FROM employee_xxx WHERE deptno = 10;

查询职员表中职位是’MANAGER’的员工：
SELECT * FROM employee_xxx WHERE job = ‘MANAGER’;–如果数据是’Manager’查不出结果
注：oracle中sql语句大小写不敏感，数据大小写敏感

三、字符串操作
1.CHAR和VARCHAR2类型

CHAR和VARCHAR2类型都是用来表示字符串数据类型，用来在表中存放字符串信息
CHAR
char数据类型存储固定长度的字符。一个CHAR数据类型可以包括1到2000个字符。如果对CHAR没有明确地说明长度，它的默认长度则设置为1。如果对某个CHAR类型变量赋值，其长度小于规定的长度，那么Oracle自动用空格填充。
VARCHAR2
VARCHAR2 存储可变长度的字符串。虽然也必须指定一个VARCHAR2数据的长度，但是这个长度是指赋值的最大长度而非实际赋值长度。不需用空格填充。VARCHAR2必须指定长度，最多可设置为4000个字符。因为VARCHAR2数据类型只存储为该列所赋的字符（不加空格），所以VARCHAR2需要的存储空间比CHAR数据类型要小。

在Oracle中，所有字符串类型都以同样的格式存储。在数据库存储单元上，最前面都有一个1～3字节的长度字段，其后才是数据

如果一个包含"hello world"的varchar2(80), 存储结构如图所示：

如果在char(80)中存储同样的信息，存储结构如图所示：

CHAR和VARCHAR2的存储编码
字符串在数据库中存储的默认单位是字节，也可显式指定为字符。如：
CHAR(80)，等价于 CHAR(80 BYTE)
如果指定单位为字符：CHAR(80 CHAR)，160个字节
VARCHAR2(80)， 等价于VARCHAR2（80 BYTE）
指定单位为字符：VARCHAR2(80 CHAR)，160个字节

每个英文字符占用一个字节，每个中文字符按编码不同，占用2-4个字节：
GBK: 2个字节
UTF-8: 2-4个字节

2.varchar
varchar是标准sql提供的数据类型
varchar2是oracle独有的字符串类型，建议使用varchar2

3.LONG和CLOB类型

LONG和CLOB类型可以存储更大容量的字符数据
LONG
long数据类型可以存放2GB的字符数据，它是从早期版本中继承下来的。但是LONG类型有诸多限制，所以不建议使用：
每个表只能有一个LONG类型列；
不能作为主键；
不能建立索引；
不能出现在查询条件中等
现在如果存储大容量的数据，Oracle推荐使用CLOB。

例如：
CREATE TABLE manager(
id NUMBER(4),
name VARCHAR2(20),
workExperience CLOB
);

LONG
CLOB用来存储定长或变长字符串，最多达4GB的字符串数据

字符串操作函数

4.SQL 函数

5.单行函数:
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值

6.LENGTH
LENGTH(char)用于返回参数字符串的长度。如果字符类型是VARCHAR2，返回字符的实际长度(字符个数)，如果字符类型是CHAR，长度还包括后补的空格。

例如：
select ename,length(ename) from employee_xxx;
会
列出ename和ename的字符串长度

7.CONCAT和“||”
CONCAT是字符串连接函数，语法是：
CONCAT(char1, char2)

用于返回两个字符串连接后的结果，两个参数char1、char2是要连接的两个字符串。
–查询所有的员工姓名，按’员工的姓名是xxx’格式显示
select concat(‘员工的姓名是’,ename) name from employee_xxx;

concat只能有两个参数，所以如果连接三个字符串时，需要两个concat函数。
–连接employee_xxx表中的ename列和job列，显示格式为:xxx的工作xxx
SELECT CONCAT(CONCAT(ename, ’ 的工作是’), job) FROM employee_xxx;

在连接两个以上操作符时并不是很方便。concat的等价操作是连接操作符”||”(类似于java的”+”)。当多个字串连接时，用||符号更直观。下述SQL语句实现相同的效果：
SELECT ename||‘的工作是’||job from employee_xxx;

练习：
查询出所有的员工工号，并连接ename和job，显示格式为xxx work as xxx，并给该连接显示结果取别名为detail
select empno,ename ||’ work as '||job detail from employee_xx;

8.UPPER、LOWER和INITCAP
这三个函数全部是英文的大小写转换函数，用来转换字符的大小写：
UPPER(char)用于将字符转换为大写形式
LOWER(char)用于将字符转换为小写形式
INITCAP(char)用于将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格和非字母字符分隔
如果这三个函数的输入参数是NULL值，仍然返回NULL值。例如：
SELECT UPPER(‘i like oracle’), LOWER(‘I LIKE ORACLE’), INITCAP(‘i like oracle’) FROM DUAL;
将列出参数“i like oracle”的大写、小写和首字符大写的形式。

一般用来查询数据表中不确定大小写的情况。
–查询员工Jones的信息
select * from employee_xxx where ename=‘Jones’;
Oracle中不区分大小写，是指SQL语句不区分大小写，但是数据是大小写敏感的,数据库中的数据是JONES，所以查不到数据
将字符数据转换为小写或大写( 如果不知道职位的大小写形式 , 可以使用 lower/upper 函数 , 忽略大小写 )
select * from employee_lzh where lower(ename)=‘jones’;
select * from employee_lzh where upper(ename)=‘JONES’;

9.TRIM、LTRIM、RTRIM

这三个TRIM函数的作用都是截去子字符串。语法形式及解释：
TRIM(c2 FROM c1) 表示从c1的前后截去c2
LTRIM(c1[, c2]) 表示从c1的左边（Left）截去c2
RTRIM(c1[, c2]) 表示从c1的右边（Right）截去c2

在后两个函数中，如果没有参数c2，就去除空格。例如：
SELECT TRIM(‘甜’ from ‘甜蜜生活是那么的甜’) AS TRIM1,
LTRIM(‘甜蜜生活是那么的甜’, ‘甜’) AS LTRIM2,
RTRIM(‘甜蜜生活是那么的甜’, ‘甜’) AS RTRIM3
FROM DUAL;

其中最常用的是TRIM，经常用来去掉字符串前后的空格

10.LPAD、RPAD

PAD意即补丁，LPAD和RPAD两个函数都叫做补位函数，LPAD表示LEFT PAD，在左边打补丁，RPAD表示RIGHT PAD，在右边打补丁。语法如下：
LPAD(char1, n, char2) 左补位函数
RPAD(char1, n, char2) 右补位函数
参数的含义： 在字符串参数char1的左端或右端用char2补足到n位，其中参数char2可重复多次。
例如在employee_xxx表中使用左补位，将sal用$补齐7位selectename,lpad(sal,7{,}^{\prime }$’) from employee_xxx;

11.SUBSTR

SUBSTR表示在一个字符串中截取子串：
SUBSTR(char, [m[, n]])
用于返回char中从m位开始取n个字符的子串，字符串的首位计数从1开始。参数作用：
如果m = 0，则从首字符开始，如果m取负数，则从尾部开始
如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止
SELECT substr(‘i like oracle’,3,4) FROM dual;

SELECT substr(‘i like oracle’,-11,4) FROM dual;

12.INSTR

用来返回在一个字符串中子串的位置：
INSTR(char1, char2[, m [, n]])
参数的作用：
返回子串char2在源字符串char1中的位置
从m的位置开始搜索，没有指定m，从第1个字符开始搜索
n用于指定子串的第n次出现次数，如果不指定取值1
如果在char1中没有找到子串char2 ，返回0

例如：
SELECT instr(‘i like oracle’,‘like’) FROM dual;

四、数值常用操作
1.Number表示整数和浮点数
NUMBER（p）表示整数

数据表中的数值类型用NUMBER表示：
NUMBER(p,s)

可以用来表示整数和浮点数。如果没有设置参数s，则默认取值0，即NUMBER§用来表示整数。P表示数字的总位数，取值为1-38。一般用来在表中存放整数记录的数据。例如建表时指定书本编号是6位数字：
CREATE TABLE book_xxx(
ID NUMBER(6),
NAME VARCHAR2(20)
);

NUMBER（P,S）表示浮点数
如果NUMBER(p,s)的两个参数全部显式定义，则表示浮点数：
p：NUMBER可以存储的最大数字长度（不包括左右两边的0）
s：在小数点右边的最大数字长度（包括左侧0）

NUMBER（p,s）经常用来做表中存放金额、成绩等有小数位的数据
例如：
CREATE TABLE book2_xxx(
ID NUMBER(6),
NAME VARCHAR2(20),
price NUMBER(8,4)–书本价格，小数点后4位，总共8位有效数值
);

NUMBER的变种数据类型：内部实现是NUMBER，可以将其理解为NUMBER的别名，目的是与多种数据库及编程语言兼容
NUMERIC(p,s)：等价于NUMBER(p,s)
DECIMAL(p,s)或DEC(p,s)：等价于NUMBER(p,s)
INTEGER或INT：等价于NUMBER(38)类型
SMALLINT：等价于NUMBER(38)类型
FLOAT(b)：等价于NUMBER类型

2.数值操作函数
数值函数指参数是数值类型的函数。常用的有ROUND、TRUNC、MOD、CEIL和FLOOR。

3.ROUND
ROUND(n[, m]) 用于将参数n按照m的数字要求四舍五入。
参数中的n可以是任何数字，指要被处理的数字
m必须是整数
m取正数则四舍五入到小数点后第m位
m取负数，则四舍五入到小数点前m位
m取0值则四舍五入到整数位
m缺省，默认值是0
例：
SELECT ROUND(1234.5678, 2) FROM DUAL; --1234.57
SELECT ROUND(1234.5678, -1) FROM DUAL;–1230
SELECT ROUND(1234.5678, 0) FROM DUAL;–1235
SELECT ROUND(1234.5678) FROM DUAL;–1235

4.TRUNC
TRUNC(n[, m])的功能是截取，其中n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n
SELECT TRUNC(1234.5678, 2) FROM DUAL; --1234.56
SELECT TRUNC(1234.5678, -1) FROM DUAL;–1230
SELECT TRUNC(1234.5678, 0) FROM DUAL;–1234
SELECT TRUNC(1234.5678) FROM DUAL;–1234

5,MOD
MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m。
例如：
SELECT MOD(1234,1000) FROM dual;–234
SELECT MOD(999,1000) FROM dual;–999

6.CEIL和FLOOR
CEIL(n)、FLOOR(n)这两个函数顾名思义，一个是天花板，就是取大于或等于n的最小整数值，一个是地板，就是取小于或等于n的最大整数值。比如数字n = 4.5，那么它的CEIL是5.0，它的FLOOR是4.0
SELECT CEIL(1234.5678) FROM DUAL;–1235
SELECT FLOOR(1234.5678) FROM DUAL;-1234

五、日期处理
1.DATE
它可以保存定长的日期或时间数据，包括世纪，年，月，日，时，分和秒。它典型地用来表示什么时候事情已经发生或将要发生。DATE数据类型表示时间的最小单位是秒。
DATE类型在数据库中的实际存储固定为7个字节，格式分别为：
第1字节：世纪
第2字节：年
第3字节：月
第4字节：天
第5字节：小时
第6字节：分
第7字节：秒

Date类型的数据可以显示到年月日，也可以显示到年月日时分秒，主要看存储数据的精确度
1）存储年月日只显示年月日
2）没有存时分秒，或者时分秒位00:00:00，也都只显示年月日
3）存储年月日时分秒才会显示年月日时分秒

2.TIMESTAMP
TIMESTAMP表示时间戳，与DATE的区别是不仅可以保存日期和时间，还能保存小数秒，可指定为0-9位，默认6位，最高精度可以到ns(纳秒)级别。
数据库内部用7或者11个字节存储，精度为0时，用7字节存储，与DATE功能相同，精度大于0则用11字节存储。格式为：
第1字节-第7字节：和DATE相同
第8-11字节：纳秒

例：
CREATE TABLE testTime(
d1 DATE,
d2 TIMESTAMP
);

3.SYSDATE

SYSDATE本质是一个Oracle的内部函数，用来返回当前的系统时间，精确到秒。但该函数默认显示格式是DD-MON-RR，只有年月日并不显示时分秒。
例如：
SELECT SYSDATE FROM DUAL;
显示格式与数据库所在操作系统有关 英文环境显示 ‘01- Sep-89’ 中文环境显示’01-9月-89’

如果想显示时分秒，需要用日期处理函数将格式转换一下：
SELECT TO_CHAR(SYSDATE,‘yyyy-mm-dd day hh24:mi:ss’) FROM DUAL;

在建表时，可以将系统时间SYSDATE作为某一列的默认值，当插入新的记录，该列如果没有指定值，将会取当时的系统时间该列数据保存起来。
例如用户表，用户的注册时间列默认取值为该数据记录插入的时间：
CREATE TABLE user(
id NUMBER(6),
name CHAR(20),
registerTime DATE DEFAULT SYSDATE
);

4. SYSTIMESTAMP
   SYSTIMESTAMP也是Oracle的内部日期函数，返回当前系统日期和时间，精确到毫秒。

例如：
SELECT SYSTIMESTAMP FROM DUAL;
----FF3显示TIMESTAMP的3位小数秒信息
SELECT TO_CHAR(SYSTIMESTAMP ,‘MM/DD/YYYY HH24:MI:SS:FF3’) FROM DUAL;

5.日期转换函数
日期数据有时需要和字符串数据相互转换，需要用到日期转换函数，包括TO_CHAR和TO_DATE。

TO_DATE
TO_DATE的功能是将字符串按照定制格式转换为日期类型:
TO_DATE(char[, fmt])
char是要转换的字符串，fmt是转换格式。

–例：给testTime表插入一条数据，c1列时间值为 2012 年 10 月 1 日
INSERT INTO testTime(c1) VALUES(‘01-10月-12’);–这种时间格式不符合中国常用时间表示格式
–按指定时间格式插入数据
INSERT INTO testTime(c1) VALUES(to_date(‘2012-10-1’,‘yyyy-MM-dd’));

日期格式
常用的日期格式如下：

RR日期格式

TO_CHAR
转换函数的作用是把日期数据转换为字符数据，常用于格式化显示日期数据：
to_char(日期数据 , 格式)

例如：按年-月-日 时：分：秒方式显示employee_xxx表中的员工的入职时间
select ename,to_char(hiredate,‘yyyy-MM-dd HH24:MI:SS’) from employee_xxx;

6.日期常用函数

ADD_MONTHS
ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值.
其中：
参数i可以是任何数字，大部分时候取正值整数
如果i是小数，将会被截取整数后再参与运算
如果i是负数，则获得的是减去i个月后的日期值

例如计算职员入职10周年纪念日：
SELECT ename, ADD_MONTHS(hiredate, 10 * 12) “10周年” FROM employee_xxx;

计算出去年今天的时间值
SELECT add_months(SYSDATE,-12) FROM dual;

MONTHS_BETWEEN
MONTH_BETWEEN(date1, date2)：
计算date1和date2两个日期值之间间隔了多少个月，是date1-date2，如果date2时间比date1晚，会得到负值。
除非两个日期间隔是整数月，否则会得到带小数位的结果.
比如计算2015年7月7日到2014年7月8日之间间隔多少个月，会得到11.99个月。
例如计算职员入职多少个月：
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate) hiredate FROM employee_lzh;

LAST_DAY
LAST_DAY(date)
返回日期date所在月的最后一天，一般是在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处。例子：
–查询当月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
–查询12年2月的最后一天
SELECT LAST_DAY(‘20-2月-12’) FROM DUAL;
NEXT_DAY

NEXT_DAY(date, char)
返回date日期数据的下一个周几，周几是由参数char来决定的。
在中文环境下，直接使用”星期三”这种形式，英文环境下，需要使用”WEDNESDAY”这种英文的周几。
为避免麻烦，可以直接用数字1-7表示周日-周六。

需要注意的是NEXT_DAY不要按字面意思理解为明天。查询下个周一是几号，如果本周的周一还没到则返回本周的周一，如果已经过了则返回下周的周一：
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;
LEAST和GREATEST
比较函数LEAST和GREATEST语法如下：
GREATEST(expr1[, expr2[, expr3]]…)
LEAST(expr1[, expr2[, expr3]]…)
两个函数都可以有多个参数值，但参数类型必须一致，返回结果是参数列表中最大或最小的值。
在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。

例：
SELECT LEAST(SYSDATE, ‘15-10月 -08’) FROM DUAL;
EXTRACT

EXTRACT(date FROM datetime)
从参数datetime中提取参数date指定的数据，比如提取年、月、日。
例如取出当前日期的年：
SELECT EXTRACT(YEAR FROM SYSDATE) current_year FROM DUAL;

取出指定时间的小时：
SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2015-1-1 10:10:10’) FROM DUAL;
TIMESTAMP '2015-1-1 10:10:10’的作用是将char类型数据转换成TIMESTAMP 类型
日期相减
日期数据相减 , 得到两个日期之间的天数差 , 不足一天用小数表示。

如：计算员工入职多少天?
SELECT ename , hiredate , ( sysdate - hiredate ) days FROM employee_xxx;

可以用 round 函数处理
select ename , hiredate , round( sysdate - hiredate ) days from employee_xxx;