创建和管理表(DDL)
--创建表
CREATE TABLE emp1(
ID NUMBER(4) NOT NULL,
NAME VARCHAR2(20),
salary NUMBER(5,2),
hire_date DATE
)
--修改表
-|增加新列
ALTER TABLE emp1
ADD (birthday DATE)--括号内不要加逗号
-|修改现有的列
ALTER TABLE emp1
MODIFY (birthday VARCHAR2(20))
-|重命名列名
ALTER TABLE emp1
RENAME COLUMN salary TO sal
-|删除现有的列
ALTER TABLE emp1
DROP COLUMN birthday
-|清空表中数据
TRUNCATE TABLE emp1
--重命名表
RENAME emp1 TO emp2
--删除表
DROP TABLE emp1
--------------------------------------------------------------------------------------
数据处理 DML
--增
-|插入数据
INSERT INTO emp2(NAME, sal, ID, hire_date)
VALUES('lsd', 132, 002, TO_DATE('2009-8-8', 'yyyy/mm/dd'))
-|从其它表中拷贝数据
INSERT INTO emp1
SELECT ID, NAME, sal, hire_date FROM emp2
WHERE emp2.ID = 002
--改
UPDATE emp2 SET sal=9999 WHERE ID=001
--删
DELETE emp2 WHERE ID = 2
---------------------------------------------------------------------------------------
--字符串函数
SELECT UPPER('Hello'),LOWER('Hello') FROM dual
SELECT * FROM emp WHERE ename=UPPER('&name')
SELECT LOWER(ename) FROM emp
SELECT INITCAP(ename) FROM emp
SELECT ename,LENGTH(ename) FROM emp
SELECT ename,REPLACE(ename,'A','_') FROM emp
SELECT SUBSTR('hello world',0,5) FROM dual
SELECT ' hello world ',TRIM(' hello world ') FROM dual
SELECT RPAD('sf',13,'111') FROM dual--->sf11111111111
SELECT LPAD('you',8,'love ') FROM dual--->love you
--数字函数
-|四舍五入
SELECT ROUND (34.3434),
ROUND (23.5435,1),
ROUND (434.342,-1)
FROM dual
-|截取小数
SELECT TRUNC(9615.7652) ,
TRUNC(9615.7652,2) ,
TRUNC(9615.7652,-2) ,
TRUNC(-15.5)
FROM dual
-|求余
SELECT MOD(10,3) FROM dual
--日期函数
SELECT SYSDATE FROM dual
SELECT ename, TRUNC(SYSDATE - hiredate) FROM emp
SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate)FROM emp
SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>33
计算下一个星期二
SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual
计算若干月之后的日期
SELECT SYSDATE,ADD_MONTHS(SYSDATE,4),ADD_MONTHS(SYSDATE,500) FROM dual
求出本月的最后一天日期
SELECT LAST_DAY(SYSDATE) FROM dual
--转换函数
-|转字符串 TO_CHAR
日期:年yyyy,月mm,日dd,时HH,HH24,分mi,秒ss;
数字:一位任意数字(9),本地货币(L)
SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM dual--->2016-10-11
SELECT to_char(sal,'L99,999.00') sal FROM emp--->¥800.00
-|转日期数据 TO_DATE
SELECT '1981-09-15',TO_DATE('1981-09-15','yyyy-mm-dd') FROM dual
SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual ;
SELECT '1' + '2' FROM dual(自动转换)
--通用函数
-|处理 null(遇到空的数据,按照数字0处理)
SELECT ename,sal,NVL(comm,0) FROM emp
-|if-then-else
SELECT ename,job ,
DECODE(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经 理','ANALYST','分析员','PRESIDENT','总裁','未处理')
FROM emp
-->如果job='CLERK',then'办事员'