SQL

SELECT๋ฌธ ๊ธฐ์ดˆ

yellowtrust 2023. 3. 26. 14:39

1.  SELECT๋ฌธ

๐Ÿ‘† SELECT๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ์ž…๋ ฅํ•œ ์ž๋ฃŒ ์กฐํšŒ

  - SELECT: ์ถœ๋ ฅ๊ฒฐ๊ณผ ์นผ๋Ÿผ ๋‚˜์—ด

  - FROM: ์กฐํšŒ ๋Œ€์ƒ ํ…Œ์ด๋ธ” ์ •์˜

  - WHERE: ์กฐ๊ฑด

  - ALL/DISTINCT: ์ค‘๋ณต๋œ ๋ฐ์ดํ„ฐ๋„ ์ถœ๋ ฅ(์ƒ๋žต๊ฐ€๋Šฅ)/์ค‘๋ณต์ œ๊ฑฐ๋œ ๋ฐ์ดํ„ฐ ์ถœ๋ ฅ

  - ์ˆซ์žํ˜•์€ ๊ทธ๋Œ€๋กœ ํ‘œํ˜„, ๋ฌธ์žํ˜•์œ " ์‚ฌ์šฉ

 

๐ŸŒŸ ์‹คํ–‰์ˆœ์„œ: FROM -> WHERE -> SELECT

๐ŸŒŸ ์˜ˆ์ œ)

SELECT ENAME, JOB, SAL
  FROM EMP
 WHERE SAL >= 1500;

๐ŸŒŸ  * : ์ „์ฒด ์นผ๋Ÿผ์„ ์ถœ๋ ฅ

SELECT *
  FROM EMP;

๐Ÿ‘† ALIAS ๋ถ€์—ฌ

SELECT column1 AS alias1, column2 AS "alias2", ...
  FROM table;

- alias1: ์นผ๋Ÿผ์— ๋Œ€ํ•œ ๋ณ„๋ช… ์ •์˜

- "alias2": ๊ณต๋ฐฑ, ์ˆซ์ž, ํŠน์ˆ˜๋ฌธ์ž, ๋Œ€์†Œ๋ฌธ์ž ํ‘œํ˜„์€ ""์‚ฌ์šฉ

๐ŸŒŸ ์˜ˆ์ œ)

SELECT EMPNO AS ID, ENAME AS NAME, SAL AS "201601"
	FROM EMP;

โ–ถ ์นผ๋Ÿผ์ด ์ง€์ •๋œ alias ๋ช…์œผ๋กœ ์ถœ๋ ฅ๋จ(PostgreSQL์€ ๊ธฐ๋ณธ ์†Œ๋ฌธ์ž, Oracle์€ ๊ธฐ๋ณธ ๋Œ€๋ฌธ์ž๋กœ ์ถœ๋ ฅ)

 

๐Ÿ‘† ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž์™€ ํ•ฉ์„ฑ ์—ฐ์‚ฐ์ž

- ์ˆ˜ํ•™์—ฐ์‚ฐ ์ˆœ์„œ์™€ ๊ฐ™์Œ

- ๋ฌธ์ž์™€ ๋ฌธ์ž ์—ฐ๊ฒฐ์˜ ๊ฒฝ์šฐ ||์— ์˜ํ•ด ์ด์–ด์ง

- CONCAT(string1, string2) ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ํ•ฉ์„ฑํ•  ์ˆ˜ ์žˆ์Œ

๐ŸŒŸ ์˜ˆ์ œ)

 
select sal*1.5 as sal, ename||'['||job||']' as ename
 from emp;

๐Ÿ‘† WHERE์ ˆ ํ™œ์šฉ

๐ŸŒŸ ๋น„๊ต์—ฐ์‚ฐ์ž

SELECT *
	FROM EMP
  WHERE SAL >= 3000;

๐ŸŒŸ ๋ถ€์ •์—ฐ์‚ฐ์ž

SELECT *
	FROM EMP
  WHERE NOT DEPTNO = 30

๐ŸŒŸ ๋…ผ๋ฆฌ์—ฐ์‚ฐ์ž(AND, OR)

SELECT *
	FROM EMP
  WHERE DEPTNO = 10 AND SAL >= 3000;

๐ŸŒŸ SQL์—ฐ์‚ฐ์ž_IN

- IN์กฐ๊ฑด์€ = OR์กฐ๊ฑด์œผ๋กœ ๋น„๊ต ์ˆ˜ํ–‰

- ex) ๋ถ€์„œ๋ฒˆํ˜ธ๊ฐ€ 10๋˜๋Š” 20๋˜๋Š” 30์ธ ์กฐ๊ฑด

SELECT *
	FROM EMP
  WHERE DEPTNO IN (10,20,30);

๐ŸŒŸ SQL์—ฐ์‚ฐ์ž_BETWEEN

- ex) ๊ธ‰์—ฌ๊ฐ€ 2000์ด์ƒ 3000์ดํ•˜์ธ ์กฐ๊ฑด

SELECT *
	FROM EMP
  WHERE SAL BETWEEN 2000 AND 3000;

๐ŸŒŸ SQL์—ฐ์‚ฐ์ž_LIKE

- %: ๋ชจ๋“  ๋ฌธ์ž์—ด, _: ํ•œ๊ธ€์ž์˜ ๋ชจ๋“  ๋ฌธ์ž์—ด

- ex) ์‚ฌ์›๋ช…์ด A๋กœ ์‹œ์ž‘ํ•˜๋Š” ์กฐ๊ฑด: ENAME LIKE 'A%'

- ESCAPE: ํŒจํ„ด์—์„œ ์‚ฌ์šฉํ•˜๋Š” ํŠน์ˆ˜๋ฌธ์ž๋ฅผ '๋ฌธ์ž'๋กœ ์ธ์‹์‹œํ‚ฌ ๋•Œ ์‚ฌ์šฉ

- ex) ENAME LIKE '%\%' ESCAPE '\' : ๋’ค์— %๋Š” ์ง„์งœ ํผ์„ผํŠธ ๋ฌธ์ž๋ฅผ ์˜๋ฏธํ•˜๊ฒŒ๋จ

select *
	from emp
where ename like '_A%';
=> ์‚ฌ์›๋ช…์˜ ๋‘˜์งธ ์ž๋ฆฌ์— A๊ฐ€ ๋“ค์–ด๊ฐ€๋Š” ์‚ฌ์›์ •๋ณด

select *
	from emp
where ename like '%AR%';
=> ์‚ฌ์›๋ช…์— AR์ด ํฌํ•จ๋˜๋Š” ์‚ฌ์›์ •๋ณด