SQL기본.mySQL

2023.02.06 - [MySQL] - SELECT문.mySQL

SELECT문.mySQL

 

이전글에서 계속

---

SQL의 분류

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

• 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문
• GRANT / REVOKE / DENY 구문

 

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

• 데이터베이스, 테이블, 뷰 인덱스 등의 데이터베이스 개체를 생성/삭제/변경 하는 역할
• CREATE, DROP, ALTER 자주사용
• RENAME, TRUNCATE
• DDL은 트랜잭션 발생시키지 않음.
• 되돌림(ROLLBACK)이나 완전 적용(COMMIT) 사용 불가
• 실행 즉시 MySQL에 적용

 

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

• 데이터를 조작(선택, 삽입, 수정, 삭제) 하는데 사용 되는 언어
• DML 구문이 사용되는 대상은 테이블의 행
• DML을 사용하기 위해서는 테이블이 정의되어 있어야합니다(DDL 필요)
• SQL문 중 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당됩니다.
• 트랜잭션(Transaction)이 발생하는 SQL도 DML에 속합니다.
  • 테이블의 데이터를 변경(입력/ 수정/ 삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것.
  • 취소가 가능합니다.

---

데이터의 삽입 : INSERT (DML)

INSERT 문의 기본

INSERT [INTO] 테이블[(열1, 열2, 열3, ...)] VALUES (값1, 값2, 값3, ...);

• 테이블 이름 다음에 나오는 열은 생략이 가능합니다.
  • 생략할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수가 테이블이 정의도니 열 순서 및 개수와 동일해야 합니다.

CREATE TABLE TESTTBL2 (
	ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME CHAR(3),
    AGE INT
);

INSERT INTO TESTTBL2 VALUES (NULL, '지민', 25);

 

---

자동으로 증가하는 AUTO_INCREMENT

• INSERT에서는 해당 열이 없다고 생각하고 입력
  • INSERT문에서 NULL 값 지정하면 자동으로 값 입력
• 1부터 증가하는 값 자동 입력
• 적용할 열이 PRIMARY KEY 또는 UNIQUE 일 때만 사용 가능
• 데이터 형은 숫자 형식만 사용 가능

CREATE TABLE TESTTBL2 (
	ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME CHAR(3),
    AGE INT
);
INSERT INTO TESTTBL2 VALUES (NULL, '지민', 25);
INSERT INTO TESTTBL2 VALUES (NULL, '유나', 22);
INSERT INTO TESTTBL2 VALUES (NULL, '유경', 21);
SELECT * FROM TESTTBL2;

-- 현재 어느 값 까지 입력 되었는지 확인
SELECT LAST_INSERT_ID();

---

-- AUTO_INCREMENT 시작값 변경  - AUTO_INCREMENT = 100
ALTER TABLE TESTTBL2 AUTO_INCREMENT = 100;
INSERT INTO TESTTBL2 VALUES (NULL, '찬미', 23);
SELECT * FROM TESTTBL2;
-- AUTO_INCREMENT 증가값 변경 SET @@AUTO_INCREMENT_INCREMENT = 3;

CREATE TABLE TESTTBL3 (
	ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME CHAR(3),
    AGE int
);

ALTER TABLE TESTTBL3 auto_increment = 1000;
-- 시작값을 1000으로 변경

INSERT INTO TESTTBL3 VALUES (NULL, '찬형', 25);
-- 증가값을 3으로 변경
SET @@auto_increment_increment = 3;

INSERT INTO TESTTBL3 VALUES (NULL, '나연', 20);
INSERT INTO TESTTBL3 VALUES (NULL, '정연', 18);
INSERT INTO TESTTBL3 VALUES (NULL, '모모', 19);

SELECT * FROM TESTTBL3;

-- 둘은 같은 입력 방식입니다.

INSERT INTO TESTTBL3 VALUES (NULL, '나연', 20), (NULL, '정연', 18), (NULL, '모모', 19);

==

INSERT INTO TESTTBL3 VALUES (NULL, '나연', 20);
INSERT INTO TESTTBL3 VALUES (NULL, '정연', 18);
INSERT INTO TESTTBL3 VALUES (NULL, '모모', 19);

 

---

 

-- ID가 1000인 튜플 삭제
DELETE FROM TESTTBL3 WHERE ID = 1000;

 

---

대량의 샘플 데이터 생성

• INSERT INTO ... SELECT 구문 사용

형식 :
INSERT INTO <테이블 명> (열 이름1, 열 이름2, ...)
	SELECT문;

• 다른 테이블의 데이터를 가져와 대량으로 입력하는 효과
• SELECT문의 열의 개수 = INSERT 할 테이블의 열의 개수
• 테이블 정의 까지 생략하려면 CREATE TABLE ... SELECT 구문을 사용합니다.

-- 대량의 데이터 입력 (샘플 데이터 생성) INSERT INTO ... SELECT 문
-- INSERT INTO (서브쿼리)

USE SQLDB;

CREATE TABLE TESTTBL4 (ID INT, FNAME VARCHAR(50), LNAME VARCHAR(50));

DESC TESTTBL4;

INSERT INTO TESTTBL4
	SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEES.EMPLOYEES;
    
-- 출력
SELECT * FROM TESTTBL4;

 

 

• 테이블 정의 까지 생략하려면 CREATE TABLE ... SELECT 구문을 사용합니다.

CREATE TABLE TESTTBL5
	SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEES.EMPLOYEES;
    
SELECT * FROM TESTTBL5;

 

---

데이터의 수정 : UPDATE (DML)

• 기존에 입력되어 있는 값을 변경하는 구문

UPDATE <테이블 명>
	SET 열1 = 값1, 열2 = 값2 ...
    WHERE 조건;

• WHERE 절 생략 가능하나, WHERE 절 생략하면 테이블의 전체 행의 내용이 변경됩니다.
  • 실무에서 실수가 종종 일어납니다. 주의 필요합니다.
  • 원상태로 복구하기 복잡하며, 다시 되돌릴 수 없는 경우도 있습니다.

---

데이터의 삭제 : DELETE FROM (DML)

• 행 단위로 데이터 삭제하는 구문

DELETE FROM <테이블 이름> WHERE 조건;

-- 입력한 데이터 삭제 : DELETE FROM
DELETE FROM TESTTBL4
	WHERE FNAME = 'AAMER';

• WHERE 절 생략되면 전체 데이터를 삭제합니다.
• 테이블을 삭제하는 경우의 속도 비교
  • DML 문인 DELETE 는 트랜잭션 로그 기록 작업 때문에 삭제가 느립니다.
  • DDL 문인 DROP과 TRUNCATE문은 트랜잭션이 없어서 빠릅니다.
    • 테이블 자체가 필요없을 경우에는 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; -- 테이블 구조는 남기고 데이터만 삭제 (DDL) 2.156초 -- 삭제에 대한 로그를 기록
DROP TABLE BIGTBL2;	-- 테이블 전체를 삭제 (DML 이라 테이블 내부의 값만 접근이 가능) 0.032
TRUNCATE TABLE BIGTBL3; -- 테이블 구조는 남기고 데이터만 삭제 (DDL) -- 0.015 로그를 기록하지 않고 삭제

 

---

조건부 데이터 입력, 변경

• 기본 키가 중복된 데이터를 입력한 경우
  
  • 오류로 입력 불가
• 대용량 데이터의 처리 경우 에러발생하지 않은 구문을 실행합니다.
  • INSERT IGNORE문
    • 에러 발생해도 다음 구문으로 넘어가게 처리
    • 에러 메시지 보면 적용되지 않은 구문이 어느것인지 구분이 가능합니다.

-- 오류가 발생해도 강제로 나머지 부분을 실행합니다.
INSERT IGNORE INTO membertbl1 VALUES ('BBK', '비비코', '미국');
INSERT IGNORE INTO membertbl1 VALUES ('SJH', '서장훈', '서울');
INSERT IGNORE INTO membertbl1 VALUES ('HJY', '현주엽', '경기');

 

• ON DUPLICATE KEY UPDATE 구문
  • 기본 키가 중복되면 데이터를 수정되도록 하는 구문도 활용 가능합니다.

---

트랜잭션을 사용해서 복구 시키기 ( ROLLBACK)

 

 

---