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
