자체 조인, MySQL 데이터 형식

자체 조인(self join)

-- 자신과 자신이 조인

-- 테이블은 1개만 사용

-- 예) 회사의 조직 관계

 

-- 데이터 생성

create table emp_table (emp char(4), manager char(4), phone varchar(8));

 

insert into emp_table values('대표', null, '0000');

insert into emp_table values('영업이사', null, '1111');

insert into emp_table values('관리이사', '대표', '2222');

insert into emp_table values('정보이사', '대표', '3333');

insert into emp_table values('영업과장', '영업이사', '1111-1');

insert into emp_table values('경리부장', '관리이사', '2222-1');

insert into emp_table values('인사부장', '관리이사', '2222-2');

insert into emp_table values('개발팀장', '정보이사', '3333-1');

insert into emp_table values('개발주임', '정보이사', '3333-2');

 

select * from emp_table;

 

-- 자체 조인의 기본 형식

 

select 열목록

from 테이블 별칭A

inner join 테이블 별칭b

on 조인될 조건

where 검색 조건;

 

-- 경리 부장의 직속 상관 연락처를 알고 싶다면

select a.emp 직원, b.emp 직속상관, b.phone 직속상관 연락처,

from emp_table a

inner join emp_table b

on a.manager = b.emp

where a.emp = '경리부장';

 

<<< 뭔가 코드가 잘못되었는지 에러가 뜬다................... 수정해서 업로드 해야겠다!!!!!

 

 

-- 연습문제

-- 1. buy 테이블과 member 테이블을 이용하여 한 번이라도 구매기록이 있는 회원들 리스트 추출하기

-- 조회할 열 : mem_id, addr.중복이 없도록.

 

select distinct m.mem_id, m.addr

from buy b

inner join member m

on b.mem_id = m.mem_id

order by mem_id;

 

 

이번 문제를 풀기 위해서는 aac outcomes.csv 자료가 필요하다.

그리고 추가로

 

CREATE TABLE aac_outcomes (

age_upon_outcome varchar(15) COLLATE utf8mb4_general_ci NOT NULL,

animal_id char(7) COLLATE utf8mb4_general_ci NOT NULL,

animal_type varchar(10) COLLATE utf8mb4_general_ci NOT NULL,

breed varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,

color varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,

date_of_birth datetime NOT NULL,

datetime datetime NOT NULL,

monthyear varchar(90) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,

name varchar(20) COLLATE utf8mb4_general_ci NOT NULL,

outcome_subtype varchar(25) COLLATE utf8mb4_general_ci NOT NULL,

outcome_type varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,

sex_upon_outcome varchar(20) COLLATE utf8mb4_general_ci NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

위의 코드 실행하기

 

-- 2. 천재지변으로 인해 데이터가 유실되었음.

-- 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 id와 이름을 id의 오름차순으로 조회

-- 조회할 열 : animal_id, name, outs.datetime, ins.datetime

 

select ins.animal_id, ins.name, outs.datetime, ins.datetime

from outs.datetime outs

inner join ins.datetime ins

on outs.animal_id = ins.animal_id

where ins.datetime is null

order by animal_id;

 

select outs.animal_id, outs.name, outs.datetime, ins.datetime

from aac_outcomes outs

left outer join aac_intakes ins

on outs.animal_id = ins.animal_id

where ins.animal_id is null

order by outs.animal_id asc;

 

 

-- 3. 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었음

-- 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 sql문을 작성하기

-- 단, 보호시작일이 빠른 순으로 조회해야함

-- 조회할 열 : animal_id, name, outs.datetime, ins.datetime

 

select ins.animal_id, ins.name, outs.datetime outtime, ins.datetime intime

from aac_intakes ins

inner join aac_outcomes outs

on ins.animal_id = outs.animal_id

where outs.datetime <= ins.datetime

order by ins.datetime asc;

 

-- 4. 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 sql문 작성하기

-- 이 때, 결과는 보호시작일 오름차순으로 조회

-- 조회할 열 : name, datetime

 

select ins.name, ins.datetime, outs.datetime

from aac_intakes ins

left outer join aac_outcomes outs

on ins.name = outs.name

where outs.datetime is null

order by outs.datetime desc

limit 3;

 

select ins.name, ins.datetime

from aac_intakes ins

left outer join aac_outcomes outs

on ins.animal_id = outs.animal_id

where outs.animal_id is null

and ins.name != '' -- 이름이 빈칸인 경우 제외(선택)

and ins.name not like '*%' -- 이름이 *로 시작하는 경우 제외(선택)

order by ins.datetime asc

limit 3;

 

-- 5. 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 함

-- 보호소에 들어올 당시에는 중성화되지 않았지만

-- 보호소를 나갈 당시에는 중성화된 동물의 데이터를 조회

-- 조회할 열 : animal_id, animal_type, name, sex_upon_intake, sex_upon_outcome

-- 정렬 순서 : animal_id 오름차순

 

select * from aac_intakes;

select ins.animal_id, ins.animal_type, ins.name, ins.sex_upon_intake, outs.sex_upon_outcome

from aac_intakes ins

inner join aac_outcomes outs

on ins.animal_id = outs.animal_id

where ins.sex_upon_intake != outs.sex_upon_outcome

order by ins.animal_id asc;

 

-- MySQL 데이터 형식

-- 데이터 형식은 크게 숫자형,문자형, 날짜형이 있음

-- 실제로 저장될 데이터의 형태가 다양하기 때문에

-- 이를 효율적으로 저장하기 위해 위의 데이터 형식에서 세부적으로 다시 여러 데이터 형식으로 나뉨

-- 예) 사람의 이름을 저장하기 위해 100글자를 저장할 칸을 준비하는 것은 비효율적임

 

-- 정수형

-- -- tinyint (숫자 범위 : -128 ~ 127)

-- -- smallint (숫자 범위 : -32768 ~ 32767)

-- -- int (숫자 범위 : 약 -21억 ~ 21억)

-- -- bright (숫자 범위 : 약 -900경 ~ 900경)

 

create table tbl_type (

tinyint_col tinyint,

smallint_col smallint,

int_col int,

bigint_col bigint); # 테이블을 만들 때부터 데이터 형식 지정

 

-- 각 열의 최댓값까지는 이상없이 입력 가능

insert into tbl_type values(127, 32767, 2147483647, 900000000000000000);

select * from tbl_type;

 

-- 최댓값에 0을 더 붙여서 입력해보기

insert into tbl_type values(1270, 327670, 21474836470, 9000000000000000000);

 

-- 문자형

-- 문자형은 글자를 저장하기 위해 사용

-- 입력할 최대 글자의 개수를 지정해야 함

-- -- char(개수)

-- -- varchar(개수)

 

-- char는 Character의 약자로, 고정길이 문자형이라고 부름

-- 자릿수가 고정됨

-- 예) char(10)에 "가나다" 3글자만 저장해도 10자리를 모두 확보한 후에 앞의 3자리만 사용하고 뒤의 7자리는 낭비하게 됨

 

-- varchar는 가변길이 문자형으로, "가나다" 3글자를 저장하면 3자리만 사용함(Variable Character)

 

-- vachar는 char보다 공간을 더 효율적으로 사용할 수 있지만 MySQL의 처리 속도는 CHAR가 더 빠름

-- 예) 거주 지역 컬럼에 서울/부산/경북/전남과 같이 모두 2글자로 일정한 경우에는 char(2)로 설정하는 것이 더 좋음

-- 반면에 가수 이름은 글자 수가 다양하기 때문에 varchar로 설정하는 것이 더 좋음

 

-- 위에서 배운 내용을 바탕으로 member 테이블 생성 코드를 다시 작성

create table member -- 회원 테이블

( mem id char(8) not null primary key, -- 사용자 아이디(pk)

mem_name varchar(10) not null, -- 이름

mem_number tinyint not null, -- 인원수

addr char(2) not null, -- 지역(경기, 서울, 경남 식으로 2글자만 입력)

phone1 char(3), -- 연락처의 국번(02, 031, 055 등)

phone2 char(8), -- 연락처의 나머지 전화번호(하이픈 제외)

height tinyint unsigned, -- 평균 키 # 부호를 뗀다

debut_date date); -- 데뷔 일자