8. 데이터베이스 언어

파도·2022년 8월 25일
DDLDMLSQL 데이터정의dbsql데이터 베이스데이터베이스 언어출처: 데이터베이스의 원리와 응용_한양대학교_KOCW
0

1. SQL의 소개

SQL(Structured Query Langauge)

  • 관계 데이터베이스를 위한 표준 질의어
  • 1947년에 IBM 연구소에서 데이터베이스 시스템, "시스템 R"을 질의하기 위해서 만들어진 구조화된 언어
  • 미국 표준 연구소인 ANSI와 국제표준화 기구인 ISO에서 표준화 작업을 진행
    • 1999년 SQL-99(SQL3)까지 표준화 작업이 오나료된 후 계속 수정 및 보완디고 있음

SQL의 분류

📲 데이터 정의어(DDL)
: 테이블을 생성하고 변경·제거하는 기능을 제공

🕹️ 데이터 조작어(DML)
: 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정·삭제·검색하는 기능을 제공

🗜️ 데이터 제어어(DCL)
: 보안을 위해 데이터에 대한 접근 및 사용권한을 사용자별로 부여하고거나 취호하는 기능을 제공

유형명령문
📲 DDL: Data Definition Language (데이터 정의어)-객체 생성 및 변경 시 사용CREATE (테이블 생성), ALTER (테이블 변경), DROP (테이블 삭제)
🕹️ DML: Data Manipulaion Language (데이터 조작어)-데이터 변경 시 사용SELECT(데이터 검색 시 사용), INSERT(데이터 입력), UPDATE(데이터 수정), DELETE(데이터 삭제)

📢 '테이블' 생성/변경/삭제는 데이터 정의어(DDL) VS '데이터' 검색/수정/삭제는 데이터 조작어(DML)

질의에 사용할 판매 데이터 베이스: 고객 릴레이션

질의에 사용할 판매 데이터 베이스: 제품 릴레이션

질의에 사용할 판매 데이터 베이스: 주문 릴레이션

2. SQL를 이용한 데이터 정의

SQL의 데이터 정의 기능

  • 테이블을 생성, 변경, 제거

📌 테이블 생성: CREATE TABLE문

CREATE TABLE 테이블_이름(
		1️⃣ 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
        2️⃣ [PRIMARY KEY (속성_리스트)]
        3️⃣ [UNIQUE (속성_리스트)]
        4️⃣ [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
        5️⃣ [CONSTRAINT 이름] [CHECK(조건]
 );
  • []의 내용은 생력이 가능
  • SQL 질의문은 세미콜론(;)으로 문장의 끝을 표시
    (Microsofy Access SQL에서는 필요없음)
  • SQL 질의문은 대소문자를 구분하지 않음

1️⃣ 테이블을 구성하는 각 속성의 이름, 데이터 타입, 기본 제약사항 정의

💕 속성의 정의
: 테이블을 구성하는 각 속성의 데이터 타입을 선택한 다음 널 값 허용 여부와 기본 값 필요 여부를 결정

  • NOT NULL
    속성이 널 값을 허용하지 않음을 의미하는 키워드
    ex) 고객 아이디 VARCHAR(20) NOT NULL (✏️고객아이디라는 속성에 데이터 타입은 20자 캐릭터, NULL값을 가질 수 없다)
  • DEFAULT
    속성의 기본 값을 지정하는 키워드
    ex) 적립금 INT DEFAULT 0 (✏️적립금이라는 속성을 만드는데 INT형태이고 기본값으로 0을 넣겠다)
    ex) 담당자 VARCHAR(10) DEFAULT '방경아' (✏️담당자라는 속성에는 캐릭터를 10자이하로하고 속성값이 없다면, 기본값으로 방경아로 하겠다)

2️⃣: 기본키 정의

💕 PRIMARY KEY

  • 기본키를 지정하는 키워드
    ex) PRIMARY KEY(고객 아이디) (✏️고객아이디를 PK로 하겠다)
    ex) PRIMARY KEY(주문고객, 주문제품) (✏️PK키를 주문고객과 주문제품을 결합해서 사용하겠다)

3️⃣: 대체키 정의(Microsoft Access SQL로 작동하지 않음)

💕 UNIQUE KEY

  • 대체키를 지정하는 키워드
  • 대체키로 지정되는 속성의 값은 유일성을 가지면서 기본키와 달리 널 값이 허용됨 (cf. 최소성은 가지지 않음)
    ex) UNIQE(고객이름) (✏️동명이인 값이 없다면, 고객이름을 대체키로 사용하겠다)

4️⃣ : 외래키 정의

  • FOREIGN KEY
    • 외래키를 지정하는 키워드
    • 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 제시
    • ex) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) (✏️소속부서를 외래키로 사용. 부서라는 릴레이션의 부서번호와 연결 - FK는 REFERENCES와 함께 사용된다)

5️⃣ : 데이터 무결성을 위한 제약조건 정의(Microsoft Access SQL로 작동하지 않음)

💕 데이터 무결성 제약 조건의 정의

  • CHECK
    • 테이블에 정확하고 유효한 데이터를 유지하기 위해 특정 속성에 대한 제약조건을 지정
    • CONSTRAINT 키워드와 함께 고유의 이름을 부여할 수도 있음
      ex) CHECK(재고량 >=0 AND 재고량 <= 10000) (✏️재고량은 0보다 커야하고, 10000보다 작아야한다)
      ex) CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')

👨🏻‍💻 고객 테이블 생성을 위한 CREATE TABLE 문 작성 예

CREATE TABLE 고객( 
       고객 아이디 CHAR(20) NOT NULL, #기본키라서 NOT NULL
       고객 이름 CHAR(10) NOT NULL,
       고객 나이 INT,
       등급 CHAR(10) NOT NULL,
       직업 CHAR(10),
       적립금 INT DEFAULT 0,
       PRIMARY KEY (고객 아이디)
  );

CREATE TABLE 제품(
        제품번호 CHAR(5) NOT NULL,
        제품명 CHAR(20),
        재고량 INT,
        단가 INT,
        제조업체 CHAR(20),
        PRIMARY KEY(제품 번호)
        CHECK(재고량>=0 AND 재고량 =<10,000)
 );


CREATE TABLE 주문(
		주문번호 INT NOT NULL,
        주문고객 VACHAR(20),
        주문제품 CHAR(3),
        수량 INT,
        배송지 VACHAR(30),
        주문일자 DATETIME,
        PRIMARY KEY(주문번호),
        FOREIGN KEY(주문고객) REFERENCE 고객(고객아이디),
        FOREIGN KEY(주문제품) REFERENCE 제품(제품번호)
);

# FK에서 주의해야 할 점: 속성의 이름은 달라도 되지만, 도메인은 같아야한다!🧐

📌 테이블 생성: ALTER TABLE문

☑️ 새로운 속성 추가

 ALTER TABLE 테이블_이름
 	ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];

ALTER TABLE 고객 ADD 가입날짜 DATETIME;

☑️ 기존 속성 삭제

ALTER TABLE 테이블_이름 DROP 속성_이름 CASCADE \ RESTRICT;

  • CASCADE
    : 삭제할 속성과 관련된 제약 조건이나 참조하는 다른 속성을 함께 삭제

  • RESTRICT
    : 삭제할 속성가 관련된 제약조건이나 참조하는 다른 속성이 존재하면 삭제 '거부'👋

ALTER TABLE 고객 DROP 등급 CASCADE;

📌 테이블 제거: DROP TABLE문

DROP TABLE 테이블_이름 CASCADE \ RESTRICT;

  • CASCADE
    : 제거할 테이블을 참조하는 다른 테이블도 함께 제거

  • RESTIRCT
    : 제거할 테이블을 참조하는 다른 테이블이 존재하면 제거 '거부'👋

DROP TABLE 고객 RESTRICT;

3.SQL을 이용한 데이터 조작

SQL의 데이터 조작 기능

  • 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제

📌 테이블 검색: SELECT 문

(1) 기본 검색

⭐️ SELECT 키워드와 함께 검색하고 싶은 속성의 이름 나열
⭐️ FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름 나열

📍 검색 결과는 테이블 형태로 반환 됨

SELECT [ALL|DISTINCT]속성_리스트
FROM 테이블_리스트;

  • ALL
    : 결과 테이블이 투플의 중복을 허용하도록 지정. 생략가능 (Microsoft Access default값)(✏️ex. 20살 5명, 25살 3명 존재 시, 중복이 존재함에도 20,20,20,20,20,25,25,25의 값을 반환)

  • DISTINCT
    : 결과 테이블이 투플의 중복을 허용하지 '않도록' 지정(✏️ 20,25 반환)

SELECT 고객아이디, 고객이름, 등급 
FROM 고객;


SELECT 고객아이디, 이름, 나이, 등급, 직업, 적립금 #1️⃣모든 속성을 적어준다 
FROM 고객;

OR

SELECT * #2️⃣ *을 적어준다
FROM 고객;

SELECT 제조업체
FROM 제품


⇒ 결과 테이블에서 제조업체가 중복 됨
(✏️ ALL OR DISTINCT를 적어주지 않았기 때문에 기본적으로 ALL이 들어간다)

🐣만약 , 내가 제조업체를 중복하지 않고 검색하고 싶다면?

SELECT DISTINCT 제조업체
FROM 제품


⇒ 결과 테이블에서 제조업체가 한 번씩만 나타남


📍 AS 키워드를 이용해 결과 테이블에서 속성의 이름을 바꾸어 출력 가능

SELECT 제품명, 단가 AS 가격
FROM 제품

(2) 산술식을 이용한 검색

  • SELECT 키워드와 함께 산술식 제시
    • ⭐️산술식: 속성의 이름과 +,-,*,/ 등의 산술 연산자와 상수로 구성
  • 속성의 값이 실제로 변경되는 것은 아니고 결과 테이블에서만 계산된 결과 값이 출력됨

SELECT 제품명, 단가+500 AS 조정단가
FROM 제품

(3) 조건 검색

📍 조건을 만족하는 데이터만 검색

SELECT [ALL|DISTINCT]속성_리스트
FROM 테이블_리스트
[WHERE 조건];

  • ⭐️WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건

  • 숫자뿐만 아니라 문자나 날짜 값을 비교하는 것도 가능
    ex) 'A'<'C'
    ex) '2013-12-01'<'2013-12-02

  • 조건에서 문자나 날자 값은 작은 따옴표로 묶어서 표현

SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과'; 

SELECT 주문제품, 수량, 주문일자
FROM 주문
WHERE 주문고객 = 'apple' AND 수량 >=15;

SELECT 주문제품, 수량, 주문일자, 주문고객
FROM 주문
WHERE 주문고객 = 'apple' OR 수량 >= 15;

SELECT 제품명, 단가, 제조업체
FROM 제품
SELECT 단가>=2000 AND 단가 <=3000;

(4) LIKE를 이용한 검색

  • LKIE 키워드를 이용해 부분적으로 일치하는 데이터를 검색
  • 문자열을 이용하는 조건에만 LIKE 키워드 사용 가능

SELECT 고객이름, 나이, 등급, 적립금
FROM 고객
WHERE 고객이름 LIKE '김*';

SELECT 고객아이디, 고객이름, 등급
FROM 고객
WHERE 고객아이디 LIKE '?????';

(5) NULL을 이용한 검색

  • IS NULL 키워드를 이용해 검색조건에서 특정 속성의 값이 널 값인지를 비교
  • IS NOT NULL 키워드를 이용하면 특정 속성의 값이 널 값이 아닌지를 비교 

SELECT 고객이름
FROM 고객 
WHERE 나이 IS NULL;

SELECT 고객이름
FROM 고객
WHERE 나이 IS NOT NULL;

(6) 정렬 검색

  • ⭐️ORDER BY 키워드를 이용해 결과 테이블 내용을 사용자가 원하는 순서로 출력

  • ORDER BY 키워드와 함게 정열 기준이 되는 속성과 정렬 방식을 지정

    • 오름차순(기본): ASC/ 내림차순: DESC
    • 여러 기준에 따라 정렬하려면 정렬 기준이 되는 속성을 차례대로 제시
SELECT [ALL|DISCTINT]속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[ORDER BY 속성_리스트 [ASC|DESC]];

SELECT 고객 이름, 등급
FROM 고객
ORDER BY 나이 DESC;

SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량 >=10 
ORDER BY 주문제품 ASC, 수량 DESC;

(7) 집계 함수를 이용한 검색

  • 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계함수를 이용
    • 집계함수(aggregate function)
      • 개수, 합계, 평균, 최대값, 최소값의 계산 기능을 제공
  • 집계 함수 사용 시 주의 사항
    • 집계 함수는 널인 속성 값은 제외하고 계산함
      (✏️ 5개의 튜플이 있고 10 4개, NULL값 1개 존재 → 이때, 널 값을 고려한다면 (10+10+10+10)/5가 됨. BUT❗️ 널 값 제외하기 때문에 40/4=10이 평균이 됨)
    • 집계 함수는 🚫WHERE 절에는 사용할 수 없고 SELECT절이나 HAVING절에서 사용가능

SELECT AVG(단가) 
FROM 제품


(✏️ 원래 있던 속성 값을 가지고 온 것이 아니기 때문에, '속성 값이 없음'이 열 이름이 됨)

SELECT SUM(재고량) AS '재고량 합계' # SUM(재고량) 열 값/속성 값이 없어서 재고량 합계라는 이름으로 합계 값 가지고 옴
FROM 제품
WHERE 제조업체 ='한빛제과';

✔️ 널 값을 가지고 있는 경우가 있기 때문에, 어떤 기준으로 검색하느냐에 따라서 결과 값이 달라짐

(✏️ 나이에 NULL 값 1개 존재 → NULL 값은 카운터가 되지 않음)

⇒ *을 통해 계산을 하거나, PK를 통해 계산함

SELECT COUNT(DISTINCT 제조업체) AS '제조업체 수'
FROM 제품;

(8) 그룹별 검색

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

  • ⭐️GROUP BY 키워드를 이용해 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색

    • GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성을 지정

  • ⭐️HAVING 키워드를 함께 이용해 그룹에 대한 조건을 작성

  • 그룹을 나누는 기준이 되는 속성을 SLEECT 절에도 작성하는 것

SELECT 주문제품, SUM(수량) AS 총주문수량 # GROUP BY가 들어가면, SELECT 절에 주문제품도 들어가줘야함. 그래야 수량이 어떤 제품의 합계인지 알 수 있음
FROM 주문
GROUP BY 주문제품;


❓ 아래 * 이해가 안 됨

SELECT 제조업체, COUNT(*) AS 제품 수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체;

SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*)>=3;

SELECT 등급, COUNT(*) AS 고객수, AVG(적립금) AS 평균적립금
FROM 고객
GROUP BY 등급 HAVING AVG(적립금) >=1000;

SELECT 주문고객, 주문제품, SUM(수량)
FROM 주문
GROUP BY 주문고객, 주문제품;


(✏️ SELECT 절에 배송지를 추가한다고 가정. 그룹이 이뤄져있기 때문에 배송지 값은 단일 값을 가질 수 없다. 집계함수 등을 사용하지 않았기 때문에 SELECT절에 작성이 불가하다.)

(9) 여러 테이블에 대한 조인 검색

  • 조인 검색: 여러 개의 테이블을 연결하여 데이터를 검색하는 것
  • 조인 속성: 조인 검색을 위해 테이블을 연결해주는 속성
    • 연결하려는 테이블 간에 조인 속성의 이름은 달라도 되지만 도메인은 같아야 함
    • 일반적으로 외래키가 조인 속성으로 이용됨
  • FROM 절에 검색에 필요한 모든 테이블을 나열
  • WHETE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시
  • 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시
    ex) 주문.주문고객


(✏️ '바나나라는 고객이 주문한 제품의 이름은 무엇인가요?'라고 검색을 하고 싶다. 주문테이블만 가지고 제품명을 확인 할 수 없다. 제품 테이블을 가지고 와야한다.)

SELECT 제품.제품명
FROM 주문, 제품
WHERE 주문.주문고객 = 'banana' AND  제품.주문제품 = 주문.제품번호;

# 어떤 테이블이 필요한지를 먼저 파악해야한다.

SELECT 주문.주문제품, 주문.주문일자
FROM 고객, 주문
WHERE 고객.나이 >=30 AND 고객.고객아이디 = 주문.주문고객;

SELECT 제품.제품명
FROM 제품, 고객, 주문
WHERE 고객.고객이름 = '고명석' AND 제품.제품번호 = 주문.주문번호 AND 주문.주문고객 = 고객.고객아이디;

여러테이블에 대한 조인 검색: INNER JOIN..ON 문법

SELECT *
FROM 고객, 주문
WHERE 고객.고객아이디 = 주문.주문고객

SELECT *
FROM 고객 INNER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객

(10) 부속 질의문을 이용한 검색

  • SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
    • 상위 질의문(주 질의문): 다른 SELECT 문을 포함하는 SELECT문
    • 부속 질의문(서브 질의문): 다른 SELECT 문 안에 내포된 SELECT문
      • 괄호로 묶어서 작성, ORDER BY절을 사용할 수 없음
      • 1️⃣ 단일 행 부속 질의문: 하나의 행을 결과로 반환
      • 2️⃣ 다중 행 부속 질의문: 하나 이상의 행을 결과로 반환
  • 부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문을 수행
  • 부속질의문과 상위 질의문을 연결하는 연산자가 필요
    • 단일 행 부속 질의문은 비교연산자(=,<>,>,>=,<,<=)사용가능
    • 다중 행 부속 질의문은 비교 연산자 사용불가

SELECT 제품명, 단가
FROM 제품
WHERE 제조업체 = (SELECT 제조업체 
				 FROM 판매 
                 WHERE 제품명 = '달콤비스켓');
                 
# 괄호 안(단일 질의)를 통해 '한빛제과'를 도출, 단일 행 부속질의문이기 때문에 비교연산자 =를 이용 

SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (SELECT MAX(적립금) 
			   FROM 고객);

# 최대 적립금은 단일 값이므로 단일 행 부속질의문(비교 연산자 =를 이용)
❓ 상의 SELECT 절에서 MAX(적립금)과 다른 점은 뭐징..?

SELECT 제품명, 제조업체 
FROM 제품
WHERE 주문 제품 IN (SELECT 주문제품
				   FROM 제품
                   WHERE 주문고객 = 'banana');
                   
# 'banana' 고객이 주문한 제품은 여러 개이므로 다중 행 부속 질의문(IN 연산자 사용)                   

SELECT 제품명, 제조업체 
FROM 제품
WHERE 주문 제품 NOT IN (SELECT 주문제품
				       FROM 제품
                       WHERE 주문고객 = 'banana');


SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 > ALL (SLELECT 단가
			FROM 제품
            WHERE 제조업체 = '대한식품');


(✏️ 매운쫄면이 대한식품의 어떤 제품보다 단가가 높다)

📌 데이터 삽입: INSERT문

  • ⭐️ 데이터 직접 삽입
INSERT 
INTO 테이블_이름[(속성_리스트)]
VALUES (속성값_리스트);
  • INTO 키워드와 함께 투플을 삽입할 테이블의 이름과 속성의 이름을 나열
    • 속성 리스트를 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 값이 삽입됨
  • VALUES 키워드와 함께 삽입할 속성 값들을 나열
  • INTO 절의 속성 이름과 VALUES 절의 속성 값은 숙서대로 일대일 대응되어야 함

# 삽입
INSERT
INTO 고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('stravberry', '최유경','30','vip', '공무원','100');


# 확인
SELECT * FROM 고객;
  • ⭐️ 부속 질의문을 이용한 데이터 삽입
    • SELECT 문을 이용해 다른 테이블에서 검색한 데이터를 삽입
INSERT
INTO 테이블_이름[(속성_리스트)]
SELECT 문;

📌 데이터 수정: UPDATE문

  • 테이블에 저장된 투플에서 특정속성의 값을 수정
UPDATE 테이블_이름
SET 속성_이름1 = 값1, 속성_이름2 =값2, ···
[WHERE 조건];
  • SET 키워드 다음에 속성 값을 어떻게 수정할 것인지를 지정
  • WHERE 절에 제시된 조건을 만족하는 투플에 대해서만 속성값을 수정
    • WHERE절을 생략하면 테이블에 존재하는 모든 투플을 대상으로 수정

UPDATE 제품
SET 제품명 = '통큰파이'
WHERE 제품번호 = 'p03'; # WHERE절 없으면 모든 제품이 '통큰파이'로 변경 됨


#결과
SELECT * FROM 제품;

UPDATA 제품
SET 단가 = 단가*1.1

# 결과
SELECT * FROM 제품;

UPDATA 주문
SET 주문수량 = 5
WHERE 주문고객 IN (SELECT 고객아이디
				  FROM 고객
                  WHERE 고객이름 ='정소화'); # 단일 행이라 = 사용해도 됨 

# 결과
SELECT * FROM 제품;


(정소화 고객 아이디 apple임)

📌 데이터 삭제: DELETE 문

  • 테이블에 저장된 데이터를 삭제
DELETE
FROM 테이블_이름
[WHERE 조건];
  • WHERE 절에 제시한 조건을 만족하는 투플만 삭제
    • WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 삭제해 빈 테이블이 됨

DELETE 
FROM 주문;

# 결과
SELECT * FROM 주무문;

🧐 빈 테이블이 남음. DROP TABLE과는 다름. DROP TABLE은 테이블까지 삭제

profile
파도
우당탕탕 / 블로그 이사 중
이전 포스트

5. 관계 데이터 모델

다음 포스트

6. 이상현상, 함수종속, 정규화

0개의 댓글