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

복습하기 위해 학부 수업 내용을 필기한 내용입니다.
이해를 제대로 하지 못하고 정리한 경우 틀린 내용이 있을 수 있습니다.
그러한 부분에 대해서는 알려주시면 정말 감사하겠습니다.

▶SQL 개요

SQL은 관계형 데이터베이스의 표준 언어로써 모든 주요 제품에서 채택되고 있다.

 

SQL의 특징

데이터 정의어, 데이터 조작어, 데이터 제어어 등 모든 기능 제공한다.

대화형 질의어와 Java, C 등 고급 프로그래밍 언어 내에 SQL이 삽입된 형태로 사용이 가능하다.

 

▶SQL 데이터 정의어

테이블을 생성하고 정의하거나 새로운 애트리뷰트를 추가/제거할 수 있다.

또한 뷰 생성/제거, 인덱스 생성/제거를 할 수 있다.

CREATE TABLE 수강(
	학번 INTEGER NOT NULL,
	과목코드 CHAR(5) NOT NULL,
	점수 INTEGER,
	성적 CHAR(2),
	PRIMARY KEY(학번, 과목코드),
	FOREIGN KEY(학번) REFERENCES 학생(학번)
		ON DELETE CASCADE 
		ON UPDATE CASCADE, 
	FOREIGN KEY(과목코드) REFERENCES 과목(과목코드)
		ON DELETE CASCADE 
		ON UPDATE CASCADE, 
	CHECK(성적 >= 0 AND 성적 <= 100));

학번, 과목 코드, 점수, 성적 4개의 애트리뷰트가 있다.

각각의 데이터 타입은 INTEGER, CHAR(5), INTEGER, CHAR(2)이다.

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

(학번, 과목 코드는 PRIMARY KEY라서 NULL이 될 수 없다.)

PRIMARY KEY는 기본키를 명세하는 것으로써 하나의 튜플을 유일하게 식별할 수 있도록 중복된 값이 존재할 수 없다.

FOREIGN KEY는 외래 키로서 참조 무결성을 유지하기 위한 것이다.

참조하고 있는 행이 삭제(ON DELETE)되거나 변경(ON UPDATE)될 때 취해야 할 동작을 명시한다.

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

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

728x90

▶SQL 데이터 조작어

데이터 검색

SQL 검색 문의 기본적인 구조

    select 열_리스트 from 테이블_리스트 where 조건;

    ex. select 학번, 성명 from 학생 where 학과='컴퓨터';

명확성을 위해 소속 테이블의 이름과 열 이름이 구두점으로 연결된 형태를 취하는 것이 원칙이다.

    select 학생.학번, 학생.성명 from 학생 where 학생.학과 = '컴퓨터';

 

SQL과 이론적 관계형 데이터 모델과의 차이점

SQL은 질의 결과에서 똑같은 튜플들을 자동으로 제거하지 않는다. (정렬 연산은 시간이 길 수 있으므로)

select 결과 테이블은 이론상의 튜플 집합은 아니다.

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

따라서 사용자가 select 문에 DISTINCT를 명시적으로 작성할 때만 튜플의 중복성을 제거한다. (default는 all)

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

 

☞ 테이블 열 전부를 검색

    select * from 학생;

 

☞ DISTINCT를 이용한 검색

    학생 테이블에 어떤 학과들이 있는지 검색.

    select distinct 학과 from 학생;

 

☞ 조건 검색

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

SELECT 과목코드, 과목명
	FROM 과목
	WHERE 학점 = 3 AND 학년 = 4;

 

☞ LIKE를 이용하는 검색

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

SELECT 과목코드, 과목명
	FROM 과목 WHERE 과목코드 LIKE 'CO%';

    '%'는 패턴에 일치하는 모든 문자, '_'는 패턴에 일치하는 하나의 문자

 

☞ NULL을 사용한 검색

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

SELECT 교수번호, 과목코드
	FROM 강의 WHERE 강의실 IS NULL;

 

☞ 범위 검색

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

SELECT *
	FROM 수강 WHERE 점수 BETWEEN 80 AND 90;

 

☞ ORDER BY를 이용한 검색

    ORDER BY를 이용해 오름차순(ASC)이나 내림차순(DESC)으로 결과 레코드를 정렬

SELECT 학번, 성적
	FROM 수강
	WHERE 성석 >= 80
	ORDER BY 학번 ASC, 성적 DESC;

첫 번째 속성 학번은 주 정렬이고, 두 번째 나오는 성적은 부 정렬 키이다.

 

☞ 계산된 열 검색(이름 변경)

간단한 계산식을 이용해 데이터를 가져오는 경우, AS를 사용해 이름을 설정할 수 있다.

SELECT 학번 AS DB수강학번, '최종점수 = ' AS 내용, 점수 + 2 AS 수정점수
	FROM 수강 WHERE 과목코드 = 'CO123';

DB수강학번	내용	수정점수
2018...	최종점수 =	20+2
2019...	최종점수 =	30+2

☞ 조인 검색

두 개 이상의 테이블로부터 데이터를 조인하는 복수 테이블 질의

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

    성명과 학과는 학생 테이블이고, 성적은 수강 테이블이다.

SELECT 성명, 학과, 성적
	FROM 학생, 수강
	WHERE 학생.학번 = 수강.학번 AND 과목코드 = 'CO423';

열 이름이 애매하게 되면 테이블 이름을 붙인 한정된 열 이름을 써야 한다.

    ex. 학생.학번, 수강.학번

자연 조인은 FROM절에 관련 테이블들을 여러 개 명세하게 된다.

WHERE 절에 어떤 조건으로 조인하느냐를 표현하는 학생.학번 = 수강.학번을 꼭 작성해야 한다.

이를 조인 조건 또는 조인 프레디킷(join predicate)이라 한다.

 

조인 질의에서 열 이름을 단순화하기 위해 FROM 절에 별칭을 사용할 수 있다.

이는 셀프 조인에서 명확한 열 참조를 가능하게 한다.

SELECT 성명, 학과, 성적
	FROM 학생 S, 수강 E
	WHERE S.학번 = E.학번 AND 과목콛, = 'CO423';

 

☞ 자기 자신의 테이블을 조인하는 검색

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

SELECT S1.학번, S2.학번
	FROM 학생 S1, 학생 S2
	WHERE S1.학과 = S2.학과
	ORDER BY S1.학번;

 

☞ 집계 함수를 이용한 검색

    학생수

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

    평균

SELECT AVG(점수) AS 평균 FROM 수강
	WHERE 과목코드 = 'CO123';

 

☞ GROUP BY을 사용한 검색

    과목별 점수의 평균을 구하시오.

SELECT 과목코드, AVG(점수) AS 평균
	FROM 수강 GROUP BY 과목코드;

 

☞ HAVING을 사용한 검색

    2명 이상 수강한 과목에 대한 점수의 평균을 구하시오.

SELECT 과목코드, AVG(점수) AS 평균
	FROM 수강 GROUP BY 과목코드
	HAVING COUNT(*) >= 2;