day1

์ƒ์€๐Ÿ‘ธยท2023๋…„ 11์›” 27์ผ
0

๋šœ๋ฒ…๋šœ๋ฒ… ์„ธ๋ฒˆ์งธ

๋ชฉ๋ก ๋ณด๊ธฐ
1/11
post-thumbnail

๐Ÿ“–sql_developer

windows OS PC -> ์˜ค๋ผํด DB -> OracleListener -> sqldeveloper ํˆด์„ ์‚ฌ์šฉํ•ด์„œ ์ ‘์†

=> ๊ทธ๋ƒฅ cmd์ฐฝ์—๋‹ค๊ฐ€ ์ž‘์„ฑํ•˜๋ฉด ๋ญ๊ฐ€ ํ‹€๋ฆฐ์ง€๋„ ๋ชจ๋ฅด๊ณ  ์ž๋™์ƒ์„ฑ๋„ ๋˜์ง€๋„ ์•Š๊ณ  ๊ทธ๋ž˜์„œ ๋ถˆํŽธํ•˜๋‹ˆ๊นŒ sqldeveloper ํˆด์„ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐ„ํŽธํ•˜๊ฒŒ ์ฟผ๋ฆฌ๋ฌธ ์ž‘์„ฑ~!

๐Ÿ“–DB ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌ(์กฐ์ž‘) ๊ธฐ๋ณธ

CRUD

Create (์ƒ์„ฑ)
Read (์กฐํšŒ-์ฝ๊ธฐ)
Update (์ˆ˜์ •)
Delete (์‚ญ์ œ)

๐Ÿ“–SQL ๋ฌธ๋ฒ• : (์ฟผ๋ฆฌ๋ฌธ - ์ฟผ๋ฆฌ์ข€ ์งœ๋ด๋ผ ์ฟผ๋ฆฌ ํ•˜๋‚˜ ์งœ์™€๋ผ)

  • ์กฐํšŒ SELECT * FROM dept; / SELECT ์กฐํšŒํ•  ์ปฌ๋Ÿผ FROM ํ…Œ์ด๋ธ”๋ช…;
  • ์ˆ˜์ •
  • ์ƒ์„ฑ
  • ์‚ญ์ œ -> ๊ฑฐ์˜X / ๊ฐ€์ž…์ƒํƒœ๊ฐ’: ํƒˆํ‡ด์ƒํƒœ๋กœ ๋ณ€๊ฒฝํ•˜๋Š”๊ฑฐ์ง€ ๋ฐ์ดํ„ฐ๋ฅผ ์‚ญ์ œํ•˜์ง€ ์•Š์Œ!

๐Ÿ“–select๋ฌธ

  1. ์กฐํšŒ : select

๐Ÿ– (1) ์ „์ฒด ์กฐํšŒ : SELECT * FROM dept;

dept ํ…Œ์ด๋ธ”์— ์žˆ๋Š” ์ „์ฒด ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒ

๐Ÿ” (2) ์›ํ•˜๋Š” ์ปฌ๋Ÿผ๋งŒ ์กฐํšŒ : SELECT ์กฐํšŒํ•  ์ปฌ๋Ÿผ FROM ํ…Œ์ด๋ธ”๋ช…;

SELECT DEPTNO FROM dept;
dept ํ…Œ์ด๋ธ”์— ์žˆ๋Š” DEPTNO ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐ ์กฐํšŒ

๐ŸŸ (3) ์ปฌ๋Ÿผ๋ช… ํ™•์ธ

ํ…Œ์ด๋ธ” ์ปฌ๋Ÿผ ๊ตฌ์กฐ ํ™•์ธ : desc ํ…Œ์ด๋ธ”๋ช…;
=> ํ…Œ์ด๋ธ”์— ์žˆ๋Š” ๋ชจ๋“  ์ปฌ๋Ÿผ๋ช…์ด ๋‹ค ๋‚˜์˜ด!
desc dept;
dept ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ๋ช… ๋‹ค ๋‚˜์˜ด

๐Ÿ’ป

๐Ÿบ (4) ์ปฌ๋Ÿผ๋ช… ์ง€์ •ํ•˜๊ธฐ (์ปฌ๋Ÿผ ํ—ค๋”๊ฐ’ ์ง€์ •ํ•˜๊ธฐ)

SELECT ์ปฌ๋Ÿผ๋ช… AS "์ปฌ๋Ÿผ๋ช…์ƒˆ๋กœ" ,
์ปฌ๋Ÿผ๋ช… "์ปฌ๋Ÿผ๋ช…์ƒˆ๋กœ" ,
์ปฌ๋Ÿผ๋ช… ์ปฌ๋Ÿผ๋ช…๋ณ„์นญ์ƒˆ๋กœ ,
... FROM ํ…Œ์ด๋ธ”๋ช…;

SELECT
'ABC' AS "์•ŒํŒŒ๋ฒณ",
'๋ฌธ์ž์—ด' "ํ•œ๊ธ€๋ฌธ์ž์—ด",
550 STD_PAY,
deptno deptno_new,
LOC USA_location
FROM dept;

๐Ÿ’ป

๐Ÿค (5) ์ค‘๋ณต์ œ๊ฑฐํ•˜๊ณ  ์กฐํšŒํ•˜๊ธฐ : DISTINCT

DISTINCT(์ปฌ๋Ÿผ๋ช…)
SELECT DISTINCT job from emp; --job ์ปฌ๋Ÿผ ์ค‘๋ณต์ œ๊ฑฐ ์กฐํšŒ!

  • ์ปฌ๋Ÿผ ๋ถ™์ด๊ธฐ (์—ฐ๊ฒฐ ์—ฐ์‚ฐ์ž) : ||
    ์ปฌ๋Ÿผ๋ช… || ์ปฌ๋Ÿผ๋ช… ex)์ž๋ฐ” name + " " + id

์˜†์— ์ปฌ๋Ÿผ์ด ํ•˜๋‚˜ ๋” ๋ถ™์œผ๋ฉด์„œ ์ปฌ๋Ÿผ๋ช…์€ sal_description_per_ename
ename || '์€(๋Š”)' || sal || '๋ฅผ ๋ฐ›์Šต๋‹ˆ๋‹ค' ๋กœ ๋‚ด์šฉ์ด ํ‘œ์‹œ๋จ
SELECT ename ์ด๋ฆ„, sal ๊ธ‰์—ฌ,
ename || '์€(๋Š”)' || sal || '๋ฅผ ๋ฐ›์Šต๋‹ˆ๋‹ค' sal_description_per_ename
FROM emp;

๐Ÿ’ป

๐ŸŒญ (6) WHERE ์ ˆ : ์กฐ๊ฑด์ ˆ -> ์›ํ•˜๋Š” ๋ฐ์ดํ„ฐ๋งŒ ๊ฐ€์ ธ์˜ค๊ธฐ

SELECT ์ปฌ๋Ÿผ๋ช…
FROM ํ…Œ์ด๋ธ”๋ช…
WHERE ์›ํ•˜๋Š” ์กฐ๊ฑด

--์ง์›์ •๋ณดํ…Œ์ด๋ธ”์—์„œ ์ง์—…์ด 'SALESMAN'์ธ ์ง์›๋“ค์˜ ์‚ฌ๋ฒˆ, ์ด๋ฆ„, ์ง์—…๋ช… ์กฐํšŒํ•˜์‹œ์˜ค.
SELECT empno, ename, job
FROM emp
WHERE job = 'SALESMAN';

๐Ÿ’ป

๐Ÿ™ (7) ์กฐ๊ฑด ์—ฐ์‚ฐ์ž

1) ๋ณตํ•ฉ์กฐ๊ฑด = AND OR

2) ๋ฒ”์œ„๊ฐ’๋น„๊ต = BETWEEN a AND b : a์™€ b์‚ฌ์ด ๋ฒ”์œ„
===> a์ด์ƒ b์ดํ•˜

3) ํŠน์ •์กฐ๊ฑด๊ฐ’ = ์ปฌ๋Ÿผ๋ช… IN(10, 30, 40) == deptno = 10 OR deptno = 30 OR deptno = 40
=> 10์ด๊ฑฐ๋‚˜ 30์ด๊ฑฐ๋‚˜ 40 (OR)
์•„๋‹ˆ๋‹ค ์กฐ๊ฑด = ์ปฌ๋Ÿผ๋ช… NOT IN

--emp2 ํ…Œ์ด๋ธ”์—์„œ ์ทจ๋ฏธ๊ฐ€ Climb ์ด๊ฑฐ๋‚˜ Drinking ์ธ ์ง์›๋“ค ์ •๋ณด ์กฐํšŒ
SELECT *
FROM emp2
WHERE hobby IN ('Climb' , 'Drinking');

๐Ÿ’ป

4) ๋„ ์—ฌ๋ถ€ ๊ฒ€์ƒ‰ = ์ปฌ๋Ÿผ๋ช… IS NULL
์ปฌ๋Ÿผ๋ช… IS NOT NULL

--emp2 ํ…Œ์ด๋ธ”์—์„œ ์ทจ๋ฏธ๊ฐ€ Climb, Drinking ์ด ์•„๋‹Œ ์ง์›๋“ค ์ •๋ณด ์กฐํšŒ (NULL์ธ๊ฒƒ๋„ ์กฐํšŒ!)
SELECT *
FROM emp2
WHERE hobby NOT IN ('Climb' , 'Drinking') OR hobby IS NULL;

๐Ÿ’ป

5) ํŒจํ„ด์กฐ๊ฑด๊ฒ€์ƒ‰ = ์ปฌ๋Ÿผ๋ช… LIKE(%, )
% : ์•„๋ฌด๊ธ€์ž ์•„๋ฌด๊ฐฏ์ˆ˜ 0~n๊ฐœ
: _ํ•œ๊ฐœ๊ฐ€ ํ•œ์ž๋ฆฌ์ˆ˜

SELECT *
FROM emp
WHERE ename LIKE '%M%'; --์ด๋ฆ„์— M์•ŒํŒŒ๋ฒณ์ด ๋“ค์–ด๊ฐ€๋Š” ์‚ฌ๋žŒ

๐Ÿ’ป

SELECT *
FROM emp
WHERE ename LIKE '_M___'; --์ด๋ฆ„์ด ์ด ๋‹ค์„ฏ๊ธ€์ž์— ๋‘๋ฒˆ์งธ๊ฐ€ M์ธ ์‚ฌ๋žŒ

๐Ÿ’ป

๐Ÿ• (8) ์ •๋ ฌ ORDER BY

SELECT ์ปฌ๋Ÿผ๋ช…
FROM ํ…Œ์ด๋ธ”๋ช…
WHERE ์กฐ๊ฑด
ORDER BY ์ปฌ๋Ÿผ๋ช… ASC(์˜ค๋ฆ„์ฐจ์ˆœ):๊ธฐ๋ณธ๊ฐ’ / DESC(๋‚ด๋ฆผ์ฐจ์ˆœ)
ORDER BY ์ปฌ๋Ÿผ๋ช…, ์ปฌ๋Ÿผ๋ช…

--ํ•™์ƒ๋“ค ํ‚ค ์ˆœ์œผ๋กœ ์ •๋ ฌํ•˜๊ธฐ
SELECT * FROM student
--ORDER BY height ASC; --ASC ์˜ค๋ฆ„์ฐจ์ˆœ => (๊ธฐ๋ณธ๊ฐ’)์ด๋ผ ๊ตณ์ด ์•ˆ์จ์ค˜๋„ ๋จ!
ORDER BY height DESC, weight DESC; --DESC ๋‚ด๋ฆผ์ฐจ์ˆœ => ํ‚ค ๋จผ์ € ์ •๋ ฌํ•˜๊ณ  ๋ชธ๋ฌด๊ฒŒ ์ •๋ ฌ!

๐Ÿ’ป

--์ˆซ์ž์œ„์น˜๊ฐ’์œผ๋กœ ์ •๋ ฌํ•˜๊ธฐ
SELECT studno, id, grade -- 1,2,3 ์ˆœ์„œ๋Œ€๋กœ ORDER BY ์ •๋ ฌ ๊ธฐ์ค€์œผ๋กœ ํ™œ์šฉ
FROM student
ORDER BY 3 DESC, 1, 2; --grade๋จผ์ € ์ •๋ ฌ, ์ดํ›„์— studno, id ์ˆœ์œผ๋กœ ์ •๋ ฌ!

๐Ÿฐ (9) ์ง‘ํ•ฉ ์—ฐ์‚ฐ์ž

UNION ํ•ฉ์ง‘ํ•ฉ (์ค‘๋ณต์ œ๊ฑฐ)
UNION ALL ํ•ฉ์ง‘ํ•ฉ (์ค‘๋ณต์ œ๊ฑฐ์•ˆํ•จ)
X INTERSECT ๊ต์ง‘ํ•ฉ : ๊ฒน์น˜๋Š” ๋ถ€๋ถ„!
X MINUS ์ฐจ์ง‘ํ•ฉ A-B: ๊ฒน์น˜๋Š” ๋ถ€๋ถ„ ์ œ์™ธํ•œ A๋ถ€๋ถ„

1) SELECT ์ปฌ๋Ÿผ ๊ฐœ์ˆ˜ ๋™์ผ
2) SELECT ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐํ˜• ๋™์ผ
3) ์ปฌ๋Ÿผ๋ช…์€ ๋‹ฌ๋ผ๋„ OK

--101๋ฒˆ ํ•™๊ณผ์— ์†ํ•ด ์žˆ๋Š” ํ•™์ƒ๊ณผ ๊ต์ˆ˜์˜ ์ •๋ณด๋ฅผ ์กฐํšŒํ•˜๊ณ  ์‹ถ๋‹ค.
--ํ•™๋ฒˆ/๊ต์ˆ˜๋ฒˆํ˜ธ ์ด๋ฆ„ ์•„์ด๋””
SELECT studno "ํ•™๋ฒˆ/๊ต์ˆ˜๋ฒˆํ˜ธ", name ์ด๋ฆ„, id ์•„์ด๋””
FROM student
WHERE deptno1 = 101
UNION ALL
SELECT profno, name, id
FROM professor
WHERE deptno = 101;

๐Ÿ’ป

profile
๋’ค์ฃฝ๋ฐ•์ฃฝ ๋ฒจ๋กœ๊ทธ

0๊ฐœ์˜ ๋Œ“๊ธ€