[SQL operators] #SQL #DB #SQLite #Database
-- MATH
+
-
*
/
%
-- Comparison
= / ==
!= / <>
>
<
>=
<=
-- Logical
AND
OR
BETWEEN --> a BETWEEN 1 and b
IN --> a IN (1,2,3)
NOT --> a NOT IN (1,2,3)
IS NULL --> a IS NULL
IS NOT NULL --> a IS NOT NULL
-- Text
|| --> city || ', ' || state
LIKE --> state LIKE 'D_l%'
REGEXP --> state REGEXP '[A-Z]{2}'
-- Common Core Functions
abs()
coalesce() --> coalesce(z, y) converts possible null value into a default value y
instr() --> instr('HTX', 'TX') = 2; returns index if match or 0 otherwise
length() --> length('Dalls') = 6
trim() --> trim(' TX ') = TX
ltrim()
rtrim()
random() --> pseudo random number
round() --> round(182.436, 2)
replace() --> replace substring; replace('Tom Nield','Tom',"Thomas") = Nield
substr() --> extracts range of char from string; substr('DOG', 2, 3) = OG
lower()
upper()
-- Aggregate Functions
avg(X) --> avg of values in column X
count(X) --> no of all non-null values in column X
count(*) --> no of records
max(X)
min(X)
sum(X)
group_concat(X) --> concats all non-null values in column X
-- Date & Time
SELECT '2015-05-14' > '2015-01-12'
SELECT DATE('now')
SELECT DATE('now', '-1 day')
SELECT DATE('2015-12-07','+3 month','-1 day')
SELECT '16:31:15'
SELECT '16:31'
SELECT '16:31' < '08:31'
SELECT TIME('now')
SELECT TIME('16:31','+1 minute')
SELECT '2015-12-13 16:04:11'
SELECT DATETIME('2015-12-13 16:04:11', '-1 day', '+3 hour')