mysql 51cto 函数_MySQL常用函数

1. 得到需要查询字符的ASCII码

SELECT ASCII('中');

SELECT CHAR(97);

2.根据字符集查询得到字符串的长度

SELECT CHAR_LENGTH("中国");

SELECT CHAR_LENGTH(sname) FROM student;

3.utf8编码下，一个中文字占3个字符长度

SELECT LENGTH("中");

4.拼接字符串

SELECT CONCAT('My', 'S', 'QL');

SELECT CONCAT(sname,sage) FROM student;

SELECT sname,sage FROM student;

5.大写转小写

SELECT LOWER("ABC");

6.写转大写

SELECT UPPER("abc");

7.查询学生表中所有学生姓名的最后一个字

SELECT RIGHT(sname,1) FROM student;

8.查询学生表中所有学生姓什么

SELECT LEFT(sname,1) from student;

9.向下取整

SELECT FLOOR(4.9);

10.向上取整

SELECT CEIL(4.9);

11.查询得到本地时间

SELECT NOW();

CREATE TABLE teset(

tid int PRIMARY KEY auto_increment,

ttime datetime

);

SELECT * FROM teset;

INSERT INTO teset(ttime) values (NOW());

SELECT CURDATE(),CURTIME();

SELECT CURTIME();

12.聚合函数:

COUNT  统计数量：select count(xsnianling) from stu;

SUM求和：select sum(xsnianling) fromstu;

MAX求最大值：select max(xsnianling) fromstu;

MIN求最小值：select min(xsnianling) fromstu;

AVG平均数：select avg(xsnianling) fromstu;