orcal学习

SQL语言类

  1. DDL（数据定义语言）：  create（创建）    alter（修改）  drop（删除）————主要是对表操

  2. DML（数据操作语言）：  insert(插入)    delete(删除)  update(更新)  select(查询) 、select……for update(查询更新)—-表中数据进行操作

  3. DQL数据查询语言：基本语句、Order by 子句、Group by 分组语句

  4. TCL（事务控制语言）：Commit(提交) 、Savepoint(保存) 、rollback(回滚)、

  5. DCL（数据控制语言）：Grant(授权)、revoke(撤销)命令。

Orcal数据类型

Char:  字符型（最大长度2000，定长、不足时以空格补充）rchar2:字符型 最大长度 4000，变长，实际长度由存储的数据长度决定（与存储的数据长度一致）

Number(x,y):既可以存储浮点型，也可以存储整形，x表示有效位数的最大位数，y表示小数位最大位数。

Date：存储时间类型。默认格式：dd–mm–yy:  天-月-年。

Clob:存储较大的文本，比如存储非结构化XML文档，最大为 4G

Blob:存储二进制对象，如图形、视频、声音等。

Long :存储较长字符串，最大长度为2G

表操作

  1. 创建表（同mysql）
1
2
3
Creat talbe testTable(
字段名 类型 约束
)
  1. 修改表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
添加字段
Alter  table  表名 add( 字段名称  数据类型 )
alter table testTable(test_name varchar2(200))
删除字段
Alter table 表名 set unused column 列名
Alter table testTable set unused column test_name
一般：不建议删除数据库中的列。
修改字段名
Alter table 表名 rename column 旧列名 to 新列名
Alter table testTable rename column old_name to new_name
修改字段属性
Alter talbe 表名 modify(字段名 要修改的字段属性)
Alter table testTable modify(test_name varchar(3000))
修改字段约束
alter table test_tb1 add constraint  p_k primary key(test_num)-----主键约束
alter table test_tb1 add constraint c_k check(msg='男' or msg='女')---检查约束
Not null----非空约束
修改表名
Rename  表名1 （旧名字）to 表名2（新名字）
例子：rename  test_tb to test_tb1

DML操作

查询

  • 简单案例

Select *|列名|表达式 from 表名 where 条件 order by 列名

1.* 表示所有列

2.列名可以选择若干个表中列名，各个表中列名用逗号分隔。

3.表达式可以是函数，列名，常数等组成表达式。

4.Where子句是查询的条件

5.Order by 要求在查询结果中排序，默认是升序。

查询方式类似mysql

  • select * from emp order by sal desc（降序）
  • select * from emp order by sal （升序）
  • select * from emp order by sal asc(升序)

插入

插入：

Insert into 表名  values(所有字段对应值)；

Insert into 表名（字段名1,字段名称2….） values(对应字段值)

例子：insert into test_tb1(test_num,msg) values(666,'MSG values')

更新

Update 表名 set 字段名称 =值 where 更新条件

例子：update test_tb1 set msg='hello oracle' where test_num=123

删除

  • Delete 表名 where 条件

例子：delete test_tb1 where msg='hello oracle'

  • Truncate–将表中数据一次性删除

语法： truncate table 表名

Truncate和detele区别：

  1. truncate是DDL命令，删除数据不能回复；delete是DML命令，删除数据可以通过数据库的日志文件进行恢复。
  2. 如果一个表中记录很多，truncate相对delete速度快。

警告：由于truncate命令比较危险，所有在实际开发中，truncate命令慎用。

函数

Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。 Oracle 数据库中主要使用两种类型的函数：

字符串函数

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

函数 示例 说明
ASCII(X) SELECT ASCII('a') FROM dual; 返回字符X的ASCII码
CONCAT(X,Y) SELECT CONCAT('Hello','world') FROM dual; 连接字符串X和Y
INSTR(X,STR[,START][,N) SELECT INSTR('Hello world','or') FROM dual; 从X中查找str，可以指定从start开始，也可以指定从n开始
LENGTH(X) SELECT LENGTH('Hello') FROM dual; 返回X的长度
LOWER(X) SELECT LOWER('Hello') FROM dual; X转换成小写
UPPER(X) SELECT UPPER('hello') FROM dual; X转换成大写
LTRIM(X[,TRIM_STR]) SELECT LTRIM('=Hello=','=') FROM dual; 把X的左边截去trim_str字符串，缺省截去空格
RTRIM(X[,TRIM_STR]) SELECT RTRIM('=Hello=','=') FROM dual; 把X的右边截去trim_str字符串，缺省截去空格
TRIM([TRIM_STR FROM]X) SELECT TRIM('='FROM'=Hello=') FROM dual; 把X的两边截去trim_str字符串，缺省截去空格
REPLACE(X,old,new) SELECT REPLACE('ABCDE','CD','AAA')FROM dual; 在X中查找old，并替换成new
SUBSTR(X,start[,length]) SELECT SUBSTR('ABCDE',2,3) FROM dual; 返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾

数字函数

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

函数 说明 示例
ABS(X) X的绝对值 ABS(-3)=3
ACOS(X) X的反余弦 ACOS(1)=0
COS(X) 余弦 COS(1)=0.54030230586814
CEIL(X) 大于或等于X的最小值 CEIL(5.4)=6
FLOOR(X) 小于或等于X的最大值 FLOOR(5.8)=5
LOG(X,Y) X为底Y的对数 LOG(2，4)=2
MOD(X,Y) X除以Y的余数 MOD(8，3)=2
POWER(X,Y) X的Y次幂 POWER(2，3)=8
ROUND(X[,Y]) X在第Y位四舍五入 ROUND(3.456，2)=3.46
SQRT(X) X的平方根 SQRT(4)=2
TRUNC(X[,Y]) X在第Y位截断 TRUNC(3.456，2)=3.45
  • 说明
  1. ROUND(X[,Y])，四舍五入。

在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。

y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

  1. TRUNC(x[,y])，直接截取，不四舍五入

在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。

Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。、

日期函数

日期函数对日期进行运算。常用的日期函数有：

  1. ADD_MONTHS(d,n) ，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。

    • d 表示日期，n 表示要加的月数。
    • 例：SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

  2. LAST_DAY(d)，返回指定日期当月的最后一天

    • 例：SELECT SYSDATE,last_day(SYSDATE) FROM dual;

  3. ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是date， fmt 是格式

    模型。默认 fmt 为 DDD，即月中的某一天。

    • 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
    • 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
    • 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
    • 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
      • 例：SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;
    • 与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。
    1
    2
    3
    4
    5
    6
    select  trunc(sysdate,'yyyy') from dual;--返回当年第一天.
    select  trunc(sysdate,'mm')   from dual; --返回当月第一天.
    select  trunc(sysdate,'d')   from dual;-返回当前星期的第一天.
    select  trunc(sysdate,'dd')  from dual;-返回当前年月日
    select  trunc(sysdate, 'hh')  from dual;--返回当前小时
    select  trunc(SYSDATE, 'mi')  from dual;--返回当前分钟

  4. EXTRACT(fmt FROM d)，提取日期中的特定部分。

    • fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
    • HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT SYSDATE "date",

           EXTRACT(YEAR FROM SYSDATE)"year",

           EXTRACT(MONTH FROM SYSDATE)"month",

           EXTRACT(DAY FROM SYSDATE)"day",

           EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",

           EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",

           EXTRACT(SECOND FROM SYSTIMESTAMP)"second"
           
           FROM dual;

转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有

  • TO_CHAR(d|n[,fmt])

把日期和数字转换为制定格式的字符串。Fmt是格式化字符串

例：SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

针对数字的格式化，格式化字符有：

参数 示例 说明
9 999 指定位置处显示数字
, 99,99 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字式负数则用尖括号进行表示

代码演示：TO_CHAR对数字的处理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

  • TO_NUMBER(X,[,fmt])

把一个字符串以fmt格式转换为一个数字

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

日期转化函数

  • TO_DATE(X,[,fmt])

把一个字符串以fmt格式转换成一个日期类型

在使用Oracle的to_date函数来做日期转换时，很多Java程序员也许会直接的采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。

to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss')

原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。

to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss')

另要以24小时的形式显示出来要用HH24 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
to_date(sysdate,'yyyy-MM-dd HH24:mi:ss') //mi是分钟
to_date(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份  oracle中的to_date参数含义

D 一周中的星期几  
DAY 天的名字，使用空格填充到9个字符  
DD 月中的第几天  
DDD 年中的第几天  
DY 天的简写名  
IW ISO标准的年中的第几周  
IYYY ISO标准的四位年份  
YYYY 四位年份  
YYY,YY,Y 年份的最后三位，两位，一位  
HH 小时，按12小时计  
HH24 小时，按24小时计  
MI 分  
SS 秒  
MM 月  
Mon 月份的简写  
Month 月份的全名  
W 该月的第几个星期  
WW 年中的第几个星期  1.日期时间间隔操作 
//当前时间减去7分钟的时间 
select sysdate,sysdate - interval ’7’ MINUTE from dual 
//当前时间减去7小时的时间 
select sysdate - interval ’7’ hour from dual 
//当前时间减去7天的时间 
select sysdate - interval ’7’ day from dual 
//当前时间减去7月的时间 
select sysdate,sysdate - interval ’7’ month from dual 
//当前时间减去7年的时间 
select sysdate,sysdate - interval ’7’ year from dual 
//时间间隔乘以一个数字 
select sysdate,sysdate - 8 *interval ’2’ hour from dual

oracle有毫秒级的数据类型

  • –返回当前时间 年月日小时分秒毫秒

    select to_char(current_timestamp(5),’DD-MON-YYYY HH24:MI:SSxFF’) from dual;

  • –返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)

    select to_char(current_timestamp(9),’MI:SSxFF’) from dual;

其它单行函数

  • NVL(X,VALUE)

如果X为空，返回value，否则返回X

例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元 (已有奖金则不发)

SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;

  • NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2

例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元

SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm"

聚合函数

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值

名称 作用 语法
AVG 平均值 AVG（表达式）
SUM 求和 SUM(表达式)
MIN、MAX 最小值、最大值 MIN(表达式)、MAX(表达式)
COUNT 数据统计 COUNT（表达式）

查询增强

查询所有列资料或特定列资料

–查询订单产品,产品品名资料

SELECT oeb04,oeb06 FROM oeb_file;

distinct

消除查询结果的重复行

–查询订单产品,产品品名资料，且结果消除重复行(请对比SQL示例1)

SELECT DISTINCT oeb04,oeb06 FROM oeb_file;

别名及算术表达式

在SELECT查询资料列上使用算术表达式(+、-、*、/)及as应用使用列别名

–as指定列别名(列别名也可以不使用as,直接在查询列后面指定即可)，及SQL中使用乘法运算*

SELECT oeb01 as "订单单号", oeb04 as "订单产品",oeb06 as "产品品名",oeb12*oeb13 as "订单金额" FROM oeb_file

注意：如果取了别名,此别名是不能在WHERE 子句中引用的,必须使用原运算式或内嵌表解决

nvl函数

nvl函数或nvl2函数 处理算术表达式运算中栏位空值问题

如果查询的栏位参与+ - / *算术运算，只要参与运算的栏位有一个为空值，则会导致整个运算结果为空值

  • **nvl(expr1,expr2)**如果expr1不为空，则返回expr1，否则返回expr2；expr1与expr2可以是任意数据类型,但是expr1与expr2需是相同的数据类型
  • **nvl(expr1,expr2,expr3)**如果expr1不为空，则返回expr2，否则返回expr3；expr1可以是任意数据类型,但是expr2与expr3需是相同的数据类型

–冲销数量ta_oeb013栏位有空值现象 ，ta_oeb013为本公司客制栏位(冲销数量)

SELECT oeb01 as "订单单号", oeb04 as "订单产品",oeb06 as "品名",oeb12-oeb24-ta_oeb013 as "订单未交量" FROM oeb_file

where 条件子句

  • 常用的WHERE条件如下：

    等于：= 不等于：<>，!= 大于：>

​ 大于或等于：>= 小于或等于：<= 小于：<

​ 匹配于列表值：IN( )

​ 在两值之间：BETWEEN .. AND ..

​ 测试是否为NULL：IS NULL (注意：”没有空格”、”一个或多个空格” 与 IS NULL 是不同的)

​ 匹配于字符样式：LIKE，执行模糊查询需要使用LIKE,常用LIKE通配符如下：

​ %：匹配0个或多个字符

​ _ ：匹配单个字符

​ 如果模糊查询本身要查的栏位资料里面包含有’%’或’_’字符时，则需要使用转义字符ESCAPE

​ 注意：如果某值是空则并不能用LIKE “%”匹配的到，写SQL查异常这点经常出错

  • 常用的WHERE条件逻辑操作符如下：

​ 逻辑与：AND，逻辑或：OR，取反：NOT

升降序

ORDER BY desc (升序排序:默认)，ORDER BY asc (降序排序)  将查询出来的资料排序

排序时也可以指定多列排序、非选择资料栏位排序、别名排序、列位置编号排序

例：

SELECT zx01,zx02 as "姓名",zx03,zx04 FROM zx_file WHERE zx03 = '2G69' or zx03 = '2G60' ORDER BY zx01 asc,"姓名",3 asc,zx09 desc –不同列排序必须分别指定排序规则

排序优化

通常我们会为排序添加rownum让整个查询结果从1开始列出名次，但在排序查询结果中并没有按名次排名,原因是rownum赋值是在排序之前。

sql：select q.QUEUEID,q."NAME",q."OWNER",ROWNUM from QUEUES q ORDER BY QUEUEID;

  • rownum并不是一个真实存在的数据列，而是一个随数据集生成而生成的伪数据列。rownum是oracle系统顺序分配为从查询返回的行的编号，oracle在select出一条记录后便加上一个rownum，而不等所有的结果都select出来后再加上rownum。

解决方法：

  1. 可以使用嵌套子查询

sql:select q1.queueid,Q1."NAME" ,ROWNUM FROM (select q.QUEUEID,q."NAME",q."OWNER",ROWNUM from QUEUES q) q1 ORDER BY Q1.QUEUEID

结果正确，不过在官方文档中，Oracle推荐使用分析函数来解决序号问题，根据不同的实际需求，可以使用row_number()、rank()和dense_rank()几个选择。

  1. row_number() 根据order by顺序排出一个名次，不会出现并列排名 1 2 3 4 5

    rank() 根据order by排序，会出现并列排名，下一个值会跳过并列值 比如 1 2 2 4 5

    DENSE_RANK() 根据order by排序，出现并列排名以后，下一个值不跳过并列值 1 2 2 3 4

分析函数使用：

分析函数最大的一个功能是可以使用partition可选参数，用来在其中分组，这三个分析函数都可以在各个分组内从1开始排序

语法：ROW_NUMBER()|RANK()|DENSE_RANK() OVER(PARTITION BY COLUMN ORDER BY COLUMN ASC|DESC) 

1
2
3
4
5
6
7
8
9
10
11
SELECT
	q.QUEUEID,
	q."NAME",
	q."OWNER",
	ROW_NUMBER () OVER (ORDER BY q.QUEUEID),
	RANK () OVER (ORDER BY q.queueid),
	DENSE_RANK () OVER (ORDER BY q.queueid)
FROM
	QUEUES q
ORDER BY
	QUEUEID;

关联查询

基于2个或2个以上的视图或表的查询

说明：

  1. 必须在FROM子句后接上2个或2个以上的表或视图
  2. 查询的资料列如果在FROM子句后面所捷的表或视图有多个,则必须在查询的资料栏位前面加上表或视图名作为首码,否则会引起意性错误
  3. 当使用连接查询时必须在WHERE子句中指定连接的条件,否则会产生笛卡尔积(X*Y)
  4. 使用别名可以简化SQL的编写,别名必须跟在表或视图的后面

简单链接查询

FROM子句接 表或视图，WHERE子句指定连接条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
----查询tiptop GP系统用户帐号和部门信息
SELECT zx_file.zx01,zx_file.zx02,zx_file.zx03,gem_file.gem02   
  FROM zx_file,gem_file  
 WHERE zx_file.zx03 = gem_file.gem01  
--等同于
----查询tiptop GP系统用户帐号和部门信息
SELECT zx01,zx02,zx03,gem02   
  FROM zx_file,gem_file  
 WHERE zx03 = gem01
ZX01      ZX02     ZX03     GEM02

-------    -------     -----           ---------
terry       李帅        2G69     		  海外资讯
zhanna      詹妹        2G89 			  会计部

内连接INNER JOIN

相关链接：https://www.cnblogs.com/misswjr/p/9437170.html

相关链接：https://blog.csdn.net/u011955534/article/details/14168913

参考链接：https://blog.csdn.net/idomyway/article/details/78785112

SQL
#orcal
orcal学习
https://andrewjiao.github.io/2019/10/24/Orcal/
作者
Andrew_Jiao
发布于
2019年10月24日
许可协议