oracle数据库知识总结

oracle数据库知识总结

1.创建表空间

create tablespace demodata //表名
datafile ‘c:\demodata.dbf’ //物理地址
size 100m //大小
autoextend on //自动扩容
next 10m; //扩容大小

2.删除表空间

drop tablespace demodata;

3.创建用户

create user demouser //用户名
identified by demopass //密码
default demodata; //默认表空间
(此时的用户因为没有被授予权限，还不能使用)

4.授予权限

connect --连接角色
resource --开发角色
dba --超级管理员
grant dba to demouser;

5.创建表

create table users(
uid NUMBER(20)，
username varchar2(10),
gender varchar(3)
)

6.修改表结构

①添加列
alter table users add(
password varchar(20),
hobby varchar(50)
)
②修改列结构
alter table users modify gender char(1);

③修改列名
alter table users rename column gender to sex;

④删除列
alter table users drop column hobby;

7.创建序列(每次默认自增的一个值，可以当作主键使用)

create sequence s_users; s_users.currval 当前值 (注意：第一次使用currval前要先 使用一次nextval 因为开始时没有值)； nextval 下一个值 默认自增1
再创建用户时，使用 insert into users (uid,username) values (s_users.nextval,‘xxx’);

8.scott 用户 默认密码tiger

这个用户默认锁定，解锁命令如下
解锁用户
alter user scott account unlock;
解锁(修改)密码
alter user scott identified by xxx;

9.dual 虚表 为了补全语义，没有实际意义

10.日期函数

日期可以直接加减 得到一个 数字 为两日期之间所隔天数
系统当前日期:sysdate
month_between(date1,date2) 算出两个日期之间的月数
month_between(date1,date2) / 12 算出两个日期之间的年数
(date1 - date2) / 7 算出两个日期之间的周数

日期转换函数
日期-> 字符串
to_char(date,“fm yyyy-mm-dd hh24:mi:ss”)
fm: 去除前面的0 24：24小时制
字符串->日期
to_date(“2020-3-22 10:44:45”,“fm yyyy-mm-dd hh24:mi:ss”)

11.nvl 函数

nvl(val1,val2) 如果val1为null 使用val2 值 如果不是null 使用val1

12.case xxx (固定值 case)

​ when xxx then xxx
​ when xxx then xxx
​ …
​ [else xxx]
​ end

13.case (表达式 case)

​ when xxx?xxx then xxx
​ when xxx?xxx then xxx
​ …
​ [else xxx]
​ end

14.oracel 独有的条件表达式:

decode(字段,xxx,xxx,xxx,xxx,[xxx]) xxx第一个和第二个对 第三个和第四个对 最后一个为else的内容 可以省略

15.oracle 中除了起别名中用 “”（可以不用） 别的地方用’’

16.查询注意点 select

在使用group by时，如果要查询的字段不在group by 中 要用聚合函数包裹才行 ，因为数据库也不知道你要查的是哪一条
所有的查询条件中都不能使用查询字段的别名 因为 查询条件的执行顺序优于select+字段
where 过滤分组前的数据 having 过滤分组后的数据

17.oracle专有外连接

select * from emp e , dept d
where e.deptid = d.deptid(+); ==左外连接

select * from emp e , dept d
where e.deptid(+) = d.deptid(); ==右外连接

18.自连接就是在不同角度上，把一张表看成多张表
select emp_name , mana_name from
emp e1 , emp e2
where e1.mana_id = e2.emp_id;
查询员工表1中领导编号等于员工表2中员工编号的 值

19.分页查询

查询表中按工资倒序排列的第二页的五条记录（每页五条）
工资倒序
select * from emp order by sal desc;
工资倒序加行号
select rownum, t.*from(
select * from emp order by sal desc
) t;
工资倒序加行号加第二页6-10 条记录
select * from(
select rownum rn, t.*from( //前
select * from emp order by sal desc
) t where rownum < 11 //后
) where rn > 5;

20.视图

create view xxx as …(查询语句) [with read only];
修改视图 原表数据会改变 加上 with read only 就改不了了
作用:隐藏一些敏感字段，不让人看
保证总公司和分公司之间数据保持一致。(分公司看视图，只要总公司的数额变了，分公司那里实时就能看到，不必select)

21.索引

索引的概念就是在表的列上创建一个二叉树
可以提高查询效率，但是会降低增删改效率
单列索引： 创建 create index idx_username on users(username);
触发条件:条件必须是索引列中的原始值，单行函数、模糊查询都会影响索引的触发
复合索引： 创建 create index idx_namepass on users(username,password);
复合索引中第一列为优先检索列 如果要触发复合索引，必须包含有优先索引列中的原始值
同时符合单列索引和符合索引的查询语句，优先使用单列索引
or的话 一边触发索引，一边不触发的情况下，为不触发

22.触发器

就是制定一个规则，在我们做增删改操作的时候
—只要满足该规则，自动触发，无需调用
—语句级触发器 不包含 for each row
—行级触发器：包含 for each row
加for each row 是为了使用：old或者：new对象或者一行记录