2023.01.08 DB
DB
1강. 학습안내(SQL, DB, DBMS의 의미와 필요성)
SQL: DBMS에게 질의하는 명령어 DATABASE + MANAGEMENT SYSTEM
질의: 무엇을? 구조화된 데이터를
DATABASE 중복을 없애는 방법으로 결함을 줄임. 데이터를 한곳에 몰아 둬서 일을 중복으로 안하게 됨
단점
1.테이블이 여러개로 쪼개짐 근데 이것을 참조하게됨 참조방식이 여러개임
요즘엔 RELATIONAL DBMS 관계형임
2.동시성 한쪽에서 들어오면 한쪽에서도 건들수있게됨
3.성능 기다림이 생김
4.보안 네트워크 연결 상태니 보안문제도 생김 관리자에게 부탁해야 사용할 수있음. 관리시스템을 만드는게 중요함 그래서 그중 오라클이 가장유명함.
SQL(Structured Query Language) 질의던질때 고차원적인 질문을 함.
DDL어떤데이터 사용할지 명령 create/ alter/ drop
DML 데이터를 어떻게 관리할건지 select/insert/update/delete
DCL 관리자가 어떤 사용자의 계저에 대해 허가 취소 등 하는 제어명령어 grant revoke
2. 오라클
sqlplus (콘솔기반) /sql developer 1.인증 2.실행 3.결과확인
3.SQL Developer
콘솔기반 ui익숙하지 않음. sql developer사용하고자함.
4. 오라클 PDB 서버에 접속하기
오라클 서버가 종류가 두종류가 잇다. 버전이 올라가면서 가상화된 db가 등장햇다.
CREATE
PLUGGABLE DATATBASE hrpdb
ADMIN USER dba1
IDENTIFIED BY password
5. 수업용 사용자와 데이터베이스 생성하기
관리자 게정과 샘플데이터 계정요이 잇다.
db데이터를 모아놓는것 여러파일 가지고 있음. 데이터 저장할때 테이블 따라 저장됨.
객체 내가 관리하고자하는게 테이블 스페이스에 알아서 저장됨.
6. MEMBER 테이블 생성하기
DDL CREATE ALTER DROP 프로젝트 정수실수문자단위로 다루는게 아니라 큰단위 데이터 구조화딘데이터 속성이 업무에 따라 다를 수있음.
DB한테 학생이란 데이터를 할건데 이렇게 할것이다. 데이터 구조 생성 / 수정 / 삭제기능
이것을 조작 DML INSERT SELECT UPDATE DELETE - CRUD 데이터조작에 대한 부분을 숙달해야함.
DCL 권한 줫는데 GRANT REVOKE 등이 있다. 권한을 뺏거나 줄수 있다.
가장중요한건 DML이다.
데이터 정의하기 - 테이블 정의하기 데이터구조정의하기 : 개념사으이 데이터 정의하기 : Entity정의하기
여기에서는 '테이블'이라고 한다.
class Member
{
ID int,
}
다른 곳에서는 class로 묶고 자료형 필드이름으로 적었엇지만 이것을 오라클의 형태로 바꾸면된다.
CREATE TABLE MEMBER
(
ID VARCHARS2(50),
PWD VARCHARS2(50)
NAME VARCHARS2(50),
GENDER VARCHARS2(50),
AGE NUMBER,
BIRTHDAY VARCHARS2(50),
PHONE VARCHARS2(50),
REGDATE DATE
);
7. 오라클 데이터 형식 #1 (문자 형식)
Oracle Built-in Date Types
-Character 형식 ' '안에 넣어서 표시
-Numeric 형식 38 3.85F 등등
-Date 형식 '2013-02-09' - TIMESTAMP '2013-06-03 10.38.29.000'
-LOB 형식
1.Character 형식
CHAR(byte|char) 고정길이데이터임 50이면 공간이 50개를 다 안써도 차지함. 가변길이 데이터는 이거 사용하지 말자.
VARCHAR2(size [byte|char]) 가변길이로 최대값만 지정 데이터를 구분자로 비교함.
데이터마다 길이가 달라서 4번째 데이터가 얼마나 가야 4번째인지 알기힘들다. 그래서 검색에서 시간이 오래걸리는 단점이 있다.
그래서 고정길이는 char 가변길이는 varchar쓰자.
size=>1byte
밑에는 national이다. 값저장시 영어 한국어말고 다른 언어들이 한번에 들어갈일도 있다. 더 많은 바이트를 사용함.
NCHAR[(size)]
NVARCHAR2(size)
size => 2 or 3bytes
UTF-8로 인코딩됨.
멤버 좀 고쳐보자.
생일과 전화번호는 고정된 숫자이니 CHAR로 바꿔보자.
CREATE TABLE MEMBER
(
ID VARCHAR2(50),
PWD VARCHAR2(50),
NAME VARCHAR2(50),
GENDER CHAR(2 CHAR), --남성, 여성
AGE NUMBER,
BIRTHDAY CHAR(10), --2000-01-02
PHONE CHAR(13), --010-1234-2345
REGDATE DATE
);
size는 바이트 수를 의미한다 한글은 한글자당 2씩 들어감을 잘 알자. 글자 수가 아님!
아니면 size뒤에 char를 해주면 6바이트로 만들어지고 2글자가 들어갈수있게된다.
그런데 이렇게 적는것은 바람직하지 않다.
인코딩테이블에 따라 문자크기가 달라진다.
GENDER NCHAR(2), --남성, 여성 그래서 NCHAR로 하는게 더 바람직하다.
바이트기반이 아닌 문자열기반이면 N을 넣는게 낫다. 그런데 ID같은건 영어만들어가니 N이 들어갈일없다.
CREATE TABLE MEMBER
(
ID VARCHAR2(50),
PWD NVARCHAR2(50),
NAME NVARCHAR2(50),
GENDER NCHAR(2), --남성, 여성
AGE NUMBER,
BIRTHDAY CHAR(10), --2000-01-02
PHONE CHAR(13), --010-1234-2345
REGDATE DATE
);
비밀번호와 이름같은건 영 한글둘다 들어갈수있으니 N으로 해주자.
MAX SIZE 자료형으로
32767 bytes 익스텐드
4000 bytes 스탠다드
2천자 까지임.
VARCHAR2 숫자2는 오라클만 예약해놓은 키워드임 2없어도 되긴함.
8. 오라클 데이터 형식 #2 (숫자,날짜 형식)
Character 형식 추가
LONG 2기가바이트 잘쓰지 않음. CLOB가 대체로 나왔음. A라는 컬럼에 쓰면 다른 커럼에 사용불가
CLOB 대용량 텍스트 데이터타입(최대 4기가)
NCLOB 대용량 텍스트 유니코드 데이터타입(최대 4기가)
2.Numeric 형식 숫자
NUMBER 정수와 실수를 한번에 표기함.
NUMBER(4) 최대 4자로 이루어진 숫자
NUMBER(6,2) 소수점 2자리를 포함하는 최대 6자리 수ㅗ수점 둘째잧리에서 반올림
NUMBER(6,-2) 소수점 -2자리에서 반올림하는 최대 6자리의 숫자
NUMBER 아무것도 안쓰면 38자리숫자 = NUMBER(38,)
NUMBER(,5) 소수점 5자리가 들어간 38자리 숫자
DATE 4712 BC~9999AD (EX:01-JAN-99)
TIMESTAMP NLS_TIMESTAMP_FORMAT 파라미터에 명시된 값을 따름
TIMESTAMP WITH TIME ZONE / NLS_TIMESTAMP_TZ_FORMAT
TIMESTAMP WITH LOCAL TIME ZONE / NLS_TIMESTAMP_FORMAT
지역마다 표현방식이 다름.
SELECT * FROM NLS_DATABASE_PARAMETERS
9. 테이블 수정하기(ALTER TABLE)
1.ID를 NVARCHAR로 바꾸고싶음 2.AGE생년월일이있어서 필요없다. 3.REGDATE앞에 EMAIL을 추가하고싶다.
수정 삭제 추가 할것이다.
수정 ALTER TABLE MEMBER MODIFY ID NVARCHAR(50);
명령어는 소대문자 상관X 값은 소대문자 구분함.
테이블 드랍하고 다시만드는게 낫지않나? 데이터가 없어져버리니 유지하면서 컬럼을 바꿀땐 ALTER를 사용해야한다.
NUMBER로 바꾸거나 데이터를 줄인다? 수정이 가능해야 바뀐다. 먼저 보고 바꾸자.
삭제 ALTER TABLE MEMBER DROP COLUMN AGE;
추가 ALTER TABLE MEMBER ADD EMAIL VARCHAR2(200);
사실 이런쿼리는 잘 사용하지 않는다. DDL같은경우 수정사항이 어쩌다 한번 사용되서 잘까먹음.
보통 그냥 윈도우 기반 툴로 이용해서 편집하는 경우가 많다. 보통 편집도구사용한다.
10. SQL 쿼리 연습을 테이블 준비하기
DML은 잘외우고 있어야한다.
MEMBER 관리하는 NOTICE - 댓글
역할이 구분이 되어야하니 ROLE 회원이 멤버 롤 등.
테이블 명이 예약어나 이런걸 포함하면 부적합하다고 나온다.
""에 감싸면 부적합하더라도 사용할 수있다.
CREATE TABLE NOTICE
(
ID NUMBER,
TITLE NVARCHAR2(100),
WRITER_ID NVARCHAR2(50),
CONTENT CLOB,
REGATE TIMESTAMP,
HIT NUMBER,
FILES NVARCHAR2(1000)
);
CREATE TABLE "COMMENT"
(
ID NUMBER,
CONTENT NVARCHAR2(2000),
REGDATE TIMESTAMP,
WRITER_ID NVARCHAR2(50),
NOTICE_ID NUMBER
);
CREATE TABLE ROLE
(
ID VARCHAR2(50),
DISCRIPTION NVARCHAR2(500)
);
CREATE TABLE MEMBER_ROLE
(
MEMBER_ID NVARCHAR2(50),
ROLE_ID VARCHAR2(50)
);
11. 데이터 조작하기 #1 (INSERT/SELECT)
1.INSERT 명령규칙
INSERT INTO 테이블 VALUES 값목록
2.모든필드값을 입력하기
INSERT INTO MEMBER VALUES( 'newlec'...);
필수컬럼이있고 옵션컬럼이있다. 어떤것도 지정안한다하면 커럼갯수에 맞춰서 다 넣어야함.
3.원하는 필드만 원하는 순서대로 입력하기
INSERT INTO MEMBER(ID, PWD) VALUES('newlec','111');
테이블에()넣고 넣고싶은 컬럼만 지정할 수 있다. 순서가 바뀌어도 상관없다.
안넣는것은 어떻게 되나? 비워지나? NULL값이 들어간다.
꺼내보고싶으면
SELECT * FROM MEMBER;
필드 속성 컬럼이 프로그램마다 부르는게 달라서 혼용되서 사용되는데 다 같은 용어이다.
모든컬럼은 *인데 지정할 수도 있다.
SELECT id, name, pwd FROM MEMBER; 하면 세컬럼만 나온다.
키값을 이용해서 꺼내올 수도있다. 이름을 지정할 수도 있다. 사용자측에서 user_id로 사용한다면? db를 수정하기 힘들다.
SELECT id as user_id, name, pwd FROM MEMBER; 별칭을 제공할 수 있다.
as를 생략하더라도 별칭으로 인식한다. SELECT id user_id, name, pwd FROM MEMBER;
빈공백이 있는 별칭이 필요하다? SELECT id "user id", name, pwd FROM MEMBER;
""감싸면 그대로 유지하고자하고 그대로 제공하기때문에 대소문자 잘 생각해서 사용하자.
12. 데이터 조작하기 #2 (UPDATE/DELETE)
모든행의 pwd를 '222'로변경하기
UPDATE MEMBER SET PWD = '222';
주의할점 모든게 변경이 되었다. 만약 회원이 80만명인데 이렇게 되면 큰일남! 시스템마다 다르다.
똑같은 DB더라도 환경이 달라지면 SELECT 안될 수 있다. 오라클은 계정이 중요한게아니다. 사용자의 접속정보를 다르게 적용해서 따로 관리함 접속정보가 다르면 세션이 다르다.
그래서 데이터를 처리하는 방법이 다르다. 자세한 내용은 다음시간에..
특정 유저만 바꾸고 싶었다. dragon 유저는 다시 111로 바꾸고싶다.
UPDATE MEMBER SET PWD = '111' WHERE ID= 'dragon';
where절로 조건을 적용하지 않으면 전체를 바꿔버릴수 있으니 잘 보고 하자.
여러개 수정하자고하면 ,로 구분해서 수정하자.
삭제
DELETE MEMBER WHERE ID = 'test'; 당연한거지만 where절을 잘 확인하자.
13. 트랜잭션 처리를 위한 COMMIT과 ROLLBACK
트랜젝션이란? 업무 실행단위 / 논리 명령단위 / 개념상의 ...
업무적인 단위 물리적인 명령어 단위
계좌이체 update a계좌
update b계좌
한쪽에서 돈을뺏는데 한쪽에는 돈이 들어왓다? 한쪽이 돈이없으면 꽁돈을 줘버린거임
모든 명령이 만족이 되어야만 사용자에게 문제가 없다는 것을 확인하고 제공되어야한다.
한번에 실행될 이유가 있다. 이것을 업무적인 단위란 트랜잭션이다. 한쪽이 안되면 취소해버려야한다.
이벤트게시글등록 insert
update
1.현재 세션을 위한 임시저장소에서 테스트 완료전까지는 임시 다른사람이 들어와서 망치지 못하게 하자.
2.그동안 다른 세션이 건드리지 못하도록 LOCK, UNLOCK 해서 기다리게하기
3.이것이 COMMIT;과 ROLLBACK;이다.
모든게 완료된것 같다하면 COMMIT;을 해주자.
COMMIT이나 ROLLBACK전에는 LOCK이 걸린다. 다른곳에서 이것을 수정하려고하면 실행이 안된다. 한쪽에서 커밋이나 롤백을 해줘야 바뀐다.
14. 연산을 통한 데이터 조회 (산술 연산자)
사용자데이터를 날것으로 가져갈 일은 별로 없다.
원하는 컬럼만 -> 이미함
원하는 레코드만 선택적으로 필터링해보자.
원하는 컬럼의 원하는 레코드만, 데이터를 합쳐서 새로운 데이터를 만들수 있는가,
이번시간은 산술연산자
+,-,*,/
공지사항의 조회수를 조회하시오 단 기존값에 1을 더해서 조회하시오
SELECT HIT+1 FROM NOTICE;
-> 컬럼명이 HIT+1로나옴 사용자가 결과집합의 컬럼명으로 사용한다. 그래서 별명을 붙여보자
이렇게하면 자바 프로그래머가 쉽게 가져다 쓸 수 있다.
SELECT 1+'3' FROM DUAL;
실제 테이블의 값이 아니라 덧셈만 하고싶은데? DUAL이라는 더미테이블을 넣을 수 있게 해준다.
=> 4가나옴. 다른언어에서는 13으로 나오는데 오라클은 무조건 숫자만 나온다. 왜냐하면 +는 오라클에서는 숫자만 계산하는 연산자이기때문이다.
문자열을 결합하고자하면 ||을 사용해야한다. 문자열을 더함.
SELECT '3' ||10 FROM DUAL;
SELECT 1+'a' FROM DUAL; 하면 숫자로 바꿀수없어서 오류가 난다.
모든회원의 이름을 조회하시오 단 이름은 ID를 붙여서 나타내시오 예 홍길동(hong)
SELECT NAME || '(' || ID || ')' FROM MEMBER;
기호를 끼얹기 위해서 자바에서 + " " + 하는것처럼 || ' ' ||이런식으로 작성해주는 것이다.
연산된 컬럼에 별칭 사용해서 사용자가 쉽게 만들어주자
2023.01.08 리뷰
DB시작 완전 새로울 줄 알았지만 자바에서 배운 것이 도움이 된다.