농부지기

* SQL Server 교육 *

1. 사용자별 CPU
   - Oracle인 경우 Process방식
     . 1인 접속자 기준으로 PGA가 설정되면 CPU가 할당 된다.
     . run -> wait -> ready
     . Context : run중 실행중이 였던 내용을 저장하는 곳.
     .
   - MS Sql인 경우 Thread방식
     . Core당 1개의 Process가 생성됨.
     . Thread 1개당 메모리 512kb 자원이 할당 됨
     . 4 Core기준으로 최대 512개 Thread 생성
     . 1명의 user가(1개의 select문장) cpu를 여러개 사용할 수 있다.

2. hint
   - MS Sql은 hint를 사용하지 않아서 Optimizer가 알아서 모든 처리를 자동으로 해줌.
   - 대용량 select문장을 기술시 패널널로 Optimzer가 수행 시켜줌.

3. Memory
   - 5개 영역으로 구성되어 있음
   - Utility, Lock, Query Work Space(dirty page)(rollback, commit등 페이지), Plan Cash(실행계획), Data Buffer Cash
   - Data Buffer Cash : 한번 읽었던 자원을 메모리 올리는데 이곳이 Data Buffer Cash영역이다.
                      : Logical Reading

4. Covering index
   - select시 조회 컬럼정보가  모두 index컬럼들이면 index영역에서 자료를 조회 하는 기준

5. Tuning
   - set statistics io on;
     set statistics time on;
   - page reading(logical, physical page)하는 갯수를 보여줌.  (자료 읽은 페이지수)
   - 최소 메모리사이즈는  db용량의 10%정도가 권장사항
   - Logical, Physical 속도 : 약 5배 차이
   - Hit 수  : Logical read 수 (ms 권장율은 97%)

6. sql
   - select * from a;
     . *을 사용 하지 말것
     . Covering index를 사용하기 위해서임
   - bind query
     . 한번 날라온 query를 재 사용하게 해줌
     . 대소문자 구분, 중간에 공백등에 의해서 queyr를 해석할지, 그냥 사용할지 구분함
   - addon query.
     . query문장을 재사용하지 않고 계속 sql문장을 재 해석 함.
  
 
7. Procedure
   - sp_ : 를 앞쪽에 붙이면 db서버는 Master db를 먼저 뒤짐
           즉, sp_는 system에 기본 설정 naming rule임
           고로 개발시 앞쪽에 sp_를 붙이면 안됨.
   - exec : 로 Procedure를 수행시 모든 sql문장은 재 해석함.
            addon query라는 용어를 사용함.
   - sp_executesql : 로 sql을 수행해야 sql을 재 해석하지 않음.
   - 해석 : sql문장 parsing, 실행계획 수립... 기타 등등.
  
  

.
8. function
   - f_ : 도 위와 같은 형태로 옮겨짐
   - 

9. schema
   - dbo : default를 사용
   - 되도록 schema는 생성하지 말것
   -

9. Table
   - name : 대문자 권장
   - varchar  : 1byte
     nvarchar : 2byte, unicode, 다국어 지원
                offset을 활용함
                하지만 값이 null인 경우에는 offset이 없음.
   - varchar 와 nvarchar join시 varchar가 nvarchar보다 작기 대문에 varchar가 navarchar로 converting됨
     이렇다 보면 index가 수행되지 않음 (내부적으로 함수를 사용했기 때문)
   - 8k * 8 = 1 extent (한개의 테이블이 속한 최소 단위)
   - nchar : 2byte형 char
           : offset 정보를 활용하지 않음. 자료수가 고정 되었기 때문에
   -

11. 1page(8k)
   - Header :
            : time stamp - 최종 수정시각
   - Footer : offset - varchar의 시작위치, 길이를 저장 함
                     - 모든 컬럼의 varchar에 대한 정보가 저장 됨
                     -

10. Sequence, indentity
   - indentity : int 보단 bigint를 사용
               : 컬럼의 type임
   - Sequence  : sequece 는 numeric이 좋음 (27,0) (8byte)
               : object임
   -

10. Partition
   - 단점 : index tree가 partion별로 구성 되어 있음
            이 단점을 극복하려면 where조건에 partion을 지정 해야됨
            (참고, oracle에서는 global index를 만듬)
   -

11. isolation
   - Latch, Lock
     . Latch : phycial (물리적인 자원)
             : memory, cpu, process등. 서버의 모든 자원이 Latch
             : page io latch : 조회시 내가 필요한 자원이 memory에 없을 경우 hdd에 가게 되는데 
                               이때 ... page io latch 가 발생
     . Lock  : logical (논리적인 자원)(자료)
             :

12. Lock
   - Share-Lock, eXeclusiv-Lock, Update-Lock, Schema-Lock
   - Share-Lock     :
   - eXeclusiv-Lock :
   - Update-Lock : update전에 where조건절로 찾을 때는 Share-Lock이 발생하고,
                   조건을 찾은 후 Update를 할때는 eXeclusiv-Lock 을 발생 시킴.
   - Schema-Lock :
13. Lock 에스컬레이션
   - Lock 범위 : key -> Row -> Page -> Table -> DB
               : 자동으로 momory등을 보고 위 단계가 상승됨
   - Lock hint를사용 해야됨
   -
  
14.  Undo
   - Oracle : 값을 update시 최종 commit전에는 이전 자료는 undo segment에 저장 됨
              그래서 다른 사용자가 update자료를 select하게 되면 undo segment에서 조회 한다.
   - MS SQL : undo 가능
             
   - Undo 단점 : 대량의 정보를 undate하려면 undo의 용량이 커짐
                 속도도 무한으로 생성 됨
  
  
  
  
  
  
              
select * from sys.sysprocess; 
---------------------------------------------------------------------------------------------

1. connect by

2. int, bigdecimal, numric

---------------------------------------------------------------------------------------------
 Loop join : 결과 자료가 적을 경우
 Hash join : 결과 자료가 많을 경우
  
  
  
  
  
  
  
  
  
  
  

[ MySQL.설치 후 기본 설정 ]

1. 정의 : Insert, Update, Delete시 MySql은  PK를 조건절에 존재 하지 않으면 오류 발생

   - 해결방법 : 메뉴 : Edit > Preferences > SQL Editor > 맨 밑에

                     [ ] Safe Updatges (rejects UPDATEs and DELETEs with no restrictions)  해제

   - 이 checkbox의 문구는 version별로 조금씩 다름.

[ MySQL - 임시 ]

--- 설치 후 환경설정 ----------------------------------------------------

Tomcat에서 getConnection 등과 같이 db Connection 을 가져 오기 위해서는

mysql-connector-java-3.0.7-stable-bin.jar 를 C:\Tomcat 4.1\common\lib

이곳에 위치 시켜야 한다.

--- My SQL 사용 명령어 ----------------------------------------------------

*. 설치 후

1. c:\mysql\bin>mysqld-max-nt  -install     ; MySQL 서비스를 Windows 에 등록한다

2. c:\mysql\bin>net start mysql             ;MySQL Client 를 실행한다

3. c:\mysql\bin>net stop mysql              ;MySQL Client 를 종료한다

4. c:\mysql\bin>mysql                       ; MySQL 의 서비스 데몬 수행

--- *. Command 명령어  - 일반명령어 ----------------------------------------------------

1. mysql>exit;                              ;MySQL Client를 실행

--- *. Command 명령어  - 데이터베이스관련 -------------------------------------------------

1. mysql>create database mydb;               ; mydb라는 데이터 베이스 생성 (folder는   c:\mysql\db\mydb)

2. mysql>show databases;                     ; database list를 보여줌

3. mysql>use mydb;                           ;mydb의 데이터 베이스를 사용하겠다는 의미.

4. mysql>drop database mydb;                 ; 해당 database 를 삭제

--- *. Command 명령어  - 테이블 관련 ----------------------------------------------------

1. mysql>show tables;                        ;해당 db에서 table list를 보여 준다.

2. mysql>desc mytables;                      ;해당 테이블에 대한 구조를 보여줌

   or    describe mytables;

3. mysql>drop table mytables;                ;해당 테이블 삭제

4. mysql>use mysql;

   mysql>update user set password=password('1234546') where user='root';

                                             ; 해당 Database(mysql)에 대한 root password를 변경

                                               신규 DB를 생성하면 user에 대한 테이블이 존재 하지 않는다.

select name, (to_days(now())-to_days(birth))/365 from pet;

select name, birth from pet where month(birth) = 10;

month(date_add(now(), interval 1 month));

mod(month(now()), 12) + 1;

WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

WHERE MONTH(birth) = MOD(MONTH(NOW()),12) + 1;

출력 결과를 다른 파일로 저장할 수 있다. 저장된 파일은 추가 작업의 출발점으로 활용될 수 있다.

shell> mysql < batch-file > mysql.out

출력 결과가 굉장히 많다면 배치모드로 실행시키고 페이저(일정한 페이지 줄수로 문서를 보여주는 프로그램을 통칭하는 명칭)를 통해 보면 좋을 것이다. 다음 처럼:

shell> mysqlk < batch-file | less

mysql에 user 만들기 ---------------------

1. mysql을 실행한다.

2. mysql은 기본적으로 mysql, test라는 데이터베이스가 존재한다. mysql데이터 베이스에는 user와 db 테이블이 있는데, user는 mysql에 접속할수있는 사용자를 관리하는 테이블이고 db 테이블은 사용자 별로 데이터베이스를 할당하는 테이블이다.

3. 먼저 create database silver; (데이터 베이스를 생성한다.)

4. grant all on silver.* to kouh; (kouh라는 아이디를 하나 만들면서 silver라는 데이터베이스를 할당해 준다. user와 db 테이블에 할당 되어 있다.

5. update user set password=password("1234") where user="kouh"; (kouh 아이디에 암호를 할당한다.)

6. update user set host="localhost" where user="kouh";

7. quit (mysql을 빠져나온다.)

8. mysqladmin reload

9. mysql -u kouh -p silver (kouh 아이디로 silver 데이터베이스에 접근한다.)

* 사실 root도 5번부터 해서 암호를 설정하여 보안을 유지해야 한다.

SELECT 결과를 text 파일로 저장하기 ---------------------

SELECT * INTO OUTFILE "저장할 파일 경로"

FIELDS TERMINATED BY "필드 구분자"

LINE TERMINATED BY "라인 구분자"

FROM "테이블명"

WHERE "조건"

예) select * into outfile "./album_code_tbl.txt" fields terminated by "|" lines terminated by "\r\n" from album_code_tbl;

[ 5. MySQL - TABLE 생성 ]

1. 테이블 생성

    - 테이블 생성 시 기본적으로 schema name을 입력해야 된다.

       그렇지 않으면 [ Error Code: 1064 ] 가 발생 한다.

       하지만 권한 설정을 해주면 schema name을 입력하지 않아도 가능하다. (아래 설명 존재)

2. Table생성한 script 입력 후 [번개] - [Execute] icon클릭

     - Script :  CREATE TABLE hanjul.DUAL

                          ( DUMMY   CHAR(1)    NOT   NUYLL );

      - [ Execute ] icon을 클릭 하면 Table 생성 됨

3. 아래는 Schema name을 미 입력 후 Table을 생성 하면

    - 아래 오류 발생

       [ Error Code: 1064. You have an error in your SQL syntax;  ]

4. Object(Table, Index, 등)를 생성 시 Schema 를 입력하지 않고 생성할 수 있는 방법

     - 오류 - [ Error Code: 1064. You have an error in your SQL syntax;  ]

     - 왼쪽 Navigator 에서 > MANAGERMENT > Users and Privileges  클릭

5. Schema에 권한 부여 창 뜸

    - User Accounts : hanjul

    - [Schema Privileages] Tab

    - [Add Entry]버튼 클릭

6. Schema 선택

     - [ OK ] 버튼 클릭

7. 권한 부여

    - [Select ALL]버튼 클릭

       . 권한부여하고 싶은 항목을 선택 하거나 [Select ALL]버튼을 클릭 해서 모두 선택 함

    - [ Apply ] 버튼 클릭

8. 위 까지 완료 되면 hanjul schema에 권한이 다 적용 되었다.

    - 그런데, 이때 반드시 MySQL Workbench를 닫고 다시 실행 해야 된다.

[ 4. MySQL - DB 접속 ]

1. 첫 DB 접속

   -. MySQL Workbench에서 DB를 접속 하려면 접속 Connection정보를 생성해야 된다.

2. Home 화면에서

    - [+]버튼을 클릭

3. Connection 환경 정보 창

     - Connection Name : Connection 명 (Home화면에 보여줄 명)

     - Username             : 생성한 User

     - Default schema     : 생성한 db명

     - [OK] 버튼 클릭

4. DB접속 환경 정보 생성 완료

    - 위 화면에서 생성한 Connection명으로   Home 화면에 생성 됨

    - 이제 실제 db접속

    - connection name - [hanjul] 클릭

5. db접속가능 id/pw문의

    - pw를 입력 후 [OK]

6. DB접속 되고  DB관리할 수 있는 최상단에 [Sheet]가 생성 됨

7. 테이블 생성 해봄 Good

[ 3. MySQL - DB 생성 ]

1. 첫 Database만들기

    - MySQL Workbech를 실행

    - 아래 화면이 나오면 [Local Instance MySQL 57] 클릭

2. root 로 접속

    - 초기 설치시 입력한 root pw를 입력 후 [ok]

3. root는 db생성권한이 있기 때문에   root권한에 맞게 icon들이 보임

    - [ Create a new schema in the connected server ] icon 클릭

4. DB생성할 수 있도록 Schema Tab이 조회 됨

    - Name : [ 신규db ]입력

     - [ Apply ] 버튼 클릭

5. 위 과정을 거치면 MySQL Workbench는 db를 만들지

    않고DB를 생성하는 script문장을 만들어 준다.

     - [ Apply ]버튼을 클릭

6. 최종 DB 생성 후 확인 창

[ 2. MySQL - Workbench 설치 ]

1. 참고

    - MySQL을 설치할때 WorkBench도 설치 한다고 되어 있는데.. 이상하게 설치가 안되었다.

       그래서 추가 적으로 WorkBench를 설치

    - 이상하게 Workbench가 한번에 설치가 잘 안됐다.

       난 여러번 해서 성공 ^^

2. MySQL Installer 실행

    - 시작 > 모든 프로그램

       > MySQL > MySQL Installer - Community > MySQL Installer - Community

    - [ Add ] click

3. 설치할 여러가지 목록이 조회 됨

   : Application > MySQL Workbench >MySQL Workbench 6.3

                       > MySQL Workbench 6.3.8 - x86 선택

4. Workbench를 설치 하려먼 MS Visual C++ Redistiributable관련 모듈을 필수 설치야 됨

    - 아래가 안뜨면 여러면 [취소]하고 재 실행하면 뜸.. ㅋㅋ

5. [Execute]버튼 클릭

6. 위 [5]번에서 설치가 안되는 경우

    1. 사유        :  .Net Framework 4.5.2    가 설치가 안되어 있는 경우

        해결방법 : 위 파일을 찾아 설치

    2. .Net Framework 4.5.2 
         URL : https://www.microsoft.com/ko-KR/download/

         위 site에서 가서 [.Net Framework 4.5.2 ] 으로 검색 후 각 OS에 맞게 download 후 설치

** MySql 설치 **

1. download URL : http://dev.mysql.com/downloads/mysql/

2. 파일명 : MySQL Installer 5.7

                  32-bit용 

                  mysql-5.6.17-winx64.zip

3. Setup 시작

    1. License Agreement        : [v] I Accept the license terms.

    2. Choosing a Setup Type : (o) Full

    3. Check Requirements     : [Execute]

    4. [팝업] : [v] I agree

    5.

** mysql 관리 Tool **

1. URL : http://bryan7.tistory.com/109