데이터베이스/MySQL
MySQL 3일차 - 내장함수, 내부조인, 외부조인, 셀프조인, cross조인, natural조인
비전공개미
2022. 9. 7. 18:47
반응형
SMALL
2022-09-07-수요일
[문제풀이]
문제1] job컬럼 필드에서 manager가 아닌 데이터조회
SELECT * FROM emp WHERE job != 'MANAGER';
SELECT * FROM emp WHERE job not in ('MANAGER');
문제2] comm컬럼 필드에서 null인 데이터조회
SELECT * FROM emp WHERE comm is null; <-> SELECT * FROM emp WHERE comm is not null;
문제3] deptno컬럼 필드별 sal급여합계 구하기
SELECT deptno, sum(sal) FROM emp GROUP BY deptno;
문제4] ename컬럼 필드에서 scott, ford 데이터조회
SELECT * FROM emp WHERE ename in ('scott', 'ford');
문제5] hiredate컬럼 필드의 2011년도에 입사한 사람 like
SELECT * FROM emp WHERE hiredate like '2011%';
SELECT * FROM emp WHERE date_format(hiredate, '%y') like '11';
문제6] sal컬럼 필드의 2000~5000사이 데이터조회
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 5000;
문제7] ename컬럼 필드의 M/m 포함된 사람
SELECT * FROM emp WHERE ename like '%M%';
문제8] job컬럼 필드 그룹별 sal급여합계 구하기 합계가 많은 순으로 sort
SELECT job, sum(sal) FROM emp GROUP BY job ORDER BY sum(sal) DESC;
SELECT job, sum(sal) as total FROM emp GROUP BY job ORDER BY total DESC;
SELECT job, sum(sal) as total FROM emp GROUP BY job ORDER BY 2 DESC;
문제9] job그룹별 급여총합계가 5000이하인 그룹을 조회한다
SELECT job, sum(sal) as myavg FROM emp GROUP BY job HAVING myavg <= 5000 ORDER BY myavg DESC; (o)
ㄴgroup으로 되어잇으면 having절에서 값을 조회
SELECT job, sum(sal) as myavg FROM emp WHERE sum(sal) <= 5000 GROUP BY job ORDER BY myavg DESC; (x)
SELECT job, sum(sal) as myavg FROM emp WHERE myavg <= 5000 GROUP BY job ORDER BY myavg DESC; (x)
ㄴgroup으로 되어있으면 where절에 입력시 조회하지 못한다
ㄴ그룹이 없을시 where절에 / 그룹일시 having절에 입력
문제]
고객테이블 customer테이블 hit조회수 defualt 0 추가
ALTER TABLE customer ADD hit int(10) default 0;
고객테이블 customer테이블 birth필드를 hiredate필드명 변경
ALTER TABLE customer CHANGE birth hiredate date;
hit조회수 데이터 0을 1로 변경
UPDATE customer SET hit = 1;
★W G H O L 순서대로 기입★
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
첫번째 table생성 customer고객
idx,
name,
gender,
birth,
job,
local
데이터 신규등록2건 insert
DROP TABLE customer;
CREATE TABLE customer (
idx int(4) primary key,
name char(10) not null,
gender int(1) default 0,
birth date default '0000-00-00', #birth date default current_timestamp(버전에러),
job char(20),
local char(20) default '서울'
);
INSERT INTO customer (idx, name, birth) VALUES (1000, '홍길동', '1990-09-25');
INSERT INTO customer (idx, name, birth) VALUES (1001, '성춘향', '1992-05-15');
INSERT INTO customer (idx, name, birth) VALUES (1002, '임꺽정', '1982-07-30');
------------------------------------------------------------------------------------------------------
usertbl이승기.txt참조
//DROP DATABASE IF EXISTS sqldb; -- 만약 sqldb가 존재하면 우선 삭제한다.
//CREATE DATABASE sqldb;
//USE sqldb;
DROP TABLE usertbl;
CREATE TABLE usertbl ( -- 회원 테이블
userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
name VARCHAR(10) NOT NULL, -- 이름
birthYear INT NOT NULL, -- 출생년도
addr CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
mobile1 CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 키
mDate DATE -- 회원 가입일
);
DROP TABLE buytbl;
CREATE TABLE buytbl ( -- 회원 구매 테이블(Buy Table의 약자)
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
userID CHAR(8) NOT NULL, -- 아이디(FK)
prodName CHAR(6) NOT NULL, -- 물품명
groupName CHAR(4) , -- 분류
price INT NOT NULL, -- 단가
amount SMALLINT NOT NULL, -- 수량
FOREIGN KEY (userID) REFERENCES usertbl(userID)
);
desc usertbl;
desc buytbl;
-- 데이터입력 --
INSERT INTO usertbl VALUES ('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES ('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES ('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES ('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES ('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO usertbl VALUES ('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES ('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO usertbl VALUES ('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES ('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES ('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES (NULL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES (NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES (NULL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buytbl VALUES (NULL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buytbl VALUES (NULL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buytbl VALUES (NULL, 'BBK', '메모리', '전자', 80, 10);
INSERT INTO buytbl VALUES (NULL, 'SSK', '책' , '서적', 15, 5);
INSERT INTO buytbl VALUES (NULL, 'EJW', '책' , '서적', 15, 2);
INSERT INTO buytbl VALUES (NULL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buytbl VALUES (NULL, 'BBK', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES (NULL, 'EJW', '책' , '서적', 15, 1);
INSERT INTO buytbl VALUES (NULL, 'BBK', '운동화', NULL , 30, 2);
join - 하나 이상의 테이블이서 원하는 필드를 조합해서 새로운 데이터구성을 출력
join에서 inner생략가능, outer생략가능
ㄴ내부조인 SELECT 필드~ FROM A테이블 INNER JOIN B테이블 ON A.연결필드=B.연결필드;
ㄴ내부조인 SELECT 필드~ FROM A테이블, B테이블 WHERE A.연결필드=B.연결필드;
ㄴnon-equi조인 SELECT 필드~ FROM A테이블 INNER JOIN B테이블;
ㄴ외부조인 SELECT 필드~ FROM A테이블 LEFT/RIGHT OUTER JOIN B테이블 ON A.연결필드=B.연결필드;
ㄴ셀프조인 SELECT 필드~ FROM A테이블 INNER JOIN A테이블 ON A.연결필드=A.연결필드;
ㄴcross조인 SELECT 필드~ FROM A테이블 CROSS JOIN B테이블;
ㄴnatural조인 SELECT 필드~ FROM A테이블 NATURAL JOIN B테이블;
SELECT u.userid, name, prodName, price, amount FROM usertbl u, buytbl b WHERE u.userid=b.userid;
SELECT u.userid, name, prodName, price, amount FROM usertbl u inner join buytbl b WHERE u.userid=b.userid;
INNER JOIN(=JOIN) -- inner를 생략해도 사용가능
LEFT OUTER JOIN -- A테이블의 모든내용을 null값도 출력하며 가져온다
RIGHT OUTER JOIN -- B테이블의 모든내용을 null값도 출력하며 가져온다
#non-equi조인 emp테이블 + salgrade테이블 sal급여데이터로 grade등급 표시
SELECT empno as '사번', ename as '사원명', FORMAT(sal, 0) as '급여', CONCAT(grade, '등급') as '등급'
FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
SELECT empno as '사번', ename as '사원명', LPAD(CONCAT(FORMAT(sal, 0),'만원'), 10, '*') as '급여', CONCAT(grade, '등급') as '등급'
FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
#셀프조인 emp테이블 자신테이블 참조
[출력결과] = smith의 상사는 ford입니다.
SELECT DISTINCT(b.empno), CONCAT(b.ename, "의 상사는 ", a.ename, " 입니다")
FROM emp a, emp b WHERE a.empno=b.mgr AND b.mgr is not null;
SELECT e.ename, CONCAT('님의 상사는 '), f.ename, CONCAT('입니다')
FROM emp e INNER JOIN emp f ON e.mgr=f.empno;
#cross조인 비권장
SELECT empno, ename, job, dname, loc FROM emp e CROSS JOIN dept d;
ㄴcross join은 on절을 사용안함
#natural조인 알아서 조인
SELECT empno, ename, job, dname, loc FROM emp e NATURAL JOIN dept d;
ㄴnatural join은 on절을 사용안함# 3개 테이블 조인 emp+dept+salgrade
SELECT empno, ename, job, dname, loc, sal, grade
FROM emp e, dept d, salgrade s
WHERE e.deptno=d.deptno AND sal BETWEEN losal AND hisal;
문제] deptno컬럼필드 그룹별 sal급여합계 구하기
SELECT deptno, sum(sal) as myavg FROM emp GROUP BY deptno;
SELECT dname, sum(sal) as "총급여" FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY dname;
+------------+----------+
| dname | 총급여 |
+------------+----------+
| SALES | 20100.00 |
| RESEARCH | 14575.00 |
| ACCOUNTING | 7250.00 |
+------------+----------+
데이터 출처 : 한빛미디어 이것이 MySQL이다 - 교재소스코드
반응형
LIST