image.png

image.png

mysql> select concat('imooc','musql');

image.png

mysql> select concat(first_name,last_name) as fullname from test;
mysql> select concat_ws('|',first_name,last_name) as fullname from test;
mysql> select format(12560.7533,2);//保留两位小数
mysql> select upper('mysql');//转换大写
mysql> select lower('MYSQL');//转换小写
mysql> select left('mysql',2);//取‘my’
mysql> select lower(left('MYSQL',2));//函数嵌套，取‘MY’并转换成小写

image.png

mysql> select length('mysql');//长度为5
mysql> select trim(leading'?'from'??mysql???');//删除前导的？，变成了mysql???
mysql> select trim(trailing'?'from'??mysql???');//变成??mysql
mysql> select trim(both'?'from'??mysql???');//变成mysql
mysql> select replace('??my??sql???','?','');//字符替换，将？去掉_(:з」∠)_
mysql> select substring('mysql',1,2);//字符串截取，需要注意的是，在mysql中，字符串以1开始
mysql> select substring('mysql',3);//从第三位截取到末尾就是sql
mysql> select substring('mysql',-2);//意思是倒数第二位取到结尾就是ql

image.png

mysql> select*from test where first_name like'%\%%';//也可以实现上图第三种的效果

%代表任意个字符
_代表任意一个字符

image.png

mysql> select ceil(3.01);//4就是无视小数点进位

mysql> select floor(3.01);//3就是无视小数点

mysql> select 3 div 4//0

mysql> select 5%3;//2

mysql> select power(3,3);//27三的三次方

mysql> select round(3.652,2);//3.65四舍五入

mysql> select truncate(125.89,2);//125.89截取

mysql> select truncate(125.89,0);//125

mysql> select truncate(125.89,-1);//120

image.png

mysql> select 15 between 1 and 22;//1
mysql> select 35 not between 1 and 22;//1
mysql> select 10 in(5,10,15,20);//1
mysql> select*from test where first_name is not null;//会列出符合条件的表

image.png

mysql> select now();
mysql> select curdate();
mysql> select curtime();
mysql> select date_add('2014-3-15',interval 365 day);//2015-03-15加加减减，注意后面单位
mysql> select date_add('2014-3-15',interval 1 week);//2014-03-22
mysql> select date_format('2014-3-12','%m/%d/%Y');//03/12/2014换格式，注意大写Y

image.png

mysql> select connection_id();
mysql> select database();
mysql> select last_insert_id();//若同时插入两条，则只显示前一条的id
mysql> select user();
mysql> select version();

image.png

mysql> select avg(goods_price)as avg_price from tdb_goods;//只能计算表中某数据的avg其他同理
mysql> select round(avg(goods_price),2) as avg_price from tdb_goods;//能和数值运算符函数一起用
mysql> select count(goods_id)as counts from tdb_goods;//计数
mysql> select max(goods_price)as counts from tdb_goods;
mysql> select min(goods_price)as counts from tdb_goods;
mysql> select sum(goods_price)as counts from tdb_goods;

image.png