[DB] SQL (Structured Query Language)

1     SQL (Structured Query Language)

1.1    SQL의 개요

-      관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어이다.

-      데이터베이스로부터 정보를 얻거나 갱신하기 위한 표준 대화식 프로그래밍 언어이다.

-      애플리케이션에서 데이터를 저장 또는 조회하기 위해 데이터베이스와 통신할 때 데이터베이스 서버로 전달되는 것은 SQL뿐이다.

1.2    SQL의 종류

1.2.1     DDL (Data Definition Language).

-      사용자는 데이터 정의어를 사용하여 데이터베이스 스키마를 정의한다.

-      데이터베이스나 테이블의 구조를 변경하기 위한 문장이다.

-      가장 기본적인 요소는 CREATE, ALTER, RENAME, DROP, TRUNCATE 구문이다.

1)      CREATE: 데이터베이스에서 객체(예: 테이블)를 생성한다.

사용 예	CREATE TABLE sample_table ( col1 INT, col2 VARCHAR2(50), col3 DATE NOT NULL, PRIMARY KEY (col1, col2) ) ;

 

 

 

 

2)      ALTER: 현존하는 객체의 구조를 변경한다. (예: 컬럼추가, 제한추가)

칼럼 정의 변경	ALTER TABLE 테이블_이름 MODIFY 칼럼_이름 자료형 ;
칼럼 추가	ALTER TABLE 테이블_이름 ADD 칼럼_이름 자료형 ;
칼럼 이름과 정의 변경	ALTER TABLE 테이블_이름 CHANGE 칼럼_이름 자료형 ;
칼럼 삭제	ALTER TABLE 테이블_이름 DROP …
사용 예	ALTER TABLE sample_ table ADD col4 NUMBER(5) NOT NULL ;

3)      TRUNCATE: 테이블에서 모든 데이터를 빠르게 삭제한다. DELETE와 달리 실행 후엔 ROLLBACK이 불가능하다. (COMMIT연산을 내포하고 있다.)

사용 예	TRUNCATE TABLE 테이블_이름 ;

4)      DROP: 데이터베이스에서 객체를 삭제하며, 보통 ROLLBACK을 통해 복원할 수 없다.

사용 예	DROP TABLE 테이블_이름 ;

1.2.2     DML (Data Manipulation Language)

-      데이터를 조작하기 위한 문장이다.

1)      SELECT: 테이블의 데이터를 검색한다.

사용 예	SELECT col1, col2 FROM sample_table WHERE co1=5 ORDER BY DESC;

2)      INSERT: 새로운 데이터를 삽입한다.

사용 예	INSERT INTO col1, col2 VALUES 5, sample_text ;

3)      UPDATE: 기존 데이터를 수정한다.

사용 예	UPDATE sample_table SET sample_text WHERE col2=abc ;

4)      DELETE: 기존 데이터를 삭제한다.

사용 예	DELETE FROM sample_table WHERE col2=abc ;

1.2.2.1                 SELECT에 조건 설정

-      WHERE를 사용하여 데이터 추출하기

1)      WHERE를 사용하면 조건을 설정해서 그 조건에 일치하는 레코드만 추출할 수 있다.

사용 예	SELECT 칼럼_이름 FROM 테이블_이름 WHERE 조건 ;

-      비교 연산

비교 연산자	의미
=	같다
>	보다 크다
>=	이상
<	보다 작다
<=	이하
<>	다르다
O IN X	X 중에 O가 있다
O NOT IN X	X 중에 O가 없다
O BETWWEN X AND XX	X부터 XX의 사이에 O가 있다
O NOT BETWWEN X AND XX	X부터 XX의 사이에 O가 없다

-      조건에 문자열 사용

1)      LIKE: 퍼지 검색

s   데이터베이스 검색할 때 일부를 포함하는 검색이 가능하다. 이러한 검색을 퍼지 검색이라 한다.

사용 예	SELECT 칼럼_이름 FROM 테이블_이름 WHERE 조건 ;

s   퍼센트(%)나 밑줄(_) 등의 와일드카드를 사용한다.

s   퍼센트(%)는 ‘임의의 문자열’, 밑줄(_)은 ‘임의의 한 문자’를 검색 조건으로 설정할 수 있다.

s   LIKE ‘예시%’는 전방 일치, LIKE ‘%예시’는 후방 일치, LIKE ‘%예시%’는 부분 일치이다.

사용 예	SELECT * FROM sample_table WHERE ‘%1’ ;
실행 결과	후방 일치로써 끝에 ‘1’이 들어가는 모든 결과가 나온다.

2)      NOT LIKE

s   LIKE와 반대로 특정 문자열을 포함하지 않는 데이터를 검색할 수 있다.

-      조건에 NULL 사용

s   컬럼 값이 NULL인 레코드를 추출할 때에는 IS NULL을 사용한다.

s   반대로 칼럼 값이 NULL이 아닌 레코드를 추출할 때에는 IS NOT NULL을 사용한다.

-      AND / OR 사용

s   조건1과 조건 2를 한 번에 조건 2가지를 쓰기 위해선 AND나 OR로 연결해야 한다.

s   AND와 OR을 동시에 사용한다면, 순서에 상관없이 AND가 먼저 선행된 후 OR이 선행된다. (OR을 먼저 선행시킬 경우엔 먼저 실행시키고 싶은 부분을 괄호()로 묶어서 OR을 먼저 실행되도록 한다.

1.2.2.2                 하위 질의 (또는 서브 쿼리)

-      하위 질의를 사용하면, 질의를 실행해서 추출한 데이터를 이용해서 다시 질의를 실행하는 2단계의 처리를 할 수 있다.

-      하위 질의를 사용하는 처리 대부분은 내부 조인 등으로 대체할 수 있다. 하지만, 단계별로 처리하는 하위 질의를 사용하는 것이 훨씬 효율적이다.

-      IN 사용

s   칼럼을 반환한다.

사용 방법	SELECT 표시할_칼럼 FROM 테이블_이름 WHERE 칼럼_이름 IN (SELECT를_이용한_하위_질의로_추출한_칼럼) ;
사용 예	SELECT * FROM sample_table WHERE number IN (SELECT number FROM table WHERE sales>=200) ;
실행 결과	월급이 200 이상인 조건에 일치하는 사원번호(number)가 나온다.

1.2.3     DCL (Data Control Language)

-      사용자는 DCL을 사용하여 데이터베이스 트랜잭션을 명시하고 권한을 부여하거나 취소한다.

1)      GRANT: 권한을 부여한다.

s   GRANT 라는 명령문을 사용하여 사용자의 접근을 제한할 수 있다. 반대로 WITH GRANT OPTION을 통해 권한을 부여받은 유저는 다른 유저에게 권한을 부여할 수도 있다.

s   권한 부여 자에게 CREATE USER 권한이 있고 유저가 존재하지 않을 경우 유저도 생성해준다.

사용 예	GRANT select on *.* TO ‘sample_user’@’localhost’ identified by ‘password’ WITH GRANT OPTION ;

2)      REVOKE: 부여했던 권한을 회수한다.

s   WITH GRANT OPTION으로 권한을 부여했던 유저의 권한이 회수되어도 하위 유저의 권한은 사라지지 않는다.

사용 예	REVOKE select on *.*  FROM ‘sample_user’@’localhost’ ;

1.2.4     트랜잭션 제어어

-      사용자는 트랜잭션의 시작, 철회, 완료 등을 명시하기 위해 트랜잭션 제어를 사용한다.

-      Commit, Roolback, Savepoint 등이 있다.

1.3    View

-      미리 정의된 단일 질의를 나중에 사용하기 위해 데이터베이스에 개체로 저장해 놓은 것이다.

-      뷰 테이블은 어떠한 기억 공간을 차지하지는 않는다.

-      유도된(Derived) 또는 가상(Virtual) 테이블이라고도 한다.

-      뷰는 실제 데이터 행을 가지고 있는 것처럼 동작하지만 데이터 행은 없다.

1.3.1     기반 테이블(Base Table)

-       뷰를 정의하는데 사용되는, 데이터를 가진 진짜 테이블이다.

1.3.2     뷰의 장점

-       사용자를 위한 데이터에 초점을 맞춘다.

s   사용자에게 꼭 필요한 데이터만 보여주고, 그 밖의 데이터는 감춘다. 사용자는 뷰가 돌려주는 데이터를 기반 테이블의 데이터처럼 조작할 수 있다.

-       데이터베이스의 복잡도를 줄여준다.

s   데이터베이스 구조를 바꾸지 않고도 사용자에게 필요한 맞춤식 데이터를 제공할 수 있다. 또한, 복잡한 질의 대신 단순화된 뷰를 쓸 수 있다.

-       사용자 액세스 관리를 단순화시켜준다.

s   기반 테이블의 특정 열에 대해 사용자의 액세스 권한 들을 설정할 필요 없이, 사용자들이 뷰를 통해서만 데이터를 액세스 하도록 설정하면 되므로 관리가 쉬워진다.

-       테이블 구조가 바뀌었을 경우, 이전 버전과 호환되는 뷰를 만들어서 기존의 응용 프로그램이 사용하도록 하면 이전 버전과의 호환성을 제공할 수 있다.

-       다른 응용 프로그램에서 사용할 때 ‘데이터 내보내기’를 할 수 있다.

s   복잡한 질의에 기반을 둔 뷰를 만들고, 이를 통해 데이터를 내보내 다른 응용 프로그램이 사용하도록 할 수 있다.

-       뷰의 생성은 CREATE를 사용한다

-       이미 같은 이름의 뷰가 존재할 때 뷰를 생성하려고 하면 이미 존재하는 뷰의 이름이기에 오류가 발생한다. 이럴 때 CREATE OR REPLACE VIEW를 사용하여 덮어쓰기 한다. 즉, 이미 존재하는 같은 이름의 뷰를 삭제하고 새롭게 뷰를 생성하게 된다.

사용 예	CREATE VIEW sample_view AS SELECT col_num, col_name FROM sample_table ;
	CREATE OR RELACE VIEW sample_view AS SELECT another() ;

1.3.3     뷰의 수정과 삭제

-      뷰를 수정하거나 삭제할 때는 ALTER(수정)와 DROP(삭제) 명령어를 사용한다.

-      Alter view문으로 뷰를 수정하면 액세스 권한이 그대로 유지된다.

-      Drop view 문으로 삭제된 뷰를 참조하는 개체(또 다른 뷰)를 액세스 하면 오류가 발생한다.

-      뷰를 참조하는 개체가 삭제되더라도 해당되는 뷰는 자동으로 삭제되지 않으므로 수작업으로 삭제해주어야 한다.

뷰 수정	ALTER VIEW 뷰_이름 AS SELECT 칼럼_이름 FROM 테이블_이름 ;
뷰 삭제	DROP VIEW 뷰_이름 ;
사용 예	ALTER view sample_view AS SELECT col_num, col_name FROM sample_table ;
	DROP view sample_view ;

 

 

 

1.4    데이터베이스 자료형

1.4.1     특징

-      데이터베이스의 테이블에 데이터를 저장할 경우, 각 칼럼에 설정한 자료형의 데이터만 입력할 수 있다.

-      자료형은 잘못된 데이터가 입력될 가능성을 낮추고, 데이터베이스 전체에 대한 신뢰성을 유지하기 위한 특징이다.

-      데이터베이스 종류마다 자료형 이름에 차이가 있다.

-      기본적으로 숫자형, 문자형, 바이너리형, 날짜형이 있다.

-      가변형 문자열은 길이를 고정하지 않기 때문에 관리에 유리하다.

-      이미지 파일 등의 저장에는 바이너리형이 필요하다.

-      날짜 기간 검색 등을 위해서는 날짜형을 반드시 사용해야 한다.

-      숫자를 문자형으로 저장한 경우에는 사칙연산 같은 연산이 되지 않는다.

-      사용 목적에 따라 자료형을 바꿔서 사용해야 한다.

1.4.2     데이터 타입 (MySQL 기반)

	데이터 타입	사용되는 Byte	속성
1)	CHAR(n)	정확히 N (≤ 255)	CHAR(5) ‘HELLO’는 5byte 사용 CHAR(50) ‘HELLO’는 50byte 사용
	VARCHAR(n)	최대 n까지(≤ 65535)	VARCHAR(100) ‘HELLO’는 5byte 사용 VARCHAR(5) ‘HELLO’는 5byte 사용
2)	BINARY(n) or BYTE(n)	정확히 n (≤ 255)	CHAR이지만 바이너리 데이터를 가짐
	VARBINARY(n)	최대 n까지 (≤ 65535)	VARCHAR이지만 바이너리 데이터를 가짐
3)	TINYTEXT(n)	최대 n (≤ 255)	문자열로 취급
	TEXT(n)	최대 n까지(≤ 65535)	문자열로 취급
	MEDIUNTEXT(n)	최대 n까지(≤ 16777215)	문자열로 취급
	LONGTEXT(n)	최대 n까지(≤ 4294967295)	문자열로 취급
4)	TINYBLOB(n)	최대 n (≤ 255)	바이너리 데이터로 취급
	BLOB(n)	최대 n까지(≤ 65535)	바이너리 데이터로 취급
	MEDIUMBLOB(n)	최대 n까지(≤ 16777215)	바이너리 데이터로 취급
	LONGBLOB(n)	최대 n까지(≤ 4294967295)	바이너리 데이터로 취급

 

 

1)     문자열 데이터 타입

s   CHAR와 VARCHAR(VARiable length CHARacter string)은 모두 텍스트 문자열을 허용하고, 필드의 크기를 제한한다. 두 타입의 차이점은 CHAR 필드의 모든 문자열은 크기가 정해진다는 것이다. 즉, 더 작은 문자열을 입력하면 공백으로 채워진다.

s   반면, VARCHAR의 경우, 텍스트를 채우지 않으며, 입력한 텍스트 크기에 맞게 가변적으로 크기를 가진다.

s   그러나, VARCHAR는 각 값의 크기를 추적할 수 있는 약간의 오버헤드가 필요하기 때문에 모든 데이터의 크기가 비슷하다면, CHAR가 효율적이다.

2)     BINARY 데이터 타입

s   관련된 문자 세트가 없는 문자의 전체 바이트를 저장하는 데 사용된다.

3)     TEXT와 VARCHAR 데이터 타입

s   TEXT와 VARCHAR에는 작은 차이점이 있다.

s   TEXT 필드는 기본 값을 가질 수 없다.

s   MySQL은 TEXT열의 처음 n개의 문자만 인덱싱 할 수 있다.

4)     BLOB 데이터 타입

s   BLOB(Binary Large Object)는 65535 바이트를 넘는 바이너리 데이터에 유용하며, 기본 값을 가질 수 없다.

5)     날짜와 시간 데이터 타입

	데이터 타입	의미	특징
5)	DATETIME	날짜와 시간	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
	DATE	날짜	1000-01-01 ~ 9999-12-31
	YEAR	연도	1901 ~ 2155(4 자릿수일 때) 1970 ~ 2069(70 ~ 69) (2 자릿수일 때)
	TIME	시간	-838:59:59 ~ 838:59:59
	SYSDATE	시간	지금 현재 시간 (now와 같은 역할)

 

 

 

6)     숫자형 데이터 타입

	데이터 타입	의미	Byte	최소 값 (signed/unsigned)	최대 값 (signed/unsigned)
6)	TINYINT	매우 작은 정수	1	-128 0	127 255
	SMALLINT	작은 정수	2	-32768 0	32767 65535
	MEDIUMINT	중간 크기의 정수	3	-8388608 0	8388607 16777215
	INT or INTEGER	오른쪽의 범위 안에 있는 정수	4	-2147483648 0	2147473647 4294967295
	BIGINT	큰 정수	8	-9223372036854775808 0	9223372036854775807 18446744073709551615
	FLOAT	단정도 부동소수점 수	4	-3.40E+45 (no unsigned)	3.40E+45 (no unsigned)
	double or REAL	배정도 부동소수점 수	8	-1.7976E+320 (no unsigned)	1.7976E+320 (no unsigned)
	DECIMAL	고정소수점 수	8	DEMICAL(최대자릿수, 소수점 이하 자릿수) 형식으로 최대자릿수는 65까지 이며, 소수점 이하 자릿수는 30까지 지정할 수 있다. 오차는 발생하지 않는다.