SHOW DATABASES;
문법 사용CREATE DATABASE TableName;
문법 사용SELECT DATABASE()
명령 이후 USE TableName;
문법 사용DROP DATABASE TableName;
사용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,
);
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),
--...
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;
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;
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;
-- 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과의 비교는 UNKNOWN이기 때문에 아무것도 반환되지 않음
SELECT id FROM employee WHERE birth_date IS NULL;
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
SELECT D.name FROM emplyee AS E, department AS D
WHERE E.id 1 and E.dept_id = D.id;
SELECT D.name FROM employee AS E
JOIN department AS D ON E.dept_id = D.id
WHERE E.id = 1;
-- 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
SELECT * FROM employee E
INNER JOIN department D USING (dept_id, name);
SELECT * FROM employee E
NATURAL LEFT JOIN department D;
-- implicit cross join
FROM table1, table2
-- expilict cross join
FROM table1 CROSS JOIN table2
-- 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;
- 연봉 기준으로 임직원들 정보를 오름차순으로 정렬
SELECT * FROM emplooyee ORDER BY salary;
- 연봉 기준으로 임직원들 정보를 내림차순으로 정렬
SELECT * FROM emplooyee ORDER BY salary DESC;
- 부서 id 기준으로 오름차순 정렬을 한 다음, 같은 부서내에서는 연봉 내림차순으로 정렬
SELECT * FROM emplooyee ORDER BY dept_id ASC, salary DESC;
- 임직원 수 확인
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;
- 각 프로젝트에 참여한 임직원 수, 최대 연봉, 평균 연봉 구하기
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