MySQL 2일차 - 테이블생성, 내부함수, WHERE / GROUP BY / HAVING / ORDER BY / LIMIT, View테이블, JOIN(조인)

SQL
ㄴDDL(CREATE, ALTER, DROP)
ㄴDML(INSERT, SELECT, UPDATE, DELETE)
ㄴDCL(GRANT, REVOKE)

교재 80페이지 참조
MS사 닷넷기반 MSSQL서버는 SP=Stored Procedure
oracle, mysql은 DB2와 비슷한형태
면접때 join, subquery 사용여부

3가지 내부 join조인
SELECT  e.empno, e.ename, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno ;  
SELECT empno, ename, job, dname FROM emp INNER JOIN dept ON emp.deptno=dept.deptno ;
SELECT e.empno, e.ename, e.job, d.dname, d.loc FROM emp e INNER JOIN dept d USING(deptno) ;
ㄴusing을 써서 동일한 값을 뽑아내기

문제] emp테이블 sal급여가 1500~3000사이 데이터 구하기
 SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

SELECT empno, ename, sal, grade FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
ㄴemp테이블과 salgrade테이블은 조건이 똑같은 컬럼이 없지만 join하여 값을 맞춰볼 수 있다.

 

77페이지 View가상테이블(속도빠름)

★★★View테이블 생성
CREATE VIEW 테이블명 as SELECT 컬럼 FROM 테이블명;
ㄴview테이블을 만든다 as select 가져올 내용

 

60페이지 새로운테이블 memberTBL생성

CREATE TABLE memberTBL (
    memberID CHAR(8) PRIMARY KEY,
    memberName CHAR(5) NOT NULL,
    memberAddress CHAR(20)
);

 

WORKBENCH 테이블 생성시 제약조건 
여기서 제약조건에 해당하는 PK NN UQ B UN ZF AI G Default/Expression은 다음과 같다

PK - Primary key, 중복이나 빈값(NULL)이 들어올 수 없음
NN - Not Null(빈값) 못들어옴
UQ - Unique, 중복 값을 넣을 수 없음
B - 데이터를 이진 문자열로 저장함(010101 같은)
UN - Unsigned data type (- 범위 삭제)
INT, DOUBLE 등의 경우 UN을 사용해 주면 -값 +값 이던 범위가
      - 값은 없어지고 +값만 2배로 늘어남
ZF - Zero Filled 컬럼 크기보다 작은 값을 넣었을 경우 0으로 채운 뒤 삽입시킴
AI - Insert 시마다 값 1씩 늘어남
G - 다른 열을 기반으로 한 수식으로 생성된 값

 

 

★★★ W G H O L 순서대로 기입해야한다!!!! ★★★

 

SELECT 필드1, 필드2 
FROM 테이블명 
WHERE 필드명 = 필드값
GROUP BY 묶을필드명
HAVING 조건값
ORDER BY 필드명 ASC/DESC
LIMIT 갯수

SELECT * FROM board WHERE sabun = 2234;  -- 같다
SELECT * FROM board WHERE sabun != 2234;  -- 다르다
SELECT * FROM board WHERE sabun <> 2234;  -- 다르다

SELECT * FROM board WHERE hit > 50;  -- 50보다 크다
SELECT * FROM board WHERE hit <= 80;  -- 80보다 작거나 같다
SELECT * FROM board WHERE hit BETWEEN 50 AND 80;  -- 50이상 ~ 80이하

SELECT * FROM board WHERE name = '홍길동';  -- name이 홍길동
SELECT * FROM board WHERE name LIKE '홍%';  -- 홍으로 시작하는 단어
SELECT * FROM board WHERE name LIKE '%홍';  -- 홍으로 끝나는 단어
SELECT * FROM board WHERE name LIKE '%홍%';  -- 홍이 들어가있는 단어

문제] hit조회 50~70, 날짜 2022-01-01 ~ 2022-07-30
SELECT * FROM board WHERE hit BETWEEN 50 AND 70;

문제] board테이블 name필드 'lee', 'ck'
SELECT * FROM board WHERE name='lee' OR name='ck';
SELECT * FROM board WHERE name IN ('lee', 'ck');
SELECT * FROM board WHERE name NOT IN ('lee', 'ck');


SELECT empno, ename, job, sal, comm FROM emp;
SELECT empno, ename, job, sal, comm, (sal*comm) as "total" FROM emp;
SELECT empno, ename, job, sal, comm, (sal+ifnull(comm,0)) as "total" FROM emp;

emp테이블, dept테이블, salgrade테이블
문제] emp전체데이터출력, emp테이블 데이터 갯수 count(), emp테이블 최대급여 max(sal)

SELECT * FROM emp;
SELECT count(*) FROM emp;
SELECT count(empno) FROM emp;
SELECT count(comm) FROM emp;
SELECT max(sal) as "급여최대값" FROM emp;
SELECT min(sal) as "급여최소값" FROM emp;
SELECT format(max(sal),0) as "급여최대값" FROM emp;

count(), max(), min(), sum(sal), avg(sal)  ==>  결과값이 하나만 나온다
SELECT sum(sal) FROM emp;
SELECT avg(sal) FROM emp;


SELECT empno, ename FROM emp GROUP BY job; 직책별, 부서별, 지역별, 월별, 성별
-- 그룹화를 하면 집계함수 사용
SELECT job, sum(sal) FROM emp GROUP BY job;  --  직업별 급여합계
SELECT deptno, sum(sal) FROM emp GROUP BY deptno;
DESC emp;

SELECT job, format(sum(sal),0) FROM emp GROUP BY job;

총합계가 7000이상인 사람 데이터보기
SELECT job, format(sum(sal),0) FROM emp GROUP BY job HAVING sum(sal) >= 7000;
SELECT job, format(sum(sal),0) as total FROM emp GROUP BY job HAVING total >= 7000; 
--  total명을 만들어 결과도출

SELECT job, sum(sal) as total FROM emp GROUP BY job ORDER BY total DESC;
SELECT job, sum(sal) FROM emp GROUP BY job ORDER BY 2 DESC;  
--  2번째 값을 기준으로 order by 정렬

job데이터에서 teacher제외하고 그룹화
SELECT job, sum(sal) FROM emp WHERE job != 'teacher' GROUP BY job ORDER BY 2 DESC;
SELECT job, sum(sal) FROM emp WHERE job NOT IN ('teacher') GROUP BY job ORDER BY 2 DESC;

직책별 총합계가 5000미만인 사람만 조회
SELECT job, sum(sal) FROM emp GROUP BY job HAVING sum(sal) < 5000 ORDER BY 2 DESC;
SELECT job, sum(sal) as total FROM emp GROUP BY job HAVING total < 5000 ORDER BY 2 DESC;

문제] job필드가 MANAGER 조회, deptno필드가 20 조회
SELECT * FROM emp WHERE job = 'manager';  --  소문자 manager
SELECT * FROM emp WHERE job = 'MANAGER';  --  대문자 MANAGER
SELECT * FROM emp WHERE job = upper('manager');  --  소문자를 대문자로 변경
SELECT * FROM emp WHERE deptno = 20;

 

※ 내장함수 실습 (교재 248페이지 참조)

SELECT least(값1, 값2, 값3, ...); -- 제일작은값 출력
SELECT greatest(값1, 값2, 값3, ...);  --  제일큰값 출력
SELECT truncate(소수점값, 소수점몇번째자리) ret;  --  소수점단위 자를때
ㄴex) SELECT truncate(1234.1212, 2) ret;  ==>  1234.12
SELECT mod(7, 4), mod(9, 10);  --  나눈 나머지값 출력
SELECT round(값, 소수점몇번째자리)  --  소수점자리에서 반올림
SELECT floor(값)  --  소수점자리 버림
SELECT ceil(값)  --  소수점자리 올림
SELECT format(값)  --  숫자단위 컴마
SELECT reverse('문자열')  --  문자열이 반대로 출력
SELECT upper('문자열')  --  대문자변환
SELECT lower('문자열')  --  소문자변환
SELECT concat(값1, 값2)  --  문자열값을 합쳐서 출력
SELECT replace(값, 변경전문자, 변경할문자)  --  값내에 문자열을 변경한다
SELECT ascii('값')  --  ascii코드로 변경한다
SELECT if(값1 > 값2 , 참일때 반환값, 거짓일때 반환값);
SELECT ifnull(null, 'X')  --  (값, '출력값') 값이 null일때 'X'를 출력한다
ㄴex) SELECT empno, ename, sal, ifnull(comm, 0) FROM emp;

 

★★집계함수를 썼을때와 안썼을때의 차이점★★

SELECT idx, name, kor, eng, mat, sum(kor+eng+mat) total, avg(kor+eng+mat) avg 
FROM student;
ㄴ집계함수 (sum, avg)를 사용하여 한가지값밖에 안나온다

SELECT idx, name, kor, eng, mat, (kor+eng+mat) total, (kor+eng+mat) avg FROM student;
ㄴ전체 학생의 내용이 전부출력

SELECT idx, name, kor, eng, mat, (kor+eng+mat) as total, (kor+eng+mat) as avg, 
if( floor((kor+eng+mat) / 3)>80, '축합격', '재시험') as "결과" 
FROM student;

SELECT idx, name, kor, eng, mat, (kor+eng+mat) as total, (kor+eng+mat) as avg, 
if( floor((kor+eng+mat) / 3)>80, '축합격', '재시험') as "결과" 
FROM student ORDER BY 7 DESC;

 

※ 날짜에 대한 내용

SELECT now(), sysdate(), current_timestamp();  --  시간을 나타내는 함수
SELECT now(), date_add(now(), interval 3 day);  --  +/-로 미래/과거 날짜를 설정할 수 있다
SELECT year(now()), year('2022-09-15');  --  연도만 추출
SELECT month(now()), month('2022-07-15');  --  월만 추출
SELECT day(now()), day('2022-07-15');  --  일만 추출
SELECT date_format(now(), '%Y');  --  2022
SELECT date_format(now(), '%y');  --  22
SELECT str_to_date('15 07 2022', '%d %m %Y');

SELECT user(), database();