기획
- 데이터(베이스) 모델링
=> ERD(Entity 0 Relation Diagram)(DBMS 선택 : Oracle / MariaDB)=> 테이블 상세명세서 ERDCloud exERD --- ppt테이블 / 컬럼 자료형(사이즈) 옵션 PK / FK not null / check / defaultentity / field / 관계 - 한글 이름 중심의 설계 entity / field - 사각형 관계 - 선- => tools - 통합처리
- => 물리적 모델링
- => 개념적/논리적 모델링
=> 테이블 상세명세서
=> sql 스크립트
1.데이터베이스 생성
2.테이블 생성
컬럼 생성
제약 조건 생성
- not null
- unique
- primary key
- foreign key
- check
- 테이블 생성시
=> 테이블 단위 제약조건 / 컬럼 단위 제약조건 - 테이블 생성후
=> alter - 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
데이터 검색
- table full scan
테이블 전체를 통해 원하는 데이터 select- index scan
- `인덱스(테이블 : 공간)를 통해서 원하는 데이터 select
- 정렬 내림차순 /올림차순`(x)
=> B-Tree
=> Tree(이진 트리)
=> Balanced
=> 중간값정렬
- 인덱스 구성(별도의 기능)
- 인덱스 재구성(=> 관리)쿼리옵티마이저 쿼리를 실행하는 흐름
=> 실행 계획 => execution plan => tuning- 시간 <= 시스템을 사용하지 않는 시간(새벽)
인덱스
자동생성
primary key - unique
수동생성
점심시간 이후
root@localhost
다른 사용자 + 권한
Windows
Linux
사용자
암호 - 문자열 복호화 비교(X)
문자열 암호화
사용자 생성 - root
=> user 테이블명 등록
권한(DCL - Data Control Lang)
grant - 권한 부여
시스템 권한 - 시스템 관리
객체 권한 - 테이블, 뷰에 대한 권한
revoke - 권한 회수
프로젝트 진행시
- 데이터베이스
create database 데이터베이스명;- 사용자
- 권한
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
외부 추출 -> 데이터베이스
- 백업 - 데이터파일의 위치
- 바이너리
데이터베이스 정지 상태...
mysqldump
mysqldump -u root -p sample > sample.sql
mysqldump -u root -p sample emp > emp.sql => 특정데이터 주입
mysql -u root -p sample < sample.sql