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) from stu;

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

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

AVG    平均数：select avg(xsnianling) from stu;