MySQL 사용자 및 테이블 만들기

create database emprelation default character set utf8 collate utf8_general_ci;

//  사용자 접속가능 현황 보기
select user, host from user where user not like '';

// 모든 권한으로 생성
grant all privileges on *.* to 'emprelation'@'localhost' identified by 'password';

// 원격접속 가능

grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER on emprelation.* to emprelation@'%' identified by 'password';

// 로컬에서 접속 가능 

grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER on emprelation.* to emprelation@'localhost' identified by 'password';

// grant 작업 후 바로 적용
flush privileges;

create table EM_TRELATION (

  sourceEpId       varchar(100) NOT NULL,

  targetEpId       varchar(100) NOT NULL,

  point            BIGINT UNSIGNED default 0,

  CONSTRAINT EM_TRELATION_PK PRIMARY KEY (targetEpId, sourceEpId)

);

create table EM_TRELATION_DATA {

  sourceEpId       varchar(100) NOT NULL,

  targetEpId       varchar(100) NOT NULL,

  type             char(2) NOT NULL DEFAULT '00',

  count            BIGINT UNSIGNED default 0,

  latestDate       varchar(14),

  CONSTRAINT EM_TRELATION_DATA_PK PRIMARY KEY (sourceEpId, targetEpId, type)

);

CREATE INDEX EM_TRELATION_DATA_IDX01 ON EM_TRELATION_DATA(targetEpId, sourceEpId, type);

CREATE INDEX EM_TRELATION_DATA_IDX02 ON EM_TRELATION_DATA(latestDate);

create table EM_TEMPLOYEE_DATA {

  epId             varchar(100) NOT NULL,

  name             varchar(100),

  orgCode          varchar(100),

  orgName          varchar(100),

  subOrgCode       varchar(100),

  subOrgName       varchar(100),

  deptCode         varchar(100),

  deptName         varchar(100),

  titleCode        varchar(100),

  titleName        varchar(100),

  mail             varchar(100),

  nickName         varchar(100),

  mobile           varchar(100),

  picture          varchar(500),

  CONSTRAINT EM_TEMPLOYEE_DATA_PK PRIMARY KEY (epId)

);

// 데이터 베이스 및 사용자 삭제하기
1. 데이터 베이스 삭제
mysql>drop database emprelation;

2. 사용자 권한 삭제
mysql>revoke all privileges, grant option from emprelation;

3. 사용자 삭제
mysql>drop user 'emprelation'@'localhost';
mysql>drop user 'emprelation'@'%';

// maven 로컬 리파지토리에 저장하기
 mvn install:install-file -DgroupId=mysql \

                         -DartifactId=mysql-connector-java \

                         -Dversion=5.1.15 \

                         -Dpackaging=jar \

                         -Dfile=mysql-connector-java-5.1.15-bin.jar