12/02 - SQL(4) : unique, primary key, foreign key, alter, view, inline view, B-tree, index, grant, revoke

복습 & 개념 정리

두개 이상 쿼리의 결합 -  실제 데이터가 변경되진 않음

1. subquery

      쿼리 안에 쿼리

      * 1열

      1. 단일행 서브쿼리 

                  비교 연산자

      2. 복수행 서브쿼리

                  in

                  ~any, ~all

2. join

       equi join               =

       non-equi join    부등호

       outer join            한쪽을 중심으로 표현

                      상품(회원)(전부) / 주문

       *self join              한개의 테이블

     

       emp(deptno) / dept(deptno)

                  참조             코드

        * 옷

                  사이즈

                  색상

                  옵션이 많으면  - join

 

* 테이블의 구조 - ERD

 

DDL

         create / drop

         1. 데이터베이스 생성

         2. 데이터베이스 삭제

 

         create / alter / drop

         1. 테이블 생성

         2. 테이블 수정

                  컬럼의 추가, 수정, 이름변경, 삭제

         3. 테이블 삭제

 

create table 테이블명  (

컬럼명    자료형(크기)   not null default,

컬럼명    자료형(크기), 

컬럼명    자료형(크기)

); 

 

DML -      insert / update / delete  -> DDL로 테이블의 구조를 생각하면서 써야함

 

---

 

 

 

 

 

 

 

 

 

제약조건

---

 

테이블을 생성할 때    -  데이터에 (입력/수정/삭제)조건 - 제약조건(constraint)

    =>   제약조건은 프로그램이 검사할 수 있음. 데이터베이스에서 한번더 검사를 한다.

 

* information_schema.table_constraints

               관리자가 사용하는 테이블

               -  제약조건 정보

 

1. not null  /  null     -  필수 입력항목

2. unique                    -  중복을 배제한 고유값

                                           (unique는 컬럼단위 제약조건이고 distinct는 select할때의 조건이라는 점에서 차이가 있음)

3. primary                  -  unique  +  not null

                                       -  테이블당 한개

                                       => 행 단위

4. foreign key           -  참조키, 외래키

                                       -  primary key, unique key 참조해서 생성

 

 

 

 

*  테이블마다 존재하는 제약 조건  모아보기

select constraint_name, table_schema, table_name, constraint_type 
from information_schema.table_constraints 
where constraint_schema='데이터베이스명';

 

 

* 스크립트로 메모장에 저장해서 불러오기

 

 

1. not null

 

2. unique

2-1. 제약 조건 주는 방법

1) 컬럼 정의 옆에

2) constraint 행으로 따로

 

 

2-2. 확인하기

unique 로 설정되어 있고 중복된 값을 넣으면 에러가 발생하는 것을 확인 가능하다.

 

 

하이디에서도 확인할 수 있다.

 

 

2-3. null 중복은?

값에 대한 중복은 허용하지 않지만 null 은 허용

 

 

3. primary key

2-1. 제약 조건 주는 방법

1) 컬럼 정의 옆에

2) constraint 행으로 따로

 

2-2. 확인하기

deptno컬럼 값을 중복된 값을  넣으면 에러가 발생한다.

 

 

 

 

4. foreign key

4-1. 선언과 에러

create table dept_p(

deptno int(2),

dname varchar(14),

loc varchar(13)

);

 

create table emp_f(

empno int(4),

ename varchar(10),

job varchar(9),

deptno int(2),

constratint emp_f_deptno_fk foreign key (deptno) references dept_p (deptno)

);

 

 

 참조 하는 컬럼이 가질수 있는 값은 null, 참조하는 컬럼의 값 두가지 이다. 

 

 

 

5. alter 구문

5-1. primary key

한테이블에 하나만 있기 때문에

생성시 PRIMARY로 자동으로 제약조건명이 정해지고,

삭제 시 따로 제약조건명을 써주지 않아도 된다.

 

alter table dept_p add constraint primary key (deptno);
alter table dept_p drop primary key;

 

5-2. unique

 

alter table dept_p add constraint unique(deptno);

 

5-3. foreign key

한 테이블에 여러개가 될수 있기 때문에 drop 할때 제약조건명을 조회해서 적어줘야 한다.

1) 추가

dept_p(primary)

alter table emp_f add constraint foreign key (deptno) references dept_p (deptno);

 

2) 삭제

alter table emp_f drop foreign key 제약조건명;

 

---

 

 

 

 

 

 

 

 

view

---

복잡한 sql문을 단순화 시키거나 보안을 강화 하고 싶을 때 쓴다. 

1. 생성

view - select 문에 별명

create view 뷰이름 as 서브쿼리

 

테이블 처럼 사용

create view emp_salgrade2 
as 
select empno, ename, sal, grade 
from emp_sal e inner join salgrade s 
on e.sal between s.losal and s.hisal;

 

 

2. 조회

show full tables; 

Table_type에 view는 view라고 나옴

 

show full tables where table_type='view'; 

view만 나옴

 

show create view emp_vu2;

내가 view 만들때 쳤던 문장들 나옴

 

 

* 인라인 뷰

create view emp_salgrade3 
as
select empno, ename, sal, grade 
from (select empno, ename, sal from emp) e inner join salgrade s 
on e.sal between s.losal and s.hisal;

 

3. 수정

(1) 생성

 

create view emp_vu_10 as select * from emp where deptno = 10;

 

 

(2) 수정

 

   alter view emp_vu_10 as select * from emp where deptno = 20;

 

(3) 삭제

drop view 뷰이름;

 

view 전부 삭제 후 확인

show full tables where table_type='view';   

 

 

 

4. 인라인 뷰

from 절 안에 select가 오는 것을 말한다.

 

select * from (select empno,  ename, sal, comm from emp);

 

에러난다.

 

select * from (select empno,  ename, sal, comm from emp);

↓

select * from (select empno,  ename, sal, comm from emp) e;

 

뷰는 이렇게 alias를 붙여줘야 함.

 

 

---

 

 

 

 

 

 

 

B-tree

---

검색 - 효율적인 데이터( 순서 )저장

           -> index (색인-목차)

           -> 오름차순, 내림차순

           -> 데이터 접근하는 속도..

 

 

B-tree   -  Balanced Tree

             중간 값 정렬방식

             기존 다른 저장구조(index table)

1. 자동 index

             primary key

             unique

2. 수동 index 

 

 

1. 자동 index

show index from dept;

create table dept_i ( 
deptno int(2), 
dname varchar(14), 
loc varchar(13) 
);

테이블을 만들고 바로 인덱스가 생기는건 아니다.

 

 

 

primary key 를 만들면 인덱스가 자동으로 생긴다.

alter table dept_i add constraint primary key (deptno);

 

 

primary key 삭제하면 인덱스도 사라진다.

alter table dept_i drop primary key;

 

 

 

2. 수동 index

(1) 인덱스 생성

create index dept_i_idx on dept_i (dname);

 

(2) 인덱스 삭제

drop index dept_i_idx on dept_i;

 

 

문제)  unique deptno 만들고 인덱스 확인

 alter table dept_i add constraint unique(deptno);

 

 

 

 

 

 

인덱스 유지 보수

*  인덱스 리빌딩

            => 새벽

            => 은행(1 - 2) / 백업 / 인덱스 재구성

 

swconsulting.tistory.com/380

인덱스 주기적으로 리빌드 하는 이유( Why does index rebuild in regulary?)

 

 

---

 

 

 

 

 

 

 

 

 

데이터베이스 권한 

---

 

데이터베이스

             데이터베이스 파일

             DBMS

                            네트워크

                            보안 - 사용자

권한

              데이터베이스  -  일부사용자

              사용자  -  전체데이터

절대  - root

 

사용자 - 접속아이피, 아이디, 비밀번호(암호)

 

 

 

use mysql;
show tables;

mysql 관리와 관련된 테이블

 

desc user;

 

desc db;

 

 

 

 

1. 사용자 

1-1. 생성

select host, user, password from user;

하면 전체 등록된 user를 모두  볼수 있다.

 

ip 주소를 %로 하면 전체가 다 다들어갈수 있다.

 

 

(1) 로컬로 접근 가능하게 만들었을 때 (localhost)

create user tester1@localhost identified by '1234';

mysql -u tester1 -p

로컬로 접속이 가능하다. 

 

mysql -h 192.168.219.120 -u tester1 -p

로컬 호스트로 권한을 줬기 때문에 내 ip주소로는 접근 불가

 

 

(2) 모두 접근 가능하게 만들었을 때 ( % ) 

create user tester2@'%' identified by '1234';

mysql -h 192.168.219.120 -u tester2 -p

 

 

 

1-2. 조회 , 삭제

이미 접속된 사용자는 도중에 사용자가 삭제되어도 로그아웃 후에 적용이 된다.

 

 

---

 

 

 

 

 

 

 

 

권한

---

특정 데이터베이스만 사용하게 만들기

→ 권한 주기

grant 권한 on 데이터베이스명

→  권한 뺏기

reboke 권한 on 데이터베이스명

 

 

< 프로젝트 >

1. 프로젝트용 데이터베이스

2. 데이터베이스 사용자

3. 테이블 생성

 

1. 모든 권한

사용자가 tester2일 때

 

갖고 있는 권한을 확인 후, sample이라는 데이터베이스에 대한 모든 권한을 부여 

show grants for tester2@'%';
grant all privileges on sample.* to  tester2@'%';

 

모든 데이터베이스의 모든 권한을 부여

grant all privileges on *.* to  tester2@'%'; 

 

sample 데이터에 대한 모든 권한을 삭제 후 갖고 있는 권한을 확인

revoke all on sample.* from tester2@'%';
show grants for tester2@'%';

 

 

grant로 권한을 주고 revoke로 권한을 삭제하면 user 의 권한에서 권한이 삭제된걸 확인 가능하다.

 

이미 접속한 상태에서 도중에 revoke 되면

그래도 계속 사용할 수 있다. (로그인 된 상태에서 revoke되면 로그아웃 까진 사용가능)

 

 

 

로그아웃 후에 새롭게  권한을 가진 사용자 tester3 를 만들고 

접속을 하면  모두 허용된다.

 

 

 

 

 

2. 부분권한

grant select on sample.emp to tester2@'%';
revoke select on sample.emp from tester2@'%';

 

 

문제 )  tester4 라는 사용자 (1234)에게  dept테이블에 select, insert 권한만 부여하기

create user tester4@'%' identified by '1234';
grant select, insert on sample.dept to tester4@'%';
show grants for tester4@'%';

또는

grant select, insert on sample.dept to tester4@'%' identified by '1234' ;
show grants for tester4@'%';

으로 사용자를 생성하고

 

사용자가 데이터베이스에 로그인하면 

mysql -u tester4 -p
use sample;
select * from dept;   // 허용
select * from emp;   // 거부

 

---

 

 

 

 

 

 

덤프

---

         - 데이터베이스를 백업 받는 행위(backup) / 리스토어(retore)

         => program

 

         * mysqldump 백업

         * mysql 리스토어

 

mysqldump

mysqldump -u root -p sample > c:\sql\sample.sql

테이블 dept, emp 등등 을 삭제한후 

덤프파일을 가져와서 백업하고 다시 sample에 들어가서 테이블 리스트를 확인하면

mysql -u root -p sample < c:\sql\sample.sql

삭제한 테이블들이 다시 생겨 있는 것을 확인 할 수 있다.

 

mysqldump -u root -p sample emp > c:\sql\emp.sql

하고 sample 데이터 베이스 안에서 

drop table emp; 한후 

mysql -u root -p sample < c:\sql\emp.sql

하면 삭제된 emp 테이블이 들어와 있다.

 

* salgrade  테이블을 백업 받고 지운다음에 복구

mysqldump -u root -p sample salgrade > c:\sql\salgrade.sql

mysql -u root -p sample < c:\sql\salgrade.sql

 

---

 

 

 

---

 

 

 

 

 

※ 테스트

 

1. main 메서드 형식

• public static void main(String [] arg)

 

2. 조건식 표현

 

 

3. 1차원배열 선언(초기화) 법

• int[] arr = new int[5];                         //  new 키워드 이용
• int[] arr = {1,2,3,4,5};                         //  리터럴 이용
• int[] arr = new int[]{1,2,3,4,5};      //   new 키워드와 리터럴 혼합

 

4. 생성자 표기법

• new 연산자를 통해서 인스턴스를 생성할 때 반드시 호출이 되고 제일 먼저 실행되는 일종의메소드(하지만 메소드와는 다름).
• 인스턴스 변수를 초기화 하는 역할을 한다.
• 리턴 타입이 없고, 클래스명과 동일하다. 

 

5. 오버로딩 조건

• 메서드 및 생성자 이름이 같아야한다.
• 인자리스트는 반드시 달라야 한다. (인자 개수 또는 인자 타입 또는 인자 순서)
• 리턴타입은 달라도 무관하다.

 

6. 오버라이딩 조건

• 상속이 전제되어야 한다. 
• 메서드 이름이 반드시 동일해야 한다.
• 메서드 리턴타입이 반드시 동일해야 한다. 단, 상속관계인 경우에는 작은 타입으로 재정의 가능하다.
• 메서드 인자 리스트가 반드시 동일 해야 한다.
• 접근 지정자는 부모의 레벨보다 같거나 확대만 가능하다.
• 예외 클래스는 부모의 클래스보다 계층적으로 같거나 하위 클래스만 사용 가능하다.
• static, final, private 지정자를 가진 메서드를 오버라이딩이 불가능하다.

 

7. 접근제어자

• public : 모든 클래스에서 접근이 가능하다.
• protected : 같은 클래스의 멤버, 같은 패키지 안의 다른 클래스의 멤버, 다른 패키지의 상속관계인 클래스의 멤버를 접근 할 수 있다. 상속 관계가 아닌 다른 패키지의 클래스의 멤버는 접근 할 수 없다.
• default : 같은 클래스의 멤버, 같은 패키지안의 다른 클래스의 멤버만 접근 가능하다. 패키지가 다르면 접근이 불가능하다.
• private : 같은 클래스의 멤버만 접근 가능하다.

 

8. final 의미

• 클래스에 사용하면 상속이 불가능하다. 
• 변수에 사용하면 값 변경이 불가능하다. 따라서 상수가 되며 변수와 구별하기 위해서 일반적으로 대문자로 표현한다.

... 

 

 

 

 

 

영구 저장장치

              로컬 - 파일

              원격 - 데이터베이스(조작의 편의성  -  SQL)

 

              JAVA - 데이터베이스