[DB개념] :: SQL (Structured Query Language, 구조화 질의어)

SQL(Structured Query Language

SQL

What is it?

• Relational DBMS의 query/modify에 가장 많이 사용되는 언어로,
  RDBMS(관계형 DBMS)의 Data를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어이다.
  
• RA(Relational Algebra)와 비슷한 역할을 하기도 하고,
  Database modify에 참여하기도 한다. (modify : 삽입, 삭제, 선언 등)
  즉, DML과 DDL 두 개의 역할을 모두 수행한다.
  

• 또한 무척 대표적인(Standard) 언어라, 갖가지 변형된 언어들도 존재하는데,
  우선 주 표준인 ANSI(American National Standards Institue) SQL을 기반으로 학습한다.
  
• 다양한 표현과 기법이 있지만, 우선 주로 Query Interface(문법적인 부분)을 다뤄보겠다.
  

ANSI SQL? 
1986년부터 미국 표준협회에서 표준화한 SQL로, 
표준이기 때문에 모든 DB에서 호환된다고는 하지만,
개념과 표현 방법의 이론상일 뿐 실제론 많이 다를 수 있다.

Simple Queries in SQL

1. SELECT clause 
   
   • SELECT clause를 사용하여 Projection(추출)하며, 일반적으로 중복은 허용한다. 
     중복을 허용하지만, 명시적으로 'ALL', 'DISTINCT'를 표기한다. 
     또한 *(Asterisk)를 표기하여 모든 column을 나타낼 수도 있다. 
     
   • SELECT 절에는 column 이름, 'AS'를 사용한 Renaming, 사칙 연산 등이 가능하다. 
     
   • SELECT 절에 작성된 column들에 대한 tuple을 반환한다. 
     

2. FROM clause 
   
   • FROM clause를 사용하여 해당 쿼리를 수행할 Relation(s)를 표기한다. 
     즉 접근해야하는 Relation 이름을 작성한다. 
     
   • ,(comma)를 사용하여 JOIN 연산을 사용할 수 있다. 
     

FROM CLAUSE WARNING
콤마를 사용하여 조인 연산을 행한다고 했지만,
이는 empty한 relation과의 어떠한 쿼리가 
원하는 결과값과는 다를 수 있으니, PRODUCT를 사용하는것이 좋다.
콤마보다는 어떠한 연산을 할 것인지 명시하는 것이 가장 좋다.

3. WHERE clause 
   
   • WHERE clause를 사용하여 Selection 연산을 행할 수 있다.
     
   • WHERE 절에는 'Predicate'를 작성하여 comparing할 수 있다. 
     ( =, <>, <, >, <=, >=),(c나 java와 동일, <>는 !=와 같다) 
     
   • comparison의 값은 boolean값(TRUE,FALSE), 논리연산자인 AND,OR, NOT 등등이 가능하다. 
     
   • WHERE clause의 모든 조건을 만족해야 한다.( TRUE만 반환, FLASE/UNKNOWN은 제거 )
     

즉, 표준 표기법은 다음과 같다.

SELECT  A1, A2, ...
FROM  T1, T2, ...
WHERE  P (Predicate)

READING/WRITING 관점
S-F-W 순서로 기재되어있지만, 사실 읽고 쓸땐 F-W-S순으로 한다.

Case Insensitivity 
SQL작성 시의 대소문자 구별 (column name, relation, aliases(renamed name)) 등등은 상관없다. 
(예 : From = FROM = from = FroM) 
하지만, 그 내용(Component)은 반드시 구별해야 한다.
그렇다고 항상 Insensitive한 것은 아니니 유념하도록 하자.

SQL & RA 

SELECT  L 
FROM  R 		=		πL(σC(R))
WHERE  C

L : column list
R : Relations
C : Conditions

여기서도, F-W-S순서라는 걸 알 수 있다. 

Rules of SQL

Comparison of Strings

• String에 있어서, 크다/작다는 lexicographically하게 비교한다.
• 알파벳순 앞에오는게 작은값이며 같은패턴이라면 단어가더 긴 String이 더 크다. (예 : ant < bar, bar < bargin)
• 또한 LIKE를 사용하여 pattern matching을 할 수 있다.
• s LIKE 'p __' : p(Pattern), 그리고 언더바 갯수만큼의 임의의 String (예 : 'Star ____' : Star Wars, Star Trek)
• s LIKE '%' : 임의값. ( 예 : '%o%' : Coco, Iron man 3)
• 대소문자 구별은 DB system마다 다르다.
• 패넡에 %, _와같이 특수한 문자를 사용하기위해 escape(\)를 사용한다. 
  
• '을 사용하고 싶을 땐 ''로 사용, 즉 따옴표는 두번 표기해서 사용한다. 
  

예 : 

 LIKE 'ab\%cd%' : ab%cd~~~ 
 LIKE 'ab\\cd%' : ab\cd~~~
 escape '\'

Dates and Times

• 5/14/1948이나 14 May 1948같이 날짜나 시간에 대한 data type은 특별하다.
• DATE '1948-05-14' 로 사용, 년도-월-일에 2자리로(필요하다면 0 추가)
• TIME '15:00:02.5' 로 사용, 시간-분-초 (시간은 24시간, 역시 필요하면 0 추가)
• TIMESTAMP '1948-05-14 12:00:00' 로 사용, 두개를 섞은 것이다.
• 이들도 대소비교가 가능하며 일찍인게 작은값이다. 
  

Null Values and Comparisons Involving NULL

• SQL은 특별한 값인 NULL을 허용하는데, DB마다 처리 방식이 다르다.
• 일반적으로 보편적인 처리방법은
  1. UNKNOWN : 정확히 몇인지는 모르지만 무슨 '값'이 있긴 있다. (생일 등)
  2. INAPPLICABLE : 존재할 수 없는 값. (미혼자의 배우자 등)
  3. WITHHELD : 볼 권한이 없는 값 (대외비 등)

• WHERE절에서 중요한 두 가지 규칙은
  1. NULL과 다른 값과(NULL포함)의 사칙연산(＋, －, ×, ÷)은 무조건 NULL이다.
  2. NULL과 다른 값과(NULL포함)의 대소비교(＜, ＝, ＞ 등)는 무조건 UNKNOWN이다. (T/F둘다가능하기 때문)

• NULL은 tuple에 나타날 수 있는, constant하지않은 어떠한 '값'이다.
• 'IS NULL'을 사용하여 판단할 수 있다. (x IS NULL에서 x가 NULL값이면 TRUE)

Pitfalls Regarding NULLs

NULL은 '모르지만, 존재는 하는 값'이라 했지만, 
가만히 생각해보면 이것 또한 잘못된 표현이다. 
예를 들어, 0×i에서 도메인이 인티져 값이면 i가 NULL이어도 
0인게 맞지만, rule에 의해 unknown이다. 
또, i-i에서 어떤 인티져라도 0이지만, 역시 unknown이다. 
Be Careful!! NULL is NULL

Syntax of SQL

Ordering the Output

• S-F-W clause 뒤에 ORDER BY를 사용하여
  정렬된 output을 얻을 수 있다.(default는 ascending, DESC(descending)을 명시하여 내림차순도 가능)
  

예 : 

SELECT  name
FROM  student
WHERE  dept_name = 'Comp. Sci.'
ORDER BY  s_id;

Disambiguating Attributes

• 같은 이름의 attribute를 가진 여러 개의 relation의 combine에서 
  어떤 relation의 column인지 명시할 필요가 있다.
• .(dot)으로 구별한다. 
  

예 : 

SELECT  student.name, instructor.name
FROM  student, instructor;

Tuple Variables

• 한개의 relation에서 비교를 하고 싶을 때 
  FROM clause에 AS(생략가능)로 같은 relation을 다른 이름으로 구별한 뒤 
  쿼리문을 작성한다. 
  

예 : 

FROM  Movie M1, Movie M2

Union, Intersection, and Difference of Queries

• UNION, INTERSECT, EXCEPT를 사용하여 RA에서와 같은 연산들을 수행할 수 있다. 
  

예 : 

(SELECT  name FROM  Movie)
  INTERSECT
(SELECT  name FROM  MovieStar);

Readable SQL Queries (가독성) 

SQL에서 일반적으로 FROM이나 WHERE clause가 상당히 중요하기 때문에 
line의 맨 앞에 작성하는 것이 당연히 가독성이 좋다. 
하지만, 짧고 간결한 SQL문이면 
다음과 같이 한 line에 작성해도, 쿼리규칙을 준수하며 가독성도 괜찮다고 할 수 있다. 

예 : 

SELECT  *		 ->		SELECT  * FROM  Movies
FROM  Movies;

Subqueries

• SQL에서는 한 쿼리가 다양하게 쓰이는데, 
  이 쿼리의 일부분을 Subquery라고 부른다.
  
• 하위 쿼리 또한 하위 쿼리를 가질 수 있고, 원하는 대로 하위 단계를 계속 쌓을 수 있다.
  
• 하위쿼리가 쓰이는 경우 
  
  1. Subqueries는 한 개의 constant를 반환, 이 constant는 WHERE clause에서 쓰인다. (즉, WHERE clause에서 사용된다.)
  2. Subqueries는 WHERE clause에서 다양하게 쓰이는 'Relation'도 반환가능.
  3. Subqueries는 그들의 relation도 소유할 수 있다. (다른 저장된 relation과 마찬가지로) 
     

-> 즉, FROM clause에도 사용 가능하다. 

예 : 

SELECT  name
FROM  student, takes
WHERE  course_name in (SELECT  course_name
		                         FROM  instructor, teached
		                         WHERE  instructor.name = 'Bohyun');

Conditions Involving Relations

1. EXISTS : 존재하는 
   
2. s (NOT) IN R : s가 R에 존재(안)하는 
   
3. s (<)> ALL R : s가 R의 모든 값보다 크(작)다면
   

   ( s <> ALL R = s NOT IN R ) 

4. s (<)> ANY R : s가 R의 값들 중 어느 하나와 크(작)다면
   

   ( s = ANY R = s IN R )

EXISTS와 IN의 차이점 

둘 다 존재하는 row를 찾는 condition이지만 
NULL의 여부에 따라, EXISTS는 해당 row가 존재하는지만 찾기 때문에 
다른 row에 NULL 값이 있어도 TRUE가 반환될 수 있다.
반면 IN은 데이터의 값까지 확인하기 때문에, NULL 값 존재시 
IN은 무조건 FALSE를 반환한다.

JOINS
역시 RA와의 의미는 같고, SQL상의 표기법을 예시를 통해 알아보자. 

• NATURAL JOINS 
  

예 : 

SELECT  student.name
FROM  student NATURAL JOIN instructor;

• OUTER JOINS 
  

예 : 

SELECT  student.name
FROM  student FULL OUTER JOIN instructor ON ~;

SELECT  student.name
FROM  student LEFT OUTER JOIN instructor ON ~;

SELECT  student.name
FROM  student RIGHT OUTER JOIN instructor ON ~;

Full-Relation Operations

• 적은, 몇개의 개개 tuple을 연산하는 것 말고, 
  전체 Relation에 대한 연산을 한번 알아보자. 
  

Eliminating Duplicates

• 일반적으로, 쿼리 작성은 Bag 기반이기 때문에 (Set이어도 쿼리 수행 시 중복 tuple이 발생할 수 있지만) 
  중복이 허용된다. 이 때, DISTINCT 를 표기함으로써, 중복을 제거할 수 있다. 
  

Cost of duplicate elimination

Very expensive하다. 
time 또한 쿼리수행보다 오래걸린다 
그러므로, 현명하게 사용하자. 

Duplicates in Unions, Intersections, and Differences

• 각 연산에 ALL을 붙여 중복을 허용할 수 있다. 
  

UNION ALL 
INTERSECT ALL 
EXCEPT ALL 

Grouping and Aggregations

• RA와 같은 그룹화, Aggregation이다. 
  
• GROUP BY를 사용하여 그룹화 한다. 
  전에도 언급했듯, 단순 그룹화만은 중복을 제거하며 정렬하는 것일 뿐 의미가 없다. 
  즉, Aggregation operator와 같이 사용한다. 
  
  • NULL의 경우, NULL도 그룹화 해서 NULL끼리의 aggregation를 시행한다. 
    
• Aggregation 연산자로는 RA와 같은 의미를 가진
  
  • SUM, AVG, MIN, MAX, COUNT를 사용한다. ( COUNT*, COUNT (DISTINCT x) )
    
  • SELECT clause에 작성하며, Aggregation의 기준이 되는 그룹 명도 '1개만' 표기한다.
    

예 : 

SELECT  studio_name, AVG(salary)
FROM  Movies
GROUP BY  studio_name

• HAVING을 사용하여 본래의 WHERE clause처럼 group에 조건을 부여하는 데 사용하지만, 
  이는 group화 한 이후의 조건을 따지는 것이므로 작성시 주의하자. 
  

예 : 

SELECT  studio_name, AVG(salary)
FROM  Movies
GROUP BY  studio_name
HAVING  AVG(salary) > 2000;

마침.