Oracle Sqlplus Note

学习Oracle时记录的一些常用命令,需要与不需要只要有时间都可以再看看



Oracle连接：driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:oracle";

启动oracle监听器：lsnrctl start
启懂oracle实例：oradim -startup -sid orcl

启动服务 net start +服务名	e:net start oracleserviceoracle
关闭服务 net stop +服务名	e:net stop mssqlserver

--授权(系统权限)
create user user_name; identified by user_password;
grant create session to user_name;
grant create table to user_name;
grant unlimited tablespace to user_name;	//不受表空间限制
unlimited tablespace == resource

grant create any table to public;//授予权限给所有人

grant alter any table to user_name;//sys授予用户但此用户不能再授予另一用户
grant alter any table to user_name with admin option;//sys授予用户可以在授予另一用户

--授权(对象权限)
grant select on table_name to user_name;	
grant insert on table_name to user_name;
grant update on table_name to user_name;
grant delete on table_name to user_name;

grant all on table_name to user_name;//授予某人对于某表的所有权限

grant select on table_name to user_name with grant option;
--撤销
revoke create table form user_name;;
revoke unlimited table from user_name;;

revoke all on table_name from user_name;//撤销某人对于某表的所有权限

--查询当前用户拥有哪些系统权限   oracle有一个表(视图)
select * from user_sys_privs;

--查询当前用户用户哪些对象权限
select * from user_tab_privs;
select * from user_col_privs;//查询权限控制到列

--关闭数据库
shutdown immediate//仅系统用户有权限

--修改用户密码（管理员专用---不需要知道要修改的用户的密码）
alter user user_name identified by password;

password;//需要登录进用户里修改

--查询特权用户
select * from v$pwfile_users;

--查询当前连接的数据库全称
select * from global_name

--设置显示的宽度、宽度
set linesize 400
set pagesize 20

--角色
create role role_name;//创建角色
drop role role_name;//删除角色

grant create session to role_name;//将权限授予给角色
grant create table to role_name;//即将多权限放入一角色中
。。。(有些系统权限无法直接赋予角色example:unlimited tablespace此权限太大了)
grant select on table_name to role_name;//将对象权限授予给角色

grant role_name to user_name;//角色赋予用户 即用户将继承角色中的所有权限

--删除用户
drop user user_name cascade;


--显示系统当前时间
select sysdate from dual;
--打开记事本
edit;
--清屏
cl scr;
--执行dos的dir(显示目录)
hos dir;
--保存成文件
spool E：\save.text
。。。。
spool off;
--四舍五入
select round(1.233,2) from dual;


--修改表名
rename table_name to table_name
--使用函数to_date()函数可以指定日期格式
to_date('xxxx-xx-xx','yyyy-mm-dd')
--查询当前用户可以使用哪些表
select table_name from user_tables;
--查看执行语句所花费的时间
set timing on;


——逻辑备份和恢复

  导出使用exp命令完成、导入导出 的时候，必须从cmd到oracle 的bin下去
导出表：
	D:\oracle\product\10.2.0\db_1\bin>exp userid=scott/password@oracle tables=(emp,dept) file=d:\emp.mp;
导出表结构：
	D:\oracle\product\10.2.0\db_1\bin>exp  userid=scott/password@oracle tables=(emp,dept) file=d:\emp.dmp  direct=y;
导出自己的方案：
	D:\oracle\produc\10.2.0\db_1\bin>exp userid=scott/password@oracle owner=scott file=D:\emp.dmp
导出数据库：
	D:\oracle\product\10.2.0\db_1\bin>exp userid=system/password@oracle inctype=complete file=D:\emp.dmp


导入数据库：
	D:\oracle\product\10.2.0\db_1\bin>imp userid=system/password@oracle full=y file=D:\emp.dmp
导入自身方案：
	D:\oracle\ora92\bin>imp  userid=scott/password@oracle owner=scott file=d:\emp.dmp 
导入其他方案：
	D:\oracle\ora92\bin>imp userid=scott/password@oracle file=D:\emp.dmp fromuser=system touser=scott
导入数据：
	D:\oracle\ora92\bin>imp userid=scott/passowrd@oracle tables=(emp,dept) file=D:\emp.dmp ignore=y
导入表：
	D:\oracle\ora92\bin>imp userid=scott/password@oracle tables=(emp,dept) file=D:\emp.dmp
导入表给其他用户：
	D:\oracle\ora92\bin>imp userid=scott/password@oracle tables=(emp,dept) file=D:\emp.dmp touser=scott;

——用户名，权限和角色
--查询所有数据库用户的详细信息
select * from dba_users;
--查询所有用户的系统权限
select * from dba_sys_privs;	select * from user_sys_privs;
--查询所有用户的对象权限
select * from dba_tab_privs;	select * from user_tab_privs;
--查询所有用户的列权限
select * from dba_col_privs;	select * from user_col_privs;
--查询所有用户的角色
select * from dba_role_privs;	


--查询角色具有的系统权限与对象权限(sys)
select * from role_sys_privs where role='role_name';(role_name要大写)
system用户无权限。
--查询用户具有哪些角色
select * from dba_role_privs where grantee='user_name';(user_name要大写)
system用户有此权限。

--帐户锁定(指定该帐户登陆时最多可以输入密码的次数，也可以指定锁定的时间)
create profile a limit failed_attempts 3 password_lock_time 2;//创建配置文件	alter user user_name profile a;//指定配置用户。
--帐户解锁
alter user user_name account unlock;

--用户锁
alter user user_name account lock;//给用户加锁
alter user user_name account unlock;//给用户解锁
alter user user_name password expire;//用户登录即改密码 即 口令“已失效”

--让管理员修改密码(为了安全/不要一直用一个密码)
create profile a limit password_life_time 10 password_grace_time 2;//10是每隔10天就要修改，2是宽限期	alter user user_name profile b;//此方法最好是用sys给system管理员设置	
	/	create profile b limit password_life_time 10 password_grace_time 2 password_reuse_time 10;	alter user user_name profile b;	//password_reuse_time 10 意思是要想修改的新密码和旧密码一样的话，隔10天后就可以。哈哈

--删除profile
drop profile a/b;

Linux 下oracle 的启动过程：
	lsnrctl start	//监听器
	sqlplus sys/mylove as sysdba;
	startup

Windows下oracle 的启动过程：
	lsnrctl start;	
	oradim -starup -sid orcl;==net start oracleserviceorcl	//实例


oracle数据库的三种验证：
	1、操作系统验证   即  不输入用户名、密码也能登进sys。
	2、密码文件验证
		如果忘记了sys的密码的话：
	先把目录下database里的PWDorcl.ora文件给删掉，之后在dos下输入：	orapwd file=(路径，应写下database的目录与文件名) password=(你想输入的密码)
	3、数据库验证 unknown

dba职责：
	1、安装和升级oracle数据库
	2、建库，表空间，表，视图，索引
	3、制定并实施备份与恢复计划
	4、数据库权限管理，调优，故障排除
	5、对于高级dba，要求能参与项目开发，会编写sql语句、存储过程。触发器。规则。约束、包。

--运行打开服务
services.msc


SQL支持下列类别的命令：
1、数据定义语言(DDL)
2、数据操纵语言(DML)
3、事务控制语言(TCL)
4、数据控制语言(DCL)

Oracle数据类型：
字符、数值、日期时间、RAW/LONG RAW 、LOB

--DML – 利用现有的表创建表



——修改表

--给列添加主键（primary key）
alter table 表名 add constraint aaa primary key(列名);
--给列添加唯一性(但注意它不是主键，因为主键就是唯一性)
alter table 表名 add constraint aaa unique(列名);
--给列添加外键（foreign key 。。。references）
alter table 表名1 add constraint aaa foreign key(表名1的列名) references 表名2(表名2的列名);
--给列添加检查约束（check）
alter table 表名 add constraint aaa check(sex='M' or sex='F');


--添加字段
alter table 表名 add 列名 类型;
--修改字段
alter table 表名 modify 列名 类型;
--删除字段
alter table 表名 drop column 列名

--替代变量的使用 &
select * from emp where deptno=&a;
--定义替代变量
define a = 10;	查看替代变量 define a;
select * from emp where deptno=&a;
--查询所有替代变量	define;
--删除替代变量	undefine;

--用带rollup或cube的group by 实现超级聚合,即对group by 进行再聚合
rollup 实现从右往左再聚合、cube除了形成rollup的结果，还会按相反的方向形成结果。Ex：(select deptno,job,sum(sal) from emp group by rollup/cube (deptno,job);
--高级查询
Ex：select a.*,b.avg_sal from scott.emp a,(select deptno,avg(sal)avg_sal from scott.emp group by deptno)b where a.deptno=b.deptno and a.sal>b.avg_sal

--修改如期的格式
alter session set nls_date_format='yyyy-mm-dd';

--查询表空字段的信息
select * from 表名 where 列名 is null;

--有经验的dba一上班可能就设一个回滚点。
savepoint 名;rollback to 名;

--设置自动提交事务的开关(回车即是自动提交)
set autocommit on/off;

--去重复
select distinct * from 表名;

--仔细看下面的代码
 select sal*12+nvl(comm,0)*12 年薪,ename 员工,comm 奖金 from scott.emp; 	假如用  select sal*12+comm*12 年薪,ename 员工,comm 奖金 from scott.emp; 		处理null值的函数(nvl)

--如何查询出第三个字符为大写O的信息
select * from 表名 where 列名 like '__O%';

--如何查询出id是1，3，9，46，123。。。
select * from 表名 where id in(1，3，9，46，123。。。);

--如何查询出部门号升序而员工号工资降序的信息
select * from 表名 order by deptno,sal desc;


--创建视图
create or replace view 视图名 as select * from 表名 + 条件;
[with read only]/[with check option]
多表连接的视图不让插入和修改
--查询用户的视图
select * from user_views;

--创建索引
create index 索引名 on 表名[列名]
--查询当前用户的索引
select * from user_indexes;

--创建同义词
create synonym 别名 for 用户.表名;(私有的，只能由创建者自己使用)
create public synonym 别名 for 用户.表名;(公有的，由管理员创建，所有用户均可访问）

--创建序列(在SQLServer里是标识列即identity(1,1))
create sequence 序列名
increment by 1
start with 1
maxvalue 1.0E28
minvalue 1
nocycle;

insert into 表名 values(序列名.nextval,xxxx);

--查询序列的最大下标
select 序列名.nextval from dual;


——表空间
--查看有表空间
select * from v$tablespace;
--查看数据文件
select * from v$datafile;
--临时表空间
temporary tablespace;
--还院表空间
undo tablespace;
--创建表空间(表空间名要用双引号，下同)
create tablespace "表空间名" logging datafile 'D:\oracle\product\10.2.0\db_1\oradata\sample\表空间名.dbf' size 5m reuse autoextend on next 512k maxsize 10m;（autoextend on：从512K自动扩张到10m）
--修改表空间的状态
alter tablespace "表空间名" offline/online;
alter tablespace "表空间名" read only/read write;
--删除表空间
drop tablespace "mylove" including contents and datafiles;(including contents ：只删除表空间的对象，不删除文件。datafile：删除数据文件)





——函数
1、数值函数：
	abs(n)：返回n的绝对值
select abs(n) from dual;
	ceil(n)：返回≥n的最小整数
select ceil(n) from dual;
	floor(n)：返回 ≤n的最小整数
select floor(n) from dual;
	round(n,m)：按照m表明的小数点前后取n四舍五入的值
select round(n,m) from dual;
	trunc(n,m)：与round类似，只是不四四舍五入
select trunc(n,m) from dual;
	sign(n)：n>0返回1;n<0返回-1;n=0返回0
select sign(n) from dual;
	mod(n,m)：返回n/m的余数
select mod(n,m) from dual;
	power(n,m)：返回n的m次方
select power(n,m) from dual;
	sqrt(n)：n的平方根
select sqrt(4) from dual;
	
2、字符函数
	concat('n','m')：对字符串n,m进行边连接,返回连接后的字符串
select concat('n','m') from dual;
	substr('n',m,length)：从s串中第m个字符起，取长度为n的一个子串，如果n省略，一直到s串的尾部
select substr('n',m,length from dual;
	length('n')：返回字符串n的长度
select length('n') from dual;
	lower('n')：返回字符串n的小写，即将字符串转化为小写
select lower('n') from dual;
	upper('n')：返回字符串n的大写，即将字符串转化为大写
select upper('n') from dual;
	Ltrim(' n')：移除左边的空格字符
select Ltrim(' n') from dual;
	Rtrim('n ')：移除右边的空格字符
select Rtrim('n ') from dual;
	replace('s1','s2','s3')：字符串s1中查找s2字符串，并用s3字符串代替，如果s3省略，删除s1中所有的s2串
select replace('s1','s2','s3') from dual;
	instr('n','m')：在字符串n中查找m，返回其位置。不是下标，未找到返回0
select instr('n','m') from dual;
	

　　

转载于:https://www.cnblogs.com/guoxianlong/archive/2013/04/06/3002393.html