6장 Basic SQL

SQL이란 관계형 데이터베이스 관리자를 위한 포괄적인 언어이다. 

Table, row, column는 관계형 모델에서 relation, tuple, attribute 등으로 사용되곤 한다. 

 

Create statement

 

create는 주로 데이터를 정의할 때 주로 사용한다. 

 

 

SQL schema

 

스키마 이름으로 식별한다. 

 

각 엘리먼트를 위한 권한을 부여하는 식별자나 서술자가 포함되어있음 

 

테이블, 제약, views, 도메인, 등등이 스키마에 포함되어 있다. 

 

각 SQL은 세미콜론으로 끝난다. 

 

Catalog

SQL 환경안에 있는 스키마의 컬렉션 이름

 

새로운 테이블 생성 명령어 

테이블의 이름을 제공한다. attributes, type, 초기 제약들을 나열한다. 

 

Base tables

테이블 그리고 튜플은 사실상 만들어지고 DBMS로 파일형식으로 저장된다. 

 

Virtual relations(View)
뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블이다. 주로 하나의 테이블을 기반으로 일부의 정보만을 보여주고 싶을 때 사용한다. 

 

파일 형태로 저장되지 않는다. 

 

Create View 명령어를 통해 만들어지며 물리적 파일과 같지 않습니다.

 

 

Basic data types

Numeric data types

Integer numbers: INTEGER, INT and SMALLINT

Floating-point numbers: FLOAT or REAL, and DOUBLE PRECISION

 

Character-String data types

Fixed length: CHAR (n), CHARACTER(n)

 

Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

 

Bit-string data types

Fixed length: BIT(n)

 

Varying length: BIT VARYING(n)

 

Boolean data type

Value of TRUE or FALSE or NULL

 

DATE data type

Ten positions

Components are YEAR, MONTH, and DAY int the form YYYY-MM-DD

 

Additional data types 

Tiemstamp data type '2008-09-27 09:12:47.648302'

Date와 Time fields를 포함한다. 

 

최소 여섯자리의 10진수 비율을 더한다. 

 

옵션으로 WITH TIME ZONE 등을 제공할 수 있다. 

 

Interval data type

상대적인 값을 나열할 수 있다. 상승하거나 감소하는 값 및 날짜 시간 등등

 

 

DATE, TIME, TIMESTAMP, INTERVAL 데이터 타입은 문자열 형식으로 변환할 수 있다. 

 

Domain

하나의 속성이 취할 수 있는 동일한 타입의 원자값들의 집합이다. 프로그래밍 언어에서 데이터 타입과 비슷하다. 속성 값들을 일반적으로 atomic해서 더 이상 나눌 수 없다. 특별한 값 NULL은 모든 domain의 멤버가 될 수 있다.

 

schema를 읽기 쉽게 향상 시킨다. 

 

Example 

CREATE DOMAIN SSN_TYPE AS CHAR(9);

 

Basic Constratins

관계형 모델은 주로 세가지 제약 타입을 갖고 있다. 

 

key constraint: primary key는 겹칠 수가 없습니다. 하나의 테이블은 무조건 하나의 FK, PK를 갖고 있어야 합니다. 

 

Entity Integrity: pk의 값은 null이 올 수 없습니다. 

 

Referential Integrity: fk는 pk 또는 Null 같은 이미 존재하는 값을 가져야한다. 

 

Attribute Constraints

attribute에 Default Value 설정

 

DEFALUT <value>

 

NULL은 특정 attribute에 허용하지 않는다. 

 

Check 절

 

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

 

Primary 절: 하나 또는 하나 이상의 절을 Primary Key로 설정 가능

Dnumber INT PRIMARY KEY;

 

UNIQUE 절

 

특정 컬럼에 중복된 값이 오는 것을 막을 수 있다. 

Dname VARCHAR(15) UNIQUE;

 

FOREIGN KEY 절

 

다른 테이블을 참조하기 위해 필요한 키이다. 주로 아래와 같이 사용할 수 있다. 

 

즉 위의 사진과 같이 course 테이블의 courseid를 통해서 batches의 courseid를 통해 원하는 데이터를  참조할 수 있다. 

CREATE TABLE orders ( order_id INTEGER, 
order_date DATE, 
customer_sid INTEGER, 
amount DOUBLE, 
PRIMARY KEY (order_id), 
FOREIGN KEY (customer_sid) REFERENCES customer (sid) 
);

 

check

 

check 절은 주로 CREATE TABLE 뒤에 작성해준다. 각 튜플에 개인적으로 적용된다.

 

즉 Dept_create_date가 Mgr_start_date 보다 작은 데이터만 테이블에 저장될 수만 있다는 의미입니다.

CHECK (Dept_create_date <= Mgr_start_date);

 

SELECT 

database에서 원하는 데이터를 검색할 수 있는 명령어다. 원하는 튜플의 값을 찾을 수 있다. 

 

SELECT <attribute list> FROM <table list> WHERE <condition>;

example

SELECT Bdate, Address FROM EMPLOYEE WHERE Fname='John' AND Minit='B' AND Lname ='Smith';

 

Attribute에 이름은 서로 다른 테이블이면 같은 이름을 사용할 수 있다. 

 

각 테이블에는 예명을 붙여서 편리하게 사용할 수 있습니다. 

 

Aliases or tuple variables

FROM 에 작성해주는 테이블에 AS를 붙여서 간략하게 사용할 수 있다. 

SELECT E.Fname, E.Lname, S.Fname, S.Lname 
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn =S.Ssn;

 

WHERE 

조건을 주어 원하는 데이터 만을 뽑을 때 사용한다. 일반적으로 SELECT 문에 제일 뒤에 작성한다. 

 

*

*는 모든 attribute의 값을 나열한다. 

SELECT * FROM EMPLOYEE WHERE Dno=5;

 

SELECT의 결과에서 SQL은 자동적으로 겹치는 데이터를 제거하지 않는다. 그래서 우리는 SELECT를 DISTINCT 키워드를 사용해서 겹치지 않는 데이터만을 추출할 수 있습니다.

SELECT DISTINCT Salary FROM EMPLOYEE;

 

%를 사용해서 문자열을 맞추어서 다이나믹하게 값을 넣어줄 수 있다.

_를 사용해 하나의 문자 값을 대체해줄 수 있다.

WHERE Address LIKE '%Houston,TX%';
WHERE Ssn LIKE '__1__8901';

 

 

BETWEEN 비교 연산

WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;

 

수학 연산 기호

 

덧셈 (+), 뺄셈 (-), 곱셈 (*), division(/) SELECT에 포함해서 사용할 수 있다. SELECT 옆에 attribute의 값에 붙여서 사용할 수 있다. 

SELECT E.Salary * 1.1 FROM EMPLOYEE;

 

ORDERBY

SELECT 결과 값을 정렬해서 출력할 수 있다. 

SELECT * FROM TABLE NAME ORDER BY (ASC, DESC);

ASC는 오름 차순이고 DSC는 내림차순입니다. 

 

Insert 

원하는 tuple을 테이블에 삽입 시킬 수 있습니다. Table의 attribute가 존재하는 것과 같은 순서로 삽입되어야 한다. 

 

INSERT INTO 테이블이름
SELECT 테이블에 들어갈 값
FROM selct값을 구하기 위한 테이블
WHERE 조건

Select로 찾은 값을 반환해서 바로 테이블에 삽입할 수도 있다. 혹은 아래와 같이 사용할 수도 있다. 

 

INSERT INTO table 이름 VALUES(값 1, 값 2, 값 3, ...);

여기서 값들은 table attribute의 순서에 맞아야한다. 

 

BULK LOADING

 

BULK LOADING을 사용해서 다량의 값을 빠르고 편리하게 테이블에 넣을 수 있다. 

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

 

DELETE 

WHERE 절을 사용해서 원하는 데이터를 삭제할 수 있다. 

DELETE FROM EMPLOYEE 
WHERE Lname='Brown';

 

UPDATE

 

데이터를 원하는데로 수정할 수 있다. 이 또한 WHERE 절을 사용해서 원하는데이터만 수정할 수 있다. 

 

UPDATE PROJECT 
SET Plocation='Bellaire', Dnum=5
WHERE Pnumber=10;