[18일] Database (2) - 테이블 생성 삭제 수정, CRUD, as, count, where 조건절, between A and B 연산자, in 연산자, Like 연산자, Null

테이블 생성

형식

   create table 테이블(
       칼럼명1  자료형  제약조건
      ,칼럼명2  자료형  제약조건
      ,칼러명3  자료형  제약조건
      , ~~~
   );

제약조건을 꼼꼼히 주어야 쓰레기 값이 들어가는 것을 방지할 수 있다.

 

--sungjuk 테이블 생성
create table sungjuk(
     uname varchar(20)    --영문자 20글자 이내까지
    ,kor   int            --정수형
    ,eng   int
    ,mat   int
    .tot   int
    ,aver  int
);

칼럼의 순서는 의미가 없다.

실행 단축키 : Ctrl + Enter

실행할 때는 실행하고자 하는 코드의 블록을 잡고 한다.

 

테이블 삭제

형식

drop table 테이블명;

테이블을 삭제하면 모든 데이터도 같이 삭제된다

 

--sungjuk 테이블 삭제
drop table sungjuk;

 

테이블 수정

형식

alter table 테이블명 수정사항

 

관계형 DB에서 테이블의 핵심기능

• C (Create) : 생성   - insert 문
• R (Read) : 조회     - select 문
• U (Update) : 수정  - update 문
• D (Delete) : 삭제   - delete 문

 

문자열 데이터

데이터베이스에서 문자열 데이터는 ' '로 감싼다.

 

테이블 CRUD 작업

행 추가

형식) insert into 테이블명(칼럼명1, 칼럼명2, 칼럼명3, ...)
    values (값1, 값2, 값3, ...);
 
insert into sungjuk(uname, kor, eng, mat)
values ('홍길동', 100, 90, 80);

insert into sungjuk(uname, kor, eng, mat)
values ('진달래', 85, 75, 60);

 

행 조회 및 검색

형식) select 칼럼명1, 칼럼명2, 칼럼명3, ...
     from 테이블명;
     
select uname, kor, eng, mat, tot, aver from sungjuk;

null, '', ' ' 이 세가지는 다르다. null은 값이 아예 없기 때문에 글자 개수를 셀 수가 없다. ''는 0개이며 ' '는 1개이다.

 

행 수정

형식) update 테이블명 set 칼럼명1=값1, 칼럼명2=값2, 칼럼명3=값3, ...

update sungjuk set tot=kor+eng+mat, aver=(kor+eng+mat)/3;

(aver에 tot/3을 쓰면 값이 나오지 않는다. 아직 tot가 null값이라 tot/3 은 null/3이기 때문이다.)

수정한 후 다시 조회를 해보면

이렇게 총점과 평균이 나온다.

 

행 삭제

형식) delete from 테이블명;

delete from sungjuk;

삭제 후 다시 조회해보면 아래와 같이 테이블만 존재하고 행은 없다.

 

전체 레코드 조회

select uname, kor, eng, mat, tot, aver from sungjuk;

전체 행 개수 조회

select count(*) from sungjuk;

 

select 조회 및 검색

select kor, eng, mat from sungjuk;
select tot, aver from sungjuk;
select * from sungjuk;   --전체 모든 칼럼 조회

 

칼럼명 임시 변경

[as] --칼럼명을 임시 변경
select kor as 국어, eng as 영어, mat as 수학
from sungjuk;

-- as는 생략 가능
select kor korea, eng english, mat mate
from sungjuk;

select uname as 이름, kor as 국어, eng as 영어, mat as 수학, tot 총점, aver 평균
from sungjuk;  -- 임시 한글 칼럼명은 일시적으로 분석해야 하는 경우만 추천!!

별칭에 공백, 특수문자, 대소문자 등을 사용하려면 “Emp Id”처럼 큰따옴표(“)로 묶어서 사용해야 한다.

count 함수

[count 함수] --행 개수 조회
select count(uname) from sungjuk;
select count(kor) from sungjuk;
select count(eng) as cnt_eng from sungjuk;
select count(mat) 수학갯수 from sungjuk; --임시 칼럼명으로 한글 가능

--null값은 카운트하지 않는다.
select count(tot) from sungjuk;  --0
select count(aver) from sungjuk; --0

 

--sungjuk 테이블의 전체 행의 갯수
select count(*) from sungjuk;
select count(*) as 전체행갯수 from sungjuk;
select count(*) 전체행갯수 from sungjuk;

null 값이 있어도 전체 행의 갯수를 알 수 있다.

 

명령어 완료와 취소

- insert, update, delete 명령어를 사용하는 경우, 명령어 취소와 명령어 완료를 선택할 수 있다.
- commit : 명령어 완료
- rollback : 명령어 취소

※ SQL Developer툴에서 자동커밋 설정해 놓을 수 있다
   도구->환경설정->데이터베이스->객체뷰어->자동커밋설정

 

where 조건절

조건을 만족하는 레코드만을 대상으로 조회(select), 수정(update), 삭제(delete) 하기 위해서 사용한다.

문제를 풀어보며 where 조건절 및 연산자의 사용법을 익혀보자

 

--문1) 국어점수가 50점이상 행을 조회하시오
select uname, kor
from sungjuk
where kor>=50;

① sungjuk 테이블에 가서, ② kor이 50 이상인지를 확인하고 ③ 조건에 해당하는 행의 uname과 kor을 조회

 

--문2) 영어 점수가 50점미만 행을 조회하시오
select uname, eng
from sungjuk
where eng<50;

① sungjuk 테이블에 가서, ② eng가 50 미만인지를 확인하고 ③ 조건에 해당하는 행의 uname과 eng을 조회

 

--문3) 이름이 '대한민국' 행을 조회(출력)하시오
select uname, kor, eng, mat
from sungjuk
where uname='대한민국';  --좌우가 서로 같다

① sungjuk 테이블에 가서, ② uname이 대한민국인지를 확인하고 ③ 조건에 해당하는 행의 uname, kor, eng, mat를 조회

 

--문4) 이름이 '대한민국' 아닌 행을 조회하시오
select uname, kor, eng, mat
from sungjuk
where uname!='대한민국';

select uname, kor, eng, mat
from sungjuk
where uname<>'대한민국';

① sungjuk 테이블에 가서, ② uname이 대한민국이 아닌지를 확인하고 ③ 조건에 해당하는 행의 uname, kor, eng, mat를 조회

 

--문5) 국어, 영어, 수학 세과목의 점수가 모두 90이상 행을 조회하시오
select uname, kor, eng, mat
from sungjuk
where kor>=90 and eng>=90 and mat>=90;

① sungjuk 테이블에 가서, ② 국어, 영어, 수학이 90점 이상인지를 확인하고 ③ 조건에 해당하는 행의 uname, kor, eng, mat를 조회

 

--문6) 국어, 영어, 수학 중에서 한과목이라도 40미만 행을 조회하시오
select uname, kor, eng, mat
from sungjuk
where kor<40 or eng<40 or mat<40;

 

--문7) 국어점수가 80 ~ 89점 사이 행을 조회하시오
select uname, kor, eng, mat
from sungjuk
where kor>=80 and kor<=80

 

--문8) 이름이 '무궁화', '봉선화'를 조회하시오
select uname, kor, eng, mat
from sungjuk
where uname='무궁화' or uname='봉선화';

 

between A and B 연산자

A부터 B까지

-- 국어점수가 80 ~ 89점 사이 행을 조회하시오
select uname, kor
from sungjuk
where kor between 80 and 89;

in 연산자

목록에서 찾기

-- 이름이 '무궁화', '봉선화'를 조회하시오
select uname
from sungjuk
where uname in ('무궁화', '봉선화');

-- 국어점수 10, 30, 50점을 조회하시오
select uname
from sungjuk
where kor in (10, 30, 50);

-- 문9) 국, 영, 수 모두 100점 아닌 행을 조회하시오
select uname, kor, eng, mat
from sungjuk
where not(kor=100 and eng=100 and mat=100);

 

Like 연산자

• 문자열 데이터에서 비슷한 유형을 검색할 때 사용
• % 글자갯수와 상관없음
•  _ 글자갯수까지 일치해야 함

--문1) 이름에서 '홍'으로 시작하는 이름을 조회하시오
select uname
from sungjuk
where uname like '홍%';

--문2) 이름에서 '화'로 끝나는 이름을 조회하시오
select uname
from sungjuk
where uname like '%화';

--문3) 이름에 '나'글자 있는 이름을 조회하시오
select uname
from sungjuk
where uname like '%나%';

--문4) 두글자 이름에서 '화'로 끝나는 이름을 조회하시오
select uname
from sungjuk
where uname like '_화';

--문5) 이름 세글자 중에서 가운데 글자가 '나' 있는 행을 조회하시오
select uname
from sungjuk
where uname like '_나_';

--문6) 제목+내용을 선택하고 검색어가 '파스타'일때
where subject like '%파스타%' or content like '%파스타%';

 

--문7) 국어 점수가 50점 이상인 행에 대해서 총점(tot)과 평균(aver)을 구하시오
select * from sungjuk where kor>=50;          --데이터 확인

update sungjuk
set tot=kor+eng+mat, aver=(kor+eng+mat)/3
where kor>=50;

select * from sungjuk;                        --update 확인

 

Null

비어있는 값

-- 총점의 갯수를 구하시오
select count(tot) from sungjuk; --null값은 카운트 되지 않음

-- 총점에 NULL이 있는 행의 갯수를 구하시오
select count(*)from sungjuk where tot=null;  --null값 확인 못함. 0

select count(*) from sungjuk where tot is null;  -- null값 확인해야 하는 경우. 5

--평균에 비어있지 않은 행의 갯수를 구하시오 (null이 아닌 값)
select count(*) from sungjuk where aver is not null;

--문8) 비어있는 총점과 평균을 모두 구하시오
update sungjuk
set tot=kor+eng+mat, aver=(kor+eng+mat)/3
where tot is null or aver is null;