Data Analysis/혼공SQL

[혼공SQL] 3-3. 데이터 변경을 위한 SQL문

알밤바 2022. 6. 22. 19:15
728x90
반응형

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

1. INSERT

1.1 INSERT 문의 기본 문법

  • 테이블에 행 데이터를 입력하는 SQL문
INSERT INTO 테이블[(열1, 열2, ...)] VALUES (값1, 값2, ...)

 

  • 테이블 이름 다음에 나오는 열 생략 가능
    • 열 생략 시 VALUES 다음에 나오는 값들의 순서 및 개수는 테이블을 정의할 떄의 열 순서 및 개수가 동일해야 함
use market_db;
create TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
insert into hongong1 values (1, '우디', 25);

 

  • ‘age’를 입력하고 싶지 않다면 테이블 이름 뒤에 열의 이름 추가해야 함
insert into hongong1 (toy_id, toy_name) values(2, '버즈');

 

  • 열의 순서를 바꿔서 입력하고 싶을 때 열 이름과 값을 순서에 맞춰 추가하면 됨
insert into hongong1 (toy_name, age, toy_id) values('제시', 20, 3);

 

1.2 AUTO_INCREMENT

  • 열을 정의할 때 1부터 증가하는 값을 입력해줌
  • INSERT에서는 해당 열이 없다고 생각하고 입력하면 됨
  • 단, AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY로 지정해줘야 함

 

📌 아이디(toy_id)열을 자동 증가로 설정

create table hongong2 (
	toy_id int auto_increment primary key,
	toy_name char(4),
  age int);

 

  • 데이터 추가
    • 자동 증가하는 부분은 NULL값으로 채워 놓으면 됨
insert into hongong2 values (null, '보핍', 25);
insert into hongong2 values (null, '슬랭키', 22);
insert into hongong2 values (null, '렉스', 21);

select * from hongong2;

 

  • AUTO_INCREMENT로 입력되는 다음 값을 100부터 시작하도록 하려면?
alter table hongong2 auto_increment = 100;
insert into hongong2 values (null, '재남', 35);
select * from hongong2;

 

📌 일정한 크기만큼 증가하도록 설정하기

  • 시스템 변수인 @@auto_increment_increment를 변경
create table hongong3 (
	toy_id int auto_increment primary key,
    toy_name char(4),
    age int);
alter table hongong3 auto_increment = 1000; -- 시작값을 1000으로 지정
set @@auto_increment_increment=3;  -- 증가값은 3으로 지정

 

  • 시스템 변수란?
    • MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수
    • 주로 MySQL의 환경과 관련된 내용이 저장되어 있으며 그 개수는 500개 이상
    • 시스템 변수 앞에 @@가 붙음
    • 시스템 변수의 값을 확인하려면 SELECT @@시스템변수를 실행하면 됨
    • 전체 시스템 변수의 종류를 알고 싶다면 SHOW GLOBAL VARIABLES를 실행하면 됨

 

insert into hongong3 values (null, '토마스', 20);
insert into hongong3 values (null, '제임스', 23);
insert into hongong3 values (null, '고든', 25);

select * from hongong3;

 

1.3 INSERT INTO ~ SELECT

  • 다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~ SELECT 구문을 사용해 해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있음
  • SELECT 문의 열 개수는 INSERT할 테이블의 열 개수와 같아야 함
INSERT INTO 테이블_이름 (열_이름1, 열_이름2, ...)
	SELECT 문 ;

 

📌 world 데이터베이스의 city 테이블로 실습

  • city 테이블의 개수 조회
select count(*) from world.city;

 

  • city 테이블의 구조 확인
desc world.city;

 

  • 데이터 5건만 확인
select * from world.city limit 5;

 

  • Name, Population 열만 가져와 새로운 테이블(city_popul) 생성
create table city_popul (city_name char(35), population int);

 

  • world.city 테이블의 내용을 city_popul 테이블에 입력
insert into city_popul
	select Name, Population from world.city;

 

  • 데이터가 잘 입력되었는지 확인
select * from city_popul;

 

2. UPDATE

2.1 UPDATE 문의 기본 문법

  • MySQL Workbench에서는 기본적으로 UPDATE, DELETE를 허용하지 않기에 설정 필요
    • [Edit] - [Preferences]
    • 빨간색 체크박스 부분 체크 해제한 후 실행
    • 그리고 MySQL 재실행

 

  • city_popul 테이블의 city_name 중 'Seoul'을 '서울'로 변경
use market_db;
update city_popul
	set city_name = '서울'
    where city_name = 'Seoul';
    
select * from city_popul where city_name = '서울';

 

  • 여러 열의 값을 변경
update city_popul
	set city_name = '뉴욕', population = 0
  where city_name = 'New York';
    
select * from city_popul
where city_name = '뉴욕';

 

2.2 WHERE가 없는 UPDATE문

  • UPDATE 문에서 WHERE 절은 문법 상 생략이 가능하지만, WHERE 절을 생략하면 테이블의 모든 행의 값이 변경됨
  • 전체 행의 값을 변경하는 경우는 별로 없기에 주의해야 함
  • 아래의 코드를 실행했다면 where 절이 없기 떄문에 city_name 열의 모든 값이 ‘서울’로 변경됨
-- 문제가 되는 코드 (절대 실행x)
update city_popul
	set city_name = '서울';

 

📌 전체 테이블의 내용을 변경하는 경우

  • population의 단위를 10,000명 단위로 변경
    • 모든 population 열을 한꺼번에 10,000으로 나누기
update city_popul
	set population = population / 10000;

select * from city_popul limit 5;

단위 변경 전 데이터

 

단위 변경 후 데이터

 

3. DELETE

  • city_popul 테이블에서 ‘New’로 시작하는 도시를 삭제
delete from city_popul
	where city_name like 'New%';

 

  • ‘New’로 시작하는 도시 중 몇 건만 삭제
    • LIMIT 구문 사용
delete from city_popul
	where city_name like 'New%'
    limit 5;

 

4. 대용량 테이블 삭제

  • 대용량 테이블 생성
create table big_table1 (select * from world.city, sakila.country);
create table big_table2 (select * from world.city, sakila.country);
create table big_table3 (select * from world.city, sakila.country);
create table big_table4 (select * from world.city, sakila.country);

select count(*) from big_table1;

 

  • 대용량 테이블 삭제
    • DELETE : 빈 테이블을 남김 / 오래 걸림
    • DROP : 테이블 자체를 삭제 / 빠름
    • TRUNCATE : 빈 테이블을 남김 / 빠름
      • DELETE와 달리 WHERE 문을 사용할 수 없음
      • 조건 없이 전체 행을 삭제할 때만 사용
delete from big_table1;
drop table big_table2;
truncate table big_table3;

 

  • 대용량 테이블의 전체 내용을 삭제할 때 테이블 자체가 필요 없을 경우 DROP 사용
  • 테이블의 구조를 남겨놓고 싶다면 TRUNCATE 사용
728x90
반응형