파트 4-4. Insert,Update,Delete 및 CTE

1. 데이터의 삽입 : INSERT


INSERT문 기본

테이블에 데이터를 삽입하는 명령어이다. 테이블 이름 다음에 나오는 열은 생략이 가능하나, 생략할 경우 VALUES 다음에 아노능 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야 한다.

INSERT [INTO] 테이블[(1, 2,...)] VALUES (1, 2..)

-- 예시
CREATE TABLE testtbl1 (id int, username char(3), age ine);
INSERT INTO testtbl1 VALUES (1,'홍길동',25);

-- id와 username만 입력하고 싶다면 테이블 뒤에 열 이름을 입력해줘야한다.
INSERT INTO testtbl1(id,username) VALUES (1,'홍길동');

-- 여러 행을 한꺼번에 입력
INSERT INTO 테이블이름 VALUES (1,2..), (1,2,..),...(1,2);


자동으로 증가하는 AUTO_INCREMENT


테이블의 속성이 AUTO_INCREMENT로 지정되어 있다면, INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다. AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력해준다. AUTO_INCREMENT로 지정할 때는 반드시 PK 또는 UNIQUE로 지정해줘야 하며 데이터 형은 숫자 형식만 사용할 수 있다. AUTO_INCREMENT로 지정된 열은 INSERT문에서 NULL값을 지정하면 자동으로 값이 입력된다.

CREATE TABLE testtbl2 (id int AUTO_INCREMENT PRIMARY KEY, username char(3),age int);
INSERT INTO testtbl2 VALUES (NULL,'지민',25);

-- 입력을 하다가 어느 숫자까지 증가되었는지 확인하는 쿼리
SELECT LAST_INSERT_ID();

-- AUTO_INCREMENT 입력값의 설정값을 수정하고 싶은 경우
ALTER TABLE testtbl2 AUTO_INCREMENT=100;

-- AUTO_INCREMENT의 증가값을 변경
-- 서버 변수인 @@auto_increment_increment 변수를 변경
SET @@auto_increment_increment=3;


대량의 샘플 데이터 생성

INSERT INTO 테이블이름(열이름1, 열이름2,...)
    SELECT문;

SELECT문의 결과 열의 개수는 INSERT를 할 테이블의 열 개수와 일치해야 한다.

USE sqldb;
CREATE TABLE testtbl4 (id int, fname varchar(50),lname varchar(50));
INSERT INTO testtbl4
    SELECT emp_no, first_name, last_name
        FROM employees.employees;

-- 테이블이 없던 경우 만들면서 바로 대입
CREATE TABLE testtbl5
    (SELECT emp_no, first_name, last_name FROM employees.employees);


2. 데이터의 수정 : UPDATE


기존에 입력되어 있는 값을 변경하기 위해 사용한다.

UPDATE 테이블이름
    SET 열1=값1, 열2=값2
    WHERE 조건;

-- 예시
UPDATE testtbl4
    SET lname = '없음'
    WHERE fname = '코우키';

-- WHERE절이 없으므로 모든 price 수정
UPDATE buytbl SET price = price *1.5; 


3. 데이터의 삭제 : DELETE FROM


DELETE는 행 단위로 삭제한다.

DELETE FROM 테이블이름 WHERE 조건;

-- fname이 harry인 모든 행 삭제
DELETE FROM testtbl4 WHERE fname = 'harry';

-- fname이 harry인 것 중에서 상위 5개만 삭제
DELETE FROM testtbl4 WHERE fname = 'harry' LIMIT 5;


실습

use sqldb;
create table bigtbl1 (select * from employees.employees);
create table bigtbl2 (select * from employees.employees);
create table bigtbl3 (select * from employees.employees);

delete from bigtbl1; -- bigtbl1 테이블을 한 행씩 삭제 -> 테이블의 구조는 남아있음
drop table bigtbl2; -- bigtbl2 테이블 자체 통채로 삭제 -> 구조 자체가 사라짐
truncate table bigtbl3; -- DELETE와 같은데 한 번에 삭제 -> 구조는 남아있음


4. 조건부 데이터 입력, 변경


기본 키가 중복된 데이터를 입력하면 당연히 입력되지 않는다. 하지만 100건을 입력하고자 하는데 첫 번째 한 건의 오류 때문에 나머지 66건도 입력되지 않는 것도 문제가 될 수 있다. MySQL은 오류가 발생해도 계속 진행하는 방법을 제공한다.

use sqldb;
create table membertbl (select userid,name,addr from usertbl limit 3);
alter table membertbl
	add constraint pk_membertbl primary key(userid); -- pk지정

-- ignore 오류가 나면 무시하고 다음으로 넘어간다.
insert ignore into membertbl values('bbk','비비코','미국');
insert ignore into membertbl values('sjh','서장훈','서울');
insert ignore into membertbl values('hjy','한주엽','경기');
select * from membertbl;

-- pk키가 중복되면 데이터를 update로 수정
insert into membertbl values('bbk','비비코','미국')
	on duplicate key update name = '비비코',addr='미국';
insert into membertbl values('djm','동짜몽','일본')
	on duplicate key update name='동짜몽',addr='일본';


5. WITH절과 CTE


WITH절은 CTE를 표현하기 위한 구문이다. CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며 더 간결한 식으로 보여지는 장점이 있다. CTE는 비재귀적 CTE와 재귀적 CTE 두 가지가 있다. 비재귀적 CTE만 알아보자.

비재귀적 CTE

비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE다. 단순한 형태이며, 복잡한 쿼리문장을 단순화 시키는 데에 적합하게 사용할 수 있다.

WITH CTE_테이블이름(열이름)
AS
(
    <쿼리문>
)
SELECT 열 이름 FROM CTE_테이블이름;

-- 예시
USE sqldb;
SELECT userid as '사용자', sum(price*amount) as '총구매액'
    from buytbl group by userid;

-- 위와 같은 쿼리를 보내면 결과로 하나의 테이블 형식으로 조회가 될 것이다. 
-- 근데 이 결과 자체를 실제 테이블로 사용하는 것이 CTE의 개념이다.
-- 조회한 결과가 테이블 abc가 되고 각 열은 userid, total로 되는 것이다.
WITH abc(userid, total)
as
(select uerid, sum(price*amount) from buytbl group by userid)

select * from abc order by total; -- 조회

select 구문 외에 update 등도 가능하지만 주로 select문을 사용한다.



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony