[13일차] 서브 쿼리, 사용자계정(작성중)

1. 서브쿼리 Sub Query

• SQL문 안에 들어가는 또 다른 SELECT문
• 예시

-- 김사과보다 포인트가 많은 회원을 검색
select point from member where name = '김사과';
select * from member where point > 150;

-- 서브쿼리를 사용하여 한 번에 작성
select * from member where point > (select point from member where name = '김사과');

 

1. 단일 행 서브쿼리

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

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

-- 서브쿼리만 실행해보면 max(point)가 250인 것 확인 가능
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 절 서브쿼리

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

-- 포인트가 150 이상인 회원의 이름과 포인트를 조회
select name, point from (select name, point from member where point >= 150) as high_point_member;

-- from 절 서브쿼리를 조회하면 테이블 형태로 나올 것
select name, point from member where point >= 150;

 

4. select 절 서브쿼리

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

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

 

5. exists 서브쿼리

• 존재 여부를 확인할 때 사용
• 아래 orders 예제를 모두 푼 후 다시 보자

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

-- select 1 : 하나라도 존재하는지 확인

 

# 예제를 풀어보자

• orders 테이블 생성 후 데이터 삽입

-- orders 테이블 생성
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);

-- 잘 만들어졌는지 확인
select * from orders;

• 예제를 풀어보자

-- 1. 주문한 적이 있는 회원들만 조회
select * from member where idx in (select member_idx from orders);

-- 2. 주문한 적이 없는 회원들만 조회
select * from member where idx not in (select member_idx from orders);

-- 3. 가장 비싼 상품을 주문한 회원 조회
select * from member where idx = (select member_idx from orders where price = (select max(price) from orders));

-- 4. 가장 비싼 상품을 주문한 회원의 이름과 상품명, 가격을 조회
select m.name, o.product_name, o.price from member as m left join orders as o on m.idx = o.member_idx
where o.price = (select max(price) from orders);

-- 4번 처음 풀 때, 아래처럼 만들었는데 결과는 같음
select m.name, o.product_name, o.price
	from member as m left join orders as o on m.idx = o.member_idx
    where idx = (
		select member_idx from orders where price = (select max(price) from orders)
        )
	order by o.price desc limit 1;

 

6. 서브쿼리로 스키마와 테이블 복사

• orders 스키마를 복사해와서 orders_copy 스키마 생성

-- orders 테이블 형태만 복사
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)
);

-- 형태만 복사했으니 데이터는 없는 상태
select * from orders_copy;

 

• 서브쿼리를 사용하여 orders 테이블의 데이터를 orders_copy로 복사 (테이블 형태가 똑같아야 가능)

-- 서브쿼리로 데이터 복사
insert into orders_copy (select * from orders);

-- 잘 복사되었는지 확인
select * from orders_copy;

---

2. UNION

• 여러 개의 select 결과를 하나로 합쳐주는 기능
• 조회 결과를 위 아래로 이어 붙이는 기능
• 중복 데이터 제거
• union all : 중복 데이터 제거하지 않음

 

• 서울 회원과 부산 회원 합치기

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

• 새로운 voca_new 테이블 생성 후, 데이터 삽입하고 잘 들어갔는지 확인

-- voca_new 테이블 생성
create table voca_new (
	eng varchar(50) primary key,
    kor varchar(50) not null,
    lev int default 1,
    regdate datetime default now()
);

-- 데이터 삽입
insert into voca_new values('pineapple', '파인애플', 2, now());
insert into voca_new values('papaya', '파파야', 2, now());
insert into voca_new values('blueberry', '블루베리', 1, now());

-- 잘 들어갔는지 확인
select * from voca_new;

• voca의 eng, kor, lev와 voca_new의 eng, kor, lev를 조회

select eng, kor, lev from voca
union
select eng, kor, lev from voca_new;

• 겹치는 데이터 사과 추가 후 다시 union 해보자
  -> 중복되는 데이터는 보이지 않는 것이 확인 가능

insert into voca_new values('apple', '사과', 1, now());

select eng, kor, lev from voca union select eng, kor, lev from voca_new;

• 그렇다면 voca와 voca_new의 eng, kor, lev, regdate를 모두 조회
  -> regdate는 다르기 때문에 중복 데이터가 아니라 사과가 2개가 조회됨

select eng, kor, lev, regdate from voca union select eng, kor, lev, regdate from voca_new;

• 중복데이터를 제거하지 않고 조회

select eng, kor, lev from voca union all select eng, kor, lev from voca_new;

---

3. 사용자 계정

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

1. 계정 생성

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

• localhost : 같은 컴퓨터(내 컴퓨팅)에서만 접속
• 'apple'@'%' : 어디서든 접속 가능
• 'apple'@'192.168.0.%' : 192.168.0.으로 시작하는 내부망에서만 접속 가능
• 'apple'@' 192.168.0.10' : 특정 ip에서만 접속 가능

-- apple이라는 계정을 만들고 비밀번호는 1111, localhost로 접속하도록 계정 만들기
create user 'apple'@'localhost' identified by '1111';

 

2. 권한 부여 및 확인

권한 부여	grant 권한종류 on 데이터베이스명.테이블명 to '계정명'@'접속위치';
권한 확인	show grants for '계정명'@'접속위치';

• all : 모든 일반 권한. select, insert, update, delete, create, drop, alter, index
• ai.* : ai 데이터베이스 안의 모든 테이블
• *.* : 모든 데이터베이스 안의 모든 테이블
• ai.member : ai 데이터베이스 안의 member 테이블

 

• apple, banana, orange 계정 생성 후 아래와 같이 권한 부여 
  • apple : 모든 권한
  • banana : select 권한만 
  • orange : select, insert, update, delete 권한

-- apple 계정에 모든 권한 부여
grant all on ai.* to 'apple'@'localhost';

-- 권한 확인
show grants for 'apple'@'localhost';

-- banana 계정 생성 후 select 권한만 부여
create user 'banana'@'localhost' identifited by '2222';
grant select on ai.* to 'banana'@'localhost';

-- orange 계정 생성 후 select, insert, update, delete 권한 부여
create user 'orange'@'localhost' identified by '3333';
grant select, insert, update, delete on ai.* to 'orange'@'localhost';

• apple, banana, orange 계정으로 로그인해보고 파일 만들어보기

-- 김사과.sql
use ai;
show databases;
show tables;

select * from member;

-- root 계정이 아닌 다른 계정에서는 create user 불가능
-- create user 'banana'@'localhost' identified by '2223';

 

3. 권한 회수하기

 

4. 사용자 비밀번호 변경하기

 

# ip 주소란

 

---

4. MySQL 함수

1. 문자열 함수

 

2. 수학 함수

 

3. 날짜 함수

 

4. 조건 함수

 

5. 형변환 함수

 

6. 집계 함수

---