基础的orcle操作命令

                                         基础的orcle操作命令

 

 orcle基础操作：

 

1. 建用户名和密码

SQL>create user username identified by password;

2.给用户授权 ：

创建的新用户是没有任何权限的，甚至连登录的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令 revoke。 

SQL> grant connect,resource to username;

SQL>grant connect,resource,dba to username;

SQL>grant connect,resource,dba,sysdba to username;

3.进入连接：（需要权限：）

SQL>connect username/password@orcl;

4.退出当前用户连接；

SQL>dise;

5.显示当前连接用户

　　SQL> show user

6.查看系统拥有哪些用户

SQL> select * from all_users;

7.查看所有的表

select table_name from all_tables;

8.查询用户下的所有表 (仅显示一列表名)

select  table_name from user_tab_columns;//(仅显示一列表名)

select  table_name from user_table;//.查询用户下的所有表

9. 查询当前用户下的所有表及视图

select * from tab;

10. 查看数据库有多少个tablespace

select * from dba_tablespaces;

11. 查看哪些用户拥有SYSDBA、SYSOPER权限

　　SQL>conn sys/change_on_install –登不上去

SQL>select * from V_$PWFILE_USERS;

12. 查看用户拥有的权限?

SELECT * FROM dba_sys_privs ;

　

oracle表的操作

　1、创建一个表 （field：指定列名　datatype：指定列类型）

　CREATE TABLE table_name

(

    field1  datatype,

    field2  datatype,

    field3  datatype,

)

2、查询表结构

　　SQL> desc 表名;

3.插入新记录

　　SQL> insert into 表名 values(1);

4.查询

　　SQL> select * from表名;

5.更改

　　SQL> update a set a=2;

6.删除

　　delete from 表名;  //删除所有记录,表结构还在,写日志,可以恢复的,速度慢

 drop table 表名;  //删除表的结构和数据

 delete from student where xh='A001';//  删除一条记录

 truncate table 表名; //删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

7.回滚

　　SQL> roll;

　　SQL> rollback;

8.提交

　　SQL> commit;

9. 修改表名

alter table old_table_name rename to new_table_name;

10．添加表列

ALTER TABLE tablename

ADD          (列名  列类型（大小）);

11.修改列属性

ALTER TABLE table

MODIFY    (列名  列类型（大小）);

12.删除列

ALTER TABLE table

DROP   column (column);

13.修改列名(即修改字段名)

alter table 表名 rename column 现列名 to 新列名;

14.查询表中的表名，字段名等等。

select table_name,column_name from user_tab_columns where

table_name='TABLE_NAME';

15.查询出前N条信息

select * from tablename where rownum<n；

16.快速清空一个大表

　　SQL>truncate table table_name;

17.快速做一个和原表一样的备份表

　　create table new_table as (select * from old_table);

18.修改一张表（test）的主键

　　alter table test drop constraint test _key ;

　　alter table test add constraint test _key primary key(a1,b1) ;

19.查询数据库中某个表（test）所在的tablespace

　　select tablespace_name from user_tables where table_name='TEST';

20.如何查找重复记录？

　　SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAMe WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

21.如何删除重复记录？

DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

22. 如何知道表在表空间中的存储情况

select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name; --把&tablespace_name改成相应的表空间名称。

23.复制表结构和表数据
a. 复制表结构及其数据：

create table new_table_name as select * from old_table_name

b. 只复制表结构：

create table new_table_name as select * from old_table_name where 1=2;

或者：

create table new_table_name like old_table_name

c. 只复制表数据：

如果两个表结构一样：

insert into new_table_name select * from old_table_name

如果两个表结构不一样：

insert into new_table_name (column1,column2...) select

column1,column2... from old_table_name

创建带主键的表：

create table stuInfo(stuID int primary key,stuName

 varchar2(20),age int);

或是不直接增加主键

alter table stuInfo add constraint stuInfo _PK primary key

stuID)

 

orcal其它操作：

 

1. 如何测试SQL语句执行所用的时间?

　　SQL>set timing on ;

　　SQL>select * from tablename;

2. 字符串的连接

　　SELECT CONCAT(COL1,COL2) FROM TABLE ;

　　SELECT COL1||COL2 FROM TABLE ;

3. 把select出来的结果导到一个文本文件中

　　SQL>SPOOL C:\ABCD.TXT;

　　SQL>select * from table;

　　SQL >spool off;

4. 如何执行脚本SQL文件?

SQL>@PATH/filename.sql;

5. 将select语句查询结果自动生成序号

　　select rownum COL from table;

6. 如何在字符串里加回车?

　　select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ; --‘||chr(10)||’作为换行符

7. 查有数据库实例个数

　　SQL>SELECT * FROM V$INSTANCE;

8.创建uuid

select sys_guid() from dual;　

oracle数值运算和日期运算

 

1. 如何查询某天的数据?

select * from a where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-

dd');

若是date型数据

insert into bsyear values(to_date('20130427','yyyymmdd'));

或者是insert into bsyear values('27-4月-2013');

2. sql 语句如何插入全年日期？

　　create table BSYEAR (d date);

insert into BSYEAR select to_date('20030101','yyyymmdd')+rownum-1
from all_objects where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd'); --在表后直接插入365行数据日期。

3. 如何统计两个表的记录总数?

　　select (select count(id) from aa)+(select count(id) from bb) 总数 from dual; --总数那是没有单引号的，双引号可以。

4. 如何在给现有的日期加上2年？

select add_months(sysdate,24) from dual; -- 2015/4/27 9:28:52

5. 返回当前月的最后一天?

SELECT LAST_DAY(SYSDATE) FROM DUAL;

数值操作

1. 返回大于等于N的最小整数值?

　　SELECT CEIL(N) FROM DUAL;

2. 返回小于等于N的最小整数值?

SELECT FLOOR(N) FROM DUAL;

3. 两个结果集互加的函数?

　　SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;

　　SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;

　　SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;

4. 两个结果集互减的函数?

　　SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;