오늘도 이어서 MySQL을 사용한 join과 서브쿼리에 대해서 배워봤다.
핵심키워드는 교차조인, 내부조인, 등가조인, 자연조인, using 조인이다.
-- 인물 테이블
drop table tableA;
CREATE TABLE tableA (
id INT NOT NULL AUTO_INCREMENT,
pno int, -- 직업번호
name VARCHAR(10), -- 이름
PRIMARY KEY (id));
INSERT INTO tableA VALUES ( null, 101, '이순신');
INSERT INTO tableA VALUES ( null, 102, '홍길동');
INSERT INTO tableA VALUES ( null, 100, '안중근');
INSERT INTO tableA VALUES ( null, 102, '임꺽정');
INSERT INTO tableA VALUES ( null, 100, '윤봉길');
INSERT INTO tableA VALUES ( null, 101, '강감찬');
select * from tableA;
-- 직업 테이블
drop table tableB;
CREATE TABLE tableB (
id INT NOT NULL AUTO_INCREMENT,
cno int, -- 직업번호
name VARCHAR(10), -- 직업
PRIMARY KEY (id));
INSERT INTO tableB VALUES ( null, 100, '의사');
INSERT INTO tableB VALUES ( null, 101, '장군');
INSERT INTO tableB VALUES ( null, 102, '의적');
select * from tableB;
drop table tableA;
CREATE TABLE tableA (
id INT NOT NULL AUTO_INCREMENT,
pno int, -- 교수 번호
name VARCHAR(10), -- 교수 이름
PRIMARY KEY (id));
INSERT INTO tableA VALUES ( null, 100, '홍길동');
INSERT INTO tableA VALUES ( null, 101, '이순신');
INSERT INTO tableA VALUES ( null, 102, '안중근');
INSERT INTO tableA VALUES ( null, 103, '임꺽정');
INSERT INTO tableA VALUES ( null, 104, '강감찬');
drop table tableB;
CREATE TABLE tableB (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(10), -- 과목명
num INT, -- 학점
pno int, -- 담당 교수번호
PRIMARY KEY (id));
INSERT INTO tableB VALUES ( null, '국어', 4, 103);
INSERT INTO tableB VALUES ( null, '영어', 3, 104);
INSERT INTO tableB VALUES ( null, '수학', 2, 102);
INSERT INTO tableB VALUES ( null, '사회', 1, 101);
INSERT INTO tableB VALUES ( null, '체육', 2, 103);
INSERT INTO tableB VALUES ( null, '생물', 2, 102);
select * from tableB;
-- 2학점 과목을 강의하는 교수를 검색하세요.
-- 서브쿼리 또는 조인을 생각해보기 1st 조인!
select distinct a.name from tableA a,tableB b
where a.pno = b.pno
and b.num=2;
drop table tableA;
CREATE TABLE tableA (
id INT NOT NULL AUTO_INCREMENT,
eno int, -- 학번
name VARCHAR(10), -- 학생 이름
major VARCHAR(10), -- 학과명
year int, -- 학년
PRIMARY KEY (id));
INSERT INTO tableA VALUES ( null, 1000,'홍길동','국어',1);
INSERT INTO tableA VALUES ( null, 1001,'이순신','화학',1);
INSERT INTO tableA VALUES ( null, 1002,'안중근','화학',2);
INSERT INTO tableA VALUES ( null, 1003,'임꺽정','국어',2);
INSERT INTO tableA VALUES ( null, 1004,'강감찬','화학',1);
select * from tableA;
-- 기말고사 점수 테이블
drop table tableB;
CREATE TABLE tableB (
id INT NOT NULL AUTO_INCREMENT,
eno int, -- 학번
cno int, -- 시험과목번호
result int, -- 점수
PRIMARY KEY (id));
INSERT INTO tableB VALUES ( null, 1000, 10, 59);
INSERT INTO tableB VALUES ( null, 1000, 20, 34);
INSERT INTO tableB VALUES ( null, 1001, 10, 80);
INSERT INTO tableB VALUES ( null, 1001, 20, 79);
INSERT INTO tableB VALUES ( null, 1001, 30, 33);
INSERT INTO tableB VALUES ( null, 1002, 20, 48);
INSERT INTO tableB VALUES ( null, 1003, 30, 55);
INSERT INTO tableB VALUES ( null, 1004, 10, 99);
select * from tableB;
-- 화학과 1학년 학생들의 성적(점수)을 검색하세요.
select a.eno,a.name,b.result
from tableA a, tableB b
where a.eno = b.eno and major='화학'and year = 1;
교차조인,내부조인, using조인
-- join의 종류
-- 교차조인 , cross join
select *
from tableA a, tableB b
where a.eno = b.eno;
-- 자연 조인
select * from tableA
natural join tableB;
-- 등가조인(내부 조인)
select *
from tableA
inner join tableB
on tableA.eno = tableB.eno;
-- using 조인
select * from tableA
join tableB using(eno);
내부조인 과 between
between A and B
=> A와 B사이 모든 값을 조건으로 합니다!
-- 내부조인
select name, grade from tableA t1
inner join tableB t2
on t1.salary >= t2.losalary
and t1.salary <= t2.hisalary;
-- between
select name, grade from tableA t1
inner join tableB t2
on t1.salary between t2.losalary and t2.hisalary;
-- 일반조인
select name,grade from tableA t1, tableB t2
where t1.salary between t2.losalary and t2.hisalary;
-- 등급이 A 인 사람 이름 보여주기
select name,grade
from tableA t1, tableB t2
where t2.grade='A'
and t1.salary between t2.losalary and t2.hisalary;
셀프 조인
자기자신 테이블끼리 조인하기!
DROP TABLE table01;
CREATE TABLE table01 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) NULL,
eno INT NULL, -- 사원번호
mgr INT NULL, -- 사수번호, 멘토번호
salary INT NULL,
PRIMARY KEY (`id`)); -- 위에 변경하면 같이 변경한다.
insert into table01 values (null, '홍길동', 1000, null, 100);
insert into table01 values (null, '이순신', 1001, 1000, 200);
insert into table01 values (null, '안중근', 1002, 1001, 300);
insert into table01 values (null, '임꺽정', 1003, 1002, 400);
-- self join
select * from table01 a, table01 b
where a.mgr = b.eno;
전형적으로 동명이인 검색문제는 셀프 조인 입니다! (self join - 자기자신 테이블 안에서 join하는 것)
동명이인 검색?
1. t1.id!=t2.id
2. name끼리 같은 것
3. distinct t1.name
-- 동명이인을 검색하세요! 셀프조인 문제
-- cross join한 결과
select distinct t1.name
from tableA t1, tableA t2
where t1.id!=t2.id
and t1.name=t2.name;
세미조인
-- DROP TABLE menu;
CREATE TABLE menu(
foodnum int,
name varchar(20)
);
-- DROP TABLE sell;
CREATE TABLE sell(
no int, -- 인덱스
count int, -- 판매수량
foodnum int -- 판매음식 번호
);
insert into menu values (1, '짜장');
insert into menu values (2, '우동');
insert into menu values (3, '냉면');
insert into menu values (4, '탕슉');
insert into menu values (5, '양장');
insert into sell values (1, 2, 1);
insert into sell values (2, 3, 2);
insert into sell values (3, 4, 2);
insert into sell values (4, 2, 2);
insert into sell values (5, 2, 1);
-- 오늘 판매된 음식을 출력하세요.
-- 판매된 음식 번호(1,2,2,2,1)
select distinct (s.foodnum)
from sell s;
select * from menu d -- main쿼리에서 사용된 테이블
-- where d.foodnum = 1 or d.foodnum = 2
-- where d.foodnum in(1,2);
-- where d.foodnum in(1,2,2,2,1);
where d.foodnum in(select distinct (s.foodnum) -- 서브쿼리에서 사용된 테이블
from sell s);
-- 1. menu에서 row 1개 가져온다
-- 2. 가져온 row가 exists 안의 문장에서 참인지 거짓인지 평가한다.
-- 3. 참인 경우는 가져온 row를 출력한다.
-- 거짓인 경우는 다음 row를 가져온다.
-- 4. 이러한 조인방식을 세미조인이라고 한다.
-- 5. 세미조인의 반대는 안티조인( not exist)
-- performance 좋음, 수행속도 굉장히 빠름
select *
from menu d
where not exists (select *
from sell s
where d.foodnum = s.foodnum);
outer join
outerjoin 중에서도 Left join에 대해서 배워보겠다.
DROP TABLE table01;
CREATE TABLE table01
(
ID int,
name VARCHAR(20)
);
DROP TABLE table02;
CREATE TABLE table02
(
ID int,
age int
);
INSERT INTO table01(ID, name) VALUES (1, 'tiger1');
INSERT INTO table01(ID, name) VALUES (2, 'tiger2');
INSERT INTO table01(ID, name) VALUES (3, 'tiger3');
INSERT INTO table01(ID, name) VALUES (4, 'tiger5');
INSERT INTO table01(ID, name) VALUES (5, 'tiger6');
INSERT INTO table02(ID, age) VALUES (3, 30);
INSERT INTO table02(ID, age) VALUES (4, 40);
INSERT INTO table02(ID, age) VALUES (5, 50);
INSERT INTO table02(ID, age) VALUES (6, 60);
INSERT INTO table02(ID, age) VALUES (7, 80);
select *
from table01, table02
where table01.ID = table02.ID;
-- outer join
select *
from table01
left join table02 using (ID);
병합 union, union all
drop table table01;
CREATE TABLE `table01` (
`id` INT NOT NULL AUTO_INCREMENT,
`eno` int,
`name` VARCHAR(45) NULL,
`salary` INT NULL,
PRIMARY KEY (`id`));
drop table table02;
CREATE TABLE `table02` (
`id` INT NOT NULL AUTO_INCREMENT,
`eno` int,
`name` VARCHAR(45) NULL,
`salary` INT NULL,
PRIMARY KEY (`id`));
INSERT INTO table01 VALUES (null, 10, 'tiger1', 100);
INSERT INTO table01 VALUES (null, 20, 'tiger2', 200);
INSERT INTO table01 VALUES (null, 30, 'tiger3', 300);
SELECT * FROM table01;
INSERT INTO table02 VALUES (null, 10, 'tiger3', 100);
INSERT INTO table02 VALUES (null, 20, 'tiger4', 200);
INSERT INTO table02 VALUES (null, 30, 'tiger5', 300);
SELECT * FROM table02;
-- 중복 데이터값 제거
select name from table01
union
select name from table02;
-- 중복 데이터값 제거하지 않음
select name from table01
union all
select name from table02;
-- 병합의 결과
select name from table01
where eno <15
union
select name from table02
where eno>25;
upper lower, length 관련 쿼리
DROP TABLE table01;
CREATE TABLE table01
(
eno int,
ename VARCHAR(20),
sal int
);
INSERT INTO table01 values(10, '이순신', 100 );
INSERT INTO table01 values(20, '홍길동', null );
INSERT INTO table01 values(30, '김신', 300 );
INSERT INTO table01 values(40, '한글을만들자abc', 100 );
select * from table01;
select upper(ename) from table01;
select lower(ename) from table01;
-- select initcap(ename) from table01;
select *
from table01
where upper(ename) = upper('TIGER1');
select length(ename) from table01;
select ename from table01 where length(ename)=9;
오후수업
DROP TABLE table01;
CREATE TABLE table01
(
eno int,
tel varchar(20),
ename varchar(20)
);
insert into table01 values(10, '02)200-300', '호랑이만세' );
insert into table01 values(20, '051)800-900', '만호랑이세' );
insert into table01 values(30, '1234)500-600', '만세호랑이' );
insert into table01 values(40, '1234)500-600', ' 호 랑 이 ' );
insert into table01 values(null, '1234)500-600', null );
select * from table01;
-- 5번 인덱스부터 3자리를 얻어온다.
select substr(tel,5,3) from table01;
-- 9번 인덱스 부터 끝까지
select substr(tel,9) from table01;
-- 끝에서 부터 인덱스를 카운팅하여 끝까지
select substr(tel,-4) from table01;
select substr(tel,5,3), substr(tel,9),substr(tel,-4) from table01;
-- 문자열 찾았을 때의 인덱스 번호를 리턴한다.
select instr(ename,'호랑이') from table01;
select instr(tel,')' ) from table01;
select substr(tel,1,instr(tel,')')-1) from table01;
select replace(ename,'호랑이','코끼리') from table01;
select replace(tel,'-',' ') from table01;
-- right pad
select rpad(ename,20,'*') from table01;
-- left pad
select lpad(ename,20,'*') from table01;
select substr(tel,-7,4) from table01; -- 끝에서부터 7번째 인덱스에서 4개 취함
select rpad(substr(tel,-7,4),7,'*') from table01;
문자열 관련
DROP TABLE table01;
CREATE TABLE table01
(
eno int,
tel varchar(20),
ename varchar(20)
);
insert into table01 values(10, '02)200-300', '호랑이만세' );
insert into table01 values(20, '051)800-900', '만호랑이세' );
insert into table01 values(30, '1234)500-600', '만세호랑이' );
insert into table01 values(40, '1234)500-600', ' 호 랑 이 ' );
insert into table01 values(null, '1234)500-600', null );
select * from table01;
-- 5번 인덱스부터 3자리를 얻어온다.
select substr(tel,5,3) from table01;
-- 9번 인덱스 부터 끝까지
select substr(tel,9) from table01;
-- 끝에서 부터 인덱스를 카운팅하여 끝까지
select substr(tel,-4) from table01;
select substr(tel,5,3), substr(tel,9),substr(tel,-4) from table01;
-- 문자열 찾았을 때의 인덱스 번호를 리턴한다.
select instr(ename,'호랑이') from table01;
select instr(tel,')' ) from table01;
select substr(tel,1,instr(tel,')')-1) from table01;
select replace(ename,'호랑이','코끼리') from table01;
select replace(tel,'-',' ') from table01;
-- right pad
select rpad(ename,20,'*') from table01;
-- left pad
select lpad(ename,20,'*') from table01;
select substr(tel,-7,4) from table01; -- 끝에서부터 7번째 인덱스에서 4개 취함
select rpad(substr(tel,-7,4),7,'*') from table01;
select concat(eno, ' : ', ename) from table01;
select concat(eno, concat( ' : ', ename)) from table01;
-- 맨 끝 공백을 제거시켜주는 역할
select trim(ename) from table01;
select 900 + ifnull(eno,99), ifnull(ename,'익명') from table01;
select eno,
case eno
when 10 then '호랑이1'
when 20 then '호랑이2'
when 30 then '호랑이3'
end as '결과'
from table01;
select eno,
case eno
when 10 then eno*2
when 20 then eno*3
when 30 then eno*4
end as '결과'
from table01;
select round(1234.4) from dual;
select round(1234.5) from dual;
select round(1234.5678) from dual;
select round(1235,-1) from dual; -- 1240
select round(1234,-1) from dual; -- 1230
select ceil(3.14) from dual;
select floor(3.14) from dual;
select ceil(-3.14) from dual;
select floor(-3.14) from dual;
select ceil(7/3) from dual;
select floor(7/3), mod(7,3) from dual;
select 10+20 as 결과 from dual; -- 결과 이름의 column 값? 30
select 10+'20' as 결과 from dual; -- 숫자로 인식해, 구태여 문자를 숫자로 변환 시킬 필요 없음
select '10'+20 as 결과 from dual;
select 'tiger'+ 'lion' as '결과' from dual; -- 문자로 인식해 합산? 합치기? 되지 않음
집계함수
DROP TABLE table01;
CREATE TABLE table01(
eno int,
sal int
);
insert into table01 values(10,100);
insert into table01 values(20,null);
insert into table01 values(30,300);
insert into table01 values(40,300);
-- 집계함수
select * from table01;
select sum(sal),
sum(distinct sal),
count(eno),
max(eno),
min(eno),
variance(eno), -- 분산
stddev(eno) -- 표준편차
from table01;
select count(*) from table01
where sal = 300;
DROP TABLE table01;
CREATE TABLE table01
(
eno int,
address varchar(20),
salary int
);
INSERT INTO table01 VALUES (10, '서울', 100);
INSERT INTO table01 VALUES (20, '부산', 200);
INSERT INTO table01 VALUES (20, '대구', 300);
INSERT INTO table01 VALUES (10, '서울', 400);
select * from table01;
select avg(salary) from table01 where eno=20;
-- 부서번호가 20인 사원중에 급여가 가장 많은 사원의 금액은 얼마인가?
select max(salary) from table01
where eno=20;
-- 부서번호가 20인 사원중에 급여가 가장 많은 사람의 이름은 무엇인가?
select address from table01
where salary=(select max(salary)from table01 where eno=20);
통계함수 Group by 와 관련지어서
DROP TABLE table01;
CREATE TABLE table01(
eno int,
sal int);
insert into table01 values(1, 800);
insert into table01 values(2, 200);
insert into table01 values(2, 400);
insert into table01 values(1, 500);
insert into table01 values(2, 300);
select * from table01;
select eno, sum(sal) from table01
group by eno;
select eno, sum(sal)
from table01
where sal>300
group by eno;
select eno,sum(sal)
from table01
where sal>300
group by eno
having sum(sal)>1000;
'개발 교육' 카테고리의 다른 글
| 11.17 리눅스 (1) | 2023.11.17 |
|---|---|
| 11.15 DB (2) | 2023.11.15 |
| 11.10 수업 MySQL (0) | 2023.11.10 |
| 11.9 수업 MySQL (0) | 2023.11.09 |
| 11.03 <자바FX> (0) | 2023.11.04 |
