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