SQL中的函数

-- 使用testdemo数据库

use testdemo;

select * from access_log;

-- GROUP BY函数的使用

查询个网站的浏览量

SELECT site_id,SUM(count) as nums from access_log GROUP BY(site_id);

SELECT websites.`name`,access_log.site_id,SUM(access_log.count) as nums from websites,access_log

where websites.id = access_log.site_id GROUP BY(site_id);

select websites.name,access_log.site_id,SUM(access_log.count) as nums from websites

RIGHT JOIN access_log on websites.id = access_log.site_id GROUP BY(access_log.site_id);

-- where不能与group by函数一起使用

SELECT site_id,SUM(count) as nums from access_log GROUP BY(site_id) where site_id BETWEEN 1 and 5;

-- HAVING用于筛选分组后的结果

SELECT site_id,SUM(count) as nums from access_log GROUP BY(site_id) HAVING site_id BETWEEN 1 and 5;

SELECT site_id,SUM(count) as nums from access_log GROUP BY(site_id) HAVING nums >200;

-- 将网站浏览量在200以上的进行排名

SELECT websites.name, SUM(access_log.count) as rank from websites

INNER JOIN access_log on websites.id = access_log.site_id GROUP BY(access_log.site_id)

HAVING rank>200 ORDER BY rank DESC;

-- 大小写转化函数

SELECT UCASE(name) from websites;

SELECT LCASE(name) from websites;

-- 字符提取函数

SELECT MID(name,1,3) from websites;

-- 获取字段长度函数

SELECT LENGTH(name) FROM websites;

-- 四舍五入函数

SELECT ROUND(12.5);

SELECT ROUND(12.123,2);

-- 日期函数

SELECT NOW();

-- 格式化函数

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');