SQL 用于很多可用于计数和计算的内建函数。
select function(列) from 表
在Sql中,函数的基本类型是:
Aggregate函数(合计函数)
scalar函数
-----合计函数 :面向一系列的值,并返回一个单一的值
如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句,则这个 SELECT 必须使用 GROUP BY 语句!
平均值 avg(列名)
某列的行数 count(列名)
指定域中第一个记录的值 first(列名)
指定域中最后一个记录的值 last(列名)
某列最高值 max(列名)
某列最低值 min(列名)
返回某列总和 sum(列名)
scalar函数:面向单一值,并返回一个单一值。
函数 | 描述 |
---|---|
UCASE(c) | 将某个域转换为大写 |
LCASE(c) | 将某个域转换为小写 |
MID(c,start[,end]) | 从某个文本域提取字符 |
LEN(c) | 返回某个文本域的长度 |
INSTR(c,char) | 返回在某个文本域中指定字符的数值位置 |
LEFT(c,number_of_char) | 返回某个被请求的文本域的左侧部分 |
RIGHT(c,number_of_char) | 返回某个被请求的文本域的右侧部分 |
ROUND(c,decimals) | 对某个数值域进行指定小数位数的四舍五入 |
MOD(x,y) | 返回除法操作的余数 |
NOW() | 返回当前的系统日期 |
FORMAT(c,format) | 改变某个域的显示方式 |
DATEDIFF(d,date1,date2) | 用于执行日期计算 |
- avg() : NULL 不包括计算中
select avg(列名) from 表名 select avg(列名) as 别名 from 表名 SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
2.count() :(NULL 不计入)
select count(列名) from 表名 返回表中的记录数: select count(*) from 表名 返回指定列的不同值的数目 select count(distinct 列名) from 表名 SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter
3.First() 函数 :返回第一个记录的值 可使用ORDER BY 对记录进行排序
select first (列名) from 表名
4 last()函数 :返回最后一个记录的值 可使用ORDER BY 对记录进行排序
select last(列名) as 别名 from 表名
5.max():min() ,sum()
SELECT MAX(column_name) FROM table_name
SELECT min (column_name) FROM table_name
SELECT sum (column_name) FROM table_name
6.GROUP:GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name1, column_name2
7. having 子句:原因是:where 关键字无法与合计函数一起使用
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
group by 分组后,select选列的时候肯定有合计函数列。
8 ucase () 函数 大写 lcase() 函数 小写
select ucase(列名) from 表名
select lcase(列名) from 表名
9.mid() 用于从文本字段中提取字符。
SELECT MID(column_name,start[,length]) FROM table_name
10 len ():返回文本字段中值的长度。
SELECT LEN(column_name) FROM table_name
11 round() : 用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name
12.now () :返回当前的日期和时间
SELECT NOW() FROM table_name
13 format() FORMAT 函数用于对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name
14 快速参考:
语句 | 语法 |
---|---|
AND / OR | SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
ALTER TABLE (add column) | ALTER TABLE table_name ADD column_name datatype |
ALTER TABLE (drop column) | ALTER TABLE table_name DROP COLUMN column_name |
AS (alias for column) | SELECT column_name AS column_alias FROM table_name |
AS (alias for table) | SELECT column_name FROM table_name AS table_alias |
BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
CREATE DATABASE | CREATE DATABASE database_name |
CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) |
CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) |
CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
DELETE FROM | DELETE FROM table_name (Note: Deletes the entire table!!) or DELETE FROM table_name |
DROP DATABASE | DROP DATABASE database_name |
DROP INDEX | DROP INDEX table_name.index_name |
DROP TABLE | DROP TABLE table_name |
GROUP BY | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 |
HAVING | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value |
IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
INSERT INTO | INSERT INTO table_name VALUES (value1, value2,....) or INSERT INTO table_name |
LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
SELECT | SELECT column_name(s) FROM table_name |
SELECT * | SELECT * FROM table_name |
SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
SELECT INTO (used to create backup copies of tables) | SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) |
TRUNCATE TABLE (deletes only the data inside the table) | TRUNCATE TABLE table_name |
UPDATE | UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value |
WHERE | SELECT column_name(s) FROM table_name WHERE condition |