[포스코x코딩온] 스마트팩토리 개발자 과정 11주차 회고 | 데이터 조작어 (DML)

DML

• DML ( Data Manipulation Language )
• 데이터베이스의 내부 데이터를 관리하기 위한 언어

CRUD

• 대부분의 컴퓨터 소프트웨어가 가지는 기본적인 처리 기능

• Create (생성)	->	INSERT
• Read (읽기)	->	SELECT
• Update (갱신)	->	UPDATE
• Delete (삭제)	->	DELETE

new_customer 테이블

new_orders 테이블

INSERT 문

• 테이블에 새로운 투플 추가 
• INSERT INTO 테이블명 (필드1, 필드2, 필드3, ...) VALUES (값1, 값2, 값3)
• INSERT INTO 테이블명 VALUES (값1, 값2, 값3)
• 필드를 명시하지 않은 경우 테이블의 모든 컬럼에 값을 순서대로 추가해야 한다.

INSERT 뒤에는 INTO가 따라 붙는다는 것을 잊지 말자!!

Ex)

-- 1) 첫번째 방법: 긴 명령어 버전
INSERT INTO new_customer (custid, custname, addr, phone, birth) VALUES ('lucky', '강해원', '미국 뉴욕', '01022223333', '2002-03-05');

-- 속성 순서를 바꿔서 추가하고 싶다면?
-- 순서 바꿀 수 있음! 단, 속성명과 속성값 순서가 차례대로 대응되어야 함.
INSERT INTO new_customer (birth, custid, custname, addr, phone) VALUES ('2007-04-28', 'wow', '이지은', '대한민국 부산', '01098765432');

-- 2) 두번째 방법: 짧은 명령어 버전 (속성 순서를 맞추어서 추가해야 함.)
INSERT INTO new_customer VALUES ('happy', '최시은', '일본 오키나와', '01033331234', '1970-10-31');

-- 여러 고객 정보를 동시에 추가하고 싶으면 INSERT 문을 여러번 작성하면 되겠네요? 
-- Yes! 그러나 더 간편한 방법이 있다.
INSERT INTO new_customer VALUES 
	('asdf', '강세희', '대한민국 부산', '01033331235', '2004-11-11'),
	('sdfg', '윤지성', '일본 도쿄', '01033331236', '1995-02-15'),
    ('dfgh', '이재은', '미국 뉴욕', '01033331237', '2004-06-07');

UPDATE 문

• 테이블에서 특정 속성 값 수정 
• UPDATE 테이블명 SET 필드1 = 값1 WHERE 필드2 = 조건2;

Ex)

-- custid가 'apple'인 사람 주소를 '대한민국 서울'로 변경
UPDATE new_customer SET addr = '대한민국 서울' WHERE custid = 'apple';

-- custid의 끝이 'y'인 사람 주소를 '대한민국 서울'로 변경
UPDATE new_customer SET addr = '대한민국 서울' WHERE custid LIKE '%y'; -- 안전장치를 풀어야 실행 가능
SET sql_safe_updates = 0;	-- 안전장치 해제, 실행 후 1회에 한해서만 해제

DELETE 문

• 테이블의 기존 투플을 삭제 
• DELETE FROM 테이블명 WHERE 필드1 = 값1;

-- custid가 'happy'인 사람 삭제
DELETE FROM new_customer WHERE custid = 'happy';

-- 외래키 연결되어 있는 경우, 연쇄 삭제
-- orders 테이블에 있는 kiwi 주문목록도 같이 삭제된다. cascade 설정되어 있기 때문. 
DELETE FROM new_customer WHERE custid = 'kiwi';	

참고 DROP과 TRUNCATE

DROP

• 테이블 삭제하기
• 테이블을 잘못 만들었거나 더 이상 필요 없는 경우
• DROP TABLE 테이블이름;

• IF EXISTS: 삭제하려는 테이블이 존재하지 않아서 발생하는 에러 방지 키워드
DROP TABLE IF EXISTS 테이블이름;

TRUNCATE

• 테이블 초기화하기
• 테이블의 모든 행(row) 일괄 삭제
• TRUNCATE TABLE 테이블명;

실습 문제

실습 문제를 풀어보자

테이블 만들기

-- book_store 데이터베이스(스키마) 생성
CREATE DATABASE book_store DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

USE book_store;

-- authors 테이블 생성
CREATE TABLE authors
(
	author_id INT PRIMARY KEY, 
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50)
);
DESC authors;

-- books 테이블 생성
CREATE TABLE books
(
	book_id INT PRIMARY KEY, 
	title VARCHAR(100),
	author_id INT,
	publication_date DATE,
    FOREIGN KEY(author_id) REFERENCES authors(author_id) ON UPDATE CASCADE ON DELETE CASCADE
);
DESC books;

-- orders 테이블 생성
CREATE TABLE orders
(
	order_id INT PRIMARY KEY, 
	book_id INT,
    customer_name varchar(50),
	order_date DATE,
    FOREIGN KEY(book_id) REFERENCES books(book_id) ON UPDATE CASCADE ON DELETE CASCADE
);
DESC orders;

외래키를 지정할때 CASCADE를 잊지말자!

• ON UPDATE CASCADE: 기준 테이블의 데이터 수정시 -> 참조 테이즐 데이터도 수정
• ON DELETE CASCADE: 기준 데이터의 데이터 삭제시 -> 참조 테이블 데이터도 삭제

데이터 채우기

-- authors 데이터 채우기 
INSERT INTO authors VALUES 
	('1', 'J.K.', 'Rowling', 'jkrowling@gmail.com'),
	('2', 'George R.R.', 'Martin', 'grmartin@yahoo.com'),
    ('3', 'Stephen', 'King', 'sking@hotmail.com');
DESC authors;
SELECT * FROM authors;

-- books 데이터 채우기 
INSERT INTO books VALUES 
	('1', 'Harry Potter and the Philosopher''s Stone', '1', '1997-06-26'),
	('2', 'A Game of Thrones', '2', '1996-08-006'),
    ('3', 'The Shining', '3', '1977-01-28');
DESC books;
SELECT * FROM books;

-- orders 데이터 채우기 
INSERT INTO orders VALUES 
	('1', '1', 'John Smith','2022-02-15'),
	('2', '2', 'Jane Doe','2022-02-16'),
    ('3', '3', 'Bob Jhonson','2022-02-17');
DESC orders;
SELECT * FROM orders;

주의사항

Harry Potter and the Philosopher's Stone와 같이 문장 안에 '가 있을 때는 ''를 사용한다.
'Harry Potter and the Philosopher''s Stone' 이런 식으로!

-- 문제 
-- 1. author_id가 1인 작성자의 이메일을 jkrowling@yahoo.com으로 업데이트하는 SQL 문을 작성합니다.
UPDATE authors SET email = 'jkrowling@yahoo.com' WHERE author_id = '1';
SELECT * FROM authors;

-- 2. books 테이블에서 book_id 2인 책을 삭제하는 SQL 문을 작성합니다.
DELETE FROM books WHERE book_id = '2';
SELECT * FROM books;

-- 3. 다음 세부 정보가 포함된 새 책을 삽입하는 SQL 문을 작성합니다.
-- 책 ID: 4
-- 제목: 스탠드
-- 저자 ID: 3
-- 발행일자 : 1978-01-01
INSERT INTO books VALUES 
    ('4', 'Stand', '3', '1978-01-28');
SELECT * FROM books;

-- 4. book_id 1인 책의 출판 날짜를 1997-06-30으로 업데이트하는 SQL 문을 작성하십시오.
UPDATE books SET publication_date = '1977-06-30' WHERE book_id = '1';
SELECT * FROM books;

-- 5. 2022-02-17 이전에 접수된 모든 주문을 삭제하는 SQL 문을 작성합니다.
DELETE FROM orders WHERE order_date < '2022-02-17';
SELECT * FROM orders;

-- 6. 다음 세부 정보와 함께 새 주문을 삽입하는 SQL 문을 작성합니다.
-- 주문 ID: 4
-- 책 ID: 1
-- 고객 이름: 사라 존슨
-- 주문일자 : 2022-02-18
INSERT INTO orders VALUES ('4', '1', 'Sara Jhonson', '2022-02-18');
SELECT * FROM orders;

-- 7. order_id가 3인 주문의 고객 이름을 Jack Smith로 업데이트하는 SQL 문을 작성합니다.
UPDATE orders SET customer_name = 'Jack Smith' WHERE order_id = '3';
SELECT * FROM orders;

-- 8. 다음 세부 정보와 함께 새 작성자를 삽입하는 SQL 문을 작성합니다.
-- 저자 ID: 4
-- 이름: 아가사
-- 성: 크리스티
-- 이메일: agatha.christie@example.com
INSERT INTO authors VALUES ('4', 'Agatha', 'Christie', 'agatha.christie@example.com');
SELECT * FROM authors;

-- 9. author_id 2인 작성자의 성을 Martinez로 업데이트하는 SQL 문을 작성합니다.
UPDATE authors SET first_name = 'Martinez' WHERE author_id = '2';
SELECT * FROM authors;

-- 10. author_id 3인 저자가 쓴 모든 책을 삭제하는 SQL 문을 작성합니다.
DELETE FROM authors WHERE author_id = '3';
SELECT * FROM authors;

DML은 크게 어려운 것은 없었던 것 같다.
용어들과 배치 순서만 잘 기억하면 좋을 것 같다.