SQL(2)
来源:互联网 发布:仆の知らない母 编辑:程序博客网 时间:2024/06/06 09:24
SQL中一些常用的函数:
AVG函数:返回数值列的平均值。
例:select AVG(count)As countAverage from access_log;
Select site_id,count from access_log where count>(select AVG(count) from access_log);
COUNT():返回匹配指定条件的行数
COUNT(*)返回表中的记录数;
COUNT(DISTINCT column_name)from table_name;返回指定列不同值的记录数。
例:select count(count) as nums from access_log where site_id=3;
Select count(*) as nums from access_log;
Select count(distinct site_id) from access_log
选取表中name列的第一个记录的值:
Select name as firstSite from websites limit 1;
选取表中name列中最后一个记录的值:
Select name from websites
Order by id desc
MAX()返回指定列的最大值
Select MAX(alexa) AS max_alexa from websites;
MIN()返回指定列的最小值
Select MIN(alexa)AS min_alexa from websites;
SUM()返回数值列的总和
Select SUM(alexa) AS sum_alexa from websites;
GROUP BY语句:用于结合聚合函数,根据一个或多个列对结果集进行分组。
无法与where一起使用。
例:
Select site_id ,sum(access_log.count) AS sums
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 = wedsites.id
group by websites.name;
HAVING :where关键字无法与聚合函数一起使用,having子句让我们筛选分组后的各组数据;
Select websites.name sum(access_log.count) from access_log inner join websites on access_log.site_id = websites.id group by websites.name having sum(access_log.count)>200;
`
Select websites.name sum(access_log.count)
from access_log inner join websites on access_log.site_id = websites.id
where websites.alexa<200
group by websites.name
having sum(access_log.count)>200
UCASE()把字段的值转换为大写
Select UCASE(name) AS site_title from websites;
LCASE()把字段的值转换为小写
Select LCASE(name) AS site_title from websites;
MID()用于从文本字段中提取字符
Select MID(name,1,4) AS shortTitle
From wenbsites;
参数:name:要提取的字符字段 示例中name
Start:开始位置,起始值为1 1
Length:要返回的字符数,如果省略,返回剩余文本。 4
LEN()返回文本字段中值的长度。
Select name,LEN(url) as LengthOfURL from wensites;
ROUND()函数把数值字段舍入为指定的小数位数。
示例:
Select ROUND(-1.23); 结果-1
Select ROUND(-1.58); 结果-2
Select ROUND(1.58); 结果2
Select ROUND(1.298,1); 结果1.3
NOW()函数返回当前系统的日期和时间
示例:
select name,url,now() AS date from websites;
结果:
FORMAT()用于对字段的显示进行格式化
Select FORMAT(column_name,format) from teble_name;
Select name,url,DATE_FORMATE(NOW(),’%Y-%M-%D’) AS date
From websites
- sql 2
- SQL 2
- SQL--2
- SQL 2
- sql 2
- sql 2
- SQL(2)
- SQL(2)
- sql 2
- SQL(2)
- SQL(2)
- [SQL]提升SQL执行效率诀窍2-优化SQL语句
- PL/SQL学习笔记[2]-SQL语句
- sql语句-sql学习笔记2
- sql学习笔记(2)----------SQL语法
- SQL Server系列(2)--T-SQL语句
- SQL(2) -- SQL性能优化[未完成]
- SQL--2常用sql语句&实例
- C语言指针专题——常见问题解答合集
- 测试两个容器是否连通
- BZOJ 4532: [BeiJing2014 WinterCamp] 珠链
- 分区恢复之 $MFT 详解
- npm run webpack时报错,同时生成npm-debug.log文件
- SQL(2)
- 获取MP4视频文件的时长,宽度,长度
- Android进阶之路
- 《编程之美》学而思
- Android串口通信:串口读写实例
- Android混淆、优化包大小 记录
- JAVA企业面试题精选 Java SE 81-90
- 学习VB.net串口发送与接收(二)
- JavaScript HTML DOM