sql語句

數據類型

數字型
BINARY_INTEGER

	NATURAL     0 - 2147283647  
	NATURALN    0 - 2147283647   NOT NULL
	POSITIVE    1- 2147283647
	POSITIVEN   1- 2147283647    NOT NULL
	SIGNTYPE    -1,0,1

NUMBER 
	NUMBER (精度,刻度)
	精度:數字個數
	刻度:小數點右邊的數字個數
	DEC                  38位數字
	DECIMAL              38位數字
	DOUBLE PRECISION     38位浮點數字
	FLOAT                38位浮點數字
	INTEGER              38位整數
	INT                  38位整數
	NUMERIC              38位數字
	REAL                 18位浮點數字
	SMALLINT             38位整數

字符型


	CHAR          32767字節
	VARCHAR2      2048字節
	LONG          32760字節 
	LONGRAW       32760字節

大型對象

 
	BFILE         4G
	BLOB          4G
	CLOB          4G
	NCLOB         4G


日期
DATE
TIMESTAMP


--創建部門表
	DROP TABLE DEPARTMENT;

	CREATE TABLE DEPARTMENT (
	  DEPTID    NUMBER(3),
	  DEPTNAME  VARCHAR2(20),
	  LEADER    VARCHAR2(10)
	);

	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(1,'JAVA','TOMCAT');

	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(2,'MARKETING','HARRY');

	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(3,'HR','ANN');


	INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
	VALUES(4,'TEST','ROSE');

	COMMIT;

	SELECT * FROM DEPARTMENT;

--創建員工表
   

DROP TABLE EMPLOYEE


CREATE TABLE EMPLOYEE(
    EMID NUMBER(6),
    NAME VARCHAR2(10),
    AGE  NUMBER(3),
    JOB  VARCHAR2(10),
    SALARY NUMBER(10,3),
    BIRTHDAY DATE
)

--日期函數
--字符串轉化成日期
-- TO_DATE('1980-10-15','YYYY-MM-DD')  TO_DATE('1988-10-15 19:30:59','YYYY-MM-DD HH24:MI:SS'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY) 
VALUES (1,'TOM',20,'PROGRAMMER',3000,TO_DATE('1986/10-15','YYYY/MM-DD'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY) 
VALUES (2,'HARRY',20,'PROGRAMMER',3000,TO_DATE('1986-1-15','YYYY-MM-DD'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY) 
VALUES (3,'ANN',20,'CODING',1000,TO_DATE('1986-10-15','YYYY-MM-DD'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY) 
VALUES (4,'SUNNY',25,'CODING',1500,TO_DATE('1981-10-15','YYYY-MM-DD'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY) 
VALUES (5,'LEE',27,'TEAMLEADER',8000,TO_DATE('1979-10-15','YYYY-MM-DD'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY)
 VALUES (6,'BLACK',18,'BOSS',10000,TO_DATE('1988-10-15','YYYY-MM-DD'))

INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY)
 VALUES (7,'BLACK',18,'BOSS',10000,TO_DATE('1988-10-15 19:30:59','YYYY-MM-DD HH24:MI:SS'))
INSERT INTO EMPLOYEE  (EMID,NAME) VALUES (8,'JOR')

COMMIT；


-查詢系統時間

SELECT SYSDATE FROM DUAL


-- 日期轉化成字符串  TO_CHAR(BIRTHDAY,'YYYY/MM/DD')

SELECT EMID,NAME,AGE,JOB,SALARY, TO_CHAR(BIRTHDAY,'YYYY/MM/DD') FROM EMPLOYEE

--為了在程序中處理 增加別名

SELECT EMID,NAME,AGE,JOB,SALARY, TO_CHAR(BIRTHDAY,'YYYY/MM/DD') AS BIRTH FROM EMPLOYEE 

--日期加減運算
--間隔幾個月
SELECT MONTHS_BETWEEN (SYSDATE , TO_DATE('2006-05-31','YYYY-MM-DD')) FROM DUAL

SELECT MONTHS_BETWEEN (SYSDATE , TO_DATE('2006-05-31','YYYY-MM-DD')) FROM EMPLOYEE

--給日期 + n個月

SELECT BIRTHDAY,ADD_MONTHS (BIRTHDAY, 3)  ADDMON FROM EMPLOYEE

SELECT BIRTHDAY,ADD_MONTHS (BIRTHDAY, 5)  ADDMON FROM EMPLOYEE

SELECT BIRTHDAY,ADD_MONTHS (BIRTHDAY, -5) ADDMON FROM EMPLOYEE

--給日期 + n天

SELECT BIRTHDAY,BIRTHDAY + 5  ADDDAY FROM EMPLOYEE

SELECT BIRTHDAY,BIRTHDAY - 5  ADDDAY FROM EMPLOYEE


--指定日期的下一個星期幾   1 - 星期天   'MONDAY' 

SELECT NEXT_DAY(SYSDATE, 1) DAYOFWEEK FROM DUAL

--指定月份的最后一天
SELECT LAST_DAY(SYSDATE) LASTDAY   FROM DUAL



-- TO_CHAR(NUMBER,'FMT')
SELECT TO_CHAR(SALARY,'$9,999,999') AS MONEY  FROM EMPLOYEE
SELECT TO_CHAR(SALARY,'L9,999,999') AS MONEY  FROM EMPLOYEE
SELECT TO_CHAR(SALARY,'L0,000,000.00') AS MONEY  FROM EMPLOYEE
SELECT TO_CHAR(SALARY,'L9,999,999.00') AS MONEY  FROM EMPLOYEE




--數字函數
-- TO_NUMBER
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY,BIRTHDAY)
 VALUES (6,'JACK',18,'BOSS',TO_NUMBER('1234','9999'),TO_DATE('1988-10-15','YYYY-MM-DD'))

--ROUND(列名 ，n) 四舍五入到小數點后n位
SELECT ROUND(3.
1415926,4) FROM DUAL
-- 四舍五入到整數位
SELECT NAME,SALARY ,ROUND(SALARY,  -1) FROM EMPLOYEE

--TRUNC(列名 ，n) 截取到小數點后n位
SELECT TRUNC(3.1415926,3) FROM DUAL
--
SELECT NAME,SALARY ,TRUNC(SALARY,  -1) FROM EMPLOYEE


--字符串函數
--UPPER 

SELECT UPPER(NAME) UP FROM EMPLOYEE

--LOWER

SELECT LOWER(NAME) UP FROM EMPLOYEE


--INITCAP 首字母大寫

SELECT INITCAP (NAME ) INS FROM EMPLOYEE

--CONCAT 連接字符串

SELECT CONCAT (NAME,SALARY ) INS FROM EMPLOYEE


--SUBSR（COLOMN，M，N） 截取子串，從m開始，長度為n

SELECT NAME,SUBSTR(NAME,0,2 ) INS FROM EMPLOYEE

SELECT NAME,SUBSTR(NAME,1,2 ) INS FROM EMPLOYEE

SELECT NAME,SUBSTR(NAME,2,2 ) INS FROM EMPLOYEE

--TREM( [leading | trailing | both ] 要去掉的字符串 from 源字符串) DEFAULT：BOTH

SELECT NAME,TRIM('E' FROM NAME ) INS FROM EMPLOYEE

--REPLACE

SELECT NAME,REPLACE(NAME,'O' ,'XXXXXX' ) INS FROM EMPLOYEE

--INSTR
-- 語法: instr(string1,string2,[n],[m]) 這里的 [] 表示可選參數
-- 
-- 從 string1 的第 n 個字符開始尋找 string2 的第 m 個出現。
-- 如果 n 是負數，則從后向前著。如果沒有傳遞 m，假定為 1（從第一個字符開始找）。
-- 
--INSTR(C1,C2,I,J)
在一個字符串中搜索指定的字符,返回發現指定的字符的位置;
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的開始位置,默認為1
J     出現的位置,默認為1
 select instr('oracle traning','ra',1,2) instring from dual;
結果：9
'oracle traning'中第二次出現ra的位置是9（從1 開始）


分組函數
--平均值
SELECT AVG(SALARY) FROM EMPLOYEE

--記錄數量 注意NULL的問題
SELECT count (SALARY) FROM EMPLOYEE

--MAX
SELECT MAX(SALARY) FROM EMPLOYEE

--MIN
SELECT MIN(SALARY) FROM EMPLOYEE

--SUM
SELECT SUM(SALARY) FROM EMPLOYEE

--GROP

SELECT JOB,SUM(SALARY) FROM EMPLOYEE
GROUP BY JOB


SELECT JOB,SUM(SALARY) FROM EMPLOYEE
GROUP BY JOB
HAVING JOB !='BOSS'

--NVL
SELECT NVL(AGE,0) FROM EMPLOYEE

--DECODE
SELECT NAME,DECODE(JOB,'BOSS','老板','打工的') FROM EMPLOYEE