1. auto_increment
Auto_increment 는 자동으로 증가하는 속성 이다.
Table 생성시 속성으로 지정해 주거나 이미 생성된 Table 에 추가 해 줄 수 있다.
단, auto increment 속성을 사용되는 컬럼은 키 설정이 되어 있어야 한다.
-- auto_increment 는 자동 증가하는 속성
-- 중복되지 않고, 반드시 들어가며 테이블에 종속적이므로 PK 에 자주 사용된다.
-- 생성법 1 : 테이블 생성시 함께 생성
create table auto_inc(
no int(10) primary key auto_increment
,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;
-- 생성법 2 : 이미 생성된 테이블에 추가
create table test(
no int(10)
,name varchar(10) not null
);
insert into test (no,name)values(1,'a');
-- 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');
-- 속성 초기화
alter table test auto_increment = 100;
insert into test (name)values('d');
insert into test (name)values('e');
select * from test;
2. limit
데이터가 많을 경우 한번에 볼 수 없을 수 있다.
그래서 우리는 limit 와 offset 을 활용 한다.
많은 양의 데이터를 paging 하여 보여줄 때 유용 하다.
select * from employees e ; -- 13
-- 5개 씩
-- 0~4 / 5~9 / 10~13
-- limit n: n개 보여주기
select * from employees e limit 5;
-- limit n,m : n 번부터 m 개
select * from employees e limit 0,5;
select * from employees e limit 5,5;
select * from employees e limit 10,5;
-- offset n: n번 부터
select * from employees e limit 5 offset 0;
select * from employees e limit 5 offset 5;
select * from employees e limit 5 offset 10;
-- 위 내용은 일반적인 페이징 처리 방식
-- 위 방식은 데이터가 많아지면 느려진다.
-- offset 의순서를 위해서 하나씩 세면서 내려가기 때문
-- 속도 개선 1단계> 데이터 정렬 후 내용을 가져온다.
select e.emp_no, e.first_name, e.family_name
from (select * from employees e order by emp_no) e
limit 0,5;
-- 속도 개선 2단계> 인덱스가 없다면 인덱스 추가
desc employees;
-- 인덱스가 있다면 활용
select e.emp_no, e.first_name, e.family_name
from (select * from employees e order by emp_no limit 0,5) e
join employees e2 on e.emp_no = e2.emp_no;
-- 이게 왜 빠를까?
-- 조인을 통해서 이미 적용된 인덱스를 활용해서 값을 찾는 방식이므로 더 빠르다.
3. 집계 함수 (count, min, max, avg, sum)
-- 집계 함수 : 여러행 또는 테이블 전체 행으로 부터 하나의 결과값을 반환하는 함수
-- 1) COUNT(column) : 검색된 행의 갯수 반환
select count(deptno) from dept d;
-- 2) MAX
select max(salary) from employees e;
-- 3) MIN
select min(salary) from employees e;
-- 최대 급여를 받는 사람과, 최소 급여를 받는 사람의 이름과 급여를 출력 하세요
-- union all
select family_name, first_name, salary from employees where salary = (select max(salary) from employees)
union all
select family_name, first_name, salary from employees where salary = (select min(salary) from employees);
-- in
select family_name, first_name, salary from employees
where salary in ((select max(salary) from employees), (select min(salary) from employees));
-- 4) AVG : 평균 반환
-- round() : 소숫점 1자리에서 반올림
select round(avg(salary),1) from employees;
-- 5) SUM : 총 합계 반환
select sum(salary) from employees where depart_no = 'dev004';
4. 날짜 관련 함수
/* 1. 날짜 표시 */
-- 1) dayofweek(date) : 날짜를 한 주의 몇 번째 요일인지를 나타내는 숫자로 리턴한다.
-- (1 = 일요일, 2 = 월요일, ... 7 = 토요일)
select dayofweek('2021-06-30') as '요일'; -- 4
-- 2) weekday(date) : 날짜를 한 주의 몇 번째 요일인지를 나타내는 숫자로 리턴한다.
-- (0 = 월요일, 1=화요일 ... 6 = 일요일)
select weekday('2021-06-30') as '요일'; -- 2
-- 3) dayofmonth(date) : 그 달의 몇 번째 날인지를 알려준다. 리턴 값은 1에서 31 사이이다.
select dayofmonth('2021-06-29') as '일';
-- 4) dayofyear(date) : 한 해의 몇 번째 날인지를 알려준다. 리턴 값은 1에서 366 사이이다.
select dayofyear('2021-01-12');
select dayofyear('2021-07-30');
-- 5) month(date) : 해당 날짜가 몇 월인지 알려준다. 리턴 값은 1에서 12 사이이다.
select month('2021-02-03');
-- 6) dayname(date) : 해당 날짜의 영어식 요일이름을 리턴한다.
select dayname('2021-12-25');
-- 7) monthname(date) : 해당 날짜의 영어식 월 이름을 리턴한다.
select monthname('2021-02-05');
-- 8) quarter(date) : 분기를 리턴한다 (1~ 4)
select quarter('2021-04-01') as '분기';
-- 9) week(date) : 해달 날짜가 몇 번째 주일인지를 리턴
-- select week('date',[mode]);
/*
mode 기준 범위
0 Sunday 0-53 주의 첫째 날은 일요일
1 Monday 0-53 주의 첫째 요일이 월요일이고 첫째 주가 3일 이상인 경우
2 Sunday 1-53 주의 첫째 날은 일요일
3 Monday 1-53 주의 첫째 요일이 월요일이고 첫째 주가 3일 이상인 경우
4 Sunday 0-53 주의 첫째 요일이 일요일이고 첫째 주가 3일 이상인 경우
5 Monday 0-53 주의 첫째 날은 월요일
6 Sunday 1-53 주의 첫째 요일이 일요일이고 첫째 주가 3일 이상인 경우
7 Monday 1-53 주의 첫째 날은 월요일
*/
select week('1998-01-01'); -- mode 를 사용하지 않으면 0과 같다.
select week('1998-01-01',0); -- 0
select week('1998-01-01',1); -- 1
/* 2. 날짜 계산 */
-- 1) period_add(p,n) : yymm 또는 yyyymm 형식으로 주어진 달에 n개월을 더한다. 리턴 값은 yyyymm의 형식이다.
select period_add(2109,36); -- 202409
-- 2) period_diff(p1,p2) : yymm 또는 yyyymm 형식으로 주어진 두 기간사이의 개월을 구한다
select period_diff(2104,202109); -- -5
select period_diff(202104,202109);
-- 3) date_add(date,interval expr type) or adddate(date,interval expr type)
-- 특정한 단위를 더한다.
-- seconds, minutes, hours, days, months, years
select date_add("2021-12-31 23:59:59",interval 1 second); -- 1초 더하기
select date_add("2021-12-31 23:59:59",interval 30 day); -- 30일 더하기
select date_add("2021-12-31 23:59:59",interval 1 month); -- 1개월 더하기
select date_add("2021-12-31 23:59:59",interval "10:1" minute_second); -- 10분:1초 더하기
select date_add("2021-12-31 23:59: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("2022-01-01 00:00:00",interval "1 1" day_hour); -- 1일 1시간
select date_sub("2022-01-01 00:00:00",interval "1-1" year_month); -- 1년-1개월
select date_sub("2022-01-01 00:00:00",interval "1:1:1" hour_second); -- 1시간:1분:1초 빼기
select date_sub("2022-01-01 00:00:00",interval "1 1:1" day_minute); -- 1일 1시간:1분 빼기
select date_sub("2022-01-01 00:00:00",interval "1 1:1:1" day_second); -- 1일 1시간:1분:1초 빼기
-- 5) to_days(date) : 주어진 날짜를 0000년-00월-00일 부터의 일수로 바꾼다.
select to_days(210629); -- 0000년-00월-00일 부터 2021년-06월29일 까지의 일 수
select to_days('2021-06-29');
-- 6) from_days(n) : 주어진 일수로부터 날짜를 구한다(to_days 반대 개념)
select from_days(738335);
/* 3. 날짜 형식 */
-- 1) date_format(date,format) format 의 정의에 따라 날자 혹은 시간을 출력한다.
-- %y : 2자리 년도 - %Y 는 4자리 년도
-- %m : 월(01~12) - %M 은 월 이름
-- %d 일(00~31)
select date_format('2021-07-01 13:54:00', '%y년 %m월 %d일');
select date_format('2021-07-01 13:54:00', '%M %Y');
-- %b : 짧은 월이름 (jan..dec)
-- %D : 영어식 접미사를 붙인 일(1st, 2nd, 3rd, etc.)
-- %a : 짧은 요일명(sun~sat)
select date_format('2021-07-01 13:54:00', '%b %D (%a)');
-- %W : 요일명, %w : 일주일의 몇 번째 요일인가(0=sunday~6=saturday)
select date_format('2021-07-01 13:54:00', '%b %D (%W)');
-- %c : 월(1~12), %e : 일(0~31), %H : 24시 형식의 시간 (00~23), %i : 분, %s : 초(00~59)
select date_format('2021-07-01 13:54:00', '%c-%e %H:%i:%s');
-- %h : 12시 형식의 시간 (01~12), %p : am 또는 pm 문자
select date_format('2021-07-01 13:54:00', '%h:%i:%s %p');
-- %r : 시분초12시 형식 (hh:mm:ss [ap]m)
select date_format(NOW(), '%r');
-- %T : 시분초 24시 형식 (hh:mm:ss)
select date_format(NOW(), '%T');
-- %U : 한해의 몇 번째 주인가(0~52) 일요일이 시작일
-- %u : 한해의 몇 번째 주인가(0~52) 월요일이 시작일
select date_format(NOW(), '%U');
-- 2) time_format(time,format) : date_format()와 비슷한 역할을 하지만 단지 시,분,초 만을 나타낼 수 있다.
select date_format('2021-07-01 13:54:00', '%h:%i:%s %p')
union all
select time_format('2021-07-01 13:54:00', '%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(1625031063);
select from_unixtime(1625031063) + 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(2378);
select sec_to_time(2378) + 0;
-- 10) time_to_sec(time) : 주어진 시간을 초 단위로 바꿔준다.
select time_to_sec('22:23:00');
-- 특정 년-월 의 마지막 날짜 구하기
select LAST_DAY('2022-02-01') as date;
-- 날짜와 날짜 사이 모든 날짜 구하기
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');
-- 날짜 기준으로 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;
5. 숫자형 함수
-- 1. 숫자형 함수 : 수 값을 인수로 받아 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 숫자 2자리를 반올림 하여 1자리 까지 표현
-- 5) TRUNCATE(n, m) : n값을 절삭하는 함수로 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
6. 문자형 함수
-- 2. 문자형 함수: 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;
-- UNION 은 합집합이며, UNION ALL 은 중복을 포함하는 합집합 이다.
-- 3) LPAD(char1, n [,char2]), RPAD(char1, n [,char2])
-- LPAD : 왼쪽에 문자열을 끼어 넣는 역할을 한다.
-- RPAD : 오른쪽에 문자열을 끼어 넣는 역할을 한다.
-- 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)
-- 문자열의 길이를 반환 한다.(한글은 한 글자에 3byte <- UTF-8)
SELECT LENGTH('마리아디비') length;
-- 6) REPLACE(char1, str1, str2)
-- REPLACE는 문자열의 특정 문자를 다른 문자로 변환 한다.
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','OR') idx; -- 2 반환
-- 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;
7. Load
-- 백업 파일(dump)을 데이터베이스로 복원하는 방법
-- 1. 복원할 데이터베이스를 생성 (없다면...)
create database employees;
show databases;
-- 2. mariadb 가 설치되어 있는 bin 폴더로 접근
-- C:/Program Files/MariaDB 11.3/bin
-- cmd 창을 열어서 다음 명령어 실행
-- [mysql | mariadb] -u [관리자게정] -p [복원할 데이터베이스 명] < [백업파일 위치]
mysql -u root -p employees < C:/test_data/employees.sql
-- ERROR at line 113: Failed to oprn file open file 'load_departments.dump', error 2
-- 발생 시 employees.sql 파일을 열어서 source 부분을 수정해준다.
-- 예> source C:/test_data/load_salaries3.dump;'DB' 카테고리의 다른 글
| 06. 정규화 (Normalization) (2) | 2024.02.27 |
|---|---|
| 04. INDEX, EXISTS, VEIW (0) | 2024.02.27 |
| 03. 서브 쿼리, JOIN, SET (0) | 2024.02.27 |
| 02. Constraint과 Relation (0) | 2024.02.27 |
| 01. DB의 기초 (0) | 2024.02.27 |
