10장~12장
DB스키마의 중복정보 제거 -> 모델링
정규화 -> 중복제거
제1정규형 - 나눌수 있을 만큼 쪼개라
제2정규형 - 테이블의 컬럼들이 기본키와 직접 연관되는 컬럼만으로 구성해라
제3정규형 - 컬럼들 간의 종속관계가 있으면 안됨
Foreign Key있는 경우 데이터추가, 삭제에 신중할 것
city와 country가 있을때..
insert할때는 country 먼저 하고 그다음 city를 insert 함
delete할때는 반대로 city를 delete하고 그다음 country를 delete한다.
11장 스키마 수정, 삭제
Alter table 테이블명 다음에
ADD 컬럼추가
DROP 컬럼삭제
CHANGE 컬럼명 수정
MODIFY 컬럼데이터타입 변경
ALTER TABLE 테이블명 ADD PRIMARIY KEY (컬럼명) --> 기본키 제약조건 추가
ALTER TABLE 테이블명 DROP PRIMARIY KEY --> 기본키 제약조건 삭제
ALTER TABLE 테이블명 ADD UNIQUE (컬럼명)
ALTER TABLE 테이블명 ADD CONSTRAINT 제약명 UNIQUE (컬럼명1, 컬럼명2 ...)
ALTER TABLE 테이블명 DROP UNIQUE 제약명
ALTER TABLE 테이블명 ADD FOREIGN KEY (컬럼명) REFERENCES 원테이블명(원컬럼명)
ALTER TABLE 테이블명 DROP FOREIGN KEY 컬럼명
ALTER TABLE 테이블명 RENAME 새테이블명; 테이블명 변경
ALTER TABLE 테이블명 ALTER 컬럼명 SET DEFAULT '기본값';
ALTER TABLE 테이블명 ALTER 컬럼명 DROP DEFAULT;
DROP DATABASE 데이터베이스명;
DROP TABLE 테이블명;
DELETE FROM 테이블명; --> 데이터를 일부 지울때 사용하는 것이고, 몽땅 지울거면 아래 truncate를 사용해야 함.
TRUNCATE TABLE 테이블명; --> 데이터는 모두 삭제하고 테이블의 스키마는 남아있음
12장 권한설정
DCL(Data Control Language)
권한 및 역할 설정하는 언어, 특정테이블에 대한 CRUD 권한 설정
권한부여(GRANT), 권한회수(REVOKE) 두개의 명령어만 있음
use mysql;
select user, host from user; 유저테이블을 select
사용자추가시 예전방식은 insert into user~ 하는 방식이었는데, MySQL 5.7이후는 password() 함수가 작동하지 않아 사용불가함.
MySQL 5.7이후는 CREATE USER 'sampleUser@'localhost' IDENTIFIED BY '비밀번호';
drop user 사용자명@호스트;
flush previleges; --> 이렇게 해야 실제로 삭제된다.
sampleUser (localhost / 로컬랜 / 인터넷전체) 접속허용하시오
localhost의 sampleUser에게 world DB의 검색(CRUD에서 select만)/추가(CRUD에서 insert)권한을 부여하시오
localhost의 sampleUser에게 world DB의 city테이블의 도시명(Name)의 업데이트 권한 부여
localhost의 sampleUser의 모든 권한 삭제
show grants for sampleUser@localhost; 현재 권한확인
grant all on world.* to 'sampleUser'@'localhost' identified by '비밀번호'; 모든권한 부여
grant all on world.* to 'sampleUser'@'192.168.0.%' identified by '비밀번호'; 로컬랜 전체모든권한 부여(%는 전체를 의미)
grant all on world.* to 'sampleUser'@'%' identified by '비밀번호'; 인터넷 전체 허용(모든 IP이므로)
grant select, insert on world.* to sampleUser@localhost identified by '비밀번호'; select, insert 권한부여
grant update(Name) on world.city to sampleUser@localhost identified by '비밀번호'; (city테이블 Name컬럼 update만 부여)
revoke all on *.* from sampleUser@localhost;
역할 설정(MySQL에서는 없음, oracle에서만 가능.. 그래도 나중에 MySQL에 적용될수 있으니 배워놓자)
grant명령어로 DB, table, 사용자별, CRUD 각각 지정이 가능했으므로.. 엄청나게 경우의 수가 많아지게 된다. 그래서 role을 사용
create role 역할명;
grant CRUD on 테이블명 to 역할명;
grant 역할 to 사용자명;
create role world_admin;
grant insert, delete on city to world_admin;
grant world_admin to sampleUser;
원격접속설정
grant all privileges on DBaud.테이블명 to 사용자명@'%' identified by '비밀번호';
my.ini 파일 수정 (리눅스는 my.cnf)
이 파일안에
bind-address = 127.0.0.1 이런 부분이 있는데 맨앞에 #을 붙여 주석처리한다.
#bind-address = 127.0.0.1
파일수정하고 재기동해야함.
리눅스) sudo mysql restart; 그리고 방화벽 3306 개방
실습)
use mysql; root로 접속
desc user;
select user, host from user; 확인해보고
create user 'sampleUser'@'localhost' identified by 'test'; -- localhost에서만 접속가능한 user 생성됨
create user 'sampleUser'@'%' identified by 'test'; user테이블 select 해보면 1건 더 생겼음을 확인 .. 어디서든 접속가능한 유저
show grants for pieruser; -- 내 노트북꺼
grant all on world.* to 'sampleUser'@'localhost' identified by 'test'; 로컬접근시 world DB의 모든 테이블에 모든 권한 부여
grant select on world.city to 'sampleUser'@'%' identified by 'test'; 외부에서 접근시 city테이블의 select 만 부여
grant update(CountryCode) on world.city to 'sampleUser'@'localhost' identified by 'test';
-- 외부접근시 city테이블 CountryCode컬럼에 update 권한부여