mysql常用函數與檢視索引全面梳理

2022-10-22 14:01:14

常用函數

函數的概念及特點：

與java，js中的函數概念一致

特點：資料庫函數必須要有返回值（一行一列）

幾種函數型別

1、日期函數

now() 獲取當前日期；

範例：selectnow();

day() 獲取指定日期的日部分；

範例：select day(now());

month() 獲取指定日期的月部分；

範例：select month(now());

year()獲取指定日期的月部分；

範例：selectyear(now());

date_format() 將日期按指定的格式轉換為字串;

範例：selectdate_format(now(), '%Y-%m-%d %H:%i:%s');

str_to_date()：將特定格式的日期轉換成日期；

範例：select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s');

綜合範例：

1）直接按年份計算學生年齡
    SELECT t.sname, YEAR(NOW()) - YEAR(t.birthday) FROM t_student t
2）按照出生日期來算，當前月日 < 出生年月的月日則（說明月份還沒到），年齡減一
SELECT sid,sname,birthday,YEAR(NOW())-YEAR(birthday) '年齡',
       YEAR(NOW())-YEAR(birthday) + IF(CONVERT(DATE_FORMAT(NOW(),'%m%d'),SIGNED)-CONVERT(DATE_FORMAT(birthday,'%m%d'),SIGNED)<0,-1,0) '真實年齡'
FROM t_student;
3）查詢本月過生日的學生資訊
SELECT * FROM t_student t WHERE MONTH(NOW()) - MONTH(t.birthday) = 0;

2)範例執行結果如下：

2、字串函數

upper() 轉換成大寫字元

範例：select upper('faafafa')

lower() 轉換成小寫字元

範例：Sselect lower('FEFEFF')

replace() 搜尋並替換字串中的子字串

範例：select replace('www.google.net','w','n')

substring() 從具有特定長度的位置開始的最一個子字串

範例：

select substring('abcdefghijk', 1, 3)
select substring(('abcdefghijk', 4);
select substring(('abcdefghijk', -3);

trim() 去除首尾空格

範例：select trim(' fdfdfdfd ');

length() 獲取字串長度

範例：select length('abcdef');

3、數位函數

floor()向下取整

範例：select floor(123.8934);

ceil() 向上取整

範例：select ceil(123.8934)；

round() 四捨五入

範例：select round(123.8934, 2);

4、聚合函數

特點：常用group by一同使用，也可單獨使用，如需求過濾，可以使用having子句

SUM 求和

COUNT 統計記錄數

AVG 求平均值

MAX 求最大值

MIN 求最小值

合併

關鍵字：

union 將所有的查詢結果放在一起，並去掉相同的記錄

union all將所有的查詢結果合併在一起，不去掉相同的記錄

合併的前提：結果集列數個數相同

使用場景：在專案統計報表模組，用來合併資料

select 'abc', 123 
union
selet 'def',456
select 'abc', 123
union 
select 'abc', 123
select sid fromt_score where cid = 1
union
select sid from t_score where cid = 2
# 注意此處去掉了重複的值，可以與下面的語句執行結果比較
select sid fromt_score where cid = 1
union all
select sid from t_score where cid = 2
select 'abc', 123
union all
select 'def', 456
select 'abc', 123
union all
select 'abc', 123

檢視索引

檢視

概念及特點

檢視是一種虛擬表，是從資料庫中的一個或多個表中匯出的表

資料庫中存放檢視的定義，而不會存放檢視的資料，資料依然存放在原來的表中。使用檢視查詢資料時，資料庫會從原來的表中獲取資料（注意：此處並沒有包括物化檢視，目前mysql預設不支援物化檢視）

檢視作用

1）簡化操作

2）增加資料的安全性

3）提高表的邏輯獨立性

基本語法

createview 檢視名 as select 語句

範例：

create view stu_score_statistics as
select t1.sid, t1.sname, t1.ssex, t2.courses, t3.total total_score from t_student t1 
left join (select sid, count(*) courses from t_score group by sid) t2 on t1.sid=t2.sid
left join (select sid, sum(score) total from t_score group by sid) t3 on t1.sid=t3.sid

索引

概念

索引是由資料庫表中一列或多列組合而成，其作用是提高對錶中資料的查詢速度

可以理解為書本的目錄的作用

優缺點

優點：對於中型或大型表，恰當的使用索引可以顯著提高查詢效能

缺點：增加了索引維護的工作，使的插入，修改，刪除操作變慢

分類

普通索引（基本索引，目的就是提高查詢效能）

唯一索引（除提高查詢效能外，還可起到避免列值出現重複）

主鍵索引（特殊的唯一索引，一個表只能有一個主鍵，不允許有空值）

組合索引（多列組合生成的索引，使用是需要注意索引順序）

全文索引（用於支援全文搜尋（FULLTEXT））

索引維護

建立

語法

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(欄位名[(長度)][ASC|DESC])

範例

CREATE INDEX sname_inx ON t_student(sname);

修改

語法

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(欄位名[(長度)][ASC|DESC])

範例

ALTER TABLE t_student ADD INDEX birthday_inx(birthday);

刪除

語法

DROP INDEX 索引名 ON 表名

範例

DROP INDEX birthday_inx ON t_student;

常見的錯誤程式碼

1075

有自增鍵，但不是未將其設定為主鍵

1142

操作被拒絕，一般是沒有許可權

1064

一般是存在語法錯誤，如關鍵字錯誤，缺少空格，sql指令碼中sql語句後缺少;等原因

1048

列不能為空

1055

不在GROUP BY中

1265

儲存資料的格式與定義是不同

1366

資料編碼

1451

違反外來鍵約束

到此這篇關於mysql常用函數與檢視索引全面梳理的文章就介紹到這了,更多相關mysql常用函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！