[SQL] 문법정리

 

1. 쿼리(Query)문이란? 쿼리는 질의를 의미하죠. 데이터베이스에 명령을 내리는 것을 의미합니다.

• 테이블: orders라는 엑셀 시트명 보이시죠? 테이블은 데이터가 담긴 엑셀 시트와 동일합니다. 이런 형태의 값이 데이터베이스에 담기면, orders라는 이름의 테이블이 되겠죠.
• 필드: order_no, created_at, course_title, user_id, payment_method, email 각각이 필드입니다.
• Where 절은, Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것을 의미해요.
• Group by는 동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것을 의미합니다.
• Order by를 사용하면 한 번에 정렬할 수 있어요. ( 오름차순 ASC, 내림차순 DESC)

 

- 일치하는 문자열 가져오기 : like

/* 둘중 하나라도 만족하는 DATA 검색 */
SELECT * FROM 테이블 WHERE (컬럼 LIKE '%a1%' OR 컬럼 LIKE '%b2%')

/* 둘다 만족하는 DATA 검색 */
SELECT * FROM 테이블 WHERE (컬럼 LIKE '%a1%'  AND 컬럼 LIKE '%b2%')

 

- 날짜 범위 정하기 : betteen ... and ...;

/* 최근 3개월 */ 
select * from TABLE 
and regdate BETWEEN DATE_ADD(NOW(), INTERVAL -3 MONTH) AND NOW();

/* 위 부분을 더 짧게 수정 */
and regdate >= DATE_ADD(NOW(), INTERVAL -3 MONTH)

 

- 중복 데이터는 제외하고 가져오기: Distinct

select distinct(payment_method) from orders;

- 몇 개인지 숫자 세보기: Count

select count(*) from orders

 

 

2. Join이란? 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.

• Join의 종류: Left Join, Inner Join
• left join :  첫 번째 테이블을 기준으로, 두번째 테이블을 조합하는 JOIN ( null 값도 함께 join , 없는 정보를 통하여 통계를 내고 싶을때 left join을 이용)
• inner join: ON절과 함께 사용되며, 조건을 만족하는 데이터 만을 가져옵니다. ( null값이 없는 부분만 join)
• 그외 (outer join, left outer join)

 

Q) enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

• 조인해야 하는 테이블: enrolleds, enrolleds_detail
• 조인하는 필드: enrolled_id

SELECT e.enrolled_id , e.user_id , count(e.enrolled_id) as max_count FROM enrolleds e 
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id 
where ed.done = 1
group by e.enrolled_id 
order by max_count desc

 

 

3. Subquery 쿼리 안의 쿼리라는 의미입니다.

• where 필드명 in (subquery) 
• select 필드명, 필드명, (subquery) from ..
• From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
• With절
• case문 : 경우에 따라 원하는 값을 새 필드에 출력
  SELECT pu.user_id, pu.point, (case when pu.point > 10000 then '잘 하고 있어요!' else '조금만 더 파이팅!' end ) as msg from point_users pu

---

서브쿼리가 사용 가능한 곳

1. SELECT 절

2. FROM 절

3. WHERE 절

4. HAVING 절

5. ORDER BY 절

6. INSERT 문의 VALUES 절

7. UPDATE 문의 SET 절

 

SELECT 절에 사용하는 서브쿼리

 

스칼라 서브쿼리라고도 합니다.

스칼라 서브쿼리는 한 행, 한 컬럼만을 반환하는 서브쿼리를 말합니다.

SELECT T1.C1, (SELECT AVG(T2.C1) FROM T2 T2)
FROM T1 T1;

 

 

FROM 절에 사용하는 서브쿼리

 

인라인 뷰 라고 합니다.

기본적으로 FROM 절에는 테이블 명이 오도록 되어있습니다. 그런데 서브쿼리가 FROM 절에 사용되면 동적으로 생성된 테이블인 것처럼 사용할 수 있습니다.

인라인 뷰는 SQL 문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않습니다.

인라인 뷰는 동적으로 조인 방식을 사용하는 것과 같습니다.

SELECT T1.C1, T2.C1, T2.C2
FROM T1 T1,
(SELECT C1, C2 FROM T2) T2
WHERE T1.C1 = T2.C1;

 

HAVING 절에서 사용하는 서브쿼리

 

그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해 사용합니다.

SELECT T1.C1, T2.C1, T2.C2
FROM T1 T1, T2 T2
WHERE T1.C1 = T2.C1
GROUP BY T1.C1, T2.C1, T2.C2
HAVING AVG(T1.C1) < (SELECT AVG(C1)
FROM T2 );

 

UPDATE 문의 SET 절에서 사용하기

UPDATE T1 T1
SET T1.C1 = (SELECT T2.C1 FROM T2 T2 WHERE T2.C1 = T1.C1);

 

INSERT 문의 VALUES 절에서 사용하기

INSERT INTO T1 (C1, C2, C3) SELECT C1, C2, C3 FROM T2;

INSERT INTO T1 (C1, C2, C3) VALUES ((SELECT C1 FROM T2), (SELECT C2 FROM T2), (SELECT C3 FROM T2));

---

4. 유용한 문법

 

- SUBSTRING_INDEX 

/* @기준으로 뒤 부분 */ select user_id, email, SUBSTRING_INDEX(email, '@', -1) FROM users;

- SUBSTRING

/* 1번부터 10글자 나오기 */ SELECT order_no, SUBSTRING(created_at,1,10) as date FROM orders;

/* 일별로 몇개씩 주 */ SELECT SUBSTRING(created_at,1,10) as date, count(*) FROM orders group by date;

 

 

 

5. 기타

 

- 제약 조건(constraint)

제약 조건(constraint)이란 데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사 규칙을 의미합니다.

이러한 제약 조건은 CREATE 문으로 테이블을 생성할 때나, ALTER 문으로 필드를 추가할 때도 설정할 수도 있습니다.

 CREATE TABLE 문에서 사용할 수 있는 제약 조건은 다음과 같습니다.

 

1. NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 됩니다.

2. UNIQUE : 해당 필드는 서로 다른 값을 가져야만 합니다.

3. PRIMARY KEY : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.

4. FOREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만듭니다.

5. DEFAULT : 해당 필드의 기본값을 설정합니다.

 

 

- 데이터 유형

CHAR(s) 

- 고정 길이 문자열 정보

- s는 기본 길이 1바이트, 최대 길이 Oracle 2000바이트 SQL Server 8000바이트 

- s만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당도니 변수 값의 길이가 s보다 작을 경우에는 그 차이 길이만큼 공간으로 채워진다.

VARCHAR(2) 

- CHARACTER VARYING의 약자로 가변 길이 문자열 정보(Oracle은 VARCHAR2 로 표현, SQL Server는 VARCHAR로 표현)

- s는 최소 길이 1바이트, 최대 길이 Oracle 4000바이트, SQL Server 8000 바이트

- s만큼의 최대 길이를 갖지만 가변 길이로 조정이 되기 때문에 할당된 변수값의 바이트만 적용된다. 

출처: https://hyeonstorage.tistory.com/290 [개발이 하고 싶어요]