쿼리 명령어 총정리

*

SELECT * FROM 테이블명

 

Date검색

SELECT * 
    FROM 테이블명
    WHERE TO_CHAR(regdate,'YYYY-MM-DD') > '2021-11-29';

format 은 '-'를 제거한 'YYYYMMDD' 로도 가능하다.

 

AS

qr_name(제목), qr_desc(내용)만 보려면 어떻게해야할까

SELECT M.qr_name, M.qr_desc FROM QR_CODE_MEMBER AS M;

 

'****' AS password

패스워드를 '****'로 처리

WHERE

is_deleted가 1인 애들만하는등의 조건이 필요할경우 WHERE을 사용한다.

SELECT M.qr_name, M.qr_desc, M.is_deleted FROM QR_CODE_MEMBER AS M 
WHERE 1=1
	AND M.is_deleted = 1;

 

LIMIT

리스트중 몇개의 게시물만 보고싶을때.

paging처리시 유용함

LIMIT 1로 설정하면 맨처음 게시물만 출력됨

LIMIT start_no, page_count로 2개로 표현가능

SELECT * FROM 테이블명 WHERE sender=지갑주소 ORDER BY idx DESC LIMIT 0,5

 

INNER JOIN

QR_CODE 테이블이랑 연결해보자 공통되는부분인 qr_code_no를 이용해 qr_uuid만 가져오려한다.

 

SELECT M.qr_name, M.qr_desc, M.is_deleted, C.qr_uuid 
FROM QR_CODE_MEMBER AS M INNER JOIN QR_CODE AS C ON C.qr_code_no = M.qr_code_no
WHERE 1=1
	AND M.is_deleted = 1
LIMIT 1
;

INNERJOIN 2개도 가능함

SELECT
	C.qr_code_no
	, M.qr_name
	, M.qr_desc
	, DATE_FORMAT(IFNULL(M.reg_date, C.reg_date), '%Y-%m-%d %H:%i') AS reg_date
	, C.qr_uuid
	, M.member_qr_no
	, M.member_no
	, C.serial_code
	, R.company_name
FROM QR_CODE AS C
	INNER JOIN QR_CODE_MEMBER AS M ON C.qr_code_no = M.qr_code_no
		AND M.is_deleted = 0
	INNER JOIN MEMBER AS R ON R.member_no = M.member_no
WHERE 1 = 1
	AND C.qr_uuid = 'f797729d-c270-11eb-8130-fa163e32733f'
ORDER BY M.member_qr_no DESC
LIMIT 1;

ORDER BY 6 DESC

위의 ORDER BY M.member_qr_no DESC는 역순으로 M.member_qr_no를 기준으로 정렬한다는 뜻이다.

신기하게도? select문에서 나열된 M.member_qr_no이 6번째에 해당되기때문에 M.member_qr_no대신 숫자 6으로 대체가능하다.

 

OUTER JOIN, LEFT (OUTER) JOIN

INNERJOIN이 교집합이라면 OUTERJOIN은 합집합느낌이다.

통상적으로 LEFT를 앞에 붙여준다.

SELECT
	C.qr_code_no
	, M.qr_name
	, M.qr_desc
	, DATE_FORMAT(IFNULL(M.reg_date, C.reg_date), '%Y-%m-%d %H:%i') AS reg_date
	, C.qr_uuid
	, M.member_qr_no
	, M.member_no
	, C.serial_code
	, R.company_name
FROM QR_CODE AS C
	INNER JOIN QR_CODE_MEMBER AS M ON C.qr_code_no = M.qr_code_no
		-- AND M.is_deleted = 0
	LEFT OUTER JOIN MEMBER AS R ON R.member_no = M.member_no
WHERE 1 = 1
	AND C.qr_uuid = 'f797729d-c270-11eb-8130-fa163e32733f'
ORDER BY M.member_qr_no DESC
LIMIT 1;

INNER JOIN일경우 겹치는게없어서 테이블이 안나옴

A, B테이블이있다고 가정할때 A,B테이블에 공통 외래키값이 있을경우

조인을통해 연결할수있고 둘중 하나가 오타가 났을때도 '=' 을 사용해 이용가능하다.

SELECt poll_answer_no, poll_no, Q.member_qr_no, phone_no
FROM POLL_ANSWER AS P
	LEFT OUTER JOIN QR_CODE_MEMBER AS Q ON Q.member_qr_no = P.qr_no

DISTINCT, GROUP BY

조인혹은 조회시에 발생되는 중복제거 활용

GROUP BY는 정렬까지 자동으로해줌 그래서 속도가 DISTINCT보다 느리다함

 

dataformat

기존 reg_date '년-월-일 시:분:초' 를 초만빼서 '년-월-일 시:분'으로 나타내고싶을때

SELECT M.qr_name, M.qr_desc, M.is_deleted, C.qr_uuid , DATE_FORMAT(C.reg_date, '%Y-%m-%d %H:%i')
FROM QR_CODE_MEMBER AS M INNER JOIN QR_CODE AS C ON C.qr_code_no = M.qr_code_no
WHERE 1=1
	AND M.is_deleted = 1
LIMIT 1
;

 

* 보너스

IFNULL(M.reg_date, C.reg_date)이란 뜻은 둘중 한테이블에서 reg_date값이 null일경우

M테이블이나 C테이블중에 값이 있는 테이블로 대체한다는 뜻

SELECT 
	M.qr_name
	, M.qr_desc 
	, M.is_deleted
	, C.qr_uuid
	, DATE_FORMAT(IFNULL(M.reg_date, C.reg_date), '%Y-%m-%d %H:%i') AS reg_date
FROM QR_CODE_MEMBER AS M INNER JOIN QR_CODE AS C ON C.qr_code_no = M.qr_code_no
WHERE 1=1
	AND M.is_deleted = 1
LIMIT 1
;

 

%s

문자열이나 숫자를 대신나타내주는 것?

SELECT
	C.qr_code_no
	, M.qr_name
	, M.qr_desc
	, DATE_FORMAT(IFNULL(M.reg_date, C.reg_date), '%Y-%m-%d %H:%i') AS reg_date
	, C.qr_uuid
	, M.member_qr_no
	, M.member_no
	, C.serial_code
FROM QR_CODE AS C
	INNER JOIN QR_CODE_MEMBER AS M ON C.qr_code_no = M.qr_code_no
		AND M.is_deleted = 0
WHERE 1 = 1
	AND C.qr_uuid = %s
ORDER BY M.member_qr_no DESC
LIMIT 1;
;

테이블결과를 보고싶으면 %s자리에 알고싶은 문자열로 대체한다

LIKE '%문구%'

문구가 포함되는 모든것을 검색해줌. 예를들어 교보문구, 문구, 문방구 등등..

%없이 LIKE '문구'하면 딱 문구만 출력됨

SELECT * FROM 테이블명 
WHERE sender=지갑주소
AND mainTokenId LIKE '%221%'
OR subTokenIds LIKE '%221%'
ORDER BY idx DESC

 

CONCAT()

%s나 문자열을 붙여줄때 사용

	SELECT
		id
		, name
		, address
		, descript
		, DATE_FORMAT(reg_date, '%Y-%m-%d %H:%i') AS join_date
	FROM MEMBER
	WHERE 1 = 1
		AND ( (id LIKE CONCAT('%',%s,'%')) OR (id LIKE CONCAT('%',%s,'%')) )

ORDER BY ~ DESC

~ 를 기준으로 역순으로 정렬

ORDER BY M.qr_code_no DESC

 

update ~ SET

UPDATE QNA_BBS SET
	title = 'reply'
	, content = %s
WHERE 1 = 1
	AND qna_no = %s
	AND member_no = %s

 

insert

INSERT INTO QNA_BBS
(
	parent_qna_no, member_no, title, content, qr_code_no
)
VALUES
(
	%s, %s, 'reply', %s, %s
)
;

단, insert는 cursor.lastrowid와 같은 추가 내용이 필요함

* lastrowid는 커서객체를 생성할때 마지막 행(새로생긴행?)의 id를 제공.

https://docs.python.org/ko/3/library/sqlite3.html

sqlite3 — SQLite 데이터베이스용 DB-API 2.0 인터페이스 — Python 3.9.6 문서

https://stackoverflow.com/questions/2548493/how-do-i-get-the-id-after-insert-into-mysql-database-with-python

How do I get the "id" after INSERT into MySQL database with Python?

SHA2

데이타베이스 패스워드를 hash함수처리하면 암호화처리가됨

 

이름 NOT IN (쿼리문 | 문자열들)

()안의 이름과 같은 내용은 포함되지 않는다. 주로 where 절이후에 오는듯

SELECT * FROM TB_TEST WHERE TYPE=2 AND itemId NOT IN ('226654','226653') ORDER BY itemId DESC LIMIT 12,12;

itemId가 226654랑 226653은 포함하지않는다.  sql은 대괄호 []을 인식하지 않는데 post할때는 배열인 대괄호로 감싸 보내서 헷갈린다.

 

LIKE, IN

In은 or과 같은 의미로 하나의 조건만 만족회도 조회가능

SELECT * FROM EMP WHERE JOB IN ('MANAGER', 'DEVELOPER') -- 직업이 매니저 혹은 개발자인 데이터조회
SELECT * FROM EMP WHERE NAME LIKE 'test%' --네임이 test로 시작하는 데이터 조회

 

COUNT

//단순 카운트
SELECT COUNT(id) AS times FROM `race` WHERE id=1156
//조건있는 카운트
SELECT COUNT(`rank`) AS wins FROM `race` WHERE token_id=? AND `rank`=1

id의 경우는 문제가없었는데 rank의경우는 에러가떠서 문자열로 감싸니까 해결됨

 

SQL_CALC_FOUND_ROWS 

페이징처리할때 유용

SELECT SQL_CALC_FOUND_ROWS * FROM my_table LIMIT 0,10;-- 페이징용으로 0페이지 10개 데이터만 추출
SELECT FOUND_ROWS() as total; -- 전체갯수