Oracle 中有默认的2个用户一个是Sys另外一个是System
Sys:是超级用户,具有最高权限,具有SYSDBA角色,有创建数据库的权限
System:是操作管理员,具有SYSOPER角色,没有创建数据库的权限
Oracle 的卸载
软件环境:
1、Windows XP + Oracle 10g
2、Oracle安装路径为:d:\Oracle
实现方法:
1、开始->设置->控制面板->管理工具->服务停止所有Oracle服务.
2、开始->程序->Oracle – OraDb10g_home1>Oracle Installation Products-> Universal Installer 卸装所有Oracle产品,但Universal Installer本身不能被删除;
3、运行regedit,选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,按del键删除这个入口.
4、运行regedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滚动这个列表,删除所有Oracle入口.
5、运行refedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \Eventlog\Application,删除所有Oracle入口.
6、开始->设置->控制面板->系统->高级->环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定.
7、从桌面上、STARTUP(启动)组、程序菜单中,删除所有有关Oracle的组和图标.
8、删除c:\Program Files\Oracle目录.
9、重新启动计算机,重起后才能完全删除Oracle所在目录.
10、删除与Oracle有关的文件,选择Oracle所在的缺省目录C:\Oracle,删除这个入口目录及所有子目录,并从Windows XP目录(一般为d:\WINDOWS)下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、 oraodbc.ini等等.
11、WIN.INI文件中若有[ORACLE]的标记段,删除该段.
12、如有必要,删除所有Oracle相关的ODBC的DSN.
13、到事件查看器中,删除Oracle相关的日志 说明:如果有个别DLL文件无法删除的情况,则不用理会,重新启动,开始新的安装,安装时,选择一个新的目录,则安装完毕并重新启动后,原来的目录及文件就可以删除掉了
Oracle 系统命令
1、连接数据库
2、显示当前用户
3、修改密码
4、退出
5、运行sql脚本
6、编辑sql脚本
7、将指定查询内容输出到指定文件中去
把emp表中的数据放在d:\test.sql这个文件中
Oracle 用户管理
切换用户
创建用户
修改别人密码
删除用户(自己删除自己不可以)
如果要删除用户,并且同时要删除用户所创建在表,那么就需要在删除时带一个参数cascade;
角色管理权限
受理连接数据库权限(connect)给一个用户
授权username查询emp权限
授权username对emp所有权限
权限传递
如果是对象权限,权限在传递就在后面加with grant option
如果是系统权限,权限在传递就在后面加with admin option 这使username可以把select权限传递给其他人
收回username在我的emp表上在select权限
profile 规则
账户锁定
指定账户登陆最多可以输入密码在次数,也可以指定用户锁定在时间(天)一般用dba在身份去执行该命令
把锁lock_account给scott.如果用户连续输入3次错误密码,那么你就被锁定2天,2天后才能登陆
把 profile文件删除
表管理
char 与varchar2的区别
char定长 varchar2不定长.char查询效率快浪费硬盘空间,varchar2查询慢节省空间
存图片使用blob数据类型
向表里面添加一个字段
修改字段长度
修改字段的类型或名字(在这列没有数据的情况下才能修改)
删除一个字段
修改表名
Oracle 中默认的时间格式是dd-mm-yyyy,而且插入时间mm必须带一个"月",也就是说insert into test(testTime)
values('01-5月-09');
可以使用nls_date_format来改变Oracle默认的日期格式
设置查询时间点
nvl()函数
nvl(num,0)
%表示任意字符
_表示一个字符
查询比deptno=30所有人工资都高的人
查询比deptno=30任何人工资都高的人
给表加别名的时候不能加as,给列加别名的时候可以加as
oracle 分页有3种方式
1、rownum方式
2、rowid方式
3、分析函数来分
rownum 方式
rownum 是oracle分配的行号
查询前10条数据
注意:rownum只能用一次看下面:查询emp表 第6条-第10条数据
如果要指定查询列那么只需该修改最里面的select查询(select * from emp)把这个改了就行了
合并查询用到的几个关键字
删除表中所有记录不写日志,无法回滚
设置事务保存点
回滚事务
常见的sql函数,字符函数
数学函数
日期函数
转换函数
系统函数sys_context()
数据库管理
数据库(表)的逻辑备份与恢复
数据字典和动态性能试图
管理表空间和数据文件
SYS: 所有Oracle的数据字典的基表和试图都存放在SYS用户中,这些基表和试图对于Oracle的运行至关重要的,
由数据库自己维护,任何用户都不能手动更改.SYS用户拥有DBA,SYSDBA,SYSOPER角色或权限,是Oracle权限最高的
用户
SYSTEM: 用于存放次一级的内部数据,如Oracle的一些特性或工具的管理信息.SYSTEM用户拥有DBA,SYSDBA角色或
系统权限
SYS 用户必须以as sysdba或as sysoper形式登录.不能以normal方式登录数据库
SYSTEM如果正常登录,它其实就是个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的
数据库的导入导出
导出:
导出具体分为:导出表,导出方案,导出数据库三种方式..
导出用exp命令,该命令常用的有:
userid: 用于指定执行导出操作的用户名、口令,连接字符串
tables: 用于指定执行导出操作的表
owner: 用于指定执行导出操作的方案
full=y: 用于指定执行导出操作的数据库
inctype: 用于指定执行导出操作的增量类型
rows: 用于指定执行导出操作是否导出表中的数据
file: 用于指定导出文件名
导出表结构和数据
导出指定的表结构和数据emp表和dept表
导出表结构
直接导出表和数据,速度非常快
导出scott方案
导出数据库(增量备份)
导入:
导入具体分为:导入表,导入方案,导入数据库三种方式..
导出用exp命令,该命令常用的有:
userid: 用于指定执行导入操作的用户名、口令,连接字符串
tables: 用于指定执行导入操作的表
formuser: 用于指定源用户
touser: 用于指定目标用户
file: 用于指定导入文件名
full=y: 用于指定执行导入整个文件
inctype: 用于指定执行导入操作的增量类型
rows: 指定是否要导入表行
ignore: 如果表存在,则只导入数据
导入自己表
导入其他用户
导入表结构
导入数据
导入方案
导入其他方案
导入数据库
数据字典:
数据字典是Oracle数据库中最重要的组成部分,它提供了数据库的一些系统信息.静态信息和动态信息.
数据字典记录了数据库系统的信息,它是只读表和动态性能试图的集合,数据字典的所有者为SYS用户.
用户只能在数据字典上执行查询操作,而其维护是由系统自动完成的
数据字典包括(基表+动态性能试图)
基表:
存储着数据库的基本信息.静态信息
动态性能试图:
动态性能试图是记载了例程启动后相关信息.动态信息
user_XXX,all_XXX,dba_XXX
用户、权限、角色
数据库用户详细信息
dba_users;
可以显示所有用户具有的系统权限
dba_sys_privs
可以显示所有用户具有的对象权限
dba_tab_privs
可以显示所有用户具有的例权限
dba_col_privs
可以显示所有用户具有的角色
dba_role_privs
表空间
表空间是数据库的逻辑组成部分.从物理上讲,数据库数据存放在数据文件中;从逻辑上讲,数据库则是存放在表空
间中,表空间是由一个或多个数据文件组成
Oracle 逻辑结构包括表空间、段、区和块.
数据库有是由表空间构成,而表空间是由段构成,而段又是由区构成,
而区又是由Oracle块构成的这样的一种结构,可以提高数据库的效率
表空间用于逻辑上组织数据库的数据.数据库逻辑上是由一个或多个表空间组成的.通过表空间可以达到以下作用:
(1)控制数据库占用的磁盘空间
(2)dba可以将不同数据类型部署到不同位置,这样有利于提高I/O的性能,同时有利于备份和恢复等管理操作
创建表空间:sp001是表空间名
表空间的几种状态
让表空间脱机
让表空间联机
让表空间只读
让表空间可读可写
查看表空间里面的表
查看表所属哪个表空间
删除表空间
彻底删除表空间里面所有内容
扩展表空间,有3种方式
1、增加数据文件
2、增加数据文件大小,这里的resize不能大于500M
3、设置文件自动增长
移动表空间、数据迁移
1、确定数据文件所在的表空间
2、使表空间在脱机状态
3、使用命令移动数据文件到指定目标位置
4、移动数据文件,在物理上移动了数据后,还必须对数据库文件进行逻辑修改
5、使表空间在连机状态
还有几种重要的表空间:
1、索引表空间
2、 undo表空间
3、临时表空间
4、非标准块表空间
Oracle 数据完整性
在 Oracle中数据完成性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现
在Oracle中包括5种约束:not null(非空),unique(唯一),primary key(主键),foreign key(外键),check(检查)
添加表主键
添加表外键[b][/b]
添加表约束,10-20之间
删除约束
删除主键的时候会报错,所有表引用那么就可以加cascade关键字来删除主从表的约束
显示约束信息
显示约束列
列级定义
列级定义是在定义列的时同时定义约束
表级定义
表级定义在把表创建好之后,再定义约束,需要注意到是 not null 约束只能在列级定义上
索引
索引是加速数据存取的数据对象.合理的使用索引可以大大的降低I/O次数,从而提高数据访问性能.
创建索引
单列索引
要经常查询ename则就在ename上建立一个索引
复合索引
要经常查询job,deptno则就在job,deptno上建立一个索引
建立索引的原则
1、在大表上建立索引才有意义
2、在where语句或者连接条件上经常引用的列上建立索引
索引的缺点:
1、建立索引,系统要占用大约为表1.2倍的硬盘和内存空间来保存条件
2、更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性.
[/b]
索引的分类
1: 按照数据存储方式:可以分为B*树、反向索引、位图索引
2: 按照索引列的个数分类:可分为单列索引和复合索引
3: 按照索引列值得唯一性:可分为唯一索引和非唯一索引
4: 此外还有函数索引,全局索引,分区索引
c
[b]查询索引
存储过程
创建一个存储过程
创建或者替换一个存储过程create or replace
查看错误信息用show error;
执行过程
1、exec 过程名(参数1,参数2,参数3...)
2、call 过程名(参数1,参数2,参数3...)
exec 与call有什么区别
处理异常
记录类型
表
Oracle就相当于数组
Oracle 循环
函数
创建函数
执行函数
包
创建包
创建包体
触发器
触发器实质上是隐含的存储过程.当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括
insert,update,delete
视图
在pl/sql程序中包括有:
标量类型
复合类型
参展类型
一个有用的分页存储过程
Sys:是超级用户,具有最高权限,具有SYSDBA角色,有创建数据库的权限
System:是操作管理员,具有SYSOPER角色,没有创建数据库的权限
Oracle 的卸载
软件环境:
1、Windows XP + Oracle 10g
2、Oracle安装路径为:d:\Oracle
实现方法:
1、开始->设置->控制面板->管理工具->服务停止所有Oracle服务.
2、开始->程序->Oracle – OraDb10g_home1>Oracle Installation Products-> Universal Installer 卸装所有Oracle产品,但Universal Installer本身不能被删除;
3、运行regedit,选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,按del键删除这个入口.
4、运行regedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滚动这个列表,删除所有Oracle入口.
5、运行refedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \Eventlog\Application,删除所有Oracle入口.
6、开始->设置->控制面板->系统->高级->环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定.
7、从桌面上、STARTUP(启动)组、程序菜单中,删除所有有关Oracle的组和图标.
8、删除c:\Program Files\Oracle目录.
9、重新启动计算机,重起后才能完全删除Oracle所在目录.
10、删除与Oracle有关的文件,选择Oracle所在的缺省目录C:\Oracle,删除这个入口目录及所有子目录,并从Windows XP目录(一般为d:\WINDOWS)下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、 oraodbc.ini等等.
11、WIN.INI文件中若有[ORACLE]的标记段,删除该段.
12、如有必要,删除所有Oracle相关的ODBC的DSN.
13、到事件查看器中,删除Oracle相关的日志 说明:如果有个别DLL文件无法删除的情况,则不用理会,重新启动,开始新的安装,安装时,选择一个新的目录,则安装完毕并重新启动后,原来的目录及文件就可以删除掉了
Oracle 系统命令
1、连接数据库
Sql代码
- SQL> conn 用户名/密码@网络服务名SID [as sysdba/sysoper] 当用特权用户身份连接的时,必须带上as sysdba或者as sysoper
SQL> conn 用户名/密码@网络服务名SID [as sysdba/sysoper] 当用特权用户身份连接的时,必须带上as sysdba或者as sysoper
2、显示当前用户
Sql代码
- SQL> show user;
SQL> show user;
3、修改密码
Sql代码
- 管理员自己修改密码: passw 管理员修改其他用户密码: passw 用户名
管理员自己修改密码: passw 管理员修改其他用户密码: passw 用户名
4、退出
Sql代码
- SQL> exit;
SQL> exit;
5、运行sql脚本
Sql代码
- SQL> start c:\test.sql; 或者SQL> @ c:\test.sql;
SQL> start c:\test.sql;或者SQL> @ c:\test.sql;
6、编辑sql脚本
Sql代码
- SQL> edit c:\test.sql;
SQL> edit c:\test.sql;
7、将指定查询内容输出到指定文件中去
把emp表中的数据放在d:\test.sql这个文件中
Sql代码
- SQL> spool d:\test.sql;
- SQL> select * from emp;
- SQL> sppool off;
SQL> spool d:\test.sql; SQL> select * from emp; SQL> sppool off;
Oracle 用户管理
切换用户
Sql代码
- SQL> conn system/manager
SQL> conn system/manager
创建用户
Sql代码
- SQL> create user 用户名 identified by 密码
SQL> create user 用户名 identified by 密码
修改别人密码
Sql代码
- SQL> password 用户名
- SQL> alter user 用户名 identified by 新密码
SQL> password 用户名 SQL> alter user 用户名 identified by 新密码
删除用户(自己删除自己不可以)
如果要删除用户,并且同时要删除用户所创建在表,那么就需要在删除时带一个参数cascade;
Sql代码
- SQL> drop user 用户名 [cascade]
SQL> drop user 用户名 [cascade]
角色管理权限
受理连接数据库权限(connect)给一个用户
Sql代码
- SQL> grant connect to 用户名
SQL> grant connect to 用户名
授权username查询emp权限
Sql代码
- SQL> grant select on emp to username
- SQL> grant insert on emp to username
- SQL> grant update on emp to username
- SQL> grant delete on emp to username
SQL> grant select on emp to username SQL> grant insert on emp to username SQL> grant update on emp to username SQL> grant delete on emp to username
授权username对emp所有权限
Sql代码
- SQL> grant all on emp to username
SQL> grant all on emp to username
权限传递
如果是对象权限,权限在传递就在后面加with grant option
Sql代码
- SQL> grant select on emp to username with grant option
SQL> grant select on emp to username with grant option
如果是系统权限,权限在传递就在后面加with admin option 这使username可以把select权限传递给其他人
Sql代码
- SQL> grant connect on emp to username with admin option
SQL> grant connect on emp to username with admin option
收回username在我的emp表上在select权限
Sql代码
- SQL> revoke select on emp from username
SQL> revoke select on emp from username
profile 规则
账户锁定
指定账户登陆最多可以输入密码在次数,也可以指定用户锁定在时间(天)一般用dba在身份去执行该命令
Sql代码
- SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
- lock_account: 名称随便取名
- 3:最多输入3次密码
- 2:锁定2天
- 其他的都是关键字
SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; lock_account:名称随便取名 3:最多输入3次密码 2:锁定2天 其他的都是关键字
把锁lock_account给scott.如果用户连续输入3次错误密码,那么你就被锁定2天,2天后才能登陆
Sql代码
- SQL> alter user scott profile lock_account;
SQL> alter user scott profile lock_account;
把 profile文件删除
Sql代码
- SQL> drop profile profilename cascade
SQL> drop profile profilename cascade
表管理
char 与varchar2的区别
char定长 varchar2不定长.char查询效率快浪费硬盘空间,varchar2查询慢节省空间
存图片使用blob数据类型
向表里面添加一个字段
Sql代码
- SQL> alter table tablename add(columnName varchar2(10));
SQL> alter table tablename add(columnName varchar2(10));
修改字段长度
Sql代码
- SQL> alter table tablename modify(columnName varchar2(30));
SQL> alter table tablename modify(columnName varchar2(30));
修改字段的类型或名字(在这列没有数据的情况下才能修改)
Sql代码
- SQL> alter table tablename modify(columnName varchar2(20));
SQL> alter table tablename modify(columnName varchar2(20));
删除一个字段
Sql代码
- SQL> alter table tablename drop column columnName;
SQL> alter table tablename drop column columnName;
修改表名
Sql代码
- SQL> rename tablename to newtablename;
SQL> rename tablename to newtablename;
Oracle 中默认的时间格式是dd-mm-yyyy,而且插入时间mm必须带一个"月",也就是说insert into test(testTime)
values('01-5月-09');
可以使用nls_date_format来改变Oracle默认的日期格式
Sql代码
- SQL> alter session set nls_date_format='yyyy-mm-dd' set timing on;
SQL> alter session set nls_date_format='yyyy-mm-dd' set timing on;
设置查询时间点
Sql代码
- set timing on;
set timing on;
nvl()函数
nvl(num,0)
Sql代码
- 如果 num为空,那么把0赋值给num
如果num为空,那么把0赋值给num
%表示任意字符
_表示一个字符
查询比deptno=30所有人工资都高的人
Sql代码
- SQL> select * from emp where sal> all(select sal from emp where deptno=30)
SQL> select * from emp where sal> all(select sal from emp where deptno=30)
查询比deptno=30任何人工资都高的人
Sql代码
- SQL> select * from emp where sal> any(select sal from emp where deptno=30)
SQL> select * from emp where sal> any(select sal from emp where deptno=30)
给表加别名的时候不能加as,给列加别名的时候可以加as
oracle 分页有3种方式
1、rownum方式
2、rowid方式
3、分析函数来分
rownum 方式
Sql代码
- SQL> select t.*,rownum rn from (select * from emp) t;
SQL> select t.*,rownum rn from (select * from emp) t;
rownum 是oracle分配的行号
查询前10条数据
Sql代码
- SQL> select t.*,rownum rn from (select * from emp) t where rownum<=10;
SQL> select t.*,rownum rn from (select * from emp) t where rownum<=10;
注意:rownum只能用一次看下面:查询emp表 第6条-第10条数据
Sql代码
- SQL> select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;
SQL> select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;
如果要指定查询列那么只需该修改最里面的select查询(select * from emp)把这个改了就行了
合并查询用到的几个关键字
Sql代码
- union,union all,intersect,minus
union,union all,intersect,minus
删除表中所有记录不写日志,无法回滚
Sql代码
- SQL> truncate table student;
SQL> truncate table student;
设置事务保存点
Sql代码
- SQL> savepoint a
SQL> savepoint a
回滚事务
Sql代码
- SQL> rollback to a
SQL> rollback to a
常见的sql函数,字符函数
Sql代码
- 将字符串转换为小写
- lower(char)
- 将字符串转换为大写
- upper(char)
- 返回字符串的长度
- length(char)
- 取字符串字串
- substr(char,start,end)
- 替换字符串
- replace(char,search_string,replace_string);
- 字符串的位置
- instr(char1,char2)
将字符串转换为小写 lower(char) 将字符串转换为大写 upper(char) 返回字符串的长度 length(char) 取字符串字串 substr(char,start,end) 替换字符串 replace(char,search_string,replace_string); 字符串的位置 instr(char1,char2)
数学函数
Sql代码
- 执行四舍五入
- round(n,[m])
- 截取数字
- trunc(n,[m])
- 返回小于或等于n的最大整数
- floor(n)
- 返回大于或等于n的最大整数
- ceil(n)
执行四舍五入 round(n,[m]) 截取数字 trunc(n,[m]) 返回小于或等于n的最大整数 floor(n) 返回大于或等于n的最大整数 ceil(n)
日期函数
Sql代码
- oracle 系统时间
- sysdate
- 加月数
- add_months(d,n)
- 返回指定日期在月份的最后一天
- last_day(d)
oracle系统时间 sysdate 加月数 add_months(d,n) 返回指定日期在月份的最后一天 last_day(d)
转换函数
Sql代码
- to_char(d,'yyyy-mm-dd hh24:mi:ss');
- 数字转换成金额格式
- to_char()
to_char(d,'yyyy-mm-dd hh24:mi:ss'); 数字转换成金额格式 to_char()
系统函数sys_context()
Sql代码
- 数据库名称
- SQL> select sys_context('userenv','db_name') from dual;
- 数据库语言
- SQL> select sys_context('userenv','language') from dual;
- 当前用户
- SQL> select sys_context('userenv','session_user') from dual;
- 方案名
- SQL> select sys_context('userenv','current_schema') from dual;
数据库名称 SQL> select sys_context('userenv','db_name') from dual; 数据库语言 SQL> select sys_context('userenv','language') from dual; 当前用户 SQL> select sys_context('userenv','session_user') from dual; 方案名 SQL> select sys_context('userenv','current_schema') from dual;
数据库管理
数据库(表)的逻辑备份与恢复
数据字典和动态性能试图
管理表空间和数据文件
SYS: 所有Oracle的数据字典的基表和试图都存放在SYS用户中,这些基表和试图对于Oracle的运行至关重要的,
由数据库自己维护,任何用户都不能手动更改.SYS用户拥有DBA,SYSDBA,SYSOPER角色或权限,是Oracle权限最高的
用户
SYSTEM: 用于存放次一级的内部数据,如Oracle的一些特性或工具的管理信息.SYSTEM用户拥有DBA,SYSDBA角色或
系统权限
SYS 用户必须以as sysdba或as sysoper形式登录.不能以normal方式登录数据库
SYSTEM如果正常登录,它其实就是个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的
数据库的导入导出
导出:
导出具体分为:导出表,导出方案,导出数据库三种方式..
导出用exp命令,该命令常用的有:
userid: 用于指定执行导出操作的用户名、口令,连接字符串
tables: 用于指定执行导出操作的表
owner: 用于指定执行导出操作的方案
full=y: 用于指定执行导出操作的数据库
inctype: 用于指定执行导出操作的增量类型
rows: 用于指定执行导出操作是否导出表中的数据
file: 用于指定导出文件名
导出表结构和数据
Sql代码
- SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp
SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp
导出指定的表结构和数据emp表和dept表
Sql代码
- SQL> exp userid=soctt/tiger@orcl tables=(emp,dept) file=c:\workplan.dmp
SQL> exp userid=soctt/tiger@orcl tables=(emp,dept) file=c:\workplan.dmp
导出表结构
Sql代码
- SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp rows=n
SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp rows=n
直接导出表和数据,速度非常快
Sql代码
- SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp direct=y
SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp direct=y
导出scott方案
Sql代码
- SQL> exp userid=soctt/tiger@orcl owner=scott file=c:\workplan.dmp
SQL> exp userid=soctt/tiger@orcl owner=scott file=c:\workplan.dmp
导出数据库(增量备份)
Sql代码
- SQL> exp userid=system/manager@orcl full=y inctype=complete file=c:\workplan.dmp
SQL> exp userid=system/manager@orcl full=y inctype=complete file=c:\workplan.dmp
导入:
导入具体分为:导入表,导入方案,导入数据库三种方式..
导出用exp命令,该命令常用的有:
userid: 用于指定执行导入操作的用户名、口令,连接字符串
tables: 用于指定执行导入操作的表
formuser: 用于指定源用户
touser: 用于指定目标用户
file: 用于指定导入文件名
full=y: 用于指定执行导入整个文件
inctype: 用于指定执行导入操作的增量类型
rows: 指定是否要导入表行
ignore: 如果表存在,则只导入数据
导入自己表
Sql代码
- SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp
SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp
导入其他用户
Sql代码
- SQL> imp userid=system/manager@orcl tables=(emp) file=d:\gbzh2.dmp touser=scott
SQL> imp userid=system/manager@orcl tables=(emp) file=d:\gbzh2.dmp touser=scott
导入表结构
Sql代码
- SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp rows=n
SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp rows=n
导入数据
Sql代码
- SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp ignore=y
SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp ignore=y
导入方案
Sql代码
- SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp
SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp
导入其他方案
Sql代码
- SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp formuser=system touser=scott
SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp formuser=system touser=scott
导入数据库
Sql代码
- SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp full=y
SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp full=y
数据字典:
数据字典是Oracle数据库中最重要的组成部分,它提供了数据库的一些系统信息.静态信息和动态信息.
数据字典记录了数据库系统的信息,它是只读表和动态性能试图的集合,数据字典的所有者为SYS用户.
用户只能在数据字典上执行查询操作,而其维护是由系统自动完成的
数据字典包括(基表+动态性能试图)
基表:
存储着数据库的基本信息.静态信息
动态性能试图:
动态性能试图是记载了例程启动后相关信息.动态信息
user_XXX,all_XXX,dba_XXX
用户、权限、角色
数据库用户详细信息
dba_users;
可以显示所有用户具有的系统权限
dba_sys_privs
可以显示所有用户具有的对象权限
dba_tab_privs
可以显示所有用户具有的例权限
dba_col_privs
可以显示所有用户具有的角色
dba_role_privs
表空间
表空间是数据库的逻辑组成部分.从物理上讲,数据库数据存放在数据文件中;从逻辑上讲,数据库则是存放在表空
间中,表空间是由一个或多个数据文件组成
Oracle 逻辑结构包括表空间、段、区和块.
数据库有是由表空间构成,而表空间是由段构成,而段又是由区构成,
而区又是由Oracle块构成的这样的一种结构,可以提高数据库的效率
表空间用于逻辑上组织数据库的数据.数据库逻辑上是由一个或多个表空间组成的.通过表空间可以达到以下作用:
(1)控制数据库占用的磁盘空间
(2)dba可以将不同数据类型部署到不同位置,这样有利于提高I/O的性能,同时有利于备份和恢复等管理操作
创建表空间:sp001是表空间名
Sql代码
- SQL> create tablespace sp001 datafile 'd:\sp001' size=5M uniform size 128K;
SQL> create tablespace sp001 datafile 'd:\sp001' size=5M uniform size 128K;
表空间的几种状态
让表空间脱机
Sql代码
- SQL> alter tablespace sp001 offline;
SQL> alter tablespace sp001 offline;
让表空间联机
Sql代码
- SQL> alter tablespace sp001 online;
SQL> alter tablespace sp001 online;
让表空间只读
Sql代码
- SQL> alter tablespace sp001 read only;
SQL> alter tablespace sp001 read only;
让表空间可读可写
Sql代码
- SQL> alter tablespace sp001 read write;
SQL> alter tablespace sp001 read write;
查看表空间里面的表
Sql代码
- SQL> select * from all_tables where tablespace_name='TABLESPACE_NAME';
SQL> select * from all_tables where tablespace_name='TABLESPACE_NAME';
查看表所属哪个表空间
Sql代码
- SQL> select tablespace_name,table_name from user_tables where table_name='TABLE_NAME'
SQL> select tablespace_name,table_name from user_tables where table_name='TABLE_NAME'
删除表空间
Sql代码
- SQL> drop tablespace sp001
SQL> drop tablespace sp001
彻底删除表空间里面所有内容
Sql代码
- SQL> drop tablespace sp001 including contents and datafiles;
SQL> drop tablespace sp001 including contents and datafiles;
扩展表空间,有3种方式
1、增加数据文件
Sql代码
- SQL> alter tablespace sp001 add datafile 'd:\TABLESPACETEST1.dbf' size 20M;
SQL> alter tablespace sp001 add datafile 'd:\TABLESPACETEST1.dbf' size 20M;
2、增加数据文件大小,这里的resize不能大于500M
Sql代码
- SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' resize 50M;
SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' resize 50M;
3、设置文件自动增长
Sql代码
- SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' autoextend on next 10M maxsize 500M;
SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' autoextend on next 10M maxsize 500M;
移动表空间、数据迁移
1、确定数据文件所在的表空间
Sql代码
- SQL> select sp001 from dba_data_files where file_name='d:\TABLESPACETEST1.dbf';
SQL> select sp001 from dba_data_files where file_name='d:\TABLESPACETEST1.dbf';
2、使表空间在脱机状态
Sql代码
- SQL> alter tablespace sp001 offline;
SQL> alter tablespace sp001 offline;
3、使用命令移动数据文件到指定目标位置
Sql代码
- SQL> host move 'd:\TABLESPACETEST1.dbf' 'c:\TABLESPACETEST1.dbf'
SQL> host move 'd:\TABLESPACETEST1.dbf' 'c:\TABLESPACETEST1.dbf'
4、移动数据文件,在物理上移动了数据后,还必须对数据库文件进行逻辑修改
Sql代码
- SQL> alter tablespace sp001 rename datafile 'd:\TABLESPACETEST1.dbf' to 'c:\TABLESPACETEST1.dbf'
SQL> alter tablespace sp001 rename datafile 'd:\TABLESPACETEST1.dbf' to 'c:\TABLESPACETEST1.dbf'
5、使表空间在连机状态
Sql代码
- SQL> alter tablespace sp001 online;
SQL> alter tablespace sp001 online;
还有几种重要的表空间:
1、索引表空间
2、 undo表空间
3、临时表空间
4、非标准块表空间
Oracle 数据完整性
在 Oracle中数据完成性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现
在Oracle中包括5种约束:not null(非空),unique(唯一),primary key(主键),foreign key(外键),check(检查)
添加表主键
Sql代码
- SQL>
SQL>
添加表外键[b][/b]
Sql代码
- SQL>
SQL>
添加表约束,10-20之间
Sql代码
- SQL>
SQL>
删除约束
Sql代码
- SQL> alter table tableName drop constraint 约束名称;
SQL> alter table tableName drop constraint 约束名称;
删除主键的时候会报错,所有表引用那么就可以加cascade关键字来删除主从表的约束
Sql代码
- SQL> alter table tableName drop primary key cascade;
SQL> alter table tableName drop primary key cascade;
显示约束信息
Sql代码
- SQL> select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名'
SQL> select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名'
显示约束列
Sql代码
- SQL> select column_name,position from user_cons_columns where constraint_name='约束名'
SQL> select column_name,position from user_cons_columns where constraint_name='约束名'
列级定义
列级定义是在定义列的时同时定义约束
表级定义
表级定义在把表创建好之后,再定义约束,需要注意到是 not null 约束只能在列级定义上
索引
索引是加速数据存取的数据对象.合理的使用索引可以大大的降低I/O次数,从而提高数据访问性能.
创建索引
单列索引
要经常查询ename则就在ename上建立一个索引
Sql代码
- SQL> select * from emp where ename='SMITH';
SQL> select * from emp where ename='SMITH';
Sql代码
- SQL> create index indexName on emp(ename);
SQL> create index indexName on emp(ename);
复合索引
要经常查询job,deptno则就在job,deptno上建立一个索引
Sql代码
- SQL> select * from emp where job='CLERK' and deptno='20';
SQL> select * from emp where job='CLERK' and deptno='20';
Sql代码
- SQL> create index indexName on emp(job,deptno);
SQL> create index indexName on emp(job,deptno);
建立索引的原则
1、在大表上建立索引才有意义
2、在where语句或者连接条件上经常引用的列上建立索引
索引的缺点:
1、建立索引,系统要占用大约为表1.2倍的硬盘和内存空间来保存条件
2、更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性.
[/b]
索引的分类
1: 按照数据存储方式:可以分为B*树、反向索引、位图索引
2: 按照索引列的个数分类:可分为单列索引和复合索引
3: 按照索引列值得唯一性:可分为唯一索引和非唯一索引
4: 此外还有函数索引,全局索引,分区索引
c
[b]查询索引
Sql代码
- SQL> select * from user_indexes where table_name='表名';
SQL> select * from user_indexes where table_name='表名';
存储过程
创建一个存储过程
Sql代码
- create procedure sp_pro1 is
- begin
- insert into mytest values ('hanshunping','m123');
- end;
create procedure sp_pro1 is begin insert into mytest values ('hanshunping','m123'); end;
创建或者替换一个存储过程create or replace
Sql代码
- create or replace procedure sp_pro1 is
- begin
- insert into mytest values ('hanshunping','m123');
- end;
create or replace procedure sp_pro1 is begin insert into mytest values ('hanshunping','m123'); end;
查看错误信息用show error;
执行过程
1、exec 过程名(参数1,参数2,参数3...)
2、call 过程名(参数1,参数2,参数3...)
exec 与call有什么区别
Sql代码
- declare
- /*定义常量,变量,游标...*/
- v_ename varchar2(30);等价于 v_ename emp.ename%type;
- v_ename 表名.列名%type;
- begin
- /*要执行 pl/sql语句*/
- exception
- /*处理各种错误*/
- end;
- declare
- v_ename varchar2(30);
- begin
- select ename into v_ename from emp where empno=&no;
- --&代表从控制台输入一个字符
- dbms_output.put_line('用户名是:' || v_ename);
- end;
declare /*定义常量,变量,游标...*/ v_ename varchar2(30);等价于v_ename emp.ename%type; v_ename 表名.列名%type; begin /*要执行pl/sql语句*/ exception /*处理各种错误*/ end; declare v_ename varchar2(30); begin select ename into v_ename from emp where empno=&no; --&代表从控制台输入一个字符 dbms_output.put_line('用户名是:' || v_ename); end;
处理异常
Sql代码
- declare
- v_ename varchar2(30);
- begin
- select ename into v_ename from emp where empno=&no;
- --&代表从控制台输入一个字符
- dbms_output.put_line('用户名是:' || v_ename);
- exception
- when no_data_found then
- dbms_output.put_line('没有此用户');
- end;
declare v_ename varchar2(30); begin select ename into v_ename from emp where empno=&no; --&代表从控制台输入一个字符 dbms_output.put_line('用户名是:' || v_ename); exception when no_data_found then dbms_output.put_line('没有此用户'); end;
记录类型
Sql代码
- declare
- /*定义一个
- 记录类型emp_record_type
- 包含三个数据分别是v_ename,v_sal,v_job
- 也就是emp_record_type储存了 v_ename,v_sal,v_job.这么一来方便了管理
- */
- type emp_record_type is record(v_ename emp.ename%type,v_sal emp.sal%type,v_job emp.job%type);
- /* 定义了一个变量sp_record,它的类型是emp_record_type*/
- sp_record emp_record_type;
- begin
- select ename,sal,job into sp_record from emp where empno=7788;
- dbms_output.put_line('员工名:'||sp_record.v_ename);
- end;
declare /*定义一个 记录类型emp_record_type 包含三个数据分别是v_ename,v_sal,v_job 也就是emp_record_type储存了v_ename,v_sal,v_job.这么一来方便了管理 */ type emp_record_type is record(v_ename emp.ename%type,v_sal emp.sal%type,v_job emp.job%type); /*定义了一个变量sp_record,它的类型是emp_record_type*/ sp_record emp_record_type; begin select ename,sal,job into sp_record from emp where empno=7788; dbms_output.put_line('员工名:'||sp_record.v_ename); end;
表
Oracle就相当于数组
Sql代码
- declare
- /*定义一个
- pl/sql表类型emp_table_type,该类型用于存放emp.ename%type
- index by binary_integer 标识下表为整数
- emp_type是定义一个变量类型为emp_table_type
- */
- type emp_table_type is table of emp.ename%type index by binary_integer;
- /* 定义了一个变量sp_record,它的类型是emp_record_type*/
- emp_type emp_table_type;
- begin
- select ename into emp_type(0) from emp where empno=7788;
- dbms_output.put_line('员工名:'||emp_type(0));
- end;
declare /*定义一个 pl/sql表类型emp_table_type,该类型用于存放emp.ename%type index by binary_integer标识下表为整数 emp_type是定义一个变量类型为emp_table_type */ type emp_table_type is table of emp.ename%type index by binary_integer; /*定义了一个变量sp_record,它的类型是emp_record_type*/ emp_type emp_table_type; begin select ename into emp_type(0) from emp where empno=7788; dbms_output.put_line('员工名:'||emp_type(0)); end;
Oracle 循环
Sql代码
- for
- loop
- while
for loop while
函数
创建函数
Sql代码
- create or replace function sp_fun(vname varchar2)
- return number is vsal number;
- begin
- select sal into vsal from emp where emp.ename=vname;
- return vsal;
- end;
create or replace function sp_fun(vname varchar2) return number is vsal number; begin select sal into vsal from emp where emp.ename=vname; return vsal; end;
执行函数
Sql代码
- SQL> var abc number;
- SQL> call sp_fun('SCOTT') into:abc;
SQL> var abc number; SQL> call sp_fun('SCOTT') into:abc;
包
创建包
Sql代码
- create package sp_package is
- end;
create package sp_package is end;
创建包体
Sql代码
- create package body sp_package is
- end;
create package body sp_package is end;
触发器
触发器实质上是隐含的存储过程.当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括
insert,update,delete
视图
在pl/sql程序中包括有:
标量类型
复合类型
参展类型
一个有用的分页存储过程
Sql代码
- create or replace package body mypakage is
- procedure page1(
- tableName in varchar2,--一页显示多少条录数
- pageSize in number,--
- pageNow in number,--
- myRows out number,-- 总记录数
- myPageCount out number,-- 总页数
- o_cursor out c_cursor)
- is
- v_sql varchar2(2000);
- v_begin number:=(pageNow-1)*pageSize+1;
- v_end number:=pageNow*pageSize;
- begin
- v_sql := 'select * from (select t.*,rownum rn from (select * from '|| tableName ||') t where rownum <= '|| v_end ||') where rn>='|| v_begin ||'';
- open o_cursor for v_sql;
- v_sql:='select count(*) from '|| tableName;
- execute immediate v_sql into myRows;
- --if()
- if mod(myRows,pageSize)=0 then
- myPageCount := myRows/pageSize;
- else
- myPageCount := myRows/pageSize + 1;
- end if;
- --关闭游标
- close o_cursor;
- end;
- end mypakage;