002_SQL 입문[MySQL]

LBR56·2023년 2월 7일

DDL Index JOIN RDBMS TCL dbeaver 개발자 데이터 타입 빅데이터 천재교육 천재교육IT교육센터 코딩

준비

데이터 추가

테이블 추가

CREATE TABLE `buy` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `member_id` bigint DEFAULT NULL,
  `product_id` bigint DEFAULT NULL,
  `qty` int DEFAULT NULL,
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

`buy` 추가

INSERT INTO testdb.buy (member_id,product_id,qty,create_date,update_date) VALUES
	 (1,1,10,'2023-02-07 08:24:33','2023-02-07 08:24:33'),
	 (1,2,30,'2023-02-07 08:29:48','2023-02-07 08:29:48'),
	 (2,1,10,'2023-02-07 08:29:58','2023-02-07 08:29:58'),
	 (5,2,10,'2023-02-07 08:30:07','2023-02-07 08:30:07'),
	 (6,8,5,'2023-02-07 08:30:30','2023-02-07 08:30:30'),
	 (3,3,4,'2023-02-07 08:30:39','2023-02-07 08:30:39'),
	 (3,5,10,'2023-02-07 08:30:50','2023-02-07 08:30:50'),
	 (4,4,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
	 (5,2,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
	 (4,1,20,'2023-02-07 08:38:40','2023-02-07 08:38:40');
INSERT INTO testdb.buy (member_id,product_id,qty,create_date,update_date) VALUES
	 (6,7,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
	 (9,4,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
	 (7,2,30,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
	 (1,7,20,'2023-02-07 08:38:40','2023-02-07 08:38:40');

`member` 추가

INSERT INTO testdb.`member` (member_id,name,address,phone_number,create_date,update_date) VALUES
	 ('TWC','트와이스','Seoul','010-1111-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('BLK','블랙핑크','Seoul','010-1111-2222','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('WMN','여자친구','Daegu','010-1111-3333','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('OMY','오마이걸','Daegu','010-1111-4444','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('GRL','소녀시대','Daegeon','010-1111-5555','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('ITZ','잇지','Daegeon','010-2222-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('RED','레드밸벳','Daegeon','010-2222-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('APN','에이핑크','Busan','010-2222-2222','2023-02-06 14:04:08','2023-02-06 14:04:08'),
	 ('SPC','우주소녀','Junnam','010-2222-2222','2023-02-06 14:04:08','2023-02-06 14:04:08');

`product` 추가

INSERT INTO testdb.product (name,qty,price,create_date,update_date) VALUES
	 ('carrot',15,1000,'2023-02-06 13:54:11','2023-02-06 13:54:11'),
	 ('apple',105,500,'2023-02-06 13:54:11','2023-02-06 13:54:11'),
	 ('pear',35,800,'2023-02-06 13:54:11','2023-02-06 13:54:11'),
	 ('orange',55,1000,'2023-02-06 13:54:11','2023-02-06 13:54:11'),
	 ('honey',15,3000,'2023-02-06 13:54:11','2023-02-06 13:54:11'),
	 ('pine',25,5000,'2023-02-06 13:54:11','2023-02-06 13:54:11'),
	 ('mellon',15,10000,'2023-02-06 13:54:11','2023-02-06 13:54:11');

RDBMS(Relational DataBase Management System)

관계형 데이터 베이스
- 관계를 맺기 위해 기본키(PK)와 FK(외래키)를 사용함
FK(외래키)를 걸어주면 관계가 있음을 보장할 수 있음
- 관계로만 이루어진 Table도 만들 수 있음(바람직 X)

Foreign Key

데이터 참조 무결성을 만족시켜줌
데이터 참조 무결성을 위해 수정(ON UPDATE)와 삭제(ON DELETE)에 옵션이 존재함
- CASCADE
  - 부모 따라 자식도
- RESTRICT
  - 자식이 있으면 부모 변경 및 삭제 불가
- SET NULL
  - 부모가 변하면 NULL
- SET DEFAULT
  - 부모가 변하면 DEFAULT
- NO ACTION
  - 아무것도.. 안함

JOIN

위 관계를 토대로 연결을 만들어 냄

INNER JOIN

SELECT
	pro.name, 
	# 겹치는 컬럼은 무조건 테이블을 명시해야 함
	buy.qty 
FROM
	# AS 생략 가능
	product AS pro,
	buy
WHERE
	pro.id = buy.product_id;

# 또는

SELECT
	pro.name, 
	# 겹치는 컬럼은 무조건 테이블을 명시해야 함
	buy.qty 
FROM
	# AS 생략 가능
	product pro
# INNER를 뺄 수 있지만 빼지 말자(약속)
INNER JOIN buy ON
	pro.id = buy.product_id;

FK가 아니더라도 JOIN이 가능함
다양한 테이블 함

SELECT
	pro.name,
	buy.id,
	mem.name
FROM
	product AS pro
INNER JOIN buy ON
	pro.id = buy.product_id
INNER JOIN MEMBER mem ON
	mem.id = buy.member_id;

OUTER JOIN

LEFT, RIGHT

SELECT
	pro.name,
	buy.qty
FROM
	product pro
# RIGHT OUTER JOIN buy ON
LEFT OUTER JOIN buy ON
	pro.id = buy.product_id;

DBeaver

설정을 변경하지 말고 설치함

DDL(Data Definition Language)

CREATE

CREATE TABLE 테이블이름(
	컬럼1 [data] [NULL|NOT NULL] [UNIQUE] [DEFAULT] [PRIMARY KEY] [AUTO_INCREMENT],
	컬럼2,
  [PRIMARY KEY (컬럼1)]
	[FOREIGN KEY)(컬럼2) REFERENCES 테이블 명(key)] # CONSTRAINT를 알 수 없음
)

ALTER

# 추가 변경 삭제
ALTER TABLE 테이블이름 [ADD | MODIFY| DROP] [컬럼|인덱스|자료형|CONSTRAINT|]

# 추가
ALTER TABLE member ADD COLUMN email VARCHAR(20); # DEFAULT '0';
# 변경
ALTER TABLE member MODIFY COLUMN email VARCHAR(20) DEFAULT '0';
# 삭제
ALTER TABLE member DROP COLUMN email;

# FK 추가 예시
ALTER TABLE 테이블명 ADD CONSTRAINT 관계이름 FOREIGN KEY (테이블_FK) REFERENCES (테이블.PK)

DROP

# FK가 걸려있으면 문제가 생김
DROP TABLE IF EXISTS member; # member라는 테이블이 존재하면 삭제

TCL(Transaction Control Language)

구매와 판매가 동시에 될 때

A와 B가 동시에 업무를 진행하면, 값이 잘못 변경 될 수 있음
어떤 작업이 끝날 때까지 변경을 미룸 == 트랜젝션)
보통 INSERT, UPDATE, DELETE에 사용

ACID

원자성(Atomicity)
- 모든 작업이 수행되거나, 하나도 수행되지 않거나
일관성(Consistency)
- 무결성을 만족해야함(FK)
격리성(Isolation)
- A 트랜젝션이 B 트랜젝션을 관여할 수 없음
내구성(Durability)
- DB 정보는 영구 보존되야 함

명령어

START TRANSACTION

commit과 rollback이 될 수 있도록 함

COMMIT

지금까지 진행한 명령어를 DB에 반영함

ROLLBACK

지금까지 진행한 명령어를 취소함

SET AUTOCOMMIT 설정

안하는게 좋음

INDEX

데이터 베이스 ← 100만건 매우 느림
특정 column에서 성능이 높아지지만, 많으면 좋지 않음
- 많을수록 기본 검색과 동일한 성능

CREATE INDEX 인덱스_이름 ON 테이블(컬럼);

데이터 타입 복습 및 추가 학습

숫자

정수(소수점 X)

~~TINYINT~~
INT
BIGINT(id에 많이 사용)

실수

~~FLOAT(7자리)~~
DOUBLE(15자리)

문자

CHAR(고정)[식별자, FLAG, 약어]
- is_use, delete_yn ← [‘y’, ‘n’], [‘o’, ‘x’], [0, 1]
- category ← ‘C’, ‘M’, ‘D’
VARCHAR(가변)

대량 데이터

TEXT, LONGTEXT(대본, 자막)
BLOB(Binary Long Object), LONGBLOG(사진, 음성, 영상)
- 정보를 특정 타입으로 변경함

날짜

DATE (시간 X)
DATETIME 날짜 + 시간
- NOW(), CREATE_TIMESTAMP 등 존재

형변환

CONVERT
- CONVERT(컬럼 또는 함수, 데이터형);
CAST
- CAST(컬럼 또는 함수 AS 데이터형);
DATE_FORMAT
- DATE_FORMAT(날짜 데이터, ‘%y-%m’);

실습

JOIN

INNER

member_id가 OMY 인 Member가 구매한 product_id를 가져온다

SELECT
	product_id
FROM
	MEMBER mem
INNER JOIN buy ON
	mem.id = buy.member_id
WHERE
	mem.member_id = 'OMY';

product 테이블의 name이 carrot의 구매량(buy에 있는 qty)을 합산해서 가져온다

SELECT
	SUM(buy.qty)
FROM
	product pro
INNER JOIN buy ON
	pro.id = buy.product_id
WHERE
	pro.name = 'carrot';

구매 이력이 있는 모든 member의 이름을 가져오기(중복 제거)

SELECT
	DISTINCT
	mem.name
FROM
	MEMBER mem
INNER JOIN buy ON
	mem.id = buy.member_id;

구매 이력이 있는 member 테이블의 member_id, name, 구매한 product의 name, price를 가져오기

SELECT
	mem.member_id,
	mem.name,
	pro.name,
	pro.price
FROM
	MEMBER mem
INNER JOIN buy ON
	mem.id = buy.member_id
INNER JOIN product pro ON
	pro.id = buy.product_id;

가장 많이 팔린 product의 name과 price를 가져오기(중복 제거)

SELECT
	DISTINCT
	pro.name,
	pro.price,
	buy.qty
FROM
	product pro
INNER JOIN buy ON
	pro.id = buy.product_id
WHERE
	buy.qty = (
	SELECT
		MAX(qty) qty
	FROM
		buy
	);

SELECT
	pro.name,
	pro.price,
	SUM(buy.qty)
FROM
	product pro
INNER JOIN buy ON
	pro.id = buy.product_id
GROUP BY
	pro.name,
	pro.price
ORDER BY
	SUM(buy.qty) DESC
LIMIT 1;

LEFT, RIGHT

member 테이블의 모든 사람이 구매한 구매 양을 가져온다
- 구매가 없으면 NULL

SELECT
	mem.member_id,
	SUM(buy.qty)
FROM
	MEMBER mem
LEFT OUTER JOIN buy ON
	mem.id = buy.member_id
GROUP BY
	mem.member_id;

product 테이블에 있는 모든 품목(name)이 팔린 갯수(qty)를 가져온다
- 비어있으면 NULL

SELECT
	pro.name,
	SUM(buy.qty)
FROM
	product pro
LEFT OUTER JOIN buy ON
	pro.id = buy.product_id
GROUP BY
	pro.name;

어떤 맴버(member_id)가 구매한 품목(product.name)과 구매 수량(buy. qty)를 가져온다
- 비어있으면 NULL

SELECT
	mem.member_id,
	pro.name,
	buy.qty
FROM
	MEMBER mem
LEFT OUTER JOIN buy ON
	mem.id = buy.member_id
LEFT OUTER JOIN product pro ON
	pro.id = buy.product_id;

난이도 상승

member 테이블이 있는 member_id가 TWC인 사람이 구매한 물품중에 가장 비싼 물품(product의 price가 가장 높은)의 name, price

SELECT 
	p.name ,
	p.price
FROM 
	`member` m
INNER JOIN buy b ON
	m.id = b.member_id
INNER JOIN product p ON
	p.id = b.product_id
WHERE
	m.member_id = 'TWC'
ORDER BY 
	p.price DESC
LIMIT 1;

＃ 또는
WHERE
	1 = 1
	AND m.member_id = 'TWC'
	AND p.price = (
	SELECT
		MAX(price)
	FROM
		product
);

product의 name이 pine인 뭎품의 총 판매수량(buy.qty)

SELECT 
	sum(b.qty)
FROM
	buy b
INNER JOIN product p ON 
	b.product_id = p.id
WHERE 
	p.name = 'pine';

단골고객(구매 횟수가 제일 많은 member)의 member_id, 이름, 총 수량

SELECT 
	m.member_id,
	m.name,
	sum(b.qty)
FROM
	`member` m
INNER JOIN buy b ON
	m.id = b.member_id
WHERE 
	b.member_id = (
	SELECT
		member_id
	FROM
		(
		SELECT
			member_id ,
			count(1) AS cnt
		FROM
			buy
		GROUP BY
			member_id
		ORDER BY
			cnt DESC
		LIMIT 1
		) AS cnt2
	)
GROUP BY
	m.member_id ,
	m.name ;

# 또는

SELECT 
	m.member_id,
	m.name,
	SUM(b.qty)
FROM
	buy b
INNER JOIN `member` m ON
	b.member_id = m.id
GROUP BY 
	m.member_id ,
	m.name
ORDER BY 
	SUM(b.qty) DESC
LIMIT 1
;

추가 Query

product 테이블에서 가장 비싼 물건을 구매한 member의 member_id와 name을 가져온다

SELECT
	m.member_id ,
	m.name
FROM
	product p
INNER JOIN	buy b ON p.id = b.product_id 
INNER JOIN	`member` m ON	b.member_id = m.id
WHERE 
	p.price = (
	SELECT 
		max(p2.price)
	FROM
		product p2 
	);

member 테이블에서 Daegu에서 사는 사람이 구매한 모든 product의 name과 price를 가져온다

SELECT
	p.name,
	p.price
FROM
	product p
	INNER JOIN	buy b ON	
	p.id = b.product_id
INNER JOIN `member` m ON
	b.member_id = m.id
WHERE 
	m.address = 'Daegu';

member_id가 TWC인 member가 구매한 물품의 양 합계를 구한다

SELECT
	SUM(b.qty)
FROM
	product p
	INNER JOIN buy b ON p.id = b.product_id
	INNER JOIN	`member` m ON b.member_id = m.id
WHERE 
	m.member_id = 'TWC'；

member_id가 WMN가 구매한 product의 모든 정보를 가격(price)이 비싼 순으로 정렬한다

SELECT
	p.*
FROM
	product p
INNER JOIN	buy b ON	p.id = b.product_id
INNER JOIN `member` m ON	b.member_id = m.id
WHERE 
	m.member_id = 'WMN'
ORDER BY 
	p.price DESC;

테이블 작성하기

학생 테이블

CREATE TABLE student(
	id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	student_number VARCHAR(20) NOT NULL ,
	name VARCHAR(10) DEFAULT NULL,
	password VARCHAR(20) NOT NULL ,
	email VARCHAR(50) DEFAULT NULL,
	addr_big VARCHAR(20) DEFAULT NULL,
	addr_middle VARCHAR(20) DEFAULT NULL,
	addr_small VARCHAR(100) DEFAULT NULL,
	create_date DATETIME NOT NULL DEFAULT current_timestamp,
	update_data DATETIME NOT NULL DEFAULT current_timestamp
);

과목 테이블

CREATE TABLE subject(
	id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	subject_name VARCHAR(20) NOT NULL ,
	description VARCHAR(2000) DEFAULT NULL,,
	parent_id BIGINT DEFAULT NULL,,
	create_date DATETIME NOT NULL DEFAULT current_timestamp,
	update_data DATETIME NOT NULL DEFAULT current_timestamp
);
ALTER TABLE subject ADD CONSTRAINT subject_FK FOREIGN KEY (parent_id) REFERENCES subject(id);

수강신청 테이블

CREATE TABLE enrolment(
	id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	student_id BIGINT NOT NULL,
	subject_id BIGINT NOT NULL,
	create_date DATETIME NOT NULL DEFAULT current_timestamp,
	update_data DATETIME NOT NULL DEFAULT current_timestamp,
	FOREIGN KEY (student_id) REFERENCES student(id), # 가장 편함
	KEY enrolment_FK_1 (subject_id),
	CONSTRAINT enrolment_FK_1 FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`)

);
# ALTER TABLE enrolment ADD CONSTRAINT enrolment_FK FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
# ALTER TABLE enrolment ADD CONSTRAINT enrolment_FK_1 FOREIGN KEY (subject_id) REFERENCES subject(id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;

시험 테이블

CREATE TABLE exam(
	id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	student_id BIGINT NOT NULL,
	subject_id BIGINT NOT NULL,
	score DOUBLE NOT NULL DEFAULT 0.0,
	create_date DATETIME NOT NULL DEFAULT current_timestamp,
	update_data DATETIME NOT NULL DEFAULT current_timestamp
);
ALTER TABLE exam ADD CONSTRAINT exam_FK FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE exam ADD CONSTRAINT exam_FK_1 FOREIGN KEY (subject_id) REFERENCES subject(id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;

회고

JOIN의 CROSS는 어디에 사용하는지 궁금함
DBeaver라는 GUI IDE를 통해 다양한 SQL를 접근하여 사용함
다양한 실습을 통해 한 명령어가 다양하게 해석될 수 있음을 앎
- 효율적인 SQL문이 어떤 것인지 모르겠음

Ref

LBR56

DA DE DS

이전 포스트

001_SQL 입문[MySQL]

다음 포스트

002_SQL 입문[MySQL]

준비

데이터 추가

테이블 추가

buy 추가

member 추가

product 추가

RDBMS(Relational DataBase Management System)

Foreign Key

JOIN

INNER JOIN

OUTER JOIN

LEFT, RIGHT

DBeaver

DDL(Data Definition Language)

CREATE

ALTER

DROP

TCL(Transaction Control Language)

구매와 판매가 동시에 될 때

ACID

명령어

START TRANSACTION

COMMIT

ROLLBACK

SET AUTOCOMMIT 설정

INDEX

데이터 타입 복습 및 추가 학습

숫자

정수(소수점 X)

실수

문자

대량 데이터

날짜

형변환

실습

JOIN

INNER

LEFT, RIGHT

난이도 상승

추가 Query

테이블 작성하기

학생 테이블

과목 테이블

수강신청 테이블

시험 테이블

회고

Ref

001_SQL 입문[MySQL]

003_SQL 입문[MySQL]

0개의 댓글

`buy` 추가

`member` 추가

`product` 추가