CS/데이터베이스
[데이터베이스] ch07. 데이터베이스 언어 SQL
rngPwns
2025. 4. 27. 13:50
01. SQL(Structured Query Language)
- 의미 : 관계 데이터베이스를 위한 표준 질의어 , 비절차적 데이터 언어
- 발전 역사 : 미국 표준 연구소 ANSI와 국제 표준화기구 ISO에서 표준화작업을 진행, 계속 수정 및 보완
- 사용방식 :
- 대화식 SQL : 데베 관리 시스템에 직접 접근 -> 질의 작성+실행
- 삽입 SQL : 프로그래밍 언어로 작성한 응용프로그램에 삽입
(우리는 only 권한부여만 배운다)
- SQL의 분류
- 데이터 정의어(DDL) : 테이블을 생성하고 변경, 삭제하는 기능 제공
- 데이터 조작어(DML) : 테이블에 새 데이터를 삽입하거나 테이블에 저장된 데이터를 수정, 삭제, 검색하는 기능 제공
- 데이터 제어어(DCL) : 보안을 위해 데이터에 대한 접근 및 사용권한을 사용자별로 부여하거나 취소하는 기능 제공
02. SQL을 이용한 데이터 정의
테이블 생성 : CREATE TABLE 문
- [ ] 의 내용은 생략 가능
- SQL문은 세미콜론(;)으로 문장 끝 표시 (속성나열 : 쉼표로 구분)
- SQL문은 대문자 구분 X
- 추가설명
- 테이블 구성하는 각 속성의 이름, 데이터 타입, 기본 제약사항 정의
- 기본키 정의
- 대체키 정의
- 외래키 정의
- 데이터 무결성을 위한 제약조건 정의
- 속성의 정의
- 테이블을 구성하는 각 속성의 데이터 타입을 선택한 후, 널 값 허용 여부와 기본 값 필요 여부 결정
- NOT NULL
- 속성이 널 값을 허용하지 않음을 의미
- ex) 고객아이디 VARCHAR(20) NOT NULL
- DEFAULT
- 속성의 기본 값을 지정하는 키워드
- ex) 적립금 INT DEFAULT 0 (적립금이 들어오지 않으면 0)
- ex) 담당자 VARCHAR(10) DEFAULT '방경아'
- 문자열이나 날짜 데이터는 작은 따옴표로 묶어 표현 (작은 따옴표로 묶인 문자열은 대소문자 구분)
- 속성의 기본 값을 지정하는 키워드
키의 정의
- PRIMARY KEY : 기본키 지정 키워드
- ex. PRIMARY KEY(고객아이디), PRIMARY KEY(주문고객, 주문제품)
- 개체무결성 위함 : not null 지정 안 해도 자동으로 not null 된다.
- UNIQUE : 대체키 지정 키워드. 대체키 지정 속성값은 유일성을 가지며, 기본키와 달리 널값 허용
- ex) UNIQUE(고객이름)
- FOREIGN KEY : 외래키 지정 키워드.
- 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 제시.
- 참조 무결성 제약조건 유지를 위함
- 참조되는 테이블에서 투플 삭제 시 처리방법 지정하는 옵션
- ON DELETE NO ACTION : 투플을 삭제하지 못하게 함
- ON DELETE CASCADE : 관련 투플을 함께 삭제
- ON DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경
- ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본값으로 변경
- ON UPDATE NO ACTION : 투플 변경하지 못하게 함
- ON UPDATE CASCADE : 관련 투플에서 외래키 값 함께 변경
- ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경
- ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본값으로 변경
- ex) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE(or UPDATE) CASCADE
데이터 무결성 제약조건의 정의
- CHECK
- 테이블에 정확, 유효한 데이터 유지를 위해 특정 속성에 대한 제약조건 지정
- CONSTRAINT 키워드와 함께 고유 이름 부여 가능
- ex) CHECK(재고량 >=0 AND 재고량 <=10000) - (제약조건 이름 직접 붙여줌)
- ex) CONSTRAINT CHL_CPY CHECK(제조업체 = '한빛제과')
테이블 변경 : ALTER TABLE 문
새로운 속성 추가
기존 속성 삭제
새로운 제약조건 추가
기존 제약조건의 삭제
테이블 삭제 : DROP TABLE 문
- 만약 삭제할 테이블을 참조하는 테이블이 있다면?
- 테이블 삭제가 수행되지 않음
- 관련된 외래키 제약조건 먼저 삭제해야 함
[DELETE VS DROP]
- DELETE: 데이터 조작어에서 사용, 정의(스키마)는 사라지지 않고 고객테이블의 모든 레코드가 다 삭제
- DROP : 데이터 정의어에서 사용, 테이블 자체가 없어짐, 정의(스키마) 자체를 삭제
03. SQL을 이용한 데이터조작
데이터 검색 : SELECT 문
기본검색
- SELECT 키워드와 함께 검색하고 싶은 속성의 이름 나열
- FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름을 나열
- 검색 결과는 테이블 형태로 반환됨
SELECT [ALL | DISTINCT ] 속성_리스트
/* ALL : 결과 테이블이 투플의 중복 허용하도록 지정, 생략가능
DISTINCT : 결과 테이블이 투플의 중복을 허용하지 않도록 지정*/
FROM 테이블_리스트;
그럼 ALL 키워드를 사용해 제품 테이블에서 제조업체를 검색해볼까?
SELECT ALL 제조업체
FROM 제품;
--> 결과테이블에서 제조업체 중복되는 똑같은 결과 나옴
그럼 중복없이 검색하려면?
SELECT DISTINCT 제조업체
FROM 제품;
AS키워드를 이용해 결과 테이블에서 속성의 이름을 바꾸어 출력 가능
- 새로운 이름에 공백이 포함돼있으면 큰따옴표나 작은따옴표로 묶어줘야 한다.
- 오라클 : 큰따옴표, MS SQL 서버에서는 작은따옴표 사용
- AS 키워드는 생략가능
산술식을 이용한 검색
- SELECT 키워드와 함께 산술식 제시
- 산술식: 속성의 이름과 +, -, *, / 등의 산술 연산자와 상수로 구성
- 결과 테이블에서만 계산된 결과 값이 출력됨
- 속성의 값이 실제로 변경되는 것은 X
조건검색
조건을 만족하는 데이터만 검색
- WHERE 키워드와 함께 비교연산자와 논리연산자를 이용한 검색조건 제시
- 숫자뿐만 아니라 문자, 날짜 값 비교도 가능
- ex) 'A' < 'C'
- ex) '2022-12-01' < '2022-12-02'
- 조건에서 문자나 날짜 값은 작은 따옴표로 묶어 표현
- 숫자뿐만 아니라 문자, 날짜 값 비교도 가능
Like를 이용한 검색
- LIKE 키워드를 이용해 부분적으로 일치하는 데이터 검색
- 문자열을 이용하는 조건에만 LIKE키워드 사용가능
NULL을 이용한 검색
- IS NULL 키워드를 이욯해 특정 속성의 값이 널값인지 비교
- IS NOT NULL 키워드를 이용해 특정 속성의 값이 널 값이 아닌지 비교
- 검색 조건에서 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 된다.
정렬검색
- ORDER BY 키워드를 이용해 결과 테이블 내용을 사용자가 원하는 순서로 출력
- ORDER BY 키워드와 함께 정렬 기준이 되는 속성과 정렬 방식 지정
- 오름차순(기본): ASC / 내림차순 : DESC
- 널값은 오름차순에서는 맨 마지막, 내림차순에서는 맨 먼저 출력
- 여러 기준에 따라 정렬하려면 정렬 기준이 되는 속성들을 차례대로 제시
집계 함수를 이용한 검색
- 특정 속성 값을 통계적으로 계산한 결과를 탐색하기 위해 집계함수 이용
- 집계함수(aggregate function)
- 열 함수(column function)라고도 한다.
- 개수, 합계, 평균, 최댓값, 최솟값의 계산기능 제공
- 집계함수(aggregate function)
- 집계함수 사용 시 주의 사항
- 집계함수는 널인 속성 값 제외하고 계산
- WHERE절에서는 사용 불가, SELECT 절이나 HAVING 절에서만 사용가능
그룹별 검색
- GROUP BY 키워드를 이용해 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색
- GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성 지정
- HAVING 키워드와 함께 그룹에 대한 조건 작성 가능
- 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋음
여러 테이블에 대한 조인검색
- 조인 검색 : 여러 개의 테이블을 연결하여 데이터 검색
- 조인 속성 : 조인 검색을 위해 테이블을 연결해주는 속성
- 연결하려는 테이블 간에 조인 속성의 이름은 달라도 되지만 도메인은 같아야 한다.
- 일반적으로 외래키를 조인 속성으로 이용
- FROM 절에 검색에 필요한 모든 테이블 나열
- WHERE절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건 제시
- 속성 이름 앞에 해당 속성이 소속된 테이블 이름 표시
- 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있어서! ex) 주문.주문고객
- 여러 테이블에 대한 조인 검색
- 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있어서! ex) 주문.주문고객
- 표준 SQL에서는 INNER JOIN과 ON키워드를 이용해 작성하는 방법도 제공
외부 조인 검색
- 조인 조건을 만족하지 않는 투플에 대해서도 검색 수행
- OUTER JOIN과 ON키워드를 이용해 작성
- 분류
- 모든 투플을 검색 대상으로 하는 테이블이 무엇이냐에 따라 분류
- 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인
부속 질의문을 이용한 검색
- SELECT 문 안에 또 다른 SELECT문 포함하는 질의
- 상위 질의문(주 질의문): 다른 SELECT문을 포함하는 SELECT문
- 부속질의문(서브질의문) : 다른 SELECT문 안에 들어있는 SELECT문
- 괄호로 묶어서 작성, ORDER BY 절 사용불가
- 단일 행 부속 질의문 : 하나의 행을 결과로 반환
- 다중 행 부속 질의문 : 하나 이상의 행을 결과로 반환
- 부속 질의문 먼저 수행 -> 그 결과를 이용해 상위 질의문 수행
- 부속 질의문과 상의 질의문을 연결하는 연산자 필요
- 단일 행 부속질의문 : 비교연산자(=, <>, >, >=, <, <=) 사용 가능
- 다중 행 부속질의문 : 비교연산자 사용불가
상관관계질의
- 상관관계질의(Correlated Subquery) : 메인 쿼리의 값을 참조하여 결과 반환하는 질의
- 메인 쿼리의 각 행에 대해 서브쿼리 실행 -> 일반적 서브쿼리와 달리 메인쿼리, 서브쿼리가 서로 의존적
- 기본구조
SELECT 메인쿼리_속성
FROM 메인쿼리_테이블
WHERE 조건=(SELECT 서브쿼리_속성
FROM 서브쿼리_테이블
WHERE 서브_테이블_속성 = 메인_테이블_속성)
- 질의 내용은 다양하게 표현 가능. 사용자가 자유롭게 선택
데이터 삽입 : INSERT 문
데이터 직접 삽입
- 테이블에 투플 직접 삽입
- INTO 키워드와 함께 투플을 삽입할 테이블의 이름과 속성의 이름 나열
- 속성 리스트를 생략하면 테이블을 정의할 때 지정된 속성의 순서대로 값이 삽입됨
- VALUES 키워드와 함께 삽입할 속성 값들을 나열
- INTO 절의 속성 이름과 VALUES 절의 값은 순서대로 일대일 대응되어야 함
데이터 수정: UPDATE 문
- 테이블에 저장된 투플에서 특정속성의 값을 수정
- SET 키워드 다음에 속성 값을 어떻게 수정할 것인지 지정
- WHERE 절에 제시된 조건을 만족하는 투플만 속성 값을 수정
- WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 대상으로 수정
데이터 삭제 : DELETE 문
- 테이블에 저장된 데이터 삭제
- WHERE 절에 제시한 조건을 만족하는 투플만 삭제
- WHERE 절 생략 -> 테이블에 존재하는 모든 투플 삭제 -> 빈 테이블이 된다.
04. 뷰
- 다른 테이블과 뷰를 기반으로 만들어진 가상 테이블
- 데이터를 실제로 저장X, 논리적으로만 존재
- 일반 테이블과 동일한 방법으로 사용
- 뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있음 BUT 기본 테이블의 내용을 바꾸는 작업은 제한적
- 기본테이블 : 뷰를 만드는 데 기반이 되는 물리적 테이블
- 다른 뷰를 기반으로 새로운 뷰를 만드는 것도 가능
- 기본테이블을 들여다볼 수 있는 창 역할 담당(보고싶은 부문만 발췌해서 볼 수 있다.)
뷰 생성 : CREATE VIEW 문
- CREATE VIEW 키워드와 함께 생성할 뷰의 이름, 속성 이름 나열
- 속성 리스트 생략 -> SELECT 절에 나열된 속성 이름 그대로 사용
- AS 키워드와 함께 기본 테이블에 대한 SELECT 문 제시
- SELECT 문은 생성하려는 뷰의 정의 표현, ORDER BY는 사용불가
- 오라클과 같은 일부 DBMS에서는 ORDER BY 허용하기도 한다.
- SELECT 문은 생성하려는 뷰의 정의 표현, ORDER BY는 사용불가
- WITH CHECK OPTION
- 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건 지정
뷰 생성 : CREATE VIEW 문
뷰 활용 : SELECT 문
- 일반 테이블과 같은 방법으로 원하는 데이터 검색 가능
- 뷰에 대한 SELECT문이 내부적으로는 기본 테이블에 대한 SELECT문으로 변환되어 수행
- 검색 연산은 모든 뷰에 수행가능
뷰 활용 : INSERT, UPDATE, DELETE 문
- 뷰에 대한 삽입, 수정, 삭제 연산 가능
- 실제로 기본 테이블에 수행 -> 결과적으로는 기본테이블이 변경
- 뷰에 대한 삽입,수정,삭제 연산은 제한적으로 수행
- 변경 가능한 뷰 VS 변경 불가능한 뷰
- 변경 불가능한 뷰의 특징
- 기본테이블의 기본키를 구성하는 속성이 포함 X
- 기본 테이블에서 NOT NULL로 지정된 속성이 포함되어 있지 않은 뷰
- 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함하는 뷰
- DISTINCT 키워드를 포함하여 정의한 뷰
- GROUP BY 절을 포함하여 정의한 뷰
- 여러 개의 테이블을 조인한여 정의한 뷰는 변경 불가한 경우가 많다.
- 변경 불가능한 뷰의 특징
- 변경 가능한 뷰 VS 변경 불가능한 뷰
뷰의 장점
- 질의문 좀 더 쉽게 작성 가능
- GROUP BY, 집계함수, 조인 등을 이용해 미리 뷰를 만들어 놓으면, 복잡한 SQL 문 대신 SELECT 절, FROM절만으로 원하는 데이터 검색 가능
- 데이터 보안유지 도움 : 자신에게 제공된 뷰를 통해서만 데이터 접근하도록 권한 설정가능
- 데이터 좀 더 편리하게 관리
- 제공된 뷰와 관련없는 다른 내용에 대해 사용자가 신경쓸 필요 X
뷰 삭제 : DROP VIEW 문
뷰를 삭제해도 기본테이블은 영향 X
만약 삭제할 뷰를 참조하는 제약조건 존재?
- 뷰 삭제 수행 X
- 관련 제약조건 먼저 삭제해야 함 ex) 삭제할 뷰를 이용해 만들어진 다른 뷰가 존재하는 경우
05. 삽입 SQL
- 삽입 SQL(ESQL; Embedded SQL)
- 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문
- 주요 특징
- 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
- 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙임
- 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 때는 이름 앞에 콜론(:) 붙여서 구분
- 커서(cursor)
- 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터
- 여러개의 행을 결과로 반환하는 SELECT문을 프로그램에서 사용할 때 필요
- 삽입 SQL문에서 사용할 변수 선언 방법
- BEGIN DECLARE SECTION과 END DECLARE SECTION 사이에 선언
커서가 필요없는 삽입 SQL
- CREATE TABLE 문, INSERT 문, DELETE문, UPDATE문
- 결과로 행 하나만 반환하는 SELECT
① : 삽입 SQL 문에서 사용할 변수 선언
• 테이블 내에 대응되는 속성과 같은 타입으로 변수의 데이터 타입을 선언
• C 프로그램에서는 문자열의 끝을 표시하는 널 문자(‘\0’)을 포함할 수 있도록 변수 선언 시 대응되는 속성의 문자열 길이 보다 한 개 더 길게 선언
② : 검색하고자 하는 제품의 제품번호를 사용자로부터 입력받는 부분
③ : 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품명과 단가를 검색하여 대응되는 각각의 변수에 저장하는 삽입 SQL 문
• 변수는 INTO 키워드 다음에 차례대로 나열
④ : 검색된 제품명과 단가를 화면에 출력
커서가 필요한 삽입 SQL
커서를 선언하는 삽입 SQL문
커서를 이동시키는 삽입 SQL문
커서의 사용을 종료하는 삽입 SQL문
SELECT 제품명, 단가, 제조업체
FROM 제품 P1
WHERE 단가 = (SELECT MAX(단가)
FROM 제품 P2
WHERE P1.제조업체= P2.제조업체);
-- 1. 메인에서 제품 테이블의 각 행을 처리하는데
-- 2. 서브는 메인의 제조업체를 참고하여 업체 별 최고 단가를 찾고
-- 3. 메인의 단가가 서브의 결과와 일치하는 행만 반환
-- 존재여부를 확인하는 경우
/* SELECT 고객이름
FROM 고객
WHERE EXISTS = (SELECT MAX(단가)
FROM 주문
WHERE 주문고객 = 고객아이디);*/
-- 1. 메인에서 제품 테이블의 각 행(고객 개인들)을 접근
-- 2. 서브쿼리에서 해당 고객의 주문내역이 존재하는지 확인
-- 3. EXISTS 연산자로 존재여부 평가
--내장함수(수학, 문자열, 시간/날짜, ...)
--수학
SELECT ABS(-78), ROUND(4.875, 1);
SELECT CEILING (4.1), FLOOR(4.1);
SELECT POWER(2,3), LOG(1000), SQRT(3);
SELECT
FROM 제품 INNER JOIN 주문 ON 제품번호=주문제품
--TOP n 질의
Select
-- 부속질의
--1. 중첩질의 : where절에서
--2. 스칼라 부속질의 : select절에서
--3. 인라인 뷰 : from절에서
-- 스칼라 부속질의 : 부속질의의 결과 ㄱ밧이 단일 값이 단일 행, 단일 열로 반환
-- select 문과 UPDATE 문에서 사용
select 주문고객,(select 고객이름
from 고객
where 고객아이디 = 주문고객), AS 이름, count(*) 총주문수
from 주문
group by 주문고객
--주문테이블에 있는 제품번호와 부속테이블에 있는 제품번호 매칭
alter table 주문 add 제품명 varchar(20);
update 주문
set 제품명 = (select 제품명
from 제품
where 주문제품 = 제품번호);
--인라인 뷰 : 테이블 대신 실제 테이블처럼 사용
-- 다중 행, 다중 열로 반환
select 등급, avg(적립금) as 평균적립금
from 고객
group by 등급
having avg(적립금) >= 1000;
select 등급,평균적립금
from (select 등급, avg(적립금) as 평균적립금
from 고객
group by 등급)등급평균
where 평균적립금 >= 1000;
select 고객이름, count(*)
from(select 고객아이디, 고객이름
from 고객
where 나이 >= 25) C, 주문 O
where C. 고객아이디 = O.주문고객
group by 고객이름
-- rank() over(order by 순위를 정하는 데 사용되는 열)
select 주문번호, 수량, rank() over(order by 수량 desc) NGROUP
from 주문
------
select 주문고객, 고객이름 ,sum(수량*단가) 주문금액
from (제품 inner join 주문 on 제품번호 = 주문제품)
inner join 고객 on 주문고객 = 고객아이디
group by 주문고객, 고객이름
------
select 주문고객, 고객이름, sum(수량*단가) 총구매액,
case
when (sum(수량*단가) >= 200000) then '최우수고객'
when (sum(수량*단가) >= 100000) then '우수고객'
when (sum(수량*단가) >= 80000) then '일반고객'
else '고객'
end 고객등급
from (제품 inner join 주문 on 제품번호 = 주문제품)
inner join 고객 on 주문고객 = 고객아이디
group by 주문고객, 고객이름