Oracle

Oracle基础教程

创建表空间

CREATE tablespace helloOracle2 datafile 'E:\OracleTest\OracleTest.dbf' SIZE 5m autoextend ON next 5m

创建表空间

CREATE USER litch IDENTIFIED BY 123456 DEFAULT tablespace helloOracle2

赋予权限

GRANT dba to LITCH;

创建表

--字符型 char，varchar2，LONG
--数值型 number（5）最大为99999，number（5,2）最大为999.99
--日期型 DATE ,timestamp
--二进制型 clob （存字符4个G），blob（存声音图形视频4个G）
create table helloworld(
id number primary key,
name VARCHAR2(20),
address NUMBER,
phone NUMBER,
time DATE
)

插入一条数据

insert into HELLOWORLD (ID,Time) values (123,SYSDATE);

更新一条数据

update helloworld set id = 12 where id =123

删除数据（可回滚）

delete from helloworld where id =12

添加字段

alter table helloworld add(sex VARCHAR2(5))

修改列名

alter table helloworld rename COLUMN sex to hobby

修改表的属性

alter table helloworld modify (hobby VARCHAR2(10))

修改字段长度

alter table helloworld modify (hobby VARCHAR2(30))

修改表名

alter table helloworld rename to test

删表（不可回滚）

truncate table helloworld

显示用户

select user from dual

修改用户密码

alter user LITCH identified by 123456

赋予修改、查询，删除，更新表的权限

grant all on LITCH.HELLOWORLD to LITCH

收回权限

revoke all on LITCH.HELLOWORLD from LITCH

将从其它表中选择数据并将其插入另一个表中

insert into test(id,time) select id from helloworld 

把一个表的数据插入多表(无条件)

insert all 
into test(address) values ('上窑村')
into test2(address) values ('武江')
select id,address from helloworld

把一个表的数据插入多表(有条件)

insert all 
    when id =222 then 
    into helloworld values (id,address,name,hobby,time,phone)
    when id =111 then
    into test2 values (id,address,name,hobby,time,phone)
    select id,address,name,hobby,time,phone from test

复制表结构创建表（连带数据）

create table test2 as select * from test 

复制表结构创建表（不带数据）

create table test3 as select * from test2 where 1=2

insert插入结果集

insert into test select * from test2

update操作

update test set address=1233333 where id = 111

删除A表id为1的值，同时删除B表外键ID=1的值，只需要在建表时设置外键删除关联即可

CREATE TABLE order_items 
(
    id NUMBER(12), 
    FOREIGN KEY(id) 
    ON DELETE CASCADE
);

查询消重

select DISTINCT address from test

查询空值

select * from test where hobby is NULL

查询非空值

select * from test where hobby  is not null

in使用

select * from test where address in('北京','上海')
select * from test where address not in ('广东')

删除某列的所有数据

update TEST set ADDRESS=null

between用法

select * from test where id BETWEEN 111 and 132

union用法

select id from test UNION All select id from HELLOWORLD

内连接

select TEST.ADDRESS from test JOIN TEST2 on TEST.ID=TEST2.ID

外连接（左连接、右连接）

select TEST.ADDRESS from test LEFT JOIN TEST2 on TEST.ID =TEST2.ID;
select TEST.ADDRESS from test right JOIN TEST2 on TEST.ID =TEST2.ID;

查询前面3条数据，类似于limit，但是oracle没有limit语法

SELECT * FROM TEST where ROWNUM<4

子查询的子查询

select id,name from test where id in (select id from test2 where id in (select id from test3 where id = 222))

排序

select id from test ORDER BY id asc --desc

分组

select name from test GROUP BY test.NAME

having

select name from test GROUP BY name having name ='123123'

新增列

alter table test add test VARCHAR2(20)

删除列

alter table test drop COLUMN test

索引

CREATE INDEX user_index ON TEST (id);

存储过程1

create or replace procedure myDemo1
as
begin
  dbms_output.put_line('hello word, my name is stored procedure');
end;

调用存储过程

DECLARE
begin
myDemo1;
end;

call myDemo1();

存储过程2(变量声明，赋值)

create or replace PROCEDURE myDemo2
as name varchar2(10);
age int;
begin
    name:='小明';
    age:=18;
    dbms_output.put_line('name='||name||',age='||age);
    end;

存储过程2(带有参数的存储过程)

create or replace PROCEDURE myDemo3(name in varchar2,age in int)
as 
begin 
dbms_output.put_line('name='||name||',age='||age);
end;

begin 
myDemo3('小明',12);
end;

增删改查存储过程

create or REPLACE procedure myDemo4(ID in NUMBER,NAME in VARCHAR2,ADDRESS in VARCHAR2,PHONE in NUMBER,HOBBY in VARCHAR2)
as 
begin
insert into test(ID,NAME,ADDRESS,PHONE,HOBBY)values(ID,NAME,ADDRESS,PHONE,HOBBY);
commit;
end;

begin
myDemo4(6666,'存储过程','韶关','13222222','研究技术');
end;

调用存储过程

call myDemo4(7777,'存储过程调用','韶关','13222222','研究技术')

分页查询

select id,address,rownum from TEST where ROWNUM<6;

每页显示m条数据，查询第n页数据

(  select * from (select rownum r,e. * from 要分页的表 e where rownum<=m*n) t where r>m*n-m ;)
select * from(select ROWNUM r ,t.* from (select * from TEST ORDER BY ID) t where ROWNUM<=4*2)where r>4*2-4