SQL

SQL(Structured Query Language)

구조화 질의어

종합 데이타베이스 언어 역할

단순히 검색만을 위한 데이타 질의어가 아님

기능

데이타 정의어(DDL)
데이타 조작어(DML)
데이타 제어어(DCL) 등의 기능 모두 제공

SQL 명령어 분류 체계

SQL의 특징

관계 대수에 기초

고급(high-level) 데이터 언어

비절차적(non-procedural) 데이타 언어

선언적 데이터 언어: SQL 명령문에는 데이타 처리를 위한 접근 경로(access path)에 대한 명세가 불필요

사용자 친화적인 인터페이스 제공

표준화된 언어

상용 RDBMS간의 전환이 용이
여러 관계 데이타베이스를 접근하는 데이타베이스 응용 프로그램의 작성을 지원

터미널을 통해 대화식 질의어로 사용 가능

응용 프로그램에 삽입된 형태로도 사용 가능

Java, Python, PHP, C, C++ 등과 같은 범용 프로그래밍 언어로 개발된 응용 프로그램에 삽입

데이터 처리 단위

개개의 투플 단위가 아닌 집합(테이블) 단위로 처리
관계 대수와 유사하게 입력이 테이블이고 출력도 테이블인 연산에 기초

SQL 데이터 정의문

데이터베이스 생성 및 삭제

한 기관이 관리하는 모든 데이터는 database라는 가장 큰 논리적 단위에 저장
a database == a set of tables
a table == a set of tuples
데이터베이스 생성문

CREATE DATABASE dbname

데이터베이스 삭제문

DROP DATABASE dbname

테이블의 생성

테이블의 종류

기본 테이블 (base table)

CREATE TABLE 문으로 만들어지는 테이블
DBMS의 화일로 물리적으로 생성되고 저장

가상 테이블 (virtual table)

CREATE VIEW 문으로 만들어지는 테이블
어떤 기본 테이블로부터 유도되어 만들어지는 테이블
물리적으로 존재하지 않음

일반 형식


CREATE TABLE 테이블이름
				({열이름 데이타타입 [NOT NULL] [DEFAULT 값],}+
				[PRIMARY KEY (열이름_리스트),]
				{[UNIQUE (열이름_리스트),]}*
				{[FOREIGN KEY(열이름_리스트)
					REFERENCES 기본테이블[(열이름_리스트)]
					[ON DELETE 옵션]
					[ON UPDATE 옵션] ,]} *
				[CONSTRAINT 이름] [CHECK(조건식)]);

PRIMARY KEY

기본 키와 개체 무결성 제약 조건을 명세

UNIQUE

대체 키를 명세, 후보 키

NOT NULL

애트리뷰트 값 제약조건

FOREIGN KEY

외래 키와 참조 무결성 제약조건을 명세
SQL에서는 참조된 테이블의 후보 키도 외래 키로 될 수 있음

옵션에는 SET NULL, SET DEFAULT, CASCADE가 있음

ON DELETE SET NULL

참조 투플(열 값)이 삭제되면 NULL로 설정

ON UPDATE CASCADE

참조 투플(열 값)이 갱신되면 갱신된 값이 파급적으로 갱신됨

CHECK

제약조건을 명세

데이터 타입

숫자

INT, INTEGER, SMALLINT, BIGINT : 정수
FLOAT(n), REAL, DOUBLE: 부동소숫점 숫자
DECIMAL(i, j), NUMERIC(i, j) : 고정소숫점 숫자

문자스트링

CHAR(n) : 고정 길이 문자
VARCHAR(n) : 가변 길이 문자

날짜

DATE : YY-MM-DD 예) ‘2024-04-01’
DATETIME 예) ‘2024-04-19 19:32:50’

시간

TIME : hh:mm:ss 예) ’14:30:00’
TIMESTAMP 예) '2024-04-04 15:45:30'
INTERVAL: day, hour, minute, second 예) ‘1 day’

Example 1


CREATE TABLE STUDENT (
				SNO integer primary key,
				SNAME varchar(10) not null,
				YEAR int not null default 1,
				DEPT varchar(5),
				ENTER_DATE datetime default now() on update now(),
				constraint YEAR_CONST check(year >= 1 and year <= 4)
)

Example 2


CREATE TABLE ENROL (
				SNO integer,
				CNO char(4),
				GRADE char(1),
				MIDTERM integer,
				FINAL integer,
				ENTER_DATE datetime default now() ON UPDATE now(),
				
				PRIMARY KEY (SNO, CNO),
				FOREIGN KEY(SNO) references STUDENT(SNO)
					ON UPDATE CASCADE
					ON DELETE NULL,
				FOREIGN KEY(CNO) references COURSE(CNO)
					ON UPDATE CASCADE
					ON DELETE NULL,
				CHECK (GRADE >= 'A' AND GRADE <= 'F')
)

기본 테이블의 제거

DROP TABLE 기본_테이블_이름 { RESTRICT | CASCADE };

RESTRICT: 참조하는 뷰나 외래키 제약조건이 있으면 실행 실패

CASCADE: 참조하는 뷰나 외래키 제약조건도 모두 삭제

기본 테이블의 변경

기본 테이블의 애트리뷰트 변경

일반 형식

ALTER TABLE 기본_테이블_이름

([ADD 열_이름 데이타_타입] [DEFAULT 기정의 값] | [DROP 열_이름] [CASCADE|RESTRICT] | [MODIFY 열_이름 데이타_타입 [DEFAULT 값]);

SQL 검색문 - SELECT

SELECT 문: 관계형 데이터베이스에서 데이터를 검색하는 유일한 문장 형식

기본 형식: SELECT-FROM-WHERE

SELECT-FROM-WHERE 예시


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

SQL과 관계 데이터베이스 이론

SQL 기반 DBMS와 관계 데이터 모델 이론과의 차이점

이론적 관계데이터 모델에서는 한 릴레이션 안에서 중복된 투플을 허용하지 않음
실제 SQL기반 관계 데이터베이스 시스템에서의 테이블은 기본 키를 반드시 가져야 하는 것은 아님

기본키나 unique constraint가 명시되지 않은 테이블은 같은 원소의 중복을 허용하는 multiset으로 정의됨

SQL과 관계대수의 공통점

관계 대수와 마찬가지로 입력과 출력이 테이블이라는 점
폐쇄속성(closure property): 검색 결과가 또 다시 테이블이 됨
중첩 질의문(nested query)을 구성할 수 있음

SQL과 관계대수의 차이점

관계대수는 결과에서 중복을 허용하지 않음
SQL은 결과에서 중복된 투플을 허용함

Boolean expression 및 *


SELECT * FROM student WHERE not (dept = '컴퓨터' or syear = 3)

*는 모든 애트리뷰트를 출력하고자 할 때 빈번하게 사용함.

WHERE절에는 and, or, not, () 등을 활용하여 Boolean expression의 조합을 표현할 수 있음.

질의문 해석1: 학생 테이블에서 (학과가 컴퓨터 또는 학년이 3학년) 이 아닌 학생들의 모든 애트리뷰트를 검색하라.

질의문 해석2: 학생 테이블에서 학과가 컴퓨터가 아니고, 학년도 3학년이 아닌 학생들의 모든 애트리뷰트를 검색하라.

ORDER BY


SELECT sno, sname FROM student WHERE syear = 3 order by sname desc

SQL검색결과는순서가정해지지않은형태로출력됨.왜?출력테이블도집합이기때문.출력순서 를 정해주기 위해서는 order by 절을 사용함.

기본 순서는 오름차순(ascending order). desc 는 내림차순(descending order)을 지정함.

문자열 검색


SELECT sno, sname FROM student WHERE sname = '정기태'


SELECT sno, sname FROM student WHERE sname like '정%'

완전일치 매치(Exact match): WHERE sname = ’정기태’

SQL 에서 부분문자열 매칭(substring matching) 검색이 가능함.

Regular expression 표현

%: 0개 이상의 임의의 문자열을 나타냄
_: 임의의 1개 문자를 나타냄

DISTINCT


SELECT distinct syear FROM student WHERE dept = '컴퓨터'

중복된 투플을 제거한 검색결과를 원할 때에는 SELECT절에 distinct 사용

NULL


SELECT sno, sname FROM student WHERE dept is NULL

NULL은애트리뷰트값이지정되어않을때사용되는특수한값

길이가 0인 문자열은 NULL 이 아님

애트리뷰트 값이 NULL 인지, 아닌지 체크하는 구문: is NULL, is not NULL


SELECT sno, sname FROM student WHERE dept <> '컴퓨터'

<> 는 not equal을 표시하는 구문임

어떤 투플의 dept 값이 NULL로 지정되어 있을 때, 이 투플은 이 검색문의 결과로 반환될까? No.

집계함수 (Aggregation Function)


SELECT COUNT(*) as e_count FROM ENROL;


SELECT COUNT(DISTINCT cno) FROM ENROL;


SELECT AVG(MidTerm), MAX(Mideterm) FROM ENROL WHERE cno = 'C413';

SELECT 절에 집계함수를 사용할 수 있음

집계함수: COUNT, AVG, MIN, MAX, SUM

집계함수가 SELECT 절에 나오면, 일반 애트리뷰트는 나올 수 없음. 동시에 여러개의 집계함수를 사용할 수는 있음.

집계함수가 SELECT 절에 나오면, 검색결과 테이블의 카디날리티는 1임.

GROUP BY


SELECT CNO, AVG(mideterm) AS m_avg
FROM ENROL
WHERE Final >= 80
GROUP BY CNO
HAVING count(*) >= 3
ORDER BY AVG(mideterm) desc

그룹 애트리뷰트로 입력 테이블을 그룹을 짓고, 각 그룹에 대하여 집계함수를 계산할 수 있음.

GROUP BY 절을 쓸 경우, SELECT절에는 집계함수 이외에, 그룹 애트리뷰트만 나올 수 있음.

HAVING은 각 그룹에 대하여 필터링 조건을 명세하는 구문. 집계함수를 사용한 boolean expression 을 표현하는 게 일반적임.

• 해석순서: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

부속질의문과 중첩질의문

SELECT 문의 결과가 테이블이기 때문에 검색문의 결과를 또다른 SQL의 입력으로 사용가능

부속질의문(sub-query): 다른 질의문에 포함되는 SQL 질의문

중첩질의문(nested query): 부속질의문을 하나 이상 포함하는 SQL 질의문

FROM 절에 부속질의문 포함


SELECT *
FROM (select * from student where dept = ‘컴퓨터’) cs
WHERE syear = 3

(해석) 컴퓨터과 학생 중 3학년인 학생들의 정보를 출력하라.

WHERE 절에 부속질의문 포함


SELECT sno
FROM enrol
WHERE final = (select max(final) from enrol where cno = ’C413’)

SELECT 절에 부속질의문 포함


SELECT sno, (select count(cno) from enrol e where s.sno = e.sno) as course_count
FROM Student s
WHERE syear = 3

(해석) 3학년 학생들의 학번과 해당 학생이 신청한 과목의 수를 출력하라.

IN을 이용한 중첩질의문

WHERE 절에 IN을 통해서 조건을 표시


SELECT sname 
FROM STUDENT
WHERE sno IN (SELECT sno FROM ENROL WHERE cno = 'C413’);

(해석1) C413 과목을 수강하는 학생의 학번에 포함된 학생의 이름을 검색하라.

(해석2) C413 과목을 수강하는 학생의 이름을 검색하라.

WHERE 절에 IN을 포함하는 중첩질의문은 조인질의문으로 변환이 가능함.


SELECT sname
FROM STUDENT s, ENROL e
WHERE s.sno = e.sno and e.cno = 'C413’;

NOT IN을 이용한 중첩질의문

WHERE 절에 NOT IN을 통해서 조건을 표시


SELECT sname
FROM STUDENT
WHERE sno NOT IN (SELECT sno FROM ENROL WHERE cno = 'C413’);

(해석1) C413 과목을 수강하는 학생의 학번에 포함되지 않는 학생의 이름을 검색하라.

(해석2) C413 과목을 수강하지 않는 학생의 이름을 검색하라.

WHERE 절에 NOT IN을 포함하는 중첩질의문은 조인질의문으로 변환이 어려움


SELECT sname
FROM STUDENT s, ENROL e
WHERE s.sno = e.sno and e.cno <> 'C413’;

(해석) C413이 아닌 과목들을 수강한 학생의 이름을 검색하라.

EXISTS을 이용한 중첩질의문

WHERE 절에 EXISTS를 통해서 조건을 표시


SELECT sname
FROM STUDENT s 
WHERE EXISTS (SELECT *
							FROM ENROL e
							WHERE e.sno = s.sno and e.cno = 'C413’);

EXISTS( subquery ): 자체가 Boolean expression이고 subquery의 결과가 투플이 존재하면 TRUE를, 투플이 없으면 FALSE를 반환함.

(해석1) C413 과목을 수강하는 ENROL 테이블 투플이 있는 학생의 이름을 검색하라.

(해석2) C413 과목을 수강하는 학생의 이름을 검색하라.

조인질의문으로 변환이 가능함

NOT EXISTS을 이용한 중첩질의문

WHERE 절에 NOT EXISTS을 이용해서 조건을 표시


SELECT sname
FROM STUDENT s
WHERE NOT EXISTS (SELECT *
									FROM ENROL e
									e.sno = s.sno and e.cno = 'C413’);

(해석1) C413 과목을 수강하는 ENROL 테이블 투플이 없는 학생의 이름을 검색하라.

(해석2) C413 과목을 수강하지 않는 학생의 이름을 검색하라.

조인질의문으로 변환이 어려움.

UNION, INTERSECT를 이용한 중첩질의문

두개 이상의 SQL 검색문을 UNION, INTERSECT로 묶을 수 있음.


( SELECT Sno
	FROM STUDENT
	WHERE YEAR = 1)
		UNION
( SELECT Sno
	FROM ENROL
	WHERE Cno = 'C324');

검색문의 결과들이 UNION-compatible 해야 함.

UNION: 합집합 테이블. 중복 투플은 제거됨 UNION ALL: 중복을 제거하지 않는 합집합 테이블 INTERSECT: 교집합 테이블

차집합은 NOT IN을 사용할 것.

조인 검색: 복수의 테이블에 대한 검색


SELECT S.Sname, S.Dept, E.Grade
FROM STUDENT S, ENROL E;


SELECT S.Sname, S.Dept, E.Grade
FROM STUDENT S, ENROL E
WHERE S.Sno = E.Sno;


SELECT S.Sname, S.Dept, E.Grade
FROM STUDENT S, ENROL E
WHERE S.Sno = E.SNO AND E.CNO = 'C413';

이론적으로, FROM 절에 두개 이상의 테이블이 명세될 때에는, 테이블들의 곱집합의 결과를 검색대상의 테이블로 설정하고 검색문을 수행

WHERE 절에 조인 조건이 나오면 비로소 세타조인이 됨. 예에서는 동일조인.

조인 조건은 다른 검색조건과 조합 가능.

조건이 없는 복수테이블 검색

조인 검색

조인 검색


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

셀프조인: FROM 절에 동일한 테이블 2개가 명세됨.

서로 다른 테이블로 간주.

(해석) 같은 과 학생들의 학번 쌍을 중복없이 출력하라.

2 가지 조인 표기법


SELECT S.Sname, S.Dept, E.Grade
FROM STUDENT S, ENROL E
WHERE S.Sno = E.Sno AND E.Cno = 'C413'


SELECT Sname, Dept, Grade
FROM STUDENT JOIN ENROL ON STUDENT.Sno=ENROL.Sno
WHERE ENROL.Cno = 'C413';

조인과 Group by 결합

컴퓨터과 학생들이 수강하는 각 과목별, 과목번호, 학생 수를 검색하라.


SELECT e.cno, count(*) as student_count
FROM student s, enrol e
WHERE s.sno = e.sno
GROUP BY e.cno

LEFT / RIGHT / FULL (OUTER) JOIN


SELECT Sname, Dept, Grade
FROM STUDENT LEFT OUTER JOIN ENROL
ON STUDENT.Sno = ENROL.Sno

일반적인 (inner) join은 조인조건을 만족하는 양쪽 투플의 조합을 새로운 투플로 반환.

Left outer join은 기존 조인되는 투플 쌍 이외에, 왼쪽 테이블에 있는 조인 안되는 투플들도 결과 테이블에 나옴. 이 경우 오른쪽 테이블의 애트리뷰트 영역은 NULL로 채워져 나옴.

Right outer join은 기존 조인되는 투플 쌍 이외에, 오른쪽 테이블에 있는 조인안되는 투플들도 결과 테이블에 나옴.

Full outer join 은 조인안되는 양쪽 투플이 모두 나옴.

INSERT INTO 문: 투플의 삽입

일반 형식


INSERT INTO 테이블(열이름_리스트) VALUES 열값_리스트;


INSERT
INTO STUDENT(sno, sname, syear, dept)
VALUES (1000, ‘박정식', 1, '컴퓨터');


INSERT
INTO STUDENT
VALUES (1000, ' 박정식 ', 1, '컴퓨터');


INSERT
INTO STUDENT(Sno, Sname, Year)
VALUES (1000, ' 박정식 ', 1);

검색결과를 저장할 때

이미 만들어진 테이블에 투플을 삽입할 때


INSERT INTO student3(sno, sname, dept)
SELECT sno, sname, dept
FROM student
WHERE syear =3

새로운 테이블을 생성함과 동시에 저장할 때


CREATE TABLE student3
SELECT sno, sname, dept
FROM student
WHERE syear =3

UPDATE 문: 투플의 갱신

일반적인 형식


UPDATE 테이블
SET 열이름=산술식 [, 열이름=산술식]*
[WHERE 조건식];

하나의 레코드 변경


UPDATE STUDENT
SET syear = 2
WHERE sno = 300;

복수의 레코드 변경


UPDATE COURSE
SET credit = credit + 1
WHERE dept = '컴퓨터';

부속 질의문을 이용한 변경


UPDATE ENROL
SET final = final + 5
WHERE Sno IN
				( SELECT sno
					FROM STUDENT
					WHERE dept = '컴퓨터');


UPDATE STUDENT
SET dept = (SELECT dept
						FROM COURSE
						WHERE cname = ‘데이타베이스’)
WHERE syear = 4;

JOIN UPDATE 문

DB 과목에 대하여 컴퓨터과 학생들의 기말성적을 5점씩 올려라


UPDATE enrol e INNER JOIN student s ON e.sno = s.sno
SET final = final + 5
WHERE e.cno = ‘DB’ AND s.dept = ‘컴퓨터’

DELETE FROM 문: 투플의 삭제

일반 형식


DELETE
FROM 테이블
[WHERE 조건];

레코드 삭제


DELETE
FROM STUDENT
WHERE sno = 100;

주의사항

참조 무결성 고려할 것

조건이 없는 경우 테이블의 모든 투플 삭제


DELETE
FROM STUDENT

참고: TRUNCATE TABLE 테이블

부속질의문을 사용한 삭제


DELETE 
FROM ENROL e
WHERE e.cno = 'C413' AND e.final < 60
			AND e.sno IN
								(SELECT sno
								FROM STUDENT
								WHERE dept = '컴퓨터');

JOIN DELETE 문

홍길동 학생의 DB 수강 기록을 삭제하라.


DELETE e
FROM enrol e INNER JOIN student s
		ON e.sno = s.sno
WHERE e.cno = 'db' and s.sname = '홍길동'