oracle基础指令总结

--SELECT * FROM emp;

--INSERT INTO EMP (EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, DEPTNO)VALUES (7946, 'LISId', 'clear', 7782, SYSDATE,1300, 10);

--UPDATE table_name SETcolum1=v1, colum2 = v2 WHERE condition;

--UPDATE emp e SET e.sal =e.sal+100  WHERE e.ename= 'Zhangsan'

--SELECT NVL(E.COMM,0) FROM EMPA

--UPDATE EMP E SET E.SAL = E.SAL- 100,E.COMM=nVL(E.COMM,0)+100  WHEREE.ENAME = 'KING'

UPDATE EMP E SET E.SAL = E.SAL +100, E.COMM = NVL(E.COMM, 0) + 100 WHERE E.DEPTNO = 30

DELETE FROM emp e  WHERE e.eNAME='LISId';

DELETE FROM EMP E WHEREE.DEPTNO=10 AND e.sal BETWEEN 1300 AND 1600

序列的

INSERT INTO EMP

 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

VALUES

 (SEQ_EMP.NEXTVAL, 'wangwu', 7690, SYSDATE, 1400, 20);

 

select ceil(5.5) from dual;--大于5.5的最小整数

select ceil(-5.5) from dual;

 

select floor(5.5) from dual;--小于5.5的最大整数

select floor(-5.5) from dual;

 

select trunc(9.68,1) from dual;--舍去，保留一位小数

select round(9.68,1) from dual;--四舍五入，保留一位小数

-----------------------------------------------------------------------

--字符串、

SELECT ascii('0') FROM dual; --0-48

SELECT ascii('a') FROM dual;--a-97

SELECT ascii('A') FROM dual; --A-65

SELECT chr(47777) FROM dual;--ascii对应汉字

SELECT concat('青岛','城阳') FROM dual;--连接词

SELECT'青岛'||'城阳'FROM dual;--同上

 

SELECT instr('aaaabaaba','b') FROM dual;--找第一个b的位置

SELECT instr('aaaabaaba','b',6) FROM dual;--从第6位开始找

SELECT instr('aaaabaaba','b',6,2) FROM dual;--从第6位开始找第2个b的位置

 

SELECT upper('hello')FROM dual;--变大写

SELECT lower('NIHAO')FROM dual;--变小写

SELECTNLS_INITCAP('hellolll')FROM dual;--首字母大写

 

SELECT LPAD('jenny',10,'*') FROM dual;--左填充

SELECTLPAD(ename,10,'*') FROM emp;

SELECTRPAD(ename,10,'*') FROM emp;--右填充

 

SELECT ltrim('     jenny') FROM dual;--左省略

SELECT ltrim('!@!@!@!!!!@@@@jenny','!@') FROM dual;

SELECT rtrim('!@!@!@!@!!!@@@jenny!@!!@@!!!@@@','!@') FROM dual;

SELECTrtrim(ltrim('!@!@!@!@!!!@@@jenny!@!!@@!!!@@@','!@'),'!@') FROM dual;--左右都省略

SELECTtrim('!'FROM'!!!@!@!!!@@@jenny!@!!@@!!!@@@') FROM dual;--最左边不到@的叹号省略

 

SELECT substr('444于剑111',4,2) FROM dual;--从第4个开始出，出2个

SELECT substr('372330198809194214',7,4)||'-'||substr('373330198809194214',11,2)||'-'||

       substr('372330198809194214',13,2)

       FROM dual;先取出再连接

      

SELECTreplace('this is','is','was') FROM dual;--替换

SELECTreplace('this is','i','') FROM dual;--空替换，用作删除

SELECT length('this is') FROM dual;

----------------------------------------------------

SELECTSYSDATEFROM dual;--当前日期

SELECTadd_months(SYSDATE,-2) FROM dual;--此前两个月日期

SELECTadd_months('28-2月-2012',-2) FROM dual;

 

SELECT extract(YEARFROMSysdate) FROM dual;--却出年

SELECT extract(MONTHFROMSysdate) FROM dual;

SELECT extract(DAYFROMSysdate) FROM dual;

SELECTnext_day(sysdate,'星期五') FROM dual;--次日后的周五日期

 

SELECT trunc(Sysdate,'year') FROM dual;--截取日期，按年，月，日等

SELECT trunc(Sysdate,'month') FROM dual;

SELECT trunc(Sysdate,'day') FROM dual;

SELECT trunc(Sysdate,'hh') FROM dual;

----------------------------------

SELECT3.5||'abc'FROM dual;

SELECT to_char(123.98,'$999.999.999.99')FROM dual;--数字转化为美元形式

SELECT to_char(1234.89,'L999.999.999.99')FROM dual;--数字转化为人民币形式

SELECT to_char(sysdate,'yyyy-mm-dd')FROM dual;--转换日期格式

SELECT to_char(sysdate,'yyyy-mm-dd')FROM dual;

SELECT to_char(sysdate,'yyyy"年"mm"月"dd"日"')FROM dual;

 

SELECT to_date('2012年07月16日','yyyy"年"mm"月"dd"日"')FROM dual;--将输入的日期设置成指定类型

 

INSERTINTO EMP VALUES(7777,'test','test',7777,

 TO_DATE('2012年07月16日', 'yyyy"年"mm"月"dd"日"'),900,60,20);--插入指定类型的日期

INSERTINTO EMP VALUES(7778,'test','test',7777,'16-7月-2012',900,600,20);

INSERTINTO EMP VALUES(7770,'大爷','奶奶',7777,

 TO_DATE('2012年07月16日', 'yyyy"年"mm"月"dd"日"'),900,60,20);

 

 

SELECT * FROM emp WHERE hiredate>'2-4月-1981'--查找1981.4.2后入职的

SELECT * FROM emp WHEREhiredate>to_date('1981-4-2','yyyy-mm-dd')

 

 

SELECTto_number('$1,234,567,89','$999,999,999.99')+55FROM dual;

------------------------------------------------------------------------------------

SELECTCOUNT(*) FROM emp WHERE deptno=30;--查出部门号是30的

SELECTAVG(sal),SUM(sal) FROM emp WHERE deptno=30;

SELECTMAX(sal),MIN(sal) FROM emp WHERE deptno=30;

SELECT trunc(,2) FROM dual;

 

CREATESEQUENCE seq_emp INCREMENTBY1STARTWITH8000;--创建新序列

SELECT seq_emp.NEXTVALFROM dual;

SELECT seq_emp.CURRVALFROM dual;--查询当前的

INSERTINTO EMP VALUES(seq_emp.nextval,'小弟弟','啊滋玉',7777,

 TO_DATE('2012年07月16日', 'yyyy"年"mm"月"dd"日"'),900,60,20);--插入下一条

 DELETEFROM emp e WHERE e.ename='大爷';--删除name是'大爷'的

 SELECT * FROM emp WHERE deptno = 20ORDERBY sal ASC;

 SELECT ename FROM emp UNIONSELECT dname FROM emp ;--两个表联合查询

 

 SELECT * FROM emp;

 SELECT * FROM dept;

 SELECT e.ename,e.sal,d.NAMEFROM emp e,dept d WHERE e.deptno=d.deptno;--隐式内链接

 SELECT e.ename,e.sal,d.dname FROM emp e INNERJOIN dept d

  ON e.deptno=d.deptno AND e.sal>1600;--显示内连接

 SELECT e.ename,e.sal,d.dname FROM emp e leftJOIN dept d

  ON e.deptno=d.deptno AND e.sal>1600;--左连接

 SELECT e.ename,e.sal,d.dname FROM emp e fullJOIN dept d

  ON e.deptno=d.deptno AND e.sal>1600;--全链接

 

 SELECT sal FROM emp WHERE ename='ALLEN'

 SELECT * FROM emp WHERE sal>

  (SELECT sal FROM emp WHERE ename='ALLEN')--子查询（单行）以上一行为条件

 

SELECTename,job,sal,deptno FROM emp WHERE job IN

  (SELECTDISTINCT job FROM emp WHERE deptno=20) --子查询(多行) all any in

 

 

 SELECT * FROM emp;

 

Select * from emp where name like’老%’;

补充：

1. 查询当前数据库：

select name from v$database;

注：该指令需要用户具有dba权限

 

2. 查看当前用户

show user;

 

3. 产看数据库字符集：

1) select user('language') from dual;

2) select * from v$nls_parameters;

注：这两条指令都可以查看到该数据库的字符集，第二条指令查询的内容相对较多，第一条指令只显示字符集

 

4. 产看数据库所拥有的表：

select table_name from tabs;

 

5. 创建临时表空间：

create temporary tablespace test_temp tempfile 'E:/oracle/product/10.2.0/oradata/testserver/test_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; 

 

6. 创建数据库表空间：

create tablespace test_data datafile 'd:/oracle/tablespace/test.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;

 

7. 创建用户并指定表空间：

create user username identified by password default tablespace test_data temporary tablespace test_temp; 

 

8. 给用户赋予权限：

grant connect, resource, dba to username;

注：此处赋予用户三个权限：connect，resource，和dba权限；

 

9. 修改数据库字符集：

      Startup nomount;
　　Alter database mount exclusive;
　　Alter system enable restricted session;
　　Alter system set job_queue_process=0;
　　Alter database open;
　　Alter database character set zhs16gbk;

注：修改后要重启一下该数据库