[MySQL]SQL Function

SQL AVG 函数

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
SELECT AVG(column_name) FROM table_name

SQL MAX()和MIN() 函数

MAX（MIN）函数返回一列中的最大（小）值。NULL 值不包括在计算中。
SELECT MAX(column_name) FROM table_name
SELECT MIN(column_name) FROM table_name
注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。

SQL SUM() 函数

SUM 函数返回数值列的总数（总额）。
SELECT SUM(column_name) FROM table_name

SQL HAVING 子句

在 SQL 中增加 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

SQL UCASE()和LCASE() 函数

UCASE（LCASE） 函数把字段的值转换为大（小）写。
SELECT UCASE(column_name) FROM table_name
SELECT LCASE(column_name) FROM table_name

SQL MID() 函数

MID 函数用于从文本字段中提取字符。
SELECT MID(column_name,start[,length]) FROM table_name

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

SQL LENGTH() 函数

LEN 函数返回文本字段中值的长度。
SELECT LENGTH(column_name) FROM table_name

SQL ROUND() 函数

ROUND 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

SQL NOW() 函数

NOW 函数返回当前的日期和时间。
SELECT NOW() FROM table_name

SQL FORMAT() 函数

FORMAT 函数用于对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name

参数	描述
column_name	必需。要格式化的字段。
format	必需。规定格式。

SQL 快速参考

SQL 语句

语句	语法
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 WHERE condition
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 (column_name1, column_name2,...) VALUES (value1, value2,....)
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) INTO new_table_name FROM original_table_name
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