GINS WIKI 긴스 위키

사용자 도구

사이트 도구

현재 위치: 긴스위키 GINS wiki » 개발 언어 » php » 웹 DB 디자인
추적:
language:php:php와mysql-ch78-db-mysql

차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

다음 판		이전 판
language:php:php와mysql-ch78-db-mysql [2013/05/24 12:19] – 새로 만듦 kieunslanguage:php:php와mysql-ch78-db-mysql [2024/04/23 22:44] (현재) – 바깥 편집 127.0.0.1
줄 1: 줄 1:
 +{{page>language:php:php_문서_목차&noheader&nofooter&noeditbtn&firstseconly}}
  
 +====== 웹 DB 디자인 ======
 +
 +  * PrimaryKey : 한 테이블에서 각 데이터를 구분하는 식별자 역할을 하는 열. key 또는 PrimaryKey라고 한다.
 +  * ForeignKey : PrimaryKey가 다른 테이블에서 사용되는 경우, ForeignKey가 된다．
 +  * 웹DB디자인시 유의사항
 +    * Table에 중복 data를 두지 말자.
 +    * 하나의 항목에는 하나의 데이터만
 +    * 사용할 쿼리를 생각하면서 작성
 +    * 빈내용이 많지 않게 작성
 +
 +그외 건질게 없다.
 +
 +====== 웹 DB 만들기 ======
 +
 +  * 문장 끝은 ';'로 끝난다.
 +  * mysql 서버 접속 <code bash>mysql -h [hostname] -u [username] -p [press enter key]</code>
 +    * -h [hostname] : [hostname] 부분에 서버 아이피나 이름을 넣는다.
 +    * -u [username] : [username] 옵션을 적지 않으면 현재 로그인한 유저 아이디로 로그인한다.
 +    * -p 암호. 적는 부분이 아니고, -p를 입력하면 암호 입력 화면이 다시 보인다.
 +    * [press enter key] : 진짜로 엔터키를 입력하라고 적었다.
 +
 +===== DB 만들기 =====
 +
 +<code sql>
 +create database DATABASENAME;
 +</code>
 +
 +DATABASENAME는 새로운 DB이름을 넣는다.
 +
 +이렇게 해도 되지만, 인코딩 방식과 DB 타입을 적어주지 않으면 이상하게 생성될 수 있으니 주의.
 +
 +===== 사용자 권한 : GRANT =====
 +
 +사용자의 권한을 4단계에 맞춰 설정하는 명령어.
 +
 +  - Global
 +  - Database
 +  - Table
 +  - Column
 +
 +grant의 일반적인 형식을 보면,
 +<code sql>
 +GRANT privileges [columns] 
 +ON item
 +TO user_name [IDENTIFIED BY 'password']
 +[REQUIRE ssl_options]
 +[WITH [GRANT OPTION | limit_option] ]
 +</code>
 +
 +'[ ]'에 있는 것은 옵션.
 +
 +  * privileges : 허용할 권한을 적는다. 한개 이상이면 ','로 구분해서 적는다.
 +  * [columns] : 옵션이고, 열 단위로 지정해줄 수 있게 한다. 권한이 적용 되는 열 이름들을 안에 써준다. 여러개면 ','로 구분.
 +  * item : 권한이 적용될, DB나 테이블 이름을 넣어준다. 모두 적용하려면 *.* 라고 입력한다.
 +    * DBName.* : 특정 db에 모든 테이블
 +    * DBName.DBTable : 특정 db, 특정 테이블
 +  * user_name : mysql에 접속할 사용자의 이름. 사용자 이름은 호스트 이름까지 포함한다. ( xyz@localhost, xyz@somewhere.com )
 +  * 'password' : 사용자 암호.
 +  * REQUIRE : SSL을 통해서만 접속할 수 있다고 정하는데 사용한다.
 +  * WITH [GRANT OPTION] : 권한 이외의 다른 옵션을 설정하는데 사용한다.
 +    * MAX_QUERIES_PER_HOUR (n) : 시간당 쿼리 제한
 +    * MAX_UPDATES_PER_HOUR (n) : 시간당 업데이트 제한
 +    * MAX_CONNECTIONS_PER_HOUR (n) : 시간당 접속 수 제한
 +
 +권한은 mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv에 저장된다.
 +
 +===== 권한의 종류와 레벨 =====
 +
 +^ 명령어  ^ 대상 ^ 설명 ^
 +| SELECT | table,col | 선택만 |
 +| INSERT | table,col | 추가만 |
 +| UPDATE | table,col | 갱신만 |
 +| DELETE | table | 테이블 행 삭제 |
 +| INDEX  | table | 인덱스 추가나 삭제 |
 +| ALTER  | table | 테이블 구조 변경 - 열 추가, 열이나 테이블 이름 변경, 열의 데이터 형 변경 |
 +| CREATE | table |  |
 +| DROP   | table | 테이블 삭제 |
 +
 +관리자 권한
 +
 +| CREATE TEMPORARY TABLES | CREATE TABLES문에 TEMPORARY을 사용할 수 있게 허가...가 무슨 뜻일까? |
 +| FILE | 파일의 데이터가 읽혀지는 것을 가능하게. ( 그 반대도 ) |
 +| LOCK TABLES | LOCK TABLES 를 직접 사용할 수 있게 |
 +| PROCESS | 관리자가 사용자에게 속한 서버에서 실행되는 프로세스들에 대해서 볼 수 있게 한다. (?) |
 +| RELOAD | grant를 테이블을 다시 읽어들여 권한,호스트,로그,테이블을 최신 정보로 갱신한다 |
 +| REPLICATION CLIENT | 복사 마스터와 슬레이브에 대해 show status를 할 수 있다. |
 +| REPLICATION SLAVE | 복사 슬레이브 서버가 마스터 서버에 접속 할 수 있게 한다. |
 +| SHOW DATABASES | 모든 db 이름을 볼 수 있다. 이것도 권한이 필요한 거로군 |
 +| SHUTDOWN | 정지 가능 |
 +| SUPER | 관리자가 사용자에 속한 스레드를 종료할 수 있다. (?) |
 +
 +특별한 권한
 +
 +| ALL | 모든 권한. ALL PRIVILEGES |
 +| USAGE | NO 권한 |
 +
 +===== revoke 명령어 =====
 +
 +사용자로부터 권한을 뺏을때 사용한다.
 +
 +===== grant와 revoke의 예제 =====
 +
 +fred에게 모든 DB 권한 허용.
 +<code sql>
 +grant all on *
 +  to fred identified by 'mnb123'
 + with grant option;
 +</code>
 +
 +권한 취소
 +<code sql>
 +revoke all privileges, grant, from fred;
 +</code>
 +
 +권한 없는 일반 사용자
 +<code sql>
 +grant usage on books.* to sally identified by 'magic123';
 +</code>
 +
 +abc에게 필요한 권한 부여
 +<code sql>
 +grant select, insert, update, delete, index, alter, create, drop on books.* to abc;
 +</code>
 +
 +abc에게서 일부 삭제
 +<code sql>
 +revoke alter, create, drop on books.* from abc;
 +</code>
 +
 +===== 웹을 위한 사용자 설정 =====
 +
 +select, insert, delete, update 네가지면 기본은 충분하다,라고 한다.
 +
 +<code sql>
 +grant select, insert, delete, update on SomeDB.* to {id} identified by '{password}';
 +</code>
 +
 +모두 하고 싶다면,
 +
 +<code sql>
 +grant select, insert, update, delete, index, alter, create, drop
 + on SomeDB.* 
 + to {id} identified by '{password}';
 +</code>
 +
 +====== DB Table 만들기 ======
 +
 +기본 구문
 +
 +<code sql>
 +CREATE TABLE {tablename}( {columns} )
 +</code>
 +
 +별도 sql 파일로 생성 스크립트가 있다면
 +
 +<code sql>
 +mysql -h {host} -u {userId} -D {dbname} -p < {fileName}.sql
 +</code>
 +
 +{filename}.sql -> 샘플로 입력
 +
 +<code sql>
 +-- 샘플로 입력하는 스크립트, 책과 정확하게 일치하지는 않는다.
 +create table customers (
 + customerid int unsigned not null auto_increment primary key,
 + name char(50) not null,
 + address char(100) not null,
 + city char(30) not null
 +);
 +create table xxyy ( -- 다른 컬럼들 추가
 + quantity tinyint unsigned,
 + -- ...
 + amount float(6,2),
 + date date not null,
 + -- ...
 + primary key( orderid, isbn ),
 + -- ...
 + review text
 +);
 +</code>
 +
 +생성하는 컬럼을 적어준다... 너무 당연한가.
 +
 +===== 키워드의 의미 =====
 +
 +**NOT NULL** : 반드시 값이 있어야한다.
 +
 +**AUTO_INCREMENT** : 한 행의 데이터를 입력한다면, 숫자가 자동 증가된다.
 +
 +**PRIMARY KEY** : 이 열이 테이블의 기본 키라는 뜻.
 +
 +**integer** : 정수 데이터 형 뒤에 붙는 **unsigned**는 데이터가 0 이상의 양수여야 한다는 뜻.
 +
 +CHAR은 고정 버퍼, VARCHAR는 사용한 만큼만 공간할당, VARCHAR가 더 느리다고 한다.. 왜 그러지? 사이즈가 변경 될때? 그렇다는 건가?
 +
 +===== db 살펴보기 =====
 +
 +<code sql>
 +show tables; -- 테이블의 목록을 보여준다.
 +show databases; -- 데이터베이스 목록을 보여준다.
 +describe {table}; -- 테이블 컬럼을 보여준다.
 +</code>
 +
 +===== 인덱스 만들기 =====
 +
 +기본 키는 인덱스를 생성한다.
 +
 +기본 키가 아닌 다른 열에 인덱스를 추가할 수 있다.
 +
 +<code sql>
 +CREATE [UNIQUE|FULLTEXT] INDEX index_name
 +ON table_name (index_column_name [(length) [ASC|DESC],...])
 +</code>
 +
 +length 옵션을 사용해서 일정 자리수 만큼 인덱스로 사용할 수 있다.
 +
 +====== MySQL 식별자 ======
 +
 +패스
 +
 +====== 열의 데이터 형(type) ======
 +
 +옵션은 [,] 사이에 넣는다.
 +()안의 m은 크기에 해당하는 숫자를 입력.
 +
 +===== 숫자 =====
 +
 +  * unsigned : 추가해서 양수로 설정 가능.
 +  * zerofill : 데이터를 화면에 출력할때, 앞부분에 0을 자동으로 붙인다.
 +
 +==== 정수 ====
 +
 +^ 타입 ^ 범위 ^ 저장공간(바이트) ^ 설명 ^
 +| TINYINT[(m)]   | -127 ~ 128, 0 ~ 255 | 1 | 작은 정수 |
 +| BIT            | | | tinyint 와 같다 |
 +| BOOL           | | | tinyint 와 같다 |
 +| SMALLINT[(m)]  | -32768 ~ 32767 / 0 ~ 65535 | 2 | 작은 정수 |
 +| MEDIUMINT[(m)] | -8388608 ~ 8388607 / 0 ~ 16777215 | 3 | 보통크기 |
 +| INT[(m)]       | -2<sup>31</sup> ~ 2<sup>31</sup>-1 / 0 ~ 2<sup>32</sup>-1 | 4 | 보통의 정수 |
 +| INTEGER[(m)]   | | | int와 같다 | 
 +| BIGINT[(m)]    | -2<sup>63</sup> ~ 2<sup>63</sup>-1 / 0 ~ 2<sup>64</sup>-1 | 8 | 큰 정수 |
 +
 +==== 부동소수의 표기 ====
 +
 +참고 :
 +  * E를 사용하는 표기법에서는 소수점(.)을 E 다음 나오는 숫자만큼 앞,뒤로 이동시킨다.
 +  * 123456789.1234567 --> 1.234567891234567e+8
 +  * 0.00000000000000000001 --> 1e-20
 +
 +^ 타입 ^ 범위 ^ 저장공간(바이트) ^ 설명 ^
 +| FLOAT(precision) | precision 값에 따라 다르다 | 다양 | 32비트,64비트 부동소수점 |
 +| FLOAT[(m,d)]     | ±1.175494351E-38 / \\ ±3.402823466E+38 | 4 | 32비트 부동소수로 float(4)와 같은데 \\ 전체 자리수와 소수점 자리수를 정해줄 수 있다 |
 +| DOUBLE[(m,d)]    | ±1.7976931348623157E+308 / \\ ±2.2250738585072014E-308 | 8 | float(8)과 같은데, 전체 자리수와 소수점 자리수를 정할 수 있다 |
 +| DOUBLE \\ PRECISION[(m,d)] | 위와 같다 | | double[(m,d)] 같다 |
 +| REAL[(m,d)] | 위와 같다 | | double[(m,d)] 같다 |
 +| DECIMAL[(m[,d])] | 가변 | m 2 | char형으로 저장됨. m에 의해 범위 결정 |
 +| NUMERIC[(m,d)] | 위와 같음 | | DECIMAL과 같다 |
 +| DEC[(m,d)] | 위와 같음 | | DECIMAL과 같다 |
 +| FIXED[(m,d)] | 위와 같음 | | DECIMAL과 같다 |
 +====== 날짜,시간 =====
 +
 +^ type ^ 범위 ^ 설명 ^
 +| DATE | 1000-01-01 / 9999-12-31 | YYYY-MM-DD 형식의 날짜 |
 +| TIME | -838:59:59 / 838:59:59 | HH:MM:SS 형식으로 표기 \\ 시간값이 실생활보다 값의 범위가 크단다 |
 +| DATETIME | 1000-01-01 00:00:00 / 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS 형식 |
 +| TIMESTAMP[(m)] | 1970-01-01 00:00:00 | 타임스탬프. 데이터의 최종 변경 시각을 저장하는데 유용. \\ m 값에 따라 표기 형식이 달라진다. 한계값은 2037 |
 +| YEAR[(2<nowiki>|</nowiki>4)] | 70 ~ 69(1970-2069) \\ 1901~2155 | 년도, 2자리나 4자리 형식에 맞춰 표기 | 
 +
 +===== 타임스탬프 =====
 +
 +^ 종류 ^ 표기 형식 ^
 +| TIMESTAMP     | YYYYMMDDHHMMSS |
 +| TIMESTAMP(14) | YYYYMMDDHHMMSS |
 +| TIMESTAMP(12) | YYMMDDHHMMSS |
 +| TIMESTAMP(10) | YYMMDDHHMM |
 +| TIMESTAMP(8)  | YYYYMMDD |
 +| TIMESTAMP(6)  | YYMMDD |
 +| TIMESTAMP(4)  | YYMM |
 +| TIMESTAMP(2)  | YY |====== 문자열 데이터 형 =====
 +
 +  * char(고정된 길이의 문자) : 데이터 길이가 고정길이보다 작다면 특별한 문자(?)가 나머지를 채운다.고 하네.
 +  * varchar(가변 길이의 문자) : 길이만큼만 유지된다.
 +  * TEXT : 긴 길이의(? 어느정도의 길이?) 문장 저장
 +  * BLOB : 이진데이터를 저장. 이미지,사운드 데이터도 저장 가능.
 +  * SET : 여기에 들어가는 데이터는 미리 정의한 집합 안의 원소들로만 되어야(넣을 수 있다가 아니고) 한다. 집합에는 원소가 64개 가능.
 +  * ENUM : 미리 정의된 집합 안의 원소에 있는 값만 데이터로 저장. 집합에는 65535까지 가능.
 +
 +^ 연산자 ^ 사용법 ^ 동일식 ^
 +| [NATIONAL] \\ CHAR(m) \\ [BINARY <nowiki>|</nowiki> ASCII <nowiki>|</nowiki> UNICODE] | 0 ~ 255 | 길이==m \\ **NATIONAL** : 기본 문자 집합 사용 \\ **binary** : 대소문자 구분 없다 |
 +| CHAR | | CHAR(1)과 같다 |
 +| [NATIONAL] \\ VARCHAR(m) \\ [BINARY] | 1~255문자 | 가변 길이 지원 외, char과 같다 |
 +
 +text, blob
 +
 +사이즈가 용도를 결정해준다.
 +
 +^ type ^ size ^ 설명 ^
 +| TINYBLOB | 2<sup>8</sup>-1(255) | |
 +| TINYTEXT | 2<sup>8</sup>-1(255) | |
 +| BLOB | 2<sup>16</sup>-1(65,535) | |
 +| TEXT | 2<sup>16</sup>-1(65,535) | |
 +| MEDIUMBLOB | 2<sup>24</sup>-1(16,777,215) | |
 +| MEDIUMTEXT | 2<sup>24</sup>-1(16,777,215) | |
 +| LONGBLOB | 2<sup>32</sup>-1(4,294,967,295) | |
 +| LONGTEXT | 2<sup>32</sup>-1(4,294,967,295) | |
 +
 +enum, set
 +
 +^ type ^ 집합에 들어갈 수 있는 최대 개수 ^ 설명 ^
 +| ENUM( 'v1', 'v2', ... ) | 65,535 | 정의된 value 중에 하나만 들어가거나 NULL |
 +| SET( 'v1', 'v2', ... ) | 64 | 정어된 value를 여러개 사용한 데이터가 들어가거나 NULL |