2023.03.07 DB
DB MySQLver
1. 학습안내(SQL, DB, DBMS의 의미와 필요성)
SQL: DBMS에게 질의하는 명령어 DATABASE + MANAGEMENT SYSTEM
질의: 무엇을? 구조화된 데이터를
DATABASE 중복을 없애는 방법으로 결함을 줄임. 데이터를 한곳에 몰아 둬서 일을 중복으로 안하게 됨
단점
1.테이블이 여러개로 쪼개짐 근데 이것을 참조하게됨 참조방식이 여러개임
요즘엔 RELATIONAL DBMS 관계형임
2.동시성 한쪽에서 들어오면 한쪽에서도 건들수있게됨
3.성능 기다림이 생김
4.보안 네트워크 연결 상태니 보안문제도 생김
즉 관리자에게 부탁해야 사용할 수있음.
관리시스템을 만드는게 중요함 그래서 그중 오라클이 가장유명하고
mysql로 이 수업을 진행해보고자 한다.
SQL 이 DBMS에게 해달라 부탁하는 것이다. 내가 직접 DBMS를 건드는게 아니다.
SQL(Structured Query Language) 질의던질때 고차원적인 질문을 한다.
DDL어떤데이터 사용할지 명령 create/ alter/ drop
DML 데이터를 어떻게 관리할건지 select/insert/update/delete
DCL 관리자가 어떤 사용자의 계정에 대해 허가 취소 등 하는 제어명령어 grant revoke
https://chunpinge.tistory.com/130
MYSQL초반부 설정
5. MEMBER 테이블 생성하기
프로젝트를 다룰때 정수실수문자단위로 다루는게 아니라 큰단위 데이터 구조화딘데이터 속성이 업무에 따라 다를 수 잇다.
내가 데이터를 이야기할때 이런 속성으로 이루어져있음을 DBMS에게 데이터의 구조를 이야기하는 것이다.
5.1 DDL
CREATE / ALTER / DROP 데이터 구조 생성 / 수정 / 삭제기능이다.
이것을 조작할수 있어야한다.
5.2 DML
INSERT SELECT UPDATE DELETE
-> CRUD 데이터조작에 대한 부분을 숙달해야함.
5.3 DCL
GRANT REVOKE 권한을 뺏거나 줄수 있다.
5.4 데이터 정의하기
테이블 정의하기 데이터구조정의하기 : 개념상의 데이터 정의하기 : Entity정의하기 등등
정의하고자하는 데이터는 MEMBER데이터이다. 개념화된걸 DBMS에게 알려주어야한다.
다른 언어에서는 Entity라고 햇엇고 여기에서는 table이라고한다.
class Member
{
ID int,
}
다른 곳에서는 class로 묶고 자료형 필드이름으로 적었엇지만
이것을 dbms의 형태로 바꾸면된다.
https://arrkaize86.tistory.com/entry/MySQL-Oracle-%EA%B3%BC-%EB%B9%84%EA%B5%90
기존 오라클과 MYSQL비교
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 (문자 형식)
http://www.incodom.kr/DB_-_%EB%8D%B0%EC%9D%B4%ED%84%B0_%ED%83%80%EC%9E%85/MYSQL 참조
Oracle Built-in Date Types
-Character 형식 ' '안에 넣어서 표시
-Numeric 형식 38 3.85F 등등
-Date 형식 '2013-02-09' - TIMESTAMP '2013-06-03 10.38.29.000'
-LOB 형식
7.1 Character 형식
CHAR(size[byte]) 고정길이데이터 50이면 공간이 50개를 다 안써도 차지한다.
가변길이 데이터는 이거 사용하지 말자.
들쭉날쭉하다보니 공간이 비어잇어도 공간을 다 차지하고있어서 비효율적이다.
VARCHAR(size [byte]) 가변길이로 최대값만 지정 데이터를 구분자로 비교한다.
데이터마다 길이가 달라서 4번째 데이터가 얼마나 가야 4번째인지 알기힘들다. 그래서 검색에서 시간이 오래걸리는 단점이 있다.
그래서 고정길이는 char 가변길이는 varchar쓰자.
가변 길이 데이터 타입(최대 65535byte)
size=>1byte
NCHAR
NVARCHAR
'유니코드 문자열(한국어, 중국어 등 비영어, 다국어 지원 일 때)' 사용일 때 적합하다.
전세계각국을 지원하면 이것들을 사용해야한다.
값 저장시 영어 한국어말고 다른 언어들이 한번에 들어갈일도 있다. 더 많은 바이트를 사용한다.
size => 2 or 3bytes
UTF-8로 인코딩된다.
멤버 좀 고쳐보자.
생일과 전화번호는 고정된 숫자이니 CHAR로 바꿔보자.
CREATE TABLE MEMBER
(
ID VARCHAR(50),
PWD VARCHAR(50),
NAME VARCHAR(50),
GENDER CHAR(2), -- 남성 여성
AGE INT,
BIRTHDAY CHAR(10), -- 2000-01-02
PHONE CHAR(13), -- 010-1234-2345
REGDATE DATE
);size는 바이트 수를 의미한다 한글은 한글자당 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으로 해주자.
char (최대 255byte)
VARCHAR(n) 가변 길이 데이터 타입(최대 65535byte)이다.
8. 데이터 형식 #2 (숫자,날짜 형식)
8.1 Character 형식
큰 문자열을 담으려면 다음것을 사용하면된다. 오라클의 CLOB과 같은 것들이다.
TEXT(n) 문자열 데이터 타입(최대 65535byte)
MEDIUMTEXT(n) 문자열 데이터 타입(최대 16777215byte)
LONGTEXT(n) 문자열 데이터 타입(최대 4294967295byte) 4기가 == 오라클CLOB
8.2 Numeric 형식 숫자
8.2.1 정수형 타입
오라클의 number(숫자) 최대 숫자만큼으로 이루어진 숫자
TINYINT == number(3)
smallint == number(5)
mediumint == number(8)
int == number(10)
int는 정수형 데이터 타입(4byte)
-2147483648 ~ +2147483647 또는 0 ~ 4294967295수 표현할 수 있다.
BIGINT(n) 아주큰 범위의 정수를 저장하는 데이터타입이다.
정수형 데이터 타입(8byte) - 무제한 수 표현할 수 있다.
보통은 int를 사용하면 될 것 같다.
8.2.2 실수형 타입
오라클 NUMBER(N,M) 소수점 M자리를포함하는 최대 N자리
실수형 타입은 얼마나 저장할지와 소수점뒤에 얼마나 많은 개수의 자리수가 있는지를 지정할 수 있다.
DECIMAL(길이, 소수) 고정 소수형 데이터 타입고정(길이+1byte) -소수점을 사용 형태이다.
일반적으로 사용된다.
길이는 최대로 쓸수잇는 전체숫자의 자리수이고
소수는 소수점뒤에 잇는 자리수를 의미한다.
길이는 최대 65 소수자리수는 최대 30까지의 값을 가질 수 있다.
FLOAT(길이, 소수) 부동 소수형 데이터 타입(4byte) -고정 소수점을 사용 형태이다.
DOUBLE(길이, 소수) 부동 소수형 데이터 타입(8byte) -DOUBLE을 문자열로 저장한다.
8.3 날짜
DATE 날짜(년도, 월, 일) 형태의 기간 표현 데이터 타입(3byte)
DATETIME 날짜와 시간을 저장하는 데이터타입 년원일 시분초
TIMESTAMP 날짜와 시간 년월일 시분초 + 타임존 정보도 함께저장
오라클 기준 그냥 TIMESTAMP이 DATETIME이고 TIMESTAMP WITDH TIMEZOME = TIMESTAMP인듯하다.
타임존 정보를 저장해야하면 TIMESTAMP 아니라면 DATETIME
9. 테이블 수정하기(ALTER TABLE)
테이블에서 수정사항이 잇을 경우 어떻게 해야할까
1.id들 nvarchar로 바꾸고싶다.
2.생일이 잇으니 age를 굳이 받을 필요가 없다.
3.regdate 앞에 이메일을 받고 싶다.
컬럼 타입 수정
ALTER TABLE 테이블이름 MODIFY 필드 타입
ALTER TABLE MEMBER MODIFY ID NVARCHAR(50);
컬럼 삭제
ALTER TABLE MEMBER DROP COLUMN AGE;
컬럼추가
ALTER TABLE 테이블이름 ADD 컬럼이름 타입
ALTER TABLE 테이블이름 ADD 컬럼이름 타입 FIRST - 맨위에 추가
ALTER TABLE 테이블이름 ADD 컬럼이름 타입 AFTER another_field - 어떤컬럼이후에 추가
ALTER TABLE MEMBER ADD EMAIL VARCHAR(200);
일반적으로 이런 쿼리는 잘 사용하지 않는다.
이런 DDL같은 경우는 수정사항이 정말 어쩌다한번 일어나게 된다.
이런걸 잘 사용하는 일이 별로 없다. 보통 윈도우 기반 툴로 수정한다.
10. SQL 쿼리 연습용 테이블 준비하기
DDL은 굳이 암기할 필요는 없지만
CURD를위한 DML은 잘 이해하고 있어야 한다.
CREATE TABLE NOTICE
(
ID INT,
TITLE NVARCHAR(100),
WRITER_ID NVARCHAR(50),
CONTENT TEXT,
REGDATE DATETIME,
HIT INT,
FILES NVARCHAR(1000)
);
CREATE TABLE COMMENT
(
ID INT,
CONTENT VARCHAR(1000),
REGDATE DATETIME,
WRITER_ID NVARCHAR(50),
NOTICE_ID INT
);
CREATE TABLE ROLE
(
ID VARCHAR(50),
DESCRIPTION VARCHAR(500)
);
CREATE TABLE MEMBER_ROLE
(
MEMBER_ID NVARCHAR(50),
ROLE_ID VARCHAR(50)
);MEMBER 관리하는 NOTICE - 댓글
역할이 구분이 되어야하니 ROLE 회원이 멤버 롤 등.
테이블 명이 예약어나 이런걸 포함하면 부적합하다고 나온다.
오라클은 ""에 감싸면 부적합하더라도 사용할 수있다.
MYSQL의 경우 ``백틱을 사용하면 부적합한 이름을 넣을 수 있다.
11. 데이터 조작하기 #1 (INSERT/SELECT)
INSERT INTO table1 (field1, field2) VALUES (value1, value2);
데이터를 넣어볼 것이다.
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;
""감싸면 그대로 유지하고자하고 그대로 제공하기때문에 대소문자 잘 생각해서 사용하자.
``백틱도 됨.
2. 데이터 조작하기 #2 (UPDATE/DELETE)
2.1 UPDATE
UPDATE table1 SET field1=new_value1
모든행의 pwd를 '222'로변경하기
주의할점 모든게 변경이 되었다. 만약 회원이 80만명인데 이렇게 되면 큰일남! 신중하게 하자.
특정 유저만 바꾸고 싶었다. dragon 유저는 다시 111로 바꾸고싶다.
UPDATE MEMBER SET PWD = '111' WHERE ID= 'dragon';
where절로 조건을 적용하지 않으면 전체를 바꿔버릴수 있으니 잘 보고 하자.
여러개 수정하자고하면 ,로 구분해서 수정하자.
삭제
DELETE FROM MEMBER WHERE ID = 'test'; 당연한거지만 where절을 잘 확인하자.
오라클은 DELETE문에 FROM이 없어도된다. 차이 기억하기(JDBC보면 FROM 쓰는데 생략가능한것일듯)
UPDATE MEMBER SET PWD = '222';
-> safemode가 걸려잇어서 where절 없이 한꺼번에 변경이 불가능하다.
edit - 설정 - sql editers
https://thalals.tistory.com/85
13. 트랜잭션 처리를 위한 COMMIT과 ROLLBACK
트랜젝션이란? 업무 실행단위 / 논리 명령단위 / 개념상의 ...의 의미를 가지고 있다.
업무적인 단위 물리적인 명령어 단위이다.
계좌이체 update a계좌
update b계좌
한쪽에서 돈을뺏는데 한쪽에는 돈이 들어왓다? 한쪽이 돈이없으면 꽁돈을 줘버린거이다.
모든 명령이 만족이 되어야만 사용자에게 문제가 없다는 것을 확인하고 제공되어야한다.
한번에 실행될 이유가 있다. 이것을 업무적인 단위란 트랜잭션이다. 한쪽이 안되면 취소해버려야한다.
이벤트게시글등록 insert
update
1.현재 세션을 위한 임시저장소에서 테스트 완료전까지는 임시 다른사람이 들어와서 망치지 못하게 하자.
2.그동안 다른 세션이 건드리지 못하도록 LOCK, UNLOCK 해서 기다리게하기
3.이것이 COMMIT;과 ROLLBACK;이다.
모든게 완료된것 같다하면 COMMIT;을 해주자.
COMMIT이나 ROLLBACK전에는 LOCK이 걸린다.
다른곳에서 이것을 수정하려고하면 실행이 안된다.
한쪽에서 커밋이나 롤백을 해줘야 바뀐다.
14. 연산을 통한 데이터 조회 (산술 연산자)
연산자 모음 https://oneul-losnue.tistory.com/114
사용자데이터를 날것으로 가져갈 일은 별로 없다.
원하는 컬럼만 -> 이미했다.
원하는 레코드만 선택적으로 필터링해보자.
원하는 컬럼의 원하는 레코드만, 데이터를 합쳐서 새로운 데이터를 만들수 있는가, 등등 자세하게 배워보자.
이번시간은 산술연산자
MySQL은 대부분의 프로그래밍 언어에서 지원하는 기본적인 연산자를 모두 제공한다. 당연히 사칙연산 모두 가능하다.
오라클과 동일하게 +,-,*,/와 DIV, %또는 MOD이 있다.
DIV : 왼쪽 피연산자를 오른쪽 피연산자로 나눈 후, 소수 부분을 버린다.
%또는 MOD : 왼쪽 피연산자를 오른쪽 피연산자로 나눈 후, 그 나머지를 반환한다.
공지사항의 조회수를 조회하시오 단 기존값에 1을 더해서 조회하시오
SELECT HIT+1 FROM NOTICE;
-> 컬럼명이 HIT+1로나옴 사용자가 결과집합의 컬럼명으로 사용한다. 그래서 별명을 붙여보자
SELECT HIT+1 HIT FROM NOTICE;
이렇게하면 자바 프로그래머가 쉽게 가져다 쓸 수 있다.
덧셈을할때 숫자 + 문자열을하면어떻게 될까?
SELECT 1 + '3' FROM DUAL;
DUAL은 더미테이블로 결과만 확인할때 사용한다.
-> MySQL은 생략해도 더미테이블 결과로 보여준다!!!
4가나온다.
다른언어에서는 13으로 나오는데 오라클은 무조건 숫자만 나온다.
왜냐하면 +는 오라클에서는 숫자만 계산하는 연산자이기때문이다.
문자열을 결합하고자하면 concat(문자, 문자)함수를 사용해야한다.
select concat('3',10) FROM DUAL;
오라클에서는 ||로 문자열을 더하는데 MySQL에서는 ||은 참거짓을 판별하는 연산자이다.
모든회원의 이름을 조회하시오 단 이름은 ID를 붙여서 나타내시오 예 홍길동(hong)
-> select concat(NAME,'(', ID ,')') FROM MEMBER;
기호를 끼얹기 위해서 자바에서 + " " + 하는것처럼 ''를 넣어주면된다.
-> 오라클 버전 SELECT NAME || '(' || ID || ')' FROM MEMBER;
15. 비교연산자
=, != , <>, > ,<, >=, <+, IS NULL, IS NOT NULL
!= , <> 다 같지 않다를 나타내는 연산자이다
!=은 오라클제외로 다썻엇는데 표준이아닌 표준이 되어버렷다.
<> 표준을 정의하자고 한 ASNI SQL에서 정한 것이다.
^=은 오라클에서 만든 같지않다이다.-> 그래서 MYSQL에는 없다.
연습 문제
게시글 중 작성자가 newlec인 게시글만 조회하시오.
SELECT * FROM NOTICE WHERE WRITER_ID = 'newlec';
게시글 중 조회수가 3이 넘는 글만 조회하시오.
SELECT * FROM NOTICE WHERE HIT >= 3;
게시글 중에서 내용을 입력하지 않은 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE CONTENT IS NULL
= 'NULL'은 안된다. NULL이라는 문자가 들어가있을 수도 있기 때문이다.
DB에선 반드시 IS NULL을 사용해야한다.
2023.03.07 후기
오라클db강의를 mysql로 연습하면서 듣고 있다. 예상처럼 거의 비슷하지만 다른점이 한두개식 있다. 이런점을 확인하면서 공부해나가니 좀더 집중하는 결과가 낳아지는 것 같다.
중요한것은 꺾이지 않는 마음이다.