Oracle数据库常用的概念及SQL

1.体系结构

实例

拥有一系列后台进程和存储结构，一个数据库可拥有一个或多个实例，一般只有1个实例

数据文件（.dbf/.ora）

数据文件是数据库的物理存储单元，一个表空间由一个或多个数据文件组成，一个数据文件只能属于一个表空间。当某个数据文件被加到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属的表空间

表空间

实质上是对数据文件的一种逻辑映射，大小由所包含的数据文件所定义，每个数据库至少有一个表空间

用户

用户在表空间下创建

oracle数据库相当于房间，表空间相当于房间内的空间，可以自由分配，数据文件相当于空间内堆放的箱子，表则为箱子内放的物件。

Oracle中的几类日志文件

Redo log files      -->联机重做日志
Archive log files   -->归档日志
Alert log files     -->告警日志
Trace files         -->跟踪日志
    user_dump_dest          -->用户跟踪日志
    backupground_dump_dest  -->进程跟踪日志

联机重做日志

Oracle数据库有联机重做日志 这个日志是记录对数据库所做的修改 比如插入 删除 更新数据等 对这些操作都会记录在联机重做日志里 当一个联机重做日志组被写满的时候 就会发生日志切换 这时联机重做日志组 成为当前使用的日志 当联机重做日志组 写满的时候 又会发生日志切换 去写联机重做日志组 就这样反复进行

归档模式

如果数据库处于非归档模式 联机日志在切换时就会丢弃 而在归档模式下 当发生日志切换的时候 被切换的日志会进行归档 比如 当前在使用联机重做日志 当 写满的时候 发生日志切换 开始写联机重做日志 这时联机重做日志 的内容会被拷贝到另外一个指定的目录下 这个目录叫做归档目录 拷贝的文件叫归档重做日志

数据库使用归档方式运行时才可以进行灾难性恢复

归档日志模式和非归档日志模式的区别

非归档模式只能做冷备份 并且恢复时只能做完全备份 最近一次完全备份到系统出错期间的数据不能恢复

归档模式可以做热备份 并且可以做增量备份 可以做部分恢复

使用下面的SQL语句去查看归档信息。
SQL> archive log list

2.Oracle启动和关闭

数据库启动三种状态

• no mount
  只启动数据库实例，不启动控制文件、数据文件。
• mount
  启动数据库实例，启动控制文件，不启动数据文件。
  该状态ORACLE根据参数文件中控制文件的位置找到并打开控制文件，读取控制文件中的各种参数信息，如数据文件和日志文件的位置等。
• open
  数据库完全启动。
  该状态数据库将打开数据文件并进行一系列的检查工作，这些检查工作用于数据恢复

关闭数据库命令

• Shutdown immediate
  这种方式可以较快且安全地关闭数据库，是DBA经常采用的一种关闭数据库的方式，此时ORACLE会做一些操作，中断当前事务，回滚未提交的事务，强制断开所有用户连接，执行检查点把脏数据写到数据文件中。虽然参数IMMEDIATE有立即关闭数据库的含义，但是它只是相对的概念，如果当前事务很多，且业务量很大，则中断事务以及回滚数据、断开连接的用户都需要时间 。
• Shutdown transactional
  使用TRANSACTIONAL参数时，数据库当前的连接继续执行，但不允许新的连接，一旦当前的所有事务执行完毕，则关闭数据库。
  显然这种方式，通常情况下，在生产数据库系统中，这方式也不会快速关闭数据库，因为如果当前的某些事务一直执行，或许会用几天时间关闭数据库。
• Shutdown abort
  这是一种很不安全地关闭数据库的方法，最好不要使用使用该方式关闭数据库。SHUTDOWN ABORT关闭数据库时，ORACLE会断开当前的所有用户连接，拒绝新的连接，断开当前的所有执行事务，立即关闭数据库。使用这种方式关闭数据库，当数据库重启时需要进行数据库恢复，因为它不会对未完成事务回滚，也不会执行检查点操作。
• Shutdown normal
  这种方式是SHUTDOWN数据库的默认方式，如果用户输入SHUTDOWN，则默认采用NORMAL参数，这种方式关闭数据库时，不允许新的数据库连接，只有当前的所有连接都退出时才会关闭数据库，这是一种安全地关闭数据库的方式，但是如果有大量用户连接，则需要较长时间关闭数据库。

示例

• 关闭数据库。
  SQL>shutdown immediate
• 开启数据库mount状态。
  SQL>startup mount
• 查看控制文件是否打开。
  SQL> select * from v$controlfile;
• 从MOUNT状态切换到OPEN状态。
  SQL> alter database open；
• 验证控制文件是否打开。
  SQL>select * from scott.dept;
• 直接启动数据库
  SQL>startup open

３.表空间的相关操作

查看表空间

select * from dba_tablespaces;
select tablespace_name from dba_tablespaces;
desc dba_tablespaces;

创建表空间

create tablespace 表空间的名字
datafile ‘XXX.dbf’ # 指定表空间的数据文件
size XXXm # 指定数据文件的大小
autoextend on next YYm # 开启自动增长，指定每次扩容的大小
maxsize unlimited; # 限制数据文件最大的大小，unlimited时，最大为32G

查看数据文件

select * from dba_data_files;
select file_name from dba_data_files;

更改表空间

扩容1：增加数据文件大小

alter database datafile ‘XXX.dbf’ resize YYYm;

扩容2：增加数据文件数量

alter tablespace 表空间的名字 add datafile ‘’ size XXXm autoextend on next YYm maxsize unlimited;

删除数据文件

alter tablespace 表空间的名字 drop datafile ‘xxx.dbf’;

删除表空间

drop tablespace 表空间的名字；

４.用户相关操作

角色，默认的有connect、resource、dba

select * from dba_role_privs where grantee = ‘用户名大写’;

权限

select * from system_privilege_map; # 查看所有系统权限
select distinct privilege from dba_tab_privs; # 查看所有对象权限

select * from dba_sys_privs where grantee = ‘CONNECT’;
select * from dba_sys_privs where grantee = ‘RESOURCE’;
select * from dba_sys_privs where grantee = ‘DBA’;

select * from dba_sys_privs where grantee = ‘角色名称’;

查看所有用户

select * from dba_users;
select * from all_users;
select * from user_users; # 查看当前用户信息

查看用户拥有的表

select * from all_tables where owner = ‘用户名大写’；

切换用户

conn 用户名 # scott 用户默认密码为：tiger

修改账户状态

alter user 用户名 account lock|unlock;

创建用户

create user 用户名
identified by 密码
default tablespace 表空间名

alter user 用户名 identified by 密码 # 修改密码

用户系统权限赋予与撤回

grant XXX to 用户名
grant XXX to 用户名 with admin option # 可以把自己的权限赋予其他用户
revoke XXX from 用户名

用户对象权限赋予与撤回

赋予、撤回某项权限

grant XXX on USERNAME.TABLENAME to 用户名；
revoke XXX on USERNAME.TABLENAME from 用户名；

赋予、撤回所有权限

grant all on USERNAME.TABLENAME to 用户名；
revoke all on USERNAME.TABLENAME from 用户名；

删除用户

drop user 用户名
drop user 用户名 cascade # 删除用户及下属所有表

５.角色相关操作

创建角色

create role 角色名称

赋予与撤回权限

grant XXX to 角色名称
revoke XXX from 角色名称

删除角色

drop role 角色名称

６.表相关操作

建表

create table 表名(
字段名 字段类型(长度) 限制，
字段名 字段类型(长度) 限制，
字段名 字段类型(长度) 限制，

constraint 限制名称 cons,
constraint 限制名称 cons,
constraint 限制名称 cons
) tablespace 表空间名;

字段类型

1. number(n, m): 数字类型，n代表数字范围为n（包含m），m指定小数点后精度，默认n=18
2. char(n): 字符串类型，长度固定，用空格扩充，最大存储2000B
3. varchar2(n): 字符串类型，长度可变，最大存储4000B
4. long : 存储4G
5. date：日期类型的数据，sysdate
6. clob：存储字符，最大4G
7. blob：存储图像、声音等二进制数据，最大4G

限制

1. primary key：主键，可以唯一标识一行数据的字段
2. not null：要求字段非空
3. unique：要求字段不重复
4. default VAL：设定字段的默认值

1.constraint TBALE_PK primary key(COL, COL);
2.constraint TBALE_FK foreign key(COL) references TABLE_NAME(col); # 参考其他表的主键
3.constraint AGE_CK check((age>0) and (age<200));
4.constraint GENDER_CK check(GENDER in (‘M’, ‘F’, ‘O’));

操作约束

查看

select constraint_name, constraint_type from user_constraints where table_name = ‘’;

constraint_type

1. C : check
2. P : primary key
3. U : unique
4. R : foreign key

其他

alter table 表名 add constraint 限制名称 cons # 添加约束
alter table 表名 disable constraint 限制名称 # 关闭约束
alter table 表名 enable constraint 限制名称 # 开启约束
alter table 表名 drop constraint 限制名称 # 删除约束

示例

create table class(
cid number(4) primary key,
teacher varchar2(20) not null,
phone char(4) unique
);

create table student(
sid number(4) primary key,
sname varchar2(20) not null,
gender char not null,
phone char(4) unique,
age number(3),
cid number(4),
constraint AGE_CK check((age>0) and (age<150)),
constraint GENDER_CK check(GENDER in (‘M’, ‘F’, ‘O’)),
constraint STU_FK foreign key(cid) references class(cid)
);

插入数据

insert into 表名(字段名, 字段名,…) values(值1, 值2, …);
insert into 表名 values(值1, 值2, …);

修改数据

update 表名 set 字段名=值 where…

删除数据

delete from 表名 where…
delete from 表名
truncate table 表名

修改表

rename 旧表名 to 新表名

增加字段

alter table 表名 add(
字段名 字段类型(长度) 限制，
字段名 字段类型(长度) 限制，
字段名 字段类型(长度) 限制
);

修改已有字段类型

alter table 表名 modify(
字段名 字段类型(长度) 限制，
字段名 字段类型(长度) 限制，
字段名 字段类型(长度) 限制
);

修改字段名称

alter table 表名 rename column 旧字段名 to 新字段名；

删除字段

alter table 表名 drop column 字段名；

删除表

drop table 表名；

７.查询

select * from 表名； # 查询所有数据
select 字段名,字段名, from 表名；

单表查询

条件查询

select * from 表名 where …
select * ftom 表名 where 字段=值；

模糊查询：%用于匹配0-n个字符，_用于匹配一个字符

where 字段 like ‘%KEY%’ # 含有KEY的记录
where 字段 like ‘%KEY’ # 以KEY结尾的
where 字段 like ‘KEY%’ # 以KEY开始的
where 字段 like ‘KEY’ # KEY前后各有一个字符的

范围查询

where 字段 >|>=|<|<= 值
where 字段 between lo and hi # lo和hi范围之间，包含lo和hi
where 字段 in (值) # 选择
where 字段 not in (值)

空值查询

where 字段 is null
where 字段 is not null

查询结果去重

select distinct 字段

查询结果排序

select * from 表名 oder by 字段 [asc]# 默认升序
select * from 表名 oder by 字段 desc# 降序

聚合统计

select sum(字段) from
select max(字段) from
select min(字段) from
select avg(字段) from
select count(*) from

分组统计

select 字段,聚合统计 from 表名 group by 字段 # select后面只可以跟聚合函数或者是分组聚合的条件
select 字段,聚合统计 from 表名 group by 字段 having count(*) > 值 #

练习

1. 查询每个dept部门分别有多少员工、平均工资是多少
   select deptno, count(*), avg(sal) from emp group by deptno;
2. 查询每个dept部门、job岗位分别有多少员工、工资最高是多少
   select deptno, job, count(*), max(sal) from emp group by deptno,job;
3. 查询comm为0的有多少员工
   select count(*) from emp where comm=0 or comm is null;
4. 查询comm不为0的员工数量及平均工资
   select count(*), avg(sal) from emp where comm is not null and comm > 0;
5. 查询sal工资在[1200, 3000]内的员工数量
   select count() from emp where sal between 1200 and 3000;
   select count() from emp where sal>=1200 and sal<=3000;
6. 查询各岗位的最高工资，并按最高工资降序排序
   select job, max(sal) from emp group by job order by max(sal) desc;
7. 查询最低工资小于1500的岗位
   select job, min(sal) from emp group by job having min(sal)<1500;

伪列查询

select rowid, t.* from emp t;

select rownum, t.* from emp t; # 在生成查询记录时赋值rownum，从1开始计数
select rownum, t.* from emp t where rownum > 0;
select rownum, t.* from emp t where rownum >= 1;
select rownum, t.* from emp t where rownum <= n; # 前n条记录
select rownum, t.* from emp t where rownum != n; #前n-1条记录

多表连接查询

内连接

select * from emp e, dept d;
select e.ename, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;

左外连接

select d.dname, e.ename from dept d left join emp e on d.deptno = e.deptno;
select d.dname, e.ename from dept d, emp e where d.deptno=e.deptno(+);

select d.dname, count(*) from dept d left join emp e on d.deptno = e.deptno group by d.dname;
select d.dname, count(e.ename) from dept d left join emp e on d.deptno = e.deptno group by d.dname;

右外连接

select e1.ename as directboss, e2.ename as clerk from emp e1 right join emp e2 on e1.empno=e2.mgr;
select e1.ename as directboss, e2.ename as clerk from emp e1, emp e2 where e1.empno(+)=e2.mgr;

子查询

where子句中加入子查询

单行

select ename, sal from emp where sal > (select avg(sal) from emp);

多行

select ename, sal from emp where ename in (select ename from emp where ename like ‘%S’);

from子句中加入子查询

select * from (select deptno, count(*) as cnt from emp group by deptno) where cnt > 3;

select子句中加入子查询

select e.ename, e.sal, (select grade from salgrade s where e.sal between s.losal and hisal) salgrade from emp e;

select e.ename, e.sal, g.grade from emp e, salgrade g where e.sal between g.losal and g.hisal;

８.数据导出与导入

cmd下，文件保存在cmd当前目录

整库导出与导入

exp system/orcl file=data.dmp full=y
imp system/orcl file=data.dmp full=y

按用户导出与导入

exp system/orcl file=scott.dmp owner=‘SCOTT’
imp system/orcl file=scott.dmp fromuser=‘SCOTT’

按表导出与导入

exp scott/tiger file=emp.dmp tables=‘EMP’
imp scott/tiger file=emp.dmp tables=‘EMP’