데이터 삽입, 수정, 삭제
1. SQL 문의 종류
SQL문은 크게 DML, DDL, DCL로 분류한다.
- DML
DML (Data Manipulation Language, 데이터 조작어)
데이터를 검색 및 삽입, 수정, 삭제하는데 사용하는 언어
DML 구문으로 조작하는 대상은 테이블의 행이며, DML을 사용하려면 반드시 테이블이 정의 되어 있어야 한다.
SELECT, INSERT, UPDATE, DELETE 문
또한 트랜잭션이 발생하는 SQL문도 DML에 해당한다.
-
DDL
DDL (Data Definition Language, 데이터 정의어)
데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성, 삭제, 변경하는 데 사용하는 언어
CREATE, DROP, ALTER, TRUNCATE 문
DDL은 트랜잭션을 발생시키지 않기 때문에 한 번 실행한 내용을 롤백으로 되돌리거나 커밋으로 완전 적용할 수 없다.
DDL 문으로 실행한 내용은 즉시 MySQL에 적용된다. -
DCL
DCL (Data Control Language, 데이터 제어어)
사용자에게 어떤 권한을 부여하거나 빼앗을 때 사용하는 언어이다.
GRANT, REVOKE, DENY 문이 속한다.
GRANT : 권한 부여, REVOKE : 권한 회수, DENY : 권한 차단DENY는 특정 테이블의 권한만 차단하는 등 부분적인 권한에 관여
2. INSERT 문
INSERT [INTO] 테이블이름[(열1, 열2, ...)] VALUES (값1, 값2, ...)INSERT 문에서는 테이블 이름 다음에 나오는 열을 생략할 수 있다.
생략하는 경우에는 VALUES 다음에 오는 값의 순서 및 개수가 테이블이 정의된 열의 순서 및 개수와 동일해야한다.
2.1 AUTO_INCREMENT
테이블을 생성할 때 특정 열의 속성이 AUTO_INCREMENT로 지정되어 있으면 INSERT 문에서는 해당 열이 없다고 간주해도 된다.
AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력하는 키워드이다.
특정 열을 AUTO_INCREMENT로 지정할 때는 반드시 PRIMARY KEY 또는 UNIQUE로 설정해야 한다.
또한 데이터 형식이 숫자인 열에만 사용할 수 있다.
AUTO_INCREMENT로 지정된 열은 INSERT 문에서 NULL 값으로 지정하면 자동으로 값이 입력된다.
데이터의 마지막 숫자를 확인하고 싶을 때는 SELECT LAST_INSERT_ID();문을 사용하면 마지막에 입력된 값을 볼 수 있다.
AUTO_INCREMENT의 입력 값을 100부터 시작하도록 변경하고 싶다면 다음과 같이 작성
ALTER TABLE 테이블이름 AUTO_INCREMENT=100;다음은 초깃값을 100으로 하고 증가 값을 3으로 변경하는 구문
ALTER TABLE 테이블이름 AUTO_INCREMENT=100;
SET @@auto_increment_increment=3;2.2 대량 데이터 삽입
대량의 데이터를 삽입할 때 지금까지 해온 방식으로 일일이 입력하면 많은 시간이 걸린다.
이때는 INSERT INTO ... SELECT 문을 사용한다.
이는 다른 테이블의 대량 데이터를 가져와 입력하는 방식이며 형식은 다음과 같다.
INSERT INTO 테이블이름 (열1, 열2, ...)
SELECT 문;3. UPDATE 문
UPDATE 테이블이름
SET 열1=값1, 열2=값2, ...
WHERE 조건;UPDATE 문에서는 WHERE 절을 생략할 수 있지만 WHERE 절을 생략하면 테이블 전체의 행이 수정되기 때문에 주의.
전체 테이블의 내용을 수정하고 싶을 때는 WHERE 절을 생략할 수도 있다.
ex) 모든 테이블의 가격이 1.5배 인상되는 쿼리
UPDATE buyTBL
SET price = price * 1.54. DELETE 문
DELETE 문은 테이블의 데이터를 행 단위로 삭제하는 명령.
DELETE 테에블이름 WHERE 조건;DELETE 문에서 WHERE 절을 생략하면 테이블에 저장된 전체 데이터가 삭제 된다.
ex) Aamer라는 사요ㅕㅇ자가 필요 없다면 다음과 같이 작성
DELETE FROM testTBL4 WHERE Fname = 'Aamer';만약 200개의 Aamer를 모두 지우는 것이 아니라 상위 몇 건만 삭제하고자 한다면 LIMIT 절 사용
DELETE FROM testTBL4 WHERE Fname = 'Aamer' LIMIT 5;DML인 DELETE 문은 트랜잭션 로그를 기록하는 작업을 하기 때문에 삭제하는 데 시간이 오래걸린다.
반면 DDL인 DROP문은 테이블 자체를 삭제하고 트랜잭션 로그를 기록하지 않는다.
역시 DDL인 TRUNCATE문은 DELETE와 결과가 동일하지만 트랜잭션 로그를 기록하지 않으므로 속도가 아주 빠르다.
대용량 테이블 전체 내용을 삭제할 때 테이블 자체가 필요 없는 경우는 DROP, 테이블의 구조를 남겨놓고 싶은 경우에는 TRUNCATE 문으로 삭제하는 것이 효율
출처 : https://lee-mandu.tistory.com/476
5. 조건부 데이터 삽입과 수정
INSERT 문은 테이블에 행 단위로 데이터를 삽입하는 명령이다.
만약 기본키가 중복된 데이터를 삽입한다면 하지 않는다.
오류가 발생해도 계속 삽입하도록 설정하기
IGNORE 키워드 사용하기
INSERT INTO memberTBL VALUES ('KHD', '강호동', '미국');
INSERT INTO memberTBL VALUES ('LSM', '이상민', '서울');만약 이때 첫 번째 쿼리에서 에러가 발생했다면 나머지 1 건도 삽입되지 않는다.
INSERT IGNORE INTO memberTBL VALUES ('KHD', '강호동', '미국');
INSERT IGNORE INTO memberTBL VALUES ('LSM', '이상민', '서울');하지만 위처럼 수정하면 중복되더라도 무시하고 넘어가며 오류 메시지만 출력한다.
기본키가 중복되면 새로 삽입한 내용으로 수정하기
INSERT IGNORE INTO memberTBL VALUES ('KHD', '강호동', '미국')
ON DUPLICATE KEY UPDATE userName='강호동', addr='미국';
INSERT IGNORE INTO memberTBL VALUES ('LSM', '이상민', '서울');
ON DUPLICATE KEY UPDATE userName='이상민', addr='서울';ON DUPLICATE KEY UPDATE 구문은 기본키가 중복되지 않으면 일반 INSERT 문처럼 동작하고, 기본키가 중복되면 그 뒤의 UPDATE 문을 수행한다.
윈도우 함수와 피벗
1. 윈도우 함수 개념
윈도우 함수는 테이블의 행과 행 사이 관계를 쉽게 정의하기 위해 MySQL에서 제공하는 함수
OVER 절이 들어간 함수이다. 윈도우 함수를 활용하면 복잡한 SQL을 손쉽게 활용할 수 있다.
윈도우 함수와 함께 사용되는
집계 함수 AVG(), COUNT(), MAX(), MIN(), STDDEV(), SUM(), VARIANCE() 등이고
비집계 함수는 CUME_DIST(), DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), NTH_VALUE(), NTITLE(), PERCENT_RANK(), RANK(), ROW_NUMBER() 등이다.
STDDEV() : 표준 편차를 구하는 함수
VARIANCE() : 분산을 구하는 함수
CUME_DIST() : 상대적인 누적 분포를 계산하는 함수
DENSE_RANK() : 중복순위를 포함해서 순위를 계산하는 함수
LAG() : 이전 행 값을 리턴하는 함수
LEAD() : 다음 행 값을 리턴하는 함수
NTH_VALUE() : N번쨰 행을 나타내는 함수
NTITLE() : 레코드 수에 대해서 NTITLE()에 파라미터에 세팅된 값으로 나눈 비율로 순위를 구함 파라미터 값으로 레코드 행 전체를 나누어 그룹화
PERCENT_RANK() : 그룹 내의 백분위 순위 반환
ROW_NUMBER() : 조회된 값들의 순번을 매기는 함수
윈도우 함수 사용 형식
SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER (PARTITION BY 칼럼
ORDER BY WINDOWING절)
FROM 테이블명;2. 순위 함수
RANK(), NTITLE((), DENSE_RANK(), ROW_NUMBER() 등이 해당한다.
순위 함수의 형식은 다음과 같다.
<순위함수이름>() OVER(
[PARTITION BY <partition_by_list>]
ORDER BY <order_by_list>)3. 분석 함수
비집계 함수 중에서 CUME_DIST(), LEAD(), FIRST_VALUE(), LAG(), LAST_VALUE(), PERCENT_RANK() 등을 분석함수라고 함
분석 함수를 이용해 이동 평균, 백분율, 누계 등의 결과를 계산할 수 있다.
4. 피벗
피벗은 한 열에 포함된 여러 값을 여러 열로 변환하여 출력하고 필요하면 집계까지 수행하는 기능으로, 수행 결과가 피벗 테이블이 생성된다.
3. WITH 절과 CTE
WITH 절과 CTE의 개요
WITH 절은 기존의 뷰, 파생 테이블, 임시 테이블 등을 더 간결하게 표현하는 CTE(Common Table Expression)를 포함한 구문이다.
CTE는 비재귀적(non-recursive) CTE와 재귀적 CTE로 구분된다.
비재귀적 CTE
비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE이다. 복잡한 쿼리문을 단순하게 만들 대 사용하며 형식은 다음과 같다.
WITH CTE_테이블이름(열이름)
AS
(
<쿼리문>
)
SELECT 열이름 FROM CTE_테이블이름:쿼리문을 작성할 때 실제 데이터베이스에 있는 테이블을 사용했지만 CTE는 바로 위의 WITH절에서 정의한 CTE테이블 이름을 사용한다.
즉, WITH CTE테이블이름(열이름) AS ... 형식의 테이블이 하나 더 있다고 보는 것이다.
실습
