[MySQL] Day 05: 쿼리문 정리

2022/05/09

 

**MySQL
1.Sub Query
=>다른 SQL 절에 포함된 SQL
일반적으로 select 구문에서 많이 사용하는데 insert 구문이나 delete, update 구문에서도 사용 가능

1)종류
=>sub query 가 리턴되는 행의 개수에 따른 분류
단일 행 서브 쿼리: 서브 쿼리가 리턴하는 행의 개수가 0개 이거나 1개인 경우

다중 행 서브 쿼리: 서브 쿼리가 리턴하는 행의 개수가 0개 이상인 경우
                   2개 이상의 행을 리턴한다면 단일 행 연산자(=, !=, >, >=, <, <=)를 직접 사용 안됨 
   이런 경우는 다중 행 연산자(in, not in, all, any 등) 와 사용해야 합니다.

=>서브 쿼리가 작성된 위치에 따른 분류
from 절에 사용된 서브 쿼리: Inline View

where 절에 사용된 서브 쿼리: Sub Query

2)서브 쿼리는 메인 쿼리 보다 먼저 한 번만 수행되고 먼저 수행해야 하기 때문에 ( )로 감싸게 됩니다.

3)일반 서브 쿼리로 해결할 수 없는 상황은 from 절에서 2개 이상의 테이블 이름이 사용되는데 select 절에서 2개 테이블의 컬럼을 출력해야 하는 경우는 서브 쿼리로 해결할 수 없고 join을 이용해야 합니다.


4)usertbl 테이블에서 name 이 김태연 인 데이터의 birthyear 보다 크거나 같은 birthyear 를 가진 데이터의 모든 정보를 조회

select birthyear
from usertbl
where name='김태연';

select *
from usertbl
where birthyear >= 1989;

-- 서브 쿼리를 이용해서 해결
select *
from usertbl
where birthyear >= (select birthyear
from usertbl
where name='김태연');

5)usertbl 테이블에서 addr 이 광주인 데이터와 birthyear 가 같은 데이터의 모든 컬럼을 조회
select birthyear
from usertbl
where addr='광주';

select *
from usertbl
where birthyear = 1994 or birthyear = 1991;

-- 에러 : 서브쿼리가 리턴하는 데이터가 2개 이상이라서 단일 행 연산자 사용 못함
select *
from usertbl
where birthyear = (select birthyear
from usertbl
where addr='광주');

-- 다중 행 연산자
-- = : in, != : not in

select *
from usertbl
where birthyear in (select birthyear
from usertbl
where addr='광주');

-- <, <=, >, >=: ALL 이나 ANY 와 함께 사용
-- ALL 이면 전체가 되는 것이고 ANY는 아무거나 하나보다
-- MIN, MAX를 알면 대체가 가능합니다.

select *
from usertbl
where birthyear < ALL (select birthyear
from usertbl
where addr='광주');

2. 정렬
=>order by 절에서 수행
=>order by 컬럼이름이나 연산식 또는 select 에서 사용한 컬럼의 인덱스 [asc | desc], 다음 정렬 조건...;
=>asc는 오름차순이고 desc는 내림차순인데 asc 는 기본값이라서 생략이 가능
=>여러 개의 정렬 조건을 작성하게 되면 앞의 정렬 조건의 값이 같은 경우 뒤의 조건이 적용
=>관계형 데이터베이스의 행 과 열의 순서는 없다 라는 특징을 가지고 있으므로 여러 개의 행을 조회할 때 나오는 순서는 예측할 수 없으므로 되도록이면 2개 이상의 행이 리턴될 것 같으면 정렬을 해주는 것이 좋습니다.

1)usertbl 테이블의 name 과 birthyear를 조회하는데 birthyear의 오름차순으로 정렬해서 출력
select name, birthyear
from usertbl
order by birthyear asc;

select name, birthyear
from usertbl
order by 2 asc;

2)usertbl 테이블의 name 과 birthyear를 조회하는데 birthyear의 오름차순으로 정렬해서 출력하는데 birthyear 의 값이 같으면 name의 내림차순으로 정렬

select name, birthyear
from usertbl
order by birthyear asc, name desc;


3.distinct
=>select 절에서 맨 앞에 1번만 작성할 수 있음
=>select 절의 나열된 모든 컬럼의 값이 일치하는 경우만 제외

-- usertbl 테이블에서 addr 의 값을 중복되지 않게 조회
select distinct addr
from usertbl;

4.페이징, TOP-N
=>select 구문의 마지막 절에 limit 시작위치번호, 데이터개수 를 추가해주면 됩니다.
숫자를 1개만 입력하면 시작위치번호부터 입력한 숫자 개수만큼 조회합니다.
=>시작 인덱스는 0부터

1)usertbl 테이블에서 birthyear 가 가장 큰 5개의 데이터를 조회
select *
from usertbl
order by birthyear desc
limit 0, 5;
 
2)usertbl 테이블에서 birthyear 가 처음부터 5개의 데이터를 조회
select *
from usertbl
order by birthyear desc
limit 5;

5.데이터 그룹화
1)그룹 함수
sum
avg
min
max
count

stddev
var_samp
=>함수들의 매개변수는 컬럼이나 연산식이 되는데 count 에서는 * 가능
=>함수들은 null 인 데이터는 제외하고 집계를 수행합니다.

2)groupy by 절
=>컬럼이나 연산식으로 그룹화하고자 할 때 사용
=>2개 이상 그룹화 가능
=>이 절이 수행된 이후 그룹 함수를 사용할 수 있습니다.
=>from 과 where 절에서는 그룹 함수를 사용할 수 없습니다.

3)having 절
=>group by 다음에 작성하는 절로 group by 이후에 행 단위 추출을 위한 조건을 설정합니다.
=>그룹 함수를 사용하는 것이 일반적입니다.
=>데이터베이스 종류에 따라서는 group by 없이 사용할 수 있는 경우도 있습니다.
=>having 절을 잘못 사용하면 비효율적인 sql 구문이 만들어 질 수 있습니다.

4)실습
-- usertbl 테이블의 데이터 개수 조회
select count(userid)
from usertbl;

select count(*)
from usertbl;

-- buytbl 의 평균 amount 조회
select avg(amount)
from buytbl;

-- buytbl 테이블에서 userid 별 평균 amount 조회
select userid, avg(amount)
from buytbl
group by userid;


-- buytbl 테이블에서 userid 의 개수가 3번이상 등장한 데이터의 userid를 조회
select userid
from buytbl
group by userid
having count(userid) >= 3;

-- 비효율적인 having 의 사용:where 절에 사용 가능한 조건은 where 절에 사용
-- group by에서 그룹화를 하게 되면 그룹 함수 와 group by에서 그룹화 하지 않은 컬럼을 같이 출력 못함
-- mysql 은 그룹화하지 않은 항목을 출력하면 첫번째 것 하나를 출력
select addr, name, count(*)
from usertbl
group by addr;

5)전체 총계 나 중간 소계를 조회 - with rollup 이용
select userid, avg(price)
from buytbl
group by userid
with rollup;

6.별명 사용
=>열 이름 이나 연산식에 별명을 사용할 수 있는데 열 이름 이나 연산식 뒤에 공백을 하나주고 별명을 입력하면 되는데 한글을 사용하거나 공백이 있는 경우는 " "로 묶어 주어야 합니다.
별명 앞에 as를 추가해도 됩니다.
이 형태는 별명이라서 select 절 이후에 사용되는 order by에서 사용이 가능합니다.
=>from 절에서 테이블 이름 뒤에서 다른 이름을 부여할 수 있는데 이 경우는 별명이 아니고 다른 이름으로 변경하는 것입니다.
from 절에서 테이블 이름에 다른 이름을 부여하면 그 이후 절에서는 다른 이름만 사용해야 합니다.

=>order by 절에서 주소 대신에 addr 이고 작성해도 됩니다.
select addr as "주소"
from usertbl
order by 주소;

=>select 절에서 U 대신에 usertbl을 사용하면 에러가 발생
select U.addr as "주소"
from usertbl U
order by 주소;

7.함수
=>매개변수를 받아서 작업을 수행한 후 리턴을 해주는 개체
=>그룹 함수가 아닌 함수는 컬럼을 매개변수로 대입하면 컬럼의 각 행에 작업을 수행한 후 결과를 모아서 컬럼으로 리턴

1)제어 흐름 함수
=>데이터베이스에서는 null 과 다른 종류의 데이터가 산술 연산을 하게되면 결과가 null
=>null 관련 함수
ifnull(수식1, 수식2): 수식1이 null 이 아니면 수식1이 리턴되고 수식1이 null 이면 수식 2가 리턴

nullif(수식1, 수식2): 수식1 과 수식 2가 같으면 null을 리턴하고 다르면 수식1을 리턴


EMPLOYEE_SALARY 테이블에서 SALARY 의 값이 null 이면 0으로 그렇지 않으면 원래 값으로 조회
select ifnull(salary, 0) from employee_salary;

2)문자열 함수
=>좌우 공백 제거: trim
=>대소문자 변환: upper, lower

=>분할: substring(문자열, 시작 위치, 길이) - 문자열에서 시작 위치 부터 길이만큼 잘라서 리턴
관계형 데이터베이스는 하나의 컬럼에 여러 개의 값을 저장할 수 없습니다.

3)숫자 함수
=>반올림, 올림, 버림 함수: CELIING, FLOOR, ROUND 

4)날짜 관련 함수
=>날짜 연산 함수: addtime(날짜, 시간), subtime(날짜, 시간)
adddate(날짜, 날짜차이), subdate(날짜, 날짜차이)

=>현재 날짜 및 시간
current_date(), current_time(), now(), current_timestamp()

=>문자열을 날짜로 변환: 일반적인 날짜 포맷의 문자열은 날짜로 인식
str_to_date(날짜 형식의 문자열, 날짜 포맷)


8.데이터 삽입
=>insert into 테이블이름(컬럼이름 나열) values(값을 나열);

=>테이블을 만들 때 작성한 컬럼의 순서대로 모든 값을 입력할 때는 컬럼 이름을 나열하지 않아도 됩니다.

=>auto_increment 가 적용된 컬럼에 값을 대입하지 않으면 일련번호가 대입됩니다.

=>default 가 설정된 경우는 값을 대입하지 않으면 default 값이 대입됩니다.

=>auto_increment 나 default 가 설정되지 않은 경우 값을 대입하지 않으면 null이 대입됩니다.

=>usertbl 테이블에 userid 가 kjn 이고 name은 제니 birthyear는 1996 addr은 서울 mobile은 01012341234 mdate 는 1996-01-16 인 데이터를 삽입

insert into usertbl(userid, name, birthyear, addr, mobile, mdate)
values('kjn', '제니', 1996, '서울', '01012341234', '1996-01-06');

=>여러 개의 데이터를 한꺼번에 삽입 가능
insert into 테이블이름(컬럼이름 나열) values(값1, 값2...), (값1, 값2..);
값은 컬럼에 해당하는 값들을 나열

=>subquery를 이용한 추가
insert into 테이블이름(컬럼 이름 나열)
select 구문

9.데이터 수정
=>update 테이블이름 set 컬럼이름=수정할값,... [where 조건];
where 절이 없으면 테이블의 모든 데이터를 수정하고 where 절이 있으면 where 절에 해당하는 데이터만 수정

=>usertbl 테이블에서 userid 가 kjn 인 데이터의 name을 김제니로 수정
update usertbl
set name='김제니'
where userid='kjn';


select *
from usertbl;

10.데이터 삭제
delete from 테이블이름 [where 조건식];

=>usertbl 테이블에서 userid 가 kjn 인 데이터 삭제
delete from usertbl where userid='kjn';

11.join
=>2개의 테이블을 합쳐서 하나의 테이블을 만드는 작업

1)cartesian product - cross join
=>2개 테이블의 모든 조합을 만들어 내는 것
=>행의 개수는 양쪽 테이블의 행의 개수를 곱한 것 과 같고 열의 개수는 양쪽 테이블의 열의 개수를 더한 것 과 같음
=>from 절에 ,로 구분해서 테이블 이름을 2개 나열하거나 테이블 이름 사이에 , 대신에 cross join 이라고 입력하면 됩니다.

=>usertbl 테이블 과 buytbl 테이블의 cross join

select *
from usertbl, buytbl;

select *
from usertbl cross join buytbl;

2)Inner Join
=>양쪽 테이블의 동일한 의미를 갖는 2개의 컬럼 값이 같은 데이터만 합쳐서 새로운 테이블을 만드는 join
=>실제 구문 상에서는 자료형 만 동일하면 되고 컬럼의 이름은 아무 상관 없음
=>외래키로 설정된 컬럼을 가지고 하는 것을 권장
=>컬럼 값을 비교할 때 = 로 비교하면 equi join 이라고 하고 = 가 아닌 연산자로 비교하면 non equi join 이라고 합니다.

=>조인 조건은 where 절에 기술하기도 하고 on 을 이용해서 기술할 수 도 있고 natural join을 이용할 수 도 있는데 natural join을 사용할려면 양쪽 테이블의 컬럼 이름이 같아야 합니다.

=>usertbl 테이블 과 buytbl 테이블에는 userid 라는 공통된 컬럼이 존재
이 컬럼을 가지고 InnerJoin(Equi Join) 수행

select *
from usertbl, buytbl
where usertbl.userid = buytbl.userid;


select *
from usertbl inner join buytbl on usertbl.userid = buytbl.userid;

select *
from usertbl natural join buytbl;

3)outer join
=>한쪽 테이블에만 존재하는 데이터도 조인에 참여하는 것
=>inner join 이라는 예약어 대신에 left outer join, right outer join, full outer join을 사용하면 됩니다.

=>usertbl 테이블에는 존재하지만 buytbl 테이블에는 존재하지 않는 데이터도 join에 참여
select *
from usertbl left outer join buytbl on usertbl.userid = buytbl.userid;

4)self join
=>동일한 테이블끼리 join
=>이런 경우는 하나의 테이블에 동일한 의미를 갖는 2개의 컬럼이 존재하는 경우 수행
=>하나의 테이블의 사원id 가 존재하고 관리자의사원id 가 존재하고 name 이 있을 때 특정 사원의 관리자의 name을 알고자 하는 경우
이 와 유사한 경우가 SNS에서 친구 테이블의 경우
회원아이디 와 친구아이디가 같은 테이블에 존재하는데 내 친구의 친구를 찾고자 할 때

=>self join을 위한 샘플 데이터
CREATE TABLE empTbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));

INSERT INTO empTbl VALUES(N'나사장',NULL,'0000');
INSERT INTO empTbl VALUES(N'김재무',N'나사장','2222');
INSERT INTO empTbl VALUES(N'김부장',N'김재무','2222-1');
INSERT INTO empTbl VALUES(N'이부장',N'김재무','2222-2');
INSERT INTO empTbl VALUES(N'우대리',N'이부장','2222-2-1');
INSERT INTO empTbl VALUES(N'지사원',N'이부장','2222-2-2');
INSERT INTO empTbl VALUES(N'이영업',N'나사장','1111');
INSERT INTO empTbl VALUES(N'한과장',N'이영업','1111-1');
INSERT INTO empTbl VALUES(N'최정보',N'나사장','3333');
INSERT INTO empTbl VALUES(N'윤차장',N'최정보','3333-1');
INSERT INTO empTbl VALUES(N'이주임',N'윤차장','3333-1-1');

-- emp는 자신의 이름이고 manager는 관리자의 이름이고 empTel은 전화번호 
-- emp 가 김부장인 사원의 관리자 전화번호를 알고자 하는 경우

select e2.emp, e2.empTel
from emptbl e1, emptbl e2
where e1.emp = '김부장' and e1.manager = e2.emp;


5)join 과 sub query
=>select 절의 결과가 하나의 테이블에서만 가져오는 경우는 sub query로 해결할 수 있습니다.
2개 이상의 테이블에서 가져와야 하는 경우는 join으로 해결해야 합니다.

usertbl 테이블에는 userid, name, birthyear, addr, mdate 가 존재
buytbl 테이블에는 num, userid, productname, groupname, price, amount 가 존재

=>addr 이 서울인 유저의 name 과 productname을 조회
usertbl 테이블 과 buytbl 테이블을 이용해야 하는데 name 과 productname을 조회해야 하므로 join을 이용해서 해결

select name, productname
from usertbl inner join buytbl on usertbl.userid = buytbl.userid
where addr='서울';

=>addr 이 서울인 유저의 userid 와 productname 과 price, amount을 조회
usertbl 테이블 과 buytbl 테이블을 이용해야 하는데 userid 와 productname 과 price, amount 가 buytbl에 존재하기 때문에 이 경우는 subquery로도 해결 가능
join을 한 경우에 양쪽 테이블에 모두 존재하는 컬럼이름을 사용할 때는 테이블을 명시해야 합니다.

-- join으로 해결
select buytbl.userid, productname, price, amount
from usertbl inner join buytbl on usertbl.userid = buytbl.userid
where addr='서울';

-- subquery로 해결
select userid, productname, price, amount
from buytbl 
where buytbl.userid in (select userid from usertbl where addr='서울');

6)집합 연산 : 테이블 간의 수직 연산
=>동일한 구조(컬럼의 자료형 과 개수 및 순서가 동일)를 갖는 테이블끼리 연산을 수행하는 것
=>합집합
select 구문
union
select 구문

union 대신에 union all 을 하게되면 동일한 데이터를 2번 출력

=>교집합: union 대신에 intersect

=>한쪽에만 존재하는 데이터는 except(oracle은 minus)

12.Transaction Control Language
1)Transaction
=>한 번에 이루어져야 하는 작업의 논리적인 단위

=>제가 gamemoney를 이용해서 상대방의 item을 구매
제 gamemoney 의 값을 수정 - update
상대방 item 을 수정 - update
상대방 gamemoney 수정 - update
제 item 수정 - update

이런 경우는 4개의 update 구문이 모두 수행되거나 하나도 수행되지 않아야 합니다.
이렇게 한꺼번에 수행되어야 하는 작업의 단위를 트랜잭션이라고 합니다.

2)commit, rollback
=>commit: 작업이 완료
=>rollback: 작업을 철회

3)Transaction 의 성질
=>Atomicity(원자성): All or Nothing
=>Consistency(일관성): 트랜잭션 수행 전 과 수행 후가 일관성이 있어야 한다.
=>Isolation(격리성): 트랜잭션 수행 중에는 다른 트랜잭션이 이 데이터를 수정하면 안된다.
=>Durability(영속성): 한 번 완료된 트랜잭션은 계속 되어야 한다.
 
4)savepoint
=>트랜잭션이 수행 완료 되거나 철회가 될 때는 데이터베이스에 lock 이 걸려서 다른 작업을 수행할 수 없습니다.
=>commit을 일정한 주기를 가지고 하는 방법으로 위의 문제를 해결할 수 있는데 이렇게 만들면 rollback 할 때 너무 많이 rollback을 수행해야 합니다.
중간 중간에 rollback 할 수 있는 지점을 만드는데 이 지점을 savepoint 라고 합니다.

5)commit 되는 경우
=>DDL(Create, Alter, Drop, Truncate, Rename) 이나 DCL(Grant, Revoke)을 성공적으로 수행한 경우
=>접속 프로그램이 정상 종료되는 경우
=>명시적으로 commit을 호출하는 경우

6)rollback 되는 경우
=>명시적으로 rollback을 호출하는 경우
=>접속 프로그램의 비정상적인 종료