home *** CD-ROM | disk | FTP | other *** search
/ Chip 2001 January / Chip_2001-01_cd1.bin / tema / mysql / mysql-3.23.28g-win.exe / DATA1.CAB / Examples / examples / tests / function.res < prev    next >
Text File  |  2000-11-22  |  9KB  |  259 lines
  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)),exp(log(sqrt(10))*2),pow(10,log10(10)),rand(999999),rand()
  15. --------------
  16.  
  17. abs(-10)    log(exp(10))    exp(log(sqrt(10))*2)    pow(10,log10(10))    rand(999999)    rand()
  18. 10    10.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
  259.