MySQL 명령어 요약

기본 테이블 명령

create table 테이블명(     --테이블 생성
	컬럼 int primary key,
	컬럼 float,
	컬럼 char(20),
	컬럼 varchar(20),
	컬럼 date,
	foreign (속성명)
		references 테이블명(컬럼)	--출처
		ON DELETE CASCADE	--삭제 동시 적용(set null. 삭제될 시 null로 변경. update도 동일)
		ON UPDATE CASCADE	--업뎃 동시 적용
	);
drop table 테이블명		--테이블 삭제

show tables;     --테이블 보기
show create table 테이블명;    --테이블 조건 보기
explain 테이블명;     --테이블 구조 보기

alter table 테이블명 add 컬럼 데이터타입;	--테이블에 컬럼 추가
alter table 테이블명 drop 컬럼;	--테이블에 컬럼 삭제
alter table 테이블명 modify 컬럼 데이터타입;	--테이블 컬럼 데이터타입 변경. 제약조건 추가 가능.
alter table 테이블명 rename 새테이블명	--테이블명 바꾸기
alter table 테이블명 change 원래컬럼 새컬럼 데이터타입	--컬럼명 바꾸기

create table 새테이블명 select * from 원본테이블명;	--내용 복사(프라이머리 기본키 사라짐)
create table 새테이블명 like 원본테이블명;	--스키마만 복사됨(프라이머리 기본키 남아있음)

insert into 테이블명 values (내용,내용...), ();	--튜플 추가(데이터 빼고 넣을 땐 넣는 부분만 테이블명 뒤에(컬럼,컬럼...) 작성)
update 테이블명 SET 컬럼 = 24 WHERE id = 1;	--튜플 수정
delete from 테이블명 where 컬럼=7;    --테이블의 7이 입력된 튜플 삭제

 

데이터 검색

select 찾는요소
from 테이블명
where 조건문
group by 컬럼	--속성명으로 묶어서 정리(컬럼의 위치를 적어도 됨)
order by 컬럼 asc	--desc 내림차순
limit 숫자	--조회할 개수 제한
having 검색조건

 

SELECT절 조건

-- select 이후에 원하는 찾는 요소를 작성할 때의 조건

select distinct 컬럼	--컬럼내 중복 제거
select * --전체
select 컬럼 as "지정이름" --지정이름으로 속성명을 변경해 띄움

--집계함수
select count(*)
select sum(*)
select avg(*)
select min(*)
select max(*)

--검색 시 데이터타입 변경
cast(if(rating='Not given', '1', rating) as decimal)	--숫자로 변경
concat(restaurant_name, '-', cast(order_id as char))	--문자로 변경
date(컬럼)	--날짜로 변경

 

WHERE절 조건

-- where절의 조건 입력하는 방법
select d from d

where 컬럼 = "내용"	--부등호, 등호 등 비교연산자 <>(다르다)
where 컬럼 between 숫자 and 숫자	--두 숫자 사이 찾기
where 컬럼 in (숫자, 숫자, 숫자)	--각 숫자 or 찾기(숫자외 텍스트 가능)
where 컬럼 like "김%"	--%는 원하는 곳에 붙여 김으로 시작하는 모든 문자 찾기(어느 방향 가능)

where exists (select * from orders where cust.cid = orders.cid) --존재함? 모두 안 함?(not exists도 가능)

where orders.cid is null;	--null만 조회하는 조건


--여러 개의 조건으로 필터링하기
select d from d
where student=1 and student=2
where student=1 or student=2

where not student=2	--부정문

 

여러 조건으로 검색하기

--이름을 변경해서 표시
--replace(컬럼, '원텍스트', '변경텍스트')
select restaurant_name "원래 상점명",
       replace(restaurant_name, 'Blue', 'Pink') as "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

--원하는 문자만 남기기
--substr(컬럼, 시작문자숫자, 문자개수) 시작문자숫자부터 문자개수만큼 가져옴. 문자개수 생략 가능(전체)
select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

--여러 컬럼 문자 합치기
--concat(문자 나열) 텍스트나 컬럼 등을 작성해 한번에 보이게 함
select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

--값 대치
--coalesce(컬럼, 대치값) 컬럼에 있는 것을 전부 대치값으로 표시
coalesce(b.age, 20)

--테이블 컬럼명 변경 조건문

--if문
--if(조건, true일 경우, false일 경우)
select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

--case문
--case when 조건 then 컬럼 end
select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity in (2, 3) then price/quantity	--연산 가능
            else '기타' end "음식 단가"
from food_orders

 

서브쿼리문

select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from 
(
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            when price_per_plate between 5000 and 19999 then 0.01
            when price_per_plate between 20000 and 29999 then 0.02
            else 0.03 end ratio_of_add,
       price_per_plate
from 
	(
	select restaurant_name, avg(price/quantity) price_per_plate
	from food_orders
	group by 1
	) a
) b

 

JOIN

from cust, orders;

from book natural join ordrs;	--자연조인

--left조인(right는 기준 반대) 조인 테이블 전체내
select f.order_id,
		f.customer_id,
		f.restaurant_name,
		f.price,
		c.name,
		c.age,
		c.gender
from food_orders f left join customers c on f.customer_id=c.customer_id

--내부조인 - 조인 테이블 공통컬럼만
from 테이블1 inner join 테이블 on 조건
where cust.cid = orders.cid --이런 식으로 하게 되면 각각 테이블에서 cid가 같은 애들만 출력되게 됨

--외부조인
from cust left outer join orders on cust.cid = orders.cid

 

Pivot Table

--max함수 사용하지 않을 시, null값이 포함되어있으면 오류날 수 있음.
select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15",
       max(if(hh='16', cnt_order, 0)) "16",
       max(if(hh='17', cnt_order, 0)) "17",
       max(if(hh='18', cnt_order, 0)) "18",
       max(if(hh='19', cnt_order, 0)) "19",
       max(if(hh='20', cnt_order, 0)) "20"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc
--
select age,
	   max(if(gender='male', cnt_order, 0)) "male",
	   max(if(gender='female', cnt_order, 0)) "female"
from
(
select gender,
	   case when age between 10 and 19 then 10
	        when age between 20 and 29 then 20
	        when age between 30 and 39 then 30
	        when age between 40 and 49 then 40
	        when age between 50 and 59 then 50 end age,
	   count(1) cnt_order
from food_orders f inner join customers c on f.customer_id = c.customer_id
where age between 10 and 59
group by 1, 2
) a
group by 1
order by 1 desc

 

윈도우 함수

--구분기준칼럼으로 나누고, 그 안에서 순위기준칼럼으로 랭킹을 매김
rank() over (partition by 구분기준칼럼 order by 순위기준칼럼 desc) ranking
--
select cuisine_type, restaurant_name, cnt_order,
	   rank() over (partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type, restaurant_name, cnt_order,
	   rank() over (partition by cuisine_type order by cnt_order desc) ranking
from
	(
	select cuisine_type, restaurant_name, count(1) cnt_order
	from food_orders
	group by 1, 2
	) a
) b
where ranking<=3

--기준컬럼의 합계할컬럼의 합계를 구함, order by를 붙이면 순서대로 합계되는 과정을 출력함
sum(합계할컬럼) over (partition by 기준컬럼 order by 정렬컬럼 asc)
--
select cuisine_type, restaurant_name, cnt_order,
	   sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
	   sum(cnt_order) over (partition by cuisine_type order by cnt_order asc) cum_cuisine
from
	(
	select cuisine_type, restaurant_name, count(1) cnt_order
	from food_orders
	group by 1, 2
	) a
order by cuisine_type, cnt_order

 

날짜 데이터 다루기

--보기 좋게 포맷 변경
date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"

YEAR(date)
MONTH(date)
DAY(date)
DAYOFMONTH(date)
DAYOFWEEK(date) -- 요일 (1=일요일 ~ 7=토요일)

EXTRACT(YEAR FROM start_date)

CURDATE() --오늘
NOW()	--날짜+시간

DATE_ADD(date, INTERVAL 값 단위)	--값만큼 + 날짜
DATE_SUB(date, INTERVAL 값 단위)	--값만큼 - 날짜
DATEDIFF(날짜1, 날짜2)	--날짜1-날짜2

 

앞으로…

프로그래밍용 MySQL을 배워서 여러 검색 조건에 대해 배우지 않거나, 다른 분야로 공부한 게 좀 있었다.

복습도 되고 어려 조건에 대해 알아보는 방법도 알았지만, 너무 많아서 역시 헷갈린다.

자주 사용하는 건 어차피 사용하면서 외워지게 되어있으니, 천천히 해보려고 한다.

나중에 추가적으로 알게 되는 게 있으면 추가해 작성할 것이다.