[SQLD] SQL 기본

당당·2023년 5월 3일
DDLDMLHAVINGJOINORDER BYSQL 기본SQLDTCLgroup bywhere절관계형 데이터베이스함수

SQLD

목록 보기
3/6

📔설명

관계형 데이터베이스, DDL, DML, TCL, WHERE 절, 함수, Group By, Having, Order By, Join에 대해 알아보자!

🤝🏻관계형 데이터베이스

데이터베이스

데이터베이스 : 데이터들을 저장하는 공간

용도와 목적에 맞는 데이터들끼리 모아서 저장

관계형 데이터베이스 정의

RDB(Relational Database) : 관계형 데이터 모델에 기초를 둔 데이터베이스.

RDB에서 설계는 모든 데이터를 2차원 테이블 형태로 표현한 뒤, 각 테이블 간의 관계를 정의하는 것으로 시작

RDBMS : RDB 관리, 감독 시스템
ex) Oracle, MS-SQL, MariaDB, MySQL, PostgreSQL

TABLE

Entity->TABLE

RDB는 모든 데이터를 2차원의 테이블 형태로 표현.
세로 열이 Column(속성), 가로 행이 Row(인스턴스)

테이블은 RDB의 기본 단위이고, 일반적으로 DB는 여러 개의 테이블로 구성.
테이블로 데이터를 저장하는 주된 목적은 데이터 활용목적.
-> 테이블 형태로 조회하고 변경하고 삭제 가능

SQL(Structured Query Language)

SQL : 관계형 DB에서 데이터를 다루기 위해 사용하는 언어

  • 데이터 베이스가 이해할 수 있는 언어
  • 복잡한 SQL은 어떻게 작성하느냐에 따라 성능 차이가 있으므로, SQL을 잘 작성하고 튜닝하는 것은 중요

🔔DDL

DDL 정의

DDL(Data Definition Language) : 데이터를 정의하는 명령어

  • CREATE, ALTER, DROP, RENAME, TRUNCATE
  • 문자형 데이터를 정의시 한글은 한 글자가 3byte, 영어는 1byte
  • char : 고정 길이(나머지 칸은 공백)
    ex)'dang' = 'dang '
  • varchar : 가변 길이
    ex)'dang' != 'dang '
유형데이터 타입
문자char
varchar
clob
숫자number
날짜date



CREATE/CTAS

  • 테이블 생성 명령어
  • default : 데이터 기본값

create table 테이블명(
	컬럼명 데이터 타입 [DEFAULT/NULL|NOT NULL]
    ...
);
--oracle
--테이블 복사
CREATE TABLE 테이블명 AS SELECT * FROM 복사할 테이블명;

--테이블 구조만 복사
CREATE TABLE 테이블명 AS SELECT * FROM 복사할 테이블명 where 1=2;

--데이터만 복사
INSERT INTO 복사할 테이블명 SELECT * FROM 복사할 테이블명;

--ms-sql
--테이블 복사
SELECT * INTO 테이블명 FROM 복사할 테이블명;
  • 기존에 존재하는 테이블 복사해서 생성시 사용
  • 제약조건 완전히 복사X, NOT NULL조건만 복사
  • PRIMARY KEY, CHECK 등은 초기화되므로 다시 정의 필요

1.테이블 생성 시 규칙

  • 테이블명은 고유
  • 한 테이블 내 컬럼명은 고유
  • 컬럼명 뒤에 데이터 유형과 데이터 크기 명시
  • 컬럼에 대한 정의는 ()안에 기술
  • 각 컬럼들은 ,로 구분
  • 테이블명과 컬럼명은 숫자로 시작X, 벤더별로 길이 한계 있음
  • 벤더별 사전 예약어 사용 불가
  • 마지막은 ;로 끝남
  • 객체를 의미할 수 있는 적절한 이름 사용
  • 가능한 단수형
  • A-Z, a-z, 0-9, _, $, # 문자만 허용

2.테이블 생성 팁

  • 테이블은 각각의 정체성을 나타내는 이름
  • 컬럼명정의 시, 다른 테이블과 통일성이 있어야 한다. (데이터 표준화 관점)
    ex)A 테이블에서는 USER_ID, B 테이블에서는 MEMBER_ID (같은 데이터 저장 컬럼)

3.제약 조건(CONSTRAINT) : 데이터의 무결성(데이터의 정확성과 일관성 유지), 데이터에 결손과 부정합이 없음을 보증

  • PRIMARY KEY(기본키) : 테이블에 저장된 각 행에 대한 고유성 보장.
    한 테이블에 하나씩만 정의, NULL 불가, UNIQUE 인덱스 생성, 테이블당 1개씩만 생성 가능하며 생성하지 않을 수도 있다.
  • UNIQUE KEY(고유키) : 각 행에 대한 고유성 보장.
    NULL 허용
  • NOT NULL : NULL 값 허용X
  • CHECK : 컬럼에 저장될 수 있는 값의 범위 제한
    ex)CONSTRAINT CHK_YN CHECK(DEL_YN IN('Y','N'));
  • FOREIGN KEY(외래키) : 하나의 테이블이 다른 테이블을 참조시 FK 정의
    다른 컬럼 참조 시 반드시 참조하는 값은 참조되는 값에 존재해야 함

참조 무결성 옵션

  1. Delete Action( 부서 - 사원 )
    -CASCADE : Parent 삭제 시 Child 값 같이 삭제
    -SET NULL : Parent 삭제 시 해당 Child 컬럼 NULL처리
    -SET DEFAULT : Parent 삭제 시 해당 Child 컬럼 DEFAULT 값변경
    -RESTRICT : Child 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 Parent값 수정 및 삭제 가능
    -NO ACTION : 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가
  2. Insert Action ( 부서 - 사원 )
    -Automatic : Master 테이블에 PK가 없는 경우 Master PK 생성 후 Child 입력
    -Set Null : Master 테이블에 PK X시 Child 외부키를 Null값으로 처리
    -Set Default : Master 테이블에 PK X시 Child 외부키를 지정된 기본값으로 입력
    -Dependent : Master 테이블에 PK가 존재할 때만 Child 입력 허용
    -No Action : 참조 무결성 위반하는 입력 액션을 취하지 않음



ALTER

  • 테이블의 구조 변경
  • 컬럼 추가,컬럼 변경,컬럼 삭제, 제약조건 추가,제약 조건 삭제

1.ADD COLUMN

  • 새로운 컬럼 추가
  • 추가된 컬럼 위치는 맨 끝(별도 위치 지정 불가)
alter table 테이블명 add 컬럼명 데이터 유형;

2.DROP COLUMN

  • 기존 컬럼 삭제
  • 삭제시 복구 불가
alter table 테이블명 drop column 컬럼명;

3.MODIFY COLUMN

  • 기존 컬럼 변경
  • 데이터 유형, DEFAULT값, NOT NULL 제약 조건 변경 가능
  • 컬럼에 저장된 모든 데이터의 크기가 줄이고자 하는 컬럼의 크기보다 작을 경우 가능
  • 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형 변경 가능
  • 크기를 늘리는 것은 데이터와 상관 없이 가능
  • DEFAULT 값 변경시에는 변경 이후 저장되는 데이터에만 적용
  • 현재 NULL값이 저장되어있지 않은 컬럼에만 NOT NULL 제약 조건 추가 가능
--oracle
alter table 테이블명 modify (컬럼명1 데이터 유형 [DEFAULT 값] [NOT NULL], 컬럼명2 데이터 유형...);

--ms-sql
alter table 테이블명 alter column 컬럼명 데이터유형 [DEFAULT 값] [NOT NULL], ...;
  • NOT NULL 지정 X 시 이전 NOT NULL이 NULL 로 될 수 있음
  • MSSQL은 컬럼명 앞 뒤로 () 안함
  • MSSQL은 여러 컬럼 변경 한번에 못함

4.RENAME COLUMN

  • 기존에 있던 컬럼의 이름을 변경
alter table 테이블명 rename column 변경할 컬럼명 to 변경할 이름;

5.RENAME TABLE

  • 기존 테이블 이름 변경
alter table 테이블명 rename to 변경된 테이블명;


--ansi 표준
rename 테이블명 to 변경된 테이블명;

6.ADD CONSTRAINT

  • 제약조건 추가
alter table 테이블명 add constraint 제약조건명 제약조건(컬럼명);

alter table teacher 
add constraint teacher_fk foreign key(subject_id) references subject(subject_id);



DROP

1.DROP TABLE

  • 테이블 삭제
  • 해당 테이블 참조하는 다른 테이블 존재 시 CASCADE 옵션 명시하지 않으면 삭제 안됨
drop table 테이블명 [CASCADE CONSTRAINT]; --외래키 존재시



TRUNCATE

1.TRUNCATE TABLE

  • 테이블에 저장되어 있는 데이터 모두 제거
  • 저장 공간 재사용 되도록 초기화
  • ROLLBACK 불가
truncate table 테이블명;

🎇DML

DML 정의

DML(Data Manipulation Language) : DDL에서 정의한 대로 데이터 입력, 입력한 데이터를 수정,삭제,조회하는 명령어

  • oracle은 dml 별도의 commit 필요
  • ms-sql은 dml도 auto commit
  • 비절차적 데이터 조작어 : 사용자가 무슨 데이터를 원하는지만 명세
  • 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(Data Sub Language)라고 한다

INSERT

INSERT : 테이블에 데이터를 입력하는 명령어

insert into 테이블명(컬럼명, 컬럼명...) values(값1, 값2...);

--전체 값
insert into 테이블명 values (전체 컬럼에 입력될 데이터 들);
  • 전체 컬럼에 대한 데이터가 컬럼 순서대로 빠짐없이 나열 되어야 하며,
    순서가 뒤바뀌어 데이터 유형 맞지 않거나, 누락된 데이터가 있어 전체 컬럼 갯수와 맞지 않을 경우 데이터베이스는 에러발생

UPDATE

UPDATE : 이미 저장된 데이터를 수정시 사용하는 명령어

update 테이블명 set 컬럼명=새로운 값, 컬럼명=새로운 값2... where 조건;

DELETE

DELETE : 이미 저장된 데이터 삭제 시 사용

  • 테이블 전체 삭제시 truncate 사용이 시스템 부하 측면 유리
    하지만 truncate는 별도의 로그 쌓지 않아 rollback 불가
delete from 테이블명 where 조건;

SELECT

SELECT : 저장되어 있는 데이터를 조회 하고자 할 때 사용하는 명령어.

  • RETRIEVE 라고도 함
SELECT [ALL/DISTINCT] 컬럼, 컬럼,...
FROM 테이블
WHERE 조건;
  • ALL : Default 옵션. 중복된 데이터도 모두 출력
  • Distinct : 중복된 데이터 존재 시 1건으로 처리

* : 전체 컬럼 조회

SELECT * FROM 테이블;
  • 조회되는 컬럼 순서 : 테이블의 컬럼 순서
  • WHERE절 X : 테이블 전체 ROW 출력

테이블명이나 컬럼명에 별칭(Alias) 붙이기 가능

  • JOIN 등을 할 때 테이블명이 길기 때문에 짧게 줄여쓰기 위함
  • Alias 사용시, 테이블 명 대신 무조건 Alias 사용해야함
SELECT topic.name, topic.id
FROM topic
WHERE topic.id=1;

--Alias 사용
SELECT t.name, t.id
FROM topic t
WHERE t.id=1;

산술 연산자

  • 사칙연산 기능을 가진 연산자
  • NUMBER, DATE 데이터 타입과 함께 사용 가능
연산자의미우선순위
()괄호로 우선순위 조정1
*곱하기2
/나누기2
+더하기3
-빼기3
SELECT a+b*a as mul,
	(a+b)*a	as mul2
FROM number;

=> 속성 이름이 mul, mul2로 나온다!
=> as명령어를 통해 별칭 지정 (생략 가능)

합성 연산자

  • 문자와 문자 연결시 사용
  • || 으로 연결
SELECT '재밌다'||' '||'SQLD'||'!' as fun
FROM DUAL;

MERGE

-MERGE : 테이블에 새로운 데이터 입력하거나 이미 저장되어있는 데이터에 대한 변경 작업을 한 번에 할 수 있는 명령어

  • 만약 update 바로 밑에 delete가 함께 있다면, delete는 update가 된 애들 중에 조건 만족하면 삭제
merge
	into 변경할 테이블명
using 비교 테이블명
    on 조건
when matched then
	update
    	set 컬럼명=새로운 값
when not matched then
	insert [(컬럼명, 컬럼명..)]
    values 값1, 값2;
    
    
--ex
merge
	into depart_bk db --db 테이블의 데이터 변경
using depart d --d 테이블을 이용
	on (db.depart_id=d.depart_id) --동일한 값이 있는지가 조건
when matched then --동일한 값이 있으면, 그 값 변경
	update
    	set db.depart_name=d.depart_name,
        	db.manger_id=d.manager_id,
            db.location_id=d.location_id
when not matched then --동일한 값 없으면 데이터 생성
	insert values (d.de part_id, d.depart_name, d.manager_id, d.location_id);

🎡TCL

TCL 정의

TCL(Transaction Control Language) : 트랜잭션 제어 명령어

  • COMMIT, ROLLBACK, SAVEPOINT
  • 트랜잭션 : 쪼개질 수 없는 업무처리의 단위, 논리적인 업무 단위
  • MSSQL에서는 auto commit false시 DDL 종료 후 Auto Commit 안됨
BEGIN TRANSACTION
...
COMMIT

BEGIN TRANSACTION
...
ROLLBACK

-BEGIN TRANSACTION(BEGIN TRAN) 으로 트랜잭션 시작, COMMIT TRANSACTION(COMMIT) 또는 ROLLBACK TRANSACTION(ROLLBACK)으로 트랜잭션 종료

트랜잭션 특징

  • 원자성(Atomicity) : 트랜잭션이 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야 함.
    All or Nothing
  • 일관성(Consistency) : 트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 함.
  • 고립성(Isolation) : 하나의 트랜잭션은 고립되어 수행되어야 함.
  • 지속성(Durablity) : 트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장 되어야 함. 모든 트랜잭션은 로그에 남겨진 뒤 commit 되어야 하고, 시스템 장애 발생 시 복구 가능해야 함.

트랜잭션 격리성이 낮을 경우 발생하는 문제점

  • Dirty Read : 다른 트랜잭션에 의해 수정되었고 커밋되지 않은 데이터를 읽는 것
  • Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 했는데, 그 사이에 다른 트랜잭션이 값을 수정또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
  • Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

COMMIT

COMMIT : DML 후 변경된 내용을 확정, 반영하는 명령어

  • commit 실행 X 시, 메모리까지만 반영되는데 메모리는 휘발성이며 다른 사용자는 변경된 값 조회 불가
  • update한 뒤, 오랫동안 commit 또는 rollback 하지 않으면 lock에 걸려 다른 사용자가 변경할 수 없는 상황 발생

ROLLBACK

ROLLBACK : DML 후 변경된 내용을 취소하는 명령어

  • rollback시 변경 이전 값으로 복구
  • update한 뒤, 오랫동안 commit 또는 rollback 하지 않으면 lock에 걸려 다른 사용자가 변경할 수 없는 상황 발생

SAVEPOINT

SAVEPOINT : rollback 수행 시, 전체 작업을 되돌리지 않고, 일부만 되돌릴 수 있게 하는 기능을 가진 명령어.

  • 만약 같은 이름을 가진 savepoint 생성 시, 나중 savepoint 로 rollback됨
--oracle
savepoint sv1;
...
rollback to sv1;

--mssql
save transaction sp1;
...
rollback transaction sp1;

👒WHERE 절

where : INSERT를 제외한 DML문을 수행할 때, 원하는 데이터만 골라 수행할 수 있도록 해주는 구문

  • 조건식에서 컬럼명은 반드시 좌측에 위치할 필요 X

WHERE 절 위치

  1. SELECT
select * from study where col1=1;

select * from study where col1<>1;



  1. UPDATE
update study set col1=4 where col1=3;

select * from study;



  1. DELETE
delete from study where col1=4;

select * from study;



비교 연산자

연산자뜻
=같음
<작음
<=작거나 같음
>큼
>=크거나 같음
  • 문자형 컬럼을 비교 조건으로 사용 시, 우측 상수값을 반드시 인용부호''로 감싸야 함
select * from study where col2='wow';




부정 비교 연산자

연산자뜻
!=같지 않음
^=같지 않음
<>같지 않음
not 컬럼명 =같지 않음
not 컬럼명 >크지 않음
select * from study where not col1>2;




SQL 연산자

연산자뜻
BETWEEN A AND BA와 B 사이(A, B 포함)
LIKE '비교 문자열'비교 문자열을 포함
IN(LIST)LIST 중 하나와 일치
IS NULLNULL값
select * from study where col1 between 2 and 3;

select * from study where col2 like '%o%';

select * from study where col2 like 'w%w';

w로 시작하고, w로 끝나는 행

select * from study where col2 in ('wow', 'hi');

ex) col2의 값 중 '%A', 'C%' 와 같이 %를 포함한 문자열 중,
_ 혹은 %가 포함된 문자 검색 시 ESCAPE 지정하여 쿼리 작성

SELECT * FROM test
where col2 like '%#%%' ESCAPE '#';



부정 SQL 연산자

연산자뜻
NOT BETWEEN A AND BA와 B 사이가 아님(A,B 미포함)
NOT IN(LIST)LIST 중 일치하는 것이 하나도 없음
IS NOT NULLNULL이 아님
select * from study where col1 not between 2 and 3;

--아래와 같음
select * from study where NOT (col1 between 2 and 3);
select * from study where NOT (col1>=2 and col1<=3);
select * from study where col1<2 or col1>3;


select * from study where col2 not in ('wow', 'hi');

--아래와 같음
select * from study where not(col2 in ('wow', 'hi'));
select * from study where not(col2='wow' or col2='hi');
select * from study where (col2!='wow' and col2!='hi');

select * from study where col2 is not null;




논리 연산자

연산자뜻
AND모든 조건이 true
OR하나 이상 조건이 true
NOTtrue면 false, false면 true
  • 처리 순서 : ()->NOT->SQL연산자, 비교연산자->AND->OR
select * from study where col1=1 and col2='wow';

🎢함수(Oracle/MS-SQL)

  • 수학의 함수처럼 INPUT에 따른 OUTPUT이 있는 함수
  • 사용자 정의 함수
  • 내장 함수(Built-in) : 단일행 함수, 다중행 함수(집계 함수, 그룹 함수, 윈도우 함수)
  • 함수의 입력 행 수에 따라 단일행 함수와 다중행 함수로 구분
  • 단일행 함수 : SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용 가능
  • 1:M 관계 테이블을 조인 시 M쪽에 출력된 행이 하나씩 단일행 함수의 입력값으로 사용
  • 다중행 함수도 단일행 함수와 동일하게 단일 값만 반환

DUAL 테이블 : 사용자 SYS가 소유하며 모든 사용자가 액세스 가능

  • DUMMY TABLE
  • DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 한 건 포함

문자 함수

1.CHR(ASCII 코드)/CHAR(ASCII코드)

  • ASCII코드 입력 시, 매핑되는 문자 출력
SELECT CHR(97) FROM DUAL;




2.LOWER(문자열)

  • 문자열을 소문자로 변환
SELECT LOWER('DANG') FROM DUAL;




3.UPPER(문자열)

  • 문자열을 대문자로 변환
SELECT UPPER('dangdangs') FROM DUAL;




4.LTRIM(문자열[,특정 문자])

  • 문자열의 왼쪽 공백 제거 (특정 문자x시)
  • 문자열을 왼쪽부터 한 글자씩 특정 문자와 비교하여 특정 문자에 포함되어 있으면 제거, 포함되지 않으면 멈춤 (특정 문자o시)
  • MS-SQL은 공백 제거만 가능
  • 대소문자 구분
SELECT LTRIM('Database','de') FROM DUAL;

SELECT LTRIM('Database','DE') FROM DUAL;

SELECT LTRIM('    Database') FROM DUAL;




5.RTRIM(문자열[,특정 문자])

  • 문자열의 오른쪽 공백 제거 (특정 문자X시)
  • 문자열의 오른쪽부터 한 글자씩 특정 문자와 비교하여 특정 문자에 포함되어 있으면 제거, 그렇지 않으면 멈춤 (특정 문자o시)
  • MS-SQL은 공백 제거만 가능
  • 대소문자 구분
select rtrim('dangdang    ') from dual;

select rtrim('oracle','le') from dual;

select rtrim('sllel','le') from dual;




6.TRIM([위치][특정 문자][FROM] 문자열)

  • 문자열의 왼쪽과 오른쪽 공백을 제거 (옵션X)
  • 문자열을 위치[LEADING|TRAILING|BOTH]로 지정된 곳부터 한 글자씩 특정 문자와 비교하여 같으면 제거하고 그렇지 않으면 멈춤 (옵션o)
  • 특정 문자는 한 글자만 지정 가능(LTRIM, RTRIM과 다름)
  • MS-SQL은 공백 제거만 가능
select trim('    dang    ') from dual;

select trim(leading 'g' from 'ggoo') from dual;

select trim(both 'g' from 'ggoog') from dual;




7.SUBSTR(문자열, 시작점 [,길이])/SUBSTRING(문자열)

  • 문자열을 원하는 부분만 잘라서 반환
  • 길이 명시 X시 문자열의 시작점부터 끝까지 반환
  • 시작은 1부터
select substr('sleepygood',7,4) from dual;

select substr('sql',3,3) from dual;



8.LENGTH(문자열)/LEN(문자열)

  • 문자열의 길이 반환
select length('dang') from dual;




9.REPLACE(문자열, 변경 전 문자열 [,변경 후 문자열])

  • 문자열에서 변경 전 문자열을 변경 후 문자열로 바꿔주는 함수
  • 변경 후 문자열 명시X시 문자열에서 변경 전 문자열을 제거
select replace('헷갈려요','헷갈려','좋아') from dual;

select replace('헷갈려요','헷갈려') from dual;





10.CONCAT(문자열1, 문자열2)

  • 문자열1과 문자열2를 연결해주는 함수


11.LPAD(값, 총 문자 길이, 채움 문자)

  • 지정한 길이 만큼 왼쪽부터 채움문자로 채움


숫자 함수

1.ABS(수)

  • 수의 절댓값 반환
select abs(-999) from dual;




2.SIGN(수)

  • 수의 부호를 반환
  • 양수는 1
  • 음수는 -1
  • 0은 0
select sign(0) from dual;

select sign(-300) from dual;

select sign(300) from dual;




3.ROUND(수 [,자릿수])

  • 수를 지정된 소수점 자릿수까지 반올림하여 반환
  • 자릿수 명시 X시 기본값은 0, 반올림된 정수 반환
  • 자릿수 음수지정 시, 지정된 정수부를 반올림하여 반환
  • 소수점 첫째자리가 0
  • 정수부 첫째자리가 -1
select round(39.5) from dual;

select round(391.53,1) from dual;

지정한 자릿수가 1이기 때문에, 소숫점 첫째자리만 나온다.
지정한 자릿수에서 반올림해준다고 생각하자!!

select round(391.53,-2) from dual;




4.TRUNC(수 [,자릿수])

  • 수를 지정된 소수점 자릿수까지 버림 하여 반환
  • 자릿수 명시X시 기본값은 0, 버림된 정수 반환
  • 자릿수 음수시 정수부에서 버림하여 반환
select trunc(391.53,-2) from dual;




5.CEIL(수)/CEILING(수)

  • 소수점 이하의 수를 올림한 정수 반환
  • 수보다 크거나 같은 최소 정수 반환
select ceil(391.53) from dual;

select ceil(-391.53) from dual;




6.FLOOR(수)

  • 소수점 이하의 수를 버림한 정수 반환
select floor(391.53) from dual;

select floor(-391.53) from dual;

-391보다 -392가 더 작기 때문!!



7.MOD(수1, 수2)

  • 수1을 수2로 나눈 나머지를 반환
  • 수2가 0이면 수1 반환
  • 두 수가 모두 음수이면, 나머지도 음수
select Mod(15,3) from dual;

select Mod(15,-4) from dual;

select Mod(-15,-4) from dual;

select Mod(-15,4) from dual;



날짜 함수

1/24 : 1일을 24로 나눔
1/24/60 : 1시간으로 60으로 나눔(1분)
1/24/6 : 1시간을 6으로 나눔(10분)

--ex
select to_char(to_date('2015.01.10 10','YYYY.MM.DD HH24')
+ 1/24/(60/10), 'YYYY.MM.DD HH24:MI:SS') from dual;

1.SYSDATE/GETDATE()

  • 현재의 연, 월, 일, 시, 분, 초를 반환해주는 함수
  • nls_date_format에 따라 sysdate의 출력 양식은 달라진다.
select SYSDATE from dual;




2.EXTRACT(특정 단위 FROM 날짜데이터)/DATEPART(특정 단위, 날짜 데이터)

  • 날짜 데이터에서 특정 단위(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)만을 출력해서 반환
select extract(day FROM sysdate) from dual;




3.ADD_MONTHS(날짜 데이터, 특정 개월 수)/DATEADD(MONTH, 특정 개월 수, 날짜 데이터)

  • 날짜 데이터에서 특정 개월 수를 더한 날짜를 반환해주는 함수
  • 날짜 이전 달이나 다음 달에 기준 날짜의 일자 존재X시 해당 월의 마지막 일자 반환
select add_months(to_date('2023-05-31','YYYY-MM-DD'),-1) from dual;

select add_months(to_date('2023-05-5','YYYY-MM-DD'),2) from dual;



변환 함수

데이터베이스에서 데이터 유형에 대한 형변환을 할 수 있는 경우는 두 가지

  • 명시적 형변환 : 변환 함수를 사용하여 데이터 유형 변환을 명시적으로 나타냄
  • 암시적 형변환 : 데이터베이스가 내부적으로 알아서 데이터 유형을 변환
    ex) 조건절에서 VARCHAR유형의 BIRTHDAY 컬럼를 숫자와 비교시,
    (SELECT * FROM WHERE BIRTHDAY=20000216;)
    데이터베이스는 내부적으로 BIRTHDAY 컬럼을 NUMBER형으로 변환 => 암시적 형변환

=> 암시적 형변환이 가능하다고, 데이터 유형 고려 X하며 SQL 작성 시, 성능 저하 불러올 수 있으며, 에러 발생할 수 있기 때문에, 명시적 형변환사용 추천




❕ MS-SQL은 CONVERT 또는 CAST 함수 사용가능

1.TO_NUMBER(문자열)

  • 문자열을 숫자형으로 변환해주는 함수
  • 문자열이 숫자로만 구성되어있어야 함
select to_number('123444') from dual;




2.TO_CHAR(수 or 날짜 [,포맷])

  • 수나 날짜형의 데이터를 포맷 형식의 문자형으로 변환해주는 함수
select to_char(14123) from dual;

select to_char(sysdate,'YY-MM-DD') from dual;

select to_char(sysdate,'YYYYMMDD HH24MISS') from dual;

select to_char(sysdate,'YYYYMMDD HHMISS') from dual;




3.TO_DATE(문자열, 포맷)

  • 포맷 형식의 문자형 데이터를 날짜형으로 변환해주는 함수

    포맷 표현
    포맷 형식뜻
    YYYY년
    MM월
    DD일
    HH시(12)
    HH24시(24)
    MI분
    SS초
select to_date('20230216','YYYYMMDD') from dual;



NULL관련 함수

1.NVL(인수1, 인수2)/ISNULL(인수1, 인수2)

  • 인수1 값이 NULL일 경우, 인수2 반환
    NULL이 아닐 경우, 인수1 반환
  • 인수1, 인수2 같은 데이터타입이어야 함

select col2, nvl(col1,-1) from study;

select col1, nvl(col2,'baaaaad') from study;




2.NVL2(인수1, 인수2, 인수3)

  • 인수1 값이 NULL일 경우, 인수3 반환
    NULL이 아닐 경우, 인수2 반환
select col2,nvl2(col2,'is not null', 'is null') from study;




3.NULLIF(인수1, 인수2)

  • 인수1과 인수2가 같으면 NULL
    같지 않으면 인수1 반환
select nullif('issame','issame') from dual;

select nullif('is not same','issame') from dual;




4.COALESCE(인수1, 인수2, 인수3, ...)

  • NULL이 아닌 최초의 인수 반환
select coalesce(null,null,'good','bad') from dual;



CASE(DECODE)

  • ~이면 ~이고, ~이면 ~이다
  • Oracle의 DECODE함수와 같은 기능
  • ELSE가 별도로 없으면 NULL이 DEFAULT
  • SELECT 절의 Alias 쓰기 X
-- Searched Case Expression
CASE WHEN 조건 열=조건값1 THEN 무엇1
	 WHEN 조건 열=조건값2 THEN 무엇2
     [ELSE 무엇3]
END
-- Simple Case Expression
CASE 조건 열
	WHEN 조건값1 THEN 무엇1
	WHEN 조건값2 THEN 무엇2
    [ELSE 무엇3]
END
DECODE(조건 열,조건값1,무엇1,조건값2,무엇2[,무엇3])

select col1,
    case when col1=1 then 'first'
         when col1=2 then 'second'
         else 'third'
    end as "read"
FROM study;

select col1,
    case col1
        when 1 then 'first'
        when 2 then 'second'
        else 'third'
    end as read
FROM study;

select col1, decode(col1,1,'first',2,'second','third') as decode from study;

🎭Group By, Having 절

Group By 정의

Group By : 데이터를 그룹별로 묶을 수 있도록 해주는 것

  • By뒤에는 그룹핑의 기준이 되는 컬럼이 온다.
  • 컬럼은 하나 이상이 온다.


집계 함수

데이터를 그룹핑하면, 그룹별로 집계 데이터도출이 가능해진다.

함수뜻
COUNT(*)전체 ROW Count(null 포함)
COUNT(컬럼)컬럼값 NULL인 행 제외 Count
COUNT(DISTINCT 컬럼)컬럼값 NULL 아닌 행에서 중복 제거한 Count
SUM(컬럼)컬럼값들 합계 (null 제외)
AVG(컬럼)컬럼값들 평균 (null 제외)
MIN(컬럼)컬럼값들 최솟값 (null 제외)
MAX(컬럼)컬럼값들 최댓값 (null 제외)
STDDEV(컬럼)컬럼값들의 표준 편차
VARIAN(컬럼)컬럼값들의 분산



Having 정의

HAVING절 : GROUP BY절 사용 시 WHERE절처럼 사용하는 조건절

  • 주로 데이터 그룹핑 후, 특정 그룹 골라낼 때 사용
  • group by이후 수행되기 때문에, 그룹핑 후 가능한 집계 함수로 조건 부여 가능
  • select절에 명시되지 않은 집계 함수로도 조건 부여 가능
  • where절을 사용해도 되는 조건은 성능을 위해 where절에서 필터링 선행
    =>group by는 비교적 비용이 많이 들기 때문에 수행 전 데이터량 줄여야함
  • sELECT절에서 정의한 Alias 사용 불가 (having이 먼저 실행됨)
  • 테이블 전체가 한개의 그룹이 되는 경우 HAVING단독 사용 가능

select col1,sum(col3) as "group_sum",
    count(col3) as "group_count"
from study
group by col1
having count(col1)>=2;



SELECT 문 논리적 수행 순서

select		...		5
from		...		1
where		...		2
group by	...		3
having		...		4
order by	...		6

🎏Order By 절

Order By 정의

order by절은 select문에서 논리적으로 맨 마지막에 수행

  • SELECT 한 데이터를 정렬
  • order by X시 임의의 순서대로 출력
  • order by뒤에는 정렬 기준 컬럼
  • 컬럼은 하나 이상
  • 집합 연산을 적용한 최종 결과에 대한 정렬 처리이므로 가장 마지막 줄에 한번만 기술

  • ASC(Ascending) : 오름차순
  • DESC(Descending) : 내림차순
  • 생략시 ASC 가 기본값
select col1, col2, col3
from study
order by col1;

select col1, col2, col3
from study
order by col1 desc, col2 asc;

NULLS FIRST/NULLS LAST

  • DB에 따라 NULL의 정렬 위치가 다르다
  • Oracle은 NULL을 최댓값으로 취급 (오름차순 시 마지막)
  • MS-SQL은 NULL을 최솟값으로 취급 (오른차순 시 처음)
  • 순서 변경을 원한다면 order by 절에 NULLS FIRST, NULLS LAST 옵션 사용
  • select절 다음이므로, alias 사용 가능
-- oracle
select col1, col2, col3
from study
order by col2 nulls first;

🔗조인(JOIN)

JOIN 정의

JOIN : 각기 다른 테이블을 한 번에 보여줄 때 쓰는 쿼리

  • 테이블 간 PK, FK 연관 관계 없어도 JOIN 가능
  • 일반적으로 PK, FK 연관성에 의해 JOIN 성립
  • DBMS 옵티마이저는 FROM절에 나열된 테이블들을 2개정도씩 묶어서 JOIN
  • 대부분 Non EQUI JOIN 수행 가능하지만, 때로는 설계상의 이유로 수행 불가능


EQUI JOIN

EQUI JOIN:= 조건으로 JOIN하는 것

  • 가장 흔히 볼 수 있는 방식

select s.col1 as s_col1, s.col3, t.col2 as name
from study s, test t
where s.col1=t.col1;



Non EQUI JOIN

Non EQUI JOIN : = 조건이 아닌 다른 조건(BETWEEN, >, >=, <, <=) 으로 JOIN

select s.col1, t.col1 as tcol, t.col2
from study s, test t
where s.col1 between t.col3 and t.col4;



OUTER JOIN

OUTER JOIN : JOIN 조건에 만족하지 않는 행도 출력되는 형태

  • ORACLE의 경우 모든 행이 출력되는 테이블의 반대편에 (+) 기호 붙여 작성
    왼쪽 또는 오른쪽 한 곳에만 표기

ex) LEFT OUTER JOIN

select s.col1, t.col1 from study s, test t
where s.col1=t.col1(+);

profile
당당
MSSQL DBA 신입
이전 포스트

[SQLD] 데이터 모델과 성능

다음 포스트

[SQLD] SQL 활용

0개의 댓글