5️⃣ SQL
01. NoSQL과 SQL
SQL은 데이터를 쿼리, 조작 및 정의하고 액세스 제어를 제공하기 위해 거의 모든 관계형 데이터베이스에서 사용되는 프로그래밍 언어이다. NoSQL은 관계형 데이터베이스가 아닌 데이터베이스 모델을 뜻한다.
NoSQL은 SQL보다 데이터모델이 다양하며 빅데이터 등 비정형화된 데이터에 적합하다는 강점이 있다.
❗️ 추가 공부 : SQL과 NoSQL 더 자세한 비교 by Parkito
02. SQL 종류
SQL은 기능에 따라 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)의 세 가지로 분류된다.
DDL은 데이터베이스 객체(테이블)를 정의, 생성, 수정하는 기능을 수행하며 다음 세 가지의 SQL문이 있다.
create: 테이블을 생성한다.
drop: 테이블을 삭제한다.
alter: 기존에 존재하는 테이블을 다시 정의한다.
DML은 데이터베이스 테이블 내에 있는 내용을 직접 조작하며 검색, 삽입, 수정, 삭제하는 데 사용된다. DML에 포함되는 SQL문은 다음과 같다.
insert: 테이블 내에 데이터를 입력한다.
delete: 테이블 내의 데이터를 삭제한다.
update: 테이블 내의 데이터를 수정한다.
select: 테이블 내의 데이터를 조회한다.
DCL은 데이터의 사용 권한을 관리하는 데 사용하며, 특정 사용자가 테이블에 조회하는 것을 허가 및 금지한다. DCL의 SQL문은 다음과 같다.
grant: 데이터베이스 접근 권한을 부여한다.
revoke: 이미 부여된 데이터베이스 접근 권한을 취소한다.
03. SQL 예제
Memeber, Room, Chat 테이블을 가진 숙소 예약 데이터베이스 예제를 사용했다.
1) select, from, where
select 컬럼명 from 테이블명 where 조건절;
select * from Member;
Member 테이블 전체 조회
select number,title from Room; Room 테이블에서 number,title 컬럼만 조회
select roomNumber from Member where userId = 'nul010';Member 테이블에서 userId가 nul010인 roomNumber 조회
2) subquery
select *
from Room
where roomNumber in (select roomNumber from Member where userId = 'nul010');Member 테이블에서 userId가 nul010인 roomNumber 조회한 후 해당 roomNumber와 동일한 컬럼을 Room 테이블에서 조회
3) join
inner join에 대한 설명 by 뉴렉처
outer join에 대한 설명 by 뉴렉처
inner join은 합치는 테이블 모두에서 데이터가 존재하는 컬럼만을 허용하므로 null 값을 허용하지 않는다.
outer join은 null 값을 허용하며, left outer join은 왼쪽 테이블 값을 기준으로, right outer join은 오른쪽 테이블 값을 기준으로 한다.
select *
from Member
inner join Room on Member.roomNumber = Room.number
where userId = 'nul010';Room 테이블의 number와 Member 테이블의 roomNumber를 같은 대상으로 하여 두 테이블을 합친 뒤 userId가 nul010인 데이터를 Memeber 테이블에서 조회한다.
join이 subquery보다 성능이 좋다!
select문은 한 번 사용할 때마다 for함수를 활용하여 데이터를 불러오는 것과 같은 부하가 오므로 select문을 최소로 사용할 수 있는 join을 사용하는 것이 좋다.
4) as
select roomNumber, imageUrl as roomImageUrl, Room.createdAt from Member
inner join Room on Member.roomNumber = Room.number
where userId = 'nul010';Room 테이블의 number와 Member 테이블의 roomNumber를 같은 대상으로 하여 두 테이블을 합친 뒤 userId가 nul010인 데이터의 roomNumber, imageUrl을 Memeber 테이블에서 조회한다.
imageUrl 컬럼은 roomImageUrl 컬럼으로 이름을 바꿔 표시한다. 조인한 Room 테이블에서 createdAt 컬럼도 조회한다. ( * 실제 테이블값이 바뀌는 것이 아니다!)
5) group by
select max(number) from Chat group by roomNumber;
각 roomNumber 컬럼을 기준으로 그룹화하여 Chat 테이블에서 가장 큰 number 컬럼의 데이터를 조회한다.
6) insert into
insert into 테이블명 (컬럼명) values (값);
insert into Member (roomNumber, userId) values (12, "keepcalm");Member 테이블에서 roomNumber, userId 각각의 컬럼에 대응한 값인 12, keepcalm을 삽입한다.
6) update
update 테이블명 set 컬럼명=값 where 조건절;
update Member set userId="catdog" where roomNumber=12;Memeber 테이블에서 roomNumber가 12인 데이터의 userId를 catdog으로 수정한다.
7) delete
delete from 테이블명 where 조건절;
delete from Member where (userId="coffee") and (roomNumber=15);Member 테이블에서 userId가 coffee이고 roomNumber이 15인 데이터를 삭제한다.
8) distinct
select distinct roomNumber from Member where userId = 'nul010';조회한 값에서 중복된 값들이 나올 경우 이를 제거하고 표시한다.
9) and, or, not
select * from Chat where userId = 'nul010' and number>1000;
select * from Chat where userId = 'nul010' or number>1000;
select * from Chat where userId != 'nul010';and, or, not 연산자를 활용한다.
10) order by, limit
select * from Chat where userId = 'nul010' order by number desc limit 10;Chat 테이블에서 userId가 nul010인 데이터를 number의 내림차순 값으로 상위 10개만 표시한다.
- desc는 내림차순, asc는 오름차순을 의미한다.
11) function
- count() : 데이터의 개수를 리턴한다.
- avg() : 컬럼값의 평균을 리턴한다.
- sum() : 컬럼값의 합을 리턴한다.
- min() : 컬럼값의 최소값을 리턴한다.
- max() : 컬럼값의 최대값을 리턴한다.
- length() : 문자열 데이터의 글자수를 리턴한다.
- mid() : 문자열의 중간부분을 리턴한다.
- upper() : 문자열을 대문자로 리턴한다.
- lower() : 문자열을 소문자로 리턴한다.
- round() : 숫자열 데이터를 반올림해서 리턴한다.
select avg(number) from Chat group by userId;12) create view
create view 임시테이블명 as select 컬럼명 from 테이블명 where 조건절;
create view sampleView as select avg(number) from Chat group by userId;표시된 쿼리 결과값을 sampleView라는 임시테이블로 저장한다. 후에 drop view sampleView 문으로 삭제할 수 있다.
13) %
select * from Chat where userId = 'n%';Chat 테이블에서 n으로 시작하는 userId의 데이터를 조회한다.
select * from Chat where userId = '%n%';Chat 테이블에서 n이 포함된 userId의 데이터를 조회한다.
14) case
select
userId,
case
when type = 'normal'
then '활동 유저'
when type = 'dormant'
then '휴면 유저'
when type = 'deleted'
then '탈퇴 유저'
else '미분류 유저'
end as userType,
userName
from Member;Member 테이블에서 userId, type, userName을 조회한다. type 컬럼의 데이터값에 따라 각각 다르게 값을 표시하며 userType 컬럼으로 이름을 바꿔 표시한다.
15) exists
select * from Member me where exists(select * from Room ro where me.roomNumber = ro.number);조건절에 맞는 데이터가 있다면 표시한다. not exist로 적는다면 조건절에 맞지 않는 데이터를 반환하게 된다.
16) create
create table Users(
Email varchar(30) not null,
constraint Users_pk
primary key (Email),
);User 테이블을 구성, 정의하여 생성한다.
17) alter
이미 생성된 테이블의 컬럼(속성)과 컬럼에 관한 제약을 변경한다.
alter table Member add email varchar(20);Member 테이블에 varchar(20)의 자료형을 가진 email 컬럼(속성)을 추가한다.
alter table Member modify email char(20);Member 테이블의 email 컬럼의 자료형을 char(20)로 수정한다.
alter table Member drop column email;Member 테이블에서 email 컬럼(속성)을 삭제한다.
alter table Member modify email not null;Member 테이블의 email 컬럼을 not null 제약 조건으로 수정한다.
alter table Member add primary key(userId);Member 테이블의 userId 속성에 기본키를 추가한다.
❗️ 추가 공부 : SQL 더 많은 함수에 대해서 by 망나니개발자
6️⃣ Transaction
트랜잭션에 대한 이해는 영상으로 보는 것이 이해가 빠르므로 영상 링크를 첨부한다. > 트랜잭션의 정의와 역할
❗️ 한 줄 정리 : 트랜잭션이란 데이터베이스를 변경하는 도중 오류가 났을 경우를 대비해 데이터베이스의 일관성을 유지할 수 있는 하나의 논리적 기능 혹은 연산의 단위를 의미한다.
7️⃣ 추가: Tuning
❗️ 한 줄 정리 : 튜닝이란 데이터베이스를 효율적으로 관리하기 위해 기존 쿼리를 최적화된 쿼리로 변화시키는 것을 의미한다.
❗️ 추가 공부 : 튜닝의 원리에 대해 알고 싶다면 친절한 SQL 튜닝 by aaron.park
기본적인 튜닝의 몇 가지 규칙에 대해 알고 싶다면 더 빠른 SQL 쿼리를 위한 21가지 데이터베이스 튜닝 규칙 by Sean McCown
