그외

[SQL] 문법 정리 및 예제

체봄 2024. 2. 13. 13:20

SQL 실행되는 순서

: FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

CREATE 절

사용자 생성

CREATE USER 사용자명 IDENTIFIED BY 비밀번호;

테이블 생성

CREATE TABLE 테이블명(
    컬럼명 타입 제약조건,    # 제약조건(생략 가능): NOT NULL / NULL / UNIQUE(=중복X)
    PRIMARY KEY (컬럼명),   # 생략 가능, NOT NULL & UNIQUE 조건을 포함함
    FOREIGN KEY (컬럼명) REFERENCES 관계테이블명(관계테이블 컬럼명),  # 관계테이블 컬럼은 관계테이블의 PRIMARY KEY여야함
);

ALTER 절

테이블 수정

ALTER TABLE 테이블명 RENAME 새테이블명;

ALTER TABLE 테이블명 CHANGE COLUMN 컬럼명 새컬럼명 새컬럼타입;

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 새컬럼형식;

ALTER TABLE 테이블명 ADD COLUMN 새컬럼명 새컬럼형식;    # PRIMARY KEY 추가도 가능

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;               # PRIMARY KEY 삭제도 가능

DROP 절

테이블 삭제

참고: FOREIGN KEY가 있을 경우 자식 테이블부터 삭제해야함
```
DROP TABLE 테이블명;
```

INSERT 절

테이블에 인스턴스 추가

INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) 
                         VALUES (값1, 값2, ...);

UPDATE 절

인스턴스 수정

UPDATE 테이블명
  SET 컬럼명1=새값1,
      컬럼명2=새값2
  WHERE 조건;   # 생략 가능

DELETE 절

인스턴스 삭제

참고: 자식 테이블이 있는 경우, 부모 테이블에서 참조된 인스턴스들은 삭제되지 않고 에러 발생
```
DELETE FROM 테이블명;
```
```
DELETE FROM 테이블명 WHERE 조건;
```

GRANT/REVOKE 절

사용자 권한 설정

GRANT 권한1, 권한2 PRIVILEGES ON 테이블명 TO 사용자명 IDENTIFIED BY 비밀번호;   # 권한: ALL, SELECT, ...
FLUSH PRIVILEGES;   # GRANT문을 실행하는 명령어로, GRANT문이 여러개 있어도 한번만 작성

사용자 권한 해제

REVOKE 권한1, 권한2 ON 테이블명 TO 사용자명;

SELECT 절

행 가져오기

SELECT 컬럼명 FROM 테이블명

WHERE, GROUP BY, HAVING, ORDER BY 절을 추가할 수 있음
컬럼명에다가 aggregate 함수를 사용할 수 있음: SUM(), AVG(), MIN(), MAX(), COUNT()
컬럼명 앞에 DISTINCT 키워드를 붙이면 해당 컬럼의 값이 중복되지 않게 출력할 수 있음
SELECT 컬럼명 + '고객님' FROM 테이블명 이런식도 가능함

CASE WHEN 구문

사용법 1

SELECT 
  CASE WHEN 조건1 THEN 값1
       WHEN 조건2 THEN 값2
       ELSE 값3    # ELSE는 생략 가능
  END as 새컬럼명
FROM 테이블명

사용법 2

SELECT 
  집계함수(CASE WHEN 조건1 THEN 값1
                WHEN 조건2 THEN 값2
                ELSE 값3
          END) as 새컬럼명
FROM 테이블명

예시

SELECT NAME as '이름',
  CASE WHEN AGE BETWEEN 10 AND 20 THEN '10대'
       WHEN AGE BETWEEN 20 AND 30 THEN '20대'
       ELSE AGE
  END as '나이대'
FROM [MEMBER]

문제: 성별 및 연령대별로 회원의 수를 카운트하되, 가입한 날짜(join_date)가 2018년인 회원과 2019년인 회원 수를 나눠서 카운트하시오. (출력 컬럼: gender, ageband, join_2018, join_2019)
```
SELECT gender, ageband,
  COUNT(CASE WHEN YEAR(join_date)=2018 THEN join_date
        END) as join_2018,
  COUNT(CASE WHEN YEAR(join_date)=2019 THEN join_date
        END) as join_2019,
FROM [MEMBER]
GROUP BY gender, ageband;
```

FROM 절

테이블명 별칭(alias) 설정하기

FROM 테이블명 AS 별칭

FROM 테이블명 별칭

예시

SELECT C.name, C.address
FROM customer C
WHERE C.age > 21;

JOIN

INNER JOIN

SELECT 컬럼명 FROM 테이블명1 INNER JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명;

SELECT 컬럼명 FROM 테이블명1 JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명;

SELECT 컬럼명 FROM 테이블명1, 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명;

LEFT OUTER JOIN (LEFT JOIN)

: 왼쪽 테이블은 다 출력, 오른쪽 테이블은 조건 만족하는 것만 붙여서 출력
```
SELECT 컬럼명 FROM 테이블명1 LEFT OUTER JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명;
```
RIGHT OUTER JOIN (RIGHT JOIN)

: 오른쪽 테이블은 다 출력, 왼쪽 테이블은 조건 만족하는 것만 붙여서 출력
```
SELECT 컬럼명 FROM 테이블명1 RIGHT OUTER JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명;
```

FULL OUTER JOIN (FULL JOIN)

: 왼쪽 테이블과 오른쪽 테이블을 합집합해서 다 출력

SELECT 컬럼명 FROM 테이블명1 FULL OUTER JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명;

WHERE 절

AND
OR
<>: 값이 다른거 찾을때 (ex: WHERE age <> 20)
BETWEEN 값1 AND 값2: 값1 이상 값2 이하
IN: 속하는거 찾을때 (ex: WHERE age IN (20, 30))
LIKE: 패턴 지정해서 찾을때. %는 모든 글자, _는 한 글자를 의미 (ex: WHERE name LIKE '김%', WHERE name LIKE '_수진')
EXISTS: 뒤에 보통 서브쿼리가 옴. 서브쿼리의 결과가 한건이라도 존재하면 조건 만족.
NOT EXISTS

GROUP BY 절

: 집계함수를 적용할 그룹을 나누는 기능

이 경우 SELECT 절에는 GROUP BY의 대상 컬럼 및 aggregation 함수만 올 수 있다

HAVING 절

: GROUP BY 결과 (각 그룹)에 대한 조건을 지정하는 기능

GROUP BY 절이 있을 때만 사용가능
문제: 주소지(addr)별로 남자인 회원의 수를 집계하되, 50명 이상인 경우만 나오도록 하여라.
```
SELECT addr, COUNT(mem_no) as mem_cnt
FROM [MEMBER]
WHERE gender='man'
GROUP BY addr
HAVING COUNT(mem_no) >= 50;
```

ORDER BY 절

ASC, DESC (ex: ORDER BY user_name ASC)

UNION/INTERSECT/EXCEPT

UNION: 합집합 (중복은 존재하지 않음)
INTERSECT: 교집합 (모든 컬럼 값이 동일)
EXCEPT: 차집합 (앞에꺼에서 뒤에꺼를 제거)

예시

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

서브 쿼리 (Sub-query) 예제

문제: [MEMBER] 테이블에서 gender='man'인 회원의 모든 주문 내역을 [ORDER] 테이블에서 조회하여라.
```
SELECT *
FROM [ORDER]
WHERE mem_no IN (SELECT mem_no
  FROM [MEMBER]
  WHERE gender='man');
```

문제: 수학 과목을 수강하는 학생들의 점수를 조회하여라.

SELECT 학생이름, 수학점수
FROM (SELECT 학생.학생이름 AS 학생이름,
             과목.과목점수 AS 수학점수
      FROM 학생, 과목 
      WHERE 학생.학생이름 = 과목.학생이름 AND 과목.과목이름 = '수학');

문제: 수학 과목을 수강하는 학생들의 모든 정보를 조회하여라.

SELECT *
FROM 학생
WHERE 학생.학생이름 IN (SELECT 과목.학생이름 FROM 과목 WHERE 과목.과목이름='수학');

문제: 사원들의 평균 급여보다 더 많은 급여를 받는 사원을 검색하여라.
```
SELECT NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE);
```

문제: 30번 부서에서 급여를 가장 많이 받는 사원보다 전체 부서 사원 중 더 많은 급여를 받는 사원의 이름과 급여를 출력하시오.

답안1

SELECT NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT MAX(SALARY)
                FROM EMPLOYEE
                WHERE DEPT_NO=30);

답안2

SELECT NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY
                    FROM EMPLOYEE
                    WHERE DEPT_NO=30);

문제: MANAGER 직급의 사원들 중 DEPT_HISTORY 테이블에 정보가 있는 사원들의 사원번호, 이름, 부서 번호를 출력하시오.

SELECT E.EMPNO, E.NAME, E.DEPTNO
FROM EMPLOYEE E
WHERE E.JOB='MANAGER'
  AND EXISTS(SELECT 1
             FROM DEPT_HISTORY D 
             WHERE E.EMPNO=D.EMPNO);

추천하는 블로그: https://suy379.tistory.com/121

저작자표시