DB SQL(4)

2022-08-25

SQL의 주요 특징, 스키마와 카탈로그 개념, DDL(CREATE, ALTER, DROP), DML(SELECT, UPDATE, INSERT, DELETE) 명령어, 다양한 조인 방식, 부속 질의문, 그리고 뷰의 생성 및 조작에 대해 설명합니다.

SQL - Structured Query Language

SQL의 특징

관계 대수 + 확장된 투플 관계 해석 기초
고급(대화식), 비 절차적 데이터 언어 (비절차 언어 : what)
- 절차는 how까지 있어서 더 복잡하기 때문
SQL이 표준화가 됨
터미널 통해 대화식 질의어로 사용 – 대화형 모드(dynamic)
응용 프로그램에 삽입된 형태로도 사용 가능 – 임베디드 모드(static)
- 두가지 모드를 이중 모드라 칭한다.
개개의 레코드 단위 처리 보다는 레코드 집합 단위로 처리
일반 용어 사용(테이블, 행, 열)
- 릴레이션, 투플, 애트리뷰트 가 아닌 일반 용어 사용이란 의미
선언적 언어(데이터 처리를 위한 접근경로 명세가 불필요)

스키마 : 하나의 응용(사용자)에 속하는 테이블과 기타 구성요소 등의 그룹

MySQL은 CREATE SCHEMA = CREATE DATABASE 동의어
스키마는 DBA로부터 허가받은 자만 소유
- 권한 예시 : create schema university authorization SHLEE;

카탈로그 : SQL시스템 내에서 한 스키마 집합

하나의 특별한 스키마 포함(Information_schema)

도메인, 테이블(DDL, DML)

도메인 정의문(CREATE, ALTER, DROP) - DDL
테이블 생성(기본(TABLE), 가상(VIEW), 임시 테이블)
- 기본 TABLE : DMBS 파일
- 가상 VEIW : create, drop만 제공(alter은 X)
  - 어떤 기본 테이블로부터 유도되어 만들어지는 테이블(독자적 존재 불가)
- 임시 테이블 : DDL로 만들어지는게 아님
  - 질의문 처리 과정의 중간 결과로 만들어지는 테이블
SQL 데이터 조작문(검색, 갱신, 삽입, 삭제) - DML

CREATE - DOMAIN

CREATE DOMAIN DEPT CHAR(4)  
	DEFAULT '???’ 
	CONSTRAINT VALID-DEPT CHECK( VALUE IN 
	('COMP', 'ME', 'EE', 'ARCH', '???'));

CREATE - VIEW

create view deptsize(DEPT, SIZE)
	AS SELECT DEPT, COUNT(*)
		FROM STUDENT
		GROUP BY DEPT;
		[WITH CHECK OPTION;]

CREATE - TABLE

외래키 등록하는것 잘 확인
CHECK() 는 제약조건을 의미

CREATE TABLE ENROL(
    Sno INTEGER NOT NULL,
    Cno CHAR(6) NOT NULL,
    Grade INTEGER,
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES STUDENT(sno)
        ON DELETE CASCADE
        ON UPDATE CASCADE, 
    FOREIGN KEY(Cno) REFERENCES COURSE
        ON DELETE CASCADE 
        ON UPDATE CASCADE, 
    CHECK(Grade ≥ 0 AND Grade ≤100)
);

ALTER - DOMAIN

alter domain (도메인)이름 <내용>

ALTER - TABLE

alter table (테이블)이름 [add 열(이름) 타입 ...]
						drop 열(이름) 타입 ...
						alter 열(이름) 타입 ...

DROP - DOMAIN

drop의 대표적 옵션
- RESTRICT : 다른 곳에서 이 도메인을 참조하고 있지 않을 때 삭제
- CASCADE : 이 도메인을 참조하고 있는 뷰나 제약조건도 삭제

drop domain (도메인)이름 ...

DROP - TABLE

drop table (테이블)이름 ...

DROP - VIEW

drop view (뷰)이름 ...

SELECT

참고 옵션
- ALL : 전체(기본값)
- DISTINCT : 중복 불가
- Having : 그룹에 조건 주는것

Select [ALL | DISTINCT] 열_리스트
From 테이블_리스트
[Where 조건]
[Group by 열_리스트
[Having 조건]]
[Order by 열_리스트 [ASC|DESC]];

UPDATE

Update 테이블 Set { 열_이름 = 산술식 }+
[Where 조건]

INSERT

Select문 : 부속 질의문(여러 투플 동시 삽입)

Insert into 테이블([열_리스트])
Select 문;

직접 값 하나 삽입

Insert into 테이블([열_리스트])
VALUES (열 값_리스트)

DELETE

Delete 
From 테이블
[Where 조건]

다양한 속성들

INNER JOIN

동일 조인 : ‘=’
자연 조인 : 동일 조인 + 중복 제거
STUDENT JOIN ENROL ON ( )
STUDENT JOIN ENROL USING( )
STUDENT NATURAL JOIN ENROL
- 3개 전부 동일한 동작
- 조인이 합치는걸 의미

OUTER JOIN

EMPLOYEE 테이블은 id가 1~6까지, PROJECT 테이블은 id가 1~4까지 존재 가정
SELECT ... FROM EMPLOYEE LEFT JOIN PROJECT ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
- id 1~6까지 출력
SELECT ... FROM EMPLOYEE RIGHT JOIN PROJECT ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
- id 1~4까지 출력
FULL JOIN PROJECT ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
- id 1~6까지 출력

부속 질의문

부속질의문 형태는 보통 select문 일 것이다.
Insert into 테이블([열_리스트]) Select 문;
[NOT] IN(부속질의문)
- ’=’ 과 동일
ALL(부속질의문)
EXITS(부속질의문)
- 만약 select문과 사용시 참 때 실행
UNION+부속질의문(중복은 제거함)
- 참고 : 합집합(UNION)

기타

Sno AS 학번
- 이름 변경
LIKE % 축구 %
- 포함을 의미
Is [NOT] NULL
- 누락된 정보
- 값은 있지만 모르는 값
- 해당되지 않는 값
- 의도적으로 유보한 값

다양한 DML 예시

검색 결과에 레코드의 중복 제거

SELECT DISTINCT Dept FROM STUDENT;

테이블의 열 전부를 검색하는 경우

SELECT * FROM STUDENT;

조건 검색

SELECT Sno,Sname FROM STUDENT
WHERE Dept = '컴퓨터' AND Year = 4;

순서를 명세하는 검색

SELECT Sno, Cno FROM ENROL
WHERE Midterm ≥ 90 ORDER BY Sno DESC, Cno ASC;

산술식과 문자 스트링이 명세된 검색(이름 변경)

SELECT Sno AS 학번, '중간시험 = ' AS 시험, Midterm + 3 AS 점수 FROM ENROL
WHERE Cno = 'C312';

복수 테이블로부터의 검색(조인)

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

자기 자신의 테이블에 조인하는 검색
같은 학과 학생의 학번을 쌍으로 검색하라. 단 첫 번째 학번은 두 번째 학번 보다 작다.

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

FROM 절에 조인 명세

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

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

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

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

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

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

SELECT COUNT(DISTINCT Cno) FROM ENROL
WHERE Sno = 300;

SELECT AVG(Midterm) AS 중간평균 FROM ENROL
WHERE Cno = ‘C413’;

GROUP BY를 이용한 검색

SELECT Cno, AVG(Final) AS 기말평균 FROM ENROL
GROUP BY Cno;

HAVING을 사용한 검색

SELECT Cno, AVG(Final) AS 평균 FROM ENROL
GROUP BY Cno HAVING COUNT(*) ≥ 3;

부속 질의문(subquery)을 사용한 검색

형태 : SELECT-FROM-WHERE-GROUP BY-HAVING

SELECT Sname FROM STUDENT
WHERE Sno IN(SELECT Sno FROM ENROL 
			WHERE Cno = 'C413');
			
SELECT Sname FROM STUDENT
WHERE Sno NOT IN(SELECT Sno FROM ENROL 
				WHERE Cno = ‘C413’);

SELECT Sname, Dept FROM STUDENT 
WHERE Dept =(SELECT Dept FROM STUDENT 
			WHERE Sname = ‘정기태’);

학번이 500인 학생의 모든 기말 성적보다 좋은 학기말 성적을 받은 학생의 학번과 과목번호를 검색하라.

SELECT Sno, Cno FROM ENROL
WHERE Final > ALL(SELECT Final FROM ENROL 
				WHERE Sno = 500);

LIKE를 사용하는 검색

SELECT Cno, Cname FROM COURSE 
WHERE Cno LIKE 'C%';

NULL을 사용한 검색 (cont’d)

“열_이름 = NULL”의 형식은 불법

SELECT Sno, Sname FROM STUDENT 
WHERE Dept IS NULL;

EXISTS를 사용하는 검색

과목 ‘C413’에 등록한 학생의 이름을 검색하라.

SELECT Sname FROM STUDENT 
WHERE EXISTS(SELECT * FROM ENROL
			WHERE Sno = STUDENT.Sno AND Cno = 'C413');

과목 ‘C413’에 등록하지 않은 학생의 이름을 검색하라.

SELECT Sname FROM STUDENT
WHERE NOT EXISTS(SELECT * FROM ENROL
				WHERE Sno = STUDENT.Sno AND Cno = 'C413');

UNION이 관련된 검색

SELECT Sno FROM STUDENT 
WHERE Year = 1
UNION
SELECT Sno FROM ENROL 
WHERE Cno = 'C324';

부속 질의문을 이용한 변경

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 Year = 4;

부속 질의문을 이용한 레코드 삽입

INSERT INTO COMPUTER(Sno, Sname, Year) 
	SELECT Sno, Sname, Year FROM STUDENT 
	WHERE Dept = '컴퓨터';

부속 질의문을 사용한 삭제

DELETE FROM ENROL
WHERE Cno = 'C413' AND Final < 60 AND ENROL.Sno IN
(SELECT Sno FROM STUDENT WHERE Dept = '컴퓨터');

뷰의 생성 예시

CREATE VIEW CSTUDENT(Sno, Sname, Year) AS SELECT   Sno, Sname, Year
FROM STUDENT WHERE  Dept = '컴퓨터’ WITH CHECK OPTION;

AS SELECT : 열의 이름 상속 상속이 불가능한 경우나 열 이름이 중복될 경우 반드시 열 이름 명세

CREATE VIEW DEPTSIZE(Dept, Size) AS  SELECT Dept, COUNT(*)
FROM    STUDENT   GROUP  BY Dept;

두 개 이상 테이블 조인

CREATE  VIEW HONOR(Sname, Dept, Grade)
AS  SELECT STUDENT.Sname,
STUDENT.Dept, ENROL.Final
FROM   STUDENT, ENROL
WHERE   STUDENT.Sno = ENROL.Sno
AND ENROL.Final > 90;

정의된 뷰를 이용하여 또 다른 뷰를 정의

CREATE VIEW COMHONOR
AS  SELECT   Sname
FROM     HONOR WHERE   Dept = '컴퓨터';

뷰의 제거 예시

DROP VIEW 뷰_이름 { RESTRICT | CASCADE };

RESTRICT : 다른 곳에서 참조되고 있지 않는 한 데이터베이스에서 제거
CASCADE : 이 뷰가 사용된 다른 모든 뷰나 제약 조건이 함께 제거

DROP VIEW DEPTSIZE RESTRICT;

뷰의 조작 예시

기본 테이블에 사용 가능한 검색(SELECT)문은 모두 뷰에 사용 가능

변경(삽입, 삭제, 갱신) 연산은 제약

열 부분 집합 뷰(column subset view)

CREATE VIEW STUDENT_VIEW1 AS SELECT Sno, Dept FROM STUDENT;
→ 기본 키 포함(Sno) : 이론적으로 삽입, 삭제, 갱신 가능

CREATE VIEW STUDENT_VIEW2 AS SELECT Sname, Dept FROM STUDENT;
→ 기본 키 불포함 : 이론적으로 삽입, 삭제, 갱신 불가

행 부분 집합 뷰(row subset view)

CREATE VIEW STUDENT_VIEW3 AS SELECT Sno, Sname, Year, Dept FROM STUDENT   
WHERE Year=4;
→ 기본 키 포함(Sno) : 이론적으로 삽입, 삭제, 갱신 가능

기타

SQL의 이중 모드(dual mode) 원리 – 대화형 모드, 임베디드 모드

하나의 언어가 대화형으로 사용할 수도 있고, 호스트언어에 임베디드(=삽입)해서 사용될 수 있음.
명령문 앞에 EXEC SQL을 붙혀서 기존 코드(호스트 언어(C,Java등 주 언어))인지 SQL문인지 구분.
콜론(:)을 통해 기존 코드(호스트 언어)의 변수와 SQL문의 변수 구분.
물론 이러한 것들은 하나의 예시일뿐 모든 언어가 동일X(당장의 jsp에서도 sql문 사용방식 달랐음)

커서가 필요한 데이터 조작은 여러 데이터고,
커서가 필요 없는 데이터 조작은 단일 데이터이다.

Dynamic SQL과 Static SQL 구분(생략)

Twitter Facebook LinkedIn

DB SQL(4)

SQL - Structured Query Language

도메인, 테이블(DDL, DML)

다양한 속성들

다양한 DML 예시

뷰의 생성 예시

뷰의 제거 예시

뷰의 조작 예시

기타

공유하기

댓글남기기

참고

2022-06-22

DB 관계 데이터베이스, 대수, 해석(3)

Database

2022-08-25

DB SQL(4)

Database

2022-08-26

DB 데이터 종속성과 정규화(5)

Database

SQL - Structured Query Language

도메인, 테이블(DDL, DML)

다양한 속성들

다양한 DML 예시

뷰의 생성 예시

뷰의 제거 예시

뷰의 조작 예시

기타

공유하기

댓글남기기

참고

2022-06-22 DB 관계 데이터베이스, 대수, 해석(3) Database

2022-08-25 DB SQL(4) Database

2022-08-26 DB 데이터 종속성과 정규화(5) Database

2022-06-22

DB 관계 데이터베이스, 대수, 해석(3)

Database

2022-08-25

DB SQL(4)

Database

2022-08-26

DB 데이터 종속성과 정규화(5)

Database