Oracle必备学习资料.docxVIP

--创建表空间Create tablespace newer datafile d:\tablespace\newer.ora size 50mautoextend on;--创建临时表空间Create TEMPORARY tablespace temp1 tempfile d:\tablespace\temp1.ORA size 10mautoextend on ;--创建用户create user newer identified by newer;--为用户指定表空间与临时表空间Alter user newer default tablespace newer ;Alter user newer temporary tablespace temp1;--为用户授权Grant connect,resource,create session, dba, create table, create view, create trigger, select any table, create sequence, create procedure, create role, grant any privilege, drop any role, create public synonym, drop public synonym,SELECT ANY DICTIONARY to newer;--ORACLE数据库中的伪列rowid,rownumselect rownum,rowid,ename,empno,sal from emp;--数据定义语言DDL--create表的约束create table temp(empno number,ename varchar2(200),deptno number,edate date default sysdate,--默认值address varchar2(200) not null,--非空sal NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (sal0),--检查constraint aa foreign key(deptno) references dept(deptno),--外键constraint bb unique(ename),--唯一constraint cc primary key(empno));--主键--ALTERalter table temp modify (tid number(5));--修改列,当表中有值的时候不允许alter table temp add(tadd varchar2(2000));--添加列alter table temp drop column tadd;--删除列--rename表的重命名alter table emp rename to newname;rename emp to newename--重命名列名alter table emp rename column ename to aaa;--truncatetruncate table temp;--截断表，保留结构。不能回滚--drop--删除用户（级联）drop user newer cascade;--删除表空间(包含的所有内容)drop tablespace including contents;--删除表drop table temp;--从数据库中删除表及全部数据--数据操纵语言DMLselectinsertupdatedelete--利用现有表创建新表create table temp as select * from emp;--向表插入现有表的数据insert into temp(ename) select ename from emp ;--使用别名 三种方法，真接写，AS，加号--delete 与truncate的区别--事务控制语言TCL(commit,savepoint,rollback)--提交commit work;commit;--保存点（标记）savepoint sname;--回滚rollback;rollback to savepoint sname;--事务示例update emp set ename=aaa where empno=7369;savepoint savename;delete emp where emp=7369;savepoint savename1;rollback to savepoint savename;commit;--数据控制语言 DCL（grant,revoke）--将对象的权限授予用户grant all on emp to username;grant se