[데이터베이스론 기초 - 11] SQL

드디어 데이터베이스론 기초의 끝장 SQL에 다달았다.

SQL은 구조화 질의어로 데이터베이스에서 언어 역할을 한다. SQL은 관계 대수와 확장된 관계 해석에 있어 기초되는 개념이다.

고급 + 비 절차적 데이터 언어이며 UI를 제공해준다. 터미널을 통해 대화식 질의어로 사용한다.

JAVA, C++, C 등 범용 프로그래밍 언어로 된 응용 프로그램에 삽입된 형태로도 사용이 가능하다. 개개의 레코드 단위로 처리하기 보다는 레코드 집합 단위로 처리한다.

데이터 정의어 DDL, 데이터 조작어 DML, 데이터 제어어 DCL 총 3가지를 가지고 있다.

SQL에 본격적으로 들어가기 전에, 용어 정리를 할 필요가 있는데, SQL에서

• 릴레이션 = Table
• 튜플 = 행
• 애트리뷰트 = 열
• 스키마(Schema) : 하나의 사용자에 속하는 테이블과 기타 구성 요소 등의 그룹
• 카탈로그(Catalog) : SQL 시스템 내에서의 한 스키마 집합

 

---

1. 데이터 정의어(DDL) : 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 삭제할 때 사용, DBA나 DB 설계자가 사용한다. 

생성 - Create

수정 - Alter

삭제 - Drop

1) 스키마 생성

CREATE  SCHEMA  UNIVERSITY  AUTHORIZATION   SHLEE ;

2) 스키마 제거  

DROP Schema 스키마 명 {제약 조건};

Drop Schema UNIVERCITY CASCADE;

3)  테이블 생성 ** Char는 고정 길이 문자열 Varchar는 가변 길이 문자열

CREATE  TABLE Enrol(

Sno Integer NOT NULL,

Cno Char(6) NOT NULL,

Grade Integer,  // 속성 이름, 타입, 조건 

Primary Key(Sno, Cno), // 기본키 지정

Foreign Key(Sno) References Student(Sno) 

 On Delete Cascade // 삭제 시 참조하고 있는 것도 함께 삭제

 On Update Cascade, // 외래 키 지정 및 제약 조건 지정

Check(Grade >= 0 AND Grade <= 100)); // Grade 속성은 0이상 100 이하로만 지정할 수 있는 조건

4) 테이블 삭제

DROP Table 테이블_이름 {제약조건};

Drop Table Course Cascade;

5) 테이블 변경

Alter Table 테이블_이름 

([ADD 열_이름 데이터_타입] [Default 기정값] |

[DROP 열_이름] [CASCADE] |

[ALTER 열_이름 (DROP Default | SET Default 기정 값)]]);

----> 직접 구현

Alter Table Enrol Add Final CHAR Default 'F';

---

2. 데이터 조작어(DML) : 사용자 혹은 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어이다.

- 삽입 - INSERT

- 수정 - UPDATE

- 삭제 - DELETE

- 검색 - SELECT

1) Insert(데이터 삽입)

Insert Into 테이블[열 이름 리스트] VALUES (열값 리스트); 

---> 예시1>

Insert into STUDENT Valus(500, 'JOO', 1, '컴퓨터');

---> 예시2>

Insert into STUDENT(Sno, Sname) Valus(500, 'JOO');

Insert Into 테이블[열 이름 리스트] SELECT 문;  // 부속 질의문 이용

---> 예시> 

lINSERT
INTO   COMPUTER(Sno, Sname, Year)
SELECT   Sno, Sname, Year
FROM    STUDENT
WHERE   Dept = '컴퓨터';

 

2) 데이터 삭제

Delete From 테이블 [Where 조건];

---> 예시1> 

Delete From STUDENT Where Sno = 100; // Sno = 100인 레코드만 삭제

Delete From Enrol Where Cno = 'C123' AND Final < 60 AND Enrol.Sno In (Select Sno From Student where dept = '컴퓨터'); // 부속 질의문 사용

3) 데이터 갱신

Update 테이블 Set {열 이름 = 산술식} [Where 조건]

---> 예시1> 

Update Student Set year = 2 where Sno = 300;

---> 예시2> 

Update Course set Credit = Credit + 1 Where Dept = '컴퓨터';

// Update 문도 부속 질의문 사용 가능

4) 데이터 검색(Select) : DML에서 가장 중요한 부문

SELECT [ALL | DISTINCT] 열_리스트
FROM        테이블_리스트
[WHERE 조건]
[GROUP BY 열_리스트 [HAVING 조건]]
[ORDER BY 열_리스트  [ASC | DESC]];

- 테이블 열 전체 : Select * From STUDENT;

---> 예시1> 일반 검색

SELECT Sno, Sname FROM Student WHERE Dept = '컴퓨터' or Year = 4;

---> 예시2> 정렬, DESC 내림차순 ASC 올림차순

SELECT Sno, Sname FROM Student WHERE midterm >= 90  ORDER BY Sno DESC;

---> 예시3> 속성의 이름 변경 AS 사용

SELECT Sno AS 학번, Sname AS 이름 FROM Student WHERE Dept = '컴퓨터' or Year = 4;

---> 예시4> 자기 자신을 조인

SELECT S1.Sno, S2.Sno FROM Student S1, Student S2 WHERE S1.Dept = S2.Dept AND S1.Sno < S2.Sno;

** 잊으면 안 되는 조인하고자 하는 두 릴레이션에 있어서 조건>

• 조인에 사용될 각 테이블에서 컬럼(열, column)을 지정한다. 일반적으로 조인 조건은 한 테이블에서 외래 키(FK, Foreign Key)를 지정하고 다른 테이블에서 이와 관련된 키(e.g. PK)를 지정한다.

• 컬럼에서 값을 비교할 때 사용할 논리 연산자(예: =, <>)를 지정한다.

---> 예시5> 조인 방법 1 SELECT  FROM  WHERE  ;

 

SELECT S.Name, E.Grade FROM STUDENT S, Enrol E WHERE S.Sno = E.Eno AND E.Cno='C413';

---> 예시6> 조인 방법 2

SELECT  Sname, Dept, Grade FROM Student Join Enrol On (Student.Sno = Enrol.Sno)  WHERE  Enrol.Cno='C413';

---> 예시6> 조인 방법 3

SELECT  Sname, Dept, Grade FROM Student Join Enrol USING(Sno)  WHERE  Enrol.Cno='C413';

---> 예시7> 조인 방법 4, 자연 조인

SELECT  Sname, Dept, Grade FROM Student NATURAL JOIN Enrol WHERE  Enrol.Cno='C413';

---> 예시8> 집계 함수 사용1

SELECT COUNT(*) AS 학생수 FROM STUDENT;

---> 예시9> 집계 함수 사용2

SELECT   COUNT(DISTINCT Cno) FROM      ENROL WHERE   Sno = 300;

---> 예시10> 집계 함수 사용3

SELECT   AVG(Midterm) AS 중간평균 FROM      ENROL WHERE   Cno = ‘C413’;

---> 예시11> Group By, Having 사용(Group BY의 조건문)

SELECT    Cno, AVG(Final)  AS  평균  FROM Enorl GROUP BY Cno HAVING COUNT(*) >=  3;

** 집계함수

COUNT	검색된 행의 수 반환
MAX	컬럼값 중에서 최대값을 반환
MIN	컬럼값 중에서 최소값을 반환
AVG	평균 값을 반환
SUM	검색된 칼럼의 합을 반환
STDDEV	표준편차를 반환

---> 예시12> IN 사용, IN은 집합의 ∈로 해석된다. 즉 부속 질의문을 통해 얻은 값들을 통해 조건문을 수행한다.

** NOT IN 은 이 얻은 값들이 아닌 집합을 통해 조건문 수행

** IN 은 = 와 같이 사용될 수 있다.

SELECT    Sname
 FROM      STUDENT
 WHERE    Sno  IN
              (SELECT  Sno
               FROM   ENROL
               WHERE   Cno = 'C413');

---> 예시13> NULL 사용(NULL은 누락된 정보, 값은 있지만 모르는 값 ,해당되지 않는 값, 의도적으로 유보한 값)

SELECT    Sno, Sname
 FROM     STUDENT
 WHERE    Dept  IS  NULL;

lSELECT    Sno, Sname
 FROM     STUDENT
 WHERE    Dept  IS NOT NULL;

---> 예시 14> EXISTS 사용 (IN이랑 헷갈릴 수 있는데 이는 부속 질의문이 존재만 한다면 즉, 공집합이 아니면 수행한다는 뜻이다)

 SELECT   Sname
 FROM     STUDENT
 WHERE    EXISTS
            (SELECT   *
              FROM    ENROL
              WHERE     Sno = STUDENT.Sno
                                      AND   Cno = 'C413');

** 이 밖에도 부속 질의문의 전체 조건을 불러오는 ALL

** 서브 스트링 패턴 비교 연산자인 LIKE(%, _사용)

SELECT Cno FROM Course WHERE Cno LIKE 'C%';

** 합집합 UNION 연산자(중복되는 투플 제거)가 있다.

---

3. 데이터 조작어(DCL) : 무결성, 보안 및 권한 제어, 회복 등을 하기 위한 언어이다.

- 데이터를 보호하고 데이터를 관리하는 목적으로 사용된다.

- 데이터의 제어 언어 기능 보안과 무결성 유지, 병행 수행을 제어한다.

---

** 제약 조건

• RESTRICT: 다른 곳에서 이 도메인을 참조하고 있으면 실행 실패
• CASCADE: 이 도메인을 참조하고 있는 뷰나 제약조건도 함께 명령 실행
• DISTINCT : 검색 결과에서 레코드의 중복 제거 (Select Distinct Dept From STUDENT;)