SQL
Structerd Query Lanuage
DB에 있는 정보를 사용할 수 있도록 지원하는 언어.
대소문자를 구분하지 않음.
DDL(데이터 정의), DML(데이터 조작), DCL(데이터 제어), TCL(트랙잭션 제어) 등이 있다.
DDL
데이터베이스 생성
create database DB명
default character set utf8mb3 collate utf8mb3_general_ci;
// utf8mb3 - 한글까지 지원, utf8mb4 - 이모지 문자까지 지원데이터베이스 변경
alter database DB명
dafault character set 값 collate 값;DB 삭제
drop database DB명;DB 사용
user DB명;Data Type
문자열 데이터
| 유형 | 정의 |
|---|---|
| char(M) | 고정 길이를 갖는 문자열, 1~255, char(20)에 10자만 저장해도 20자를 차지 |
| varchar(M) | 가변 길이를 갖는 문자열, 1~65535, 10자를 저장하면 10자만큼만 차지 |
char vs varchar
- char 고정된 길이기 때문에 연산속도가 더 빠름
- varchar는 가변길이기 때문에 메모리적으로 더 절약할 수 있음
테이블 생성
create table 테이블명(
column1 type [optional attributes]
);| attribute | desc |
|---|---|
| not null | null을 허용하지 않음 |
| default 값 | 값이 없을 때 기본값 설정 |
| unsigned | 숫자인 경우 양수로 제한 |
| auto increment | 새 레코드가 추가될 때마다 필드 값을 1씩 증가 |
| primary key | primary key로 설정 |
제약조건
열에 저장될 데이터의 조건을 설정
제약조건에 위배되는 데이터는 저장 불가
| attribute | desc |
|---|---|
| not null | null을 허용하지 않음 |
| unique | 중복된 값을 저장할 수 없음, NULL은 허용 |
| foreign key | 외래키 지정 |
| default | null일 경우 기본 값 지정 |
| primary key | primary key로 설정 |
| check | 값의 범위나 종류 지정 |
테이블 변경
- 칼럼 추가
alter table 테이블명 add column 칼럼명 data-type 제약조건;- 칼럼 변경
alter table 테이블명 modify column 칼럼명 data-type 제약조건;- 칼럼 이름 변경
alter table 테이블명 change column 칼럼명 변경할컬럼명 data-type 제약조건;- 칼럼 삭제
alter table 테이블명 drop column 칼럼명;- 테이블 이름 변경
alter table 테이블명 rename 변경할테이블명;Index
테이블에 대한 동작 속도를 높여주는 자료구조다.
RDBMS에서는 인덱스는 테이블 부분에 대한 하나의 사본이다.
인덱스는 고유 제약 조건을 실현하기 위해서도 사용된다.
고유 인덱스는 중복을 금지하므로, 인덱스의 대상인 테이블에서 고유성이 보장된다.
칼럼의 값과 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어두어 빠르게 조회할 수 있도록 한다.
문제점
- 필요 없는 index를 만들면 용량만 늘어나고, 검색 속도도 느려짐
- 중복되는 인덱스가 많으면 느려짐
- 추가적인 공간이 필요
- 해당 데이터의 변경 작업이 자주 일어나면 오히려 성능 저하가 일어남.
클러스터형 인덱스(clutstered index)
- 특정 나열된 데이터들을 일정 기준으로 정렬하는 인덱스
- 클러스터형 인덱스 생성 시 데이터 페이지 전체가 다시 정렬된다. -> 대용량 데이터에선 심각한 부하가 발생될 수 있음.
- 테이블당 하나만 생성 가능하다.
- 보조 인덱스보다 검색 속도는 빠르나, 입력/수정/삭제는 느림
- MySQL 경우, primary key가 있다면, 없다면 unique하면서 not null한 컬럼을, 그것도 없다면 임의로 보이지 않는 컬럼을 만들어 클러스터형 인덱스로 지정한다.
보조 인덱스(secondary index)
- 후보키에만 부여 가능한 index
후보키: 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합
- 보조 인덱스 생성시 데이터 페이지는 건들지 않고, 별도의 페이지에 인덱스를 구성함.
- 데이터가 위치하는 주소값(RID)
- 클러스터형 인덱스보다 검색 속도는 느리지만, 입력/수정/삭제 시 성능 부하가 적음 => 정렬을 하지 않기 때문
- 보조 인덱스는 테이블당 여러 개 생성 가능 => 너무 많으면 성능 저하
인덱스 생성 전략
- 열 단위에 생성
- where 절에 사용되는 열에 생성
- 자주 사용해야 가치가 있음
- 데이터 중복도가 낮은 열에 생성
- 외래키는 자동으로 외래키 인덱스가 생성됨
- 조인에 자주 사용되는 열에 사용하는 것이 좋음
- 데이터 변경 작업이 얼마나 자주 일어나는지 고려해야함
- 사용하지 않는 인덱스는 제거해야함
인덱스 자동 생성
- primary key를 설정하면 클러스터형 인덱스가 자동 생성됨.
- unique한 열도 인덱스가 생성됨
index 생성
- crate index 문으로는 보조 인덱스만 생성 가능.
- 클러스터형 인덱스를 만들려면 alter table을 사용해야함.
- index type은 생략 가능하며, 생략 시 B-tree 형식 사용
create index index명 on 테이블(칼럼명);
create unique index index명 on 테이블(칼럼명);
index 삭제
drop index index명 on 테이블명; // 인덱스 삭제
alter table 테이블명 drop primary key; // 클러스터형 인덱스 삭제DML
삽입
insert into 테이블명 (열1, 열2 ,열3) values(값1,값2,값3) + (값1,값2,값3)(벌크);
생략가능한 field
1. null 2. default 3. auto increment업데이트
update 테이블 set 열1=변경값 , 열2=변경값 where 조건;삭제
delete from 테이블 where 조건;조회
select 조회할 열, 조회할 열 from 테이블 where 조건;select clause
| clause | desc |
|---|---|
| * | 모든 열을 선택 |
| ALL | 선택된 모든 행을 반환 (default) |
| DISTINCT | 중복 행 제거 |
| column | 지정된 열 선택 |
| expression | 값, 연산자 및 sql 함수의 조합 |
| alias | 별칭 |
order by
조건으로 행을 정렬 함.
select * from employees order by id DESC
// id를 기준으로 내림차순
// ASC: 오름차순(default), DESC : 내림차순group by
- select 문에서 group by 절을 사용하는 경우 db는 쿼리 된 테이블의 행을 그룹으로 묶음.
- db는 선택 목록의 집계 함수를 각 행 그룹에 적용하고 각 그룹에 대해 단일 결과 행을 반환.
- group by 절을 생략하면 db는 선택 목록의 집계 함수를 쿼리 된 테이블의 모든 행에 적용.
select id , sum(salary) from employees;
//에러 발생, id는 다중행, sum은 단일행
select id , sum(salary) from employees group by id;
// group by를 통해 id를 묶음.having
- group by한 결과에 조건을 추가 할 경우 having 절을 사용
- where 절이 group by 절보다 먼저 실행되기 때문에 aggregate 조건은 having에 작성
select id, avg(salaray) from employees
where avg(salary) > 7000
group by id; // 에러 발생
select id, avg(salaray) from employees
group by id
having avg(salary) > 7000;Alias(별칭)
select member_id as id, from member_table as table where table.member_id > 1000;
// member_id란 열을 id라는 별칭을 부여해서 조회 as생략 가능
// 중간에 공백이 들어가는 경우 ""로 묶어줘야함
// 열 별칭은 where 절에서 사용 불가능(order by나 group by에선 가능), 테이블 별칭은 가능.연산자
AND, OR, NOT: 논리연산
(NOT) IN
배열 안에 존재하는 값이 있는지
select id, name from employees where id in (50,60,70);BETWEEN
범위 조건
select id, name from employees where id between 1 and 30;IS NULL, NOT NULL : null 비교
select id from employees where email is null;
select id from employees where email is not null;
select id from employees where email = null; // 불가능LIKE
해당 문자열이 들어갔는지
select id from employees where name like '%x%';
// 이름에 x가 들어간 사람 조회
// % : 와일드 카드, _ : 한 글자SQL 실행순서
SQL 내장함수
숫자 관련 함수
| function | desc |
|---|---|
| ABS(숫자) | 절대값 |
| CEILING(숫자) | 올림 |
| FLOOR(숫자) | 내림 |
| ROUND(숫자, 자릿수) | 반올림 |
| TRUNCATE(숫자, 자릿수) | 자릿수까지 버림 |
| POW(숫자,N) or POWER(숫자 ,N) | 숫자의 N승 |
| MOD(숫자, div) | 숫자를 div로 나눈 나머지 |
| GREATEST(숫자1, 숫자2,....) | 가장 큰 수 반환 |
| LEAST(숫자1, 숫자2 ....) | 가장 작은 수 반환 |
문자 관련 함수
| function | desc |
|---|---|
| ASCII(문자) | 문자의 아스키 코드 값 리턴 |
| INSERT('문자열1','문자열2'..) | 문자열들을 결합 |
| REPLACE('문자열', '기존 문자열', '바뀔 문자열') | 문자열 중 기존 문자열을 바뀔 문자열로 바꿈. |
| INSTR('문자열', '찾는 문자열') | 찾는 문자열의 위치 반환 |
| SUBSTRING(SUBSTR)('문자열',시작위치, 개수) or MID('문자열',시작위치,개수) | 문자열 중 시작위치부터 개수만큼 리턴 |
| LTRIM('문자열') | 왼쪽 공백 제거 |
| RTRIM('문자열') | 오른쪽 공백 제거 |
| TRIM('문자열') | 양쪽 공백 제거 |
| LCASE('문자열') or LOWER('문자열') | 소문자로 변경 |
| UCASE('문자열') or UPPER('문자열') | 대문자로 변경 |
| REVERSE('문자열') | 반대로 나열 |
| LENGTH('문자열') | 길이 반환 |
날짜 관련 함수
| function | desc |
|---|---|
| NOW(), CURRENT_TIMESTAMP() | 현재 날짜와 시간 리턴, select 실행 되는 순간 시간 |
| SYSDATE() | 현재 날짜와 시간 리턴, 함수가 호출될 때 시간 |
집계 함수
| function | desc |
|---|---|
| COUNT(필드) | NULL 값이 아닌 레코드 수 리턴 |
| SUM(필드) | 레코드 값의 합계 |
| AVG(필드) | 레코드 값들의 평균 |
| MAX(필드) | 레코드 값 중 가장 큰 값 |
| MIN(필드) | 레코드 값 중 가장 작은 값 |
TCL
트랙잭션 : 데이터베이스의 상태를 변화시키는 일종의 작업 단위
| name | desc |
|---|---|
| start transaction | commit, rollback이 나올 때까지 실행되는 SQL문 |
| commit | 모든 코드를 실행 |
| rollback | start transaction 상태 전으로 되돌림 |
MySQL의 경우 autocommit을 꺼줘야함
start transaction;
insert into table values('a');
insert into table values('b');
insert into table values('c');
rollback; // insert 문들 전부 취소
commit // insert 적용
//세이브포인트
start transaction;
insert into table values('a');
insert into table values('b');
savepoint p1;
insert into table values('c');
rollback to p1; // p1 이전으로 돌아감
View
가상의 테이블
실제 행과 열을 가지고 있지만 데이터를 저장하진 않음.
데이터를 조회 할 때 번거로움을 줄일 수 있음.
Table은 실질적인 데이터가 있지만 View는 데이터가 없고 SQL만 저장한다.
기존 테이블의 data가 업데이트되면 view의 내용도 변함.
장점
- 특정 사용자(권한)에 따라 테이블 전체가 아닌 필요한 필드만 보여주어 보안성을 높일 수 있음.
- 복잡한 쿼리를 단순화해서 사용할 수 있음.
- 쿼리를 재사용할 수 있음.
- 여러 방법의 데이터 조회에 알맞은 다양한 구조의 데이터 분석 기반을 구축할 수 있음. -> 기존 테이블 구조를 변경x
단점
- 삽입,삭제,갱신 작업에 대해 많은 제한 사항을 가짐
- 자신만의 인덱스를 가질 수 없음.
View 종류
- 단순 뷰(Simple View)
- 하나의 테이블로 생성
- 그룹 함수의 사용 불가능
- distinct 사용 불가능
- dml 사용 가능 (산술 표현식, distinct, group by, 폼함되지 않는 칼럼 중 not null이 있는 경우 불가능)
- 복합 뷰(Complex View)
- 여러개의 테이블로 생성
- 그룹 함수의 사용이 가능.
- distinct 사용 가능
- dml 사용 불가능
- 인라인 뷰(Inline View)
- 가장 많이 사용
- from 절 안에 SQL문장이 들어가는 것
생성, 수정, 삭제
select 문에서 선택된 필드를 이용하여 생성.
원본 테이블의 이름과 같은 이름을 사용 할 수 없음.
create view 뷰이름
as
select * from table
where condition;
// 이미 존재하면 replace 없으면 create
create or replace 뷰이름
as
select * from table
where condition;
//수정
alter view 뷰이름
as
select * from table;
//삭제
drop view 뷰이름;
