25.03.28 sql 고급

set SQL_SAFE_UPDATES = 0; /* SAFE 모드 끄기*/
set SQL_SAFE_UPDATES = 1; /* SAFE 모드 켜기*/

 

create

테이블 생성 규칙

• 테이블명

객체를 의미할 수 있는 이름(단수형 권장)

다른 테이블과 중복 불가

• 컬럼명

한 테이블 내에서 컬럼명 중복 불가

태이블 생성 시 각 컬럼은 괄호 내에서 ,로 구분됨

컬럼 뒤에 데이터 유형 반드시 지정

• 테이블명&컬럼명

예약어 사용 불가

컬럼명은 문자, 숫자, 일부 기호(_ $ #)만 허용됨

• 제약조건명

다른 제약조건과 이름 중복 불가

 

다음과 같은 속성을 가진 테이블 생성

create table newBook (
bookid int primary key not null auto_increment, 
bookname varchar(20), 
publisher varchar(20) default null , 
price int default 0
);

 

테이블 삭제

drop table newBook;

 

유니크 및 체크 추가

create table newBook (
bookname varchar(20) not null, 
publisher varchar(20) unique, 
price int default 10000 check( price > 1000),
primary key (bookname, publisher)
);

 

다음과 같은 속성을 가진 newcustomer 테이블 생성하기 3-35

[custid - 기본키, 자동증가]

create table newCustomer (
custid int primary key auto_increment,
name varchar(40),
address varchar(40),
phone varchar(30)
);

 

다음과 같은 속성을 가진 newOrders 테이블 생성하기

[orderid 기본키 자동증가 / custid not null, 외래키(newCustomer.custid), 연쇄삭제 / bookid not null]

 create table newOrders (
 orderid int primary key auto_increment,
 custid int not null,
 bookid int not null,
 saleprice int,
 orderdate date,
 foreign key(custid) references newCustomer(custid) on delete cascade
 );

 

외래키 제약조건 명시 시 참조되는 테이블이 존재해야 하며 해당 테이블에 기본키여야 함

외래키 지정 시 on delete 또는 on update 옵션은 참조되는 테이블의 투플이 삭제되거나 수정될 때 취할 수 있는 동작 지정

no action은 어떠한 동작도 취하지 않음

+ 다른테이블을 바탕으로 테이블 생성

create table aaa as select * from bbb; > not null 제약조건만 복사되고 다른 제약조건은 수동으로 추가해야함

 

 

alter 

테이블 속성과 속성에 관한 제약 변경 (기본키 및 외래키)

add - 칼럼 추가 (맨 뒤에 추가됨)

drop column -  컬럼 삭제 (삭제 후 최소 1 이상의 칼럼이 존재해야함)

rename column to - 해당 컬럼의 모든 정의 유지되고 이름만 변경

modify - 속성의 기본값 설정하거나 삭제 시 사용

+이미 입력되어 있는 값에 영향을 미치는 변경은 불허

데이터 타입 변경 - 테이블에 아무 행도 없거나 null만 갖고 있을때만 가능 

컬럼 크기 변경- 확대 항상 가능 .

   축소는 테이블에 아무 행도 없거나 null만 있거나 현재 저장된 값을 수용할 수 있는 크기로 축소만 가능

default 값 추가 및 수정 - 추가 및 수정 이후 삽입되는 행에만 영향 미침

add 제약이름, drop 제약이름 > 제약사항 추가나 삭제할 때 사용

alter 기본문법

drop

테이블 삭제 > 구조와 모든 데이터를 삭제함

데이터만 삭제 시 delete 사용

삭제 시 참조무결성에 위배될 위험존재

기본문법 - drop table 테이블명

 

---

마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오.
(1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름

SELECT distinct name 
FROM customer c2 
JOIN orders o ON c2.custid = o.custid 
JOIN book b ON o.bookid = b.bookid 
WHERE b.publisher IN (
    SELECT b.publisher 
    FROM book b 
    JOIN orders o ON b.bookid = o.bookid 
    JOIN customer c1 ON c1.custid = o.custid 
    WHERE c1.name = '박지성'
) ;
(2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름

select c.name, count(distinct b.publisher)
from book b, orders o, customer c
where o.custid = c.custid and b.bookid = o.bookid
group by name
having count(distinct b.publisher) >=2;
(3) 전체 고객의 30% 이상이 구매한 도서

selectb.bookname, count(b.bookname)
from orders o, book b
where o.bookid = b.bookid
group by b.bookname
having count (b.bookname) >= 
(select (count(*) * 0.3) from customer);

 

 

다음 질의에 대해 DML 문을 작성하시오.
(1) 새로운 도서 (‘스포츠 세계’, ‘대한미디어’, 10000원)이 마당서점에 입고되었다.
삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보자.

insert into book (bookid,bookname, publisher, price) 
values (12,'스포츠 세게','대한미디어',10000); 
(2) ‘삼성당’에서 출판한 도서를 삭제해야 한다.

delete from book where publisher = '삼성당';
(3) ‘이상미디어’에서 출판한 도서를 삭제해야 한다. 삭제가 안 될 경우 원인을 생각해보자.

delete from book where publisher = '이상미디어';
(4) 출판사 ‘대한미디어’가 ‘대한출판사’로 이름을 바꾸었다.

update book set publisher = '대한출판사' where publisher = '대한미디어';

 

---

내장함수

sql 내에서 함수의 개념 사용 > 특정 값이나 열의 입력값을 받아 그 값을 계산해 결과 값 리턴

sql 함수 =  dbms가 제공하는 내장함수 / 사용자 정의 함수로 구분

 

상수나 속성 이름을 입력값으로 받아 단일 값을 결과로 반환함

> 최초엥 선언될 때 유효한 입력값 받음

abs 함수 - 절댓값 구함

-78 +78의 절댓값 구하기

select abs(-78), abs(+78); //from 없는 select

 

round - 반올림값 구함

select round(4.87 , 1); //소수 첫째 자리까지 반올림함 

 

고객별 평균 주문 금액을 백 원 단위로 반올림한 값

select custid, round(sum(saleprice)/count(*), -2) 
from orders group by custid;

 

 

문자함수

마당서점 고객 중 같은 성을 가진 사람이 몇 명이나 되는 지 구하시오

select substr(name, 1, 1) as '성', count(*) as '인원' 
from customer
group by substr(name,1,1);

 

null값

아직 지정되지 않은 값 > 비교 연산자로 비교 불가능

주의할 점

null + 숫자 결과는 null

집계함수 연산에 null이 포함된 행은 계산에서 제외됨

해당되는 행이 하나도 없을 경우 sum, avg 등의

함수 결과 = null / count = 0

 

null값 확인

is null / in not null

 

ifnull - null값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력

ifnull(속성, 대치할 값)

---

subquery 부속질의

하나의 sql문 안에 다른 sql문이 중첩된 질의

다른 테이블에서 가져오거나 현재 테이블에 있는 정보를 찾거나 가공할 때 사용

종류

명칭	위치	영문 및 동의어	설명
스칼라 부속질의	select 절	scalar subquery	select 절에서 함수처럼 사용 단일값, 행만 반환
인라인 뷰	from 절	inline view, table subquery	from절에서 결과를 뷰형태로 반환
중첩 부속질의	where 절	nested subquery, predicate subquery	where절에 술어와 같이 사용됨 결과를 한정시키기 위해 사용 상관 혹은 비상관 형태

 

스칼라

select 절에서 사용되는 부속질의 > 결과값을 단일 행, 단일 열의 스칼라 값으로 반환

결과값이 다중 행이거나 다중 열 > 에러 출력 // 결과가 없는 경우 null 출력

원칙적으로 스칼라 값이 들어갈 수 있는 모든곳에 사용 가능

일반적으로 select 문과 update set 절에 사용됨

주질의와 부속질의 관계는 상관/비상관 모두 가능

 

마당서점의 고객별 판매액을 보이시오(고객이름과 고객별 판매액을 출력)

select name, sum(saleprice) 'total'

from orders o, customer c

where c.custid = o.custid

group by o.custid;

 

도서이름 추가 후 각 주문에 맞는 도서이름 입력하기

alter table orders add bname varchar(40);
update orders set bname = (select bookname from book where book.bookid = orders.bookid);

 

 

인라인뷰

from 절에서 사용됨

테이블 이름 대신 인라인뷰 사용해 보통의 테이블과 같은 형태로 사용가능

부속질의 결과로 반환되는 데이터는 다중행, 열이어다 상관없음

//가상의 테이블인 뷰 형태로 제공되기 때문에 상관 부속질의로는 사용 불가능

 

고객번호가 2 이하인 고객의 판매액을 보이시오(고객이름과 고객별 판매액 출력)

select name, sum(saleprice)
from (select custid, name from customer where custid <= 2) cust
join orders o on cust.custid = o.custid

group by name;

 

중첩질의(nested subquery)

where 절에서 사용되는 부속질의

조건 혹은 술어(predicate)와 같이 사용됨

반드시 단일 행, 열을 반환해야 하며 아닐경우 질의 처리 불가능

 

처리과정 = 주질의 대상 열 값과 부속질의 결과 값을 비교연산자에 적용해 참이면 주질의의 해당열을 출력함

 

평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

select orderid, saleprice from orders
where saleprice <=(select avg(saleprice) from orders);

 

각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호,금액을 보이시오.

select orderid, custid, saleprice
from orders o1
where saleprice > 
(select avg(saleprice) 
from orders o2 
where o1.custid = o2.custid);

 

in, not in

in - 주질의 속성 값이 부속질의에서 제공한 결과 집합에 있는지를 확인하는 역ㅎ할

>다중 행 가능

주질의는 where 절에서 사용되는 속성 값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참

not in은 반대로 값이 존재하지 않아야 참

 

대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

select sum(saleprice) 

from orders 

where custid in

(select custid 

from customer 

where address like '%대한민국%');

 

all, some(any)

all = 모두 / some(any) = 어떠한(최소 하나이상) 

 

3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

select orderid, saleprice

from orders

where saleprice > all ( select saleprice from orders where custid =3);