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
