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

250x250

Notice

Recent Posts

Recent Comments

Link

« 2024/06 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

Tags more

Archives

Today

Total

관리 메뉴

김미썸코딩

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

빅데이터 플랫폼 구축을 위한 자바 개발자 양성과정

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

김미썸 2020. 12. 2. 18:50

728x90

복습 & 개념 정리

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

1. subquery

쿼리 안에 쿼리

* 1열

1. 단일행 서브쿼리

비교 연산자

2. 복수행 서브쿼리

~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?)

테이블의 삽입/삭제는 인덱스에도 같이 반영됩니다. 그러나, 이런 행위 를 index rebuild라고 말하지는 않습니다. index rebuild라 함은 인덱스를 구성하는 전체 건에 대해 새로 인덱스를 생성하는 것을

swconsulting.tistory.com

데이터베이스 권한

데이터베이스

데이터베이스 파일

DBMS

네트워크

보안 - 사용자

권한

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

사용자 - 전체데이터

절대 - root

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

use mysql;
show tables;

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 - 데이터베이스

728x90

'빅데이터 플랫폼 구축을 위한 자바 개발자 양성과정' 카테고리의 다른 글

12/04 - JDBC(2) : CRUD, 네트워크(URL, URLConnection), TCP네트워킹(ServerSocket, Socket) (1)	2020.12.05
12/03 - JDBC (1) : 연결(Connection), Statement, PreparedStatement, ResultSet, 데이터베이스 정보 가져오기(MetaData) (1)	2020.12.04
12/01 - SQL(3) : 서브쿼리, join(inner, outer), create, alter, insert, update, delete, not null, default (0)	2020.12.01
11/30 - SQL(2) : where, order by, limit, 내장함수, 그룹화(group by, having) (0)	2020.12.01
11/27 - SQL(1) : Maria DB , SELECT, FROM, WHERE, DISTINCT (0)	2020.11.28

'빅데이터 플랫폼 구축을 위한 자바 개발자 양성과정' Related Articles

Comments

김미썸코딩

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

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

복습 & 개념 정리

제약조건

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

1. not null

2. unique

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

2-2. 확인하기

2-3. null 중복은?

3. primary key

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

2-2. 확인하기

4. foreign key

4-1. 선언과 에러

5. alter 구문

5-1. primary key

5-2. unique

5-3. foreign key

1) 추가

2) 삭제

view

1. 생성

2. 조회

* 인라인 뷰

3. 수정

4. 인라인 뷰

B-tree

1. 자동 index

2. 수동 index

인덱스 유지 보수

데이터베이스 권한

1. 사용자

1-1. 생성

1-2. 조회 , 삭제

권한

1. 모든 권한

2. 부분권한

덤프

※ 테스트

'빅데이터 플랫폼 구축을 위한 자바 개발자 양성과정' 카테고리의 다른 글

티스토리툴바