《Oracle必备学习资料.doc》由会员分享，可在线阅读，更多相关《Oracle必备学习资料.doc（46页珍藏版）》请在金锄头文库上搜索。
1、-创建表空间Create tablespace newer datafile d:tablespacenewer.ora size 50mautoextend on;-创建临时表空间Create TEMPORARY tablespace temp1 tempfile d:tablespacetemp1.ORA size 10mautoextend on ;-创建用户create user newer identified by newer;-为用户指定表空间与临时表空间Alter user newer default tablespace newer ;Alter user newer tem
2、porary 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
3、数据库中的伪列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(d
4、eptno) 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 em
5、p 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 *
6、 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;del
7、ete emp where emp=7369;savepoint savename1;rollback to savepoint savename;commit;-数据控制语言 DCL（grant,revoke）-将对象的权限授予用户grant all on emp to username;grant select,update,delete,insert on emp to username;-将角色的权限授予用户grant connect to username; -连接数据库grant resource to username;-使用数据表空间grant unlimited tables
8、pace to username;-表空间无限增长grant grant any privilege to username;-授予可以授权的权限-删除用户权限revoke connect for username; -删除角色权限revoke select on emp for username;-删除对象权限-使用集合操作符union-联合（去重复）union all-联合所有(不去重复)intersect-交集minus -减集-示例select deptno from emp union select deptno from dept;-取并集去重复select deptno from
9、 emp union all select deptno from dept;-取并集select deptno from emp intersect select deptno from dept;-取交集-注意下面两个表的位置不同，结果的区别,从第一个结果里找出不存在于第二个集合里的记录select deptno from emp minus select deptno from dept;select deptno from dept minus select deptno from emp;-取差集-函数-单行函数-日期函数select add_months(sysdate,2) fr
10、om dual;-月加select months_between(sysdate,sysdate) from dual;-求两日期月差select last_day(sysdate)from dual;-当月最后一天select round(sysdate,YEAR) from dual;-YEAR舍入到最近的年，MONTH舍入最近的月DAY天select next_day(sysdate,星期二) from dual;-返回下个星期几的日期select trunc(sysdate,YEAR) from dual;-YEAR截断日期到年月日，与round的区别是只舍不入默认返回天select
11、extract(year from sysdate) from dual;-返回日期的特定部分year,month,day-字符函数select initcap(adsfad) from dual;-首写母大写select lower(AAAA) from dual;-转换小写select upper(bbb) from daul;-转换大写select char(67) from dual;-返回ASCII码字符select trim(leading 9 from 9123456789) from dual;-去前面select trim(trailing 9 from 9123456789
12、) from dual;-去后面select trim(9 from 9123456789) from dual;-去所有select trim( 9123456789 ) from dual;-去空格select ltrim(9123456789,9) from dual;-去左边9select rtrim(9123456789,9) from dual;-去右边9select length(string ) from dual;-返回字符串长度注意空格也算长度select decode(abcd,abcd,1234) from dual;-逐值替换,可替换多个，完全相同才替换select
13、translate(jack,abcd,1234) from dual;-在jack中用1234翻译abcd来替换其中的字符select replace(jack,ck,hh) from dual;-把jack中的ck替换为hhselect instr(jack,c) from dual;-找出JACK中的C所处的位置，索引是从1开始的select substr(jack,2,1) from dual;-截断字符串select concat(asdf,dddd) from dual;-合并字符串-数字函数ceil-向上取整abs-取绝对值sqrt-求平方power(m,n)-取M的N次幂cos
14、-取余弦函数sin-取正弦sign-取符号mod(m,n)-取模round(m,n)-四舍五入trunc(m,n)-截断floor-向下取整-转换函数select to_char(sysdate,YYYY-MM-DD) from dual;-日期转换select to_char(456,c9999) from dual;-添加人民币符号CNYselect to_date(2005-2-3,YYYY-MM-DD) from dual;-将字符转为日期select sqrt(to_number(100) from dual;-数字转换函数,取平方根-其它函数select nvl(,空) from dual;select nvl2(,不为空,空) from dual;select nullif(123,123) from dual;-相等返回空，否则返回前一个值-组合函数select avg(sal) from emp;-求平均值select sum(sal) from emp;-求和select min(sal) from emp;-最小值select max(sal) from emp;-最大值select count(*) from emp;-求记录数
