ORCALE语句大全

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 www.2cto.com 截取屏幕上的内容 spool D:\1.sql(开始截取) spool off(停止截取) 4 用户管理 创建用户 create user zhu identified by zhu 修改密码 alter user zhu identified by orcl 删除用户 drop user zhu(cascade) cascade代表删除这个用户对应的所有对象 赋予权限 grant create session to zhu grant all on emp to zhu 权限传递 grant all on emp to zhu with grant option(对象权限) 根表有关的权限 grant create session to zhu with admin option(系统权限)其他的权限 收回权限 revoke all on emp to zhu(株连制度) 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,'朱文锋','男','01-5月-12',2000); 修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表） alter session set nls_date_format='yyyy-mm-dd'; insert into student(SNo,Name,Sex,Birthday,Salary) values(1002,'朱文锋','男','2010-12-12',2000); 插入部分字段和空值 快速加大表中数据 insert into student(SNo,Name,Sex,Birthday,Salary) select * from student; www.2cto.com 改 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 www.2cto.com 导出其它方案 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 表空间 www.2cto.com 创建表空间 create tablespace zhu datafile 'C:\oracle\product\10.2.0\oradata\zhu.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local; create user zhu identified by zhu default tablespace zhu; create table student(SNo number(4),Name nvarchar2(50),Sex char(2),Birthday date,Salary number(7,2) default 1000 not null) tablespace zhu; 知道表空间名，显示该表空间包括的所有表 select * from all_tables where tablespace_name='zhu'; 知道表名，查看该表属于那个表空间 select tablespace_name, table_name from user_tables where table_name='emp'; 删除表空间 drop tablespace zhu 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; www.2cto.com 创建一个触发器 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('朱文锋','男','01-5月-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(),'朱文锋','男','01-5月-12',2000); 在sql server中是newid()函数 ### Oracle语句大全知识点梳理 #### 一、进入与退出SQL Plus界面 - **进入界面**: 在CMD中输入`sqlplus scott/orcl@orcl`来启动Oracle的SQL Plus界面。 - **连接命令**: 使用`conn[ect] sys/orcl@orcl as sysdba`进行连接。 - **断开连接**: 输入`disc[onnect]`命令来断开当前的连接。 - **修改密码**: 使用`psssw[ord]`命令来更改密码。 - **显示用户**: 输入`show user`来显示当前登录的用户名。 - **退出界面**: 使用`exit`命令退出SQL Plus界面。 #### 二、执行与编辑SQL语句 - **执行SQL语句**: 可以通过`start D:\1.sql`或`@ D:\1.sql`来执行存储在指定路径下的SQL脚本。 - **编辑SQL语句**: 使用`edit D:\1.sql`命令来编辑存储在指定路径下的SQL脚本。 - **截取屏幕内容**: 使用`spool D:\1.sql`开始截取屏幕内容到指定文件，使用`spool off`来停止截取。 #### 三、用户管理 - **创建用户**: 使用`create user zhu identified by zhu`命令来创建新用户。 - **修改密码**: 使用`alter user zhu identified by orcl`来修改用户的密码。 - **删除用户**: 使用`drop user zhu cascade`来删除用户及其所有相关对象。 - **赋予权限**: 例如，`grant create session to zhu`赋予用户创建会话的权限。 - **权限传递**: 使用`grant all on emp to zhu with grant option`将所有权限授予用户，并允许其进一步分配这些权限。 - **收回权限**: 使用`revoke all on emp to zhu`来撤销已授予用户的权限。 #### 四、用户口令管理 - 文档中提到“用户口令管理profile”，但未给出具体实现方式。在Oracle中，可以通过设置profile来管理用户的资源限制和口令策略。 #### 五、表操作 - **创建表**: 示例`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))` - 修改字段长度: `alter table student modify(Name 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`删除表中的所有记录，保留表结构。 - **查看表字段结构**: 使用`desc student`查看表的字段结构。 #### 六、增删改查 - **增加记录**: 示例`insert into student(SNo,Name,Sex,Birthday,Salary) values(1002,'朱文锋','男','01-5月-12',2000)`用于向学生表中添加一条记录。 - **修改记录**: 使用`update student set Name='陈慧琳',Sex='女' where SNo=1002`来修改符合条件的记录。 - **删除记录**: 示例`savepoint a; delete from student where SNo=1003; rollback to a`用于删除符合条件的记录，并通过回滚点进行恢复操作。 - **查询记录**: 使用`select * from emp`来查询表中的所有记录。 - **取消重复行**: 使用`select distinct * from emp`来去除查询结果中的重复行。 - **空值计算**: 使用`select sal*13 + nvl(comm,0)*13 as 年薪 from emp`来计算员工的年薪，其中`nvl(comm,0)`用于处理空值情况。 #### 七、数据备份和恢复 - **备份**: - 备份整个数据库: `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=scott/orcl@orcl tables=emp file=d:\emp.dmp 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 tables=emp file=d:\emp.dmp fromuser=system touser=scott log=d:\empimp.log` #### 八、表空间管理 - **创建表空间**: 示例`create tablespace zhu datafile 'C:\oracle\product\10.2.0\oradata\zhu.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local`用于创建名为zhu的表空间。 - **创建用户并指定默认表空间**: `create user zhu identified by zhu default tablespace zhu`。 - **创建表并指定表空间**: `create table student(SNo number(4),Name nvarchar2(50),Sex char(2),Birthday date,Salary number(7,2) default 1000 not null) tablespace zhu`。 - **查询表空间**: - 查询表空间下的所有表: `select * from all_tables where tablespace_name='zhu'` - 查询表所属的表空间: `select tablespace_name, table_name from user_tables where table_name='emp'` - **删除表空间**: `drop tablespace zhu including contents and datafiles cascade constraints`。 #### 九、约束 - Oracle支持多种类型的约束: `not null`, `unique`, `primary key`, `foreign key`, `check`。 - **添加主键约束**: 示例`alter table class add constraint class_key primary key (classid)`。 #### 十、自动增长与GUID - **自增长序列**: - 创建序列: `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('朱文锋','男','01-5月-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(),'朱文锋','男','01-5月-12',2000)` 以上是对提供的文档内容中关键知识点的梳理和详细解释。这些知识点覆盖了Oracle数据库的基本操作、数据管理、备份恢复以及安全性等方面，对于学习和使用Oracle数据库的人来说非常实用。