SQL 기본 쿼리문 ( 2 )

황규진 2024. 7. 11. 10:13

데이터 변경을 위한 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;