SQL[Database]

SnowCat·2023년 7월 12일
0

CS - Database

목록 보기
3/10
post-thumbnail

SQL이란?

  • SQL (Structured Query Language) RDBMS에서 사용되는 언어
  • DDL, DML, VDL의 기능을 모두 수행 가능함
  • relational data model에서 relation은 table, attribute는 column, tuple은 row, domain은 그대로 domain으로 불림
  • SQL에서는 튜플의 중복이 허용됨에 유의

SQL로 DB 정의하기

  • IT 회사 직원, 부서, 프로젝트 정보를 저장하는 관계형 데이터베이스를 만들자
  • 사용할 RDBMS는 MySQL(InnoDB)다
  • 어떤 DB가 있나 확인하고자 할 때는 SHOW DATABASES; 문법 사용
  • DB 생성시에는 CREATE DATABASE TableName; 문법 사용
  • DB를 선택시에는 SELECT DATABASE() 명령 이후 USE TableName; 문법 사용
  • DB 삭제시에는 DROP DATABASE TableName; 사용
  • MySQL에서는 Database = Schema이지만, 다른 RDBMS에서는 의미가 다를 수 있음에 유의

TABLE 만들기

  • 아래와 같이 스키마를 만들고 싶다
    • DEPARTMENT: id(PK), name, leader_id
    • EMPLOYEE: id(PK), name, birth_date, sex, position, salary, dept_id
    • PROJECT: id(PK), name, leader_id, start_date, end_date
    • WORKS_ON: empl_id, proj_id (둘을 합한 합한 candidate key가 pk)
  • 이를 생성하기 위해서는 CREATE TABLE TableName(column); 문법을 사용하면 된다
CREATE TABLE DEPARTMENT(
	id INT PRIMRY KEY,
    -- pk가 2개 이상이면 밑에 PRIMARY KEY(attribute1, attribute2) 식으로 선언
    name VARCHAR(20) NOT NULL UNIQUE,
    -- unique가 2개 이상이면 밑에 UNIQUE(attribute1, attribute2) 식으로 선언
    leader_id INT
);

CREATE TABLE EMPLOYEE(
	id INT PRIMRY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    sex CHAR(1) CHECK (sex IN ('M', 'F')), 
    -- attribute가 2개 이상이면 밑에다 선언
    position VARCHAR(10),
    salary INT DEFAULT 50000000,
    dept_id INT,
    FOREGGIN KEY (dept_id)
    	references DEPARTMENT(id)
    	ON DELETE SET NULL -- (참조값이 삭제시 null로 변경)
        ON UPDATE CASCADE, -- (참조값이 변경시 foregin key에도 반영)
        -- refeerence_option으로는 RESTRICT(변경금지), NO ACTION(트랜잭션시에만 값 변경, 이외는 변경금지), SET DEFAULT(기본값으로 변경)이 추가로 있음
    CHECK minimum_salary (salary >= 50000000)
    -- constraint 이름 확인시 SHOW CREATE TABLE TEST 명령어 사용
);

CREATE TABLE PROJECT(
	id INT PRIMRY KEY,
    name VARCHAR(20) NOT NULL UNIQUE,
    leader_id INT,
    start_date DATE,
    end_date DATE,
    FOREGGIN KEY (leader_id)
    	references EMPLOYEE(id)
    	ON DELETE SET NULL
        ON UPDATE CASCADE,
    CHECK minimum_salary (start_date <= end_date)
);

CREATE TABLE WORKS_ON(
	empl_id INT,
    proj_id INT,
    PRIMARY KEY (empl_id, proj_id),
    FOREGGIN KEY (empl_id)
    	references EMPLOYEE(id)
    	ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREGGIN KEY (proj_id)
    	references EMPLOYEE(id)
    	ON DELETE CASCADE
        ON UPDATE CASCADE,
);
  • department 테이블에서 leader_id는 테이블 생성 이후에는 employ의 id를 참고해야한다.
  • 이 경우에는 ALTER TABLE Tablename 명령어를 사용해 테이블을 수정할 수 있다.
ALTER TABLE DEPARTMENT ADD FOREGIN KEY(leader_id)
	REFERENCES EMPLOYEE(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL;
  • 테이블 삭제시에는 DROP TABLE TableName;을 사용

테이블에 데이터 추가하기

  • INSER INTO tableName VALUES(values) 옵션을 사용해 테이블에 데이터 추가 가능
INSERT INTO EMPLOYEE
	VALUES(1, 'MESSI', '1987-02-01', 'M', 'DEV_BACK', 1000000000, null);

-- key값이 중복되어 error 발생
INSERT INTO EMPLOYEE
	VALUES(1, 'JANE', '1996-05-05', 'F', 'DSGN', 10000000000, null);

-- 연봉 constraint를 위반해 error 발생
INSERT INTO EMPLOYEE
	VALUES(2, 'JANE', '1996-05-05', 'F', 'DSGN', 4000000000, null);

-- foregin key가 없는 값이기 때문에 error 발생
INSERT INTO EMPLOYEE
	VALUES(2, 'JANE', '1996-05-05', 'F', 'DSGN', 9000000000, 123);

INSERT INTO EMPLOYEE
	VALUES(2, 'JANE', '1996-05-05', 'F', 'DSGN', 9000000000, null);

-- attribute를 테이블 뒤에 명시해 필요한 값만 순서대로 제공 가능
INSERT INTO EMPLOYEE (name, birth_date, sex, position, id)
	VALUES('JENNY', '2000-10-12', 'F', 'DEV_BACK', 3);

-- 여러개의 데이터를 한번의 쿼리에 처리도 가능함
INSERT INTO EMPLOYEE VALUES
	(4, 'BROWN' '1996-03-13', M, 'CEO', 120000000, null),
    (5, 'DINGYO' '1990-11-05', M, 'CTO', 120000000, null),
    --...

테이블의 데이터 수정하기

  • MESSI의 소속팀 정보를 업데이트해주고 싶다. MESSI의 employee ID는 1이며, 개발팀 소속(id 1003)이다.
  • 이 경우 UPDATE tableName SET attribute = value (WHERE condition) 문을 사용해주자.
UPDATE employee SET dept_id = 1003 WHERE id = 1;

-- 개발팀의 연봉을 2배로 인상하고 싶다면 아래와 같이 쿼리를 작성하면 된다.
UPDATE employee
	SET salary = salary * 2
    where dept_id = 1003;
    
-- 2개 이상의 테이블도 사용할 수 있다. id = 2003인 프로젝트에 참여하는 사람들의 월급을 2배로 해주자.
UPDATE employee, works_on
	SET salary = salary * 2
    where id = empl_id, proj_id = 2003;

테이블의 데이터 삭제하기

  • John이 퇴사했다. John의 employee_id = 8이고, 프로젝트 id 2001에 참여하고 있다.
  • 이 경우 DELETE FROM tableName (WHERE condition) 문을 사용하면 된다.
-- WORKS_ON에 casade 옵션을 사용했기 때문에 WORKS_ON은 자동으로 삭제됨
DELETE FROM employee WHERE id = 8;

-- 프로젝트에서만 하차하는 경우
DELETE FROM works_on WHERE impl_id = 2;

-- 한 직원이 참여하는 프로젝트가 2개 이상인 경우 하나만 남기고 싶을때
DELETE FROM works_on WHERE impl_id = 5 and proj_id != 2001;

SQL로 데이터 조회하기

SELECT문

  • 데이터를 조회하고 싶을 때에는 SELECT attribute FROM table (WHERE condition)문 사용
SELECT name, position, FROM employee WHERE id = 9;

-- 2개 이상의 테이블을 조합하는 경우
SELECT employee.id, employee.name, position
	FROM project, employee
    WHERE project.id = 2002 and project.leader_id = employee.id;
    
-- as문을 사용해 테이블, attribute의 별칭설정가능
SELECT E.id AS leader_id, E.name AS leader_name, position
	FROM project AS P, employee AS E
    WHERE P.id = 2002 and P.leader_id = E.id;

-- DISTINCT를 사용하면 중복되는 튜플을 제거할 수 있음
SELECT DISTINCT P.id, P.name
	FROM employee AS E, workn_on AS W, project AS P
    WHERE E.position = 'DSGN' and E.id = empl_id and W.proj.id = P.id;
    
-- LIKE를 사용해 특정 내용이 들어가는 데이터를 조회할 수 있음
SELECT name FROM employee WHERE name LIKE 'N%' or name LIKE'%N'; -- N으로 시작하거나 끝나는 이름

-- 이름에 NG가 들어가는 모든 사람의 이름
SELECT name FROM employee WHERE name LIKE'%NG';

-- J로 시작하는 4자리 이름
SELECT name FROM employee WHERE name LIKE 'J___';

-- 퍼센트나 언더스코어 기호 사용시 '\'기호(escape 문자)를 사용
SELECT name FROM employee WHERE name LIKE '\%%' or name LIKE'%\_';

- 모든 attributes를 알고 싶은 경우
SELECT * FROM employee WHERE id = 9;
  • 조건들을 포함해서 조회를 할 경우 조건과 관련된 attributes에 index가 걸려있어야 함
    그렇지 않을 결우 데이터가 많아질수록 조회 속도가 느려짐

subquery

  • SELECT, INSERT, UPDATE, DELETE에 포함된 query를 subquery라 함
  • 2개 이상의 쿼리를 실행할때 subquery를 사용해 한번에 쿼리를 수행할 수 있음
  • subquery는 괄호 안에 기재되야함
-- id가 14인 직원보다 생일이 빠른 임직원의 id, name, birth_date를 찾고 싶다
SELECT birth_date FROM employee WHERE id = 14; -- 1992-08-04
SELECT id, name, birth_date FROM employee
	WHERE birth_data < 1992-08-14;
    
-- 이를 subquery를 사용해 하나로 합칠 수 있다
SELECT birth_date FROM employee
	WHERE birth_date < (
    		SELECT birth_date FROM employee WHERE id = 14
    	);

-- ID가 1인 임직원과 같은 부서, 같은 성별인 임직원의 이름과 직군을 찾기
SELECT FROM employee
	WHERE (dept_id, sex) = (
			SELECT dept_id, sex FROM employee WHERE id 1;
    	);

-- ID가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 ID 찾기
SELECT DISTINCT empl_id FROM works_on
	WHERE empl_id != 5 AND proj_id IN (
    		SELECT proj_id FROM works_on WHERE empl_id = 5;
    	);

-- ID가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 ID와 이름 찾기
SELECT id, name FROM employee, (
	SELECT DISTINCT empl_id FROM works_on
    WHERE empl_id != 5 AND proj_id IN (
    		SELECT proj_id FROM works_on WHERE empl_id = 5;
    	)
    ) AS DSTNCT_E
WHERE id = DSTNCT_E.empl_id;

-- subquery 조건에 맞는 튜플이 하나라도 존재하는지 확인할때는 IN절 외에 EXISTS절도 사용 가능
SELECT P.id, P.name FROM project P
WHERE EXISTS (
	SELECT * FROM works_on W
    WHERE W.proj_id = P.id AND W.empl_id IN (7, 12)
    );
    
-- 2000년대생이 없는 부서의 ID와 이름 찾기
SELECT D.id, D.name FROM department AS D
WHERE D.id NOT IN (
    	SELECT dept_id FROM employee E
        WHERE E.birth_date >= '2000-01-01'
        );
        
-- 리더보다 높은 연봉을 받는 부서원을 가진 리더의 id와 이름과 연봉 찾기
SELECT E.id, E.name, E.salary FROM department D, employee E
-- ANY와 SOME은 같은 역할을 함 (하나라도 조건에 맞으면 TRUE 반환)
WHERE D.leader_id = E.id, AND E.salary < ANY (
	SELECT salary FROM employee
    WHERE id != D.leader_id AND dept_id = e.dept_id
	);
    
-- 리더보다 높은 연봉을 받는 부서원을 가진 리더의 id와 이름과 연봉, 해당 부서의 최고 연봉 찾기
SELECT E.id, E.name, E.salary FROM department D, employee E, (
	SELECT max(salary) FROM employee WHERE dept_id = E.dept_id
) AS dept_max_salary
WHERE D.leader_id = E.id, AND E.salary < ANY (
	SELECT salary FROM employee
    WHERE id != D.leader_id AND dept_id = e.dept_id
	);
    
-- ID가 13인 임직원과 한번도 같은 프로젝트에 참여하지 못한 임직원들의 ID, 이름, 직군 찾기
SELECT DISTINCT E.id, E.name, E.position FROM employee E, works_on W
-- ALL -> 모든 subquery 연산이 일치하면 TRUE 반환
WHERE E.id = W.empl_id AND W.proj_id != ALL(
	SELECT proj_id FROM works_on WHERE empl_id = 13
);

NULL

  • SQL에서 null은 unknown, unavailable, not applicable의 의미를 가짐
  • NULL 데이터를 조회하기 위해서는 비교 연산자 대신 IS를 사용해야 함
-- 등호 사용시 NULL과의 비교는 UNKNOWN이기 때문에 아무것도 반환되지 않음
SELECT id FROM employee WHERE birth_date IS NULL;
  • NULL에 비교연산을 시도할시 다른 조건으로 인해 TRUE, FALSE가 확정되지 않을 경우 UNKNOWN을 반환하며, 이는 TRUE일수도 있고, FALSE일수도 있다는 의미를 가짐
  • UNKNOWN이 포함된 값에 AND, OR, NOT 연산을 사용시 마찬가지로 UNKNOWN을 반환함
  • where절의 condition의 결과는 true인 경우에만 튜플이 반환됨에 주의
3 not in (1, 2 ,4) -- TRUE
3 not in (1, 2 ,3) -- FALSE
3 not in (1, 3, NULL) -- FALSE
3 not in (1, 2 ,NULL) -- UNKNOWN

JOIN

  • SQL에서 JOIN은 두개 이상의 테이블을 한번에 조회하는 것을 의미함
  • from절에는 테이블만 나열하고 where절에 join condition을 명시하는 방식을 implicit join이라 부름
    구식의 join 구문이며, where절에 selection 조건과 join 조건이 같이 있어 가독성이 떨어짐
    또한 복잡한 join 쿼리를 작성할 시 실수의 가능성이 높아짐
SELECT D.name FROM emplyee AS E, department AS D
WHERE E.id 1 and E.dept_id = D.id;
  • JOIN 구문을 명시해 join condition을 분리하는 방법을 expilict join이라 부름
SELECT D.name FROM employee AS E
JOIN department AS D ON E.dept_id = D.id
WHERE E.id = 1;
  • JOIN은 INNER JOIN과 OUTER JOIN으로 나뉨
    • INNER JOIN은 조건에 맞는 튜플들만을 출력하며 방식을 명시하지 않은 경우 INNER JOIN이 기본으로 됨
    • OUTER JOIN은 조건에 해당되지 않는 튜플들까지 result table에 포함하는 JOIN임을 의미함
    • OUTER JOIN에는 LEFT JOIN, RIGHT JOIN, FULL JOIN이 있으며, 각각 왼쪽, 오른쪽, 양쪽의 데이터를 모두 포함하도록 table을 출력해줌
-- INNER JOIN
SELECT D.name FROM employee E JOIN department D ON E.dept_id = D.id
SELECT D.name FROM employee E INNER JOIN department D ON E.dept_id = D.id

-- OUTER JOIN
SELECT D.name FROM employee E LEFT JOIN department D ON E.dept_id = D.id
SELECT D.name FROM employee E LEFT OUTER JOIN department D ON E.dept_id = D.id
SELECT D.name FROM employee E RIGHT JOIN department D ON E.dept_id = D.id
SELECT D.name FROM employee E RIGHT OUTER JOIN department D ON E.dept_id = D.id
-- MYSQL에서 지원하지 않음
SELECT D.name FROM employee E FULL JOIN department D ON E.dept_id = D.id
SELECT D.name FROM employee E FULL OUTER JOIN department D ON E.dept_id = D.id
  • join시에 등호를 사용하는 join을 equi join이라 부름
    사람에 따라 inner join의 경우에만 equi join이라 부르는 경우도 있음에 유의
  • 테이블을 join할 때 이름이 같은 attribute가 있으면 USING 키워드를 사용해 중복을 제거할 수 있음
    USING을 사용할 경우 해당 attribute가 가장 먼저 출력됨
SELECT * FROM employee E
INNER JOIN department D USING (dept_id, name);
  • 두 테이블에서 같은 이름을 가지는 attribute pair에 대해 equi join을 수행하는 경우는 natural join이라 부름
  • 이 경우 join condition을 따로 명시하지 않음
SELECT * FROM employee E
NATURAL LEFT JOIN department D;
  • 두 table의 튜플 쌍으로 만들 수 있는 모든 조합을 result table로 반환하는 경우는 cross join이라 함
  • natural join과 마찬가지로 join condition을 명시하지 않음
  • MYSQL에서는 SQL 표준과 다르게 동작함에 유의
    MYSQL에서는 cross join, inner join, join 구문은 동일한 구문으로 취급되며, 해당 구문에 ON 또는 USING 조건문이 있으면 inner join, 없으면 cross join으로 동작됨
-- implicit cross join
FROM table1, table2

-- expilict cross join
FROM table1 CROSS JOIN table2
  • 같은 테이블을 join할 수 있으며, 이 경우에는 self join이라 부름
  • 이제 여러 예시들을 살펴보자
-- id = 1003인 부서사람들의 임직원 중 리더를 제외한 부서원의 ID, 이름, 연봉 확인
SELECT E.id, E.name, E.salary FROM employee E
JOIN department D ON E.dept_id = D.id
WHERE E.dept_id = 1003 and E.id != D.leader_id

-- id = 2001인 프로젝트에 참여한 임직원들의 이름, 직군, 소속부서 이름 확인
SELECT E.name AS empl_name,
	   E.position AS empl_position,
       D.name AS dept_name
FROM works_on W
JOIN employee E ON W.empl_id = E.id
     LEFT JOIN department D ON E.dept_id = D.id
WHERE W.proj_id = 2001;

ORDER BY

  • 조회 결과를 특정 attribute 기준으로 정렬하여 가져오고 싶을 때 사용
  • 오름차순 정렬은 ASC, 내림차순 정렬은 DESC로 표현함
  • 특별히 정렬 방식을 명시하지 않을 경우 기본적으로 오른차순으로 정렬됨
- 연봉 기준으로 임직원들 정보를 오름차순으로 정렬
SELECT * FROM emplooyee ORDER BY salary;

- 연봉 기준으로 임직원들 정보를 내림차순으로 정렬
SELECT * FROM emplooyee ORDER BY salary DESC;

- 부서 id 기준으로 오름차순 정렬을 한 다음, 같은 부서내에서는 연봉 내림차순으로 정렬
SELECT * FROM emplooyee ORDER BY dept_id ASC, salary DESC;

aggregate function

  • 여러 튜플들의 정보를 요약해 하나의 값으로 추출하는 함수
  • 대표적으로 COUNT, SUM, MAX, MIN, AVG 함수가 있으며, NULL 값을 제외해 요약값을 추출함
- 임직원 수 확인
SELECT COUNT(*) FROM employee;
SELECT COUNT(position) FROM employee; -- 중복을 포함하기 때문에 다음과 같이 써도 같은 결과를 출력함
SELECT COUNT(dept_id) FROM employee; -- NULL값은 제외됨으로 값이 달라질 수 있음에 유의

- 프로젝트 id가 2002인 프로젝트에 참여한 임직원 수, 최대 연봉, 평균 연봉 구하기
SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE W.proj_id = 2002;

GROUP BY

  • 관심있는 attribute를 기준으로 그룹을 나눠 그룹별로 aggregate function을 적용하고 싶을 때 사용
  • 그룹을 나누는 기준이 되는 attribute를 grouping attribute라 부르며, grouping attribute에 NULL값이 있을 경우 NULL값을 가지는 tuple끼리 묶임
- 각 프로젝트에 참여한 임직원 수, 최대 연봉, 평균 연봉 구하기
SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
GROUP BY W.proj_id;

-- 출력하고자 하는 group을 필터링하고 싶을때에는 HAVING을 사용
SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
GROUP BY W.proj_id
HAVING COUNT(*) >= 7;

-- 각 부서별 인원수를 인원수가 많은 순서대로 정렬해 출력
SELECT dept_id, COUNT(*) AS empl_count FROM employee
GROUP BY dept_id
ORDER BY empl_count DESC;

-- 각 부서별 남녀 인원수를 인원수가 많은 순서대로 정렬해 출력
SELECT dept_id, sex, COUNT(*) AS empl_count FROM employee
GROUP BY dept_id, sex
ORDER BY empl_count DESC;

-- 회사 전체 평균 연봉보다 평균 연봉이 적은 부서들의 평균 연봉 출력
SELECT dept_id, AVG(salary) FROM employee
GROUP BY dept_id
HAVIND AVG(salary) < (
	SELECT AVG(salary) FROM employee
);

-- 각 프로젝트 별로 프로젝트에 참여한 90년대생들의 수, 평균 연봉 출력
SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id
ORDER BY W.proj_id;

-- 앞선 예시에서 프로젝트 참여 인원이 7명 이상인 경우만 필터링
SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31' 
	AND W.proj_id IN (
    	SELECT proj_id FROM works_on
        GROUP BY proj_id HAVING COUNT(*) >= 7
    )
GROUP BY W.proj_id
ORDER BY W.proj_id;

출처:
https://www.youtube.com/watch?v=c8WNbcxkRhY&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=3
https://www.youtube.com/watch?v=mgnd5JWeCK4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=4
https://www.youtube.com/watch?v=mgnd5JWeCK4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=5
https://www.youtube.com/watch?v=mgnd5JWeCK4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=6
https://www.youtube.com/watch?v=mgnd5JWeCK4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=7
https://www.youtube.com/watch?v=mgnd5JWeCK4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=8
https://www.youtube.com/watch?v=mgnd5JWeCK4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=9

profile
냐아아아아아아아아앙

0개의 댓글