分析语句语法
完整的分析语句语法如下：
SELECT [DISTINCT] (* | expression) [AS alias] [, ...]
[GROUP BY expression [, ...] [HAVING predicates]]
[ORDER BY expression [ASC | DESC] [, ...]]
[LIMIT size OFFSET offset]
SELECT
指定查询的字段。
使用*查询所有字段
SELECT *
| account_number |
firstname |
gender |
city |
balance |
employer |
state |
lastname |
age |
| 1 |
Amber |
M |
Brogan |
39225 |
Pyrami |
IL |
Duke |
32 |
| 16 |
Hattie |
M |
Dante |
5686 |
Netagy |
TN |
Bond |
36 |
| 13 |
Nanette |
F |
Nogal |
32838 |
Quility |
VA |
Bates |
28 |
| 18 |
Dale |
M |
Orick |
4180 |
null |
MD |
Adams |
32 |
查询指定字段
SELECT firstname, lastname
| firstname |
lastname |
| Amber |
Duke |
| Hattie |
Bond |
| Nanette |
Bates |
| Dale |
Adams |
使用AS给字段定义别名
SELECT account_number AS num
使用DISTINCT去重
SELECT DISTINCT age
使用SQL函数
函数相关内容请参见函数。
SELECT LENGTH(firstname) as len, firstname
| len |
firstname |
| 4 |
Amber |
| 6 |
Hattie |
| 7 |
Nanette |
| 4 |
Dale |
GROUP BY
按值分组。
按字段的值分组
SELECT age GROUP BY age
按字段别名分组
SELECT account_number AS num GROUP BY num
按多个字段分组
SELECT account_number AS num, age GROUP BY num, age
| num |
age |
| 1 |
32 |
| 16 |
36 |
| 13 |
28 |
| 18 |
32 |
使用SQL函数
函数相关内容请参见函数。
SELECT LENGTH(lastname) AS len, COUNT(*) AS count GROUP BY LENGTH(lastname)
HAVING
在分组的基础上，结合聚合函数来筛选数据。
SELECT age, MAX(balance) GROUP BY age HAVING MIN(balance) > 10000
| age |
MAX(balance) |
| 28 |
32838 |
| 32 |
39225 |
ORDER BY
按字段值排序。
使用字段值排序
SELECT age ORDER BY age DESC
LIMIT
指定返回数据的条数。
指定返回的条数
SELECT * LIMIT 1
| account_number |
firstname |
gender |
city |
balance |
employer |
state |
lastname |
age |
| 1 |
Amber |
M |
Brogan |
39225 |
Pyrami |
IL |
Duke |
32 |
指定返回的条数和偏移量
SELECT * LIMIT 1 OFFSET 1
| account_number |
firstname |
gender |
city |
balance |
employer |
state |
lastname |
age |
| 16 |
Hattie |
M |
Dante |
5686 |
Netagy |
TN |
Bond |
36 |
函数
数学类
| 函数 |
作用 |
定义 |
示例 |
| abs |
绝对值 |
abs(number T) -> T |
SELECT abs(0.5) LIMIT 1 |
| add |
加法 |
add(number T, number) -> T |
SELECT add(1, 5) LIMIT 1 |
| cbrt |
立方根 |
cbrt(number T) -> T |
SELECT cbrt(0.5) LIMIT 1 |
| ceil |
向上取整 |
ceil(number T) -> T |
SELECT ceil(0.5) LIMIT 1 |
| divide |
除法 |
divide(number T, number) -> T |
SELECT divide(1, 0.5) LIMIT 1 |
| e |
自然底数e |
e() -> double |
SELECT e() LIMIT 1 |
| exp |
自然底数e 的次幂 |
exp(number T) -> T |
SELECT exp(0.5) LIMIT 1 |
| expm1 |
自然底数e 的次幂减一 |
expm1(number T) -> T |
SELECT expm1(0.5) LIMIT 1 |
| floor |
向下取整 |
floor(number T) -> T |
SELECT floor(0.5) AS Rounded_Down LIMIT 1 |
| ln |
自然对数 |
ln(number T) -> double |
SELECT ln(10) LIMIT 1 |
| log |
以T 为底数的对数 |
log(number T, number) -> double |
SELECT log(10) LIMIT 1 |
| log2 |
以2 为底数的对数 |
log2(number T) -> double |
SELECT log2(10) LIMIT 1 |
| log10 |
以10 为底数的对数 |
log10(number T) -> double |
SELECT log10(10) LIMIT 1 |
| mod |
取余 |
mod(number T, number) -> T |
SELECT modulus(2, 3) LIMIT 1 |
| multiply |
乘法 |
multiply(number T, number) -> number |
SELECT multiply(2, 3) LIMIT 1 |
| pi |
π |
pi() -> double |
SELECT pi() LIMIT 1 |
| pow |
T 的次幂 |
pow(number T, number) -> T |
SELECT pow(2, 3) LIMIT 1 |
| power |
T 的次幂 |
power(number T) -> T,
power(number T, number) -> T |
SELECT power(2, 3) LIMIT 1 |
| rand |
随机数 |
rand() -> number, rand(number T) -> T |
SELECT rand(5) LIMIT 1 |
| rint |
舍弃小数 |
rint(number T) -> T |
SELECT rint(1.5) LIMIT 1 |
| round |
四舍五入 |
round(number T) -> T |
SELECT round(1.5) LIMIT 1 |
| sign |
符号 |
sign(number T) -> T |
SELECT sign(1.5) LIMIT 1 |
| signum |
符号 |
signum(number T) -> T |
SELECT signum(0.5) LIMIT 1 |
| sqrt |
平方根 |
sqrt(number T) -> T |
SELECT sqrt(0.5) LIMIT 1 |
| subtract |
减法 |
subtract(number T, number) -> T |
SELECT subtract(3, 2) LIMIT 1 |
| / |
除法 |
number / number -> number |
SELECT 1 / 100 LIMIT 1 |
| % |
取余 |
number % number -> number |
SELECT 1 % 100 LIMIT 1 |
三角函数
| 函数 |
作用 |
定义 |
示例 |
| acos |
反余弦 |
acos(number T) -> double |
SELECT acos(0.5) LIMIT 1 |
| asin |
反正弦 |
asin(number T) -> double |
SELECT asin(0.5) LIMIT 1 |
| atan |
反正切 |
atan(number T) -> double |
SELECT atan(0.5) LIMIT 1 |
| atan2 |
T 和 U 相除的结果的反正切 |
atan2(number T, number U) -> double |
SELECT atan2(1, 0.5) LIMIT 1 |
| cos |
余弦 |
cos(number T) -> double |
SELECT cos(0.5) LIMIT 1 |
| cosh |
双曲余弦 |
cosh(number T) -> double |
SELECT cosh(0.5) LIMIT 1 |
| cot |
余切 |
cot(number T) -> double |
SELECT cot(0.5) LIMIT 1 |
| degrees |
弧度转换为度 |
degrees(number T) -> double |
SELECT degrees(0.5) LIMIT 1 |
| radians |
度转换为弧度 |
radians(number T) -> double |
SELECT radians(0.5) LIMIT 1 |
| sin |
正弦 |
sin(number T) -> double |
SELECT sin(0.5) LIMIT 1 |
| sinh |
双曲正弦 |
sinh(number T) -> double |
SELECT sinh(0.5) LIMIT 1 |
| tan |
正切 |
tan(number T) -> double |
SELECT tan(0.5) LIMIT 1 |
时间函数
| 函数 |
作用 |
定义 |
示例 |
| curdate |
当前日期 |
curdate() -> date |
SELECT curdate() LIMIT 1 |
| date |
日期 |
date(date) -> date |
SELECT date() LIMIT 1 |
| date_format |
根据格式获取对应日期值 |
date_format(date, string) -> string |
SELECT date_format(date, 'Y') LIMIT 1 |
| day_of_month |
月份 |
day_of_month(date) -> integer |
SELECT day_of_month(date) LIMIT 1 |
| day_of_week |
周几 |
day_of_week(date) -> integer |
SELECT day_of_week(date) LIMIT 1 |
| day_of_year |
当年天数 |
day_of_year(date) -> integer |
SELECT day_of_year(date) LIMIT 1 |
| hour_of_day |
当天小时数 |
hour_of_day(date) -> integer |
SELECT hour_of_day(date) LIMIT 1 |
| maketime |
生成日期 |
maketime(integer, integer, integer) -> time |
SELECT maketime(11, 30, 00) LIMIT 1 |
| minute_of_hour |
当前小时分钟数 |
minute_of_hour(date) -> integer |
SELECT minute_of_hour(date) LIMIT 1 |
| minute_of_day |
当天分钟数 |
minute_of_day(date) -> integer |
SELECT minute_of_day(date) LIMIT 1 |
| monthname |
月份名称 |
monthname(date) -> string |
SELECT monthname(date) LIMIT 1 |
| now |
当前时间 |
now() -> time |
SELECT now() LIMIT 1 |
| second_of_minute |
秒数 |
minute_of_day(date) -> integer |
SELECT minute_of_day(date) LIMIT 1 |
| timestamp |
日期 |
timestamp(date) -> date |
SELECT timestamp(date) LIMIT 1 |
| year |
年份 |
year(date) -> integer |
SELECT year(date) LIMIT 1 |
文本函数
| 函数 |
作用 |
定义 |
示例 |
| ascii |
第一个字符的ASCII 值 |
ascii(string T) -> integer |
SELECT ascii('t') LIMIT 1 |
| concat_ws |
连接字符串 |
concat_ws(separator, string, string) -> string |
SELECT concat_ws('-', 'Tutorial', 'is', 'fun!') LIMIT 1 |
| left |
从左往右取字符串 |
left(string T, integer) -> T |
SELECT left('hello', 2) LIMIT 1 |
| length |
长度 |
length(string) -> integer |
SELECT length('hello') LIMIT 1 |
| locate |
查找字符串 |
locate(string, string) -> integer |
SELECT locate('o', 'hello') LIMIT 1 |
| replace |
替换字符串 |
replace(string T, string, string) -> T |
SELECT replace('hello', 'l', 'x') LIMIT 1 |
| right |
从右往左取字符串 |
right(string T, integer) -> T |
SELECT right('hello', 1) LIMIT 1 |
| rtrim |
去除右侧空字符串 |
rtrim(string T) -> T |
SELECT rtrim('hello ') LIMIT 1 |
| substring |
取子字符串 |
substring(string T, integer, integer) -> T |
SELECT substring('hello', 2,5) LIMIT 1 |
| trim |
去除两侧空字符串 |
trim(string T) -> T |
SELECT trim(' hello ') LIMIT 1 |
| upper |
全部转为大写 |
upper(string T) -> T |
SELECT upper('helloworld') LIMIT 1 |
其他
| 函数 |
作用 |
定义 |
示例 |
| if |
if判断 |
if(boolean, object, object) -> object |
SELECT if(false, 0, 1) LIMIT 1 ,
SELECT if(true, 0, 1) LIMIT 1 |
| ifnull |
字段为null时，填充默认值 |
ifnull(object, object) -> object |
SELECT ifnull('hello', 1) LIMIT 1 ,
SELECT ifnull(null, 1) LIMIT 1 |
| isnull |
字段是否为null，是返回1，否返回0 |
isnull(object) -> integer |
SELECT isnull(null) LIMIT 1 ,
SELECT isnull(1) LIMIT 1 |
聚合函数
| 函数 |
作用 |
定义 |
示例 |
| avg |
求平均 |
avg(number T) -> T |
SELECT avg(age) LIMIT 1 |
| sum |
求和 |
sum(number T) -> T |
SELECT sum(age) LIMIT 1 |
| min |
最小值 |
min(number T) -> T |
SELECT min(age) LIMIT 1 |
| max |
最大值 |
max(number T) -> T |
SELECT max(age) LIMIT 1 |
| count |
次数 |
count(field) -> integer ,
count(*) -> integer ,
count(1) -> integer |
SELECT count(age) LIMIT 1 ,
SELECT count(*) LIMIT 1 ,
SELECT count(1) LIMIT 1 |
