oracle 11g SQL 语句和函数

1. SQL 简介

SQL 支持下列类别的命令：

数据定义语言（DDL）：create, alter, drop
数据操纵语言（DML）：insert, delete, update, select
事务控制语言（TCL）: commit, rollback, savepoint
数据控制语言（DCL）: grant, revoke

2. Oracle 数据类型

如下是 Oracle 数据类型的类别：

2.1 字符数据类型

常用的字符类型有 CHAR，BARCHAR2 和 LONG 类型：

2.1.1 CHAR 类型

当需要固定长度的字符串时，使用 CHAR 数据类型
CHAR 数据类型存储字母数字值
CHAR 数据类型的列长度可以是 1 到 2000 个字节
oracle 同样支持 NCHAR 类型

2.1.2 VARCHAR2 类型

VARCHAR2 数据类型支持可变长度字符串
VARCHAR2 数据类型存储字母数字值
VARCHAR2 数据类型的大小在 1 至 4000 个字节范围内
Oracle 同样支持 VARCHAR 类型，不过并不推荐使用

2.1.3 LONG 类型

LONG 数据类型存储可变长度字符数据
LONG 数据类型最多能存储 2GB

2.2 数值数据类型

数值数据类型可以存储整数、浮点数和实数。最高精度则是 38 位，范围：负的 10^-38 到 10³⁸ 之间。

数值数据类型的声明语法:

1	NUMBER[(p[,s])] -- P 表示精度， S 表示小数点的位数

示例1：

SQL> create table t2(sid number(3, 2));

SQL> insert into t2 values(123.22);
insert into t2 values(123.22)
                      *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

SQL> insert into t2 values(1.22);

已创建 1 行。

由上述示例可以说明，P 并非表示小数点前面的位数，而是表示总体数值的位数，也就是精度。

示例2：

SQL> insert into t2 values(1.569);

已创建 1 行。

SQL> select * from t2;

       SID
----------
      1.22
      1.57

还是使用示例1创建的表，这里我们插入数 1.569，可以发现 Oracle 会自动进行四舍五入的操作。

需要说明的是，如果没有设置小数点的位数，此时只能插入整数。若插入了浮点数，则会进行四舍五入并进行截断处理。

2.3 日期数据类型

日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟和秒。主要的日期类型有：

DATE：存储日期和时间部分，精确到整个的秒
TIMESTAMP：存储日期、时间和时区信息，秒值精确到小数段后 6 位。

示例如下（24小时制时间输出）：

SQL> select to_char( sysdate, 'yyyy-mm-dd hh24:mi:ss' ) from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2020-09-22 22:26:46
        
SQL> select to_char( systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6' ) from dual;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DDHH24:MI:SSXFF6')
----------------------------------------------------------
2020-09-22 22:27:48.818000

2.4 二进制数据类型

RAW 数据类型用于存储二进制数据
RAW 数据类型最多能存储 2000 字节
LONG RAW 数据类型用于存储可变长度的二进制数据
LONG RAW 数据类型最多能存储 2GB

2.5 大对象数据类型

LOB 称为“大对象”数据类型，可以存储多达 128TB 的非结构化信息，例如声音剪辑和视频文件等（LOB 类型的容量从原来的 4G 增加到了最大 128T）。LOB 数据类型允许对数据进行高效、随机和分段的访问。

类型主要有：

CLOB：用于存储大量字符数据
BLOB ：存储较大的二进制对象，如图形、视频剪辑和声音文件
BFILE ：用于将二进制数据存储在数据库外部的操作系统文件中

2.6 伪列

Oracle 中伪列就像一个表列，但是它并没有存储在表中，伪列可以从表中查询，但不能插入、更新和删除它们的值。常用的伪列有ROWID和ROWNUM。

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行
ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

示例如下：

SQL> select a.*, rowid, rownum from t2 a;

       SID ROWID                  ROWNUM
---------- ------------------ ----------
      1.22 AAASNlAAEAAAAIfAAA          1
      1.57 AAASNlAAEAAAAIfAAB          2

3. 数据语言

3.1 数据定义语言

数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象。

用于操纵表结构的数据定义语言命令有：

CREATE TABLE：创建表
ALTER TABLE：修改表
TRUNCATE TABLE：删除表数据，但是不删除表结构。删除后不记录进日志，谨慎使用
DROP TABLE：删除表，包括数据和结构。删除后记录进日志。

示例如下：

-- 创建表
create table student(sid varchar2(11), name varchar2(20));
-- 添加列，注意不需要 column
alter table student add age number(3);
-- 修改列
alter table student modify name varchar2(50);
-- 删除列，注意需要 column
alter table student drop column age;
-- 重命名列
alter table student rename column name to sname;
-- 重命名表
rename student to students;

3.2 数据操纵语言

数据操纵语言用于检索、插入和修改数据

3.2.1 select 命令

利用现有的表创建表:

1	语法：CREATE TABLE <new_table_name> AS SELECT column_names FROM <old_table_name> [where 子句];

-- 利用现有的表创建表，包含指定数据
create table newstudent as select * from students;
-- 如若只想利用现有的表创建表，且不包含数据的话，可参考如下语句
create table newstudent as select * from students where 1 > 2;

3.2.2 insert 命令

这里需要说明的是，Oracle 的时间默认格式为 “DD-MON-RR”。所以在插入时间的值时，有如下两种方法:

使用日期的默认格式
使用TO_DATE函数转换

1 2	insert into students values(20200102, '李四', '22-9月-20'); insert into students values(20200102, '李四', to_date('20200923','yyyymmdd'));

插入来自其它表中的记录：

1	insert into newstudents select * from students;

4. 操作符

4.1 连接操作符

连接操作符用于将多个字符串或数据值合并成一个字符串，示例如下。由示例结果可以看出通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列。

SQL> select * from newstudents;

SID                    SNAME                                                                                                BITRH
---------------------- ---------------------------------------------------------------------------------------------------- --------------
20200101               zhangsan
20200102               李四                                                                                                 23-9月 -20
20200101               zhangsan

SQL> select (sname || '的学号为' || sid || ', 生日是在' || birth) info from students;

INFO
---------------------------------------------------------------------------------------------------------------------------------------------
李四的学号为20200102, 生日是在23-9月 -20
zhangsan的学号为20200101, 生日是在

5. Oracle 函数

Oracle 提供一系列用于执行特定操作的函数，SQL 函数带有一个或多个参数并返回一个值。SQL 函数包含单行函数、分组函数和分析函数三种。

5.1 单行函数

单行函数对于从表中查询的每一行只返回一个值，可以出现在 SELECT 子句中和 WHERE 子句中。单行函数可以大致划分为：

字符函数
日期时间函数
数字函数
转换函数
混合函数

5.1.1 字符函数

函数	输入	输出	描述
Initcap(char)	Select initcap(‘hello’) from dual;	Hello	首字母大写
Lower(char)	Select lower(‘FUN’) from dual;	fun	全小写
Upper(char)	Select upper(‘sun’) from dual;	SUN	全大写
Ltrim(char,set)	Select ltrim( ‘xyzadams’,’xyz’) from dual;	adams	切掉左侧首次连续出现的指定字符
Rtrim(char,set)	Select rtrim(‘xyzadams’,’ams’) from dual;	xyzad	切掉右侧首次连续出现的指定字符
Translate(char, from, to)	Select translate(‘jack’,’j’ ,’b’) from dual;	back	字符替换
Replace(char, searchstring,[rep string])	Select replace(‘jack and jue’ ,’j’,’bl’) from dual;	black and blue	字符替换
Instr (char, m, n)	Select instr (‘worldwide’,’d’) from dual;	5	字符出现的坐标，从1开始计算
Substr (char, m, n)	Select substr(‘abcdefg’,3,2) from dual;	cd	获取子串
Concat (expr1, expr2)	Select concat (‘Hello’,’ world’) from dual;	Hello world	拼接子串
CHR(n)	select chr(97) from dual;	a	返回 ASCII 值对应的字符
ASCII(char)	select ascii(‘a’) from dual;	97	返回字符对应的 ASCII 值
LENGTH(char)	select length(‘你好’) cn, length(‘aa’)en from dual;	2 , 2	返回字符长度，不区分中文和英文
[ { { LEADING \| TRAILING \| BOTH } [ trim_character ]	select trim(9 from 999987996799999) from dual;	879967	删除两侧或某一侧指定字符，无参数则默认删除空格

其他：

lpad( string1, padded_length, [ pad_string ] ) 函数：

string1：源字符串；
padded_length: 即最终结果返回的字符串的长度；如果最终返回的字符串的长度比源字符串的小，那么此函数实际上对源串进行截取处理，与substr(string,number1,number2)的作用完全相同，如果padded_length比源字符串的长度长，则用pad_string进行填充，确保返回的最终字符串的长度为padded_length；
pad_string:用于填充的字符，可以不填，默认为空字符。

示例如下：

select lpad('123456',2) from dual --结果为 12
select lpad('123456',7,'0') from dual --结果为 0123456
select rpad('123456',2,'0') from dual --结果为 12
select rpad('123456',7,'0') from dual --结果为 1234560

可以看到，当string1源字符串的长度小于padded_length时，lpad、rpad的作用是相同的，都是相当于substr截取字符串，当padded_length大于string1源字符串的长度时，lpad为在源字符串的左侧填充指定字符或者空格，rpad为在源字符串的右侧填充指定字符或者空格；

5.1.2 日期时间函数

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果。日期函数包括：

ADD_MONTHS(date, num)：进行月份的增加或减少
MONTHS_BETWEEN：计算两个日期之间有几个月
LAST_DAY：获取指定月的最后一天的日期
ROUND：对日期进行四舍五入
NEXT_DAY：从当前日期开始得到未来星期几的日期
TRUNC：见如下示例
EXTRACT：语法如下：

EXTRACT (  
        { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }  
        | { TIMEZONE_HOUR | TIMEZONE_MINUTE }  
        | { TIMEZONE_REGION | TIMEZONE_ABBR }  
FROM { date_value | interval_value } )

示例如下：

1 2	-- 1. add_months方法 select add_months(sysdate, -1) from dual;

-- 2. months_between 方法
SQL> select months_between(to_date('2020-08-21', 'yyyy-mm-dd'), to_date('2020-10-23', 'yyyy-mm-dd')) mulmonth from dual;

  MULMONTH
----------
-2.0645161

1 2	-- 3. last_day 方法 select last_day(sysdate) from dual;

--4. round 方法
-- 根据月份对年进行四舍五入, 1-6 月舍，7-12 月入
SQL> select round(to_date('2020-09-12', 'yyyy-mm-dd'), 'year') from dual;

ROUND(TO_DATE(
--------------
01-1月 -21

-- 根据日对月份进行四舍五入， 1-15 日舍，16-31 月入
SQL> select round(to_date('2020-09-16', 'yyyy-mm-dd'), 'month') from dual;

ROUND(TO_DATE(
--------------
01-10月-20

1 2	-- 5. next_day 方法 select next_day(to_date('2020-09-26','yyyy-mm-dd'),'星期日') from dual;

-- 6. trunc 方法
-- 日期时间处理
select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确  
-- 数字处理
TRUNC(number, num_digits)：函数截取时不进行四舍五入
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
select trunc(123.458) from dual --123
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120
select trunc(123.458,-4) from dual --0
select trunc(123.458,4) from dual --123.458
select trunc(123) from dual --123
select trunc(123,1) from dual --123
select trunc(123,-1) from dual --120

-- 获取截断的日期时间
select extract(year from date'2011-05-17') year from dual;  
      YEAR  
----------  
      2011  
select extract(month from date'2011-05-17') month from dual;  
     MONTH  
----------  
         5  
select extract(day from date'2011-05-17') day from dual;  
       DAY  
----------  
        17 

-- 获取时间间隔
select extract(day from dt2-dt1) day, extract(hour from dt2-dt1) hour, extract(minute from dt2-dt1) minute, extract(second from dt2-dt1) second  
from (  
     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1  
           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2  
     from dual);  
 
       DAY       HOUR     MINUTE     SECOND  
---------- ---------- ---------- ----------  
       102          4          1         46

5.1.3 数字函数

函数	输入	输出	描述
Abs(n)	Select abs(-15) from dual;	15	取绝对值
Ceil(n)	Select ceil(44.778) from dual;	45	向上取整
Cos(n)	Select cos(180) from dual;	-.5984601	求 cos 函数值
Cosh(n)	Select cosh(0) from dual;	1	求双曲函数值
Floor(n)	Select floor(100.2) from dual;	100	向下取整
Power(m,n)	Select power(4,2) from dual;	16	求 mⁿ 的值
Mod(m,n)	Select mod(10,3) from dual;	1	取余
Round(m,n)	Select round(100.256,2) from dual;	100.26	指定精度四舍五入
Trunc(m,n)	Select trunc(100.256,2) from dual;	100.25	截断不四舍五入
Sqrt(n)	Select sqrt(4) from dual;	2	求平方根
Sign(n)	Select sign(-30) from dual;	-1	函数返回一个数字的正负标志，负数返回-1，正数返回1，0 返 0

5.1.4 转换函数

转换函数将值从一种数据类型转换为另一种数据类型，常用的转换函数有：to_char, to_date 和 to_number。

5.2 集合操作符

UNION：求两个集合的并集
UNION ALL：求两个集合的并集（包含重复的记录）
INTERSECT：求两个集合的交集
MINUS：求两个集合的差集