MySQL - 그룹화(GROUP BY, HAVING)

그룹화(GROUP BY, HAVING)

GROUP BY 절은 동일성을 기초하여 여러개의 행을 그룹화하고,

HAVING 절은 WHERE절과 유사한 기능을 가지며, 그룹의 조건을 지정합니다.

ex)

SELECT 
    s.stu_no, s.stu_name, sum(f.fee_pay)
FROM    student s,
    fee f
WHERE
    s.stu_no = f.stu_no
GROUP BY s.stu_no;

이에 대한 결과 값은 아래에 있는 것 중 왼쪽과 같습니다.

(원래 데이터는 아래의 오른쪽과 같습니다. 일부의 모습입니다.)

중복된 것을 묶어서 SUM()을 이용하여 합으로 출력하면 위의 결과를 얻을 수 있습니다.

-HAVING 절

-GROUP BY 절은 FROM 절로부터 생성되는 결과의 행을 그룹화

-HAVING 절은 특별한 그룹 속성을 기초로 하여 행의 그룹을 선택

-HAVING 절에 있는 조건은 WHERE 절에 있는 일반적인 조건처럼 보인다.

-WHERE 절에 있는 조건 수식에서는 통계함수를 사용 불가능

-부속질의어에서는 통계함수 사용가능

-HAVING 절의 조건에 있는 수식은 통계함수를 사용가능

ex)

SELECT stu_no,fee_year 
FROM fee
GROUP BY fee_year HAVING fee_year > 2005;

위의 Query는 

GROUP BY 절과 HAVING을 사용해서 fee_year가 2005 초과한 

stu_no, fee_year 값을 가져옵니다. (그룹화 해서)

결과 값은,

+----------+-------------+
| stu_no  | fee_year |
+----------+----------+
| 20001015 | 2006   |
| 20001015 | 2007   |
+----------+----------+

fee의 모든 데이터는 오른쪽과 같은데 group by, having을 사용하여

2005 보다 큰 년도의 값을 가져오면 위와 같은 결과가 나옵니다.

MySQL - ALTER

ALTER

ALTER 명령문

1. 새로운 컬럼을 만듭니다.

ALTER TABLE table_name ADD column_name datatype

2. 컬럼을 삭제합니다.

 ALTER TABLE table_name DROP COLUMN column_name

3. 데이터 타입을 바꾼다.

ALTER TABLE table_name MODIFY COLUMN column_name datatype

++

ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명)     //테이블에 기본키를 추가

ALTER는 데이터형을 바꾸거나 컬럼을 추가하거나 삭제하는 명령어 입니다.

(테이블의 구조를 변경)

MySQL - Query문이란

Query 문

-Query란?

A precise request for information retrieval with database and information systems

[위키피디아]

쉽게 말해서 질문을 보내는 겁니다. 또는 요청이라고도 부르는데

DB에서는 데이터를 요청하거나 입력, 삭제, 수정 등을 하는데에 보내는 요청을 말합니다.

자주 쓰이는 명령문은

SELECT

INSERT

UPDATE

DELETE

CREATE

등이 있습니다. 이 중에 많이 사용되고 어떻게 짜여지느냐에 따라 속도차이가 큰 녀석은

SELECT 이녀석 입니다.

원하는 데이터, 중복되는 데이터 등 마음대로 데이터를 요청하여 받아오려면 조건문, 결합구문 등

긴 쿼리문을 만들어야하기 때문입니다.

이러한 쿼리문을 만들기 위해서 성능을 생각한다면 쿼리문의 최적화가 중요한데,

1. 쿼리문 최적화(가장 좋음)

2. 간단한 쿼리문으로 효율적으로(1의 조건이 힘들다면)

이렇게 생각을 하고 쿼리문을 만들어 주시는게 좋습니다.

(최적화는 정석은 없지만 관련 책을 찾아보시면 잘 나와있습니다.)

(저는 아직 DB를 많이 다루어보지 못해서 앞으로 더 공부해서 최적화에 대한 것을 적어봐야겠습니다)

SELECT 구문의 결과를 빠르게 가져오기 위해 

INDEX 명령을 이용해서 처리속도를 빠르게 수행 할 수가 있습니다.

INDEX 사용하는 중요한 점

1. SELECT 명령문의 처리를 최적화하기 위해서 사용한다

2. INDEX에 관한 내용은 SELECT 명령문에서 명확하게 표현하지는 않는다.

3. 명령문을 처리할 때 SQL은 이미 존재하는 인덱스가 사용될 것인지 선택한다.

4. 인덱스는 언제든지 생성되거나 삭제될 수 있다.

5. 행을 추가, 삭제, 갱신이 발생할 때 테이블에 관련된 인덱스를 유지한다.

6. SELECT 명령문의 처리 시간을 감소시킨다는 것을 의미한다.

7. 갱신 명령(INSERT, UPDATE, DELETE 등)의 시간은 증가한다.(다시 인덱싱하기 때문에)

MySQL - SELECT(2)

SELECT

앞의 포스팅에 이어서 SELECT 명령문에 사용되는 연산자를 알아보겠습니다.

연산자들은 WHERE 절에서 많이 사용되는데요

각 조건에 맞는 자료를 찾기 위해 연산하는 것입니다.

AND, OR, NOT, BETWEEN, IN, LIKE, NULL, ALL, ANY, EXISTS 등이 있습니다.

●관계 연산자

= : 같다

<= : 작거나 같다

>= : 크거나 같다

<> : 같지 않다

●AND, OR, NOT을 이용한 다중조건

예를 드는것이 더 쉬울 것입니다.

ex)

SELECT stu_no,stu_name,id_num
FROM student
WHERE substring(id_num,8,1)=2 AND birth_year > '1985'

이런식으로 중간에 AND나 OR, NOT을 넣어서 연산하여 자료를 가져옵니다.

●BETWEEN 연산자

~사이에 있는 값들을 가져올때 사용하는데

ex)

SELECT stu_no, stu_name, birth_year
FROM student
WHERE birth_year BETWEEN 1981 AND 1987;

이런식으로 사용하여 1981~1987 사이의 데이터를 가져옵니다.

●IN 연산자

데이터에 원하는 값이 있는지 찾는 것으로

ex)

SELECT stu_no, stu_name
FROM student
WHERE SUBSTRING(id_num,1,2) IN (81,83,87);

//81,83,87이 있는 데이터만 출력합니다

●LIKE 연산자

LIKE 연산자는 특별한 패던이나 마스크를 가지는 영수치 값을 선택할 때 사용합니다.

ex)

SELECT * FROM subject  

WHERE sub_name LIKE "컴%";        // 컴으로 시작하는 것 찾기

SELECT * FROM subject  

WHERE sub_name LIKE "%밍";        // 밍으로 끝나는 것 찾기

SELECT * FROM subject  

WHERE sub_name LIKE "%프";        // 중간에 프가 있는 것 찾기

SELECT * FROM subject  

WHERE sub_name LIKE "_프%";       // 2번째 자리에 프가 있는것 찾기

앞에서 N번째 글자를 찾아서 출력한다면 앞에 _(under bar)를 n개 붙이고 찾고자 하는 문자를 입력하면 됩니다.

 

●NULL 연산자

NULL 연산자는 특정한 열이 값을 가지고 있지 않는 행을 선택할 때 사용

- IS NULL  : 비어 있으면

- IS NOT NULL : 비어 있지 않다면

- IFNULL(컬럼명,'값') : 컬럼 값이 NULL이라면 '값'을 출력합니다.

ex)

SELECT stu_no 학번, stu_name 이름, IFNULL(phone_no,'없음') 핸드폰
FROM student
WHERE phone_no IS NULL;

"student 테이블에서 phone_no가 NULL인 것을 출력하는데

출력할때 NULL 이면 '없음'이라고 출력해라"

라는 뜻입니다.

MySQL - SELECT(1)

SELECT

select 문

데이터베이스에서 값을 가져올 때 사용하는 명령문입니다.

<select statement> ::=

<select clause>

<from clause>

[ <where clause> ]

[ <group by clause> [ <having clause> ] ]

[ <order by clause> ]

SELECT 명령문은 위와 같이 최대 6개 절(clause)로 구성되어 있습니다.

●SELECT 명령문을 구성할 때 규칙 및 주의할 점

-SELECT 명령문은 최소 2개 절(SELECT, FROM)로 구성

-WHERE, GROUP BY, ORDER BY 같은 절은 선택적으로 사용

-절의 순서는 고정

-ORDER BY절이 사용된다면 이 절은 항상 가장 나중에 사용

-HAVING 절은 GROUP BY 절이 사용되어야만 사용 가능

●사용 예제

mysql > SELECT ...         

->FROM ...

->ORDER BY ...

mysql > SELECT ...

->FROM ...

->GROUP BY ...

->HAVING ...

mysql > SELECT ...

->FROM ...

->WHERE ...

위의 조합이 모두 섞여 있을 수도 있습니다.  

                                                                                               옆의 예제는 모두 사용한 것 >>

예제는 

등록테이블 FEE에서 장학금을 1000000이상 지급 반은 학생중에서 

2회이상 지급반은 학생의 학번과 지급받은 횟수를 학번 내림차 순으로 출력하라

●예제에 대한 수행 순서

2번(FROM) ->3번(WHERE)->4번(GROUP BY)->5번(HAVING)->1번(SELECT)->6번(ORDER BY)

예제1>

ATTEND 테이블에서 2006년도 1학이에 수강 신청한 학생의 학번과 

수강년도,학기,교과목코드,교수코드를 교수코드 오름차순으로 나타내어라.

 A:

SELECT stu_no, att_year, att_term, sub_code, prof_code
FROM attend
WHERE att_year = '2006' AND att_term = 1
ORDER BY prof_code;

예제2> ( 다중 테이블 명세) : FROM절에 두 개 이상의 테이블 명세 사용

학생들의 학번, 이름, 수강신청구분을 나타내어라. 단, 수강신청구분은 ATTEND 테이블에 있다.

A: 

SELECT s.stu_no, stu_name, att_div
FROM student s, attend a
WHERE s.stu_no = a.sut_no;

별명(alias)

예제2 에서 봤듯이

테이블에 대해서 별명을 붙여 주는 것입니다.

쓰는 이유는 테이블의 갯수가 많거나, 컬럼의 이름이 같을 수 있기 때문에 

비교할때 사용하려는 이유 때문입니다.

SELECT s.stu_no, s.stu_name, a.att_div, sub_code
FROM student s, attend a, subject su
WHERE s.stu_no = a.stu_no AND a.sub_code = su.sub_code;

MySQL - SubQuery

SELECT

부속 질의어에 대해서 보겠습니다.

부속 질의어(sub query)는 질의 안에 또 쿼리가 있는 것을 말합니다.

예를 보면,

select stu_no, stu_name

from student

where stu_no in (select stu_no from fee);

이런식으로 in 을 사용하여 부속 질의어를 만드는데요.

이를 이용하여 이렇게도 활용할 수 있습니다.

 Q:

"적어도 한 번의 장학금을 받았던 학생의 학번과 이름을 출력하라."

학생 테이블 : student, 학생 학번 : stu_no, 이름 : stu_name

장학금 테이블 : fee, 장학금 받은 수치 : jang_total

A:

SELECT stu_no, stu_name
FROM student
WHERE stu_no IN(SELECT stu_no FROM fee WHERE jang_total>0);

답은 조금만 생각해보시고 보면 될 것 같습니다.( 생각해보는게 중요하고 직접 만들어보는게 중요합니다^^)

앞의 포스팅에서 in 연산자에 대해 배웠는데요

in("내용") 이것은 "내용"과 비교해서 내용이 포함되었다면 그에 대한 내용을 출력하게 할 수 있는 연산자 입니다.

이를 이용해서 in 연산자 안에 내용을 쿼리로 다시 넣는다면 일일이 입력하지않아도 쿼리로 처리가 가능합니다.

Q;

"'20061011'인 학생이 가입한 동아리를 제외한 다른동아리에 적어도 한 번 가입을 한 학생의

학번과 이름, 주민등록번호를 출력하라"

학생 테이블 : student, 학번 : stu_no, 이름 : stu_name, 주민등록번호 : id_num

동아리 테이블 : circle, 동아리 이름 : cir_name

A:

SELECT stu_no, stu_name, id_num
FROM student
WHERE stu_no IN
(SELECT stu_no FROM circle
WHERE cir_name NOT IN
(SELECT cir_name FROM circle WHERE stu_no='20061011'));

이번건 좀 어려운데요 ㅎㅎ

부속 질의어 안에 또 부속 질의어가 있는 문제 입니다.

첫번째 부속 질의어는 학번을 출력하는데 두번째 부속 질의어에 따라 학번을 출력합니다.

(not이 있으므로 20061011학번을 가지고 있는 학생이 없는 동아리를 포함)

두번째 부속 질의어는 동아리 이름을 출력하는데 학번이 20061011이 가입한 동아리에 대해 출력합니다.

중간에 있는 not을 이용해서 20061011학번 학생이 가입한 동아리를 제외하고,

제외한 동아리 외의 동아리에 들어있는 학생들의 정보를 출력합니다.

MySQL - JOIN

Join

Join(조인)을 사용하는 때는

-select 명령문의 from 절에 적어도 두 개의 테이블 명세가 존재할 때 

-where 절에는 서로 다른 테이블의 열을 비교하는 조건이 한 개 이상 존재할 때

-조인에서 사용되는 용어

-조인으로 정의된 select 명령문에 있는 열을 조인 열(join column)이라 합니다.

-조인의 종류

Cross Join

Equi Join

Outer Join

Left outer Join

Self Join

Union Join

Alias 이용한 Join

조인을 위한 테이블

 

                                 s1                                  s2

-기본조인(general join)

SELECT * FROM s1,s2 where s1.idx = s2.idx;

mysql> select * from s1,s2 where s1.idx= s2.idx;

+------+---------+------+----------+

| idx  | name    | idx  | name     |

+------+---------+------+----------+

|    1 | 사람1   |    1 | 사람2    |

|    2 | 사람2   |    2 | 사람4    |

|    3 | 사람3   |    3 | 사람6    |

|    4 | 사람4   |    4 | 사람8    |

|    5 | 사람5   |    5 | 사람10   |

|    6 | 사람6   |    6 | 사람12   |

+------+---------+------+----------+

-이너 조인(inner join)

SELECT * FROM tableA

INNER JOIN tableB

ON tableA,name = tableB.name

mysql> select * from s1

    -> inner join s2

    -> on s1.name = s2.name;

+------+---------+------+---------+

| idx    | name     | idx    | name     |

+------+---------+------+---------+

|    2    | 사람2     |    1   | 사람2     |

|    4    | 사람4     |    2   | 사람4     |

|    6    | 사람6     |    3   | 사람6     |

+------+---------+------+---------+

//이너 조인은 교집합을 찾는 조인이다

-left outer join

select * from s1 left outer join s2

on s1.name = s2.name;

-right outer join

select * from s1 right outer join s2

on s1.name = s2.name;

//각 outer 조인은 해당되는 테이블 값만 찾는 조인이다(left or right)

위의 outer join에 where 조건을 추가하면 차집합을 표현할 수 있습니다.

where s2.name is null (left outer일 때)

where s1.name is null (right outer일 때)

-Cross join

오른쪽의 그림처럼 만들어지는 것의 query는

각 차집합을 union하면 쉽게 가능합니다.

select * from s1 left outer join s2

on s1.name = s2.name

where s2.name is null

union

select * from s1 right outer join s2

on s1.name = s2.name

where s1.name is null;

결과 값은,

-Full outer Join

위의 cross join과 같이 하는데

tableA, tableB의 합집합을 하는데

where 부분만 없애주면 됩니다.

select * from s1 left outer join s2

on s1.name = s2.name

union

select * from s1 right outer join s2

on s1.name = s2.name;

결과 값은,

이렇게 나옵니다.

참고 사이트 : 

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

MySQL - UPDATE

UPDATE

UPDATE 명령문은 행 값을 변경합니다. ( 테이블에 있는 값을 변경하기 위해서 사용)

UPDATE table
SET column = '값'
where 조건;

이런 식으로 값을 변경합니다.

예를들면,

update student
set address = '652-9번지 3통 5반'
post_no = '150-051'
tel = '02-1111-1414'
where stu_name='김문으';

student 테이블에서 address와 post_no, tel을 바꾸는데

이름이 김문으 라는 사람의 address, post_no, tel 을 바꿉니다.

조건을 이용해서 여러개의 데이터를 바꿀수 있습니다( sub query를 사용해서도 가능합니다. )

++조건을 이용해야 원하는 데이터만 바꿀수 있는데 조건을 이용하지 않는다면

모든 값이 바뀌므로 주의해야 합니다.

MySQL - View

VIEW

기본 테이블은 CREATE table 명령문을 사용하여 생성하고 데이터 저장하는데 비해

view 테이블은 그자체로 행을 가지고 있지 않고 기본 테이블로부터 

조합한 데이터에 대한 처리로 가상 테이블을 만들어서 사용자에게 보여줍니다.

(가상 테이블  : 메모리 상에서만 있는 테이블을 말합니다.)

1. view 생성

뷰는 CREATE VIEW 명령문으로 생성합니다.

ex)

create view v_class as
select distinct stu_no, grade, class
from student;

v_class라는 이름의 view 테이블을 만드는데

stu_no, grade, class의 값을 student 테이블에서 가져옵니다.(distinct : 중복 제거)

++ view테이블 만들때 select를 잘쓰는게 좋습니다.

ex2)

create view total_view as
select s.stu_no,s.stu_name,sum(f.jang_total)
from student s,fee f
where s.stu_no = f.stu_no
and f.jang_total is not null
group by s.stu_no;

위의 쿼리는 장학금을 받은 것의 sum을 구한 것을 total_view 뷰 테이블에 저장하는 쿼리 입니다.

2. view 열 이름(column name)

view 열이름, 말그대로 뷰 테이블을 만들때 컬럼 이름을 정해주는 것입니다.

예를 보면

create view birth_view(byear, bmonth, bday) as
select
substring(id_num,1,2) "생년",
substring(id_num,3,2) "생월",
substring(id_num,5,2) "생일"
from student;

위와 같이 create view 테이블이름(컬럼1,컬럼2,컬럼3)as ...

이렇게 되어있습니다.

++주의! : 

1. 뷰 테이블의 이름은 테이블 이름하고 같게 만들수도 없지만 만들려고 하지도 말것!

2. WITH CHECK OPTION은 MySQL에서 동작하거나 동작 안하는경우가 있습니다.

3. 뷰 테이블을 수정하면 원래 테이블의 값도 변경됩니다!

4. 뷰 테이블의 정보와 네비게이터

이것은 그냥

mysql > desc 뷰테이블이름;

이렇게 하면 데이터형을 알아볼수 잇는것을 말합니다.