-- 使用testdemo數(shù)據(jù)庫
use testdemo;
select * from access_log;
-- GROUP BY函數(shù)的使用
查詢個(gè)網(wǎng)站的瀏覽量
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函數(shù)一起使用
SELECT site_id,SUM(count) as nums from access_log GROUP BY(site_id) where site_id BETWEEN 1 and 5;
-- HAVING用于篩選分組后的結(jié)果
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;
-- 將網(wǎng)站瀏覽量在200以上的進(jìn)行排名
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;
-- 大小寫轉(zhuǎn)化函數(shù)
SELECT UCASE(name) from websites;
SELECT LCASE(name) from websites;
-- 字符提取函數(shù)
SELECT MID(name,1,3) from websites;
-- 獲取字段長(zhǎng)度函數(shù)
SELECT LENGTH(name) FROM websites;
-- 四舍五入函數(shù)
SELECT ROUND(12.5);
SELECT ROUND(12.123,2);
-- 日期函數(shù)
SELECT NOW();
-- 格式化函數(shù)
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
