Oracle基本语句实例代码介绍

Oracle基本语句

1 进入界面

在cmd里面进入oracle的sqlplus界面：sqlplus scott/orcl@orcl

2 连接管理

连接命令 conn[ect] sys/orcl@orcl as sysdba

断开连接 disc[onnect]

修改密码 psssw[ord]

显示用户 show user

退出界面 exit

3 执行编辑sql语句

执行sql语句 start D:1.sql 或者 @ D:1.sql

编辑sql语句 edit D:1.sql

截取屏幕上的内容 spool D:1.sql(开始截取) spool off(停止截取)

4 用户管理

创建用户 create user ssy identified by ssy

修改密码 alter user ssy identified by orcl

删除用户 drop user ssy(cascade) cascade代表删除这个用户对应的所有对象

赋予权限 grant create session to ssy

         grant all on emp to ssy

权限传递 grant all on emp to ssy with grant option(对象权限) 根表有关的权限

         grant create session to ssy with admin option(系统权限)其他的权限

收回权限 revoke all on emp to ssy(株连制度)

5 用户口令管理profile

6 表操作

创建表 create table student(SNo number(4),Name nvarchar2(50),Sex char(2),Birthday date,Salary number(7,2) default 1000 not null)

修改表 添加一个字段 alter table student add(Address nvarchar2(100) [default value][null/not null]);

       修改一个字段的长度 alter table student modify(Name nvarchar2(10),Address nvarchar2(10));

       修改一个字段的类型 alter table student modify(Name varchar2(10));

       修改一个字段的名称 alter table student rename column Name to Name2;

       删除一个字段 alter table student drop column Salary;

修改表的名字 rename student to stu;

删除表 drop table student;

       delete from student;删除所有记录，表结构还在，写日志，可以恢复的，速度慢，Delete 的数据可以恢复。

查看表字段结构 desc student;

7 增删改查

增 insert into student(SNo,Name,Sex,Birthday,Salary) values(1002,'史守阳','男','21-8月-12',2000);

   修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）

   alter session set nls_date_format='yyyy-mm-dd';

   insert into student(SNo,Name,Sex,Birthday,Salary) values(1002,'史守阳','男','2012-08-21',2000);

   插入部分字段和空值

   快速加大表中数据 insert into student(SNo,Name,Sex,Birthday,Salary) select * from student;

改 update student set Name='陈慧琳',Sex='女' where SNo=1002;

删 savepoint a;

   delete from student where SNo=1003;

   rollback to a;

总结：删除表的三种方式 delete from student;     删除所有记录，表结构还在，写日志，可以恢复的，速度慢。

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

                       drop table student;      删除表的结构和数据。

查(查询相对较复杂，完善中。。。)

开启计时 set timing on;

取消重复行 select distinct * from emp;

空值计算   select  sal*13+nvl(comm,0)*13 as 年薪 from emp;

子查询(嵌套查询)

单行子查询

多行子查询

多列子查询

分页查询

合并查询

8 数据备份和恢复

备份(多表多文件加上大括号)

导出整个数据库 exp userid=system/orcl@orcl file=d:all.dmp full=y log=d:all.log

导出自己的方案 exp userid=scott/orcl@orcl owner=scott file=d:scott.dmp log=d:scott.log

导出其它方案   exp userid=system/orcl@orcl owner=scott file=d:scott2.dmp log=d:scott2.log

导出自己的表   exp userid=scott/orcl@orcl tables=emp file=d:emp.dmp log=d:emp.log

导出其它方案的表 exp userid=system/orcl@orcl tables=scott.emp file=d:emp.dmp log=d:emp.log

导出表的结构   exp userid=scott/orcl@orcl tables=emp file=d:emp.dmp rows=n log=d:emp.log

使用直接导出方式 exp userid=scott/orcl@orcl tables=emp file=d:emp.dmp direct=y log=d:emp.log     

这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。

这时需要数据库的字符集要与客户端字符集完全一致，否则会报错

恢复(多表多文件加上大括号)

导入整个数据库 imp userid=system/orcl@orcl file=d:all.dmp full=y log=d:allimp.log ignore=y

导入自己的方案 imp userid=scott/orcl@orcl file=d:emp.dmp log=d:empimp.log

导入其它方案   imp userid=system/orcl@orcl file=d:emp.dmp fromuser=system touser=scott log=d:empimp.log

导入自己的表   imp userid=scott/orcl@orcl tables=emp file=d:emp.dmp

导入表到其它用户 imp userid=system/orcl@orcl tables=emp file=d:emp.dmp fromuser=system touser=scott log=d:empimp.log

导入表的结构   imp userid=scott/orcl@orcl tables=emp file=d:emp.dmp rows=n log=d:empimp.log

导入数据 如果对象（如比表）已经存在可以只导入表的数据

imp userid=scott/orcl@orcl tables=emp file=d:emp.dmp ignore=y log=d:empimp.log

注意formuser是表本来属于哪个用户 touser现在传递给哪个用户

9 表空间

创建表空间 create tablespace ssy datafile 'C:oracleproduct10.2.0oradatassy.dbf'

size 50m autoextend on next 50m maxsize unlimited extent management local;

create user ssy identified by ssy default tablespace ssy;

create table student(SNo number(4),Name nvarchar2(50),Sex char(2),Birthday date,Salary number(7,2) default 1000 not null) tablespace ssy;

知道表空间名，显示该表空间包括的所有表

select * from all_tables where tablespace_name='ssy';

知道表名，查看该表属于那个表空间

select tablespace_name, table_name from user_tables where table_name='emp';

删除表空间 drop tablespace ssy including contents and datafiles cascade constraints;

10 约束

not null unique primary key foreign key check

alter table class add constraint class_key primary key (classid);

11 主键

自动增长

先创建一个表

create table student(SNo number(4) primary key,Name nvarchar2(50),Sex char(2),Birthday date,Salary number(7,2) default 1000 not null)

自定义一个sequence

create sequence student_sequence increment by 1 start with 1 nomaxvalue nocycle nocache;

创建一个触发器

create trigger student_trigger before insert on student for each row when(new.SNo is null) begin select student_sequence.nextval into:new.SNo from dual;end;/

最近插入一行数据

insert into student(Name,Sex,Birthday,Salary) values('史守阳','男','21-8月-12',2000);

GUID

先创建一个表

create table student2(SNo char(32) primary key,Name nvarchar2(50),Sex char(2),Birthday date,Salary number(7,2) default 1000 not null)

然后插入一行数据

insert into student2(SNo,Name,Sex,Birthday,Salary) values(sys_guid(),'史守阳','男','21-8月-12',2000);