SQL 개요

1. sql과 프로그래밍 언어의 차이점

2. SQL 기능에 따른 분류

데이터 정의어(Data Definition Language)

테이블이나 관계의 구조를 생성하는 데 사용하며 CREATE, ALTER, DROP 문 등이 있음!

데이터 조작어(Data Manipulation Language)

테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용하며 SELECT, INSERT, DELETE, UPDATE 문 등이 있음. 여기서 SELECT 문은 특별히 질의어(query)라고 함!

데이터 제어어(Data Control Language)

데이터의 사용 권한을 관리하는 데 사용하며 GRANT, REVOKE 문 등이 있음

3. 데이터 조작어 - 검색

1. select문

구성 요소

기본 문법

SELECT [ALL┃DISTINCT] 속성이름(들)
FROM 테이블이름(들)
[WHERE 검색조건(들)][GROUP BY 속성이름]
[HAVING 검색조건(들)]ORDER BY 속성이름 [ASC┃DESC]]
[ ] : 대괄호 안의 SQL 예약어들은 선택적으로 사용한다.
| : 선택 가능한 문법들 중 한 개를 사용할 수 있다.

SQL은 대소문자를 구별하지 않는다.
mysql은 from이 없어도 실행이 된다
oracle은 안됨

Select/From

프로젝션(파이) - select

SELECT bookname, price
FROM Book;

※ 중복을 제거하고 싶으면 DISTINCT라는 키워드를 사용한다.
count에 distinct를 많이 사용함 -> 의미있는 정보 가공 가능

SELECT DISTINCT publisher
FROM Book;

Where [조건]

셀렉션(시그마) - where
from -> where -> select 순으로 작동
where은 그룹핑을 안할때 사용

where 절에 조건으로 사용할 수 있는 술어

비교연산자 <>는 일반적인 프로그래밍 언어에서 !=와 같다.

비교

SELECT *
FROM Book
WHERE price < 20000;

범위

between은 인클루시브(포함하여)

SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;

SELECT *
FROM Book
WHERE price >= 10000 AND price <= 20000;

SELECT phone
FROM Customer
WHERE name='김연아'
= 파이 phone 시그마 '김연아' Custormer

집합

SELECT *
FROM Book
WHERE publisher IN ('굿스포츠', '대한미디어');
또는 not in을 사용할 수도 있다.

패턴

축구의 역사인 레코드 출력

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';

축구를 포함하는 레코드 출력.
문자열에 붙은 %는 와일드 문자(wildcard character)

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';

와일드 문자의 종류

Regular expression은 like 대신 regexp 사용

복합조건

SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price >= 20000;!

SELECT *
FROM Book
WHERE publisher='굿스포츠' OR publisher='대한미디어';

ORDER BY

SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;

order by | asc이 오름차순 desc가 내림차순이다. 옵션 미입력시 asc가 기본값이다.

2. 집계 함수와 GROUP BY

집계함수

SELECT SUM(saleprice)
FROM Orders;

SELECT SUM(saleprice) AS 총매출
FROM Orders;

※ 의미 있는 열 이름을 출력하고 싶으면 속성이름의 별칭을 지칭하는 AS 키워드를 사용하여 열 이름을 부여한다.

SELECT SUM(saleprice) AS Total,
AVG(saleprice) AS Average,
MIN(saleprice) AS Minimum,
MAX(saleprice) AS Maximum
FROM Orders;

집계함수의 종류

GROUP BY

group by는 헷갈리면 큰일난다아아앙
그룹핑 했을 때 최댓값, 최저값 등이 해당 그룹을 대표한다.
따라서 count, sum 등의 집계함수를 쓴다.

SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid;

보통 group by 한 컬럼이 데이터를 대표하는 값이 된다. (기준이 됨)
일반적으로 PK를 사용한다.

group by에서 중요한 점

1. having 절
2. 집계함수를 사용한다.
3. 집계함수를 제외한 컬럼은 대표 속성만 존재한다. (group by 속성만 select에 넣을 수 있고 다른 속성은 집계함수를 사용해야 한다.)
   -> 3번 불만족 시 실행은 되나 의미있는 테이블이 나오지 않음.

이 3가지 조건은 정처기에 아주 자주 나오는 중요한 개념이다!

GROUP BY와 HAVING 절의 주의사항

쿼리조건은 where, 그룹은 having

group by가 있을 시 where이 먼저 나와야 한다.

group by이전에 조건을 주기 : where
이후에 조건을 주기 : having

having에서는 select에서 제시된 컬럼만이 나올 수 있다.
group by 이후를 기준으로 하기 때문에 조건을 줄 때 집계 함수만을 써야한다.
또한, DB 엔진마다 결과가 다르게 나올 수 있다. 원래 문법의 경우 반드시 group by의 뒤에 와야 한다.
MySQL의 경우 group by 없이도 having은 작동하나, 테이블 전체를 집계하기 때문에 의미없는 데이터이다.

select custid, count() as 도서수량
from orders
where saleprice >= 8000
group by custid;
having count() >= 2
이 SQL 문에서 count(*)의 경우 group by의 row를 셀 때 사용하게 된다.
일반적으로 count(distinct bookid) 이런 식으로 사용함.
(*)의 경우 모든 row가 유니크하다.

---

실제 동작의 경우
group by 다음에 having 동작이 아니라 where로 레코드를 뽑고, group by와 having이 동시에 동작한다고 생각해야함.
그룹핑 후 having으로 동시에 조건을 준다고 생각하면 된다.

---

1. where 실행
2. group by 그룹핑
3. having 실행

3. 두 개 이상 테이블에서 SQL 질의

조인

select *
from customer, orders;
여기서 sql문과 같이 from에 테이블이 2개가 올 경우 카티전 프로덕트와 같다.
2개 이상도 되나? 된다!
DB엔진마다 정렬의 기준이 다르다고 한다.

select *
from customer, orders
where customer.custid = orders.custid;
와 같은 방식으로 유의미한 데이터를 뽑아낼 수 있다.
만약 where custid = custid와 같이 작성 시, 두 테이블 모두 해당 속성이 있으므로 구분하지 못해 에러가 뜬다!

select *
from customer c, orders o
where c.custid = o.custid;
다음과 같이 사용가능.

select *
from orders o1, orders o2
where o1.custid = o2.custid;
자기 자신을 반복하는 복잡한 쿼리를 만들 시, alias가 unique 해야 하므로 alias를 반드시 작성해야 함.

select *
from customer natural join orders;
이런 식으로 자연 조인을 만들어 줄 수 있다.
자연조인과 where을 사용한 것의 차이는, 자연 조인은 공통 속성은 하나의 컬럼만 표시한다.

보통 조인 말하면 자연조인 얘기함.

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

SELECT name, SUM(saleprice)
FROM Customer, Orders
WHERE Customer.custid =Orders.custid
GROUP BY Customer.name
ORDER BY Customer.name;

이때, 예제 질의에서 이름을 기준으로 했지만 이것은 옳지 않다.
정렬 시에는 custid 등과 같이 PK 혹은 유니크 id를 이용해 정렬해야만 한다.
예제에서 사용했던 테이블에는 동명이인이 없었지만 실제로는 유니크하지 않을 수 있기 때문에 이름을 사용하는 건 적절하지 않다.

select custid, sum(saleprice)
from orders
group by custid
order by custid

select customer.custid, customer.name, sum(saleprice)
from customer natural join orders
group by customer.custid, customer.name
order by customer.custid;
이 경우와 같이 group by에 name을 사용한 경우?
이것은 예외 케이스이다.
그룹에서 하나의 값만 가지고 있다면 사용해도 상관없는 경우이다.
정처기에서는 못봤다고 하신다. having절은 많이 나온다고 함.

select customer.custid, customer.name, sum(saleprice)
from customer natural join orders
group by customer.custid
order by customer.custid;
이런 식으로 만들어도 그룹은 달라지지 않는다.
custid 즉, PK로 그룹핑 하는 순간 해당 그룹은 유니크해지기 때문에.
따라서 select로 올려도 상관 없다.

하지만 만약에, saleprice가 올 경우?

select customer.custid, saleprice, sum(saleprice)
from customer natural join orders
group by customer.custid, saleprice
order by customer.custid;
이 경우 custid와 saleprice로 그룹핑해서 sum(saleprice)와 saleprice가 같아진다.
따라서 지역 남성, 여성 이런 식으로 그룹핑할 수도 있다.

고객의 이름과 고객이 주문한 도서의 이름을 구하시오.

SELECT Customer.name, Book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid =Orders.custid
AND Orders.bookid =Book.bookid;
-> 압축
SELECT Customer.name, Book.bookname
FROM Customer NATURAL JOIN Orders NATURAL JOIN Book;
여러 테이블을 조인할 경우 다음과 같이 한줄로 쭉 써도 됨.

+)
in은 하나 이상의 값을 넣어줄 수 있다.
작동하지만 제대로 쓰려면 2개이상 써야함

외부조인
sql문은 ppt 52p

MySQL에서는 Full Outer Join을 지원하지 않음 – Full Outer Join은 (Left Join) UNION (Right Join) 방식으로 구현할 수 있음

세타 조인 = 내부 조인

inner join (= seta join) 조건 추가해서 세타조인을 구현함
조인조건을 맘대로 줘서 포함관계 더 잘 나타낼 수 있다?

natural join
outer join

semi join?
(닫힌 쪽)왼쪽 테이블만 나오게 한다.

세미조인 구현하기

select customer.custid
from customer
inner join orders on customer.custid = orders.custid;

select custid
from customer
natural join orders;

둘다 결과는 같다. 근데 이너는 왜 테이블 명까지 쓰는가?
조인 조건에 있는 컬럼만 통합하기 때문에 중복컬럼이 나올 수 있다!

select distinct custid, name
from customer
natural join orders

따로 명령어는 없다. 다음과 같이 서브쿼리 형식으로 구현가능.

부속 질의
질의 안에 질의를 넣는 것.
내부 질의가 먼저 실행된다!
안과 밖중 안의 쿼리가 밖의 쿼리와 아무 상관없을 시 안에 있는게 먼저 실행됨
(내부 쿼리 시행시 외부 쿼리 정보가 필요 한가)

내부, 외부, 세미 조인은 조건 써준다.

질의3-29 관계대수로 만들 시
프로젝션 써준다.
name(customer semi(조건) orders)

쿼리 작동은 row by row라고 생각하면 된다ㅏㅏㅏ

데이터가 n개 일 때 순차탐색의 평균 시간복잡도
n/2

상관 부속질의
상관 부속질의(correlated subquery)는 상위 부속질의의 투플을 이용하여
하위 부속질의를 계산함. 즉 상위 부속질의와 하위 부속질의가 독립적이지 않고
서로 관련을 맺고 있음.

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
FROM Book b2
WHERE b2.publisher=b1.publisher);
둘다 똑같은 테이블이지만 다른 테이블로 본다?

작동 시 밖의 for문 안의 for문 이런식으로 돌아간다고 생각하면 됨.
왼쪽테이블의 각 투플마다 for문이 돌아가서 avg 구하면 왼쪽 테이블과 비교해서 같은거 추려내는 식

집합 연산
합집합 UNION, 차집합 MINUS, 교집합 INTERSECT

<여기서 잠깐> MINUS, INTERSECT 연산자
MySQL에는 MINUS, INTERSECT 연산자가 없으므로 다음과 같이 표현한다.
[질의 3-32]에서 MINUS 연산을 수행한 “대한민국에서 거주하는 고객의 이름에서 도서를 주문한 고객의 이름 빼고 보이시오.” 질의를 NOT IN 연산자를 사용하면 다음과 같다.

SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
name NOT IN (SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders));

[질의 3-32]에서 INTERSECT 연산을 수행한 “대한민국에서 거주하는 고객 중 도서를 주문한 고객의 이름 보이시오.” 질의를 IN 연산자를 사용하면 다음과 같다.

SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
name IN (SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders));

(in, not in 사용시 주의할 점: 비교대상의 컬럼이 같아야 한다.)
같은 도메인을 써야 한다?

EXISTS는 원래 단어에서 의미하는 것과 같이 조건에 맞는 튜플이 존재하면 결과에 포함시킴. 즉 부속질의문의 어떤 행이 조건에 만족하면 참임.
NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참임.

SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
FROM Orders od
WHERE cs.custid =od.custid);
SELECT name, address
FROM Customer cs
WHERE cs.custid in (SELECT custid
FROM Orders od
WHERE cs.custid =od.custid);
in으로도 exist 구현 가능함.
exist는 복잡한 거 쓸 때? 한다?

속도는 in이 더 빠르다.
비상관 쿼리라서.
exists는 상관 쿼리
그리고 인쿼리 데이터테이블 크기도 exists가 더 큼.

더빠르게 하려면 인쿼리 custid에 distinct 붙임

in => 비교 대상 column이 1개 이상 필요함.
비교대상 컬럼만 같으면 됌
SELECT name, address
FROM Customer cs
WHERE custid (SELECT (distinct) custid
FROM Orders);

exists => 같은 로우 존재시 출력함
정확히 매치되는 로우 값 필요
SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
FROM Orders od
WHERE cs.custid =od.custid);

exist는 정확히 같은 값을 찾기 때문에 시간이 오래걸림
따라서 in query가 더 효율적

쿼리 = DML
삽입 삭제 수정

DDL
스키마 만들때 씀.

DCL => 권한관리

데이터 조작어 - 삽입, 수정, 삭제
1. INSERT문
테이블에 새로운 투플을 삽입하는 명령

INSERT INTO 테이블이름[(속성리스트)]
VALUES (값리스트);

INSERT INTO Book(bookid, bookname, publisher, price)
VALUES (11, '스포츠 의학', '한솔의학서적', 90000);

대량 삽입(bulk insert)이란 한꺼번에 여러 개의 투플을 삽입하는 방법임.
INSERT INTO Book(bookid, bookname, price, publisher)
SELECT bookid, bookname, price, publisher
FROM Imported_book;

2. UPDATE문
   특정 속성 값을 수정하는 명령이다.
   UPDATE 테이블이름
   SET 속성이름1=값1[, 속성이름2=값2, ...][WHERE <검색조건>];

=> 한번에 여러 속성을 바꿀 수 있다!

SET SQL_SAFE_UPDATES=0; / Safe Updates 옵션 미 해제 시 실행 /

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

UPDATE Book
SET publisher = (SELECT publisher
FROM imported_book
WHERE bookid = '21')
WHERE bookid = '14' ;

3. DELETE문
   테이블에 있는 기존 투플을 삭제하는 명령

insert, delete 는 row단위 추가, 삭제
update는 특정값을 삭제(NULL)로 바꾸는 것 등... 이것도 수정의 범주이다.

데이터 정의어

1. CREATE 문

테이블 구성, 속성과 속성에 관한 제약 정의, 기본키 및 외래키를 정의하는 명령
PRIMARY KEY : 기본키를 정할 때 사용
FOREIGN KEY : 외래키를 지정할 때 사용
ON UPDATE와 ON DELETE : 외래키 속성의 수정과 투플 삭제 시 동작을 나타냄

CREATE 문의 기본 문법

CREATE TABLE 테이블이름
( { 속성이름 데이터타입
[NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
}
[PRIMARY KEY 속성이름(들)]
{[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]ON DELETE [CASCADE┃SET NULL]
}
)

not null
unique => 중복허용안함

※ 기본키를 지정하고 싶다면 다음과 같이 생성한다.

CREATE TABLE NewBook (
bookid INTEGER,
bookname VARCHAR(20),
publisher VARCHAR(20),
price INTEGER,

PRIMARY KEY (bookid));

CREATE TABLE NewBook (
bookid INTEGER PRIMARY KEY,
bookname VARCHAR(20),
publisher VARCHAR(20),
price INTEGER);

※ bookid 속성이 없어서 두 개의 속성 bookname, publisher가 기본키가 된다면 괄호를 사용하여
복합키를 지정한다.
CREATE TABLE NewBook (
bookname VARCHAR(20),
publisher VARCHAR(20),
price INTEGER,
PRIMARY KEY (bookname, publisher));

bookname은 NULL 값을 가질 수 없고, publisher는 같은 값이 있으면 안 된다. price에 값이 입력되지 않을 경우 기본 값 10000을 저장한다. 또 가격은 최소 1,000원 이상으로 한다.
CREATE TABLE NewBook (
bookname VARCHAR(20) NOT NULL,
publisher VARCHAR(20) UNIQUE,
price INTEGER DEFAULT 10000 CHECK(price > 1000),
PRIMARY KEY (bookname, publisher));

primary key가 2개이므로 북네임, 퍼블리셔의 조합이 유니크해야 한다.
북네임의 경우 중복이 되도 상관없지만, 퍼블리셔는 유니크 해야만 한다.

외래키 제약조건을 명시할 때는 반드시 참조되는 테이블(부모 릴레이션)이
존재해야 하며 참조되는 테이블의 기본키여야 함
외래키 지정 시 ON DELETE 또는 ON UPDATE 옵션은 참조되는 테이블의 튜플이 삭제되거나 수정될 때 취할 수 있는 동작을 지정함
NO ACTION은 어떠한 동작도 취하지 않음.

VARCHAR, CHAR 차이
CHAR : 고정된 길이
VARCHAR : 최대 길이

CREATE TABLE NewOrders (
orderid INTEGER,
custid INTEGER NOT NULL,
bookid INTEGER NOT NULL,
saleprice INTEGER,
orderdate DATE,
PRIMARY KEY (orderid),
FOREIGN KEY (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE );

CREATE TABLE NewOrders (
orderid INTEGER,
custid INTEGER NOT NULL,
bookid INTEGER NOT NULL,
saleprice INTEGER,
orderdate DATE,
PRIMARY KEY (orderid),
FOREIGN KEY (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE );

마지막 줄은 외래키 제약
NewCustomer 테이블의 custid 참조
on delete set null 시, 원본 데이터 삭제 시 NewOrders 테이블에서는 NULL로 바뀜.
No Action -> 지워질 때 아무거도 안함
Cascade -> 지워질 때 같이 지운다
on delete는 외래 키 삭제 시에 대한 동작, on update는 수정 에 대한 동작

외래키 제약조건을 명시할 때는 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 하며 참조되는 테이블의 기본키여야 함
외래키 지정 시 ON DELETE 또는 ON UPDATE 옵션은 참조되는 테이블의 튜플이 삭제되거나 수정될 때 취할 수 있는 동작을 지정함
NO ACTION은 어떠한 동작도 취하지 않음.

ALTER 문은 생성된 테이블의 속성과 속성에 관한 제약을 변경하며,
기본키 및 외래키를 변경함
ADD, DROP은 속성을 추가하거나 제거할 때 사용함
MODIFY는 속성의 기본값을 설정하거나 삭제할 때 사용함
ADD <제약이름>, DROP <제약이름>은 제약사항을 추가하거나 삭제할 때 사용함
무려무려 컬럼뿐만 아니라 제약조건 또한 추가가 가능하다!

ALTER TABLE 테이블이름
[ADD 속성이름 데이터타입][DROP COLUMN 속성이름]
[MODIFY 속성이름 데이터타입]MODIFY 속성이름 [NULL┃NOT NULL]]
[ADD PRIMARY KEY(속성이름)][ADD┃DROP] 제약이름]
기존 속성이름은 그대로 써야함.

DROP 문은 테이블을 삭제하는 명령
DROP 문은 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함
(데이터만 삭제하려면 DELETE 문을 사용함).
DROP TABLE 테이블이름