|
1
by brian
clean slate |
1 |
--------------
|
2 |
select 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 |
||
5 |
1+1 1-1 1+1*2 8/5 8%5 mod(8,5) mod(8,5)|0 -(1+1)*-2 sign(-5) |
|
6 |
2 0 3 1.60 3 3 3 4 -1 |
|
7 |
--------------
|
|
8 |
select floor(5.5),floor(-5.5),ceiling(5.5),ceiling(-5.5),round(5.5),round(-5.5) |
|
9 |
--------------
|
|
10 |
||
11 |
floor(5.5) floor(-5.5) ceiling(5.5) ceiling(-5.5) round(5.5) round(-5.5) |
|
12 |
5 -6 6 -5 6 -6 |
|
13 |
--------------
|
|
14 |
select 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 |
||
17 |
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() |
|
18 |
10 10.000000 10.000000 2.000000 2.000000 10.000000 10.000000 0.1844 0.7637 |
|
19 |
--------------
|
|
20 |
select least(6,1.0,2.0),greatest(3,4,5,0) |
|
21 |
--------------
|
|
22 |
||
23 |
least(6,1.0,2.0) greatest(3,4,5,0) |
|
24 |
1.0 5 |
|
25 |
--------------
|
|
26 |
select 1 | (1+1),5 & 3,bit_count(7) |
|
27 |
--------------
|
|
28 |
||
29 |
1 | (1+1) 5 & 3 bit_count(7) |
|
30 |
3 1 3 |
|
31 |
--------------
|
|
32 |
select 0=0,1>0,1>=1,1<0,1<=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") |
|
33 |
--------------
|
|
34 |
||
35 |
0=0 1>0 1>=1 1<0 1<=0 strcmp("abc","abcd") strcmp("b","a") strcmp("a","a") |
|
36 |
1 1 1 0 0 -1 1 0 |
|
37 |
--------------
|
|
38 |
select "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" |
|
42 |
1 1 1 1 1 1 |
|
43 |
--------------
|
|
44 |
select "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" |
|
48 |
1 1 0 1 1 |
|
49 |
--------------
|
|
50 |
select "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)*$" |
|
54 |
1 1 |
|
55 |
--------------
|
|
56 |
select !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 |
|
60 |
1 1 0 1 0 1 1 1 |
|
61 |
--------------
|
|
62 |
select 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 |
||
65 |
IF(0,"ERROR","this") IF(1,"is","ERROR") IF(NULL,"ERROR","a") IF(1,2,3)|0 IF(1,2.0,3.0)+0 |
|
66 |
this is a 2 2.0 |
|
67 |
--------------
|
|
68 |
select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3 |
|
69 |
--------------
|
|
70 |
||
71 |
2 between 1 and 3 "monty" between "max" and "my" 2=2 and "monty" between "max" and "my" and 3=3 |
|
72 |
1 1 1 |
|
73 |
--------------
|
|
74 |
select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0) |
|
75 |
--------------
|
|
76 |
||
77 |
2 in (3,2,5,9,5,1) "monty" in ("david","monty","allan") 1.2 in (1.4,1.2,1.0) |
|
78 |
1 1 1 |
|
79 |
--------------
|
|
80 |
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo' |
|
81 |
--------------
|
|
82 |
||
83 |
hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo |
|
84 |
hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo |
|
85 |
--------------
|
|
86 |
select concat("monty"," was here ","again"),length("hello"),ascii("hello") |
|
87 |
--------------
|
|
88 |
||
89 |
concat("monty"," was here ","again") length("hello") ascii("hello") |
|
90 |
monty was here again 5 104
|
|
91 |
--------------
|
|
92 |
select locate("he","hello"),locate("he","hello",2),locate("lo","hello",2) |
|
93 |
--------------
|
|
94 |
||
95 |
locate("he","hello") locate("he","hello",2) locate("lo","hello",2) |
|
96 |
1 0 4
|
|
97 |
--------------
|
|
98 |
select left("hello",2),right("hello",2),substring("hello",2,2),mid("hello",1,5) |
|
99 |
--------------
|
|
100 |
||
101 |
left("hello",2) right("hello",2) substring("hello",2,2) mid("hello",1,5) |
|
102 |
he lo el hello
|
|
103 |
--------------
|
|
104 |
select concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1)) |
|
105 |
--------------
|
|
106 |
||
107 |
concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1)) |
|
108 |
happy
|
|
109 |
--------------
|
|
110 |
select concat("!",ltrim(" left "),"!",rtrim(" right "),"!") |
|
111 |
--------------
|
|
112 |
||
113 |
concat("!",ltrim(" left "),"!",rtrim(" right "),"!") |
|
114 |
!left ! right!
|
|
115 |
--------------
|
|
116 |
select insert("txs",2,1,"hi"),insert("is ",4,0,"a"),insert("txxxxt",2,4,"es") |
|
117 |
--------------
|
|
118 |
||
119 |
insert("txs",2,1,"hi") insert("is ",4,0,"a") insert("txxxxt",2,4,"es") |
|
120 |
this is a test
|
|
121 |
--------------
|
|
122 |
select replace("aaaa","a","b"),replace("aaaa","aa","b"),replace("aaaa","a","bb"),replace("aaaa","","b"),replace("bbbb","a","c") |
|
123 |
--------------
|
|
124 |
||
125 |
replace("aaaa","a","b") replace("aaaa","aa","b") replace("aaaa","a","bb") replace("aaaa","","b") replace("bbbb","a","c") |
|
126 |
bbbb bb bbbbbbbb aaaa bbbb
|
|
127 |
--------------
|
|
128 |
select replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL") |
|
129 |
--------------
|
|
130 |
||
131 |
replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL") |
|
132 |
this is a REAL test
|
|
133 |
--------------
|
|
134 |
select soundex(""),soundex("he"),soundex("hello all folks") |
|
135 |
--------------
|
|
136 |
||
137 |
soundex("") soundex("he") soundex("hello all folks") |
|
138 |
H000 H4142
|
|
139 |
--------------
|
|
140 |
select password("test") |
|
141 |
--------------
|
|
142 |
||
143 |
password("test") |
|
144 |
378b243e220ca493
|
|
145 |
--------------
|
|
146 |
select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0
|
|
147 |
--------------
|
|
148 |
||
149 |
0x41 0x41+0 0x41 | 0x7fffffffffffffff | 0 0xffffffffffffffff | 0
|
|
150 |
A 65 9223372036854775807 -1
|
|
151 |
--------------
|
|
152 |
select 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 |
||
155 |
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 |
|
156 |
5 2 4 0 TWO 0 1 1.1
|
|
157 |
--------------
|
|
158 |
select 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 |
||
161 |
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) |
|
162 |
2 123.6 1,234.56 12,345.556 123,456.5555 1,234,567.55550 12,345.24
|
|
163 |
--------------
|
|
164 |
select database(),user()
|
|
165 |
--------------
|
|
166 |
||
167 |
database() user()
|
|
168 |
monty
|
|
169 |
--------------
|
|
170 |
select 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 |
||
173 |
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 |
|
174 |
NULL 1 1 1 1 TRUE TRUE 1 1
|
|
175 |
--------------
|
|
176 |
select 1 | NULL,1 & NULL,1+NULL,1-NULL
|
|
177 |
--------------
|
|
178 |
||
179 |
1 | NULL 1 & NULL 1+NULL 1-NULL
|
|
180 |
NULL NULL NULL NULL
|
|
181 |
--------------
|
|
182 |
select NULL=NULL,NULL<>NULL,NULL IS NULL, NULL IS NOT NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0
|
|
183 |
--------------
|
|
184 |
||
185 |
NULL=NULL NULL<>NULL NULL IS NULL NULL IS NOT NULL IFNULL(NULL,1.1)+0 IFNULL(NULL,1) | 0
|
|
186 |
NULL NULL 1 0 1.1 1
|
|
187 |
--------------
|
|
188 |
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null |
|
189 |
--------------
|
|
190 |
||
191 |
strcmp("a",NULL) (1<NULL)+0.0 NULL regexp "a" null like "a%" "a%" like null |
|
192 |
NULL NULL NULL NULL NULL
|
|
193 |
--------------
|
|
194 |
select 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 |
||
197 |
concat("a",NULL) replace(NULL,"a","b") replace("string","i",NULL) replace("string",NULL,"i") insert("abc",1,1,NULL) left(NULL,1) |
|
198 |
NULL NULL NULL NULL NULL NULL
|
|
199 |
--------------
|
|
200 |
select field(NULL,"a","b","c") |
|
201 |
--------------
|
|
202 |
||
203 |
field(NULL,"a","b","c") |
|
204 |
0
|
|
205 |
--------------
|
|
206 |
select 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 |
||
209 |
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
|
|
210 |
0 0 NULL NULL NULL 0 0
|
|
211 |
--------------
|
|
212 |
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2) |
|
213 |
--------------
|
|
214 |
||
215 |
insert("aa",100,1,"b") insert("aa",1,3,"b") left("aa",-1) substring("a",1,2) |
|
216 |
aa b a
|
|
217 |
--------------
|
|
218 |
select elt(2,1),field(NULL,"a","b","c") |
|
219 |
--------------
|
|
220 |
||
221 |
elt(2,1) field(NULL,"a","b","c") |
|
222 |
NULL 0
|
|
223 |
--------------
|
|
224 |
select locate("a","b",2),locate("","a",1),ltrim("a"),rtrim("a") |
|
225 |
--------------
|
|
226 |
||
227 |
locate("a","b",2) locate("","a",1) ltrim("a") rtrim("a") |
|
228 |
0 1 a a
|
|
229 |
--------------
|
|
230 |
select concat("1","2")|0,concat("1",".5")+0.0 |
|
231 |
--------------
|
|
232 |
||
233 |
concat("1","2")|0 concat("1",".5")+0.0 |
|
234 |
12 1.5
|
|
235 |
--------------
|
|
236 |
select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(curdate()+1)-to_days(curdate()),weekday("1997-01-01") |
|
237 |
--------------
|
|
238 |
||
239 |
from_days(to_days("960101")) to_days(960201)-to_days("19960101") to_days(curdate()+1)-to_days(curdate()) weekday("1997-01-01") |
|
240 |
1996-01-01 31 1 2
|
|
241 |
--------------
|
|
242 |
select period_add("9602",-12),period_diff(199505,"9404") |
|
243 |
--------------
|
|
244 |
||
245 |
period_add("9602",-12) period_diff(199505,"9404") |
|
246 |
199502 13 |
|
247 |
--------------
|
|
248 |
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()) |
|
249 |
--------------
|
|
250 |
||
251 |
now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now()) |
|
252 |
0 0 0 |
|
253 |
--------------
|
|
254 |
select now(),now()+0,curdate(),weekday(curdate()),weekday(now()),unix_timestamp(),unix_timestamp(now()) |
|
255 |
--------------
|
|
256 |
||
257 |
now() now()+0 curdate() weekday(curdate()) weekday(now()) unix_timestamp() unix_timestamp(now()) |
|
258 |
1998-08-17 04:24:33 19980817042433 1998-08-17 0 0 903317073 903317073 |