基本認(rèn)識

SELECT 'test'; -- 查詢單個值
SELECT 1, 2.3, 'test', true; -- 查詢多個值
SELECT 1 AS 整數(shù), 2.1 AS 浮點數(shù), '測試' AS 字符串, true AS 布爾型; -- 針對每個查詢到的值，在其后配合AS關(guān)鍵字設(shè)置別名
SELECT 1 + 2, 5 * 10, version() AS 版本信息; -- 查詢函數(shù)或算式運算結(jié)果

FROM關(guān)鍵字

-- 創(chuàng)建示例數(shù)據(jù)表
CREATE TABLE tips
(
    total_bill FLOAT,
    tip        FLOAT,
    sex        CHAR(6),
    smoker     CHAR(3),
    day        CHAR(3),
    time       TEXT,
    size       INT
);

-- 插入示例數(shù)據(jù)
INSERT INTO tips
VALUES (16.99, 1.01, 'Female', 'No', 'Sun', 'Dinner', 2),
       (10.34, 1.66, 'Male', 'No', 'Sun', 'Dinner', 3),
       (21.01, 3.5, 'Male', 'No', 'Sun', 'Dinner', 3),
       (23.68, 3.31, 'Male', 'No', 'Sun', 'Dinner', 2),
       (24.59, 3.61, 'Female', 'No', 'Sun', 'Dinner', 4),
       (25.29, 4.71, 'Male', 'No', 'Sun', 'Dinner', 4),
       (8.77, 2.0, 'Male', 'No', 'Sun', 'Dinner', 2),
       (26.88, 3.12, 'Male', 'No', 'Sun', 'Dinner', 4),
       (15.04, 1.96, 'Male', 'No', 'Sun', 'Dinner', 2),
       (14.78, 3.23, 'Male', 'No', 'Sun', 'Dinner', 2);

SELECT * FROM tips;
SELECT tip, sex FROM tips;
SELECT tip AS 小費 FROM tips;

LIMIT與OFFSET

SELECT * FROM tips LIMIT 3; -- 查詢前三行
SELECT * FROM tips LIMIT 3 OFFSET 8; -- 從第9行(包含)開始查詢?nèi)?/span>

ORDER BY

SELECT * FROM tips ORDER BY total_bill DESC; -- 單字段降序（默認(rèn)升序）
SELECT * FROM tips ORDER BY size ASC, total_bill DESC; -- 多字段排序

WHERE條件查詢

-- 示例數(shù)據(jù)
CREATE TABLE funds
(
    fund_code          VARCHAR(10) PRIMARY KEY,
    fund_name          TEXT,
    fund_type          TEXT,
    within_a_week      FLOAT,
    within_a_month     FLOAT,
    within_three_month FLOAT,
    within_this_year   FLOAT,
    within_six_month   FLOAT,
    within_one_year    FLOAT
);

INSERT INTO funds
VALUES ('5669', '前海開源公用事業(yè)股票', '股票型', 0.0714, 0.2844, 0.6162, 0.3708, 0.3582, 1.263),
       ('828', '泰達(dá)轉(zhuǎn)型機(jī)遇', '股票型', 0.0328, 0.1904, 0.5942, 0.2832, 0.2827, 1.0693),
       ('689', '前海開源新經(jīng)濟(jì)混合', '混合型', 0.0769, 0.2797, 0.5918, 0.3768, 0.3504, 0.9222),

單值比較

= 等于
<> 不等于
<、<= 小于、小于等于
>、>= 大于、大于等于

SELECT * FROM funds WHERE fund_type <> '混合型';

多條件組合

SELECT * FROM funds 
WHERE fund_type <> '混合型' AND within_three_month > 0.5; -- AND

SELECT * FROM funds
WHERE fund_type = '股票型' OR fund_type = '債券型'; -- OR

SELECT * FROM funds
WHERE fund_type IN ('股票型', '債券型'); -- IN

SELECT * FROM funds
WHERE NOT fund_type IN ('股票型', '債券型'); -- NOT（加在完整條件之前）

范圍篩選

SELECT * FROM funds
WHERE within_three_month BETWEEN 0.4 AND 0.5; -- BETWEEN ... AND ...

空值匹配

SELECT * FROM funds WHERE within_one_year = NULL; -- 這樣不行

SELECT * FROM funds WHERE within_one_year IS NULL; -- 正確

LIKE通配

• 主要有兩種通配符——'%'與'_'，其中'%'代表任意多個字符，'_'代表單個任意字符

SELECT * FROM funds WHERE fund_name LIKE '%新能源%';

SELECT * FROM funds WHERE fund_name LIKE '%a';
SELECT * FROM funds WHERE fund_name LIKE '%A'; -- 在MySQL中，與上面返回一致（不區(qū)分大小寫）

SELECT * FROM funds WHERE BINARY fund_name LIKE '%A'; -- 強制當(dāng)成二進(jìn)制格式后通配

條件分組

SELECT * FROM funds
WHERE (fund_name LIKE '%醫(yī)藥%' AND within_three_month > 0.4)
   OR (fund_name LIKE '%車%' AND within_three_month > 0.45);

運算符和函數(shù)

數(shù)據(jù)變換

• 文本處理

MySQL

/*
concat(字段1, 字段2, ... ,字段n)用于將傳入的若干個【字段】或單個值信息拼接為新的單個字段
left(字段, 截取字符數(shù)量)用于從【字段】每條記錄最左端開始提取【截取字符數(shù)量】個的字符
right(字段, 截取字符數(shù)量)用于從【字段】每條記錄最右端開始提取【截取字符數(shù)量】個的字符
char_length(字段)用于計算傳入【字段】每條記錄的字符數(shù)量（單個漢字亦算作1個字符）
substr(字段, 開始位置, 截取長度)用于提取【字段】從【開始位置】往后最多【截取數(shù)量】個字符
regexp_like(字段, 正則表達(dá)式)用于判斷【字段】中是否存在滿足【正則表達(dá)式】模式子串，返回1表示存在，0表示不存在
replace(字段, 目標(biāo)字符串, 替換字符串)用于將【字段】中所有【目標(biāo)字符串】替換為【替換字符串】
repeat(字段, 重復(fù)次數(shù))用于將【字段】復(fù)制【重復(fù)次數(shù)】后進(jìn)行拼接
reverse(字段)用于將【字段】中每條字符記錄進(jìn)行翻轉(zhuǎn)
*/
SELECT concat(fund_name, ' ', fund_type, ' ', within_a_month) AS 'concat()',
       left(fund_name, 2) AS 'left()',
       right(fund_name, 3) AS 'right()',
       char_length(fund_name) AS 'char_length()',
       substr(fund_name, 3, 4) AS 'substr()',
       regexp_like(fund_name, '醫(yī)藥') AS 'regexp_like()',
       replace(fund_name, '醫(yī)藥', 'medicine') AS 'replace()',
       repeat(fund_name, 3) AS 'repeat()',
       reverse(fund_name) AS 'reverse()'
FROM funds;

PostgreSQL

/*
concat(字段1, 字段2, ... ,字段n)用于將傳入的若干個【字段】或單個值信息拼接為新的單個字段
left(字段, 截取字符數(shù)量)用于從【字段】每條記錄最左端開始提取【截取字符數(shù)量】個的字符
right(字段, 截取字符數(shù)量)用于從【字段】每條記錄最右端開始提取【截取字符數(shù)量】個的字符
char_length(字段)用于計算傳入【字段】每條記錄的字符數(shù)量（單個漢字亦算作1個字符）
substr(字段, 開始位置, 截取長度)用于提取【字段】從【開始位置】往后最多【截取數(shù)量】個字符
字段 ~ 正則表達(dá)式  用于判斷【字段】中是否存在滿足【正則表達(dá)式】模式子串，直接返回返回boolean型判斷結(jié)果
replace(字段, 目標(biāo)字符串, 替換字符串)用于將【字段】中所有【目標(biāo)字符串】替換為【替換字符串】
repeat(字段, 重復(fù)次數(shù))用于將【字段】復(fù)制【重復(fù)次數(shù)】后進(jìn)行拼接
reverse(字段)用于將【字段】中每條字符記錄進(jìn)行翻轉(zhuǎn)
*/
SELECT concat(fund_name, ' ', fund_type, ' ', within_a_month) AS "concat()",
       left(fund_name, 2) AS "left()",
       right(fund_name, 3) AS "right()",
       char_length(fund_name) AS "char_length()",
       substr(fund_name, 3, 4) AS "substr()",
       fund_name ~ '醫(yī)藥' AS "~",
       replace(fund_name, '醫(yī)藥', 'medicine') AS "replace()",
       repeat(fund_name, 3) AS "repeat()",
       reverse(fund_name) AS "reverse()"
FROM funds;

在PostgreSQL中還可以使用 || 來拼接若干個字符型字段：
SELECT fund_name || ' - ' || fund_type FROM funds;

• 數(shù)值計算

PostgreSQL

SELECT 1 + 1 AS 加,
       1 - 2 AS 減,
       2 * 5 AS 乘,
       4 / 3 AS 整數(shù)除法,
       4 / 3. AS 浮點數(shù)除法,
       2 ^ 2 AS 平方,
       |/ 25 AS 平方根,
       5 % 2.2 AS 取余,
       ||/ 27 AS 立方根,
       3! AS 階乘,
       @ -5.5 AS 絕對值;

MySQL

SELECT 1 + 1 AS 加,
       1 - 2 AS 減,
       2 * 5 AS 乘,
       4 / 3 AS 整數(shù)除法,
       4 / 3. AS 浮點數(shù)除法,
       5 % 2.2 AS 取余,
       sqrt(9) AS 平方根,
       abs(-5.5) AS 絕對值;

• 聚合函數(shù)

SELECT min(within_three_month) AS 最小值,
       max(within_three_month) AS 最大值,
       avg(within_three_month) AS 平均值,
       count(fund_type) AS 計算行數(shù),
       sum(within_three_month) AS 求和
FROM funds;

SELECT DISTINCT fund_type FROM funds; -- 去重
SELECT count(DISTINCT fund_type) FROM funds; -- 統(tǒng)計不重復(fù)字段的數(shù)量

分組運算

SELECT fund_type, count(*) AS 基金數(shù)量, avg(within_a_month) AS 最近一個月平均漲幅 -- 這些字段運算后必須是和分組字段等長的，即對非分組字段進(jìn)行的運算操作一定是聚合壓縮操作
FROM funds
GROUP BY fund_type
ORDER BY 最近一個月平均漲幅 DESC
LIMIT 3;

• 分組后過濾（HAVING）

SELECT fund_type, count(*) AS 基金數(shù)量, avg(within_a_month) AS 最近一個月平均漲幅
FROM funds
GROUP BY fund_type
HAVING count(*) >= 5; -- having篩選條件中只能對分組依據(jù)字段進(jìn)行篩選，或?qū)ζ渌侄芜M(jìn)行聚合后篩選

執(zhí)行順序

第一步: FROM <left_table>
第二步: ON <join_condition>
第三步: <join_type> JOIN <right_table>
第四步: WHERE <where_condition>
第五步: GROUP BY <group_by_list>
第六步: HAVING <having_condition>
第七步: SELECT
第八步: DISTINCT <select_list>
第九步: ORDER BY <order_by_condition>
第十步: LIMIT <limit_number>

因此在select中定義的別名不能在where或having中使用

表連接

-- 創(chuàng)建商品信息表
CREATE TABLE product_info
(
    product_id       VARCHAR PRIMARY KEY,
    product_category VARCHAR,
    product_name     VARCHAR,
    product_price    FLOAT
);

-- 創(chuàng)建銷售記錄表
CREATE TABLE sale_records
(
    sold_product_id VARCHAR,
    amount          INT,
    discount        FLOAT
);

-- 插入示例數(shù)據(jù)
INSERT INTO product_info
VALUES ('ca4ef73a-dd6d-11eb-894e-287fcf8fcac7', '生活用品', '花王蒸汽眼罩12片裝', 48.8),
       ('ca4ef73b-dd6d-11eb-922a-287fcf8fcac7', '鞋類', '安踏EDGE跑鞋', 429.0),
       ('ca4ef73c-dd6d-11eb-b7ee-287fcf8fcac7', '鞋類', '匹克態(tài)極3.0跑步鞋', 499.0),
       ('ca4ef73d-dd6d-11eb-9fc4-287fcf8fcac7', '辦公用品', 'Salli馬鞍椅', 2255.0),
       ('ca4ef73e-dd6d-11eb-851b-287fcf8fcac7', '辦公用品', 'GAVEE人體工學(xué)椅', 3888.0),
       ('ca4ef73f-dd6d-11eb-ba5f-287fcf8fcac7', '辦公用品', '赫曼米勒Aeron人體工學(xué)椅', 16310.0),
       ('e2d3o9ed-dccb-11eb-we7u-287fcf8fcac7', '數(shù)碼產(chǎn)品', '華為Mate X2折疊手機(jī)', 17799.0);

INSERT INTO sale_records
VALUES ('ca4ef73a-dd6d-11eb-894e-287fcf8fcac7', 2, 0.9),
       ('ca4ef73b-dd6d-11eb-922a-287fcf8fcac7', 6, 0.8),
       ('ca4ef73d-dd6d-11eb-9fc4-287fcf8fcac7', 3, 0.9),
       ('ca4ef73b-dd6d-11eb-922a-287fcf8fcac7', 1, 0.9),
       ('ca4ef73e-dd6d-11eb-851b-287fcf8fcac7', 2, 0.8),
       ('ca4ef73c-dd6d-11eb-b7ee-287fcf8fcac7', 9, 0.7),

內(nèi)連接

-- 內(nèi)連接
SELECT DISTINCT product_name FROM sale_records AS a
INNER JOIN product_info AS b
ON a.sold_product_id = b.product_id;

-- 兩表中的字段不存在重復(fù)，可以省略別名
SELECT DISTINCT product_name FROM sale_records
INNER JOIN product_info
ON sold_product_id = product_id;

左(右)外連接

-- 左外連接
SELECT * FROM product_info
LEFT JOIN sale_records
ON sold_product_id = product_id
WHERE product_name = '華為Mate X2折疊手機(jī)';

全外連接

SELECT DISTINCT product_name FROM sale_records
FULL JOIN product_info -- 或 FULL OUTER JOIN (MySQL中沒有全外連接)
ON sold_product_id = product_id;

外鍵約束

-- 創(chuàng)建銷售記錄表
CREATE TABLE sale_records
(
    sold_product_id VARCHAR REFERENCES product_info(product_id),
    amount          INT,
    discount        FLOAT DEFAULT 1
);

在本例中，即希望sale_records中的sold_prodect_id都能在product_info中找到對應(yīng)的product_id

INSERT INTO sale_records VALUES ('not_int_product_info', 1, 1); -- 報錯，因為product_info表中并不存在product_id為'not_int_product_info'的數(shù)據(jù)

窗口函數(shù)

針對每一條數(shù)據(jù)單獨開一個窗，在窗內(nèi)執(zhí)行不同的操作（通常用于既要明細(xì)又要聚合的場景）

示例數(shù)據(jù)：

• 需求一：獲得每個type下播放量前三名對應(yīng)的記錄

SELECT *
FROM (
         SELECT type, author, title, dense_rank() OVER (PARTITION BY type ORDER BY view DESC) AS top3
         FROM bilibili
     ) AS temp
WHERE top3 <= 3;

注意：窗口函數(shù)的執(zhí)行實在WHERE之后的，因此要獲取前三名不能在內(nèi)層直接用WHERE，而是用嵌套查詢在外層使用WHERE

• 需求二：查詢每個type中，在coins降序排名下，每個視頻與其下一名之間播放量的差值

select *, view - diff as diff_view
from (
    select type, author, title, view, coins, lead(view, 1) over (partition by type order by coins desc) as diff
    from bilibili
    ) as _; -- FROM 中的子查詢必須有一個別名

注意：over子句中起的別名不能在同級select中使用，因此還是要用嵌套查詢

更多窗口函數(shù)：

• MySQL
  https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
• PostgreSQL
  http://www.postgres.cn/docs/12/functions-window.html

UNION：表上下拼接

SELECT * FROM t1
UNION
SELECT * FROM t2
UNION
SELECT * FROM t3

-- 要求：兩表具有相同的字段數(shù)量，且字段數(shù)據(jù)類型相互兼容
-- 最終查詢到的表頭字段與第一張表t1一致
-- UNION 會自動去重，使用UNION ALL不去重

子查詢

SELECT *
FROM (
	SELECT * FROM t1
) as _; -- 子查詢必須要給予別名
GROUP BY ...

條件判斷

• PostgreSQL示例數(shù)據(jù)

-- 懂車帝部分車輛評分及價格信息表
CREATE TABLE car_info
(
    brand VARCHAR,
    score FLOAT,
    price VARCHAR
);

INSERT INTO car_info
VALUES ('軒逸', 3.65, '8.48-15.59萬'),
       ('雅閣', 3.94, '15.18-24.18萬'),
       ('思域', 3.83, '9.49-15.89萬'),
       ('朗逸', 3.62, '6.79-15.89萬'),
       ('哈弗H6', 3.81, '9.19-15.49萬'),

• MySQL示例數(shù)據(jù)

-- 懂車帝部分車輛評分及價格信息表
CREATE TABLE car_info
(
    brand TEXT,
    score FLOAT,
    price TEXT
);

INSERT INTO car_info
VALUES ('軒逸', 3.65, '8.48-15.59萬'),
       ('雅閣', 3.94, '15.18-24.18萬'),
       ('思域', 3.83, '9.49-15.89萬'),
       ('朗逸', 3.62, '6.79-15.89萬'),
       ('哈弗H6', 3.81, '9.19-15.49萬')

PostgreSQL

• 對值進(jìn)行匹配

CASE 輸入值 WHEN 匹配值1 THEN 結(jié)果1 WHEN 匹配值2 THEN 結(jié)果2 … ELSE 備選值 END

-- 為奧迪、寶馬、五菱開頭的品牌單獨匹配，其他情況返回other
SELECT CASE left(brand, 2) -- 從左取brand兩個字符
           WHEN '奧迪' THEN 'Audi'
           WHEN '寶馬' THEN 'BMW'
           WHEN '五菱' THEN 'SGMW'
           ELSE 'other'
           END,
       brand
FROM car_info;

• 對條件進(jìn)行匹配

CASE WHEN 條件1 THEN 結(jié)果1 WHEN 條件2 THEN 結(jié)果2 ELSE 備選結(jié)果 END

-- 以4為閾值區(qū)分高評分與非高評分
SELECT CASE WHEN score > 4 THEN '高評分' ELSE '非高評分' END,
       brand,
       score
FROM car_info;

MySQL

CASE-WHEN-THEN-END結(jié)構(gòu)語法與PostgreSQL一致

IF(條件, 滿足條件時返回的值, 不滿足條件時返回的值)

-- 以4為閾值區(qū)分高評分與非高評分
SELECT if(score > 4, '高評分', '非高評分'),
       brand,
       score
FROM car_info;

IFNULL(a, b)，當(dāng)a為NULL時，返回b；當(dāng)a不為null時，返回a文章來源地址http://www.zghlxwxcb.cn/news/detail-427044.html

SELECT IFNULL(null, '是null'), IFNULL('非null', '不返回這個');

到了這里，關(guān)于（SQL學(xué)習(xí)隨筆3）SQL語法——SELECT語句的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！