Oracle数据库总结

一、SQL语言分类：

1.DDL（数据定义语言）：  create（创建）    alter（修改）  drop（删除）------------主要是对表操作。

 

2.DML（数据操作语言）：  insert(插入)    delete(删除)  update(更新)  select(查询) 、select......for update(查询更新)----表中数据进行操作

 

3.DQL数据查询语言：基本语句、Order by 子句、Group by 分组语句

 

4.TCL（事务控制语言）：Commit(提交) 、Savepoint(保存) 、rollback(回滚)、

 

5.DCL（数据控制语言）：Grant(授权)、revoke(撤销)命令。

 

二、Oracle数据类型：

 

Char:  字符型（最大长度2000，定长、不足时以空格补充）

 

Varchar2:字符型 最大长度 4000，变长，实际长度由存储的数据长度决定（与存储的数据长度一致）

 

Number(x,y):既可以存储浮点型，也可以存储整形，x表示有效位数的最大位数，y表示小数位最大位数。

 

Date：存储时间类型。默认格式：dd--mm--yy:  天-月-年。

 

Clob:存储较大的文本，比如存储非结构化XML文档，最大为 4G

 

Blob:存储二进制对象，如图形、视频、声音等。

 

Long :存储较长字符串，最大长度为2G

 

 

 

三、表管理：

1.创建表：

 Create   table   表名（

字段名称   类型  约束

 

）

2.修改表结构

2.1、添加列

Alter  table  表名 add( 字段名称  数据类型 )

 

例子：alter table test_tb add(test_name varchar2(200))

 

2.2、删除表中一列

 

Alter   table 表名  set unused  column 列名

 

例子：alter table test_tb set unused column test_name

 

一般：不建议删除数据库中的列。

 

2.3、修改列名

 Alter table 表名  rename  column 旧列名 to 新列名

例子：alter table test_tb rename column test_msg to msg

 

2.4、修改表名：

Rename  表名1 （旧名字）to 表名2（新名字）

例子：rename  test_tb to test_tb1

 

2.5、修改表字段

Alter   table  表名 modify( 字段名称  新的字段类型 )

例子：alter table test_tb1 modify(msg varchar(4000))

 

2.6、添加表约束：

Alter table 表名 add Constraint 约束名 约束内容

例子：

alter table test_tb1 add constraint  p_k primary key(test_num)-----主键约束

alter table test_tb1 add constraint c_k check(msg='男' or msg='女')---检查约束

Not null----非空约束

四、数据操作语言：DML

对oracle自带的表进行训练操作

简单查询 ：

Select *|列名|表达式 from 表名 where 条件 order by 列名

1.* 表示所有列

2.列名可以选择若干个表中列名，各个表中列名用逗号分隔。

3.表达式可以是函数，列名，常数等组成表达式。

4.Where子句是查询的条件

5.Order by  要求在查询结果中排序，默认是升序。

例子：

select * from emp order by sal desc（降序）

select * from emp order by sal （升序）

select * from emp order by sal asc(升序)

 

插入：

Insert into 表名  values(所有字段对应值)；

Insert into 表名（字段名1,字段名称2....） values(对应字段值)

例子：insert into test_tb1(test_num,msg) values(666,'MSG values')

 

更新：

Update 表名 set 字段名称 =值 where 更新条件

例子：update test_tb1 set msg='hello oracle' where test_num=123

 

删除：

Delete 表名 where 条件

例子：delete test_tb1 where msg='hello oracle'

Truncate--将表中数据一次性删除
语法： truncate table 表名

Truncate和detele区别：

1、truncate是DDL命令，删除数据不能回复；delete是DML命令，删除数据可以通过数据库的日志文件进行恢复。

2、如果一个表中记录很多，truncate相对delete速度快。

警告：由于truncate命令比较危险，所有在实际开发中，truncate命令慎用。

五、操作符：

算术操作符： + 、 - 、* 、/ （加、减、乘、除）

关系运算和逻辑运算符：

=  等于

> 大于

<>或 != 不等于

<= 小于或等于

<   小于

>= 大于或等于

 

 

逻辑运算符：

And  、 or 、 in  、not in

And:且

Or: 或者

Not in 不在...中

In:在.....中

 --加法
例子：SELECT ename,job,(sal+comm) FROM emp;
--减法
例子：SELECT ename,job,(sal-comm) FROM emp;
--双竖线 ||
例子：SELECT (ename||'的工资'||(sal+comm)) FROM emp;

字符串连接操作符： ||  （双竖线）

例子：select (ename||' 的工资是：'||(sal+comm)) from emp

1. 当前主流数据库：

|-微软：SQL Server、access

|-瑞典:Mysql

|-IBM: DB2

|-Sybase: Sybase

|-Oracle:Oracle

A. 小型数据库：access、foxbase。负载量小，100人内，成本千元内，安全性要求不高。例如留言板等。

B. 中型数据库：Mysql、SQL Server、Informix。日访问量5000-15000，成本万元内。例如电子商务网站等。

C. 大型数据库：Sybase<Oracle<DB2。海量负载。

2. 选择数据库，要看项目的规模：

a) 负载量多大（用户多大）

b) 成本

c) 安全

3. Oracle认证：

a) DBA认证：OCA初级认证、OCP专家认证、OCM大师认证

b) Java开发认证

c) Oracle网络开发认证

4. Oracle与其他数据库不同的是，每次登陆，登陆的是Oracle数据库的实例。每个用户，例如sys、system、scott，每个用户名登陆的数据库看到的数据对象（表、存储过程等）不同。

5. Oracle默认生成三个用户：

1. sys用户：超级管理员，权限最高，角色dba

2. system用户：系统管理员，角色dbaoper

3. scott用户：普通用户，密码tiger

sys有create database的权限，而system没有。

6. Oracle管理工具：

1. SQLPLUS

2. Oracle Enterprise Manager Console

3. pl/sql developer

7. SQL * PLUS重用命令

1. 连接命令conn[etc]:conn system/conanswp;

2. 断开连接：disc[onnect]

3. 修改密码：passw[ord];需要使用sys、system用户登录。

4. 显示当前用户：show user;

5. 退出：exit

8. 文件操作命令

1. 运行脚本：start files或者@file

2. 编辑脚本：edit file

3. 将sql*plus内容输出到文件中：spool file；select * from emp;spool off;输出查询内容到file中。

4. 提示用户输入：&name eg：select * from emp where ename=‘&name’;

9. 其他命令：

1. 显示每行字符数linesize：set linesize 30；

2. 显示分页pagesize：set pagesize 2；

10. Oracle用户管理：

1. 创建用户：create user；eg: create username identified by password；

2. 修改密码：password user或者alter user identified password；

3. 删除用户：drop user [cascade];如果待删除的用户已经创建了表，就需要带上cascade参数。

4. 指定用户的权限：grant；eg：grant connect to user；connect是权限。Oracle大约有140多种系统权限，25种对象权限。

5. 回收权限使用命令：revoke；revoke select on emp from user;

6. 一个用户查询另一个用户的表（使用对象权限）：grant select on emp to user;允许user查询emp表（sys、system和 scott）。user查询如下：select * from scott.emp;

7. 一个用户允许查询另一个用户的表并希望此用户也可以继续让第三个用户查看表：

|-如果是对象权限，使用with grant option。Eg：grant select on emp to user1 with grant option;

|-如果是系统权限，使用with admin option。

A->B->C（A让B访问A中表，B让C访问A中表），如果A回收B，现在C也不能访问A中的表了。

11. 权限：

|-系统权限：用户对数据库的相关权限。

|-对象权限：用户对其他用户的数据对象操作的权限。对象权限分为：select、insert、update、delete、all等。

12. 数据对象：用户创建的表、存储过程、视图等。

13. 角色：权限批量授权给角色。方便权限的授予。角色分为：自定义角色和预定义角色。重要的角色：DBA(系统管理)，r esource（允许在表空间建表）。grant resource to user；允许user创建表。

14. profile是口令限制，资源限制的命令集合。

1. 账户锁定： create profile lock_account limit failed_login_attempts 3 password_lock_time 2;alter user xiaoming profile lock_acco unt每个用户只能尝试登陆3此，锁定时间为2天。创建的profile名字为lock_account。

2. 给账户解锁：alter user xiaoming account unlock;

3. 终止口令，为了让用户定期修改密码。 Create profile myprofile limit password_life_time 10 password_grace_time 2;alter user xiaoming profile myprofile;每 隔10天修改自家的登陆密码，宽限期为2天。

4. 禁止新旧密码相同： create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10; alter user xi aoming profile password_history;

5. 删除profile：drop profile password_history;

15. Oracle表的管理。

1. 表名、列名的命名规则：字母开头，不超过30字符，不能使用oracle保留字。

2. 支持的数据类型：

a) 字符型：

i. char（定长）：char(10) ‘Conan’。5个存放字符，5个空着，但是查询效率高。最大2000字符。

ii. varchar2（变长）：自动增加、减少，节省空间。最大4000字符。

iii. clob（character large object）：字符型大对象，最大4G。

b) 数字型：number(4):4位。number(5,2)：共5位，2位小数。

c) 日期类型：

i. Date：包括年月日时分秒

ii. Timestamp：时间戳

d) 二进制数据：blob。可存放图片、声音

3. 建表：

create table student(xh number(4), xm varchar2(20),sex char(2),birthday date, sal number(7,2));

4. 修改表：

a).添加字段

alter table student add (classid number(2));

b).修改字段

alter table student modify (classid number(2));

c).删除字段

alter table student drop column sal;

d).修改表名

rename tablename to stu；

e).删除表名

drop table tablename；

5. 查看表：desc tablename；

6. 添加数据：Oracle默认日期格式’DD-MON-YY’

//01-12月-13中“月”必须写上！ insert into student values(‘a001’,’conan’,’male’,’01-12月-13’); //插入部分字段 insert into student (xh) values(‘a001’); //添加空值 Insert into student(xh,xm,sex,birthday) values (‘a004’,’MARTIN’,’男’,null);

通过：alter session set nls_date_format = ‘yyyy-mm-dd’;修改日期默认格式。这样就不用写“月”了。

7. 修改字段数据：

//修改一个字段 update student set sex=’female’ where xh=”a004”; //修改多个字段 update student set sex=’female’，birthday=’xx’  where xh=”a004”;

8. 删除数据

//删除所有记录，表结构还在，因为写入日志，可以恢复数据 delete from student; //删除表的结构和数据 drop table student; //删除一条记录 delete from student where xh=’a001’; //删除表中所有记录，表结构还在，不写日志，无法找回 truncate table student;

9. 设置保存点：savepoint aa;

10. 恢复到保存点：rollback to aa;

16. 查询语句select。

EMP表结构：EMPNO/ENAME/JOB/MGR（上级的编号）/HIREDATE/SAL/COMN（奖金）/DEPTN（所在部门）。

DEPT表：DEPTNO/DNAME/LOC

1. 查看表结构:desc dept;

2. 查询所有列：select ename from emp;

3. 查询指定列：select ename, sal, job from emp;

4. 取消重复行：select distinct deptno, job from emp;

问题：

1. 查询SMITH的薪水、工作，所在部门：(oracle大小写不区分，但是单引号中的区分大小写)

Select deptno,job,sal from emp where ename=’SMITH’;

2. 使用算数表达式显示每个员工的年工资

Select ename, sal*12+nvl(comm.,0)*12 from emp；nvl表示如果comm是null，则替代为0；不为空，就用comm本身。否 则，oracle规定，同含有null的进行运算，结果整体为null。

Select ename, sal*12 “年工资” from emp;(年工资是sal*12的别名)

3. 如何显示工资高于3000的员工

Select ename,sal from emp where sal>3000 and sal<10000;

4. like操作符。%表示0到多个字符；_:表示任意单个字符。显示首字母为S的员工姓名和工资。

Select ename,sal from emp where ename like ‘S%’;

5. where中使用in。显示empno为123,345,800的雇员情况

select * form emp where ename in (123,345,566);

6. is null.显示没有上级的雇员

select * from emp where MGR is null.

7. 逻辑运算符。and or

Select * from emp where (sal > 50 or job =’MANAGER’)注意括号

8. order by.按照工资从低到高的顺序显示雇员信息。

Select * from emp order by sal [asc];//从低到高

Select * from emp order by sal desc;//从高到底

按照部门号升序而雇员工资降序排列：

Select * from emp order by deptno, sal desc;

对年薪排序：

Select ename,sal*12 “年薪” from emp order by “年薪”;

9. 分页查询：详见子查询。

17. Oracle复杂查询

1. 数据分组：max,min,avg,sum,cout;

显示所有员工中最高工资和最低工资：

select max(sal),min(sal) from emp;

select ename,sal from emp where sal=(select max(sal) from emp);

2. group by（查询结果分组统计）和having子句（限制分组显示结果）

显示每个部门的平均工资和最高工资：

Select avg(sal),max(sal),deptno from emp group by deptno;

显示每个部门的每种岗位的平均工资和最低工资

Select avg(sal),min(sal),deptno,job from emp group by deptno,job;

显示平均工资低于2000的部门号和他的平均工资：

Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

总结：

1. 分组函数只能出现在选择列表、having、order by子句中；

2. 如果在select语句中同时包含group by，having，order by，那么他的书序是group by，having，order by。

3. 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现子group by 子句中，否则就会出错。

18. 多表查询：

1.显示雇员名，雇员工资及所在部门的名字：

Select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;不加where条件的话，是用第一个表中的每一条跟第二个表的全部构成，一共为第一表长度乘以第二个表长度。多表查询的条件至少是（表数-1）！

2.自连接：在同一张表的连接查询。显示某个员工的上级领导的姓名。

select worker.ename,boss.ename from emp worker,emp boss where boss.empno=worker.mgr;

3.子查询：嵌入在其他sql语句中的select查询。

单行子查询：返回一行数据的子查询语句。显示与SMITH同一部门的所有员工。

Select * form emp where deptno=(select deptno from emp where ename=’SMITH’);从右往左执行。

多行子查询：返回多行数据的子查询。查询和部门10的工作相同的雇员的名字、岗位、工资和部门号。

Select * from emp where job in (select distinct job from emp where deptno=10);

多行查询中使用all操作符。显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

Select ename,sal,dept from emp where sal>all(select sal from emp where deptno=30);

在多行查询中使用any操作符。显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号。
select ename,sal,dept from emp where sal>any(select sal from emp where deptno=30);

多列子查询。查询与smith的部门和岗位完全相同的所有雇员。

Select deptno,job from emp where ename=’smith’;

Select * from emp where (deptno,job)=(select deptno,job from emp where ename =’smith’);

显示高于自己部门平均工资的员工的信息：

(1.查询各部门的平均工资)Select deptno,avg(sal) mysal from emp group by deptno;

(2.将上面的查询看做是一张子表)select a2.ename,a2.sal,a2.deptno from emp a2, (Select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;

在from中使用子查询的时候，该子查询会被当做成一个视图对待，成为内前视图。在from中使用子查询时必须制定别名。

4. 分页查询。例如，按照雇员的id号升序取出。

Oracle分页查询一共有三种：

A.rownum分页：select a1.*, rownum from (select * from emp) a1;

select a1.*, rownum from (select * from emp) a1 where rownum <=10;不能再rownum>=6，oracle规定rn只能用一次。

Select * from (select a1.*, rownum from (select * from emp) a1 where rownum <=10) where rownum>=6;修改查询只需要在里层改动。

B.ROWID:效率很高

C.按照分析函数来分。效率太差。

5.用查询结果创建新表：

Create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

6.合并查询。使用集合操作符union，union，all，intersect，minus。

A.union:取集合的并集

Select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job=’manager’;

B.union all:不取消重复行，不排序

C.intersect:取两个集合的交集。

D.minus:取两个集合的差集。显示前面的不现实后面的。

1.使用to_date函数：to_date(‘1988-04-16’,’yyyy-mm-dd’);

2.使用子查询插入数据：导入emp表中10号部门到新表kkk中。

Insert into kkk (MyID,MyName) select empo,ename,detpno from emp where deptno=10;

3.使用子查询更新数据：更新员工scott的岗位、工资和smith员工一样

Update emp set (job,sal)=(select job,sal from emp where ename=’SMITH’) where ename=’SCOTT’;

23. SQL函数的使用

1.字符函数：

lower(char):将字符串转化为小写

upper(char):将字符串转换为大写

length(char):返回字符串长度

substr(char,m,n):取字符串的字串

replace(char1,serarch_string,replace_string):替换函数

instr(char1,char2,[,n[,m[]):取字串位置函数

eg：将所有员工的名字按照小写显示：select lower(name) from emp;

2.数学函数：

cos,cosh,exp,ln,log,sin,sinh,sqrt,tan…

round(n,[m]):执行四舍五入，保留m位小数

trunk(n,[m]);截取数字，截取n小数点后的m位

mod(m,n):去模

floor(n):返回小于或者等于n的最大整数

ceil(n):返回大于或是等于n的最小整数

abs(n):绝对值

3.日期函数：默认格式为dd-mon-yy，例如12-7月-15,显示为2015-7-12。

Sysdate：当前系统时间。Select sysdate from emp;

Add_months(d,n):加一个月份。Eg：查找入职8个月入职的员工。

Select from emp where sysdate>add_months(hiredate,8);

Last_day(d):返回指定日期的最后一天

4.转换函数：用于将数据类型的转换，oracle提供自动转换。

to_char():转换字符函数

eg1：日期显示时分秒：select ename ,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’);

eg:显示日民币：select ename ,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) to_char(sal,’L9999.99’);L表示当地语言，9999.99代表显示数字格式。

5.系统函数

Sys_context：查询系统参数

24. 数据库管理：

Sys：董事长

System：总经理

主要区别：

1. 存储的数据的重要性不同

Sys：所有oracle的数据字典的基表和视图都是放在sys用户中的，sys拥有dba，sysdba，sysoperar的角色，是用户权限最高的用户。

System：存放系统次一级的内部数据，拥有dba和sysdbajuese或者系统权限。

2. 权限的不同

Sys必须以as sysdba或者as stsoper形式登录，不能以normal形式登录

Systm如果正常登录，其实就是一个普通的dba用户，如果以as sysdba登录，结果实际上就是作为sys用户登录。

Sysoper较之sysdba不不能改变字符集、创建、删除数据库，只能万群恢复数据库等。

sysdba>sysoper>dba（大致如此）。其中dba不能启动关闭数据库。

显示初始化参数：show parameter

备份和恢复：逻辑备份使用export将数据对象的结构和数据备份到文件的过程。使用import进行导入。物理备份为指的是可以在数据库open或者close的状态下进行数据备份，而逻辑备份只能在数据库open的时候进行。

导出：导出表，导出方案，导出数据库，使用exp命令完成，常用的选项有：

Userid：用于指定齿形导出操作的命令名，口令

Tables：用于指定导出操作的表

Owner：用于指定执行导出操作的方案

Full=y：用于指定执行导出操作的数据库

Rows：用于指定导出操作的增量类型

File：用于指定导出文件名

Inctype：用于指定导出操作的增量类型

A导出表：

Exp userid=scott/tiger@MyDB tables=(emp) file=d:\e1.dmp;导出自己的表。

Exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.emp;导出其他方法的表，需要dba权限

B.导出表结构：

Exp userid=scott/tiger@MyDB tables=(emp) file=d:\e3.dmp rows=n

C.直接导出方式:速度快

Exp userid=scott/tiger@MyDB tables=(emp) file=d:\e3.dmp direct=y;需要数据库的字符集合客户端的字符集完全一致。

D.导出方案：

Exp scott/tiger@myDB owner=scott file=d:\1.dmp导出自己方案

Exp system/manager@mydb owner=(system,scott) file=d:\2.dmp;导出其他方案（使用system导出scott的方案）

E.导出数据库

Exp userid=system/manager@mydb full=y inctype=complete file=x.dmp;inctype表示增量备份。

F.导入表：

Imp userid=scott/tiger@mydb tables=(emp) file=d:\xx.dmp;导入自己的表

Imp userid=system/manager@mydb tables=(emp) file=d:\d.dmp;导入其他表

Imp userid=scott/tiger@mydb tables=(emp) file=d:\1.dmp row=n;导入数据表结构

Imp userid=scott/tiger@mydb tables=(emp) file=d:\1.dmp ignore=y;如果数据表已经存在，只导入数据。

G.导入方：

Imp userid=scott/tiger file=1.dmp;导入自己方案

Imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott;

H.导入数据库：

Imp userid=system/manager full=y file=1.dmp;

25. 数据字典和动态性能视图

数据字典提供数据库的系统信息；数据字典属于sys用户。

动态性能视图记载例程启动后的相关信息。

数据字典分为：字典基表和动态视图构成。基表存储数据库基本信息，普通用户不能直接访问数据字典的基表。数据字典视图是基于数据字典基表简历的视图，普通用户可以同过查询字典视图获取系统信息。数据字典视图主要包括：user_xxx，all_xxx，dba_xxx三种类型。

User_tables:显示当前用户所拥有的表。Select table_name from user_tables;

All_tables:显示当前用户可访问的所有表。

Dba_tables：显示所有方案拥有的数据表。

用户名、权限或者角色等信息都存储在数据字典当中。

26. 管理表空间和数据文件

Orracle的逻辑结构包括：表空间、段、区和块。数据库由表空间构成，表空间由段构成，段由区构成，区由块构成。

表空间：表空间是数据库的逻辑组成部分。表空间由一个或者多个文件组成。表空间控制数据库占用的磁盘空间，dba可以将不同数据类型不熟到不同的位置，这样有利于提高i/o性能。

1. 建立表空间：create tablespace

2. 建立数据表空间：create tablespace data01 datafile ‘c:\data01.dbf’ size 20m uniform size 128k;区大小128K

3. 使用表空间：create table mypart(deptno number(4),dname vchar2(14)) tablespace sp001;

27. 约束

数据的完整性用于确保数据库的数据遵从一定的商业和逻辑规则。在

中，数据的完整性可以使用约束、触发器，应用程序（过程、函数）三种方式完成。

约束用于确保数据库数据满足特定的商业规则。Oracle中约束包括：not null、unique，primary key，foreign key和check五中。

Not null：非空，插入数据时必须提供数据

Unique：该值不能重复，但是可以为null

Primary key：唯一的标示表行的数据，该列不能重复且不能为null，一张表只能有一个主键，但可以有多个unique。

Foreign key：用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或者unique约束当定义外键约束后，要求外键列数据必须在主表的主键列或者是null。

Check：强制行数据满足条件。

Create table goods(goodsID char(8) primary key,--主键 goodsName varchar2(30), nitprice number(10,2) check (unitprice>0), category varchar2(8), provider varchar2(30));

Create table customer(customerID char(8) privary key, name vchar2(30) not null, address varchar2(50), email varchar2(50) unique, sex char(2) default ‘男’ check(sex in ‘男’,’女’) cardId char(18));

Create table purchase (customerId char(8) references customer(customerId), goodsId char(8) references goods(goodId), nums number(5) check (nums between 1 and 30));

删除约束：alter table 表名 drop constraint 约束名称。在删除主键时，有必要加入cascade关键字，以防主键有关联关系。

28. 索引：用于加速数据存取的数据对象。

单列索引：基于单个列锁创建的索引：create index 索引名 on 表名（列名）

复合索引：基于两列或者是多列的索引。

29. 权限：执行特定类型sql命令或是访问其他方案对象的权利，包括系统权限和对象权限两种。

30. 角色：相关权限的集合，用于简化权限的管理。

31. PL/SQL

PL/SQL（procedural language/sql）是oracle标准的sql语言的扩展。它不仅允许嵌入sql语言，还可以定义变量和常量，并允许使用条件和循环语言，以及使用例外处理各种错误等。