테이블 만들기

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');

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');

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)
);

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');

RDBMS(관계형 데이터 베이스)

'member'와 'buy'에서 member_id가 관계를 갖는다.
member의 member_id는 buy에서 외래키가 되며 이러한 관계를 갖는 것을 RDBMS가 된다.
외래키만 5~6개씩 있는 테이블도 있음.

JOIN(INNER, LEFT, RIGHT, FULL OUTER)

1. INNER JOIN - A와 B의 공집합, 즉 겹치는 부분만 찾아줌
   예시
   SELECT *
   FROM member,buy # a,b모두 가져온다.
   WHERE member.member_id=buy.member_id # a의 아이디가 b에 있는 a 아이디와 같다면

1. INNER JOIN

SELECT *
FORM A
[INNER] JOIN B # INNER 생략가능
ON A.id=B.a_id
AND B.qty>30 # 이런식으로 조건 더 걸어줄 수 있음

외래키로 안묶여있어도 join가능

sql 알리아스해서 join하기

SELECT * 'A'
FROM A_MEMBER-CODE [AS] 'A'
[INNER] JOIN B_MEMBER_CODE [AS] 'B' # AS는 생략 가능
ON A.id=B.a_id

3개 JOIN시키기

SELECT *
FROM buy b
INNER JOIN product p ON b.product_id=p.id
INNER JOIN member m ON b.member_id=m.id

INNER JOIN 실습

1. member_id가 OMY인 member가 구매한 product_id를 가져옵니다.

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

3. 구매이력이 있는 (buy테이블에 데이터가 있는) 모든 member의 이름을 중복 제거하여 가져옵니다.
   

4. 구매 이력이 있는 member 테이블의 member_id, name, 구매한 product의 name,price를 가져옵니다.

5. 가장 많이 팔린 product의 name과 price를 가져옵니다.

2. OUTER JOIN (LEFT RIGHT JOIN)

left join- 왼쪽 데이터 모두 나옴
오른쪽과 겹치는 부분은 함께 나오고 겹치지 않는 부분은 null값

right join- 오른쪽 데이터 모두 나옴
왼쪽과 겹치는 부분은 함께 나오고 겹치지 않는 부분은 null값

full outer join- 왼쪽 오른쪽 전부 나오는데 겹치면 합치고 안겹치면 전부 null값으로

예시
SELECT *
FROM A
LEFT OUTER JOIN B ON A.id=B.A_id
#A값을 기준으로 없는 값은 null 정렬

LEFT, RIGHT JOIN 실습

1. member 테이블의 모든 사람이 (member_id)가 구매한 구매 양(qty)로 가져옵니다. 단, 구매 양이 없는 경우 null로 처리합니다. [member테이블을 왼쪽으로]
   

2. product 테이블에 있는 모든 품목(name)이 팔린 갯수(buy.qty)를 가져옵니다. 단, 비어있는 경우 NULL로 처리합니다.
   

3. 어떤 멤버(member_id)가 구매한 품목(product.name)과 구매 수량(buy.qty)를 가져옵니다. 단, 없는 경우 NULL로 처리합니다.
   

사용 툴 다운로드
https://dbeaver.io/download/

query연습

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

2. product의 name이 pine인 물품의 총 판매수량(buy.qty)를 가져오기

3번 못함

3. 구매횟수(COUNT)가 제일 많은 member의 member_id,이름,총 수량(SUM(buy.qty)) 가져오기

select m.member_id, m.name, sum(b.qty) as 'sum'
from member m
inner join buy b on b.member_id =m.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;

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

4번 강사님 풀이

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

6. member_id가 'TWC'인 member가 구매한 물품의 양 (buy.qt) 합계를 구합니다.
   

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

테이블 만들기

creat table [이름] (
컬럼1 [data][null/not null][unique][default]
컬럼2
컬럼3
primary key ('컬럼') # 컬럼1,2,3등에 직접 줘도 되지만 이렇게 적어도 가능
);

1. create

• creat table [이름] (
  컬럼1 [data][null/not null][unique][default]
  컬럼2
  컬럼3
  primary key ('컬럼') # 컬럼1,2,3등에 직접 줘도 되지만 이렇게 적어도 가능
  );

2. alter

• alter 테이블 명 [ADD|MODIFY|DROP][COLUMN|CONSTRAINT]
  - column엔 이름, 자료형, 제약조건들이 들어감
  ex) ALTER member ADD COLUMN email VARCHAH(20) NULL DEFAULT '0';
  ex) ALTER member MODIFY COLUMN email VARCHAH(50)
  ex) ALTER member DROP COLUMN email

3. drop -> table을 삭제시키는 것

DROP TABLE IF EXISTS member; #존재해야지 삭제 시킴.

원래있던 테이블에 FK추가 (외래키를 지정하는 것)
ALTER TABLE [테이블 명] ADD CONSTRAINT (이름:임의) FOREIGN KEY(참조 키값) REFERENCE 참조 테이블(테이블ID)

테이블 명: BUY
키 값:member_id
참조 테이블: member id, product id

새 테이블에 제약조건
CREATE TABLE buy(
1
2
FOREIGN KEY(컬럼명) REFERENCES 테이블명(레이블키)
);

(컬럼명)에 member_id
테이블명(레이블키)에 member(id)

• 회원탈퇴를 해서 member의 id가 사라질 경우 buy의 member_id는 어떻게 될까?
  -CASCADE 같이 없어진다.
  -RESTRICT 놔둔다
  -SET NULL
  -NO ACTION.
  -SET DEFAULT 컬럼 제약조건에 의해 default로 바꾸거나 아니면 null

테이블 작성해보기

• 학생(student)

  • id: 식별자 bigint auto_increment, NULL 허용 안함, PK
  • student_number: 학번 글자(20) NULL 허용 안함
  • name: 이름 글자(10) NULL 허용
  • password: 비밀번호 글자(20) NULL 허용 안함
  • email: 이메일주소 글자(50) NULL 허용
  • addr_big: 도 / 특별시 글자(20) NULL 허용
  • addr_middle: 시군구 글자(20) NULL 허용
  • addr_small: 나머지 주소 글자(100) NULL 허용
  • create_date: 생성일 datetime NULL 허용 안함 디폴트 값 current_timestamp
  • update_date: 수정일 datetime NULL 허용 안함 디폴트 값 current_timestamp
    

• 과목(subject)

  • id: 식별자 bigint auto_increment, NULL 허용 안함, PK
  • subject_name: 과목명 글자(20) NULL 허용 안함
  • description: 설명 글자(2000) NULL
  • parent_id : 부모 과목 식별자 bigint, subject의 id값 가져오기
  • create_date: 생성일 datetime NULL 허용 안함 디폴트 값 current_timestamp
  • update_date: 수정일 datetime NULL 허용 안함 디폴트 값 current_timestamp
    

• 수강신청 (enrolment)
  • id: 식별자 bigint auto_increment, NULL 허용 안함, PK
  • student_id: 학생 식별자 bigint NULL 허용 안함 학생 FK
  • subject_id: 과목 식별자 bigint NULL 허용 안함 과목 FK
  • create_date: 생성일 datetime NULL 허용 안함 디폴트 값 current_timestamp
  • update_date: 수정일 datetime NULL 허용 안함 디폴트 값 current_timestamp
    

• 시험 (exam)
  • id: 식별자 bigint auto_increment, NULL 허용 안함, PK
  • student_id: 학생 식별자 bigint NULL 허용 안함 학생 FK
  • subject_id: 과목 식별자 bigint NULL 허용 안함 과목 FK
  • score: 점수 DOUBLE NULL 허용 안함 디폴트 값 0.0
  • create_date: 생성일 datetime NULL 허용 안함 디폴트 값 current_timestamp
  • update_date: 수정일 datetime NULL 허용 안함 디폴트 값 current_timestamp
    

TRANSACTION (INSERT, UPDATE, DELETE)

buy와 product가 있을 때 10개를 사면 product에서 그만큼 빠져야함.
트렌젝션-어떤 작업이 끝까지 완료될 때 까지 데이터의 변경을 홀딩함. 데이터가 잘못 반영되는 것을 막기 위해 필요함.

1. commit-트렌잭션을 db에 반영한다.
2. rollback-트랜잭션 반영한 것을 취소한다.
3. 하는 방법
   • start transaction ~ commit | rollback;
   • set autocommit=0 오토커밋을 끄면 commit전 데이터를 반영할까 안할까를 결정

• 자원관리 방법
• 누군가가 아주 빠른 시간에 데이터를 조작해서 데이터가 잘못 반영되는 것을 막기 위해
• APP단에서 많이 사용되나 DB(MYSQL)에서 직접 써보려면 START TRANSACTION ~ 작업 ~ COMMIT | ROLLBACK

INDEX <- 성능

책 내용을 알기위해 INDEX 활용
데이터 베이스도 마찬가지 어떤 데이터가 어디 있고
위에 만든 테이블의 pk, fk도 제약조건을 걸어 인덱스가 걸림.
인덱스 원칙

• 가능한 작게 검색이 많이되는 컬럼에 거는 것이 원칙.
  인덱스의 용량이 큼
  index 만드는 법
  CREAT INDEX (인덱스 이름) ON 테이블(컬럼)
  ex) CREAT INDEX idx_member ON member(name)

mysql 데이터형, '혼공S P158'

1. 정수:소수점x INT, BIGINT(보통 ID에 사용), TIMEINT

2. 실수:소수점o FLOAT DOUBLE

3. 문자형: CHAR(고정) VARCHAR(가변)
   CHAR(4), VARCHAR(4) 일때 세글자 넣으면 CHAR의 경우 공백이 생김
   식별자, FLAG, 약어에서 CHAR를 사용
   is_use, delete_yn, category 등 y,n이나 o,x 등으로 데이터의 길이 CHAR(1)가 보장되어 있음.

4. 대량 데이터: TEXT.LONGTEXT, LONGBLOB 사용
   이미지 파일: 메모장으로 열면 여러 글자가 나옴 LONGTEXT를 사용하는 것
   LONGTEXT (문자 그대로), LONGBLOB (메모리 주소)

5. 날짜형: DATE(시간X), DATETIME(날짜+시간)
   NOW(), CURRENT_TIMESTAMP

문자형 변환, '혼공S P169'

1. CONVERT(컬럼|함수,데이터형)
2. CAST(컬럼|함수 AS 데이터형)

strudent_number: VARCHAR->INT

DATE_FORMAT(date, '%y-%m-%d)