insert into … select from…

multi table insert

insert all，分为有条件插入和无条件插入，可以有else子句，同一个when可以被多个into使用
• insert first，只要匹配when，后续判断不再执行
• 可以将每行计算的记录插入到多个表中
• 可以在相关表执行
• 只能通过子查询执行
• 不能在remote 表执行
• 不能在视图执行
• 不能用SQL*Loader执行

更新

• update

  更新单行

  单行单列

  update table set column = … where…

单行多列

update table set col1=…,col2=…, where…

可以对被更新的每一列单独使用不同子查询

• update set can modify multiple rows based on multiple conditions on a table

• update table set (select … from …)

更新多行

update table set col1=…（即没有where条件）
• select * from table for update

删

• delete

  dml，可使用rollback撤销回滚

• 可以使用where指定要删除的行
• 清空表数据

cascade：级联删除时使用

set unused（列名）

unused：由于对大表drop column很耗时，可以先用 set unused逻辑删除，等找到时间窗口再做物理删除；set unused的列仍被计算在每张表的前1000列
• 被set unused的列名可以被新列复用
• 被set unused的列中数据不再可以更新
• 清除掉字典信息（撤消存储空间），不可恢复
• 不会释放数据行的该列数据占用空间，即使commit

增

• create table

  外表

  create table cost ( cost_id number(2), credit_limit number(10) ) organization external (type **** default directory *** location(‘***.csv’))

• type: 数据转换驱动器，默认为oracle_loader/oracle_driver
• location: 指定外部表所在文件夹以及指定文件
• create table as select语句可以从外表中upload数据到数据库里的regular table
• data,metadata存储位置，都在数据库外❌
• 默认的reject limit对于外表来说是unlimited❌
• oracle_loader/datapump在外表里有相同的功效❌

临时表

global temporary table，分为事务级、会话级；事务级只对当前事务有效，on commit delete rows；会话级只对当前会话有效，on commit preserve rows
• 可以有多个索引；
• 可以被定义视图的语句引用
• 可以创建触发器
• 可以被定义为多个session访问
• 可以只授予一部分列的权限：insert，update，references

删

• truncate

  不可以通过rollrack撤销

• 不可以使用where指定要删除的行
• ddl语句，隐式提交
• 只删除表中数据，保留表结构及其列、约束、索引等，新行计数值重置

drop

drop index/table/database (表上定义的所有索引/约束被drop)
• ddl，无法回滚
• 表被移到 recycle bin

purge

对回收站中的表执行清除
• 可使用drop table…purge，表示将表彻底清除，并且可以释放表所占用的空间

函数

聚合函数

• group by。。。having 聚合函数 条件

• count

  count(*,1,常量），不忽略null，每个null计数加1

• count（column/distinct column）忽略null

max min

可以在char类型的列上使用
• 如果字段的值全是NULL, 则MIN和MAX的结果均为NULL.
• 如果该字段包含非NULL值，则MIN/MAX过滤掉所有NULL值

avg

忽略null值，而不是将其作为 0 处理。

sum

忽略NULL值

日期函数

• date类型加减法

  date-date=number

  group by （date1 – date2），可以成功执行

date-number=date

date比较，大于“>”，小于“<”

CURRENT_DATE，返回date类型

时区敏感，返回基于session时区的当前日期returns the current date in the session time zone

current_timestamp，返回timestamp类型

返回session时区的日期和时间

sysdate，返回date类型，格式默认值包含日期

to_date（c, fmt）

将char、varchar、varchar2、nchar、nvarchar2转换为date类型
• 第一个参数c不可用date类型

to_char(datetime,fmt)，第一个参数为date或其他日期类型

ADD_MONTHS(d,i)，d可以为date或可隐性转换为date的值，i为整数或可隐性转换为整数的值

date与字符常量直接比较时，字符串格式需与date默认格式相同

NULLIF（exp1，exp2），若exp1与exp2相等，则返回null，否则返回exp1，返回类型与exp1相同，exp1不能为null

replace：replace（原字段，“原字段旧内容”，“新内容”），返回新内容

ceil(n) ：取≥n的最小整数(向上取整)。e.g:ceil(1.5)=2,ceil(-1.5)=-1

floor(n)：取≤n的最大整数(向下取整)。e.g:floor(1.5)=1,floor(-1.5)=-2

substr：substr(string, start, length)，返回string从开始位置start截取,length的部分字符串start为负数时，从倒数第几位开始向后截取

trim：用来去除一个字符串的开头或结尾（或两者）的字符，字符只能为单个字符，TRAILING 参数指定去除结尾字符
而ltrim和rtrim可以去除多个字符

示例：SQL> SELECT trim(trailing ‘2’ from ‘213dsq12’) FROM dual; （213dsq1）

连接函数

• join

  inner join

• self join：自连接，可以是inner join，可以是 left outer join

• left join
  right join

  A left join B on …and…，and后条件只对B进行过滤，A不受影响

• left join on where，where条件会对A B联合后的临时表进行过滤

外链接可以用于两个表有多个链接条件时

非等值连接：non-equijoin

where a.col between b.low_col and b.high_col
<>,<,>,like,>=,<=
• 非等值连接语法比SQL：1999语法性能更好

cross join

交叉连接，笛卡尔积
• 后还可使用where子句

merge

all:满足所有查询结果

e.g.where cost<all(select max(cost) from t1 group b c1)
• 如果t1中包含null数据，那么所有all相关的比较运算将不会返回任何结果

比较运算：=,>,<,<>

insert any

case when

先筛选第一个条件，不符合的会进行第二个条件的筛选

fetch

• 使用这个子句去指定返回行的个数或者返回行的百分比

• [ FETCH { FIRST | NEXT }[ { rowcount | percent PERCENT } ]
  { ROW| ROWS } { ONLY | WITH TIES } ]

  指定only会返回明确的行数或者是百分比的行数

• 如果指定with ties子句，那么拥有和最后一行相同的排序键值的行都会被fetch。并且此时必须指定order by 。如果没有指定order by，那么不会有附加的行被返回
• 在order by之后使用

listagg…within group (order by …)

• 多行合并为一行

decode

• decode(exp1,con1,value1,con2,value2…default,value0)
• 判断exp1，若exp1==con1，则返回value1，否则继续向后判断exp1=con2…直到找到相等条件；若exp1与此后所有con都不想等，则返回default对应的value0；若未定义default，则返回null

主键&索引

键

• unique key：唯一约束，允许null值，可以有多个，可以是复合字段，如果null+非null字段重复，也会报错。
• primary key：主键，不为null，只能有一个，可以是复合字段
  可以直接创建create table（c1 varchar(10) primary key）
• foreign key：外键，允许null值，可以有多个外键
• Partition key：指定为分区键的列不能被drop
• constraint:纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。
  index: 存储在数据库中的一个物理结构，
  创建主键或唯一约束时，会自动创建唯一性索引，反之不成立。
  外键不会自动创建索引， 须关联一个唯一性索引（主键或唯一约束）

索引

• 唯一索引可以被改为非唯一索引
• 唯一索引和非唯一索引不能出现在同一列上
• 递减索引是一个 function-based 类型索引
• create table时可以创建索引/主键
• 属于用户a的表可以拥有属于用户b的 索引
• 更新表会导致表的任一或所有索引的更新
• CREATE INDEX index_name ON table_name (column_name)
• union index和 non-union index可以在同一列创建

数据类型

类型转换

• 显性转换

  函数

  1.日期->字符：to_char(date,’yyyy-mm-dd’)

• 2.字符 -> 日期：to_date(string,’yyyy-mm-dd’)

• 3.字符 -> 数字：to_number(“123”)

• 4.数字 -> 字符：to_char(123)

  to_char(1234,\”$99,99”):如果格式的位数不如数字的位数多,会返回’#’；
  被截取的字符需四舍五入

场景

anydate > date’01-02-2018’
(写成’01-Feb-2018’可以隐性转换)

隐性转换

||,concat (拼接，只能拼string):number/date->string
• =><(比较运算):string->number/date
• ±(算术运算,number，date可运算):string->number/date

日期

• timestamp：timestamp包含年、月、日，时分秒 毫秒的信息

• timezone

  DBTIMEZONE函数返回与UTC的偏移

• SESSIONTIMEZONE 函数返回与UTC的偏移

系统自带日期

current_date返回当前会话时间
• sysdate返回服务器时间
• last_day：对应日期的当月最后一天

interval year to month 类型：支持年份间隔

interval ‘123-2’ year(3) to month：123年2隔越月，year(3)表示年的精度为3位，默认为2

interval day to second：存储两个TIMESTAMP之间的时间差异

单位可为day、hour、minute、second
• support fractions of seconds

日期只能进行加、减运算

使用with check 子句可以阻止固定行被更新或插入，即不满足视图定义时where条件的数据无法更新或插入（如果视图定义包括条件（譬如 WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句）

视图可以不需要重新授权就完成更新

create or replace语句，无需先drop再create，保留现有权限

DML

不可执行DML的条件

拥有组函数

• 定义 有group by

  在使用了group by定义的视图上，通过视图执行delete/insert一行或多行数据会报错

定义有rownum伪列

不可update的其他条件

列定义为表达式

不可insert的其他条件

列定义为表达式
• 表中非空的列在视图定义中未包括
• 定义时使用了group by也有可能insert成功

synonym同义词

• 可以认为是对表、视图、序列、存储过程、函数、程序包或者其他同义词的一个别名
• 同义词也可以拥有同义词
• sequence可以拥有同义词
• 同义词拥有一个object number
• 所有用户都可以访问到
• 一个用户可以创建属于另一个用户的对象的同义词
• 不是所有用户都可以创建public 同义词

sequence序列

• 步长 increment by，初始值 start with，最大值 maxvalue ，达到最大值后是否循环从头开始 cycle/nocycle，nocycle时达到最大值后新申请值会报错，cache改善性能
• sequence 不保证不产生重复值
• 字段默认值为sequence.nextval时，插入数据的用户需有对于sequence的access权限
• 一个序列可以给两张表的key提供值
• 数据库非正常关闭后，已经缓存的序列会丢失
• 有select、alter、drop、reference权限
• alter不可改start with，其他都可改

事务

• sqlplus，用户退出，未提交的事务会自动提交

• session可以看到它中所有未提交的事务

• 开始于第一条DML/DDL语句

• 结束于

  一条显式的COMMIT或者Rollback命令

• 一条DDL或者DCL语句
• 系统崩溃
• 用户退出SQLplus或者SQLplussheet 或者iSQL*plus

rollback

回滚所有未提交事务
• to savepoint回滚到指定保存点，未提交的事务保持未提交状态

权限

• object privilege权限

  select/update 仅可以授权给 表 和 视图

• alter 仅可以授权给 表 和 序列
• 一个对象的owner，默认需要此对象的所有对象权限
• 表的owner必须被授予 references权限，从而允许其他user创建外键
• 创建关联到其他schema表的外键约束
• 从除去sys之外的任意schema中删除表
• 用户默认拥有其schema中所有object的所有object权限

system 权限

登录一个库：Log into a database

• 查询数据库中任意表

• 使用with grant option 子句

  语法grant create… on table to role with grant option

• grant create…role to role
• subquence 子序列，不能被grant SELECT、ALTER、UPDATE、DELETE

向其他schema中的现有表，增加主键约束

public权限

可以从一个user收回public权限

role

1个role可以是多个role和权限的集合
• roles are named groups of related privileges可以被users 或其他roles granted
• a role can be granted to public,not itself

read-only模式的table，可以执行 create index、drop unused columns、drop table

session_privs，用户可用的权限，包括当前可用的system权限

可单独授予表中部分列的权限

references
• select
• update

DUAL：虚拟表

• 拥有select权限都可以使用
• 只显示1行1列，varchar2类型

savepoint

• 执行前的事务不会被提交
• 可以回滚delete操作，不能回滚truncate操作
• 不能回滚create index操作

merge

• 可以使用子查询produce源数据row
• 可以使用视图produce源数据row
• 可以有条件的从多个表中将数据聚合为一行插入到单表中
• delete的update，delete需要有where
• merge into new_table n USING ( select … from old_table ) p ON (n.id=p.id) when matched then update set … delete
• merge into … using () on … when not matched then insert …values …where

substitution变量：替代变量

• 替代变量可以在任何select子句中使用
• 使用两个“&&”符号的替代变量在同一个session中只需要指定一次值
• &使用变量，&&的变量只需要第一次出现时提供值

set verify on：

• 打开时，对于有&变量的语句，输入变量值后会显示变量赋值后的语句
• 可以在SQL Developer和SQL *PLUS中使用
• 显示由DEFINE命令定义的变量的值

ERD：根据ERD选择两行相关数据

• 两个表中的每一个关系必须通过join条件实现
• 同一表中的关联数据通过self join实现

data dictionary

• data dictionary持续更新以反映数据库对象、权限和数据的更新
• sys用户拥有data dictionary下所有base table和用户可达的视图（注意区别于system用户）
• DICRIONARY是一个view，包含所有用户可访问的数据字典视图的名称

q引用：q‘引用符…引用符’，q可以大写Q.引用符可以为：

• 任意单字节或多字节字符集
• （）{}［］<>

别名

• as可有可无
• 可以数字、特殊符号开头，但此时必须用双引号
• 不可用单引号
• 注意避免用关键字
• 表别名可以提高性能

多行子查询：返回多行数据的子查询

• 可以使用group by
• 可以使用having
• 可能返回多个列
• 可以包含子查询

object 对象

• user_objects视图

  包含当前用户拥有的所有表、视图、索引、包等对象

• User_objects is a list of all the database objects (tables, views, indexes, packages, etc., etc.) belonging to the current user

all_objects视图

用户有访问权限的所有对象

dba_objects视图

数据库中的所有对象

表、视图、pakcage、同义词等

用户自定义object type