DML: SQL 연산자와 내장 함수(1)

- select

- from

- where

- group by

- having

- order by

 

연산자

 

산술 연산자

 

 

select
	10+3 as addition, //addition이라는 이름의 컬럼에 넣기
    10-3 as subtraction,
    10*3 as multiplication,
    10/3 as division
    10 div 3 as 'int division',
    10%3 as modulus,
    10 mod 3 as 'mod operator',
    mod (10, 3) as 'mod funnction';

 

select ID, name, salary, salary/12 as monthly_salary
from instructor;

비교 연산자

 

select name, salary
from instructor
where salary between 80000 and 95000; //** 80000 이상 95000 이하(80000과 95000 포함한다는 것 중요)

select name, salary
from instructor
where (salary >= 80000) and (salary <= 95000);

 

=> 위 코드 두 개는 동치이며, 아래 결과를 반환해준다.

 

select name, salary
from instructor
where salary not between 80000 and 95000;

select name, salary
from instructor
where (salary < 80000) or (salary > 95000);

 

=> 위 코드 두 개는 동치이며, 아래 결과를 반환해준다.

 

select name, instructor.ID, teaches.ID, dept_name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

select name, instructor.ID, teaches.ID, dept_name, course_id
from instructor, teaches
where (instructor.ID = teaches.ID) and (dept_name = 'Biology');

 

=> 위 코드 두 개는 동치이며, 아래 결과를 반환해준다.

select name, instructor.ID, teaches.ID, dept_name, couse_id
from instructor, teaches
where (instructor.ID = teaches.ID) or (dept_name = 'Biology');

 

=> 아래 결과를 반환해준다.

논리 연산자

 

*** "우선순위: AND 연산자 > OR 연산자" 라는 사실이 중요 ***

 

select *
from instructor
where (dept_name ='Physics' or dept_name = 'Comp.Sci.') and salary > 90000;

select *
from instructor
where dept_name = 'Physics' or dept_name = 'Comp.Sci.' and salary > 90000;

 

위에는 괄호 때문에 or 연산자가 먼저 실행되지만,

아래는 괄호가 없고 and 연산자가 우선순위가 더 높기 때문에 and가 먼저 실행됨

 

문자열 연산자: LIKE

 

패턴에 맞는 문자열 검색할 수 있도록 함

select name
from instructor
where name like '%in%'; // 'in' 을 포함한 모든 string

select name
from instructor
where name link '_in%'; // _: 문자 꼭 1개 있어야 함

 

select name
from instructor
where name like '_in_'; // 중간에 2번째 3번째가 'in'인 4글자

select name
from instructor
where name link '_in'; //'in'으로 끝나는 3글자

 

=> 없으므로 "empty set"

 

 

<예제>

select dept_name
from instructor
where dept_name like 'Comp%';

select dept_name
from instructor
where dept_name like '%sic%;

select dept_name
from instructor
where dept_name like '______'; // 5글자

 

select true
where '100%' like '100\%'; // 뒤의 '100\%'는 '100%'로 읽히기 때문에 앞의 '100%'와 동치가 됨

=> '%' 또는 '='를 문자로 인식하기 위해서는 '\'를 앞에 넣어주면 된다.

select true
where '100%' like '100#%' escape '#'; //#가 새로운 escape character임을 알려줌

=> '\' 말고 다른 문자를 escape character로 사용하고 싶다면 지정 가능

select true
where '100%' like '100%'; //100 뒤에 어떤 문자열도 올 수 있으므로 '100%'도 가능

=> "% = 0개 이상의 문자열" -> 100 뒤에 어떤 문자열도 가능

 

SQL 내장 함수

 

단일 행 함수

 

- 각 행에 개별적으로 적용 -> 결과 리턴

- SELECT, WHERE, ORDER BY에서 사용 가능

- 처리하는 데이터 타입에 따른 단일 행 함수 분류 -> 문자형, 숫자형, 날짜형, 형 변환, NULL 관련 함수

* 문자열: CONCAT(), LENGTH(), STRCMP(), LOWER(), UPPER(), SUBSTRING() 등

* 숫자형: ABS(), CEIL(), FLOOR(), ROUND(), TRUNCATE(), 삼각함수, 지수함수 등

* 날짜형: NOW(), DATE_FORMAT(), YEAR() 등

* 형 변환: CASE()

 

<CONCAT(): 문자열 연결>

 

*** 인수에 NULL 포함 -> 결과도 NULL ***

select concat('Hello', ' ', 'World') as hello_world,
	concat('Hello', '', 'World') as hello_world,
    concat('Hello', NULL, 'World') as hello_world_wn;

select concat(name, '###', ID, ':::', dept_name)
from instructor;

 

<숫자형 함수>

select abs(-10) as absolute_value,
	ceil(-4.256) as ceiling_value,
    floor(-4.256) as floor_value,
    round(-4.256, 2) as rounded_value,
    truncate(-4.256, 2) as truncated_value;

 

<날짜형 함수>

select now() as current,
	utc_timestamp() as current_utc,
    date_format(now(), '%Y-%m-%d') as formatted,
    year(now()) as year,
    month(now()) as month,
    day(now()) as day,
    hour(now()) as hour,
    minute(now())as min,
    second(now()) as sec,
    date_add(now(), interval 7 day) as after_a_week,
    datediff('2025-12-25', now()) as diff_xmas,
    timediff('18:00:00', curtime()) as time_diff;

 

<cast(): 명시적 형 변환>

select cast('2025-03-31' as datetime) as "str->datetime",
	cast('2025-03-31' as date) as "str->date",
    cast('2025-03-31' as time) as "str->time 1", // 시간이 아니라 날짜이므로 아무렇게나
    cast('2025-03-31 11:22:33' as time) as "str->time 2",
    cast(-123.456 as signed) as 'float->int'; //부호있는 정수로 반환
    cast(-123.456 as decimal(5, 2)) as 'float-decimal', //총 5자리 중 소수점 밑에 2개 오게끔 유효숫자 맞출 것->반올림
    cast(-123.456 as float) as 'float->float', // float 형식으로 변환
    cast(-123.456 as double) as 'float->double', //double 형식으로 변환
    cast(-123.456 as char) as 'float->str', //문자열 형식으로 변환
    cast('sample string' as binary) as 'str->bin'; //binary 타입으로 변환

 

특정 함수를 사용하거나 스키마에 넣어야할 때 cast()를 써서 형변환해줄 수 있다.

다중 행 함수

 

집계 함수

인자 컬럼은 1개만 가능 -> 해당 컬럼의 모든 행이 인자로 들어감

* COUNT()는 살짝 예외! => 예제 보기

select count(*) as 'num of instructors',
	sum(salary) as total_sal,
    avg(salary) as avg_sal,
    max(salary) as max_sal,
    min(salary) as min_sal,
from instructor;
//where salary > 80000

 

where 절이 없으므로 'salary'열의 모든 행이 인자로 들어간다.

where 절이 추가되면 'salary > 80000'인 행만 인자로 들어간다.

 

‼️WHERE 절에는 집계함수 사용 불가‼️

 

<예제>

(1)

select count (course_id)
from teaches;

(2) 중복 제거

select count(distinct, course_id) //중복 행 제거 => 모든 집계함수에서 가능
from teaches;

 

(3) COUNT 유일 구분 다중 컬럼

COUNT 함수에서만 인자로 유일하게 구분되는 다중 컬럼을 받을 수 있음

쉽게 말해, 두 개 이상의 컬럼이 인자가 될 수 있다는 것이다.

 

select count(distinct course_id, sec_id)
from teaches;

select count(distinct course_id, ID)
from teaches;

 

select count(*)
from teaches;

 

테이블의 모든 컬럼은 항상 유일하게 구분됨(테이블의 원래 특성) -> "*"(뜻: 모든 컬럼) 사용 가능

 

그룹 함수

윈도우 함수

 

NULL 값

 

: "모르는 값" 또는 "값이 존재하지 않음"

IS NULL / IS NOT NULL

select *
from null_test
where col_1 is null;

select *
from null_test
where col_1 is not null;

 

<산술연산자 및 비교연산자에서의 NULL값 처리>

: 피연산자에 NULL이 존재 -> 결과도 NULL

select col_1 + col_2,
	col_1 - col_2,
    col_2 * col_3,
    col_2 / col_3
from null_test

select 5 < null,
	null != null,
    null = null;

 

<집계함수에서의 NULL값 처리>

: NULL값은 제외하고 처리

select sum(col_1),
	sum(col_4),
    sum(col_1 + col_4), 
    sum(col_1) + sum(col_4)
from null_test;

 

"sum(col_1 + col_4)"

=> "30+NULL=NULL", "NULL+NULL=NULL", "0+NULL=NULL" => sum(NULL,NULL,NULL)=NULL

"sum(col_1)+sum(col_4)"

=> sum(col_1)=30, sum(col_4)=NULL => sum(col_1)+sum(col_4)=30+NULL=NULL

 

<논리연산자에서의 NULL값 처리>

"NOT null": 판단 불가 -> NULL

"x AND null": x=false -> false

"x OR null": x=true -> true

select not null;

 

select (true and null),
	(false and null),
    (null and null);

 

select (true or null),
	(false or null),
    (null or null);