SQL 기본 (2)

Preface

 

이번 장은 너무 많은 내용을 담고 있어 두 파트로 나눠서 업로드한다.

---

 

2. 데이터의 변경을 위한 SQL문

 

 

- SQL의 분류

1) DML(Data Manipulation Language): 데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어

→ 트랜잭션이 발생한다.

2) DDL(Data Definition Language): 데이터베이스 개체를 생성, 삭제, 변경하는 역할

3) DCL(Data Control Language): 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문

 

 

- 트랜잭션(transaction): 테이블의 데이터를 변경할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것

 

 

- insert문: 테이블에 데이터를 삽입하는 명령어

1) 테이블 이름 다음에 나오는 열은 생략이 가능하다.

→ 생략할 경우엔 values 다음에 나오는 값들의 순서 및 개수가 테이블에 정의된 열 순서 및 개수와 동일해야 한다.

2) 특정 열만 입력하고 싶은 경우엔 테이블 뒤에 원하는 열 목록을 나열하면 된다.

→ 생략한 값에는 null 값이 들어간다.

insert into 테이블[(열1, 열2...)] values(값1, 값2...);

 

 

- auto_increment: 자동으로 1부터 증가하는 값을 입력해 준다.

1) insert에서는 해당 열이 없다고 생각하고 null을 입력하면 된다.

2) auto_increment로 지정할 때는 꼭 primary key 또는 unique로 지정해줘야 한다.

3) 데이터 형은 숫자 형식만 사용할 수 있다.

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 입력값을 변경하는 방법

alter table testtbl2 auto_increment = 100;
insert into testtbl2 values (null, '찬미', 23);

 

 

- auto_increment의 증가값을 지정하는 방법: 서버 변수는 @@auto_increment_increment 변수를 변경

create table testtbl2
	(id int auto_increment primary key,
     userName char(3),
	 age int);
alter table testtbl2 auto_increment = 1000; -- 초깃값 1000
set @@auto_increment_increment = 3;         -- 증가값 3
insert into testtbl2 values (null, '지민', 25);
insert into testtbl2 values (null, '유나', 22);
insert into testtbl2 values (null, '유경', 21);
select * from testtbl2;

 

 

- 여러 개의 행을 한꺼번에 입력할 수도 있다.

insert into 테이블이름 valuse (값1, 값2), (값3, 값4), (값5, 값6);

 

 

- 이미 만들어진 테이블에 column 추가하는 방법

alter table 테이블이름 add 열이름 열타입;

 

 

- insert into··· select 구문: 다른 테이블의 데이터를 가져와서 대량으로 입력

→ select문의 결과 열의 개수는 insert를 할 테이블의 열 개수와 일치해야 한다.

insert into 테이블이름 (열 이름1, 열 이름2, ...)
	select문;

 

→ 아예 테이블의 정의까지 생략하고 싶으면 create table··· select 구문을 사용하면 된다.

 

 

- update문: 기존에 입력되어 있는 값을 변경

→ where절은 생략이 가능하지만, 이를 생략하면 테이블의 전체 행이 변경된다.

update 테이블이름
	set 열1 = 값1, 열2 = 값2 ...
    where 조건;

 

 

- delete문: 기존에 입력되어 있는 값을 삭제

→ 행 단위로 삭제하며, where절이 생략되면 전체 데이터를 삭제한다.

delete from 테이블이름 where 조건;

 

 

- 데이터 삭제 방법

use sqldb;
create table bigTbl1 (select * from employees.employees);
create table bigTbl2 (select * from employees.employees);
create table bigTbl3 (select * from employees.employees);

delete from bigTBl1; -- DML문인 delete는 트랜잭션 로그를 기록하므로 오래 걸린다.
drop table bigTbl2;
truncate table bigTbl3;

→ 테이블 자체가 필요 없을 경우에는 DROP으로, 테이블의 구조는 남기고 싶을 때는 TRUNCATE로 삭제하는 것이 효율적이다.

 

 

- insert ignore: 오류가 발생하더라도 SQL문을 계속 진행한다.

insert ignore into memberTBL values('BBK', '비비코', '미국'); -- PK 중복
insert ignore into memberTBL values('SJH', '서장훈', '서울');
insert ignore into memberTBL values('HJW', '현주엽', '경기');

 

 

- on duplicate update: PK가 중복되지 않으면 일반 insert, PK가 중복되면 그 뒤의 update문이 수행

insert into memberTBL values('BBK', '비비코', '미국')
	on duplicate key update username = '비비코', addr = '미쿡';
insert into memberTBL values('DJM', '동짜몽', '일본')
	on duplicate key update username = '동짜몽', addr = '일본';

 

 

- with절: CTE를 표현하기 위한 구문

 

 

- CTE(Common Table Expression): 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며, 더 간결한 식으로 보여지는 장점이 있다.

 

 

- 비재귀적 CTE 예시

with abc(userid, total)
as
(select userid, sum(price * amount)
	from buytbl group by userid)
select * from abc order by total desc;