[MariaDB] 기본 조작 (DDL/DML)

velog에서 이관해온 글

---

DDL 

DDL 이란, Data Definition Language 데이터 베이스 정의언어를 말한다.

데이터베이스를 생성/삭제, 테이블을 생성/삭제/수정하는 경우를 말한다.

데이터베이스 생성

-- 사용법
CREATE DATABASE 데이터베이스명;

-- 사용예시
CREATE DATABASE maria;

데이터베이스 삭제

DROP으로 삭제할 경우 해당 데이터베이스 자체가 삭제되므로 주의가 필요합니다. (틀도 안남기고 삭제)

-- 사용법
DROP DATABASE 데이터베이스명;

-- 사용예시
DROP DATABASE maria;

💡 식별자 규칙

• 09, az, A~Z, $, _문자를 사용할 수 있습니다.
• 식별자란, 데이터베이스명, 테이블명, 컬럼명, 인덱스명 등을 말하며 백틱으로 감싸거나, 아무것도 안감쌀 수 있습니다. 다만, 따옴표 등은 사용할 수 없습니다.
• 식별자의 인용 문자로 백틱을 붙일 수 있습니다.
• CREATE, SELECT, DROP 등 예약어는 사용할 수 없습니다.
• 이름의 최대 길이는 64문자입니다.
• 식별자 내에 공백을 사용할 수는 있지만, 사용 시 식별자를 백틱으로 감싸서 사용해야합니다.

테이블 생성/삭제

테이블 생성

-- 테이블 생성 쿼리문
USE 데이터베이스명;
CREATE TABLE 테이블이름 (
    컬럼명 데이터타입(사이즈) NULL허용여부 기본값 COMMENT 코멘트내용 COLLATE 문자셋,
    PRIMARY KEY (컬러명, 컬럼명,,,),
    INDEX 인덱스명 (컬럼명, 컬럼명,,,)
)
COMMENT='코멘트 내용'
COLLATE='테이블 문자셋' #생략 시 기본으로 설정 됨
ENGINE=InnoDB #생략 시 기본으로 설정 됨 (기본 : InnoDB)
;

-- 사용예시
USE maria;
CREATE TABLE tbl_member (
    idx INT NOT NULL AUTO_INCREMENT COMMENT '인덱스',
    memberID VARCHAR(20) NOT NULL COMMENT '회원 ID' COLLATE 'utf8mb4_unicode_ci',
    name VARCHAR(20) NOT NULL COMMENT '이름' COLLATE 'utf8mb4_unicode_ci',
    pwd VARCHAR(300) NOT NULL COMMENT '비밀번호' COLLATE 'utf8mb4_unicode_ci',
    jumin VARCHAR(300) NULL DEFAULT NULL COMMENT '주민번호' COLLATE 'utf8mb4_unicode_ci',
    addr1 VARCHAR(100) NULL DEFAULT NULL COMMENT '주소1' COLLATE 'utf8mb4_unicode_ci',
    addr2 VARCHAR(100) NULL DEFAULT NULL COMMENT '주소2' COLLATE 'utf8mb4_unicode_ci',
    birthday CHAR(10) NULL DEFAULT NULL COMMENT '생년월일(YYYYMMDD)' COLLATE 'utf8mb4_unicode_ci',
    jobCode CHAR(2) NULL DEFAULT NULL COMMENT '직업코드' COLLATE 'utf8mb4_unicode_ci',
    mileage DECIMAL(7,0) UNSIGNED NULL DEFAULT '0' COMMENT '마일리지',  
    # 숫자 타입에는 문자셋 적용 안됨
    meberState CHAR(1) NULL DEFAULT 'N' COMMENT '회원상태' COLLATE 'utf8mb4_unicode_ci',
    regDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() COMMENT '가입일' COLLATE 'utf8mb4_unicode_ci',
    leaveDate DATETIME NULL DEFAULT NULL COMMENT '탈퇴일시' COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (memberID) using BTREE,
    INDEX index (idx) using BTREE
)
COMMENT='회원테이블'
COLLATE='utf8mb4_general_ci' --소괄호 밖에서는 ,(콤마) 안들어감
ENGINE=InnoDB
;

💡 CREATE TABLE문 작성시 주의

• ()(소괄호) 내 에서 컬럼명은 가장 앞에 와야합니다. 나머지 뒷내용은 순서 상관 없습니다.
• 테이블명과 컬럼명은 반드시 명시해줘야하며, 백틱으로 구분하여 사용합니다. - 사용안해도 되긴 하는데 추후 Java로 조작 시 동적으로 조작하여야하기 때문에 백틱 사용함이 옳습니다.
• ()(소괄호) 내에서는 ,(콤마)로 구분해줘야하며, 쿼리문 종료 시에는 ;(세미콜론)으로 쿼리문이 종료되었음을 표시합니다.
• ()(소괄호) 밖에서는 ,(콤마)가 아니라 줄넘김으로 구분할 수 있습니다.
• 숫자, 날짜 타입에는 문자셋이 적용되지 않으므로 COLLATE 부분을 생략합니다.
• 기본값으로 AUTO_INCREMENT 로 적용해주기 위해서는 해당 컬럼이 인덱스이거나 기본키로 설정되어있어야 합니다.

테이블 삭제

DROP으로 삭제할 경우 해당 테이블 자체가 삭제되므로 주의가 필요합니다. (틀도 안남기고 삭제)

-- 사용법
USE 데이터베이스명;
DROP TABLE 테이블명;

-- 사용예시
USE maria;
DROP TABLE tbl_member;

반응형

DML

DML이란, Data Manipulation Language 데이터 조작 언어를 말합니다.

주로 데이터를 조회, 추가, 삭제, 수정하는 명령을 말합니다.

데이터 추가/삭제/수정

추가

테이블 내 데이터를 추가할 수 있습니다.

-- 사용법
INSERT INTO 테이블명(컬럼명, 컬러명,,,) 
VALUES ('값', '값',,,);
# 모든 컬럼에 대해 값을 작성할 것이라면, 컬럼 괄호 작성 안해도 됨
# 컬럼 명을 작성했다면, VALUES에서 해당 컬럼에 대한 값 모두 작성 필요

-- 사용예시
INSERT INTO tbl_test (title_no, title) VALUES ('0001', 'test1');
INSERT INTO tbl_test (title_no, title) VALUES ('0002', 'test2');
INSERT INTO tbl_test (title_no, title) VALUES ('0003', 'test3');
INSERT INTO tbl_test (title_no, title) VALUES ('0004', 'test4');
INSERT INTO tbl_test (title_no, title) VALUES ('0005', 'test5');

삭제

테이블 내 데이터를 특정 조건에 따라 삭제 또는 테이블 내 전체 데이터를 삭제(틀은 남기고)할 수 있습니다.

여기서 데이터만 삭제하는 방식은 DELETE 문으로 DML이고, 틀만 남기고 싹 다 삭제하는 방식은 TRUNCATE문으로 DDL입니다.

 

두 방식의 차이를 아래 내용을 통해 확인해봅시다.

• DELETE문 이용

-- 사용법
DELETE FROM 테이블명 WHERE 조건절;
# 삭제된 데이터는 복구가 안될 수 있으니 반드시 주의해야합니다.
# AUTO_INCREMENT를 사용하는 경우 삭제된 행에 대해서 번호가 비워진채로 남습니다.
# 즉, DELETE의 경우 단순 데이터 삭제이기 떄문에 실제 테이블 자체의 값을 전체 삭제 및 초기화를 진행하기 위해서는 TRUNCATE 문을 이용해야합니다.

-- 사용예시
# 특정 조건의 데이터만 삭제
DELETE FROM tbl_test WHERE title_no = '00010';
# 테이블 내 모든 데이터 삭제 (WHERE 조건절 없이 해당 테이블 내 모든 데이터를 삭제하니 주의가 필요합니다.)
DELETE FROM tbl_test;

• TRUNCATE문 이용

-- 사용법
TRUNCATE TABLE 테이블명;
# 위 DELETE문과 달리 TRUCATE문의 경우 해당 테이블의 모든 데이터 삭제 후 테이블 자체를 초기화 합니다.
-- 사용예시
TRUNCATE TABLE tbl_test;

💡 DELETE문과 TRUNCATE문 으로 하는 데이터 전체 삭제 아직도 구분을 못하겠다고?

• DELETE - DML
  WHERE절 없이 작성 시 해당 테이블 내 데이터 전체 삭제가 가능하며,
  DELETE문의 경우 해당하는 데이터만 삭제하기 때문에 삭제 전 흑적이 그대로 남는다.
  예를들어 AUTO_INCREMENT 속성을 사용하는 열의 경우 1~10번까지 있던 상태에서 DELETE로 전체 삭제 시 다음 추가되는 데이터는 11번 부터 시작됩니다.
• TRUNCATE - DDL
  TRUNCATE문의 경우 해당 테이블을 틀도 남기지 않고 전체 삭제 후 똑같은 조건으로 테이블을 재생성하는 개념입니다. 즉 어떠한 데이터도 들어있지 않은 상태로 초기화하기 때문에 삭제된 데이터의 흔적이 남지 않습니다.
  예를들어 AUTO_INCREMENT 속성을 사용하는 경우 1~10번까지 데이터가 있던 상태에서 TRUNCATE 사용 후 다음 추가되는 데이터는 다시 1번 부터 시작됩니다.

수정

테이블 내 데이터를 수정할 수 있습니다.
WHERE 조건절 없이 실행할 경우 해당 테이블의 작성한 컬럼에 대해 모두 같은 값으로 수정되니, 해당 상황을 의도한 것이 아니라면 WHERE조건절을 반드시 입려해줘야합니다.

-- 사용법
UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2,,,,
WHERE 조건절
# UPDATE 구문 사용시 WHERE 조건절을 제대로 명시 안해주면 모든 컬럼에 대해 값이 수정되므로 주의해야합니다.
-- 사용예시
UPDATE tbl_test
SET title = 'test10_test'
WHERE title_no = '00010';

데이터 조회

테이블 내 구성요소 조회

테이블 내 컬럼, 타입, 제약조건 등의 구성요소를 조회할 때 사용합니다.

-- 사용법
DESCRIBE 테이블명;
DESC 테이블명;

--사용예시
DESCRIBE tbl_member;
DESC tbl_member;

테이블 내 데이터 조회 (SELECT문)

테이블 내 데이터를 조회할 때 사용합니다.

--사용법
SELECT (DISTINCT) 컬러명, 컬럼명,,, 
FROM 테이블명
WHERE 조건절
GROUP BY 해당 그룹 기준으로 중복제거
HAVING GROUP BY의 조건절
ORDER BY 정렬조건
LIMIT 시작인덱스, 갯수
# SELECT, FROM절은 필수로 생각하는게 좋습니다.
# DISTINCT 키워드 추가 시 조회하는 컬럼 기준으로 중복제거 
# SELECT * = SELECT ALL 로 모든 컬럼의 데이터를 조회할 때 사용합니다.

--사용예시
#테이블 내 모든 컬럼의 데이터 조회
SELECT * FROM tbl_orderinfo;  

#테이블 내 특정 컬럼의 데이터만 조회
SELECT memberId, orderNo FROM tbl_orderinfo;

#테이블 내 특정 컬럼의 특정조건에 해당하는 데이터만 조회
SELECT memberId, NAME, pwd, jumin, addr1, addr2, birthday, jobCode, mileage, memberState, regDate 
FROM tbl_member
WHERE memberId = 'gee1';

💡 *(ALL) 조회는 만능일까??

해당 * / ALL의 경우 쿼리 에디터에서 스스로 조회용으로는 사용해도 상관 없으나, 실제 데이터 조회 커리를 작성 시에는 해당 * / ALL 사용 자제하고, 모든 컬럼을 조회해야한다고 하면, 명시적으로 모든 컬럼을 작성해서 사용하는 식으로 이용해야합니다.

💡 테이블 내 컬럼 개수 조회

-- 데이터 개수 조회 사용법
SELECT COUNT(*) FROM 테이블명

 

COUNT() 메서드를 활용하여 해당 테이블, 컬럼에 데이터가 몇건이나 있는지 확인할 수 있습니다.

SELECT 부분

조회해올 컬럼을 명시해주는 부분 입니다.

• DISTINCT
  조회하는 컬럼 기준으로 중복된 데이터를 제외 후 조회합니다.
  선택된 컬럼 내 데이터 모두 일치하는 경우를 중복으로 봅니다. 따라서 5개의 컬럼을 조회할 때 4개의 컬럼은 모두 일치하나 1개의 컬럼 내용이 다를 경우 이는 중복으로 보지 않습니다.

--사용법
SELECT DISTINCT 컬럼명 FROM 테이블명;

--사용예시
SELECT DISTINCT goodsCode, unitCode FROM tbl_orderdetail;

• AS
  SELECT 쿼리문의 결과를 컬럼명(Alias)을 새로 정의해서 출력할 수 있습니다.
  AS 키워드의 경우 생략해도 되지만, 가독성을 위해 명시적으로 작성해주도록 합니다.
  AS로 새로 정의한 컬럼명을 이용해 WHERE 조건절, order by절, having절 등에서 활용할 수 있습니다.

--사용법
SELECT 컬럼명 AS 새로운 컬럼명 FROM 테이블명;

--사용예시
SELECT goodsCode AS '상품코드', unitCode AS '유닛코드' FROM tbl_orderdetail;
# AS 정의 컬럼명 따옴표 생략가능
SELECT CONCAT(addr1,addr2) AS addr, regDate AS reg_date FROM tbl_member;
# AS 정의 컬럼명 활용 예시
SELECT CONCAT (addr1,addr2) AS 'addr', regDate AS reg_date
FROM tbl_member
ORDER BY reg_date asc;

• INTO
  검색 결과를 파일로 출력할 수 있습니다.

-- 사용법
# 외부파일로 저장
SELECT 컬럼명 INTO OUTFILE '절대경로' FROM 테이블명;
# 대용량파일로 저장
SELECT 컬럼명 INTO DUMPFILE '절대 경로' FROM 테이블명;
# 변수에 저장
SELECT 컬럼명 INTO 변수 FROM 테이블명
# 구분자 및 종료기호 추가
SELECT 컬럼명 
INTO OUTFILE '절대 경로' 
FIELDS TERMINATED BY '구분자' ENCLOSED BY '컬럼의 시작과 끝 기호'
FROM 테이블명;

-- 사용예시
SELECT *
INTO OUTFILE 'C:\\mariaDB\\tbl_memberAll.dat'
FIELDS TERMINATED BY '||' ENCLOSED BY '"'
FROM tbl_member;

💡 파일 불러오기
파일을 불러올 경우 LOAD DATA LOCAL INFILE로 나의 로컬 스토리지에서 불러올 수 있습니다.
파일을 불러와서 테이블에 넣을 경우 해당 테이블을 만든 상태에서 진행해야하며, 테이블의 컬럼 개수와 순서가 불러올 파일에서 동일하게 진행되야합니다.

-- 사용법
LOAD DATA LOCAL INFILE '파일 절대경로'
INTO TABLE 테이블명
FIELDS TERMINATED BY '구분자' ENCLOSED BY '컬럼의 시작과 끝 기호';
-- 사용예시
LOAD DATA LOCAL INFILE 'C:\\mariaDB\\tbl_memberAll.dat'
INTO TABLE tbl_member
FIELDS TERMINATED BY '||' ENCLOSED BY '"';

WHERE 부분

• 조건절
  각종 연산자를 사용하여 만든 조건절에 해당하는 데이터만 조회할 수 있도록 합니다.

--사용법
SELECT 컬럼명 FROM 테이블명 WHERE 조건절;

--사용예시
SELECT goodsCode, unitCode FROM tbl_orderdetail WHERE goodsCode like '%001';
SELECT goodsCode, unitCode FROM tbl_orderdetail WHERE goodsCode = 'GDS001';
SELECT goodsCode, unitCode FROM tbl_orderdetail WHERE goodsCode IS NULL; # =  null로는 비교 불가

• GROUP BY
  특정 열 기준으로 그룹핑, 그룹핑 = 해당 컬럼 기준으로 중복제거
• having
  GROUP BY의 조건절로 GROUP BY 진행 후 해당 조건절을 체크합니다.

💡 WHERE 조건절과, HAVING 조건절의 차이점
쿼리가 실행될 때는 위->아래, 좌->우 로 읽어서 실행하기 때문에
1. WHERE조건절 먼저 실행 후
2. 그 다음 GROUP BY 기준으로 그룹핑 실행
3. 그룹핑 완료 후 HAVING 조건절 실행
실행하는 순서로 인해 똑같은 조건이라도 WHERE에 조건절은 넣느냐, HAVING에 조건절을 넣느냐에 따라 실행 결과가 완전히 다를 수 있습니다.

ORDER BY 부분

특정 열 기준으로 오름차순(Ascending), 내림차순(Descending) 정렬하여 조회합니다.

ORDER BY 없이 조회하게 되면, 데이터베이스 정렬조건에 따라 출력되는데, 이 조건은 데이터베이스 상태에 따라 달라져 명확한 기준을 알 수 없습니다.

• ASC : Ascending
• DESC : Descending

정렬조건을 여러가지 넣을 수 있으며 입력한 순서대로 정렬하는 방식으로 작동합니다.

--사용법
SELECT 컬럼명 FROM 테이블명 ORDER BY 기준컬럼명 (ASC | DECS), 기준컬럼명 (ASC | DECS),,,;

--사용예시
SELECT * FROM tbl_member ORDER BY name ASC;
# 정렬조건 여러개 넣기
SELECT CONCAT (addr1,addr2) AS 'addr', regDate AS 'reg_date'
FROM tbl_member
ORDER BY 'reg_date' ASC, 'addr' DESC;

LIMIT 부분

가져올 데이터의 시작점, 개수를 설정할 수 있습니다.

--사용법
SELECT 컬럼명 FROM 테이블명 LIMIT 시작인덱스, 개수;
SELECT 컬럼명 FROM 테이블명 LIMIT 갯수;
SELECT 컬럼명 FROM 테이블명 LIMET 개수 OFFSET 시작인덱스;

--사용예시
# 데이터 0번째 인덱스 부터 5개만 갖고오기
SELECT goodsCode, unitCode FROM tbl_orderdetail LIMIT 0, 5;
# 데이터 3개만 갖고오기
SELECT goodsCode, unitCode FROM tbl_orderdetail LIMIT 3;
# OFFSET 키워드 사용
SELECT goodsCode, unitCode FROM tbl_orderdetail LIMIT 3 OFFSET 2;

💡 트랜잭션 이용
데이터를 생성/삭제/수정 할 경우 해당 데이터를 되돌리기가 어려워 주의가 필요합니다.
이럴 때 트랜잭션을 이용하게 될 경우 트랜잭션 시작점으로 롤백하거나, 데이터 작업 완료 후 커밋하여 작업을 확정하는 식으로 데이터 작업에 실수를 줄일 수 있습니다.

• START TRANSACTION : 트랜잭션 시작을 알리는 문장
• ROLLBACK : 트랜잭션 시작점으로 돌아가는 문장
• COMMIT : 트랜잭션 작업 전부 완료 후 확정하는 문장

다만 MariaDB의 경우 autoCommit이 설정되어있어서, 사용자가 쿼리를 실행할 때마다 자동으로 Commit이 됩니다.
해당 설정에 대해 조작할 떄는 SET AUTOCOMMIT에 대한 값을 1 - autoCommint 활성화 / 0 - autoCommit 비활성화 로 설정할 수 있습니디.

--사용예시
#MariadB의 autoCommit 설정 비활성화
SET AUTOCOMMIT=0;
# 트랜잭션 시작
START TRANSACTION;
# 트랜잭션 내용 작성
SELECT * FROM tbl_test;
UPDATE tbl_test SET title = 'update_test' WHERE idx = 10;
SELECT * FROM tbl_test;
# 트랜잭션 시작시점으로 되돌리기
ROLLBACK;
# 트랜잭션 내용 다시 작성
UPDATE tbl_test SET title = 'update_test' WHERE idx = 10;
SELECT * FROM tbl_test;
# 트랜잭션 커밋(확정)
COMMIT;
# 커밋이후에는 다시 롤백해도 확정 전 상태로 돌아가지지 않음

참고

MariaDB로 따라 하며 배우는 SQL프로그래밍 데이터베이스 기초에서 실무까지 - 나익수, 서연경 지음
위 책을 공부하며 작성하고 있습니다!