常用函數(shù)

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

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

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

幾種函數(shù)類型

1、日期函數(shù)

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、字符串函數(shù)

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、數(shù)字函數(shù)

floor()向下取整

示例：select floor(123.8934);

ceil() 向上取整

示例：select ceil(123.8934)；

round() 四舍五入

示例：select round(123.8934, 2);

4、聚合函數(shù)

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

SUM 求和

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

AVG 求平均值

MAX 求最大值

MIN 求最小值

合并

關鍵字：

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

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

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

使用場景：在項目統(tǒng)計報表模塊，用來合并數(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
# 注意此處去掉了重復的值，可以與下面的語句執(zhí)行結果比較
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

視圖索引

視圖

概念及特點

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

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

視圖作用

1）簡化操作

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

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

索引

概念

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

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

優(yōu)缺點

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

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

分類

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

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

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

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

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

索引維護

創(chuàng)建

語法

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

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

1366

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

1451

違反外鍵約束

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

最新評論