- 새 스키마 추가하는 방법
MySQL WorkBench로 샘플 데이터 다운로드 및 불러오기
테이블 조회
SELECT
: (with COUNT(*), MIN(), MAX(), AVG(), 기타 다양한 식 적용 가능, ROUND(value,2), as(Alias))조회할 데이터 (열로 보여주기, 복수 지정 가능)
COALESCE(컬럼, '0')
: SELECT 내에서 NULL인 경우에 '0'을 넣음
LEFT, MID(SUBSTR, SUBSTRING), RIGHT
LEFT : 문자에 왼쪽을 기준으로 일정 갯수를 가져오는 함수.
MID : 문자에 지정한 시작 위치를 기준으로 일정 갯수를 가져오는 함수.
RIGHT : 문자에 오른쪽을 기준으로 일정 갯수를 가져오는 함수.
- 참고로 MID 함수는 SUBSTR과 SUBSTRING 함수의 동의어입니다.
WHERE
: (with LIKE (with '%'), AND, OR) (행에서 조건절 부여, 복수 지정 가능)
GROUP BY
: 동일한 내용으로 묶고 싶은 열 선택 -> SELECT에도 적어주기
HAVING
: GROUP BY와 함께 사용하여야 하며 집계 함수를 사용하여 조건절을 작성하거나 GROUP BY 컬럼만 조건절에 사용
예시) 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보는 코드를 작성해주세요.
SELECT payment_method, COUNT(*) as 주문건수 FROM orders
WHERE email LIKE '%naver.com' and course_title = '앱개발 종합반'
group by payment_methodORDER BY
: 원하는 순서대로 정렬 (default = ASC(오름차순), DESC(내림차순), 숫자가 들어가면 해당 컬럼으로 기준으로 진행 (ex, order by 1 desc, 2)
- UPDATE, DELETE를 하기 위해서는 MySQLWorkbench - settingㄴ(preferences) - SQL Editor - Safe Updates - 체크 해제 - MySQLWorkbench 재실행
-> 체크 표시가 되어있으면 UPDATE, DELETE가 불가
LIMIT
: 나타내고 싶은 개수 제한
테이블 수정/삭제
CREATE
CREATE TABLE users (id INT, name CHAR(4), age INT);INSERT
: 기존 테이블과 필드 개수가 맞지 않으면 오류 발생, 빈 자리는 NULL로 채워주거나 필드 이름을 지정해야 함
insert into my_city select Null, name, population from world.city;insert into my_city (city_name, population) select name, population from world.city;UPDATE
update my_city set city_name = '서울' where city_name = 'seoul';DELETE
: where 조건절에 따라 지움, DELETE 뒤에 WHERE 없으면 다 지워짐! 주의!
delete from my_city where city_name like 'new%';TRUNCATE
: 테이블 내부의 자료를 모두 지움, 버림에도 사용됨 (ex, TRUNCATE(숫자,버릴 자릿수))
truncate table my_city;[mysql] ROUND(),TRUNCATE() - 반올림과 버림
DROP
: 테이블을 지움
truncate table my_city;테이블 조인
INNER JOIN 내부 조인
: 두 테이블을 연결 (교집합), 양쪽에 모두 포함된 내용만 추출
- ex) 상품을 구매한 유저 정보만 추출
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블> -- INNER JOIN은 'INNER' 생략가능
ON <조인될 조건>
[WHERE 검색 조건] SELECT B.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B -- 별칭을 달아서 간편하게 불러올 수 있음
JOIN member M
ON B.mem_id = M.mem_id;
WHERE B.mem_name = 'BLK'<LEFT / RIGHT / FULL> OUTER JOIN 외부 조인
: 원하는 쪽의 데이터를 모두 포함 가능
- ex) 상품을 구매하지 않은 유저까지도 추출
SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
ON <조일될 조건>
[WHERE 검색 조건];💡 LEFT OUTER JOIN 문의 의미는 ‘왼쪽 테이블의 내용은 모두 출력되어야 한다’ 정도로 이해
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
WHERE B.prod_name IS NULL -- 구매 이력이 없는 회원 조회
ORDER BY M.mem_id- 그 밖의 조인
- 자주 쓰이지 않음, 나중에 필요할 때 찾아보고 사용할 것들
- CROSS JOIN 상호 조인
- SELF JOIN 자체 조인
참조: MySQL - JOIN 정리 (INNER, OUTER, CROSS, SELF JOIN)
UNION ALL
(
select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2 // '7월' as month 처럼 select 내부에서 만들기도 가능
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week // union 안에서는 기능하지 않음
)
union all
(
select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at > '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week // union 안에서는 기능하지 않음
)-> order by는 union 내부에서 기능하지 않음을 기억!
WITH: 내일 정리 예정
with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
), table2 as (
select course_id, count(*) as cnt_total from orders
group by course_id
)
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id-> 위 쿼리가 실행되는 순서: with → from → join → select
제약 조건
Primary Key 제약조건
: 기본 키 설정
mem_id char(8) not null primary key
--or
primary key (mem_id)
--or
alter table member add constraint primary key (mem_id);Foreign Key 제약조건
: 외래 키 설정
foreign key(mem_id) references member(mem_id)
--or
alter table buy add foreign key (mem_id) references member (mem_id);- 외래키 설정 시 참조되는 member 테이블에서는 mem_id를 update, delete 불가능
- 참조하는 buy 테이블에서는 delete만 가능, update 불가능
on update cascade, on delete cascade
: 참조하는 테이블에 추가하면 update, delete 시에 양쪽에 적용됨
on update cascade
on delete cascadeUnique 제약조건
: 중복되지 않는 유일한 값만 입력 가능
email char(30) null uniqueCheck 제약조건
: 조건에 만족 되는 값만 입력 가능
height tinyint unsigned null check (height >= 100)Default 정의
: 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정
phone1 char(3) default '02'Null값 허용
: NULL(허용) or NOT NULL
mem_name varchar(10) not null날짜,시간 관련
[mysql]날짜 관련 함수 모음
[MySQL] 날짜, 시간 표기 방식 지정하기 DATE_FORMAT()
에러 모음
- control + return(enter) 눌러도 오류 발생!
-> 그 이유는 8번째 줄의 줄 바꿈 때문에 함께 인식되지 않았기 때문!
-> 줄바꿈을 없애주기 or 전체 선택 후 control + return(enter)하면 작동됨
