활용
◎ 부서가 20이고, 업무가 analyst 직원의 이름, 부서, 급여, 입사일자 조회
- SELECT empno, ename, sal, hiredate, deptno FROM emp WHERE job = 'ANALYST' AND deptno = 20;
◎ 급여가 1500이상 2500이하 받는 직원의 이름, 부서, 업무, 급여를 조회. 단, 급여가 많은 순으로 조회
- SELECT ename, deptno, hiredate, sal FROM emp WHERE 1500 <= sal and sal <= 2500 ORDER BY sal DESC;
◎ 업무가 clerk, salesman, analyst인 직원의 이름, 부서, 업무, 급여를 조회
- select ename, deptno, sal, job From emp where job = 'CLERK' or job = 'SALESMAN' or job = 'ANALYST';
IN을 사용한 수식 (같다라는 조건에서 IN을 사용할 수 있음)
- select ename, deptno, sal, job form emp where job in ('CLERK', 'SALESMAN', 'ANALYST');
◎ 1981에 입사한 사람의 이름, 업무, 입사일자를 조회 (like를 사용 시)
- select ename, deptno, hiredate from emp where hiredate > '82*1*1';
- select ename, depno, hiredate form emp where hierdate like '81%';
◎ 사번이 7902, 7788, 7566인 사원의 이름, 업무, 급여, 입사일자를 조회 (not 사용시)
- select ename, job, sal, hiredate, empno from emp where empno = '7902' or empno = '7788' or empno = '7566';
- select ename, job, sal, hiredate, empno from emp where empno not in (7902, 7788, 7566);
◎ 업무가 president 또는 salesman이고 급여가 1500인 직원의 이름, 급여, 업무, 부서번호를 조회 (or과 and는 and가 우선 순위로 잡힘)
- select ename, empno, deptno, sal, job from emp where job = 'PRESIDENT' or job = 'SALESMAN' and sal = '1500';
◎ 가장 최근에 입사한 직원의 이름, 부서, 업무, 입사일자를 조회
- select ename, deptno, job, hiredate from emp order by hiredate asc;
◎ 같은 부서내에서 같은 업무를 하는 직원의 급여가 많은 순으로 조회
- select ename, empno, job, deptno from emp order by empno, job, sal desc;
◎ 커미션이 급여보다 10%이상 많은 직원의 급여가 많은 순으로 조회
- select ename, empno, job, sal from emp where comm > sal*1.1;
◎ 이름에 L자가 2개 있고 30번 부서이거나 직속상사가 7782원인 직원의 이름, 부서, 직속상사 조회
- select ename, empno, mgr, job from emp where emame like '%l%l%' and deptno ='30' or mgr='7782';
Function (데이터베이스 함수)
1. single Row Function : 단일행함수
- string function : 문자함수 중 주로 사용하는 함수들.
lower(), upper() - 대소문자 바꿔주는 함수
substr() - 여러문자 중 일부분을 추출할 때 사용하는 함수
length() - 문자열의 길이 알아낼 때
instr() - 문자열의 위치
iltrim(), rtrim(), trim() - 공백을 제거할 때 사용하는 함수 (양쪽 공백, 문자 공백 등)
translate(), replace() - 문자 치환. (문자 바꿔치기)
chr(), ascii() - 아스키 코드를 문자로 문자를 아스키코드로 바꿔주는 함수
◎ 이름이 smith인 직원의 이름, 부서, 급여를 조회한다. (단, 대문자 소문자 구별없이 검색할 수 있도록 한다.)
- select ename, deptno, sal from emp where ename = upper('smith');
◎ 아래의 주민번호에서 성별에 해당하는 값만 추출. "901212-1000000"
- select substr('901212-1000000', 8, 1) from dual;
- select substr('901212-1000000', 8) from dual;
*문자열의 길이
- select length(ename) from emp;
*문자열의 위치
- select instr('MILLER', 'L') from dual;
*문자열의 위치
- select instr('MILLER', 'K') from dual;
- select instr('MILLER', 'L', 1, 1) from dual;
- select instr('MILLER', 'L', 4, 1) from dual;
*문자 제거 (하지만 문자보다는 공백을 제거하는데 사용)
- select ltrim('MILLER', 'M') FROM dual;
- select ltrim( 'MILLER', 'M') FROM dual;
*문자열의 재배치
- select translate('MILLER', 'L', '*') from dual;
- select replace('MILLER', 'L', '*') from dual;
- select translate(sal, '0123456789', '영일이삼사오육칠팔구')from emp; - 문자로 번역
- select replace(sal, '0123456789', '영일이삼사오육칠팔구')from emp; - 숫자로 번역
- select replace('JACK and JUE', 'J', 'BL') from dual; 문자열을 재배치하여 문자를 변경함.
- select translate('JACK and JUE', 'J', 'BL') from dual;
*아스키 코드 값 변경
- select chr(65),chr(97) from dual; -문자값으로 변경
- select ascii('a'), ascii('a') from dual; -숫자값으로 변경
2. number function
- round(숫자 -반올림), trunc(숫자 - 반올림을 안함), floor(소수점 자름), ceil(무조건 소수점 올림) = 정수 처리. (반올림을 잘라 버리는 특징)
- mod() 소수점을 반환
- power() 제곱
- sign() 숫자부호를 반환
- select round(4567.678), round(4567.678,2), round(4567.678, -2) from dual;
- select trunc(4567.678) from dual;
- select floor(4567.678), ceil(4567.278) from dual;
- select mod(10, 3) from dual; // 나머지
- select power(2, 10) from dual: // 거듭제곱
- select sign(1000, sign(-15), sign(0) from daul; // 부호 표시
3. date function
- sysdate
- months_between()
- add_months()
- next_day()
- last_day()
- round(), trunc()
- select sysdate from dual; = 현재 날짜를 보여줌.
- select sysdate+100 from dual; = 금일 날짜에서 +100일을 더한 날짜를 알려줌.
- select months_between(sysdate, '2019/2/1') from dual; 금월 단위를 표현.
- select add_months(sysdate, 21) from dual; = 월 단위를 + 함.
- select next_day('2023/9/8','금') from dual; = 해당 날짜에 요일을 확인함
- select last_day(sysdate) from dual; = 금월의 마지막 일자를 확인
- select round(sysdate) from dual; = 정오를 기준으로 오늘과 내일 날짜를 확인해줌.
- select round('2024/7/5' = 문자열은 시스데이트를 할 수 없음) from dual; = 결과값이 안나옴.
- select round(to_date('2024/7/5')) from dual; = to_date 함수를 사용하여 써주는 경우 문자열이 숫자열로 변경이 됨.
- select round(to_date('2024/8/5'), 'month') from dual; = 해당 월을 다음 월로 보여줌.
- select round(to_date('2024/8/5'), 'year') from dual; = 연도를 다음 년도로 올려줌.
convert Function
-cast(), to_char(), to_date(), to_number()
- select ename, sal, to_char(sal) from emp;
- select ename, sal, to_char(sal. '$999,999')from emp;
4 . etc
nvl(필드명, 값)
decode(필드명, 값 = 참, 거짓, 참, 값, 참, 값, 참, 거짓)
*직원의 이름, 급여, 커미션, 총급여 조회.
- select ename, sal, comm, sal+nvl(comm, 0) as 총급여 from emp;
*현재 업무가 salseman이라면 "영업"이라고 출력하고 그렇지 않으면 "일반" 이라고 출력.
-select ename, sal, decode(job, 'SALESMAN', '영업', '일반') from emp;
Aggregation Function : 다중행(집합행) 함.
sum(), avg(), max(), min(), count()
- select의 확장 문법 : 그룹화 기능
group by 필드명[, 필드명, ...] having =
* 업무가 salesman 인 직원들의 급여 평균, 최고 급여, 최저 급여, 합계를 조회
select avg(sal), max(sal), min(sal), sum(sal) from emp where job = 'SALESMAN';
* 직원이 총 몇명인가?
select empno from emp;
select count(empno) from emp;
select count(job) from emp;
select count(*) from emp;
select count(comm) from emp; 4명
- distinct : 중복된 값 제거
select distinct job from emp;
* 부서별로 급여 평균, 최저 급여, 급여 합를 조회
select distinct deptno from emp;
- select avg(sal), min(sal), max(sal), sum(sal) from emp where deptno = 10;
- select avg(sal), min(sal), max(sal), sum(sal) from emp where deptno = 20;
- select avg(sal), min(sal), max(sal), sum(sal) from emp where deptno = 30;
select avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno;
* 전체 급여의 합계가 5000을 초과하는 업무에 대해 급여 합계를 조회
- select sum(sal) from emp group by job having sum(sal)>5000;
* 전체 급여의 합계가 5000을 초과하는 업무에 대해 급여 합계를 조회 (salesman은 제외)
- select job, sum(sal) from emp where job != ' SAAESMAN' group by job having sum(sal) > 5000;
DML
insert
- insert into 테이블명 (필드명, ...)
- insert into 테이블명 values(값 ...);
update
- update 테이블명
- set 필드명 = 값[, 필드명=값, ...]
- where 조건문:
delete
-delete from 테이블명:
-delete from 테이블명 where 조건문;
create table tbltest (id number, name varchar2(10), hiredate date);
테이블을 만든다 / id와 숫자를 넣고, 이름을 넣은 후, varchar 문자를 넣음
MySQL 문자열 함수
FunctionDescription
| ASCII | Returns the ASCII value for the specific character |
| CHAR_LENGTH | Returns the length of a string (in characters) |
| CHARACTER_LENGTH | Returns the length of a string (in characters) |
| CONCAT | Adds two or more expressions together |
| CONCAT_WS | Adds two or more expressions together with a separator |
| FIELD | Returns the index position of a value in a list of values |
| FIND_IN_SET | Returns the position of a string within a list of strings |
| FORMAT | Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places |
| INSERT | Inserts a string within a string at the specified position and for a certain number of characters |
| INSTR | Returns the position of the first occurrence of a string in another string |
| LCASE | Converts a string to lower-case |
| LEFT | Extracts a number of characters from a string (starting from left) |
| LENGTH | Returns the length of a string (in bytes) |
| LOCATE | Returns the position of the first occurrence of a substring in a string |
| LOWER | Converts a string to lower-case |
| LPAD | Left-pads a string with another string, to a certain length |
| LTRIM | Removes leading spaces from a string |
| MID | Extracts a substring from a string (starting at any position) |
| POSITION | Returns the position of the first occurrence of a substring in a string |
| REPEAT | Repeats a string as many times as specified |
| REPLACE | Replaces all occurrences of a substring within a string, with a new substring |
| REVERSE | Reverses a string and returns the result |
| RIGHT | Extracts a number of characters from a string (starting from right) |
| RPAD | Right-pads a string with another string, to a certain length |
| RTRIM | Removes trailing spaces from a string |
| SPACE | Returns a string of the specified number of space characters |
| STRCMP | Compares two strings |
| SUBSTR | Extracts a substring from a string (starting at any position) |
| SUBSTRING | Extracts a substring from a string (starting at any position) |
| SUBSTRING_INDEX | Returns a substring of a string before a specified number of delimiter occurs |
| TRIM | Removes leading and trailing spaces from a string |
| UCASE | Converts a string to upper-case |
| UPPER | Converts a string to upper-case |
[국비 코딩 수업 12일차!] 3. 데이터 베이스 활용
활용 1. 부서별로 직원의 수를 조회 select count(deptno) from emp; 2. 부서별로 급여평균, 최고급여를 조회하는데, 단 급여평균이 높은순으로 조회 select avg(sal), max(sal) from emp decs; 3. 오늘부터 12월 25일까
gestyou.tistory.com
댓글