데이터베이스 제 5,6강[KNU 2021-2]

👨‍💻🏫KNU 2021-2 SW & media 데이터베이스 필기노트 5,6

1. SQL의 분류🛢

• 데이터 정의어: 테이블을 생성하고 변경및 제어
• 데이터 조작어: 테이블에 새 데이터를 삽입하거나 테이블에 저장된 데이터 관리
• 데이터 제어어: 보안을 위해 접근권한을 부여하며 관리할수있는 기능제공

2. 데이터 정의어

2.1. CREATE TABLE

CREATE TABLE( name_tbl type constraint);

구성: 속성_이름 데이터_타입 [기본 제약 사항 정의]

제약 사항 정의

• 기본키: PRIMARY KEY(atter)
• 대체키: UNIQUE(attr)
• 외래키: FOREGIN KEY(attr) REFERENCES name_tbl(attr) ON DELETE option ON UPDATE option

option	설명
NO ACTION	참고되는 투플의 삭제또는 변경을 금지
CASCADE	관련 투플을 함께 삭제 변경함
SET NULL	관련 투플의 외래키값을 NULL로 번경
SET DEFAULT	관련 투플의 외래키값을 미리 지정한 기본 값으로 변경

• 무결성을 위힌 제약사항정의: CONSTRAINT constraint_name CHECK(query)

#ex
CREATE TABLE 환자TBL(
환자번호 CHAR(4) NOT NULL,
환자전화번호 CHAR(11),
환자이름 VARCHAR(10),
나이 INT,
담당의사 CHAR(4),
PRIMARY KEY(환자번호),
UNIQUE (환자전화번호),
CHECK (나이>=1 AND 나이<=120),
FOREIGN KEY(담당의사) REFERENCES 의사TBL(의사번호) ON DELETE SET NULL 
);

2.2. ALTER TABLE

> ALTER TABLE name_tbl do;

구성: 테이블이름 명령어

#속성 추가
ALTER TABLE name_tbl ADD COLUMN col_name type ...;
#제약조건 추가
ALTER TABLE name_tbl ADD CONSTRAINT constraint_name(query);
#속성 제거. 속성 제거는 제약조건 제거가 선행되어야 한다. 
ALTER TABLE name_tbl DROP COLUMN col_name;
#제약조건 제거
ALTER TABLE name_tbl DROP CONSTRAINT constraint_name;

2.3. DROP TABLE

> DROP TABLE name_tbl;

구성: 테이블이름

테이블 제거는 외래키 제약조건이있다면 제약조건을 우선 삭제해야한다.

3. 데이터 조작어

3.1. SELECT

SELECT는 분류자에따라 DQL로 별도 분류되는 경우도 있다. 전공책에서는 DML

SELECT options column_list FROM table_list;

구성: 출력설정 속성리스트 테이블리스트
속성리스트에는 * 와일드카드사용 가능

#출력명 변경name_tbl2 -> nt2
SELECT name_tbl1,name_tbl2 AS nt2 FROM table_list;

#조건검색. WHERE뒤에 비교연산자와 논리연산자를 사용해 검색조건제시
SELECT options column_list FROM table_list WHERE(options);

# WHERE뒤에 조건에는 LIKE를 사용할수있으며 이는 와일드카드처럼 사용 가능하다 %==*, _==? 이므로 
#name_tbl1 LIK E'김__' == name_tbl1 LIKE '김%'이다.
SELECT options column_list FROM table_list WHERE(column_list LIKE options);
# 특정 속성 값이 NULL인지확인하기위해 name_tbl1 IS NULL를 사용
SELECT options column_list FROM table_list WHERE(column_list IS NULL);

#정렬 검색. ORDER BY를 사용해 테이블 내용을 사용자가 원하는 순으로 출력한다.
# ASC(정순) DESC(역순)
SELECT options column_list FROM table_list WHERE(ORDER BY column_list ASC);

#집계 함수.
##COUNT(column)는 속성값의 개수를 출력한다.
##MAX(column)는 속성값의 최댓값을 출력한다.
##MIN(column)는 속성값의 최솟값을 출력한다.
##SUM(int_type_column)는 속성값의 합계를 출력한다.
##AVG(int_type_column)는 속성값의 평균을 출력한다.
#FUNC는 함수명
select FUNC(column) from table_;

#그룹 검색. 속성값이 같은 투플을 모아 그룹화한다. HAVING을 사용해 그룹에대한 조건을  작성한다. 그룹환경우 WHERE은 사용불가하며 HAVING에 작성되어야한다.

SELECT options column_list 
FROM table_list 
GROUP BY column_list HAVING options;

# 집계함수나 그룹화된경우 그 절에 명시된 속성외에는 SELECT에서 쓸수없다.


#조인 검색. 특정 속성을 활용해 두 테이블의 연관성을 나타낼수있다.
SELECT Atbl.selectColumn #이름이 같은 속성이 없다면 테이블명은 생략해도 된다.
FROM Atbl, Btbl 
WHERE (options) AND Atbl.column=Btbl.column 

SELECT의 부속질의문

SELECT문 안에 또 다른 SELECT를 포함하는 쿼리이다. 포함하는 문은 상위질의문, 포함되는 문은 부속질의문이다.

연산자	설명
(NOT) IN	서브 쿼리 결과중 일치하는 항목이있다면 참(거짓)
(NOT) EXISTS	서브 쿼리 결과가 하나라도 있다면 참(거짓)
ALL	서브 쿼리 결과가 모든 값과 비교해서 참이면 만족->비교연산자와 사용
ANY,SOME	서브 쿼리 결과가 최소 하나의 값과 비교해서 참이면 만족->비교연산자와 사용

IN

#IN
SELECT options column_list FROM table_list WHERE IN(SELECT options column_list FROM table_list WHERE options);

#예: 수입목록에서 반스브랜드의 상품을 출력
SELECT 상품 FROM 제품 WHERE IN(SELECT 브랜드 FROM 수입목록 WHERE 제조업체='반스');

ALL

#ALL
SELECT options column_list FROM table_list WHERE (options)ALL(SELECT options column_list FROM table_list WHERE options);

#예: 나이키의 모든 상품 보다 비싼 제품 출력
SELECT 상품 FROM 제품 WHERE 가격>ALL(SELECT 가격 FROM 제품 WHERE 제조업체='나이키');

3.3. INSERT

테이블에 새 데이터를 추가한다. 모든 칼럼에 값을 넣을때는 칼럼리스트를 생략해도되며 칼럼리스트를 사용할때 칼럼에 값이 없다면 NULL로 처리된다.(NULL이 허용되는 칼럼에 한함)

INSERT INTO table(column_list) VALUES (column_values__list);

다른 테이블의 선택된 데이터를 추가 가능하다. 단 이경우에도 칼럼의 도메인은 일치해야한다

INSERT INTO a_tbl(a,b,c) SELECT a,b,c FORM b_tbl WHERE options=f

3.4. UPDATE

테이블에 저장된 투플에서 특정 속성의 값을 수정한다. 조건이 없다면 WHERE절은 생략되어도 되며 WHERE절은 서브쿼리를 허용한다.

UPDATE a_tbl SET a='vals' b=b*vals.... WHERE options...