5. SQL 명령어

1. SQL 명령어 : CREATE TABLE

1) 명령어 형태부터 보자.

CREATE TABLE EMPLOYEE ...

위 명령어는 EMPLOYEE 이름을 가진 테이블을 생성하는 명령어이다. 

우선 아래의 예시를 보자.

 

CREATE TABLE EMPLOYEE (

....,

Super_ssn CHAR(9), 

Dno   INT   NOTNULL   DEFAULT 1,

CONSTRAINT A

  PRIMARY KEY(Ssn),

CONSTRAINT B

 FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn)  ON DELETE SET DEFAULT   ON UPDATE CASCADE 

CONSTRAINT C

  FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber)  ON DELETE SET DEFAULT   ON UPDATE CASCADE 

);

 

위 명령은 EMPLOYEE 이름을 가진 table을 만드는데 그 안의 내용으로 어트리뷰트와 기본키, 외래키 그리고 외래키가 참조하는 다른 릴레이션의 이름 그리고 각종 제약 조건들이 함께 선언된 명령문 예시이다. 

 

2) 명령어 해석

<위 명령어 기본 해석>

table은 명령에 적힌 순서대로 어트리뷰트들이 순서를 가진다.

Dno은 어트리뷰트 이름이며 이 Dno의 도메인의 데이터 타입은 INT가 된다. 그리고 defualt 값이 1이다. 즉 나중에 tuple을 생성시 이 Dno의 값을 지정하지 않으면 1로 초기화 된다.  

SQL은 애트리뷰트 값으로 NULL을 허용하기 때문에 만약 NULL을 허용하지 않는다면(기본키인 경우 등..) NOT NULL을 명시하여 NULL 값을 가질 수 없다는 제약 조건을 작성한다. 

 

<CHECK>

CREATE TABLE 명령어 사용시 애트리뷰트를 지정할때 끝에 CHECK 절을 사용하면 애트리뷰트의 값을 제한할 수 있다. 

만약 Dnubmer INT NOT NULL  CHECK (Dnubmer > 0 AND Dnumber <10); 

이렇게 애트리뷰트를 지정하면 Dnubmer의 값은 0과 10 사이의 범위로 제한할 수 있다. 

 

<키와 참조 무결성 제약 조건 명시>

기본 키를 지정하는 방법은 PRIMARY KEY(Ssn) 제약 조건을 추가한다. 

외래키를 지정하는 방법으로 아래 문장을 구성하였다. 이 문장을 해석해보면 

 FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber)  ON DELETE SET DEFAULT   ON UPDATE CASCADE 

외래키는 Dno이고 이 외래키가 참조하고 있는 릴레이션의 애트리뷰트를 표기한다. ON절을 사용해 만약 참조되고 있는 Dnubmer가 삭제되거나 없데이트 될 때 외래기가 취해야할 행동(옵션)에 대해 기술한다. 

 

그리고 각각의 제약 조건에는 CONSTRAINT 라는 키워드를 통해 그 절의 해당하는 제약 조건에 별명을 붙힐 수 있다. 각 제약 조건을 식별해야할 일이 있기 때문에 식별을 위해 이런 방법을 사용한다. 

 

---

 

2. SQL 명령어 : CREATE DOMAIN

CREATE DOMAIN MOON AS INT

위 명령어는 INT라는 도메인 값의 데이터 타입을 내가 원하는 다른 이름으로 바꾸는 명령어이다. 이제 INT라는 도메인의 데이터 타입은 MOON이라는 값으로 대체 가능해진다. 

---

 

3. SQL 기본 질의의 SELECT-FROM-WHERE

1) 검색 질의 명령어

우선 명령어의 사용된 형태부터 보자. 

 

• SELECT    Bdate, Address
  FROM     EMPLOYEE
  WHERE    Fname = 'mina' AND Minit='Kin';

위 명령은 간단한데 우선 우리가 구해야할 속성들은 Bdata와 Address이다. 

Bdate	Address
...	....

위 표는 우리가 구하려는 어트리뷰트를 나타낸 것이고 이제 FROM부터 해석하여 저 어트리뷰트에 들어가는 값을 지정한다. FROM절에서 EMPLOYEE는 속성을 보는데 그 속성이 EMPLOYEE안에 속한 속성들 중에서.. 라는 의미이다. 

WHERE는 이제 조건절을 의미하는데 EMPLOYEE안에서 Fname과 Minit의 값이 위 값으로 제한된 조건에서 어트리뷰트 Bdate와 Address의 값을 EMPLOYEE에서 찾아 출력하는 것이다. 

 

 

 

• SELECT   Fname, Lname, Address
  FROM    EMPLOYEE, DEPARTMENT
  WHERE  Dname = 'Research' AND Dnumber = Dno;

위 명령을 해석해보면 일단 Dnumber = Dno는 조인 조건이라고 부른다.

Dname이 저렇고 Dnumber와 Dno가 서로 같은것이 조건이며 이 조건들은  EMPLOYEE, DEPARTMENT 릴레이션에서만 따지면 되며 어트리뷰트 Fname, Lname, Address 값 역시 EMPLOYEE, DEPARTMENT에서만 보면된다. 

그리고 조회의 결과에서 3개의 각각의 애트리뷰트는 각 애트리뷰트에 대해 독립적이다. 즉 각 애트리뷰트는 WHERE 조건만 만족시키면 된다. 

 

※ SQL 연산자

SQL에서 애트리뷰트의 값을 다른 애트리뷰트의 값과 비교하기 위한 비교 연산자가 제공된다. 

=, <, <=, >=, <>(같지 않음) 

 

 

2) 모호한 이름의 애트리뷰터 처리 법, 별명, 재명명

SQL 에서 동일한 이름을 가지는 애트리뷰트가 여러 테이블에서 사용될 수 있다. 예를 들어 name이라는 어트리뷰트가

EMPLOYEE 테이블과 DEPARTMENT 테이블 둘 다에서 사용될 때 아래 방법과 같이 모호함을 해결할 수 있다. 

• SELECT    Fname, E.Name, D.name
  FROM     EMPLOYEE AS E, DEPARTMENT AS D
  WHERE    E.name = 'Kim' AND D.name = 'Research';

FROM 절에서 릴레이션 이름을 선언할 때 AS 뒤에 간결하게 별명으로 대처가 가능하다. 

 

 

3) WHERE 절 생략

• SELECT    Ssn, Dname
  FROM     EMPLOYEE, DEFARTMENT;

위 명령을 보면 조건절에 해당하는 FROM 절이 생략되었다. 위 결과는 모든 EMPLOYEE의 Ssn과 DEFARTMENT의 Dname의 조합으로 나타나게 된다. 예를 들어 Ssn이 1,2이 있고 Dname의 값이 A, B였다면 아래와 같은 결과가 될 것이다. 

Ssn	Dname
1	A
1	B
2	A
2	B

 

 

4) 특수 기호 사용

• SELECT    *
  FROM     EMPLOYEE
  WHERE    Don = 5;

* 기호는 "모든"이란 정규표현식으로 EMPLOYEE의 Dno=5인 모든 EMPLOYEE의 속성 값에 대한 값을 나타내는 의미가 된다. 

 

• SELECT    Fname, Lname
  FROM     EMPLOYEE
  WHERE    Address LIKE '%Houston_';

%는 0보다 큰 임의의 개수의 문자열을 의미한다. 

_는 한자리 임의의 문자열을 의미한다. 

만약 위 특수 문자를 문자열 안에서 그대로 사용하고 싶은 경우 아래와 같이 표현한다. 

 

• SELECT    Fname, Lname
  FROM     EMPLOYEE
  WHERE    Address LIKE '\%Houston\_' ESCAPE;

사용하려는 특수 기호는 \기호 뒤에 쓰며 ESCAPE라고 끝에 작성해준다. 

 

 

5) 조회 결과, 중복된 투플 제거

SQL 데이블은 모든 애트리뷰트 값이 동일한 중복 투플을 하나 이상 가질 수 있다. !!

따라서 SQL 테이블의 투플은 집합이 아니라 투플들의 다중 집합이다. 이를 'bag' 이라고도 부른다. 

SQL 질의 결과에서 중복된 투플들을 삭제하려면 아래와 같이 사용한다. 

• SELECT     ALL Salary
  FROM      EMPLOYEE

ALL  키워드는 중복을 지우지 말란 뜻으로 그냥 Salary를 선언한 것과 같다. 

 

• SELECT     DISTINCT Salary
  FROM      EMPLOYEE

DISTINCT 키워드는 조회시 Salary가 중복되는게 있으면 중복은 제거하는 의미가 된다. 

 

 

6) BETWEEN 비교 연산자

• SELECT     *
  FROM      EMPLOYEE
  WHERE    (Salary BETWEEN 300000 AND 400000) 

WHERE문은 Salary >= 30000이고 동시에 Salary <=40000인 값으로 제한하는 것이다. 

 

 

7) ORDER BY 

• SELECT    Fname, E.Name, D.name
  FROM     EMPLOYEE AS E, DEPARTMENT AS D
  WHERE    E.name = 'Kim' AND D.name = 'Research';
  ORDER BY   Fname DESC, E.Name  ASC,   D.name DESC

ORDER BY는 어트리뷰트의 질의 결과를 ASC는 오름차순, DESC는 내림차순으로 정렬한다. 디폴트 정렬은 오름차순이다. 

---

 

4. SQL 삽입 명령어

1) INSERT 명령어

가장 간단한 INSERT의 형식은 한 테이블에 tuple 하나를 추가하는 것이다. 

• INSERT INTO    EMPLOYEE
  VALUES           (어트리뷰트 값1, 어트리뷰트 값2, 어트리뷰트 값2, ...);

릴레이션 이름과 애트리뷰트 값들의 리스트를 명시며 이때 애트리뷰트의 순서는 추가하려는 테이블의 애트리뷰트 순서와 일치해야한다. 

 

 

• INSERT INTO    EMPLOYEE(Fname, Lname, Don, Ssn)
  VALUES           ('Kim', 'mina', '4', '123123123') ;

EMPLOYEE 릴레이션에서 어트리뷰트의 값이 NOT NULL이 허용되거나 디폴트 값이 지정된 어트리뷰트는 따로 값을 지정하지 않으면 그 값들로 지정되고 그렇지 않은 애트리뷰트는 위와 같이 지정해서 선언해주면 된다. 

 

또한 투플의 추가시 무결성 검사를 하는데 예를 들어 기본키에 해당하는 어트리뷰트를 지정하지 않고 INSERT를 하면 이는 reject된다. 

 

 

2) DELETE 명령어

• DELETE FROM     EMPLOYEE
  WHERE              Ssn = '123';

위 삭제 명령은 EMPLOYEE 릴레이션 안의 Ssn 값이 123인 tuple을 삭제한다. 

 

3) UPDATE 명령어

• UPDATE FROM     EMPLOYEE
  SET                   Salary = Salary*1.1;
  WHERE              Dno = 5;

위 명령은 EMPLOYEE 릴레이션에서 Dno값이 5인 tuple 에 대해 Salary 어트리뷰트의 값을 10% 증가시키는 명령이다. 

새로운 값으로 널 값이거나 디폴트 값을 명시할 수 있으며 각 UPDATE 명령당 하나의 릴레이션만 명시해야한다. 

위 삭제 명령