1. 表空间
-- 创建普通表空间
create tablespace my_tbsp
datafile 'E:/app/Administrator/product/11.2.0/dbhome_1/oradata/sample/my_tbsp.dbf'
size 20M
autoextend on
next 50m maxsize 20480m
extent management local;
-- 创建临时表空间
create temporary tablespace my_temp
tempfile 'E:/app/Administrator/product/11.2.0/dbhome_1/oradata/sample/my_temp.dbf'
size 20M
autoextend on
next 50m maxsize 20480m
extent management local;
-- 查看所有的表空间
select * from user_tablespaces;
-- 删除表空间
drop tablespace my_tbsp;
drop tablespace my_temp;
2. 用户
-- 创建用户
create user zhangsan
identified by abc123
default tablespace my_tbsp
temporary tablespace my_temp;
-- 修改用户密码
alter user lisi identified by abcdefg;
-- 删除用户
drop user lisi;
-- 删除用户(级联删除该用户的数据)
drop user lisi cascade;
-- 查看用户
select * from user_users;
4. 权限和角色
-- 授予权限
grant create session to zhangsan;
grant create user to zhangsan;
grant create table to zhangsan;
-- 撤销权限
revoke create user from zhangsan;
-- 角色: connect,resource,dba
grant connect,resource to lisi;
-- 撤销角色
revoke resource from lisi;
5. 数据库语言
DDL: 数据库模式定义语言(Data Definition Language)
create,alter,drop,truncate
DML: 数据操纵语言(Data Manipulation Language)
insert,update,delete
DQL: 数据查询语言(Data QueryLanguage)
select
TCL: 事务控制语言(Transaction Control Language)
commit,rollback,savepoint
DCL: 数据控制语言(Data Control Language)
grant,revoke
6. 数据库类型
6.1 字符型
char: 定长字符,如果添加的值不足则会补齐空格,最多2000个字节
varchar2: 可变长字符,最多4000个字节
long: 可变长字符,最多2G
6.2 数值型
number(8,2) 8: 最多8位,2:接受最多两位小数(如果有小数,整数范围就要减去小数位)
整数位只能最多6位,小数位最多2位
6.4 日期时间
date: 存储日期和时间部分，精确到整个的秒
timestamp: 存储日期、时间和时区信息，秒值精确到小数点后6位
6.5 RAW
raw: 存储2进制数据,最多2000字节
long raw: 存储2进制数据,最多2G
6.6 LOB
BLOB: 2进制大字段,最多4G
CLOB: 字符字符大字段,最多4G
NCLOB: Unicode字符大字段,最多4G
BFILE: 外部文件大字段
6.7 伪列
rowid: 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行
rownum 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数
7. 表
7.1 创建表
create table t_user (
id varchar2(36) primary key,id_card char(18) not null unique,name varchar2(20) default 'hehehe',price number(4,2) default 3.1415,birthday date default sysdate
);
create table t_department(
id varchar2(36) primary key,name varchar2(40)
);
alter table t_user add depart_id varchar2(36);
primary key 主键(唯一,不为空)
not null 不能为空
unique 唯一
default 设置默认值
sysdate 表示系统当前时间
** oracle中没有自增长(auto increment),oracle中需要依赖sequence来实现自增长
7.2 删除表
drop table t_user;
7.3 修改表结构
-- 添加列
alter table t_user add gender varchar2(6) default '男';
-- 删除列
alter table t_user drop column price;
-- 修改列
alter table t_user modify gender char(6) default 'female';
7.4 外键约束
-- 创建外键约束
alter table t_user
add constraint fk_user_department
foreign key(depart_id)
references t_department(id);
-- 删除外键约束
alter table t_user drop constraint fk_user_department;
----------
1. DDL清空表
truncate table bonus;
2. DML
2.1. 添加数据
insert into
insert into dept(deptno,dname)
values(60,'财务部');
insert into dept(deptno,dname) values(50,'技术部');
commit;
-- 新增所有列数据
insert into dept values(70,'财务部','财务');
2.2. 修改数据
update
update dept
set
loc = '深圳'
where
deptno = 50;
2.3. 删除数据
delete
delete from dept where deptno = 50 or deptno = 60;
-- 删除所有
delete from dept;
** delete 和 truncate 清空表时有什么区别?
delete删除的数据会保存到回滚段,可以被回滚,效率低.
truncate直接删除数据不可以回滚,效率高.
清空表通常使用truncate.
3. 事务
事务指一组针对数据库操作的命令集合,事务是一个原子操作,不可拆分,必须同时成功或者是同时失败.
四大特性
3.1 原子性: 事务时一个原子才做,事务中的操作同时成功或者同时失败
3.2 一致性: 事务操作之前和之后的数据要保持一致
3.3 隔离性: 事务之间互不影响,互相并不知道其他事务的存在
3.4 持久性: 事务操作之后,数据应当永久保存
4. create as(根据查询结果创建表,并将查询到的数据添加到表中)
create table t_employee
as
select ename,job,sal from emp where deptno = 10;
5. insert [as](将全部查询到的数据添加到指定表中,不要as关键字)
insert into bonus select * from t_temp;
如何快速去除表中的重复数据
-- copy一个临时表
create table t_temp as select distinct * from bonus;
-- 清空原表
truncate table bonus;
-- 将临时数据导入原表
insert into bonus select * from t_temp;
commit;
drop table t_temp;
6. 简单查询 select
6.1 单表查询
select * from emp where sal >= (select max(sal) from emp)
-- * 查询所有列
select * from emp;
-- 查询指定列
select ename,sal from emp;
-- 去重复
select distinct job,mgr from emp;
-- 起别名
select
e.ename 姓名,e.sal 工资
from
emp e
-- 单条件
select * from emp where sal < 3000
select * from emp where comm is not null
select * from emp where comm is null
select * from emp where ename = 'SCOTT'
**
=: 等于
<> != ^=: 不等于(通常用<>)
in: 多值匹配(匹配in中的列表值) select * from emp where empno in (7369,7788,7934)
not in:
between and: select * from emp where sal between 1100 and 2000 (相当于 sal >= 1100 and sal <= 2000)
not between:
is null:
is not null:
like: 模式匹配,只能用于字符串(%表示任意个任意字符,_表示一个任意字符)
select * from emp where ename like 'MAR%'
select * from emp where ename like '%S'
select * from emp where ename like '%A%'
select * from emp where ename like 'A____'
not like:
-- 多条件(and)
select
*
from
emp
where
comm is not null
and
comm > 0
-- 排序
select * from emp order by sal asc -- 升序
select * from emp order by sal desc -- 降序
-- 多列组合排序
select *
from emp
order by
sal desc,comm desc
-- 分组查询
select
job,sum(sal),max(sal),min(sal),avg(sal),count(empno),count(comm)
from
emp
group by
job
having
avg(sal) > 1500
-- 聚合函数
sum() 求和(对当前组的所有值进行求和)
max() 取最大值
min() 取最小值
avg() 取平均值
count() 求有效值数,通常用于求记录数
-- 在oracle中用count查询记录数时,通常使用一个常量值,如:1 而不是用*或者是列值,效率高
select job,count(1)
from emp
group by job
7. 日期时间处理
create table t_user (
id varchar2(50) primary key,name varchar2(20),birthday date
)
to_date(将字符串转日期)
insert into t_user values('10001','zhangsan',sysdate);
insert into t_user values('10002',to_date('1992-09-15','YYYY-MM-DD'));
insert into t_user values('10003',to_date('1992-09-15 18:30:20','YYYY-MM-DD HH24:MI:SS'));
to_char(将日期转字符串)
select id,name,to_char(birthday,'YYYY/MM/DD HH24:MI:SS') from t_user
8. 虚表dual
select sysdate from dual;
select 20+30 from dual;
----------
1. 集合操作
1.1 union（合并查询结果，并集，会去重复）
select ename,sal,deptno from emp where sal > 2000
union
select ename,deptno from emp where deptno = 20
1.2 union all（并集，不会去重复）
select ename,deptno from emp where sal > 2000
union all
select ename,deptno from emp where deptno = 20
1.3 intersect(求交集)
select ename,deptno from emp where sal > 2000
intersect
select ename,deptno from emp where deptno = 20
1.3 minus(返回从第一个查询结果中排除第二个查询中出现的行。)
select ename,deptno from emp where sal > 2000
minus
selec ename,deptno from emp where deptno = 20
2. 连接操作
在oracle中使用||来连接两个字符串
select ename || '(' || sal || ')' es from emp;
操作符优先级:
()
大于
运算符(+-*/)
大于
连接符(||)
大于
比较运算(=,!=,<>,^=,>,<,>=,<=,is null,like,is not null,not like,between .. and,not between .. and)
大于
not
大于
and
大于
or
3. 常用函数
3.1 日期函数
add_months
last_day
months_between
next_day
round
trunc: 从指定位置处截断 select trunc(sysdate,'yyyy') from dual
extract
3.2 字符函数
3.2.1 TRIM
select ltrim('abababababbbbbbbabababab','ab') from dual;
select rtrim('ababababccccababab','ab') from dual;
select rtrim(ltrim( 'abababcccccababab','ab'),'ab') from dual;
3.2.2 translate & replace
-- 字符一对一替换
select translate('hehehehehehehhe','h','lol') from dual;
-- 整串替换
select replace('hehehhehehehehhehehe','he','lol') from dual;
3.2.3 instr: 相当于indexOf,从1开始
3.2.4 substr
3.2.5 Concat
3.2.6 upper lower
3.2.7 initcap 首字母大写
3.2.8 length: select length('heheh') from dual;
3.2.9 decode: 条件匹配函数,相当于switch
select
ename,decode(job,'ANALYST','分析师','CLERK','职员','SALESMAN','销售员',job
)
from emp
3.3 数字函数
...
3.4 转换函数
to_char
to_date
to_number
3.5 其他函数
nvl: 如果值为null则显示为指定值
select ename,nvl(comm,0) from emp
nvl2: 如果值不为空则取第二个参数,如果为空则取第三个参数
select ename,comm,nvl2(comm,100,0) from emp
nullif: 如果两个值相同,则为null,否则取第一个参数
select nullif('aa','aa') from dual
3.6 分析函数
row_number: 连续排位
select
ename,row_number() over(order by sal desc,comm asc)
from emp
rank: 等值同名,会跳跃
select
ename,rank() over(order by sal desc)
from emp
dense_rank: 等值同名,不会跳跃
select
ename,dense_rank() over(order by sal desc)
from emp
4. 子查询
-- 条件时使用子查询
select * from emp where deptno in (select deptno from dept where dname in ('SALES','RESEARCH'))
-- 子查询为临时表
select
*
from
(select * from emp where sal > 2000) t_temp
where
t_temp.deptno = 20
-- 查询列上使用子查询
select
ename,(select dname from dept d where d.deptno = e.deptno) dname
from
emp e
5. 连接查询
*****select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+); 左连接
select e1.ename,emp e2,dept d where e1.mgr = e2.empno(+) and e1.DEPTNO = d.DEPTNO and e1.ename='SCOTT';
*****select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
5.1 左外连接,以左边表为基准,左边表所有数据都需要被显示
select
e.ename,d.dname
from
emp e left join dept d
on
e.deptno = d.deptno
5.2 右外连接,以右边表为基准,右边表所有数据都要被显示
select
e.ename,d.dname
from
emp e right join dept d
on
e.deptno = d.deptno
5.3 全连接,两边表的数据都要显示
select
e.ename,d.dname
from
emp e full join dept d
on
e.deptno = d.deptno
5.4 内连接,只显示两边对应的数据
内连接1:
select
e.ename,d.dname
from
emp e inner join dept d
on
e.deptno = d.deptno
内连接2(多表联查):
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno
5.5 自关联
select
e.ename name,m.ename mgr
from
emp e,emp m
where
m.empno = e.mgr
select
e.ename name,m.ename mgr
from
emp e left outer join emp m
on m.empno = e.mgr
6. exists: 当子查询能查询到数据时,外层sql的当前数据将被查询出来
select * from emp e where sal > 1000 and exists(select 1 from dept d where d.deptno = e.deptno and d.dname='ACCOUNTING' )
not exists:
select * from emp e where sal > 1000 and not exists(select 1 from dept d where d.deptno = e.deptno and d.dname='ACCOUNTING' )
7. case ... when ... then ... end;
用法一:
select
ename,(case job
when 'CTO' then '首席技术官'
when 'MANAGER' then '网管'
when 'SBO' then '呵呵'
else '其他'
end) job
from
emp
用法二:
select
ename,(case
when (sal > 5000 and comm is not null) then '土豪'
when sal > 3000 then '高富帅'
when sal > 2000 then '中产阶级'
when sal > 1000 then '贫下中农'
else '低调'
end) pt
from
emp
行变列
select * from t_score
select
name,sum((case course when '语文' then score else 0 end )) 语文,sum((case course when '数学' then score else 0 end )) 数学,sum((case course when '英语' then score else 0 end )) 英语
from t_score
group by name
----------
1. 同义词
为现有对象创建一个别名（简化sql，隐藏对象原有名称）
私有
create synonym sy_xxx for emp;
需要权限
grant create synonym to scott;
其他用户使用需要权限
grant select on emp to zhangsan;
公有
create public synonym sy_xxx for emp;
删除
drop synonym sy_xxx;
drop public synonym emp_syn;
一般resource角色中没有创建同义词的权限，需要重新赋权限。
2. 序列
drop sequence seq_test;
-- 创建序列
create sequence seq_test
start with 10
increment by 100000000
--maxvalue 99
nomaxvalue
-- minvalue 0
nominvalue
-- cycle
nocycle
cache 20 -- nocache
;
-- seq_test.nextval(取出并生成下一个值)
select seq_test.nextval from dual;
-- seq_test.currval（取出当前值，不生成下一个值）
select seq_test.currval from dual;
create table t_employee(
id number(8) primary key,name varchar2(20)
);
-- 添加数据时使用
insert into t_employee(id,name) values(seq_test.nextval,'小明');
select * from t_employee;
-- 修改序列
alter sequence seq_test
increment by 1
maxvalue 200000000000000000
nominvalue
nocycle
nocache;
-- 删除序列
drop sequence seq_test;
3. 视图(存储查询语句并起别名,将复杂sql简化)
create or replace force view v_e as
select
e1.empno,e1.ename,e2.ename mgr,d.dname
from
emp e1,dept d
where
e2.empno = e1.mgr
and
d.deptno = e1.deptno;
-- 查询使用视图
select * from v_e t;
4. 索引
在逻辑和物理上都独立于表一种映射结构.
提高sql执行效率,减少磁盘I/O
4.1 一般索引(当查询条件中出现索引列时,会使用该索引)
create index idx_ename on emp(ename);
4.2 组合索引(如果条件少于索引列数,将不会被使用)
create index idx_name_job on emp(ename,job);
select * from emp where ename = 'SCOTT' and job = 'SALESMAN'
4.3 函数索引(在列上使用函数时将使用该索引)
create index indx_lower_ename on emp(lower(ename));
select * from emp where lower(ename) = 'scott';
4.4 唯一索引(要求被索引列必须值唯一,主键自带唯一索引)
create unique index idx_unique_dname on dept(dname);
insert into dept(deptno,dname,loc) values(50,'xiaoshou','沙坪坝');
select * from dept where dname = 'xiaoshou'
4.5 位图索引(适合用于低基数列,不直接索引rowid,索引列值到rowid的映射)
create bitmap index idx_bmjob on emp(job);
select * from emp where job = 'CLERK'
4.6 反向键索引(反转索引列的每个字节,适合用在连续增长列上)
create index idx_reverse_mgr on emp(mgr) reverse;
select * from emp where mgr = '7788';
4.7 索引组织表
create table t_user (
id varchar2(50) primary key,name varchar2(20)
) organization index;
合理创建索引才能给查询带来好处,如果索引不合理反而会使效率降低
在索引列上使用like,不等于(<>),is null/is not null,使用函数(如果该列创建了对应的函数索引除外),比较类型不匹配都不会使用到索引.
@H_164_1
403@
原文链接：https://www.f2er.com/oracle/212297.html
