常用Oracle语句集合

博客新开,闲来无事,总结一下常用sql语句,做数据仓储的别喷.

-Oracle 基本管理语句

conn scott/scott;

show user; --查看用户

set linesize 300; --设置每行显示字数

set pagesize 100; --设置每页显示行数

set timing on;--还有set timing off 显示完成命令需要的时间

select sysdate from dual; --显示系统时间oracle时间的格式为 23-7月 -10

select * from tab; --显示所有的表

desc emp; --显示表的结构

disc;--退出窗口exit

 

 

 

-Oracle 基本sql 和函数

select distinct job from emp; --用于返回唯一不同的值"distinct"

select distinct job,deptno from emp; -- 有两列以上只需在第一个字段后跟","第二个字段就ok了

select * from emp where (sal>800 and sal<2000) and (job='CLERK' or deptno=20);

--"where","<",">","and","or"地球人都知道

select * from emp order by job ; --order by 语句用于根据指定的列对结果集进行排序

select * from emp order by job asc,sal desc; --默认的是升序"asc",如果要倒序用关键字"desc"

select * from emp order by sal,job; --上下两个不一样

select * from (select rownum rn,emp.*  from emp where rownum < 10) where rn>5

--oracle中的rownum时能用"<"来定义用子查询分页,sqlserver用top,mysql用limit

select * from emp where job like 'A%'; --like的用法,注意通配符'%,_'的区别和用法

select * from emp where sal in (3000,800,1500); --in的用法,多说一句in后的()中看以跟一个字段的子查询

select * from emp where sal between 800 and 2500; --between的用法,800-2500之间是带上800和2500的

select * from emp where sal not between 800 and 2500; --not between的用法;

select * from emp,dept where emp.deptno = dept.deptno; -- 多表连接 等价于 iner join;

select * from emp inner join dept on emp.deptno = dept.deptno; --inner join 如果表中有至少一个匹配，则返回行

select * from emp left join dept on emp.deptno = dept.deptno;  --left join: 即使右表中没有匹配，也从左表返回所有的行

select * from emp ,dept where emp.deptno = dept.deptno(+); --(+)是Oracle特有的符号,(+)定义在那个表中,另一张表为基表.

select * from emp right join dept on emp.deptno = dept.deptno; --right join: 即使左表中没有匹配，也从右表返回所有的行

select * from emp ,dept where emp.deptno(+) = dept.deptno; --不用多少和上面的效果一样.

select * from emp full join dept on emp.deptno = dept.deptno;  --full join: 只要其中一个表中存在匹配，就返回行

select * from emp union select * from emp; --union操作符用于合并两个或多个select语句的结果集

select * from emp union all select * from emp; -- union all 不合并重复项;

select empno,ename,(sal+nvl(comm,0))*13 年薪 from emp; --nvl()如果为空,就设为0,此函数每一个数据库不一样.

select avg(sal) ,min(sal) ,max(sal) ,sum(sal) ,count(*) from emp;--avg平均,min最小,max最大,sum求和,count统计

select avg(sal) ,min(sal) themin,max(sal) ,sum(sal), count(*) ct from emp group by job having count(*)>2;

--group by having 不能使用别名,因为sql从左向右执行,编译器并不知道ct是什么.

select length(job) from emp; --字符长度.

select upper(lower(job)) from emp; --lower转小写,upper转大写

select initcap(lower(job)) from emp; --initcap首字母大写

 

 

- 用户的管理(1)

conn sys/sys as sysdba; --连接数据库

create user xiaoming identified by xiaoming; --创建用户(需要权限)

passw xiaoming; --修改用户密码()   

grant connect to xiaoming;

--把"connect"的角色授予xiaoming(其他角色例如:dba(系统管理员),resource(可以在表空间建立表))

grant resource to xiaoming; --授权建立表的权利

grant resource to xiaoming with admin option;-- 授权权限的传递(系统权限加with admin option)

grant all on scott.emp to xiaoming ; --对表进行授权(有多种种权限select insert update delete all)

grant all on scott.emp to xiaoming with grant option; --授权对象的传递(对象权限加with grant option)

 

 

- 表数据管理

conn xiaoming; --连接到xiaoming

create table test (id varchar(30), name varchar(30)); --创建一个test表

insert into test values('1002','xiaoming'); --向表中插一个数据

create table emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) as (select * from scott.emp);

--拷贝一个表,或者就是create table emp as (select * from scott.emp);

insert into test (select empno,ename from emp); --向表中插入一个查询结果,没有values

insert into emp (empno,ename) values (1001,'xiaoming'); --增加一条数据

delete from emp where sal>2500; --删除满足某个条件的表中的数据

update emp set empno=1009, deptno=90 where empno =1001; --通用sql语句

update emp set (job,mgr,sal)=(select job,mgr,sal from emp where empno=7934) where empno=1009;      

--Oracle特有的语句

drop table test; --删除一个test表;

 

 

- 用户管理(2)

conn sys/sys as sysdba; --登录

create profile lock_one limit failed_login_attempts 3 password_lock_time 2; --创建一个profile "lock_one"

alter user xiaoming profile lock_one; --更改用户，将lock_on 概要文件加载到xiaoming上。

alter user xiaoming account unlock; --解锁用户

alter user xiaoming account lock; --锁定用户

drop profile lock_one cascade; --删除一个profile，如果profile已经被分配到用户上注意加上cascade

revoke resource from xiaoming; --回收系统权限,系统权限不会被回收.(对象权限级联,系统权限不级联)

revoke connect from xiaoming; --回收系统权限,系统权限不会被回收.(对象权限级联,系统权限不级联)

revoke all on emp from xiaoming;

--收回xiaoming对emp的对象权限(回收xiaoming的权利,xiaoming授权出去的对象权限都会被收回)  

drop user xiaoming cascade; --删除一个对象

exit; --关闭窗口