[Database] 5. The Basic SQL

SQL(Structured Query Language): 상업용 relational DBMSs의 표준 언어이다. 데이터 정의, 쿼리, 그리고 수정을 위한 명령들이 포함되어 있다(DDL과 DML을 모두 가지고 있다). 핵심 서술과 추가적인 특수 확장들(공간 데이터, 시간 데이터, OLAP, 데이터 마이닝, 멀티미디어 데이터 등)로 나뉜다.

SQL 데이터 정의 및 데이터 타입

SQL 데이터 정의 언어(Data Definition Language, DDL)는 relations와 관련된 정보의 설명을 허용한다. 스키마, relations, 도메인, views, triggers, assertions의 생성, 무결성 제약 조건, 각 relation의 보안 및 권한 정보, 디스크 상에서 물리적인 저장 공간 설계, 인덱스의 집합이 유지되기 위한 정보들이 이에 해당한다.

SQL에서 스키마와 Catalog 개념

SQL 환경: 어떤 데이터가 존재할 수 있는지에 대한 프레임워크이고 데이터에 SQL operations가 실행될 수 있는지에 대한 것이다. 실제로는 SQL 환경을 곧 DBMS로 간주해야 한다. 이 환경은 clusters, catalogs, 그리고 스키마로 구성되어 있다.

Cluster: Catalogs의 모음으로, 쿼리가 영향을 미칠 수 있는 최대 범위이다. 그래서 일부 유저에게는 cluster가 곧 눈에 보이는 DB이다.

Catalog: SQL 환경에 있는 명명된 스키마의 모음이다. Catalog에 속하는 모든 스키마에 대한 정보를 포함하고 있는 INFORMATION_SCHEMA라는 특별한 스키마를 포함한다. SQL은 catalog를 생성하는 표준적인 방법을 정의하지 않는다.

SQL 스키마: 한 일부 DB 사용자 이름에 속한 DB 객체의 모음이다. DB에 있는 모든 객체들은 Sever.Catalog.Schema.Object에 있는 특별한 이름을 갖는다. DBA는 일반적으로 각 사용자에게 기본 catalog와 스키마를 지정 해주기 때문에, 사용자는 full path name을 쓰지 않고도 객체의 이름을 지정할 수 있다. 하지만 만약 사용자가 다른 스키마에 있는 객체에 접근하기 원한다면, 사용자는 명확하게 스키마의 이름을 지정한다.

CREATE/DROP SCHEMA: SQL 스키마는 스키마 이름으로 구분되고, 스키마의 각 element에 대한descriptor뿐 아니라 권한 부여 ID(Authorization identifier)를 포함한다. 아래는 여러 relations와 여러 다른 객체들을 포함한 스키마의 선언과 삭제 명령이다.

CREATE SCHEMA MovieSchema AUTHORIZATION Kim;

CREATE TABLE  MovieStar, ….

        CREATE VIEW   MovieProducer ...

        CREATE ASSERTION RichPresident, …

DROP SCHEMA MovieSchema [RESTRICT, CASCADE];

SQL의 CREATE TABLE 명령

새로운 Relation 생성: 이름, 속성, 그리고 초기 제약 조건들을 지정한다.

스키마를 선택적으로 지정해줄 수 있다:

-       CREATE TABLE EMPLOYEE ...

-       CREATE TABLE COMPANY.EMPLOYEE ... 

Base Relations: DBMS에 의해서 Relations과 그 튜플들은 실제로 생성되고 파일로써 저장된다.

Virtual Relations: CREATE VIEW 명령을 통해 생성된다.

일부 foreign keys는 참조할 테이블이 아직 생성되지 않아서 에러를 발생시킬 수 있다.

SQL에서 속성의 데이터 타입 및 도메인

속성들의 값으로 사용되는 기본 데이터 타입들이다.

DATE: YYYY-MM-DD 형식으로 년도, 월, 일로 구성되어 있다.

TIME: HH:MM:SS 형식으로 시, 분, 초로 구성되어 있다.

TIMESTAMP: DATE와 TIME 필드를 조합한 것으로, 소수점 아래 6자리까지 초 시간을 지원한다.

INTERVAL: 일정 기간을 저장하는데 사용되는 데이터 타입.

Dates와 times에 대해 비교 연산이 사용될 수 있다. 옛날 날짜가 비교적 최근 날짜보다 작은 값을 가지고, 시간도 이와 같다.

속성 제약 조건과 속성 기본값 지정

NOT NULL: 지정한 속성에 NULL은 허용되지 않는다.

기본값: DEFAULT <value>

속성 기반 CHECK 제약조건: CHECK 키워드를 통해서 더 복잡한 제약 조건이 속성이나 도메인의 선언에 덧붙여질 수 있다. 관련된 속성에 추가되는 모든 새로운 튜플에 대해 검사된다.

-       Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

-       CREATE DOMAIN D_NUM AS INTEGER CHECK (D_NUM>0 AND D_NUM<20)

Checked 속성 이외의 요소가 변경되면 복합 조건이 false가 될 수 있다.

-       presID INT CHECK (presID IN (SELECT execID FROM MovieExec)) : 만약 execID 튜플을 삭제하는 등의 경우처럼 MovieExec relation을 수정하게 되면, 위의 CHECK에서는 그 변경 사항에 대해서 알 수 없다.

Key와 무결성 제약 조건 지정

PRIMARY KEY: 하나 이상의 속성을 relation의 primary key로 지정한다. Primary key 선언은 자동적으로 속성이 not null 옵션을 갖도록 한다.

-       Dnumber INT PRIMARY KEY;

UNIQUE: Alternate(Secondary) keys를 지정한다. Unique로 지정된 속성은 null 값이 되는 것이 허용된다.

-       Dname VARCHAR(15) UNIQUE;

FOREIGN KEY: 참조하는 relation의 튜플이 삭제되거나 수정되었을 때 참조 무결성 제약 조건을 위반할 수 있다. 이 때, 기본 operation은 위반을 일으킨 update를 취소하는 것이다(SET RESTRICT). 참조된 triggered action을 덧붙이는 것으로 대체하는 행동을 지정할 수 있다. 이 옵션은 CASCADE, SET NULL, SET DEFAULT를 포함한다.

-       FOREIGN KEY (Dno) REFERENCES DEPART(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE;

제약 조건 명명

CONSTRAINT: 제약 조건의 이름은 나중에 제약 조건을 삭제하고 다른 제약 조건으로 대체해야 하는 경우 특정 제약 조건을 구분하는데 사용된다.

-       CREATE TABLE EMP

      ( ...

        CONSTRAINT EMPPK  PRIMARY KEY (Ssn)

        CONSTRAINT EMPSUPERFK 

            FOREIGN KEY (Super_ssn) REFERENCES EMP(Ssn) ...

CHECK를 사용해 튜플들에 제약 조건 지정

튜플 기반 CHECK 제약조건: CHECK는 CREATE TABLE 명령의 마지막에 올 수 있다. 이는 각 튜플에 개별적으로 적용되며 튜플이 삽입되거나 수정될 때 확인하게 된다. 속성 기반 CHECK 제약조건과 같은 다른 relations에서 이 조건의 존재를 확인할 수 없다.

-       CHECK (Dept_create_date <= Mgr_start_date);

SQL 기본 검색 쿼리

SELECT: DB에서 정보를 검색하는 기본 문법이다. 결과 relation의 튜플들은 원래 relation의 튜플들에 대한 부분 집합이다.

SELECT문의 기본 형태: SELECT <attribute list> FROM <table list> WHERE <condition>

-       SELECT절에는 쿼리의 결과로 보여줄 속성들을 나열한다.

-       FROM절에는 쿼리에 포함될 relations를 나열한다.

-       WHERE절에서는 결과가 만족시켜야만 하는 상태를 지정한다.

관계형 모델과 다르게, SQL은 자동적으로 중복되는 결과에 대한 처리를 해주지 않는다. 자원이 많이 드는 operation이기도 하고, 사용자가 중복되는 튜플들을 쿼리의 결과로 원할 수도 있기 때문이다.

기본 SQL 쿼리의 SELECT-FROM-WHERE 구조

FROM: Relational algebra(관계 대수학)의 Cartesian 곱 연산에 대응된다. Cartesian 곱 r1 X r2는 r1-r2의 속성으로 만들어 질 수 있는 모든 조합을 만들어낸다. Cartesian product는 그냥 사용했을 때는 그리 유용하지 않고, WHERE절과 함께 사용되었을 때 유용하다.

JOIN: 공통된 값을 사용하여 두 개 이상의 테이블의 필드를 결합하는 수단이다.

-       WHERE Dno=Dnumber AND Dname=’Research’: Dno=Dnumber는 join condition이다. 공통된 값을 이용해 두 튜플을 하나로 합치기 때문이다. Dname=’Research’는 selection condition으로 Dname이 ‘Research’인 튜플만 골라내기 때문이다.

모호한 속성 이름, Aliasing, 재명명, 그리고 튜플 변수

서로 다른 relations에서 두 개 이상의 속성이 같은 이름을 사용할 수 있다. 이럴 때 모호함을 방지하기 위해서 relation의 이름을 한정해줘야 한다. (EMP.Dnumber=DEPART.Dnumber)

Aliases 혹은 튜플 변수: 속성 이름의 모호성은 relation이 같은 relation을 참조할 때도 발생한다. Relation의 이름을 대체 선언하는 것을 Aliases 혹은 튜플 변수라고 부른다.

-       SELECT  E.Fname, S.Fname FROM   EMP AS E, EMP AS S WHERE E.Super_ssn=S.Ssn;

지정되지 않은 WHERE 절과 Asterisk 사용

WHERE 절의 부재: 튜플을 선택할 때 조건이 없음을 나타낸다. 만약 하나 이상의 relation이 FROM 절에 지정되고 WHERE절이 없으면, CROSS PRODUCT 결과 값을 내보내게 된다.

Asterisk(*) 지정: 선택된 튜플들의 모든 속성값을 검색한다.

SQL에서 집합으로서의 테이블

쿼리 결과에서 중복되는 튜플들을 제거하고 싶다면, SELECT를 사용할 때 DISTINT 키워드를 사용하면 된다.

집합 연산-UNION, INTERSET, EXCEPT(Set difference): 집합 연산자들은 자동적으로 중복되는 튜플들을 제거해준다. 이러한 집합 연산은 union-compatible(유니온 호환 관계)에만 적용된다.

-       (SELECT DISTINT Pnumber FROM PROJECT, DEPART, EMP WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname=‘Smith’) UNION (SELECT DISTINT Pno FROM WORKS_ON, EMP WHERE Essn=Ssn AND Lname=‘Smith’);

Substring Pattern Matching과 산술 연산자

SQL은 문자열 비교 연산자 “LIKE”를 제공한다. ‘%’는 아무런 문자열을, ‘_’는 아무 문자와 매치된다.

-       SELECT Fname, Lname FROM EMP WHERE Address LIKE '%Houston,TX%';

표준 산술 연산자: SELECT절은 상수나 튜플의 속성에 적용될 수 있는 표준 산술 연산자를 포함할 수 있다.

-       SELECT Fname, Lname, 1.1*Salary AS Increased_sal FROM  EMP, WORKS_ON, PROJECT WHERE Ssn=Essn AND Pno=Pnumber AND Pname='X';

BETWEEN 비교 연산자:

-       SELECT * FROM EMP WHERE (Salary BETWEEN 30000 AND 40000);

쿼리 결과의 정렬

ORDER BY: SQL은 사용자가 쿼리의 결과 튜플들을 정렬하는 것을 허용한다. ASC는 오름차순 정렬, DESC는 내림차순 정렬이다. 여러 개의 속성에 대해 정렬할 수 있다.

-       SELECT name FROM instructor ORDER BY name

-       ORDER BY Salary DESC, Lname ASC

튜플 비교:

-       SELECT name, course_id FROM Instructor, Teaches WHERE (Instructor.ID, dept_name) = (Teaches.ID, ’Biology’);

SQL에서 간단한 검색 쿼리는 최대 4개의 절로 구성될 수 있는데, 첫 두 개의 절(SELECT, FROM)만 의무적으로 작성해야 한다.

-       SELECT <attribute list> FROM <table list> [WHERE <condition>] [ORDER BY <attribute list>]

SQL의 INSERT, DELETE 그리고 UPDATE문

DB를 수정하는데 사용되는 명령은 세 가지가 있다.

1.     INSERT INTO relation(attribute1, …, attributen) VALUES (val1, …, valn);

2.     DELETE FROM relation WHERE condition;

3.     UPDATE relation SET attribute=val or expression WHERE condition;

INSERT 명령

Relation의 이름을 지정하고 튜플에 들어갈 값들을 나열한다. INSERT 명령의 활용으로 쿼리의 결과로 relation에 여러 튜플들을 삽입할 수 있다.

-       INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week) SELECT Lname, Pname, Hours FROM EMP, WORKS_ON, PROJ WHERE Ssn=Essn AND Pno=Pnumber ;

DELETE 명령

Relation에서 튜플들을 삭제한다. 테이블 자체를 삭제하기 위해서는 DROP TABLE 명령을 사용한다. 삭제는 DDL의 참조 무결정 제약 조건의 참조된 triggered actions에 지정되어 있을 경우 다른 relation에 있는 튜플들도 영향을 받는다.

UPDATE 명령

UPDATE 명령은 하나 이상의 선택된 튜플들의 속성 값을 수정하는데 사용된다. CASE 문을 사용할 수 있다.

-       UPDATE instructor SET salary = CASE
WHEN salary <= 100000 THEN salary * 1.05
ELSE salary * 1.03 END

SQL의 추가기능

복잡한 검색 쿼리를 지정하는 테크닉: Nested queries, aggregate functions(집계 함수: 값 집합에 대한 계산을 수행하고 단일 값을 반환함. Ex) SUM, MIN, AVG…), grouping, views, triggers, assertions(일반적인 Create 명령은 튜플이 하나도 없다면 제약 조건이 검사되지 않는데, 튜플이 존재하지 않을 때도 검사하기 위한 방법)

SQL문을 포함해 다양한 프로그램 언어에서 프로그램 작성: Embedded SQL, SQL/CLI (ODBC, JDBC), SQL/PSM, 웹 프로그래밍

Relations을 위한 파일 구조, access path, 실제 DB 설계 매개 변수를 지정하기 위한 명령 집합: Storage definition language(SDL), CREATE INDEX 명령

트랜잭션 제어 명령: 동시성 제어와 복구

사용자에게 권한 부여 및 취소 지정하기:

-       GRANT <privilege list> ON <object name> TO <user list>

-       REVOKE <privilege list> ON <object name> FROM <user list>

트리거를 만들기 위한 언어 구문

Object-Relational로 알려진 발전된 관계형 시스템: Nested relations, 사용자 지정 타입, 사용자 지정 타입에 사용되는 연산자

XML, OLAP, 데이터 웨어하우스와 같은 신기술