준비

데이터 추가

테이블 추가

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_idOMYMember가 구매한 product_id를 가져온다
SELECT
	product_id
FROM
	MEMBER mem
INNER JOIN buy ON
	mem.id = buy.member_id
WHERE
	mem.member_id = 'OMY';
  • product 테이블의 namecarrot의 구매량(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, 구매한 productname, 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;
  • 가장 많이 팔린 productnameprice를 가져오기(중복 제거)
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
);
  • productnamepine인 뭎품의 총 판매수량(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 테이블에서 가장 비싼 물건을 구매한 membermember_idname을 가져온다
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에서 사는 사람이 구매한 모든 productnameprice를 가져온다
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_idTWCmember가 구매한 물품의 양 합계를 구한다
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_idWMN가 구매한 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

profile
DA DE DS

0개의 댓글