4주차 SQL기초(2)

About this offer

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절 생략하면 테이블에 존재하는 모든 튜플을 삭제해 빈 테이블이 됨.