language:php:php와mysql-ch78-db-mysql [KIEUN's Wiki 문기은 위키]

사용자 도구

사이트 도구


language:php:php와mysql-ch78-db-mysql

8 - 웹 DB 디자인

  • PrimaryKey : 한 테이블에서 각 데이터를 구분하는 식별자 역할을 하는 열. key 또는 PrimaryKey라고 한다.
  • ForeignKey : PrimaryKey가 다른 테이블에서 사용되는 경우, ForeignKey가 된다.
  • 웹DB디자인시 유의사항
    • Table에 중복 data를 두지 말자.
    • 하나의 항목에는 하나의 데이터만
    • 사용할 쿼리를 생각하면서 작성
    • 빈내용이 많지 않게 작성

그외 건질게 없다.

9 - 웹 DB 만들기

  • 문장 끝은 ';'로 끝난다.
  • mysql 서버 접속
    mysql -h [hostname] -u [username] -p [press enter key]
    • -h [hostname] : [hostname] 부분에 서버 아이피나 이름을 넣는다.
    • -u [username] : [username] 옵션을 적지 않으면 현재 로그인한 유저 아이디로 로그인한다.
    • -p 암호. 적는 부분이 아니고, -p를 입력하면 암호 입력 화면이 다시 보인다.
    • [press enter key] : 진짜로 엔터키를 입력하라고 적었다.

DB 만들기

CREATE DATABASE DATABASENAME;

DATABASENAME는 새로운 DB이름을 넣는다.

이렇게 해도 되지만, 인코딩 방식과 DB 타입을 적어주지 않으면 이상하게 생성될 수 있으니 주의.

사용자 권한 : GRANT

사용자의 권한을 4단계에 맞춰 설정하는 명령어.

  1. Global
  2. Database
  3. Table
  4. Column

grant의 일반적인 형식을 보면,

GRANT privileges [COLUMNS] 
ON item
TO user_name [IDENTIFIED BY 'password']
[REQUIRE ssl_options]
[WITH [GRANT OPTION | limit_option] ]

'[ ]'에 있는 것은 옵션.

  • 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 권한 허용.

GRANT ALL ON *
  TO fred IDENTIFIED BY 'mnb123'
	WITH GRANT OPTION;

권한 취소

REVOKE ALL privileges, GRANT, FROM fred;

권한 없는 일반 사용자

GRANT usage ON books.* TO sally IDENTIFIED BY 'magic123';

abc에게 필요한 권한 부여

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP ON books.* TO abc;

abc에게서 일부 삭제

REVOKE ALTER, CREATE, DROP ON books.* FROM abc;

웹을 위한 사용자 설정

select, insert, delete, update 네가지면 기본은 충분하다,라고 한다.

GRANT SELECT, INSERT, DELETE, UPDATE ON SomeDB.* TO {id} IDENTIFIED BY '{password}';

모두 하고 싶다면,

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP
	ON SomeDB.* 
	TO {id} IDENTIFIED BY '{password}';

DB Table 만들기

기본 구문

CREATE TABLE {tablename}( {COLUMNS} )

별도 sql 파일로 생성 스크립트가 있다면

mysql -h {host} -u {userId} -D {dbname} -p < {fileName}.sql

{filename}.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
);

생성하는 컬럼을 적어준다… 너무 당연한가.

키워드의 의미

NOT NULL : 반드시 값이 있어야한다.

AUTO_INCREMENT : 한 행의 데이터를 입력한다면, 숫자가 자동 증가된다.

PRIMARY KEY : 이 열이 테이블의 기본 키라는 뜻.

integer : 정수 데이터 형 뒤에 붙는 unsigned는 데이터가 0 이상의 양수여야 한다는 뜻.

CHAR은 고정 버퍼, VARCHAR는 사용한 만큼만 공간할당, VARCHAR가 더 느리다고 한다.. 왜 그러지? 사이즈가 변경 될때? 그렇다는 건가?

db 살펴보기

SHOW TABLES;	-- 테이블의 목록을 보여준다.
SHOW DATABASES;	-- 데이터베이스 목록을 보여준다.
DESCRIBE {TABLE}; -- 테이블 컬럼을 보여준다.

인덱스 만들기

기본 키는 인덱스를 생성한다.

기본 키가 아닌 다른 열에 인덱스를 추가할 수 있다.

CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON TABLE_NAME (index_column_name [(LENGTH) [ASC|DESC],...])

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)] -231 ~ 231-1 / 0 ~ 232-1 4 보통의 정수
INTEGER[(m)] int와 같다
BIGINT[(m)] -263 ~ 263-1 / 0 ~ 264-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|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 | ASCII | UNICODE]
0 ~ 255 길이==m
NATIONAL : 기본 문자 집합 사용
binary : 대소문자 구분 없다
CHAR CHAR(1)과 같다
[NATIONAL]
VARCHAR(m)
[BINARY]
1~255문자 가변 길이 지원 외, char과 같다

text, blob

사이즈가 용도를 결정해준다.

type size 설명
TINYBLOB 28-1(255)
TINYTEXT 28-1(255)
BLOB 216-1(65,535)
TEXT 216-1(65,535)
MEDIUMBLOB 224-1(16,777,215)
MEDIUMTEXT 224-1(16,777,215)
LONGBLOB 232-1(4,294,967,295)
LONGTEXT 232-1(4,294,967,295)

enum, set

type 집합에 들어갈 수 있는 최대 개수 설명
ENUM( 'v1', 'v2', … ) 65,535 정의된 value 중에 하나만 들어가거나 NULL
SET( 'v1', 'v2', … ) 64 정어된 value를 여러개 사용한 데이터가 들어가거나 NULL
language/php/php와mysql-ch78-db-mysql.txt · 마지막으로 수정됨: 2013/05/24 12:19 저자 kieuns