힐링 ASMR 과 프로그래밍

MySQL SELECT한 레코드에서 또 SELECT하기 – 하위 질의

하위 질의란?

하위 질의는 서브 쿼리라고도 합니다. 하위 질의는 질의를 실행해서 추출한 데이터를 이용해서 다시 질의를 하는 것을 말합니다.

하위 질의를 이용해서 sales의 최대값 레코드를 표시해 보겠습니다.

select * from table_sales;

select * from table_sales

where sales in(select max(sales) from table_sales);

GROUP BY 함수

MAX(), AVG(), SUM()등 집계를 하는 함수를 GROUP BY 함수라고 하는데 그룹화했을 때 값을 처리하는 함수입니다. 단 GROUP BY 가 없는 경우에는 테이블 전체를 하나의 그룹으로 처리합니다.

평균 이상의 레코드 추출하기.

평균나이 이상인 레코드를 추출해 보겠습니다.

select * from table_member;

select * from table_member

where age >= (select avg(age) from table_member);

IN 사용하기 – 컬럼을 반환하는 하위 질의

이번에는 하위 질의가 컬럼을 반환하는 예입니다.

1단계의 하위 질의에서 조건에 일치하는 컬럼을 반환하고 2단계에서 그 컬럼을 포함한 값을 추출합니다.

명령문은 다음과 같습니다.

select 컬럼명 from 테이블명

where 컬럼명 in (select를 이용한 하위 질의로 추출한 컬럼);

table_sales 에서 sales 가 100이상인 number를 추출하고 table_member에서 해당하는 레코드를 표시해 보겠습니다.

select * from table_member

where number

in (select number from table_sales where sales >= 100)

EXISTS를 사용해서 존재하는 레코드를 대상으로 검색하기.

exists는 특정 컬럼이 아닌 대상이 되는 레코드가 존재하고 있다는 의미입니다.

Table_sales의 number와 table_member의 number가 양쪽에 모두 존재하는 레코드를 추출하고 table_member에서 해당하는 레코드를 표시해 보겠습니다..

select * from table_member as A

where exists (select * from table_sales as B

where A.number = B.number);

select * from table_sales as B where A.number = B.number 은 특정 컬럼이 아니라 조건에 맞는 레코드를 추출하는 것입니다. 그리고 exists를 사용해서 table_member에 존재하는 해당 레코드만 표시합니다.

NOT EXISTS 사용하기.

not exists 는 하위 질의로 추출되지 않은 레코드를 대상으로 합니다.

위의 exists 명령문에 not exists를 적용해서 비교해 보겠습니다.

select * from table_member as A

where not exists (select * from table_sales as B

where A.number = B.number);

하위 질의를 이용한 순위 정하기.

하위 질의를 사용하면 다양한 방법으로 순위를 매길 수 있습니다.

하위 질의를 사용해서 순위를 정하는 방법을 알아 보겠습니다.

1.     table_sales 컬럼 구조만 복사해서 table_rating 를 생성합니다.

2.     table_rating 에 연속 번호 기능을 설정한 rating를 추가합니다.

3.     하위 질의를 사용해서 table_sales의 컬럼 sales를 내림차순으로 정렬하고, number, sales, month를 table_rating에 추가합니다.

create table table_rating like table_sales;

alter table table_rating

add rating int auto_increment primary key;

insert into table_rating (number, sales, month)

(select number, sales, month from table_sales

order by sales desc);

select * from table_rating;

셀프 조인이란?

결합에는 자기 자신 즉, 같은 이름의 테이블을 결합하는 방법이 있습니다. 이를 셀프 조인이라 합니다. 같은 이름의 테이블을 결합하는 것이기 때문에 실행하면 같은 컬럼 이름이 2개씩 표시됩니다. 이렇게 되면 컬럼을 구별하기가 어렵기 때문에, 셀프 조인을 할 때에는 반드시 별명을 사용합니다.

명령은 다음과 같습니다.

select 컬럼명 from 테이블명 as 별명1 join 테이블명 as 별명2;

select * from table_member;

select * from table_member as A

join table_member as B

셀프 조인한 결과를 보면 모든 경우의 수를 표시한 것과 같다.

순위 정하기

테이블에 같은 테이블을 결합하는 이유는 무엇일까요? 셀프 조인을 한 결과에는 적어도 모든 경우의 조합이 존재합니다. 따라서 얻고자 하는 조합이 그 결과 안에 존재한다면, 그 다음에는 조건을 설정해서 필요한 것만 선택하면 됩니다.

셀프 조인을 이용해서 Table_member 의 name, age, 순위를 표시해 보겠습니다.

순위를 구하는 방법은 셀프 조인 했을 때 값을 보면 오른쪽이 같은 값으로 구성된 5개의 레코드 중에서 오른쪽 age 이상인 왼쪽 age의 개수가 순위가 됩니다.

즉 셀프 조인을 하고 나서 왼쪽의 age가 오른쪽의 age 이상인 레코드의 수를 number의 그룹별로 개수를 조사하면 됩니다. 개수는 count(*)로 계산합니다.

select A.name, A.age, count(*) from table_member as A

join table_member as B

where A.age <= B.age

group by A.number;

MySQL 외부 JOIN 사용하기.

내부 조인은 키가 일치하는 레코드만 추출하지만 외부 조인은 일치하지 않더라도 한쪽 테이블에 있는 모든 레코드를 표시합니다.

외부 조인은 결합하는 레코드 중 어느 쪽의 레코드를 모두 표시할 것인가에 따라 LEFT JOIN과 RIGHT JOIN 으로 나타냅니다.

LEFT JOIN 사용하기.

Left join은 왼쪽 테이블에 있는 레코드를 모두 포함해서 표시합니다.

명령문은 다음과 같습니다.

select 컬럼명 from 테이블명

left join 결합할 테이블명

on 테이블명.컬럼명 = 결합할 테이블명.컬럼명;

select * from table_sales;

select * from table_member;

select A.number, B.name from table_sales as A

left join table_member as B

using(number);

number 컬럼의 A7은 table_sales에 있지만 table_member에는 없습니다. Left join으로 table_sales 의 모든 레코드를 표시하고 있음을 알 수 있습니다. 하지만 A1은 table_member에는 있지만 table_sales에는 없으므로 left join으로 했을 때는 나타나지 않은걸 확인할 수 있습니다.

RIGHT JOIN 사용하기.

right join 오른쪽 테이블에 있는 레코드를 모두 포함해서 표시합니다.

명령문은 다음과 같습니다.

select 컬럼명 from 테이블명

right join 결합할 테이블명

on 테이블명.컬럼명 = 결합할 테이블명.컬럼명;

select A.number, B.name from table_sales as A

right join table_member as B

on A.number = B.number;

Right join 한 결과를 보면 기준이 되는 table_member 에 A1 강백호 레코드는 표시되지만 table_sales 테이블에는 A1 레코드가 없으므로 null로 표시 됨을 확인 할 수 있습니다.

OUTER 명시하기.

left join은 left outer join이라고 기술할 수 있습니다. 마찬가지로 right join은 right outer join이라고 기술할 수도 있습니다.

MySQL 내부 JOIN ON 사용하기.

여러 개의 테이블을 특정 키로 연결하는 것이 조인입니다.

A 테이블과 B 테이블이 있다면 A 테이블 컬럼과 B테이블 컬럼이 같은 것이 있을 때 같은 컬럼을 키로 하여 조인하는 방법입니다.

JOIN ON 명령어는 다음과 같이 사용합니다.

Select 컬럼명 from 테이블명 join 결합할 테이블명 on 테이블명.컬럼명 = 결합할 테이블명.컬럼명;

select * from table_sales;

select * from table_member;

select * from table_sales

join table_member

on table_sales.number = table_member.number;

일치하는 레코드만 가져오는 결합을 내부 조인이라고 합니다.

내부 조인을 명시적으로 표시하려면 join을 inner join으로 명령문을 사용합니다.

내부 조인과 달리, 한쪽 테이블에 일치하지 않는 레코드가 있어도 모두 표시하는 것을 외부조인이라고 합니다.

컬럼을 선택해서 표시하기.

앞에서는 모든 컬럼을 표시했습니다. 이번에는 표시할 레코드를 선택해서 표시하는데 테이블을 결합할 때는 어느 테이블이 컬럼인지 알 수 없기 때문에 구체적으로 밝혀주는데 테이블명.컬럼명 이렇게 어느 테이블이 컬럼인지 밝혀 주어야 합니다.

select table_sales.number, table_member.name, table_sales.sales

from table_sales

join table_member

on table_sales.number = table_member.number;

테이블 이름에 별명 사용하기.

테이블 이름에 별명을 사용할 때는 as 명령을 사용합니다.

테이블명 as 별명;

위에 컬럼을 선택해서 표시하기에 컬럼을 선택할 때 테이블 이름이 들어가서 좀 복잡하다고 생각이 들 겁니다. 이때는 간단히 테이블명에 별명을 붙여서 사용할 수 있습니다.

Select A.number, B.name, A.sales

         from table_sales as A

         join table_member as B

         on A.number = B.number;

USING을 사용해서 ON 부분 대체하기.

Join on 부분에 컬럼명이 반드시 같아야 할 필요는 없습니다. 키가 될 값만 같으면 컬럼명은 달라도 상관없습니다.

만약 컬럼명이 같을 때는 USING(컬럼명)을 이용하면 간단히 기술할 수 있습니다.

select A.number, B.name, A.sales

         from table_sales as A

         join table_member as B

         using(number);

JOIN 테이블에 WHERE 조건 설정하기.

Join 명령문에 조건을 설정할 때는 맨 마지막에 where를 사용하여 조건을 설정하면 됩니다.

다만 주의 해야 할 것은 조건을 설정할 때는 컬럼이 어느 테이블에 속한 컬럼인지 밝혀 주어야 합니다. 테이블명.컬럼명 이렇게 설정해 주어야 합니다.

table_sales 테이블과 table_member 테이블을 결합하고 table_sales 테이블의 sales컬럼이 50이상인 레코드를 number, name, sales를 표시해 보겠습니다.

select A.number, B.name, A.sales

         from table_sales as A

         join table_member as B

         using (number)

where A.sales >= 50;

여러 테이블에서 데이터 추출하기.

Join할 테이블의 수에는 제한이 없습니다.

select ~ join ~ on ~ 에 계속해서 join ~ on ~ 을 추가해서 결합합니다.

여러 개의 테이블을 이용해서 내부 조인하기 명령어.

select 컬럼명 from 테이블명

join 테이블명 on 조인할 조건

join 테이블명 on 조인할 조건

join 테이블명 on 조인할 조건

….

select A.number, A.sales, B.name, C.city

from table_sales as A

join table_member as B

using(number)

join table_city as C

using(number);

MySQL UNION 사용하기.

여러 테이블에서 추출한 데이터를 단순하게 하나의 결과로 표시할 때에는 union을 사용합니다.

다음과 같이 명령문을 사용합니다.

Select 컬럼명 from 테이블명 union select 컬럼명 from 테이블명;

select * from table_member;

select * from table_otherMember;

select * from table_member union select * from table_otherMember;

조건을 설정해서 여러 개의 추출 결과를 하나로 표시하기.

Table_sales 테이블에서 sales 컬럼 값이 100이상인 number 컬럼과 table_member 에서 age 컬럼 값이 40이상인 number 컬럼을 union 해 보겠습니다.

select * from table_sales;

select * from table_member;

select number from table_sales where sales >= 100

union

select number from table_member where age >= 40;

MySQL UNION ALL 사용하기.

위의 결과에서 A4는 table_sales 테이블에서 두 번 검색이 됬는데 union한 후 한 번만 표시 되었습니다. 중복된 값은 생략이 되었습니다.

그러나 중복된 값도 모두 표시 하고 싶을 때는 union all 명령으로 나타냅니다.

select number from table_sales where sales >= 100

union all

select number from table_member where age >= 40;

MySQL 모든 레코드 삭제하기.

테이블 구조는 그대로 유지하면서 모든 레코드를 삭제할 때는 다음 명령어를 사용합니다.

delete from 테이블명;

table_09에 있는 모든 레코드를 삭제해 보겠습니다.

select * from table_09;

delete from table_09;

select * from table_09;

테이블 자체를 삭제할 때는 다음 명령어를 사용합니다.

drop table 테이블명;

table_09를 완전히 삭제해 보겠습니다.

show tables;

drop table table_09;

MySQL 특정 레코드 삭제하기.

특정 레코드를 삭제하기 위해서는 where 를 사용해서 조건을 설정하면 됩니다.

명령어는 다음과 같습니다.

delete from 테이블명 where 조건;

age가 40이하인 레코드를 삭제해 보겠습니다.

select * from table_09;

delete from table_09 where age <= 40;

select * from table_09;

MySQL 정렬 후 삭제하기.

정렬에 사용하는 order by 명령어를 사용하여 레코드를 정렬한 후 삭제해 보겠습니다.

age 컬럼을 올림차순으로 정렬한 후 2개만 삭제 해 보겠습니다.

select * from table_09 order by age asc;

delete from table_09 order by age asc limit 2;

select * from table_09 order by age asc

MySQL 조건에 일치하는 레코드 복사하기.

MySQL 특정 레코드만 복사하기.

table_01 에 age 컬럼의 40이상인 레코드만 복사해서 새로운 테이블 table_ageOver40 을 생성해 보겠습니다.

create table table_ageOver40

select * from table_01

where age >= 40 and age < 50;

select * from table_ageOver40;

기존에 존재하는 테이블에 레코드를 추가하는 경우에는 insert into를 사용합니다.

Table_01에 있는 Age가 50이상인 레코드를 table_ageOver40 에 추가해 보겠습니다.

insert into table_ageOver40

select * from table_01 where age >= 50;

select * from table_ageOver40;

A6 이수만 60 레코드가 잘 추가 되었습니다.

MySQL 순서대로 정렬하여 복사하기

table_01의 age컬럼을 올림차순으로 정렬한 후 4번째부터 5개의 레코드를 복사하여 table_age4To8 테이블을 생성해 보겠습니다.

create table table_age4To8

select * from table_01 order by age asc

limit 5 offset 3;

select * from table_age4To8;

MySQL 데이터 수정하기, 컬럼 추가 하기, 컬럼 삭제하기.

MySQL 컬럼의 모든 데이터를 한 번에 수정하기.

데이터를 수정할 때는 update 명령을 사용합니다.

컬럼의 모든 데이터 수정하기는 다음과 같은 명령을 실행합니다.

update 테이블명 set 컬럼명 = 수정할 값;

특정 컬럼을 지정한 값으로 변경합니다. 이 명령을 실행하면 컬럼의 모든 데이터를 한 번에 변경할 수 있습니다.

우선 기존 table_01 에 memo 컬럼을 추가 해 보겠습니다.

alter table table_01 add memo varchar(100);

select * from table_01;

memo 컬럼이 생성되었으면 메모에 메모 없음을 추가 해서 확인해 보겠습니다.

update table_01 set memo = ‘메모 없음’;

select * from table_01;

MySQL 조건에 일치하는 레코드만 수정하기.

모든 컬럼을 수정할 일은 많지 않고 조건에 일치하는 레코드를 수정하는 경우가 많습니다.

조건을 설정할 때는 where 명령어에 조건을 지정합니다.

update 테이블명 set 컬럼명 = 수정할 값 where 조건;

age 컬럼의 45이상인 레코드에 중년 이라고 수정해 보겠습니다.

update table_01 set memo = ‘중년’ where age >= 45;

select * from table_01;

원빈 레코드에 number 와 age 를 수정해 보도록 하겠습니다.

update table_01 set number = ‘A4’, age = 41 where name = ‘원빈’;

Select * from table_01;

age 가 제일 어린 사람을 memo에 막내라고 수정해 보겠습니다.

update table_01 set memo = ‘막내’ order by age asc limit 1;

select * from table_01;

MySQL 특정 컬럼 삭제하기.

위에 추가 했던 memo 컬럼만 삭제 해 보겠습니다.

alter table table_01 drop memo;

Select * from table_01;

memo 컬럼이 잘 삭제 되었습니다.

MySQL 그룹화에 조건 설정하기.

그룹별로 데이터 처리하기.

group by로 그룹화하고 검색조건을 설정해 보겠습니다.

Table_01에서 age 평균을 number 별로 계산한 후 평균이 40이상인 데이터만 표시하기 위해서 having 조건 설정합니다.

select number, avg(age) from table_01 group by number having avg(age) >= 40;

먼저 데이터를 추출하고 그룹화하기.

먼저 데이터를 추출하고 그룹화하는 방법은 where 조건으로 데이터를 추출한다음 그룹화 한다는 의미 입니다.

age가 40이상인 데이터를 먼저 추출한 다음 그룹화 해 보겠습니다.

select number, avg(age) from table_01 where age >= 40 group by number;

그룹화하고 나서 정렬하기.

그룹화하고 나서 정렬하기란 select 명령에 group by 와 order by를 모두 사용하는 것을 말합니다.

Age 평균이 40이상인 데이터를 내림차순으로 정렬을 해보겠습니다.

select number, avg(age) from table_01 group by number having avg(age) >= 40 order by avg(age) desc;

그룹화 과정 정리.

1.     그룹화하는 순서는 먼저 where로 조건을 설정하고 group by로 그룹화합니다.

2.     Group by로 먼저 그룹화하고 having으로 조건을 설정합니다.

3.     그룹화한 결과를 정렬할 때는 order by를 사용합니다.

주요 명령인 where와 group by, order by의 순으로 명령을 사용합니다.

MySQL 그룹별로 표시하기.

그룹별로 표시하기.

Table_01에 number 컬럼에는 A1인 데이터가 여러 개 있습니다. 이와 같이 같은 값의 데이터를 그룹으로 만들 수 있습니다. 그룹으로 설정된 데이터는 합계와 평균을 비롯한 각종 데이터 처리를 그룹 단위로 할 수 있습니다.

그룹화할 때는 group by를 사용합니다.

select 컬럼명 from 테이블명 group by 그룹화할 컬럼명;

컬럼 number를 그룹화 하기 전과 후를 비교해 보겠습니다.

select * from table_01;

select * from table_01 group by number;

위와 같이 number에 대해 그룹화를 했을 때 number 데이터는 1종류씩 표시됨을 확인할 수 있습니다. 하지만 그룹화했을 때 그 처리 대상은 같은 그룹에 속해 있는 모든 레코드입니다.

무슨 의미인지는 위쪽 결과를 보시면 A1에 강백호, 강호동, 신동엽이 있습니다만 그룹화 했을 때 대표로 강백호 하나만 나타냈을 뿐 실제로는 A1에 속해 있는 모든 값이 대상입니다.

그러면 그룹별 몇 개가 있는지 count( ) 함수를 사용해 보겠습니다.

select number, count(*) from table_01 group by number;

그룹별 합계와 평균 표시하기.

그룹화로 합계를 계산하는 함수인 sum( )과 평균을 계산하는 함수 avg( )를 사용해 보겠습니다.

나이 합계 구하기.

select number, sum(age) as 나이합계 from table_01 group by number;

나이 평균 구하기.

select number, avg(age) as 나이평균 from table_01 group by number;