본문 바로가기
코딩도전기/DB(mariaDB)

CODO Day35_DB(Auto_increment/Limit&offset/Function)

by 코도꼬마 2023. 3. 24.

Auto_increment

  • 자동으로 증가하는 속성
  • Table 생성 시 속성으로 지정하거나 이미 생성된 Table에 추가
  • auto increment 속성이 사용되는 컬럼은 key 설정이 되어 있어야 함
    • 무조건 들어가고 절대 중복되지 않기 때문에 PK로 활용
    • 자동증가하는 속성으로 중복값이 있으면 안되기 때문

 

  • 테이블 생성 시 지정
create table auto_inc(
	no int(10) auto_increment primary key
	,name varchar(10) not null
);
desc auto_inc;

insert into auto_inc (name) values ('kim');
insert into auto_inc (name) values ('lee');
insert into auto_inc (name) values ('park');

select * from auto_inc;

 

  • 이미 생성된 테이블에 추가
    • auto_increment가 설정되는 컬럼은 키가 있어야 함
    • 중복값이 있을 경우 에러남
create table test(
	no int(10)
	,name varchar(10) not null
);
insert into test (no, name) values(1, 'a');

select * from test;

-- Error : Incorrect table definition; there can be only one auto column and it must be defined as a key
alter table test modify no int(10) auto_increment;
alter table test modify no int(10) primary key auto_increment;

insert into test(name)values('b');
insert into test(name)values('c');

 

  • auto_increment의 기본값 수정
alter table test auto_increment = 100;
insert into test(name)values('d');
insert into test(name)values('e');

 

 

 

Limit & offset

  • 데이터가 많을 경우 한번에 보기 어려움(방대한 데이터를 한번에 불러오기에는 무리가 있음)
  • limit와 offset을 활용하면 많은 양의 데이터를 paging하여 보여줄 때 유용
Limit n 첫 행부터 n개만 보여줌
Limit r, n r행 부터 n개만 보여줌
Limit n Offset r

 

  • limit : 특정 갯수를 불러옴(무조건 처음부터 불러옴)
select * from employees limit 5;

 

  • offset : 시작할 index를 지정
    • 0번 인덱스부터 5개 가져오
select * from employees limit 5 offset 0;
select * from employees limit 5 offset 5;

 

  • limit로 가져오기
    • limit o,n : offset, n개
select * from employees limit 0,5;
select * from employees limit 5,5;

 

# offset과 limit은 데이터를 확보하는 것이 아니라 처음부터 하나씩 세기 때문에 데이터가 많아지면 속도가 느려짐

  • 데이터 확보 후 가져오기(정렬,PK)
select i.emp_no
	,i.first_name
	,i.family_name
from (select * from employees order by emp_no) i limit 4,5;

 

  • join을 이용하여 필요한 데이터 확보(속도 더 빠름)
select 
	e.emp_no 
	,e.first_name 
	,e.family_name 
from
(select emp_no from employees limit 4,5) i join employees e on i.emp_no = e.emp_no;

 

 

 

Function

  • 다양한 함수들이 있음
집계(aggregate) 함수 합계, 최대, 최소, 평균 등을 구하는 함수
날짜(date time) 함수 날짜를 다루는 함수
숫자(numeric) 함수 숫자를 다루는 함수
문자(character) 함수 문자열을 다루는 함수

 

집계함수(aggregate) 

  • 여러행 or 테이블 전체 행으로 부터 하나의 결과값을 반환하는 함수
  • group by에서 데이터를 압축할 때 자주 사용
  • AVG(), COUNT(), MAX(), MIN(), SUM() 등이 있음

 

  • COUNT() : 검색된 행의 수를 반환
    • count(*)을 할 경우 null이 있으면 뛰어넘기 때문에 사용하지 않는 것이 좋음
    • null이 없는 컬럼을 대상으로 하는 것이 좋음
    • as 생략가능
select count(deptno) as cnt from dept;

 

  • MAX() : 특정 행에 대한 최대값을 반환(문자도 가능)
select max(salary) as salary from employees;

 

  • MIN() : 특정 행에 대한 최솟값을 반환
select min(salary) as salary from employees;

 

  • 활용문제
-- 최대 급여를 받는 사람의 성, 이름, 급여 추출
select family_name, first_name, salary from employees where salary = (select max(salary) as salary from employees);

-- 최소 급여를 받는 사람의 성, 이름, 급여 추출
select family_name, first_name, salary from employees where salary = (select min(salary) as salary from employees);

-- 최대/최소 한번에
select family_name, first_name, salary from employees where salary in ((select max(salary) as salary from employees),(select min(salary) as salary from employees));
select family_name, first_name, salary from employees where salary = (select max(salary) as salary from employees)
union all
select family_name, first_name, salary from employees where salary = (select min(salary) as salary from employees);

 

  • AVG : 평균
select avg(salary) from employees;

 

  • 반올림 ROUND(값, 반올림자릿수)
select round(avg(salary),1)  from employees where depart_no = 'dev002';

 

  • SUM : 합계245
select sum(salary) from employees;

 

 

날짜(date time) 함수

  • 날짜 표시
-- 1) dayofweek(date) : 해당 날짜가 한 주의 몇번째 요일인지 숫자로 리턴
-- 1 = 일요일, 2 = 월요일...
select dayofweek('2023-03-23') as '요일';  -- 목요일 = 5

-- 2) weekday(date) : 해당 날짜가 한 주의 몇번째 요일인지 숫자로 리턴
-- 0 = 월요일, 1 = 화요일...
select weekday('2023-03-23') as '요일';  -- 목요일 = 3

-- 3) dayofmonth(date) : 해당 달의 몇번째 날인지 1 ~ 31 리턴
select dayofmonth('2023-03-23') as '일';

-- 4) dayofyear(date) : 해당 해의 몇번째 날인지 1 ~ 365 리턴
select dayofyear('2023-03-23');

-- 5) month(date) : 해당 날짜가 몇월인지 1~12 리턴
select month('2023-03-23');

-- 6) dayname(date) : 해당 날짜의 요일 이름 리턴
select dayname('2023-03-23');

-- 7) monthname(date) : 해당 날짜의 월 이름 리턴 
select monthname('2023-03-23');

-- 8) quarter(date) : 해당 날짜의 분기를 리턴
select quarter('2023-03-23');

 

  • 날짜 계산
-- 1) period_add(p,n) : yymm또는 yyyymm형식으로 주어진 달에 n개월을 더해서 yyyymm 리턴
select period_add(2303,6);

-- 2) period_diff(p1,p2) :  yymm또는 yyyymm형식으로 주어진 두 기간 사이의 개월을 구함
select period_diff(2303,2308);

-- 3) date_add(date,interval expr type) or adddate(date,interval expr type) : 특정 단위의 시간을 더함
-- seconds, minutes, hours, days, months, years
select date_add('2023-03-23 11:17:59',interval 1 second);  -- 1초 더하기
select date_add('2023-03-23',interval 19 day);  -- 19일 더하기
select date_add('2023-03-23',interval 1 month);  -- 1달 더하기
select date_add('2023-03-23 11:17:59',interval '10:1' minute_second);  -- 10분 1초 더하기
select date_add('2023-03-23 11:17:59',interval '1:30' hour_minute);  -- 1시간 30분 더하기

-- 4) date_sub(date,interval expr type) or subdate(date,interval expr type) : 특정 단위의 시간을 뺌 
-- seconds, minutes, hours, days, months, years
select date_sub('2023-03-23 11:17:59',interval '1 1' day_hour);  -- 1일 1시간 빼기
select date_sub('2023-03-23',interval '1-1' year_month);  -- 1년 1개월 빼기
select date_sub('2023-03-23 11:17:59',interval '1:1:1' hour_second);  -- 1시간 1분 1초 빼기
select date_sub('2023-03-23 11:17:59',interval '1 1:1' day_minute);  -- 1일 1시간 1분 빼기
select date_sub('2023-03-23 11:17:59',interval '1 1:1:1' day_second);  -- 1일 1시간 1분 1초 빼기

-- 5) to_days(date) : 주어진 날짜를 0000년-00월-00일 부터의 일수로 바꿈
select to_days(230323);  -- 0000년-00월-00일 부터 2023년-03월23일 까지의 일 수

-- 6) from_days(n) : 주어진 일수로부터 날짜를 구함(to_days와 반대)
select from_days(738967);

 

  • 날짜 형식
-- 1) date_format(date,format) format의 정의에 따라 날짜 혹은 시간을 출력
-- %y : 2자리 년도 / %Y : 4자리 년도
-- %m : 월(숫자) / %M : 월 이름
-- %d : 일
select date_format('2023-03-23', '%M %Y');

-- %b : 짧은 월이름(jan..dec)
-- %D : 영어식 접미사를 붙인 일(1st, 2nd, 3rd...)
-- %a : 짧은 요일명(sun~sat)
select date_format('2023-03-23', '%b %D (%a)');

-- %W : 요일명 / %w : 일주일의 몇번째 요일인지(0=sun)
select date_format('2023-03-23', '%b %D (%w)');

-- %c : 월(1~12), %e : 일(0~31), %H : 24시 형식의 시간 (00~23), %i : 분, %s : 초(00~59)
select date_format('2023-03-23 15:44:23', '%c-%e %H:%i:%s');

-- %h : 12시 형식의 시간 (01~12), %p : am 또는 pm 문자
select date_format('2023-03-23 15:44:23', '%h:%i:%s%p');

-- %T : 시분초 24시 형식 (hh:mm:ss)
select date_format(NOW(), '%T');

-- %r : 시분초12시 형식 (hh:mm:ss [ap]m)
select date_format(NOW(), '%r');

-- %U : 한해의 몇 번째 주인가(0~52) 일요일이 시작일
-- %u : 한해의 몇 번째 주인가(0~52) 월요일이 시작일
select date_format(NOW(), '%U');
select date_format('2023-03-23 15:44:23', '%h:%i:%s %p');

-- 3) curdate() : 오늘 날짜를 'yyyy-mm-dd' 형식으로 리턴
select curdate(); -- yyyy-mm-dd
select curdate() + 0; -- yyyymmdd

-- 4) curtime() : 'hh:mm:ss' 형식으로 현재시간을 나타낸다.
select curtime();		-- hh:mm:ss
select curtime() + 0;	-- hhmmss

-- 5) now() | sysdate() | current_timestamp() : 오늘 날짜와 현재 시간을 'yyyy-mm-dd hh:mm:ss' 형식으로 반환
select now();
select now() + 0;

-- 6) unix_timestamp(date)
-- 인수(값) 없이 사용될 경우 현재 시간의 유닉스 타임스탬프
-- 날짜형식의 date가 인수로 주어진 경우에는 주어진 날짜의 유닉스 타임스탬프를 반환
-- 유닉스 타임스탬프 : 그리니치 표준시로 1970 년 1월 1일 00:00:00 이 후의 시간경과를 초단위로 나타낸 것
select unix_timestamp();
select unix_timestamp(now());

-- 7) from_unixtime(unix_timestamp) : 주어진 유닉스 타임스탬프 값으로부터 'yyyy-mm-dd hh:mm:ss' 형식의 날짜로 반환
select from_unixtime(1679540388);
select from_unixtime(1679540388) + 0;

-- 8) from_unixtime(unix_timestamp,format) : 주어진 유닉스 타임스탬프 값을 주어진 날짜 형식에 맞게 바꿔서 보여준다. 
select from_unixtime(unix_timestamp(),'%Y-%m-%d %h:%i:%s');

-- 9) sec_to_time(seconds) : 주어진 초를 'hh:mm:ss' 형식으로 반환
select sec_to_time(4567);
select sec_to_time(4567) + 0;

-- 10) time_to_sec(time) : 주어진 시간을 초 단위로 바꿔준다.
select time_to_sec('22:23:00');

 

  • 활용문제
-- 날짜와 날짜 사이 모든 날짜 구하기
select * from (
	select adddate('2000-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date_of_month from
		(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
		(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
		(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
		(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
		(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v where date_of_month between '2022-10-01' and LAST_DAY('2022-10-01');

select adddate('2000-01-01',10000 + 1000 + 100 + 10 + 1) date_of_month from where date_of_month between '2022-10-01' and LAST_DAY('2022-10-01');

-- 날짜 기준으로  right outer join
select b.idx,b.bHit, m.reg_date from bbs b RIGHT OUTER JOIN (
	select * from (
		select adddate('2000-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) reg_date from
			(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
			(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
			(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
			(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
			(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
	) v
where reg_date between '2022-10-01' and LAST_DAY('2022-10-01')) m ON b.reg_date = m.reg_date order by m.reg_date;

 

 

숫자(numeric) 함수

  • 수 값을 인수로 받아 NUMBER 타입의 값을 반환하는 함수(자주 사용하는 몇가지만)
-- 1) ABS(n) : 절대값 계산
SELECT ABS (-1.234) absolute;
-- 결과 : 1.234

-- 2) CEIL(n) : 올림값 반환
SELECT CEIL(10.1234) "CEIL";
-- 결과 : 11
SELECT CEIL(-10.1234) "CEIL";
-- 결과 : -10

-- 3) FLOOR(n) : 버림값을 반환하는 함수이다.
SELECT FLOOR(10.1234) "FLOOR";
-- 결과 : 10
SELECT FLOOR(-10.1234) "FLOOR";
-- 결과 : -11 

-- 4) ROUND(n, [m]) : 반올림 / n : 반올림 할 값, m : 반올림 자릿수
SELECT ROUND(192.153, 1) "ROUND"; 
-- 결과 : 192.2 / 소숫점 2자리를 반올림하여 1자리까지 표현
SELECT ROUND(192.153, -1) "ROUND";
-- 결과 : 190 / 숫자 1자리를 반올림하여 2자리까지 표현

-- 5) TRUNCATE(n, m) : n값을 절삭 / m : m의 자릿수까지 남기고 자름
SELECT TRUNCATE(7.5597, 2) "TRUNCATE";
-- 결과 : 7.55
 
-- 6) TRUNC 예제
SELECT TRUNCATE(789.5597, -2) "TRUNC";
-- 결과 : 700

-- 7) MOD(m, n) : m을 n으로 나눈 나머지를 반환 / n이 0일 경우 m을 반환
SELECT MOD(9, 4) "MOD";
-- 결과 : 1

 

 

문자(character) 함수

  • CHAR, VARCHAR 타입을 인수로 받아 VARCHAR 타입의 값을 반환
-- 1) CONCAT(char1, char2, ...)
SELECT CONCAT('www.', 'mariadb','.org'); 
  
-- 2) LOWER(char), UPPER(char)
-- LOWER : 주어진 문자열을 소문자로 변환시켜 준다.
-- UPPER : 주어진 문자열을 대문자로 변환시켜 준다.
SELECT UPPER('mariadb') name UNION ALL
SELECT LOWER('MARIADB') name;

-- 3) LPAD(char1, n, [char2]), RPAD(char1, n, [char2])
-- LPAD : 왼쪽에 char2를 빈공간 만큼 끼워 넣음
-- RPAD : 오른쪽에 char2를 빈공간 만큼 끼워 넣음
-- n : 반환되는 문자열의 전체 길이 
-- char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 잘라서 반환
SELECT LPAD('mydatabase', 12, '*') name;	-- **mydatabase
SELECT RPAD('mydatabase', 12, '*') name;	-- mydatabase**
SELECT RPAD('mydatabase',9,'*') AS pass;	-- mydatabas

-- 4) SUBSTR(char, m ,[n]), SUBSTRB(char, m ,[n])
-- SUBSTR 함수는 m 번째 자리부터 길이가 n개인 문자열을 반환
-- m이 음수일 경우에는 뒤에서 m번째 문자부터 n개의 문자를 반환
-- SUBSTRB 함수에서 B는 Byte단위로 처리하겠다는 의미
-- 세번째부터 문자열 반환
SELECT SUBSTR('mydatabase', 3) name;  
-- 세번째부터 네개의 문자열 반환
SELECT SUBSTR('mydatabase', 3, 4) name;
-- 뒤에서 세번째부터 두개의 문자열 반환(가져오는 문자열의 순서는 정방향)
SELECT SUBSTR('mydatabase', -3, 2) name;

-- 5) LENGTH(char)
-- 문자열의 길이(byte 수)를 반환(한글은 한 글자에 3byte << UTF-8)
SELECT LENGTH('마리아디비') length;
 

-- 6) REPLACE(char1, str1, str2)
-- REPLACE는 문자열의 특정 문자를 다른 문자로 변환
-- 'char1'의 'str1'을 'str2'로 변환
SELECT REPLACE('mydatabase','my','maria ') name;
 
-- 대소문자를 구분하므로 정확하게 기입
SELECT REPLACE('MyDataBase','my','maria ') name;
SELECT REPLACE('MyDataBase','My','maria ') name;
 
 
-- 7) INSTR (char1, str1)
-- 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환
-- 지정한 문자열이 발견되지 않으면 0이 반환
-- char1 : 지정문자, str1 : 검색문자
SELECT INSTR('CORPORATE FLOOR','OK') idx; -- 0
SELECT INSTR('CORPORATE FLOOR','P') idx; -- 4


-- 8) TRIM(char1) 양쪽 공백을 지움
SELECT ' mydatabase ' AS title union ALL
SELECT TRIM(' mydatabase ') AS title;

-- TRIM(char1 FROM char2) 특정한 문자열을 명시하면 앞 뒤에서 지움
SELECT TRIM('토' FROM '토마토') AS title;


-- 9) LTRIM(char1) -- 왼쪽 공백 지음
SELECT LTRIM(' mydatabase ') AS title; 


-- 10) RTRIM(char1)-- 오른쪽 공백 지움
SELECT RTRIM(' mydatabase ') AS title;
저작자표시 비영리 변경금지

'코딩도전기 > DB(mariaDB)' 카테고리의 다른 글

CODO Day36_DB_ERD(논리설계/물리설계)  (0) 2023.03.24
CODO Day35_DB(정규화&역정규화)  (0) 2023.03.24
CODO Day35_DB(In&Exists/Any&All/Index/View)  (0) 2023.03.22
CODO Day34_DB(Join/Set)  (0) 2023.03.22
CODO Day33_DB(Constraint/Relation/Sub Query)  (0) 2023.03.21

관련글

티스토리툴바