1索引
介紹
索引本質(zhì)上是一張表，保存了主鍵與索引字段，在對(duì)數(shù)據(jù)做頻繁的查詢或排序時(shí)，可在某些字段上添加索引，提高檢索的的效率，降低IO成本，并可以使用索引列，對(duì)數(shù)據(jù)進(jìn)行排序，降低排序的成本。盲目的添加索引是不正確的。索引表保存數(shù)據(jù)占用空間，在對(duì)數(shù)據(jù)進(jìn)行增刪改操作時(shí)，降低了操作的速度，在更新表的同時(shí)不僅要保存操作數(shù)據(jù)還需要更新索引表中的數(shù)據(jù)，因此我們可以在頻繁的作為查詢條件的字段上添加索引，查詢中與其他表關(guān)聯(lián)的字段，外檢關(guān)系建立索引，在高并發(fā)下創(chuàng)建組合索引等恰當(dāng)?shù)氖褂盟饕聞?wù)場(chǎng)景。索引分為四類有唯一索引，復(fù)合索引，主鍵索引，單值索引。唯一索引的索引列必須是唯一的，但允許有空值，復(fù)合索引指一個(gè)索引包含多個(gè)列。例如用戶表需要用戶名以及手機(jī)號(hào)確定一個(gè)信息，就需要添加復(fù)合索引了。主鍵索引是一個(gè)特殊的唯一索引，非空且默認(rèn)存在，單值索引，一個(gè)索引值包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引。
1.2失效原因
1.索引失效的原因
1.模糊查詢like以%開(kāi)頭，
2.數(shù)據(jù)類型錯(cuò)誤
3.對(duì)索引字段使用內(nèi)部函數(shù)
4.索引列是null
5.索引列運(yùn)行四則運(yùn)算
6.復(fù)合索引不按索引列最左特性開(kāi)始查找
1.3.索引的類型
復(fù)合(最左特性)
主鍵
單值
唯一
1.4 為什么添加了索引查詢快了
本質(zhì)上是因?yàn)閿?shù)據(jù)庫(kù)會(huì)根據(jù)索引的數(shù)據(jù)結(jié)構(gòu)（如B+樹(shù)）構(gòu)建一個(gè)索引樹(shù)。索引樹(shù)的結(jié)構(gòu)使得數(shù)據(jù)庫(kù)可以快速定位到滿足查詢條件的數(shù)據(jù)，而不需要掃描整個(gè)表。通過(guò)索引樹(shù)的搜索，數(shù)據(jù)庫(kù)可以快速確定需要讀取的數(shù)據(jù)頁(yè)，并直接讀取對(duì)應(yīng)的數(shù)據(jù)行，從而大大減少了查詢所需的讀取和比較操作。
1.4如何創(chuàng)建索引
創(chuàng)建主鍵索引：
alter table table_name add primary key(column);
添加UNIQUE(唯一索引)
alter table table_name add unique(column);
添加普通索引
alter table table_name add index index_name(column);
添加多列索引
alter table table_name add index index_name(column1,column2,column3);
2.Sql語(yǔ)句
2.1基礎(chǔ)函數(shù)
lower
SELECT 'ABC',LOWER('ABC') from dept; --數(shù)據(jù)轉(zhuǎn)小寫(xiě)
upper
select upper(dname) from dept --數(shù)據(jù)轉(zhuǎn)大寫(xiě)
length
select length(dname) from dept --數(shù)據(jù)的長(zhǎng)度
substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
concat
select dname,concat(dname,'123') X from dept --拼接數(shù)據(jù)
replace
select dname,replace(dname,'a','666') X from dept --把a(bǔ)字符替換成666
ifnull
select ifnull(comm,10) comm from dept2 #判斷,如果comm是null,用10替換
round四舍五入，ceil向上取整，floor向下取整
select comm,round(comm) from emp–直接四舍五入取整
select comm,round(comm,1) from emp–四舍五入并保留一位小數(shù)
select comm,ceil(comm) ,floor(comm) from emp–ceil向上取整,floor向下取整
now
select now() -- 年與日 時(shí)分秒
select curdate() --年與日
select curtime() --時(shí)分秒
2.2分組
group by
SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都滿足的
having
select deptno, AVG(sal) from emp
group by deptno #按部門(mén)分組
having AVG(sal)<8000 #查詢條件,類似where,但是group by只能配合having
2.3聚合
count
select count(*) from emp --底層優(yōu)化了
select count(1) from emp --效果和*一樣
select count(comm) from emp --慢,只統(tǒng)計(jì)非NULL的
max / min
select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --獲取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分組
sum / avg
select count(*) from emp --總記錄數(shù)
select sum(sal) from emp --求和
select avg(sal) from emp --平均數(shù)
2.4排序
order by
SELECT * FROM emp order by sal #默認(rèn)升序
SELECT * FROM emp order by sal desc #降序
2.5鏈接
left join : 左連接，返回左表中所有的記錄以及右表中連接字段相等的記錄。
right join : 右連接，返回右表中所有的記錄以及左表中連接字段相等的記錄。
inner join : 內(nèi)連接，又叫等值連接，只返回兩個(gè)表中連接字段相等的行。
2.6多表聯(lián)查
1.子查詢
語(yǔ)法：
select 字段名
from 表1，表2 …
where 表1.字段 = 表2.字段
and 其它查詢條件
例：以學(xué)生表student和班級(jí)表class為例
Select student.sid, student.sname, student.classid, class.classid, class.classname
from student，class
where student.classid = class.classid
2.join連接
內(nèi)連接 inner join
語(yǔ)法：
from 表1
inner join 表2
on 表1.字段 = 表2.字段
外連接:
- left join
語(yǔ)法：
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
2.right join
語(yǔ)法：
select 字段名
from 表1
right join 表2
on 表1.字段 = 表2.字段
- left join union right join
語(yǔ)法：
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
union
select 字段名
from 表1
right join 表2
???????on 表1.字段 = 表2.字段
例：
select student.* , class.*from student
left join class
on student.classid = class.classid
union
select student.* , class.*from student
right join class
on student.classid = class.classid
3.笛卡爾積
select * from t_user, t_address;
2.7條件查詢
distinct
使用distinct關(guān)鍵字，去除重復(fù)的記錄行
SELECT DISTINCT loc FROM dept;
like模糊查詢
select * from emp where ename like 'l%' --以l開(kāi)頭的
select * from emp where ename like '%a' --以a結(jié)束的
select * from emp where ename like '%a%' --中間包含a的
select * from emp where ename like 'l__' --l后面有兩個(gè)字符的 _代表一個(gè)字符位置
null
select * from emp where mgr is null --過(guò)濾字段值為空的
select * from emp where mgr is not null --過(guò)濾字段值不為空的
between and
select * from emp where sal between 3000 and 10000
limit
在mysql中，通過(guò)limit進(jìn)行分頁(yè)查詢：
select * from emp limit 0,3 --從第一條開(kāi)始,展示3條記錄--前三條
3.事務(wù)
3.1 特性（ACID）
原子性：一個(gè)事務(wù)要么全部成功，全部失敗，不會(huì)停在某個(gè)階段
一致性 ：事務(wù)的前后處于一致?tīng)顟B(tài)
隔離性：多個(gè)事務(wù)同時(shí)操作相同數(shù)據(jù)庫(kù)的同一個(gè)數(shù)據(jù)時(shí)，一個(gè)事務(wù)的執(zhí)行不受另外一個(gè)事務(wù)的干擾
持久性：一個(gè)事務(wù)一旦提交，則數(shù)據(jù)將持久化到本地，除非其他事務(wù)對(duì)其進(jìn)行修改
3.2隔離級(jí)別
1.讀未提交，最低隔離級(jí)別、事務(wù)未提交前，就可被其他事務(wù)讀取（會(huì)出現(xiàn)幻讀、臟讀、不可重復(fù)讀）；
2.提交讀，一個(gè)事務(wù)提交后才能被其他事務(wù)讀取到（會(huì)造成幻讀、不可重復(fù)讀），SQL server 的默認(rèn)級(jí)別；
3.可重復(fù)讀，保證多次讀取同一個(gè)數(shù)據(jù)時(shí)，其值都和事務(wù)開(kāi)始時(shí)候的內(nèi)容是一致，禁止讀取到別的事務(wù)未提交的數(shù)據(jù)（會(huì)造成幻讀），
MySQL 的默認(rèn)級(jí)別；
4.序列化，代價(jià)最高最可靠的隔離級(jí)別，該隔離級(jí)別能防止臟讀、不可重復(fù)讀、幻讀。
3.3三大范式
第一范式：強(qiáng)調(diào)的是列的原子性，即數(shù)據(jù)庫(kù)表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)。
第二范式：要求實(shí)體的屬性完全依賴于主關(guān)鍵字。所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一部分的屬性。
第三范式：任何非主屬性不依賴于其它非主屬性。
3.4約束
1.主鍵約束（Primary Key ）
保證數(shù)據(jù)的唯一性，并且主鍵列數(shù)據(jù)不能為空（唯一性，非空性）。
alter table 表名
add constraint 主鍵名 primary key(約束列名稱)
2.唯一約束（Unique ）：
保證數(shù)據(jù)的唯一性，唯一約束的列可以為空（唯一性，可以空，但只能有一個(gè)）。
alter table 表名
add constraint 唯一約束名 unique(字段)
3.檢查約束（Check ）：
保證數(shù)據(jù)的有效性，讓值在有效范圍內(nèi)取值，對(duì)該列數(shù)據(jù)的范圍、格式的限制（如：年齡、性別等）
alter table 表名
add constraint 檢查約束名 check(約束列名稱 between 10 and 40)
4.默認(rèn)約束（Default ）：
保證數(shù)據(jù)的完整性，如果沒(méi)有入信息時(shí)，會(huì)使用默認(rèn)信息填入，保證數(shù)據(jù)的完整性。
alter table 表名
add constraint 默認(rèn)約束名 default(默認(rèn)值) for 約束列名稱
5.外鍵約束（Foreign Key ）：
需要建立兩表間的關(guān)系并引用主表的列，保證數(shù)據(jù)的完整性，互相依賴的數(shù)據(jù)不能缺失。
alter table 子表名
add constraint 約束名稱 foreign key(外鍵列名稱) references 主表名稱(主鍵列字段)
6.非空約束
數(shù)據(jù)列不能為空
ALTER TABLE 表名
MODIFY 非空約束名 字段類型 NOT NULL;
其他操作
1.刪除約束
alter table 表名
drop constraint 約束名
2.關(guān)閉約束
alter table 表名
disable constraint 約束名 cascade
//如果沒(méi)有被引用則不需CASCADE關(guān)鍵字
3.打開(kāi)約束文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-458744.html
alter table 表名
enable constraint 約束名
4. MySQL 問(wèn)題排查都有哪些手段？
使用 show processlist 命令查看當(dāng)前所有連接信息。
使用 explain 命令查詢 SQL 語(yǔ)句執(zhí)行計(jì)劃。
開(kāi)啟慢查詢?nèi)罩?，查看慢查詢?SQL。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-458744.html
5.Sql優(yōu)化
到了這里，關(guān)于數(shù)據(jù)庫(kù)知識(shí)點(diǎn)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
