oracle学习笔记3

//数据完整性，商业和逻辑规则，可以使用约束、触发器和应用程序(过程和函数)三种方法来实现
//约束包括 not null，unique， primary key， foreign key， 和  check 五种
//一个表可以有多个unique，但是只能有一个primary key


//商品表， 商品编号为主键，单价大于0，
create table goods(goodId, char(8) primary key,goodsName varchar2(30), unitprice number(10,2) check(unitprice>0),
category varchar(8),provider varchar(20));
//客户表， 
create table customer(customerId char(8) primary key,name varchar2(50) not null --不为空,address varchar2(50),
email varchar2(50) unique --email地址唯一,
sex char(2) default '男' check(sex in('男','女'))--默认为男，不是男就是女
cardId char(18));
//购买表
create table purchase(customerId char(8) references customer(customerId)--外键指向customerId
goodId char(8) references goods(goodId)--外键
nums number(5) check(nums between 1 and 30));

//增加not null约束必须使用modify， 其他使用add
alter table goods modify goodsName not null; //更新goods表的goodsName字段不为空
alter table customer add constraint cardUnique unique(cardId)//增加身份证不允许重复,cardUnique是约束的名字
alter table customer add constraint addressConstrian check(address in ('海淀', '朝阳', '通州'));//增加客户的住址，必须是'海淀' '朝阳' '通州',addressConstrian是约束名字
alter table 表名 drop constraint 约束名称; //删除约束
//删除主键的约束有可能导致错误，所以带上必须cascade选项


//显示约束信息
select * from user_constraints where table_name='表名';  
select * from user_cons_columns where constraint_name='约束名'; //显示约束列


//列级定义，定义列的同时定义约束
//表级定义，定义了所有列之后，再定义约束，注意not null只能是列级定义
create table xxx(empId number(2), name varchar2(15), dept_id number(2), constraint pk_employee primary key empId,
constraint fk_department foreign key (dept_id) references department(dept_id)); //表级定义


//索引
//单列索引
create index nameIndex on customer(name); //在customer表的name字段上建立索引
//复合索引
create index nameSexIndex on customer(sex,name); //在name和sex上建立复合索引，sex的索引更有效，所以写在前面


//原则 1、在大表上 2、使用where语句的查询较多的列   3、索引层次不超过4层，多级索引
//缺点 1、1.2倍的硬盘和内存空间 2、更新数据时额外的系统时间更新索引，维护数据和索引的一致性，不恰当的索引会降低系统性能
不要建索引的情况：很少或不常用的字段，逻辑性的字段，比如sex，'男''女'


//显示表的所有索引
dba_indexs 显示数据库的所有索引信息
user_indexs 显示当前用户的所有索引信息
select * from 
//如果字段是unique，自动增加索引


//权限，
//系统权限(针对数据库)，system_privilege_map  (create table\conn\)
//对象权限(select\alter\delete\update\insert\references\execute)
//角色，是为了简化权限的管理
select * from dba_roles;
//授予系统权限  dba用户或者是带有grant any privilege权限，选项with admin option可以把权限赋值给别的用户 
//创建ken和tom用户
create user ken identified by m123;
create user tom identified by m123;
grant ken create session, create table to ken with admin option;
grant create view to ken;
grant create session, create table to tom with admin option; //用ken用户给tom用户赋权
//回收系统权限，一般是dba用户完成，或者拥有相应的系统权限及转系统权限的选项with admin option
revoke create session from ken; //回收ken的登陆权限，但是tom依然能登陆，证明系统级联权限不会回收 
//授予对象权限，dba用户或者是拥有with grant option 权限，此权限可以赋值给用户，但不能赋给role
grant select on emp to monkey; //scott用户把emp表的查询权限赋值给monkey用户
grant update on emp(ename,sal) to monkey; //monkey用户只能访问emp表的ename和sal两列
授予alter/index/execute权限
选项with grant option 代表可以把权限传递给别的人，该选项只能赋值给用户，但不能赋值给role
对象权限(with grant option)的级联赋值后，第一个用户的权限被rovoke后，其他用户的对象权限也会被回收


//角色 就是相关权限的命令的集合(预定义和自定义)
常用的预定义角色：connect、resource、dba
connect角色(create session ,create cluster, alter session, create database link, create sequence,create table,create view)
resource角色()
dba角色()没有启动和关闭数据库的权限


//自定义角色，一般是dba来创建，或者拥有create role的系统权限
不验证 create role 角色名not identified;
验证 create role 角色名 identified by 密码; //修改角色的时候要提供密码
//给角色授权
conn system/manager;
grant create session to 角色名 with admin option;
conn scott/tiger@数据库;
grant select on emp to 角色名;
grant update on emp to 角色名;
grant delete on emp to 角色名;
//分配角色给用户，应该使用dba用户
conn system/manager;
create user xxx identified by 密码;
grant 角色名 to 用户名 with admin option;
//删除角色
conn system/manager;
drop role 角色名; //角色被删除后，
//显示所有角色
select * from dba_roles;
//显示角色所拥有的系统权限
select privilege, admin_option from role_sys_privs where role='角色名' 
//显示角色所有的对象权限
dba_tab_privs
//显示用户所有的角色，及默认角色
select grant_role,default_role from dba_role_privs where gurantee='用户名'


//精细访问控制
策略，函数，不同客户端查询时，oracle会在查询后自动增加where语句




dba，数据库管理员
//安装升级，建库、表、表空间、视图、索引，指定并实施备份和恢复计划，权限管理、调优、故障排除，项目开发、编写sql语句、存储过程、触发器、规则、约束、包，数据库安全
sys董事长、system是总经理
sys：所有oracle数据库的基表和动态视图，任何用户都不得手工修改，拥有dba、sysdba、sysoper角色或权限
必须以sysdba或sysoper角色登陆，不能以normal形式登陆
system：次级用户数据，拥有dba、sysdba角色或权限，不能创建/删除数据库，不能查看用户数据
dba用户只能在启动数据库实例后进行操作
sysdba>sysoper>dba
shutdown;//关闭数据库
startup;//启动数据库
show parameter;//数据库初始化相关参数，200多个


逻辑备份/
物理备份/数据库关闭状态
导出表、导出方案、导出数据库
导出方案：把用户所有东西都导出了
导出整个数据库：把用户和system的所有方案都导出
//在导入导出的时候，要到oracle的bin目录下进行操作
exp userid=scott/tiger@myOra1 tables=(emp) file=d:e1.dmp; //导出scott用户自己的表，密码tiger，myOra1数据库的表emp到d盘，exp是个程序
//导出其他用户的表，需要dba权限或者exp_full_database
exp userid=system/manager @myOral1 table=(scott.emp,scott.dept) file=d:/e1.dmp;
exp userid=scott/tiger@myOra1 tables=(emp) file=d:e1.dmp rows=n  //rows=n只导出表结构
exp userid=scott/tiger@myOra1 tables=(emp) file=d:e1.dmp direct=y  //direct=y直接导出，比较快


exp userid=scott/tiger@myOra1 owner=scott file=d:e1.dmp //owner=scott 导出方案
system可以导出除了sys的任何用户的方案


//导出数据库，full=y全部导出，inctype=complete增量
exp userid=system/manager@myOral1 full=y inctype=complete file=D:/xx.dmp   


导入表、方案、数据库
imp userid=scott/tiger@myOral1 tables(emp) file=d:/dd.dmp //导入表
imp userid=scott/tiger@myOral1 tables(emp) file=d:/dd.dmp rows=n //rows=n只导入表结构
//导入数据库，full=y全部导入，inctype=complete增量
imp userid=system/manager@myOral1 full=y inctype=complete file=D:/xx.dmp   


数据字典：记录了数据库的系统信息，所有者是sys用户，只读表和视图的组合，用户只可以执行查询(select)操作，其余操作是系统自动完成
由数据字典基表和数据字典视图组成，用户只能查询视图，视图包括user_xxx， all_xxx, dba_xxx
select table_name form user_tables; //当前用户所拥有的表
select table_name from all_tables;  //当前用户可以访问到的所有的表
select table_name from dba_tables; //所有方案的表，必须是dba用户或者拥有select_any_table系统权限


在创建用户的时候，会把用户权限和角色信息存入数据字典，
select * from dba_users;  //所有数据库用户的详细信息
dba_sys_privs   //所有用户具有的系统权限，数据字典视图
dba_table_privs     //用户具有的对象权限，数据字典视图
dba_col_privs //用户具有的列权限，数据字典视图
dba_roll_privs //所有用户所具有的角色，数据字典视图


select * from dab_roll_privs;  //查询所有角色
// 一个角色包含多少权限，系统权限，对象权限
select * from system_privilege_map order by name; //所有系统权限
select dinstinct privilege from dba_tab_privs; //所有对象权限
select * from dba_sys_privs where grantee='CONNECT'; //CONNECT 角色的系统权限
select * from dba_tab_privs where grantee='CONNECT'; //CONNECT 角色的对象权限
select * from role_sys_privs where role='CONNECT'; //CONNECT 角色的权限 role_sys_privs和dba_sys_privs存的数据有相同点
select * from dba_role_privs where gurantee='SCOTT'; //scott用户具有哪些权限


select * from global_name; //myoral.us.oracle.com  显示当前数据库的全称
select * from dict where comments like '%grant%'; //显示当前用户可以访问的所有数据字典的视图


动态性能视图 v_$ 开头，一般是sys所有


从物理上讲，数据库是存放在数据文件中，从逻辑上讲，数据库是存放在表空间中，表空间由一个或多个数据文件组成
逻辑结构包括数据库、表空间、段、区、块，数据库由表空间组成，表空间由段组成，段由区组成，区由块组成
通过表空间可以实现：
控制数据库占用的磁盘空间
dba将不同的数据类型部署到不同位置，提高I/O性能，利于恢复和备份


//建立表空间
create tablespace      //dba用户或者具有create tablespace 权限
create tablespace data01 datafile d:/data01.dbf size 20m uniform size 128K;  //创建20兆大小的表空间(最大500m)，区的大小为128K
create table mypart(deptno number(4),deptname varchar2(10),loc varchar2(14)) tablespace data01;//创建表mypart，放在data01的表空间中，如果没有指定表空间，默认是在system表空间中


//改变表空间状态
alter tablespace data01 offline; //使表空间data01脱机
alter tablespace data01 online;  // 使表空间data01联机
alter tablespace data01 read only;  // 使表空间data01只读
alter tablespace data01 read write;  // 使表空间data01可读可写


select * from all_tables where tablespace="data01"; //查询data01表空间中的所有的表
select tablespace_name, table_name from user_tables where table_name='EMP';//查询emp属于哪个表空间


drop tablespace data01 including contents and datafiles; //删除表空间


//扩展表空间三种方法：1增加数据文件   2、增加数据文件的大小  3、设置自动增长 
alter tablespace data01 datafile 'd:/data02.dbf' 300M; //为表空间data01增加另外一个数据文件data02.dbf，300M大小
alter tablespace data01 'd:/data01.dbf' resize 100M;  //把原来的数据文件改变为100M大小
alter tablespace data01 'd:/data01.dbf' autoextend on next 10M maxsize 500M; //设置表空间data01文件的自动增长，


//移动数据文件
1、确定属于哪个表空间
2、将表空间转为offline
alter tablespace data01 offline;
3、使用命令移动到指定位置
host move d:/data01.dbf c:/data01.dbf
4、执行alter tablespace命令
alter tablespace data01 rename datafile 'd:/data01.dbf' to 'c:/data01.dbf'
5、使表空间联机
alter tablespace data01 online;
//表空间状态，online、offline、read only、read write
//索引表空间、undo表空间、临时表空间、非标准块的表空间




日期函数：
sysdate  //系统时间
select sysdate from dual; //获取系统日期
select ename form  emp where sysdate>add_months(hiredate,300); //300个月之前入职的
select floor(sysdate-hiredate) "入职天数" from emp; //计算入职天数
select hiredate last_day(hireday) from emp;
select hiredate ename from emp where last_day(hireday)-2=hireday; //倒数第三天入职的员工


转换函数：
oracle是隐形转换
select ename to_char(hiredate,'yyyy-mm-dd h24:mi:ss') from emp; //指定日期格式的显示
select ename to_char(sal,'L99999.99') from emp;  //显示货币币种，L代表本地币种，9代表数字，点和逗号是原意，$表示美元，0表示数字不足补0
select ename hiredate from emp where to_char(hiredate,'yyyy')=1980;//1980年入职的员工


系统函数：
select sys_context('userenv','db_name') from dual; //获取当前的数据库
select sys_context('USERENV','language') form dual;//获取当前的语言
select sys_context('userenv','session_user') from dual;//获取当前的用户
select sys_context('userenv','current_schema') from dual;//获取当前的方案
oracle是以方案的形式来管理数据对象的，包括表，存储过程、触发器等等