oracle常用sql语句

一、前言   

        简单的列举一些小k平时工作使用比较频繁的一些sql语句，各位看客朋友如果觉得太过浅显请勿见笑，如觉得有可借鉴之处，还请动动手指点一下赞哟。本篇后续会持续更新的。。。

二、常用语句

1、查看当前用户的缺省表空间 ： select username,default_tablespace from user_users;

2、查看用户下所有的表 ： select * from user_tables;

3、创建表空间 ：CREATE TABLESPACE invocie DATAFILE '/data/InvoiceData/invoicebak/invocie_01.dbf' Size 4096M AUTOEXTEND OFF；

4、创建用户并赋权限 ： CREATE USER invocie_dev identified by invocie_dev default tablespace invocie; grant connect, resource to invocie_dev;

5、解锁用户：alter user scott account unlock;

6．重设用户密码：scott/tiger为默认用户，alter user scott identified by tiger;

7、创建表：create table t1(c1 type 约束,c2 type 约束（not null,unique,check,primary key））;

8. 查询：select distinct c1 from t1 where 条件 group by c1 having by 子条件order by c1; 

9. 连接字符串：select c1 ||c2 from t1;

10.查看当前系统时间 ：select sysdate from dual;

11.更新数据操作：插入记录：insert into t1(c1,c2)values(‘’,’’)； 

插入一字段:insert into t1(c1,c2) select c3,c4 from t2; 

更新记录：update t1  set c1=’’ where  

删除记录：delete from t1 where;truncate table t1;drop table t1;

12.创建外键: create table t1(dept_no varchar2(4) not null, constraint fk_emp foreign key (dept_no) references t2(dept_no);

 对已经存在表创建外键: alter table t1 add constraint foreign_work_emp foreign key(c1) references t2(c1); 

   删除一个外键: alter table t1  drop constraint foreign_work_emp;

增加一个字段： alter table t1 add c1 varchar2(10);

13.多表查询:select * from t1,t2;(笛卡尔集c1行*c2行)

14.嵌套查询select c1 from t1 where c2 in(select c2 from t2 where c3=(select c3 from t3));

15. 将小写字母变为大写字母 : select upper('hello') from dual;

将大写字母变为小写字母 : select lower('HELLO WORLD') from dual;

将第一个字母大写 : select initcap('hello world') from dual;

1 6. 将一个字符串转换成日期类型 : select to_date('2009-01-01', 'yyyy-mm-dd') from dual;

17.联合查询 : select e.empno, e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;

18.查看当前有哪些用户连接到数据库 : select * from v$session where username='FMIS9999'(select serial#, sid from v$session; 杀掉：alter system kill session 'serial#, sid ';)

19. select count(*) from v$process --当前的连接数

select value from v$parameter where name = 'processes' --数据库允许的最大连接数

修改最大连接数:

alter system set processes = 300 scope = spfile;

重启数据库:

shutdown immediate;

startup;

--查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine

from v$session a, v$sqlarea b

where a.sql_address =b.address order by cpu_time/executions desc;

20.修改表空间名称

1、 使用oracle用户登录执行

    $sqlplus / as sysdba

2、 执行修改表空间命令如下

    SQL> alter tablespace  TEST rename to TEST1;

21、备份表数据

create table xtyhxx_bak1013 as select * from xtyhxx;

22.修改oracle中内存占用大小

show parameter sga; --显示内存分配情况

alter system set sga_max_size=200m scope=spfile; --修改占用内存的大小

23.oracle怎么把一列数据插入到另一列

update [表名] set [另外一列]=[前一列]

24.修改字段名称

alter table [表名] rename column [oldCName] to [newCName];

添加字段的语法：alter table tablename add (column datatype [default value][null/not null],….);

修改字段的语法：alter table tablename modify (column datatype [default value][null/not null],….);

删除字段的语法：alter table tablename drop (column);

25、导入导出指定表数据

1、从源数据库导出：

exp user1/ pwd @server1 file =c:\temp\ exp .dmp tables=(table1, table2)

2、导入到目标数据库：

imp user2/ pwd @server2 file =c:\temp\ exp .dmp tables=(table1, table2)

3、imp,加上fromuser,touser,ignore=Y选项

imp \'FMIS9999/1234.abcd@10.121.15.3/invoiceone\' file=/home/oracle/dump/PDF_QUEUE_20180505.DMP ignore=Y FULL=Y

26、查询表空间是否自动扩展

select file_name,autoextensible,increment_by from dba_data_files;