[SQLD] 2 -1 SQL 기본

1-1. 관계형 데이터베이스 개요

데이터베이스: 특정기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장한 것

SQL: SQL은 관계형데이터베이스에서 데이터 정의, 데이터 조작, 데이터제어를 하기 위해 사용하는 언어

table

데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의
기본 단위
칼럼과 행의 2차원 구조 (세로 방향을 칼럼(Colu
mn), 가로 방향을 행(Row)이라고 하고, 칼럼과 행이 겹치는 하나의 공간을 필드(Field))

ERD(Entity Relationship Diagram)

관계의 의미를 직관적으로 표현할 수 있는 수단 - 구성 요소는 엔터티(Entity), 관계(Relationship), 속성 (Attribute)

1-2 DDL - 데이터 정의어

데이터 유형

create table

테이블 생성하기

CREATE　TABLE　테이블이름
( 칼럼명1 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식],
칼럼명3 DATATYPE [DEFAULT 형식] ) ;
예)

SQL Server CREATE TABLE PLAYER ( PLAYER_ID CHAR(7) NOT NULL, PLAYER_NAM E VARCHAR(20) NOT NULL,

테이블 내에 칼럼명 중복 X
대소문자 구분X

제약조건 constraint

사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약

NULL은 “아직 정의되지않은 미지의 값”, “현재 데이터를 입력하지 못하는 경우”
DEFAULT는 데이터입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값을 설정 가능

생성된 테이블 구조 확인

Oracle
DESCRIBE 테이블명 or DESC 테이블명
SQL server
“sp_help ‘dbo.테이블명

select 문장으로 테이블 생성

Oracle
Create Table ~ As Select~
SQL server
SQL Server에서는 Select ~ Into ~

예) [예제] 선수(PLAYER) 테이블과 같은 내용으로 TEAMTEMP라는 복사 테이블을 만들어 본다.
_Oracle
CREATE TABLE TEAM _TEM P AS SELECT FROM TEAM ;
SQL Server
SELECT INTO TEAM _TEM P FROM TEAM ;

Alter table

테이블 수정 - 칼럼을 추가/삭제하거나 제약조건을 추가/삭제

추가: ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;
삭제: ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;
칼럼정의변경:

[Oracle] ALTER TABLE 테이블명 M ODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …);
[SQL Server] ALTER TABLE 테이블명 ALTER (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …); ```

• MODIFY COLUMN, RENAME COLUMN
• DROP CONSTRAINT: 테이블 생성 시 부여했던 제약조건을 삭제하는 명령어
• ADD CONSTRAINT: 테이블 생성 이후에 필요에 의해서 제약조건을 추가

rename table

ALTER TABLE 테이블명
RENAME COLUMN 변경해야할 컬럼명 TO 새로운 컬럼명;

drop table - 데이터 및 구조 삭제

테이블 전체 삭제
일부 칼럼 삭제

ALTER TABLE 테이블명 
ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;

Turncate table - 기존에 사용하던 테이블의 모든 로우를 제거

해당테이블에 들어있던 모든 행들이 제거되는 것(데이터만 제거)

TRUNCATE TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;

1-3 DML - 데이터 조작어

insert

INSERT INTO 테이블명 (COLUMN_LIST)VALUES (COLUMN_LIST에 넣을 VALUE_LIST);
INSERT INTO 테이블명VALUES (전체 COLUMN에 넣을 VALUE_LIST);

예) PLAYER INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO) —>(칼럼 리스트) VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);

update

UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;

예)

UPDATE PLAYER SET BACK_NO = 99;
UPDATE PLAYER SET POSITION = 'MF'; —>(문자값인 경우 ‘ ’ 사용)

DELETE

DELETE FROM 삭제를 원하는 정보가 들어있는 테이블명 WHERE 조건절;
DELETE FROM PLAYER; —>(조건절이 없으면 전체 테이블 삭제)

SELECT

SELECT 칼럼명 FROM 테이블;

EX)

SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION FROM PLAYER;
SELECT DISTINCT POSITION FROM PLAYER; —>(DISTICNT: 중복데이터를 1건으로 표시)
SELECT * FROM PLAYER; —>(*: 모든 칼럼명 선택)
SELECT PLAYER_NAME AS 선수명 FROM PLAYER; —>(AS: 칼럼명에 별명붙이고 별명으로 표시)

산술연산자 & 합성연산자

예)

SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게" FROM PLAYER;

Oracle SQL>>
SELECT PLAYER_NAME || ‘선수, ' || HEIGHT || 'cm,' || WEIGHT || 'kg' 체격정보 FROM PLAYER;

SQL>>
SELECT PLAYER_NAME +'선수, '+ HEIGHT +'cm, '+ WEIGHT +'kg'체격정보 FROM PLAYER;

->>> 정경량선수173cm,65kg 정은익선수,176cm,63kg 레오마르선수,183cm,77kg 명재용선수,173cm,63kg

1-4 TCL - 트랜젝션 제어어

트랜잭션 - 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합

분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다. 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.
트랜잭션은 분할할 수 없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소

TCL(TRANSACTION CONTROL LANGUAGE)

• 커밋(COMMIT): 올바르게 반영된데이터를 데이터베이스에 반영시키는 것을
• 롤백(ROLLBACK): 트랜잭션 시작 이전의 상태로 되돌리는 것
• 저장점(SAVEPOINT) 기능

트랜잭션의 대상이 되는 SQL문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문이다.

트랜젝션 특성: 원자성(all or nothing), 일관성, 고립성, 지속성

commit

Oracle SQL>>

UPDATE PLAYER SET HEIGHT = 100;
COMMIT;

SQL>>

UPDATE PLAYER SET HEIGHT = 100;

Oracle은 DML문장 수행 후 사용자가 임의로 COMMIT 또는 ROLLBACK 수행해줘야 함
SQL Server는 기본적으로 AUTO COMMIT

Rollback

테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전으로 되돌려,
변경 사항을 취소

Oracle SQL>>

UPDATE PLAYER SET HEIGHT = 100; ———>(480개의 행이 수정되었다.)
ROLLBACK; ———>(롤백이 완료되었다.)

SQL>>

BEGIN TRAN UPDATE PLAYER SET HEIGHT = 100;
ROLLBACK; ———>(롤백이 완료되었다.)

savepoint

저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백

SQL>>

SAVEPOINT 세이브포인트명;

SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;

1-5 WHERE 절

자신이 원하는 조건의 자료만을 검색하기 위해서

Where

SQL>>

SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명] FROM 테이블명 WHERE 조건식;

★ 처리 순서: 부정 연산자 -> 비교 연산자, sql비교 연산자 -> 논리 연산자

부정 연산자(NOT)
비교 연산자(=,>,>=,<,<=), SQL 비교 연산자(BETWEEN a AND b, IN (list), LIKE, IS NULL
논리 연산자 중에서는 AND, OR의 순으로

sql 연산자

SQL>>
SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));

-> 사원 테이블(EMP)에서 JOB이 MANAGER이면서 20번 부서에 속하거나,
JOB이 CLERK이면서 30번 부서에 속하는 사원의 정보(ENAME, JOB, DEPTNO)를 IN 연산자의 다중 리스트를 이용해 출력하라.

SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER
WHERE PLAYER_NAME LIKE ‘장%';

--> “장”씨 성을 가진 선수들의 정보를 조회하는 WHERE 절

IS NULL / IS NOT NULL

NULL(ASCII 00)은 값이 존재하지 않는 것으로 확정되지 않은 값을 표현
어떤 값과 비교할 수도 없으며, 특정 값보다 크다, 적다라고 표현할 수 없다
NULL 값과의 수치연산은 NULL 값을 리턴
NULL 값과의 비교연산은 거짓(FALSE)을 리턴

논리연산자

논리 연산자들이 여러 개가 같이 사용되었을 때의 처리 우선순위는 ( ), NOT, AND, OR의 순서

부정연산자

!=			같지않다
^=
<>
NOT 칼럼명 =	~와 같지않다
NOT 칼럼명 >	~보다 크지 않다

ROWNUM(오라클), TOP(sql server)

rownum

Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column으로써
SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호이며,
테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때
WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다

Oracle SQL>>
“MY_TABLE 이라는 테이블의 첫번 째 칼럼을 ‘고유한 키’값 혹은 ‘인덱스 값’으로 설정하라!"
—> 새롭게 넘버링 칼럼을 설정하고, 그 값을 기 테이블의 ‘고유한 키’값 혹은 ‘인덱스 값’으로 설정
UPDATE MY_TABLE SET COLUMN1 = ROWNUM;

“PLAYER 테이블에서 PLAYER_NAME 번호가 3 이하인 선수 이름을 출력하라”
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= 3;

top

SQL Server는 TOP 절을 사용하여 결과 집합으로 출력되는 행의 수를 제한

• Expression: 반환할 행의 수를 지정하는 숫자
• PERCENT: 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타낸다.
• WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며,
  TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.

SQL>>
TOP (Expression) [PERCENT][WITH TIES];

“PLAYER 테이블에서 1~5행까지의 PLAYER_NAME 을 출력하라”
SELECT TOP(5) PLAYER_NAME FROM PLAYER;

1-6함수

문자형함수

숫자형 함수

날짜형 함수

연산가능

예시
Oracle SQL>>
“사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력한다.”

EXTRACT(MONTH FROM HIREDATE) 입사월, EXTRACT(DAY FROM HIREDATE) 입사일 FROM EMP;

SQL>>
“사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력한다.”

SELECT ENAME, HIREDATE, DATEPART(YEAR, HIREDATE) 입사년도,
DATEPART(MONTH, HIREDATE) 입사월, DATEPART(DAY, HIREDATE) 입사일 FROM EMP;
(or 다음도 같은 코드)
SELECT ENAME, HIREDATE, YEAR(HIREDATE) 입사년도, MONTH(HIREDATE) 입사월,
DAY(HIREDATE) 입사일 FROM EMP;

변환형 함수

• 명시적 explicit 데이터 유형 변환: 데이터 변환형 함수로 데이터 유형을 변환하도록 명시
• 암시적 implicit 데이터 유형 변환 : 데이터베이스가 자동으로 데이터 유형 변화 계산

CASE Expression

IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할

null 관련 함수

** 공집합은 NULL 데이터와 다름

예)

SQL>>
“사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR를 표시한다.”
SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NUIF FROM EMP;

SQL>>
“사원 테이블에서 커미션(COMM)을 1차 선택값으로, 급여(SAL)를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우는 NULL로 표시한다.”
SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL FROM EMP;

1-7 Group by 절, Having 절

집계함수 aggregate fuction

여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 다중행 함수 중 하나
GROUP BY 절은 행들을 소그룹화
SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다

group by

GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다

그룹조건식] ; ```

예)

“K-리그 선수들의 포지션별 평균키는 어떻게 되는가?”
SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상, MAX(HEIGHT) 최대키,
MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키 FROM PLAYER GROUP BY POSITION;
결과 >>> 포지션 인원수 키대상 최대키 43 43 196 174 186.26 DF 172 142 190 170 180.21
FW 100 100 194 168 179.91 MF 162 162 189 165 176.31

having 절

HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용

예)
[예제] HAVING 조건절에는 GROUP BY 절에서 정의한 소그룹의 집계 함수를 이용한 조건을 표시할 수 있으므로, HAVING 절을 이용해 평균키가 180 센티미터 이상인 정보만 표시한다.

EIGHT) >= 180;

[결과] 포지션 평균키 ------ ------ GK 186.26 DF 180.21 2개의 행이 선택되었다.
실행 결과에서 전체 4개 포지션 중에서 평균 키가 180cm가 넘는 2개의 데이터만 출력된 것을 확인할 수 있다.

case expression 활용

집계 함수(CASE( ))~GROUP BY” 기능은, 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법

집계함수와 null

다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외한다

리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우에는 VL(SUM(SAL),0)이나,ISNULL(SUM(SAL),0)처럼 전체 SUM의 결과가 NULL인 경우(대상 건수가 모두 NULL인 경우)에만한 번 NVL/ISNULL 함수를 사용하면 된다.

1-8 ORDER BY

order by

SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용
칼럼(Column)명 대신에 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용
가능

SELECT 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식][GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건
식]ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ; ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한
다.(기본 값이므로 생략 가능) DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다.

예)
[예제] ORDER BY 절의 예로 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데 사람 이름을 내림차순으로
정렬하여 출력한다.

SELECT PLAYER_NAM E 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER ORDER BY PLAYER_NAM E
DESC;

[실행 결과] 선수명 포지션 백넘버 ----- ----- ---- 히카르도 M F 10 황철민 M F 35 황연석 FW 16 황승주 DF 98 홍종하 M F 32
홍인기 DF 35 홍성요 DF 28 홍복표 FW 19 홍명보 DF 20 홍도표 M F 9 홍광철 DF 4 호제리오 DF 3 480개의 행이 선택되었다.

SELECT 문장 실행 순서

GROUP BY 절과 ORDER BY가 같이 사용될 때 SELECT 문장은 6개의 절로 구성이 되고, SELECT 문장의 수행 단계는 아래와 같다.

5 SELECT 칼럼명 [ALIAS명]
1. FROM 테이블명
2. WHERE 조건식
3. GROUP BY 칼럼(Column)이나 표현식
4. HAVING 그룹조건식
6. ORDER BY 칼럼(Column)이나 표현식;

=> 1. 발췌 대상 테이블을 참조한다. (FROM)
=> 2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)
=> 3. 행들을 소그룹화 한다. (GROUP BY)
=> 4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)
=> 5. 데이터 값을 출력/계산한다. (SELECT)
6. 데이터를 정렬한다. (ORDER BY)

Top N 쿼리

SQL>>
“사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 같이 출력한다.”

SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

결과 >>> KING 5000 SCOTT 3000 FORD 3000

1-9 JOIN

두개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것

EQUI JOIN 등가 조인

두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용
대부분 PK ↔ FK의 관계를 기반으로 한다. 그러나 반드시 PK ↔ FK의 관계로만 EQUI JOIN 이 성립하는 것은 아니다
JOIN의 조건은 WHERE 절에 기술하게 되는데 “=” 연산자를 사용해서 표현

예)
SQL>>
“선수 테이블과 팀 테이블에서 선수 이름과 소속된 팀의 이름을 출력하시오.”

SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명 FROM PLAYER, TEAM 
WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;
->(다음도 같은 코드)
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명 FROM PLAYER
INNER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;

Non Equi join

두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우
“=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행