11.16 SQL学习代码

#11.16
SELECT*FROM access_log;
#从 "access_log" 表的 "count" 列获取平均值
select AVG(count) AS Countaverage FROM access_log;
#选择访问量高于平均访问量的 "site_id" 和 "count"
select site_id,count
FROM access_log
where count >(select AVG(count) AS Countaverage FROM access_log);
#计算 "access_log" 表中 "site_id"=3 的总访问量
SELECT count(count) as nums from access_log
where site_id = 3;
#计算 "access_log" 表中总记录数
SELECT count(*) AS nums from access_log;
#计算 "access_log" 表中不同 site_id 的记录数
SELECT count(distinct site_id) as nums from access_log;
#选取 "Websites" 表的 "name" 列中第一个记录的值
select name from websites
order by id ASC
limit 1;
#选取 "Websites" 表的 "name" 列中最后一个记录的值
select name from websites
order by id DESC
limit 1;
#从 "Websites" 表的 "alexa" 列获取最大值
select max(alexa) as nums from websites;
#从 "Websites" 表的 "alexa" 列获取最小值
select min(alexa) as nums from websites;
#查找 "access_log" 表的 "count" 字段的总数
select sum(count) as nums from access_log;
#统计 access_log 各个 site_id 的访问量
select site_id,sum(access_log.count)AS nums
from access_log
group by site_id;
#统计有记录的网站的记录数量
select websites.name,count(access_log.aid)AS nums
FROM access_log
LEFT JOIN websites
on access_log.site_id = websites.id
group by websites.id;
#查找总访问量大于 200 的网站
select websites.name,websites.url,SUM(access_log.count) AS nums 
from (access_log inner join websites on access_log.site_id = websites.id)
group by websites.name
having sum(count)>200;
#查找总访问量大于 200 的网站，并且 alexa 排名小于 200
SELECT websites.name,websites.url,websites.alexa,sum(access_log.count) AS nums
from (access_log inner join websites on access_log.site_id = websites.id)
where websites.alexa<200
group by websites.name
having sum(count)>200;
#查找总访问量(count 字段)大于 200 的网站是否存在
select websites.name,websites.url from websites
where exists
(select count from access_log where websites.id = access_log.site_id and count>200);
select websites.name,websites.url from websites
where not exists
(select count from access_log where websites.id = access_log.site_id and count>200);
#从 "Websites" 表中选取 "name" 和 "url" 列，并把 "name" 列的值转换为大写
select Ucase(name) as site_tile,url from websites;
#从 "Websites" 表中选取 "name" 和 "url" 列，并把 "name" 列的值转换为小写
select Lcase(name) as site_tile,url from websites;
#从 "Websites" 表的 "name" 列中提取前 4 个字符
select mid(name,1,4) as shorttitle from websites;
#从 "Websites" 表中选取 "name" 和 "url" 列中值的长度
select length(name),length(url) from websites;
#ROUND(X)： 返回参数X的四舍五入的一个整数。
SELECT ROUND(-1.23);
SELECT ROUND(-1.58);
SELECT ROUND(1.58);
#ROUND(X,D)返回参数X的四舍五入的有 D 位小数的一个数字
SELECT ROUND(1.298, 1);
SELECT ROUND(1.298, 0);
#从 "Websites" 表中选取 name，url，及当天日期
select name,url,NOW() as date
from websites;
#从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;