고정 헤더 영역
상세 컨텐츠
본문
728x90
반응형
# 테이블 조인 JOIN
- 데이터베이스 : 대학교
- 부모 테이블 : 학생 => 기본키 (PRIMARY KEY) PK => 중복 불가
- 등록 학생10명
- 자식 테이블 : 성적표 => 외래키(FOREIGN KEY) FK => 참조하면서 중복 가능
-- 데이터베이스 학교2 생성
CREATE DATABASE 대학교;
-- 데이터베이스 학교2로 이동
USE 대학교;
-- 테이블 생성
-- 1. 부모 테이블 : 학생 학번 PK
CREATE TABLE 학생 (
학번 VARCHAR(6) NOT NULL CHECK(length(학번)=6),
이름 VARCHAR(10) NOT NULL,
성별 VARCHAR(1) NULL CHECK(성별 IN('남','여')),
휴대폰 VARCHAR(13) NOT NULL CHECK(휴대폰 LIKE '010%'),
주소 VARCHAR(100) NULL,
PRIMARY KEY(`학번`)
)CHARSET=UTF8;
-- 2. 자식 테이블 : 성적표 학번 FK
CREATE TABLE 성적표 (
학번 VARCHAR(6) NOT NULL CHECK(length(학번)=6),
데이터베이스 TINYINT NOT NULL CHECK(데이터베이스 BETWEEN 0 AND 100),
JAVA프로그래밍 TINYINT NOT NULL CHECK(JAVA프로그래밍 BETWEEN 0 AND 100),
JSP프로그래밍 TINYINT NOT NULL CHECK(JSP프로그래밍 BETWEEN 0 AND 100),
HTML5프로그래밍 TINYINT NOT NULL CHECK(HTML5프로그래밍 BETWEEN 0 AND 100),
FOREIGN KEY(`학번`) REFERENCES 학생(`학번`)
)CHARSET=UTF8;
-- 테이블 구조 확인
MariaDB [대학교]> DESC 학생;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| 학번 | varchar(6) | NO | PRI | NULL | |
| 이름 | varchar(10) | NO | | NULL | |
| 성별 | varchar(1) | YES | | NULL | |
| 휴대폰 | varchar(13) | NO | | NULL | |
| 주소 | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.009 sec)
MariaDB [대학교]> DESC 성적표;
+----------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------+------+-----+---------+-------+
| 학번 | varchar(6) | NO | MUL | NULL | |
| 데이터베이스 | tinyint(4) | NO | | NULL | |
| JAVA프로그래밍 | tinyint(4) | NO | | NULL | |
| JSP프로그래밍 | tinyint(4) | NO | | NULL | |
| HTML5프로그래밍 | tinyint(4) | NO | | NULL | |
+----------------------+------------+------+-----+---------+-------+
5 rows in set (0.010 sec)
-- 데이터 입력
-- 1. 학생 데이터
INSERT INTO 학생 (학번,이름,성별,휴대폰,주소) VALUES
('240001','권은비','여','010-1564-9721','서울시 금천구'),
('240002','이채연','여','010-1574-5521','서울시 도봉구'),
('240003','사쿠라','여','010-1584-0522','서울시 노원구'),
('240004','김채원','여','010-8964-1723','서울시 강북구'),
('240005','강혜원','여','010-6564-7728','서울시 중구'),
('240006','김민주','여','010-1464-9679','서울시 양천구'),
('240007','최예나','여','010-1566-9741','서울시 강서구'),
('240008','야부키나코','여','010-6564-9821','서울시 금천구'),
('240009','혼다히토미','여','010-7564-4621','서울시 서초구'),
('240010','조유리','여','010-1598-8721','서울시 강남구'),
('240011','안유진','여','010-1546-9721','서울시 송파구'),
('240012','장원영','여','010-3764-8721','서울시 송파구');
-- 2. 성적표 데이터
INSERT INTO 성적표 (학번,데이터베이스,JAVA프로그래밍,JSP프로그래밍,HTML5프로그래밍) VALUES
('240001','80','70','70','100'),
('240002','80','80','90','85'),
('240003','90','90','70','80'),
('240004','70','90','75','85'),
('240005','85','75','80','90'),
('240006','90','75','75','80'),
('240007','100','85','85','90'),
('240008','100','100','90','75');
-- 데이터 확인
MariaDB [대학교]> SELECT * FROM 학생;
+--------+-----------------+--------+---------------+---------------------+
| 학번 | 이름 | 성별 | 휴대폰 | 주소 |
+--------+-----------------+--------+---------------+---------------------+
| 240001 | 권은비 | 여 | 010-1564-9721 | 서울시 금천구 |
| 240002 | 이채연 | 여 | 010-1574-5521 | 서울시 도봉구 |
| 240003 | 사쿠라 | 여 | 010-1584-0522 | 서울시 노원구 |
| 240004 | 김채원 | 여 | 010-8964-1723 | 서울시 강북구 |
| 240005 | 강혜원 | 여 | 010-6564-7728 | 서울시 중구 |
| 240006 | 김민주 | 여 | 010-1464-9679 | 서울시 양천구 |
| 240007 | 최예나 | 여 | 010-1566-9741 | 서울시 강서구 |
| 240008 | 야부키나코 | 여 | 010-6564-9821 | 서울시 금천구 |
| 240009 | 혼다히토미 | 여 | 010-7564-4621 | 서울시 서초구 |
| 240010 | 조유리 | 여 | 010-1598-8721 | 서울시 강남구 |
| 240011 | 안유진 | 여 | 010-1546-9721 | 서울시 송파구 |
| 240012 | 장원영 | 여 | 010-3764-8721 | 서울시 송파구 |
+--------+-----------------+--------+---------------+---------------------+
12 rows in set (0.000 sec)
MariaDB [대학교]> SELECT * FROM 성적표;
+--------+--------------------+---------------------+--------------------+----------------------+
| 학번 | 데이터베이스 | JAVA프로그래밍 | JSP프로그래밍 | HTML5프로그래밍 |
+--------+--------------------+---------------------+--------------------+----------------------+
| 240001 | 80 | 70 | 70 | 100 |
| 240002 | 80 | 80 | 90 | 85 |
| 240003 | 90 | 90 | 70 | 80 |
| 240004 | 70 | 90 | 75 | 85 |
| 240005 | 85 | 75 | 80 | 90 |
| 240006 | 90 | 75 | 75 | 80 |
| 240007 | 100 | 85 | 85 | 90 |
| 240008 | 100 | 100 | 90 | 75 |
+--------+--------------------+---------------------+--------------------+----------------------+
8 rows in set (0.000 sec)
```
```SQL
-- JOIN 연습 시작
--1. INNER JOIN ON
SELECT
성적표.학번,
학생.이름,
성적표.데이터베이스,
성적표.JAVA프로그래밍,
성적표.JSP프로그래밍,
성적표.HTML5프로그래밍,
성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍 AS `총점`,
ROUND((성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍/3), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC)
FROM `성적표`
INNER JOIN `학생`
ON 성적표.학번 = 학생.학번
ORDER BY 성적표.학번 ASC;
-- 1. 출력
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+-------------------------------------+
| 학번 | 이름 | 데이터베이스 | JAVA프로그래밍 | JSP프로그래밍 | HTML5프로그래밍 | 총점 | 평균 | RANK() OVER(ORDER BY `평균` DESC) |
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+-------------------------------------+
| 240001 | 권은비 | 80 | 70 | 70 | 100 | 320 | 253.333 | 8 |
| 240002 | 이채연 | 80 | 80 | 90 | 85 | 335 | 278.333 | 3 |
| 240003 | 사쿠라 | 90 | 90 | 70 | 80 | 330 | 276.667 | 4 |
| 240004 | 김채원 | 70 | 90 | 75 | 85 | 320 | 263.333 | 7 |
| 240005 | 강혜원 | 85 | 75 | 80 | 90 | 330 | 270.000 | 5 |
| 240006 | 김민주 | 90 | 75 | 75 | 80 | 320 | 266.667 | 6 |
| 240007 | 최예나 | 100 | 85 | 85 | 90 | 360 | 300.000 | 2 |
| 240008 | 야부키나코 | 100 | 100 | 90 | 75 | 365 | 315.000 | 1 |
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+-------------------------------------+
--2. LEFT JOIN
SELECT
성적표.학번,
학생.이름,
성적표.데이터베이스,
성적표.JAVA프로그래밍,
성적표.JSP프로그래밍,
성적표.HTML5프로그래밍,
성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍 AS `총점`,
ROUND((성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍/3), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
LEFT JOIN `학생`
ON 성적표.학번 = 학생.학번
ORDER BY 성적표.학번 ASC;
-- 2. 출력
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
| 학번 | 이름 | 데이터베이스 | JAVA프로그래밍 | JSP프로그래밍 | HTML5프로그래밍 | 총점 | 평균 | 순위 |
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
| 240001 | 권은비 | 80 | 70 | 70 | 100 | 320 | 253.333 | 8 |
| 240002 | 이채연 | 80 | 80 | 90 | 85 | 335 | 278.333 | 3 |
| 240003 | 사쿠라 | 90 | 90 | 70 | 80 | 330 | 276.667 | 4 |
| 240004 | 김채원 | 70 | 90 | 75 | 85 | 320 | 263.333 | 7 |
| 240005 | 강혜원 | 85 | 75 | 80 | 90 | 330 | 270.000 | 5 |
| 240006 | 김민주 | 90 | 75 | 75 | 80 | 320 | 266.667 | 6 |
| 240007 | 최예나 | 100 | 85 | 85 | 90 | 360 | 300.000 | 2 |
| 240008 | 야부키나코 | 100 | 100 | 90 | 75 | 365 | 315.000 | 1 |
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
--3. RIGHT JOIN
SELECT
성적표.학번,
학생.이름,
성적표.데이터베이스,
성적표.JAVA프로그래밍,
성적표.JSP프로그래밍,
성적표.HTML5프로그래밍,
성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍 AS `총점`,
ROUND((성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍/3), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
RIGHT JOIN `학생`
ON 성적표.학번 = 학생.학번
ORDER BY 학생.학번 ASC;
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
| 학번 | 이름 | 데이터베이스 | JAVA프로그래밍 | JSP프로그래밍 | HTML5프로그래밍 | 총점 | 평균 | 순위 |
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
| 240001 | 권은비 | 80 | 70 | 70 | 100 | 320 | 253.333 | 8 |
| 240002 | 이채연 | 80 | 80 | 90 | 85 | 335 | 278.333 | 3 |
| 240003 | 사쿠라 | 90 | 90 | 70 | 80 | 330 | 276.667 | 4 |
| 240004 | 김채원 | 70 | 90 | 75 | 85 | 320 | 263.333 | 7 |
| 240005 | 강혜원 | 85 | 75 | 80 | 90 | 330 | 270.000 | 5 |
| 240006 | 김민주 | 90 | 75 | 75 | 80 | 320 | 266.667 | 6 |
| 240007 | 최예나 | 100 | 85 | 85 | 90 | 360 | 300.000 | 2 |
| 240008 | 야부키나코 | 100 | 100 | 90 | 75 | 365 | 315.000 | 1 |
| NULL | 혼다히토미 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
| NULL | 조유리 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
| NULL | 안유진 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
| NULL | 장원영 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
+--------+-----------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
--4. OUTER JOIN UNION(+)
SELECT
학생.학번,
학생.이름,
학생.휴대폰,
학생.주소,
성적표.데이터베이스,
성적표.JAVA프로그래밍,
성적표.JSP프로그래밍,
성적표.HTML5프로그래밍,
성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍 AS `총점`,
ROUND((성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍/3), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
LEFT JOIN `학생`
ON 성적표.학번 = 학생.학번
UNION
SELECT
학생.학번,
학생.이름,
학생.휴대폰,
학생.주소,
성적표.데이터베이스,
성적표.JAVA프로그래밍,
성적표.JSP프로그래밍,
성적표.HTML5프로그래밍,
성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍 AS `총점`,
ROUND((성적표.데이터베이스+성적표.JAVA프로그래밍+성적표.JSP프로그래밍+성적표.HTML5프로그래밍/3), 3) AS `평균`,
RANK() OVER(ORDER BY `평균` DESC) AS `순위`
FROM `성적표`
RIGHT JOIN `학생`
ON 성적표.학번 = 학생.학번;
-- 출력
+--------+-----------------+---------------+---------------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
| 학번 | 이름 | 휴대폰 | 주소 | 데이터베이스 | JAVA프로그래밍 | JSP프로그래밍 | HTML5프로그래밍 | 총점 | 평균 | 순위 |
+--------+-----------------+---------------+---------------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+
| 240008 | 야부키나코 | 010-6564-9821 | 서울시 금천구 | 100 | 100 | 90 | 75 | 365 | 315.000 | 1 |
| 240007 | 최예나 | 010-1566-9741 | 서울시 강서구 | 100 | 85 | 85 | 90 | 360 | 300.000 | 2 |
| 240002 | 이채연 | 010-1574-5521 | 서울시 도봉구 | 80 | 80 | 90 | 85 | 335 | 278.333 | 3 |
| 240003 | 사쿠라 | 010-1584-0522 | 서울시 노원구 | 90 | 90 | 70 | 80 | 330 | 276.667 | 4 |
| 240005 | 강혜원 | 010-6564-7728 | 서울시 중구 | 85 | 75 | 80 | 90 | 330 | 270.000 | 5 |
| 240006 | 김민주 | 010-1464-9679 | 서울시 양천구 | 90 | 75 | 75 | 80 | 320 | 266.667 | 6 |
| 240004 | 김채원 | 010-8964-1723 | 서울시 강북구 | 70 | 90 | 75 | 85 | 320 | 263.333 | 7 |
| 240001 | 권은비 | 010-1564-9721 | 서울시 금천구 | 80 | 70 | 70 | 100 | 320 | 253.333 | 8 |
| 240011 | 안유진 | 010-1546-9721 | 서울시 송파구 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
| 240010 | 조유리 | 010-1598-8721 | 서울시 강남구 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
| 240012 | 장원영 | 010-3764-8721 | 서울시 송파구 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
| 240009 | 혼다히토미 | 010-7564-4621 | 서울시 서초구 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
+--------+-----------------+---------------+---------------------+--------------------+---------------------+--------------------+----------------------+--------+---------+--------+728x90
반응형
'<개인공부> > [SQL데이터베이스]' 카테고리의 다른 글
| [MariaDB]JDBC DRIVER 다운로드 _ connectors (0) | 2024.04.25 |
|---|---|
| [SQL]0422_데이터베이스_MYSQL_ 테이블변경_ALTER_ 혼자실습 (0) | 2024.04.22 |
| [SQL]0419_테이블 조인 JOIN (1) | 2024.04.19 |
| [SQL]0419_MYSQL_성적처리 (0) | 2024.04.19 |
| [SQL] 0418_MYSQL_요점정리 (0) | 2024.04.18 |
