4.18.txt

java学习的点滴

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