MySQL常用函數

聚合函數

• count：計數。count(*)≈count(1)>count(主鍵)
  • count(*)：MySQL對count(*)底層優化，count(0)。
  • count(1)
  • count(主鍵)
  • count(欄位)
• min：最小值
• max：最大值
• sum：求和
• avg：平均值

數值型函數

主要是對數值型進行處理。

• ceiling(x)：向上取整
• floor(x)：向下取整
• round(x)：四捨五入
• truncate(x,y)：返回數字x截斷為y位小數的結果
• PI：圓周率，π
• rand：返回0到1的隨機數
• abs：絕對值

-- 絕對值
select ABS(-4) 4的絕對值,ABS(-1.1);
-- 向下取整，向上取整，四捨五入
select CEILING(4.1),FLOOR(1.1),ROUND(-4.4)
-- 取餘
select MOD(60,11);
-- 隨機數
select RAND(),RAND(),RAND()
-- 截斷
select TRUNCATE(2.33999999,2);

字元串型函數

對字元串進行處理。

• length(s)：字元串的長度
• concat(s1,s2,.....sn)：合併字元串
• lower(str)：將字母轉成小寫
• upper(str)：將字母轉成大寫
• left(str,x)：返回字元串str的左邊的x個字元
• right(str,x)：返回字元串str右邊的x個字元
• trim：去掉左右兩邊的空格
• replace：替換
• substring：截取
• reverse：反轉

select LEFT('abcdefg',2);
select RIGHT('abcdefg',2);
select REVERSE('hijklmn');
select REPLACE('abcdefg','abc','x');

日期和時間函數

date,time,datetime,timestamp,year。

獲取時間和日期

• 【curdate】和【current_date】，返回當前的系統日期。
• 【curtime】和【current_time】，返回當前的系統時間。
• 【now】和【sysdate】，返回當前的系統時間和日期。

select CURRENT_DATE();
select CURTIME();
select now();

時間戳和日期轉換函數

• 【UNIX_TIMESTAMP】獲取unix時間戳函數
• 【FROM_UNIXTIME】將時間戳轉換為時間格式

select UNIX_TIMESTAMP();
select FROM_UNIXTIME(1660785720);

根據日期獲取年月日的數值

select MONTH(SYSDATE());
select MONTHNAME(SYSDATE());
select DAYNAME(SYSDATE());
select DAYOFWEEK(SYSDATE());
select WEEK(SYSDATE());
select DAYOFMONTH(SYSDATE());
select YEAR(SYSDATE());

時間日期的計算

-- 日期加法
select DATE_ADD(SYSDATE(),INTERVAL 70 DAY);
-- 日期減法
select DATE_SUB(SYSDATE(),INTERVAL 10 DAY);
-- 時間間隔
select DATEDIFF('2023-01-01',SYSDATE());
-- 日期格式化
select DATE_FORMAT(SYSDATE(),'%W %M %D %Y');

加密函數

-- 把傳入的參數的字元串按照md5演算法進行加密，得到一個32位的16進位的字元串
select MD5('123456');

md5演算法是不可逆的。

流程式控制制函數

可以進行條件判斷，用來實現SQL語句的邏輯。

• if(test,t,f)：如果test是真，則返回t，否則返回f
• ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否則返回arg2
• nullif(arg1,arg2)：如果arg1=arg2返回null，否則返回arg1

select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);

對一系列的值進行判斷：

-- 輸出學生的各科的成績，以及評級，60以下D，60-70是C，71-80是B，80以上是A
SELECT
	*,
CASE
		
		WHEN score < 60 THEN 'D' WHEN score >= 60 
		AND score < 70 THEN 'C' WHEN score >= 70 
			AND score < 80 THEN 'B' WHEN score >= 80 THEN
				'A' 
			END AS '評級' 
	FROM
	mystudent;

-- 行轉列
SELECT
	user_name,
	max( CASE course WHEN '數學' THEN score ELSE 0 END ) '數學',
	max( CASE course WHEN '語文' THEN score ELSE 0 END ) '語文',
	max( CASE course WHEN '英語' THEN score ELSE 0 END ) '英語' 
FROM
	mystudent 
GROUP BY
	user_name

資料庫設計

三範式

• 第一範式：要求有主鍵，並且要求每一個欄位的原子性不能再分。
• 第二範式：要求所有的非主鍵欄位完全依賴主鍵，不能產生部分依賴
• 第三範式：所有非主鍵欄位和主鍵欄位之間不能產生傳遞依賴。

第一範式

不符合第一範式表結構：

符合第一範式的表結構：

必須有主鍵，這是資料庫設計的基本要求，一般情況下我們採用數值型或定長字元串，列不能再分，比如：聯繫方式。

關於第一範式，保證每一行的數據是唯一，每個表必須有主鍵。

第二範式

建立在第一範式的基礎上，要求所有非主鍵欄位完全依賴於主鍵，不能產生部分依賴。

解決方案：

學生表：學號是主鍵

課程表：課程編號是主鍵

成績表：學號和課程編號為聯合主鍵

第三範式

建立在第二範式基礎上，非主鍵欄位不能傳遞依賴於主鍵欄位。

不滿足第三範式：

解決方案：

學生表：學號是主鍵

課程表：課程編號是主鍵

常見的表關係

一對一

學生信息表分為基本信息表和信息信息表。

• 分為兩張表，共用主鍵。
• 分兩張表，用外鍵連接。

一對多

兩張表，外鍵在多的一方。

• 分兩張表存儲，在多的一方加外鍵
• 這個外鍵欄位引用是一的一方的主鍵

多對多

• 分三張表存儲，在學生表存儲學生信息，在課程表存儲課程信息。
• 在成績表中存儲學生和課程的對應關係。

mysql未完待續....

索引，視圖，存儲過程，觸發器，函數....