[DATABASE] SQL 기초

SQL 기능에 따른 분류

1. 데이터 정의어 (DDL) : 테이블이나 관계의 구조를 생성하는 데 사용 CREATE,ALTER,DROP등

2. 데이터 조작어 (DML) : 테이블에 데이터를 검색 삽입 수정 삭제하는데 사용 SELECT,INSERT,DELETE,UPDATE등

3. 데이터 제어어 (DCL) : 데이터의 사용 권한을 관리하는 데 사용 GRANT,REVOKE등

+) 권한 할당 : grant / 권한 제거 : revoke

SELECT문의 구성 요소

SELECT [ALL┃DISTINCT] 속성이름(들)

FROM 테이블이름(들)

[WHERE 검색조건(들)]

[GROUP BY 속성이름]

[HAVING 검색조건(들)]

[ORDER BY 속성이름 [ASC┃DESC]]

ex) SELECT   phone FROM    Customer Where    name=‘김연아’

+) 중복을 제거하고 싶으면 DISTINCT라는 키워드 사용

SELECT DISTINCT publisher from book;

비교	=,<>,<,<=,>,>=	price<20000
범위	BETWEEN	price BETWEEN 10000 AND 20000
집합	IN, NOT IN	price IN(10000,20000,30000)
패턴	LIKE	bookname LIKE '축구의 역사'
NULL	IS NULL, IS NOT NULL	price IS NULL
복합 조건	AND,OR,NOT	(price<2000)and (bookname LIKE '축구의 역사')

+) 출판사가 '굿스포츠' 혹은 '대한미디어'가 아닌 출판사를 검색하시오

select* from book where publisher not in('굿스포츠','대한미디어');

+) 도서 이름에 '축구'가 포함된 출판사를 검색하시오.

select bookname, publisher from book where bookname like '%축구%';

+) 도서이름의 왼쪽 두번째 위치에 '구'라는 문자열을 갖는 도서를 검색하시오.

select * from book where bookname like '_구%';

+	문자열을 연결	'골프'+'바이블'
%	0개 이상의 문자열과 일치
[]	1개의 문자의 와 일치	'[0-5]%' : 0-5사이 숫자로 시작하는 문자열
[^]	1개의 문자와 불일치	'[^0-5]%' : 0-5사이 숫자로 시작하지 않는 문자열
_	특정 위치의 1개의 문자와 일치

+) 도서의 가격순으로 검색하고 가격이 같으면 이름 순으로 검색

select * from book order by price,bookname (작은 것부터 큰 것으로)

+) 도서를 가격의 내림차순으로 검색하시오. 만약 가겨이 같다면 출판사를 오름차순으로 검색한다.

select* from book order by price DESC,publisher ASC;

+) 고객이 주문한 도서의 총 판매액을 구하시오

select SUM(saleprice) from orders;

+) 의미 있는 열 이름을 출력하고 싶으면 별칭을 지칭하는 as키워드 사용

select sum(saleprice) as 총매출 from orders;

SUM	SUM(속성이름)	SUM(price)
AVG	AVG(속성 이름)	AVG(price)
COUNT	COUNT(속성 이름|*)	COUNT(*)
MAX	MAX(속성 이름)	MAX(price)
MIN	MIN(속성 이름)	MIN(price)

+) 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오

select custid, COUNT(*) as 도서수량, SUM(saleprice) as 총액 from orders group by custid;

+) 가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오.

  단 두 권 이상 구매한 고객만 구한다

select custid, count(*) as 도서 수량 from orders where saleprice>=8000 group by custid having count(*)>=2;

HAVING 절 조건

1. 반드시 group by 절과 같이 작성

2. where 절보다 뒤에 와야 함.

3. 검색 조건에는 SUM,AVG,MAX,MIN,COUNT와 같은 집계함수가 와야 한다.

join

- 조건 없이 연결하면 customer의 5명의 고객과 orders 테이블의 10개의 주문이 합쳐져 50개의 튜플이 나온다.

select* from Customer,Orders;

+) 고객과 고객의 주문에 관한 데이터를 모두 보이시오.

select * from customer,orders where customer.custid = orders.custid;

+) 고객 별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오

select name, sum(saleprice) from customer,orders where customer.custid = orders.custid 

group by customer.name order by customer.name;

+)

select customer.name, book.bookname from customer,orders,book 

where customer.custid = orders.custid and orders.bookid = book.bookid;

+) 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 가격을 구하시오

select customer.name,saleprice from customer left outer join orders on customer.custid = orders.custid;

+) select * from customer left outer join orders on customer.custid = orders.custid;

join의 개념

참고 : http://mo-world.tistory.com/entry/Mysql-Join-%EC%97%AC%EB%9F%AC%EA%B0%80%EC%A7%80-%EB%B0%A9%EB%B2%95

on 절을 이용한 조인

- 임의의 조건을 지정하거나 조인할 열을 지정하려면 on절을 사용해야함

ex)

select * from customer c join orders o on (c.custid = o.custid);

일반적인 조인	select 속성 from 테이블1, 테이블2 where 조인 조건 and 검색 조건	동등 조인, 두가지 문법 중 하나 사용 가능
	select 속성 from 테이블1 inner join 테이블2 on 조인 조건 where 검색 조건
외부조인	select 속성 from 테이블 (LEFT|RIGHT] JOIN 테이블2 on 조인조건 where 검색조건	외부 조인은 from 절에 조인 종류를 적고 on을 이용하여 조인 조건을 명시

+) 단순 조인 inner조인 = 등가조인(equal join) 이라고도 함

   동등 연산자가 아닌 연산자를 포한하는 조인

   - between and, is null, is not null, not in

   outer join : 조인 조건을 만족하지 않는 행도 반환

                where절에서 한편에만 올 수 있고 in, or 연산자는 사용할 수 없음

왼쪽 외부 조인 : 왼쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용

오른쪽 외부 조인 : 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용

완전 외부 조인

카티시안 곱(Cartesial Product) = 교차조인(cross join)

- 조인 조건이 부적합하거나 조인 조건을 완전히 생랽한 경우 행의 모든 조합을 표시하는 카타시안 곱 표가 생성

- 너무 많은 행을 생성하므로 결과에 유용하게 생성되지 않음

- 많은 수의 행을 생성해야하는 경우에는 유용

ex)  select * from customer, orders;

     select * from customer cross join orders;

자연 조인(Natural join)

- 두 테이블이 하나 이상의 공통된 컬럼을 기반으로 조인 연산을 수행

- 등가조인의 결과와 동일

- where 인지 natural join인지의 차이

ex) select * from customer natural join orders;

참고 : http://blog.ngelmaum.org/entry/lab-note-sql-join-method

+) 가장 비싼 도서의 이름을 보이시오

select bookname from book where price = (select max(price) from book);

+) 대한 미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오

select name from customer where custid in

(select custid from orders where bookid in

(select bookid from book where publisher = '대한미디어'));

+) 출판사 별로 출판사의 평균 도서가격보다 비싼 도서를 구하시오

select b1.bookname from book b1 where b1.price>

(select avg(b2.price) from book b2 where b2.publisher = b1.publisher);

+) 도서를 주문하지 않은 고객의 이름을 보이시오.

select name from customer except

select name from customer where custid in

(select custid from orders);

+) 주문이 있는 고객의 이름과 주소를 보이시U오

select name, address from customer cs where exists

(select * from orders od where cs.custid = od.custid);

Union 합집합	두 테이블의 결합, 결합시키는 두 테이블의 중복되지 않은 값을 반환	select 속성 from table이름 UNION select 속성 from table이름;
Union ALL : 중복을 포함하는 합집합	UNION과 같으나 두 테이블의 중복되는 값까지만 반환	select 속성 from table이름 union all select 속성 from table이름;
INTERSECT : 교집합	두행의 집합 중 공통된 행을 반환	select 속성 from table이름 INTERSECT select 속성 from table이름;
MINUS : 차집합	두번째 select문에 의해 반환되는 행에 존재하지 않는 행들을 반환	select 속성 from table이름 MINUS select 속성 from table이름;

CREATE

CREATE TABLE 테이블이름

 ( { 속성이름 데이터타입

     [NOT NULL]

     [UNIQUE]

     [DEFAULT 기본값]

     [CHECK 체크조건]

    }

     [PRIMARY KEY 속성이름(들)]

     {[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]

    [ON UPDATE [NO ACTION┃CASCADE┃SET NULL┃SET DEFAULT]]

    [ON DELETE [NO ACTION┃CASCADE┃SET NULL┃SET DEFAULT]]

     }

 )

+) create문에 좀더 복잡한 제약사항을 추가

create table newbook(

bookname varchar(20) not null,

publisher varchar(20) unique,

price in default 1000 check(price>1000),

primary key(bookname,publisher));

+) unique 와 primary key의 차이점?

primary key 는 constraints 제한 제약적 (unique + not null = primary key)

unique 는 인덱스

즉 not null의 차이

+) foreign key를 추가한 create문

CREATE TABLE NewOrders (

orderid   INT,

custid   INT   NOT NULL,

bookid   INT   NOT NULL,

saleprice   INT,

orderdate   DATE,

PRIMARY KEY (orderid),

FOREIGN KEY (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE);

DELETE CASCADE : 부모의 key가 삭제되면 자동으로 외래키도 삭제

조건

1) on delete or on update

2) no action

3) set null

4) set default

데이터타입의 종류

INT	정수형, 크기는 +-2	BIGINT SMALLINT
NUMERIC(p,s)	실수형 p자리 정수, s자리 소수	DECIMAL(p,s)
CHAR(n)	문자형 고정길이
VARCHAR(n)	문자형 가변길이
DATE	날짜형 기본형은 YYYY-MM-DD	DATETIME,TIME

ALTER 문

ALTER TABLE 테이블이름

[ADD 속성이름 데이터타입]

  [DROP COLUMN 속성이름]

  [ALTER COLUMN 속성이름 데이터타입]

  [ALTER COLUMN 속성이름 [NULL┃NOT NULL]]

  [ADD PRIMARY KEY(속성이름)]

  [[ADD┃DROP] 제약이름]

+) ALTER TABLE NewBook ADD isbn VARCHAR(13); //추가

+) ALTER TABLE NewBook ALTER COLUMN isbn INT; //형 변환

+) ALTER TABLE NewBook DROP COLUMN isbn; //삭제

+) ALTER TABLE NewBook ALTER COLUMN bookid INT NOT NULL; //형 변환

+) ALTER TABLE NewBook ADD PRIMARY KEY(bookid); //primary key로 변환

DROP 문

+) DROP TABLE NewBook;

INSERT문

+) INSERT  INTO Book(bookid, bookname, publisher, price)

  VALUES (11, '스포츠 의학', '한솔의학서적', 90000);

+) 수입도서 목록을 Book 테이블에 모두 삽입하시오

INSERT INTO Book(bookid, bookname, price, publisher)

SELECT bookid, bookname, price, publisher FROM  Imported_book;

UPDATE 문

+) UPDATE   Customer SET   address='대한민국 부산' WHERE   custid=5; 

+) 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오

   UPDATE  Customer SET   address = (SELECT address FROM Customer WHERE name='김연아') WHERE   name='박세리';

DELETE 문

+) DELETE FROM  Customer WHERE  custid=5;

+) DELETE FROM Customer;