9/5 데이터베이스(DB) 서브쿼리(SubQuery), Any / All, Create / Insert / Delete / Update, 합집합 (Union), Alter Table, 기본키/ 외래키, AUTO_INCREMENT

AWS 클라우드환경 네이티브 수업 76일차

 

 

 

진행

1. 서브쿼리(SubQuery)

2. 비교 연산자 Any, All

3. 연관성 있는 서브쿼리(Correlated Subquery)와 없는 서브쿼리(Noncorrelated Subquery) (9/2 참고)

4. 합집합 (Union)

5. Create / Insert / Delete

6. Alter Table

7. 기본키(Primary Key) / 외래키(Foreign Key)

8. AUTO_INCREMENT ( AUTO_INCREMENT 값 초기화 / 1부터 재정렬하는 방법 )

 

 

조퇴에에에에에에에엑~~~~~~~!

 

 

 

 

 

요약

1. 서브쿼리(SubQuery)

2. 비교 연산자 Any / All

3. Create / Insert / Delete / Update

4. 합집합 (Union)

5. Alter Table

6. 기본키(Primary Key) / 외래키(Foreign Key)

7. AUTO_INCREMENT ( AUTO_INCREMENT 값 초기화 / 1부터 재정렬하는 방법 )

 

 

 

 

 

---

 

 

 

 

 

서브쿼리(SubQuery) (9/2 이어서)

 

 

 

일반적으로 다중 row을 반환하는 서브 쿼리가 있으면 where 절에 In이나 exists가 사용된다.

 

연관성이 없는 쿼리인 경우 In

연관성이 있는 쿼리인 경우 exists

 

비교연산자도 Any / All 은 사용할 수 있다.

 

 

 

In을 사용한 서브 쿼리의 예를 보자.

미국에 위치한 부서의 이름을 알아내는 쿼리이다.

아래의 두 쿼리는 같은 결과값을 낸다.

 

select d.department_name, l.country_id
from departments d, locations l
where d.location_id = l.location_id
and l.country_id = 'US';

 

select d.department_name
from departments d
where d.location_id in (select location_id from locations where country_id = 'US');

 

 

위와 같이 23개의 결과값이 나온다.

 

 

 

 

 

한가지 생각을 해보자.

위의 서브 쿼리의 아래쪽 문단에서 테이블은 departments 한개만 사용하고 있다.

 

만약 두 개의 테이블을 이용하여 country_id 까지 볼 수 있게 결과값을 내려고 한다면

 

select d.department_name, l.country_id
from departments d, (select location_id, country_id from locations where country_id = 'US') l
where d.location_id = l.location_id ;

 

위처럼 from 에 서브쿼리를 넣으면 해결된다.

조금만 생각해보면 해결 할 수 있는 문제이니 고민해보도록 하자.

 

 

 

 

 

---

 

 

 

 

 

비교 연산자 Any, All

 

둘 다 다중행 연산자이다.

 

Any는 조건을 만족하는 값이 하나라도 있다면 결과를,

All은 모든 조건을 만족하는 결과를

반환한다.

 

 

 

 

 

Any의 예를 보자.

구매 부서원(부서 id = 30) 중 누구인지 상관없이 아무나 한명의 월급보다 많이 받는 사원들의 정보를 알아내자.

아래의 두 쿼리는 값이 같다.

 

아래 쿼리는 아무나 한명보다 높으면 되므로 가장 낮은 월급인 사람보다 월급이 높다라는 조건으로 찾은 쿼리이다.

 

select employee_id, department_id, salary
from employees
where salary > (select	Min(salary)
			from	employees
			where	department_id = 30);

 

 

 

아래의 쿼리는 any를 이용하여 월급이 상대적으로 높기만 하면 부합한다라는 조건으로 찾은 쿼리이다. 

 

select employee_id, department_id, salary
from employees
where salary > Any (select	salary
			from	employees
			where	department_id = 30);

 

 

 

 

 

 

All의 예를 보자.

구매 부서원(부서 id = 30) 모두의 월급보다 많이 받는 사원들의 정보를 알아내자.

마찬가지로 아래 두 쿼리의 결과값은 같다.

 

아래의 쿼리는 모두의 월급보다 크면 되므로 최대치인 월급보다 크면 된다는 조건으로 찾은 쿼리이다.

 

select employee_id, department_id, salary
from employees
where salary >  (select	Max(salary)
			from	employees
			where	department_id = 30);

 

 

 

아래는 말 그대로 모두의 월급보다 큰 조건으로 찾는 쿼리이다.

 

select employee_id, department_id, salary
from employees
where salary > All (select	salary
			from	employees
			where	department_id = 30);

 

 

 

 

 

 

---

 

 

 

 

 

Create (생성) / Insert (추가) / Delete (삭제) / Update (수정)

 

DDL(Data Definition Language)인 Create

DML(Data Manipulation Language)인 Insert / Delete / Update

( 해당 설명은 8/29 에 있는 DDL/DML/DCL 참고)

 

각 사용법은 아래와 같다.

 

 

 

Create

테이블 및 column을 생성한다.

CREATE TABLE 테이블명 (
	column명1 변수형식(자리수) 조건,
	column명2 변수형식(자리수) 조건,
);

 

 

 

 

 

Insert

원하는 테이블, column에 매칭되는 값을 기존 데이터 뒤에 추가한다.

INSERT INTO 테이블명 (col1, col2, col3, col4) VALUES( 값1, 값2, 값3, 값4);

 

 

 

 

 

Delete 

where 절 조건을 달지 않게 되면 모든 데이터가 삭제되니 주의해야 한다.

DELETE 테이블명 WHERE 조건col = 조건값

 

 

 

Update

where 조건에 부합하는 행이 있다면 그 행의 원하는 column 값을 수정한다.

UPDATE 테이블 SET 변경할col = 변경할값 WHERE 조건col = 조건값

 

 

 

 

 

아래의 예를 보면서 한번에 이해해보자.

먼저 테이블을 생성한다. (여기서 set2가 아닌 set3을 주의하면서 볼 것)

 

create table set1(
	id1	int,
    	names1	varchar(5)
);

create table set3(
	id2	numeric,
    	names2	char(5)
);

 

 

 

그 후 테이블이 적은 설정값대로 생성되었는지 desc로 확인한다.

 

 

 

 

그 후 값을 원하는 테이블과 column 대로 넣는다.

 

insert into set1 values(1, 'AAA');
insert into set3 (id2, names2) Values(2, 'BB');

 

 

 

잘 들어갔는지 확인.

 

 

 

 

두 개의 테이블을 합쳐보자.

union은 바로 아래 문단에서 배운다.

 

 

 

 

테이블의 모든 내용을 지워보자.

 

DELETE FROM SET1;
DELETE FROM SET3;

 

 

 

그리고 새로운 값을 추가.

 

insert into set1 (id1, names1) Values(1, 'AAA');
insert into set1 (id1, names1) Values(1, 'AAA');
insert into set1 (id1, names1) Values(2, 'BBB');

insert into set3 (id2, names2) Values(2, 'BBB');
insert into set3 (id2, names2) Values(3, 'CCC');
insert into set3 (id2, names2) Values(3, 'CCC');

 

 

 

set3 테이블의 값을 확인해보면 아래처럼 잘 들어갔음을 볼 수 있다.

 

 

 

 

 

 

---

 

 

 

 

 

합집합 (Union)

 

union을 사용한다.

우리가 일반적으로 알고 있는 합집합과는 개념이 다르다.

구조도 이해할 겸 아래의 예를 보자.

 

select 1, 3, 4, 5, 7, 8
union
select 2, 4, 5, 8, 9

 

아래의 Select의 원소 갯수가 적으면 DB에서는 에러가 뜨게 된다.

즉, 원소의 갯수 자체가 데이터인 것이다.

 

따라서 동등한 조건으로 만들어서 합집합을 활용하려면

아래와 같이 고쳐주어야 한다.

 

select 1, 3, 4, 5, 7, 8
union
select 2, 4, 5, 8, 9, null

 

 

 

 

 

합칠 때 중복된 값이 있으면 중복값을 자동으로 지워서 결과를 내지만

중복값을 안 지우고 테이블을 합치는 방법도 있다.

Union all을 사용하면 된다.

아래의 데이터를 넣었다고 치고 예를 보자.

 

insert into set1 (id1, names1) Values(1, 'AAA');
insert into set1 (id1, names1) Values(1, 'AAA');
insert into set1 (id1, names1) Values(2, 'BBB');

insert into set3 (id2, names2) Values(2, 'BBB');
insert into set3 (id2, names2) Values(3, 'CCC');
insert into set3 (id2, names2) Values(3, 'CCC');

 

 

 

아래는 일반적으로 union을 사용한 결과이다. 

 

 

 

아래는 union all을 사용한 결과이다.

중복값이 모두 살아있는 것을 볼 수 있다.

 

 

 

 

 

---

 

 

 

 

 

Alter Table

 

Alter구문은 테이블을 수정한다.

사용하는 구문 종류는 아래와 같다.

 

 

 

 

 

Column 추가 (Add)

ALTER TABLE 테이블이름 ADD COLUMN column이름 자료형 추가조건;



ALTER TABLE settable ADD COLUMN phone int(32) NOT NULL;

 

 

 

Column 자료형 변경 (Modify)

ALTER TABLE 테이블이름 MODIFY COLUMN Column이름 자료형 추가조건;



ALTER TABLE UserInfo MODIFY COLUMN Userinfo varchar(16) NULL;

// 아래의 값은 값을 추가할 때마다 데이터에 강원도가 항상 들어가게 된다.
alter table member modify column addr varchar(100) default '강원도';

 

 

 

Column 이름까지 변경 (Change)

ALTER TABLE 테이블이름 CHANGE COLUMN 기존Col이름 바꿀Col이름 자료형 추가조건;



ALTER TABLE UserInfo CHANGE COLUMN Username Usernumber int(16) NULL;

 

 

 

Column 삭제 (Drop)

ALTER TABLE UserInfo DROP COLUMN 버릴Column;



ALTER TABLE table_name DROP COLUMN ex_column;

 

 

 

테이블 이름 변경 (RENAME)

ALTER TABLE 기존테이블 RENAME 바꿀테이블이름;



ALTER TABLE User RENAME UserInfo;

 

 

 

 

 

---

 

 

 

 

 

기본키(Primary Key) / 외래키(Foreign Key)

 

 

 

PRIMARY KEY (기본키)

• 테이블당 오직 하나의 필드에만 설정할 수 있기에 고유한 색인값(INDEX 값)을 가지고 있다.
  => 데이터를 빠르게 찾기 용이하다.
• 적용된 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가짐.
• 즉, 설정된 필드는 NULL 값을 가질 수 없으며, 중복 값을 가질수도 없다.

 

 

 

기본키는 2가지 방법으로 생성할 수 있다.

 

1. Create로 PRIMARY KEY 생성

 

CREATE TABLE member
(
	id	int	primary key,		-- 기본키 설정
	name	varchar(20) not null,
	addr	varchar(100),
	regdate	date
);

-- 데이터 추가
insert into member (id, name) values (1, '향단이');

insert into member (id, name) values (1, '향단이');	-- ERROR(기본키 중복)
insert into member (id, name) values (2, '향단이');
insert into member (id, name) values (3, '심순애');

 

 

 

 

 

2. Alter로 PRIMARY KEY 생성

 

// 새로운 필드에 추가 시
ALTER TABLE 테이블이름
ADD 필드이름 필드타입 PRIMARY KEY;
   


// 기존 있는 필드에 추가 시
ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 PRIMARY KEY

 

 

 

 

 

3. Alter로 PRIMARY KEY 삭제

 

ALTER TABLE 테이블이름
DROP PRIMARY KEY

 

 

 

 

 

FOREIGN KEY (외래키)

• 특정 테이블을 다른 테이블과 연결해주는 역할을 한다.
• 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어야 한다.

 

 

 

기본키와 마찬가지로 2가지 생성방법이 있다.

 

1. Create 로 FOREIGN KEY 설정

 

CREATE TABLE 테이블이름

(
    필드이름 필드타입,
    ...,
    FOREIGN KEY (필드이름)
    REFERENCES 참조하는테이블이름 (필드이름)
)

 

 

 

 

 

2. Alter로 FOREIGN KEY  생성

 

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름]
FOREIGN KEY (필드이름)
REFERENCES 참조하려는테이블이름 (필드이름)

 

 

 

 

 

3. Alter로 FOREIGN KEY 삭제

 

ALTER TABLE 테이블이름
DROP FOREIGN KEY 제약조건이름(FOREIGN KEY 이름)

 

 

 

 

 

아래의 예를 보면서 이해해보자.

 

-- 기본키를 설정한 테이블
create table writer (
	id	int NOT NULL auto_increment,
	name	varchar(20),
	primary key (id)
);

insert into writer (name) values ('고길동');
insert into writer (name) values ('희동이');

-- 기본키와 외래키를 설정한 테이블
create table board (
	id	int NOT NULL auto_increment,
	writer_id	int,
	content		varchar(4000),
	primary key	(id),
	foreign key	(writer_id) references writer(id)
);
insert into board (writer_id, content) values (3, '외래키 연습');
-- ERROR

 

 

위의 마지막 줄이 에러가 나는 이유는 외래키가 참조하는 기본키의 값 (3)이 존재하지 않기 때문이다.

참조하는 테이블인 writer에는 id가 2까지 밖에 없는데

board 테이블에서 id = 3으로 참조를 해와서 데이터를 추가하려하니 에러가 뜬다.

이처럼 외래키를 설정한 필드의 값은 참조하는 기본키 필드의 값에 존재하는 값이어야 한다.

 

 

 

 

 

또한, 지우는 것도 writer 테이블의 기본키를 참조하는 외래키가 존재하면

참조 외래키를 먼저 지워야 기본키 지우기 가능해진다.

따라서 만약 id = 1을 board 테이블이 참조하고 있다면,

아래와 같은 쿼리는 에러가 발생한다.

 

delete from writer where id = 1;

 

 

 

 

 

---

 

 

 

 

 

AUTO_INCREMENT

 

• 값을 입력하지 않아도 ROW가 생성될때 기본적으로 1부터 숫자가 들어간다.
• AUTO_INCREMENT로 이용할 컬럼은 INT 형 자료형을 가지고 있어야 한다.
• 중복이 없는 값이기 때문에 보톨 기본키(PRIMARY KEY)로 이용한다 (필수는 아님)
• 기본키가 아니라면 null이 들어가는 것을 방지하기 위해 NOT NULL 조건을 추가해야 한다
• 데이터를 생성할때 오류가 떴을 경우에도 숫자는 지속적으로 늘어나는 문제가 있다.

 

구조는 아래와 같다.

 

 

CREATE TABLE 테이블이름 (
  컬럼1 INT AUTO_INCREMENT PRIMARY KEY,
  컬럼2 STRING,
    ...
);

 

 

 

아래의 예를 보면서 이해해보자.

 

 

위와 같이 name1의 데이터만 추가했는데 num1의 데이터가 1씩 증가하면서 들어가는 것을 볼 수 있다.

 

 

 

 

 

---

 

 

 

 

 

AUTO_INCREMENT 값 초기화 / 1부터 재정렬하는 방법

 

-- AUTO_INCREMENT 값 초기화
-- ALTER TABLE [테이블명] AUTO_INCREMENT = [시작할 값]
ALTER TABLE BOARD AUTO_INCREMENT = 1;


-- AUTO_INCREMENT 가 들어간 컬럼을 1부터 다시 정렬하는 방법
SET @COUNT=0;	-- 변수설정
UPDATE [테이블이름] SET [컬럼이름] = @COUNT:=@COUNT+1;