1. SELECT ๐
- ํ
์ด๋ธ์ด๋ ๋ทฐ์ ์๋ ๋ฐ์ดํฐ๋ฅผ ์กฐํํ์ฌ 0๊ฐ ์ด์์ ํ์ ๋ฐํํ๋ค.
- ๊ฐ์ฅ ์ผ๋ฐ์ ์ผ๋ก ์ฌ์ฉ๋๋ DQL(data query language) ๋ช
๋ น์ด๋ฉฐ, DML(data manipulation language)์ ๊ตฌ์ฑ ์์๋ก ๊ฐ์ฃผ
- SELECT ๋ฌธ์ ๋ฐ๋์ SELECT์ FROM์ ๋ ๊ฐ์ ํค์๋๋ก ๊ตฌ์ฑ
- SELECT์ ๋ค์์๋ ์ถ๋ ฅํ ์ปฌ๋ผ ๋ช
์ด๋ ํํ ์์ ๊ธฐ์ ํ๊ฑฐ๋ ํน์ ์ปฌ๋ผ ๋ช
๋์ * (๋ชจ๋ ์ปฌ๋ผ ์ ๋ณด)
- FROM์ ๋ค์์๋ ์กฐํํ ํ
์ด๋ธ(๋ทฐ)์ ์ด๋ฆ์ ๊ธฐ์
2. SELECT ๊ตฌ๋ฌธ ์์ ๋ฐ ์คํ ์์
SELECT ๊ตฌ๋ฌธ ๊ตฌํ ์์
1) SELECT ์ปฌ๋ผ๋ช (ํํ์)
2) FROM ํ ์ด๋ธ๋ช
3) WHERE ์กฐ๊ฑด์
4) GROUP BY ์ปฌ๋ผ๋ช (ํํ์)
5) HAVING ์ง๊ณํจ์ ์กฐ๊ฑด์
6) ORDER BY ์ปฌ๋ผ๋ช (ํํ์)
SELECT ๊ตฌ๋ฌธ ์คํ ์์
1) FROM ํ ์ด๋ธ๋ช
2) WHERE ์กฐ๊ฑด์
3) GROUP BY ์ปฌ๋ผ๋ช (ํํ์)
4) HAVING ์ง๊ณํจ์ ์กฐ๊ฑด์
5) SELECT ์ปฌ๋ผ๋ช (ํํ์)
6) ORDER BY ์ปฌ๋ผ๋ช (ํํ์)
3. ํ ์ด๋ธ ๋ชฉ๋ก ํ์ธ
SELECT * FROM tabs;
SELECT * FROM tab;
4. ํ ์ด๋ธ์ ์ปฌ๋ผ ๋ฐ ํ์ ํ์ธ
SELECT * FROM cols WHERE tablle_name = ‘ํ
์ด๋ธ๋ช
’;
SELECT * FROM col WHERE tname = ‘ํ
์ด๋ธ๋ช
’;
5. ์ฐ์ ์ฐ์ฐ์
+, -, *, /
๋ฌธ์์ด ๊ฒฐํฉ
||
๊ด๊ณ ์ฐ์ฐ์
- >, >=, <, <=, =, !=, <>
- ๋ ผ๋ฆฌ ์ฐ์ฐ์
- AND, OR, NOT
SQL ์ฐ์ฐ์
- BETWEEN : ์ปฌ๋ผ๋ช BETWEEN ๊ฐ AND ๊ฐ
- IN ์ปฌ๋ผ๋ช IN(๊ฐ, ๊ฐ, ๊ฐ)
๋ฐ์ดํฐ ๊ฒ์
- SELECT ์ปฌ๋ผ๋ช , ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ๋ช ;
- SELECT ์ปฌ๋ผ๋ช [AS] ๋ณ๋ช , ์ปฌ๋ผ๋ช [AS] ๋ณ๋ช ;
- SELECT ์ปฌ๋ผ๋ช , ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ๋ช WHERE ์กฐ๊ฑด;
โ SELECT ๋ฌธ์ ์ด์ฉํ ์กฐํ
์ปฌ๋ผ ์ ๋ณด
empNo : ์ฌ์๋ฒํธ
name : ์ด๋ฆ
rrn : ์ฃผ๋ฏผ๋ฒํธ
hireDate : ์ ์ฌ์ผ
city : ์ถ์ ๋
tel : ์ ํ๋ฒํธ
dept : ๋ถ์๋ช
pos : ์ง์
sal : ๊ธ์ฌ
bonus : ๋ณด๋์ค
SELECT ๋ฌธ
ο ํ
์ด๋ธ ๋๋ ๋ทฐ์ ์ ์ฒด ๋ ์ฝ๋(ํ) ์ถ๋ ฅ
-- ํน์ ์ปฌ๋ผ ๋ง ์ถ๋ ฅ(emp ํ
์ด๋ธ : empNo, name, sal ์ปฌ๋ผ)
DESC emp;
-- ํ
์ด๋ธ์ ๋ชจ๋ ๋ ์ฝ๋ ์ถ๋ ฅ
SELECT ์ปฌ๋ผ๋ช
, ์ปฌ๋ผ๋ช
FROM ํ
์ด๋ธ;
SELECT empNO, name, sal FROM emp;
SELECT empNum, name, sal FROM emp; -- ์๋ฌ
--ORA-00904 (๋ถ์ ์ ํ ์๋ณ์, ์กด์ฌํ์ง ์๋ ์ปฌ๋ผ ์ฌ์ฉ)
SELECT name, city FROM emp;
-- ์์ ์ฌ์ฉ
SELECT 10+5 FROM dual;
SELECT 10/3 FROM dual;
SELECT '๊ฒฐ๊ณผ'|| (10*3) FROM dual;
SELECT 10+5 FROM emp; -- 60๊ฐ์ ๋ ์ฝ๋๊ฐ ์กด์ฌํ๋ฏ๋ก 60๊ฐ์ 15๊ฐ ์ถ๋ ฅ
SELECT empNo, name, sal, bonus FROM emp;
SELECT empNo, name, sal, bonus, sal+bonus FROM emp;
SELECT empNo, name||'๋', sal, bonus, sal+bonus FROM emp;
-- ๋ชจ๋ ์ปฌ๋ผ ์ถ๋ ฅ(emp ํ
์ด๋ธ : ๋ชจ๋ ์ปฌ๋ผ ์ถ๋ ฅ)
-- emp ํ
์ด๋ธ์ ์ปฌ๋ผ๋ช
๋ฐ ์ปฌ๋ผํ์
ํ์ธ
SELECT * FROM col WHERE tname = 'ENP';
DESC emp;
-- ์ปฌ๋ผ๋ช
์ ๋ณ๊ฒฝํ์ฌ ์ถ๋ ฅ(emp ํ
์ด๋ธ : empNo, name, sal ์ปฌ๋ผ)
-- ์ถ๋ ฅ๋๋ ์ปฌ๋ผ์ ์์๋ ๋ณ๊ฒฝ ๊ฐ๋ฅ
SELECT empNo, name, rrn, hireDate, city, tel, dept, pos, sal, bonus From emp;
SELECT * From emp;
-- AS ์๋ต ๊ฐ๋ฅ
SELECT ์ปฌ๋ผ๋ช
AS ๋ณ๋ช
, ์ปฌ๋ผ๋ช
AS ๋ณ๋ช
FROM ํ
์ด๋ธ๋ช
SELECT empNo, name, sal FROM emp;
SELECT empNo, name, sal, bonus, sal + bonus FROM emp;
SELECT empNo, name, sal, bonus, sal + bonus AS pay FROM emp;
SELECT empNo ์ฌ๋ฒ, name ์ด๋ฆ, sal ๊ธฐ๋ณธ๊ธ, bonus ๋ณด๋์ค , sal + bonus ๊ธ์ฌ FROM emp;
SELECT empNo AS ์ฌ๋ฒ, name AS ์ด๋ฆ, sal + bonus AS ์ด๊ธ์ฌ FROM emp;
SELECT empNo AS "์ฌ ๋ฒ", name AS "์ด ๋ฆ", sal + bonus AS "์ด ๊ธ ์ฌ" FROM emp;
-- ํ
์ด๋ธ ๋ช
์ ๋ณ๋ช
๋ถ์ฌ(emp ํ
์ด๋ธ : ROWNUM๊ณผ ๋ชจ๋ ์ปฌ๋ผ ์ถ๋ ฅ)
SELECT ROWNUM, empNo, name, sal FROM emp;
-- ROWNUM : ์ถ๋ ฅ๋๋ ๋ ์ฝ๋์ ํ ๋ฒํธ
SELECT ROWNUM, * FROM emp; --์๋ฌ. *๋ ๋ค๋ฅธ ์ปฌ๋ผ๊ณผ ์ฌ์ฉ ๋ถ๊ฐ
SELECT ROWNUM, emp.* FROM emp;
SELECT ROWNUM, e.* FROM emp e; -- ํ
์ด๋ธ์ ๋ณ๋ช
์ฃผ๊ธฐ(AS ์ฌ์ฉ ๋ถ๊ฐ)
์กฐ๊ฑด์๊ณผ ํํ์
ο ๋น๊ต ์ฐ์ฐ์ : =, >, <, <=, >=, <>, !=, ^=
-- emp ํ
์ด๋ธ : city๊ฐ ์์ธ์ธ ์๋ฃ ์ค name, city ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, city
FROM emp
WHERE city = '์์ธ';
SELECT name, city
FROM emp
WHERE city <>'์์ธ';
-- emp ํ
์ด๋ธ: city๊ฐ ์์ธ์ด ์๋ ์๋ฃ ์ค name, city ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, city
FROM emp
WHERE city != '์์ธ';
-- emp ํ
์ด๋ธ : sal+bonus ๊ฐ 2500000์ ์ด์์ธ ์๋ฃ ์ค name, sal, bonus ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, sal, bonus
FROM emp
WHERE sal+bonus >= 2500000;
SELECT name, sal, bonus, sal+ bonus pay
FROM emp
WHERE pay >= 2500000;
-- ์๋ฌ: ORA-00904.
--1) FROM ๊ตฌ๋ฌธ ๋ถ์
--2) WHERE ๊ตฌ๋ฌธ ๋ถ์ : pay๋ emp ํ
์ด๋ธ์ ์ปฌ๋ผ์ด ์๋๋ฏ๋ก ์ค๋ฅ
--3) SELECT์ ๊ตฌ๋ฌธ ๋ถ์
ο ๋
ผ๋ฆฌ ์ฐ์ฐ์ : AND, OR, NOT
-- emp ํ
์ด๋ธ : city๊ฐ ์์ธ์ด๊ณ sal๊ฐ 2000000์ ์ด์์ธ ์๋ฃ ์ค empNo, name, city, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, city, sal
FROM emp
WHERE city = '์์ธ' AND sal>= 2000000;
-- emp ํ
์ด๋ธ : city๊ฐ ์์ธ, ๊ฒฝ๊ธฐ, ์ธ์ฒ์ธ ์๋ฃ ์ค empNo, name, city, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, city, sal
FROM emp
WHERE city = '์์ธ' OR city ='๊ฒฝ๊ธฐ' OR city ='์ธ์ฒ';
-- emp ํ
์ด๋ธ : sal๊ฐ 2000000~3000000์ ์ ์ธํ ์๋ฃ ์ค empNo, name, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, sal
FROM emp
WHERE sal < 2000000 OR sal > 300000;
-- emp ํ
์ด๋ธ : ์ฑ์จ๊ฐ ใฑ์จ์ธ empNo, name ์ปฌ๋ผ
SELECT empNo, name
FROM emp
WHERE name > '๊ฐ' AND name < '๋';
-- emp ํ
์ด๋ธ : dept๊ฐ ๊ฐ๋ฐ๋ถ์ด๊ณ pos๊ฐ ๋ถ์ฅ, ๊ณผ์ฅ์ธ empNO, name, dept, pos, sal ์ถ๋ ฅ
SELECT empNO, name, dept, pos, sal
FROM emp
WHERE dept = '๊ฐ๋ฐ๋ถ' AND (pos = '๋ถ์ฅ' OR pos ='๊ณผ์ฅ');
-- emp ํ
์ด๋ธ : sal 200000~300000์ธ ์ฌ๋ ์ค city๊ฐ ์์ธ, ๊ฒฝ๊ธฐ์ธ empNo, name, city, sal ์ถ๋ ฅ
SELECT empNo, name, city, sal
FROM emp
WHERE sal>=2000000 AND sal<=3000000 AND (city = '์์ธ' OR city = '๊ฒฝ๊ธฐ');
ο ๊ทธ๋ฃน ๋น๊ต ์ฐ์ฐ์ : ANY(SOME), ALL
-- emp ํ
์ด๋ธ : city๊ฐ '์์ธ', '๊ฒฝ๊ธฐ', '์ธ์ฒ' ์ธ ์๋ฃ ์ค empNo, name, city ์ปฌ๋ผ ์ถ๋ ฅ
-- ANY(SOME) : OR๊ณผ ์ ์ฌ
SELECT empNo, name, city
FROM emp
WHERE city = ANY('์์ธ', '๊ฒฝ๊ธฐ', '์ธ์ฒ');
-- emp ํ
์ด๋ธ : sal๊ฐ 2000000์ ์ด์์ธ ์๋ฃ ์ค empNo, name, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, city
FROM emp
WHERE sal >= ANY(200000, 3000000, 5000000);
SELECT empNo, name, city
-- emp ํ
์ด๋ธ : sal๊ฐ 2000000์ ์ด์์ธ ์๋ฃ ์ค empNo, name, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, city
FROM emp
WHERE sal >= ANY(200000, 3000000, 5000000);
SELECT empNo, name, city
FROM emp
WHERE sal >= 2000000;
-- emp ํ
์ด๋ธ : sal๊ฐ 3000000์ ์ด์์ธ ์๋ฃ ์ค empNo, name, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, sal
FROM emp
WHERE sal >= 3000000;
'๐ปProgramming > ๐ฝOracle' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
[Oracle] ๋ฐ์ดํฐ ๋์ ๋๋ฆฌ(Data Dictionary) (0) | 2022.08.10 |
---|---|
[ORACLE] ๋ฐ์ดํฐ ์กฐ์์ธ์ด(DML) (0) | 2022.08.09 |
[ORACLE] ๋ฐ์ดํฐ ์ ์์ธ์ด (DDL) (0) | 2022.08.09 |
[ORACLE] SQL ํจ์ - ๋จ์ผํ ํจ์ (0) | 2022.08.05 |
[Oracle] ๊ธฐ๋ณธ์ ์ธ SQL (0) | 2022.08.04 |