JDBC - 데이터 UPDATE, INSERT, DELETE

 

도서 '데이터베이스 첫걸음'을 보고 책 내용과 그 이외의 부족한 부분을 채워가며 공부한 내용입니다.

 

---

키워드 : INSERT, UPDATE, DELETE

---

1. 데이터 갱신, 삽입, 제거

update

update 테이블 명 set 열명 = 값 // set으로 지정한 열 이외의 것은 변경 x

update 테이블 명 set 열명 = 값 where 조건

• 기준 데이터를 갱신(변경)할 때 update 문 사용
• 갱신은 where 구문에 부합하는 행 전부에 대해 수행되므로 1행을 갱신하려면 where 대상 행을 유니크하게 지정해야함
• 복수 열 동시 갱신
  • 열별로 구분해 지정(set 열명1 = 값1, 열명2 = 값2)
  • update city set name = 'Siheung', population 429390 where countrycode = 'KOR' and district = 'Kyonggi' and name = 'Shihung'

update 구문 예시

 

 

 

Insert

show create table 테이블명\G           //테이블 정의 파악, \G = ; 대신 사용할 수 있는 딜리미터, 결과를 세로보기함

desc 테이블명;                               //테이블 정의 자체가 아닌 열의 정보 파악의 경우 사용

테이블 정의 및 열 정보 파악

• 열수는 5(ID, Name, CountryCode, District, Population)
• ID열과 Population 열의 데이터형은 int, 나머지는 char
• id열은 기본 키(Primary key)로 설정, auto_increment 속성이 붙어있음
  • 기본 키(Primary key)
    • 테이블의 행을 유일하게 특정할 수 있는 유니크한 값을 저장하는 열
    • update, delete를 할 경우 기본 키를 대상으로 변경 시 편리
    • auto_increment 속성 = 기계적으로 유니크한 번호를 할당 -> insert시 값을 입력 안해도 알아서 지정
• char(n) : 해당 자료형의 열 속에 넣을 수 있는 문자열의 최대 길이 = n

 

insert into 테이블명(열1, 열2, ...) values(값1, 값2, ...)

insert into city values(DEFAULT, 'Gimpo', 'KOR', 'Kyonggi', 349900);

insert into city('name', 'countrycode', 'district', 'population') values('Gimpo', 'KOR', 'Kyonggi', 344900);

• Default = insert 시 해당 테이블의 기본값을 사용
  • 현재 city 테이블은 countrycode열을 제외한 4개의 열 전체에 기본값을 설정
  • insert into city (CountryCode) values ('KOR');
• limit n = n개의 행만 돌려줌

insert into city (CountryCode) values ('KOR'); 결과가 4080

 

데이터 입력에 자주 사용되는 구문

insert into 테이블 1 select * from 테이블 2;

• values 대신에 입력한 값(레코드)으로 select문의 결과를 사용하는 것

• 위의 경우 insert 대상의 열의 열 정의가 select 결과와 같기 때문에 각종 구문을 추가해 실행 가능(where ...)

 

복수 행의 입력

insert into city('name', 'countrycode', 'district', 'population') values('Gimpo', 'KOR', 'Kyonggi', 344900);
insert into city('name', 'countrycode', 'district', 'population') values('Pocheon', 'KOR', 'Kyonggi', 155192);
insert into city('name', 'countrycode', 'district', 'population') values('Hwaseong', 'KOR', 'Kyonggi', 613091);

==

insert into city('name', 'countrycode', 'district', 'population') values('Gimpo', 'KOR', 'Kyonggi', 344900), ('Pocheon', 'KOR', 'Kyonggi', 155192), ('Hwaseong', 'KOR', 'Kyonggi', 613091);

 

Delete

delete from 테이블명;

delete from 테이블명 where 조건;

• 갱신하는 대상을 where 구문으로 추려낸 다음 delete 하는 방식이 주를 이룸

 

2. 뷰 작성 및 복수 테이블에서 선택하기

View(뷰)

Create VIEW 뷰이름 (열명1, 열명2, 열명3, ...) as select문;

create view citykyonggi as select id, name, population from city where countrycode = 'KOR' and district = 'Kyonggi';

• 복잡한 select 문을 일일이 매번 기술할 필요x
• 필요한 열과 행만 사용자에게 보여줄 수 있고, 갱신 시에도 뷰 정의에 따른 갱신으로 한정
• 데이터의 저장없이 위의 장점들을 실현 가능
• 뷰를 지워도 테이블은 영향을 받지 않음

 

뷰로의 입력, 갱신의 제한

• 어떤 행이 대응하는지 모르거나 어떤값을 넣으면 좋을지 모르는 경우 갱신할 수 없음
  • group by로 집약한 수치나 distinct로 얻은 값을 갱신하는 경우 결과의 기반이 되는 테이블의 n행 중 어떤 수치를 갱신하는 것이 좋은지 모름
  • 2가지 이상의 테이블 조합해 작성한 뷰를 갱신할 때 어느 테이블을 갱신하면 좋은지 알 수 없음
  • 뷰에서 원래 테이블의 일부열만 선택 되었다면 데이터를 삽입할 때 선택된 열 이외의 열에 기본값(default)도 없고 null도 허용되지 않는(NOT NULL) 상황에서는 해당 열에 넣을 수 있는 값이 없어서 실질적으로 뷰로의 삽입 불가능

 

서브쿼리의 실행

통상적인 SELECT문의 결과 = 테이블(선택한 열과 행으로 구성된)
그 중 특수한 형태로 하나의 행과 열로 구성된 테이블(즉, 단일값) 발생
단일값으로 구성된 테이블의 경우 마치 데이터처럼 다루거나 수치처럼 취급해 조건문에 이용가능

쿼리 결과를 마치 테이블 또는 값으로 외부 쿼리에서 사용할 수 있게 도와주는 쿼리

create view citykorea as select id, name, district, population from city where countrycode ='KOR';

select count(*) from citykorea where population > (select avg(population) from citykorea);

select district, name, population from citykorea as c1 where population > (select avg(population) from citykorea as c2 where c1.district = c2.district group by district);

• 각 행정구역에서 인구 평균을 구해 각 행정구역 내에서 인구가 평균보다 많은 도시를 집어냄
• 평균보다 많아야 하기에 1건밖에 등록되지 않은 행정구역은 제외

 

결합

SQL은 단일테이블 뿐만 아니라 2개 이상의 테이블을 대상으로 실행하는 것도 가능

 

내부 결합

• 결합 = 2개의 테이블에서 필요한 열을 가지고 올 때 행을 결합하기 위한 조건을 'ON'으로 지정
• 내부 결합은 이 ON으로 지정한 결합조건에 일치하는 행만을 2개의 테이블로부터 가져옴

내부 결합

select 선택하고 싶은 열 리스트 from 첫번째 테이블명 INNER JOIN 두번째 테이블명 ON 결합조건;

select countrylanguage.*, country.name from countrylanguage INNER JOIN country ON countrylanguage.countrycode = country.code where language = 'KOREAN';

• 결합 조건으로 'CountryCode', {'CountryLanguage' 테이블에서 '모든 열(*)'}, {country 테이블에서 'name'열}을 가져오고 있음
• 2개의 테이블의 열이 어느 테이블에서 온것인지 구별할 때 테이블명.열명
• language는 country 테이블에만 있어서 country.language == language
• where, order by, limit, group by, having 지정 가능

 

외부 결합

• 내부 결합 = 2개의 테이블에서 결합조건이 일치하는 것만 얻음
• 외부 결합 = 한쪽 테이블을 기준으로 전체 행을 표시하고 다른 테이블은 값이있으면 표시

select 선택하고 싶은 열 리스트 from 첫번째 테이블명 LEFT INNER JOIN 두번째 테이블명 ON 결합조건;

• 첫번째 테이블이 기준

 

---

참고 자료

1. 미크, 기무라메이지, 데이터베이스 첫걸음(출판지 : 한빛미디어, 2016)