1
by brian
clean slate |
1 |
# Test of functions |
2 |
# |
|
3 |
# mysql -v < this_file |
|
4 |
||
5 |
# |
|
6 |
# numerical functions |
|
7 |
# |
|
8 |
select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2,sign(-5) ; |
|
9 |
select floor(5.5),floor(-5.5),ceiling(5.5),ceiling(-5.5),round(5.5),round(-5.5); |
|
10 |
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(); |
|
11 |
select least(6,1.0,2.0),greatest(3,4,5,0) ; |
|
12 |
select 1 | (1+1),5 & 3,bit_count(7) ; |
|
13 |
# |
|
14 |
# test functions |
|
15 |
# |
|
16 |
select 0=0,1>0,1>=1,1<0,1<=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") ; |
|
17 |
select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b"; |
|
18 |
select "abc" like "a%", "abc" not like "%d%", "ab" like "a\%", "a%" like "a\%","abcd" like "a%b_%d"; |
|
19 |
select "Det här är svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$"; |
|
20 |
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; |
|
21 |
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 ; |
|
22 |
select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3; |
|
23 |
select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); |
|
24 |
||
25 |
# |
|
26 |
# string functions |
|
27 |
# |
|
28 |
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; |
|
29 |
select concat("monty"," was here ","again"),length("hello"),ascii("hello"); |
|
30 |
select locate("he","hello"),locate("he","hello",2),locate("lo","hello",2) ; |
|
31 |
select left("hello",2),right("hello",2),substring("hello",2,2),mid("hello",1,5) ; |
|
32 |
select concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1)) ; |
|
33 |
select concat("!",ltrim(" left "),"!",rtrim(" right "),"!"); |
|
34 |
select insert("txs",2,1,"hi"),insert("is ",4,0,"a"),insert("txxxxt",2,4,"es"); |
|
35 |
select replace("aaaa","a","b"),replace("aaaa","aa","b"),replace("aaaa","a","bb"),replace("aaaa","","b"),replace("bbbb","a","c"); |
|
36 |
select replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL") ; |
|
37 |
select soundex(""),soundex("he"),soundex("hello all folks"); |
|
38 |
select password("test"); |
|
39 |
# |
|
40 |
# varbinary as string and number |
|
41 |
# |
|
42 |
select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ; |
|
43 |
||
44 |
# |
|
45 |
# misc functions |
|
46 |
# |
|
47 |
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; |
|
48 |
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); |
|
49 |
||
50 |
# |
|
51 |
# system functions |
|
52 |
# |
|
53 |
select database(),user(); |
|
54 |
||
55 |
# |
|
56 |
# Null tests |
|
57 |
# |
|
58 |
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; |
|
59 |
select 1 | NULL,1 & NULL,1+NULL,1-NULL; |
|
60 |
select NULL=NULL,NULL<>NULL,NULL IS NULL, NULL IS NOT NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0; |
|
61 |
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null; |
|
62 |
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1); |
|
63 |
select field(NULL,"a","b","c"); |
|
64 |
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; |
|
65 |
# |
|
66 |
# Wrong or 'funny' use of functions. |
|
67 |
# |
|
68 |
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2); |
|
69 |
select elt(2,1),field(NULL,"a","b","c"); |
|
70 |
select locate("a","b",2),locate("","a",1),ltrim("a"),rtrim("a"); |
|
71 |
select concat("1","2")|0,concat("1",".5")+0.0; |
|
72 |
||
73 |
# |
|
74 |
# time functions |
|
75 |
# The last line should return new values for each test run |
|
76 |
# |
|
77 |
select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(curdate()+1)-to_days(curdate()),weekday("1997-01-01") ; |
|
78 |
select period_add("9602",-12),period_diff(199505,"9404") ; |
|
79 |
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); |
|
80 |
select now(),now()+0,curdate(),weekday(curdate()),weekday(now()),unix_timestamp(),unix_timestamp(now()); |