关键字
参考文章 ：https://blog.csdn.net/weixin_43201975/article/details/88953903
| 关键字 | 描述 |
|---|---|
| ADD | 添加 |
| ALL | 所有，全部 |
| ALTER | 改变，改动，修改 |
| ANALYZE | 解析 |
| AND | 逻辑与 |
| AS | 别名 alias 的缩写 |
| ASC | 这里的ASC(ASCENDING)标识升序的意思，一般和 ORDER BY 配合使用 |
| AVG | 平均 |
| ASENSITIVE | |
| BEFORE | 在……之前 |
| BETWEEN | 在……中间 |
| BIGINT | 大整形：0~18446744073709551615 |
| BINARY | 二进制 |
| BLOB | 二进制类型：0~65535字节 |
| BOTH | 双方，两者 |
| BY | 由，被 |
| AUTO | 自动 |
| AUTO_INCREMENT | 自动递增 |
| CALL | |
| CASCADE | 级联 |
| CASE | 实例 |
| CHANGE | 改变 |
| CHAR | 字符型 |
| CHARACTER | |
| CHECK | 检查 |
| COLLATE | |
| COLUMN | 列 |
| CONDITION | . 状况，状态 |
| CONNECTION | 连接 |
| CONSTRAINT | 约束 |
| CONTINUE | 继续 连续 |
| CONVERT | 转换 改变 |
| CREATE | 创建 |
| CROSS | |
| CURRENT_DATE | 当前日期 |
| CURRENT_TIME | 当前时间 |
| CURRENT_TIMESTAMP | 当前时间戳 |
| CURRENT_USER | 当前用户 |
| Concat | Concat() 函数用来拼接两个列 |
| CURSOR | 光标 |
| COUNT | 计数 |
| cartesian | 笛卡尔 |
| DATABASE | 数据库 |
| DATABASES | 数据库 |
| DAY_HOUR | 小时 |
| DAY_MICROSECOND | 微秒 |
| DAY_MINUTE | 分钟 |
| DAY_SECOND | 秒 |
| DEC | |
| DECIMAL | 十进制小数：-1.7976931348623157E+308~2.2250738585072014E-308 |
| DECLARE | |
| DEFAULT | 默认 |
| DELAYED | 延误的，延迟的 |
| DELETE | 删除 |
| DESC | DESCENDING 降序排列，通常和 ORFER BY 配合使用 |
| DESCRIBE | 描述 |
| DETERMINISTIC | 确定性 |
| DISTINCT | 去重，返回不同的值 |
| DISTINCTROW | 查询 |
| DIV | |
| DOUBLE | 浮点数据类型：-1.7976931348623157E+308~2.2250738585072014E-308 |
| DROP | 删除：用于删除表 |
| DUAL | |
| EACH | 每个 |
| ELSE | 否则 |
| ELSEIF | 否则如果 |
| ENCLOSED | 停业 |
| ESCAPED | |
| EXISTS | 存在 |
| EXIT | 出口，退出 |
| EXPLAIN | 解释说明 |
| FALSE | 错误的 |
| FETCH | 拿来，提取 |
| FLOAT | 浮点数据类型：-3.402823466E+38~-1.175494351E-38 |
| FOR | 循环 循环语句 |
| FORCE | 暴力，强制 |
| FOREIGN KEY | 外键：包含另一个表的主键，定义了两个表之间的关系 |
| FROM | 指出检索数据的表名 |
| FULLTEXT | 全文索引 |
| GOTO | 跳转 |
| GRANT | 授予 |
| GROUP BY | 根据什么进行分组 |
| HAVING | HAVING 过滤分组 ，和 GROUP BY 配合使用 |
| HIGH_PRIORITY | 高优先级 |
| IF | 如果 |
| IGNORE | 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 |
| IN | 在什么什么之内。IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配 |
| INDEX | 索引 |
| INFILE | |
| INNER JOIN | 内连接 |
| INOUT | |
| INSENSITIVE | |
| INSERT | 插入 |
| INT | 数据类型 ： 整型，整数：0~4294967295 |
| INTEGER | 整数 |
| INTERVAL | 间隔 |
| INTO | 到…里面 |
| IS | 是 |
| ITERATE | 迭代 |
| JOIN | 连接（多表关联查询） |
| KEY | 钥匙🔑 |
| KILL | 杀死 |
| LABEL | 标签，标记 |
| LEADING | |
| LEAVE | 离开 |
| LEFT JOIN | 左连接 |
| LIKE | 模糊查询，配合通配符进行使用（百分号%通配符，下划线_通配符） |
| LIMIT | 限制结果 |
| LINEAR | 线性 |
| LINES | 线，行 |
| LOAD | 加载，装载 |
| LOCALTIME | 本地时间 |
| LOCALTIMESTAMP | 日期和时间 |
| LOCK | 锁 |
| LONG | 长整型 |
| LONGBLOB | 二进制类型：0~4294967295字节 |
| LONGTEXT | 字符串类型：0~4294967295字节 |
| LOOP | 环形 |
| LOW_PRIORITY | 低优先级 |
| Ltrim | Ltrim（）函数用来去除左边多余的空格 |
| Lower | Lower（）函数将字符串转换为小写 |
| Length | 长度 |
| MATCH | |
| MEDIUMBLOB | 二进制类型： 0~16777215字节 |
| MEDIUMINT | 中型整形数据类型：0~16777215 |
| MEDIUMTEXT | 字符串类型：0~16777215字节 |
| MIDDLEINT | |
| MINUTE_MICROSECOND | |
| MINUTE_SECOND | |
| MOD | |
| MODIFIES | |
| MAX | 最大 |
| MIN | 最小 |
| NATURAL | |
| NOT | 逻辑非，非 |
| NO_WRITE_TO_BINLOG | |
| NULL | 无值 |
| NUMERIC | 数值型 |
| ON | |
| OFFSET | 偏移量，一般和 limit 配合使用 |
| OPTIMIZE | |
| OPTION | 选择 选项 |
| OPTIONALLY | 可选择地 |
| OR | 或，或者 |
| ORDER BY | 以……排序，如果按照多个列进行排序的话使用逗号（，）进行分割 |
| OUT | 输出 |
| OUTER JOIN | 外连接 |
| OUTFILE | 输出文件 |
| PRECISION | 精度 |
| PRIMARY KEY | 主键 |
| PROCEDURE | |
| PURGE | |
| RAID0 | |
| RANGE | 范围 |
| READ | 阅读 |
| REAL | 实际存在的 |
| REFERENCES | 参考文献 |
| REGEXP | 正则表达式 |
| RELEASE | 释放 |
| RENAME | 重命名 |
| RIGHTREPEAT | |
| REPLACE | 替换 |
| REQUIRE | 需要 要求 |
| RESTRICT | 限制 |
| RETURN | 返回 |
| REVOKE | 撤销 吊销 |
| RIGHT JOIN | 右连接 |
| RLIKE | 正则表达式匹配 |
| Rtrim | Rtrim（）函数用来删除数据右侧多余的空格来整理数据 |
| SCHEMA | 架构 |
| SELECT | 用途是从一个或多个表中检索信息 |
| SENSITIVE | |
| SEPARATOR | 分隔符 |
| SET | 设置 |
| SHOW | 显示 |
| SMALLINT | 整数类型：短整型：0~65535 |
| SPATIAL | |
| SPECIFIC | |
| SQLEXCEPTION | SQL异常 |
| SQLSTATE | SQL状态 |
| SQLWARNING | SQL警告 |
| STARTING | 开始；出发 |
| STRAIGHT_JOIN | |
| SUM | 总和，求和，总数 |
| TABLE | 表 |
| TEXT | 字符串类型：0~65535字节 |
| TERMINATED | 终止 |
| THEN | 然后 那么 当时 接着 |
| TINYBLOB | 二进制类型 ： 0~255字节 |
| TINYINT | 整数类型：0~255 |
| TINYTEXT | 字符串类型：0~255字节 |
| TO | 到 |
| TRAILING | |
| TRIGGER | 触发器 |
| TRUE | 真实 |
| trim | trim（）函数用来去掉左右两边的空格 |
| UNDO | 撤消 |
| UNION | 组合查询：利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集 |
| UNIQUE | 独特 |
| UNLOCK | 解锁 |
| UNSIGNED | 无符号 |
| UPDATE | 更新，更新语句 |
| USAGE | 用法 |
| USE | 用，使用 |
| USING | 使用 |
| Upper | Upper（）函数用于将文本转换为大写 |
| VALUES | 值 |
| VARBINARY | 二进制数据 |
| VARCHAR | 可变长字符串 |
| VARCHARACTER | |
| VARYING | |
| VIEW | 视图 |
| WHEN | 什么时候 |
| WHERE | 过滤条件，只检索出符合条件的数据 |
| WHILE | 当……的时候 |
| WITH | 和……在一起 |
| WRITE | 写 |
| ZEROFILL | 补零 |
数据类型
整数类型
| 数据类型 | 字节数 | 无符号数的取值范围 | 有符号数的取值范围 |
|---|---|---|---|
| TINYINT | 1 | 0~255 | -128~127 |
| SMALLINT | 2 | 0~65535 | -32768~32768 |
| MEDIUMINT | 3 | 0~16777215 | -8388608~8388608 |
| INT | 4 | 0~4294967295 | -2147483648~ 2147483648 |
| BIGINT | 8 | 0~18446744073709551615 | -9223372036854775808~9223372036854775808 |
位类型
| 数据类型 |
|---|
| BIT |
浮点型
| 数据类型 | 字节数 | 有符号的取值范围 | 无符号的取值范围 |
|---|---|---|---|
| FLOAT | 4 | -3.402823466E+38~-1.175494351E-38 | 0和1.175494351E-38~3.402823466E+38 |
| DOUBLE | 8 | -1.7976931348623157E+308~2.2250738585072014E-308 | 0和2.2250738585072014E-308~1.7976931348623157E+308 |
定点型
| 数据类型 | 字节数 | 有符号的取值范围 | 无符号的取值范围 |
|---|---|---|---|
| DECIMAL（M,D） | M+2 | -1.7976931348623157E+308~2.2250738585072014E-308 | 0和2.2250738585072014E-308~1.7976931348623157E+308 |
日期时间类型
| 数据类型 | 字节数 | 取值范围 | 日期格式 | 零值 |
|---|---|---|---|---|
| YEAR | 1 | 1901~2155 | YYYY | 0000 |
| DATE | 4 | 1000-01-01~9999-12-31 | YYYY-MM-DD | 0000-00-00 |
| TIME | 3 | -838：59：59~ 838：59：59 | HH:MM:SS | 00:00:00 |
| DATETIME | 8 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01~2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
字符串
| 数据类型 | 储存范围 |
|---|---|
| TINYTEXT | 0~255字节 |
| TEXT | 0~65535字节 |
| MEDIUMTEXT | 0~16777215字节 |
| LONGTEXT | 0~4294967295字节 |
二进制数据
| 数据类型 | 储存范围 |
|---|---|
| TINYBLOB | 0~255字节 |
| BLOB | 0~65535字节 |
| MEDIUMBLOB | 0~16777215字节 |
| LONGBLOB | 0~4294967295字节 |
枚举型
| 数据类型 |
|---|
| ENUM |
集合
| 数据类型 |
|---|
| SET |
SQL 语言规范
-
mysql对于SQL语句不区分大小写，SQL语句关键字尽量大写
-
值，除了数值型，字符串型和日期时间类型使用单引号（’’）
-
别名，尽量使用双引号（“”），而且不建议省略as
-
所有标点符号使用英文状态下的半角输入方式
-
必须保证所有(),单引号，双引号是成对结束的
-
可以使用（1）#单行注释 （2）–空格单行注释 （3）/* 多行注释 */
命名规则：
-
数据库、表名不得超过30个字符，变量名限制为29个
-
必须只能包含 A–Z, a–z, 0–9, _共63个字符
-
不能在对象名的字符间留空格
-
必须不能和用户定义的其他对象重名
-
必须保证你的字段没有和保留字、数据库系统或常用方法冲突
-
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
SQL分类
-
DDL（Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。
主要的语句关键字包括create、drop、alter等。
-
DML（Data Manipulation Language）：数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
主要的语句关键字包括insert、delete、update、select等。
-
DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括grant、revoke等。
插入数据（INSERT）
语法：
INSERT INTO 表名称 VALUES(值1，值2，......);
INSERT INTO 表名称 VALUES(值1，值2，......),(值1，值2，......),...;
INSERT INTO 表名称 (字段1，字段2，......) VALUES(值1，值2，......);
INSERT INTO 表名称 (字段1，字段2，......) VALUES(值1，值2，......),(值1，值2，......),.....;
修改数据(UPDATE)
语法：
UPDATE 表名称 SET 字段名1 = 值1, 字段名2=值2,...... 【WHERE 条件】;
UPDATE 表1，表2,...... SET 表1.字段名1 = 值1, 表1.字段名2=值2,表2.字段1 = 值1, 表2.字段2=值2...... 【WHERE 条件】;
删除数据（DELETE）
语法
delete from 表名 【where 条件】;
delete 表1，表2,....... from 表1，表2，...... 【where 条件】;
查询书写顺序
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]
into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}
sql 执行顺序
别名 AS (ALIAS)
运算符
-
算数运算符：+ - * / %
-
比较运算符：= > >= < <= !=
-
逻辑运算符：&&（逻辑与也可以写成and） ||（逻辑或也可以写成or） not（逻辑非） xor（逻辑异或）
-
范围： between … and … not between … and …
-
集合：in (值，值，值…) not in(值，值，值…)
-
模糊查询： LIKE NOT LIKE，通配符：%表示0-n个字符，_下划线代表一个字符
-
位运算符：&（按位与） |（按位或）^（按位异或）~（按位取反）>>（右移）<<（左移）
-
NULL值判断，is null 或 is not null，如果使用null=null，null<>null,null=0,null<>0,null=false等都不对
不过xxx is null 可以使用xxx <=> null ，xxx is not null 可以写成 not xxx <=> null
结论：所有的运算符遇到NULL结果都是NULL，除了<=>
DISTINCT（去重）
SELECT DISTINCT 列名 FROM 表
LIMIT （限制结果）
LIMIT [位置偏移量,] 行数
SELECT * FROM 表 LIMIT 5; 检索出前五条记录
SELECT * FROM 表 LIMIT 0,5; 检索出前五条记录
SELECT * FROM 表 LIMIT 5,10; 从下标5的位置检索出10条记录
ORDER BY(排序)
对一个或多个字段进行排序
默认是升序排列
多个字段时 使用逗号 ‘ ，’ 分割。
如果两个字段排序不一样时：
SELECT 字段1，字段2 FROM 表 ORDER BY 字段1 ASC,字段2 desc;
SELECT col_name FROM 表 ORDER BY col_name [ASC | DESC];
GROUP BY(分组查询)
[GROUP BY 字段] [HAVING <条件表达式>]
根据一个或多个列对结果集进行分组
在分组的列上可以配合使用聚集函数。（）
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
注意事项：
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
HAVING（分组过滤）
- WHERE 和 HAVING 区别
- WHERE 过滤行，而 HAVING 过滤分组
- where针对表中的列发挥作用,查询数据；having针对查询结果中的列发挥作用,筛选数据
- where后面不能写分组函数，而having后面可以使用分组函数
- having只用于group by分组统计语句
语法：
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING where_condition
JOIN（多表联合查询）
内连接（INNER JOIN）
SELECT [cols_list] from 表1 INNER JOIN 表2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表1 CROSS JOIN 表2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表1 JOIN 表2 ON [关联条件] where [其他筛选条件]
外连接
左连接(LEFT JOIN)
-
说明
返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。
SELECT [cols_list] from 表1 LEFT JOIN 表2 ON [关联条件] where [其他筛选条件]
右连接(RIGHT JOIN)
-
说明
返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。
SELECT [cols_list] from 表1 RIGHT JOIN 表2 ON [关联条件] where [其他筛选条件]
全连接（FULL JOIN）
mysql 不支持全连接，但可以通过 LEFT JOIN UNION RIGHT JOIN
SELECT [cols_list] from 表1 LEFT JOIN 表2 ON [关联条件] where [其他筛选条件]
UNION
SELECT [cols_list] from 表1 RIGHT JOIN 表2 ON [关联条件] where [其他筛选条件]
UNION（联合，结合）
语法：
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
WITH
说明：
如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。
注意事项：
- 相当于一个临时表，但是不同于视图，不会存储起来，要与select配合使用。
- 同一个select前可以有多个临时表，写一个with就可以，用逗号隔开，最后一个with语句不要用逗号。
- with子句要用括号括起来。
语法：
with 别名 AS (查询语句) 查询语句
例子：
with t1 as (
select year(hire_date) as hire_year, count(empno) as cnt from SQL_7 group by year(hire_date) order by hire_year
)
select *, sum(cnt) over(partition by null rows between unbounded preceding and current row) as sum from t1;
函数
文本处理函数
| 函数 | 说明 |
|---|---|
| Left(str, length) | 从左边开始截取str，length是截取的长度； |
| Right(str, length) | 从右边开始截取str，length是截取的长度； |
| Length(str) | 返回串的长度 |
| Locate(substr , str ) | 返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0； |
| INSTR(str,substr) | INSTR()函数返回字符串中子字符串第一次出现的位置。如果在str中找不到子字符串，则INSTR()函数返回零(0)。 |
| Lower() | 将串转换为小写 |
| Upper() | 将串转换为大写 |
| trim（） | 去除前后空格 |
| LTrim() | 去掉串左边的空格 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| REVERSE() | 字符串反转 |
| SubString(str ,n ,m) | 返回字符串str从第n个字符截取到第m个字符； |
| substring_index(str ,substr ,n) | 返回字符substr在str中第n次出现位置之前的字符串; |
| replace(str, n, m) | 将字符串str中的n字符替换成m字符； |
| CONCAT(string1,string2, … ); | 字符串拼接，如果有个字段为null值，则函数返回一个null值 |
| CONCAT_WS(seperator,string1,string2, … ); | 函数在字符串参数之间添加分隔符，并返回单个字符串，并在字符串参数之间插入分隔符。 |
| GROUP_CONCAT( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] ) | 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。 |
时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期（天、周等） |
| AddTime() | 增加一个时间（时、分等） |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff(date1,date2) | 返回两个日期之间的天数 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format(date,format) | 返回一个格式化的日期或时间串 |
| DayOfWeek() | 对于一个日期，返回对应的星期几 |
| Now() | 返回当前日期和时间 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
| Month() | 返回一个日期的月份部分 |
| Day() | 返回一个日期的天数部分 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Second() | 返回一个时间的秒部分 |
日期加减操作
MySQL 为日期增加一个时间间隔：date_add()
now() //now函数为获取当前时间
select date_add(now(), interval 1 day); - 加1天
select date_add(now(), interval 1 hour); -加1小时
select date_add(now(), interval 1 minute); - 加1分钟
select date_add(now(), interval 1 second); -加1秒
select date_add(now(), interval 1 microsecond);-加1毫秒
select date_add(now(), interval 1 week);-加1周
select date_add(now(), interval 1 month);-加1月
select date_add(now(), interval 1 quarter);-加1季
select date_add(now(), interval 1 year);-加1年
MySQL adddate(), addtime()函数，可以用date_add() 来替代。
MySQL 为日期减去一个时间间隔：date_sub()
MySQL date_sub() 日期时间函数 和date_add() 用法一致。
MySQL 中subdate(),subtime()函数，建议，用date_sub()来替代。
数值处理函数
| 函数 | 说明 |
|---|---|
| Mod() | 取余 |
| round(x,d) | 四舍五入：x指要处理的数，d是指保留几位小数 |
| truncate(x,d) | 截断：x指要处理的数，d是指保留几位小数 |
| Ceil() | 向上取整 |
| floor() | 向下取整 |
| Rand() | 返回一个随机数 |
| Sqrt() | 返回一个数的平方根 |
| Sin() | 返回一个角度的正弦 |
| Cos() | 返回一个角度的余弦 |
| Tan() | 返回一个角度的正切 |
| Abs() | 返回一个数的绝对值 |
| Exp() | 返回一个数的指数值 |
| Pi() | 圆周率 |
聚合函数
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 在一组值中找到最大值。 |
| MIN() | 在一组值中找到最小值。 |
| SUM() | 计算一组值或表达式的总和。 |
| group_concat（） | 将分组中的字符串与各种选项进行连接。(如DISTINCT，ORDER BY和SEPARATOR)的字符串。group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] ) |
条件判断函数
| 函数 | 说明 |
|---|---|
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
| if(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| ifnull(value1, value2) | 如果value1不为NULL，返回value1，否则返回value2。 |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
if
语法
IF(expr,result_true,result_false)
示例
SELECT COUNT(IF(sex=1,1,NULL)) 男生人数,COUNT(IF(sex=0,1,NULL))女生人数 FROM students
case when end else
语法1
CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END
示例
SELECT name,score,(CASE
WHEN score>=90 THEN '优秀'
WHEN score>=80 THEN '良好'
WHEN score>=60 THEN '及格'
ELSE '不及格' END) level
FROM score
IFNULL
语法
IFNULL(expression, alt_value)
说明
用于判断第一个表达式是否为NULL 。如果为NULL，则返回第二个参数，如果不为NULL，则返回第一个参数的值用于判断第一个表达式是否为NULL 。如果为NULL，则返回第二个参数，如果不为NULL，则返回第一个参数的值
示例
SELECT IFNULL(NULL, "RUNOOB");
窗口函数
| 名称 | 描述 |
|---|---|
| ROW_NUMBER | 为其分区中的每一行分配一个连续整数 |
| RANK | 与DENSE_RANK（）函数类似，只是当两行或更多行具有相同的排名时，排序值序列中存在间隙。 |
| DENSE_RANK | 根据ORDER BY子句为其分区中的每一行分配一个排名。 它为具有相同值的行分配相同的排名。 如果两行或更多行具有相同的等级，则排序值序列中将没有间隙。 |
| PERCENT_RANK | 计算分区或结果集中行的百分位数 |
| CUME_DIST | 计算一组值中值的累积分布。 |
| LAG | 返回分区中当前行之前的第N行的值。 如果不存在前一行，则返回NULL。 |
| LEAD | 返回分区中当前行之后的第N行的值。 如果不存在后续行，则返回NULL。 |
| FIRST_VALUE | 返回指定表达式相对于窗口框架中第一行的值。 |
| LAST_VALUE | 返回指定表达式相对于窗口框架中最后一行的值。 |
| NTH_VALUE | 返回窗口框架第N行的参数值 |
| NTILE | 将每个窗口分区的行分配到指定数量的已排名组中。 |
-
说明
窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。
-
作用
- 解决排名问题，e.g.每个班级按成绩排名
- 解决TOPN问题，e.g.每个班级前两名的学生
-
语法
select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名) -
分类
- 专用窗口函数：rank()，dense_rank()，row_number()
- 汇总函数：max()，min()，count()，sum()，avg()
***注意：***窗口函数是对where后者group by子句处理后的结果进行操作，因此按照SQL语句的运行顺序，窗口函数一般放在select子句中。
rank()函数
-
示例
#按班级分类，将成绩降序排序 SELECT*, rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking FROM class; -
说明
- rank()是排序函数，括号中不需要有参数；
- 通过partition by将班级分类，相当于之前用过的group by子句功能，但是group by子句分类汇总会改变原数据的行数，而用窗口函数自救保持原行数；
- 通过order by将成绩降序排列，与之前学的order by子句用法一样，后边可以升序asc或者降序desc；
-
总结
- 窗口函数这里的“窗口”表示范围，可以理解为将原数据划分范围，即分组，然后用函数实现某些目的
- 窗口函数有分组和排序的功能
- 不减少原表的行数
dense_rank()函数
用法 和 rank（）函数相同
row_number()函数
用法 和 rank（）函数相同
总结
这三个函数的区别是：如何处理并列的问题
-
如下
当成绩相同时，会存在并列的情况；
- 在rank()函数，如果有并列情况，会占用下一个名次的位置，比如，成绩为100的学生有三个并列第一，那么99分的学生是第二名，通过rank()函数，名次是：1,1,1,4；
- 在dense()函数中，如果有并列的情况，不会占用下一个名词，同用上个例子，名次是：1,1,1,2；
- 在row_number()函数中，会忽略并列的情况，同用上述例子，名次是：1,2,3,4;
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
