[1028 SQL]

참고서적 :

DBMS 아키텍처 부분 : DB 성능 최적화를 위한  SQL 실전가이드 SQL 레벨업 미크 지음, 윤인성 옮김 

DBMS 기본 : 김상형의 SQL 정복

 

SQL 관련 커뮤니티 : http://www.gurubee.net/

꿈꾸는 개발자, DBA 커뮤니티 구루비

 

http://www.yes24.com/Product/Goods/22744867

SQL 첫걸음 - YES24

 

 

SQL 기초
데이터	컴퓨터 안에 기록되어 있는 숫자.
데이터베이스	데이터의 집합, 컴퓨터 안에 기록된 모든것 - 계층형 데이터 베이스  ;  역사가 오래된 DBMS ex) 파일 시스템 , 현재 DBMS로 채택되는 경우는 많지 않음.  - 관계형 데이터 베이스 : 관계대수라는 것에 착안하여 고안한 데이터 베이스.  - 객체지향 데이터 베이스 : JAVA, C++  가능하면 객체 그대로를 데이터베이스의 데이터로 저장하는것.  - XML 데이터베이스 :  XMl 형식으로 데이터 저장, XQuery라는 전용 명령어 사용.  - 키-밸류 스토어 KVS :  NoSQL 이라는 슬로건으로부터 생겨난 데이터 베이스. 열지향 데이터베이스.                                      ex) 연상배열(=결합성배열 =맵= 딕셔너리),  해시 테이블.
DBMS	Database Managemet System 생산성, 기능성, 신뢰성
SQL	IBM 개발한 SEQUEL 관계형 데이터베이스 조작용 언어를 기반으로 만들어짐.  DML : Data Manipulation Language 데이터 추가, 삭제, 갱신 DDL : Data Definition Language  데이터 정의, 데이터베이스 객체를 만들거나 삭제 DCL : Data Control Language : 트랜젝션 제어어 외
데이터베이스 제품	Oracle DB2 : IBM SQL SERVER PostgreSQL MySQL SQLite SQL 방언존재
데이터베이스 서버	클라이언트/서버모델.
테이블 구조	DESC 테이블명;    자료형 INTEGER 형 CHAR 형 VARCHAR형   * 고정과 가변형 DATE형 TIME 형
검색조건	select where  "=" "<>" 문자열상수 리터럴 : 자료형에 맞게 표기된 상수값.  ex) 'ABC' ISNULL 비교연산자 AND, OR, NOT  : OR 보다 AND 가 우선순위가 높음.  LIKE   : 메타문자 '%', : 문자가 없거나 하나 이상의 어떤값이 와도 됨. '_'   : 하나의 문자에  어떤값이 와도 됨.  '\' : 이스캐이프 처리.  '''' : 이스케이프 처리.    " ' " 문자는 2개 연속기술.    Order by  : Null 은 가장 작은 값 취급 , order by price* quantity desc  << 연산가능.  desc asc        : Order by a asc, b desc; limit   행수  offset 시작행   ex)  limit 3 offset2  앞에 2행 제외하고 3개  rownum 조회 순서 FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT
수치연산	+ - * / % MOD 연산순서 * / % > + -
별명 Alias	AS '별명'  : ASCII 문자 이외의 것.  AS "별명"
Null	null +1 , 1+null, 2*null, 1/null   .>> 모두 null
함수	MOD(10,3)  -> 1 round truncate
문자열연산	'ABC' || '1234' => 'ABC1234'   + : SQL Server || : Oracle, DB2, PostgreSQL CONCAT : Mysql  Substring Trim 문자세트 ex) EUC-KR or UTF-8 EUC-KR ASCII 문자1바이트, 한글 2바이트  UTF-8 ASCII 문자 1바이트 한글 3바이트.
날짜 연산	SELECT CURRENT_TIMESTAMP; TO_DATE('2014/01/25', 'YYYY/MM/DD') SELECT CURRENT_DATE + INTERVAL 1 DAY;
CASE	CASE WHEN 조건1 THEN 식1            [WHEN 조건2 THEN 식2]            [else 식 3]                              // 생략시 null   처리 주의  END             CASE WHERE 조건1 THEN 값1            [WHEN 조건2 THEN 값2]            [else 값3 ] END decode (Oracle) null 체크 NVL (Oracle) isnull(SQL server) COALESCE  : 표준SQL
DML	insert into 테이블(열...) values( 값...); null, default delete from 테이블명 where 조건식.  update 테이블명 set 열=값  ... where 조건식   ... Oracle은 set순서 상관없고 mysql은 영향있음.  물리 삭제 : 실제 삭제 논리 삭제 : 삭제 플래그
집계함수	count, sum, avg, min, max, distinct count(*) 불가. : count 가 먼저 계산됨.  count(distinct name) WHERE > GROUP BY > HAVING >SELECT > ORDER by Having : 검색한뒤 그룹화  cf) having count(name)=1   200쪽부터 보기

 

DBMS 아키텍처
1. SQL 구문입력	웹 or 애플리케이션 or SQL 인터페이스 -> SQL 구문
2. 상호연계    (RDBMS)	1. 쿼리 평가엔진	플랜 실행 기능, 파서, 연산평가 기능, 옵티마이저		SQL 구문 분석, 실행계획(어떤순서 기억장치 데이터 접근 결정)
	2. 접근 메서드	->트랜젝션메니저	┓ ┿>리커버리 메니저 ┘	실행계획에 기반을 둬서 데이터에 접근
	3. 버퍼 메니저	->락메니저		버퍼 용도로 사용하는 메모리 영역 디스크 용량 매니저와 연동작동
	4. 디스크 용량 매니저	동시 실행제어		어떻게 데이터를 저장할것인가
	트랜젝션 메니저			각각의 처리 트랜젝션으로 관리
	락메니저			대기시킴
	리커버리 매니저			장애를 대비해 백업, 복구를 수행
3.참조	인덱스파일 -> 데이터 파일		: 시스템 카탈로그

Trade-off  어느것을 얻으려면 반드시 다른 것을 희생하여야 하는 경제 관계

Query : 질의 좁은의미 select 구문

DBMS 와 버퍼
기억 장치의 계층	1차 기억장치  - 레지스트 메모리	2차 기억장치 -HDD, CD, DVD, 플레시메모리	3차 기억장치 테이프	1차일수록 비쌈, 고가, 접근속도 빠름
DBMS 기억장치	하드디스크, 메모리, 버퍼를 활용한 속도 향상 버퍼, 캐시 : 성능향상을 목적으로 데이터 저장 메모리  cf) 매우 적은 양의 데이터에만 접근하는 SQL 구문은 저장소 I/O  보다 CPU 연산에 많은시간을씀
데이터 캐시	초기값 128MB	버퍼캐시(ORACLE), 버퍼풀(Mysql) 초깃값: 128MB(Mysql)		SELECT 구문에서 버퍼에서 데이터 있으면 그것 사용.
로그 버퍼	초기값 64KB	innodb_log_buffer_size 16777216 초깃값: 8MB(Mysql)		갱신처리(commit)시 로그버퍼에 변경정보 보내고 디스크 변경 >> 성능 보완, 메모리 휘발성 보안
로그

buffer 완충제, 

 

 

RDBMS	레코드 필드 테이블 인덱스 뷰 프로시저 함수 제약사항	로, 행, 튜플 컬럼, 열, 어트리뷰트  릴레이션 키-필드만 갖고 있는 테이블에 대한 동작속도 높여주는 자료구조 여러 테이블, 뷰의 데이터를 조합하여 만듬,  테이블 아닌 테이블처럼 보여줌 프로세스를 절차적으로 기술 CREATE OR REPLACE PROCEDURE 특정 계산 수행, 반드시 리턴값 하나 존재,  CREATE OR REPLACE FUNCTION 무결성 보장하기 위한 데이터베이스 객체중하나.
무결성 제약조건	데이터 정확성, 일관성	일관성, 정확성 보장위해 저장, 삭제, 수정등을 제약하기 위한 조건.  개체 무결성 : 기본키는 null, 중복값 불가 참조 무결성 : null 이거나 참조하는 릴레이션 기본키와 동일 도메인 무결성 : 속성값은 정의된 도메인값 ex) 남, 여 만 넣을수있는 조건 고유 무결성: 고유값 조건이 주어지면 같은값 없어야 Null무결성 : not null 조건 키 무결성 : 최소 테이블당 한개 이상의 키
쿼리문 종류	DDL DML DCL TCL	데이터 정의 어, DB 오브젝트 생성, 삭제 변경 ex) create 데이터 조작 어, DB 조회, 삽입, 삭제, 변경 ex)select 데이터 제어 어, 사용자 권한 관리 ex) grant 트랜젝션 제어어 ex) commit, rollback..
명명규칙	DB 키워드	책마다 기준이 달라서 추후 정리 https://docs.actian.com/ingres/11.0/index.html#page/CharQueryRepUser/Recognition_of_Delimited_Identifiers.htm
타입	INT DECIMAL CHAR VARCHAR DATE	정수 실수 고정길이 문자열 가변길이 문자열 날짜
CHAR(10) VS VARCHAR(10)	abc 작성시 char 는 10바이트를 다쓰고 varchar는 7바이트를 사용.  단 정확히 10자인 데이터를 저장할 때는 char 가 유리.  char는 길이기 일정해 속도가 빠르다.
Number vs int vs uint	number int uint	64비트, NaN, 소수점까지 표현 oracle 은 int를 number로 선언사용 32비트 정수 unsigned int , 양수만 표현 https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021
SQL 실행순서	From > (join 조건)ON > JOIN >  WHERE >  GROUP BY > HAVING > SELECT
Having	그룹화 이후 데이터 추출조건
집계함수	sum avg min max stddev / stdev(ms) variance	총합 평균 최소값 최댓값 표준편차 분산
truncate table 이름		테이블 전체 비울때
서브쿼리 연산자	ANY(서브쿼리) ALL(서브쿼리) exists(서브쿼리)	하나만 참이어도  모두 참일때 결과가 있는지만 확인
연관서브쿼리	필드, (서브쿼리)	select name , (select id from classA where classA.id = student.id) from student
인라인뷰	from (서브쿼리)
테이블 뷰	UNION Intersect minus	합집합 교집합 차집합
함수	스칼라 함수 집계함수 시스템함수 문자열함수	수학함수 sum, avg, count 널 관련 처리, 타입변환 문자길이, substring, 케이스 변환
CTE WITH ()	Common Table Expressions	쿼리 실행중에 메모리에 존재하는 테이블  with 테이블명(필드명들) as()
over		집계함수 보조.  select name, sum(salary) over() as 월급총합 from employee;
rank()		select rank() over(order by salary desc), * from  employee
통계 관련	ntile lag lead percentile pivot

 

	ORACLE	SQLServer	Mysql
데이터베이스보기	SELECT NAME FROM v$database;	SELECT name FROM master.sys.databases	show databases;
페이징 관련	의사컬럼(Pseudo column) : 인위적추가된 컬럼  ex)rownum, rowid	top, limit
offset fetch	order by 필드 offset 건너뛸 행수 rows fetch next 출력할 행수 rows only
	order by 생략가능 offset 0 rows 생략가능	order by 반드시 offset 0 rows 반드시	limit 가 유사기능
시퀀스	CREATE SEQUENCE emp_seq        INCREMENT BY 1        START WITH 1        MINVALUE 1        MAXVALUE 9999        NOCYCLE        NOCACHE        NOORDER;	CREATE SEQUENCE Schema.SequenceName AS int START WITH 1 INCREMENT BY 1 ;
	GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] ex) CREATE TABLE STUDENT( no INT GENERATED AS IDENTITY ​PRIMARY KEY );​ Number 타입	BigInt	CREATE TABLE student( no INT NOT NULL AUTO_INCREMENT PRIMARY KEY );​
숫자	Number Number(최대자리수, 소수점이하정밀도) Number(5, -3) 천의자리 반올림	Decimal or Numeric
날짜	SYSDATE select sysdate + 12 from dual;	GETDATE()	NOW()