oracle常用命令

1.查询系统时间

select sysdate from dual;

select to_char(sysdate+1/24,'yyyy-mm-dd hh24:mi:ss' ) from dual;

2.启动服务

1.启动监听的命令：lsnrctl start

2.启动实例：oradim -starup -sid orcl

3.创建表空间，指定存储文件位置和大小

create tablespace tablespacename datafile 'D:\app\tt\oradata\orcl\test_tablespace.dbf' size 20M

4.创建用户时指定默认表空间

create user username identified by passwd default tablespace tablespacename;

5.创建用户及授权

1.create user username identified by password

2.grant create session to username

3.grant create table to username

4.grant unlimited tablespace to username

6.为用户上锁

alter user username account lock;

7.为用户解锁

alter user username account unlock;

8.撤销权限

1.revoke create table from username

9.查询当前用户拥有哪些系统权限

select * from user_sys_privs

10.查询用户属于什么角色

select * from user_role_privs;

11.查询数据库用户名，默认表空间，临时表空间

select username,default_tablespace,temporary_tablespace from user_users;

12.更改表的默认表空间

alter database default tablespace tablespacename;

13.查询当前用户的表及所属表空间

select table_name ，tablespace_name from user_tables;

14.查询表结构

describe tablename;

15.修改表中某一列名

alter table tablename rename column oldname to newname;

alter table tablename add (columnname datatype);

alter table tablename modify columnname datatype;

alter table tablename drop column columnname;

16.为表添加注释及查看注释方法

comment on table tablename is '#####';

select * from user_tab_comments where comments is not null;

17.为表中某列添加注释及查看方法

comment on column tablename.column is '###';

select * from user_col_comments where comments is not null;

18.删除表及恢复方法

drop table tablename[cascade constraints][purge];

Cascade constraints用于指定级联删除从表的外部键约束，可恢复

Purge用于指定彻底删除表，不可恢复

flashback table tablename to before drop;

19.删除表中所有行,删除后不能恢复

truncate table tablename;

20.删除单行使用delete，删除后可以恢复

delete tablename;

delete tablename where condition;

rollback;

21.一次插入多行

insert into tablename01

select * from tablename02;

22.更新数据库中某列数据或某行某列数据

update tablename set columnname='#####';

update tablename set columnname='' where condition;

23.新建表并将某表中数据或部分数据拷贝

create table tablename

as select * from oldtablename;

24.约束条件

create table tb_stu(

stu_no number primary key,

stu_name varchar2(10) not null,

stu_sex char(1) not null,check(stu_sex='F' or stu_sex='M'),

stu_age number check(stu_age>0 and stu_age<100),

stu_phone number(11) unique,

stu_addr varchar2(40) default 'aa',

stu_class number,

foreign key(stu_class) references tb_class(class_id),

--constrains tb_stu_pk primary key(stu_no,stu_name),

--constrains addr_nk stu_addr not null,

--constrains tb_stu_uk unique(stu_no,stu_name)

);

create table tb_class(

class_id number primary key,

class_name varchar2(20)

);

25.查询年薪

select ename,sal*12  nianxin from emp;

26.字符串拼接

select ename||job  from emp;

27.插入日期

insert into tablename values(to_date('1991-09-11','yyyy-mm-dd'));

28.查询员工的工龄

select empno,ename,

to_char(floor(to_number((sysdate-hiredate)/365)))||' years '||to_char(ceil(months_between(sysdate,hiredate)-(floor(to_number((sysdate-hiredate)/365)))*12))||' months '

from emp

29.对重复数据进行去重

select distinct columnname from tablename;

30.between and的使用

select ename,sal from emp where empno between 7000 and 7500;

31.in的使用

select ename from emp where job in('CLERK','SALES');

32.like的用法

(%)可匹配零或多个字符

(_)可匹配一个字符

33.IS NULL的用法

查询包含空值的记录

34.升序和降序排列(默认升序)

select empno,ename from emp order by empno desc

35.连接

natural join

left join select * from emp,dept  where emp.dptno=dept.deptn (+)

right join

36.查询平均值

select empno,ename from emp where sal>

(select avg(sal) from emp);

37.计数

select count(comm) from emp;

38.求和

sum()

39.

select avg(nvl(comm,0)) from emp; nvl表示是否为空

max()

min()

40.分组

select count(*),deptno from emp group by deptno order by deptno;

41.having的使用

select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

42.

where是在分组前进行条件过滤的

Having子句中是在分组后进行条件过滤的

where子句中不能使用聚合函数

Having子句可以使用聚合函数

43.

UNION运算符从俩个查询中返回消除重复之后的结果去重（并集）

select deptno from dept

union 

select deptno from emp

union all未去重

44.

INTERSECT(交集)

第一结果集减去第二结果集使用MINUS

rownum可以用来分页

获取从2到3俩条记录

select * from (select rownum r,deptno,dname from dept where rownum<4) re where re.r>1 and re.r<4;

45.

去重

delete from

select name ,age,count(*) from tb_test 

group by name,age

having count(*)>1;

create table tb_tmp as select distinct name ,age from tb_test;

truncate table tb_test;

insert into tb_test select * from tb_tmp;

delete from tb_test where rowid in

(select a.rowid as id

from tb_test a,tb_test b

where a.rowid > b.rowid

and a.name=b.name and a.age=b.age

);

delete from tb_test a where rowid not in 

(select max(rowid) from tb_test b where a.name=b.name  and a.age=b.age);

46.

序列sequence（定义主键，实现自增长）

nextval

currval

47.查询员工信息按部门号升序，月薪降序排列

select * from emp order by deptno asc , sal desc;

48.group by  having  order by 顺序不能变

49.如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal>(select max(sal) from emp group by deptno having deptno=30);

select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);

select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);

50.查询与SMITH部门与工作都相同的职员的姓名

select ename from emp where deptno=() and job=();

select ename from emp where (deptno,job)=(select deptno,job....);

51.查询比自己部门平均工资高的员工的信息

select  a1.ename,a1.deptno, a1.sal from emp a1,(select deptno,avg(sal)  mysal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>mysal ;

52.分页

1.rownum

2.SELECT /*+ FIRST_ROWS */ * FROM 

(

SELECT A.*, ROWNUM RN 

FROM (SELECT * FROM TABLE_NAME) A 

WHERE ROWNUM <= 40

)

WHERE RN >= 21