리눅스, 자료실, 성경검색, 추억의게임, 고전게임, 오락실게임, rootman, http://www.rootman.co.kr
* 23.21.4.239 *
| Home | Profile | Linux | 자료실 | zabbix | Mysql 5.6 | 갤러리 | 성경검색 | 해피니스 | 자유게시판 | 게시물검색 | L | O | R |    

 
[sqlite] [sqlite] Comparison Example
 작성자 : rootman
Date : 2011-10-28 11:31  |  Hit : 5,001  


CREATE TABLE t1(
     a TEXT, -- text affinity
     b NUMERIC, -- numeric affinity
     c BLOB, -- no affinity
     d -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right. Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur. All
-- values are compared numerically.
SELECT b < 40, b < 60, b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers. Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40, c < 60, c < 600 FROM t1;
0|0|0

-- No affinity conversions occur. Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40, d < 60, d < 600 FROM t1;
0|0|1

-- No affinity conversions occur. INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1
All of the result in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a".


 
 

Total. 19
번호 분류 제목 작성자 등록일 조회수
19 sqlite [sqlite3] 날짜와 시간 함수 알아보기 rootman 12-14 11682
18 sqlite [sqlite3] command, DB 전체를 dump export, dump import 하기 rootman 12-12 5976
17 sqlite [sqlite3] command, 외부로 import/export하기 rootman 12-12 5358
16 sqlite [sqlite] 유용한 함수들 rootman 11-22 8531
15 sqlite [sqlite] 반올림, 버림, 올림 함수 rootman 11-22 11954
14 sqlite [sqlite] I deleted a lot of data but the database file did n… rootman 10-28 4973
13 sqlite [sqlite] How do I add or delete columns from an existing tab… rootman 10-28 4964
12 sqlite [sqlite] Collation Sequence Examples rootman 10-28 5277
11 sqlite [sqlite] Assigning Collating Sequences from SQL rootman 10-28 5058
10 sqlite [sqlite] Collating Sequences rootman 10-28 4837
9 sqlite [sqlite] Sorting, Grouping and Compound SELECTs rootman 10-28 4961
8 sqlite [sqlite] Operators rootman 10-28 4835
7 sqlite [sqlite] Comparison Example rootman 10-28 5002
6 sqlite [sqlite] Type Conversions Prior To Comparison rootman 10-28 5002
5 sqlite [sqlite] Affinity Of Comparison Operands rootman 10-28 5064
 1  2  
AND OR