oracle常用命令
Oracle用户创建及权限设置
权限：
create session 允许用户登录数据库权限
create table 允许用户创建表权限
unlimited tablespace 允许用户在其他表空间随意建表
角色：
connect
resource
dba
CONNECT角色： --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图
RESOURCE角色： --是授予开发人员的，能在自己的方案中创建表、序列、视图等。
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
DBA角色，是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限
示例：
SQL> conn / as sysdba;
SQL>create user username identified by password --username/password都是用户自定义
SQL> grant dba to username;
SQL> conn username/password
SQL> select * from user_sys_privs;
解锁用户
alter user 用户名 account unlock; #解锁用户
删除用户
select sid,serial#,username from v$session; #查询所有用户的会话连接
select sid,serial# from v$session where username='用户名(大写)'; #查看指定用户的会话信息
alter system kill session '12,235'; #kill会话信息
drop user db_test cascade; #删除db_test用户
drop user "db_test" cascade; #如果查出来的用户名是小写，则需要加双引号，忽略大小写
修改密码：
alert user scott identified by tiger;//修改密码
用户授权：
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限，允许用户登录数据库
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)
grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan;//授予删除表的权限
grant insert on tablename to zhangsan;//授予插入的权限
grant update on tablename to zhangsan;//授予修改表的权限
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限
查看权限：
select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限
角色：
角色即权限的集合，可以把一个角色授予给用户
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
启停数据库
停止数据库
sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE
[oracle@localhost ~]$ lsnrctl stop
启动数据库
su - oracle
lsnrctl start
sqlplus / as sysdba
startup
表空间
查看创建表空间的语句
SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', 表空间名字.tablespace_name))FROM DBA_TABLESPACES 表空间名字;
查看用户属于哪个表空间
select username,default_tablespace from dba_users where username='db_test';
select * from user_users; #查看当前用户使用的表空间
SELECT * FROM DBA_USERS; #查看所有用户对应的表空间
查看表空间文件的位置
select file_name from dba_data_files;
查看data_pump_dir路径
select * from dba_directories;
创建表空间
#test为表空间名字，100m代表初始大小，autoextend on表示自动扩展
create tablespace test datafile '/u01/app/oradata/test/test.dbf' size 500m autoextend on;
#创建新的表空间文件，解决表空间满的问题
ALTER TABLESPACE 表空间名字 ADD DATAFILE '/app/oracle/oradata/test/test2.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 31G;
删除表空间
drop tablespace 表空间名字 including contents and datafiles CASCADE CONSTRAINTS; #删除非空表空间，包含物理文件
查看表空间物理文件及大小
select
tablespace_name,
file_id, file_name,
round(bytes/(1024*1024),0) total_space
from
dba_data_files
order by tablespace_name;
给用户授权能访问的表空间
ALTER USER 用户名 QUOTA UNLIMITED ON 表空间名;
select语句
all/any
在Oracle中，any()表示括号内任何一个条件，只要有一个满足即可；而all()表示所有的条件都满足才可以。
all用法
--大于最大值
select * from A where id >= all(select id from A)
--这相当于
select * from A where id >= (select max(id) from A)
--小于最小值
select * from A where id <= all(select id from A)
--这相当于
select * from A where id <= (select min(id) from A)
any用法
--大于任意一个数即可，大于最小值
select * from A where id >= any(select id from A)
--这相当于
select * from A where id >= (select min(id) from A)
--小于任意一个数即可，小于最大值
select * from A where id <= any(select id from A)
--这相当于
select * from A where id <= (select max(id) from A)
having用法
对数据聚合后计数
示例：
NAME|SUBJECT|SCORE|
----+-------+-----+
张三 |数学 | 80|
张三 |英语 | 90|
张三 |语文 | 70|
李四 |数学 | 55|
李四 |英语 | 45|
李四 |语文 | 85|
王五 |数学 | 95|
王五 |英语 | 90|
王五 |语文 | 95|
赵六 |英语 | 90|
我们现在想知道，有几个人的总成绩超过了200分,正确写法应该是：
SELECT count(DISTINCT name) FROM (
SELECT name FROM CSDN_HAVING
GROUP BY name
HAVING sum(score) > 200
)
找出数据中的重复数据：
仍然以上面的CSDN_HAVING表为例，现在想找出表内重复出现的姓名，可以使用下面的sql：
SELECT name FROM CSDN_HAVING
GROUP BY name
HAVING count(name) > 1
内连接和外连接
示例：表t_user1,t_user2,t_user3,各有id,name两列
| id | name |
|---|---|
| 1 | 10A |
| 2 | 20A |
| id | name |
|---|---|
| 1 | 10B |
| 3 | 30B |
| id | name |
|---|---|
| 1 | 10C |
| 4 | 40C |
内连接
内连接，即最常见的等值连接，例：
SELECT *
FROM t_user1,t_user2
WHERE t_user1.id = t_user2.id
结果
| id | name | id | name |
|---|---|---|---|
| 1 | 10A | 1 | 10B |
外连接
1． 左外连接 left outer join 或者 left join
左外连接就是在等值连接的基础上加上主表中的未匹配数据，例：
SELECT *
FROM t_user1
LEFT OUTER JOIN t_user2
ON t_user1.id = t_user2.id
另一种写法：
SELECT *
FROM t_user1 ,t_user2
WHERE t_user1.id=t_user2.id(+)
结果：
| id | name | id | name |
|---|---|---|---|
| 1 | 10A | 1 | 10B |
| 2 | 20A |
三个表做做外连接
SELECT *
FROM t_user1
LEFT OUTER JOIN t_user2
ON t_user1.id=t_user2.id
LEFT OUTER JOIN t_user3
ON t_user1.id=t_user3.id
结果：
| id | name | id | name | id | name |
|---|---|---|---|---|---|
| 1 | 10A | 1 | 10B | 1 | 10C |
| 2 | 20A |
2． 右外连接 right outer join 或者 right join
右外连接是在等值连接的基础上加上被连接表的不匹配数据
SELECT *
FROM t_user1
RIGHT OUTER JOIN t_user2
ON t_user1.id=t_user2.id
另一种写法：
SELECT *
FROM t_user1,t_user2
WHERE t_user1.id(+)=t_user2.id
结果：
| id | name | id | name |
|---|---|---|---|
| 1 | 10A | 1 | 10B |
| 3 | 30B |
exists用法
示例：通过exists判断数据，并查找存在的数据
---查找emp表中的数据，且员工部门在dept表中能找到
select * from emp g
where 1=1 ---1=1可以不用，此用法方便再后边实现and条件的注释
and exists (select 1 from dept t where t.deptno = g.deptno);
----查找dept表中的数据，且员工部门在emp表中能找到
select * from dept t
where 1=1
and exists (select 1 from emp e where t.deptno = e.deptno)
常用函数
1.concat(x,y) 功能与|| 符类似
select id, concat(ENAME,'aaa') as ENAME from Test6
2.initcap(字段名) 首字母转大写,其他小写
select id,initcap(ENAME) from Test6
3.lower(字段名) 转小写
select id,lower(ENAME) from Test6
4.substr
格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;
解析：
格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度
格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。
示例：格式一
、select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
、select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
、select substr('HelloWorld',2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
、select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
、select substr('HelloWorld',5,3) value from dual; //返回结果：oWo
、select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
、select substr('HelloWorld',-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）
、select substr('HelloWorld',-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）
、select substr('HelloWorld',-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
、select substr('HelloWorld',-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）
（注：当a等于0或1时，都是从第一位开始截取(如：1和2)）
（注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
（注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符； 只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）
示例：格式二
11、select substr('HelloWorld',0) value from dual; //返回结果：HelloWorld，截取所有字符
12、select substr('HelloWorld',1) value from dual; //返回结果：HelloWorld，截取所有字符
13、select substr('HelloWorld',2) value from dual; //返回结果：elloWorld，截取从“e”开始之后所有字符
14、select substr('HelloWorld',3) value from dual; //返回结果：lloWorld，截取从“l”开始之后所有字符
15、select substr('HelloWorld',-1) value from dual; //返回结果：d，从最后一个“d”开始 往回截取1个字符
16、select substr('HelloWorld',-2) value from dual; //返回结果：ld，从最后一个“d”开始 往回截取2个字符
17、select substr('HelloWorld',-3) value from dual; //返回结果：rld，从最后一个“d”开始 往回截取3个字符
（注：当只有两个参数时；不管是负几，都是从最后一个开始 往回截取(如：15、16、17)）
5.length(字段名) 取字段长度
select id, length(ENAME) as ENAME from Test6
6.lpad (字符定长,不够长度时用指定的字符从左边填充)
select id, lpad(ENAME,10,'&') as ENAME from Test6
7.rpad(字段订长,不够长度是用指定的字符从右边开始填充)和lpad相反
select id, rpad(ENAME,10,'&') as ENAME from Test6
8.replace(字段名,待查找的子串,用于替换查找到的子串) 从字段名中检索需要替换的子串,并使用用于替换查找到的子串进行替换
select id, replace(ENAME,'s','66') as ENAME from Test6
数值函数
1、round(字段名,要保留的小数位数) 四舍五入,并保留指定位数的小数
select id,name,
nvl(age,30)/7 as result from Test6
对上面的结果进行四舍五入操作,结果保留0位小数
select id,name,
round(nvl(age,30)/7,0) as result from Test6 //保留0位小数
2、trunc
(1)、截断数字
trunc(n1,n2) n1表示要截断的数字,n2表示要截断到哪一位,n2的位置根据小数点的位置来确定 注意:trunc的截取不进行四舍五入运算
i、不传n2,则n2默认为0
select trunc(66.6) from test6
ii、传入n2=1,表示截取到小数点的第一位.
select trunc(66.6,1) from test6
iii、传入n2为负数
select trunc(66.6,-1) from test6
//表示从小数点左侧开始截取,把个位数全部去掉,保留十位数。结果为60
2)trunc 用于截断日期
i、截取今天,去掉时分秒
select sysdate,trunc(sysdate,'dd') from dual
ii、截取本周第一天
select sysdate,trunc(sysdate,'d') from dual
iii、截取本月第一天
select sysdate,trunc(sysdate,'mm') from dual
vi、截取本年第一天
select sysdate,trunc(sysdate,'y') from dual
vii、截取到小时
select sysdate,trunc(sysdate,'hh') from dual
viii、截取到分钟
select sysdate,trunc(sysdate,'mi') from dual
vvi、获取上月第一天
select TRUNC(add_months(SYSDATE,1),'MM') from dual
(3)mod(被除数,除数) 取余
select mod(12,5) from dual
(4)ceil(x) 向上取整
select ceil(12/5) from dual
(5)floor(x) 向下取整
select floor(12/5) from dual
日期函数
2、months_between(x,y) 两个日期之间相差的月数
计算Test6表中的用户到目前位置,成为会员已经几个月了(注册即成为会员)
select months_between(sysdate,addtime)as diff_month from test6
3、add_months(日期,要加的月份) 返回在指定的日期后，加上n个月后的日期
select add_months(sysdate,3) from dual
4、last_day(日期) 返回指定日期所在月的最后一天
select last_day(sysdate) from dual
5、 extract 日期提取函数
i、从日期中提取年份
SELECT EXTRACT(YEAR FROM sysdate) FROM dual;
Month 月
DAY 日
Hour 时
MINUTE 分
second 秒
6、TO_CHAR 将日期转换成字符串
i、将日期转换成字符串
select TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
yyyy 获取当前时间的年
mm 获取月
dd 获取当月的第几日
ddd 获取当年的第几日
2、TO_DATA 将字符串转换成为日期
select to_date('2017-03-14 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual
nvl()函数
NVL(expr1,expr2)
如果expr1和expr2的数据类型一致，则：
如果expr1为空(null),那么显示expr2，
如果expr1的值不为空，则显示expr1。
nvl2()函数
NVL2(expr1,expr2, expr3)
如果expr1不为NULL，返回expr2； expr1为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型，转换不了，则报错。
NULLIF函数
NULLIF(expr1,expr2)
如果expr1和expr2相等则返回空(NULL)，否则返回expr1。