Oracle常见不常见所有命令

-- doc连接Oracle
sqlplus 用户/口令@192.168.75.128:1521/orcl
sqlplus scott/tiger@172.21.96.128/orcl

-- ########################## 常用命令 ##########################
-- 切换用户
conn 用户/密码;
-- 显示当前用户名
show user;

-- 查询当前用户的所有表
select table_name from user_tables;

-- 查看错误信息
show error;



-- ############################# 表的操作 ################################
-- 创建表
create table 表名(
字段名 char -- 最大2000字符
字段名 varchar2 -- 最大4000字符
字段名 clob -- 字符型大对象，最大4G(character large object)
字段名 number -- -10^38~10^38 整数及小数
字段名 number(5,2) -- -999.99~999.99
字段名 number(5) -- -99999~99999
字段名 date -- 年月日和时分秒
字段名 timestamp -- 高精度
字段名 blob -- 二进制数据，可存图片、声音或视频，最大4G
) [tablespace mytablespace];-- 指定表空间
-- 复制表结构和数据
create table 新表
as 
select 源表字段1 新表字段1, 源表字段2 新表字段2, ...
from 源表;

-- 清空表
delete from 表名;
-- 删除表
drop table 表名;
-- 修改表名
rename 原表名 to 新表名;
-- 无法回滚的删除表(效率高)
truncate table 表名;

-- ############################## 字段操作： #############################
-- 添加字段
alter table 表名 add (字段名 字段类型);
-- 修改字段(如果表中存在数据，则字段长度只能增加，不能减小)
alter table 表名 modify (字段名 字段类型);
-- 删除字段
alter table 表名 drop column 字段名;
-- 修改字段名
alter table 表名 rename column 旧字段名 to 新字段名;

-- 更新字段值
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... where ...;

-- 蠕虫复制(批量复制字段值)
insert into 新表 (新表字段) select 源表字段 from 原表;
-- 批量更新字段值
update 表名 set (字段) = (select 字段 from 表名 where 条件1) where 条件2;


-- ############################# 日期字段操作 ################################
-- 插入日期数据(默认格式)
insert into 表名 values('1-10月-2018');
-- 使用to_date函数插入
insert into 表名 values(to_date('2018-10-2', 'yyyy-mm-dd'));

-- 修改日期格式
alter session set nls_date_format='yyyy-mm-dd';
-- 插入日期数据(修改后的格式)
insert into 表名 values('2018-10-1');

-- ############################### 查询操作 ###############################
-- 查看表结构
desc 表名;
-- 取消显示重复行
select distinct 字段名 from 表名;
-- 别名
select 字段名 "别名" from 表名;
-- 空值参与计算(如果该字段为null则用0代替)
select nvl(字段名,0) * 12 from 表名;
-- 日期条件查询
select * from 表名 where 日期字段 > '1-10月-2018';
-- 查询空值
select * from 表名 where 字段名 is null;
-- 查询非空值
select * from 表名 where 字段名 is not null;
-- 升序排序(降序排序)
select * from 表名 order by 字段名1 [desc], 字段名2 [desc], ...;
-- 使用别名排序
select 字段名 别名 from 表名 order by 别名 [desc];
-- 分组函数
select count(字段名),max(字段名),min(字段名),avg(字段名),sum(字段名) from 表名;
-- 分组order by
select 选择列表 from 表名 order by 选择列表中的字段/分组函数;

-- ############### 分页查询 ###############
-- 1.增加行号
select t1.*, rownum 行号列别名 from (select * from 表名) t1;
-- 2.查询前n行数据(rownum在一条查询语句中只能使用一次)
select t1.*, rownum 行号列别名 from (select * from 表名) t1 where rownum <= n;
-- 3.查询m~n行数据
select * from (select t1.*, rownum 行号列别名 from (select 字段 from 表名 ...) t1 where rownum <= n) where 行号列别名 >= m;

-- ############### 子查询: ###############

-- 单列子查询:
-- = 单等于
select 选择列表 from 表名 where 字段 = (单行单列查询结果);
-- in 多等于
select 选择列表 from 表名 where 字段 in (多行单列查询结果);
-- all 大于所有子查询结果(max)
select 选择列表 from 表名 where 字段 > all (多行单列查询结果);
-- any 大于任意子查询结果(min)
select 选择列表 from 表名 where 字段 > any (多行单列查询结果);

-- 多列子查询
select 选择列表 from 表名 where (字段1, 字段2, ...) = (select 字段1, 字段2, ...);

-- 子表查询
select 选择列表 from 表名, (查询结果) 子表别名 where 子表别名.字段, ...;

-- 联合查询
select 字段 from 表名 where 条件1
union [all] -- 取并集, 默认去除重复行; [all]: 不去重
intersect	-- 取交集
minus		-- 取差集
select 字段 from 表名 where 条件2;

-- ################################ 连接查询：####################################

-- 交叉连接(原则：条件数量至少是表的个数-1)
select sno from 表1 ,/*cross join*/ 表2 where 条件;

-- #外连接:	显示数据左表一定在左边，右表一定在右边
-- 左连接(左表有的，而右表没的置为NULL)	查询结果一定 >= 左表数量
select 字段 from 表1 
left join 表2 
on 表1.字段名1 = 表2.字段名1;

select 字段 from 表1, 表2 where 表1.字段名1 = 表2.字段名1(+);

-- 右连接(与上面相反)
select 字段 from 表1 
right join 表2 
on 表1.字段名1 = 表2.字段名1;

select 字段 from 表1, 表2 where 表1.字段名1(+) = 表2.字段名1;

-- 内连接（左右连接的交集）
select 字段 from 表1 
inner join 表2 
on 表1.字段名1 = 表2.字段名1;

-- 全外连接（左右连接的并集）
select 字段 from 表1 
full join 表2 
on 表1.字段名1 = 表2.字段名1;


-- ############################### 索引 ###############################
-- 创建单列索引
create index 列名_idx on 表名(字段名);
-- 创建复合索引
create index 表名_idx on 表名(字段1, 字段2,...);
-- 查询指定表上的所有索引
select index_name, index_type from user_indexes where table_name='TEST1';
-- 查询指定索引对应字段
select table_name, column_name from user_ind_columns where index_name='TEST_IDX';

-- ############################### 视图 ###############################
-- 创建视图(修改视图只加replace)
create [or replace] view 视图名
as
查询结果
[with read only]  -- 只读
[with check option]; -- 约束检查(是否满足查询结果中的where条件)

-- 删除视图
drop view 视图名;


-- ############################### 约束 ###############################
-- 约束类型:
NOT NULL 		-- 非空约束
UNIQUE 			-- 唯一性约束
PRIMARY KEY		-- 主键约束
FOREIGN KEY 	-- 外键约束(另一张表被引用的列需是主键或Unique)
CHECK       	-- 检查约束

-- 查询指定表的约束信息
select constraint_name, constraint_type, status, validated from user_constraints where table_name='表名(大写)';
-- 查询指定约束所对应的列
select column_name, position from user_cons_columns where constraint_name='约束名(大写)';


-- 创建约束
create table stu(
sno char(10) ,
sname varchar2(30) NOT NULL,	-- 非空约束只能采用列级定义
email varchar2(30),
gender char(2),
[constraint pk_主键名] primary key(sno),
[constraint 唯一键名] unique(email),
[constraint 检查约束名] check (gender in('m','f')),
[constraint fk_外键名] foreign key(本表字段) references 其他表(列名)
);
-- 添加约束
alter table 表名 add (
constraint 约束名 约束类型(列名),
constraint 约束名 约束类型(列名)
);
-- 添加not null约束,因为not null 类型必须声明在列上,无法声明在表上,所以必须用modify方式来写 
alter table 表名 modify (
列名 constraint 约束名 not null
);
-- 删除约束
alter table 表名 drop constraint 约束名;
-- 禁用约束
alter table 表名 disable consstaint 约束名;


-- ############################### 事务 ###############################
-- 设置保存点
savepoint 保存点名称;
-- 回滚
rollback to 保存点名称;
-- 提交事务(清空所有保存点)
commit;
-- 回滚至上次提交事务后
rollback;
-- 设置只读事务，当前用户无法进行增删改操作，其他用户可以，但是当前用户无法获取最新数据
set transaction read only;

-- ################################ 字符串函数 ##############################
-- 示例
select lower(字符串类型的字段), 其他字段 from 表名;

-- 小写
select lower(字段) from dual;
-- 大写	
select upper(字段) from dual;
-- 长度
select length(字段) from dual;
-- 截取('abcd'表示字段)
select substr('abcd', 1 ,2) from dual; 	-- ab
-- 拼接字符串
select 字段 || '后缀' from 表名;
-- 替换(字段值中的"A"替换为"啊")
select replace(字段, 'A', '啊') from 表名;
-- 返回子串在主串中出现的位置
select instr('abcde', 'bc') from dual; -- 2

-- ################################# 数学函数 #################################
-- 四舍五入至小数点后n位，负数表示小数点前n位(默认四舍五入至个位)
select round(100.56, 1) from dual;  -- 100.6
-- 截尾取整
select trunc(100.56, 1) from dual;  -- 100.5
-- 向下取整
select floor(100.56) from dual;  -- 100
-- 向上取整
select ceil(100.46) from dual;  -- 101
-- 取模(5 % 2)
select mod(5, 2) from dual;  -- 1
-- 绝对值
select abs(-3) from dual;  -- 3
-- m的n次幂
select power(3, 2) from dual;  -- 9

-- ################################# 日期函数 #################################
-- 查询系统时间
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 增加日期(单位: 月)
select add_months('2-10月-2018', 12) from dual;	-- 2-10月-2019
-- 查询指定日期的月的最后一天日期
select last_day(sysdate) from dual;
-- 格式化数字
select to_char(123.56, 'L99,999.99') from dual;	--  ￥123.56

-- ################################### 系统函数 #####################################
-- 当前数据库名？
select sys_context('userenv', 'dn_name') from dual;
-- 当前日期格式
select sys_context('userenv', 'nls_date_format') from dual;
-- 当前用户名
select sys_context('userenv', 'session_user') from dual;
-- 当前主机名
select sys_context('userenv', 'host') from dual;

-- ################################# 用户操作 #################################
-- 查询指定用户的详细信息
select * from dba_users where username='用户名';
-- 创建用户,首次创建密码必须已字母开头)(q1034)
create user 用户 identified by q1034
-- 修改当前用户密码[指定用户]
password [用户]
alter user 用户 identified by 密码;
-- 删除用户(cascade: 带表删除)
drop user 用户 [cascade];

-- ########################## 一、系统权限(见图:权限.jpg) ##########################

-- 系统权限    			说明
create table		-- 建表
create view			-- 建视图
create session		-- 登陆数据库
create user			-- 创建用户
create trigger		-- 创建触发器
alter user      	-- 改用户
alter session		-- 修改会员
alter database		-- 改数据库
create tablespace	-- 改表空间
create procedure	-- 建过程，函数
ulimited tablespace	-- 无限空间

-- 查询当前用户拥有的系统权限
select * from user_sys_privs;
-- 查询当前用户拥有的角色
select * from user_role_privs;
-- 授予系统权限/角色, [with grant option]：传递权限
grant 系统权限/角色 to 用户 [with admin option];
-- 回收权限
revoke 系统权限/角色 from 用户;

-- ###### 角色(封装了系统权限和对象权限) ######
-- 创建角色
create role 角色名 not identified;
-- 创建加密的角色
create role 创建角色 identified by shunping;
-- 给角色授权
grant 系统权限 to 角色名 [with admin option];
grant 对象权限 on 表名 to 角色名;-- 无法传递
-- 给用户分配角色
grant 角色名 to 用户 [with admin option];
-- 删除角色
drop role 角色名;


-- ########################## 二、对象权限 ##########################
权限\对象	|	表	|	视图 |	序列 |	存储程序 |
alter		|	Y	|		 |	Y	 |           |
select 		|	Y	|	Y	 |	Y	 |           |
insert		|	Y	|	Y	 |	     |           |
delete		|	Y	|	Y	 |	     |           |
update		|	Y	|	Y	 |	     |           |
refereneces	|	Y	|		 |	     |           |
execute		|	Y	|		 |		 |	Y        |
grant		|	 	|	Y	 |	Y    |  Y        |
lock		|	Y	|	Y	 |	Y	 |           |
rename		|	Y	|	Y	 |	Y	 |	Y        |
all

-- 查询本用户可访问的对象权限
select * from user_tab_privs;
-- 将查询本用户表的权限授予用户。[with grant option]：传递权限
grant select on 表名 to 用户 [with grant option];
-- 收回查询权限(级联回收)
revoke select on 表名 from 用户;

-- ########################## 账户锁定 ##########################
-- 限定指定用户尝试登陆次数(3次)，并锁定登陆时间(1h)单位: 天
-- 1.创建profile文件(名为: lock_account)
create profile lock_account limit failed_login_attempts 3 password_lock_time 1/24;
-- 2.指定用户读取profile文件
alter user 用户 profile lock_account;
-- 解锁
alter user 用户 account unlock;

-- ########################## 终止口令(密码) ##########################
-- 指定用户定期(10)修改密码，宽限期2天。[旧密码30天后可重用]
-- 1.创建profile文件(名为: password_history)
create profile password_history limit password_life_time 10 password_grace_time 2 [password_reuse_time 30];
-- 2.指定用户读取profile文件
alter user 用户 profile password_history;
-- 删除profile[cascade：当文件指定用户时强制删除(用户失去约束)]
drop profile password_history [cascade];

-- ########################## 备份表、方案和数据库 ##########################
-- 导出表
exp [userid=]用户/口令@orcl tables=(表名,...) file=c:\test\导出表.dmp
-- 导出其他用户表
exp system/manager@orcl tables=(用户.表名) file=c:\test\使用system导出表.dmp
-- 导出表结构
exp 用户/口令@orcl tables=(表名) file=c:\test\导出表结构.dmp rows=n
-- 直接方式导出表结构(效率高，需要保证Oracle字符集和客户端字符集一致)
exp 用户/口令@orcl tables=(表名) file=c:\test\直接方式导出表结构.dmp direct=y
-- 导出方案
exp 用户/口令@orcl owner=用户 file=c:\test\导出方案.dmp
-- 导出其他用户方案
exp system/manager@orcl owner=(用户, ...) file=c:\test\导出其他用户方案.dmp
-- 导出数据库
exp system/manager@orcl full=y inctype=complete file=c:\test\导出数据库.dmp

-- 导入表
imp 用户/口令@orcl tables=(表名) file=c:\test\导出表.dmp ignore=y
-- 给其他用户导入表??
imp system/manager@orcl tables=(用户.表名) file=c:\test\system导出表.dmp touser=用户
-- 导入表结构
imp 用户/口令@orcl tables=(表名) file=c:\test\导出表.dmp rows=n
-- 导入方案
imp 用户/口令 file=c:\test\导出方案.dmp
-- 给其他用户导入方案
imp system/manager file=c:\test\导出方案.dmp fromuser=用户 touser=用户
-- 导入数据库
imp system/manager full=y file=c:\test\导出数据库.dmp

-- ########################## 其他命令 ##########################

-- 断开连接
disc

-- 运行脚本
start c:\a.sql;
-- 编辑脚本
edit c:\a.sql;

-- 保存操作信息至指定文件
spool c:\b.sql;
操作记录
spool off;

-- 显示行宽
show linesize;
-- 设置行宽
set linesize 120;
-- 设置每页显示n条记录
set pagesize n+3;

-- sys用户登录
conn sys/change_on_install as sysdba[/sysoper/dba];

-- 显示参数
show parameter;

-- 查询当前用户可访问的表
select table_name from all_tables;

-- 查询当前数据库全称
select * from global_name;

-- 查询oracle中所有表
select table_name from dba_tables;

-- 查询指定用户拥有的角色
select * from dba_role_privs where grantee='用户名';
-- 查询指定角色包含的系统权限
select * from dba_sys_privs where grantee='角色名';
select * from role_sys_privs where role='角色名';
-- 查询指定角色包含的对象权限
select * from dba_tab_privs where grantee='角色名';
-- 查询指定用户具有的列权限
select * from dba_col_privs where grantee='用户名';

-- 查询oracle中所有的系统权限
select * from system_privilege_map order by name;
-- 查询oracle中所有的角色
select * from dba_roles;
-- 查询oracle中所有对象权限
select distinct privilege from dba_tab_privs;

-- ########## 表空间 ##########
-- 创建表空间
create tablespace 表空间名 datafile 'c:\test\表空间名.dbf' size 10M uniform size 128K;
-- 使表空间只读(无法进行增删改操纵)
alter tablespace 表空间名 read only;
-- 使表空间可读可写
alter tablespace 表空间名 read write;
-- 使表空间脱机
alter tablespace 表空间名 offline;
-- 使表空间联机
alter tablespace 表空间名 online;
-- 删除表空间及其文件
drop tablespace 表空间名 including contents and datafiles;
-- 增加表空间
alter tablespace 表空间名 add datafile 'c:\test\表空间名_add.dbf' size 10M;
-- 查询表空间信息
select tablespace_name from dba_tablespaces;
-- 查询oracle中的表空间
select * from dba_tablespaces;
-- 查询指定表空间所包含的数据文件
select file_name, bytes from dba_data_files where tablespace_name='表空间名(大写)';
-- 查询指定表空间下所有表
select * from all_tables where tablespace_name='表空间名(大写)';
-- 查询指定表的表空间
select tablespace_name, table_name from user_tables where table_name='表名(大写)';

-- 移动表空间的数据文件:
-- 1.确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='C:\TEST\表空间名(大写).DBF';
-- 2.使表空间脱机
alter tablespace 表空间名 offline;
-- 3.移动数据文件
host move c:\test\表空间名(大写).DBF c:\test\move\表空间名(大写).DBF
--4.对数据文件进行逻辑修改
alter tablespace 表空间名 rename datafile 'c:\test\表空间名.dbf' to 'c:\test\move\表空间名.dbf'
-- 5.使表空间联机
alter tablespace 表空间名 online;

-- ##################### 序列 #####################
-- 创建序列
create sequence 序列名 increment by 步长
start with 起始值
maxvalue 最大值 | nomaxvalue	-- 无限大
minvalue 最小值 | nominvalue	-- 无限小
cycle | nocycle	-- 循环/不循环
cache 缓存数量 | nocache; 	-- 缓存/不缓存

-- 当前值(上次执行的值)
select 序列名.currval from dual;
-- 下次值
select 序列名.nextval from dual;
-- 删除序列
drop sequence 序列名;
-- 修改序列(下列可选选项)
alter sequence seq1
increment by 步长
maxvalue 最大值 | nomaxvalue	-- 无限大
minvalue 最小值 | nominvalue	-- 无限小
cycle | nocycle	-- 循环/不循环
cache 缓存数量 | nocache; 	-- 缓存/不缓存

-- #################### 同义词(需要create synonym权限) #####################
-- 创建同义词(public: 所有用户可用)
create [public] synonym 同义词名 for 用户名.表名;
-- 使用同义词查询
select * from 同义词名;
-- 删除同义词
drop synonym 同义词名;