MYSQL 기초

O0·2022년 6월 17일

MYSQL

목록 보기

1/6

Mysql 기초

MYSQL 환경설정

1. MYSQL 다운로드 -> 전부 NEXT
2. Eclipse Mysql 연동
	1)eclipse-data source explorer -> database connections new -> mysql -> external jar 불러오기 (mysql connector ~~.jar)
    2) 설정 완료 후 sql ScrapBook에서 실행

테이블

ROW, COLUMN으로 구성

	create table table명 (
   	컬럼명 자료형
       seq INT NOT NULL AUTO_INCREMENT,
       mb_id VARCHAR(20),
       md_pw VARCHAR(20)
   ) ENGIN=MYISAM CHARSET=UTF8;
   -- 테이블 정보 조회 (스키마가 MYDB)
   SELECT *
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='mydb';

Join

두 개 이상의 테이블을 연결해서 데이터를 검색하는 방법

보통 두 개 이상의 행(ROW)들의 공통된 기본키, 외래키를 사용해서 Join 한다.

목적 : 현재 테이블에서 상대테이블의 정보(column)를 산출하기 위한 처리

기본키 : 테이블에서 중복되지 않는 column

외래키 : 다른 테이블에서 기본키로 설정되어 있다.

inner join : ****

full outer join :*

cross join :

outer join :

right join:***

left join:***

slef join: ****

Sub Query

쿼리 안의 쿼리

	SELECT 단일 ROW, 단일 COLUMN	
    FROM 다중 ROW, 다중 COLUMN
    WHERE 다중 ROW, 다중 COLUMN

기타

-- ASCI SQL
SELECT E.*, D.*
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- MY SQL
SELECT *
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_ID, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE 1=1
AND E.JOB_ID = J.JOB_ID
AND E.JOB_ID = 'IT_PROG';

SELECT EMPLOYEE_ID, E.DEPARTMENT_ID, DEPARTMENT_NAME, JOB_I
FROM EMPLOYEES E , DEPARTMENTS D, JOBS J
WHERE 1=1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID;

-- CROSS JOIN은 상호 조인이라고도 불리며, 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능을 한다.
SELECT *
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;

-- LEFT OUTER 
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- RIGHT OUTER
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- FULL OUTER :  UNION ALL이 아닌 UNION 기능과 같으므로 중복 데이터는 삭제된다.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
UNION
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 차집합
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE 1=1
AND E.DEPARTMENT_ID IS NULL;

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE 1=1
AND E.DEPARTMENT_ID IS NULL;

-- 양쪽 차집합
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IS NULL
UNION
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE D.DEPARTMENT_ID IS NULL;

-- SELF JOIN 동일한 테이블 조인(내 상사는 누구지 -> 나야)
SELECT EMP.EMPLOYEE_ID, EMP.FIRST_NAME, EMP.MANAGER_ID, MGR.EMPLOYEE_ID
FROM EMPLOYEES EMP, EMPLOYEES MGR
WHERE 1=1
AND EMP.MANAGER_ID = MGR.EMPLOYEE_ID;

-- SUB QUERY
-- SELECT(한 개의 ROW, 한 개의 COLUMN)
SELECT EMPLOYEE_ID, FIRST_NAME,
		(SELECT LAST_NAME
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID = 100)
FROM EMPLOYEES;

-- 서브쿼리 사용 안 하고 그냥 AVG(SALARY)하면 단일행만 출력된다.
SELECT FIRST_NAME, SALARY,  (SELECT AVG(SALARY) FROM EMPLOYEES) -- ,AVG(SALARY)
FROM EMPLOYEES
WHERE 1=1
AND DEPARTMENT_ID = 30;

-- FROM(임시테이블)

SELECT 
    EMPLOYEE_ID, FIRST_NAME, SALARY
FROM
    (SELECT 
        EMPLOYEE_ID, FIRST_NAME, SALARY
    FROM
        EMPLOYEES
    WHERE
        DEPARTMENT_ID = 100) E
WHERE
    1 = 1 AND SALARY > 8000;
    
-- 부서번호 50, 급여가 6000이상인 사원
SELECT 
    EMPNO, ENAME, SAL
FROM
    (SELECT 
        EMPLOYEE_ID AS EMPNO, FIRST_NAME AS ENAME, SALARY AS SAL
    FROM
        EMPLOYEES
    WHERE
        DEPARTMENT_ID = 50) A
WHERE
    1 = 1 AND SAL >= 6000;

-- 업무별로 급여의 합계, 인원수, 사원명, 월급
SELECT JOB_ID, COUNT(*), FIRST_NAME, SALARY
FROM EMPLOYEES
GROUP BY JOB_ID
;

SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.JOB_ID ,A.급여합계, A.인원수 
FROM (SELECT EMPLOYEE_ID, FIRST_NAME,JOB_ID, SUM(SALARY) AS 급여합계, COUNT(*) AS 인원수
			FROM EMPLOYEES
			GROUP BY JOB_ID) A;

-- WHERE
-- 평균급여보다 많이 받는사원
SELECT *
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

-- 부서번호가 90인 사원의 업무명
SELECT 
    JOB_ID, FIRST_NAME, DEPARTMENT_ID
FROM
    EMPLOYEES
WHERE
    JOB_ID IN (SELECT 
            JOB_ID
        FROM
            EMPLOYEES
        WHERE
            1 = 1 AND DEPARTMENT_ID = 90);
            
-- 부서별로 가장 급여를 적게 받는 사원의 급여와 같은 급여를 받는 사원
SELECT DEPARTMENT_ID, EMPLOYEE_ID, SALARY
FROM EMPLOYEES
WHERE 1=1
AND (DEPARTMENT_ID, SALARY) IN
(SELECT DEPARTMENT_ID, MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID ASC;

-- 특수쿼리
-- substr('hello world', 1, 5) --> hello
-- case == switch
select EMPLOYEE_ID, first_name, phone_number,
	case substr(phone_number, 1, 3)
		when '515' then '서울'
        when '590' then '부산'
        when '650' then '광주'
        else '기타' end as 지역
from EMPLOYEES;

-- over() 함수
-- group by를 보강하기 위해서 나온 함수
-- select절 에서만 사용
select department_id, count(*)
from employees;

select department_id, count(*)over(), first_name
from employees
group by department_id;


-- PARTITION BY == SELECT절 안에 GROUP BY
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID, COUNT(*) OVER (PARTITION BY DEPARTMENT_ID) -- department부분으로 묶어서 내보내기
FROM EMPLOYEES;

-- 순위함수
/*
	RANK() : 1 2 3 3 5 6
    DENSE_RANK() : 1 2 3 3 4 5
    ROW_NUMBER() : 1 2 3 4 5 6
    ROWNUM -> @rownum:=@rownum+1
*/

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, 
			RANK()OVER(ORDER BY SALARY DESC) AS "RANK"
            ,DENSE_RANK()OVER(ORDER BY SALARY DESC) AS "DENSE_RANK"
            ,ROW_NUMBER()OVER(ORDER BY SALARY DESC) AS "ROW_NUMBER"
FROM EMPLOYEES;

SELECT @ROWNUM:=@ROWNUM+1, EMPLOYEE_ID,FIRST_NAME
FROM EMPLOYEES
WHERE 1=1
AND (@ROWNUM:=0)=0
AND @ROWNUM<10;

SELECT @ROWNUM:=@ROWNUM+1, EMPLOYEE_ID,FIRST_NAME
FROM EMPLOYEES, (SELECT @ROWNUM:=0) RNUM
WHERE 1=1
AND @ROWNUM<10;

SELECT RNUM, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM
	(SELECT @ROWNUM:=@ROWNUM+1 AS RNUM, EMPLOYEE_ID, FIRST_NAME, SALARY
	FROM EMPLOYEES, (SELECT @ROWNUM:=0) R
	ORDER BY SALARY DESC) R
WHERE 1=1
AND RNUM >= 11 AND RNUM <=20;

다음 포스트

MYSQL 기초

MYSQL

Mysql 기초

MYSQL 기본

0개의 댓글