SQL 문법 | Notion

데이터베이스를 조작하여 원하는 자료를 추출하거나 필요한 데이터를 추가, 삭제, 수정하기 위한 비철차적 프로그래밍 언어
관계형 데이터베이스를 조작하는 언어
관계대수와 관계해석을 기초로 한 혼합 데이터 언어
독자적이고 상호 작용 형태로 터미널에서 많이 사용하고 있는 비절차적 언어
DDL, DML, DCL을 포함한 데이터베이스용 질의어

DDL(Data Definition Language)

데이터 정의어
스키마, 도메인, 테이블, 뷰, 인덱스를 정의, 변경, 삭제시에 사용되는 언어


-- <CREATE>
-- 데이터베이스, 도메인, 테이블, 뷰, 인덱스를 생성

-- <스키마 정의/설정>
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID;
-- 스키마의 식별을 위한 스키마 이름과 해당 스키마의 소유권자 혹은 허가권자를 정의

-- <도메인 정의>
CREATE DOMAIN 도메인명 데이터타입 [DEFAULT 기본값] [CONSTRAINT 제약조건명 CHECK (범위값)];
CREATE DOMAIN 지역 CHAR(2) DEFAULT '서울';

-- <인덱스 정의>
CREATE [UNIQUE] INDEX <인덱스명> ON 테이블명({속성명 [ASC | DESC][,속성명 [ASC | DESC]]})
[CLUSTER];

-- UNIQUE
-- 사용된 경우 : 중복 값이 없는 속성으로 인덱스 생성
-- 생략된 경우 : 중복 값이 허용하는 속성으로 인덱스 생성

-- 정렬 여부 지정 (기본값 ASC)
-- ASC : 오름차순 정렬
-- DESC : 내림차순 정렬

-- CLUSTER: 지정된 키에 따라 튜플들을 그룹으로 지정하기 위해 사용

-- <테이블 정의>
CREATE TABLE 대여정보
대여번호 INT NOT NULL, -- 대여번호 속성은 정수로 NULL 값을 가지지 않음
대여일자 DATE, -- 대여일자는 현재 날짜로 표시
대여자번호 CHAR(6), -- 대여자번호는 6자리로 NULL값을 가지지 않음
도서번호 VARCHAR(6), -- 도서번호는 최대 6자리를 갖음
PRIMARY KEY(대여번호), -- '대여번호'  속성을 기본키로 지정
FOREIGN KEY(대여자번호) REFERENCES 고객(고객번호) -- '대여자번호'가 고객 테이블의 '고객번호'를 참조하도록 외래키 지정
ON UPDATE CASCADE, -- 고객의 '고객번호'를 변경할시 '대여자번호'도 함께 변경되도록 설정
CHECK (대여일자 >= '2012-10-1'); -- 대여일자는 2012년 10월 1일 이후 값만 저장 가능
-- 옵션
-- CONSTRAINT - 제약조건의 이름을 지정, 이름을 지정할 필요가 없으면 CHECK만 사용
-- CHECK - 속성값에 대한 제약조건을 지정
-- ON DELETE왜 ON UPDATE의 옵션 종류
NO ACTION -- 참조 테이블에 변화가 있어도 기존 테이블에는 아무런 조치를 취하지 않음
CASCADE -- 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플 속성 값도 모두 변경됨
SET NULL -- 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플 값을 NULL 변경
SET DEFAULT -- 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플 속성값을 기본값으로 변경함

-- <VIEW>
CREATE VIEW 뷰이름[(속성명[,속성명,...])]
AS SELECT ~
[WHERE 조건문]
[WITH CHECK OPTION]

-- AS SELECT
-- 일반 SELECT문과 같으며, UNION이나 ORDER BY를 사용할 수 없음

-- WITH CHECK OPTION
-- 뷰에 대한 갱신이나 삽입 연산이 실행될 때 뷰의 정의 조건을 위배하면 실행을 거부함

-- 속성명 비 작성시
-- SELECT문의 속성명이 자동으로 뷰의 속성명이 됨

-- <ALTER>
-- 데이터베이스, 테이블 구조를 변경

ALTER TABLE 테이블명 ADD|ALTER|MODIFY|DROP 속성이름
-- ADD : 새로운 속성 추가
-- ALTER : 속성의 기본값 변경
-- DROP : 속성을 삭제
-- MODIFY : 기존 속성을 수정

-- 예시
ALTER TABLE 학생 ADD 학년 VARCHAR(3);

-- <DROP>
-- 스키마, 도메인, 테이블, 뷰, 인덱스를 삭제

-- 스키마 삭제
DROP SCHEMA 스키마이름;

-- 테이블 삭제
DROP TABLE 테이블명 CASCADE|RESTRICT;
-- CASCADE : 삭제할 요소를 참조하고 있는 모든 개체를 연쇄적으로 삭제
-- RESTRICT : 삭제할 요소를 다른 개체에서 참조하고 있으면 삭제하지 못함

-- 뷰 삭제
DROP VIEW 뷰이름 [RESTRICT | CASCADE];

DML(Data Manipulation Language)

데이터 조작어
사용자로 하여금 데이터를 처리할 수 있게 하는 도구로서, 사용자나 응용 프로그램과 DBMS간의 인터페이스를 제공하는 언어

-- SELECT
-- 데이터베이스에서 사용자가 원하는 조건을 만족하는 자료를 검색

-- <요약>
SELECT [PREDICATE] [테이블.]속성명 [AS 별칭][,[테이블.]속성명 [AS 별칭],...]
FROM 테이블이름
[WHERE 조건]
[GROUP BY 속성명[, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC][, 속성명 [ASC|DESC],...]

-- SELECT절
	-- PREDICATE : 검색할 튜플을 제한 할 목적으로 사용
	-- PREDICATE 종류
		-- ALL : 모든 튜플을 검색할 때 지정하는 것으로, 주로 생략
		-- DISTINCT : 중복된 튜플이 있으면 그 중 첫 번째 한 개만 검색
		-- DISTINCTROW : 중복된 튜플을 제거하고 한 개만 검색하지만 선택된 속성의 값이 아닌, 튜플전체를 대상으로 함
	-- 속성명 : 검색하여 불러올 속성 또는 수식
	-- 속성명 수식 방법
		-- * : 기본 테이블을 구성하는 모든 속성을 지정
		-- 테이블.속성명 : 2개 이상의 테이블 대상으로 검색시 사용
	-- AS : 속성 및 연산의 이름을 다른 명칭으로 표기
-- FROM절 : 검색할 데이터가 들어있는 테이블명을 기술
-- WHERE절 : 검색할 조건을 기술
-- GROUP BY절 : 특정 속성을 기준으로 그룹화 하여 검색
-- HAVING절 : 그룹에 대한 조건 기술 - GROUP BY 필수
-- ORDER BY : 특정 속성을 기준으로 정렬 - ASC 기본값
	-- ASC : 오름차순
	-- DESC : 내림차순

-- <규칙>
-- SQL 문에서는 대,소문자를 구분하지 않음
-- 마지막에 ';'를 입력해 SQL 문의 끝을 알림
-- DISTINCT SELECT 문에 'DISTINCT'를 입력하면 검색의 결과가 중복되는 레코드는 검색시 한번만 표시됨
-- 필드이름 : 테이블의 모든 필드를 검색할 경우에는 필드 이름 대신 '*'를 입력
-- WHERE 조건문 : 조건을 입력하여 특정 조건에 맞는 레코드만 검색할 때 사용함

SELECT * FROM 고객; -- 고객 테이블의 모든 필드를 검색
SELECT 고객명, 전화번호, 주소 FROM 고객 WHERE 성별 = "여"; -- 고객 테이블에서 성별이 "여"인 고객명, 전화번호, 주소를 검색한다.
SELECT * FROM 고객 WHERE 이름 LIKE '김%'; -- 고객 테이블에서 이름이 '김'으로 시작하는 모든 고객을 검색한다

-- % 기호는 모든 글자를 대신하는 SQL 표준 와일드카드

-- <형식1>
ORDER BY 학번 ASC, 과목번호 DESC; -- 학번 기준 오름차순, 같은 학번에 대해서는 과목 번호 기준 내림차순 정렬
-- 정렬 방식을 지정하지 않으면 기본적으로 오름차순
SELECT 고객명, 전화번호, 주소, 성별 FROM 고객등록 WHERE 성별 = "여" ORDER BY 고객명;
-- 고객등록 테이블에서 성별이 "여"인 데이터의 고객명, 전화번호, 주소, 성별을 검색하여 고객명을 오름차순으로 정렬하여 검색한다.

-- <형식2>
GROUP BY 필드이름 -- 특정 필드를 기준으로 그룹화하여 검색할 때 사용
HAVING 그룹조건식 -- 그룹에 대한 조건을 지정할 때 사용(GROUP BY ~와 언제나 함께 사용)

SELECT 출산지, COUNT(*) AS 인원수 FROM 고객등록 GROUP BY 출신지 HAVING COUNT(*) >= 5;
-- 고객등록 테이블에서 출산지별 인원수를 계산하여 표시하는 질의로서 출산지 인원이 5명 이상인 데이터에 대해서만 표시

-- <형식3>
BETWEEN 1 AND 100 -- 1에서 100까지의 숫자만 검색

SELECT * FROM 사원 WHERE 생일 BETWEEN '01/09/69' AND '10/22/73';
-- 사원 테이블에서 생일이 '01/09/69'에서 '10/22/73'사이인 튜플을 검색

-- <형식4>
NOT IN (..) -- 포함되지 않는 데이터를 의미

SELECT * FROM 사원 WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
-- 사원 테이블에서 모든 자료를 검색하는데, 여가활동 테이블에 이름이 있는 자료는 제외하고 검색
-- 불일치 쿼리문이라고도 함

-- <형식5>
UNION -- 두 테이블을 합치는 질의, 두 테이블 모두 속해있는 자료는 한개만 표시

SELECT * FROM 사원 UNION SELECT * FROM 직원;
-- 사원들의 명단이 사원 테이블과 직원 테이블에 저장되어 있음 두 테이블을 통합하는 질의를 작성. 단, 같은 레코드가 두번 나오지 않음

-- <형식6> INNER JOIN
-- WHERE 절 활용
SELECT [테이블명1.]속성명, [테이블명2.] 속성명,...
FROM 테이블명1,테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명;
-- NATURAL JOIN 활용
SELECT [테이블명1.]속성명, [테이블명2.] 속성명,...
FROM 테이블명1 NATURAL JOIN 테이블명2;
-- JOIN ~ USING절 활용
SELECT [테이블명1.]속성명, [테이블명2.] 속성명,...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

-- <형식 7> OUTER JOIN
-- <LEFT OUTER>
-- 왼쪽 릴레이션 모두 표시
SELECT [테이블명1.]속성명, [테이블명2.] 속성명,...
FROM 테이블명 1 LEFT OUTER JOIN 테이블명2
ON 테이블1.속성명 = 테이블명2.속성명;

SELECT [테이블명1.]속성명,[테이블명2.]속성명,,.. 
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+)

-- <RIGHT OUTER>
-- 오른쪽 릴레이션 모두 표시
SELECT [테이블명1.]속성명,[테이블명2.]속성명,,.. 
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1.]속성명,[테이블명2.]속성명,,.. 
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

-- <FULL OUTER>
-- 모든 릴레이션 모두 표시
SELECT [테이블명1.]속성명,[테이블명2.]속성명,,.. 
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

-- <주요 함수>
조건1 AND 조건2 -- 조건1과 조건2를 모두 만족하는 튜플 검색
조건1 OR 조건2 -- 조건1 또는 조건2 중에 한개 이상 만족하는 튜플 검색
LEN(문자열) -- 문자열의 길이
UPPER(문자열) -- 문자열을 대문자로 변환
LOWER(문자열) -- 문자열을 소문자로 변환

-- <집단 함수>
SUM(속성명) -- 속성의 합계를 구함
AVG(속성명) -- 속성의 평균을 구함
COUNT(*) -- 그룹별 튜플의 개수 계산
MAX(속성명) -- 속성의 최대값을 구함
MIN(속성명) -- 속성의 최소값을 구함

-- INSERT
-- 테이블에 레코드를 추가할때 사용
-- 값을 지정하여 추가할 수도 있고 다른 테이블의 레코드를 추출하여 추가할 수도 있음.
-- 레코드의 전체 필드를 추가할 경우에는 필드명 생략 가능

-- <형식1> 직접 추가
INSERT INTO 테이블이름 (필드1,필드2,...) VALUES(필드값1,필드값2,...)

INSERT INTO 도서등록(도서번호,도서명,도서구분) VALUES('0001','유관순','위인전');
-- 도서등록 테이블에 도서번호 0001, 도서명 유관순, 도서구분 위인전을 추가

-- <형식2> 테이블에서 추가
INSERT INTO 테이블이름(필드이름1,필드이름2,..) SELECT 필드이름 FROM 테이블 이름 WHERE 조건

INSERT INTO 도서목록(도서번호, 도서명, 도서구분)
SELECT 도서번호, 도서명, 도서구분
FROM 도서등록
WHERE 도서구분 = '소설';
-- 도서등록 테이블에서 도서구분이 '소설'인 도서번호, 도서명, 도서구분을 찾아 '도서목록' 테이블에 추가

-- DELETE
-- 테이블에서 조건에 맞는 행을 삭제하거나 전체 행을 삭제

DELETE * FROM 테이블이름 WHERE 조건

DELETE * FROM 도서등록 WHERE 도서구분 = '소설'
-- 도서등록 테이블에서 도서구분이 소설인 레코드 삭제

-- UPDATE
-- 테이블의 필드 값을 변경할 때 사용
-- 조건을 지정하여 한번에 여러 레코드의 필드값을 변경

UPDATE 테이블이름 SET 필드이름1 = 값1, 필드이름2 = 값2 WHERE 조건

UPDATE 도서등록 SET 도서구분 = '에세이' WHERE 도서구분 = '수필';
-- 도서등록 테이블에서 도서구분이 수필인 것을 찾아 모두 에세이로 변경

DCL(Data Control Language)

데이터 제어어
관리
- 데이터 무결성 유지
- 보안 권한 검사
- 회복절차 이행
- 병행 수행 제어
종류
- GRANT
  - 생성된 데이터베이스의 사용권한 부여
  - 사용자 권한
    - DBA : 데이터베이스 관리자
    - RESOURCE : 데이터베이스 및 테이블 생성 가능자
    - CONNECT : 단순 사용자
- REVOKE
  - 부여된 사용권한 제거
- COMMIT
  - 데이터베이스 관련 연산작업 실행 결과를 저장하도록 하는 명령
  - 명령 실행결과 DB에 적용
  - 명령에 의해 수행된 결과를 실제 무리적 디스크로 저장
  - 데이터베이스 조작 작업이 정상적으로 완료 되었음을 관리자에게 알려줌
- ROLLBACK
  - 실행 중인 데이터베이스 연산작업을 실행 이전의 원래 상태로 되돌림
  - 데이터베이스 조작 작업이 비정상적으로 종료되었을 떄 원래의 상태로 복구