SQL, SELECT문, 쿼리순서 및 자료형

 

 

DBMS (DataBase Management System) 

데이터베이스 관리시스템으로, 오라클은 대표적인 RDBMS(관계형 데이터베이스)이다.

데이터를 표로 관리하며, 테이블끼리 부모-자식의 상속으로 관계를 맺는다.

Oracle, MySQL, MS-SQL, DB2, INFORMIX ...등

 

>> 더 자세한 DBMS 내용은 아래와 같다

2024.01.11 - [BACK-END/SQL, DB] - 데이터베이스의 흐름, DBMS의 종류와 형태에 관하여

 

 

 

SQL 

구조화된 질의언어. DBMS을 제어하기위한 표준 질의어이다

보통 대문자로 작성하며, 작성자가 만든것을 소문자로 작성한다.

*단, 데이터는 대/소문자를 구별한다.

• DDL문(Data Definiton Language) : 객체제어
• DML문(Data Manipulation Language) : 테이블제어
• DCL문(Data Control Language) : 권한제어

DDL문

객체(Table, View, column등)을 제어 Create(생성),Alter(수정),Drop(삭제)

 

DML문

테이블에 저장된 데이터를 제어

Select(조회), Insert(삽입), Update(수정), Delete(삭제)

 

DCL문

권한을 제어. 사용자계정을 만들거나 사용자에게 권한을 부여, 뺏는다

Grant(권한을 부여), Revoke(권한을 삭제)

ex) GRANT CONNECT,RESOURCE TO USER; //user계정에 권한부여 

 

 

 

SQL 기본명령어 (모든 sql데이터베이스에서 사용가능)

> sqlplus 아이디 >exit; //시작 및 종료
> show user; //현재 세션의 유저 확인
> conn 유저이름; //유저 전환
> select * from tab;   //해당유저의 테이블목록보기
> desc 테이블명;    //테이블 구조보기  (컬럼명이 나옴)
> select 컬럼명,컬럼명,컬럼명 from 테이블명

[system단]
> CREATE USER USER01 IDENTIFIED BY USER01; //USER01이라는 user를 만들고 비번을 USER01로부여
> GRANT CONNECT, RESOURCE TO USER01; //권한부여
> CONN USER01/USER01; //sqldeveloper라면 실제로 + 버튼을 눌러 계정을 접속/추가해줘야한다. 

 

---

자료형 - Oracle 중심

숫자형

BINARY_INTEGER : (int형 정수와 같은 범위의 숫자)사이의 정수 (4byte)

NUMBER : 숫자 데이터 / 최대 38자리의 정수, 실수

NUMBER(n): 숫자 데이터 /최대 n자리까지의 정수

NUMBER(p, s) : 주로 실수를 표현하지만, 정수도 표현함.

 *p는 소수점을 제외한 전체 자리수, *s는 소수점 이하의 자릿수, *p-s 자리수만큼 정수자리

...salary number(5,2) -> 정수자릿수3, 전체자리수는5, 소수점이하는 2자리일때
123.56 // o
1234.34 // x 정수자리는3
123.345666 // o  소수자리는 신경안써도 알아서 자리값만큼 반올림해서 들어감
12 // o

 

 

문자형

문자를 저장할때는 값을 반드시 ' ' 으로 감싸자. 숫자는 감싸지말자

CHAR(size) / VARCHAR2(size) 계열 ??
단위는 byte단위이다. char와 varchar2는 데이터의 성격에 따라 결정한다. 무조건 가변길이가 좋은건 아니다.
N이 붙는건 글자단위(문자저장)

• CHAR(size) : 고정길이. 최대값 2000자/한글1000자
  ex) char(3)일때 들어온데이터가 1byte이어도, 3byte로 고정되서 만들어진다. ex)주민번호등
• NCHAR(size) : 문자단위이므로 주로 유니코드(한글)저장할때 사용. 최대값 1000자/한글1000자
• VARCHAR2(size) : VAR가 붙으면 가변길이. 들어온데이터에 따라 메모리가 만들어진다.
  최대값4000자/한글2000자   ex)제목지정할때
• NVARCHAR2(size) : 문자단위의 가변길이. 최대값2000자/한글 2000자

 

• LONG : 가변길이의 문자열(최대 4GB)
• LONG RAW : 가변길이의 바이너리 데이터(2GB). PL/SQL에 의해 해석되지 않는다
• CLOB : 대용량의 텍스트 데이터를 저장(최대 4GB)
• BLOB : 대용량의 바이너리데이터 저장(최대 4GB). 이미지저장시사용하나 직접적으로 넣는게 아닌 링크를 넣는다
• DATE : 날짜와 시간에 대한 기본형  ex) DATE SYSDATE;

 

 

---

 

 

 

SELECT 문

데이터를 조회하고자할때 사용하는 DML문

SELECT 컬럼이름,컬럼이름 ...
FROM 테이블 이름;
[WHERE 찾는 조건]
[GROUP BY 결과 데이터 그룹화]
     [HAVING 그룹에서 찾는 조건]
[ORDER BY 데이터 정렬 표현]

 

쿼리 실행순서 (**중요함)

FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

 

set linesize 숫자; (1000등) : 라인사이즈를 줄이는등 가독성 (CMD에서 사용)

 

• * : 모든열 선택
• , : 추출하고싶은 column을 , 로 구분해서 쓰면 작성한 순서대로 선택된다
• +,-,/,* : 나머지 연산을 제외한 산술연산자도 사용가능. 단, +연산자는 숫자에만 사용가능. 
  cf) 문자열을 연결해주고싶을땐 || 을 사용한다.   
  ex) SELECT '이름은' || ename || '입니다' FROM emp;
• column AS 별칭 // "별' 칭" : Column을 검색하기 쉽게 별칭부여.  공백이나 ' 을 주고싶을때는 " "로 감싼다.
  AS 생략가능함.
  ex) SELECT '이름은' || ename || '  ' || '입니다' as 별칭이름("별칭 이름") FROM emp;
• DISTINCT : SELECT바로 뒤에 작성. 중복행을 제거한다. 컬럼이 여러개일때는 AND처럼 작용.
  ex) SELECT DISTINCT ename,sal FROM emp; 했으면 , ename&&sal 둘다 중복일때만 제거된다.
• WHERE : 일반적으로는 원하는 자료를 조회할때 WHERE절을 사용해서 조회한다. FROM 다음에 작성. 
  조건은 Column명, 표현식, 상수, 문자, 비교연산자, 논리연산자, SQL연산자등을 사용한다.
  • **cf) 논리연산자 : AND, OR, NOT, ||(문자열연결)
    **cf) SQL연산자: BETWEEN a AND b (AND연산자), IN(OR연산자) ,LIKE, IS NULL(IS NOT NULL)

>>> 예시 >>>>

• SELECT ename,job FROM emp WHERE job = 'SALESMAN';
• SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000; (sal>=1500 && sal<=3000과 같음)
• SELECT ename,sal,job FROM emp WHERE job IN ('SALESMAN','CLERK');
       = [ job='SALESMAN' OR 'CLERK' ]
• SELECT ename,sal,job,deptno FROM emp WHERE (job,deptno) IN (('CLERK',20),('SALESMAN',30)); 
       = job='CLERK' AND deptno=20 OR job='SALESMAN' AND deptno=30; 값이두개니 넣는것도 두개.
• LIKE % : 임의의 0개이상의 문자열   /  LIKE_ : 임의의 한글자
      LIKE 'A%' : 컬럼이'A'로 시작하는 데이터들 검색 // ABC, A123, A 
      LIKE '%A' : 컬럼이'A'로 끝나는 데이터들 검색 // BCA, A, 123A
      LIKE '%KIM%' : 'KIM'이 포함된 데이터들 검색 //
      LIKE '_A%' : 임의의 한문자 + 두번째열에 A로 시작되는 데이터들 검색 // SA, MAA, (SMA 불가)
      ex) SELECT ename FROM emp WHERE ename LIKE '__A%' AND NOT ename LIKE 'A%'; 
       = 3번째위치에 A로 시작하면서, 전체단어가 A로 시작되지는 않는 데이터 검색
• SELECT ename,job,comm FROM emp WHERE comm IS NULL;
      = [Comm 이 null값인것들 출력. comm=null; 이런식으로는 조회할수없다]
• SELECT ename,job,comm FROM emp WHERE comm IS NOT NULL;

 

 

ORDER BY 

행을 정렬하고 싶을경우에 사용하며, 가장 뒤에서 사용한다.

Order by 컬럼||표현식 [ASC 오름차순|DESC내림차순]

**화살표를 아래로 두고봤을때 값이 커지면 오름차순, 작아지고 있으면 내림차순

 

오름차순 정렬을 기본으로 정렬이되어있으므로 ASC를 생략해도되지만,

내림차순으로 하고싶을때는 뒤에 DESC를 붙인다.

• ex) SELECT ename,sal FROM emp ORDER BY sal (ASC생략가능) 
• ex) SELECT ename,sal FROM emp ORDER BY sal DESC;

 

SELECT ename,job,deptno FROM emp ORDER BY job,deptno 일때는? (두개의 정렬조건을 주었을때)

첫번째 쓰여져있는 'job'으로 먼저 정렬(기본 오름차순)이 된다.

그다음 그 job기준으로 deptno를 정렬한다

따라서 이런식으로 CLERK안에서 10~30 이렇게 정렬이 되므로 원하던 모양이 아닐수있으니 생각하고 쿼리를 쓰자

 

 

 

 

그룹함수(집합함수)

• COUNT(*): 테이블의 전체  레코드 개수를 가져온다
  ex) SELECT COUNT(*) FROM 테이블명 ; 전체 행수 
  ex) SELECT COUNT(특정컬럼): 그 column에서 null값이 아닌 행의 수
• AVG(컬럼명): 해당 컬럼의  전체 평균값을  구한다.
  ex) SELECT AVG(컬럼명) FROM 테이블명
• MAX(컬럼명): 해당 컬럼의 가장 큰 값을 구한다.
  ex) SELECT MAX(컬럼명) FROM 테이블명
• MIN(컬럼명): 해당 컬럼의 가장 작은 값을 구한다
  ex) SELECT MIN(컬럼명) FROM 테이블명
• SUM(컬럼명) : 해당 컬럼의 합계 구하기 
  ex) SELECT SUM(컬러명) FROM 테이블명: 합계 구하기

 

각 부서에서 최고연봉을 받는 사람을 알아내고싶다면 ?

그룹함수+일반컬럼은 사용할수없기때문에, 그룹함수를 제외한것들을 그룹화(GROUP BY)해준다.

SELECT MAX(sal), ename FROM emp;  // x
SELECT MAX(sal), deptno FROM emp GROUP BY deptno;  // o

그룹화를 하지않았다면 MAX(sal)의 값은 1개, deptno의 값은 14개이기때문에 나올수가없다.

하지만 그룹화를 시키면 부서코드가 3개가 되고, MAX(sal)도 각각에 대해 나와 3개의 값이 되므로 테이블이 작성된다

 

 

 

GROUP BY / HAVING

그룹에대한 조건은 'HAVING'절에서 쓴다. 

WHERE절과 같이 쓸시, 쿼리실행순서에 따라서 WHERE절을 먼저 필터링 한후,

그룹화시키고 이후 HAVING절에 따라서 필터링한후 결과가 SELECT 된다. 

SELECT MIN(sal) m, deptno FROM emp GROUP BY deptno HAVING MIN(sal) > 1000;

 

 

 

---

- 궁금한 점 / 수정 요청사항 등은 언제든 댓글 달아주세요 :)

- 도움이 되셨다면 ❤️ 하트 한 번 눌러주세요! 감사합니다 !