[정처기실기] SQL 주요 작성 방법 - 간단 요약

[정처기] SQL 주요 작성 방법

목차

DDL

CREATE - 테이블 생성

CREATE TABLE 테이블명

CREATE -인덱스 생성

CREATE INDEX 인덱스명 

ON 테이블명(컬럼 ASC, 컬럼 DESC)

CREATE - 뷰 생성

CREATE (OR REPLACE)  VIEW 뷰명

AS

SELECT 문

CREATE - 트리거 생성

CREATE TRIGGER 트리거명

ATFER 조건

BEGIN

 조건

 SET

 조건

END

 

CREATE - 프로시저 생성

CREATE (OR REPLACE) PROCEDURE 프로시저명

IS

 변수 처리부

BEGIN

 처리내용부

EXCEPTION

 에외 처리부

END

 

CREATE - 파티션 생성

PARTITION BY LIST (컬럼명) : 목록 분할

PARTITION BY RANGE (컬럼명) : 범위 분할

 

DROP - 테이블 삭제

DROP TABLE 테이블명;

DROP - 인덱스 삭제

DROP INDEX 인덱스명

DROP - 뷰 삭제

DROP VIEW 뷰명

 

ALTER - 속성(컬럼) 추가, 변경, 삭제

ADD

ALTER TABLE 테이블명 ADD 컬럼명

예) ALTER TABLE 회원 ADD 주문수 VARCHAR(200) NULL;

마지막 NULL은 NULL값을 포함한다는 의미입니다.

포함하지 않으려면 NOT NULL을 쓰면 됩니다.

MODIFY

ALTER TABLE 테이블명 MODIFY NAME VARCHAR(10);

DROP

ALTER TAVLE 테이블명 DROP 컬럼명;

 

TRUNCATE - 초기화

TRUNCATE는 표의 모든 틀은 그대로 두고 내용만 삭제하는 것입니다.

TRUNCATE TABLE 테이블명 

DCL

GRANT : 권한 분여

GTRANT 줄 권한

ON 테이블 명

TO 권한 줄 사용자명

* 전부라고 하면 PUBLIC

* 권한을 주는 권한까지 주려면 WITH GRANT OPTION;

 

예시)

GRANT SELECT

ON TABLE1 

TO USER1

REVOKE : 권한 제거

REVOKE 줄 권한(SELECT 등)

ON 테이블명

FROM USER1 

* CASACADE : 받은 권한으로 다른 사용자에게 준 권한까지 삭제됨

REVOKE는 기본적으로 CASCADE가 적용됩니다.

 

REVOKE SELECT

ON TABLE1

FROM USER1;

 

DML

DML은 모두 특정 행이나 셀을 지정하여 조작하게 되므로 테이블과 컬럼명이 모두 들어가야합니다.

INSERT

INSERT INTO 테이블(컬럼명1, 컬럼명2) VALUES (값1, 값2);

모든 컬럼에 순서대로 넣을 때는 테이블명 뒤에 바로 VALUE를 붙임

INSERT INTO 회원 VALUES ('002','REE-1',1)

 

서브쿼리를 써서 특정 조건을 지정할 수도 있음

INSERT INTO 회원 SELECT * FROM 주문 WHERE 구매수량 IS NOT NULL

주문 테이블 구매수량이 NULL이 아닌 데이터를 회원 테이블에 삽입하는 문

 

UPDATE

UPDATE 테이블명

SET 바꿀 내용

WHERE 바꿀 조건

 

UPDATE 회원

SET

 NAME = "홍길동"

 QUANTITY = 3

WHERE NAME ="훙길동"

 

서브쿼리로 작성할 수 있음

SET

 NAME = "홍길동"

 QUANTITY = 3

WHERE NAME ="훙길동" IN

  SELECT 고객번호

  FROM 회원

  WHERE 등급명 LIKE 'GOLD%'

 

INT 형의 자료는 연산도 가능함.

SET

 GRADE = GRADE+1

 

DELETE - 특정 행이나 값 삭제

DELETE FROM 테이블 WHERE 조건(컬럼 = 조건)

SELECT

조건이 없는 상태에선 SELECT 컬럼 FROM 테이블명만하면됩니다.

하지만 일반적으로 조건을 걸기 때문에 아래와 같이 씁니다.

 

SELECT 컬럼1, 컬럼2

FROM 테이블명

WHERE 조건

SELECT - WHERE

BETWEEN 컬럼 조건 1 AND  조건1

BETWEEN은 범위 포함부터~포함까지로 표현됩니다.

WHERE 점수 BETWEEN 30 AND 40 : 점수가 30부터 40까지

 

DISTINCT

중복을 제거하고 싶을 때 쓰는 명령어입니다. 컬럼 앞에 씁니다.

SELECT DISTINCT 이름 FROM 학생 

 

SELECT 문에서 AS

AS는 별칭으로 컬럼명을 지정하는 것입니다.

SELECT DISTINCT 이름 AS 이르음 FROM 학생

 

COUNT

컬럼 내의 튜플(행)의 갯수를 찾을 떄 씁니다.

SELECT COUNT(*) FROM 학생 : NULL 포함

 

NULL을 가져오지 않는 경우는?

SELECT COUNT(*) FROM 학생 : NULL 포함

SELECT COUNT(학과) FROM 학생 : NULL 미포함

SELECT에서 선택하는 컬럼에 NULL이 포함되어있는지에 따라, 

만약 NULL이 포함된 컬럼을 지목하면 NULL을 제외하고 출력합니다.

집합 - UNION ALL

뒤에 언급될 JOIN과 헷갈릴 수 있는데, UNION은 단순 더하기로 보면 됩니다.

 

A테이블에 10개 B테이블에 10개 레코드가 있을 경우,

SELECT 이름 FROM A

UNION ALL

SELECT 이름 FROM B

위 쿼리의 결과는 20개입니다.

집합 - UNION

중복을 제거한 결과가 나오게 됩니다.

위의 예시에서 A와 B에서 총 5개의 중복이있다면,

15개의 결과가 나오게 됩니다.

 

INTERSECT

교집합인 부분만 보기 위해 사용하는 명령입니다.

SELECT 이름 FROM A

INTERSECT

SELECT 이름 FROM B

A와 B에 동시에 존재하는 레코드만 나오게 됩니다.

ORDER BY

출력되는 컬럼의 오름차순, 내림차순을 결정하는 명령어입니다.

쿼리 마지막에 쓰입니다.

SELECT 학생번호 FROM 학생

WHERE 학년 = '4'

ORDER BY 학생번호 ASC

학생 번호가 오름차순으로 정렬되어 나옵니다.

LIKE

문자열을 검색하여 가져올 때 쓰는 명령어입니다.

LIKE '이%' 이로 시작하는 단어

LIKE '이_' 이로 시작하는데, 단 한글자만 붙는 단어

WHERE 이름 LIKE '이%'이러한 형식으로 씁니다.

연산자명	역할	예시	의미
%	임의의 문자열	'abc%'	"abc"로 시작하는 모든 문자열
_	임의의 단일 문자	'a_e'	세 번째 문자가 "e"인 세 글자 단어
[]	문자 클래스	'a[bcd]e'	"a", "b", "c", "d" 중 하나로 시작하고 "e"로 끝나는 세 글자의 단어
[^]	부정 문자 클래스	'a[^bcd]e'	"a"로 시작하고 "e"로 끝나는 세 글자의 단어 중에서 두 번째 문자가 "b", "c", "d"가 아닌 경우
ESCAPE	이스케이프 문자	'100\%' ESCAPE '\'	"100%"과 일치하는 문자열을 찾습니다. 이때 이스케이프 문자로 백슬래시(\)를 사용합니다.

JOIN

JOIN을 쓰지 않고 INNER JOIN을 쓸 수도 있습니다.

SELECT 이름, 점수

FROM R1,(콤마가 INNER JOIN을 말함) R2

WHERE(ON과 같음) R1.이름= R2.이름

AND R1. 점수 >= 90

* 여기에서 WHERE문 이하가 없으면 카디시안 곱이 되어 모두  CROSS 조인이 됩니다.

INNER JOIN

LEFT OUTTER JOIN

작성한 쿼리의 왼쪽 테이블을 기준으로 JOIN하는 것입니다.

왼쪽에 있는 테이블을 기준으로 오른쪽테이블이 가지고 있는 것을 가져옵니다.

R1				R2
A	B			A	B
1	2	R1의 2와 동일한 R2 의 2는 두 개가 연결됨		2	3
4	2	R1의 2와 동일한 R2의 2는 두 개가 연결됨		2	4
7	8	R1의 2와 동일한 R2의 8은 없음		6	7

위 테이블에서 B를 기준으로 LEFT OUTTER JOIN을 하면 아래와 같은 결과가 나옵니다.

A	B	C
1	2	3
1	2	4
4	2	3
4	2	4
7	8	NULL

 

RIGHT OUTTER JOIN

이름 그대로 오른쪽 테이블을 기준으로 JOIN하게 됩니다.

일단 OUTTER 조인의 특징 대로 위와 동일한 형태를 먼저 구성하고,

오른쪽에 있는 것만 해주면 됩니다.

A	B	C
1	2	3
1	2	4
4	2	3
4	2	4
NULL	6	7

 

앞선 LEFT 조인과 비교하면 NULL 값의 기준이 다르다는 것을 알 수있습니다.

 

FULL OTTER JOIN

완전 외부조인은 두 테이블 모두를 표현하는 것입니다. 위 예시처럼 NULL값의 기준이 양쪽에 모두 있습니다.

A	B	C
1	2	3
1	2	4
4	2	3
4	2	4
7	8	NULL
NULL	6	7

INNER JOIN

두 테이블의 필드가 일치하는 레코드만 결합하는 조인입니다. 쉽게 말하면, 둘다 동시에 존재하는 데이터로만 출력하는 것입니다.

SELECT 이름

FROM 테이블1 (INNER) JOIN 테이블 2

ON 테이블 1. A = 테이블2.A 

 

CROSS JOIN

열(필드)과 행(레코드)를 모두 곱하는 것이 됩니다.

R1				R2
A	B	속성은 4개가 됩니다.		A	B
1	2	1223 1224 1267		2	3
4	2	4223 4224 4267		2	4
				6	7

IN

IN 연산자는 WHERE 뒤에 쓰이면서 두개이상의 조건을 표기합니다.

SELECT A,B 

FROM 학생

WHERE 학년 IN (3,4)

 

반대로 포함하고 싶지 않은 항목들도 나열하여 쓸 수 있습니다.

SELECT A, B

FROM 직원

WHERE 부서 NOT IN ('개발', '회계')

NULL 값은 비교 못합니다.

SUBQUEARY

SELECT 문안에서 다른 SELECT를 쓰면서 쓰는 방식입니다.

JOIN을 쓰지 않아도 다른 테이블의 일부 레코드를 가져올 수 있습니다. VIEW와 같은 방식입니다.

SELECT 이름

FROM 학생

WHERE

 (SELECT 성적 FORM 학적 WHERE 성적 >= 10)

다중행 연자

기호	설명	예시	의미
IN	서브쿼리의 결과 집합에 값이 포함됨	SELECT * FROM products WHERE product_id IN ( SELECT product_idFROM orders WHERE customer_id = 123);	주어진 값이 서브쿼리의 결과 집합에 포함되는지 확인
ANY	서브쿼리의 결과 집합의 모든 값 중 하나라도 조건을 만족함	SELECT * FROM products WHERE price > ANY ( SELECT price FROM competitor_products WHERE category = 'Electronics');	주어진 조건을 만족하는지 여부를 확인
ALL	서브쿼리의 결과 집합의 모든 값이 조건을 만족함	SELECT * FROM products WHERE price > ALL (SELECT price FROM competitor_products WHERE category = 'Electronics');	주어진 조건을 모두 만족하는지 여부를 확인
EXISTS	서브쿼리의 결과 집합이 비어있지 않음	SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id);	서브쿼리의 결과 집합이 비어있지 않은지 확인

다중행 연산자 - ANY

서브쿼리 조건 중 어떤 값이라도 주어진 조건과 비교하여 해당되거나 해당되지 않는 것을 찾는 연산자입니다.

SELECT *

FROM 학생

WHERE 수학 점수 > ANY(

SELECT 수학점수

FROM 성적

WHERE 전공과 = '이과')

위 쿼리와 같이 전공과가 이과인 학생의 수학점수보다 하나라도 높은 수학 점수를 가지는 레코드를 찾는 방법입니다.

이과 학생이 두명밖에 없고 수학점수가 80, 95면, 둘 중 하나만 만족시키면 됩니다.

다중행 연산자 - ALL

서브쿼리 조건에 모두 해당하는 레코드를 찾는 연산자입니다.

 

SELECT *

FROM 학생

WHERE 수학 점수 > ALL(

SELECT 수학점수

FROM 성적

WHERE 전공과 = '이과')

위 쿼리와 같이 전공과가 이과인 학생의 수학점수보다 하나라도 높은 수학 점수를 가지는 레코드를 찾는 방법입니다.

이과 학생이 두명밖에 없고 수학점수가 80, 95면, 둘 다 만족시키야 됩니다.

 

다중행 연산자 - EXISTS

EXISTS는 단순히 비어있지않은 즉, NULL이 없는 것을 제외하고 출력합니다.

IN보다 효율이 좋습니다.

행이 없는 것을 찾기 위해선 NOT EXISTS

GROUP BY

GROUP BY는 동일한 값을 가지는 행들을 묶어서 보는 것입니다. 아래 테이블이 R1이라고 할때 그룹 바이를 하면 어떻게 되는지 보겠습니다.

A	B
서울	5
경기	10
경기	10
서울	5
강원
충청	10

SELECT A, SUM(B)

FROM R1

GROUP BY A;

이렇게 HAVING 조건이 없이 하면 기본적으로 NULL을 포함합니다.

A	SUM(B)
서울	10
경기	20
강원	NULL
충청	10

 

HAVING

GROUP BY와 함께 집합연산자를 이용합니다.

집계 함수	설명	예시	결과
COUNT	그룹 내 행의 수를 세어 반환	SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;	각 부서별 직원 수
SUM	그룹 내 행의 합을 계산하여 반환	SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;	각 부서별 급여 총액
AVG	그룹 내 행의 평균을 계산하여 반환	SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;	각 부서별 평균 급여
MAX	그룹 내 행의 최댓값을 반환	SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;	각 부서별 최고 급여
MIN	그룹 내 행의 최솟값을 반환	SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;	각 부서별 최저 급여
GROUP_CONCAT	그룹 내 행의 특정 열 값을 문자열로 연결하여 반환	SELECT department_id, GROUP_CONCAT(employee_name) FROM employees GROUP BY department_id;	각 부서별 직원 이름을 쉼표로 구분하여 연결한 문자열

SELECT A

FROM 직원

GROUP BY 부서

HAVING COUNT(*) >=2;

카테시안 곱(Cartesian product)이란?

카테시안 곱(Cartesian product)은 관계형 데이터베이스에서 두 개 이상의 테이블을 조인할 때 발생하는 결과 중 하나입니다. 카테시안 곱은 모든 행을 다른 테이블의 모든 행과 결합하여 생성되는 행의 조합을 나타냅니다.

 

간단한 예를 들어보겠습니다. 두 개의 테이블 A와 B가 있다고 가정해보겠습니다. 각각의 테이블이 다음과 같은 데이터를 가지고 있다고 합시다: