oracle基础知识

安装位置：E：\app\liu\oradata  ：存放数据位置
全局数据库名和SID：orcl
用户名：SYS或SYSTEM  密码：Gepoint
访问数据库：sqlplus / as sysdba  或 sqlplus  system/Gepoint(账号/密码)
退出：quit
配置监听：netca  查看监听：lsnrctl  status
标准端口号：1521
远程访问：(IP：端口/d服务名)：开防火墙
sqlplus epoint/Gepoint@127.0.0.1：1521/orcl
修改密码：
alter user epoint identified by Gepoint
用户与权限：
Oracle角色权限分类：
Connect：只能登录，无法操作
Resource：可以在自己的模式下操作对象
DBA:拥有全部权限，只有DBA才可以创建数据库结构
默认系统账号：
SYS:拥有SYSDBA权限，可以关闭启动oracle，只能以SYSDBA或SYSPOPER登录
SYSTEM:DBA权限，只能以Normal登录，管理数据库
oricle的别名需小心使用，因为有顺序，有时候无法引用。
顺序：from  join  on  where group by  having  
select  order by  limit

操作：
创建表空间(类似与sql数据库)：
create tablespace 表空间名 datafile '物理文件名.dbf'  size 50m --初始大小 autoextend on --自动扩展 next 50m 
例子：CREATE tablespace epoint datafile 'E:\app\liu\oradata\orcl\epoint.dbf' SIZE 100M autoextend ON NEXT 50m;

创建账号登录oracle(使用创建的表空间)：
Create user epoint(账号) identified by Gepoint(密码)  default  tablespace epoint(表空间名);

赋予权限：
Grant dba to epoint;
Grant connect,resource to epoint;
回收权限：
Revoke dba from epoint;
Revoke connect,resource from epoint;
删除用户：
Drop user epoint cascade;(加上cascade会将其创建的东西一起删除)
查看用户权限：
Select * from  dba_role_privs;//dba账号查看所有权限
Select * from  user_role_privs;//普通账号查看自己的权限
Select * from  dba_users;//查看所有用户信息

创建表：create table emp(id int);
插入数据：insert into emp values(1);
删除表数据：delete from emp where 1=1(条件为真全删);回收站可以找回。
oricle分组必须把所有的非聚合函数字段加入到group by 中，不然报错


视图的创建与查询：
Create view view_user as select id,name,age from user;(创建视图)
Select * from view_user;(查询视图)


Oracle数据类型：
Number(p,s):1-22字节，p表示有效数字的位数(38)，s表示精度，正则约束小数位数，负则约束整数小数点左边几位位数四舍五入。都不指定，则都取最大值。
p默认为38，如果只指定p那s默认为0。
Integer:1-22字节，存储整数(有小数则四舍五入)
Char(n): 固定长度，0-2000字节(字符串类型)
Varchar2(N):可变长，0-4000字节
Date:7字节(日期类型)，可精确到秒
Timestasmp,7字节,可精确到纳秒(9位)
Blob(大字段类型)，最大32TB,存储非结构化二进制数据
Clob：4GB,存储大文本数据


创建表空间(类似与sql数据库)：
create tablespace 表空间名 datafile '物理文件名.dbf'  size 50m --初始大小 autoextend on (自动扩展) next 50m maxsize filemaxsize [unlimited](不限制最大大小(32G最大))
例子：CREATE tablespace epoint datafile 'E:\app\liu\oradata\orcl\epoint.dbf' SIZE 100M autoextend ON NEXT 50m maxsize unlimited;

增加数据文件：
Alter tablespace 表空间名 add datafile '物理文件名.dbf'  size 50m --初始大小 autoextend on (自动扩展) next 50m maxsize filemaxsize [unlimited]
例子：alter tablespace epoint add datafile 'E:\app\liu\oradata\orcl\epoint_02.dbf' SIZE 100M autoextend ON NEXT 50m maxsize unlimited;
删除表空间：
Drop tablespace 表空间名 including contents and datafiles;(所有数据及文件)
例子：Drop tablespace epoint including contents and datafiles;
查询所有表空间：
SELECT * FROM dba_tablespaces;

Oracle创建表时，约束索引需要单独创建，不能和表一起创建。

函数：
主要作用于一个date或者interval类型中截取特定的部分
extract (
{ year | month | day | hour | minute | second | 某一时区 }
from { date类型值 | interval类型值} )


触发器：
要素：1.事件类型(增删改) 2.触发事件：事件类型前后(before,after)
3.触发对象：表中的每一条记录(行),即整张表
作用:保证数据一致，基于某个表的变动在其他表记录，计算列的值或更新时间戳
语法(mysql):
Create trigger 触发器名 {before|after} {insert|update|delete} on 表名 for each row  begin  触发器执行的sql语句  end;
触发器名称：64个字符(t或者tri开头)
{before|after}：触发器执行的时间，事件发生前后
{insert|update|delete}：触发的事件是什么
表名：触发器属于那个表
for each row：触发器的执行间隙，for each row子句通知触发器，每行执行一次动作
Sql语句：事件触发时需要执行的sql语句
New:表示将要或已经插入的数据或将要已经被修改的数据
Old:被修改之前的数据或被删除之前的数据
另外old是只读的，而new可以在触发器中使用set赋值且不会触发触发器，造成循环调用。
插入操作：(插入的数据的行的字段改为大写)
Create trigger tri_customer_state before insert on customers for each row beginSet new.cust.state=Upper(new.cust.state) end
更新操作：(如果更新了，则记录在日志)
Create trigger tri_custname_update  after update on customers for each row beginIf new.cust_name != old.cust_name(数据修改了)  then  insert into cust_log  values(old.cust_name,new.cust_name,now());  end if;  end


序列(oracle):
mysql实现自增：create table a(id int not null auto_increment,....primary key(id));
Oracle提供sequence对象，由系统提供自增长的序列号
Create sequence 序列名 [increment by n] [start with n] [maxvalue/minvalue n|nomaxvalue][cycle | nocycle] [cache n | nocache];
1.increment by :定义序列的步长,不写则为1，为负则递减
2.start with：序列的初始值，默认1
3.maxvalue/minvalue：定义序列能产生的最大最小值，默认nomaxvalue则无限制
4.cycle | nocycle：表示序列生成到限制值是否循环，循环(cycle)则从最小序列开始
5.cache n | nocache:定义存放序列的内存大小，默认20，nocache则不进行内存缓存，内存缓冲可以改善序列的性能
创建序列：Create sequence seq_test increment by 1 minvalue 1 start with 1 maxvalue 9999 cache 50;
获取序列当前值:select seq_test.currval from dual;(dual虚拟表)
获取序列的下一个值:select seq_test.nextval from dual;
Oricle实现自增:
创建序列：CREATE SEQUENCE Tab_UserInfo_Sequence START WITH 1 MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 cache 20;
创建使用自增序列的表：CREATE TABLE UserInfo(id number(10) NOT NULL,username varchar2(15) NOT NULL,password varchar2(15) NOT NULL,CONSTRAINTS PF_UserInfo PRIMARY key(id));
使用触发器对表实现自增：CREATE TRIGGER Tig_UserInfo_insert BEFORE INSERT ON UserInfo FOR EACH ROW when(NEW.id IS null)BEGIN   SELECT Tab_UserInfo_Sequence.nextval INTO:NEW.id FROM dual;END