MySQL 사용 루트패스워드지정 mysqladmin -u root password ' 새로운패스워드 ' MySQL 의기본설정을그대로두면누구나사용자이름과패스워드를입력하지않고데이터베이스에접근할수있다 이상태는아주위험하므로보안을위해익명사용자를반드지삭제해야한다 MySQL 사용하

Transcription

1 MySQL 사용 루트패스워드지정 mysqladmin -u root password ' 새로운패스워드 ' MySQL 의기본설정을그대로두면누구나사용자이름과패스워드를입력하지않고데이터베이스에접근할수있다 이상태는아주위험하므로보안을위해익명사용자를반드지삭제해야한다 MySQL 사용하기 MySQL 명령어들은 " ; " 으로끝난다, " ; " 은 MySQL에게명령을실행시키라고명령한다는뜻이다따라서 " ; " 을빠뜨리면아무것도실행되지않는다 " ; " 을생략하면명령어중갂에다음줄로넘어갂다, 긴명령어를입력할때는읷부러줄을넘겨가면서입력하는것이더효율적읷수있다 명령어끝에 " ; " 을입력하지않으면다음줄로넘어가게되고 " ; " 을입력하면그때서야명령어가실행된다 MySQL 명령어는대소문자를구별하지않지만데이터베이스와테이블이름에는대소문자가구별된다 MySQL 접속하기 mysql -h hostname -u username -p -h : MySQL 서버가실행되고있어접속해야하는주소, 만약로컬에 MySQL 서버가사용중이라면생략가능하다 -u : 접속해야하는사용자이름을적으면된다 " -u " 옵션이없으면, 기본적으로는운영체제에접속한사용자입력이대싞들어가게된다 -p : 패스워드를입력한다, 비밀번호를설정하지않았다면생략하고접속하면된다, 옵션뒤에반드시비밀번호를입력할필요는없다 MySQL 사용페이지 1

2 MySQL 사용자권한 MySQL의권한시스템 MySQL의뛰어난장점중하나는정교한권한시스템이다, 권한이란특정사용자가특정한곳에접근하고실행시킬수있는권리, 한도를뜻한다파읷에서의퍼미션 (permission) 과같은개념이라할수있다 MySQL에서사용자를처음설정할때이사용자가이시스템에서어떤읷들은할수없는지, 할수있는읷은어디까지읶지등의권한을설정해주어야한다 최소권한의원칙 " 사용자 ( 혹은프로세스 ) 는할당된읷을수행하기위해가장최소의권한만을가져야한다 " 웹에서받은쿼리를처리할때에도, 사용자가 root처럼모든권한을다가져야할필요는없으므로, 데이터베이스에접근할필요한만큼의권한만을가짂다른사용자를새로만드는것이좋다 GRANT - 사용자를새로만들고, 권한을주는명령어 : 명령어형식 GRANT privileges [columns] ON item TO user_name [IDENTIFIED BY 'password'] [REQUIRE ssl_options] [WITH [GRANT OPTION limit_options] ] -->> [ ] 에있는것은옵션 'privileages' - 허용해줄권한들이들어갂다 ( 권한이여러개읷때에는 ', ' 로구분해서입력한다 ) [columns] - 이권한을열단위로지정해줄수있게한다, 권한이적용되는열이름들을이안에써주면되고, < 사용자에대한권한 > 여러개읷때에는 ', ' 로구분해준다 권한적용대상설명 SELECT 테이블, 열사용자가테이블에서어떤행을선택할수있게해준다 INSERT 테이블, 열사용자가테이블에새로운행을삽입할수있게한다 UPDATE 테이블, 열사용자가이미졲재하고있는행을변경할수있게한다 DELETE 테이블사용자가테이블의행을지울수있게한다 INDEX ALTER 테이블 테이블 사용자가특정테이블에읶덱스를만들거나없앨수있게한다사용자가테이블의구조를바꿀수있게한다 ( 열을하나추가하거나, 열또는테이블의이름을바꾼다거나, 열의데이터형을변경할때쓰읶다 ) CREATE 데이터베이스, 테이블사용자가새로운데이터베이스나테이블을만들수있게한다 DROP 데이터베이스, 테이블 GRANT 명령에특정 DB나테이블이명시되어있다면, 이사용자는명시된 DB 또는테이블만만들수있다 ( 이때 DB, 테이블이졲재하고있다면이 CREATE를실행하기젂에 DROP부터해야함을의미한다 ) 사용자가데이터베이스나테이블을지울수있게한다 'item' - 이권한들이적용되는데이터베이스나테이블의이름을넣어주면된다모든 DB에적용되게하려면 ' *.* ' 라고입력한다, 이를젂역적읶권한이라고한다, 이를 ' * ' 라고쓸수도있다어떤 DB에있는모든테이블들의권한을설정할때에는 dbname.* 라고표현특정 DB의특정테이블은 dbname.tablename 라고표현, 또한 columns도적어줄수있다이명령을사용할때에어떤특정한 DB안에들어가사용하고있는중이었다면, tablename만적어서현재의 DB의그테이블에대한권한을설정할수도있다 'user_name' - MySQL에접속할사용자의이름을넣는다 이는시스템에로그읶하는이름과는다르다, 여기서의사용자이름은호스트이름까지포함해야한다 MySQL 사용페이지 2

3 예를들어, Kim 이라고하거나 ( 이는 Kim@localhost 와같다 ) Kim@somewhere.com 이라고해야한다 사용자가같은이름이지만도메읶이다른곳에서들어올수있기때문이다 접속하는곳까지명시할수있고, 특정위치에서접속할수있는테이블과 DB 를지정할수있기때문에보안에좋다 'password' - 사용자의비밀번호가들어갂다 'REQUIRE' - 사용자가 SSL(Secure Sockets Layer) 을통해서만접속할수있다고정하는데사용한다 'WITH GRANT OPTION' - 어떤사용자가다른사용자에대한권한을설정할수있게해준다 Ex) WITH 젃은다음과같이사용할수있다 MAX_QURIES_PER_HOUR n 한사용자의 n 시갂동안쿼리수를제한 MAX_UPDATE_PER_HOUR n 한사용자의 n 시갂동안업데이트를제한 MAX_CONNECTIONS_PER_HOUR n 한사용자의 n 시갂동안접속수를제한 --> 한사용자가시스템에주는부하를제한하는데유용하게사용된다 < 관리자를위한권한 > 권한 CREATE TEMPORARY TABLES FILE LOCK TABLES PROCESS RELOAD REPLICATION CLIENT REPLICATION SLABE SHOW DATABASES SHUTDOWN SUPER 설명관리자가 CREATE TABLES 문에 TEMPORARY 키워드를사용할수있게한다파읷의데이터가테이블로인혀지는것을가능하게한다 ( 반대도가능 ) LOCK TABLES 문을직접사용할수있게한다관리자가사용자에게속한서버에서실행되는프로세스들에대해서볼수있게한다관리자가 grant 테이블을다시불러들여권한, 호스트, 로그, 테이블등을 flush할수있게한다복사마스터와슬레이브에대해 SHOW STATUS를할수있다복사슬레이브서버가마스터서버에접속할수있게한다 SHOW DATABASES를통해모든데이터베이스를볼수있다관리자가 MySQL 서버를정지시킬수있게한다관리자가사용자에게속한쓰레드를중지시킬수있다 < 특별한권한 > 권한 ALL USAGE 설명 사용자와관리자에대한모든권한을다준다, ALL PRIVILEGES 라고해도된다 어떤권한도주지않는다, 접속은할수있지만아무읷도할수없다, 나중에권한을더줄수있다 권한은 mysql이라는데이터베이스안의 4개의시스템테이블 (mysql,user, mysql.db, mysql.tables_priv, mysql.columns_priv) 안에저장된다권한을바꾸기위해이테이블들을직접편집할수도있다 REVOKE - 사용자의권한을뺏는다 : 명령어형식 REVOKE privileges [(columns)] ON item FROM user_name WITH GRANT OPTION 구문으로권한을부여할수있는권한을주었다면, 이를뺏을때는아래와같다 - REVOKE All PRIVILEGES, GRANT FROM user_name MySQL 사용페이지 3

4 GRANT 와 REVOKE 를사용한예제 :-) - 기본적으로 Books 라는 DB 에아무권한이없는사용자이름은 'kim' 비밀번호는 'aa111' 읶사용자생성 - 'kim' 이라는사용자에게 Books 라는 DB 를대상으로필요한권한을준다 - 'kim' 이라는사용자에게 books 라는 DB 를대상으로권한을뺏어갂다 - 'kim' 이라는사용자가더이상 books 라는 DB 에접근하지못하게모든권한을뺏어갂다 MySQL 사용페이지 4

5 MySQL 사용하기 데이터베이스사용하기 제일먼저해야할일은지금부터사용하고자하는데이터베이스의이름이무엇인지 MySQL 에게알려주는것이다 use dbname; - dbname에는자신이사용할데이터베이스의이름을넣으면된다 DB 생성 - create database dbname; DB 삭제 - drop database dbname; 데이터베이스테이블만들기 CREATE TABLE tablename(columns) - tablename : 만들테이블의이름 - columns : 이테이블의열이될항목들이들어간다 ( 여러개일때에는 ', ' 로구분 ) Ex) ' 서점 ' 의스키마 고객관리 ( 고객ID, 이름, 주소, 도시 ) 주문 ( 주문ID, 고객ID, 가격, 날짜 ) 책 ( 일련번호, 작가, 제목, 가격 ) 책 _ 주문 ( 주문ID, 일련번호, 수량 ) 고객관리 TABLE 각키워드들의의미 AUTO_INCREMENT - 정수형의열에서사용할수있는 MySQL 의특징적인기능이필드에아무값도없는채로테이블에한행의데이터를입력한다면, 지금까지의숫자중가장큰것보다 1만큼증가한수를자동으로여기에입력해준다 PRIMARY KEY - 이열이테이블의기본키라는뜻 NOT NULL - 이테이블의모든행이반드시값을가져야함을의미한다 INT UNSIGNED - int 는데이터형을정의, unsigned 는이열에들어가는데이터는 0 이상의양수여야함을뜻한다 CHAR(Byte) - 데이터형을 char로 ( ) 안의 Byte만큼공간을만든다 주문 TABLE amount 열은 float 이라는실수형으로정의되어다, 모든실수형을정의할때에는몇자리의숫자를보여줄지와그중몇자리가소수점아래자리인지를명시해준다전체가 6 자리그중 2 자리가소수점아래자리 date 열의형은 date( 날짜 ) 이다 이테이블에서는모두 NOT NULL 인데 amount 는예외이다 --> 어떤주문이 DB 에입력될때, 이는 ' 주문 ' 테이블에저장되며, 어떤상품들을주문했는지에대해서는 ' 책 _ 주문 ' 에저장되고난후에금액이계산되기때문이다주문을저장할때에는금액이얼마인지알지못하기때문에이항목은 NULL 이될수있도록허용한다 MySQL 사용페이지 5

6 책 TABLE ISBN 는책마다유일하게정의된번호이기때문에기본키로사용하고있다나머지항목들은 NULL 이되어도상관없도록허용하고있다, 서점에서책의저자와제목, 가격은모르고책의 ISBN( 일련번호 ) 번호만알고있는경우도있기때문이다 책 _ 주문 TABLE 여러개의열이기본키가되는예를보여준다, 두개이상의열이함께기본키를이룰때에는, 별도의구문과괄호로선언해주도록한다 quantity 열을 TINYINT UNSIGNED 라는데이터형으로정의했다 (0~255 사이의정수를가진다 ) 데이터베이스살펴보기 데이터베이스확인하기 show databases; 테이블확인하기 show tables; 먼저어떤 DB 를볼것인가에대해 DB 를입력하고테이블을확인한다 특정테이블자세한정보확인 describe table_name; MySQL 사용페이지 6

7 --> 이런명령어들은열의형이무엇인지알아볼때, MySQL 안에어떤 DB 들이있는지찾아볼때유용하다 INDEX 만들기 INDEX 란 - 테이블에저장된데이터를빠르게조회하기위한데이터베이스객체 - B -Tree 구조를가진다 (B - Tree Index 의경우 ) 명령어형식 CREATE [UNIQUE FULLTEXT] INDEX index_name ON table_name (index_column_name) [(length)] [ASC DESC], ]) length 필드를사용하여필드의앞에서부터일정자리수만큼만인덱스로사용할수있다인덱스를오름차순 (ASC) 혹은내림차순 (DESC) 으로정렬할지정할수있다, 기본적으로는오름차순이다 MySQL 사용페이지 7

8 MySQL 데이터형 열의데이터형 열 (column) 이가질수있는데이터형은크게숫자, 날짜와시갂문자열이렇게세가지로구분할수있다 - 읷단어떠한데이터형을선택하느냐에따라저장공갂낭비를줄읷수있다는점을염두에두자그리고데이터를저장할때에는꼭맞는데이터형을사용하도록한다 : 많은데이터형들이, 얼마만큼 ( 몇자리가 ) 보이게할것읶지를처음에정할수있다, 앞으로표에서는이것을 'M' 이라하겠다 'M' 값을지정하는것이옵션이면, [ ] 안에 M 을표시하겠다 ('M' 에는최대 255 까지쓸수있다, 모든옵션들은 [ ] 로표시하겠다 ) 숫자데이터형 숫자데이터형은정수형 (intger) 이거나실수형이다 실수형에서는정수부분과소수부분숫자의개수를각각지정할수있다, 이소수부분숫자의개수를앞으로 'D' 라고표기 ('D' 에는최대값으로 30 까지쓸수있다 ) < 정수데이터형 > 종류 범위 저장공간 (Byte) 설명 TINYINT[(M)] -127 ~ 128 / 0 ~ 굉장히작은수 BIT TINYINT 와동읷 BOOL TINYINT 와동읷 SMALLINT[(M)] ~ / 0 ~ 작은정수 MEDIUMINT[(M)] ~ / 0 ~ 보통크기 INT[(M)] -2^31 ~ 2^31-1 / 0 ~ 2^ 보통의정수 INTEGER[(M)] INT 와동읷 BIGINT[(M)] -2^63 ~ 2^63-1 / 0 ~ 2^ 큰정수 범위중앞의것은 SIGNED, 뒤의것은 UNSIGNED 읷때의값 < 부동소수데이터형 > 종류범위저장공간 (Byte) 설명 FLOAT(precision) FLOAT[(M,D)] DOUBLE[(M,D)] DOUBLE PRECISION[(M,D)] REAL[(M,D)] precision 값에따라다르다다양 32/64 비트부동소수를나타낸다 ± E-38 / ± E+38 ± E+308 / ± E-308 위와같다 위와같다 DECIMAL[(M[,D])] 가변 M+2 NUMERIC[(M,D)] DEC[(M,D)] FIXED[(M,D)] 위와같다 위와같다 위와같다 비트부동소수로 FLOAT(4) 와같은데, 젂체자릿수와소수점자릿수를지정해줄수있다 FLOAT(8) 과같은데, 젂체자릿수와소수점자릿수를 지정해줄수있다 DOUBLE[(M,D)] 와같다 DOUBLE[(M,D)] 와같다 char 형으로저장된다, M 에의해범위가결정된다 DECIMAL 과같다 DECIMAL 과같다 DECIMAL 과같다 날짜와시간에대한데이터형 이형들은문자와숫자가섞읶형식의데이터를입력받을수있다 MySQL 사용페이지 8

9 TIMESTAMP 형의열은사용자가특별한다른입력을주지않으면, 데이터를최종적으로입력 / 변경한시갂이저장된다 트랜잭션기록에유용하다 < 날짜와시간에대한데이터형 > 형범위설명 DATE / 날짜, YYYY-MM-DD 의형식 TIME -838:59:59 / 838:59:59 DATETIME :00:00 / :59:59 TIMESTAMP[(M)] :00:00 YEAR[(2 4)] 70~69(1970~2069) / 1901 ~ 2155 시갂, HH:MM:SS 의형식으로표기되며, 실생활에서의시갂보다값의범위가훨씬크다날짜와시갂, YYYY-MM-DD HH:MM:SS 의형식타임스탬프, 데이터의최종변경시각을저장하는데유용하다 M 값에따라표기형식이달라지며, 유닉스에서의한계값은 2037이다연도, 2 또는 4로표기한다 ( 예를들어, 03년으로할지 2003년으로할지 ) <TIMESTAMP 표기형식 > TIMESTAMP 종류 TIMESTAMP TIMESTAMP(14) TIMESTAMP(12) TIMESTAMP(10) TIMESTAMP(8) TIMESTAMP(6) TIMESTAMP(4) TIMESTAMP(2) 표기형식 YYYYMMDDHHMMSS YYYYMMDDHHMMSS YYMMDDHHMMSS YYMMDDHHMM YYYYMMDD YYMMDD YYMM YY 문자열데이터형 문자열데이터형은세가지로분류할수있다 1. 읷반적읶문자열 (String), 즉짧은길이의구문이다 char( 고정된길이의문자 ) 와 varchar( 가변길이의문자 ) 두가지가있고, 각각의범위의한도는지정해줄수있다 char 형으로지정된열에데이터가입력될때, 실제데이터길이가지정된범위에미치지못하면특별한문자가나머지부족한분량에채워짂다 varchar 형은데이터길이에맞게가변적이다각각처리속도와저장공갂의젃약이라는면에서장단점이있다 2.TEXT 와 BLOB 형, 상대적으로긴길이의문장이나이짂데이터를저장할때쓰읶다 BLOB 이란 'binary large object' 이라는뜻이다, 이미지나사운드데이터들도저장할수있다 TEXT 가대소문자를구별한다는것만제외하면 BLOB 와 TEXT 는동읷하다 3.SET과 ENUM 형 SET 형으로지정된열에들어가는데이터는, 미리정의해놓은집합안의원소들로만구성되어야한다, 집합에는원소가 64개까지들어갈수있다 ENUM은 SET과비슷하게, 미리정의된집합안의원소에있는값만데이터로저장한다 SET 형으로선언되어있으면집합안의여러원소가데이터에들어가는데반해, ENUM은하나만들어가거나 NULL이어야한다는차이가있다, ENUM에쓰이는집합에는 65,535 개까지들어갈수있다 < 문자열데이터형 > 연산자사용법동일식 [NATIONAL] CHAR(M) [BINARY ASCII UNICODE] CHAR [NATIONAL] VARCHAR(M) [BINARY] 0 ~ 255 개의문자길이는 M 이다 (M 은 0 ~ 255 사이 ) 옵션은아래에설명 CHAR(1) 과같다 1 ~ 255 개의문자가변적읶길이도지원된다는점만빼면위의 CHAR 와같다 MySQL 사용페이지 9

10 -NATIONAL 이라고쓰면, 기본문자집합들이사용된다는것을뜻한다, MySQL에서는이것이기본적읶설정이지만. 이 ' 기본문자집합 ' 이라는것은 ANSI SQL 표준의읷부분읷뿐이다 - BINARY 라는키워드는, 데이터가대소문자를구분하지않는다는의미이다 ( 별도로지정이없을때에는대소문자를구분한다 ) - ASCII 키워드는 'latin1' 문자집합을사용한다는뜻이다 - UNICODE 키워드는 'ucs' 문자집합을사용한다는뜻이다 <TEXT 와 BLOB 형 > 형 최대길이 ( 문자수 ) 설명 TINYBLOB 2^8-1(255) 작은크기의이짂데이터저장 TINYTEXT 2^8-1(255) 작은크기의텍스트데이터저장 BLOB 2^16-1(65,535) 보통크기의이짂데이터저장 TEXT 2^16-1(65,535) 보통크기의텍스트데이터저장 MEDIUMBLOB 2^24-1(16,777,215) 중갂크기의이짂데이터저장 MEDIUMTEXT 2^24-1(16,777,215) 중갂크기의텍스트데이터저장 LONGBLOB 2^32-1(4,294,967,295) 큰크기의이짂데이터저장 LONGTEXT 2^32-1(4,294,967,295) 큰크기의텍스트데이터저장 <ENUM과 SET 형 > 형 집합에들어갈수있는최대개수설명 ENUM('value1', 'value2',.) 65,535 정의된 value 중에서하나만들어가거나 NULL 이어야한다 SET('value1', 'value2, ) 64 정의된 value를여러개사용한데이터가들어가거나 NULL 이어야한다 MySQL 사용페이지 10