고정 헤더 영역
상세 컨텐츠
본문
728x90
반응형
# 테이블 조인 JOIN
- 데이터베이스 : 학교
- 부모 테이블 : 학생 => 기본키 (PRIMARY KEY) PK => 중복 불가
- 자식 테이블 : 성적표 => 외래키(FOREIGN KEY) FK => 참조하면서 중복 가능
- INNER JOIN 이너 조인
- OUTER JOIN 아우터 조인
- LEFT JOIN 래프터 조인
- RIGHT JOIN 라이트 조인
-- 데이터베이스 학교 생성
CREATE DATABASE 학교;
-- 데이터베이스 학교로 이동
USE 학교;
-- 관계 Relation => 학번 => (학생)1:(성적표)다
-- 1. 부모 테이블 : 학생 => 기본키 (PRIMARY KEY) PK
CREATE TABLE 학생(
학번 VARCHAR(6) NOT NULL,
이름 VARCHAR(10) NOT NULL,
성별 VARCHAR(1) NULL CHECK(성별 IN('남','여')),
휴대폰 VARCHAR(13) NOT NULL CHECK(휴대폰 LIKE '010%'),
주소 VARCHAR(100) NULL,
PRIMARY KEY(`학번`)
) CHARSET=UTF8;
-- 학생 테이블 세부 정보 확인
DESC 학생; => 1 : PRI
MariaDB [학교]> DESC 학생;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| 학번 | varchar(6) | NO | PRI | NULL | |
| 이름 | varchar(10) | NO | | NULL | |
| 성별 | varchar(1) | NO | | NULL | |
| 휴대폰 | varchar(13) | NO | | NULL | |
| 주소 | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.010 sec)
-- 2. 자식 테이블 : 성적표 => 외래키(FOREIGN KEY) FK
CREATE TABLE 성적표(
학번 VARCHAR(6) NOT NULL,
국어 TINYINT NOT NULL,
영어 TINYINT NOT NULL,
수학 TINYINT NOT NULL,
FOREIGN KEY(`학번`) REFERENCES 학생(`학번`)
) CHARSET=UTF8;
-- 성적표 테이블 세부 정보 확인
DESC 성적표; => 다 : MUL
MariaDB [학교]> DESC 성적표;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| 학번 | varchar(6) | NO | MUL | NULL | |
| 국어 | tinyint(4) | NO | | NULL | |
| 영어 | tinyint(4) | NO | | NULL | |
| 수학 | tinyint(4) | NO | | NULL | |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.010 sec)
-- INSERT INTO 자료 입력
-- 학생 자료 입력
INSERT INTO 학생(학번,이름,성별,휴대폰,주소) VALUES
('240001','이수정','여','010-7942-5305','서울시'),
('240002','정지연','여','010-3942-9306','안산시'),
('240003','김영숙','여','010-4942-6307','서울시'),
('240004','조지현','여','010-5942-7308','인천시'),
('240005','김순자','여','010-7942-8309','광주시'),
('240006','이영자','여','010-8942-1300','부산시'),
('240007','홍우현','남','010-9942-2301','대구시'),
('240008','김지원','여','010-0942-5302','대전시'),
('240009','김수현','남','010-1942-3303','세종시'),
('240010','노동수','남','010-3942-4304','서울시');
-- 성적표 자료 입력
INSERT INTO 성적표(학번,국어,영어,수학) VALUES
('240001','99','48','67'),
('240002','99','88','100'),
('240003','99','48','67'),
('240004','99','97','88'),
('240005','94','48','85'),
('240006','84','88','24');
-- 학생 데이터 출력
SELECT * FROM 학생;
MariaDB [학교]> SELECT * FROM 학생;
+--------+-----------+--------+---------------+-----------+
| 학번 | 이름 | 성별 | 휴대폰 | 주소 |
+--------+-----------+--------+---------------+-----------+
| 240001 | 이수정 | 여 | 010-7942-5305 | 서울시 |
| 240002 | 정지연 | 여 | 010-3942-9306 | 안산시 |
| 240003 | 김영숙 | 여 | 010-4942-6307 | 서울시 |
| 240004 | 조지현 | 여 | 010-5942-7308 | 인천시 |
| 240005 | 김순자 | 여 | 010-7942-8309 | 광주시 |
| 240006 | 이영자 | 여 | 010-8942-1300 | 부산시 |
| 240007 | 홍우현 | 남 | 010-9942-2301 | 대구시 |
| 240008 | 김지원 | 여 | 010-0942-5302 | 대전시 |
| 240009 | 김수현 | 남 | 010-1942-3303 | 세종시 |
| 240010 | 노동수 | 남 | 010-3942-4304 | 서울시 |
+--------+-----------+--------+---------------+-----------+
10 rows in set (0.000 sec)
-- 성적표 데이터 출력
SELECT * FROM 성적표;
MariaDB [학교]> SELECT * FROM 성적표;
+--------+--------+--------+--------+
| 학번 | 국어 | 영어 | 수학 |
+--------+--------+--------+--------+
| 240001 | 99 | 48 | 67 |
| 240002 | 99 | 88 | 100 |
| 240003 | 99 | 48 | 67 |
| 240004 | 99 | 97 | 88 |
| 240005 | 94 | 48 | 85 |
| 240006 | 84 | 88 | 24 |
+--------+--------+--------+--------+
6 rows in set (0.000 sec)
-- 기준 성적표 출력 => 성적표,학생 데이터 같이 쓸 수 있다.
-- 1. 이너 조인 INNER JOIN (교집합) = 학번 FROM으로 설정한 성적표가 왼쪽 테이블 됨. / 학생 테이블이 오른쪽 테이블 됨.
-- 이너 조인 INNER JOIN
SELECT
성적표.학번,
학생.이름,
성적표.국어,
성적표.영어,
성적표.수학,
성적표.국어+성적표.영어+성적표.수학 AS `총점`,
ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
INNER JOIN `학생`
ON 성적표.학번 = 학생.학번
ORDER BY 성적표.학번 ASC;
MariaDB [학교]> SELECT
-> 성적표.학번,
-> 학생.이름,
-> 성적표.국어,
-> 성적표.영어,
-> 성적표.수학,
-> 성적표.국어+성적표.영어+성적표.수학 AS `총점`,
-> ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
-> RANK() OVER(ORDER BY `평균` DESC) AS `순위`
-> FROM `성적표`
-> INNER JOIN `학생`
-> ON 성적표.학번 = 학생.학번
-> ORDER BY 성적표.학번 ASC;
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 학번 | 이름 | 국어 | 영어 | 수학 | 총점 | 평균 | 순위 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 240001 | 이수정 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240002 | 정지연 | 99 | 88 | 100 | 287 | 287 | 1 |
| 240003 | 김영숙 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240004 | 조지현 | 99 | 97 | 88 | 284 | 284 | 2 |
| 240005 | 김순자 | 94 | 48 | 85 | 227 | 227 | 3 |
| 240006 | 이영자 | 84 | 88 | 24 | 196 | 196 | 6 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
6 rows in set (0.001 sec)
-- 2. 래프터 조인 LEFT JOIN (교집합)
SELECT
성적표.학번,
학생.이름,
성적표.국어,
성적표.영어,
성적표.수학,
성적표.국어+성적표.영어+성적표.수학 AS `총점`,
ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
LEFT JOIN `학생`
ON 성적표.학번 = 학생.학번
ORDER BY 성적표.학번 ASC;
MariaDB [학교]> SELECT
-> 성적표.학번,
-> 학생.이름,
-> 성적표.국어,
-> 성적표.영어,
-> 성적표.수학,
-> 성적표.국어+성적표.영어+성적표.수학 AS `총점`,
-> ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
-> RANK() OVER(ORDER BY `평균` DESC) AS `순위`
-> FROM `성적표`
-> LEFT JOIN `학생`
-> ON 성적표.학번 = 학생.학번
-> ORDER BY 성적표.학번 ASC;
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 학번 | 이름 | 국어 | 영어 | 수학 | 총점 | 평균 | 순위 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 240001 | 이수정 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240002 | 정지연 | 99 | 88 | 100 | 287 | 287 | 1 |
| 240003 | 김영숙 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240004 | 조지현 | 99 | 97 | 88 | 284 | 284 | 2 |
| 240005 | 김순자 | 94 | 48 | 85 | 227 | 227 | 3 |
| 240006 | 이영자 | 84 | 88 | 24 | 196 | 196 | 6 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
6 rows in set (0.001 sec)
-- 3. RIGHT JOIN
SELECT
학생.학번,
학생.이름,
성적표.국어,
성적표.영어,
성적표.수학,
성적표.국어+성적표.영어+성적표.수학 AS `총점`,
ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
RIGHT JOIN `학생`
ON 성적표.학번 = 학생.학번
ORDER BY 성적표.학번 ASC;
MariaDB [학교]> SELECT
-> 학생.학번,
-> 학생.이름,
-> 성적표.국어,
-> 성적표.영어,
-> 성적표.수학,
-> 성적표.국어+성적표.영어+성적표.수학 AS `총점`,
-> ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
-> RANK() OVER(ORDER BY `평균` DESC) AS `순위`
-> FROM `성적표`
-> RIGHT JOIN `학생`
-> ON 성적표.학번 = 학생.학번
-> ORDER BY 성적표.학번 ASC;
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 학번 | 이름 | 국어 | 영어 | 수학 | 총점 | 평균 | 순위 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 240008 | 김지원 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240009 | 김수현 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240007 | 홍우현 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240010 | 노동수 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240001 | 이수정 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240002 | 정지연 | 99 | 88 | 100 | 287 | 287 | 1 |
| 240003 | 김영숙 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240004 | 조지현 | 99 | 97 | 88 | 284 | 284 | 2 |
| 240005 | 김순자 | 94 | 48 | 85 | 227 | 227 | 3 |
| 240006 | 이영자 | 84 | 88 | 24 | 196 | 196 | 6 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
10 rows in set (0.001 sec)
-- 4. OUTER JOIN => FULL OUTER JOIN => LEFT JOIN [+(UNION)] RIGHT JOIN => (합집합)
-- 유니온 UNION ; 두 개를 JOIN해서 합치는 개념이다.
SELECT
학생.학번,
학생.이름,
성적표.국어,
성적표.영어,
성적표.수학,
성적표.국어+성적표.영어+성적표.수학 AS `총점`,
ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
LEFT JOIN `학생`
ON 성적표.학번 = 학생.학번
UNION
SELECT
학생.학번,
학생.이름,
성적표.국어,
성적표.영어,
성적표.수학,
성적표.국어+성적표.영어+성적표.수학 AS `총점`,
ROUND((성적표.국어+성적표.영어+성적표.수학), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
RIGHT JOIN `학생`
ON 성적표.학번 = 학생.학번;
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 학번 | 이름 | 국어 | 영어 | 수학 | 총점 | 평균 | 순위 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
| 240002 | 정지연 | 99 | 88 | 100 | 287 | 287 | 1 |
| 240004 | 조지현 | 99 | 97 | 88 | 284 | 284 | 2 |
| 240005 | 김순자 | 94 | 48 | 85 | 227 | 227 | 3 |
| 240003 | 김영숙 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240001 | 이수정 | 99 | 48 | 67 | 214 | 214 | 4 |
| 240006 | 이영자 | 84 | 88 | 24 | 196 | 196 | 6 |
| 240009 | 김수현 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240010 | 노동수 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240007 | 홍우현 | NULL | NULL | NULL | NULL | NULL | 7 |
| 240008 | 김지원 | NULL | NULL | NULL | NULL | NULL | 7 |
+--------+-----------+--------+--------+--------+--------+--------+--------+
10 rows in set (0.001 sec)728x90
반응형
'<개인공부> > [SQL데이터베이스]' 카테고리의 다른 글
| [SQL]0422_데이터베이스_MYSQL_ 테이블변경_ALTER_ 혼자실습 (0) | 2024.04.22 |
|---|---|
| [SQL]0419_MYSQL_JOIN_아이즈원멤버이름_연습 (0) | 2024.04.19 |
| [SQL]0419_MYSQL_성적처리 (0) | 2024.04.19 |
| [SQL] 0418_MYSQL_요점정리 (0) | 2024.04.18 |
| [SQL]0417_SQL사용법_추가_조회_수정_삭제 (0) | 2024.04.17 |
