ORACLE教程
一.表空间,用户,表创建DDL&&DCL
1.创建表空间
create tablespace heber datafile '/u01/app/oracle/oradata/orcl/heber01.dbf' size 100m autoextend on next 100m;
创建 表空间 表空间名 路径 大小
自动扩张 扩的大小
select tablespace_name,(sum(bytes)/1024/1024) tablespace_size from dba_data_files group by tablespace_name;
2.创建用户
create user heber identified by test default tablespace heber_DATA;
创建 用户 用户名 密码 指定表空间
3.用户授权
grant dba to heber;
4.锁定用户/解锁用户
alter user heber account lock;
alter user heber account unlock;
5.修改用户密码
alter user heber identified by heber
6.创建表
create table t_owners(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);
comment on column t_owners.id is 'id';
comment on column t_owners.name is '业主名称';
comment on column t_owners.addressid is '地址id';
comment on column t_owners.housenumber is '门牌号';
comment on column t_owners.watermeter is '水表编号';
comment on column t_owners.adddate is '登记日期';
comment on column t_owners.ownertypeid is '业主类型id';
7.修改表
alter table t_owners add
(
remark varchar2(30),
outdate date
);
alter table t_owners modify
(
remark char(20)
);
alter table t_owners rename column remark to remarks;
alter table t_owners drop column remark;
8.删除表
drop table t_owners;
二.数据类型
1.数字型
number()
2.字符串类型
varchar2()
3.字符型
char()
4.时间类型
date
三.数据增删改DML
1.插入数据
insert into t_owners values (1,'张三',1,'2-2','5678',sysdate,1);
2.修改数据
update t_owners set adddate=adddate-3 where id=1;
commit;
3.删除数据
delete from t_owners where id=1;
四.查询DQL
1.查询一张表
select * from user_tables;
2.查询需要的字段
select ename,sal from emp;
3.取别名
select ename 姓名,sal 薪资,sal*12 年薪 from emp;
4.去重
select distinct job from emp;
5.nvl函数
select ename, sal+nvl(comm,0)*12 from emp;
6.where条件
select * from emp where deptno=10;
7.between and
select * from emp where sal between 1000 and 3000;
8.order by
select * from emp order by sal desc;
9.lower/upper/initcap函数
select lower("Love") from dual;
select upper("Love") from dual;
select initcap("love") from dual;
10.case函数
select ename,job,sal, case when job='CLERK' then sal*2 else sal*1 end as new_sal from emp;
11.组合函数
avg count max min sum
平均值 总数 最大值 最小值 求和
12.group by
select deptno,sum(sal) from emp group by deptno;
五.视图
1.视图是什么
2.创建视图
create view view_owners1 as
select * from t_owners t where t.ownertypeid=1;
3.查看视图
select * from view_owners1 t;
六.同义词
1.创建同义词
create public synonym t_owners for t_owners;
七.索引
1.创建索引
create index index_t_owners on t_owners(name);
八.创建存储过程
1.创建存储过程
create or replace procedure del_tablelog is
begin
execute immediate 'truncate table tablelog';
end;
/
九.创建定时器
1.创建定时器
declare
jobno number;
begin
dbms_job.submit(jobno,
'del_tablelog;',
sysdate,
'sysdate+3'
);
commit;
end;
/
2.查看定时任务
select * from user_jobs;