close
프로필 배경
프로필 로고

쉽게 켜다

데이터베이스 · 2022. 11. 26. fullscreen 넓게보기

[데이터베이스] SQL(Structured Query Language) - 1

SQL은 관계형 대수와 관계형 해석을 기반으로 집계 함수와 갱신 연산 등을 추가하여 개발된 데이터 언어이다. 

DDL, DML, DCL 등 데이터베이스 언어의 모든 기능을 제공하고 있으며, 터미널을 통한 대화식 질의어와 Java, Visual Basic, C, C++, Python 등과 같은 고급 프로그래밍 언어 내에 SQL 삽입된 형태로 사용이 가능하다. 

📂 SQL 데이터 정의어

사용자는 SQL 데이터 정의어를 사용하여

테이블을 생성하고 제거하거나, 새로운 애트리뷰트를 추가하거나 제거할 수 있다. 
또한, 뷰를 생성하고 제거하거나, 인덱스를 생성하고 제거할 수도 있다.

 

정의어의 한 예로, 관계형 테이블을 생성할 때 SQL의 CREATE TABLE 문으로 만들 수 있는데, 수강 테이블을 정의한 SQL문은 다음과 같다. 

CREATE TABLE Enroll(
    sno INTEGER NOT NULL,
    code CHAR(5) NOT NULL,
    score INTEGER,
    grade CHAR(2),
    PRIMARY KEY(sno, code),
    FOREIGN KEY(sno) REFERENCES student(sno)
    	ON DELETE CASCADE
        ON UPDATE CASACDE,
    FOREIGN KEY(code) REFERENCES course(code)
    	ON DELETE CASCADE
        ON UPDATE CASCADE,
    CHECK(score>=0 AND score<=100)
);

이 테이블에는 sno, code, socre, grade 4개의 애트리뷰트가 있으며, 각각의 데이터 타입은 INTEGER, CHAR(5), INTEGER, CHAR(2)이다. 

SQL 문은 애트리뷰트 값으로 NULL을 허용하기 때문에 기본키와 같이 만일 어떤 애트리뷰트의 값으로 NULL을 허용하지 않으려면 NOT NULL을 명시해야 한다. 

 

PRIMARY KEY는 기본키를 명세하는 것으로서 기본키의 애트리뷰트는 NULL 값을 가질 수 없고 하나의 튜플을 유일하게 식별할 수 있도록 중복된 값이 존재할 수 없다. 

 

FOREIGN KEY는 외래키로서 참조 무결성을 유지하기 위한 것이다. 참조하고 있는 행이 삭제(ON DELETE)되거나 변경(ON UPDATE)될 때 취해야 할 동작을 명시하고 있다. 

이 동작에는 CASCADE, SET NULL, SET DEFAULT, NO ACTION의 4가지 옵션을 지정할 수 있다. 

  1. CASCADE 
    • 외래키가 참조하는 기본키에 삭제/변경이 일어났을 때 이를 참조하고 있는 테이블의 모든 튜플에서도 기본키와 동일한 값으로 연쇄적으로 삭제/변경이 전파된다.
  2. SET NULL
    • 외래키가 참조하는 기본키에 삭제/변경이 일어났을 때 이를 참조하고 있는 테이블의 모든 튜플은 NULL 값으로 설정(업데이트)된다. 
  3. SET DEFAULT
    • 외래키가 참조하는 기본키에 삭제/변경이 일어났을 때 이를 참조하고 있는 테이블의 모든 튜플은 DEFAULT 값으로 설정(업데이트)된다.
    • MySQL에서는 지원되지 않는다!
  4. NO ACTION
    • A개체를 삭제/변경할 때, A개체를 참조하고 있는 개체가 존재하면 A개체에 대한 명령(삭제/변경)이 취소된다. 
    • MySQL에서는 NO ACTION이 RESTRICT와 동일하다. 

CHECK 절에서는 한 애트리뷰트가 가질 수 있는 값의 범위를 지정한다. 

 

📂 SQL 데이터 조작어

SQL의 데이터 조작어에는 검색(SELECT), 삽입(INSERT), 삭제(DELETE), 갱신(UPDATE)이 있다.

📄 검색(SELECT)

SELECT 문의 기본적인 형식은 다음과 같다. 

SELECT 열_리스트
FROM 테이블_리스트
WHERE 조건;

예를 들어, '컴퓨터'과 학생의 학번과 성명을 검색하는 SELECT 문은 다음과 같다. 

SELECT sno, sname
FROM student
WHERE sdept = '컴퓨터';

한 가지 유의할 점은 SELECT 문에서 열의 이름이 명확하지 않고 다른 테이블에서 같은 이름이 중복되어 사용될 경우에 이들이 서로 혼돈되지 않도록 다음과 같이 그 소속 테이블 이름과 열 이름이 구두점(.)으로 연결된 형태를 취하는 것이 원칙이다. 

SELECT student.sno, student.sname
FROM student
WHERE sdept = '컴퓨터';

 

SQL의 SELECT 문과 이론적인 관계형 데이터 모델과의 차이점을 살펴보자. 

SQL의 SELECT 문은 질의 결과에서 똑같은 튜플을 자동적으로 제거하지 않는다. 이유는, 질의 결과에서 중복을 제거하기 위해서는 질의 결과를 먼저 정렬해야 하는데, 정렬 연산은 시간이 오래 걸릴 수 있어 DBMS는 한 테이블 내에 똑같은 튜플의 중복을 그냥 허용한다. 따라서 SELECT 결과 테이블은 이론상의 튜플 집합은 아니다. 

이론적인 관계형 데이터 모델에서는 튜플의 유일성으로 인해 두 개의 똑같은 튜플을 허용하지 않는다. 

 

따라서, DBMS에서는 사용자가 SELECT 문에 DISTINCT를 명시적으로 작성할 때만 튜플의 중복을 제거한다. DISTINCT를 명시하지 않는 경우에는 ALL을 작성한 것과 같이 중복된 레코드가 나타나더라도 이를 제거하지 않는다. 

 

SELECT 문의 일반적인 형식은 다음과 같다. 여기서 [ ] 안에 있는 내용들은 선택 사항이다.

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

 

이제 예시들을 하나씩 살펴보자. 

🏷 테이블 열 전부 검색

질의 : 학생 테이블 전부를 검색하라.

SELECT sno, sname, sdept, stel
FROM student;

SELECT *
FROM student;

테이블 전체를 검색할 때는 열 이름을 일일이 나열할 필요 없이 '*'로 표현할 수 있다. 

🏷 DISTINCT를 이용한 검색

질의 : 학생 테이블에 어떤 학과들이 있는지 검색하라.

SELECT DISTINCT sdept
FROM student;

질의 결과에서 중복된 튜플들을 제거하려면 DISTINCT 키워드를 사용한다. 이때 default 값은 ALL이다. 

🏷 조건 검색

질의 : 과목 테이블에서 학점이 3이고 학년이 4인 과목코드와 과목명을 검색하라.

SELECT code, cname
FROM course
WHERE credit=3 AND year=4;

사용자가 테이블의 일부 튜플만 검색하기를 원하는 경우 조건 검색을 수행하는데, 이때 WHERE 절을 사용한다. WHERE 절에 나오는 조건식에는 비교연산자( =, !=, <, <=, >, >= ...)와 boolean 연산자 AND, OR, NOT을 사용할 수 있다. 

🏷 LIKE를 이용하는 검색

질의 : 과목코드가 'CO'로 시작하는 과목의 과목코드와 과목명을 검색하라.

SELECT code, cname
FROM course
WHERE code LIKE 'CO%';

LIKE를 이용하면 패턴에 일치하는 데이터를 가져올 수 있다. LIKE에서 사용할 수 있는 와일드 카드 문자에는 '%'와 '_'가 있는데, '%'는 패턴에 일치하는 모든 문자를 의미하며, '_'는 패턴에 일치하는 하나의 문자(character)를 의미한다. 

🏷 NULL을 이용한 검색

질의 : 강의 테이블에서 강의실이 NULL인 교수번호와 과목코드를 검색하라.

SELECT pno, code
FROM lecture
WHERE classroom IS NULL;

애트리뷰트 값이 NULL인지 아닌지 확인하기 위해서, 'IS NULL' 또는 'IS NOT NULL'을 사용할 수 있다. 

🏷 범위 검색

질의 : 수강 테이블에서 점수가 80점부터 90점 사이의 열들을 검색하라.

SELECT *
FROM enroll
WHERE score BETWEEN 80 AND 90;

BETWEEN A AND B 를 사용해 특정 데이터 값이 기술된 두 값(A, B) 사이에 존재하는지를 검사한다. 

🏷 ORDER BY를 이용한 정렬 검색

질의 : 수강 테이블에서 점수가 80점 이상인 학생의 학번과 성적을 검사하되, 학번은 오름차순으로, 같은 학번에 대해서 성적은 내림차순으로 검색하라.

SELECT sno, score
FROM enroll
WHERE score >= 80
ORDER BY sno ASC, score DESC;

테이블에 저장되어 있는 레코드들은 어떠한 순서도 가지지 않은 상태로 저장된다. 만약 정렬된 상태로 결과를 보기를 원한다면 ORDER BY키워드를 사용하여 오름차순(ASC)이나 내림차순(DESC)으로 결과 레코드를 정렬할 수 있다.
여기서 ORDER BY 절의 첫 번째 속성은 주(1차) 정렬키이고, 두 번째 나오는 속성은 부(2차) 정렬키이다. 

🏷 계산된 열 검색

질의 : 수강 테이브에서 과목코드 'CO123'에 수강한 학생의 학번과 점수에 2점을 더한 값을 검색하되, 학번의 열 이름은 'DB수강학번', 점수의 열 이름은 '수정점수', 그리고 그 사이에 '최종점수 = '라는 텍스트 내용을 '내용'이라는 열 이름으로 디스플레이하라.

SELECT sno AS DB수강학번, '최종점수 = ' AS 내용, score+2 AS 수정점수
FROM enroll
WHERE code = 'CO123';

SELECT 문에서는 간단한 계산식을 이용하여 데이터를 가져올 수 있다. 이때 AS를 사용하여 원하는 열 이름으로 출력할 수 있다. 

🏷 조인 검색

질의 : 과목코드 'CO423'에 수강한 학생의 성명, 학과, 성적을 검색하라.

#1
SELECT sname, sdept, score
FROM student, enroll
WHERE student.sno = enroll.sno AND code='CO423';

#2
SELECT sname, sdept, score
FROM student S, enroll E
WHERE S.sno = E.sno AND code='CO423';

테이블을 조인하는 경우, 동일한 도메인을 갖는 공통 속성들을 연결해야 한다. 

조인 질의에서 열 이름이 애매하고 모호하게 되면 테이블 이름을 붙인 한정된 열 이름을 써야 한다(student.sno, enroll.sno). 
자연 조인은 FROM 절에 관련 테이블들을 여러 개 명시하면 된다. 이때 WHERE 절에서 사용하는 조건(student.sno=enroll.sno)을 조인 조건 or 조인 프레디킷(Join Predicate)이라 한다.  

#2에서와 같이, 조인 질의에서 한정 열 이름을 단순화하기 위해 FROM 절에 별칭을 사용할 수 있다. 이는 특히 셀프 조인에서 명확한 열 참조를 가능하게 한다. 바로 확인해보자.

🏷 셀프 조인(자기 자신의 테이블에 조인하는 검색)

질의 : 같은 학과 학생들의 학번을 쌍으로 검색하라. 단 첫 번째 학번은 오름차순으로 정렬하라.

SELECT S1.sno, S2.sno
FROM student S1, student S2
WHERE S1.sdept = S2.sdept
ORDER BY S1.sno;

하나의 테이블을 자신과 연결하여 자신 간에 존재하는 관련성을 분석할 수 있다. 

🏷 집계 함수(aggregate function)를 이용한 검색

질의 1 : 학생 테이블에서 학생 수를 구하여라.

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

질의 2 : 과목 'CO123'에 대한 점수의 평균을 구하여라.

SELECT AVG(score) AS 평균
FROM enroll
WHERE code='CO123';

SQL의 집계 함수에는 COUNT, SUM, AVG, MAX, MIN 등이 있다. 이들은 SELECT 절과 HAVING 절에서만 사용된다. 

🏷 GROUP BY를 이용한 검색

질의 : 과목별 점수의 평균을 구하라.

SELECT code, AVG(score) AS 평균
FROM enroll
GROUP BY code;

같은 값끼리 그룹을 묶어 데이터를 가져오는 방법이다. 

🏷 HAVING을 이용한 검색

질의 : 2명 이상 수강한 과목에 대한 점수의 평균을 구하라.

SELECT code, AVG(score) AS 평균
FROM enroll
GROUP BY code
HAVING COUNT(*) >= 2;

SELECT 문에서 조건절에 사용되는 키워드는 WHERE이다. 하지만 그룹으로 묶여서 계산된 SUM, COUNT... 와 같은 속성들에 대해 조건을 줄 때는 WHERE 절을 사용할 수 없고 HAVING 절을 사용해야 한다. 

🏷 중첩 질의문을 사용한 검색

데이터베이스에서 어떤 값들을 검색한 후에 이를 다시 비교 조건에서 사용하는 질의는 중첩 질의문을 사용하여 편리하게 표현할 수 있다.

SELECT 문의 WHERE 절 안에 다시 "SELECT-FROM-WHERE" 형태를 취하고 있는 질의문으로서 이를 부속 질의문(sub-query)라고 한다. 중첩 질의문은 소괄호 안에 표기해야 하고 INSERT, DELETE, UPDATE 문에도 사용될 수 있다. 

 

1. 한 개의 단일 값이 반환되는 경우

질의 : 학생 '김철수'와 같은 학과에 속하는 학생의 성명을 검색하라.

SELECT sname
FROM student
WHERE sdept = 
	(SELECT sdept
         FROM student
         WHERE sname='김철수');

중첩 질의문은 외부 질의문보다 먼저 수행되고 외부 질의문의 WHERE 절에서 비교연산자를 사용하여 중첩 질의문의 결과와 비교한다. 위의 경우에 중첩 질의문으로부터 여러 값들이 반환되는 경우에 에러가 발생한다. 위의 예에서 중첩 질의문으로부터 반환되는 결과는 '컴퓨터' 단일 값이다. 

 

2. 한 개의 애트리뷰트로 이루어진 테이블이 반환되는 경우

질의 : 과목코드 'CO123'을 수강한 학생의 성명을 검색하라.

SELECT sname
FROM student
WHERE sno IN
	(SELECT sno
         FROM student
         WHERE code='CO123');

질의 : 과목코드 'CO123'을 수강하지 않은 학생의 성명을 검색하라.

SELECT sname
FROM student
WHERE sno NOT IN
	(SELECT sno
         FROM student
         WHERE code='CO123');

중첩 질의문의 결과로 한 개의 애트리뷰트로 이루어진 다수의 튜플들이 반환될 수 있다. 이런 경우 외부 질의문의 WHERE 절에서는 IN, ANY, ALL, EXISTS와 같은 연산자를 사용해야 한다. 위 예시에서의 IN의 경우, 한 애트리뷰트가 중첩 질의문 결과 값들의 집합에 속하는가를 테스트한다. NOT IN은 속하지 않는가를 테스트한다.

 

질의 : 수강 테이블에서 학번 20181234의 점수보다 좋은 점수를 받은 학생의 학번과 과목코드를 검색하라.

SELECT sno, code
FROM enroll
WHERE score > ALL
    	(SELECT score
    	 FROM enroll
    	 WHERE sno = 20181234);

여기서는 > 왼쪽에 있는 점수가 오른쪽에 검색된 모든 점수보다 클 때 이 조건식은 참이 된다.

ALL은 왼쪽의 애트리뷰트 값이 오른쪽 값을 모두 만족해야 하는 AND의 의미이고, ANY는 하나 이상 만족해도 되는 OR의 의미로 생각할 수 있다.

ALL과 ANY는 비교연산자와 결합하여 사용되는데, '= ANY'는 IN과 같은 의미이고, '<> ALL'은 NOT IN과 같은 의미이다. 

 

3. 여러 애트리뷰트들로 이루어진 테이블이 반환되는 경우

질의 : 과목 'CO123'에 수강한 학생의 성명을 검색하라.

SELECT sname
FROM student
WHERE EXISTS
    	(SELECT *
         FROM enroll
         WHERE enroll.sno = student.sno AND code='CO123');

중첩 질의문의 결과로 여러 애트리뷰트로 이루어진 테이블이 반환되는 경우에는 EXISTS 연산자를 사용하여 중첩 질의문의 결과가 빈 테이블인지를 검사한다. 결과에 적어도 하나의 레코드가 들어 있으면 참이 되고, 그렇지 않으면 거짓이다. 

위의 예는 "학생 테이블에서 성명을 검색하는데, 어떤 학생이냐면, 과목 'CO123'을 수강하여 수강 테이블에 그 튜플이 존재하는 그런 학생이다'라는 뜻이다. 

🏷 UNION을 이용한 검색

질의 : '컴퓨터'과 학생이거나 'EE123'에 수강한 학생의 학번을 검색하라.

SELECT sno
FROM student
WHERE sdept = '컴퓨터'
UNION
SELECT sno
FROM enroll
WHERE code='EE123';

UNION 키워드를 적용하려면 두 SELECT 문의 결과 테이블이 합병 가능해야 한다. 또한, UNION은 합집합과 같으므로 UNION ALL을 제외하고 모든 결과 테이블에서 중복된 튜플은 자동적으로 삭제된다. 

📄 삽입(INSERT)

기존 테이블에 데이터를 추가하는 작업이다. 

주의할 점은, 참조되는 테이블에 튜플이 삽입되는 경우에는 참조 무결성 제약조건에 위배가 되지 않지만, 참조하는 테이블에 튜플이 삽입되는 경우에는 참조 무결성에 위배될 수도 있다. 

🏷 한 번에 한 튜플씩 삽입

질의 : 학번: 20181234, 성명: 오영주, 학과: '컴퓨터', 전화번호: '010-1234-5678'인 학생을 삽입하라.

INSERT
INTO student(sno, sname, sdept, stel)
VALUES (20181234, '오영주', '컴퓨터', '010-1234-5678');

INSERT
INTO student
VALUES (20181234, '오영주', '컴퓨터', '010-1234-5678');

테이블에 정의된 순서와 열이 같은 경우는 아래와 같이 생략할 수 있다. 열 이름이 생략되면 SQL은 자동적으로 전체 애트리뷰트들 중에서 왼쪽부터 오른쪽으로 순차적으로 삽입된다. 

🏷 한 번에 여러 개의 튜플을 삽입

질의 : 학생 테이블에서 '컴퓨터'과 학생의 학번, 성명, 전화번호를 검색하여 컴퓨터 테이블에 삽입하라.

INSERT
INTO computer(sno, sname, stel)
SELECT sno, sname, stel
FROM student
WHERE sdept='컴퓨터';

위의 예에선 컴퓨터 테이블이 이미 존재하고 있다는 것을 가정한다. 만일 없는 경우에는 컴퓨터 테이블을 먼저 생성해야 한다. 이 INSERT 문은 SELECT 부속 질의문을 먼저 실행하고 그 결과를 컴퓨터 테이블에 삽입한다. 

📄 삭제(DELETE)

기존 테이블에 데이터를 삭제하는 작업이다.

삽입 작업과는 반대로, 참조되는 테이블의 삭제 작업은 참조 무결성 제약조건에 위배될 수 있으나, 참조하는 테이블에서 튜플을 삭제하면 참조 무결성에 위배되지 않는다. 

🏷 단일 레코드 삭제

질의 : 학번 20181234인 학생을 삭제하라.

DELETE
FROM student
WHERE sno = 20181234;

🏷 복수 레코드 삭제

질의 : 학생 테이블의 모든 행을 삭제하라.

DELETE 
FROM student;

DELETE 문에서 WHERE 절을 생략하면 해당 테이블에서 모든 튜플이 삭제되어 빈 테이블이 된다. 

🏷 중첩 질의문을 이용한 삭제

질의 : 과목 'CO123'의 점수가 85점 이상인 '컴퓨터'과 학생을 수강 테이블에서 삭제하라.

DELETE
FROM enroll
WHERE code='CO123' AND score >= 85 AND
    enroll.sno IN (SELECT sno
                   FROM student
                   WHERE sdept='컴퓨터');

📄 갱신(UPDATE)

기존 테이블의 데이터를 갱신하는 작업이다.

갱신 작업에서는 기본키나 외래키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건에 위배될 수 있다. 

🏷 단일 레코드 변경

질의 : 성명이 '홍길동'인 학생의 학과를 '컴퓨터'로 변경하라.

UPDATE student
SET sdept = '컴퓨터'
WHERE sname = '홍길동';

🏷 복수 레코드 변경

질의 : 과목 'CO123'의 점수를 2점씩 증가시켜라.

UPDATE enroll
SET score = score+2
WHERE code = 'CO123';

🏷 중첩 질의문을 이용한 변경

질의 : '컴퓨터'과 학생의 점수를 2점씩 증가시켜라.

UPDATE enroll
SET score = score + 2
WHERE sno IN
        (SELECT sno
         FROM student
         WHERE sdept = '컴퓨터');

 

저작자표시

'데이터베이스' 카테고리의 다른 글

[데이터베이스] 데이터베이스 정규화  (2) 2022.11.29
[데이터베이스] SQL - 2  (0) 2022.11.27
[데이터베이스] 데이터베이스 모델링  (0) 2022.10.18
[데이터베이스] 데이터베이스 시스템  (0) 2022.10.18
[데이터베이스] DBMS  (0) 2022.10.15
데이터베이스 관련 글
더 보기

티스토리툴바