5/22 25일 (view , index, 계정관리)

기획

• 데이터(베이스) 모델링
  => ERD(Entity 0 Relation Diagram)(DBMS 선택 : Oracle / MariaDB)=> 테이블 상세명세서
• ERDCloud exERD --- ppt
• 테이블 / 컬럼 자료형(사이즈) 옵션 PK / FK not null / check / default
• entity / field / 관계 - 한글 이름 중심의 설계 entity / field - 사각형 관계 - 선
• => tools - 통합처리
• => 물리적 모델링
• => 개념적/논리적 모델링

---

=> 테이블 상세명세서
=> sql 스크립트

1.데이터베이스 생성
2.테이블 생성
컬럼 생성
제약 조건 생성

• not null
• unique
• primary key
• foreign key
• check

1. 테이블 생성시
   => 테이블 단위 제약조건 / 컬럼 단위 제약조건
2. 테이블 생성후
   => alter
3. DML
   insert / update / delete
   select

---

View

create view 뷰이름
as subquery

create view emp_vu1
as select * from emp; --뷰 생성

desc information_schema.views; --뷰정보 출력

create view emp_vu_10
as
select empno, ename, mgr, job
from emp
where deptno =10;

create view emp_vu_20
as
select empno no, ename name, mgr, job
from emp
where deptno = 20;

사원번호, 사원이름 ,급여 ,연봉정보를 조회할 수 있는 emp\_sal이라는 view 생성  
연봉 : sal \* 12 + comm  
create view emp\_sal  
as  
select empno,ename,sal,ifnull(sal_12+comm, sal_12) as annualsal  
from emp;

사원번호,사원이름,담당업무,연봉정보,호봉을 조회할수 있는 emp\_salgrade 라는 view 생성  
연봉 : sal * 12 + comm  
create view emp_salgrade  
as  
select e.empno,e.ename,e.job,e.sal_12+ifnull(e.comm,0) annsal,s.grade  
from emp e join salgrade s  
on e.sal between s.losal and s.hisal;

from 절에 들어가는 Inline view (별칭 정해줘야함)
select *
from(select empno, ename, sal from emp where deptno =10)e
inner join salgrade s
on (e.sal between s.losal and s.hisal);

View 수정

• alter / replace

INDEX

• 데이터 검색

  1. table full scan
  2. 테이블 전체를 통해 원하는 데이터 select
  3. index scan
  4. `인덱스(테이블 : 공간)를 통해서 원하는 데이터 select
  • 정렬 내림차순 /올림차순`(x)
    => B-Tree
    => Tree(이진 트리)
    => Balanced
    => 중간값정렬
  1. 인덱스 구성(별도의 기능)
  2. 인덱스 재구성(=> 관리)쿼리옵티마이저 쿼리를 실행하는 흐름
  3. => 실행 계획 => execution plan => tuning
  4. - 시간 <= 시스템을 사용하지 않는 시간(새벽)

인덱스

자동생성
primary key - unique
수동생성

---

점심시간 이후

root@localhost

다른 사용자 + 권한
Windows
Linux
사용자
암호 - 문자열 복호화 비교(X)
문자열 암호화

사용자 생성 - root
=> user 테이블명 등록

권한(DCL - Data Control Lang)
grant - 권한 부여
시스템 권한 - 시스템 관리
객체 권한 - 테이블, 뷰에 대한 권한
revoke - 권한 회수

프로젝트 진행시

1. 데이터베이스
2. create database 데이터베이스명;
3. 사용자
4. 권한
5. grant all privileges on 데이터베이스명.* to 사용자명@localhost identified by '암호'; grant all privileges on 데이터베이스명.* to 사용자명@'%' identified by '암호';

create user '사용자'@'아이피나 도메인' identified by '암호';
create user tester1@localhost identified by '1234';

사용한 sql

select user();
show create user;

user mysql;
show tables;
desc user;
desc db;

select host, user, password from user;
select password('123456');

create user tester1@localhost identified by '1234';

mysql -u tester1 -p
show databases;

mysql -h 192.168.0.27 -u root -p
mysql -h localhost -u tester1 -p

create user tester1@'%' identified by '1234';            => %는 어디에서나 접근할수있음을 표시
이로써 mysql -h 192.168.0.27 -u tester1 -p 가 가능해짐 

mysql -h 상대방아이피 -u root -p
mysql -h 상대방아이피 -u tester1 -p
create user tester1@localhost identified by '1234';
create user tester1@'%' identified by '1234';
select host, user, password from user;

drop user tester1@'%'
mysql -h 192.168.0.27 tester1 -p     ==> Access denied 됨

show privileges;      => 권한 목록 확인  

show grants for root@localhost;     => 내가 가지고 있는 권한 확인
show grants for tester1@localhost;

create databases tester1db;
grant create on tester1db.* to tester1@localhost;    => create 권한 부여

grant drop on tester1db.* to tester1@localhost;       => drop 권한 부여

revoke create, drop on tester1db.* from tester1@localhost;    => create, drop 권한 철회


grant all privileges on tester1db.* to tester1@localhost;   => 모든 권한 부여




create database tester1db;
grant create on tester1db.* to tester1@localhost;
grant drop on tester1db.* to tester1@localhost;
revoke create, drop on tester1db.* from tester1@localhost;
grant all privileges on tester1db.* to tester1@localhost;

revoke all on tester1db.* from tester1@localhost;

grant select on tester1db.emp to tester1@localhost;     => select 권한부여

grant insert on tester1db.emp to tester1@localhost;     => insert 권한부여

grant delete on tester1db.emp to tester1@localhost;     => delete 권한부여

grant update on tester1db.emp to tester1@localhost;     => update 권한부여

문제

1. tester2 사용자 생성
create user tester2@localhost identified by '1234';

2. tester2db 데이터베이스 생성
create database tester2db;

3. tester2 사용자에게 tester2db 데이터베이스에 대한 사용권한 부여 및 확인
grant all privileges on tester2db.* to tester2@localhost;

4. tester2 사용자가 tester2db 데이터베이스에 테이블 생성 테스트
mysql -h localhost -u tester2 -p
use tester2db;
create table qq(col1 varchar(10));
show tables;

5. tester2 사용자에게서 tester2db 데이터베이스에 대한 사용권한 제거 및 확인
revoke all privileges on tester2db.* from tester2@localhost;

백업

데이터베이스 -> 외부 추출

Restore

외부 추출 -> 데이터베이스

1. 백업 - 데이터파일의 위치

• 바이너리
  데이터베이스 정지 상태...

mysqldump

mysqldump -u root -p sample > sample.sql

mysqldump -u root -p sample emp > emp.sql => 특정데이터 주입

mysql -u root -p sample < sample.sql