SQL 문법
1. 정의
SQL은 관계형 데이터베이스 관리 시스템의 데이터를 관리하기 위해 설계된 프로그래밍 언어이다.
SQL(Structured Query Language) 문법은 데이터베이스를 접근하고 조작하는데 필요한 표준 언어를 활용할 수 있게 해주는 규칙이다.
분류로는 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)이 있다.
※ 사전 지식
- Relational(관계형): MySQL, MariaDB, PostgreSQL, SQLite
- Non-Relational(비관계형): MongoDB, CouchDB, Redis
- 관계형은 행과 열의 집합인 테이블 형식으로 데이터를 저장하고, 비관계형은 테이블 형식이 아닌 키-값 (Key-Value) 형태로 값을 저장
2. DDL
(1) 개념
DDL(Data Definition Language)란 데이터를 정의하는 언어로서, 데이터를 담는 그릇을 정의하는 언어이다.
DDL의 기능으로는 DB 생성, TABLE 삭제, INDEX 생성 등이 있다.
(2) 대상
- 도메인(Domain) : 속성의 데이터 타입과 크기, 제약조건 등의 정보
- 스키마(Schema) : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
- 테이블(Table) : 데이터 저장 공간
- 뷰(View) : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
- 인덱스(Index) : 검색을 빠르게 하기 위한 데이터 구조
1) TABLE
- 데이터를 저장하는 항목인 Field들로 구성된 데이터의 집합체이다. 릴레이션(Relation) 또는 엔터티(Entity) 라고 불린다.
- 테이블에 포함된 행들 간에는 순서가 존재하지 않는다. 열들 간에도 순서가 존재하지 않는다.
<Table 관련 용어>
- Tuple / 행(Row) : 테이블 내의 행을 의미, 레코드라고도 한다.
- Attribute / 열(Column) : 테이블 내의 열을 의미
- Cardinality : Tuple의 개수, 최대 Tuple의 수는 각 속성의 Domain 값을 모두 곱하기
- Degree : Attribute의 개수
- Domain : 하나의 Attribute가 취할 수 있는 같은 타입의 원자값들의 집합
<데이터 타입>
- VARCHAR2(n) : 가변 길이 문자 데이터(1~4000byte)
- CHAR(n) : 고정 길이 문자 데이터(1~2000byte)
- NUMBER(p,s) : 전체 p자리 중 소수점 이하 s자리
- DATE : 날짜 및 시간 데이터
- LONG : 가변 길이 문자 데이터(1~2Gbyte)
- CLOB : 단일 바이트 가변 길이 문자 데이터(1~4Gbyte)
- RAW(n) : n Byte의 원시 이진 데이터(1~2000)
- LONG RAW : 가변 길이 원시 이진 데이터(1~2Gbyte)
- BLOB : 가변 길이 이진 데이터(1~4Gbyte)
- BFILE : 가변 길이 외부 파일 이진 데이터(1~4Gbyte)
<명령어>
- CREATE TABLE 테이블명(컬럼명 데이터타입(사이즈) 제약조건, 컬럼명2 데이터타입(사이즈) 제약조건, ... );
- ALTER : 테이블 구조 변경, ADD 컬럼 추가, DROP 컬럼 삭제, MODIFY 컬럼 수정(데이터타입, 사이즈 변경가능, 데이터있는경우 불가)
- PK키 지정 -> ALTER TABLE 테이블명 ADD CONSTRAINT 테이블명_PK PRIMARY KEY (컬럼명);
- DROP TABLE 학생;
- DESC 테이블명; : 테이블의 스키마 구조 확인
2) VIEW
- 논리 테이블로서 단순질의어 사용하기 위해 사용
- 논리적 데이터 독립성 제공, 데이터 조작 연산 간소화, 보안 기능(접근제어) 제공
- 뷰 정의 변경 불가(DROP 후 CREATE해야 함), 데이터 변경 제약 존재, 뷰 자체 인덱스 불가
- 뷰가 정의된 기본 테이블이 삭제되면 뷰도 자동적으로 삭제된다.
- 스캔방식으로는 인덱스 범위 스캔, 인덱스 전체 스캔, 인덱스 단일 스캔, 인덱스 생략 스캔이 있다.
<명령어>
- CREATE VIEW 뷰이름 컬럼명 AS SELECT 문;
SELECT 문에는 UNION이나 ORDER BY 절 사용 불가, 컬럼명 생략하면 SELECT 문의 컬럼명이 자동으로 사용됨
- DPOP VIEW 뷰이름;
3) INDEX
- 데이터를 빠르게 찾을 수 있는 수단, 테이블에 대한 조회속도를 높여주는 자료구조
- 인덱스는 자동으로 생성되지 않는다. 다만, 기본 키 컬럼은 자동으로 인덱스 생성된다.
<종류>
- 순서 인덱스 : 데이터가 정렬된 순서로 생성되는 인덱스, B-Tree 알고리즘 활용
- 해시 인덱스 : 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스, 데이터 접근비용 균일, 튜플 양에 무관
- 비트맵 인덱스 : 각 컬럼에 적은 데이터 저장된 경우 선택하는 인덱스, 수정이나 변경 적을 경우 유용
- 함수기반 인덱스 : 수식이나 함수를 적용하여 만든 인덱스
- 단일 인덱스 : 하나의 컬럼으로만 구성된 인덱스, 주 사용 컬럼이 하나일 경우 사용
- 결합 인덱스 : 두 개 이상 컬럼으로 구성된 인덱스, WHERE 절 사용빈도 높을 경우 유용
- 클러스터드 인덱스 : 기본 키 기준으로 레코드를 묶어서 저장하는 인덱스, 특정 범위 검색시 유용
<명령어>
- CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
- DROP INDEX 인덱스명;
- ALTER INDEX 인덱스명 ON 테이블명(컬럼명);
(3) 명령어
1) Create
데이터베이스 오브젝트 생성
- CREATE TABLE 테이블명(컬럼명 데이터타입(사이즈) 제약조건, 컬럼명2 데이터타입(사이즈) 제약조건, ... );
<제약조건>
- NOT NULL : NULL값을 허용하지 않음, 반드시 데이터 입력필요
- UNIQUE : 중복불가
- CHECK(조건식) : 조건식에 맞는 값만 입력가능. 데이터 무결성 유지하기 위함
- DEFAULT : 입력값이 없는 경우의 기본값
- PRIMARY KEY : 테이블의 기본키 (UNIQUE & NOT NULL), 테이블당 1개만 생성가능
- FOREIGN KEY : 외래키, 참조하는 테이블의 기본키 중에 하나이거나 NULL, REFERENCES 테이블명(컬럼명) 으로 사용
- SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = '테이블명';
제약조건 확인하기, WHERE 절 안쓰면 제약조건 전부 다 나오는거 주의
- CREATE TABLE 테이블1 AS SELERT * FROM 테이블2;
서브쿼리를 이용하여 테이블 생성시 구조, 자료 복사되지만, 제약조건은 복사되지 않는다.
2) ALTER
데이터베이스 오브젝트 변경
- ALTER TABLE 테이블명 ADD (컬럼명 데이터_타입);
테이블에 필요한 컬럼을 추가하는 명령어
- ALTER TABLE 테이블명 MODIFY (컬럼명 데이터_타입 [DEFAUT 값] [NOT NULL]);
기존 테이블에 존재하는 컬럼의 데이터 유형, 기본값, NOT NULL 등의 제약조건에 대한 변경
- ALTER TABLE 테이블명 DROP (컬럼명);
테이블에 존재하는 컬럼을 삭제하는 명령어
- ALTER TABLE 테이블명 RENAME COLUMN 변경전_컬럼명 TO 변경후_컬럼명;
테이블의 컬럼명을 변경하는 명령어
<제약조건 활성화/비활성화>
- ALTER TABLE 테이블명 ENABLE CONSTRAINT 제약조건명;
- ALTER TABLE 테이블명 DISABLE CONSTRAINT 제약조건명;
3) DROP
데이터베이스 오브젝트 삭제
테이블의 모든 데이터를 삭제하고, 디스크 사용량 초기화할 수 있지만, 테이블의 스키마 정의도 함께 삭제된다.
- DROP TABLE 테이블명;
- CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
- RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션
4) TRUNCATE
데이터베이스 오브젝트 내용 삭제
특정 테이블의 모든 데이터를 삭제하고, 디스크 사용량 초기화
동일 데이터량 삭제시 DELETE보다 빠르다.
- TRUNCATE TABLE 테이블명;
3. DML
(1) 개념
DML(Data Manipulation Language)은 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어이다.
(2) 명령어
1) SELECT
데이터의 내용을 조회할 때 사용하는 명령어이다.
<SELECT 절>
- 검색하고자 하는 속성명, 계산식
- AS "별칭" : 컬럼의 별칭, AS는 생략 가능, 별칭에 공백없으면 큰따옴표 생략가능
- || : 컬럼을 연결
- DISTINCT : 중복 행의 제거
<FROM 절>
- 검색될 데이터들을 포함하는 테이블명을 기술
- DUAL : 임시 테이블명
<WHERE 절>
- 검색할 조건을 기술, 결과는 항상 T, F 중에 하나
- 비교연산자 : =, <, >, >=, <=, !=, <>, ><
ex) WHERE CONDITION = 'Sick'; /* 큰 따옴표는 안됨 */
- 논리연산자 : AND, OR, NOT
- 숫자를 조건으로 줄때는 숫자만 넣기
- 문자는 반드시 작은따옴표로 감싸야 한다.
- 날짜의 경우, TO_DATE('문자', '날짜패턴') : 문자를 날짜로 변환, 아니면 그냥 문자열로 써도 된다.
- BETWEEN ... AND ... : 두 값 사이 값, 속하지 않는다고 하려면 NOT BETWEEN ... AND ... 으로 사용
- IN : 여러 값 중 하나와 일치하는 값, NOT IN은 반대 의미
- LIKE : = 연산자 , % 0개 이상의 문자열과 일치, _ 특정 위치의 1개 문자와 일치, [] 1개 문자와 일치, [^] 1개 문자와 불일치
- IS NULL : = 연산자, 값이 NULL일때 사용, IS NOT NULL은 반대 의미
- WHERE ROWNUM = 1; -> 행 숫자 한 개만 출력, 1등 출력할 때 사용하면 좋음
ex) SELECT NAME FROM (SELECT * FROM 테이블명 ORDER BY 순위나누는기준컬럼명) WHERE ROWNUM = 1;
<GROUP BY 절>
- 속성값을 그룹으로 분류하고자 할 때 사용
- GROUP BY 컬럼명1, ... : 컬럼명1로 그룹을 만든다.
- SELECT 절에 그룹함수(통계함수) 사용시, 일반컬럼은 반드시 GROUP BY 절에 기술해야 한다.
- 중첩된 그룹함수 결과값이 1개인 경우 오류발생. ex) AVG(COUNT(*))는 결과가 1개이므로 GROUP BY 사용시 오류
<HAVING 절>
- GROUP BY에 의해 분류한 후 그룹에 대한 조건을 기술
- 사용법은 WHERE과 같다.
- HAVING은 그룹을 제한하는 것이므로, ROW를 제한하는 것은 WHERE을 사용한다.
ex) 6000만원이 넘는 부서만 선택 -> ~ GROUP BY 부서 HAVING 급여합계 >= '6000';
- GROUP BY 없이 HAVING절 사용가능. 이때는 반드시 HAVING절에 그룹함수(통계함수) 사용해야 한다.
<ORDER BY 절>
- 속성값을 정렬하고자 할 때 사용
- 기본적으로 오름차순으로 정렬
- ORDER BY DESC : 내림차순 정렬
- ORDER BY 절은 항상 SELECT 문의 마지막 절에 작성
- 컬럼의 순서로 주로 사용한다.
- SELECT절에 기술되지 않은 컬럼으로도 정렬가능
- 다만, GROUP BY이 먼저 수행된 경우, SELECT절에 기술되지 않고 COUNT(*)같은 경우가 아니면 오류 발생
- 여러 조건으로 정렬하는 경우에는 반점(,)으로 나누기
ex) ORDER BY AGE, DATETIME DESC; /* 나이로 정렬하고 같은 나이면 들어온 시간이 늦은 순서 먼저 조회 */
<TOP(N)>
- 가장 큰 값 N개를 출력
- SQL Server의 TOP N 질의문에서 N에 해당하는 값이 동일한 경우 함께 출력할려면, TOP(N) WITH TIES 옵션을 ORDER BY절과 함께 사용하여야 한다.
2) INSERT
데이터의 내용을 삽입할 때 사용하는 명령어이다.
CREATE는 테이블의 스키마를 생성하는 DDL이고, INSERT는 테이블 내의 값을 생성하는 DML이다.
- INSERT INTO 테이블명(컬럼명1, ...) VALUES(데이터1, ...);
컬럼명과 값 갯수, 타입, 순서 일치해야 한다.
모든 컬럼에 자료 입력시 컬럼 생략가능, 원하는 컬럼에 대한 자료만 입력가능 but 입력안된 컬럼에는 NULL 값 된다.
즉, 삽입 컬럼을 명시하지 않았을 경우 모든 컬럼을 삽입해야 한다.
- INSERT INTO 테이블명(컬럼명1, ...) SELECT 컬럼명1, ... FROM 테이블명2 WHERE 조건식;
다른 테이블의 자료를 읽어서 입력할 수 있다.
3) UPDATE
데이터의 내용을 변경할 때 사용하는 명령어이다.
ALTER는 테이블의 스키마를 변경하는 DDL이고, UPDATE는 테이블 내의 값을 변경하는 DML이다.
- UPDATE 테이블명 SET 속성명 = 데이터1, ... WHERE 조건;
조건 만족할 경우 특정 컬럼 값 변경한다.
만약 WHERE 절 생략하는 경우, 테이블의 해당 컬럼 값이 전부 수정된다.
4) DELETE
테이블의 내용을 삭제할 때 사용하는 명령어이다.
DROP은 테이블의 스키마를 삭제하는 DDL이고, DELETE는 테이블 내의 값을 삭제하는 DML이다.
- DELETE FROM 테이블명 WHERE 조건;
조건에 만족하는 경우 내용 삭제, 테이블 구조는 남아있다.
DELETE는 행의 삭제를 의미, 컬럼의 값을 지울려는 것은 UPDATE로 수정해야 한다.
4. DCL
(1) 개념
DCL(Data Control Language)는 DB관리자가 데이터 보안, 무결성 유지, 병행수행 제어, 회복을 위해 사용하는 언어이다.
(2) 명령어
1) GRANT
데이터베이스 관리자가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어이다.
- GRANT 권한 TO 사용자; : 관리자가 사용자에게 테이블, 뷰, 프로시저 등을 생성, 삭제할 수 있는 권한 부여
- GRANT 권한 ON 테이블 TO 사용자; : 테이블을 수정, 삽입, 삭제, 조회, 프로시저 실행할 수 있는 권한 부여
- GRANT 구문 마지막에 WITH GRANT OPTION : 필요한 경우 다른 사용자에게 권한 부여가능
- SELECT * FROM USER_TAB_PRIVS_MADE; : 자신이 준 권한 확인하기
2) REVOKE
데이터베이스 관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어이다.
- REVOKE 권한 FROM 사용자; : 권리자가 사용자에게 테이블, 뷰, 프로시저 등을 생성, 삭제할 수 있는 권한 회수
- REVOKE 권한 ON 테이블 FROM 사용자; : 테이블을 수정, 삽입, 삭제, 조회, 프로시저 실행할 수 있는 권한 회수
- REVOKE 구문에 CASCADE CONSTRAINTS : WITH GRANT OPTION으로 부여된 사용자들의 권한까지 회수
- SELECT * FROM USER_TAB_PRIVS; : 자신이 받은 권한 확인하기
3) TCL 명령어
- COMMIT : 트랜잭션 확정
- ROLLBACK : 트랜잭션 취소
- SAVEPOINT 이름; : 특정 지점을 지정
- ROLLBACK TO SAVEPOINT 이름; : 세이브포인트 이후 발생한 트랜잭션 취소
5. 기타 문법
(1) 특징
- COLUMN 이나 TABLE에서는 스페이스바 사용할 수 없다.
- SQL은 대소문자를 구분하지 않는다. 다만, 따옴표 안에서는 구분한다.
- 줄바꿈은 필요없다. 다만, 끝에는 항상 ; 을 써야한다. ; 으로 문장을 구분한다.
- 여러 스페이스 바는 하나의 스페이스바로 인식한다.
(2) 산술 표현식
- + : 더하기
- - : 빼기
- * : 곱하기
- / : 나누기
(3) NULL
사용할 수 없고, 지정되지 않고, 알려져 있지 않고, 적용할 수 없는 값
연산하고자 하는 값 중 하나라도 NULL 값을 가지면 연산의 결과는 무조건 NULL
(4) 주석
- -- : 해당 라인 끝까지 실행되지 않도록 함
- /* 문장들 */ : /*이 시작하는 부분부터 */이 나타날 때까지 여러 라인에 걸쳐 실행되지 않도록 함
(5) 힌트
실행하려 하는 SQL 문에 사전에 정보를 주어서 실행시 빠른 결과를 가져오는 효과를 만든다.
주석에 + 기호를 붙이면 힌트로 인식한다.
(6) 쿼리 순서
- 쿼리 작성 순서
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
- 쿼리 실행 순서
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
따라서 HAVING 절에서는 SELECT 절에서 정의한 별칭 등을 쓸 수 없다.
이거에 따라서 WHERE과 HAVING 사용해야하는지 결정가능
(7) 서브 쿼리
- 단독 쿼리문만으로는 해결할 수 없는 검색 수행시 사용
- 서브쿼리가 메인쿼리보다 먼저 수행, 반드시 소괄호로 감싸서 수행, 비교연산자의 우측에 위치
- 서브쿼리 결과값이 NULL이면, 메인쿼리 결과도 NULL
- 유형으로 SELECT 절 서브쿼리, FROM 절 서브쿼리, WHERE 절 서브쿼리가 있다.
- 단일행 서브쿼리 : 서브쿼리 결과값이 하나, 단일행 비교연산자(=, <, <=, >, >=, <>)와 함께 작성
- 다중행 서브쿼리 : 서브쿼리 결과값이 둘 이상, 다중행 비교연산자와 함께 작성
- 다중컬럼 서브쿼리 : 결과가 여러 컬럼으로 반환
<다중행 비교연산자>
- IN : 서브쿼리 결과에 존재하는 임의의 값과 동일한 조건을 의미
- EXIST : 서브쿼리 결과를 만족하는 값 존재여부를 확인하는 조건을 의미
- < ANY : ANY 뒤에 있는 것들 중 하나보다 작다.
- > ANY : ANY 뒤에 있는 것들 중 하나보다 크다.
- > ALL : ALL 뒤에 있는 것들 모든 것보다 크다.
- < ALL : ALL 뒤에 있는 것들 모든 것보다 작다.
ex) SELECT ANIMAL_ID, NAME FROM ANIMAL_OUTS WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS) ORDER BY ANIMAL_ID;
조인 잘 못쓰면 서브쿼리를 쓰기(WHERE절에 적용시키면 됨)
