SQLD 02 : SQL개념, DDL, DCL, TCL, DML, ORDER BY, AS, 연산자, 내장함수, 집계

LeeWonjin·2022년 8월 26일
SQLD

2022 백엔드스터디

목록 보기
20/20

SQL 개념

언어 특징

SQL (Structured Query Language 구조화된 질의언어)

  • RDB의 표준 언어
  • 자연어에 가까운 비절차적 언어 (방법, 절차 명시 없이 원하는 작업 명시)
  • 원격으로 DB에 접속해 사용 가능
  • 다른 언어와 결합 가능 (이식성 우수)

RDBMS별 형태

ANSI SQL은 특정 제품과 관련없음
MS-SQL : T-SQL
ORACLE : P/L SQL

구성

  • DDL (Data Definition Language) : 데이터 제어어
    • 데이터 구조 정의, 생성, 변경, 삭제
    • CREATE ALTER DROP RENAME TRUNCATE
  • DML (Data Manipulation Lang) : 데이터 조작어
    • 테이블의 데이터 검색, 수정, 삭제, 입력
    • SELECT UPDATE
  • DCL (Data Control Lang) : 데이터 제어어
    • 사용자 권한 생성 및 회수
    • GRANT, REOKE
  • TCL (Transaction Control Lang) : 트랙잭션 제어어
    • ROLLBACK

데이터 타입

분류자료형oraclems-sql
문자형고정문자CHARCHAR
문자형가변문자VARCHAR2VARCHAR
문자형날짜DATEDATE, DATETIME
숫자형정수NUMBERINT
숫자형실수FLOATFLOAT, DOUBLE

DDL

CREATE	DATABASE
		DOMAIN
        TABLE
        INDEX
        VIEW

DROP	DATABASE
		DOMAIN
        TABLE
        INDEX
        VIEW
        CONSTRAINT : 제약조건 제거
        
ALTER	TABLE
		DOMAIN
        COLUMN

CREATE

데이터베이스, 테이블 생성

CREATE DATABASE 데이터베이스-이름
CREATE TABLE 테이블-이름 (
	컬럼이름 자료형 NOT NULL,		<--- NULL 허용 안함
    컬럼이름 자료형 NULL			<--- NULL 허용
)

인덱스 생성

CREATE INDEX 인덱스명 ON 테이블명(컬럼명)

e.g. 
 중복없는 인덱스 idx_1을 table_1테이블의 속성 col에 대해 생성
CREATE UNIQUE INDEX idx_1 ON table_1(col)

DROP

DROP DATABASE 데이터베이스-이름
DROP TABLE 테이블-이름

ALTER

ALTER TABLE 테이블이름 ALTER COLUMN 컬렴명 데이터타입
ALTER TABLE 테이블이름 ADD 컬럼명 데이터타입			<--- COLUMN키워드 없음
ALTER TABLE 테이블이름 DROP COLUMN 컬럼명 데이터타입

DCL

권한 종류는 아래와 같다

ALL		모든 권한
INDEX	인덱스 추가/삭제
ALTER	테이블 컬럼 수정

SELECT	테이블 데이터 검색
UPDATE	테이블 데이터 변경
INSERT	테이블 데이터 추가
DELETE	테이블 데이터 삭제

CREATE SESSION	세션 생성
CREATE TABLE	테이블 생성
CREATE VIEW		뷰 생성

GRANT

GRANT 권한 ON 객체 TO 사용자
GRANT 권한_1, 권한_2, 권한_3 ON 객체 TO 사용자

e.g. (MEMBER테이블의 수정, 검색 권한을 사용자 WONJIN에게 허용)
GRANT UPDATE, SELECT ON MEMBER TO WONJIN

REVOKE

REVOKE 권한 ON 객체 TO 사용자
REVOKE 권한_1, 권한_2, 권한_3 ON 객체 TO 사용자

e.g. (MEMBER테이블에 대한 사용자 WONJIN의 모든 권한 회수)
REVOKE ALL ON MEMBER TO WONJIN

TCL

트랜잭션

  • Transaction : DB 최소 작업단위
  • 속성
    • 원자성 : 트랜잭션 다 하든지 / 다 하지 말든지. 둘 중 하나만
    • 일관성 : 트랜잭션 실행 전후 모두 DB상태 정상이어야 함
    • 고립성 : 트랜잭션끼리 영향 주면 안됨
    • 지속성 : 트랜잭션 완료 결과가 DB에 남아야 함

동작원리

DBMS가 동작할 때 군데군데 세이브포인트를 생성

트랜잭션이 정상적으로 완료되면 COMMIT
트랜잭션 실행 도중 문제가 생기면 특정 세이브포인트로 ROLLBACK (지정된 세이브 포인트 이름이 유일하지 않으면 가장 마지막 포인트로)

일반적으로 UPDATE, INSERT, DELETE 작업에 적용

  • ORACLE
    • SAVEPOINT 세이브포인트-이름
    • ROLLBACK TO 세이브포인트-이름
  • MS-SQL
    • SAVEPOINT TRANSACTION 세이브포인트-이름
    • ROLLBACK TRANSACTION 세이브포인트-이름

DML

INSERT

속성, 값의 쌍이 맞아야 함

여러 레코드를 한 번에 INSERT 할 수 없음 (100개 레코드 INSERT하고싶으면 100줄 적어야 함)

INSERT INTO 테이블이름 (속성_1, 속성_2, 속성_3) VALUES (값_1, 값_2, 값_3)

UPDATE

WHERE절 없으면 테이블 내 모든 레코드의 해당 속성 값이 바뀌는 대참사 발생함

UPDATE 테이블이름 SET 속성=값 WHERE 조건
UPDATE 테이블이름 SET 속성1=값1, 속성2=값2, 속성3=값3 WHERE 조건

DELETE

WHERE절 없으면 테이블의 모든 레코드 삭제
특정 컬럼이 아닌 레코드 삭제

DELETE FROM 테이블이름 WHERE 조건

SELECT

아래와 같이 검색가능

SELECT 속성이름 FROM 테이블이름
SELECT 속성이름 FROM 테이블이름 WHERE 조건
SELECT 속성1, 속성2, 속성3 FROM 테이블이름 WHERE 조건
SELECT * FROM 테이블이름 WHERE 조건

ORDER BY ASC/DESC로 정렬기준 및 방향 선택가능 (기본값 ASC)

SELECT * FROM 테이블이름 WHERE 조건 ORDER BY 기준속성 ASC
SELECT * FROM 테이블이름 WHERE 조건 ORDER BY 기준속성 DESC

별칭 AS

속성(컬럼), 테이블 이름에 별칭 지정 가능
속성 AS 별칭 또는 속성 별칭(공백)

SELECT 속성 AS 별칭 FROM 테이블이름 AS 테이블별칭
SELECT 속성 별칭 FROM 테이블이름 테이블별칭

SELECT col_1, col_2 FROM tbl as T
SELECT T.col_1, T.col_2 FROM tbl T
SELECT T.* FROM tbl T

연산자

CREATE TABLE MEMBER (
	ID INT,
    NAME VARCHAR(20),
    AGE INT )
INSERT INTO MEMBER (ID, NAME, AGE) VALUES (1. 'wonjin', 25)
INSERT INTO MEMBER (ID, NAME, AGE) VALUES (2, 'hihi', 21)
INSERT INTO MEMBER (ID, NAME, AGE) VALUES (3, 'something', 10)
SELECT * FROM MEMBER
+----+------------+------+
| ID | NAME       | AGE  | 
+----+------------+------+
|  1 | wonjin     | 25   |
|  2 | hihi       | 21   |
|  3 | something  | 10   |
+----+------------+------+

사칙연산자

+ - / * MOD

정수에 대해 정수연산, 실수에 대해 실수연산 적용
문자열에 대해 +로 concat연산

NULL에 연산을 가하면 결과는 NULL
어떤 컬럼이 NULL일 때 특정 값으로 바꾸려면

  • Oracle은 NVL(컬럼, 바꿀 값)
  • MS-SQL은 ISNULL(컬럼, 바꿀 값)
  • MY-SQL은 IFNULL(컬럼, 바꿀 값)
SELECT ID*10+10 FROM MEMBER
+----------+
| ID*10+10 |
+----------+
|       20 |
|       30 |
|       40 |
+----------+

SELECT 'Name is :'+NAME FROM MEMBER
+----------------------+
|  'Name is : '+NAME   |
+----------------------+
| Name is : wonjin     |
| Name is : hihi       |
| Name is : something  |
+----------------------+
** mysql에선 안됨. concat함수 써야함.

비교연산자

대소 : < > <= >=
같다 : =
같지 않다 : <> !=
NULL이다/아니다 : IS NULL IS NOT NULL

NULL의 비교에 대하여

  1. 아래 두 구문은 컬럼의 값이
    1) NULL인지 ( =NULL이라는 값이 들어가있는지 )
    2) 아닌지 ( =NULL이라는 값이 아닌 다른 값이 들어가있는지 )
    를 검사한다.
WHERE 컬럼=NULL
WHERE 컬럼<>NULL
  1. 아래 두 구문은 컬럼이
    1) NULL인지(=값이 없는지)
    2) 아닌지(=값이 있는지)
    를 검사한다.
WHERE 컬럼 IS NULL
WHERE 컬럼 IS NOT NULL

논리연산자

AND OR

SELECT * FROM MEMBER ID>1 AND ID<3
+----+------------+------+
| ID | NAME       | AGE  | 
+----+------------+------+
|  2 | hihi       | 21   |
+----+------------+------+

SELECT * FROM MEMBER ID<3 OR NAME='something'
+----+------------+------+
| ID | NAME       | AGE  | 
+----+------------+------+
|  1 | wonjin     | 25   |
|  2 | hihi       | 21   |
|  3 | something  | 10   |
+----+------------+------+

특수연산자

BETWEEN A AND B LIKE IN

BETWEEN은 구간을 잡는 AND문의 대안이 될 수 있다.
아래 두 문장은 같다

SELECT * FROM MEMBER WHERE ID>=1 AND ID<=3
SELECT * FROM MEMBER WHERE ID BETWEEN 1 AND 3

LIKE는 특정 문자열이 포함되었는지 여부를 판단한다.
%는 와일드카드라 부른다.

WHERE NAME LIKE 'STR%'  <-- 'STR'로 시작하는가? (e.g. 'STRAAA')
WHERE NAME LIKE '%STR'  <-- 'STR'로 끝나는가? (e.g. 'AAASTR')
WHERE NAME LIKE '%STR%' <-- 'STR'을 포함하는가? (e.g. 'ASTRA', 'STRAAA', 'AAASTR')

IN은 한 컬럼에 대한 여러 OR문의 대안이 될 수 있다.
아래 두 문장은 같다.

SELECT * FROM MEMBER WHERE ID=1 OR ID=3
SELECT * FROM MEMBER WHERE ID IN (1, 3)

내장 함수

문자

ORACLEMS-SQL반환
SUBSTR('wonjin', 3, 2)SUBSTRING('wonjin', 3, 2)'nj'
CONCAT('wonj', 'in')CONCAT('wo', 'nj', 'in')'wonjin'
LENGTH('wonjin')LEN('wonjin')6
ASCII('A')동일65
LOWER('aBcDeF')동일'abcdef'
UPPER('aBcDeF')동일'abcdef'
LTRIM(' ABC')동일'ABC'
LTRIM('##@ABC', '#')----없음----'@ABC'
RTRIM('ABC ')동일'ABC'
RTRIM('ABC#@@', '@'----없음----'ABC#'
TRIM(' ABC ')동일'ABC'
TRIM('@@ABC@@@', '@')----없음----'ABC'

숫자

ORACLEMS-SQL반환
CEIL(5.353)CEILING(5.353)6
FLOOR(5.353)동일5
ROUND(5.353)동일5
ROUND(5.353, 1)동일5.4
TRUNC(5.999)동일5
TRUNC(5.999, 2)동일5.99
ABS(-53)동일53
MOD(10,4)동일2

** round함수 mysql기준

그 외 SIN, COS, TAN, EXP, LOG, PI, SQRT 등

날짜

ORACLEMS-SQL반환
SYSDATEGETDATE()현재시간 e.g. 2022-08-27 02:14
EXTRACT(DAY FROM 2022-08-27)DATEPART(DAY, 2022-08-27)'27'
SYSDATE+53GETDATE()+53현재 날짜에 53일 더한 날짜
----없음----DATEADD(YEAR, 53, GETDATE())현재 날짜에 53년 더한 날짜
TO_DATE(SYSDATE, 'YYYY') - TO_DATE('1998-06-05', 'YYYY')동일현재 연도-1998에 해당하는 값
----없음----DATEDIFF(DAY, '1998-06-05', GETDATE())1998년6월5일~현재 날짜 사이의 일수

형 변환

ORACLEMS-SQL반환
TO_CHAR(123)동일'123'
TO_NUMBER('123')동일123
TO_CHAR(2022-05-03, 'MONTH')동일'05'
TO_NUMBER(2022-05-03, 'MONTH')동일5

집계

SELECT * FROM ITEM_LIST;
+-------+----------+
| ITEM  | CATEGORY |
+-------+----------+
| A-001 |        1 |
| A-010 |        2 |
| B-111 |        1 |
| B-090 |        3 |
| B-555 |        3 |
| C-222 |        1 |
+-------+----------+
6 rows in set

** ITEM은 VARCHAR(20), CATEGORY는 INT

GROUP BY

어떤 컬럼을 기준으로 뷰를 만들 수 있음

SELECT LEFT(ITEM, 1) AS TYPE, COUNT(LEFT(ITEM, 1)) AS CNT
FROM ITEM_LIST
WHERE CATEGORY<3
GROUP BY LEFT(ITEM, 1)
ORDER BY COUNT(ITEM) DESC;
+------+-----+
| TYPE | CNT |
+------+-----+
| A    |   2 |
| B    |   1 |
| C    |   1 |
+------+-----+
3 rows in set

HAVING

GROUP BY로 나온 결과를 다시 필터링
(WHERE이 SELECT-FROM에 대한 조건이라면, HAVING은 GROUP BY에 대한 조건)

SELECT LEFT(ITEM, 1) AS TYPE, COUNT(LEFT(ITEM, 1)) AS CNT
FROM ITEM_LIST
WHERE CATEGORY<3
GROUP BY LEFT(ITEM, 1)
HAVING TYPE BETWEEN 'A' AND 'B'		<--- HAVING절 추가됨
ORDER BY COUNT(ITEM) DESC;
+------+-----+
| TYPE | CNT |
+------+-----+
| A    |   2 |
| B    |   1 |
+------+-----+
2 rows in set
profile
LeeWonjin
노는게 제일 좋습니다.
이전 포스트

Spring 09 : MVC ( Controller Exception, Controller Advice, Json 요청/응답 )

0개의 댓글