mysql 字符串运算符_MYSQL读书笔记---运算符、字符串操作

运算符###########################################

=,!=(<>),>,>=,

流程#############################################

mysql> select ifnull(1,0); #如果第一个参数为NULL，则返回第二个参数值，否则返回原值.

------------------------------------------------

mysql>select case 1 when then 'one' when 2 when 'two' else 'more' end;

------------------------------------------------

mysql>select if(1>0,'ok','no') #如果条件为真则返回OK，否则返回NO.

------------------------------------------------

mysql>select nullif(1,2) #如果exp1 = exp2则返回NULL，否则返回exp1

------------------------------------------------

字符串###########################################

mysql>select ASCII('a'); #返回字符串最左边那个字符的ASCII码值

-----------------------------------------------

mysql>select oct(12); #返回字符串表示的八进制形式.

-----------------------------------------------

mysql>select hex(255); #返回字符串的十六进制形式.

-----------------------------------------------

mysql>select char(3333342); #返回ASSCII码值对应的字符串

------------------------------------------------

mysql>select char_length('text') #返回字符串的个数

------------------------------------------------

mysql>select length(text) #返回字符串的字节数

________________________________________________

mysql>select concat('str1','str2','str3','str4','strn',) #将参数连接成字符串返回

-------------------------------------------------

mysql>select locate('字串','母串') #返回字串在母串中第一次出现的位置，如果不存在则返回0

-------------------------------------------------

mysql>select lpad('str',11,'.');

mysql>select rpad('str',11,'.'); #用字符串对str进行左边或右边填充直到他的长度达到11.

-------------------------------------------------

mysql> select left('strstrstr',5); #返回字符串最左边的N个字符. mysql>select right('strstrstr',5); #返回字符串最右边的N个字符.

-------------------------------------------------

mysql> select substring('xxxxxx',3,3); #从字符串的第三个字个位置起截取三个字符.

-------------------------------------------------

mysql> select ltrim(str);

mysql>select rtrim();

mysql> select trim(' str ');

mysql> select trim(both 'x' from 'xxx..xxx'); #去空格

-------------------------------------------------

mysql> select space(6); #返回门个空格

-------------------------------------------------

mysql> select repeat('str',3); #返回重复三次的字符串

-------------------------------------------------

mysql>select replace('www.tom.com','tom','sina'); #字符串替换

-------------------------------------------------

mysql>select reverse('abc'); #字符串颠倒

-------------------------------------------------

mysql>select lower('UPPER');

mysql>select upper('lower'); #转换大小写

--------------------------------------------------

mysql> update table set filed = load_file('/tmp/file'); where id=1; #读入文件

--------------------------------------------------

mysql> select abs(-1); #返回绝对值

--------------------------------------------------

mysql> select 4 div 2; #除

--------------------------------------------------

mysql> select 5 mod 2; #余

--------------------------------------------------

mysql> select floor(x) #返回不大于X的最大整数值

--------------------------------------------------

mysql> select ceiling(x) #返回大于X的最小整数

--------------------------------------------------

mysql> select round(2.23) #四舍五入

__________________________________________________

时间 mysql>select now(); select now() + 0; #返回时间

--------------------------------------------------

mysql>select curdate(); select curdate() + 0; #返回年月日

--------------------------------------------------

mysql>select curtime(); select curtime() + 0 #返回小时分秒

--------------------------------------------------

mysql>select database() #返回当前数据库的名子

--------------------------------------------------

mysql>select user();

select system_user();

select session_user(); #返回当前MYSQL用户名子

---------------------------------------------------

mysql>select password('abc'); #口令加密

---------------------------------------------------

删除所有表记录 #truncate table 表名;

#速度快于delete from table