[MySQL] 테이블 생성과 데이터 조작

데이터 조작 언어(Data Manipulation Language, DML)

데이터를 검색(SELECT), 삽입(INSERT), 수정(UPDATE), 삭제(DELETE) 하는 데 사용한다. DML을 사용하려면 반드시 테이블이 있어야 한다. 트랜잭션이 발생하는 SQL도 해당된다.
* 트랜잭션 : 테이블의 데이터를 변경(입력, 수정, 삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것. 만약 실수가 있었을 경우에 임시로 적응시킨 것을 취소시킬 수 있게 해 줌.

 

데이터 정의 언어(Data Definition Language, DDL)

데이터베이스, 테이블, 뷰, 인덱스 등의 개체를 생성(CREATE), 삭제(DROP), 변경(ALTER) 한다. 여기서는 트랜잭션을 발생시키지 않는다. 따라서 ROLLBACK이나 완전 적용 COMMIT을 시킬 수 없다. 여기서는 실행 즉시 MySQL에 적용한다.

 

데이터 제어 언어(Data Control Language, DCL)

사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문으로 GRANT, REVOKE, DENY 등이 이에 해당된다.

데이터베이스 생성하기

-- 데이터베이스를 생성하는 CREATE 문의 기본 형식
CREATE DATABASE [데이터베이스 이름]

CREATE DATABASE testDB;

 

데이터베이스 삭제하기

-- DROP 문으로 데이터베이스 삭제
DROP DATABASE testDB;

 

테이블 생성하기

-- 데이터베이스 선택
USE testDB;

-- 테이블을 생성하는 CREATE 문의 기본 형식
CREATE TABLE 테이블 이름 (
열1 자료형,
열2 자료형,
(..생략..)
);

CREATE testTBL (
col_1 INT,
col_2 VARCHAR(50),
col_3 DATETIME
);

 

테이블에 데이터 삽입하기

-- 데이터를 삽입하는 INSERT 문의 기본 형식
INSERT INTO 테이블 [열1, 열2, ...] VALUES [값1, 값2, ...];

INSERT INTO testtbl (col_1, col_2, col_3) VALUES (1, 'SQL', '2021-01-01');

-- 테이블의 특정 열에만 데이터 삽입
INSERT INTO testtbl(col_1, col_2)
VALUES (3, 'col_3 값 생략');

 

INSERT 문은 테이블에 데이터를 삽입할 때 열 이름을 생략할 수 있다. 하지만 열 이름을 생략하려면 VALUES 문 뒤에 테이블의 열 순서와 개수에 맞춰 데이터를 채워야 한다.

INSERT INTO testtbl VALUES (2, '열 이름 생략', '2021-01-02');

 

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

CREATE testTBL (
col_1 INT AUTO_INCREMENT PRIMARY KEY,
col_2 VARCHAR(50),
col_3 DATETIME
);

INSERT INTO testtbl VALUES (NULL, 'SQL', '2021-01-01');

-- 이후에 AUTO_INCREMENT 입력값을 100부터 입력되도록 변경하고 싶다면 다음과 같이 수행
ALTER TABLE testtbl1 auto_increment = 100;

-- 증가값을 지정하려면 서버 변수인 @@auto_increment_increment 변수를 변경
set  @@auto_increment_increment = 3;

-- 마지막에 입력된 값을 보기
SELECT LAST_INSERT_ID();

 

중복 데이터 수정하기

MySQL에서 데이터베이스에 레코드를 삽입할 때, 기본 키나 유니크 인덱스에 충돌이 발생하는 경우 기존 레코드를 업데이트하는 기능을 제공한다. 이 구문을 사용하면 동일한 기본 키나 유니크 인덱스 값이 이미 존재하는 경우, 새로운 값으로 업데이트할 수 있다. 값이 존재하지 않아도 업데이트 된다.

-- 기본 형식
INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...)
ON DUPLICATE KEY UPDATE
컬럼1 = 값1, 컬럼2 = 값2, ...;

INSERT INTO membertbl values ('BBK', '바비킴', '미국'); -- 중복 값 에러
INSERT INTO membertbl values ('SJH', '서장훈', '서울');
INSERT INTO membertbl values ('HJY', '현주엽', '경기');

INSERT INTO membertbl values ('BBK', '바비킴', '미국')
	ON duplicate key update name = '수정할 내용1', addr = '수정할 내용2';

 

테이블에 데이터 수정하기

UPDATE 문에서 WHERE 문을 생략할 수 있는데, 이 경우 테이블의 전체 데이터를 수정하므로 항상 주의해야 한다.

-- 데이터를 수정하는 UPDATE 문의 기본 형식
UPDATE [테이블 이름] SET [열1 = 값1, 열2 = 값2, ...]
WHERE [열] = [조건]

UPDATE testtbl SET col_2 = '데이터 수정'
WHERE col_1 = 4;

-- WHERE 문 생략
UPDATE testtbl SET col_1 = col_1 + 10;

 

테이블에 데이터 삭제하기

-- DELETE 문으로 데이터 삭제하기
DELETE [테이블 이름] WHERE [열] = [조건]

DELETE testtbl WHERE col_1 = 14;

-- 전체 열 데이터 삭제: WHERE 문 생략
DELETE testtbl;

 

TRUNCATE

truncate는 delete와 동일하지만 트랜잭션 로그를 기록하지 않아서 속도가 매우 빠르다. 그러므로 대용량의 테이블 전체 내용을 삭제할 때는 테이블 자체가 필요 없는 경우에는 drop으로 삭제하고, 테이블의 구조는 남겨놓고 싶다면 truncate로 삭제하는 것이 효율적이다.

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

-- 행 전체 삭제(테이블 구조는 남아있음)
delete from bigtbl1;

-- 테이블 삭제(테이블 자체를 삭제시킴)
drop table bigtbl2;

-- 행 전체 삭제(테이블 구조는 남아있음)
truncate table bigtbl3;

 

테이블 삭제하기

DROP TABLE testtbl;

 

다른 테이블에 검색 결과 입력하기

-- 이미 생성된 테이블에 입력
INSERT 대상 테이블 SELECT 열 FROM 기존 테이블;

-- 새 테이블을 생성하며 입력
SELECT 열 INTO 새로운 테이블 FROM 기존 테이블;