1 将username字段的截取两个字符,其中将包含为1的字符替换为q
SELECT REPLACE(SUBSTRING(username,1,2),'1','q') FROM guanliyuan;
2 将日期时间转换为字符串
SELECT DATE_FORMAT('2009-10-11 22:12:12','%Y%m%d%H%i%s');
3 从日期中截取年份
SELECT SUBSTRING(DATE_FORMAT('2009-10-11 22:12:12','%Y%m%d%H%i%s'),1,4) FROM DUAL;
4 显示现在的时间的年月日
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y') FROM DUAL;
5 将字符串变为日期
SELECT STR_TO_DATE('20140911153704','%Y%m%d%H%i%s');
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s');
6 将字符串变为数字
SELECT CAST('11' AS UNSIGNED INT);
SELECT CONVERT('12',SIGNED);
SELECT '123.6'+0;
7 将用户名与分数两个字段连接成一句话
SELECT CONCAT(CONCAT(username,'\'s grade is '),grade) FROM guanliyuan;
8 RPAD(str,len,padstr)返回字符串str,右面用字符串padstr填补直到str是len个字符长。如果str比len长,则返回str的前n个字符
SELECT id,RPAD(username,11,'qwert') FROM guanliyuan WHERE id = 17;
SELECT id,RPAD(username,1,'qwert') FROM guanliyuan WHERE id = 17;
9 LPAD(str,len,padstr)返回字符串str,左面用字符串padstr填补直到str是len个字符长。如果str比len长,则返回str的前n个字符
SELECT id,LPAD(username,11,'qwert') FROM guanliyuan WHERE id = 17;