[DB] 01.데이터베이스
<0216 데이터베이스 2일차>
<0216 데이터베이스 3일차>
– 숫자 함수 – – ————
– count : 조건에 만족하는 레코드 (행)의 개수 SELECT COUNT(*) FROM student; – 학생의 수 – * 은 모든 행(레코드)의 개수를 구해준다 (주로 컬럼이 온다)
SELECT COUNT(*) FROM emp; SELECT COUNT(empno) FROM emp; SELECT COUNT(comm) FROM emp; – 4 – 실제 존재하는 값만 구해옴 (널 값 세지않음)
– emp 테이블에서 부서번호가 10인 사원의 수 SELECT * FROM emp;
SELECT COUNT(*) From emp WHERE deptno = 10;
– sum : 해당 칼럼의 합 SELECT SUM(sal)+SUM(comm) FROM emp; SELECT SUM(sal) + SUM(IFNULL(comm,0)) FROM emp WHERE deptno = 30;
– avg : 해당 칼럼의 평균 SELECT SUM(sal), COUNT(), SUM(sal)/COUNT(), AVG(sal) FROM emp;
– emp 테이블에서 comm의 평균 구하기 SELECT AVG(IFNULL(comm,0)) FROM emp; SELECT format(AVG(IFNULL(comm,0)),0) FROM emp;
– max : 최대값 출력, min : 최소값 출력 SELECT MAX(sal) FROM emp; SELECT MIN(sal) FROM emp;
– professor 테이블에서 교수들의 연봉을 초회하시오 – 교수번호, 이름, 월급액, 보너스, 연봉 – sum, avg, count는 그룹함수 전체가 기준이다 – 그룹함수는 group by와 같이 쓰인다. SELECT * FROM professor; SELECT profno, NAME, pay, bonus, (pay*12)+ifnull(bonus,0) AS “yearPay” FROM professor;
– GROUP BY SELECT deptno, COUNT(), SUM(sal) FROM emp GROUP BY deptno; SELECT deptno, COUNT(), avg(sal) FROM emp GROUP BY deptno; SELECT deptno, COUNT(), max(sal) FROM emp GROUP BY deptno; SELECT deptno, COUNT(), min(sal) FROM emp GROUP BY deptno;
– student 테이블에서 학년별 키 평균 조회 SELECT * FROM student; SELECT grade, AVG(height), MAX(height), MIN(height) FROM student GROUP BY grade;
– as (알리아스) 로 선언했으면 알리아스롤 변수명처럼 사용해서 해도 됨 SELECT deptno NO, MAX(sal) FROM emp GROUP BY NO;
– group by도 where 처럼 여러개 있으면 제일 앞쪽부터 정렬하고 – 동일한 값이 있을경우 그 다음 것을 기준으로 정렬 SELECT deptno, job, COUNT(*), SUM(sal) FROM emp GROUP BY deptno, job;
– group by의 조건절은 having!!
– emp 테이블에서 평균 급여가 2000 이상인 부서의 – 부서번호, 평균급여 조회 SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >= 2000;
– student 테이블에서 각 학년별 평균 몸무게가 50 이상인 – 학생의 학년과 평균 몸무게 조회 SELECT * FROM student; SELECT deptno1, grade, AVG(weight) FROM student GROUP BY deptno1, grade HAVING AVG(weight) >= 50 ORDER BY deptno1, grade;
==============================================================
– CASE : if문 같은 역활 SELECT empno,ename,deptno, case when deptno = 10 then ‘ACCOUNTING’ when deptno = 20 then ‘RESEARCH’ when deptno = 30 then ‘SALES’ when deptno = 40 then ‘OPERATIONS’ END AS dname FROM emp;
– 딱 떨어지는 값일 경우에는 이렇게 써도 된다 SELECT empno, ename, deptno, case deptno when 10 then ‘ACCOUNTING’ when 20 then ‘RESEARCH’ when 30 then ‘SALES’ when 40 then ‘OPERATIONS’ END AS dname FROM emp;
– exam_01 테이블에서 total 값이 90이상이면 a, – 80이상이면 b, 70이상이면 c, 60이상이면 d, 나머지 f SELECT * FROM exam_01;
SELECT studno, total, case when total >= 90 then ‘A’ when total >= 80 then ‘B’ when total >= 70 then ‘C’ when total >= 60 then ‘D’ – else ‘F’ when total < 60 then ‘F’ END AS “Grade” FROM exam_01; – 밑에 꺼는 안되는 경우– – 몇점 이상 몇점 이하 이렇게 쓰이는 경우는 위의 방식으로 SELECT studno, total, case total when 90 then ‘A’ when 80 then ‘B’ when 70 then ‘C’ when 60 then ‘D’ when 0 then ‘F’ END AS “Grade” FROM exam_01; – ——————————- – student 테이블에서 주민번호를 이용해서 성별을 조회 – 주민번호 7번째 숫자가 1 이나 3이면 man, – 숫자가 2 이나 4 이면 woman으로 조회 – 이름, 주민번호, 성별(MAN/WOMAN) – 2가지 방법 다 가능 SELECT * FROM student; SELECT NAME, jumin, case SUBSTR(jumin,7,1) when ‘1’ then ‘MAN’ when ‘2’ then ‘WOMAN’ when ‘3’ then ‘MAN’ ELSE ‘WOMAN’ END AS “gender” FROM student;
SELECT NAME, jumin, case when SUBSTR(jumin,7,1) = ‘1’ then ‘man’ when SUBSTR(jumin,7,1) = ‘2’ then ‘Woman’ when SUBSTR(jumin,7,1) = ‘3’ then ‘man’ when SUBSTR(jumin,7,1) = ‘4’ then ‘Woman’ END AS “gender” FROM student; – —————————————————— SELECT NAME, jumin, case when SUBSTR(jumin,7,1) = 1 OR SUBSTR(jumin,7,1) = 3 then ‘MAN’ when SUBSTR(jumin,7,1) = 2 OR SUBSTR(jumin,7,1) = 4 then ‘WOMAN’ END AS “Gender” FROM student;
– student 테이블에서 학생의 이름, 전화번호, 지역을 조회 – 단, 지역은 전화번호의 지역번호로 – 02: 서울, 031: 경기, 051: 부산, 052: 울산, 055: 경남으로 표시 SELECT NAME, tel, case SUBSTR(tel,1,INSTR(tel,’)’)-1) when 02 then ‘서울’ when 031 then ‘경기’ when 051 then ‘부산’ when 052 then ‘울산’ when 055 then ‘경남’ END AS ‘country’ FROM student;
– student 테이블에서 생년월일을 창조하여 태어난 달과 분기를 조회 – 태어난 달이 1~3월이면 1/4분기, 4~6월이면 2/4분기 – 7~9월이면 3/4분기, 10~12월이면 4/4분기 – 조회 컬럼 : 이름, 생년월일, 태어난달, 분기 SELECT * FROM student; SELECT NAME,birthday,MONTH(birthday) AS “MONTH”, case when QUARTER(birthday) >= 1 then ‘1/4분기’ when QUARTER(birthday) >= 4 then ‘2/4분기’ when QUARTER(birthday) >= 7 then ‘3/4분기’ when QUARTER(birthday) >= 10 then ‘4/4분기’ END AS “quarter” FROM student;
SELECT * FROM student; SELECT NAME,birthday,MONTH(birthday) AS “MONTH”, case when month(birthday) BETWEEN 1 AND 3 then ‘1/4분기’ when month(birthday) BETWEEN 4 AND 6 then ‘2/4분기’ when month(birthday) BETWEEN 7 AND 9 then ‘3/4분기’ when month(birthday) BETWEEN 10 AND 12 then ‘4/4분기’ END AS “quarter” FROM student;
– BETWEEN 1 AND 3
– professor 테이블에서 학과번호, 교수명, 학과명 조회 – 단, 학과번호가 101인 교수만 학과명을 computer engineering – 나머지는 아무것도 출력하지 않는다 SELECT * FROM professor; SELECT deptno, NAME, case deptno when ‘101’ then ‘COMPUTER ENGINEERING’ ELSE ‘’ END AS “ddd” FROM professor;
============================================================
– drop : 테이블을 삭제 DROP TABLE test1;
– join
– 테이블 생성 CREATE TABLE test1 ( A VARCHAR(10), B VARCHAR(20));
CREATE TABLE test2 ( A VARCHAR(10), C VARCHAR(20), D VARCHAR(20));
INSERT INTO test1 VALUES(‘a1’,’b1’); INSERT INTO test1 VALUES(‘a2’,’b2’);
INSERT INTO test2 VALUES(‘a3’,’c3’,’d3’); INSERT INTO test2 VALUES(‘a4’,’c4’,’d4’); INSERT INTO test2 VALUES(‘a5’,’c5’,’d5’);
– 이것이 조인 한것 – 행은 각각의 행의 개수를 곱한다 – 열은 각각의 열의 개수를 더한다 SELECT * FROM test1 JOIN test2;
SELECT * FROM test1; SELECT * FROM test1, test2;
SELECT * FROM test1 JOIN test2 ORDER BY 1;
– 조인과 where 절 사용 방법 ANSI(안시)조인 (표준) SELECT emp.ename, emp.deptno, dept.dname FROM emp JOIN dept WHERE emp.DEPTNO = dept.DEPTNO;
SELECT emp.ename, emp.deptno, dept.dname FROM emp e, dept d – (e, d 는 as알리아스) WHERE e.DEPTNO = d.DEPTNO;
SELECT emp.ename, emp.deptno, dept.dname FROM emp, dept WHERE emp.DEPTNO = dept.DEPTNO;
– student, department 테이블을 이용하여 학번, 이름, 학과명 조회 SELECT s.deptno1, s.name, d.dname, p.name FROM student s JOIN department d ON s.deptno1= d.deptno JOIN professor p ON s.profno = p.profno;
SELECT s.studno, s.name, d.dname, p.name FROM student s, department d, professor p WHERE s.deptno1=d.deptno AND s.profno = p.profno;
– student, professor 테이블을 이용하여 학번, 이름, 담당교수명 조회 SELECT s.studno, s.name, p.name FROM student s JOIN professor p ON s.profno = p.profno;
– s.* student 에 관한 모든 정보를 출력 SELECT s.*, p.name FROM student s left JOIN professor p ON s.profno = p.profno;
– student, department 테이블을 이용하여 – 학번, 이름, 학년, 제1전공과명, 제2 전공과명 조회 SELECT s.studno,s.name, s.grade, d1.dname, d1.dname FROM student s JOIN department d1 ON s.deptno1 = d1.deptno left JOIN department d2 ON s.deptno2 = d2.deptno; – left join 쓰면 on 을 반드시 써야 한다
SELECT s.*, p.name FROM student s RIGHT JOIN professor p ON s.profno = p.profno;
SELECT s.name, p.name FROM student s left JOIN professor p ON s.profno = p.profno UNION SELECT s.name, p.name FROM student s RIGHT JOIN professor p ON s.profno = p.profno;
– student, exam_01 테이블을 이용하여 학번, 이름, 학년, 점수 조회 SELECT s.studno, s.name, s.grade, e.total FROM student s JOIN exam_01 e ON s.studno = e.studno;
– emp2 테이블을 이용하여, 사번, 이름, 나의 관리자 사번, 관리자명 조회 SELECT e2.EMPNO, e2.NAME, e2.PEMPNO,e22.NAME FROM emp2 e2 left JOIN emp2 e22 ON e2.PEMPNO = e22.EMPNO;
– emp 테이블을 이용하여 사번, 이름, 나의 관리자 사번, 관리자명 조회 SELECT e1.EMPNO, e1.ENAME, e1.MGR, e2.ENAME FROM emp e1 left JOIN emp e2 ON e1.mgr = e2.empno;
– student, exam_01, hakjum 테이블을 이용하여 학번, 이름, 학년, 점수 – 학점 조회 SELECT s.studno, s.grade, e.total, e.total, h.grade FROM student s JOIN exam_01 e ON s.studno = e.studno JOIN hakjum h ON e.total BETWEEN h.min_point AND h.max_point;
– gogak과 gift 테이블을 이용하여 고객이름, 보유포인트, 포인트로 – 받을 수 있는 가장 좋은 선물 이름 SELECT g.gname, g.point, gi.gname FROM gogak g JOIN gift gi ON g.point BETWEEN gi.g_start AND gi.g_end;
– emp2, p_grade 테이블을 이용하여 이름, 직급, 급여 – 같은 직급의 최소급여, 같은 직급의 최대급여 조회 SELECT e.NAME, e.position, e.pay, p.s_pay, p.e_pay FROM emp2 e JOIN p_grade p ON e.POSITION = p.position AND e.pay BETWEEN p.s_pay AND p.e_pay;
– emp2, p.grade 테이블을 이용하여 본인의 직급에 해당하는 최대 급여보다 – 많이 받는 직원의 이름, 직위, 급여, 같은 직급의 최대급여 조회 SELECT e.NAME, e.position, e.pay, p.s_pay, p.e_pay FROM emp2 e, p_grade p WHERE e.POSITION = p.position AND e.PAY > p.e_pay;
ON e.POSITION = p.position AND e.pay BETWEEN p.s_pay AND p.e_pay;
– emp2, p_grade 테이블을 이용하여 이름, 직급, 나이 – 본인의 나이에 해당하는 예상 직급 조회 SELECT e.NAME, e.POSITION, format(DATEDIFF(CURDATE(), birthday)/365,0) AS “age”, p.position FROM emp2 e JOIN p_grade p WHERE format(DATEDIFF(CURDATE(), birthday)/365,0) BETWEEN p.s_age AND p.e_age
댓글남기기