[SQL] 문법 정리

1. 관계형 DB(RDB)
관계형 DB(RDB)
- 정형화 되어있고, oracle mysql, maria db 등등이 있음                                                                          - 2차원 테이블 형태로 데이터를 관리함	비정형, mongoDB, {key: value} 형식임
DBMS에 독립적: ANS1 SQL
DQL, DML+TCL, DDL
2. Data 종류
수치: 정수, 실수
문자
날짜: ‘A’, ‘ABC’, char (byte): 고정, 최대 2000 byte, varchar2(byte): 가변, 최대 4000 byte
3. DQL
projection: 컬럼 선택 (select 컬럼명 선택)
selection: 행 선택(where 절)
join: 연결하는 키 foreign key(참조키)
4. Selection
select	*, 컬럼명(쉼표), 연산, 연결(|| or concat)
from
where
여러개 선택	in(값, 값2, 값3)
비슷한 값 검색	- like( %: 0 개 이상 문자 치환) - _: 반드시 한개와 치환
5. null 값
연산 시 null 반환	nvl(컬럼, 대체 값)
6. 제약조건
table data 의 무결성 보장
primary key	자동으로 index 생성  중복되는 이름이 아닌 유니크한 key로 찾음
unique	null 허용(자동으로 Index 생성)
not null	제약조건을 변경하는 문법
check	비즈니스 규정
foreign key	참조용

1. 함수
문자	길이, 첫글자, 대문자, 공백제거, 채우기, 치환, 등
날짜	DBMS에 의존적인 함수  - 남은 개월 연산  - 현재 날짜
수치	반올림, 절삭

-- 1. 테이블 정보 확인 (oracle에서는 show tab과 같음)

show table status

 

-- 2. 모든 컬럼 보기

select *

from emp

 

-- 3. 보고싶은 컬럼만 select

select deptno, loc

from dept

 

-- 4. 컬럼명 별칭 alias (띄어쓰기해서 as 쓰고 싶을 때는 "부서 번호" 사용),show table status;

 

select deptno, loc as 부서_번호, Loc 위치

from dept

 

-- 5. 연산하기

 

select empno, sal, sal+100

from emp

 

-- 6. 연결하기

 

select concat(ename, sal)

from emp;

 

-- 8. 중복값 제거

-- select distinct 컬럼명,컬럼명 as 별칭, 컬럼 || 컬럼(값),  컬럼 + 100,

-- oracle 에서는 from 절 필수

-- select ~ from 절은 항상 모든 레코드 출력된다. ==> 항상 12개가 나옴

select distinct(job) 

from emp;

 

-- 9. null 값은 연산시 항상 null 이 나옴

-- Null 값은 임의의 값으로 변경해서 연산해야 됨(함수)

-- nvl 함수를 사용시, nvl(컬럼명, 값(null인 경우에 치환할 값))

select empno, sal, comm, (sal*12) +  + nvl(comm, 0) as yearly

from emp

-- 기본적으로 컬럼은 null 값을 갖게 되는데 강제적으로 NULL 값을 허용하지 않을 수 있음

-- 제약조건 type(데이터 무결성을 위함- 데이터를 믿고 사용할 수 있게끔 보장하기 위함): primary key(레코드 식별), unique(유일한 값,null 허용), not null(null 허용 x, 반드시 값을 가져야 하며 record 식별이 가능), check(비즈니스 규칙 정하기), foreign key(참조키: 두개 이상의 테이블을 연결할 때 씀)

-- 컬럼에서 Null 조회 : isNull 연산자 이용, is not null(부정)

-- 정렬(오름차순, 내림차순): 오라클에서는 null을 최댓값으로 처리하게됨

-- not  null  추가 시 Null 허용 방지용 방지

 

-- 10. where 문 사용

select empno, ename, sal

from emp

where sal = 800

 

select empno, ename, sal

from emp

where ename = "ford"

 

 

select empno, ename, sal, Hiredate

from emp

where HIREDATE= "80/12/17"

 

 

-- 범위 찾을 시, between a and b: a와 b가 포함됨

select empno, ename, sal, Hiredate

from emp

where sal between 800 and 1000

 

 

-- 한꺼번에 여러개 지정, 내부적으로 또는 으로 연산됨

select empno, ename, sal

from emp

where sal in (800,1500)

 

 

-- null 값 찾기

select empno, ename, sal, hiredate

from emp

where comm in null;

 

 

-- 비슷한 값 찾기 like + % 또는 

select empno, ename, sal, hiredate

from emp

where ename like 'A%' --A로 시작하는 사원 조회

-- where ename like '%A%' --A를 포함하는 사원 조회

-- where ename like '%A' --S로 끝나는 사원 조회

 

-- 두번째가 A인 사원 조회

select empno, ename, sal, hiredate

from emp

where ename like '_A%' --A로 시작하는 사원 조회

 

-- 문제: 이름이 5글자고 마지막은 N으로 끝나는 사원 조회

select empno, ename, sal, hiredate

from emp

where ename like '____N' -- A로 시작하는 사원 조회, 뒤에 %는 붙지 않음 왜냐하면 뒤에 글자는 없기 때문. 

 

-- 논리 연산자: and, or, not

select empno, ename, sal, hiredate

from emp

where job = 'salesman' and sal >= 1500

 

select empno, ename, sal, hiredate

from emp

where job = 'salesman' or sal >= 1500

 

-- not in 부정 포함

SELECT empno, ename, sal

from EMP

where NOT ename = 'FORD';

 

SELECT empno, ename, sal, hiredate

from EMP

where ename IN ('SMITH','FORD');

 

SELECT empno, ename, sal, hiredate

from EMP

where not ename IN ('SMITH','FORD');

 

-- 정렬(기본적으로 오름차순임)

SELECT empno, ename, sal, hiredate

from EMP

order by sal desc

 

-- order by의 뒤에 올 수 있는 것 중 3가지가 있음 1. as

SELECT empno, ename, sal, hiredate as salary, hiredate

from EMP

order by sal desc

 

-- order by 컬럼명

SELECT empno, ename, sal as salary, hiredate

from EMP

order by salary -- 컬럼명 써도 가능함

 

-- order by 컬럼순서

SELECT empno, ename, sal as salary, hiredate

from EMP

order by 3 -- 컬럼명 써도 가능함

 

 

-- 4. 다중 정렬 : ordery by 컬럼명 1, 컬럼명 2

SELECT empno, ename, sal as salary, hiredate -- 컬럼명 1으로 정렬하고 같다면 컬럼 2로 다시 정렬

from EMP

order by salary, hiredate desc 

-- order by 3,4 desc 도 가능함

 

 

 

 

-- 230523

-- 1. 단일행 함수 - 문자관련

-- dual = 더미 테이블, 이때 존재하는 임의의 table 관련 작업이 아닌 경우(연산)에 사용됨(for test용도)

selet lower('MANAGER'), upper('manager') from dual

 

select empno, ename, deptno

from emp

where lower(ename) = 'smith'

 

-- 2. 문자열 연결 

select concat('oracle','server') from dual

 

-- 3. 문자열 끼우기 

select lpad('miller',10), rpad('miller',10) from dual

select lpad('miller',10,'*') from dual

select ename, lpad(ename, 15, '*') from emp

 

-- 4. 부분열 반환 

Select substr('000101-3234232',8,2) from dual -- 8번째에서 2개 내놔 

 

-- 5. 문자열 길이 

select length('000101-3234232')

 

-- 6. 함수 중첩

select length(substr('00101-3234232',8))

 

-- 7. 문자열 치환 (마지막에 변경 문자를 안 주면 공백으로 지정)

select replace('JACK and JUE','J','BL')

 

-- 8. 특정 문자의 위치 반환(특정 문자의 위치 반환)

select instr('miller','l')

 

-- 9. 공백 제거  (특정 문자는 제거 불가함)

select ltrim(' millerm ')

select rtrim(' millerm ')

 

select trim(leading 1 from 111123456111) -- 앞에 1

select trim(trailing 1 from 1111234451111) -- 뒤에 1

select trim(both 1 from 1112341111)

 

 

select '891202-1234567' from dual;

select replace('891202-1234567', '234567', '******')

select replace('891202-1234567',1,8,'*')

 

select rpad('891202-1234567',8,'*')

 

-- 10. 올림 값

select ceil(10.1) 

-- 내림값

select floor(10.1)

-- 나머지 구하기

select mod(10,3)

 

select round(456.66)

select round(456.66,-2) 0.1 -- 기준으로 왼쪽 값 500

 

-- 11. 절삭

select truncate(456.789,-2) -- 400

select truncate(456.889, 0) -- 456

 

-- 12. 부호 식별(양수면 1, 음수면 -1, 0이면 0)

select sign(100), sign(-10), sign(0) 

 

-- 13. 현재 날짜 구하기 

select sysdate()

 

select SYSDATE(), NOW() from DUAL;

SELECT CURDATE(),  CURRENT_DATE(),  CURRENT_DATE FROM DUAL;

SELECT CURTIME(),  CURRENT_TIME(),  CURRENT_TIME FROM DUAL;

 

-- 14. 두 날짜 사이의 일수 계산 

select datediff('2023-01-04','2022-01-04') 

 

-- 15. 날짜에 날짜 더하기 

select date_add('2008-01-02', interval 1 day) as A1,

date_add('2008-01-02', interval 1 month) as A2,

date_add('2008-01-02', interval 1 year) as A3,

NOW() as B4,

DATE_SUB(NOW(), INTERVAL 10 MINUTE) as B5,

DATE_SUB(NOW(), INTERVAL 2 HOUR) as B6

 

-- 16. 월의 마지막 날 반환

select last_day(sysdate()) from dual

 

-- 17. 날짜에서 특정 정보만 얻기

select now(),

extract(second from now())

 EXTRACT(MINUTE FROM NOW()),

EXTRACT(HOUR FROM NOW()),

EXTRACT(DAY FROM NOW()),

EXTRACT(MONTH FROM NOW()),

EXTRACT(YEAR FROM NOW()),

EXTRACT(YEAR_MONTH FROM NOW())

 

 

-- 18. 변환 함수 문자 -> 숫자

select CAST('100' AS INT )+100

 

select CAST(1000 AS CHAR) -- '1000'

 

-- 19. 문자를 날짜로 변환

select str_to_date('2020-08-08','%Y-%m-%d')

select str_to_date('2020년03월 05일')

 

-- 20. 날짜를 문자로 변환 

select now(),

date_format(now(),'%Y/%m/%d'),

   DATE_FORMAT(NOW(),'%Y/%m/%d'),

       DATE_FORMAT(NOW(),'%Y년 %m월 %d일'),

       DATE_FORMAT(NOW(),'%H:%i:%S'),

DATE_FORMAT(NOW(),'%Y')

 

--  

 

-- 21. case 함수

select empno, ename, sal, job,

case job when 'analyst' then sal*1.1

-- case로 새로운 열 만들때 

end '급여'

from emp

 

 

select empno, ename,

case when sal >=0 and sal<=1000 then 'A'    

end "등급"

from emp

 

select empno, ename, 

       CASE when sal >=0 and sal <=1000 then 'E' 

            when sal >1000 and sal <=2000 then 'D'

            when sal >2000 and sal <=3000 then 'C'

            when sal >3000 and sal <=4000 then 'B'

            when sal >5000 and sal <=5000 then 'A'

      END "등급"

from emp;

 

 

-- 그룹함수

select max(sal), min(sal), sum(sal), avg(sal), count(*)

from emp

 

-- group by 절 

select deptno, sum(sal)

from emp

group by deptno

 

select deptno, sum(sal)

from emp

 

group by deptno

having sum(sal)>8000