- SQL Developer 설치및 생성 관련
-
제약조건
- primary key:
레코드를 식별하기 위한 용도.
내부적으로 unique제약조건과 not null 제약조건을 포함
자동으로 인덱스(index)가 생성됨
- unique
컬럼에 유일한 값을 저장하기 위한 용도. null 값 포함 가능. 자 동으로 인덱스(index)가 생성됨.
- not null
컬럼에 반드시 값을 저장해야 하는 용도.
- check
컬럼에 임의의 조건에 일치하는 데이터만 저장하기 위한 용도.
예> 학년이 1학년만 저장, 성별이 ‘남’ 만 저장등
- foreign key
하나의 테이블에서 다른 테이블을 참조하기 위해 사용
-
sql문 종류
- DQL(질의어): select
- DML(데이터 조작어) : insert(데이터 입력) / update / delete / merge(데이터 병합)
- DDL(데이터 정의어) :
1) create(데이터베이스 객체 생성)
* 오라클 객체 : table / view(복잡한 sql 간소화, 보안)/ sequence / index/ synonym(동의어)
2) alter (수정)
3) drop (삭제)
4) rename (객체 이름 변경)
5) truncate (객체 정보 절삭)
* DDL은 TCL과 관련이 없다. 자동 commit
- TLC: DML과 같이 엮어서 기억해야함
- DCL : grant / revoke
- 테이블 구조
-숫자
number(6) -> 자릿수 의미 : 0 -999999
number(8,2) -> 8: 소수점 포함 전체 자리, 2: 소수점 자리
- 문자
가변길이: varchar2(n)
고정길이: char(n)
ex) n = 3일때 만약 'a'를 저장한다면 varchar2는 하나의 크기로 만들어짐 /
char은 상관없이 3개로 만들어짐
-날짜
DATE
- employees에서 department_id -> foreign key
- SELECT
SELECT [DISTINCT] {*, column [Alias], . . .}
FROM 테이블명;
- 별칭 ( alias)
select last name as 이름
*as 생략가능select last name 이름
- 별칭은 띄어쓰기가 포함되면 에러나기 때문에 ""를 붙여서 써준다. ex) "이 름" => 오라클은 "를 별칭에서만 사용한다! 문자/문자열은 '를 사용
- NULL
- NVL(컬럼명, 값) 사용
=> 컬럼이 NULL이면 해당값을 사용하도록
ex)
SELECT last_name 이름, salary 월급, commission_pct 수수료,
salary * 12+NVL(commission_pct,0) as 연봉
FROM employees;
=> 연봉이 null인 것들은 계산이 됨
- 연결연산자
- ||
=> SELECT 컬럼명 || ‘값’ || 컬럼명 || ‘값’ || 컬럼명
FROM 테이블명;
-
중복 데이터 제거
- DISTINCT
: 중복 제거
- WHERE
- 비교 연산자: 문자 / 날짜 값비교 가능
=> WHERE hire_date BETWEEN '07/01/01' AND '08/12/31';
- 문자와 날짜는 ' 를 사용해야한다.
- 식별자는 대/소문자를 가리지 않지만 값은 대/소문자를 구분해야한다.
- IN
: 또는으로 계산 => OR
ex) WHERE employee_id IN ( 100,200,300 );
=> WHERE employee_id=100 OR employee_id=200 OR employee_id=300;
- LIKE 연산자
: %(0개 이상의 문자(열)과 대체) // _ (반드시 1개 문자와 대체)
ex)사원들 중에서 이름이 'ai' 글자를 포함하는
=> WHERE last_name LIKE '%ai%';
- ESCAPE
* like '%$%%' escape '$' => $뒤에 있는 문자는 문자로 사용하는 것임
- NOT / NULL
- NULL:
정렬시 가장 큰 값
기본적으로 null 허용 -> 허용하지 않도록 하는게 not null =====> 기능이 변경된것임
is null / is not null 수정(modify)하는 형태로 not null 제약조건 핸들링 가능
- OR / AND : AND가 우선순위가 더 높다.
=> 괄호 이용해서 or 먼저 가능
- ORDER BY
- ORDER BY: 정렬
SELECT [DISTINCT] {*, column [Alias], . . .} //컬럼명, 별칭, 순서를 적을 수 있음
FROM 테이블명
[WHERE 조건식]
ORDER BY { column, 표현식} [ASC|DESC];
-> 컬럼명 대신 순서값을 사용해도 된다.
EX) SELECT employee_id,last_name,job_id,salary as "월급“
FROM employees
ORDER BY 4 DESC; // 4-> salary
- 다중 데이터 정렬
: 같은 것일때 같은것끼리 정렬 가능
ex) SELECT employee_id,last_name,salary,hire_date
FROM employees
ORDER BY salary DESC, hire_date; // salary가 같으면 hire_date로 정렬
=> ORDER BY 3 DESC, 4; //이렇게도 표현 가능
- 함수
- 단일행 함수: 행 단위로 적용
문자함수 / 숫자 함수/ 날짜 함수/ 변환 함수 / 일반 함수
- 문자 처리 함수
1) INITCAP 함수
: 각 단어의 첫 문자를 대문자로 바꾸고 나머지는 소문자로 변경
2) UPPER / LOWER
: 대문자로 변경 / 소문자로 변경
ex) WHERE UPPER(last_name)='KING'; //대소문자 상관안하고 KING을 찾을 수 있음
3) CONCAT
: 두 문자열을 연결함
ex) SELECT CONCAT( last_name, salary)
4) LENGTH
: 길이를 가져옴
5) INSTR
: 문자열에서 특정 문자가 나타나는 위치를 가져옴
INSTR( 컬럼명|표현식, 검색값, [m ,n] )
ex) INSTR('MILLER' , 'L', 1 , 2 ) // 1번부터 L을 찾다가 두번째 나오는 값의 위치를 반환함
-> 4
=> 찾는 것이 없으면 0이 반환됨
6) SUBSTR
: 문자열에서 일부분의 문자열을 추출할 때 사용
8번째 글자에서 1글자 뽑아오기 - SUBSTR('900303-1234567' , 8 , 1 ) //1이면 남자, 2면 여자 -> 성별 구분 가능
7) REPLACE
: 특정 문자열을 치환할 때 사용
EX) SELECT REPLACE('JACK and JUE' , 'J' , 'BL' )
8) LPAD
:문자열을 오른쪽 정렬 후에 특정 문자를 왼쪽부터 지정한 문자로 채우는 함수
EX) SELECT LPAD('MILLER' , 10 , '*' ) ===> ****MILLER
* RPAD 는 반대
9) LTRIM
:특정 문자를 삭제하기 위한 용도로 사용
EX) LTRIM('MILLER' , 'M' )
*지정하지 않으면 공백이 지워짐
10) TRIM
:특정 문자를 삭제하기 위한 용도로 사용
TRIM( LEADING 'str' FROM 컬럼명|표현식 ) // 왼쪽 지움
TRIM( TRAILING 'str' FROM 컬럼명|표현식 ) //오른쪽 지움
TRIM( BOTH 'str' FROM 컬럼명|표현식 ) // 양쪽 지움
EX) SELECT TRIM( '0' FROM '0001234567000' ) => 1234567
- 숫자 처리 함수
1) ROUND
: 지정한 자리 수 이하에서 반올림한 결과를 반환
EX) SELECT ROUND( 456.789, 2 ) //두번째 자리에서 반올림
2) MOD
: 나누기 연산을 한 후에 몫이 아닌 나머지를 반환
EX) MOD( 10 , 3 ) => 1
3) SIGN
: 지정된 값이 양수인지 음수인지 또는 0인지 판단
EX) SELECT SIGN( 100 ) , SIGN(-20) , SIGN(0) ===> 1 -1 0
- 날짜 함수
-> 7byte 처리 => 연산가능
1) SYSDATE
: DB서버에 설정된 날짜를 반환
EX) SELECT SYSDATE
* SYSTIMESTAMP => 시/분/초도 나옴
- 연산가능
=> SELECT SYSDATE 오늘, SYSDATE+1 내일, SYSDATE-1 어제
2) MONTHS_BETWEEN
: 날짜와 날짜 사이의 개월 수를 반환
EX) MONTHS_BETWEEN( sysdate, hire_date )
* 소수점 이하를 포함하지 않은 개월 수만 출력하기 위해서 다음과 같이 TRUNC 함수를 사용
=> SELECT last_name, hire_date, TRUNC(MONTHS_BETWEEN(sysdate, hire_date)) "근무 월수"
3) ADD_MONTHS
: 지정된 날짜에 특정 개월 수를 더하거나 뺀 날짜를 반환
4) NEXT_DAY
: 지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜
EX) SELECT last_name, hire_date, NEXT_DAY(hire_date, '금'),NEXT_DAY(hire_date, 6)
5) LAST_DAY
: 해당 날짜가 속한 달의 마지막 날짜를 반환
EX) SELECT last_name, hire_date, LAST_DAY(hire_date)
6) ROUND
: 가장 가까운 년도 또는 월로 반올림이 가능 => 다 초기화가 됨 01로
EX) SELECT last_name, hire_date, ROUND(hire_date,'YEAR') ==> 03/01/01
- RR / YY 타입 비교
: RR - 가장 가까운 세기 / YY - 현재세기
EX) 현재년도 명시된 날짜 YY형식 반환년도 RR형식 반환년도
1995 95/10/27 1995 1995
1995 17/10/27 1917 2017
2001 95/10/27 2095 1995
- 변환 함수
1) 형 변환
- 자동 형변환
WHERE salary = '17000'; //자동으로 숫자로 변환
- TO_CHAR
: 숫자 및 날짜를 문자로 변환
현재 날짜와 시간을 특정 형식에 맞게 출력
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD,(AM) DY HH24:MI:SS')
* WHERE TO_CHAR(hire_date, 'MM')='09'; => 입사 날짜가 9월인 사원들의 정보를 출력
* ''으로 묶었으면 안에는 ""를 사용해야함 => TO_CHAR(SYSDATE, ' YYYY "년" MM "월" DD "일" ')
- TO_NUMBER
: 문자를 숫자로 변환
- TO_DATE
: 날짜 형태의 문자열을 명시된 날짜 데이터로 변환
TO_DATE( '20170802' , 'YYYYMMDD' )
- 조건 함수
1) DECODE
: 조건이 반드시 일치하는 경우에 사용하는 함수
DECODE( salary , 1000, salary*0.1, // salary가 1000이면 0.1 곱함
2000, salary*0.2,
3000, salary*0.3,
salary*0.4 ) //아무것도 일치하지 않으면 0.4 곱함
2) CASE
: 다양한 비교 연산자를 이용하여 조건을 설정가능
CASE salary WHEN 1000 THEN salary*0.1
WHEN 2000 THEN salary*0.2
WHEN 3000 THEN salary*0.3
ELSE salary*0.4
END
*DECODE는 동등연산자만 가능하지만 CASE는 다 가능(부등호등)
- 그룹 함수
: 그룹으로 묶어서 사용
COUNT 함수 / SUM 함수 / AVG 함수 / MAX 함수 / MIN 함수
1) SUM 함수
: 합계 구함
2) COUNT 함수
: 레코드의 개수
- COUNT(*) => ALL
- GROUP BY
* SELECT last_name, MAX(salary) => last_name은 groupinig이 안되어있어서 같이 사용할 수 없다.
일반 함수와 그룹함수를 같이 사용하려면 group by 사용해야합
SELECT [ 단순 컬럼 ,] 그룹함수 , 그룹함수2
FROM 테이블명
[WHERE 조건식]
[GROUP BY 단순 컬럼] // * GROUP BY에는 별칭 사용불가 -- 컬럼명으로 사용
[ORDER BY 표현식];
- 다중 그룹 가능
SELECT TO_CHAR( hire_date , 'YYYY' ) 년
,TO_CHAR( hire_date , 'MM') 월 , SUM(salary)
FROM employees
GROUP BY TO_CHAR( hire_date , 'YYYY'),
TO_CHAR( hire_date , 'MM') ; //같은 년도, 같은 월끼리 그룹가능
- HAVING
: GROUP BY 조건식
*실행순서
SELECT department_id, SUM(salary) // 4번으로 실행
FROM employees // 1번으로 실행
GROUP BY department_id // 2번으로 실행
HAVING SUM(salary) >= 90000 //3번으로 실행
ORDER BY 1; // 5번으로 실행
'신입사원 입문교육_정리' 카테고리의 다른 글
| workshop 3 (0) | 2021.01.27 |
|---|---|
| oracle db - 나머지공부 - 정리 (0) | 2021.01.27 |
| ddl workshop (0) | 2021.01.27 |
| SQL - Day2 (0) | 2021.01.27 |
| sql workshop - day1 (0) | 2021.01.26 |