SQL 学习二

--合计函数（Aggregate functions）, Aggregate 函数的操作面向一系列的值，并返回一个单一的值。
	--AVG()，返回数据列的平均值，NULL值不包括在计算中
		SELECT AVG(OrderPrice) AS OrderAverage FROM Orders;
		SELECT Customer FROM Orders WHERE OrderPrice > (SELECT AVG(OrderPrice) FROM Orders);
	--COUNT()，统计记录数
		SELECT COUNT(*) FROM table_name;--返回表中的记录数
		SELECT COUNT(DISTINCT column_name) FROM table_name;--返回指定列的不同值的数目
	--FIRST(),返回指定的字段中第一个记录的值
		SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders;
	--LAST(),返回指定的字段中最后一个记录的值
		SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders;
		--在MySQL中，FRIST()和LAST()无效可用limit代替
		SELECT OrderPrice AS FirstOrderPrice FROM Orders LIMIT 1;
		SELECT OrderPrice AS LastOrderPrice FROM Orders ORDER BY id DESC LIMIT 1;
	--MAX()，最大值
		SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;
	--MIN()，最小值
		SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders;
	--SUM()，求合
		SELECT SUM(OrderPrice) AS OrderTotal FROM Orders;
	

--Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。	
	--UCASE()，转大写
		SELECT UCASE(LastName) AS LastName, FirstName FROM Persons;
	--LCASE()，转小写
		SELECT LCASE(LastName) AS LastName, FirstName From Persons;
	--MID()，截取字段内容
		SELECT MID(City,1,3) AS SmallCity FROM Persons;
	--LEN()，统计字段长度
		SELECT LEN(City) AS LengthOfCity FROM Persons;
		--MySQL
		SELECT LENGTH(City) AS LengthOfCity FROM Persons;
	--ROUND()，四舍五入
		SELECT ROUND(column_name, decimals) FROM table_name;
	--NOW()，返回当前的日期和时间
		SELECT NOW() FROM table_name;
	--FORMAT(),格式化字段
		SELECT FORMAT(NOW(), 'YYYY-MM-DD') as PerDate FROM Products;
		--MySQL
		SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') as PerDate FROM Products;