[데이터분석] 서비스 기획자가 알아두면 좋은 SQL 기초 지식 (SELECT, INSERT, DELETE, UPDATE 등)

데이터 설계 시 고려사항

데이터 베이스 설계 시 지켜야 할 데이터의 속성이 있다. 데이터는 기본적으로 무결성, 일관성, 회복, 보안, 효율성, 확장성 등이 해당한다. 

◼ 무결성 : 삽입, 삭제, 갱신 등의 연산 후에도 DB에 저장된 데이터가 정해진 제약조건을 항상 만족해야 한다.
◼ 일관성 : DB에 저장된 데이터들 사이나 특정 질의에 대한 응답이 처음부터 변함없이 일정해야 한다.
◼ 회복 : 시스템 장애 발생 시, 장애 발생 직전의 상태로 복구할 수 있어야 한다.
◼ 보안 : 외부 데이터 공격 혹은 노출, 변경으로부터 보호할 수 있어야 한다.
◼ 효율성 : 응답시간의 단축, 시스템의 생산성, 저장 공간의 최적화 등이 가능해야 한다.
◼ 데이터베이스 확장 : 데이터베이스 운영에 영향을 주지 않으면서 지속적으로 데이터를 추가할 수 있어야 한다.

 

SQL이란?

Structured Query Language로, 데이터베이스에서 데이터를 추출하고 조작하는 데에 사용하는 데이터 처리 언어이다.

 

SQL의 분류

1) DDL(Data Define Language, 데이터 정의어)

DDL은 스키마, 도메인, 테이블, 뷰, 인덱스 등을 정의하거나 변경 또는 삭제할 때 사용하는 언어이다.

논리적, 물리적 데이터 구조의 사상을 정의하고 삭제하는 데에 사용한다. 

 

명령어	기능
CREATE	스키마, 도메인, 테이블, 뷰, 인덱스를 정의한다.
ALTER	TABLE에 대한 정의를 변경하는데 사용한다.
DROP	스키마, 도메인, 테이블, 뷰, 인덱스를 삭제한다.

 

 

*가장 대중적으로 사용되는 TABLE을 기준으로 명령어 사용방법에 대해 작성했다. 이외의 도메인, 테이블, 뷰, 인덱스에 대해서 공부하려면 다른 블로그나 유튜브를 참조하면 좋겠다. SQL이나 데이터를 공부할 때, 필자가 가장 도움을 많이 받았던 방법은 '손으로 그려보고 작성해 보는 방법'이다.  아래 예시들을 문제만 보고 작성할 수 있다면 큰 도움이 될 것이다. 

 

 

① CREATE TABLE : 테이블을 정의하는 명령문

CREATE TABLE  테이블명
(
속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL],... ▶ 속성과 값의 조건을 정의한다. 
[, PRIMARY KEY(기본키_속성명,...)] ▶ 기본키로 사용할 속성 또는 속성의 집합 
[, UNIQUE(대체키_속성명,...)] ▶ 대체키로 사용할 속성 또는 속성의 집합, 중복된 값을 가질 수 없다. 
[, FOREIGN KEY(외래키_속성명,...)]
               REFERENCES 참조테이블(기본키_속성명,...)
               [ON DELETE 옵션]
               [ON UPDATE 옵션]
[, CONSTRAINT 제약조건명] [CHECK(조건식)]
);

→ 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본 값, NOT NULL 여부를 지정한다. (* NOT NULL : 모르는 값 혹은 적용할 수 없는 값)

 

 

FOREIGN KEY ~ REFERENCES ~ : 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정한다. 

 - ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테블에 취해야 할 사항을 지정한다.

 - ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에서 취해야 할 사항을 지정한다.

 

   ◽ NO ACTION : 참조 테이블에 변화가 있어도 기본 테이블에 아무런 조치를 취하지 않는다.

   ◽ CASCADE : 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경된다. 

   ◽ SET NULL : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 NULL로 변경한다.

   ◽ SET DEFAULT : 참조테이블에 변화가 있으면 기본테이블의 관련 튜플의 속성 값을 기본 값으로 변경한다.

 

 

CONSTRAINT : 제약조건의 이름을 지정한다. 이름지정이 필요 없다면 CHECK절에서 제약조건을 명시한다.

CHECK : 속성 값에 대한 제약 조건을 정의한다.

 

---

➕ 기본키와 대체키 (용어)

 

SQL 기본키와 외래키

 

 

 

 

💻 문제로 이해하는 CREATE TABLE 

✒ 예제
'이름'. '학번', '전공', '성별', '생년월일'로 구성된 <학생> 테이블을 정의하는 SQL을 작성하시오. 단 제약조건은 다음과 같다.
▪ '이름'은 NULL이 올 수 없고, '학번'은 기본키이다.
▪ '전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용된다.
▪ <학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만든다.
▪ <학과> 테이블에서 '학과코드'가 변경되면 전공 값도 같은 값으로 변경한다.
▪ '생년월일'은 1980-01-01 이후의 데이터만 저장할 수 있다.
▪ 제약조건의 이름은 '생년월일 제약'으로 한다.
▪ 각 속성의 데이터 타입은 적당하게 지정한다. 단 '성별'은 도메인 'SEX'를 사용한다.

 

 

✒ 답안
CREATE TABLE 학생  
(
이름 VARCHAR(15) NOT NULL,
학번 VARCHAR(8),
전공 VARCHAR(10),
성별 SEX,
생년월일 DATE,

PRIMARY KEY(학번),
FOREIGN KEY(전공) REFERENCES 학과(학과코드)
 ON DELETE SET NULL
 ON UPDATE CASCADE

CONSTRAINT 생년월일 제약 CHECK (생년월일 >='1980-01-01') 
);

※ 관계연산자에서 크거나 같다(>=) 혹은 작거나 같다(<=)를 표시할때 같다(=)서식이 뒤에 배치되도록 한다.

 

---

 

② ALTER TABLE  : 테이블에 대한 정의를 변경하는 명령문

ALTER TABLE 테이블명 ADD 속성명 데이터 타입[DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAUT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

 

• ADD : 새로운 속성(열)을 추가할 때 사용한다.
• ALTER : 특정 속성의 DEFAULT 값을 변경할 때 사용한다.
• DROP COLUMN : 특정 속성을 삭제할 때 사용한다.

 

 

예제  <학생> 테이블에 최대 3 문자로 구성되는 '학년' 속성을 추가하세요.

ALTER TABLE 학생 ADD 학년 VARCHAR(3);

 

 

예제 <학생> 테이블의 '학번'필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경하세요.

ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;

 

---

 

③ DROP TABLE : 테이블을 제거하는 명령문

DROP TABLE 테이블명 [CASCADE | RESTRICTED];
DROP CONSTRAINT 제약조건명;

 

• CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다. 주 테이블 제거 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거.
• RESTRICTED : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소한다.

 

예제 <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하세요.

DROP TABLE 학생 CASCADE ;

 

 

 

 

---

2) DML(Data Manipulation Language, 데이터 조작어)

 

DML은 데이터베이스 사용자가 프로그램이나 질의어를 통하여 저장된 데이터를 검색, 삽입, 삭제, 갱신 등 실질적으로 처리할 때 사용하는 언어이다.

명령어	기능
SELECT	테이블에서 조건에 맞는 튜플을 검색한다.
INSERT	테이블에 새로운 튜플을 삽입한다.
DELETE	테이블에서 조건에 맞는 튜플을 삭제한다.
UPDATE	테이블에서 조건에 맞는 튜플의 내용을 변경한다.

 

 

ⓛSELECT ~ FROM ~ WHERE ~ ORDER BY : 테이블의 튜플을 조회할 때 사용한다.

SELECT 속성명1, 속성명2,...

FORM 테이블명

WHERE 조건 
 [GROUP BY 속성명, 속성명, ...]
 [HAVING 조건]

ORDER BY 속성명 [ASC|DESC] ;

 기본적인 SELECT 명령문의 기본 구조는 위와 같다. 그룹함수, WINDOW 함수 등 복잡한 부분은 제외했다. 

 

⚠ SELECT 명령어 사용시 주의해야할 점

⬜ 속성명 관련

속성명은 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정한다.

• 기본 테이블의 모든 속성을 지정할 때는 '*'을 기술한다.
• 두개 이상의 테이블을 대상으로 검색할 때는 '테이블명.속성명'으로 표현한다.
• AS(=별칭) : 속성 및 연산의 이름을 다른 제목으로 표시하기 위해 사용된다. 

⬜ FROM절 

질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술한다. 

 

⬜ WHERE절

검색할 조건을 기술한다.

 

⬜ ORDER BY절

특정 속성을 기준으로 정렬하여 검색할 때 사용한다.

• 속성명 : 정렬의 기준이 되는 속성명을 기술한다.
• ASC| DESC : 정렬방식으로서 'ASC'는 오름차순, 'DESC'는 내림차순이다. 생략시 오름차순으로 지정된다.

 

 

예시 아래 <사원>테이블과 <여가활동>테이블을 보고 예제 문제를 풀어보세요.

<사원>

이름	부서	생일	주소	기본급
홍길동	기획	04/05/61	망원동	120
임꺽정	인터넷	01/09/69	성산동	80
황진이	편집	07/21/75	연희동	100
김선달	편집	10/22/73	망원동	90
성춘향	기획	02/20/64	대흥동	100
장길산	편집	03/11/67	상암동	120
일지매	기획	04/29/78	연남동	110
강건달	인터넷	12/11/80		90

 

 

<여가활동>

이름	취미	경력
김선달	당구	10
성춘향	나이트 댄스	5
일지매	태껸	15
임꺽정	씨름	8

 

 

예제1 <사원> 테이블이 모든 튜플을 검색하세요.

SELECT * FROM 사원;

 

예제2 ⭐<사원> 테이블에서 '기본급'에 특별수당 10을 더한 월급을 "XX부서의 XXX의 월급 XXX" 형태로 출력하세요. 

SELECT 부서 + '부서의' AS 부서2, 이름+'의 월급' AS 이름2, 기본급 +10 AS 기본급2  FROM 사원;

 

예제3 <사원> 테이블에서 '기획'부의 모든 튜플을 검색하세요.

SELECT * FROM 사원 WHERE 부서='기획';

 

예제4 <사원> 테이블에서 "기획" 부서에 근무하면서 "대흥동"에 사는 사람의 튜플을 검색하세요.

SELECT * FROM 사원 WHERE 부서='기획' AND 주소='대흥동';

 

예제5 <사원> 테이블에서 '생일'이 '01/01/69'에서 '12/31/73' 사이인 튜플을 검색하세요.

SELECT * FROM 사원 WHERE 생일 BETWEEN '01/0169' AND '12/31/73';  

 

예제6 <사원> 테이블에서 '부서'를 기준으로 오름차순 정렬하고, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜 검색하세요.

SELECT * FROM 사원 ORDER BY 부서(ASC) , 이름 (DESC);

 

 

* 생각보다 다양하고 재밌는 예시, 문제가 많은것 같다. 정보처리기사 시험을 준비할 때 공부했던 '이기적' 서적에 있는 예제를 가져와 정리해 두었다. 

---

② INSERT INTO ~ VALUES : 테이블에 튜플을 삽입할 때 사용한다.

INSERT INTO 테이블명(속성1,속성2,속성3...)
  VALUES (데이터1,데이터2,데이터3...)

 

⚠ INSERT 명령어 사용시 주의해야할 점

• 대응하는 속성과 데이터의 개수, 유형이 일치해야 한다.
• 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
• SELECT 문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다.

 

예시 아래 <사원>테이블을 보고 예제 문제를 풀어보세요.

이름	부서	생일	주소	기본급
홍길동	기획	04/05/61	망원동	120
임꺽정	인터넷	01/09/69	성산동	80
황진이	편집	07/21/75	연희동	100
김선달	편집	10/22/73	망원동	90

 

예제1 <사원>테이블에 (이름-홍승현, 부서-인터넷)을 삽입하세요.

INSERT INTO 사원(이름, 부서) VALUES('홍승현', '인터넷');

※ 삽입하는 값의 데이터가 VARCHAR과 같이 문자열인 경우 ' '으로 묶어준다. 

 

예제2 <사원>테이블에 (장보고, 기획, 05/06/73,홍제동,90)을 삽입하세요.

INSERT INTO 사원 VALUES('장보고', '기획', '05/06/73', '홍제동', 90);

※ 모든 튜플의 값을 삽입하는 경우는 속성명을 생략할 수 있다.

 

예제3 <사원>테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블에 삽입하세요.

INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
  SELECT 이름, 생일, 주소, 기본급 
  FROM 사원
  WHERE 부서='편집';

※ 다른 테이블에서 조회한 데이터를 삽입하는 코드이다. 

 

 

---

③ DELETE FROM ~ WHERE : 기본 테이블에서 특정 튜플(행)을 삭제할 때 사용한다.

DELETE FROM 테이블명 WHERE 조건 ;

 

⚠ DELETE 명령어사용시 주의해야할 점

• 모든 레코드를 삭제할 때는 WHERE 절을 생략한다.
• 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP과는 다르다.

예제 1 <사원> 테이블에서 "임꺽정"에 대한 튜플을 삭제하세요.

DELETE FORM 사원 WHERE 이름='임꺽정';

 

예제 2 <사원> 테이블에서 "인터넷" 부서에 대한 모든 튜플을 삭제하세요.

DELETE FROM 사원 WHERE 부서='인터넷';

 

예제3 <사원> 테이블의 모든 레코드를 삭제하세요.

DELETE FROM 사원;

 

 

---

④ UPDATE ~ SET 속성=데이터 WHERE : 특정 튜플의 내용을 변경할 때 사용한다.

UPDATE 테이블명 SET 속성명=데이터, 속성명=데이터 ... WHERE 조건 ; 

 

예제 1 <사원> 테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정하시오.

UPDATE 사원 SET 주소='수색동' WHERE 이름='홍길동';

 

예제 2 <사원> 테이블에서 "황진이"의 '부서'를 "기획부"로 변경하고, '기본급'을 5만원 인상시키시오.

UPDATE 사원 SET 부서='기획', 기본급=기본급+5⭐  WHERE 이름='황진이';

 

 

 

---

3) DCL(Data Control Language, 데이터 제어어)

 

DCL은 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용되는 언어이다.

 

명령어	기능
COMMIT	명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려준다.
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래 상태로 복구한다.
GRANT	데이터베이스 사용자에게 사용 권한을 부여한다.
REVOKE	데이터베이스 사용자의 사용권한을 취소한다.

 

 

 

 

 

 

함께보면 좋은 서비스 기획 포스팅 

[서비스기획] 와이어프레임(Wireframe)의 특징과 작성방법에 대해 알아보자

[서비스기획] 메뉴구조도(Menutree)기획과 작성방법에 대해 알아보자

 

함께보면 좋은 인프라 관련 포스팅

[서비스기획/PM] 홈페이지를 오픈하며 해결했던 문제들 (feat1. 클라우드 및 인프라 환경 설정 편 )

[서비스기획/PM] 홈페이지를 오픈하며 해결했던 문제들 (feat2. 개발이슈 편, API연동 개발 이슈, 알