CDA資料分析師 - SQL資料庫基礎 查詢&連線

• SQL 查詢

【應用】

查詢單表語句

select語句、from 語句、where 語句、group by 語句、having 語句、order by 語句、limit語句&子查詢等

聚合函式，包括 count、sum、avg、max&min 等

【DEMO】

-- 查詢單表資料
SELECT *
FROM `score_info` AS SCO
WHERE SCO.id > 0
GROUP BY SCO.id
HAVING SCO.id = 2
ORDER BY SCO.id DESC
-- LIMIT a, b -- a代表起始值（預設為0），b代表限制總數
LIMIT 0, 5;

• SQL 連線

【領會】

表的連線型別，包括內連線（等值、不等值）、外連線（左、右、全）&交叉連線（笛卡爾連線）

查詢的集合操作，只包括並集操作

資料的更新及刪除

檢視的概念及建立

【應用】

查詢多表語句

inner join 的用法、left/right/full join 的用法、cross join 的用法&union 的用法等

【DEMO】

-- 查詢多表資料 橫向連線 （備註：MySQL不支援FUll JOIN）
CREATE OR REPLACE VIEW `v_left_score` AS
SELECT
-- *
STU.id AS '學號',
`STU`.`name` AS '姓名',
SUM( SCO.score ) AS '總分',
AVG( SCO.score ) AS '均分',
ROW_NUMBER ( ) OVER ( ORDER BY SUM( SCO.score ) DESC ) AS '排名' 
FROM
	`student_info` AS STU
	LEFT JOIN `score_info` AS SCO ON STU.id = SCO.stu_id 
GROUP BY
	STU.id 
ORDER BY
	SUM( SCO.score ) DESC;
	
-- 交叉連線（笛卡爾連線）
SELECT * FROM `student_info` 
CROSS JOIN `subject_info`;
-- OR
SELECT * FROM `student_info`, `subject_info`;
	
-- 查詢多表資料 縱向連線
SELECT SCO.score FROM `score_info` AS SCO 
UNION
SELECT SCO.score FROM `score_info` AS SCO;

-- 更新資料
UPDATE `student_info`
SET `name` = '華盛頓'
WHERE `id` = 3;

-- 刪除資料
DELETE FROM `student_info`
WHERE `id` = 4;

-- 新建檢視
CREATE VIEW `v_subject_info` AS SELECT
* 
FROM
	subject_info;

-- 更新檢視
CREATE OR REPLACE VIEW `v_subject_info` AS SELECT
	* 
FROM
	subject_info;

-- 刪除檢視
DROP VIEW `v_subject_info`;

• 其它 SQL 語句中常用的關鍵字、運算子&函式

【領會】

關鍵字

運算子

函式

其他函式&Oracle / Hive row number over partition by

MySQL正則匹配

【解析&DEMO】

1.常用關鍵字 AND、OR、NOT、IN、BETWEEN、LIKE、IS NULL、DISTINCT等 ，DISTINCT為去重；

2.常用運算子包括比較運算子、算數運算子、邏輯運算子；

3.常用函式包括數值型別函式、字串型別函式、日期時間函式&其他函式；

【DEMO】

-- 數值型別函式
SELECT
-- N的平方根
SQRT(121),
-- N的符號值（正數1 負數-1 0為0）
SIGN(11),
-- N的截斷M位小數
TRUNCATE(1.5555, 3),
-- N的四捨五入M位小數
ROUND(1.5555, 3),
-- 大於N的最小整數
CEILING(10),
-- 小於N的最大整數
FLOOR(11),
-- 0至1的隨機數
RAND(),
-- π值
PI(),
-- 取餘數
MOD(8,3),
-- 以2為底8的對數
LOG(2,8),
-- N的自然對數
LN(EXP(11)),
-- 最小值
LEAST(22,22,11),
-- 最大值
GREATEST(1,2,11),
-- e的N次方
EXP(3),
-- 十六機制
HEX(17),
-- 八進位制
OCT(9),
-- 二進位制
BIN(3),
-- 絕對值
ABS(-100);

-- 字串型別函式
SELECT
-- 去前後空格
TRIM(' abcd '),
-- 去後空格
RTRIM(' abcd '),
-- 去前空格
LTRIM(' abcd '),
-- 顛倒順序
REVERSE('abcd'),
-- str重複N次
REPEAT('a',4),
-- 引用轉義
QUOTE('a''b''c'),
-- substr in str 的位置
POSITION('c'IN'abcd'),
-- 長度
LENGTH('1234'),
-- 後第N位的字元
RIGHT('abcd',1),
-- 前第N位的字元
LEFT('abcd',1),
-- 轉大寫
UPPER('aBc'),
UCASE('aBc'),
-- 轉小寫
LOWER('AbC'),
LCASE('AbC'),
-- str in strlist的位置
FIND_IN_SET('c','a,b,c'),
-- 替換str中b為f
REPLACE('abcdBBBbef','b','f'),
-- 替換 ostr的第a到b位 為nstr 
INSERT('a',1,4,'bb'),
-- 用 連線strlist
CONCAT_WS(' ','aa','bb','cc'),
-- 連線strlist
CONCAT('aa','bb','cc'),
-- str的位元長度（二進位制位數）
BIT_LENGTH(' '),
-- str的ASCII碼值
ASCII(NULL);

-- 時間日期函式
SELECT
-- fmt格式日期
DATE_FORMAT(NOW(),'%a'),
-- fmt格式UNIX
FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()),'%a'),
-- 月
MONTHNAME(NOW()),
-- 星期
DAYNAME(NOW()),
-- 日期
DATE(NOW()),
-- 時間
TIME(NOW()),
-- 當前日期
CURDATE(),
CURRENT_DATE(),
-- 當前時間
CURTIME(),
CURRENT_TIME(),
-- 一年的第幾天
DAYOFYEAR(NOW()),
-- 一月的第幾天
DAYOFMONTH(NOW()),
-- 一週的第幾天（範圍1-7，週日為第一天）
DAYOFWEEK(NOW()),
-- 年
YEAR(NOW()),
-- 季度
QUARTER(NOW()),
-- 月
MONTH(NOW()),
-- 周
WEEK(NOW()),
-- 日
DAY(NOW()),
-- 時
HOUR(NOW()),
-- 分
MINUTE(NOW()),
-- 秒
SECOND(NOW());

-- 其他函式
-- 結果聚合
SELECT
    GROUP_CONCAT(STU.`name`)
FROM
	`student_info` STU 
GROUP BY
    STU.remarks;
-- 資料型別轉換
SELECT
CAST('2019-01-01' AS DATE),
CAST(12+13 AS CHAR);

4.正規表示式匹配。

正規表示式是使用某種模式匹配一類字串的方法，使用REGEXP關鍵字來匹配查詢正規表示式。

正規表示式模式字元	說明
^	匹配字串開始
$	匹配字串結束
.	代表字串中任意字元（包括/n,/t）
[字符集合]	匹配字符集中任意一個位元組
[^字符集合]	匹配除字符集中任意一個位元組
*	匹配前面的子表示式零次或多次，str* 等價於str{0,N}。
+	匹配前面的子表示式一次或多次，str+ 等價於str {1,N}。
字元{M,N}	匹配字元出現>=M次
字元{N}	匹配字元出現>=N次
S1|S2|S3	匹配S1、S2和S3中任意一個

-- 正則匹配
SELECT *
FROM student_info STU
WHERE STU.`name` 
-- 匹配學生姓名以‘張’開始的學生
-- REGEXP '^張'
-- 匹配學生姓名以‘強’結尾的學生
-- REGEXP '強$'
-- 匹配學生姓名包含‘欣’的學生
-- REGEXP '.欣|欣.'
-- REGEXP '[欣]'
-- 匹配學生姓名不叫‘李四’的學生
-- REGEXP '[^李四]'
-- 匹配學生姓名中‘李’出現0,1……次的學生
-- REGEXP '李*'
-- 匹配學生姓名中‘李’出現大於0次的學生
-- REGEXP '李+'
-- 匹配學生姓名中‘李’大於等於1的學生
-- REGEXP '李{1,2}'
-- 匹配學生姓名中‘李’大於等於2的學生
-- REGEXP '李{2}'
-- 匹配學生姓名中含‘李’或‘強’或‘張三’的學生
REGEXP '李|強|張三'
;

 備註：正規表示式模式字元解釋說明中部分為自己嘗試實踐後重新定義，與部分參考資料不太一致，若有興趣請參加作者的帖子討論 https://bbs.csdn.net/topics/392551271 ，非常感謝您的指正！