ํ์ต ๋ด์ฉ
- ํ ์ด๋ธ ์์ฑ
CREATE TABLE ํ
์ด๋ธ๋ช
(
์ปฌ๋ผ๋ช
1 ์๋ฃํ,
์ปฌ๋ผ๋ช
2 ์๋ฃํ,
:
:
);
- ํ ์ด๋ธ ์ญ์
DROP TABLE ํ
์ด๋ธ๋ช
;
- ์๋ฃํ (Java vs DB)
Java DB
String VARCHAR2, CHAR, LONG
int INTEGER, NUMBER
double NUMBER
Date DATE
- ๋ฌธ์์ด
CHAR
CREATE TABLE TB_CHAR(
COL1 CHAR(10 BYTE),
COL2 CHAR(10 CHAR), -- 11111 11113 -> 12BYTE(ํ๊ธ 1๊ฐ์ฐ๋ฉด)
COL3 CHAR(10)
);
INSERT INTO ํ
์ด๋ธ๋ช
(์ปฌ๋ผ๋ช
, ์ปฌ๋ผ๋ช
... )
VALUES( ๊ฐ, ๊ฐ ... )
INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('ABC', 'ABC', 'ABC'); -- ์๋ฌธ์ 1๋ฌธ์์ ํฌ๊ธฐ 1 BYTE
SELECT * FROM TB_CHAR;
INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('๊ฐ', '๋', '๋ค'); -- ํ๊ธ์ ํ๋ฌธ์ ํฌ๊ธฐ 3 BYTE
INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('๊ฐ๋', '๊ฐ๋', '๊ฐ๋');
INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('๊ฐ๋๋ค', '๊ฐ๋๋ค', '๊ฐ๋๋ค');
SELECT COL1, COL2, COL3, LENGTHB(COL1), LENGTHB(COL2), LENGTHB(COL3)
FROM TB_CHAR;
CHAR : 10 BYTE 11111 11111
๊ฐ(3BYTE) : 12 BYTE 11111 11113
๊ฐ๋(6BYTE) : 14 BYTE 11111 11133
VARCHAR2
CREATE TABLE TB_VARCHAR(
COL1 VARCHAR2(10 BYTE),
COL2 VARCHAR2(10 CHAR),
COL3 VARCHAR2(10)
);
LONG
์ต๋ 2GB๊น์ง ์ ์ฅ ๊ฐ๋ฅ
TABLE๋น 1๊ฐ์ ์ปฌ๋ผ๋ง ์ฌ์ฉ๊ฐ๋ฅ
- ์ซ์
INTEGER ์ ์
CREATE TABLE TB_INTEGER(
COL1 INTEGER,
COL2 INTEGER
);
NUMBER ์ ์, ์์ ๋๋ค ๊ฐ๋ฅ
CREATE TABLE TB_NUMBER(
COL1 NUMBER,
COL2 NUMBER(5), --์ ์ ์๋ฆฌ์
COL3 NUMBER(5, 2), --์ ์์๋ฆฌ์, ์์์ ์๋ฆฌ์
COL4 NUMBER(*, 2) -- ์ ์์๋ฆฌ๋ ์ ํ์๋ค
);
DATE (๋ ์ง)
์ฐ๋, ์, ์ผ, ์, ๋ถ, ์ด
CREATE TABLE TB_DATE(
COL1 DATE,
COL2 DATE
);
SYSDATE = ํ์ฌ์๊ฐ
INSERT INTO TB_DATE(COL1,COL2)
VALUES (SYSDATE, SYSDATE -1); -- -1 = ํ๋ฃจ ์ ๋
INSERT INTO TB_DATE(COL1,COL2)
VALUES (SYSDATE, TO_DATE('2021-02-05 05:23:21', 'YYYY-MM-DD HH:MI:SS') );
-- TO_DATE('๋ฌธ์์ด', 'ํ์')
- Standard Function (ํ์ค ํจ์)
- DUAL TABLE : ๊ฐ์ํ ์ด๋ธ -> ๊ฒฐ๊ณผ ํ์ธ์ฉ ํ ์ด๋ธ
SELECT 'A' FROM DUAL;
- ๋ฌธ์ ํจ์
1. CHR( N ) : ASCII ๊ฐ์ ๋ฌธ์๋ก ๋ณํ
SELECT CHR(65) FROM DUAL; --๊ฒฐ๊ณผ A
2. ๋ฌธ์์ด ํฉ์น๊ธฐ ( || )
SELECT '๋ด ์ ์๋ ' || CHR(65) || '์
๋๋ค' FROM DUAL;
3. LPAD( or RPAD) : ๋๋จธ์ง๋ฅผ ๋น์นธ(or ์ง์ ๋ฌธ์)์ผ๋ก ์ฑ์ด๋ค.
SELECT LPAD('BBB', 10) FROM DUAL; -- ์ด 10์นธ, ์ผ์ชฝ๋ถํฐ ๋น์นธ 7 + BBB
SELECT RPAD('CCC', 10) FROM DUAL; -- ์ด 10์นธ, ์ค๋ฅธ์ชฝ๋ถํฐ ๋น์นธ 7 + CCC
SELECT LPAD('123', 10, '0') FROM DUAL; -- ์ด 10์นธ, ์ผ์ชฝ๋ถํฐ '0' 7 + 123
4. INSTR == indexOf('a') --> "abcde" - > 0 -- ์ค๋ผํด์ 1๋ฒ์ง๋ถํฐ ์์ํ๋ค.
SELECT INSTR('123ABC','A') FROM DUAL; -- ๊ฐ : 4
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL; -- 7๋ฒ์งธ ์ดํ๋ก ๋์ค๋ ๋๋ฒ์งธ A์ ์์น : ๊ฐ 16
SELECT INSTR('123ABC','Y') FROM DUAL; -- ๋ฌธ์๊ฐ ์์ผ๋ฉด 0์ด ๋์จ๋ค.
5. REPLACE : ๋ฌธ์์ด ์นํ
SELECT REPLACE('AAAAABCD', 'A') FROM DUAL; -- ๋ฐ๊พธ๊ณ ์ถ์ ๋ฌธ์๋ฅผ ์ง์ ํ์ง ์์ผ๋ฉด 'A'๊ฐ ์์ด์ง๋ค.
SELECT REPLACE('AAAAABCD', 'A', 'a') FROM DUAL; -- 'A'๊ฐ 'a'๋ก ๋ฐ๋๋ค.
SELECT REPLACE('AAAAABCD', 'AA', 'a') FROM DUAL; -- ๋ฌธ์์ด๋ ๊ฐ๋ฅ
6. TRANSLATE : ๋ฌธ์ ์นํ
SELECT TRANSLATE('AAAAABCD', 'A', 'a') FROM DUAL;
SELECT TRANSLATE('AAAAABCD', 'AA', 'a') FROM DUAL; -- ๋ฌธ์๋ง ์นํ๋๊ธฐ ๋๋ฌธ์ 'A'ํ๊ธ์๋ง ์ธ์ํ๋ค.
-- ๋๋ค ๊ฒฐ๊ณผ๋ aaaaaBCD๋ก ๊ฐ๋ค.
7. SUBSTR : ํน์ ์์น ๋ฌธ์์ด ๊ฐ์ ธ์ค๊ธฐ
SELECT SUBSTR('ABCDE', 3) FROM DUAL; --3๋ฒ์ง๋ถํฐ ๋๊น์ง
SELECT SUBSTR('ABCDE', 3, 2) FROM DUAL; -- 3๋ฒ์ง๋ถํฐ 2๋ฌธ์
SELECT SUBSTR('2021/02/09', 6, 2) FROM DUAL; -- ์๋ง ๋ฝ์๋ด๊ธฐ
- ์ซ์ ํจ์
1. CEIL : ์ฌ๋ฆผ
SELECT CEIL(13.1) FROM DUAL; --๊ฐ : 14
2. FLOOR : ๋ด๋ฆผ
SELECT FLOOR(13.9) FROM DUAL; --๊ฐ : 13
3. MOD : ๋๋จธ์ง๊ฐ
SELECT MOD (7, 4) FROM DUAL; -- ๊ฐ : 3
4. POWER : ์ ๊ณฑ
SELECT POWER(3,2) FROM DUAL; -- 3์ 2์น
5. ROUND : ๋ฐ์ฌ๋ฆผ
SELECT ROUND(13.5) FROM DUAL; --๊ฐ : 14
6. SIGN : ๋ถํธ ( + = 1 0 = 0 - = -1)
SELECT SIGN(13.4) FROM DUAL; -- ๊ฐ : 1
SELECT SIGN(0) FROM DUAL; -- ๊ฐ : 0
SELECT SIGN(-0.4) FROM DUAL; -- ๊ฐ : -1
7. TRUNC : ๋ฒ๋ฆผ(์์)
SELECT TRUNC(123.456) FROM DUAL; -- ๊ฐ : 123
SELECT TRUNC(123.456, 2) FROM DUAL; -- ๊ฐ : 123.45
SELECT TRUNC(123.456, -1) FROM DUAL; -- ๊ฐ : 120
SELECT TRUNC (17/6) FROM DUAL; -- ๊ฐ : 2
8. ASCII : ๋ฌธ์ -> ASCII
SELECT ASCII('A') FROM DUAL; -- ๊ฐ : 65
- ๋ณํ ํจ์
1. TO_CHAR (DATE -> VARCHAR2 ํ ๋ ์ ์ผ ๋ง์ด ์ฌ์ฉ)
SELECT SYSDATE FROM DUAL; -- ์ค๋ ๋ ์ง๋ฅผ
SELECT TO_CHAR(SYSDATE) FROM DUAL; -- ๋ฌธ์์ด๋ก ๋ณํ
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(243556546, '$999,999,999') FROM DUAL; -- ์ด๋ ๊ฒ๋ ํ์ฉ ๊ฐ๋ฅ. ๊ฐ : $243,556,546
2. TO_DATE (VARCHAR2 -> DATE)
SELECT TO_DATE('20210208') FROM DUAL; -- ๊ฒฐ๊ณผ : 21/02/08
SELECT TO_DATE('20210208', 'YYYYMMDD') FROM DUAL;
3. TO_NUMBER (VARCHAR2 -> NUMBER) ==parseInt()
SELECT TO_NUMBER('123') + 45 FROM DUAL; -- 123์ด ์ซ์๋ก ๋ณํ๋๊ธฐ ๋๋ฌธ์ ์ฐ์ฐ์ด ๋๋ค.
4. LAST_DAY (๋ฌ์ ๋ง์ง๋ง ๋ ์ง)
SELECT LAST_DAY('21/02/08') FROM DUAL;
SELECT LAST_DAY(TO_DATE('210201', 'YYMMDD')) FROM DUAL; -- ์๋๋๋ก ํ๋ค๋ฉด ์ด๋ฐ ์(๊ฐ์ ๊ฐ๋ค.)
5. MONTHS_BETWEEN (๋ ์ง์ ๋ ์ง ์ฌ์ด์ ๊ฐ์ ์)
SELECT MONTHS_BETWEEN(TO_DATE('211231', 'YYMMDD'), TO_DATE('210331', 'YYMMDD')) FROM DUAL;
6. ADD_MONTHS ์์ ์ฆ๊ฐ
SELECT ADD_MONTHS(TO_DATE('210331', 'YYMMDD'), -1 ) FROM DUAL;
-- ์ด์ ๋ฌ (2์์๋ 31์ผ์ด ์กด์ฌํ์ง ์์ผ๋ฏ๋ก 28์ผ์ด ์ถ๋ ฅ๋๋ค)
SELECT ADD_MONTHS(TO_DATE('210331', 'YYMMDD'), 1 ) FROM DUAL;
-- ๋ค์ ๋ฌ (4์์๋ 31์ผ์ด ์กด์ฌํ์ง ์์ผ๋ฏ๋ก 30์ผ์ด ์ถ๋ ฅ๋๋ค)
'๐ป ๊ฐ๋ฐ > ๐ TIL (Today I Learned)' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
21.02.16 TIL (ORACLE SUB QUERY, JOIN, TABLE, DDL, DML, ๋ฌด๊ฒฐ์ฑ) (0) | 2021.02.16 |
---|---|
21.02.09 TIL (D+22 Oracle Select) (0) | 2021.02.09 |
21.02.01 TIL (D+16 JAVA ArrayList, ์ ๋ค๋ฆญ) (0) | 2021.02.01 |
JAVA ์ ์๊ด๋ฆฌ ํ๋ก๊ทธ๋จ ๋ง๋ค๊ธฐ (0) | 2021.02.01 |
21.01.30 TIL (JAVA flnal, static, ์ถ์ ํด๋์ค, ์ธํฐํ์ด์ค) (0) | 2021.01.31 |