[데이터베이스 05] 기본 SQL 문법

SQL

 현재 데이터베이스 계열에서 데이터에 접근하기 위해 가장 많이 사용하는 언어로,  Chamberlin 과 Boyce 두 학자들이 저술한 "SEQUEL TO SQUARE" 이라는 논문에서 처음 등장했다고 한다. 이때 당시에 SEQUEL(Structured English Query Language) 라는 표현에 이미 상표가 붙어 있어, IBM사에서 대신 SQL이라는 용어를 사용했다고 한다.

 Relational Data Model을 문법으로 표현할 때 사용되지만, Index 지정 등 Physical 수준의 속성을 지정하는 기능도 포함되어 있다. 또한 DDL(Data Definition Language), DML(Data Manipulation Language) 의 기능을 모두 수행하는, 실질적으로 데이터베이스 계열에서의 표준 언어로 간주되고 있다.

디자인 종류	표현되는 방식
Conceptual Design	ER Model
Logical Design	Relational Model
Relational + Physical Design	SQL

 

Formal	Informal
Relational Model	SQL
Relation	Table
Tuple	Row
Attribute	Column

 

---

SQL의 특성

SQL이 Relational Model의 요소를 표현할 수 있지만, 그렇다고 완벽히 대응되는 것은 아니다.

Multi set 

 Relational Model은 기본적으로 집합(set) 의 성질을 가졌으므로, 내부에 원소의 중복이 허용되지 않으며, 특정 relation schema에 대한 state 사이에 순서가 존재하지 않는다는 특징이 있었다. 그런데 SQL은 multi set의 성질을 가지기에, 중복을 허용한다. 그런데 이상한 점이 있다. DBMS은 Relational Model 에 기반하여 중복을 허용하지 않는데, 어떻게 DBMS 을 다루는데 사용되는 SQL에서는 중복을 허용할 수 있을까?

create database my_test;
use my_test;

create table Person  (
	name varchar(100) not null,
	age int not null
);

insert into Person 
values
	("blaxsior", 23),
	("blaxsior", 23),
	("blaxsior", 23);
    
select * from Person;

 위 코드에서는 Person 테이블을 생성하고, 동일해 보이는 3개의 데이터를 입력하고 있다. 만약 우리의 생각대로 DBMS가 Relational Model 에 기반한다면, 세개의 데이터는 중복되므로 입력에 실패해야 할지도 모른다.

 그러나 예상과는 달리, 아무 문제 없이 잘 삽입된 모습을 볼 수 있다. DBMS가 Relational Model 이라고 했는데, 왜 중복을 허용하는 것일까? 그것은 어차피 DBMS 자체는 이들을 구분할 수단이 있기 때문이다.

 우리가 DBMS에 동일한 값을 넣는다고 생각하겠지만, DBMS는 값을 입력받아 데이터베이스에 삽입할 때 각 값을 식별하기 위한 자신만의 키를 추가한다. 따라서 동일한 데이터가 입력되더라도 실제 DBMS 상에서는 이들이 중복되지 않는 원소로 취급될 수 있다. 따라서 DBMS은 Relational Model 의 제약조건을 지키면서도 SQL에 중복을 허용할 수 있다.

 DBMS에서 각각의 데이터에 부여하는 Id는 내부 식별용이므로, 당연히 SQL상에는 노출되지 않는다. 이러한 정보 차이에 의해 SQL 시점에서는 중복된 데이터를 허용하게 되는 것이다.

실제 구현이 아니다

https://en.wikipedia.org/wiki/ISO/IEC_9075

 SQL은 근본적으로 논문에서 출발한 언어이다. IBM사에서 출발한 SQL 및 Relational Database 개념을 이후 오라클, MS 및 기타 다양한 단체들에서 채택하여 해당 명세에 따라 실제로 구현한 것 뿐이다. 이로 인해 실제 DBMS의 구현 방식 및 기능은 명세에서 필수적으로 요구하는 특징들을 제외하면 단체마다 다양한 바리에이션을 가진다. 

---

자주 사용되는 표준 문법들

---

CREATE SCHEMA / CREATE DATABASE

 CREATE SCHEMA COMPANY AUTHORIZATION 'Blaxsior';

데이터베이스를 생성하는데 사용된다. SCHEMA 및 DATABASE 라는 표현이 혼용된다.

데이터베이스의 모든 스키마는 Catalog/Dictionary 라는 공간에 저장된다.

---

CREATE TABLE

CREATE TABLE PERSON (
	column1 attributes ...
	column2 attributes ...
    );

테이블을 생성하는데 사용되며, Attribute, 타입, constraints 등의 정보를 부여할 수 있다.

테이블은 크게 2종류로 나뉜다.

• Base Tables ( base relations )
  : DBMS 상에 저장되어 있는 실제 Relation 및 튜플들
• Virtual relations ( views )
  : CREATE VIEW 을 통해 생성하는 가상의 테이블로, 실제 DBMS의 Relation 에 대응되지는 않는다.
  전체 테이블에서 특정 정보만을 따로 보거나, 여러 테이블을 join한 자료에 자주 접근하는 경우 사용된다.

---

데이터 타입

 명세에 제시된 타입도 있지만, 각각의 데이터베이스마다 고유하게 가지는 타입이 있을 수도 있다. 이러한 타입들은 사실 직접 문서를 보고, 찾아 쓰는게 맞는 것 같다. ( ex : mysql에는 json 타입이 존재! )

일반적인 타입

• Numeric
  
  • Integer : INTEGER, INT, SMALLINT ...
  • Float : FLOAT, REAL, DOUBLE ...
  • 정확도 보장 : DECIMAL
• Char-String
  • Fixed : CHAR(n) , CHARACTER(n) ..
  • Varying : VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)...
• Bit-String
  • Fixed : BIT(n)
  • Varying : BIT VARYING(n)
• Boolean : TRUE, FALSE, NULL
• 시각 / 시간 관련
  : 문자열로 형변환 될 수 있다.
  • DATE : YEAR, MONTH, DAY ...
  • TIME : 시간 단위
  • TIMESTAMP : DATE + TIME
  • INTERVAL : 시각과 시각 사이

Domain

 기본 자료형의 새로운 정의로, 제약조건을 선택적으로 추가하여 만든 것. 다양한 Attribute에서 사용되는 도메인을 쉽게 관리할 수 있다는 장점이 생긴다.

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

TYPE

사용자 정의 타입을 지원하기 위한 기능. 여러가지 조건을 명시하여 새로운 타입을 만드는 것.

https://www.ibm.com/docs/ko/db2/11.1?topic=statements-create-type-distinct https://www.postgresql.org/docs/current/sql-createtype.html

---

SQL의 제약 조건

• Key Constraint
  : Primary Key 는 중복 불가
• Entity Integrity
  : Primary Key로 지정된 Attribute는 NULL일 수 없음
• Referential Integrity
  : Foreign Key는 반드시 존재하는 Primary Key의 값을 가지거나, NULL이어야 함

제약조건에 따른 기능들

• Attribute 에 대한 제약조건
  • DEFAULT <value> : 기본값 지정, NULL 불가능
  • CHECK : 제약조건에 맞는지 검사

# 스키마의 각 컬럼 정의 중 ...
#부서의 사람은 (0, 21) 사이에 있어야 한다.
Dnumber INT NOT NULL CHECK ( Dnumber > 0 AND Dnumber < 21 );

#지원자의 나이는 (20, 65) 이어야 함
applicant_age NOT NULL CHECK ( applicant_age > 20 AND applicant_age < 65 );

# 튜플에 대한 검사
CHECK (Dept_create_date <= Mgr_start_date);

• Key 에 관련된 제약 조건
  • PRIMARY KEY(attr) / Constraint attr_name PRIMARY KEY(attr)
    
    • 반드시 UNIQUE & NOT NULL 조건에 속한다.
  • UNIQUE(attr) / Constraint attr_name UNIQUE(attr)
    • 꼭 NOT NULL로 지정될 필요는 없다.
  • FOREIGN KEY(attr) REFERENCES TABLE2(attr2)
    • Referential Integrity을 위해 해당 무결성 위반 시 수행할 동작을 지정할 수 있다.
      
      • SET NULL : NULL 로 설정한다.
      • SET DEFAULT : DEFAULT 값이 지정되어 있다면, 해당 값으로 설정한다.
      • CASCADE : 같이 변경 혹은 같이 삭제한다. 매우 위험한 방식이므로, 필요한 경우에만 사용 권고.
    • ON DELETE, ON UPDATE : 특정 상황에서의 동작을 지정할 수 있다. 각 동작은 상대방이 기준이다.
  • CONSTRAINT
    : KEY 제약조건들에 이름을 개별적으로 붙일 수 있음.

---

 

SELECT-FROM-WHERE

SELECT	<attr_list> # 검색 대삳 목록
FROM	<table_list> # 정보를 가져올 테이블 목록
WHERE	<condition>; # 정보를 가져오는 조건

# Aliasing : 이름을 변경하거나, 다른 이름으로 가져올 수 있다.

# 동일 테이블의 정보를 사용하면 aliasing 사용하는게 편하다. AS는 생략 가능
SELECT E.Fname, E.Lname, S.Fname, S.Lname
	FROM EMPLOYEE AS E, EMPLOYEE AS S
	WHERE E.Super_ssn=S.Ssn;

# Attribute 이름도 변경해서 가져올 수 있는데, 보통 안한다.
SELECT Fn, Mi, Ln
	FROM EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno);
    
# WHERE의 경우, 서로 구별되는 값만을 가져올 수도 있음!
SELECT DISTINCT Fname # multi set 의 특성을 제거 ( 중복 비허용 )
	FROM EMPLOYEE;
# ALL은 조건의 기본 설정
SELECT ALL Fname
	FROM EMPLOYEE;
# DISTINCT 옵션의 구현 방법
# 1. 정렬 이후 중복된 값을 제거한다.
# 2. 해싱 + 버킷을 통해 중복 요소 제거.
# 배운 자료구조 및 알고리즘을 어떻게 써먹을지 생각해라!

• WHERE 조건이 명시되지 않은 경우
  : 선택된 테이블들에 대한 CROSS PRODUCT을 수행한다. 만약 A 와 B 테이블이 있을 때, A 테이블에는 1000 개의 row가 있고, B 테이블에는 500 개의 row 가 있으면 WHERE 문 없이 SELECT 을 수행하면 1000 * 500 개의 row을 가진 CROSS PRODUCT 가 생성된다.

수학적 기호들

• Logical : = | < | > | <= | >= | <>  ( !=)
• arithmetic : + | - | * | /  

# 연산이 구문 안에 포함될 수 있다.
# 남성의 이름, 성 및 월급 * 1.1 한 결과를 보여준다.
SELECT Fname, Lname, 1.1 * Salary
	FROM EMPLOYEe
	WHERE Sex = 'm';

패턴 매칭

• LIKE : LIKE "______-_______" ( 주민등록번호 )
  • % : 0개 이상의 문자
  • _  : 1개의 문자
• target REGEXP "condition" ( 공식 문법 X )
  
  • 통상의 정규표현식을 SQL 문법에서 사용하는 것.

# 성이 L 로 시작하는 사람의 SSN 정보를 가져온다.
SELECT Ssn FROM Employee WHERE Lname REGEXP "^L.*$"

순서 지정

ORDER BY을 통해 순서를 지정할 수 있다. ASC / DESC 옵션을 통해 오름차순 및 내림차순 지정도 가능하다.

SELECT Fname, Lname, Ssn, Dno
	FROM EMPLOYEE
	ORDER BY 4 ASC, 1 DESC;

INSERT

테이블 내 Attribute에 대응되는 값만 정상적으로 삽입될 수 있다. 

# 새로운 값 삽입
INSERT INTO TABLE_NAME
VALUES
    (attr1, attr2 ... ),
    (attr1, attr2 ... ),
    (attr1, attr2 ... );
    
# 다른 테이블로부터 값을 받아서 삽입
INSERT INTO WORKS_ON_INFO ( Emp_name, Proj_name, Hours_per_week ) 
	SELECT E.Lname, P.Pname, W.Hours
	FROM	PROJECT P, WORKS_ON W, EMPLOYEE E
	WHERE	P.Pnumber = W.Pno AND W.Essn = E.Ssn;

BULK LOADING 

CREATE TABLE D5EMPS LIKE EMPLOYEE
	(SELECT E.*
	FROM EMPLOYEE AS E
	WHERE E.Dno=5)
WITH DATA;

DELETE 

 WHERE 문 조건을 만족하는 튜플들을 제거한다. 이때, Referential Integrity을 만족하기 위해 값이 제거되지 않거나, FK의 값이 변경되는 일이 발생할 수 있다. WHERE 문 없이 실행하면 모든 데이터를 제거할 수도 있으므로, 주의해야한다.

DELETE TABLE_NAME
WHERE CONDITON;

UPDATE

DELETE 처럼 WHERE 문 조건을 만족하는 row을 찾아, SET에 정의된 업데이트 동작을 수행한다.

 내부적으로 이전 값을 삭제하고 다시 만들 수도 있고, 일부 값만 변경할 수도 있는 등 세부 구현 사항은 DBMS에 따라 다를 수 있지만, multi set 이므로 순서가 존재하지 않으므로 어떤 방식도 성능만 좋다면 상관 없다.

UPDATE	TABLE_NAME
SET	업데이트 내역
WHERE	condition;

집합 연산

 여러 쿼리에 대한 집합 연산이 가능하다. 이때 해당 연산은 Union-Compatible 한 쿼리들에 대해서만 가능하다. 즉, Attribute의 수, 해당 Attribute에 대한 도메인 조건을 만족하는 경우에만 집합 연산을 할 수 있다. 

 간단하게 말하면, 집합 연산은 SELECT 문에 지정한 값들의 속성 및 타입이 같을 때만 가능하다.

• UNION : 합집합 연산
• EXCEPT : 차집합 연산
• INTERSECT : 교집합 연산