SQL 기본 쿼리문 ( 2 )

데이터 변경을 위한 SQL문

INSERT

INSERT는 테이블에 데이터를 삽입하는 명령

USE sqldb;

CREATE table testTbl1 (id int, userName char(3), age int);
INSERT INTO testTbl1 VALUES(1, '홍길동', 25);
SELECT * FROM testTbl1;

• 테이블 이름 다음에 나오는 열은 생략이 가능, 하지만 생략할 경우 VALUES 다음에 나오는 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야함

INSERT INTO testTbl1 (id, userName) VALUES (2, '설현');

SELECT * FROM testTbl1;

• 열의 순서를 바꿔서 입력할 때 꼭 열 이름을 입력할 순서에 맟줘 나열

INSERT INTO testTbl1 (userName, age, id) VALUES ('하니', 26, 3);

SELECT * FROM testTbl1;

자동으로 증가하는 AUTO_INCREMENT

• 자동으로 1부터 증가하는 값을 입력해줌
• AUTO_INCREMENT로 지정할 때는 꼭 PRIMARY KEY 또는 UNIQUE로 지정해 줘야함
• INSERT문에서 NULL 값을 지정하면 자동 입력

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;

한꺼번에 INSERT

INSERT INTO 테이블이름 VALUES (값1, 값2 ...), (값3, 값4...), (값5, 값6, ...);

 

ALTER

• AUTO_INCREMENT 입력값을 100부터 입력되도록 변경

ALTER TABLE testTbl2 AUTO_INCREMENT = 100;
INSERT INTO testTbl2  VALUES (NULL, '찬미', 27);
INSERT INTO testTbl2  VALUES (NULL, '찬영', 22);
SELECT * FROM testTbl2;

SET @@auto_increment_increment = 3;

increment 값을 3씩 증가하도록 설정

 

대량의 샘플 데이터 생성

• INSERT INTO … SELECT
• 기존에 있던 employees 데이터에서 employees 테이블에 있는 대량의 데이터를 가져옴

CREATE TABLE testTbl4 (id int, Fname varchar(50), Lname varchar(50));
INSERT INTO testTbl4 
	SELECT emp_no, first_name, last_name FROM employees.employees;

SELECT * FROM testTbl4

CREATE TABLE testTbl5 
	SELECT emp_no, first_name, last_name FROM employees.employees;

SELECT * FROM testTbl5;

• 달라진 부분은 컬럼 명이 다르다.

 

UPDATE

• UPDATE 테이블 이름 SET

UPDATE testTbl4 SET Lname = '없음' WHERE Fname = 'Kyoichi';
SELECT * FROM testTbl4;

• 만약 WHERE절 안 줬으면 모든 데이터 Lname 다 ‘없음’ 으로 된다

 

곱하기 가능

UPDATE buytbl SET price = price * 1.5;

 

DELETE FROM

SELECT * FROM testTbl4 WHERE Fname = 'Aamer';
DELEte FROM testTbl4 WHERE Fname = 'Aamer';

테이블 삭제

DELETE FROM bigTbl1; -- 데이터만 삭제
DROP TABLE bigTbl2; -- 테이블 전체를 삭제
TRUNCATE TABLE bigTbl3; -- 테이블의 구조는 남겨놓고 삭제.

• DML 문인 DELETE 는 트랜잭션 로그를 기록하는 작업 때문에 삭제가 오래 걸린다.
• DDL 문인 DROP문은 테이블 자체를 삭제한다.
  • DDL은 트랜잭션을 발생 시키지 않는다.
• DDL 문인 TRUNCATE문은 DELETE와 동일하지만 트랜잭션 로그를 기록하지 않아 속도가 무척 빠르다. → 복원을 할 수 없다.
• ⇒ 테이블 전체를 삭제 할때는 DROP, 테이블 구조는 남겨 놓고 싶으면 TRUNCATE로 삭제하는 것이 효율적이다.
• 트랜잭션은 복원하기 위해 로그를 기록해 놓는다. ex) 계좌이체

 

조건부 데이터 입력, 변경

constraint

CREATE TABLE memberTbl
	SELECT userID, name, addr FROM usertbl LIMIT 3;

ALTER TABLE memberTbl
	ADD CONSTRAINT pk_memberTbl PRIMARY KEY (userID);

 

IGNORE

• PK는 중복 값을 넣으면 안되는데 하나만 중복 되었을 때 나머지는 넣고 싶을 때
• IGNORE을 사용하면 중복 되는 것은 무시하고 나머지 데이터를 추가 입력해준다.

INSERT IGNORE INTO memberTbl VALUES ('BBK', '비비코', '미국');
INSERT IGNORE INTO memberTbl VALUES ('SJH', '서장훈', '서울');
INSERT IGNORE INTO memberTbl VALUES ('HJY', '현주엽', '경기');

 

DUPLICATE KEY UPDATE

• 똑같은 데이터일 때 업데이트 해줘라
• 중복값이 없을 때는 그냥 추가된다.

INSERT INTO memberTbl VALUES('BBK', '비비코', '미국')
	ON DUPLICATE KEY UPDATE name = '비비토', addr = '미국';

 

컬럼명 변경

ALTER TABLE usertbl CHANGE birthYeaar birthYear INT;

 

 

WITH절과 CTE

WITH절과 CTE 개요

• WITH 절은 CTE(Common Table Expression)를 표현하기 위한 구문으로 MySQL 8.0 이후부터 사용
• CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신 할 수 있으며, 더 간결한 식으로 보여지는 장점
• CTE는 ANSI-SQL99 표준에서 나온것이다.
• 기존의 SQL은 ANSI-SQL92를 기준으로 한다. 하지만, 최근의 DBMS는 대개 ANSI-SQL99와 호환되므로 다른 DBMS에서도 같거나 비슷한 방식으로 응용

비재귀적 CTE

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

WITH abc(userid, total)
AS
(SELECT userid , SUM(price * amount)
	FROM buytbl GROUP BY userid)
SELECT * FROM abc ORDER BY total DESC;