MySQL Insert, Update, Delete, Join

EUNLEE·2022년 7월 20일
dbmysql카카오클라우드스쿨
0

카카오 클라우드 스쿨 1기 개발자 과정

목록 보기
12/14
post-custom-banner

2022.07.20

🐬 Database

  • Database
    • 데이터의 집합
    • 데이터가 모여있는 공간
  • 사용, 관리하기 위한 software ⇒ DBMS
  • Table
  • Index
  • View
    • select 구문
      • 보안
      • 효율성
  • Stored procedure
  • Trigger
    • Table에 부착,

🐬 SQL

  1. DML(Data Manuplation Language)
    1. INSERT
    2. SELECT
    3. UPDATE
    4. DELETE
  2. DDL(Data Definition Language)
    1. CREATE
    2. DROP
    3. ALTER
  3. DCL(Data Conrtol Language)
    1. GRANT
    2. DENY

🐬 SELECT

SELECT 구문의 구조

  • SELECT
  • FROM
  • WHERE
    • WHERE 절에는 집계함수(SUM, MAX, MIN, …)가 올 수 없다.
  • GROUP BY
  • HAVING
    • HAVING은 그룹에 대한 조건이 온다. 집계함수 가능.
  • ORDER BY
-- 집계함수(SUM(), COUNT(), MAX(), MIN(), AVG))

-- 전체 구매자가 구매한 물품 개수의 평균은 얼마인가요?
SELECT AVG(amount)
FROM buyTBL;

-- 각 사용자별로 한번 구매시 물건을 평균 몇 개 구매했는지 조회하세요!
SELECT userID, AVG(amount) AS '평균 구매 개수'
FROM buyTBL
GROUP BY userID;

-- 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력하세요!(userTBL)
-- 문제가 어려우면 subQuery로 생각을 좀 전환해보세요!
SELECT name, height
FROM userTBL
WHERE height = (
	SELECT MAX(height) FROM userTBL
) OR height = (
	SELECT MIN(height) FROM userTBL
);

-- 사용자별 총 구매금액이 1,000 이상인 사용자만 출력하세요!
SELECT userID, sum(price*amount) AS '총 구매 금액'
FROM buytbl
GROUP BY userID
HAVING sum(price*amount) >= 1000; -- HAVING은 그룹에 대한 조건!, WHERE절에는 집계함수가 올 수 없다!

🐬 Transaction

  • 작업의 최소단위
  • 임의로 설정할 수 있는 개념
  • 여러 SQL문장을 하나의 논리적인 단위인 Transaction으로 설정!

WHY? 왜 Transaction을 설정하나요??

  • ACID
    • ACID는 데이터베이스 내에서 일어나는 하나의 Transaction의 안전성을 보장하기 위해 필요한 성질이다.
    • A(Atomicity): 원자성
      • 한 트랜잭션의 연산들이 모두 성공하거나, 반대로 전부 실패되는 성질
      • “All or nothing”
    • C(Consistency): 일관성
      • 하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 한다.
      • 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다는 뜻
      • Transaction이 끝난 후 결과가 correct state 제약 조건을 충족해야 해요.(잔액은 0이상)
    • I(Isolation): 독립성
      • 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다는 뜻
      • Thread 동기화 처리
    • D(Durability): 영속성
      • 하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야하는 성질
      • 만약 런타임 오류나 시스템 오류가 발생하더라도, 해당 기록은 영구적이어야 한다는 뜻
      • Transaction이 정상적으로 종료되면 해당 결과가 2차 저장소에 영구적으로 저장

“이체”라는 작업

  • A계좌를 select
  • A계좌를 update
  • B계좌를 select
  • B계좌를 update

⇒ 4가지 작업이 일련으로 진행되면서 이체가 수행된다.

참고
[데이터베이스] 트랜잭션의 ACID 성질 - 하나몬

Transaction 설정

  • Commit (반영)
  • Rollback (무효화)

🐬 INSERT

USE sqldb;

CREATE TABLE testtbl1 (
	id		 INT,
    userName CHAR(3),
    age		 INT
);
SELECT * FROM testtbl1;
-- 일반적인 Insert 구문
INSERT INTO testtbl1 VALUES(1, '홍길동', 25);
-- 필요한 것만 선택해서 데이터 입력
INSERT INTO testtbl1(id, userName) VALUES (2, '이순신');
-- 순서 맞출 필요 없어요!
INSERT INTO testtbl1(userName, id) VALUES ('강감찬', 3);

-- auto_increment: 숫자형식만 가능. PRIMARY KEY, UNIQUE
CREATE TABLE testtbl2 (
	id		 INT AUTO_INCREMENT PRIMARY KEY, -- id는 자동으로 증가
    userName CHAR(3),
    age		 INT
);
SELECT * FROM testtbl2;
INSERT INTO testtbl2 VALUES(NULL, '홍길동', 25);
INSERT INTO testtbl2 VALUES(NULL, '김길동', 30);

ALTER TABLE testtbl2 AUTO_INCREMENT = 100;
INSERT INTO testtbl2 VALUES(NULL, '최길동', 28);

SET @@auto_increment_increment = 5; -- @@는 환경변수. default는 1인데 5로 변경함.
INSERT INTO testtbl2 VALUES(NULL, '박길동', 60);

🐬 UPDATE, DELETE

START TRANSACTION;
-- UPDATE 구문
UPDATE testtbl3
SET lname = '없음'
WHERE fname = 'Kyoichi';

SELECT * FROM testtbl3 WHERE fname = 'Kyoichi';
ROLLBACK;

-- DELETE 구문
DELETE
FROM testtbl3
WHERE fname = 'Kyoichi'
LIMIT 100;

🐬 MySQL Data Type (30개)

  • 숫자
    • INT: 4byte → -21억 ~ +21억
    • BIGINT: 8byte → -900경 ~ +900경
    • FLOAT: 4byte → 소수점 7자리 정도까지 표현(명확한 실수값이 아닌 근사값으로 저장)
    • DOUBLE: 8byte → 소수점 14자리 정도까지 표현(명확한 실수값이 아닌 근사값으로 저장)
    • 🌟 DECIMAL: 전체 자리수와 소수점 자리수를 정할 수 있다.
  • 문자

    • CHAR: 고정 길이(1~255)

    • VARCHAR: 가변 길이(1~65535)

    • LONGTEXT: 48

    • LONGBLOB : (long bynary objcet)

      💡 CLOB, BLOB → 일반적인 용어임
      C: Character
      B: Bynary
      LOB: Large Object 

CREATE DATABASE moviedb;

USE moviedb;

CREATE TABLE movieTBL (
	movie_id	 INT,
    movie_title	 VARCHAR(30),
    movie_script LONGTEXT,
    movie_film	 LONGBLOB
) DEFAULT CHARSET=utf8mb4;

-- 데이터를 입력 !
INSERT INTO movieTBL VALUES(1, '쉰들러 리스트', 
	LOAD_FILE('/Users/eun/Downloads/Schindler.txt'),
    LOAD_FILE('/Users/eun/Downloads/Schindler.mp4')
);

SELECT * FROM movieTBL;
-- 환경설정이 안되었기 때문에 정상적으로 동작하지 않는다.
-- 설정을 하고 오자. 
-- 다시 실행하면 정상적으로 insert가 수행된다.
DELETE FROM movieTBL;
-- 이미 저장되어 있는 대용량의 데이터를 다운로드 하려면
SELECT  movie_film
FROM movieTBL
WHERE movie_id=1
INTO DUMPFILE '/Users/eun/Downloads/Schindler1.mp4';

🐬 MySQL의 내장 함수

  • 문자열 함수
  • 날짜 함수
  • 기타 등등 많아요 ~~

🐬 JOIN

  • Inner Join
    • 일반적으로 JOIN이라고 하면 Inner Join을 가리킨다.
  • Outer Join: JOIN 조건을 만족하지 않는 행도 포함
    • LEFT
    • RIGHT
    • FULL
  • Self Join
  • Cross Join
    • 일반적으로 dummy data를 생성할 때.
USE sqldb;

-- 구매 테이블에서 'JYP'라는 아이디를 가진 사람이 구매한 물건을
-- 발송하기 위해서 이름과 주소가 필요해요! 이 정보는 userTBL에 존재해요!
-- 두 테이블을 결합해서 결과를 알아내야 해요!

-- 표준방식
SELECT B.userID, name, addr
FROM buyTBL B
	INNER JOIN userTBL U
    ON B.userID = U.userID
WHERE B.userID = 'JYP';

SELECT buyTBL.userID, name, addr
FROM buyTBL, userTBL
WHERE buyTBL.userID = userTBL.userID;

🐬 간단한 예제

-- 간단한 예제
USE sqldb;

-- 학생 테이블 생성
CREATE TABLE stdTBL(
	std_name	VARCHAR(10) NOT NULL PRIMARY KEY, -- 학생 이름
    std_addr	VARCHAR(4) NOT NULL -- 학생 주소
);

-- 동아리 테이블 생성
CREATE TABLE clubTBL(
	club_name	VARCHAR(10) NOT NULL PRIMARY KEY, -- 동아리 이름
    club_room	VARCHAR(4) NOT NULL -- 동아리 방번호
);

-- 학생 동아리 테이블 생성
CREATE TABLE stdclubTBL(
	num		 INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 인덱스 
    std_name VARCHAR(10) NOT NULL, -- 학생 이름
    club_name VARCHAR(10) NOT NULL, -- 동아리 이름
    FOREIGN KEY(std_name) REFERENCES stdTBL(std_name),  -- FK 설정
    FOREIGN KEY(club_name) REFERENCES clubTBL(club_name)  -- FK 설정
);

-- 데이터 입력
INSERT INTO stdTBL VALUES('김범수', '경남');
INSERT INTO stdTBL VALUES('성시경', '서울');
INSERT INTO stdTBL VALUES('조용필', '경기');
INSERT INTO stdTBL VALUES('은지원', '경북');
INSERT INTO stdTBL VALUES('바비킴', '서울');

INSERT INTO clubTBL VALUES('수영', '101호');
INSERT INTO clubTBL VALUES('바둑', '102호');
INSERT INTO clubTBL VALUES('축구', '103호');
INSERT INTO clubTBL VALUES('봉사', '104호');

INSERT INTO stdclubTBL VALUES (NULL, '김범수', '바둑');
INSERT INTO stdclubTBL VALUES (NULL, '김범수', '축구');
INSERT INTO stdclubTBL VALUES (NULL, '조용필', '축구');
INSERT INTO stdclubTBL VALUES (NULL, '은지원', '축구');
INSERT INTO stdclubTBL VALUES (NULL, '은지원', '봉사');
INSERT INTO stdclubTBL VALUES (NULL, '바비킴', '봉사');

-- 학생을 기준으로 학생이름, 지역, 가입한 동아리, 동아리방번호를 출력하세요!
SELECT S.std_name, S.std_addr, SC.club_name, C.club_room
FROM stdTBL S
	 INNER JOIN stdclubTBL SC
     ON S.std_name = SC.std_name
     INNER JOIN clubTBL C
     ON SC.club_name = C.club_name
ORDER BY S.std_name;

-- userTBL과 buyTML을 이용해서 다음을 구해보아요!
-- 전체 회원의 구매기록을 조회하세요. 단 구매 기록이 없는 회원도 출력되어야 한다.
SELECT U.userId, U.name, B.ProdName, U.addr
FROM userTBL U
	LEFT OUTER JOIN buyTBL B
    ON U.userId = B.userId
ORDER BY U.userId;

-- 한번도 구매한 적이 없는 회원의 목록을 출력하세요!
SELECT U.userId, U.name, B.ProdName, U.addr
FROM userTBL U
	LEFT OUTER JOIN buyTBL B
    ON U.userId = B.userId
WHERE B.ProdName IS NULL
ORDER BY U.userId;
profile
EUNLEE
은
이전 포스트

Database MySQL

다음 포스트

Servlet

post-custom-banner

0개의 댓글