아니근데 진짜 SCHEMAS가 뭐임? 스키마???? (MySQL에선 DB와 SCHEMAS가 동일하게 쓰이나 다른 DBMS에선 안그럼) 스키마 : 쉽게 얘기해서 데이터베이스를 생성, 정의하는 규칙서 같은 느낌??
(트친님의 도움설명1) 현업에서는 앗! 문제가 발생했다! 뭐가문제지? 스키마가 잘못됬네 -> DB 뿌수고 create부터 다시 해야하네 or 복구를 위해선 쉽지 않은 작업을 해야겠네 정도.. 한번 만들어진 스키마를 변경하는건 매우 어려운 일 그래서 개발시점엔 이것땜시 개발이 느려져서, 스키마가 없는 NoSql을 찾아보게되고.. 저는 설계서 정도로 이해하고 있습니다..마는 정처기에서 말하는 스키마는 현업의 그것을 넘기 때문에.. (정처기 대비라면 외워야함)
(트친님의 도움설명2)
외부(서브)스키마 =사용자 뷰. 사용자가 DB에 접근한다고 하면 이쪽 이야기. 전체 DB에서 논리적인 부분(대충 DB의 일부분) 같은 데이터베이스에 대해서도 서로 다른 관점을 정의할수 있도록 허용하는 쪽 외부 스키마는 이렇지 싶은데(곰곰) 대충 사용자가 접근할 수 있는, 바깥에 보여지는 부분?
개념 스키마 =전체적인 뷰... 그러니까 DB당 1개인. 그 DB 전체를 기술한 거니까 DB에 대한 온갖 정보가 있다고 보면 되지 싶은데요 접근 권한, 보안, DB내 데이터들끼리 어떻게 관계되는가 등등등
내부 스키마 =DB에 데이터가 저장되는 방법을 기술해둔 부분... 개념 스키마를 디스크 기억장치에 물리적으로 구현하기 위한 방법을 기술한 것인데, 이게 뭔 소린가 하면 기억 장치(물리적)에 데이터(비물리적(이런 표현 괜찮은가))를 어떻게 저장-구현할 건가! 그 방법을 기술해둔? +그렇다고 내부 스키마가 바로 DB를 만드는 건 아니고, 내부 스키마는 그냥 '방법'만 갖고 있는 느낌... 이라고 보면 될 것 같은데요
1. 외부 스키마(사용자 뷰)에서 사용자가 나 데이터 이렇게 저장할래요 하면 2. 개념 스키마(전체적인 DB 뷰)에 어떻게 저장해야 적합한지 변경하고 3. 그걸 내부 스키마에서 아 이건 이런 방법으로 해야 물리적 기억 장치에 저장할 수 있다고 변환해주는?
회원테이블(memberTBL)
[SCHEMAS]에서 아까 새로만든 shopdb > Tables > 오른쪽클릭>Create Table
이렇게 표를 만들고 Apply. 역시 SQL문도 함께 나온다. 이걸 쿼리창에 입력해도 동일한 결과가 나온다.
제품테이블(productTBL) 이하동문 제작. PK : Primary Key라서 기준이 되는 키 값. 오직 하나. NN : Not Null. 비워지는건 허용하지 않음.
데이터 활용 : 이렇게 입력한 친구들을 활용해보자. SQL을 입력하기! 데이터를 활용한다는 것은 'SELECT'문을 사용한다는 의미이다.
1.new Query Tab을 눌러보자.
이때 schemas 창에서 shopdb를 더블클릭하면 shopdb가 진하게 색상이 변하면서 선택되는데, 이건 앞으로 쿼리창에 입력할 SQL문이 shopDB에 적용된다는 의미임.
이제 쿼리창에 다음처럼 입력해서 제품 테이블의 모든 데이터를 조회한다.
select * FROM producttbl;
실행은 ctrl + shift + enter 결과는 이렇게 뜬다. 두둥.
이 SQL문을 분석하자면 다음과 같다. Select의 기본 형식 : SELECT 열이름FROM 테이블이름 [WHERE조건]
이번에는 이렇게 입력하자.
select memberName,memberAddress from membertbl;
그러면 membertbl에서 memberName과 memberAddress만 출력된다.
인덱스 : 책의 제일 뒤에 붙어있는 '찾아보기'
Create TABLE indexTBL(first_name varchar(14), last_name varchar(16), hire_date date);
insert into indexTBL
select first_name,last_name,hire_date from employees limit 500;
select * from indextTBL;
이렇게 SQL문을 작성해서 쿼리를 실행해보면,
생성된게 보인다.
먼저 인덱스가 없는 상태에서 작동하는 쿼리를 살펴봅시다...
select *from indextbl where first_name ='Mary';
indextbl에서 Mary라는 이름을 가진 사람을 조회해본다. 결과창을 띄우고 앙래창을 누르면 [execution Plan]이 있는데, 이걸 눌러보자.
Full Table Scan : 500건을 모두 읽어서 query_block #1 1개의 결과를 찾아냈다는 느낌.
이제는 인덱스를 만들어서 비교해보자. 인덱스를 만드는 건 SQL문 이걸로.
create index idx_indextbl_firstname on indextbl(first_name);
SQL문을 해석하자면 이러하다. 실행해보면 결과는 똑같은데, Execution Plan은 좀 다르다.
Query Cost가 확실히 줄어든 것을 발견할 수 있다.
뷰 : 가상 테이블 (실체가 없음) (뷰를 SELECT하면 결국 진짜 테이블 데이터를 조회하는 것과 동일함) ex.회원주소를 대량변경하는 작업이 필요 > '주소'만 변경해야함 > 수정해야하는 가상의 데이터 테이블을 만들어줌 <뷰 만들기>
create view uv_membertbl as select memberName,memberAddress from membertbl;
select * from uv_membertbl;
이렇게 만들면, 뷰 uv_membertbl이 보인다.
스토어드 프로시저(stored proceduer) : mysql에서 제공해주는 프로그래밍 기능. sql문을 하나로 묶어서 편리하게 사용하는 기능. ex)매번 회원테이블 '당탕이'의 정보와 제품테이블의 '냉장고'정보를 동시에 조회한다고 가정하자.
DELIMITER //
create procedure myProc()
begin
select * from membertbl where memberName='당탕이';
select * from producttbl where productName='냉장고';
end //
DELIMITER ;
트리거 : 테이블에 부착되어 테이블에 insert, update, delete 작업이 발생되면 실행되는 코드. ex.당탕이가 탈퇴했다고 생각해보자. 나중에 탈퇴한 사람이 누구인지 정보를 알 수가 없다. > 당탕이를 삭제하기 전에 다른 곳에 복사해두면 됨. > 삭제 전 미리 다른 곳에 자동저장하는 기능!
먼저 새로운 회원 Figure/연아/경기도 군포시 당정동을 입력 후 잘 입력됐는지 확인해보자.
insert into membertbl values('Figure','연아','군포시 당정동');
select * from membertbl;
결과값은
이번에는 이름이 '연아'인 회원의 주소를 '서울 강남구 역삼동'으로 바꾸어보자.
update membertbl set memberAddress = '서울 강남구 역삼동' where memberName='연아';
select * from membertbl;
했는데 오류가 떴다... (ㅠㅠ) Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 라는데.. 이 경우는 Safe mode를 해제해줘야한다. (참고 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=kkson50&logNo=221251167091) ①[Edit] - [Preferences...]
②[SQL Editor]에서 Safe Updates 체크를 해제하고 [OK] ③workbench를 재시작하고 다시 SQL문을 실행해보면..
결과가 바뀐걸 볼 수 있다.
create table deletedMemberTBL(memberID char(8), memberName char(5),memberAddress char(20), deletedDate date);
delimiter //
create trigger trg_deletedMembertbl
after delete on membertbl for each row
begin insert into deletedMemberTBL values(old.memberID,old.memberName,old.memberAddress,curdate());
end //
delimiter ;
①지워진 데이터를 보관할 백업테이블(deletedMemberTBL)을 만들기 ②membertbl에서 delete가 일어나면 > 백업테이블(deletedMemberTBL)에 지워진데이터가 기록되는 트리거 만들기 memeberName이 당탕이를 delete하고 확인해보자.
delete from membertbl where memberName='당탕이';
select * from membertbl;
그리고 이제 백업 데이터를 확인해보면,
select * from deletedmembertbl;
이렇게 백업테이블에 들어간 것을 확인 할 수 있다.
데이터베이스 백업 및 관리
백업 : 현재 db를 다른 매체에 보관하는 작업
복원 : db에 문제가 발생했을때 백업된 데이터를 통해 원상태로 돌려놓는 작업
실습 1.백업용폴더를 C에 만들어보자. 나는 C에 DB백업이라는 폴더를 만들었다.
2.현재 데이터를 확인해본다.
use shopdb;
select * from producttbl;
3개의 데이터가 보이는 것을 확인할 수 있다.
3.[Navigator]>[Administration]>[Data Export]를 눌러본다.
4.①shopdb의 데이터베이스를 모두 선택한다. ②백업경로는 [Export to Self-Contained File]>파일경로 수정 ③Create Dump in a Single~~ 도 체크 ④Include Create Shema도 체크 ⑤Start Export 기다려보면
Compeleted!
5.이제 복원을 해보자. 일단 producttbl 테이블을 삭제하고 조회해보면..
delete from producttbl;
select * from proudctbl;
Table 'shopdb.producttbl' doesn't exist. 가 나온다.
6.사용중인 db를 복원하면 문제가 생길수도 있으므로, 현재 db인 shopdb에서 다른 db로 바꿔본다.
use sys;
[Navigator]-[Administrator]-[Data Import/Restore]로 들어간다.
7.①[Import from Self-Contained File]에서 shopdb.sql을 선택한다. ②[Default Traget schema]는 shopdb를 선택 ③Start Import Import 완료! 8.다시 producttbl 테이블을 확인해보면 원래대로 돌아온것을 확인할 수 있다.
