Oracle语法整理

前言

今天在整理资料的时候看到的，就拿过来记录一下，内容比较多，就不一一调整了。

内容

sql分为：数据定义语言DDL 数据操作语言DML 数据查询语言DQL 数据控制语言DCL 事务控制语言TCL 其中最主要的是数据查询语言，当进行数据操作语言时会自动进行事务控制。
数据库有sql标准，但是不同的数据库会有自己的sql语言，即普通话与方言，，，，
写数据库sql时，建议关键字大写，非关键字小写
数据库的对象：表、视图、索引、序列 --ddl操作数据库对象

一、语法

1.不等于
<>,!=,~=,^=

2.null
oracle 数据库中对null的判断只能是is null或is not null
当某个字段中有null值时，若只是单纯的判定<>***，比如name<>zs是不行的，因为搜索出来的是不为null的记录，若要将null也查出来，需要写name<>zs or name is null
null与数字运算结果为null
null与字符串拼接表示什么都没干还为字符串

空值函数
NVL（a，b）
当a为null时，返回b，当a不为bull时返回a

NVL2（a,b,c）
当a不为null时，返回b，当a为null时返回c

3.if
1)
if *** then

---

end if;
2)
if *** then

---

else

---

end if;
3)
if *** then

---

elsif *** then

---

end if;
注意：3中的是elsif，而不是else if

4.varchar2类型
只有Oracle有varchar2,最大长度4000字节，与varchar相比就只相差一个2.

5.DATE类型
Java中是八个字节的long类型存时间
数据库不一样，是用七个字节存时间，分别表示世纪、年、月、日、时、分、秒 精度只能到秒

6.desc
查看表结构
desc tableName

7.default默认值
数据库中所有的数据类型默认值都是null，如果不想默认为null，可以使用default添加默认值

8.大小写敏感问题
数据库中对sql语句本身是不区分大小写的，即大小写不敏感，但是字符串中的字符是区分的，即‘M’与’m’是不一样的

9.sql字符串
sql中字符串是用单引号引起来的

10.not null
某字段非空使用该关键字

11.修改表名
rename a to b

12.=
在java中判定等于使用==
在sql中只用= 比如：where id=1

13.varchar2
必须加长度
char可以不加，默认为1

14.long
varchar2的加长版，存储变长字符串，最大长度2G，一张表只能有一个long类型字段，它有很多限制，比如不能作为主键，不能加索引，不能出现在查询条件中

15.clob
存储定长或变长字符串，最长达4G的字符串数据

16.select
后面的内容可以为表中的具体字段，也可以是函数，也可以是表达式

17.concat（）函数
用来连接字符串，比如用来将两个查询出来的字段连接在一起

18.upper（），lower（），initcap（）
upper（）将字符串转换为大写
lower（）将字符串转换为小写
initcap（）将字符串首字符大写，用空格隔开多个单词，每个单词首字符都会大些

19.trim（）
截去字符串
截掉字符串两边的指定字符，比如trim（c2 from c1） 从c1两边截去c2，如果没有c2，则截去空格，并且c2只能是单一字符
ltrim（）、rtrim（），LTRIM（c1，c2）将c1左侧的e去掉，若没有c2则去除空格
注：Ltrim()和rtrim（）有一个和trim（）不同的地方，c2可以是多个字符，且c1中满足c2中任意一个单一字符都会去除掉c1中的对应字符，
比如select ltrim(‘eessryyteeeeessr’，‘esr’) from dual; 查询出来的结果是yyteeeeessr

20.lpad(),rpad()
补位函数
比如select lpad（name，6，‘ ’） from emp，查询emp中name，如果name位数不足6位，则在它的左边加上空格。实现右对齐效果

21.substr
截取字符串
比如：substr（‘hello java’，7，4）结果为java，第一个参数位需要截取的字符串，第二个是从那个字符开始，若是负数，则表示从倒数第几个开始截取，第三个是截取长度，可以不写，不写则表示截取到末尾
注：数据库中下标从1开始，Java中从0开始

22.instr（char1，char2【，m，n】）
表示查找char2在char1中的位置，m表示第几个开始，n表示，第几次出现，m、n都不写则默认为1，若找不到返回0

23.round（m，n）
四舍五入
m表示数字，m表示精度，可以为负数，也可以为0，0表示整数，1表示小数点后一位，-1表示十位

24.trunc（m，n）
截取数字
m表示数字，n表示位数
trunc（345.67，1）结果345.6
trunc（345.67，0）结果345
trunc（345.67，-1）结果340

25.mod（m，n）
求余数，此时n可以为o，为o表示什么都没干，返回m

26.ceil、floor
向上取整和向下取整
select ceil(45.67) from dual;结果46
select floor(45.67) from dual;结果45

27.sysdate/systimestam
返回当前系统时间date类型和当前系统时间时间戳类型

28.to_date(‘a’,‘b’)
将字符串a按照给定的b格式进行解析成日期
b有YY、YYYY.MM.DD HH24 HH12 MI SS
b中如果有除了字母，数字，符号之外的字符出现，比如‘年月日’，必须用双引号括起来““；

29.日期的计算
日期可以与一个数字进行加减法，表示加上指定的天数，即日期+1等于日期加一天
两个日期相减，得到相差的天数

30.to_char（）
将日期转为字符串

注：两位数rr关注实际，将字符串两位数的年转为日期年时。有以下规则
user\sys 0-49 50-99

0-49 本世纪 下世纪

50-99 上世纪 本世纪

31.last_day(date)
返回给定日期月底的日期，比如 select last_day(2019-3-24) from dual 结果为2019-3-31
运用场景：月底结账，需要知道月底的天数

32.add_mouth(date,i)
给指定日期加上i月，i为负数就是减去

32.mouth_between（date1，date2）
给定日期相差月数，计算规则时date1-date2得到

33.next_day(date,i)
返回给定日期第二天开始一周之内的指定周几的日期，1为周天，7为周六
也就是指明天开始，最近的一个周i，

34.least（date1，date2，date3……），greatest（）
求最小日期和最大日期

35.别名
若想别名区分大小写或加空格，给别名加上双引号

36.any、all
any、all不能单独使用，需要和<、<=、>、>=配合使用
例：

any（list）
any(200,346,700) 一班情况 list为不确定的值，用于子查询

37.distinct
去重，用于查询
在distinct前面不能写其他字段，无法匹配，
在distinct后面写其他字段，可对distinct后的全部字段进行组合去重

38.order by
用于排序，将数据按照order by后的字段排序，默认升序，
asc 升序
desc 降序
也可按照多个字段进行排序，不过有优先级，前面的先执行，后面的后执行，每个字段可单独设置排序方式，即升序还是降序
null被认定为最大值

39.聚合函数
也称为多行函数，组合函数，集合函数等。
max，min，sum，avg 数据统计
count 记录数统计
所有聚合函数都忽略null值

40.group by分组
凡是select后有聚合函数，又有其他字段，其他字段必须在group by子句后

41.where，having
where中不能使用聚合函数作为过滤条件是因为过滤时机不对。where是在数据库检索表中数据时对数据进行逐条过滤以决定是否使用该数据，所以where用来确定结果集的数据的
聚合函数的过滤应该是在where之后的，
having子句必须跟group by之后，having是用来过滤分组的

42.查询语句执行顺序
1).from子句：执行顺序，从后往前、从右往左
数据量少的表尽量放后面
2).where子句：自下而上、从右往左
将能过滤掉最大数量记录的条件写在where的最右边
3).group by
执行顺序从左往右分组
最好在group by前使用where 将不需要的记录在group by前过滤掉
4).having子句 消耗资源
尽量避免使用，having会在检索出所有记录之后才对结果集进行过滤，需要排序等操作
5).select子句
少用号，尽量使用字段名称
Oracle在解析时，通过查询数据字典将号依次转换为所有字段，消耗时间
6).order by子句
执行顺序从左往右，消耗资源

43.关联查询如果不加关联条件，会出现笛卡尔积，笛卡尔积一般情况下为无意义的结果集，他的记录数为参与查询的表的乘积，要避免出现，数据极大时极易出现内存溢出，宕机等现象

44.关联查询n张表至少要有n-1个查询条件

45.关联查询对于不满足连接条件的不会查询出来，可以用外连接，分为左外链接 left outer join、右外链接 right outer join、全外链接 full outer join
on e.name（+）=d.name 等价于右外链接 加号在哪边 那边就加null 全外链接无法通过这种方式实现

46.自链接
同一类别，且存在上下级关系，比如菜单
在同一张表的一条记录可以对于当前表的多条记录，采用自链接

47.子查询
可通过子查询快速创建新表，只要能够把需要创建的表的数据查出来，就可以直接创建，也可以修改字段名

48.子查询结果集分类
分为
单行单列子查询：常用于过滤条件，配合=，>,>=,<,<=使用
多行单列子查询：常用于过滤条件，判断=时使用in，判断>,<等时使用any，all
多行多列子查询：常看作一张表

49.exist
exists 后面跟一个子查询，当该子查询至少能查出一条记录时，则exists表达式成立并返回true

50.分页
分页查询时将查询表中数据时分时段查询，而不是一次性将所有数据查出来，优势查询的数据量非常庞大，会导致资源消耗大，响应速度慢，数据冗余严重，因此使用分页，数据库基本都支持分页查询，可惜都是方言，sql没有规定标准
Oracle的分页是基于伪劣rownum实现的，该字段的值是随着查询自动生成的，每当可以从表中查询出一条记录时，该字段的值即为该条记录的行号，从1开始递增。
在使用rownum对结果i集进行编号的查询过程中不能使用rownum做>1以上的数字判断，否则将查不出任何数据

计算区间公式
pagesize：每页显示的条目数
page：页数

star：（page-1）pageSize+1
end：pageSizepage

51.decode函数
decode（expr，va1,result1,va2,result2……default vn）
如果expr=va1,就返回result1，如果等于va2，返回result2，都不满足，返回vn
default可以不写，不写就返回null

decode 还可以用于将某些不一样的值替换为相同的值用group by统计

52.case
case expr when va1 then result1
when va2 then result2
……
else vn end

53.排序函数
排序函数允许等结果集按照指定的字段分组，在组内按照制定的字段排序，最后生成组内的编号
row_number()函数生成组内连续且唯一的数字
row_number() over(
partition by deptno//按dept分组
order by sal desc//按照sal倒叙排序
)

54.rank（）
生成组内不连续也不唯一的数字，组内排序字段值一样的记录，生成的数字也一样

55.dense_rank()
生成组内连续但不唯一的数字，

集合操作–》就是对数据库中查出来的结果集操作
56.union/union all（并集）
将两边的结果集合并到一个结果集中显示，前提条件是字段名、字段个数、字段顺序必须一样
57.intersect（交集）
获得两个结果集的交集，只有同事存在与两个结果集中的数据，才会被显示出来
58.minus（差集）
获得前面结果集中有，后面结果集中没有的数据，

高级分组函数
高级分组函数用在group by子句中每个高级分组函数都有一套分组策略
59.rollup(a,b,c……)
分组原则：参数逐次递减，一直到所有参数都不要，每一种分组都统计一次结果并且并在一个结果集显示。

60.cube（a,b,c……）
每种组合分一次组
分组次数：2的参数个数次方

61.grouping sete
每个参数是一种分组方式，然会将这些分组统计后并在一个结果集中显示

二、视图

基本知识：
视图也是数据库对象之一
所有数据库对象名字不能重复，所以视图一般以“v_”开头。
视图再sql语句中体现的角色与表相同。但是视图并不是一张真实存在的表，我们称之为虚表。
它只是对应一个select语句的查询结果集。并将其当作表看待而已。
使用视图的目的是简化sql语句的复杂度。重用子查询，限制数据访问

视图分为简单视图和复杂视图
简单视图：对应的子查询没有关联查询，查询的字段不包含函数、表达式等，没有分组，没有去重，反之则是复杂视图。

对视图进行dml操作
仅能对简单视图进行dml操作
对视图进行dml操作就是对视图数据来源的基表进行的操作，操作不当可能对基表进行数据污染
新增、修改会影响，删除不会有影响（是对看不见的数据操作，不是对看的见的数据）

with check option 写在最后
为视图添加检查选项，可以保证对视图的dml操作后视图对其可见，否则不允许进行该dml操作，这样就避免了对基表进行数据污染

with read only
为视图添加只读选项
那么该视图不允许进行dml操作

删除视图是不会影响基表数据的。
删除视图中的数据实惠删除基表中的数据的

数据字典：
user_objects --object_name
user_views --view_name
user_tables --table_name
user_update_columns

三、序列

序列也数据库对象之一。
作用是生成一系列数字
序列常用于为某张表的主键字段提供值使用

序列支持两个伪列
nextval：获取序列下一个值
若是新创建的序列，那么第一次调用返回的是start with指定的值，以后每次调用，都会得到当前序列值加上步长后的数字
nextval会导致序列发生步进，且序列不能回退。

currval:获取序列当前值，即，最后依次调用nextval后得到的值，currval不会导致步进，但是新创建的序列至少调用依次nextval后才可以使用currval

四、索引

索引也是数据库对象之一
索引是为了提高查询效率

索引的统计与应用是数据库自动完成的，只要数据库认为可以使用某个已创建的索引时就会自动应用
create index idx_xxx_xxx on xxx(xxx) //第一个xxx为表，第二个为需要增加索引的字段
alter index idx_xxx_xxx rebuild;//重建索引
drop index idx_xxx_xxx

五、约束

非空约束 not null
alter table emp
modify(id number(4) not null)
alter table emp
modify(id number(4) null)

唯一性约束 unique
可以保证表中该字段的值任何一条记录都不可以重复，null除外
constraint emp_name_uk unique(name);
可以直接在字段后写唯一性约束，unique，也可以在创建表的字段定义最后写上面一句话。
建表之后添加唯一性约束
alter table emp
add constraint emp_name_uk unique(name)

主键约束
一张表建议只有一个主键，即单列主键，主键也就意味着非空且唯一，一般选择数据库中第一列作为主键，他与表中数据无关，只是做唯一标识。
也会有多列组合主键，但是不建议，数据库不支持。
建表后添加主键和添加唯一性约束类似

外键约束
一般不用，关联并不一样需要外键

检查约束
eg.
check（salary>2000）
多个约束条件的话使用多个check