SECURITY_STUDY

Create 문 : 데이터베이스 만들기, 테이블 만들기

DataBase 만들기

Create Database DB명

Table 만들기

Create Table [DB명.소유자명.]테이블명 (MyDB.dbo.UserInfo)

(

    필드명1 데이터타입 제약조건,

    필드명2 데이터타입 제약조건,

    필드명3 데이터타입 제약조건,

)

1) 제약조건1 : Null허용여부

    필드명 데이터형 null

    필드명 데이터형 not null

ex)

Create Table UserInfo

(

    id varchar(8) not null,

    login int null,

    userip varchar(15) null

)

2) 제약조건2 : Identity

- 데이터가 입력될 때마다 행에 순서 있는 유일한 번호를 붙일 때 사용

- DBMS에서 자동으로 채워 줌

- int형 필드에만 지정 가능

- not null 지정

- Identity(Seed, Increment)

필드명 int not null identity(1, 1)

3) 제약조건3 : 기본키 설정

각 레코드를 구별할 수 있는 유일한 값을 가지는 필드를 대상 설정, Primary Key

- 개체 무결성 제약조건, null일 수 없음

- 레코드를 식별할 수 있는 유일한 값을 가지는 필드에 설정

필드명 데이터형 not null constraint 기본키명(pk_테이블명) primary key

Foreign Key (외래키) : 참조 무결성 제약조건

Create Table 테이블명 (필드명 데이터형 not null constraint PK명 primary key,

필드명 데이터형 not null constraint FK명 foreign key reference 참조되는테이블(필드명))

테이블 생성 후 Key 제약조건 삭제

- 외래키 설정된 후에 참조되는 테이블의 기본키 삭제의 경우

외래키 먼저 제거 한 후에 참조되는 테이블의 기본키를 삭제할 수 있음

Alter Table 테이블명 drop constraint PK명

Alter Table 테이블명 drop constraint FK명

Table 수정하기

생성된 테이블에 필드 추가하기

Alter table 테이블명 add 필드명 데이터형 제약조건

1) null 필드추가 가능

2) not null 필드 추가 시에는 defalut 제약조건을 걸고 기본값 제공

생성된 테이블에서 필드 삭제

Alter table 테이블명 drop column 필드명

일반필드 (null, not null 제약조건은 그냥 삭제 가능)

기타 제약조건이 추가된 경우

- 필드 삭제문을 실행 : 제약조건 Key가 에러메시지에 노출

  Alter table 테이블명 drop column 필드명

- 제약조건 삭제 : alter table 테이블명 drop constraint Key값

- 필드 삭제 : alter table 테이블명 drop column 필드명

Insert 문

Insert into 테이블명 (필드명....)

values (값1, 값2, 값3....)

- 필드명은 모든 필드를 순서대로 입력하는 경우 테이블명과 values 사이의 필드명은 생략 가능 (identity 필드 제외)

ex)

Insert into Userinfo

(id, login, userip) values ('kh', 5, '192.168.0.2')

Insert into Userinfo

(id, login) values ('test', 2)

Insert into Userinfo

(id, userip) values ('test2', '192.168.0.3')

Insert into Userinfo

(login, userip) values (3, '192.168.219.4')       // id테이블이 not null 이므로 안됨

테이블 생성 후 PK설정

Alter table 테이블명 add constraint PK명 primary key (필드명)

중복 데이터 제거 (Distinct)

select distinct 필드명 from emp 테이블명 [where 조건]

ex) 직급 종류를 알고 싶은 경우

select distinct job from emp order by job

연산함수 (집계 함수)

sum(필드명) : 합계, 숫자타입의 필드에만 적용

avg(필드명) : 평균, 숫자타입의 필드에만 적용

count(필드명), count(*) : 개수

max(필드명) : 최대값

min(필드명) : 최소값

ex) deptno(부서번호) 가 10인 부서원들의 월급의 합

select sum(sal) as 월급합 from emp where deptno=10

ex) 부서번호가 10인 부서의 사원 수

select count(*) as 사원수 from emp where deptno=10

그룹화 문법 (group by)

select 필드명 from 테이블명 [where 조건] group by 필드명(그룹화 기준 필드) [order by 기준필드명]

group by 도 중복제거 효과가 있다.

distinct 와 group by 차이점

    - distinct : 그룹화, 중복제거는 할 수 있지만 그룹별 집계를 낼 수 없다.

    - group by : 그룹화, 그룹별 집계를 낼 수 있다.

그룹별 집계 (group by 와 집계함수 사용)

ex) job별 월급의 최대값, job으로 그룹화 지은 후에 각각 그룹의 월급 최대값

select job,max(sal) as 최대월급 from emp group by job

ex) 사장이 아닌 직급에 대해서 job별 월급의 최대값 구하세요

select job, max(sal) as 최대월급 from emp where job != '사장' group by job

그룹별 집계결과로 조건 (having 조건)

select 필드명 from 테이블명 [where 조건] group by 필드명(그룹화 기준 필드) [having 조건] [order by 기준필드명]

[where 조건] : 그룹화 시키기 전의 조건

[having 조건] : 그룹별 집계결과로 조건

ex) 사장이 아닌 직급에 대해서 job별 월급의 최대값, 최대값이 300 이상인 그룹만 검색

select job,max(sal) as 최대월급 from emp where job != '사장' group by job having max(sal)>=300

order by 에서는 별칭으로 쓸 수 있지만

having은 집계함수 원본을 써야한다.

상위 N 개의 레코드만 검색 (정렬과 함께 사용)

Top N : select 절에 사용

select top 5 필드리스트 from 테이블명 where 조건 order by 정렬기준

ex) emp 테이블에서 상위 5명만 검색하세요.

select top 5 * from emp

ex) emp 테이블에서 sal 순으로 내림차순 정렬 후 상위 5명의 데이터를 모두 검색하세요.

select top 5 * from emp order by sal desc

- 동순위 데이터가 있는 경우 (Top 5 한 경우 5위 6위 데이터가 동일한 경우)

top 5 with ties

검색 시 날짜 처리

날짜 처리 함수 getdate() : 현재 시스템의 날짜 시간을 얻어오는 함수

year() : datetime 타입의 필드를 대상으로 (예) 2020-07-22 를 정수로 연도만 반환해줌

          year(2020-07-22)  --> 2020

month() : datetime 타입의 필드를 대상으로 (예) 2020-07-22 를 정수로 월만 반환해줌

          year(2020-07-22)  --> 7

day() : datetime 타입의 필드를 대상으로 (예) 2020-07-22 를 정수로 일만 반환해줌

          year(2020-07-22)  --> 22

Between A and B 구문

where 절에 사용, A이상 B이하

select 필드리스트 from 테이블명 where 필드명 between A and B

ex) 월급이 200이상 300이하인 사람의 모든 값을 검색하세요.

select * rom emp where sal between 200 and 300

IN 연산자 (목록)

where 절에 사용

select 필드리스트 from 테이블명 where 필드명 in (값1, 값2, 값3....)

ex) 부서번호가 10, 20 인 사람의 모든 데이터를 검색

select * from emp where deptno in (10,20)

ex) 부서번호가 10도 아니고 20도 아닌 모든 데이터 검색

select * from emp where deptno not in (10,20)

문자열 비교

like 연산자

select 필드리스트 from 테이블명 where 필드명 like '문자+와일드카드 조합'

와일드 카드

_(underscore) : Any one character

% : Any zero or more

[ ] : [ ]안에 있는 글자들 중 한 문자

[^ ] : ^다음에 있는 글자를 제외한 다른 한 문자

예)

book_  : books / booka / booky  가능

            book / booked 불가능

book%  : book / books / booked 가능

             abook / adbooks / acbooked 불가능

%book%  : abook / abooks / book 가능

                brook / brooks 불가능

[st]ing  : sing / ting 가능

            ving / king / k / v / vin 불가능

[b-f]ing  : bing / cing / ding / eing / fing 가능

              Aing / b / f / ging 불가능

M[^c]%  : Mike / many / mickey 가능

              Mcdonald / mcatur / m / mc 불가능

ex) emp 테이블에서 이씨 사람의 ename 을 찾으세요

select * from emp where ename like '이%'

ex) emp 테이블에서 ename에 '성' 이 포함되 사람의 ename 을 찾으세요

select ename emp where ename like '%성%'

ex) emp 테이블에서 이름의 두번째 글자는 '성' 이고 그 뒤는 무엇이든 관계 없는 사원의 모든 정보를 찾으세요

select * from where ename like '_성%'

기본문법

select 필드명1, 필드명2, 필드명3........ from 테이블명 [where 조건]

dept

deptno      deptname       loc       tel

-------------------------------------------

10             security         서울       02

20             game           경기       031

30             design          서울       02

Student

sno        sname       tel       age       major       addr       deptno

----------------------------------------------------------------------------

s0001       kim        010       20       security      서울         10

s0002       lee         012       20       security      서울         10

1) 모두 검색

    select deptno, deptame, loc, tel from dept

    select * from dept

2) 일부 필드 검색 (부서번호, 부서명)

    select deptno, deptname from dept

3) 기본 조건 검색

    select * from dept where 조건

        - 문자타입 : ' ' 로 싸서 비교 (char, varchar, nchar, nvarchar, text)

        - 숫자타입 : 그냥 비교 (int, tinyint, money, smallmoney)

    ex) dept 테이블에서 loc가 경기인 모든 필드를 검색하세요.

    select * from dept where loc='경기'

    ex) student 테이블에서 나이가 20이상인 학생의 모든 필드를 검색하세요.

    select * from student where age>=20

비교연산자

같다 : =

다르다 : !=  /   (<>)    // != 나 (<>) 중 아무거나 사용해도 상관없음

비교 : >, <

이상, 이하 : >=, <=

산술연산자

+, -, *, /

논리연산자

and, or, not

ex) student 테이블에서 나이가 20이상이고 전공이 security인 학생의 모든 필드를 검색

select * from student where age>=20 and major='security'

4) 검색되는 필드명 바꾸기 (별명으로 바꾸기)   // 한글로도 변경이 가능하지만 영문 권장

select 필드명1 as 별명1, 필드명2 as 별명2.... from 테이블명 [where 조건]

ex) student에서 sno는 학번, sname은 학생이름 검색하세요. 단 age가 25이상인 사람만 검색하세요.

select sno as 학번, sname as 학생이름 from student where age>=25

5) 조건절에 null인 필드 찾기

null : 값이 없음을 뜻함

- null인 필드 검색

where 필드명 is null

- null이 아닌 필드 검색

where 필드명 is not null

6) 정렬 (sort)

오름차순 : ㄱ~ㅎ, 1~10, a~z

내림차순 : ㅎ~ㄱ, 10~1, z~a

- 오름차순 정렬

select 필드명.... from 테이블명 [where 조건] order by 기준필드명 [asc]

asc : 오름차순  //생략해도 오름차순으로 정렬 됨

- 내림차순 정렬

select 필드명.... from 테이블명 [where 조건] order by 기준필드명 desc

desc : 내림차순  //생략 불가능

- 정렬기준 여러 필드인 경우

select 필드명.... from 테이블명 [where 조건] order by 기준필드명 [asc or desc] , 기준필드명 [asc or desc]

Database

어느 한 조직의 여러 응용 시스템들이 공용할 수 있도록 통합, 저장된 운영 데이터의 집합이다.

Database의 특징

실시간 접근성(Real-time accessibility)

- 사용자의 요구에 대한 즉각적인 응답(Response)

계속적인 변화(Continuous evolution)

- 삽입, 삭제, 갱신 작업이 수시로 발생

동시 공용(Concurrent sharing)

- 여러 사용자가 동시에 자기가 원하는 데이터에 접근 가능

내용에 의한 참조(Content reference)

- 물리적 주소가 아닌, 데이터에 대한 조건으로 원하는 결과를 검출

Data Language (SQL)

DDL : Data Definition Language (데이터 정의어) (Create문, Drop문, Alter문)

    - 데이터베이스의 구조를 생성하거나 변경할 때 사용되는 언어

    - 스키마 전체 또는 구성 요소를 생성

    - 데이터베이스의 전체 구조를 생성하거나 변경

DML : Data Manipulation Language (데이터 조작어) (Select문, Insert문, Update문, Delete문)

    - 데이터를 조작하기 위한 언어로서 사용자와 데이터베이스 관리 시스템간의 인터페이스 제공

    - 검색, 삽입, 삭제, 갱신 연산을 제공

DCL : Data Control Language (데이터 제어어) (Grant문, Deny문)

    - 데이터를 제어하기 위한 언어로 사용자 권한 부여 및 철회, 데이터 무결성 관리, 회복과 백업 수행 명령어 제공

데이터

- 관찰이나 측정을 통해서 수집된 사실(facts)이나 값(value)

정보

- 의사결정에 도움이 되도록 데이터를 의미 있는 패턴으로 정리 한 것

정보처리 시스템

- 한 조직체의 활동에 필요한 데이터를 수집, 조직, 저장 하여 유용한 정보를 생성하고 분배하는 수단이다.

개체 (entity)

개체를 Table이라고 부르기도 함

하나의 DB는 하나 이상의 Table (entity)이 존재한다.

- 표현하려는 유무형 정보의 객체

- 정보의 단위

- 하나 이상의 속성으로 구성

개체의 예)

    학교 : 학생, 교수, 학과, ....

    서점 : 고객, 책, ....

    항공사 : 항공기, 고객, 조종사, ....

'학생' Table(객체) 의 예

학번, 이름, 학과를 필드(속성) 라고 한다.

학생 table에서 필드는 3개가 있고, 필드 이름은 학번, 이름, 학과 이다.

001 A 경영 -> 연관된 데이터이고, Record나 Tuple 이라고 부른다.

학생 table에서 Record의 개수는 1500개이다.

관계 (Relationship)

- 개체(table)와 개체(table) 간의 관계

- 관계도 데이터베이스에 저장할 대상

관계의 예

    - 학교 : 학생과 교수 사이에 '지도' 관계

    - 항공사 : 항공기와 고객 사이에 '예약' 관계

E-R 다이어그램  (entity - relationship)

예약 번호가 1234인 고객이름을 검색해라 -> 고객 객체에서 가능

예약 번호가 1234인 항공기 번호를 검색하라 -> '예약' 라는 관계를 이용

DBMS (DataBase Management System)

정의

- 사용자와 데이터베이스 사이에 위치하여 사용자의 요구에 따라 데이터베이스를 생성, 조작하고, 제어하는 기능을

  제공하는 소프트웨어

기능

    - 정의 기능 (Create문, Drop문, Alter문)

        데이터베이스의 논리적, 물리적 구조를 정의할 수 있는 기능 제공

    - 조작 기능 (Select문, Insert문, Update문, Delete문)

        사용자가 데이터베이스 내의 데이터를 조작할 수 있도록 하기 위한 기능 제공

    - 제어 기능 (Grant문, Deny문)

        데이터베이스가 항상 정확하고 올바른 데이터를 유지하도록 하기 위한 기능 제공

장점

- 데이터 중복 최소화

- 데이터의 공용성 증대

- 데이터의 일관성 유지

- 데이터의 무결성 유지

- 데이터의 보안 보장

- 범기관적 표준화 가능

DBA (DataBase Administrator)

데이터베이스 설계와 운영

- 데이터베이스 구성 요소 결정, 스키마 정의, 저장 구조와 접근 방법 설정, 보안 및 권한 부여 정책 결정, 백업, 회복 절차

  수립 등 작업 수행

행정 및 불평 해결

- 사용자의 요구를 받아 분석하고 불만 해소

시스템 감시 및 성능 분석

- 시스템 이용도, 병목 현상, 이용 패턴, 데이터 사용 추세, 각종 통계 등의 분석 작업을 수행한다.