[SQL] 데이터베이스 기본, SQL 기본 문법

관계형 DBMS(=RDBMS)

최소단위 테이블(열과 행으로 구성)

 

 

데이터베이스 모델링

폭포수 모델(waterfall model): 소프트웨어 개발 절차

1. 프로젝트 계획
2. 업무 분석
3. 시스템 설계
4. 프로그램 구현
5. 테스트
6. 유지보수

 

 

데이터베이스 개체

인덱스, 뷰, 스토어드 프로시저의 간단한 개요

 

1)인덱스

인덱스가 없는 경우

select * from member2 where member_name = '레몬';

Full Table Scan: 전체 테이블에서 찾음. 시간이 오래 걸림

 

인덱스가 있는 경우

(동일 쿼리)

인덱스를 통해 검색. query cost가 절반가량 감소

 

 

2)뷰

가상의 테이블: 보안 강화, SQL문 간단해짐

CREATE VIEW member_view AS SELECT * FROM member;
select * from member_view;

 

 

3)스토어드 프로시저

프로그래밍 기능. 데이터베이스 성능이 저하되기 때문에 가능한 사용하지 않는 것이 좋음

쿼리 두 개를 동시에 실행하는 스토어드 프로시저

DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
	select * FROM member WHERE member_name = '달팽이';
	select * from product where product_name = '삼각김밥';
END //
DELIMITER ;
CALL myProc();

 

 

 

Chapter 03 SQL 기본 문법

create: 데이터베이스 개체를 만드는 명령어

drop: 데이터베이스 개체를 삭제하는 명령어

 

FK(foreign key): 유효성 검사(참조 칼럼에 없는 값이면 INSERT 불가능), 다른 테이블의 PK만 FK로 가져올 수 있음.

 

create table member(
mem_id char(8) not null primary key,
mem_name varchar(10) NOT NULL,
mem_number int not null,
addr varchar(2) not null,
phone1 varchar(3),
phone2 varchar(8),
height smallint,
debut_date date
)default charset=utf8;

create table buy(
num int auto_increment not null primary key,
mem_id varchar(8) not null,
prod_name varchar(6) not null,
group_name varchar(4),
price int not null,
amount smallint not null,
foreign key (mem_id) references member(mem_id)
)default charset = utf8;

auto_increment: 자동으로 숫자를 입력(증가)

 

1. SELECT문

SELECT 칼럼명
    [from 테이블명]
    [where 조건식]
    [group by {칼럼명|표현절|포지션}]
    [HAVING 조건식]
    [ORDER BY {칼럼명|표현문|포지션}]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

 

select * from market_db.member; -- use 데이터베이스를 하지 않은 경우
select * from member; -- use 데이터베이스로 데이터베이스가 지정된 경우

 

 

1.1 WHERE절

SELECT 컬럼명 FROM 테이블명 WHERE 조건식;
select * from member where mem_name="블랙핑크";
select * from member where mem_number >5;

 

관계 연산자, 논리 연산자 사용

select mem_id, mem_name from member where height<=162;
select mem_name, height, mem_number from member where height >165 and mem_number >6;
select mem_name, height, mem_number from member where height >165 or mem_number >6;

 

BETWEEN ~ AND

-- between 미사용
select mem_name, height from member where height >=163 and height <=165;
-- between 사용
select mem_name, height from member where height between 163 and 165;

 

IN

-- in 미사용, = + or 사용
select mem_name, addr from member where addr = '경기' or addr = '전남' or addr = '경남';
-- in 사용
select mem_name, addr from member where addr in('경기', '전남', '경남');

 

 

LIKE

-- '우'가 포함되면 됨
-- 우%: 앞에 '우'가 와야 함
-- %우: 뒤에 '우'가 와야 함
select * from member where mem_name like '%우%';
-- oo핑크(언더바 하나당 한 글자)
select * from member where mem_name like '__핑크';

 

 

서브쿼리: SELECT 안에 또다른 SELECT를 넣음(두 개의 쿼리를 하나로 합침)

-- 서브쿼리 x
select height from member where mem_name = '에이핑크'; -- 결과값 164
select mem_name, height from member where height>164;
-- 서브쿼리 사용
select mem_name, height from member
	where height>(select height from member where mem_name = '에이핑크');
    
-- 결과 동일

 

 

 

2. SELECT문 심화

2.1 ORDER BY

2.2  LIMIT

2.3 DISTINCT

2.4 GROUP BY

2.5 HAVING

 

 

 

2.1 ORDER BY

ASC: 기본값, 오름차순

DEC: 내림차순

-- 데뷔일을 기준으로 정렬
select mem_id, mem_name, debut_date from member order by debut_date;
select mem_id, mem_name, debut_date from member order by debut_date desc;

 

*ORDER BY 절은 WHERE절 앞에 올 수 없음

-- 오류 발생
select mem_id, mem_name, debut_date from member order by debut_date desc WHERE height >= 164;
-- 올바른 예시
select mem_id, mem_name, debut_date from member WHERE height >= 164 order by debut_date desc;

 

정렬된 결과를 다른 기준으로 재정렬 가능

-- 키순으로 정렬 후 데뷔일순으로 정렬
select mem_id, mem_name, debut_date from member order by height desc, debut_date asc;

 

 

2.2 LIMIT: 조회 개수 제한

3개만 조회

select * from member limit 3;

 

데뷔일순으로 정렬 후 3개만 조회

select * from member order by debut_date limit 3;

 

키순으로 정렬 후 3번째부터 2개만 조회

select * from member order by height desc limit 3, 2;

 

 

2.3 DISTINCT: 중복 제거

컬럼 명 앞에 붙이면 중복되는 결과는 제외하고 출력

select distinct addr from member;

 

 

 

2.3 GROUP BY: 그룹을 만들어서 중복 제거

order by 예시

select mem_id, amount from buy order by mem_id;

 

GROUP BY를 사용해서 중복 제거, 한 번에 모음

select mem_id, sum(amount) from buy group by mem_id;

 

GROUP BY는 주로 집계 함수와 함께 사용

 

2.3.1 집계함수

집계 함수	설명
SUM()	합계
AVG()	평균(결과값 실수)
MIN()	최소값
MAX()	최대값
COUNT()	행의 개수
COUNT(DISTINCT)	행의 개수(중복 제외)

 

+ AS 사용

select mem_id '회원 아이디', sum(amount) '총 구매 개수' from buy group by mem_id;

 

집계 함수의 파라미터에 식을 넣어서 새로운 컬럼을 만들 수도 있다.

select mem_id '회원 아이디', sum(amount*price) '총 구매 금액' from buy group by mem_id;

 

2.3.2 AVG()

select avg(amount) "평균 구매 개수" from buy;
select mem_id, avg(amount) "평균 구매 개수" from buy group by mem_id;

 

2.3.3 COUNT()

select count(*) from member; -- 전체 행 개수
select count(phone1) "연락처가 있는 회원" from member; -- phone1 칼럼에 값이 있는 행 개수

 

2.3.4 HAVING절: GROUP BY에서 사용하는 WHERE절

group by에서는 where절을 사용할 수 없고 having절을 사용해야 한다. 기능은 where절과 같다.

select mem_id "회원 아이디", sum(price*amount) "총 구매 금액" from buy
	group by mem_id having sum(price*amount)>1000;

 

ORDER BY는 GROUP BY절 뒤에 사용할 수 있다.

select mem_id "회원 아이디", sum(price*amount) "총 구매 금액" from buy
	group by mem_id having sum(price*amount)>1000
    order by sum(price*amount) desc;