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;