SQL 기초

#데이터베이스(Data Base)란?

우리가 사용하는 거의 모든 프로그램에는 데이터베이스(DB)라는 공간이 같이 사용됩니다. DB는 시스템을 운용하는데 필요한 정보 대부분이 담기게 됩니다. 여기에는 누군가의 이름, 학교, 전화번호 등의 개인정보가 담기기도 하고 게시판에 쓰는 글의 제목, 내용, 작성자 등의 정보가 담기기도 합니다.

우리가 앞으로 만들 시스템에서 다음과 같은 일이 이루어진다고 합시다. 

 

0. 카메라를 통해 영상을 읽어온다.

1. 영상 안에 얼굴을 찾아서 얼굴 주위에 테두리를 씌운다.

2. 그 얼굴이 누구의 얼굴인지 알아내는 작업을 수행한다.

3. 테두리 한쪽에 그 사람의 이름을 찍어낸다.

 

2번과 3번을 보세요. 컴퓨터는 어떻게 영상에서 찾아낸 얼굴의 주인이 누구인지 알아낼까요? 우리가 처음 본 사람의 얼굴과 이름을 알 수 없듯이 컴퓨터도 주어진 얼굴과 비교할 여러 얼굴과 이름들을 미리 알고 있어야 해요.  

우리가 만나는 사람들을 "기억에 담는" 동안에 컴퓨터는 인식하는 얼굴과 이름들을 "저장"해야 해요. 여기서 인식할 사람의 이름을 미리 저장해 놓는 곳이 DB에요.

우리가 해야 할 것은 

1. 카메라 영상에 보이는 사람의 이름을 입력받아 DB에 저장하고 

2. 카메라  영상에서 미리  학습시킨 얼굴을 인식했을 때 이전에 이 DB에  저장한 이름 중에 그 얼굴 주인의 이름을 찾아 보여주도록 해야 해요.

정리하자면 "DB에  저장", 그리고 "DB에서 검색"을 해야 한다고 할 수 있어요. 이렇게 DB에 저장하고 검색할 때 사용할 또 다른 프로그래밍 언어를 SQL(Structured Query Language)이라고 해요. DB에게 우리가 하고자 하는 일을 알아먹을 수 있도록 일러주는데 사용하는 명령어라고 할 수 있겠네요. 

 

#함께 보면 좋은 것 ○DMBS란? 응용프로그램(사용자)과 데이터베이스 사이에서 정보를 생성하고, 관리해주는 소프트웨어로 우리가 흔히 알고 있는 ORACLE, MySQL, MSSQL 등의 소프트웨어 제품을 지칭합니다. 특히 이들은 RDBMS라고 하는데  DBMS에는 ~형태로 ~가 저장된다고 하면  RDBMS는 테이블 형태로 ~가 저장됩니다. (참고 -https://www.javatpoint.com/difference-between-dbms-and-rdbms -http://gomcine.tistory.com/entry/Database-1-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4%EC%9D%98-%EC%97%AD%EC%82%AC%EC%99%80-%EA%B0%9C%EB%85%90)

 

 

#SQL (Structured Query Language)란?

SQL은 관계형 데이터베이스 관리 시스템(RDBMS - Relational Database Management System )에 저장된 데이터들을 관리하고 조작하기 위해 만들어진 특수한 언어입니다. 우리는 우리가 만드는 프로그램 소스에서 DB에 접속하게 되고 이때 SQL로 작성한 각각의 쿼리(Query, 질의어)와 그 결과값을 DB와 주고받게 됩니다.

특히 관계형 데이터베이스(RDB - Relational Database)에서는 데이터를 테이블(표) 형식으로 관리하는데요. 여러 테이블이 하나의 DB를 구성하고 각각의 테이블은 여러 컬럼(Column, 열)을 가집니다. 

예를 들어 어떤 사이트에 등록한 사용자를 관리하기 위해서는 "사용자정보"라는 테이블을 만들고 그 테이블 안에 "ID", "이름", "휴대폰번호", "이메일주소" 등의 컬럼을 만들게 돼요. 아래의 "사용자정보" 테이블에 정의된 각각의 컬럼과 컬럼에 저장한 데이터를 보세요. 여기서는 우리가 사용할 DB가 테이블(표)의 구조를 가진다는 것만 알고 넘어가도록 합시다.

순번	ID	이름	휴대폰번호	이메일주소	자택주소	자택주소_상세
1	choi123	최샘	010-1234-5678	choi123@abc.ef	서울시 **구 **동 **번지	*호
2	kim456	김샘	010-5678-1234	kim456@abc.ef	인천시 **구 **동 **번지	*호

 

 

○SQL의 종류

SQL은 크게 4가지로 나뉩니다.

- 데이터 정의 언어 (DDL - Data Definition Language) : 테이블과 같은 DB의 구조를 정의

- 데이터 조작 언어 (DML - Data Manipulation Language) : 데이터를 검색, 삽입, 수정, 삭제하는 등의 조작

- 데이터 제어 언어 (DCL - Data Control Language) : DB에 접근하는 권한을 부여하고 회수

- 트랜잭션 제어 언어 (TCL - Transaction Control Language) : DML 조작 작업의 끝과 적용을 제어

종류	핵심 명령어	설명
데이터 정의 언어 (DDL - Data Definition Language)	CREATE	데이터베이스 객체 생성
	ALTER	데이터베이스 객체 변경
	DROP	데이터베이스 객체 삭제
데이터 조작 언어 (DML - Data Manipulation Language)	SELECT	데이터 검색
	INSERT	데이터 저장
	UPDATE	데이터 수정
	DELETE	데이터 삭제
트랜잭션 제어 언어 (TCL - Transaction Control Language)	COMMIT	트랜잭션의 종료 처리
	ROLLBACK	트랜잭션 취소한다.
데이터 제어 언어 (DCL - Data Control Language)	GRANT	DDL, DML 등 DB에  대한 권한 부여
	REVOKE	DDL, DML 등 DB에  대한 권한 취소

(참고 : 

-https://www.w3schools.in/mysql/ddl-dml-dcl/

-http://sungwoon.tistory.com/56

-http://brownbears.tistory.com/180)

 

#함께 보면 좋은 것  ○NoSQL 이란? ○Transaction 이란? ○CRUD란?

 

  

#데이터 정의 언어 (Data Definition Language)

 

○ 데이터베이스(DB) 생성 (CREATE)

가장 먼저 해야할 것은 당연히 우리가 사용할 DB를 생성하는 것이겠죠.

CREATE DATABASE <DB이름>

* 우리는 sqlite를 사용할 거에요. sqlite는 커맨드라인(?)에 "<DB이름>.db"를 입력했을 때 해당 이름의 db가 없는 경우 자동으로 DB가 생성돼요.

 

 

○ 테이블(TABLE) 생성 (CREATE)

위에서 생성한 DB에 테이블을 생성합시다.

CREATE TABLE <테이블이름>
( 
	컬럼이름1 데이터타입,
	컬럼이름2 데이터타입,
	컬럼이름3 데이터타입,
);

*여기서 varchar2(50)과 같은 데이터타입은 각 컬럼의 형식을 나타내요. 앞에 “varchar2”의 자리는 받고자 하는 컬럼이 글자인지, 숫자인지, 시간인지 등을 나타냅니다. 뒤에 괄호 안에는 최대 몇 자의 데이터를 받도록 할 것인지 정해주는 것입니다. “varchar2(50)”의 경우 최대 50자의 글자형식의 데이터만 저장하도록 설정하는 것이지요.

*not null의 경우 “ID”컬럼과 “이름“컬럼에만 붙어있지요? 해당 컬럼에는 무조건 값이 있어야한다는 뜻이에요. 이건 아래 INSERT에서 다시 얘기하기로 해요.

*not null과 같이 꼭 설정하지 않아도 되는 값들이 있지만, 컬럼이름과 데이터타입은 꼭 정의해줘야 해요. 추가로 정의 가능한 요소들은 생략하도록 할게요.

 

○ 테이블 변경 (ALTER)

만약 “사용자정보” 테이블에 “가입일시” 컬럼을 추가하고자 한다면 어떻게 해야할까요? 이 때 ALTER 문을 사용하게되는데요. 특히 날짜/시간을 나타내는 DATE 형식은 크기를 따로 정해주지 않으니 유의합시다.

ALTER TABLE 사용자정보 ADD (
	가입일시 DATE
);

*그 외에도 ADD 자리에는 MODIFY(데이터 타입 수정), RENAME(컬럼명 변경), DROP(컬럼 삭제) 등이 들어갈 수 있으나 여기서는 간단히 보고 넘어가도록 합시다.

 

○ DB 삭제 (DROP)

우리는 테이블을 삭제하거나

DROP TABLE <테이블이름>;

DB 자체를 삭제할 수도 있습니다.

DROP DATABASE;

 

#함께 보면 좋은 것 ○SQL 제약조건 (Primary Key(기본키), Foreign Key(외부키) 등)

#데이터 조작 언어 (Data Manipulation Language)

○ 데이터 검색 (SELECT)

 

가장 많이 사용되는 SQL 구문으로 DB에 저장된 데이터를 읽어옵니다.

SELECT <컬럼이름1>, <컬럼이름2>, <컬럼이름3>
FROM <테이블이름>
WHERE <컬럼이름1> = ‘<조건>’;    *WHERE 조건은 생략해도 됩니다.

예에서는 위에서 만든 “사용자정보” 테이블에 미리 넣어둔 최샘과 김샘의 “ID”와 “이름” 정보를 검색(SELECT)해 오고 있네요.

 

이것처럼 WHERE 조건을 부여해서 갖고 오고 싶은 정보만 가져올 수도 있어요. 이름이 “최샘”인 사용자의 ID와 이름을 가져오고 있네요.

 

테이블의 모든 컬럼의 정보를 확인하고 싶을 때는 <컬럼이름>의 자리에 “*”을 사용하면 모든 정보를 불러오게 됩니다. 위에서 검색한 ID, 이름뿐만 아니라 휴대폰번호, 이메일주소 등과 같이 테이블에 정의된 모든 컬럼의 정보들도 검색되었네요.

 

  

○ 데이터 저장 (INSERT)

 

위에서는 이미 저장된 정보를 불러오는 SELECT문을 봤는데요. 그 전에 이뤄져야 할 것이 바로 INSERT문입니다. DDL(데이터 정의 언어)로 정의한 테이블의 각각의 형식에 맞게 정보를 입력해 주어야 합니다.

INSERT INTO <테이블이름> (<컬럼이름1>, <컬럼이름2>, <컬럼이름3>) 
VALUES (‘데이터1’, ‘데이터2’, ‘데이터3’);

영호의 정보를 INSERT하고 방금 INSERT한 ID로 영호의 정보를 검색해 보았어요.

위처럼 <테이블이름> 다음에 오는 괄호에 입력하기 원하는 컬럼 이름만 설정해서 넣어줄 수 있어요.

이때 유의해야 할 것이 처음 DDL에서 테이블을 생성할 때 설정해 준 “not null”이에요. “ID”컬럼과 “이름”컬럼은 무조건 값이 있어야 한다고 했죠? 만약 “사용자정보” 테이블에 insert를 하는데 “ID”나 “이름”을 빼놓고 입력한다면 오류가 발생할 거에요. 그에 비교해 “이메일주소”, “자택주소”, “자택주소_상세”는 입력하지 않았는데도 오류가 발생하지 않았어요.

 

만약 모든 컬럼의 정보를 입력하려 한다면 <테이블이름> 뒤의 괄호는 생략해도 돼요.

○ 데이터 수정 (UPDATE)

 

방금 저장한 아린이의 정보 중 휴대폰 번호를 수정하려면 어떻게 해야할까요?

UPDATE <테이블이름> 
SET <컬럼이름1> = ‘<수정데이터1>’,
    <컬럼이름2> = ‘<수정데이터2>’,
    <컬럼이름3> = ‘<수정데이터3>’
WHERE <컬럼이름1> = ‘<조건>’;

휴대폰 번호를 ‘010-2000-1000’에서 ‘010-2001-1001’로 수정해보았어요. 이번엔 영호의 정보 중에 입력하지 않은 3가지 정보를 마저 입력해볼게요.

 

이렇게 원하는 조건의 데이터들을 수정할 수 있답니다.

 

  

○ 데이터 삭제 (DELETE)

만약 회원가입한 사람이 회원 탈퇴를 하면 어떻게 해야 할까요? 테이블에서 해당 회원의 데이터를 지워야겠죠?

DELETE FROM <테이블이름>
WHERE ‘<컬럼이름>’ = ‘<조건>’;

최샘을 DB에서 삭제해 보았어요.

 

기본적으로 DML(데이터 조작 언어)에서 배운 구문들은 익숙해지도록 합시다.

 

 

 

#트랜잭션 제어 언어(Transaction Control Language)

위에서 TCL(트랜잭션 제어 언어)의 설명으로 DML 조작 작업의 끝과 적용을 제어한다고 했어요. 말이 어렵죠? 예를 들어 얘기해봅시다.

 

최샘과 김샘이 “사용자정보” 테이블을 사용하고 있는데 최샘이 아린이의 정보를 UPDATE 했다고 합시다. 이때 김샘이 아린이의 정보를 SELECT 하면 최샘이 수정하기 이전의 정보가 나오게 될 거에요. 왜냐하면 최샘이 UPDATE를 하고 “COMMIT”(커밋)하지 않았기 때문이에요.

 

SQL에서 COMMIT은 “실행한 작업(트랜잭션)을 DB에 저장하고 종료한다”라는 뜻이에요. 그럼 UPDATE를 하는 즉시 작업을 처리하고 저장하지 않는 이유는 무엇일까요? 생각해봅시다.

 

1. 만약 최샘이 실수로 김샘의 정보를 DELETE 해버렸다고 합시다. 이 일이 우리가 사용하는 포털사이트 회사에서 일어났다고 하면 더욱 끔찍해지겠죠? 어느 날 포털 사이트에 접속하려 하는데 계정이 없다고 한다면?

2. 좀 더 크게 보자면, 최샘이 김샘에게 은행 계좌를 통해 50만원을 보낸다고 합시다(트랜잭션의 시작). 최샘의 계좌에서 UPDATE를 통해 “ 잔액 = ‘원래금액’-‘50만원’ ” 이 되고서 김샘의 은행 계좌에 UPDATE를 통해 “ 잔액 = ‘원래금액’+‘50만원’ ” 이 되어야 하는데 은행 컴퓨터의 오류로 김샘의 은행 계좌에 UPDATE가 실패했다고 합시다. 그러면 최샘의 50만 원은 어디로 가게 될까요?

 

이렇게 DB에서는 중요한 데이터들을 관리하게 되는데요. 위와 같이 중요한 데이터를 삭제했을 때나 은행 송금 업무와 같이 실행한 작업(트랜잭션) 중에 오류가 발생했을 때는 ROLLBACK을 통해 작업을 실행하기 이전의 상태로 되돌릴 수 있어요. ROLLBACK은 “실행한 작업(트랜잭션)을 DB에 저장하지 않고 되돌린다.”라는 뜻이에요.

 

SQL에서는 쿼리를 실행한 후 간단하게 아래와 같이 TCL을 수행할 수 있어요.

COMMIT;

ROLLBACK;

 

#함께 보면 좋은 것 ○SAVEPOINT

 

 

#데이터 제어 언어 (Data Control Language)

위 TCL을 설명하면서 포털 사이트의 사용자 정보나 은행의 계좌정보들도 DB로 관리된다는 걸 볼 수 있었을 거예요. DB에서는 이렇게 중요한 정보들을 관리하게 되는데요. 이런 정보들을 아무나 INSERT하고 DELETE하고 UPDATE할 수 있으면 큰일이 나겠죠? SELECT도 마찬가지예요. 특별한 권한을 가진 사람만 조회할 수 있는 정보들을 따로 분리해야 할 경우가 그런 경우겠죠.

DCL(데이터 제어 언어)가 그러한 역할을 해요. 여기서는 간단히 구문만 확인하고 넘어갑시다.

GRANT CREATE TABLE TO <사용자이름>;		*테이블 생성 권한 부여
GRANT DROP ANY TABLE TO <사용자이름>;		*테이블 드롭 권한 부여
REVOKE CREATE TABLE FROM <사용자이름>;		*테이블 생성 권한 회수
GRANT SELECT ON <테이블이름> TO <사용자이름>;	*검색 권한 부여