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) -- UNKNOWNJOIN
- 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
냐아아아아아아아아앙