[MySQL] 서브쿼리 (Sub Query)

서브쿼리 (Sub Query)

 

서브쿼리는 SQL문 안에 들어가는 또 다른 SELECT문을 의미한다.

select ... from where 컬럼 = (select ... );

-- 김사과보다 포인트가 많은 회원을 검색

select point from member where name='김사과'; -- 김사과의 포인트 검색
select * from member wherer point > 150; -- 150보다 많은 멤버의 정보 검색
select * from member where point > (select point from member where name='김사과');
-- 서브 쿼리를 써서 김사과보다 포인트가 많은 회원을 검색할 수 있다.

1. 단일 행 서브쿼리

결과가 하나의 값만 나오는 서브쿼리

-- 포인트가 가장 높은 회원을 검색
select * from member where point = (select max(point) from member);

2. 다중 행 서브쿼리

결과가 여러 개 나오는 서브쿼리 (in, any, all을 사용)

-- 포인트가 200 이상인 userid 목록에 포함된 회원을 검색
select * from member where userid in (select userid from member where point >= 150);

3. from절 서브쿼리

서브쿼리 결과를 임시 테이블처럼 사용하는 방식

select name, point from (select name, point from member where point >= 150) as high_point_member;

4. select절 서브쿼리

조회 결과에 서브쿼리 결과를 함께 보여주는 방식

-- 각 회원의 포인트와 전체 평균 포인트를 함께 검색
select name, point, (select avg(point) from member) as avg_point from member;

5. exists 서브쿼리

존재 여부를 확인할 때 사용

-- 주문 내역이 있는 회원을 검색
select * from member m where not exists (select 1 from orders o where o.member_idx = m.idx);

6. 주문 테이블 생성

create table orders (
    order_id int auto_increment primary key,
    member_idx int not null,
    product_name varchar(100) not null,
    price int not null,
    order_date datetime default now(),
    foreign key (member_idx) references member(idx)
);

insert into orders(member_idx, product_name, price)
values
(1, '키보드', 50000),
(1, '마우스', 30000),
(2, '모니터', 250000),
(2, '노트북 거치대', 40000),
(3, 'USB', 15000),
(4, '노트북', 1200000),
(4, '헤드셋', 90000);

 

6-1. 주문한 적이 있는 회원들만 조회 (in 사용)

select * from member where idx in (select member_idx from orders);

 

6-2. 주문한 적이 없는 회원들만 조회 (not in 사용)

select * from member where idx not in (select member_idx from orders);

 

6-3. 가장 비싼 상품을 주문한 회원 조회

-- 1. orders에서 가장 비싼 가격을 찾기
-- 2. 그 가격의 주문을 한 회원 번호를 찾기
-- 3. 해당 회원 번호를 가진 회원 정보를 조회하기
select * from member where idx = 
(select member_idx from orders where price = (select max(price) from orders));

 

6-4. 가장 비싼 상품을 주문한 회원의 이름과 상품명을 조회

-- join을 활용해서 조회
select m.name, o.product_name, o.price from member m join orders o on m.idx = 
o.member_idx where o.price=(select max(price) from orders);

7. 테이블 복사

create table orders_copy (
    order_id int auto_increment primary key,
    member_idx int not null,
    product_name varchar(100) not null,
    price int not null,
    order_date datetime default now(),
    foreign key (member_idx) references member(idx)
);

-- 기존 테이블의 데이터를 다른 테이블로 복사할 수 있다.

insert into orders_copy(select * from orders);
-- 테이블의 형태가 동일하면 insert 서브쿼리도 가능하다

create table orders_copy_copy(select * from orders);
-- 복사한 테이블로 새로운 테이블을 만들 수도 있다.

8. UNION

• 여러 개의 select 결과를 하나로 합쳐주는 기능
• 조회 결과를 위아래로 이어 붙이는 기능

select name, address1 from member where address1 like '서울%'
union
select name, address1 from member where address1 like '부산%';

 

이때, UNION은 중복데이터를 제거하고 합친다.

9. UNION ALL

UNION과 동일하게 select 결과를 합쳐주지만 중복데이터를 제거하지않고 모두 합쳐준다.

10. 사용자 계정

• 데이터베이스에 접속할 수 있는 로그인 계정
• root 계정은 모든 권한을 가진 계정이기 때문에 실제 사용 시 위험할 수 있음
• 프로젝트별로 계정을 따로 만들고, 필요한 권한만 부여하는 것이 일반적이다.

-- create user '계정명'@'접속위치' identified by '비밀번호';

-- localhost : 같은 컴퓨터(내 컴퓨터)에서만 접속 > 외부에서 접근할 수 없음.

-- 'apple'@'%' : 어디서든 접속 가능.

-- 'apple'@'192.168.0.%' : 198.168.0.으로 시작하는 내부망에서만 접속이 가능

-- 'apple'@'192.168.0.10' : 특정 ip에서만 접속 가능 (해킹을 막기 위해)

 

권한부여 방법

-- grant 권한종류 on 데이터베이스명/테이블명 to '계정명'@'접속위치';

-- all : 모든 일반 권한, select, insert, update, delete, create, drop, alger, index

-- ai.* : ai 데이터베이스 안의 모든 테이블 (특정 테이블만 원한 다면 * 대신 테이블 명으로 기입)

-- 권한 회수
revoke delete on 데이터베이스명/테이블명 from '계정명'@'접속위치';

-- 사용자 비밀번호 변경
alter user '계정명'@'접속위치' identified by '변경할 비밀번호';

-- 사용자 삭제
drop user '계정명'@'접속위치';