기본 sql 연습
# 이노디비 + 인덱스 관련 자료
#직접 테이블과 필드를 만들어에 FULLTEXT 인덱스를 추가
CREATE TABLE author (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
FULLTEXT (author)
) ENGINE=InnoDB;
ALTER TABLE books ADD FULLTEXT (AUTHR_NM);
ALTER TABLE books ADD FULLTEXT INDEX TITLE_FULLTEXT (TITLE_NM);
ALTER TABLE books ADD FULLTEXT INDEX ISBN_THIRTEEN_NO (ISBN_THIRTEEN_NO);
#B트리 인텍스 (숫자정보는 FULLTEXT로 인덱스화할 수 없음)
CREATE INDEX idx_ISBN_THIRTEEN_NO ON test.books(ISBN_THIRTEEN_NO);
alter table books add FULLTEXT index
AUTHR_NM_IDX(AUTHR_NM) WITH PARSER ngram;
# ngram passer로 인덱싱하여 테이블에 저장
# [AUTHR_NM_IDX]이 만들어질 테이블 이름
# (AUTHR_NM) 인덱싱할 컬럼
alter table books add FULLTEXT index
TITLE_NM_test1(TITLE_NM) WITH PARSER ngram;
#이노 db 최소 검색값 확인하기
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
#이노 db 최소 검색값 재설정하기 > 실행후 mysql 재실행 필요
#콘솔에서 변경 불가 > 직접 파일을 찾아서 수정해야함
SET GLOBAL innodb_ft_min_token_size = 2;
#ngram 파서 토큰 사이즈 확인하기 = 기본사이즈 2
SHOW VARIABLES LIKE 'innodb_ft_ngram_size';연습2. JPQL 연습
create database test;
use test;
create table memver(id varchar(20) primary key, name Long);
use test;
INSERT INTO member (id, password, username) VALUES (1, 'asdf1234', 'gamdol');
select * from oldbooks;
select TITLE_NM from oldbooks;
select AUTHR_NM from oldbooks;
select IMAGE_URL from oldbooks;
#where 조건을 달아서 검색할 수 있다. 그런데 조건을 여러개 걸고싶으면?
select TITLE_NM, AUTHR_NM,BOOK_INTRCN_CN,ADTION_SMBL_NM from oldbooks
where ADTION_SMBL_NM < 5000;
# select * from book where TITLE_NM != "쑥 캐는 불장이 딸";
select TITLE_NM from oldbooks where TITLE_NM like '나사렛%';
# 조건을 두개 붙이고싶어
select TITLE_NM from oldbooks
where TITLE_NM like '%자바%'
and TITLE_NM like '%정석%';
#자바의 정석은 왜 안나오는가? 그냥 책이 없음
select TITLE_NM from oldbooks
where TITLE_NM like '자바의 정석';
#특정 컬럼의 중복 데이터 제외하고 가져오기
select distinct TITLE_NM from oldbooks;
#모든 컬럼의 중복 데이터 제외하고 가져오기
select distinct * from oldbooks;
#카운트 세기
select count(*) from oldbooks;
#중복되는 책제목 제외하고 카운트 세기
select count(distinct (TITLE_NM)) from oldbooks;
#제목에 자바가 들어간 책의 작가 불러오기
select AUTHR_NM from oldbooks
where TITLE_NM like '%자바%';
#제목이 알고싶어. 북테이블에있는. 기준은 10000만원이 가면서 (조건추가) 책제목에 자바가 들어가는거.
#만원이 넘는 자바관련 책
select TITLE_NM from oldbooks where ADTION_SMBL_NM >10000 and TITLE_NM like '%자바';
#만원이 넘는 자바관련 책 숫자세기
select count(TITLE_NM) from oldbooks where ADTION_SMBL_NM >10000 and TITLE_NM like '%자바';
#같지않음 조건 걸기
# select * from orders
# where course_title != "웹개발 종합반";
#범위조건
# select * from orders
# where created_at between "2020-07-13" and "2020-07-15";
#포함조건
# select * from checkins
# where week in (1, 3);
#패턴 문자열 조건 걸어보기
# select * from users
# where email like '%daum.net';
# where email like 'a%': email 필드값이 a로 시작하는 모든 데이터
# where email like '%a' email 필드값이 a로 끝나는 모든 데이터
# where email like '%co%' email 필드값에 co를 포함하는 모든 데이터
# where email like 'a%o' email 필드값이 a로 시작하고 o로 끝나는 모든 데이터
select * from oldbooks where TITLE_NM like '%가나안%'
#테이블 만들기
create table test(id int primary key auto_increment ,count int, value varchar(255));
#가나안이 들어가는 책 카운트하기
SELECT COUNT(*), '가나안'
FROM oldbooks
WHERE TITLE_NM LIKE '%가나안%';
#구해온 값을 새로운 테이블에 넣기
insert into test(count, value)
select count(*), '가나안으로 검색했을때 나온 책 권수'
from oldbooks
where TITLE_NM like '%가나안%';
#group by 연습1 틀렸음
SELECT COUNT(*), '자바'
FROM oldbooks
WHERE TITLE_NM LIKE '%자바%'group by 'kakao';
#group by 연습2 그룹바이에 넣는 컬럼을 기준으로 묶인다.
#group by는 동일한 범주를 갖는 데이터를 하나로 묶어서 범주별 통계를 내주는 것, 같은 제목의 책을 묶어서 권수를 셀 수 있다.
select TITLE_NM, count(*)
from oldbooks
where TITLE_NM like '%스프링%'
group by TITLE_NM ;
select TITLE_NM from oldbooks
where TITLE_NM like '%요리%';
select * from test.oldbooks where title_nm like '%제주%';
select * from oldbooks b where b.TITLE_NM like '%제주%';
#동일한 범주의 개수 구하기
# select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
# group by 범주별로 세어주고 싶은 필드명;연습3. Full-Text Search
#JPQL 쿼리 연습하기
select * from oldbooks b;
select * from oldbooks b where b.TITLE_NM like '%제주%';
#여러개의 컬럼에서 같이 불러오고 싶다면? 어떻게 해야하는가
select * from oldbooks b where b.TITLE_NM like '%자바%';
#order by 검색결과 정렬하는 방법 오름차순 ASC
select b.TITLE_NM from oldbooks b order by b.TITLE_NM ASC;
select * from oldbooks b order by b.TITLE_NM ASC;
select * from oldbooks b order by b.PRC_VALUE ;
SELECT * FROM oldbooks b WHERE b.title_nm LIKE '%자바%' OR b.AUTHR_NM LIKE '%자바%' order by b.PRC_VALUE;
#가격이 null이 아니고 제목에 자바가 들어가는 책 검색
SELECT * FROM oldbooks b WHERE b.PRC_VALUE IS NOT NULL AND b.title_nm LIKE '%자바%';
#널 값인 데이터 지우기
# DELETE FROM Book b WHERE b.title_nm IS NULL
#JPQL에서는 엔티티의 속성 값을 삭제할 수 있지만, 속성 자체를 삭제하는 것은 불가능
# 속성값을 null값으로 설정하면 해당 속성이 삭제된 것과 같은 효과를 얻을 수 있다.
#컬럼 날려버리기
# ALTER table book drop column INTNT_BOOKST_BOOK_EXST_AT;
# ALTER table book drop column TWO_PBLICTE_DE
# ALTER table book drop column PORTAL_SITE_BOOK_EXST_AT;
select * from books b where b.TITLE_NM like '%자바%' and b.TITLE_SBST_NM like '%자바%';
#특정 단어 제외하고 찾기
SELECT * FROM books b WHERE b.TITLE_NM LIKE '%자바%' AND b.TITLE_NM NOT LIKE '%스크립트%';
SELECT * FROM books b WHERE b.TITLE_NM LIKE '%자바%' AND b.TITLE_NM LIKE '%정석%';
SELECT * FROM books b WHERE b.TITLE_NM LIKE '%스프링%' and b.TITLE_NM like '%자바%';
SELECT * FROM books b WHERE b.TITLE_NM LIKE '%스프링%' ;
#group by 와 as, count 사용하기
select TITLE_NM, count(*) as '사랑인가봐' from books b where b.TITLE_NM group by b.TITLE_NM;
SELECT * FROM books b WHERE b.TITLE_NM LIKE '%자바의 정석%' ;
SELECT * FROM books b WHERE b.TITLE_NM LIKE '%스프링%' OR b.AUTHR_NM LIKE '%스프링%' ;
select b.AUTHR_NM, b.AUTHR_SBST_NM from books b ;
#직접 테이블과 필드를 만들어에 FULLTEXT 인덱스를 추가
CREATE TABLE author (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
FULLTEXT (author)
) ENGINE=InnoDB;
ALTER TABLE books ADD FULLTEXT (AUTHR_NM);
ALTER TABLE books ADD FULLTEXT INDEX TITLE_FULLTEXT (TITLE_NM);
ALTER TABLE books ADD FULLTEXT INDEX ISBN_THIRTEEN_NO (ISBN_THIRTEEN_NO);
#B트리 인텍스 (숫자정보는 FULLTEXT로 인덱스화할 수 없음)
CREATE INDEX idx_ISBN_THIRTEEN_NO ON test.books(ISBN_THIRTEEN_NO);
alter table books add FULLTEXT index
AUTHR_NM_IDX(AUTHR_NM) WITH PARSER ngram;
# ngram passer로 인덱싱하여 테이블에 저장
# [AUTHR_NM_IDX]이 만들어질 테이블 이름
# (AUTHR_NM) 인덱싱할 컬럼
alter table books add FULLTEXT index
TITLE_NM_test1(TITLE_NM) WITH PARSER ngram;
#이노 db 최소 검색값 확인하기
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
#이노 db 최소 검색값 재설정하기 > 실행후 mysql 재실행 필요
#콘솔에서 변경 불가 > 직접 파일을 찾아서 수정해야함
SET GLOBAL innodb_ft_min_token_size = 2;
SELECT * FROM test.books WHERE MATCH(TITLE_NM) AGAINST ('자바의 정석' IN NATURAL LANGUAGE MODE);
#자연어 검색
select *from books
where match(TITLE_NM) against ('자바' IN NATURAL LANGUAGE MODE );
#불리언 검색
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바' WITH QUERY EXPANSION);
# 자바 and 프로그래밍
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 +프로그래밍' IN BOOLEAN MODE);
# 자바 or 프로그래밍
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바 | 파이썬' IN BOOLEAN MODE);
# 자바 not JavaScript
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 -JavaScript' IN BOOLEAN MODE);
#쿼리 확장 검색
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바' WITH QUERY EXPANSION);
#and 연산자 사용
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 +프로그래밍' IN BOOLEAN MODE);
#or 연산자 사용
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바 | 파이썬' IN BOOLEAN MODE);
# not 연산자 사용
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('+자바 -JavaScript' IN BOOLEAN MODE);
#Limit 절로 반환하는 데이터 제한하기
SELECT * FROM books
WHERE MATCH(TITLE_NM) AGAINST ('자바' WITH QUERY EXPANSION)
LIMIT 10;
# 불리언 모드
select ISBN_THIRTEEN_NO,TITLE_NM,
match(TITLE_NM) against ('+총 +균 +쇠' IN BOOLEAN MODE ) as score
from books
where match(TITLE_NM) against ('+총 +균 +쇠' IN BOOLEAN MODE );
select * from books where ISBN_THIRTEEN_NO = 9788970127248;
select * from books where ISBN_THIRTEEN_NO = 9788970127248;
# 검색 점수 스코어로 보기
SELECT *, MATCH(TITLE_NM) AGAINST('가나안') AS score
FROM books
WHERE MATCH(TITLE_NM) AGAINST('가나안');
SELECT MATCH (TITLE_NM) AGAINST ('가나안') FROM books GROUP BY ="가나안" WITH ROLLUP;
SELECT 1 FROM t GROUP BY a, MATCH (a) AGAINST ('abc') WITH ROLLUP;
#자연어 임계값 확인
SHOW VARIABLES LIKE 'ft_min_word_len';
SHOW VARIABLES LIKE 'ft_max_word_len';
나중에 지워질까봐 백업용