DML(Data Manipulation Language) - 데이터베이스 테이블에서 새로운 행을 입력하고 기존의 행을 변경하고 제거한다.
- 데이터 조작기능
- 테이블의 레코드를 CRUD(Create, Retrieve, Update, Delete)
- INSERT : 데이터베이스 객체에 데이터를 입력
- INSERT INTO 테이블명 (칼럼이름1, 칼럼이름2, 칼럼이름3, .....)
VALUES(칼럼 값1, 칼럼 값2, 칼럼 값3, .....) 형식으로 데이터를 입력한다 - 생략이 가능한 필드
- NULL이 허용된 칼럼
- DEFAULT가 설정된 칼럼
- AUTO INCREMENT가 설정된 칼럼
- INSERT INTO 테이블명 (칼럼이름1, 칼럼이름2, 칼럼이름3, .....)
- SELECT : 데이터베이스 객체에서 데이터를 조회
- * : FROM 절에 나열된 테이블에서 모든 열을 선택
- ALL : 선택된 모든 행을 반환. ALL이 default
- DISTINCT : 선택된 모든 행 중에서 중복 행 제거
- COLUMN : FROM 절에 나열된 테이블에서 지정된 열을 선택
- EXPRESSION : 표현식은 값으로 인식되는 하나 이상의 값, 연산자 및 SQL 함수의 조합을 뜻함
- ALIAS : 별칭(별명)
- UPDATE : 데이터베이스 객체에서 데이터를 수정
- UPDATE 테이블명 SET 칼럼이름1 = 칼럼 값1, 칼럼이름2 = 칼럼 값2, .....
WHERE 조건 형식으로 조건에 만족하는 레코드의 값을 변경한다 - WHERE절을 생략하면 모든 데이터가 변경된다
- UPDATE 테이블명 SET 칼럼이름1 = 칼럼 값1, 칼럼이름2 = 칼럼 값2, .....
- DELETE : 데이터베이스 객체의 데이터를 삭제
- DELETE FROM 테이블명
WHERE 조건 형식으로 조건을 만족하는 레코드의 값을 삭제한다 - WHERE절을 생략하면 모든 데이터가 삭제된다
- DELETE FROM 테이블명
쿼리문 구성
SELECT 필드 - 5번
FROM 테이블 - 1번
WHERE 조건 - 2번
GROUP BY 필드 - 3번
HAVING 집계함수 - 4번
ORDER BY 정렬 (기본 ASC) - 6번GROUP BY 사용 시 WHERE절이 GROUP BY보다 먼저 실행되므로 aggregate 조건은 HAVING에서 사용해야한다.
SELECT 문 안에서 필드값 줄 때 유용하게 사용할 수 있는
CASE WHEN THEN ELSE END
SELECT employee_id, first_name, salary,
CASE WHEN salary > 15000 THEN '고액연봉'
WHEN salary > 8000 THEN '평균연봉'
ELSE '저액연봉'
END "연봉등급"
FROM employees;
WHERE 절에서 사용하는 AND, OR, NOT, IN, BETWEEN, IS NULL, IS NOT NULL, LIKE
- AND : 두 개 이상의 조건이 모두 성립할 때 사용
- OR : 두 개 이상의 조건중 하나 이상이 성립할 때 사용
- NOT : ~이 아닐 때 사용
- IN : IN(조건1, 조건2, 조건3, .....)형식으로 사용 내부 조건들을 or로 검색 not과 결합도 가능하다
- BETWEEN : BETWENN A AND B의 형식으로 사용 A이상 B이하의 값을 검색할 때 유용
- IS NULL, IS NOT NULL : NULL 이거나 NULL이 아닌것을 조건으로 걸 때 사용 =을 사용하는 것 이 아닌 IS NULL 과 IS NOT NULL을 사용해야 한다.
- LIKE : 특정 이름등을 검색하려고 할 때 사용
name LIKE "%x%"; - 이름에 x가 들어간 사람 검색
name LIKE "%x__"; - 이름의 끝에서 3번째 자리에 x가 들어간 사람 검색
DDL(Data Definition Language) - 테이블로부터 데이터 구조를 생성, 변경, 제거한다.
- 데이터베이스 객체의 구조를 정의(table, view, index...)
- 테이블 생성, 칼럼 추가, 타입변경, 제약조건 지정, 수정 등...
- CREATE : 데이터베이스 객체를 생성
- create database 데이터베이스명;
- create database 데이터베이스명
default character set 값
collate 값;- character set - 각 문자가 컴퓨터에 저장 될 때 어떠한 '코드'로 저장될지에 대한 규칙의 집합
- collation - 특정 문자 셋에 의해 데이터베이스에 저장된 값들을 비교 검색하거나 정렬등의 작업을 위해 문자들을 서로
'비교' 할 때 사용하는 규칙들의 집합을 의미
- ALTER : 기존에 존재하는 데이터베이스 객체를 수정
- alter database 데이터베이스명
default character set 값 collate 값;
- alter database 데이터베이스명
- DROP : 데이터베이스 객체를 삭제
- drop database 데이터베이스명;
- USE : 데이터베이스를 사용
- use 데이터베이스명
- RENAME
- rename table old_table to new_table;
- rename table old_table1 to new_table1,
old_table2 to new_table2,
old_table3 to new_table3;
- TRUNCATE
- truncate table 테이블명;
DCL(Data Control Language) - 데이터베이스와 그 구조에 대한 접근권한을 제공하거나 제거한다.
- DB, Table의 접근권한이나 CRUD 권한을 정의
- 특정 사용자에게 테이블의 검색권한 부여/금지 등
- GRANT : 데이터베이스 객체에 권한을 부여
- REVOKE : 데이터베이스 객체 권한 취소
- COMMIT : 실행한 쿼리를 최종적으로 적용
- ROLLBACK : 실행한 쿼리를 마지막 COMMIT 전으로 취소시켜 데이터를 복구
TCL (Transaction Control Language) - DCL에서 트랜잭션 관련 명령어만 별도 분리한것
- COMMIT : 실행한 쿼리를 최종적으로 적용
- ROLLBACK : 실행한 쿼리를 마지막 COMMIT 전으로 취소시켜 데이터를 복구
MySQL 트랜잭션 도구
- START TRANSACTION : COMMIT, ROLLBACK이 나올 때까지 실행되는 모든 SQL
- COMMIT : 모든 코드를 실행
- ROLLBACK : START TRANSACTION 실행 전 상태로 되돌림
- SAVEPOINT : ROLLBACK 할 수 있는 시점 저장
SET Operator
SQL JOIN
JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현해 주는, Relation Database 에서 가장 많이 쓰이는 녀석이다.
(INNER) JOIN
- 조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력
- 표준 SQL과는 달리 MySQL에서는 JOIN, INNER JOIN, CROSS JOIN이 모두 같은 의미로 사용된다.
select u.userid, name
from usertbl as u inner join buytbl as b
on u.userid=b.userid
where u.userid="111" -- join을 완료하고 그다음 조건을 따진다.Copy
inner join 함축 구문
단순히 from 절에 콤마 쓰면 inner join 으로 치부된다.
select u.userid, name
from usertbl u, buytbl b
where u.userid=b.userid and u.userid="111"CopyTip
뒤에서 배울 left outer join은 1 : N 관계에서 어느 테이블을 기준으로 left할 것이냐에 따라, 레코드 갯수가 달리지게 된다. 그리고 또한 조인에 부합되지 않는 레코드는 테이블 기준에 따라 남기도 한다.
하지만 inner join 은 어느 테이블에서 기준으로 조인하든 조인 관계에 부합되는 레코드를 모두 가지게 된다. 그리고 조인에 부합되지 않는 레코드는 모두 삭제 된다.
왼쪽 외부 조인 = left outer join : 빈 필드 있어도 그냥 합쳐진다
LEFT / RIGHT OUTER JOIN
- 두 테이블이 합쳐 질때 왼쪽/오른쪽을 기준으로 했느냐에 따라 기준 테이블의 것은 모두 출력되어야 한다고 이해하면 된다.
- OUTER JOIN은 조인하는 테이블의 ON 절의 조건 중 한쪽의 데이터를 모두 가져온다
- OUTER JOIN은 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 이렇게 3가지가 있다.
- LEFT OUTER JOIN을 거의 대부분 사용하여, FULL OUTER JOIN은 성능상 거의 사용하지 않는다.
Left join
LEFT JOIN은 두 테이블이 있을 경우, 첫 번째 테이블을 기준으로 두 번째 테이블을 조합하는 JOIN이다.
-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.
SELECT STUDENT.NAME, PROFESSOR.NAME
FROM STUDENT LEFT OUTER JOIN PROFESSOR -- STUDENT를 기준으로 왼쪽 조인
ON STUDENT.PID = PROFESSOR.ID
WHERE GRADE = 1CopyTip
LEFT JOIN을 여러번 할 때 주의할 점
1) INNER JOIN과는 달리 LEFT JOIN은 조인하는 테이블의 순서가 상당히 중요하다.
어떤 순서로 테이블을 조인하는지에 따라 결과 테이블에 조회되는 행의 개수며 구성 등이 달라질 수 있다.
따라서 JOIN 문을 작성할 때, 만약 LEFT JOIN을 할 거라면 가장 첫 번째의 테이블로 SELECT문에 가장 많은 열을 가져와야 할 테이블을 우선으로 적어준다.
2) 조인을 여러 번 해야하는데 시작을 LEFT JOIN으로 했다면 나머지 조인도 LEFT JOIN을 이어나간다.
즉, LEFT JOIN을 쓰다가 갑자기 INNER JOIN 이나 다른 조인을 사용하지 않는다는 이야기다.
Right join
RIGHT JOIN은 두 테이블이 있을 경우, 두 번째 테이블을 기준으로 첫 번째 테이블을 조합하는 JOIN이다.
-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.
SELECT STUDENT.NAME, PROFESSOR.NAME
FROM STUDENT RIGHT OUTER JOIN PROFESSOR -- PROFESSOR를 기준으로 오른쪽 조인
ON STUDENT.PID = PROFESSOR.ID
WHERE GRADE = 1Copy
3중 조인
만일 원하는 정보가 테이블 3개로 흩어져있을때, 이 세개의 테이블을 모아야 할때 outer join을 연속으로 3번 사용하면 된다.
-- 3개의 테이블을 join하고 한국에대한 정보만 뷰로 생성해라
create view allView as
(
select A.Name, A.CountryCode
from city A
left join country B
on A.countrycode = B.code -- 테이블 2개 조인 완료
left join countrylanguage C
on B.code = C.countrycode -- 테이블 3개 조인 완료
where A.countrycode in ('KOR');
)CopyFULL OUTER JOIN
select *
from topic FULL OUTER JOIN autor
on topic.auther_id = authoer.idCopy대부분 DB는 FULL OUTER JOIN을 지원하지 않는다. 하지만 간접적으로 구현하는 방법이 존재한다
= (같은 구문)
(select * from topic LEFT JOIN autor on topic.auther_id = authoer.id))
UNION
(select * from topic RIGHT JOIN autor on topic.auther_id = authoer.id))Copy* LEFT 조인한 테이블과 RIGHT조인한 테이블을 UNION 합집합 해주면 된다.
* UNION 은 DISTICT자동 포함이라, 따로 중복제거 안해줘도 자동으로 중복제거 해준다
UNION
- UNION은 여러 개의 SELECT 문의 결과를 하나의 테이블이나 결과 집합으로 표현할 때 사용
- 이때 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 같아야 하며, 필드의 순서 또한 같아야 한다.
- 기본 집합 쿼리에는 (DISTINCT) 중복제거가 자동 포함되어있다.
SELECT 필드이름 FROM 테이블이름
UNION
SELECT 필드이름 FROM 테이블이름CopyUNION ALL
- UNION은 DISTINCT 자동 포함이라 중복되는 레코드를 제거한다.
- 따라서 중복되는 레코드까지 모두 출력하고 싶다면, ALL 키워드를 사용하면 된다.
SELECT 필드이름 FROM 테이블이름
UNION ALL
SELECT 필드이름 FROM 테이블이름CopyEXCLUSIVE LEFT JOIN
- 어느 특정 테이블에 있는 레코드만 가져오는 것
EXCLUSIVE JOIN는 만약 테이블 두개를 JOIN한다면 둘중 한가지 테이블에만 있는 데이터를 가져온다.
다른 JOIN들과는 다르게 별도의 EXCLUSIVE JOIN함수가 있는 것은 아니고 기존의 LEFT JOIN과 Where절의 조건을 함께 사용하여 만드는 JOIN 문법이다.
SELECT *
FROM table1 A LEFT JOIN table2 B
ON A.ID_SEQ = B.ID_SEQ
WHERE B.ID_SEQ IS NULL -- 조인한 B 테이블의 값이 null만 출력하라는 말은, 조인이 안된 A 레코드 나머지값만 출력하라는 말Copy자체 조인(SELF JOIN)
- 말 그대로 테이블 자기자신을 조인 한 것
-- 예) 모든 사원에 대해 사원의 이름과 직속 상사의 이름을 검색 해라.
-- EMPNAME 테이블에 어떤 사원의 MANAGER 번호가 같은 테이블 내에서 어떤 사원의 EMPNO와 같으면 그 사원이 직속 상관
SELECT E.EMPNAME as 사원, M.EMPNAME as 직속상관
FROM EMPLOYEE E, EMPLOYEE M -- inner join
WHERE E.MANAGER = M.EMPNO;Copy
JOIN에서 중복된 레코드 제거하기
Distinct 사용
- mysql에서 지원하는 distinct 문법을 사용하면 된다.
- 매우 쉬운 방법이지만 레코드 수가 많은 경우 성능이 느리다는 단점이 있다.
-- DISTINCT를 추가
SELECT DISTINCT person.id, person.name, job.job_name
FROM person INNER JOIN job
ON person.name = job.person_name;Copy
JOIN 전에 중복을 제거하기
- 앞서 말한 것처럼 SELECT DISTINCT는 간단하지만 성능이 느리다.
- 성능을 위해서는 JOIN 전에 중복을 제거하는 작업을 해주는 것이 좋다. (물론 더 좋은 것은 중복이 없도록 테이블 설계를 잘 하는 것이다)
JOIN 전에 중복 제거는 다음과 같이 조인할 테이블에 서브쿼리의 inline view를 사용해 distinct 하고 조인하면 된다.
select A.name, A.countryCode
from city A
left join ( select distinct name, Code from country ) as B
-- 조인할 테이블에 먼저 distinct로 중복을 제거한 select문을 서브쿼리로 불러와 임시테이블로 만든뒤 조인한다
on A.countrycode = B.CodeCopy언뜻보면 복잡해보일 수 있고 성능이 더 느려보일 수 있는데, 일반적으로 레코드 수가 많은 경우 JOIN 전에 중복을 제거해서 1:1 JOIN으로 바꾸는 것이 훨씬 빠르다.
물론 WHERE 절에 조건이 존재 여부, index 존재 여부, 한쪽 테이블의 크기가 엄청 적은 경우 등등 많은 시나리오가 있으니 성능 비교를 하는 것이 좋다.
MySQL 내장함수
- 숫자 관련 함수
- ABS(숫자) : 절대값
- CEILING(숫자), CEIL(숫자) : 값보다 큰 정수중 가장 작은 수(올림)
- FLOOR(숫자) : 값보다 작은 정수중 가장 큰 수(버림)
- ROUND(숫자, 자릿수) : 숫자를 자릿수까지 반올림
- TRUNCATE(숫자, 자릿수) : 숫자를 자릿수까지 버림
- POW(X, Y), POWER(X, Y) : X의 Y승
- MOD(분자, 분모) : 분자를 분모로 나눈 나머지
- GREATEST(숫자1, 숫자2, 숫자3, .....) : 주어진 수에서 가장 큰 수를 반환
- LEAST(숫자1, 숫자2, 숫자3, .....) : 주어진 수에서 가장 작은 수를 반환
- 문자 관련 함수
- ASCII(문자) : 문자의 아스키 코드 값 리턴
- CONCAT(문자열1, 문자열2, 문자열3, .....) : 문자열들의 결합
- INSERT(문자열, 시작위치, 길이, 새로운 문자열) : 문자열의 시작위치부터 길이만큼 새로운 문자열로 대치
- REPLACE(문자열, 기존 문자열, 바뀔 문자열) : 문자열 중 기존 문자열을 바뀔 문자열로 변경
- INSTR(문자열, 찾는 문자열) : 문자열 중 찾는 문자열의 위치 값을 리턴
- MID(문자열, 시작위치, 개수) : 문자열 중 시작위치부터 개수만큼 리턴 -> 위치는 1번부터 시작
- SUBSTRING(문자열, 시작위치, 개수) : 문자열 중 시작위치부터 개수만큼 리턴
- LTRIM(문자열) : 문자열 중 왼쪽의 공백을 제거
- RTRIM(문자열) : 문자열 중 오른쪽의 공백을 제거
- TRIM(문자열) : 양쪽 모두의 공백을 제거
- LCASE(문자열), LOWER(문자열) : 모든 문자를 소문자로 변경
- UCASE(문자열), UPPER(문자열) : 모든 문자를 대문자로 변경
- LEFT(문자열, 개수) : 문자열 중 왼쪽에서 개수만큼 추출
- RIGHT(문자열, 개수) : 문자열 중 오른쪽에서 개수만큼 추출
- REVERSE(문자열) : 문자열을 반대로 나열
- LENGTH(문자열) : 문자열의 길이
- 날짜 관련 함수
- NOW() : 현재 날짜와 시간 리턴, select 실행되는 순간의 시간
- SYSDATE() : 현재 날짜와 시간 리턴, 함수가 호출 될 때의 시간
- CURRENT_TIMESTAMP() : 현재 날짜와 시간 리턴, select 실행되는 순간의 시간
- CURDATE(), CURRENT_DATE() : 현재 날짜 리턴
- CURTIME(), CURRENT_TIME() : 현재 시간 리턴
- DATE_ADD(날짜, INTERVAL 기준 값) : 날짜에서 기준 값만큼 더한다
- DATE_SUB(날짜, INTERVAL 기준 값) : 날짜에서 기준 값만큼 뺀다
- YEAR(날짜) : 날짜의 연도 리턴
- MONTH(날짜) : 날짜의 월 리턴
- MONTHNAME(날짜) : 날짜의 월을 영어로 리턴
- DAYNAME(날짜) : 날짜의 요일을 영어로 리턴
- DAYOFMONTH(날짜) : 날짜의 월별 일자 리턴
- DAYOFWEEK(날짜) : 날짜의 주별 일자 리턴, 일요일(1) ~ 토요일(7)
- WEEKDAY(날짜) : 날짜의 주별 일자 리턴, 월요일(0) ~ 일요일(6)
- DAYOFYEAR(날짜) : 일년을 기준으로 한 날짜까지의 일 수
- WEEK(날짜) : 일년 중 몇 번째 주
- FROM_DAYS(날수) : 00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 리턴
- TO_DAYS(날짜) : 00년 00월 00일부터 날짜까지의 일자 수 리턴
- DATE_FORMAT(날짜, 형식) : 날짜를 형식에 맞게 리턴
- 논리 관련 함수
- IF(논리식, 값1, 값2) : 논리식이 참이면 값1이 리턴 아니면 값2 리턴
- IFNULL(값1, 값2) : 값1이 NULL이면 값2로 대치, NULL이 아니면 값1 리턴
- NULLIF(값1, 값2) : 값1 = 값2이 TRUE이면 NULL 그렇지 않으면 값1이 리턴
- 집계 함수
- COUNT(필드명) : NULL 값이 아닌 레코드 수를 리턴
- SUM(필드명) : 필드명에 해당하는 레코드 값의 합계를 리턴
- AVG(필드명) : 각각의 그룹 안에서 필드명에 해당하는 레코드 값의 평균을 리턴
- MAX(필드명) : 필드명에 해당하는 레코드 값 중 최대값을 리턴
- MIN(필드명) : 필드명에 해당하는 레코드 값 중 최소값을 리턴
'CS 지식 > 데이터베이스' 카테고리의 다른 글
| [데이터베이스] 정규화 정리 (0) | 2023.03.30 |
|---|---|
| [데이터베이스] JDBC 정리 (0) | 2023.02.13 |
| [데이터베이스] SQL 함수 및 키워드 정리 (0) | 2023.02.02 |
| [데이터베이스] 앨리어스(Alias) 사용했을 때 ORDER BY 가 정상 동작 하지않을 때 해결 방법 (0) | 2023.02.02 |
| [데이터베이스] DML, DDL, DCL 정리 (0) | 2023.02.02 |
