[DATABASE] DML - SELECT
< 지난시간 정리 >
데이터베이스
=> 데이터베이스 관리 시스템 (DBMS)
- 대량/분산의 데이터를 효율적으로 관리, 운영하기 위해 사용 (파일시스템의 단점 보완)
SQL(Structured Query Language)
- DBMS에 데이터를 구축하고 관리, 활용하기 위해 사용되는 언어
[DML] - Data Manipulation Language
- 데이터 조작
- select / insert / update / delete
[DDL] - Data Definition Language
- 데이터 저장 구조 조작
- create / alter / drop
[DCL] - Data Control Language
- 데이터 보안
- grant / revoke
[TCL] - Transaction Control Language
- 동시성 처리
- commit / rollback
DB의 정의
- 여러 명의 사용자, 응용프로그램이 공유
- 동시 접근이 가능해야 함
- DB 서버 (네트위크 기능이 있어 상시 대기중임)
- DB 접속 (client)
DBMS의 종류
- Oracle
- MySQL
- MariaDB
[MariaDB]
- 서비스 안에서 자동으로 돌아가는 시스템(서버)
- MariaDB Command (클라이언트)
- 함수 확인 가능 링크
MariaDB Server Documentation
mariadb.com
원격접속
1. ip
2. port (3306)
============================================================================================
root권한 명령어
> mysql -u root -p
* mariadb 규칙
- 한 문장이 끝나면 ; 붙이기
- 대소문자 구분 없음
원격접속 명령어
> mysql -u root -p -h [접속할 IP]
데이터베이스 목록 확인
> show databases;
특정 데이터베이스 사용
> use 데이터베이스명;
=> mysql -u 아이디 -p 데이터베이스명; (원격접속부터 사용할 데이터베이스 지정까지 한번에 할 수 있는 명령어)
테이블 목록 확인
> show tables;
테이블 구조 확인
> describe 테이블명;
> desc 테이블명;
============================================================================================
인사관리
dept - 부서정보
deptno - 부서번호
dname - 부서이름
loc - 부서위치
emp - 사원정보
empno - 사원번호
ename - 사원이름
job - 직책
mgr - 관리자 사원번호
hiredate - 입사일자
sal - 급여(월급)
comm - 수당
deptno - 부서번호
salgrade - 호봉정보
grade - 호봉
losal - 급여하한
hisal - 급여상한
데이터 조회: select
select - 컬럼절 / 컬럼의 목록 / *
from - 테이블이름
where - 조건 / 행선택을 위한 조건
조건: 비교연산자, 논리연산자(and, or)
목록: in
범위: between and
> select * from emp where deptno =10; => emp에서 deptno가 10번인 내용만 가져오기
> select comm from emp where comm is null; => emp의 comm이 null인 comm만 추출
> select comm from emp where comm is not null; => emp의 comm이 null이 아닌 comm만 추출
> select sal, comm, sal*12+comm from emp where comm is not null; => 연봉 확인(null이 아닌)
[컬럼명 like]
% - 여러글자 대치 ( a 뒤나 앞이나 사이에 어떤글자가 있어도 a만 있으면 검색됨)
a%, %a, %a%
_ - 한 글자 대치 (_와 글자수가 맞아야함)
a_, _a_,
> select * from emp where ename like 'al%'; =>al로 시작하는 이름의 사원 추출
> select * from emp where ename like '%er'; => er로 끝나는 이름의 사원을 추출
> select * from emp where hiredate like '2011%'; => 2011년에 입사한 사원의 정보를 추출
> select * from emp where ename like '____'; => 사원이름이 4글자인 사원에 대한 정보
( _는 글자 수를 조절할 수 있음)
> select * from emp where ename not like '____'; => 사원이름이 4글자가 아닌 사원에 대한 정보
(like앞에 not을 붙여 표현)
job에 대한 목록 만들기
> select distinct job from emp; => distinct (중복 제거)
두 분야(job, deptno)를 묶은 후 중복을 제거
> select distinct job, deptno from emp;
limit를 걸어 상위에서 3개의 정보만 추출
> select * from emp limit 3;
시작 위치(위에서 2번째부터)를 정해 3개의 정보만 추출
> select * from emp limit 2, 3;
[정렬] - order by
오름차순
내림차순
deptno를 오름차순으로 정렬 (기본)
> select*from emp order by deptno asc;
deptno를 내림차순으로 정렬
> select*from emp order by deptno desc;
급여가 많은 사원부터 출력
> select*from emp order by sal desc;
부서별 급여로 정리
=> deptno를 먼저 정렬 후 동률일 때 sal을 정렬하여 부서별로 급여를 정리할 수 있다.
> select*from emp order by deptno,sal;
사원정보를 직책별, 이름별 정리
> select*from emp order by job,ename;
* order by n => n열의 위치를 오름차순으로 정렬
[함수]
현재 사용하고 있는 DB이름 출력
> select database();
현재 사용자 이름 출력
> select user();
상태 확인
> status;
< 수학 >
절대값
> select abs(123), abs(-123);
올림
> select ceil(4.4), ceil(4.5), ceil(4.6);
내림
> select floor(4.4), floor(4.5), floor(4.6);
반올림
> select round(4.4), round(4.5), round(4.6);
소수점 버리기
> select truncate(999.999,0);
소수점 두번째 자리까지만 출력
> select truncate(999.999,2);
백의 자리까지만 출력
> select truncate(999.999,-2);
거듭제곱 표현
=> 첫번째 인자에 대한 두번째 인자만큼의 거듭제곱
> select power(2,2);
나머지 출력
> select mod(5,2);
최대값
> select gratest(100,101,102);
최소값
> select least(100,101,102);
< 문자열 >
문자열 길이
> select length('abc');
한글 문자열 길이
> select char_length('테스트');
deptno가 10인 사람의 이름과 이름의 길이
> select ename, char_length(ename) from emp where deptno=10;
이름의 길이가 4인 사람들의 이름과 이름의 길이
> select ename, char_length(ename) from emp where char_length(ename) =4;
문자열을 계속적으로 연결
> select concat('asp','php');
출력을 문장으로 연결하여 표현 가능
> select concat(ename, '님의 직책은',job,'입니다') from emp where deptno =10;
사원목록에서
연봉은 급여 * 12 + comm
xxx님의 연봉은 xxx원 입니다. 로 출력하기
> select concat(ename, '님의 연봉은',truncate(sal*12+comm,0),'원 입니다') from emp where deptno =30;
문자열이 시작되는 위치
> select instr('mariadb database study', 'da');
사원 이름에서 해당 문자열이 시작되는 위치를 반환
> select ename, instr(ename,'er') from emp;
문자열을 원하는 길이만큼(왼쪽/오른쪽)에서 분리
> select left('mariadb database study',5);
> select right('mariadb database study',4);
문자열 중간에서 분리
> select mid('mariadb database study',5,3);
=> 인자는 문자열, 처음, 끝 순이며, 어디서부터 어디까지 분리할지를 알려줘야함
사원이름 s로 시작하는 사원에 대한 사원번호, 사원이름, 급여 출력
like --> > select empno,ename,sal from emp where ename like 's%';
mid --> > select empno,ename,sal from emp where mid(ename,1,1)='s';
instr --> > select empno, ename, sal from emp where instr(ename,'s')=1;
치환
> select replace('mariadb database study','study','스터디');
9번째 지점에서 8개의 문자열을삭제하고 '데이터베이스'를 삽입해라
> select insert('mariadb database study' , 9,8,'데이터베이스');
대문자-> 소문자
> select lcase('ABC');
소문자 -> 대문자
> select ucase('abc');
문자열 거꾸로 출력
> select reverse('abc');
문자열 왼쪽 공백 제거
> select ltrim(' mariadb study');
문자열 오른쪽 공백 제거
> select rtrim(' mariadb study ');
문자열 전체 공백 제거
> select trim(' mariadb study ');
문자열의 남는 공간을 특정 문자로 채우기
> select lpad('hi' , 4,'?'); // 문자열을 4개로 지정하고 hi 2개를 뺀 나머지 2개의 값을 ?로 대체한다. (왼쪽기준)
> select rpad('hi' ,4,'?'); // 오른쪽 기준
< 시간 >
현재 시간과 날짜를 확인
> select now();
> select curtime(); //시간만 확인
> select now(), now()+1; //현재 시간과 날짜에 1초 더함
시스템 날짜와 현재 날짜
> select sysdate(), current_timestamp();
현재 날짜에서 2일 후
> select now(), date_add(now(), interval 2 day);\
> select now(), date_add(now(), interval 2 month); //일수 뿐 아니라 달도 계산 가능
> select '2023-09-05', adddate('2023-09-05', interval 2 month); 특정 날짜의 +2달 계산 가능
현재 날짜에서 2일 전
> select now(), date_sub(now(), interval 2 day);
날짜에서 날짜 빼기
> select datediff('2023-09-01','2023-09-02');
now()에서 년도만 떼기
> select left(now(),4); //문자열을 응용
> select year(now());
> select extract(year from now());
> select extract(year from '2023-09-05');
> select extract(year from '20230905');
> select extract(year from '2023/09/05');
요일 확인하기
> select dayname(now());
언어 설정 확인
> show variables like 'lc%';
한국어로 변경 (해당 설정은 일시적인것이며, mariadb를 종료 후 다시 시작하면 초기값으로 돌아간다.)
> set lc_time_names='ko_KR';
날짜 포맷 변경
> select now(), date_format(now(), '%Y-%m-%d');
시간 포맷 변경
> select now(), date_format(now(), '%H-%i-%S');
기존 날짜와 변경한 포맷을 같이 비교
> select hiredate, date_format(hiredate, '%Y:%m:%d') from emp where deptno=10;
Q.) XXX사원번호 XXX의 입사일은 XXX년 XX월 XX일이다. (이때 부서번호는 10이다)
> select concat(empno,'사원번호', ename,'의 입사일은',date_format(hiredate,'%Y'),'년',date_format(hiredate,'%m'),'월',date_format(hiredate,'%d'),'일이다') from emp where deptno =10;
Control Flow Functions
조건문 ( 삼항연산자와 비슷한 원리)
> select if(1=2,'참','거짓');
> select ename, sal, if(sal<2000,'적음','많음') from emp where deptno=30; //응용
Q) 사원번호가 짝수이면 '짝수', 홀수이면 '홀수'로 나타낼 수 있도록 함
> select empno,ename, if(empno%2=0,'짝수','홀수')from emp;
null 값 계산이 안되는 것을 해결하기 위해 다른 값으로 변경하기
> select sal, comm, sal*12+ifnull(comm,0) from emp where deptno=30;
case when..then..else end;
db에서의 switch case
> select case 1
-> when 1 then 'one'
-> when 2 then 'two'
-> else 'more'
-> end;
* 긴 sql문은 sql문서를 따로 만들어 놓은 후 저장 경로를 찾아 실행하는 방식으로 사용한다.
> source c:\mariadb\ex01.sql
==> visual studio code로 작성 후 실행은 Command prompt로 하기
각 직급 한글로 표현하기
-> empno, ename, job, sal을 출력하는데 sal은 '현재급여'로 출력하고 deptno에 따른 인상급여 후 end에 그 결과를 표현할 '인상급여'라는 이름을 가진 곳에 표현한다.
단일행 함수 입력 -> 값
집합(복수행) 함수 여러 개의 입력 -> 한 개의 값 ex)합계, 평균, 개수, 분산
행(데이터)의 개수 - count
> select count(*) from emp; // 모든 데이터에 대한 개수
> select count(sal), count(comm) from emp; //null값을 빼고 계산한다. (의미있는 데이터에 대한 개수)
> select count(sal)sal, count(ifnull(comm,0))comm from emp; //arias를 사용하여 표시 테이블의 컬럼 명 지정 가능
// null값을 0으로 지정하여 자료의 개수를 셀 수 있도록 하였다.
그룹화 함수를 사용할 때 낱낱의 데이터를 뽑아내면 에러가 날 수 있다.
에러) > select ename, max(sal) from emp; //sal에서는 max가 추출되지만 해당 max에 맞는 이름이 추출되진 않는다.
* 평균 계산할 때 주의
null개수를 포함하지 않은 평균
=> 그냥 comm을 count하게 되면 null개수를 빼고 count하기 때문에 평균에 문제가 생길 수 있다.
> select sum(comm)/count(comm) from emp;
null개수를 포함한 평균
> select avg(ifnull(comm,0))from emp;
[그룹으로 묶어 출력하기]
select 컬럼명 / 그룹함수
from 테이블명
group by 컬럼명
그룹별 사원수
> select deptno, count(*)
from emp
group by deptno;
부서별 최고급여
> select deptno, max(sal)
from emp
group by deptno;
[그룹의 그룹화]
group by => ~ 을 기준으로 그룹을 지어라
특정 조건으로 필터링 하고 싶을 때는 HAVING을 사용
{연습문제}
1. 데이터베이스의 목록을 확인할 수 있는 SQL 문장을 기술하십시오.
> show databases;
2. 테이블의 목록을 확인할 수 있는 SQL 문장을 기술하십시오.
> show tables;
3. 테이블의 구조를 확인할 수 있는 SQL 문장을 기술하십시오.
> desc emp;
4. 사원 테이블에서 직원들의 연봉(SAL*12+comm)을 계산하여, 컬럼명은 "사원 연봉"으로 출력하는 SQL 문장을 기술하십시오.
> select sal,ifnull(comm,0), sal*12 + ifnull(comm,0)'사원 연봉' from emp;
* 현재 사용하는 있는 데이터베이스의 이름을 출력하는 SQL 문장을 기술하십시오.
> select database();
* 현재 사용자 이름을 출력하는 SQL 문장을 기술하십시오.
> select user();
5. 사원 테이블을 이용하여 다음과 같은 결과를 얻을 수 있는 SQL 문장을 기술하십시오.
> select concat(ename,'의 업무는', job,'이고 급여는', truncate(sal,0), '만원입니다.')from emp;
사원정보
-----------------------------------------------------------------------
SMITH의 업무는 CLERK이고 급여는 800만원입니다
ALLEN의 업무는 SALESMAN이고 급여는 1600만원입니다
WARD의 업무는 SALESMAN이고 급여는 1250만원입니다
JONES의 업무는 MANAGER이고 급여는 2975만원입니다
MARTIN의 업무는 SALESMAN이고 급여는 1250만원입니다
BLAKE의 업무는 MANAGER이고 급여는 2850만원입니다
CLARK의 업무는 MANAGER이고 급여는 2450만원입니다
SCOTT의 업무는 ANALYST이고 급여는 3000만원입니다
KING의 업무는 PRESIDENT이고 급여는 5000만원입니다
TURNER의 업무는 SALESMAN이고 급여는 1500만원입니다
ADAMS의 업무는 CLERK이고 급여는 1100만원입니다
JAMES의 업무는 CLERK이고 급여는 950만원입니다
FORD의 업무는 ANALYST이고 급여는 3000만원입니다
MILLER의 업무는 CLERK이고 급여는 1300만원입니다
6. 사원 테이블에서 입사일이 2011년도인 사원의 사원번호, 사원이름, 입사일, 업무, 급여를 검색하는 SQL 문장을 기술하십시오.
> select empno, ename, hiredate, job,sal from emp where year(hiredate)=2011;
7. 사원 테이블에서 입사일이 2011년이고 업무가 'SALESMAN'이 아닌 사원의 사원번호, 사원이름, 입사일, 업무, 급여를 검색하는 SQL 문장을 기술하십시오.
> select empno, ename, hiredate, job,sal from emp where year(hiredate)=2011 and job not like 'SALESMAN';
8. 사원 테이블의 사원번호, 사원이름, 입사일, 업무, 급여를 급여가 높은 순으로 정렬하고, 급여가 같으면 입사일이 빠른 사원으로 정렬하는 SQL 문장을 기술하십시오.
> select empno, ename, hiredate, job,sal from emp order by sal desc ,hiredate asc;
9. 사원 테이블에서 사원이름의 세 번째 알파벳이 'N'인 사원의 사원번호, 사원이름을 검색하는 SQL 문장을 기술하십시오.
> select empno,ename from emp where ename like '__N%';
10. 사원 테이블에서 연봉(SAL*12)이 35000 이상인 사번, 사원명, 연봉을 검색하는 SQL 문장을 기술하십시오.
> select empno, ename, sal*12 from emp where sal*12>=35000;
11. 사원 테이블의 사원명에서 2번째 문자부터 3개의 문자를 추출하여 출력하는 SQL 문장을 기술하십시오.
> select mid(ename,2,3) from emp;
12. 사원 테이블에서 입사일이 12월인 사원의 사번, 사원명, 입사일을 검색하는 SQL 문장을 기술하십시오.
> select empno, ename, hiredate from emp where date_format(hiredate,'%m')=12;
* group by 내용 확실하게 알 수 있도록 하기 (아직 헷갈림)