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
这个不是很懂(:з」∠)
