常用函數(shù)

函數(shù)的概念及特點(diǎn)：

與java，js中的函數(shù)概念一致

特點(diǎn)：數(shù)據(jù)庫(kù)函數(shù)必須要有返回值（一行一列）

幾種函數(shù)類型

1、日期函數(shù)

now() 獲取當(dāng)前日期；

示例：selectnow();

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

示例：select day(now());

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

示例：select month(now());

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

示例：selectyear(now());

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

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

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

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

綜合示例：

1）直接按年份計(jì)算學(xué)生年齡
    SELECT t.sname, YEAR(NOW()) - YEAR(t.birthday) FROM t_student t
2）按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則（說(shuō)明月份還沒(méi)到），年齡減一
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) '真實(shí)年齡'
FROM t_student;
3）查詢本月過(guò)生日的學(xué)生信息
SELECT * FROM t_student t WHERE MONTH(NOW()) - MONTH(t.birthday) = 0;

2)示例 運(yùn)行結(jié)果如下：

2、字符串函數(shù)

upper() 轉(zhuǎn)換成大寫字符

示例：select upper('faafafa')

lower() 轉(zhuǎn)換成小寫字符

示例：Sselect lower('FEFEFF')

replace() 搜索并替換字符串中的子字符串

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

substring() 從具有特定長(zhǎng)度的位置開(kāi)始的最一個(gè)子字符串

示例：

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

trim() 去除首尾空格

示例：select trim(' fdfdfdfd ');

length() 獲取字符串長(zhǎng)度

示例：select length('abcdef');

3、數(shù)字函數(shù)

floor()向下取整

示例：select floor(123.8934);

ceil() 向上取整

示例：select ceil(123.8934)；

round() 四舍五入

示例：select round(123.8934, 2);

4、聚合函數(shù)

特點(diǎn)：常用group by一同使用，也可單獨(dú)使用，如需求過(guò)濾，可以使用having子句

SUM 求和

COUNT 統(tǒng)計(jì)記錄數(shù)

AVG 求平均值

MAX 求最大值

MIN 求最小值

合并

關(guān)鍵字：

union 將所有的查詢結(jié)果放在一起，并去掉相同的記錄

union all將所有的查詢結(jié)果合并在一起，不去掉相同的記錄

合并的前提：結(jié)果集列數(shù)個(gè)數(shù)相同

使用場(chǎng)景：在項(xiàng)目統(tǒng)計(jì)報(bào)表模塊，用來(lái)合并數(shù)據(jù)

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
# 注意此處去掉了重復(fù)的值，可以與下面的語(yǔ)句執(zhí)行結(jié)果比較
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

視圖索引

視圖

概念及特點(diǎn)

視圖是一種虛擬表，是從數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)表中導(dǎo)出的表

數(shù)據(jù)庫(kù)中存放視圖的定義，而不會(huì)存放視圖的數(shù)據(jù)，數(shù)據(jù)依然存放在原來(lái)的表中。 使用視圖查詢數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)會(huì)從原來(lái)的表中獲取數(shù)據(jù) （注意：此處并沒(méi)有包括物化視圖，目前mysql默認(rèn)不支持物化視圖）

視圖作用

1）簡(jiǎn)化操作

2）增加數(shù)據(jù)的安全性

3）提高表的邏輯獨(dú)立性

基本語(yǔ)法

createview 視圖名 as select 語(yǔ)句

示例：

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

索引

概念

索引是由數(shù)據(jù)庫(kù)表中一列或多列組合而成，其作用是提高對(duì)表中數(shù)據(jù)的查詢速度

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

優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：對(duì)于中型或大型表，恰當(dāng)?shù)氖褂盟饕梢燥@著提高查詢性能

缺點(diǎn)：增加了索引維護(hù)的工作，使的插入，修改，刪除操作變慢

分類

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

唯一索引（除提高查詢性能外，還可起到避免列值出現(xiàn)重復(fù)）

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

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

全文索引（用于支持全文搜索（FULLTEXT））

索引維護(hù)

創(chuàng)建

語(yǔ)法

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(長(zhǎng)度)][ASC|DESC])

示例

CREATE INDEX sname_inx ON t_student(sname);

修改

語(yǔ)法

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(長(zhǎng)度)][ASC|DESC])

示例

ALTER TABLE t_student ADD INDEX birthday_inx(birthday);

刪除

語(yǔ)法

DROP INDEX 索引名 ON 表名

示例

DROP INDEX birthday_inx ON t_student;

常見(jiàn)的錯(cuò)誤代碼

1075

有自增鍵，但不是未將其設(shè)置為主鍵

1142

操作被拒絕，一般是沒(méi)有權(quán)限

1064

一般是存在語(yǔ)法錯(cuò)誤，如關(guān)鍵字錯(cuò)誤，缺少空格，sql腳本中sql語(yǔ)句后缺少;等原因

1048

列不能為空

1055

不在GROUP BY中

1265

保存數(shù)據(jù)的格式與定義是不同

1366

數(shù)據(jù)編碼

1451

違反外鍵約束

到此這篇關(guān)于mysql常用函數(shù)與視圖索引全面梳理的文章就介紹到這了,更多相關(guān)mysql常用函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論