Mikkel84
10/8/2018 - 7:23 AM

[SQL operators] #SQL #DB #SQLite #Database

[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')