Oracle数据库操作

一、Oracle数据库操作

1、创建数据库

     create database databasename

2、删除数据库

     drop database dbname

3、备份数据库

· 完全备份

     exp demo/demo@orcl buffer=1024 file=d：\back.dmp full=y

     demo：用户名、密码

     buffer: 缓存大小

     file: 具体的备份文件地址

     full: 是否导出全部文件

     ignore: 忽略错误，如果表已经存在，则也是覆盖

· 将数据库中system用户与sys用户的表导出

     exp demo/demo@orcl file=d:\backup\1.dmp owner=(system,sys)

· 导出指定的表

     exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

· 按过滤条件，导出

     exp demo/demo@orcl file=d：\back.dmp tables=（table1） query=\" where filed1 like 'fg%'\"

     导出时可以进行压缩；命令后面 加上 compress=y ；如果需要日志，后面： log=d:\log.txt

· 备份远程服务器的数据库

     exp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

4、数据库还原

     打开cmd直接执行如下命令，不用再登陆sqlplus。

· 完整还原

     imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt

     指定log很重要，便于分析错误进行补救。

· 导入指定表

     imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

· 还原到远程服务器

     imp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

二、Oracle表操作

1、创建表

     create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

     根据已有的表创建新表：

     A：select * into table_new from table_old (使用旧表创建新表)

     B：create table tab_new as select col1,col2… from tab_old definition only<仅适用于Oracle>

2、删除表

     drop table tabname

3、重命名表

     说明：alter table 表名 rename to 新表名

        eg：alter table tablename rename to newtablename

4、增加字段

     说明：alter table 表名 add (字段名 字段类型 默认值 是否为空);

        例：alter table tablename add (ID int);

       eg：alter table tablename add (ID varchar2(30) default '空' not null);

5、修改字段

     说明：alter table 表名 modify (字段名 字段类型 默认值 是否为空);

        eg：alter table tablename modify (ID number(4));

6、重名字段

     说明：alter table 表名 rename column 列名 to 新列名 （其中：column是关键字）

        eg：alter table tablename rename column ID to newID;

7、删除字段

     说明：alter table 表名 drop column 字段名;

        eg：alter table tablename drop column ID;

8、添加主键

     alter table tabname add primary key(col)

9、删除主键

     alter table tabname drop primary key(col)

10、创建索引

     create [unique] index idxname on tabname(col….)

11、删除索引

     drop index idxname

     注：索引是不可更改的，想更改必须删除重新建。

12、创建视图

     create view viewname as select statement

13、删除视图

     drop view viewname

三、Oracle操作数据

1、数据查询

     select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

2、插入数据

     insert into 表名 values(所有列的值);

     insert into test values(1,'zhangsan',20);

     insert into 表名(列) values(对应的值);

     insert into test(id,name) values(2,'lisi');

3、更新数据

     update 表 set 列=新的值 [where 条件] -->更新满足条件的记录

     update test set name='zhangsan2' where name='zhangsan'

     update 表 set 列=新的值 -->更新所有的数据

     update test set age =20;

4、删除数据

· delete from 表名 where 条件 -->删除满足条件的记录

     delete from test where id = 1;

     delete from test -->删除所有

     commit; -->提交数据

     rollback; -->回滚数据

     delete方式可以恢复删除的数据，但是提交了，就没办法了 delete删除的时候，会记录日志 -->删除会很慢很慢

· truncate table 表名

     删除所有数据，不会影响表结构，不会记录日志，数据不能恢复 -->删除很快

· drop table 表名

     删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复-->删除很快

5、数据复制

· 表数据复制

     insert into table1 (select * from table2);

· 复制表结构

     create table table1 select * from table2 where 1>1;

· 复制表结构和数据

     create table table1 select * from table2;

· 复制指定字段

     create table table1 as select id, name from table2 where 1>1;

 

 

 

 

 

 

 

 

oracle数据库的基本操作

 

管理员登录：sqlplus  sys /密码  as sysdba

 

sqlplus  /as  sysdba

 

 

创建用户名并设置密码：create  user laolu identified by laolu;

 

给该用户授权：grant coonect,resource,dba to laolu;

 

coonect:是链接数据库权限，可以对数据库进行增删改查

 

resource：资源使用权限，用来创建表格

 

dba/system:是数据库管理员的权限

 

修改用户密码：alert user laolu identified by laolu123;

 

 

回收权限：revoke connect,resource from laolu;

 

revoke :回收

 

connect，resource ：创建以及查询的权限

 

from ：从哪里回收

 

 

备份数据库

 

 

完全备份：exp demo/demo@orcl buffer=1024 file =d:\back.dmp full=y

 

demo:用户名

buffer：缓存大小

file：具体的备份文件地址

full：是否导出全部文件

ignore：忽略错误，如果表已经存在，则也是覆盖

 

将数据库中system用户与sys用户的表导出

     exp demo/demo@orcl file=d:\backup\1.dmp owner=(system,sys)

 

导出指定的表

     exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

 

按过滤条件，导出

     exp demo/demo@orcl file=d：\back.dmp tables=（table1） query=\" where filed1 like 'fg%'\"

 

     导出时可以进行压缩；命令后面 加上 compress=y ；如果需要日志，后面： log=d:\log.txt

 

备份远程服务器的数据库

     exp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

 

4、数据库还原

 

     打开cmd直接执行如下命令，不用再登陆sqlplus。

 

完整还原

     imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt

 

     指定log很重要，便于分析错误进行补救。

 

导入指定表

     imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

 

还原到远程服务器

     imp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

 

 

 

 

 

给用户加锁、解锁

 

alter user laolu account lock;

 

alter user laolu account unlock;

 

alter user 是修改的关键字

 

account 是加锁、解锁的关键字

 

lock 给用户加锁

 

unlock 给用户解锁

 

删除用户  :   drop user laolu

 

 

基本的select 语句

 

select * from tablename

 

select {distinct} column1,column2 from tablename;

 

distinct :是去掉重复的关键字

 

select colum1 col1 ,column2 col2 from tablename ;

 

col1/col2 :是别名

 

describe tablename:显示表结构

 

where :过滤和排序

 

 

select * from city where id=1:查询ID为1的城市

 

between  and  :在俩值之间

 

in/set :等于俩值中的一个

 

like: 模糊查询

 

select country from city where country like ‘国%’：查询含有国的国家

 

select cname ,country from city where country is null: 查询国家为空的时候的cname

 

order by asc (升序)/desc(降序)

 

select ID，dname，ranking from country order by ranking:根据排名查询id/dname/ranking

 

大小写控制函数：lower/upper

 

select id ,name ,ranking ,code from country where upper(code)='zhongguo':拼音大写

 

字符控制函数：

 

concat('hello','world') :helloworld

 

substr('helloworld',1,5)" hello

 

length ('helloworld') 10

 

instr ('helloworld') 6

 

ipad (id,5,'*') ****1

 

rpad (id,5,'*') 1****

 

trim ('h'from 'helloworld') elloword

 

replace ('abcd','b','m') amcd

 

数字函数：

 

round 四舍五入

 

trunc 截断

 

mod 求余

 

日期函数：

 

months_between  俩个日期相差的月数

 

add_months  向指定日期加上若干月数

 

next_day 指定日期的下一个日期

 

last_day 本月的最后一天

 

round 日期的四舍五入

 

trunc 日期截断

 

 

转换函数： to_date/to_char/to_number

 

 

group by :分组

 

select id dname,avg(money) from country group by money ;

 

select id ,dname,sum(money) from country group by dname,id;