[57일차 복습]mysql foreign key 외래키 설정/ left join / union / 특정 field 값 가져오기

mysql promt 창에서 create database class3; 생성

 

HeidiSQL 들어가서 비밀번호 입력 / 방금 만든 'class3' 클릭 

 

 

 

 

 

 

<- 요렇게 3개의 테이블을 만들 예정 

 

 

 

 

 

 

 

 

 

 

 

쿼리 클릭 table 생성하기 

CREATE TABLE curriculum(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	subject VARCHAR(100) NOT NULL,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	content TEXT
)CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI

desc 으로 해당 테이블 잘 생성되었는지 체크 

 

DATE_FORMAT  = date를 string으로 표현

STR_TO_DATE = String을 date로 표현 

초록색 - string 

빨강색 - date  - str_to_date은 %y 소문자 y가 안되는 것 같다. 

 

 

 

curriculum  table에 내용 넣기 x 3개 ! - 날짜를 다르게 

INSERT INTO curriculum(
	subject, start_date, end_date,content
)VALUES(
	'blockchain',
	STR_TO_DATE('2021-06-03', '%Y-%m-%d'),
	STR_TO_DATE('2021-06-21', '%Y-%m-%d'),
	'블록체인 내용' 
)

테이블 1개 완성 

 

---

 

해당 테이블에서 특정 값 가져오기 (table 1개인 경우) 

 

end_date = 7월인 것만 가져오기

SELECT * FROM curriculum WHERE DATE_FORMAT(end_date,'%m') ='07';

 

 

end_date = 7월1일 ~ 7월 31일인  것만 가져오기

SELECT * FROM curriculum WHERE end_date BETWEEN '2021-07-01' AND '2021-07-31';

 

 

 

curriculum table에서 start_date, end_date fields 만 가져오기 

SELECT start_date, end_date FROM curriculum;

 

 

start_date과 end_date fields 중 MONTH만 가져오기 

SELECT MONTH(start_date), MONTH(end_date) FROM curriculum;

 

start_date과 end_date fields 중 DAY만 가져오기 

SELECT DAY(start_date), DAY(end_date) FROM curriculum;

위의 방식대로 YEAR을 쓰면 2,021 숫자로 가져와 진다 (처음 테이블 만들 대 STR_TO_DATE으로 숫자형태로 만듬) 

SELECT YEAR(start_date), YEAR(end_date) FROM curriculum;

 

string type으로 년도 가져오기 

SELECT DATE_FORMAT(start_date,'%Y') FROM curriculum;

 

 

start_date, end_date 년도 가져오기 

SELECT DATE_FORMAT(start_date,'%Y'), DATE_FORMAT(end_date, '%Y') FROM curriculum;

 

 

 

 

UNION ALL 

SELECT start_date AS 'date' FROM curriculum 
UNION ALL
SELECT end_date AS 'date' FROM curriculum;

AS 의 의미는 명령에서 가져올 fields 값들의 새로운 field명을 만들어서 보여주겠다 라는 뜻 

 

 

select 1 -> 1을 field로 만들어서 보여줄게 (근데 field명이 없어서 data 값 ==1을 field명으로 만듬) 

select 1 as 'Number' -> 1이라는 data를 Number라는 field 명으로 보여줄게 

 

 

 

---

 

 

테이블 2개 더 만들기 

CREATE TABLE skill(
	curr_id INT NOT NULL,
	item_id INT NOT NULL
)CHARACTER SET UTF8 collate UTF8_GENERAL_CI;

CREATE TABLE skill_item(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(50) NOT NULL,
	TYPE VARCHAR(50) NOT NULL
)CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

skill_item 내용 채우기

INSERT INTO skill_item (NAME, TYPE) VALUES ('html', 'front-end');
INSERT INTO skill_item (NAME, TYPE) VALUES ('CSS', 'front-end');
INSERT INTO skill_item (NAME, TYPE) VALUES ('JavaScript', 'front-end');
INSERT INTO skill_item (NAME, TYPE) VALUES ('NodeJS', 'back-end');
INSERT INTO skill_item (NAME, TYPE) VALUES ('MariaDB', 'back-end');
INSERT INTO skill_item (NAME, TYPE) VALUES ('C++', 'Programming');
INSERT INTO skill_item (NAME, TYPE) VALUES ('Unity', 'Programming');

 

blockcahin = id 1번 

 

 

skill 내용 채우기

INSERT INTO skill (curr_id, item_id) VALUES(1,1);
INSERT INTO skill (curr_id, item_id) VALUES(1,2);
INSERT INTO skill (curr_id, item_id) VALUES(1,3);
INSERT INTO skill (curr_id, item_id) VALUES(1,4);
INSERT INTO skill (curr_id, item_id) VALUES(1,5);
INSERT INTO skill (curr_id, item_id) VALUES(2,6);
INSERT INTO skill (curr_id, item_id) VALUES(3,7);

 

 

만든 tables( curriculum, skill, skill_item) 전체 

 

 

SELECT * FROM skill WHERE curr_id=1; 

 

SELECT A.item_id FROM (SELECT * from skill WHERE curr_id=1) AS A; 

 

 

LEFT JOIN 사용 

SELECT * FROM (SELECT * from skill WHERE curr_id=1) AS A
LEFT JOIN skill_item AS B
ON A.item_id=B.id;
	

on -> 조건 (조건이 같은만큼 rows 수가 같아서 가져올 수 있다. ) 

여기서 name, type만 보여주고 싶으면

 

SELECT B.NAME, B.type FROM (SELECT * from skill WHERE curr_id=1) AS A
LEFT JOIN skill_item AS B
ON A.item_id=B.id;

name, type만 적어도 되지만 A, B 두개의 테이블 중 같은 field 이름이 있을 수도 있으니 어떤 테이블의 field명인지 적어주는게 좋음! ex) B.name 

 

 

 

Foreign KEY  외래키

외래키 사용 이유 : 참조하는 테이블에 데이터 무결성 보장 역할

 

외래키에 대한 좋은 글 

https://brunch.co.kr/@dan-kim/26#:~:text=%EC%99%B8%EB%9E%98%ED%82%A4%20%EC%97%AD%ED%95%A0,%ED%95%98%EB%8A%94%20%EC%97%AD%ED%95%A0%EC%9D%84%20%EC%88%98%ED%96%89%ED%95%A9%EB%8B%88%EB%8B%A4.

19. 외래키 이해하기

질문 : 외래키 안한 상태에서도 외래키 설정한 것과 어떻게 다른지 -> db 검증 

 

외래키 역할 : 두 테이블을 연결해주는 다리 역할 

새롭게 추가되는 table A의 row 행에서 외래키에 해당하는 값이 외래키가 참조하는 table에 존재하는지를 체크한다. -> 존재하지않으면 에러를 발생시켜서 해당 데이터의 삽입을 막는다. 

이 기능은 table 내, 저장되어 있는 데이터가 항상 참조하는 값이 있다는 것을 보장하는 역할을 한다. 

 

 

외래키가 없어도 쿼리문 작성은 되지만 외래키의 기능이 좋아서 쓰는게 좋다. 

id를 삭제할 때 item_id의 값도 같이 삭제될 수 있다.

테이블을 생성할 때 or 생성 후에 외래키를 만들 수 있다. 

skill이 curriculum과 skill_item 모두 이어준다. 

생성되어진 table - skill 에 외래키 만들기 

둘을 연결하는 변수 명이 skillcurrid 

ALTER TABLE skill
ADD CONSTRAINT skillcurrid
FOREIGN KEY (curr_id)
REFERENCES curriculum(id); 	

외래키 확인 방법 ↓↓

restrict - 잘못하게되면 삭제 안되도록 기능을 막음

cascade - 연관된 아이들 다 삭제 ( 부모 테이블 삭제되면 나도 삭제될게 ) 

 

 

이제 skill table에  skill_item 와 연결된 item_id 외래키 설정하기

ALTER TABLE skill
ADD CONSTRAINT skillitemid
FOREIGN KEY (item_id)
REFERENCES skill_item(id)
ON UPDATE CASCADE