1--------------
2select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2,sign(-5)
3--------------
4
51+1	1-1	1+1*2	8/5	8%5	mod(8,5)	mod(8,5)|0	-(1+1)*-2	sign(-5)
62	0	3	1.60	3	3	3	4	-1
7--------------
8select floor(5.5),floor(-5.5),ceiling(5.5),ceiling(-5.5),round(5.5),round(-5.5)
9--------------
10
11floor(5.5)	floor(-5.5)	ceiling(5.5)	ceiling(-5.5)	round(5.5)	round(-5.5)
125	-6	6	-5	6	-6
13--------------
14select abs(-10),log(exp(10)),ln(exp(10)),log2(65535),log(2,65535),exp(log(sqrt(10))*2),pow(10,log10(10)),rand(999999),rand()
15--------------
16
17abs(-10)	log(exp(10))	ln(exp(10))	log2(65535)	log(2,65535)	exp(log(sqrt(10))*2)	pow(10,log10(10))	rand(999999)	rand()
1810	10.000000	10.000000	2.000000	2.000000	10.000000	10.000000	0.1844	0.7637
19--------------
20select least(6,1.0,2.0),greatest(3,4,5,0)
21--------------
22
23least(6,1.0,2.0)	greatest(3,4,5,0)
241.0	5
25--------------
26select 1 | (1+1),5 & 3,bit_count(7)
27--------------
28
291 | (1+1)	5 & 3	bit_count(7)
303	1	3
31--------------
32select 0=0,1>0,1>=1,1<0,1<=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a")
33--------------
34
350=0	1>0	1>=1	1<0	1<=0	strcmp("abc","abcd")	strcmp("b","a")	strcmp("a","a")
361	1	1	0	0	-1	1	0
37--------------
38select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b"
39--------------
40
41"a"<"b"	"a"<="b"	"b">="a"	"b">"a"	"a"="A"	"a"<>"b"
421	1	1	1	1	1
43--------------
44select "abc" like "a%", "abc" not like "%d%", "ab" like "a\%", "a%" like "a\%","abcd" like "a%b_%d"
45--------------
46
47"abc" like "a%"	"abc" not like "%d%"	"ab" like "a\%"	"a%" like "a\%"	"abcd" like "a%b_%d"
481	1	0	1	1
49--------------
50select "Det h�r �r svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$"
51--------------
52
53"Det h�r �r svenska" regexp "h[[:alpha:]]+r"	"aba" regexp "^(a|b)*$"
541	1
55--------------
56select !0,NOT 0=1,!(0=0),1 AND 1,1 && 0,0 OR 1,1 || NULL, 1=1 or 1=1 and 1=0
57--------------
58
59!0	NOT 0=1	!(0=0)	1 AND 1	1 && 0	0 OR 1	1 || NULL	1=1 or 1=1 and 1=0
601	1	0	1	0	1	1	1
61--------------
62select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0
63--------------
64
65IF(0,"ERROR","this")	IF(1,"is","ERROR")	IF(NULL,"ERROR","a")	IF(1,2,3)|0	IF(1,2.0,3.0)+0
66this	is	a	2	2.0
67--------------
68select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3
69--------------
70
712 between 1 and 3	"monty" between "max" and "my"	2=2 and "monty" between "max" and "my" and 3=3
721	1	1
73--------------
74select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0)
75--------------
76
772 in (3,2,5,9,5,1)	"monty" in ("david","monty","allan")	1.2 in (1.4,1.2,1.0)
781	1	1
79--------------
80select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'
81--------------
82
83hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
84hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
85--------------
86select concat("monty"," was here ","again"),length("hello"),ascii("hello")
87--------------
88
89concat("monty"," was here ","again")	length("hello")	ascii("hello")
90monty was here again	5	104
91--------------
92select locate("he","hello"),locate("he","hello",2),locate("lo","hello",2)
93--------------
94
95locate("he","hello")	locate("he","hello",2)	locate("lo","hello",2)
961	0	4
97--------------
98select left("hello",2),right("hello",2),substring("hello",2,2),mid("hello",1,5)
99--------------
100
101left("hello",2)	right("hello",2)	substring("hello",2,2)	mid("hello",1,5)
102he	lo	el	hello
103--------------
104select concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1))
105--------------
106
107concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1))
108happy
109--------------
110select concat("!",ltrim("  left  "),"!",rtrim("  right  "),"!")
111--------------
112
113concat("!",ltrim("  left  "),"!",rtrim("  right  "),"!")
114!left  !  right!
115--------------
116select insert("txs",2,1,"hi"),insert("is ",4,0,"a"),insert("txxxxt",2,4,"es")
117--------------
118
119insert("txs",2,1,"hi")	insert("is ",4,0,"a")	insert("txxxxt",2,4,"es")
120this	is a	test
121--------------
122select replace("aaaa","a","b"),replace("aaaa","aa","b"),replace("aaaa","a","bb"),replace("aaaa","","b"),replace("bbbb","a","c")
123--------------
124
125replace("aaaa","a","b")	replace("aaaa","aa","b")	replace("aaaa","a","bb")	replace("aaaa","","b")	replace("bbbb","a","c")
126bbbb	bb	bbbbbbbb	aaaa	bbbb
127--------------
128select replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL")
129--------------
130
131replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL")
132this is a REAL test
133--------------
134select soundex(""),soundex("he"),soundex("hello all folks")
135--------------
136
137soundex("")	soundex("he")	soundex("hello all folks")
138	H000	H4142
139--------------
140select password("test")
141--------------
142
143password("test")
144378b243e220ca493
145--------------
146select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0
147--------------
148
1490x41	0x41+0	0x41 | 0x7fffffffffffffff | 0	0xffffffffffffffff | 0
150A	65	9223372036854775807	-1
151--------------
152select interval(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0
153--------------
154
155interval(55,10,20,30,40,50,60,70,80,90,100)	interval(3,1,1+1,1+1+1+1)	field("IBM","NCA","ICL","SUN","IBM","DIGITAL")	field("A","B","C")	elt(2,"ONE","TWO","THREE")	interval(0,1,2,3,4)	elt(1,1,2,3)|0	elt(1,1.1,1.2,1.3)+0
1565	2	4	0	TWO	0	1	1.1
157--------------
158select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555,3),format(123456.5555,4),format(1234567.5555,5),format("12345.2399",2)
159--------------
160
161format(1.5555,0)	format(123.5555,1)	format(1234.5555,2)	format(12345.5555,3)	format(123456.5555,4)	format(1234567.5555,5)	format("12345.2399",2)
1622	123.6	1,234.56	12,345.556	123,456.5555	1,234,567.55550	12,345.24
163--------------
164select database(),user()
165--------------
166
167database()	user()
168	monty
169--------------
170select null,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null
171--------------
172
173NULL	isnull(null)	isnull(1/0)	isnull(1/0 = null)	ifnull(null,1)	ifnull(null,"TRUE")	ifnull("TRUE","ERROR")	1/0 is null	1 is not null
174NULL	1	1	1	1	TRUE	TRUE	1	1
175--------------
176select 1 | NULL,1 & NULL,1+NULL,1-NULL
177--------------
178
1791 | NULL	1 & NULL	1+NULL	1-NULL
180NULL	NULL	NULL	NULL
181--------------
182select NULL=NULL,NULL<>NULL,NULL IS NULL, NULL IS NOT NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0
183--------------
184
185NULL=NULL	NULL<>NULL	NULL IS NULL	NULL IS NOT NULL	IFNULL(NULL,1.1)+0	IFNULL(NULL,1) | 0
186NULL	NULL	1	0	1.1	1
187--------------
188select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null
189--------------
190
191strcmp("a",NULL)	(1<NULL)+0.0	NULL regexp "a"	null like "a%"	"a%" like null
192NULL	NULL	NULL	NULL	NULL
193--------------
194select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1)
195--------------
196
197concat("a",NULL)	replace(NULL,"a","b")	replace("string","i",NULL)	replace("string",NULL,"i")	insert("abc",1,1,NULL)	left(NULL,1)
198NULL	NULL	NULL	NULL	NULL	NULL
199--------------
200select field(NULL,"a","b","c")
201--------------
202
203field(NULL,"a","b","c")
2040
205--------------
206select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null,2 between null and 1,2 between 3 AND NULL
207--------------
208
2092 between null and 1	2 between 3 AND NULL	NULL between 1 and 2	2 between NULL and 3	2 between 1 AND null	2 between null and 1	2 between 3 AND NULL
2100	0	NULL	NULL	NULL	0	0
211--------------
212select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2)
213--------------
214
215insert("aa",100,1,"b")	insert("aa",1,3,"b")	left("aa",-1)	substring("a",1,2)
216aa	b		a
217--------------
218select elt(2,1),field(NULL,"a","b","c")
219--------------
220
221elt(2,1)	field(NULL,"a","b","c")
222NULL	0
223--------------
224select locate("a","b",2),locate("","a",1),ltrim("a"),rtrim("a")
225--------------
226
227locate("a","b",2)	locate("","a",1)	ltrim("a")	rtrim("a")
2280	1	a	a
229--------------
230select concat("1","2")|0,concat("1",".5")+0.0
231--------------
232
233concat("1","2")|0	concat("1",".5")+0.0
23412	1.5
235--------------
236select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(curdate()+1)-to_days(curdate()),weekday("1997-01-01")
237--------------
238
239from_days(to_days("960101"))	to_days(960201)-to_days("19960101")	to_days(curdate()+1)-to_days(curdate())	weekday("1997-01-01")
2401996-01-01	31	1	2
241--------------
242select period_add("9602",-12),period_diff(199505,"9404")
243--------------
244
245period_add("9602",-12)	period_diff(199505,"9404")
246199502	13
247--------------
248select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now())
249--------------
250
251now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(now())
2520	0	0
253--------------
254select now(),now()+0,curdate(),weekday(curdate()),weekday(now()),unix_timestamp(),unix_timestamp(now())
255--------------
256
257now()	now()+0	curdate()	weekday(curdate())	weekday(now())	unix_timestamp()	unix_timestamp(now())
2581998-08-17 04:24:33	19980817042433	1998-08-17	0	0	903317073	903317073
259