mysql 외래키 옵션

 

//==================== 리눅스에서 sql파일 실행 =======================

] # mysql -h  localhost  -u  root  -D  mChart  -p < create_table.sql

 

//========================== DB 한글 깨짐 방지 =========================================

   db접속후 케릭터 셋을 지정해준다.

   <php 예>

   $db = new mysqli('localhost','root','1234','myDB');
   $db->query('set names euckr');

 //=========================== 계정 비밀번호 변경 ==========================================

   update mysql.user set password = password('암호') where user = '계정@localhost';
   flush privileges;

 

 

//==================== 기본 명령 ====================================

mysql> create database dbName;   // db 생성

              예) create databases dbName default character set utf8 ;

mysql> drop database dbName;       //db 삭제

 

mysql> create table tableNae(columns); //table 생성

          예) create table books ( isbn char(13) not null primary key,   //null값을 가질수 없는 기본키

                                           author char(50),

                                           title char(100),

                                           price float(4,2) );                          

          예) create table books ( isbn char(13) not null ,  //기본키가 여러개일때 

                                               author char(50) not null ,

                                               title char(100),

                                              price float(4,2),

                                             primary key(isbn, author ) ); 

                                          price float(4,2) );                          

          예) create table books ( isbn char(13) not null ,  //테이블 collation 변경 

                                              author char(50) not null ,

                                               title char(100) character set utf8 collate utf8_general_ci not null,

                                              price float(4,2),

                                              primary key(isbn, author ) ); 

        예) create  view  cust_user_view  as

                                    select  user.user_id, cust.cust_id,  cust.cust_name,user.user_name

                                   from  cust,user

                                   where cust.cust_id = user.cust_id ;

        

mysql> drop table tableName;       //table 삭제

 

mysql> show databases [ like database ];                 // 모든 사용할 수 있는 db 보여줌

 

mysql> show [ open ] tables [ from database ][ like table ];    // 선택된 DB의 모든 table 보여줌

 

mysql>  show grants for user ;                  // user가 가진 권한을 설정하기위한 grant 문을 보여준다.

 

mysql>  show create table db.table ;               //테이블 생성 구조 보기

 

mysql> describe tableName [column];             //table의 구조를 보여줌

 

mysql> use dbName;                         // DB 사용 선택

 

//====== 사용자 및 권한 부여==================================================================

 

 ※ 모든 호스트  'id'@'%'

 

mysql> grant all                                    //모든권한

      -> on *                                        // 모든 DB

      -> to fred  identified by 'mnb123'     // 비밀번호 mnb123으로 접속하는 fred라는 사용자에게

      -> with grant option;                       // 다른사용자에게 권한을 부여할 수도 있게

 

mysql> grant usage                                 //권한없는

      -> on books.*                                // books DB및 table에 대해서

      -> to sally  identified by 'mnb123' ;    // 비밀번호 'magic123'으로 접속하는 sally라는 사용자에게

 

mysql> grant select, insert, update, delete, index, alter, create, drop    // 해당 명령어 권한 부여

      -> on books.*                                       // books DB및 table에 대해서

      -> to sally ;                                           // sally라는 사용자에게

 

mysql> grant select, insert, update, delete    // 해당 명령어 권한 부여

      -> on books.*                                       // books DB및 table에 대해서

      ->to sally@localhost   identified by 'sally123' ;    // sally@localhost 와 비밀번호로 접속하는 사용자에게

    

mysql> revoke alter, create, drop               //해단 권한을 취소

      -> on books.*                                 // books DB및 table에 대해서

      -> from sally;                                  // sally라는 사용자의

 

mysql> revoke all                                   // 모든 권한을 뺏어 해당 DB에 접근 하지 못하도록 한다.

      -> on books.*

      -> from sally;

 

mysql> use mysql;    //계정 삭제

mysql> drop user  sally@localhost ;

=======================================================================================================

 

 ================== Select 문 ==================

 

select [ options ] items [ into file_details ]

          from tables

          [ where conditions ]

          [ group by group_type ]

          [ having where_definition ]

          [ order by order_type ]

          [ limit limit_criteria ]

          [ procedure proc_name(arguments) ]

          [ lock_options ]

         ;

 예) custtomers 테이블에서 city 값이 seoul인 레코드의 name,city 값을 불러온다 5개만

        select name, city

                 from customers

                 where city='seoul'

                       limit 5 ;

 

 예) 현재 날자와의 차이 구하기 (D-Day)

     select end_date,dateDIFF(end_date, now() ) as dday

              from  date_count

 

================== Insert 문 ==================

 

insert [ into ] tableName [(column1,column2,column3,...)]           //table의 필드에 해당 값을 저장

         values (value1, value2, value3,...) ;

 

 예) insert customer_user

              set customer = 'gmate',

              user_name = 'gmate',

              user_password = sha1('gmate');

 예)  insert customer_user values

              ( 'gmate', 'gmate', sha1('gmate')),

              ('ccr','user', sha1('gmate'))

       ;

================== delete 문 ==================

 

delete [ low_priority ] [ quick ] [ ignore ] from tableName            //table의 레코드 삭제

          [ where conditions ]

          [ order by order_cols ]

          [ limit number ]

 

예) delete from customer_user

             where customer =  'gmate' ;

 ================== update 문 ==================

 

update [ low_priority ] [ ignore ] tableName                    //table의 레코드 수정

           set column1=expression1, column2=expression2,...

           [ where conditions ]

           [ order by order_criteria ]

           [ limit number ]

  예) update customer

              set customer_name='성재욱'

              where customer_id ='aaa';

 

================== alter table문 ==================

alter table  tableName    add fieldName varchar(10) not null ;

 

예) alter table  myTable add name varchar(30) not null;

 

//-----필드 삭제

alter table  tableName  drop fieldName ;l

//-----엔진타입 변경

 alter table tableName ENGINE = innoDB ;

 alter table tableName ENGINE = MYISAM ;

 

 

============= 외래키(foreign key) 생성 ====================================================

＃ 테이블 타입이 innoDB 이어야 적용된다(부모자식 테이블 모두).

 

create table board_10001 (

     postid int(10) unsigned not null auto_increment primary key,

    sub_code char(4) not null  ,

    title varchar(40) not null,

   contents text not null,

   date timestamp ,

   writer char(10) not null ,

  foreign key(sub_code) references  board_sub_list(sub_code),

  foreign key(writer) references  user(user_id)

)type=innodb , character set = utf8;

 

 

============= 테이블에 외래키 추가수정 ===========================================

alter  table  board_10002

 add  constraint  foreign  key(sub_code)  references  board_sub_list(sub_code);

 

=================== 외래키 옵션 ===========================================================

CASCADE
참 조되는 측 관계 변수의 행이 삭제 되었을 경우에는 참조하는 측 관계 병수와 대응되는 모든 행들이 삭제 됩니다 . 참조되는 측 관계 변수의 행이 갱신 되었을 경우에는 참조하는 측 관계 변수의 외래 키 값은 같은 값으로 갱신됩니다.
 예) 

   foreign key(id) references  customer_info(id) ON  DELETE  CASCADE ON  UPDATE  CASCADE

RESTRICT
참조하는 측 관계 변수의 행이 남아 있는 경우에는 참조되는 측의행을 갱신하거나 삭제 할 수 없습니다. 이 경우에는 데이터 변경이 이루어 지지 않습니다.

NO ACTION
참조되는 측 관계변수에 대해 UPDATE, DELETE 가 실행됩니다. DBMS에서 SQL문장의 실행 종료시에 참조 정합성을 만족하는지 검사합니다. RESTRICT와 차이점은 트리거 또는 SQL문장의 시멘틱스 자체가 외래키의 제약을 채울것이라는 데에 있습니다. 이때는 SQL 문장 실행이 성공합니다. 외래 키의 제약이 만족되지 않은 경우에는 SQL문장이 실패한다.

SET NULL
참 조되는 측 관계 변수에 대해 행이 갱신 또는 삭제 되었을 경우 , 참조하는 측 관계 변수의 행에 대한 외래키 값은 NULL로 설정이 됩니다. 이 옵션은 참조하는 측 관계 변수의 외래 키에 NULL 을 설정할 수 있는 경우에만 가능합니다. NULL 의 시멘틱스에 의해 참조하는 측 관계 변수에 대해 NULL이 있는 행은 , 참조 되는 측 관계 변수의 행을 필요로 하지 않습니다.

SET DEFAULT
SET NULL 과 비슷하지만 참조되는 측 관계 변수의 행이 갱신 또는 삭제 되었을 경우 참조하는 측 관계 변수의 외래키 값은 속성의 기본값으로 설정됩니다.

위와 같은 5개의 참조 조작이 SQL:2003에 규정되어 있습니다.

MySQL 에서는 InnoDB 에서 지원을 합니다. 지원하는 참조 조작은 RESTRICT, CASCADE, SET NULL, NO ACTION 을 지원합니다

 

====================== 뷰(view) 테이블 생성 ============================================

 

 

create  view  cust_user_view  as

   select  user.user_id, customer.customer_id,  customer.customer_name,user.user_name

   from  customer,user

   where customer.customer_id = user.customer_id ;

   

출처 - http://okwizard.blog.me/70100376686