[DB08-1] SQL 알아보기_SELECT문

* 12.01 업데이트

해당 게시물은 한양대학교 컴퓨터소프트웨어학부 김상욱 교수님 데이터베이스시스템 온라인 강의를 듣고 정리한 자료입니다.

오류가 있다면 언제든 알려주세요!

● 해당 강의의 목표

1. SQL(Structured Query Language)의 기초 개념에 대해서 배운다. 

▪ SQL을 위한 데이터 정의

▪ data retrieval queries에 대해 배운다.

 

◆  Introduction to SQL

◆ History

▪ IBM 연구소에서 개발한 언어

 - 데이터베이스를 다루는 특별한 언어이고 procedure한 언어와 다르게 내가 원하는 것이 무엇이다 선언을 하는 declarative programming language이다. 

 - IBM의 첫 DBMS인 System R을 위한 language로 만들어졌다.

 - 초창기에는 SEQUEL(Structured English Query Language)라고 하다가 SQL(Structured Query Language)로 이름이 바뀌었다.

▪ ANSI(American National Standards Institute)에 의해 표준화되었다.

▪ 많은 DBMS가 해당 언어를 사용한다.

 

◆ Components

▪ Data Definition Language (DDL)

 - 데이터베이스, 테이블과 인덱스에 생성, 변경, 삭제 등의 명령을 제공한다.

▪ Data Manipulation Language (DML)

 - tuple에 대한 연산, retrieval(가져와), insert, modify, delete 등을 제공한다.  

◆  Example Database: COMPANY

 

 

◆  Schema Creation

위의 database를 구축하려고 한다. 가장 먼저 해야할 것은 해당되는 database를 DBMS에게 알려주는 일이다. 이를 Schema Creation이라고 한다.

 

◆ SQL Schema

▪ 테이블과 constraint을 포함한 구조들의 집합

 - 보통 같은 database application에 속하는 것을 묶어놓는 용도로 사용된다.

▪ CREATE SCHEMA statement를 이용해서 데이터베이스 스키마를 생성한다.

 - 이는 schema name으로 식별된다.

 

◆ Example

▪ Jsmith가 가진 COMPANY schema를 만들자.

▪ CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

 

◆  Table Creation

◆ CREATE TABLE statement

▪ 새로운 table을 구체화한다.

 - table name

 - attributes(list of <attribute_name, data_type> pairs)

 - Initial constraints (각 attribute과 관련된 제약조건)

위의 세 가지를 명시함으로써 하나의 테이블을 생성한다.

 

◆ Attribute

▪ Data types

 - INT, FLOAT, DECIMAL, CHAR(n) (고정길이 n), VARCHAR(n) (최대 n, 가변길이),...

▪ NOT NULL attribute constraint

 - NULL이 value로 들어올 수 없도록 한다. DBMS가 NULL이 들어오면 안 받는다.

▪ DEFAULT clause

 - 아무것도 명시를 하지 않았을 때 default value가 자동으로 들어간다. null 값이 들어오면 해당 디폴트가 자동으로 setting된다.

 

◆ Initial constraints

▪ PRIMARY KEY clause

 - 테이블을 만들 때 어느 속성이 primary key인지 명시하는 문구이다.

 - Primary key는 테이블에 하나만 존재할 수 있는데 하나의 key에 두 개 이상의 attribute가 연합될 수 있다.

▪ UNIQUE clause

 - 여러 개의 candidate key 중에서 primary key가 되지 못한 나머지 키들을 secondary key라고 한다.

 - Unique를 통해서 secondary key(alternate key)를 명시할 수 있다.

▪ FOREIGN KEY clause

 - FK임을 명시하여 referential integrity constraint을 만족시키기 위해 사용된다.

 - 위배되는 상황이 발생하면 action을 취한다. 이를 referential triggered action이라고 부른다.

 ex) 1의 FK가 2의 PK를 잘 가리키고 있었는데 갑자기 2에 있는 tuple이 삭제됐다고 하자. 그러면 1은 없는 값을 가지고 있게 된다. (ON DELETE)

 ex) FK가 7이라는 primary key를 가리키고 있었는데 PK가 7 -> 9 가 되면 referential integrity가 깨지게 된다. (ON UPDATE)

- Actions: SET NULL(FK를 NULL로 바꾼다.), CASCADE(FK도 자동으로 7->9로 바뀐다.), SET DEFAULT(7->9로 바뀌면 디폴트 값으로 바뀌게 한다.)

- Triggers(언제 이 상황이 발생하나): ON DELETE, ON UPDATE

 

◆ Example

▪ DEPARTMENT table

▪ CREATE SCHEMA DEPARTMENT

  (Dname                  VARCHAR(10)          NOT NULL,          // 길이가 다를 수 있기 때문에 varchar로 선언

  Dnumber                INT                             NOT NULL,

  Mgr_ssn                 CHAR(9)                   NOT NULL,          // 고정 길이

  Mgr_start_date     DATE,                                                      // 년, 월, 일이 정의되어있는 타입

  PRIMARY KEY (Dnumber),                                                  // primary key를 알려주는 constraint

  UNIQUE (Dname),                                                                 // secondary key를 알려주는 constraint

  FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) );  

▪ 다 명시해서 DBMS에게 primary key니까 나중에 중복된 값이 들어오는 것을 막아줘!

  unique도 secondary key니까 중복 허용하면 안 돼!

  Mgr_ssn이 foreign key니까 employee(ssn)에 없는 값들이 나오면 안 돼!

  로 강제한다.

 

◆  Table Creation: Examples

◆ COMPANY schema

▪ EMPLOYEE table를 정의해보자.

 - salary는 10자리 중에 소숫점 2자리까지 허용한다. 

 

 - Q. 왜 DEPARTMENT를 다시 선언하지?

    A. Employee 선언 후 department를 선언해야 한다. department에 employee를 참조하라는 줄이 있다. employee가 정의되기 전에 department를 선언하면 error가 발생한다. 

 - 서로 맞물려 있는 경우에는 다른 테이블을 참조하는 부분은 선언하지 않는다. 이후 table에 add하여 넣어준다.

 - 대응되는 ER schema, relational schema를 떠올리면서 하기.

 - Plocation은 NULL이어도 된다. 재택근무도 할 수 있는 것이다. 

 - M:N이라 FK로 해결할 수 없어서 새롭게 relation을 만들었다. 

 

 

◆ Referential triggered actions

 

 - Dno는 foreign key이다. 값을 주지 않으면 default 1로 선언한다.

 - constraint을 변경할 때 사용하기 위해 PRIMART KEY (Ssn) 제약사항을 EMPPK라고 이름을 붙여주었다. 

 - constraint이 위배 되는 경우를 생각해보자. 101번 employee가 100번 supervisor를 가지고 있었는데 상사가 회사를 그만뒀다. 100번의 튜플이 사라지니 이는 referential integrity를 깨뜨리는 상황이다. 이런 경우는 100번인 사람을 지울 수 없고 (ON DELETE SET NULL)로 추가적인 action을 넣어 위반 사항을 피해 삭제가 가능하다. 

 - 다른 예시를 들어보자. 100번인 사람이 번호가 200번으로 바뀌었다. 그러면 100번을 상사로 가지고 있던 부하직원도 200번을 가리키게끔 해주어야한다. 따라서 ON UPDATE CASCADE를 통해 100 -> 200를 자동으로 바꾸도록 한다. 이를 Referential triggered actions라고 부른다.

 - department 10번이 없어지면 set default를 통해 1을 가리키도록 하라는 뜻이고 20으로 update될 때 자동으로 모든 employee의 dno를 20번으로 바꿔주라는 뜻이다.

 

 - DEPTSK를 통해서 unique (Dname)을 없앨 수 있다.

 - ON DELETE가 되면 default로 바꿔주고 id가 바뀌면 자동으로 업데이트 된다.

 

 - department의 한 tuple이 없어졌으면 이를 참조하고 있던 tuple들도 다 delete한다.

 - department의 dno가 바뀌면 자동으로 업데이트 된다.

 

 

◆ Schema and Table Deletion

◆ DROP SCHEMA statement

▪ 스키마(database) 전체가 필요없을 때 사용하는 명령어이다.

▪ Options

 - CASCADE

    스키마와 안에 있는 모든 element를 지우고 싶을 때 사용한다. 

 - RESTRICT

    안에 element가 아무것도 없을 때 schema를 지운다. 만약 뭔가 있었다면 오류를 출력한다.

▪ Example

 - COMPANY schema와 모든 element를 지워보자.

 - DROP SCHEMA COMPANY CASCADE;

 

◆ DROP TABLE Statement

▪ 테이블과 그 안에 있던 tuple들을 지우는 명령어이다.

▪ Options

 - CASCADE

    테이블과 그 테이블을 참고하고 있는 모든 element를 삭제한다.

 - RESTRICT

    테이블을 없애는데 그 테이블과 관련된 무언가 있으면 없애지 못한다.

▪ Example

 - COMPANY schema에서 DEPENDENT table을 지워보자.

 - DROP TABLE DEPENDENT CASCADE;

▪ Drop Schema, Table은 거의 일어나지 않는다. 

 

 

◆ Altering Tables

table안에 있는 tuple을 변경하겠다는 뜻이 아니다. table의 구조를 schema level에서 바꾼다는 것이다. DDL에 속하는 부분이다.

 

◆ ALTER TABLE statement

▪ table 안에 있는 attribute(column), 어떤 column을 추가하거나 삭제할 때

▪ column의 정의를 바꾸고 싶을 때

▪ table 내에 들어있는 constraint을 추가하거나 삭제하고 싶을 때

 

◆ Examples

▪ ALTER TABLE COMPANY.EMPLOYEE

  ADD COLUMN Job VARCHAR(12);

▪ ALTER TABLE COMPANY.EMPLOYEE

  DROP COLUMN Address CASCADE;   // address 관련되어 있던거 다 삭제

▪ ALTER TABLE COMPANY.EMPLOYEE

  ALTER COLUMN Mgr_ssn DROP DEFAULT;   // mgr_ssn에 default가 있었는데 이를 더 이상 사용하고 싶지 않다. 즉, column에 있는 constraint을 없애고 싶다.

▪ ALTER TABLE COMPANY.EMPLOYEE

  ALTER COLUMN Mgr_ssn SET DEFAULT `333445555’;   // 새롭게 default를 set할 것이다.

▪ ALTER TABLE COMPANY.EMPLOYEE

  DROP CONSTRAINT EMPSUPERFK;   // 이름이 있었기 때문에 훨씬 쉽게 constraint을 삭제할 수 있다. 해당 외래 키 제약조건이 삭제된다.

 

--> 이 모든 것이 Table를 바꾸는 것이지 Tuple을 바꾸는 것이 아니다.

 

DDL에 해당되는 것을 배웠다. data definition language 즉, schema와 관련된 것이다. 스키마를 create, drop하고 table을 create drop 등으로 table 구조를 변경하는 것처럼 구조, 뼈대에 관련되어있다.

 

 

이제부터 DML instance와 관련된 연산들이다.

◆ SELECT Query Basics

매우 중요!!!

◆ SELECT statement

▪ 데이터베이스로부터 우리가 원하는 정보를 가져오는데 사용하는 기본적인 statement이다.

 - relational algebra에서 배운 SELECT와 다른것이다. 이는 하나의 table 내에서 우리가 원하는 tuple들을 골라내는 operation이었다. SQL에서 SELECT는 relational algebra에서 SELECT, PROJECT, JOIN이 모두 하나에 담겨진 기본적인 연산의 단위이다. 

▪ SQL은 table내에 모든 attribute value가 똑같은 tuple들이 2개 이상 존재하는 것을 허용한다. 완전히 똑같은 2개 이상의 tuple들이 존재하는 것은 원래 relational model에서 허용하지 않았다. set of tuples가 하나의 table이었기 때문이다.

하지만 SQL에서는 table을 set으로 간주하지 않기 때문에 이를 허용한다.

▪ 만약 중복을 원하지 않으면 DISTINCT clause를 통해 제한한다.

 

◆ Basic Form:

▪ SELECT     <attribute list>

  FROM        <table list>

  WHERE      <condition>;

 

<table list> 테이블로부터 (여러개 테이블이 들어갈 수 있지만 일단은 하나라고 생각하자) tuple들이 있을텐데 그 중 이 condition을 만족하는 tuple들만 골라내서 attribute list에 있는 attribute값들만 보여줘.

 

▪ <attribute list> : query에 의해서 꺼내지길 바라는 attribute의 list 

▪ <table list> : 관심있는 table name들의 list

▪ <condition> : table에 있는 tuple들 중 우리가 원하는 tuple들만 골라내기 위한 boolean expression

 

◆ Query 0. Retrieve the birth date and address of the employee(s) whose name is ‘John B. Smith’

이름이 ‘John B. Smith’인 employee의 생일과 주소를 알려줘.

 

1. employee

from 절

2. ‘John B. Smith’를 찾아야한다.

where 절 

3. birth date, address

select 절

 

 

먼저 FROM절부터 작성한다. 어떤 table을 보여줄지 먼저 알려주는 것이다.

두 번째로 어떤 tuple들에 관심있는지 작성한다. WHERE절

마지막으로 어떤 속성을 보고 싶은지 작성한다. SELECT 절

 

 

8-2에서 계속!