//====

테스트 데이터베이스

- 예제 테이블


* users 테이블

mysql> SELECT * FROM users;

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

| id | name  |

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

|  1 | user1 |

|  2 | user2 |

|  3 | user3 |

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


* tasks 테이블

mysql> SELECT * FROM tasks;

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

| no | task  | user_id |

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

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 13 | task4 |       4 |

| 14 | task5 |       1 |

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



//==========

//JOIN(조인,집합) 개념 다이아그램

https://www.w3schools.com/sql/sql_join.asp



//===========

* CROSS JOIN (교차) - 집합

mysql> SELECT * FROM tasks CROSS JOIN users;

- 동일: SELECT * FROM tasks , users;

- 4 x 3 = 12개 결과

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

| no | task  | user_id | id | name  |

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

| 11 | task1 |       1 |  1 | user1 |

| 11 | task1 |       1 |  2 | user2 |

| 11 | task1 |       1 |  3 | user3 |

| 12 | task2 |       2 |  1 | user1 |

| 12 | task2 |       2 |  2 | user2 |

| 12 | task2 |       2 |  3 | user3 |

| 13 | task4 |       4 |  1 | user1 |

| 13 | task4 |       4 |  2 | user2 |

| 13 | task4 |       4 |  3 | user3 |

| 14 | task5 |       1 |  1 | user1 |

| 14 | task5 |       1 |  2 | user2 |

| 14 | task5 |       1 |  3 | user3 |

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



//=====

- 다른 테이블에 같은 이름의 칼럼이 있는 경우

-> AS (Alias)로 이름 지정


예) SELECT t.id AS tid, t.task, u.id AS uid, u.name FROM tasks AS t , users AS u

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

| tid | task  | uid | name  |

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

|  11 | task1 |   1 | user1 |

...




//============

* INNER JOIN (내부) - 집합

mysql> SELECT * FROM tasks INNER JOIN users ON (users.id = tasks.user_id);

- 동일) SELECT * FROM tasks , users WHERE (users.id = tasks.user_id);

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

| no | task  | user_id | id | name  |

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

| 11 | task1 |       1 |  1 | user1 |

| 12 | task2 |       2 |  2 | user2 |

| 14 | task5 |       1 |  1 | user1 |

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



//========================

* LEFT JOIN  (외부1)

- 일치하는 값이 없어도 왼쪽 테이블은 모두 가져옴

- ON 의 조건에 맞지 않은 오른쪽 테이블의 레코드는 NULL 로 설정됨

mysql> SELECT * FROM tasks LEFT JOIN users ON (users.id = tasks.user_id);

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

| no | task  | user_id | id   | name  |

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

| 11 | task1 |       1 |    1 | user1 |

| 14 | task5 |       1 |    1 | user1 |

| 12 | task2 |       2 |    2 | user2 |

| 13 | task4 |       4 | NULL | NULL  |

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


- WHERE 사용 해서 필터

mysql> SELECT * FROM tasks LEFT JOIN users ON (users.id = tasks.user_id)

    -> WHERE  (users.id = tasks.user_id);



//======

* RIGHT JOIN (외부2)

mysql> SELECT * FROM tasks RIGHT JOIN users ON (users.id = tasks.no);

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

| no   | task | user_id | id | name  |

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

| NULL | NULL | NULL    |  1 | user1 |

| NULL | NULL | NULL    |  2 | user2 |

| NULL | NULL | NULL    |  3 | user3 |

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



//============

* FULL OUTER JOIN - 합집합

- MySQL에서 지원하지 않음

- UNION 연산자로 해결



//================================

* UNION (집합 연산자) 

- 두 테이블을 합침, 

- 칼럼 갯수가 동일해야 함, 같은 종류의 칼럼을 사용해야함 

- UNION : 중복 제거

- UNION ALL : 중복 제거 안함


mysql> SELECT no, task FROM tasks  UNION (SELECT id, name FROM users);

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

| no | task  |

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

| 11 | task1 |

| 12 | task2 |

| 13 | task4 |

| 14 | task5 |

|  1 | user1 |

|  2 | user2 |

|  3 | user3 |

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

- 주의! : no와 id 가 겹쳐짐, 

같은 종류의 데이터를 지정 해야 함



//==============================================

* GROUP BY

- 중복자료 제거, 정렬 작업

- SELECT 절에 함수(Aggregation, 집합연산함수) 사용가능


SELECT user_id FROM tasks GROUP BY user_id;

+---------+

| user_id |

+---------+

|       1 |

|       2 |

|       4 |

+---------+



* DISTINCT

- 중복자료 제거, 정렬 안함

SELECT DISTINCT user_id FROM tasks;

+---------+

| user_id |

+---------+

|       1 |

|       2 |

|       4 |

+---------+


//==============

* DISTINCT 와 GROUP BY의 차이

https://intomysql.blogspot.com/2011/01/distinct-group-by.html

- DISTINCT로만 가능한 기능

SELECT COUNT(DISTINCT fd1) FROM tab;

      -- 이런 형태의 쿼리는 서브 쿼리를 사용하지 않으면 GROUP BY로는 작성하기 어렵다.



- GROUP BY로만 가능한 기능

SELECT fd1, MIN(fd2), MAX(fd2) FROM tab GROUP BY fd1;

      -- 이렇게 집합함수(Aggregation)가 필요한 경우에는 GROUP BY를 사용해야 한다.



//=============================

//결과값 참고

mysql> SELECT user_id , COUNT(user_id) FROM tasks GROUP BY user_id;

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

| user_id | COUNT(user_id) |

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

|       1 |              2 |

|       2 |              1 |

|       4 |              1 |

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


mysql> SELECT DISTINCT user_id , COUNT(user_id) FROM tasks;

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

| user_id | COUNT(user_id) |

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

|       1 |              4 |

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


mysql> SELECT user_id , COUNT(DISTINCT user_id) FROM tasks;

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

| user_id | COUNT(DISTINCT user_id) |

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

|       1 |                       3 |

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



//===================

* HAVING

- WHERE 과 같지만 집합함수를 사용 가능

- WEHRE은 함수를 사용 불가

- GROUP BY 와 집합함수에 영향을 미침



mysql> SELECT user_id FROM tasks WHERE  MIN(user_id);  <== 에러

1111 - Invalid use of group function


mysql> SELECT user_id FROM tasks HAVING  MIN(user_id);

+---------+

| user_id |

+---------+

|       1 |

+---------+




//===============

- 기타 연산자


* IN 

- 하나라도 만족하면 true

SELECT * FROM tasks WHERE user_id IN( 1, 2 );

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

| no | task  | user_id |

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

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 14 | task5 |       1 |

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



* ANY (=SOME)  (or 연산)

- 하나라도 만족하면 true

- IN  과 다른점 : 비교 연산자를 사용 가능

mysql> SELECT * FROM tasks WHERE user_id <= ANY ( SELECT id FROM users WHERE id <= 2);

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

| no | task  | user_id |

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

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 14 | task5 |       1 |

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



* ALL   (and 연산)

- 모두 만족해야만 true

SELECT * FROM tasks WHERE user_id <= ALL ( SELECT id FROM users WHERE id <= 2);

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

| no | task  | user_id |

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

| 11 | task1 |       1 |

| 14 | task5 |       1 |

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



* EXISTS

- 서브 쿼리의 결과값이 1개라도 있다면 true

- ANY와 비슷, ANY가 더 사용하기에 명확

- NOT EXISTS 가능

SELECT * FROM tasks WHERE EXISTS ( SELECT id FROM users WHERE users.id = tasks.user_id );

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

| no | task  | user_id |

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

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 14 | task5 |       1 |

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



반응형

'Code > Database (DB)' 카테고리의 다른 글

[SQL] MySql 프로시저(PROCEDURE )  (0) 2019.02.07
[SQL] 서브쿼리(SubQuery)  (0) 2019.02.07
SQL 정리 (JOIN, 연산자)  (0) 2018.11.08
MySQL root 비밀번호 리셋  (1) 2018.10.03
FireFox DB(sqlite) 정리  (0) 2017.02.14
손상된 SQLite 데이터베이스 파일 복구  (0) 2015.07.18
Posted by codens codens

댓글을 달아 주세요