文档介绍：MySQL运算符(1)
分组运算符
即括号,强制改变运算顺序
例: Mysql>select 1+1*3,(1+1)*3;
算术运算符(只适用于数字运算)
+、-、*、/、%
例: Mysql>select -,”21de”+”32”,10/2,10/3,5%3,5%(1-1);
Mysql>use bc_db
Mysql>select p_name,price,price* from product;
注意:
字符串参加算术运算时,将开始字符尽可能转化为数值,若开头不是数字,转化为0.
%运算符只用于两个整数相除后取余数,若浮点数四舍五入后再操作,在作“/”和“%”运算时,若第二个数字为0,返回NULL
MySQL运算符(2)
比较运算符(用于数字和字符串的比较)
=、<=>、<>、!=、<、>、<=、>=
IS NULL、IS NOT NULL、
expr BETWEEN min AND max
expr IN(value1,value2…)
expr NOT IN (value1,value2…)
结果为1(TRUE)、0(FALSE)或NULL
例: Mysql>select 1=0,NULL=NULL,NULL<=>NULL,”abc”<“abd”,2 is null;
Mysql>select 1 between 2 and 3,”b” between “a” and “c”;
Mysql>select 2 in(0,1,2,”abc”),”acb” not in (0,1,2,”abc”);
Mysql>use bc_db;
Mysql>select * from product where price between and ;
Mysql>select p_name from product where place is null;
Mysql>select p_name from product where place not in(“tj”,”bj”);
两个都是字符串,且不分大小写
两个都是数值(一整数,一浮点数)
一个字符串,一个数值
带有NULL时如何处理
=与< = >的区别(安全的等号)
MySQL运算符(3)
位操作运算符(对二进制进行运算)
|、&、~、<<、>>
例: Mysql>select 0xa3|0x21,bin(0xa3|0x21),0xa3&0x21,bin(0xa3&0x21);
其中0xa3为10100011,0x21为00100001
结果为: 163 10100011 33 100001
Mysql>select ~0x21,bin(7<<2);
Mysql>select bin(0xa3&0x0f); //取低4位
MySQL运算符(4)
逻辑运算符(只用于数值参数,字符串转换为数字)
非0为真,用1表示;0为假,用0表示; 注意null的处理
NOT(!)、OR(||)、AND(&&)
例: Mysql>select !”abc”,!”1abc”,1||0,1&&2,”abc”&&”dfg”;
Mysql>use bc_db
Mysql>select * from product where price< or price>10;
Mysql>select * from product where not place=“tj”;
注意:
NOT NULL结果为NULL
非零数||NULL为1, 而0||NULL为NULL
任何数&&NULL为NULL
MySQL运算符(5)
模式匹配运算符
用LIKE(不区分大小写):%匹配任意个字符,_匹配单个字符
例: Mysql>select “asdf” like “as%”, “asdf” like “as_”;
Mysql>select * from user where name like “j%” and birthday like “%-02-%”;
用REGEXP匹配(正则表达式,区分大小写)
str REGEXP pattern 或 str NOT REGEXP pattern
例: Mysql>select “asdf” regexp “df$”, “asdf” regexp “^as”;
Mysql>select “asdf” regexp “as[c-f][c-f]”;
Mysql>select * from product where p_name regexp “^pen”;
MySQL运算符(6)
二进制操作符(BINARY, 强制数字、字符串作二进制处理)
例: Mysql>select “