1) DDL

CREATE DATABASE FRIENDS_SIM;


SHOW DATABASES;

use FRIENDS_SIM;

SHOW TABLES;

DROP TABLE IF EXISTS `FRIENDS_R`;

CREATE TABLE IF NOT EXISTS `FRIENDS_SIM`.`FRIENDS_R`(
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`ID1` VARCHAR(45) NOT NULL,
`ID2` VARCHAR(45) NOT NULL
);
ALTER TABLE `FRIENDS_R` CONVERT TO character SET utf8;

DESC `FRIENDS_R`;

• pk 하나를 제외하고는 ID1, ID2 라고 하는 문자열로 이루어진 테이블 하나 생성

2) 데이터 삽입

INSERT INTO `FRIENDS_R` (ID1, ID2) VALUES 
	('Dong','Hun'),
	('Dong','Jun'),
	('Dong','Onux'),
	('Dong','Kim'),
	('Dong','Min'),
	('Kim','Jun'),
	('Kim','Min'),
	('Kim','Do'),
	('Do','Jun'),
	('Do','ICN'),
	('Do','GMP'),
	('GMP','Kim'),
	('GMP','Dong'),
	('GMP','Hun'),
	('Do','CJU'),
	('Do','JKF'),
	('ICN','JFK'),
	('ICN','LGA'),
	('Dong','GMP'),
	('Dong','ICN'),
	('Dong','JFK'),
	('Dong','LGA'),
	('Dong','SIN'),
	('Dong','CDG');

SELECT * FROM `FRIENDS_R`;

DELETE FROM `FRIENDS_R`;

SELECT COUNT(*) FROM FRIENDS_R;

• ID1과 ID2 컬럼이 같은 레코드에 존재하면 서로 친구라는 의미

3) 두 테이블을 결합해 데이터 조회하기

• 친구가 많은 순서대로 정렬하거나
• id 알파벳순으로 정렬

id 를 사전순으로 정렬

SELECT id, COUNT(id) FROM 
(
SELECT ID1 AS id FROM `FRIENDS_R` 
UNION ALL
SELECT ID2 AS id FROM `FRIENDS_R`
) AS fr
GROUP BY id
ORDER BY id;

친구가 많은 순서대로 정렬

SELECT id, COUNT(id) FROM 
(
SELECT ID1 AS id FROM `FRIENDS_R` 
UNION ALL
SELECT ID2 AS id FROM `FRIENDS_R`
) AS fr
GROUP BY id
ORDER BY `COUNT(id)` DESC;