基础的orcle操作命令 -金沙1005

                                         基础的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

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;