sql语法基础(3)
一. Date函数
Mysql Date 函数
| 函数 | 描述 |
|---|---|
| NOW() | 返回当前的日期和时间 |
| CURDATE() | 返回当前的日期 |
| CURTIME() | 返回当前的时间 |
| DATE() | 提取日期或日期/时间表达式的日期部分 |
| EXTRACT() | 返回日期/时间的单独部分 |
| DATE_ADD() | 向日期添加指定的时间间隔 |
| DATE_SUB() | 从日期减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的天数 |
| DATE_FORMAT() | 用不同的格式显示日期/时间 |
Mysql Date数据类型
| 类型 | 格式 |
|---|---|
| DATE | YYYY-MM-DD |
| DATETIME | YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS |
| YEAR | YYYY 或 YY |
日期处理
不涉及时间，比较容易，Orders表如下：
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 2 | Camembert Pierrot | 2008-11-09 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
| 4 | Mascarpone Fabioli | 2008-10-29 |
| 选取一定的OrderDate |
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
但是如果时间部分类似2008-11-11 13:23:44那么，同样的语句不能使用，因为缺少时间部分
因此，一般不要加时间部分。
二. null值/null函数
代表遗漏的未知数据，默认可以存放null值；
无法比较null和0，二者不等价
无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>
is null能够选取null的值
is not null能够选取非null得值
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL
IFNULL()/COALESCE
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM Products
如果例子中的UnitsOnOrder是null，那么IFNULL或者COALESCE的结果就是后边规定的参数，0
三. 通用数据类型
在MySQL中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。
Text类型
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符）。 在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字符）。 在括号中指定字符串的最大长度。最多 255 个字符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) | 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。 如果列表中不存在插入的值，则插入空值。注释：这些值是按照您输入的顺序排序的。 可以按照此格式输入可能的值： ENUM('X','Y','Z') |
| SET | 与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。 |
Number 类型
整数类型默认包含正负，可以添加UNSIGNED，范围从0开始了
| 类型 | 描述 |
|---|---|
| TINYINT(size) | -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 |
| SMALLINT(size) | -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 |
| MEDIUMINT(size) | -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 |
| INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
| BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。 |
| FLOAT(size,d) | 带有浮动小数点的小数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DOUBLE(size,d) | 带有浮动小数点的大数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
Date 类型
| 数据类型 | 描述 |
|---|---|
| DATE() | 日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31' |
| DATETIME() | *日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
| TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC)至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
| TIME() | 时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59' |
| YEAR() | 2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。 |
| DATETIME和TIMESTAM不同 | |
| TIMESTAM在update和insert中，会把自身自动设置为当前时间，也可以接受不同的格式：YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD |
还有其他的数据类型
| 数据类型 | 描述 |
|---|---|
| sql_variant | 存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。 |
| uniqueidentifier | 存储全局唯一标识符 (GUID)。 |
| xml | 存储 XML 格式化数据。最多 2GB。 |
| cursor | 存储对用于数据库操作的指针的引用。 |
| table | 存储结果集，供稍后处理。 |
四.函数
4.1 函数
Aggregate函数，从列中取值，返回一个单一的值，有用的Aggregate函数如下：
- AVG() 返回平均值
- COUNT() 返回行数
- FIRST() 返回第一个记录的值
- LAST() 返回最后一个记录的值
- MAX() 返回最大值
- MIN() 返回最小值
- SUM() 返回总和
Scalar 函数，基于输入值，返回一个单一的值
- UCASE() 将某个字段转换为大写
- LCASE() 将某个字段转换为小写
- MID() 从某个文本字段提取字符
- LEN() 返回某个文本字段的长度
- ROUND() 对某个数值字段进行指定小数位数的四舍五入
- NOW() 返回当前的系统日期和时间
- FORMAT() 格式化某个字段的显示方式
4.2 Aggregate函数
AVG函数
SELECT AVG(column_name) FROM table_name
SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);
COUNT() 函数
SELECT COUNT(*) FROM table_name;
查找这列的不同类型的数量
SELECT COUNT(DISTINCT column_name) FROM table_name;
FIRST() 函数 limit
SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1;
LAST() 函数 limit
SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1;
MAX() 函数
SELECT MAX(column_name) FROM table_name;
MIN() 函数
SELECT MIN(column_name) FROM table_name;
SUM() 函数
SELECT SUM(column_name) FROM table_name;
4.3 group by
结合聚合函数，根据一个或多个列对结果集进行分组；
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
原表内容如下
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
针对site_id列进行group by
select * from access_log group by site_id;
效果和distinct一样，在每个site_id的值上都挑选了第一个记录
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 4 | 2 | 10 | 2016-05-14 |
| 2 | 3 | 100 | 2016-05-13 |
| 6 | 4 | 13 | 2016-05-15 |
| 5 | 5 | 205 | 2016-05-14 |
+-----+---------+-------+------------+
执行了聚合语句，并把聚合后的新列作为展示列，对于聚合操作，是对group by后相同的值分别聚合；
select aid,site_id,count,date,count(site_id) from access_log group by site_id ;
group by多表连接,统计所有网站的访问的记录数
Websites表存所有的网站信息，access_log存网站的访问信息，join left ON基于网站的访问access_log，GROUP BY作用于结果整体
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.name;
4.4 having
where子句中无法添加聚合函数，having筛选分组后的各组数据，筛选的是组；
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;
查找访问量大于200的网站,首先....SUM(access_log.count) access_log inner join websites ON access_log.site_id=Websites.id GROUP BY Websites.name
统计出各个网站的访问总量，然后HAVING SUM(access_log.count) > 200选出访问总量大于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(access_log.count) > 200;
4.5 Scalar函数
UCASE()/LCASE() 函数
UCASE把列内容转换为大写
LCASE把列内容转换为小写
SELECT UCASE(column_name) FROM table_name;
SELECT LCASE(column_name) FROM table_name;
MID() 函数
从文本中提取字符串
SELECT MID(column_name,start[,length]) FROM table_name;
| 参数 | 描述 |
|---|---|
| column_name | 必需。要提取字符的字段。 |
| start | 必需。规定开始位置（起始值是 1）。 |
| length | 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。 |
比如：
SELECT MID(name,1,4) AS ShortTitle FROM Websites;
LEN() 函数
mysql中是LENGTH,返回文本字段中值的长度，数值，日期都可以用
SELECT LENGTH(column_name) FROM table_name;
ROUND() 函数
SELECT ROUND(column_name,decimals) FROM table_name;
| 参数 | 描述 |
|---|---|
| column_name | 必需。要舍入的字段。 |
| decimals | 必需。规定要返回的小数位数。 |
ROUND(X)四舍五入
ROUND(X,D)四舍五入到D位小数，如果D为0，结果没有小数的部分；
NOW() 函数
now返回得当前时间
SELECT name, url, Now() AS date FROM Websites;
FORMAT() 函数
SELECT FORMAT(column_name,format) FROM table_name;
| 参数 | 描述 |
|---|---|
| column_name | 必需。要格式化的字段。 |
| format | 必需。规定格式。 |
格式化日期
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date FROM Websites;
把Websites中的日期格式化为YYYY-MM-DD
SELECT name, url, date_format(now(),'%Y-%m-%d') AS date FROM websites;
浙公网安备 33010602011771号