Post

[CS 학습] SQL이란?

Posted Updated
By waterricecake
26 min read
[CS 학습] SQL이란?

1. SQL이란

SQL (Structure Query Languate) : 관계 데이터베이스를 위한 표준 질의어로 많이 사용되는 언어

SEQUEL(Structure English Query Language)에서 유례되었다. 이는 IBM에서 SYSTEM R을 다루기 위한 언어로 개발되었다. ISO(국제 표준화 기구)와 ANSI(미국 표준 연구소)에서 SQL을 관계 데이터베이스 표준 질의어로 채택하고 표준화 작업을 진행하면서 표준 질의어로 사용되게 되었다.

1.1. SQL 분류

SQL은 기능에 따라 DDL, DML, DCL로 나뉜다.

1.1.1. 데이터 정의어 (DDL, Data Definition Language)

테이블을 생성하고 변경 삭제하는 기능을 제공한다.

  • 테이블 생성 : CREATE TABLE
  • 테이블 변경 : ALTER TABLE
  • 테이블 삭제 : DROP TABLE

1.1.1.2. CREATE TABLE

새로운 테이블을 생성하는 구문이다

1
2
3
4
5
6
7

CREATE TABLE 테이블_명 (
	속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본값] // 1.
	[PRIMAR KEY (속성_리스트)] // 2.
	[UNIQUE (속성_리스트)] // 3.
	[FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션] // 4.
	[CONSTRAINT 속성_이름] [CHECK(조건)] // 5.
)

다음과 같은 구조로 이루어져 있다

  1. 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본값] 테이블의 속성명과 그 타입과 기본적인 제약 사항(NOT NULL, DEAFAULT)을 정의
  2. PRIMAR KEY (속성_리스트) 기본키를 정의한다. 테이블당 하나씩밖에 존재할 수 있다. 생략 가능하다.
  3. UNIQUE (속성_리스트)] Unique 제약 조건을 부여하는 속성이다.
  4. FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션] 외레키를 설정하는 옵션이다. 여기서 살펴보면 좋은 옵션은 CASACADE인데 이는 참조하는 키가 삭제시 같이 삭제해달라는 옵션이다. -> 참조 무결성을 지키는 방법이다.
  5. `CONSTRAINT 이름 CHECK(조건) 데이터의 무결성을 위한 제약조건을 정의한다.

예시)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE MEMBER(
	id INT NOT NULL UNIQUE PRIMARY KEY, // Column에서 pk나 unique를 설정할 수 있다.
	name VARCHAR(20) NOT NULL,
	age INT NOT NULL
);
CREATE TABLE ITEM(
	id INT NOT NULL,
	owner INT NOT NULL,
	name VARCHAR(20) NOT NULL,
	PRIMARY KEY(id),
	price INT NOT NULL,
	FOREINGN KEY(owner) REFERENCES MEMBER(id),
	CHECK(price >= 0)
)

1.1.1.2. ALTER TABLE

테이블을 변경하는 구문이다

  • 테이블 속성 추가
    1

    ALTER TABLE MEMBER ADD CREATE_DATE DATE;
  • 테이블 속성 삭제
    1

    ALTER TABLE MEMBER DROP COLUMN age;

    이때 다른 테이블에 제약 사항이 묶여있는 경우 삭제가 불가능하다. 즉 해당 속성을 외래키로 사용하는 테이블의 제약사항을 제거후에 속성 삭제가 가능하다

  • 속성 제약사항 추가
    1

    ALTER TABLE MEMBER ADD CONSTRAINT CHECK_AGE CHECK(age > 0);
  • 속성 제약사항 삭제
    1

    ALTER TABLE MEMBER DROP CONTRAINT CHECK_AGE;

    1.1.1.3. DROP TABLE

테이블을 삭제하는 구문이다. 이때 해당 테이블을 참조하는 테이블이 있을 경우 삭제가 되지 않는다.

1
2
3
4

// DROP TABLE MEMBER; // 수행되지 않음 -> ITEM이 참조하고 있기 때문에

DROP TABLE ITEM;
DROP TABLE MEMBER;

1.1.2. 데이터 조작어 (DML. Data Manipulation Language)

테이블에 새 데이터를 삽입하거나 테이블에 저장된 데이터를 수정 삭제 검색하는 기능을 제공한

  • 데이터 조회 : SELECT
  • 데이터 삽입 : INSERT
  • 데이터 수정 : UPDATE
  • 데이터 삭제 : DELETE

1.1.2.1. SELECT

데이터를 조회하는 구문이다

1

SELECT name, age FROM MEMBER;
nameage
철수10
영희20

가장 기본적인 선택 구문이다. 이 경우 위 MEMBER 테이블에서 name, age의 데이터를 가져와 준다. 모든 속성을 가져오고 싶다면 *을 사용하면된다

1

SELECT * FROM MEMBER;
idnameage
1철수10
2영희20

이 외에도 ALL, DISTINT 키워드가 존제한다. 조회하는 속성이 UNIQUE가 아닐 경우 중복된 값이 나올 수 있는데 ALL의 경우 모든 값(중복 허용)을 가져오고 DISTINT의 경우 중복을 제거하고 한번씩만 가져오도록 하는 키워드이다.

1
2

SELECT ALL age FROM MEMBER;
SELECT DISTINT age FROM MEMBER;
age
10
10
20
age
10
20

또한 값을 가져올때 속성명을 변경할 수도 있다.

1

SELECT price AS cost FROM ITEM;
cost
10
1.1.2.1.1. 조건 검색

검색시 조건을 통해 검색도 가능하다

1

SELECT age FROM MEMBER WHERE age >10 AND age < 30;

문자열에서도 조건을 줄 수 있는데 이는 LIKE키워드를 활용해야 한다.

% : 0개 이상의 문자 _ : 1개의 문자

| LIKE ‘aa%’ | aa로 시작하는 문자 | | ———— | ——————— | | LIKE ‘%aa’ | aa로 끝나는 문자 | | LIKE ‘%aa%’ | aa가 포함된 문자 | | LIKE ‘aa___’ | aa로 시작되는 길이 5(개수) 문자 | | LIKE ‘__aa’ | aa로 끝나는 길이 4( 개수) 문자 |

1

SELECT * FROM MEMBER WHERE name LIKE '김%';
1.1.2.1.2. 정렬 검색
1

SELECT * FROM member ORDER BY age ASC;

다음과 같이 검색시 해당 데이터를 ORDER BY 뒤 속성을 기준으로 정렬을 해준다 (정렬 기준은 ASC, DESC)

1.1.2.1.3. 집계 함수 검색

통계적 결과 개수, 합계, 평균, 최댓값, 최솟값의 계산 결과를 조회할 수 있다.

  • 개수
    1

    SELECT COUNT(DISTINT name) FROM ITEM;
  • 합계 (숫자 속성만 가능)
    1

    SELECT SUM(price) FROM ITEM;
  • 평균 (숫자 속성에서만 가능)
    1

    SELECT AVG(age) FROM MEMBER;
  • 최대, 최소
    1

    SELECT MAX(age) FROM MEMBER;

여기서 중요한 점은 집계함수의 경우 WHERE절을 사용할 수 없는데 이는 다음에 나올 GROUP BY와 HAVING절을 활용해야 한다.

1.1.2.1.4. 그룹별 조회

테이블 내에서 특정 속성값이 같은 투플을 모아 그룹을 만들고 그룹별로 검색하는 키워드이다.

해당 그룹의 조건을 추가할떄에는 HAVING을 사용한다.

1

SELECT age, count(*) AS m FROM MEMBER GROUP BY age;

이 경우 age가 같은 member 별로 숫자를 세서 나타내 준다.

agem
102
201

조건추가의 경우

1

SELECT age, count(*) AS m FROM MEMBER GROUP BY age HAVING age > 10;

다음과 같이 조건을 부여할 수 있다.

또한 여러개의 그룹을 나눌수도 있는데

1

SELECT owner, name, count(*) AS m FROM ITEM GROUP BY owner, name;

이럴 경우 1차적으로 owner가 같은 그룹으로 나눈후 해당 그룹에서 name이 같은 그룹으로 다시 나눈다

| owner | name | m | | —– | —– | — | | 1 | car | 10 | | 1 | drink | 5 | | 2 | book | 2 |

1.1.2.1.5. 조인 검색

한 테이블에서만 조회하는 것이 아닌 여러 테이블에서 조회하는 기능을 조인 검색이라고 한다.

일반적으로 테이블의 외래키를 활용하여 검색한다.

1

SELECT * FROM MEMBER, ITEM WHERE MEMBER.id = ITEM.owner;

또한 참조하는 테이블의 명을 약식으로 지어줄 수 도 있는데

1

SELECT * FROM MEMBER m, ITEM i WHERE m.id = i.owner;

다음과 같이 변경해 줄 수도 있다.

조인 조건을 주고 싶을 경우 JOIN 키워드와 ON 키워드를 같이 사용하면 된다.

이때 조건에 부합하지 않은 튜플에 대한 처리는 INNER와 OUTER 의 차이로 해결하면 된다.

1.1.2.1.5.1. INNER JOIN

조건에 부합하는 튜플만 처리하는 JOIN이다.

일단 그전에 위 아이템 테이블에서 owner가 unique하자고 가정하자

1
2

SELECT name, age  FROM MEMBER INNER JOIN ITEM ON MEMBER.id = ITEM.owner
WHERE ITEM.price >= 1000;

이 경우 item과 member의 이름이 같은 튜플끼리 합쳐지게 되는데 이때 ITEM.owner에 해당 값이 없을 경우 그값은 무시하게 된다.

하지만 item을 소유하고 있지 않은 멤버까지 확인하거나 아무도 소유하고 있지않은 아이템을 확인하고 싶을때에는 어떻게 해야할까

1.1.2.1.5.2. OUTER JOIN

조인 조건에 부합하지 않아도 처리해주는 JOIN이다.

OUTER JOIN에는 LEFT, RIGHT, FULL 3가지의 키워드가 추가로 존재하는데

이는 조건 누락을 허락할 테이블을 말하는 것이다. 예를 들어 item을 가지고 있지 않은 유저까지 포함시키고 싶을땐

1

SELECT name, age  FROM MEMBER LEFT OUTER JOIN ITEM ON MEMBER.id = ITEM.owner;

아무도 소유하지 않은 item을 표기하고 싶을 땐

1

SELECT name, age  FROM MEMBER RIGHT OUTER JOIN ITEM ON MEMBER.id = ITEM.owner;

둘다는

1

SELECT name, age  FROM MEMBER FULL OUTER JOIN ITEM ON MEMBER.id = ITEM.owner;
1.1.2.1.5.3. CROSS JOIN

이는 양쪽 모든 행을 서로 조인 시키는 기능이다.

카테시안 곱이라고도 하는데

결과로 N x M개의 행이 생성된다.

1

SELECT * FROM MEMBER CROSS JOIN ITEM ON MEMBER.id = ITEM.owner;
1.1.2.1.6. 부속 질의

부속 질의란 SELECT 문 안에 SELECT 문을 의미한다. 서브 쿼리라고도 한다

1
2
3

SELECT count(*) FROM MEMBER WHERE id = (
	SELECT owner FROM ITEM WHERE price >= 1000
);

위는 1000원 이상의 아이템을 보유한 사람들의 명수를 세는 부속 질의이다.

1.1.2.2. INSERT

데이터를 삽입하는 구문이다.

1

 INSERT INTO MEMBER(id, name, age) VALUES (3, '김철수', 15);

또한 VALUES 대신 table에 존재하는 값을 삽입할 수 도 있다.

1

INSERT INTO MEMBER(name, age) SELECT name, age FROM MEMBER WHERE name = '김철수';

이경우 김철수씨 데이터가 복사되어 들어간다 (id가 자동으로 삽입된다면 auto increament 같은거)

1.1.2.3. UPDATE

데이터를 수정하는 구문이다.

1

UPDATE MEMBER SET age = 30 WHERE id = 1;

특정 조건을 만족하는 튜플을 수정하는 구문이다

SELECT 를 활용할 수 도있다.

1
2
3

UPDATE MEMBER SET age = 10 WHERE id in (
	SELECT owner FROM ITEM WHERE name = 'car'
);

1.1.2.4. DELETE

데이터를 삭제하는 쿼리이다

1

DELETE FROM MEMBER WHERE id = 1;

똑같이 SELECT도 활용할 수 있다.

1
2
3

DELETE FROM MEMBER WHERE id IN (
	SELECT owner FROM ITEM WHERE name = 'car'
);

WHERE 조건을 주지 않을 시 테이블의 모든 데이터를 삭제한다

1

DELETE FROM MEMBER;

DROP TABLE과는 다르게 테이블 자체는 남아있다. (INSERT 할경우 다시 넣을 수 있지만, DROP TABLE의 경우 CREATE TABLE을 다시 해줘야한다)

1.1.2.5. DROP, TRUCATE, DELETE 차이

여기서 중요한것은 DROP, TRUNCATE, DELETE의 차이이다.

1
2
3

TRUNCATE TABLE MEMBER;
DROP TABLE MEMBER;
DELETE TABLE MEMBER;

위 3개의 삭제 구문은 모두 테이블을 삭제하는 구문이다.

하지만 각각이 차이점은 명확하다.

| 키워드 | 테이블 정의 | 저장 공간 | 작업속도 | 롤백 | 구분 | | ——– | —— | —– | —- | — | — | | DELETE | 존재 | 유지 | 느림 | 가능 | DML | | TRUNCATE | 존재 | 반납 | 빠름 | 불가능 | DDL | | DROP | 삭제 | 반납 | 빠름 | 불가능 | DDL |

  1. DELETE
    • 데이터만 삭제되며 테이블 용량은 줄어들지 않는다
    • 롤백이 가능하다
    • 자원소모가 심하다
    • 전체 또는 일부 삭제 가능
    • 삭제 행수 반환
  2. TRUNCATE
    • 테이블을 생성 초기로 만든다
    • 용량이 줄어든다
    • 롤백 불가능
    • 전체 삭제만 가능
  3. DROP
    • 구조 자체를 제거하는것이다
    • 롤백이 불가능

1.1.3. 데이터 제어어 (DCL, Data Control Language)

데이터베이스에 대한 보안 유형으로는 크게 3가지로 나뉜다

  • 물리적 환경에 대한 보안 자연재해 등으로 부터 데이터베이스의 물리적 손실을 방지
  • 권한 관리를 통한 보안 권한이 없는 사용자로 부터 보호
  • 운영 관리를 통한 보안 권한이 있는 사용자로 부터 보호

이 중 SQL의 데이터 제어어는 권한 관리를 통한 보호로 보안을 위한 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소 하는 기능을 제공한다

사용자의 권한을 부여하는 방법에는 크게 객체 권환과 역할이 있다.

1.1.3.1. 객체 권한

DBMS는 보안을 유지하기 위해 계정이 발급된 사용자가 로그인에 성공하였을 때에만 접근이 가능하도록 접근 제어 기능을 제공한다.

로그인에 성공하였어도 특정 DB나 테이블에 접근하는 권한이 없을 경우 접근은 제한할 수 있다.

즉 사용자는 자신이 허용된 권한 내에서만 데이터베이스를 사용할 수 있다.

1.1.3.1.1. 권한 부여
1

GRANT {권한} ON {객체} TO {사용자} [WITH GRANT OPTION];

사용자에게 특정 객체에 할 어떤 행위에 대한 권한을 부여하는 방식이다.

1

GRANT SELECT ON MEMBER TO user_1;

이 경우 user_1로 접근한 사용자에게 MEMBER table의 SELECT 권한을 부여하는다는 의미이다.

또한 모든 사용자에게 권한을 부여하고 싶다면

1

GRANT SELECT ON MEMBER TO PUBLIC;

아니면 테이블의 특정 속성에만 부여할 방법도 있다.

1

GRANT UPDATE(age) ON MEMBER TO user_1;

WITH GRANT OPTION의 경우 부여받은 권한을 다른 사용자에게 부여할 권한이 생기는 것이다

1

GRANT SELECT ON MEMBER TO user_1 WITH GRANT OPTION;

이 경우 user_1으로 접근한 유저는 다른 유저들에게 MEMBER의 SELECT 권한을 부여할 권한이 생긴다.

1.1.3.1.2. 권한 취소
1

REVOKE {권한} ON {객체} FROM {사용자} CASCADE : RESTRICT;

CASCADE의 경우 해당 사용자가 다른 사용자에게 부여한 권한을 모두 취소하는 옵션이고 RESTRICT는 해당 사용자만 취소하는 옵션이다.

1

REVOKE SELECT ON MEMBER FROM user_1 CASCADE;

1.1.3.2. 역할

DB는 여러 사람이 공용으로 사용하는 것을 목적으로 한다.

즉 사용자에게 주어질 권한이 중복될 경우가 많다는 것이다.

이를 모든 사용자에게 GRANT구문으로 부여한다면 매우 번거로운 일이다.

역할은 해당 권한을 미리 설정하고 이를 사용자에게 부여하는 방식이다.

1.1.3.2.1. 역할 생성
1

CREATE ROLE {역할이름};

1

CREATE ROLE admin
1.1.3.2.2. 역할 권한 부여
1

GRANT {권한} ON {객체} TO {역할이름};

위에 생성된 역할에 권한을 부여하는 구문이다

1

GRANT SELECT ON MEMBER TO admin;
1.1.3.2.3. 역할 부여
1

GRANT {역할이름} TO {사용자};

생성한 역할을 사용자에게 부여하는 구문이다

1

GRANT admin TO user_1;
1.1.3.2.4. 역할 취소
1

REVOKE {역할이름} FROM  {사용자};

부여하였던 역할을 취소하는 것이다.

1

REVOKE admin FROM user;
1.1.3.2.5. 역할 삭제
1

DROP ROLE {역할 이름}

역할을 삭제하는 구문이다.

1

DROP ROLE admin;

2. VIEW

A view is a logical table that is based on one or more tables.

Understading Views (oracle)

View란 하나 이상의 다른 테이블(다른 view도 포함)을 기반으로 만들어진 논리적인 테이블이다.

view는 실제 데이터는 가지고 있지 않다. 기존 테이블을 기반으로 원하는 데이터를 보여줄 뿐이다.

view의 장점은 아래와 같다

  • 질의문을 쉽게 작성할 수 있다. GROUP BY나 WHERE절을 미리 view에 반영하기 때문에 SELECT절이나 FROM 절로 편하게 가져올수 있다

  • 데이터 보안 유지에 도움이 된다. 사용자 요구에 따라 미리 view를 지정해 두면 그 외의 데이터를 보호 할 수 있기 때문에 요구에 따라 권한을 부여하여 보안할 수 있다.

  • 데이터를 좀 더 편리하게 관리할 수 있다. 사용자에게 있어서 view외의 다른 데이터는 신경쓸 필요가 없기 때문에 편하게 사용할 있다.

2.1. CREATE VIEW

1

CREATE VIEW {view_명} ({속성_리스트}) AS {SELECT 절} [WITH CHECK OPTION];

SELECT를 통해 원하는 데이터를 가져오고이를 view로 생성하는 것이다.

1
2
3

CREATE VIEW old_member(name, age) 
AS SELECT name, age FROM MEMBER WHERE age > 30
WITH CHECK OPTION;

WITH CHECK OPTION 키워드의 경우 이후 view에서 수행할 연산에서 뷰 생성시 조건 (WHERE 절)의 조건을 위반하지 않도록 제약조건을 부여하는 옵션이다.

이때 view 속성 정의시 속성명을 주지 않을 경우 SELECT문의 속성들을 그대로 가져오게된다

1
2
3

CREATE VIEW old_member
AS SELECT name, age FROM MEMBER WHERE age >30
WITH CHECK OPTION;

2.2. 활용

Table과 같이 SELECT, INSERT, DELETE를 수행할 수 있다.

주의하여야 할 점은

INSERT의 경우 기존 table의 속성을 가져오기 때문에 pk나 unique등의 제약조건을 따라서 삽입해줘야 한다.

1

INSERT INTO old_members VALUES('김', 10);

참고로 view에 수행된 결과들은 기존 table에 그대로 반영이 된다. 하지만 view가 기존 테이블을 명확히 반영하지 못할 경우 검색외에 삽입, 삭제, 수정 등의 연산은 반영되지 않는다.

다음은 변경이 불가능한 view들이다.

  • 기존 테이블의 기본키 (PK)를 포합하지 않는 뷰
  • 기존 테이블의 내용이 아닌 집계 연산의 결과를 속성으로 가지고 있는 뷰
  • DISTINCT를 포합하여 정의한 뷰
  • GROUP BY절을 포함하여 정의한 뷰
  • 여러개의 테이블을 조인한 뷰

2.3. VIEW 삭제

1

DROP VIEW {view_명};

참고

[데이터베이스 개론 3판, 김연히 저, 한빛 아카데미]

학습, CS, 스터디
스터디
This post is licensed under CC BY 4.0 by the author.