一、前言

假設(shè)有一張用戶表system_user，里面包含主鍵id、姓名name、年齡age的列屬性，用戶足跡表system_user_history，里面包含主鍵id、外鍵user_id、足跡name。

二、DataManipulitionLanguage 數(shù)據(jù)操作語言

insert 新增操作

insert into system_user values (1,'wjx',25); -> 默認(rèn)全字段新增，按列屬性順序依次填入?yún)?shù)
insert into system_user (id,name) values (1,'wjx'); -> 顯式指定需要新增的列屬性
insert into system_user select id,name,age from system_user; -> 默認(rèn)全字段新增，通過查詢語句賦值
insert into system_user (id,name) select id,name from system_user; -> 顯式指定需要新增的列屬性

update 更新操作

update system_user set name = 'wjx' where id = 1; -> 顯式指定需要更新的列屬性

delete 刪除操作

delete from system_user where id = 1; -> 顯式指定需要刪除的行

select 查詢操作

select 1 from dual; -> 虛擬表
select * from system_user; -> 真實表

full outer join / full join 全外連接，a表有x行，b表有y行，總共為x*y行

select * from system_user,system_user_history; -> 方式一
select * from system_user full outer join system_user_history; -> 方式二

left outer join / left join 左外連接，a表有x行，b表有y行，總共為大于等于x行

select * from system_user su,system_user_history suh where su.id = suh.user_id(+); -> 方式一
select * from system_user su left join system_user_history suh on su.id = suh.user_id; -> 方式二

right outer join / right join 右外連接，a表有x行，b表有y行，總共為大于等于y行

select * from system_user su,system_user_history suh where su.id(+) = suh.user_id; -> 方式一
select * from system_user su right join system_user_history suh on su.id = suh.user_id; -> 方式二

inner join / join 內(nèi)連接，匹配兩個表都符合條件的行

select * from system_user su inner join system_user_history suh on su.id = suh.user_id;

group by 分組，分組條件必須作為查詢列

select id from system_user group by id having  id = 1;

select
    id, login
from (
    select
        id,
        login,
        row_number() over (partition by id order by login) as row_num
    from system_user_history -> 獲取以id分組login排序的行號
) suh
where row_num = 1; -> 取出分組數(shù)據(jù)的第一條

select
    id, login
from (
    select
        id,
        login,
        rank() over (partition by id order by login) as row_num
    from system_user_history -> 獲取以id分組login排序的行號
) suh
where row_num = 1; -> 取出分組數(shù)據(jù)的第一條

Merge 聯(lián)合操作，有則更新，無則新增

MERGE INTO 
SYSTEM_USER_DETAIL SUD 
USING 
(SELECT * FROM SYSTEM_USER where AGE = 20) SU
ON 
(SU.USER_ID = SUD.USER_ID)
WHEN MATCHED THEN 
UPDATE SET SUD.USER_ID = SU.USER_ID
WHEN NOT MATCHED THEN 
INSERT (SUD.USER_ID) VALUES (SU.USER_ID);

三、運(yùn)算符號

alias 別名

select id alias_id from system_user; -> 空格
select id as alias_id from system_user; -> as
select id as "alias_id" from system_user; -> 雙引號

|| 連接符

select id || '-' || name || '-' || age from system_user; -> 以id-name-age格式輸出屬性值

in(set) 等于集合里某個元素即返回

select * from system_user where id in (1,2,3); -> 返回id為1的行

all(set) 大于/小于集合里的所有元素即返回

select * from system_user where id > all (0,2,4); -> 不返回行，id為1的行不滿足大于2/4

any(set) 大于/小于集合里的某個元素即返回

select * from system_user where id > any(0,2,4); -> 返回id為1的行，id為1的行滿足大于0

distinct 去重

select distinct * from system_user;

= 等于、> 大于、>= 大于等于、< 小于、<= 小于等于、!= 不等于、<> 不等于

select * from system_user where id = 1;
select * from system_user where id > 1;
select * from system_user where id >= 1;
select * from system_user where id < 1;
select * from system_user where id <= 1;
select * from system_user where id != 1;
select * from system_user where id <> 1;

between and 范圍閉區(qū)間

select * from system_user where id between 1 and 10; -> id在1到10的行

like 模糊查詢，%匹配任意多個字符，_匹配任意單個字符，escape 轉(zhuǎn)義關(guān)鍵字

select * from system_user where name like '%\_%' escape '\'; -> 匹配name帶有_的行

is null 判斷空

select * from system_user where age is null;

is not null 判斷非空

select * from system_user where age is not null;

and 并

select * from system_user where id = 1 and name = 'wjx';

or 或

select * from system_user where id = 2 or name = 'wjx';

not 非，搭配其它關(guān)鍵字使用

select * from system_user where id not between 1 and 10; -> id不在1到10范圍內(nèi)
select * from system_user where id not in (1,2,3); -> id不等于1/2/3

order by 排序，desc降序、asc升序

select * from system_user order by id asc,name desc; -> id升序、name降序來排列行

case when then else end 多條件分支

select case id when 1 then 1 else 0 end from system_user;

decode 多條件分支

select decode(id,1,1,0) from system_user;

case id when 1 then 1 else 0 end;
case when id = 1 then 1 else 0 end;

savepoint 設(shè)置保存點

savepoint point_user;

rollback 回滾事務(wù)

rollback; -> 全回滾
rollback to savepoint point_a; -> 回滾到某個保存點

commit 提交事務(wù)

commit;

四、內(nèi)置函數(shù)

lower 全小寫

lower('A'); -> a

upper 全大寫

upper('a'); -> A

initcap 首字母大寫，空格隔開被認(rèn)為不同詞匯

initcap('hello world'); -> Hello World

concat 連接

concat('a','b'); -> ab
concat(concat('%', 'a'), '%'); -> %a%

substr 截取字符串

substr('abcdefg',1,5); -> abcde，從第1個字符開始，截取5個

length 字符串長度

length('abcde'); -> 5

regexp_replace 根據(jù)正則表達(dá)式替換字符串

regexp_replace(str, ',', ''); -> 字符串/被替換字符/替換字符

regexp_substr 根據(jù)正則表達(dá)式切割字符串

select regexp_substr(str, '[^,]+', 1, Level,'i') from dual
connect by Level <= LENGTH(str) - LENGTH(regexp_replace(str, ',', '')) + 1);

instr 返回字符串的下標(biāo)，沒有則-1

instr('hello',o); -> 5

lpad 左對齊，缺位補(bǔ)符號

lpad('hello',10,'*'); -> *****hello

rpad 右對齊，缺位補(bǔ)符號

rpad('hello',10,'*'); -> hello*****

trim 清除左右兩側(cè)，默認(rèn)是空格

trim(' hello '); -> hello
trim('h' from 'hello'); -> ello

replace 替換字符串

replace('abc','b','d'); -> adc

round 四舍五入

round(5.234,2); -> 5.23
round(2.345,2); -> 2.35
round(sysdate,'month'); -> 按月份四舍五入

trunc 截斷

trunc(2.345,2); -> 2.34
trunc(sysdate); -> 2022-01-01

mod 求余

mod(10,3); -> 1

sysdate 當(dāng)前日期

select sysdate from dual; -> 今天
select sysdate+1 from dual; -> 明天
select sysdate-1 form dual; -> 昨天

add_months 月份加減

add_months(sysdate,2); -> 兩個月后
add_months(sysdate,-2); -> 兩個月前

last_day 月份最后一天

last_day(sysdate); -> 當(dāng)月最后一天

next_day 下一個同等日期

next_day(sysdate,'星期五'); -> 下個星期五日期

to_date 字符串轉(zhuǎn)日期

to_date('2022-01-01','yyyy-mm-dd');

to_char 其它類型轉(zhuǎn)字符串

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'); -> 日期轉(zhuǎn)字符串
to_char(1234567.89,'999,999,999.99'); -> 1,234,567.89 -> 9代表不補(bǔ)零
to_char(1234567.89,'000,000,000.99'); -> 001,234,567.89 -> 0代表補(bǔ)零

to_number 字符串轉(zhuǎn)數(shù)字

to_number('1,234','9,999'); -> 1234

nvl 輸出非空

nvl(null,0); -> 0
nvl(1,0); -> 1

nvl2 非空輸出表達(dá)式一，空輸出表達(dá)式二

nvl2(null,0,1); -> 1
nvl2(2,0,1); -> 0

coalesce 輸出非空，可變長度參數(shù)

coalesce(null,0,1); -> 0
coalesce(null,null,1); -> 1

nullif 相等輸出空

nullif(1,1); -> null
nullif(1,2); -> 1

avg 平均值

select avg(id) from system_user group by id;

count 統(tǒng)計非空行數(shù)

select count(*) from system_user;

max 最大值

select max(id) from system_user group by id;

min 最小值

select min(id) from system_user group by id;

stddev 標(biāo)準(zhǔn)差

select stddev(id) from system_user group by id;

sum 合計

select sum(id) from system_user group by id;

listagg 分組拼接

select user_id, listagg(name,',') within group (order by user_id) name
from system_user_history group by user_id; -> 將用戶所有足跡用,串起來

CHR & ASCII 特殊字符轉(zhuǎn)義

select ascii('''') from dual; -> 39
select chr(39) from dual; -> '

ASCIISTR & UNISTR 編碼互轉(zhuǎn)解決亂碼

select ASCIISTR('我') from dual;
select UNISTR('\6211') from dual;

greatest 取兩者的最大值

select greatest(1, 2) from dual; -> 2

least 取兩者的最小值

select least(1, 2) from dual; -> 1

五、約束

not null 非空約束

alter table system_user modify id constraint suid  not null; -> 添加名為suid的約束

unique 唯一約束

alter table system_user add constraint suid unique(id); -> 添加名為suid的約束

primary key 唯一非空約束

alter table system_user add constraint suid primary key(id); -> 添加名為suid的約束

foreign key 外鍵

alter table system_user_history add constraint suhuserid foreign key(user_id)
references system_user(id) -> 關(guān)聯(lián)外表
on delete cascade / on delete set null -> 外表刪除時執(zhí)行操作(級聯(lián)刪除/級聯(lián)置空)

check 檢查

alter table system_user add constraint suage check(age between 18 and 35);

drop 刪除約束

alter table system_user drop constraint suid;

disable 約束無效化

alter table system_user disable constraint suid;

enable 約束有效化

alter table system_user enable constraint suid;

modify 約束不能修改，只能刪除再添加

alter table system_user modify(id number(10,0) constraint suid not null);

六、DataDefinitionLanguage 數(shù)據(jù)定義語言

創(chuàng)建表

create table system_user( -> 列級約束
  id number(10) constraint id primary key,
  name varchar2(20) constraint name unique,
  age number(10) constraint age not null
);

create table system_user( -> 表級約束
  id number(10),
  name varchar2(20),
  age number(10),
  constraint id primary key(id),
  constraint name unique(name),
  constraint age not null(age),
  constraint range check(age>0 and age<100)
);

create table system_people as select * from system_user; -> 復(fù)制現(xiàn)有的表和數(shù)據(jù)
create table system_people as select * from system_user where 1=2; -> 復(fù)制空表

修改表

alter table system_user add(email varchar2(20)); -> 添加列
alter table system_user modify(age number(15) default 0); -> 修改列
alter table system_user drop column email; -> 刪除列
alter table system_user rename column name to username; -> 修改列名

刪除表

drop table system_user;

清空表

truncate table system_user;

重命名表

rename system_user to system_people;

創(chuàng)建索引

create index index_name on system_user(name);

刪除索引

drop index index_name;

創(chuàng)建序列

create sequence seq_system_user -> 序列名
increment by 1 -> 自增量
start with 1 -> 起始值
maxvalue 99999999 -> 最大值
cycle/nocycle -> 是否循環(huán)
order/noorder -> 是否有序
cache 20/nocache -> 是否緩存

修改序列

alter sequence seq_system_user order cycle; -> 修改多個屬性

刪除序列

drop sequence seq_system_user;

使用序列作為自增主鍵

select seq_system_user.nextval from dual; -> 下一個值
select seq_system_user.currval from dual; -> 當(dāng)前值

創(chuàng)建視圖

create view view_system_user as select id,name from system_user with read only; -> 添加只讀權(quán)限
create or replace view view_system_user as select * from system_user; -> 修改視圖

更新視圖

update view_system_user set age = 25 where id = 1;
delete from view_system_user where id = 1;

刪除視圖

drop view view_system_user;

兼容語法

declare
    num number;
begin
    select count(1) into num from user_tables where table_name = upper('system_user');
    if num > 0 then
        execute immediate 'drop table system_user';
    end if;
    
    select count(1) into num from user_ind_columns where index_name = upper('system_user_index');
    if num > 0 then
        execute immediate 'drop index system_user_index';
    end if;
    
    select count(1) into num from user_sequences where sequence_name= upper('system_user_index_seq');
    if num > 0 then
        execute immediate 'drop sequence system_user_index_seq';
    end if;
    
    select count(1) into num from user_tab_cols where table_name = upper('system_user') and column_name = upper('user_id');
    if num > 0 then
        execute immediate 'alter table system_user drop column user_id';
    end if; 

    select count(1) into num from user_constraints where table_name = upper('system_user') and constraint_name = upper('user_id');
    if num > 0 then
        execute immediate 'alter table system_usermodify user_id null';
    end if;
end;

七、存儲過程

create 創(chuàng)建函數(shù)

CREATE OR REPLACE PACKAGE -> 定義包名
CUSTOM_PACKAGE IS -> 包內(nèi)可以定義多個函數(shù)
  PROCEDURE CUSTOM_PROCEDURE_1(PARAM IN VARCHAR2);
  PROCEDURE CUSTOM_PROCEDURE_2(PARAM OUT VARCHAR2);
END CUSTOM_PACKAGE;

CREATE OR REPLACE PACKAGE BODY -> 定義包體
CUSTOM_PACKAGE IS 
  PROCEDURE CUSTOM_PROCEDURE_1(PARAM IN VARCHAR2) AS ... END;
  PROCEDURE CUSTOM_PROCEDURE_2(PARAM OUT VARCHAR2) AS ... END;
END CUSTOM_PACKAGE;

BEGIN -> 調(diào)用函數(shù)
  CUSTOM_PACKAGE.CUSTOM_PROCEDURE_1('');
END;

declare 定義變量

declare
  name varchar2(20) := null;
  age student.age%type := null; -> 動態(tài)定義類型
  row student&rowtype := null; -> 動態(tài)定義整行

into 賦值

begin
  select student.name,student.age into name,age from student where id = 1;
exception
  when not found then
end;

cursor 游標(biāo)，存儲臨時數(shù)據(jù)

cursor student_cursor is select * from student; -> 定義游標(biāo)

student_id student.id%type; -> 定義游標(biāo)屬性
student_name student.name%type; -> 定義游標(biāo)屬性

open student_cursor; -> 打開游標(biāo)
loop
  fetch student_cursor into student_id, student_name; -> 讀取數(shù)據(jù)
  exit when student_cursor%NOTFOUND; -> 無數(shù)據(jù)時退出
end loop;
close student_cursor; -> 關(guān)閉游標(biāo)

type student_type is record ( -> 定義游標(biāo)類別
  student_id student.id%type,
  student_name student.name%type
);
type student_cursor is ref cursor return student_type; -> 定義游標(biāo)返回類別

exception 異常，捕獲程序錯誤

declare custom_exception exception; -> 自定義異常
begin
  raise custom_exception; -> 手動拋出異常
  commit;
exception
  when NO_DATA_FOUND then
    dbms_output.put_line(sqlcode); -> 編號
    dbms_output.put_line(sqlerrm); -> 描述信息
    dbms_output.put_line(dbms_utility.format_error_backtrace); -> 發(fā)生位置
    dbms_output.put_line(dbms_utility.format_call_stack); -> 發(fā)生棧
  when others then -> 捕獲并處理
    rollback;
end;

dbms_output 輸出日志

set serveroutput on; -> 開啟輸出服務(wù)
set serveroutput off; -> 關(guān)閉輸出服務(wù)
dbms_output.put('不換行');
dbms_output.new_line; -> 換行
dbms_output.put_line('自動換行');

dbms_metadata 獲取創(chuàng)建語句

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_SCHEMA') from dual; -> 參數(shù)2/3動態(tài)變更

if (boolean) then (logic) elsif (boolean) then (logic) else (logic) end if; 條件表達(dá)式

if name = 'wjx' then update student set age = '20' where id = 1;
elsif name = 'xjw' then update student set age = '21' where id = 2;
else update student set age = '21' where id = 3;
end if;

goto 循環(huán)

declare x :=10;
begin
  <<circle_point>>
  if x>0 then 
    x := x -1;
    goto circle_point;
  else
    dbms_output.put_line('循環(huán)結(jié)束');
  end if;

for (var) in (set) loop (logic) end loop; 循環(huán)

begin
  for student_list in select * from student loop
    if student_list.name = 'wjx' then
      update student set age = '20' where id = 1;
    else end if;
  end loop;
end;

begin
    for i in 1..100 loop
        dbms_output.put_line(i); -> 打印1-100
    end loop;
end;

while (boolean) loop (logic) end loop; 循環(huán)

declare x := 0;
begin
  while x < 10 loop
    x := x+1;
  end loop;
end;

loop (logic) exit when (boolean) end loop; 循環(huán)

declare x:= 0;
begin
  loop x := x + 1;
  exit when x > 10;
  end loop;
end;

八、權(quán)限

為用戶授予權(quán)限

create public synonym db_name.table_name for table_name; -> 同義詞
grant select on db_name.table_name to oracle_username; -> 查詢
grant update on db_name.table_name to oracle_username; -> 更新
grant delete on db_name.table_name to oracle_username; -> 刪除
grant insert ondb_name.table_name to oracle_username; -> 新增

查詢用戶表權(quán)限

select * from dba_tab_privs 
where 1= 1
and table_name = 'table_name' 
and grantee = 'oracle_username'
and privilege in ('SELECT','UPDATE','DELETE','INSERT');

九、觸發(fā)器

觸發(fā)類型（12種），包含新增、修改、刪除

Insert 時機(jī)

CREATE OR REPLACE TRIGGER SYSTEM_USER_A_I
    AFTER INSERT
    ON system_user
    FOR EACH ROW
BEGIN
    INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
    VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'INSERT', :NEW.id, :NEW.name, :NEW.age);
END;

Update 時機(jī)

CREATE OR REPLACE TRIGGER SYSTEM_USER_B_U
    BEFORE UPDATE
    ON system_user
    FOR EACH ROW
BEGIN
    INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
    VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'UPDATE', :OLD.id, :OLD.name, :OLD.age);
END;

CREATE OR REPLACE TRIGGER SYSTEM_USER_A_U
    AFTER UPDATE
    ON system_user
    FOR EACH ROW
BEGIN
    INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
    VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'UPDATE', :NEW.id, :NEW.name, :NEW.age);
END;

Delete 時機(jī)

CREATE OR REPLACE TRIGGER SYSTEM_USER_B_D
    BEFORE DELETE
    ON system_user
    FOR EACH ROW
BEGIN
    INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
    VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'DELETE', :OLD.id, :OLD.name, :OLD.age);
END;

十、JDBC連接

SQL

String sql = "select id from system_user where name = ?";
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
    connection = super.dataSource.getConnection();
    ps = connection.prepareStatement(sql);
    ps.setString(1, "wjx"); -> 入?yún)?
    rs = ps.executeQuery();
    while (rs.next()) {
        String id = rs.getString("id"); -> 出參
    }
} finally {
    if(rs != null) rs.close();
    if(ps != null) ps.close();
    if(connection != null) connection.close();
}

PLSQL

CREATE OR REPLACE PROCEDURE SCHEMA.GET_NEW_USER(in_date IN DATE DEFAULT sysdate, out_user_list OUT SYS_REFCURSOR) IS
--     DECLARE
--       in_date DATE := to_date('2023-01-01', 'yyyy-mm-dd');

    BEGIN
        OPEN out_user_list FOR
            SELECT su.*
            FROM system_user su
            WHERE create_date > TRUNC(in_date - 7);
    END;
/

String sql = "{call SCHEMA.GET_NEW_USER(?, ?)}";

Connection connection = null;
CallableStatement cs = null;
ResultSet rs = null;

try {
    connection = super.dataSource.getConnection();
    cs = connection.prepareCall(sql);
    cs.setString(1, new java.sql.Date());
    cs.registerOutParameter(2, -10);
    cs.execute();

    rs = cs.getObject(2, ResultSet.class);
    while (rs.next()) {
        String id = rs.getString("id"); -> 出參
    }
} finally {
    if(rs != null) rs.close();
    if(ps != null) ps.close();
    if(connection != null) connection.close();
}

FUNTION

create or replace function f_no_param return varchar2 is
begin
    return 'hello, no param';
end;

create or replace function f_have_param(id in integer, name out varchar2) return varchar2 is
begin
    name := 'wjx';
    return 'hello, have param';
end;

String noParamSql = "{? = call f_no_param}";
String haveParamSql = "{? = call f_have_param(?,?)}";
Connection connection = null;
CallableStatement cs1 = null;
CallableStatement cs2 = null;

try {
    connection = super.dataSource.getConnection();
    cs1 = connection.prepareCall(noParamSql);
    cs1.registerOutParameter(1, Types.VARCHAR); -> 返回值是出參
    cs1.execute();
    String noParamVarchar = cs1.getString(1);

    cs2 = connection.prepareCall(haveParamSql);
    cs2.registerOutParameter(1, Types.VARCHAR); -> 返回值是出參
    cs2.setInt(2, 10); -> id是入?yún)?    cs2.registerOutParameter(3, Types.VARCHAR); -> name是出參
    cs2.execute();
    String haveParamVarchar = cs1.getString(1);
    String haveParamName = cs1.getString(3);
} finally {
    if(rs != null) rs.close();
    if(ps != null) ps.close();
    if(connection != null) connection.close();
}

SQL 執(zhí)行計劃

explain plan for select * from system_user where id = 1; -> 生成執(zhí)行計劃
select * from table(dbms_xplan.display); -> 查詢執(zhí)行計劃