'SQL'에 해당되는 글 9건

text타입이 존재하는 mysql postgre 등 에서는 아래와 같이 하면 된다

해당하는 컬럼에 아래 어노테이션 중 하나를 추가해준다.
@Column(columnDefinition = "TEXT")
@Column(columnDefinition = "LONGTEXT")

update에서는 동작하지 않아서 create-drop으로 적용해야 한다.

ALTER TABLE comment_db MODIFY content LONGTEXT;

오라클에선 text 타입이 존재하지 않아 지원되지 않는다.

@Column(length=1000)으로 하면 1000 varchar2(1000 char)로 저장된다

마찬가지로 update에서는 동작하지 않는다.

수동으로 테이블을 변경할 때는 아래와 같다.

ALTER TABLE comment_db MODIFY content VARCHAR2(1000 char);

단위인 char를 생략하면 byte가 된다(byte는 언어별로 길이가 다르다)
오라클의 varchar2 최대길이는 4000 byte이다

데이터베이스 설계(스키마 설계)

물리명(실제 테이블 이름, 열 이름)과 논리명(설계상의 이름)

자료형(check 제약으로 조건에 맞는 값만 허용가능)

큰 데이터는 lob지정(인덱스가 안된다)

ER다이어그램

기본키가 되는 열부터 상단 열 이름은 논리명

연계(릴레이션십) 외래키와 다른 테이블 기본키 연결

정규화

제1정규형 : 반복되는 데이터를 줄이기 위해 테이블 추가 생성

테이블 분할과 기본키 지정

제2정규형 : 부분 함수 종속성을 찾아내 테이블 분할

함수종속성은 키 값을 이용해 데이터를 특정지을수 있는 것

제3정규형 : 중복되는 항목이 있을 경우 기본키 생성하고 분할

제5정규형까지 있지만 대부분 제3정규형까지 채택

목적 : 하나의 데이터가 한 곳에만 존재하도록 해 관리 편이

트랜잭션

start transaction;

insert into ..

...

commit;     commit하기전에 rollback;하면 트랜잭션 이후의 명령 롤백됨

sql server postgresql은 begin transaction

oracle이나 db2에는 transaction 없다고 하지만 oracle에는 존재한다

sqlplus로 확인해 보았고 start transaction을 사용한다

집합 연산

union 합집합

select * from sample71_a

union

select * from sample71_b; 두개의 테이블이 합쳐서 나온다 중복제거

열의 자료형이 일치해야 한다

select a from sample71_a 세개의 테이블의 결합

union

select b from sample71_b

union

select age from sample31;

select a as c from sample71_a

union

select b as c from sample71_b order by c; 별명을 붙여서 정렬한다

union all 중복을 제거하지 않고 합치기(union은 기본이 distict중복제거)

oracle 교집합 intersect 차집합minus(except)

테이블 결합

곱집합 적집합 카티전곱 요소종류1 x 요소종류2 의 개수가 된다

cross join 교차결합 곱집합으로

select * from sample72_x, sample72_y; 복수테이블 지정시 교차결합

select 상품.상품명, 재고수.재고수 from 상품, 재고수

  where 상품.상품코드 = 재고수.상품코드    결합조건

    and 상품.상품분류 = '식료품';                  검색조건

inner join 내부결합

select 상품.상품명, 재고수.재고수             

  from 상품 inner join 재고수                   

    on 상품.상품코드 = 재고수.상품코드      on으로 결합조건

  where 상품.상품분류 = '식료품';

foreign key 외부키 : 다른 테이블의 기본키를 참조하는 열

외부결합 left join right join(기준이 left or right)

select 상품3.상품명, 재고수.재고수

  from 상품3 left join 재고수

    on 상품3.상품코드 =  재고수.상품코드

  where 상품3.상품분류 = '식료품';

select 상품3.상품명, 재고수.재고수       구식방법은 되도록 사용하지 않는다

  from 상품3, 재고수

  where 상품3.상품코드 = 재고수.상품코드(+) (oracle:부재가능한 테이블의 열에 +)

    and 상품3.상품분류 = '식료품';

관계형 모델

릴레이션은 테이블

attribute(속성)은 열

tuple(튜플)은 행

union(합집합) union

difference(차집합) except

intersection(교집합) intersect

cartesian product(곱집합) from복수 테이블, cross join

selection(선택) 튜플의 추출은 where 데이터 검색

projection(투영) 반환될 열 지정

join(결합) 내부결합

데이터베이스 객체 테이블 뷰 인덱스 등 DB에서 정의하는 모든 것

기존 이름과 예약어 불가 / 숫자 시작X / 언더스코어_ 이외의 기호 사용 불가

한글은 더블쿼트(mysql백쿼트)로 둘러싼다 / 시스템 허용길이 준수

스키마라는 그릇에 담기므로 객체 이름이 같아도 스키마가 다르면 허용

mysql에서는 데이터베이스가 스키마

oracle에서는 데이터베이스와 데이터베이스 사용자가 계층적 스키마

이름이 충돌하지 않도록 기능하는 그릇(스키마나 테이블)을 네임스페이스

select, insert, delete, update 는 dml

테이블 생성

create table sample62 ( 

  no integer not null,

  a varchar(30),

  b date);

테이블 삭제 drop table

테이블 유지 데이터만 삭제할시 truncate table 테이블명

테이블 변경 

alter table 테이블명 변경명령

alter table sample62 add newcol integer; 열 추가

alter table sample62 modify newcol varchar(20); 열 속성 변경

alter table sample62 change newcol c; 열 이름 변경

alter table sample62 drop c; 열 삭제하기

제약

create table sample631(

  no integer not null, 널이 아니도록 제약

  sub_no integer not null,

  name varchar(30) unique, 유일한 값이도록 제약

  primary key(no, sub_no) 기본키가 되도록(유일) 제약

  //constraint pkey_sample primary key(no, sub_no) 제약에 이름짓기 contraint

);

제약 추가하기 제거하기

alter table sample631 modify c varchar(30) not null; 널이 아니도록 제약 추가

alter table sample631 modify c varchar(30); 널 제약 제거

alter table sample631 add constraint pkey_sample631 primary key(a); 기본키 추가

alter table sample631 drop constraint pkey_sample631; 기본키 제거

alter table sample631 drop primary key; 기본키 제거

복수의 열로 기본키 구성하기 a와 b가 둘다 동일하지 않으면 다름

인덱스 이진트리로 검색(항목 하부선택지3 좌:작다 중:같다 우:크다) 띄엄띄엄검색

create index isample65 on sample62(no); (여러 개의 열 지정 가능) 인덱스 생성

drop index isample65 on sample62; 인덱스 제거 sample62는 테이블명

검색은 테이블에서 수행

explain sql명령 index를 사용해서 검색을 하는지 확인가능

뷰 가상테이블

create view sample_view_67 as select * from sample54; 뷰 생성

뷰에서 검색가능

drop view sample_view_67; 뷰 제거

집계함수 count(집합) / sum(집합) / avg(집합) / min(집합) / max(집합)

select count(*) from sample51 where name='a'; count만 * 사용가능

select count(no), count(name) from sample51; null값은 제외하고 카운트 됨

ALL중복 상관없이 모두 조회, DISTINCT 중복제거하고 조회 기본은 ALL

select count(ALL name), count(DISTINCT name) from sample51;

select sum(quantity) from sample51; 합계

select avg(quantity), sum(quantity)/count(quantity) from sample51; 동일한 평균

select avg(case when quantity is null then 0 else quantity end) 

as avgnull0 from sample51; 널값을 0으로 변환해서 평균

select min(quantity),max(quantity),min(name),max(name) from sample51;

select * from 테이블명 group by 열1, 열2, ... 집계함수로 넘겨줄 인자를 그룹화

group by 할시 중복이 제거되어 distinct와 같은 효과

select name,count(name),sum(quantity) from sample51 group by name;

네임의 값마다 집계가 된다.

내부처리 순서 where -> group by -> having -> select-> order by

where에서는 집계함수 사용불가 대신 select에서 having으로 대체

having에서도 별명 사용불가

select name, count(name) from sample51 group by name having count(name) = 1;

name의 갯수가 1개인 모든 항목의 이름과 갯수를 검색

group by 지정한 열 이외의 열은 집계함수 사용하지 않고는 select에 포함불가

select min(no), name, sum(quantity) from sample51 group by name;

서브쿼리 : 쿼리 안에 (select)문 추가

delete from sample54 where a=(select min(a) from sample54); 

a가 가장 작은 행 삭제

서브쿼리 패턴

1. 하나의 값 반환 80 select min(a) from sample54;

2. 복수의 행, 하나의 열 select no from sample54;

3. 하나의 행, 복수의 열 select min(a),max(no) from sample54;

4. 복수의 행, 복수의 열 select no, a from sample54;

1번 패턴은 스칼라값 단일 값 스칼라 서브쿼리

스칼라 서브쿼리는 where에서 사용가능

select

    (select count(*) from sample51) as sq1,

    (select count(*) from sample54) as sq2 from dual; 

(oracle from dual 을 제외하면 mysql)

update sample54 set a=(select max(a) from sample54);

select * from (select * from samplee54) as sq; 중첩구조, 내포구조, nested구조

오라클에서는 as 제외 

오라클에서 limit 대체 명령

select * from (select * from sample54 order by a desc) sq where rownum <= 2;

insert into sample541 values

( (select count(*) from sample51), (select count(*) from sample54) );

insert into sample541 select 1,2; 동일 insert into sample541 values(1,2);

insert into sample542 select * from sample543; 테이블 복사

상관서브쿼리 

exist(select 명령) 반환 값의 존재 여부 확인

update sample551 set a='있음' where 

    exists (select * from sample552 where no2 = no);

update sample551 set a='없음' where

    not exists (select * from sample552 where no2 = no);

열에 테이블명 붙이기

update sample551 set a='있음' where

    exists (select * from sample552 where sample552.no2 = sample551.no);

열명 in 집합

select * from sample551 where no in (3,5);

select * from sample551 where no in (select no2 from sample 552);

NOT IN은 집합에 값이 포함되어있지 않으면 참 / NULL체크는 불가

테이블에 데이터 추가하기

insert into 테이블명 values(값1,값2,...)

insert into 테이블명 (열1,열2,...) values(값1,값2,...)

널 허용이거나 default가 설정됐다면 NULL이나 DEFAULT로 값을 지정할 수 있다

데이터 삭제하기

delete from 테이블명 where 조건식

데이터 갱신하기

update 테이블명 set 열1=값1,열2=값2,... where 조건식

update sample41 set no = no+1 모든 행의 no에 1 더함

update sample41 set no = no+1, a = no; 

mysql(no에 1+되고 a=현재 no)과 oracle 결과(no에 1+되고 a= 직전no)가 다르다

물리삭제와 논리삭제

물리삭제 : delete를 이용해 실제 삭제

논리삭제 : 삭제 플래그를 수정해 검색되지 않도록 구현

select 열명 from 테이블명 where 조건식 order by 열명;

조건식에 해당하는 행들의 열을 열명으로 정렬한다

desc 내림차순(큰 값에서 내려간다) asc 오름차순(작은 값에서 올라간다) 기본asc

select 열명 from 테이블명 where 조건식 order by 열명1 [정렬], 열명2 [정렬] ...

select * from sample32 order by a,b; a 우선 정렬 그 뒤에 b 정렬

select * from sample32 order by a asc, b desc; 

널 값의 정렬순서는 DB마다 다르다 mysql에선 가장 작은값

limit mysql비표준sql

select 열명 from 테이블명 limit 행수 [offset 시작행] offset은 0부터 시작(page*size)

select * from sample33 order by no desc limit 3; 내림차순 후 상위 3건만

limit 사용불가 db에서

sql server : select top 3 * from sample33;

oracle : select * from sample33 where rownum <= 3;

산술연산

*/% 우선순위 1

+- 우선순위 2

select *,price * quantity as amount from sample34;

ascii 문자 이외에 지정시에 더블쿼트 "금액"

select *,price * quantity as amount from sample34 where price * quantity >=2000;

where->select 순서로 처리되어 select의 별명을 where에서 쓸수 없다

NULL값의 연산은 NULL이다

select *,price * quantity as amount from sample34 order by price * quantity desc;

select *,price * quantity as amount from sample34 order by amount desc;

10%3 MOD(10,3) MOD(나머지연산 mysql,oracle)

ROUND(amount) 소수점 이하 반올림 amount는 DECIMAL 타입

ROUND(amount,1) 소수점 둘째 자리 반올림 ROUND(amount,-2) 10단위를 반올림

TRUNCATE 버림 함수 SIN,COS,SQRT,LOG 등의 함수 존재

문자열 연산

'ABC' || '1234' -> 'ABC1234 oracle,db2,postgresql 

a+b sql server / CONCAT(quantity,unit) mysql

SUBSTRING('20140125001',1,4) -> '2014'

SUBSTRING('20140125001',5,2) -> '01'

TRIM('ABC     ') -> 'ABC' 앞 뒤 공백문자 제거

CHARACTER_LENGTH CHAR_LENGTH

OCTET_LENGTH는 문자를 바이트 단위로 계산 주의 필요(EUC-KR 한글2/UTF-8 한글3)

날짜연산

CURRENT_TIMESTAMP CURRENT_DATE INTERVAL

select current_timestamp;

oracle to_date('2014/01/25','YYYY/MM/DD') to_char

CASE gender

    WHEN 1 THEN 'male'

    WHEN 2 THEN 'female'

END

select a, case when a IS NULL then 0 else a end "a(null=0)" from sample37;

a가 널일 때 값을 0으로

널값 반환일 경우 COALESCE 사용

select a, COALESCE(a,0) from sample37;

검색 케이스

select a as "코드"

case 

    when a=1 then 'male'

    when a=2 then 'female'

    else 'unknown'

end as "gender" from sample37;

단순 케이스

select a as "코드"

case a

    when 1 then 'male'

    when 2 then 'female'

    else 'unknown'

end as "gender" from sample37;

else가 없으면 else null 이 되므로 지정하는게 좋다

널값체크는 검색케이스를 사용

select * from 테이블명; 테이블의 모든 데이터 검색 (*:애스터리스크는 모든 열)

desc 테이블명 테이블의 열 정의 확인

자료형

INTEGER 수치형 (최대길이)

CHAR 고정 길이 문자열(최대길이) 길이가 최대길이보다 짧으면 공백문자로 채움

VARCHAR 가변 길이 문자열(최대길이)

DATE 날짜값 연월일

TIME 시간 시분초

select 열1,열2 from 테이블명 where 조건식 조건식을 만족하는 행에서 열1과 열2

select from where의 순서로 하지 않으면 에러 

문자열 리터럴은 싱글쿼트('')로 둘러싸 표기

비교연산자(연산자는 뛰어쓰면 안된다)

a = b 같다 / a <> b 다르다 / a > b a가 크다 / a >= b a가 크거나 같다 / 

a < b b가 크다 / a <= b b가 크거나 같다

NULL 은 IS NULL로 확인

select * from sample21 where birthday IS NULL; 

sample21테이블에서 birthday 열이 NULL인 행들의 모든 열

조건 조합

조건식1 AND 조건식2 둘 다 만족

select * from sample24 where a <> 0 and b <> 0

sample24테이블에서 a열과 b열이 0이 아닌 행들의 모든 열

조건식1 OR 조건식2 둘 중에 하나 만족

select * from sample24 where a <> 0 or b<>0;

sample24테이블에서 a열이 0이 아니거나 b열이 0이 아닌 행들의 모든 열

AND OR중 AND 우선순위가 높다 같이 사용한다면 괄호를 사용할 것

select * from sample24 where (a=1 or a=2) and (b=1 or b=2);

NOT 조건식 조건식의 부정을 만족

select * from sample24 where not(a<>0 or b<>0) 

a 열이 0이 아니거나 b열이 0이 아닌 행을 제외한 나머지 행들의 모든 열

열 LIKE '패턴' 매칭 부분검색 SQL% 전방일치 %SQL 후방일치 %SQL% 중간일치

select * from sample25 where text like '%SQL%' 

sample25테이블의 text열에 SQL이 포함된 행의 모든 열

%임의의 문자 / _임의의 문자 하나

%, _ 검색시에는 이스케이프 문자를 붙여 사용 '\%' '\_'

DB database의 약어

DBMS database management system의 약어

RDBMS Relational dbms

RDBMS를 조작하는 언어 SQL(Sequel)

DML data manipulation language 데이터 조작 가장 기본적인 명령

DDL data definition language 데이터 정의

DCL data control language 데이터 제어

DB의 종류

계층형 데이터베이스 폴더와 파일 등의 계층 구조(DBMS 채택 많지 않음)

관계형 데이터베이스 관계대수에 착안 행렬을 가지는 표형식 데이터

객체지향 데이터베이스 객체 그대로 데이터베이스 데이터로 저장

XML 데이터베이스 XQuery를 이용해 xml 검색

키 밸류 스토어(KVS) NoSQL 열 지향 데이터 베이스

RDBMS 제품

Oracle 오라클 개발 가장 많이 쓰이며 사실상 표준

DB2 IBM개발 

SQL server MS개발 윈도우 서버에서 사용

PostgreSQL 오픈소스

MySQL 오픈소스

SQLite 오픈소스 임베디드 시스템에 자주 쓰임 작은 규모