分析語句
更新時間 2025-09-22 16:07:08
最近更新時間: 2025-09-22 16:07:08
分享文章
本節介紹分析語句的語法。
分析語句語法
完整的分析語句語法如下：
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
| num |
|---|
| 1 |
| 16 |
| 13 |
| 18 |
使用DISTINCT去重
SELECT DISTINCT age
| age |
|---|
| 32 |
| 36 |
| 28 |
使用SQL函數
函數相關內容請參見函數。
SELECT LENGTH(firstname) as len, firstname
| len | firstname |
|---|---|
| 4 | Amber |
| 6 | Hattie |
| 7 | Nanette |
| 4 | Dale |
GROUP BY
按值分組。
按字段的值分組
SELECT age GROUP BY age
| age |
|---|
| 28 |
| 32 |
| 36 |
按字段別名分組
SELECT account_number AS num GROUP BY num
| num |
|---|
| 1 |
| 16 |
| 13 |
| 18 |
按多個字段分組
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)
| len | count |
|---|---|
| 4 | 2 |
| 5 | 2 |
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
| age |
|---|
| 28 |
| 32 |
| 32 |
| 36 |
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 |
