首页 > 代码库 > MySql

MySql

PHP等程序设计语言一样,MySQL等许多DBMS都有自己的数据类型(即列类型),运算符,语句结构,关键字,以及函数。其中广泛用于SELECTWHERE子句中的函数,对程序员简化查询语句的构造,提高查询计算的效率,起着非常重要的作用。这些函数将一些复杂的查询计算操作用函数封装起来,由MySQL自己执行计算,仅将结果返回给PHP
这些函数,涵盖了数学运算,字符串处理,逻辑运算,日期时间运算,比较运算,语句流程控制,分组汇总,排序等许多方面的处理,下面介绍常用到的一些函数。
为简便起见,用->表示执行查询后MySQL返回的结果。
1)强制运算()
括号。使用它们来强制在一个表达式的计算顺序。
mysql> select1+2*3;
        -> 7
mysql> select(1+2)*3;
        -> 9
2)算术运算
加法
mysql> select3+5;
        -> 8
以此类推:
减法
乘法
除法
被零除产生一个NULL结果:
mysql> select102/(1-1);
        -> NULL
3)逻辑运算
所有的逻辑函数返回1TRUE)或0FALSE)。NULL被认为是假值。
NOT !)逻辑非
OR ||)逻辑或
AND&&)逻辑与
4)比较运算符
比较操作得出值1TRUE)、0FALSE)或NULL等结果。这些函数工作运用在数字和字符串上。
= 等于
<>不等于
!= 不等于
<= 小于或等于
< 小于
>= 大于或等于
> 大于
mysql> select 2 > 2;
        -> 0
IS NULL 是否为空
IS NOT NULL 是否不为空
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
        -> 00 1
expr BETWEEN min AND max
如果expr对大于或等于minexpr是小于或等于maxBETWEEN返回1,否则它返回0
mysql> select 1 BETWEEN 2 AND 3;
        -> 0
mysql> select 2 BETWEEN 2 AND ‘3‘;
        -> 1
expr IN (value,...)
如果expr是在IN表中的任何值,返回1,否则返回0。如果所有的值是常数,那么所有的值根据expr类型被计算和排序,然后项目的搜索是用二进制的搜索完成。这意味着如果IN值表全部由常数组成,IN是很快的。如果expr是一个大小写敏感的字符串表达式,字符串比较以大小写敏感方式执行。
mysql> select 2 IN(0,3,5,‘wefwf‘);
        -> 0
mysql> select ‘wefwf‘ IN (0,3,5,‘wefwf‘);
        -> 1
expr NOT IN(value,...)
NOT (expr IN (value,...))相同。

ISNULL(expr)
如果exprNULLISNULL()返回1,否则它返回0
mysql> select ISNULL(1+1);
        -> 0
mysql> select ISNULL(1/0);
        -> 1

注意,使用=NULL的值比较总为假!

5)字符串比较函数
通常,如果在字符串比较中的任何表达式是区分大小写的,比较以大小写敏感的方式执行。
expr LIKE pat tern[ESCAPE ‘escape-char‘]
expr与模式字符串pattern进行模式匹配。返回1TRUE)或0FALSE)。用LIKE,你可以在模式中使用下列2%  匹配任何数目的字符,甚至零个字符
_  精确匹配一个字符
mysql> select ‘David!‘ LIKE ‘David_‘;
        -> 1
mysql> select ‘David!‘ LIKE ‘%D%v%‘;
        -> 1

为了测试一个通配符的文字实例,用转义字符的加在字符前面。如果你不指定ESCAPE字符,假定为“\”:

\%  匹配%字符
\_  匹配_字符

mysql> select ‘David!‘ LIKE ‘David\_‘;
        -> 0
mysql> select ‘David_‘ LIKE ‘David\_‘;
        -> 1

为了指定一个不同的转义字符,使用ESCAPE子句:
mysql> select ‘David_‘ LIKE ‘David|_‘ ESCAPE ‘|‘;
        -> 1

LIKE允许用在数字的表达式上!(这是MySQLANSI SQL LIKE的一个扩充。)

mysql> select 10 LIKE ‘1%‘;
        -> 1

expr NOT LIKEpattern [ESCAPE ‘escape-char‘]
NOT (expr LIKE pattern[ESCAPE‘escape-char‘])相同。
6)控制流函数
IF(expr1,expr2,expr3)
如果expr1TRUE(expr1<>0expr1<>NULL),那么IF()返回expr2,否则它返回expr3IF()返回一个数字或字符串值,取决于它被使用的上下文。
mysql> select IF(1>2,2,3);
        -> 3
mysql> select IF(1<2,‘yes‘,‘no‘);
        ->‘yes‘
mysql> selectIF(strcmp(‘test‘,‘test1‘),‘yes‘,‘no‘);
        ->‘no‘
expr1作为整数值被计算,它意味着如果你正在测试浮点或字符串值,应该使用一个比较操作来做。
mysql> select IF(0.1,1,0);
        -> 0
mysql> select IF(0.1<>0,1,0);
        -> 1
在上面的第一种情况中,IF(0.1)返回0,因为0.1被变换到整数值导致测试IF(0)。这可能不是你期望的。在第二种情况中,比较测试原来的浮点值看它是否是非零,比较的结果被用作一个整数。
CASE value WHEN [compare-value] THEN result [WHEN[compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition]THEN result ...] [ELSE result] END
第一个版本返回result,其中value=http://www.mamicode.com/compare-value。第二个版本中如果第一个条件为真,返回result。如果没有匹配的result值,那么结果在ELSE后的result被返回。如果没有ELSE部分,那么NULL被返回。
mysql> SELECT CASE 1 WHEN 1 THEN "one"WHEN 2 THEN "two" ELSE "more" END;
       ->"one"
mysql> SELECT CASE WHEN 1>0 THEN"true" ELSE "false" END;
       ->"true"
mysql> SELECT CASE BINARY "B" when"a" then 1 when "b" then 2 END;
       ->NULL
7)数学函数
所有的数学函数在一个出错的情况下返回NULL
ABS(X)
返回X的绝对值。
SIGN(X)
返回参数的符号,为-101,取决于X是否是负数、零或正数。
MOD(N,M)
%
 (类似C中的%操作符)。返回NM除的余数。
mysql> select MOD(29,9);
        -> 2
FLOOR(X)
X向下取整。
mysql> select FLOOR(1.23);
        -> 1
mysql> select FLOOR(-1.23);
        ->-2
CEILING(X)
X向上取整。
mysql> select CEILING(1.23);
        -> 2
mysql> select CEILING(-1.23);
        ->-1
ROUND(X)
返回参数X的四舍五入的一个整数。
mysql> select ROUND(-1.23);
        ->-1
mysql> select ROUND(-1.58);
        ->-2
mysql> select ROUND(1.58);
        -> 2
ROUND(X,D)
返回参数X的四舍五入的有D位小数的一个数字。如果D0,结果将没有小数点或小数部分。
mysql> select ROUND(1.298, 1);
        ->1.3
mysql> select ROUND(1.298, 0);
        -> 1
RAND()
RAND(N)
返回在范围01.0内的随机浮点值。如果一个整数参数N被指定,它被用作种子值。
mysql> select RAND();
        ->0.5925
mysql> select RAND(20);
        ->0.1811
mysql> select RAND(20);
        ->0.1811
mysql> select RAND();
        ->0.2079
mysql> select RAND();
        ->0.7888
你不能在一个ORDER BY子句用RAND()值使用列,因为ORDER BY将重复计算列多次。然而在MySQL3.23中,你可以做: SELECT * FROMtable_name ORDER BY RAND(),这是有利于得到一个来自SELECT * FROM table1,table2 WHERE a=b ANDc的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。
LEAST(X,Y,...)
22个以上的参数,返回最小(最小值)的参数。参数使用下列规则进行比较:
如果返回值被使用在一个INT上下文,或所有的参数都是整数值,他们作为整数比较。
如果返回值被使用在一个FLOAT上下文,或所有的参数是实数值,他们作为实数比较。
如果任何参数是一个大小敏感的字符串,参数作为大小写敏感的字符串被比较。
在其他的情况下,参数作为大小写无关的字符串被比较。
mysql> select LEAST(2,0);
        -> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
        ->3.0
mysql> selectLEAST("B","A","C");
        ->"A"
MySQL 3.22.5以前的版本,你可以使用MIN()而不是LEAST
GREATEST(X,Y,...)
返回最大(最大值)的参数。参数使用与LEAST一样的规则进行比较。
mysql> select GREATEST(2,0);
        -> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
        ->767.0
mysql> selectGREATEST("B","A","C");
        ->"C"
MySQL 3.22.5以前的版本你能使用MAX()而不是GREATEST.  
8)字符串函数
对于针对字符串位置的操作,第一个位置被标记为1
MySQL根据上下文自动变换数字为字符串,并且反过来也如此:
mysql> SELECT 1+"1";
        -> 2
CONCAT(str1,str2,...)
返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
mysql> select CONCAT(‘My‘, ‘S‘, ‘QL‘);
        ->‘MySQL‘
mysql> select CONCAT(‘My‘, NULL, ‘QL‘);
        ->NULL
mysql> select CONCAT(14.3);
        ->‘14.3‘
mysql> SELECT CONCAT(2,‘ test‘);
        ->‘2 test‘

LEFT(str,len)
返回字符串str的最左面len个字符。
mysql> select LEFT(‘foobarbar‘, 5);
        ->‘fooba‘
RIGHT(str,len)
返回字符串str的最右面len个字符。
mysql> select RIGHT(‘foobarbar‘, 4);
        ->‘rbar‘
SUBSTRING(str,pos,len)
MID(str,pos,len)
从字符串str返回一个len个字符的子串,从位置pos开始。
mysql> select SUBSTRING(‘123456789123456‘,5,6);
        ->‘567891‘
LTRIM(str)
返回删除了其前置空格的字符串str
mysql> select LTRIM(‘  barbar‘);
        -> ‘barbar‘
RTRIM(str)
返回删除了其尾部空格的字符串str
mysql> select RTRIM(‘barbar   ‘);
        ->‘barbar‘
TRIM(str)
返回去处了首尾空格的字符串str
mysql> select TRIM(‘  bar  ‘);
        ->‘bar‘
SPACE(N)
返回由N个空格字符组成的一个字符串。
mysql> select SPACE(6);
        ->‘      ‘
REPEAT(str,count)
返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果strcountNULL,返回NULL
mysql> select REPEAT(‘MySQL‘, 3);
        ->‘MySQLMySQLMySQL‘
LCASE(str)
LOWER(str)
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1Latin1)把所有的字符改变成小写。
mysql> select LCASE(‘ABCDEFG‘);
        ->‘abcdefg‘
UCASE(str)
UPPER(str)
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1Latin1)把所有的字符改变成大写。
mysql> select UCASE(‘ abcdefg ‘);
        -> ‘ABCDEFG ‘

LOAD_FILE(file_name)
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出,函数返回NULL
mysql> UPDATE table_name
           SETblob_column=LOAD_FILE("/tmp/picture")
          WHERE id=1;
9)日期和时间函数
NOW()
SYSDATE()
CURRENT_TIMESTAMP
‘YYYY-MM-DD HH:MM:SS‘YYYYMMDDHHMMSS格式返回当前的日期和时间,取决于函数是在一个字符串还是在数字的上下文被使用。
mysql> select NOW();
        ->‘1997-12-15 23:50:26‘
mysql> select NOW() + 0;
        ->19971215235026
CURDATE()
CURRENT_DATE
‘YYYY-MM-DD‘YYYYMMDD格式返回今天日期值,取决于函数是在一个字符串还是数字上下文被使用。
mysql> select CURDATE();
        -> ‘1997-12-15‘
mysql> select CURDATE() + 0;
        ->19971215
CURTIME()
CURRENT_TIME
‘HH:MM:SS‘HHMMSS格式返回当前时间值,取决于函数是在一个字符串还是在数字的上下文被使用。
mysql> select CURTIME();
        ->‘23:50:26‘
mysql> select CURTIME() + 0;
        ->235026
        ->10
10)分组计算函数
这些函数,常常是与GROUP BY子句一起使用的函数,作用是对聚合在组内的行,进行计算。
如果在不包含GROUP BY子句的一个语句中使用聚合函数,它等价于聚合所有行。
COUNT(expr)
返回由一个SELECT语句检索出来的行的非NULL值的数目。
mysql> select student.student_name,COUNT(*)
           fromstudent,course
          where student.student_id=course.student_id
          GROUP BY student_name;
COUNT(*)在它返回的检索出来的行数目上有些不同,不管他们是否包含NULL值。如果SELECT从一个表检索,或没有检索出其他列并且没有WHERE子句,COUNT(*)被优化以便快速地返回。例如:
mysql> select COUNT(*) from student;
COUNT(DISTINCT expr,[expr...])
返回一个无重复值的数目。
mysql> selectCOUNT(DISTINCT results) from student;
MySQL中,你可以通过给出一个表达式列表以得到不同的表达式组合的数目。
AVG(expr)
返回expr的平均值。
mysql> select student_name, AVG(test_score)
           fromstudent
          GROUP BY student_name;
MIN(expr)
MAX(expr)
返回expr的最小或最大值。MIN()MAX()可以有一个字符串参数;在这种的情况下,他们返回最小或最大的字符串值。
mysql> select student_name, MIN(test_score),MAX(test_score)
           fromstudent
          GROUP BY student_name;
SUM(expr)
返回expr的和。注意,如果返回的集合没有行,它返回NULL

MySql