SQL

DML (Data Manipulation Laguage)

- 테이블의 데이터 조작

- CRUD (Creat, Retrieve, Update, Delete)

INSERT # 데이터 입력
DELETE # 데이터 삭제
UPDATE # 데이터 수정
SELECT # 데이터 조회

INSERT INTO 테이블명(컬럼명) VALUES (값);
DELETE FROM 테이블명 WHERE 조건;
UPDATE 테이블명 SET 컬럼명=값, ... WHERE 조건;
SELECT 컬럼명 FROM 테이블명 WHERE 조건;

 

DISTINCT

- 중복 제거

 

논리연산자 

- AND

- OR 

- NOT 

- IN ( ... )

- BETWEEN 00 and 00

 

정렬 

- ORDER BY 컬럼명 ASC(기본값: 오름차순)/DESC

 

결과값 일부 조회 

- LIMIT(오라클: ROWNUM, SQLServer: TOP) 

 

집합 함수

- 특정 컬럼에 적용해서 한개의 값을 리턴 

- COUNT(컬럼) : 레코드 개수

- SUM(컬럼) / AVG(컬럼) : 컬럼값의 합/평균

- MIN(컬럼) / MAX(컬럼) : 컬럼값의 최소/최대

- FIRST(컬럼) / LAST(컬럼) : 첫 번째 값, 마지막 값 (정렬시 쓰기 좋음) == 정렬 후 LIMIT 1;  

 

 

함수 

- LENGTH(컬럼) : 레코드 문자열 컬럼의 글자수 

- MID(컬럼, a, b) : 문자열의 중간(a부터 b자리)부분

- UPPER(컬럼) / LOWER(컬럼) : 문자열을 대문자/소문자로 

- ROUND(컬럼, a) : 레코드 숫자컬럼값을 반올림(a자리까지) 

 

 

JOIN 

- 서로 다른 테이블을 공통 컬럼을 기준으로 합치는 테이블 단위 연산

SELECT * FROM 테이블1 JOIN 테이블2 ON 테이블1.컬럼명 = 테이블2.컬럼명;

 

- INNER JOIN : 조인 시 NULL 값 허용하지 않음

- LEFT JOIN : 조인 시 왼쪽 테이블의 NULL 값 포함 

- RIGHT JOIN : 조인 시 오른쪽 테이블의 NULL 값 포함 

- FULL JOIN : MySQL 지원하지 않음 (UNION 사용가능)

 

 

ALIAS

- SQL 쿼리 결과 생성시 컬럼명에 대한 별명을 사용하여 표시

- 조인 시 많이 사용됨

SELECT 테이블1.컬럼명1 AS 별명1, 테이블2.컬럼명2 AS 별명2 
FROM 테이블1 JOIN 테이블2 ON 테이블1.컬럼명 = 테이블2.컬럼명;

 

VIEW

- SQL 쿼리 값을 임시 테이블로 저장해서 사용

- 사용 후 삭제해야 함

CREATE VIEW 뷰이름 AS SELECT ..; # 뷰 생성
SELECT * FROM 뷰이름;            # 뷰 확인
DROP VIEW 뷰이름;                # 뷰 삭제

 

 

SELECT INTO 

- SQL 쿼리 결과 새 테이블로 만듬

- MySQL : CREATE TABLE 테이블명 SELECT * FROM 테이블명

CREATE TABLE 테이블명 SELECT * FROM 테이블명 # MySQL

 

 

INSERT INTO SELECT 

- SQL 쿼리 결과를 기존 테이블에 추가 (기존 테이블 존재해야 함)

- SELECT와 INSERT 하는 테이블은 동일한 구조를 가져야 함 

- 두개의 별도 쿼리를 하나로 합침

INSERT INTO 테이블명1 SELECT * FROM 테이블명2 WHERE 조건 ... ;

 

 

CASE WHEN ... END

- 조건문 

- 조건값에 따른 처리 구분 가능

CASE WHEN 조건1 THEN ... 
     WHEN 조건2 THEN ... 
     ELSE ... 
END

 

 

LIKE 검색 

- 와일드 카드 (%:0-N글자, _: 1글자) 를 사용하여 패턴 매칭

- 매칭으로 DBMS에 부담이 많아 OR 같은 조건자 중복해서 사용하지 않는게 좋음 (ex. 컬럼명1 like '_' or 컬럼명2 like '_') 

 

NULL

- 해당 컬럼의 값이 없다 (컬럼이 NULL 값 허용하는 경우)

- IS NULL / IS NOT NULL : NULL 값인 컬럼 / 아닌 컬럼

 

NULL 함수

- 숫자 연산시 NULL 처리하는 함수 

- NULL 값이 나오면 다른 값(주로 0)으로 대체해서 계산에 문제 없도록 처리 

- IFNULL / COALESCE (MySQL), ISNULL(SQL Server), NVL(오라클)

- 숫자연산/집합함수(SUM) 에는 처리가 내장되어 있음

 

집합합수

- 그룹별 연산 적용

SELECT 컬럼명, 집합함수명(컬럼명) FROM 테이블명 GROUP BY 컬럼명 HAVING 조건;

 

 

서브쿼리

- 쿼리 안에 다른 쿼리문이 있는 형태

- () 로 감싸 사용

- ORDER BY 를 사용할 수 없음

- 사용가능 위치 : SELECT, FROM,WHERE, HAVING, ORDER BY, VALUES(INSERT), SET(UPDATE) ...

 

- 단일행 서브쿼리 

  : 결과가 레코드 하나, 일반연산자(=,>,<,..) 사용

- 다중행 서브쿼리

  : 결과가 레코드 여러개, 다중행연산자(IN, ALL, ANY, EXISTS) 사용

- 다중컬럼 서브쿼리

  : 결과가 컬럼 여러개 

 

다중행연산자(IN, ALL, ANY, EXISTS)

- ALL : 여러개 래코드의 AND , 컬럼 > ALL (..) : 가장 큰 값 보다 큰

- ANY : 여러개 레코드의 OR,  컬럼 > ANY (..) : 가장 작은 값보다 큰

- IN / EXISTS

  : 결과값 중에 있는 것 

  IN : 전체 스캠

  EXISTS : 존재 여부만 확인 (속도 빠름), TRUE/FALSE 가 넘어옴

 

 

집합연산

- 합집합(UNION), 교집합(INTERSECT), 차집합(MINUS)

- MySQL은 교집합/차집합 지원하지 않음 

 

- UNION

  : 두 쿼리의 결과 값을 합쳐서 리턴 

  두 쿼리의 결과 형식이 동일해야 함 (다른 테이블이여도 결과 형식만 동일하면 가능) 

  기본적으로 DISTINCT 적용

- UNION ALL 

  : 중복을 허용하는 UNION 

 

- INTERSECT

  : 두 쿼리의 결과 중 공통 값만 리턴

  두 쿼리의 결과 형식이 동일해야 함 (다른 테이블이여도 결과 형식만 동일하면 가능) 

  기본적으로 DISTINCT 적용

 

- MINUS / EXCEPT

  : 쿼리1 결과 에서 쿼리2 결과를 빼서 리턴 

# 합집합
SELECT 쿼리1 UNION SELECT 쿼리2 UNION ... 
SELECT 쿼리1 UNION ALL SELECT 쿼리2 UNION ...

# 교집합
SELECT 쿼리1 INTERSECT SELECT 쿼리2 

# 차집합
SELECT 쿼리1 MINUS SELECT 쿼리2

 

 

 

DDL (Data Definition Language)

- 데이터베이스와 테이블의 스키마 정의, 수정

- 테이블 생성, 컬럼 추가, 타입 변경, 제약조건 지정, 수정 ... 

- 테이블에 대한 정보는 메타데이터로 데이터 사전에 저장 및 관리

CREATE DATABASE 디비명 # DB 생성
CREATE TABLE 테이블명 (컬럼명1 데이터타입(크기), 컬럼명2 ...) # 테이블 생성

DROP   # DB 객체 삭제
ALTER  # 기존의 DB 객체 재정의

 

 

자료형 

- 정수형 (부호 있음/없음) : TINYINT(-128~127 / 255), INT(-21억~21억 / 43억), BIGINT(-9경~9경 / 18경)

- 실수형 (길이, 소수점 이하 자리수) : FLOAT(size, d), DOUBLE(size, d), DECIMAL(size, d)

- 문자열 : CHAR(고정길이 문자열, ~255자), VARCHAR(가변길이 문자열, ~255자)

 

- TEXT 문자열 : TEXT(~65,535자), MEDIUMTEXT(~16,777,215자), LONGTEXT(~4,294,967,295자)

- BLOB(Binary Large OBjct) : BLOB(~65,535byte), MEDIUMBLOB(~16,777,215byte), LONGBLOB(~4,294,967,295byte)

- 시간 관련 : DATE(YYYY-MM-DD), TIME(HH:MM:SS), DATETIME(YYYY-MM-DD HH:MM:SS), TIMESTAMPE(YYYY-MM-DD HH:MM:SS)

 

 

제약조건 

- 입력 데이터의 제약조건

 

- NOT NULL : NULL값은 안됨

- UNIQUE : 동일한 값이 있을 경우 안됨

- PRIMARY KEY : 기본키 제약조건 (UNIQUE, NOT NULL)

- FOREIGN KEY : 외래키 제약조건

- CHECK : 입력값 체크 (MySQL에서는 안됨)

- DEFAULT : 기본값 설정 

 

- AUTO_INCREMENT : 자동증가, 기본키에 자동으로 값을 추가 

 

 

ALTER 

- 스키마 수정 

# 제약조건 추가/삭제
ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명);
ALTER TABLE 테이블명 ADD FOREIGN KEY (컬럼명) REFERENCES 테이블명(컬럼명);
ALTER TABLE 테이블명 ADD CONSTRAINT 제약이름 UNIQUE(컬럼명1, 컬럼명2); # UNIQUE 추가 
ALTER TABLE 테이블명 DROP PRIMARY KEY;
ALTER TABLE 테이블명 DROP FOREIGN KEY 컬럼명;

# 테이블명 변경
ALTER TABLE 테이블명 RENAME 새테이블명;

# 컬럼 추가/삭제/수정
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE 테이블명 CHANGE 컬럼명 새컬럼명 데이터타입; # 컬럼명 변경
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;        # 컬럼타입 변경

 

 

DROP 

- 스키마 삭제

# DB 삭제
DROP DATABASE 디비명; 

# 테이블 삭제
DROP TABLE 테이블명; # 테이블,내용 전체 삭제
DELETE * FROM 테이블명; # 레코드 일일이 하나씩 지움(시간오래걸림), 테이블 스키마는 유지
TRUNCATE TABLE 테이블명 # 테이블 내용만 지움, 테이블 스키마는 유지

 

 

정규형 

 

참조 무결성 

 

 

DCL (Data Control Laguage)

- DB, 테이블의 접근권한, CRUD 권한 정의

- DBA(Database Administrator) 가 설정 

GRANT  # DB 객체에 권한 부여
REVOKE # 기존의 DB 객체 권한 취소

 

 

권한 설정

CREATE USER 유저명@호스트 IDENTIFIED BY 비밀번호; # 유저 등록
DROP USER 유저명@호스트; # 유저 삭제
FLUSH PRIVILEGES; # DB에 반영

GRANT ALL PRIVILEGES ON 디비명.* TO 유저명@localhost IDENTIFIED BY 비밀번호; #localhost 접속 허용 
GRANT ALL PRIVILEGES ON *.* TO 유저명@'192.168.0.%' IDENTIFIED BY 비밀번호; # 로컬랜 접속 허용
GRANT ALL PRIVILEGES ON *.* TO 유저명@% IDENTIFIED BY 비밀번호; # 인터넷 전체 접속 허용

GRANT SELECT, INSERT ON 디비명.* TO 유저명@localhost IDENTIFIED BY 비밀번호; 
GRANT UPDATE(컬럼명) ON 디비명.테이블명 TO 유저명@localhost IDENTIFIED BY 비밀번호; 

REVOKE ALL PRIVILEGES ON *.* TO 유저명@localhost; # 권한 회수

SHOW GRANTS FOR 유저명; # 권한 보기

 

 

역할 설정 

- 역할 별 권한 설정 후 사용자에게 역할을 부여 

- 여러개의 역할 가지는 것 가능

- MySQL 에서는 ROLE 안됨

CREATE ROLE 역할명;
GRANT CRUD ON 테이블명 TO 역할명;
GRANT 역할 TO 사용자명;