3. 두개 이상의 테이블을 이용한 SQL 질의
조인검색 : 여러개의 테이블을 연결하여 데이터를 검색하는 것
-연결하려는 테이블간 조인속성 이름은 달라도 되지만, 도메인은 같아야함.
-외래키를 조인 속성으로 이용함.
-FROM절에 검색에 필요한 테이블 나열
-WHERE절에 조인속성 값이 같아야 함을 의미하는 조인조건제시
*_예시)
SELECT
FROM Customer,Orders
WHERE Customer.custid=Orders.custid; _
Order by Customer.custid
외부조인
조인조건을 만족하지 않는 튜플에 대해서도 검색을 수행
LEFT / RIGHT / FULL OUTER JOIN 과 ON 키워드를 이용해 작성
예시)
도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하세요
SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders ON
Customer.custid=Orders.custid;
3-4 부속질의_ JOIN의 대안
1) SELECT문 안에 또 다른 SELECT문을 포함하는 질의 = 서브쿼리
부속질의문(서브질의문): 다른 SELECT문 안에 들어 있는 SELECT문
- 괄호로 묶어 작성 / ORDER BY절을 사용할 수 없음.
- 단일 행 부속질의문: 하나의 행을 결과로 반환
- 다중 행 부속질의문: 하나 이상의 행을 결과로 반환
부속질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문 수행
예시)도서를 구매한 적이 있는 고객의 이름을 알려면 어떻게 해야할까?
먼저 주문 테이블에서 주문내역 확인후-> 주문한 고객의 고객번호를 찾음
-> 찾은 고객번호를 이용하요 고객테이블에서 고객의 이름 찾기
부속 질의문과 상의 질문을 연결하는 연산자 필요
- 단일 행 부속질의문은 비교연산자 사용가능
- 다중 행 부속질의문은 비교연산자 사용불가!!
2) 상관(연결) 부속질의
- 부속질의 간에는 상하 관계가 있으며, 상의 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음
- 상위 쿼리와 하위 쿼리는 서로 의존적, 상위쿼리의 특정 행 값이 하위 쿼리 조건에 사용된다.
- 일반적인 부속질의와 달리 행단위로 반복 실행됨.
예시)질의: 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하세요
실행과정 : 아래 과정을 반복한다는 흐름을 이해해야함.
3) 집합연산- UNION, MINUS,INTERSECT
테이블간의 집합 연산을 이용하여 합집합, 차집합, 교집합을 구할 수 있음
합집합
IN, NOT IN
MYSQL에는 MINUS와INTERSECT 연산자가 없는 대신 NOT IN, IN 사용한다.
예시) 질의: 대한민국에 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 제외하고 나타내세요
EXISTS, NOT EXISTS
상관 부속질의문 형식으로 부속질의의 결과가 존재하는지 여부를 확인하는 연산자
- 부속질의 문이 한 행이라도 반환하면 True
- NOT EXISTS는 반환행이 하나라도 존재하지 않으면 True
4. 데이터 정의어 (DDL)
CREATE / ALTER / DROP
1) CREATE TABLE
- 속성과 속성에 관한 제약을 정의 : NULL / NOT NULL / DEFAULT
- 기본키 및 외래키를 정의하는 명령어
외래키 지정시 :
참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플 삭제시 처리방법을 지정하는 옵션
ON DELETE CASCADE:관련 튜플을 함께 삭제
ON DELETE SET NULL:관련 튜플의 외래키 값을 NULL로 변경함
ON DELETE RESTRICT : 부모행이 참조되고 있으면 삭제, 수정 불가 (기본값)
ON DELETE NO ACTION : SQL표준 키워드 RESTRICT와 동일
2) ALTER TABLE
생성된 테이블의 속성 변경 및 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경함.
예시)질의: 앞에서 생성한 NewBook테이블에 VARCHAR(31)의 자료형으로 isbn의 속성을 추가하세요
--> ALTER TABLE NewBook ADD isbn VARCHAR(31)
-->isbn자료형을 INTEGER로 변경: MODIFY isbn INTEGER
-->isbn 속성을 삭제: DROP COLUMN isbn
--> bookname속성에 NOT NULL제약조건 적용: ALTER TABEL NewBook MODIFY bookname VARCHAR(20) NOT NULL
--> NewBook테이블의 bookid속성을 기본키로 변경하세요 : ALTER TABEL NewBook ADD PRIMARY KEY(bookid)
3) DROP TABLE
테이블을 삭제하는 명령어, 테이블의 구조와 데이터를 모두 삭제함.
- 데이터만 삭제하려면 DELETE문 사용
- 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않음
- NewCustomer테이블을 삭제하기 위해서는 이 테이블을 참조하고 있는 NewOrders테이블부터 삭제해야함.
DROP TABEL 테이블 이름
무결성 제약조건
데이터베이스의 정확성과 일관성 유지하기 위한 규칙
유효하지 않은 데이터가 저장되는 것을 방지
무결성 3개원칙 ( 개체 / 참조 / 도메인 )
- 개체 무결성 : 기본키, 고유성, not null
테이블의 각 행을 고유하게 식별해야함.
기본키로 지정된 컬럼은 중복값과 null값 절대불가 - 참조 무결성: 외래키, 테이블간의 관계
외래키 값은 참조하는 테이블의 기본키 값과 반드시 일치해야함
참조할 수 없는 고립된 외래키 값은 허용 단됨 (단.null은 허용) - 도메인 무결성
속성의 값이 정의된 데이터 타입, 범위에 속해야함.
DATA TYPE, CHECK, DEFAULT, NOT NULL
5. 데이터 조작어 - 삽입, 삭제, 수정
INSERT, UPDATE, DELETE
1) INSERT
테이블에 새로운 튜플을 삽입하는 명령
예시)
INSERT INTO 테이블이름 [(속성리스트)]
VALUES (값리스트)
속성리스트 생략하면 테이블 정의할때 지정한 속성의 순서대로 값이 삽입됨.
속성의 순서는 변경가능, 그러나 INTO절의 속성이름과 VALUES절의 값은 순서대로 일대일 대응이 되어야함.
INSERT문은 SELECT문을 사용하여 작성할 수 있음
질의) 수입도서테이블에 있는 모든 목록을 BOOK테이블에 모두 삽입합세요
2) UPDATE문
특정 속성값을 수정하는 명령
UPDATE문에서 다른 테이블의 속성값을 이욯라 수 있음
예시)
질의) Customer테이블에서 고객번호가 5인 고객의 주소를 대한민국 부산으로 변경하세요
UPDATE Customer
SET address='대한민국 부산'
WHERE custid=5
3) DELETE문
DELETE FROM 테이블이름
WHERE 검색조건
*WHERE절 생략하면 테이블에 존재하는 모든 튜플을 삭제해 빈 테이블이 됨.
