[SQL] sql 구문 몇가지

[출처 : 빠르게 활용하는 모바일 데이타베이스 SQLite3]

insert into 매출 select * from 매출 where 날짜='2014-1-1';

select 항목.명칭, 항목.카테고리, 장부.날짜, 장부.금액, 장부.부가세 from 항목, 장부 where 장부.항목id=항목.id and 날짜 like '2013-%';

select 장부.날짜, sum(장부.금액),sum(장부.부가세), sum(장부.금액)+sum(장부.부가세) from 항목, 장부 where 장부.항목id=항목.id group by 날짜 order by 날짜;

월 로열티 발주비용 인건비 기타비용 비용합계 매출 카드사입금 현금입금 총입금 손익

1

2

3

SELECT * FROM tblBookInfo WHERE publisher_id IN ( 1, 4 ) ==

SELECT * FROM tblBookInfo WHERE publisher_id IN ( SELECT publisher_ID FROM tblPushliderInfo WHERE name IN ('aaa', 'bbb') );

SELECT name, (SELECT COUNT(*) FROM tblBookInfo WHERE tblBookInfo.publisher_id = tblPublisherInfo.publisher_id) as count FROM tblPublisherInfo;

SELECT tblBookInfo.title, tblBookInfo.price, tblPublisherInfo.name FROM tblBookInfo INNER JOIN tblPublisherInfo

ON tblBookInfo.publisher_id = tblPublisherInfo.publisher_id WHERE tblPublisherInfo.name = 'Wikibooks'

SELECT title, price FROM tblBookInfo WHERE price IN ( SELECT price FROM tblBookInfo WHERE title = 'SQLite 3' );

== >

SELECT B1.title, B1.price FROM tblBookInfo as B1, tblBookInfo as B2 WHERE B1.price = B2.price

AND B2.title = 'SQLite3';  (중첩보다 조금 빠르다)

SELECT name, count(*) as Count FROM tblBookInfo, tblPublisherInfo WHERE tblBookInfo.publisher_id = tblPublisherInfo.publisher_id

GROUP BY tblBookInfo.publisher_id

ORDER BY Count DESC;

CREATE VIEW 뷰이름 AS 뷰를 만들 셀렉트