MySQL不熟悉知識(shí)點(diǎn)
一哑蔫、基礎(chǔ)部分
-
insert操作
insert into 表名(字段1，字段2) values(值1箕母，值2);
-
update操作
update 表名 set 列名1=值1姑蓝，列名2=值2··· where ···; -
delete操作
delete from 表名 where ···; -
select
image-20201203195411841-
distinct關(guān)鍵字可以去除重復(fù)
image-20201203201819343 -
排序降序DESC
select * from emp order by sal DESC,empno;--sal降序排列，sal相同則按照empno升序 having區(qū)別于where储狭，having在分組后（group by）進(jìn)行過濾
-
limit
select * from emp limit 0,5; --0,5沒有括號(hào)！ -
順序
查詢語句的書寫順序：select-from-where-group by-having-order by-limit
查詢語句的執(zhí)行順序：FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT
-
-
主鍵
-
方式一
create table stu( sid int primary key, sname varchar(20) ); -
方式二
CREATE TABLE student1( sid INT, sname VARCHAR(20), PRIMARY KEY(sid) ); -
方式三：創(chuàng)建聯(lián)合主鍵——同時(shí)參照所有主鍵捣郊，都完全相同視為數(shù)據(jù)不唯一辽狈，所有鍵聯(lián)合為一個(gè)主鍵。如下例呛牲，只有當(dāng)
sid,score這兩個(gè)都相同時(shí)刮萌，才算作主鍵重復(fù)CREATE TABLE student1_1( sid INT, sname VARCHAR(20), score DOUBLE, PRIMARY KEY(sid,score) ); -
追加添加主鍵（alter）
-- 在表格外部給表添加單主鍵 CREATE TABLE student2( sid INT, classid INT, sname VARCHAR(20) ); ALTER TABLE student2 ADD CONSTRAINT PRIMARY KEY(sid); -- 在表格外部給表格添加聯(lián)合主鍵 CREATE TABLE student2_1( sid INT, classid INT, sname VARCHAR(20) ); ALTER TABLE student2_1 ADD PRIMARY KEY(sid,classid); --CONSTRAINT 關(guān)鍵字可以省略
-
-
unique唯一約束，可以有null
CREATE TABLE student3( sid INT , sname VARCHAR(20) UNIQUE ); -
auto_increment自動(dòng)增長列
CREATE TABLE student4( sid INT UNIQUE AUTO_INCREMENT, sname VARCHAR(20) ); CREATE TABLE student4_1( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) ); -
外鍵約束
格式：
constraint 外鍵名稱 foreign key(字段名1,字段名2···) references 從表的名稱(從表主鍵列)CREATE TABLE connStudent( sid INT PRIMARY KEY, sname VARCHAR(20), sbirthday DATE ); CREATE TABLE connScore( id INT, sid INT, sscore DOUBLE, CONSTRAINT fk_connScore_connStudent FOREIGN KEY(sid) REFERENCES connStudent(sid) ); -
連接查詢（實(shí)質(zhì)也是內(nèi)連接娘扩，但不是標(biāo)準(zhǔn)格式）
-
運(yùn)用主外鍵去除無用信息
select * from emp,dept where emp.deptno=dept.deptno;指定別名
SELECT e.ename,e.sal,e.comm,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
-
-
內(nèi)連接（標(biāo)準(zhǔn)格式着茸，inner join ··· on···）
SELECT e.ename,e.sal,e.comm,d.dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno; -
外連接（分為左連接和右連接）
? 外連接可以分為左連接和右連接壮锻，兩種連接方式大同小異，只是左連接按照條件查詢時(shí)參照左邊的表的值涮阔，右連接按照參照條件參照右邊表的值猜绣。當(dāng)兩個(gè)表的查詢條件都滿足時(shí)，查詢出來的結(jié)果與內(nèi)連接相同敬特；當(dāng)查詢只滿足左邊查詢時(shí)掰邢，使用左查詢可以查出表，右表部分使用null填充伟阔，使用右連接無法查詢出這條記錄辣之；當(dāng)查詢只滿足左邊查詢時(shí)道理相似。
? 例子來說明：表中emp表中“張三”這條記錄中皱炉，部門編號(hào)為50怀估，而dept表中不存在部門編號(hào)為50的記錄，所以“張三”這條記錄合搅，不能滿足e.deptno=d.deptno這條件奏夫。但在左連接中，因?yàn)閑mp表是左表历筝，所以左表中的記錄都會(huì)查詢出來，即“張三”這條記錄也會(huì)查出廊谓，但相應(yīng)的右表部分顯示NULL梳猪。
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
因?yàn)樽蟊碇袥]有40這個(gè)部門，所以沒有查出deptno=40這條記錄：
[圖片上傳失敗...(image-45b6df-1609650136719)]
二蒸痹、創(chuàng)建索引
1.建表時(shí)創(chuàng)建索引
2.建表后創(chuàng)建索引（create···on··· 也可以創(chuàng)建復(fù)合索引）
創(chuàng)建復(fù)合索引
3.建表后創(chuàng)建索引（alter：用來更改表格式）
alter table 表名 add 主鍵/唯一索引/普通索引/全文索引(需要加索引的列名)
4.查看索引
show index from 表名;
5.刪除索引
drop index 索引名稱 on 表名;
三春弥、視圖
? **視圖就是一條select語句執(zhí)行后返回的結(jié)果集 **，簡單叠荠、安全匿沛、數(shù)據(jù)獨(dú)立。
- 創(chuàng)建修改視圖（create榛鼎，alter）
create view 視圖名稱 as 查詢語句
alter view 視圖名稱 as 查詢語句
--例如：
create or replace view city_country_view
as
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;
四逃呼、存儲(chǔ)過程和函數(shù)
-
創(chuàng)建存儲(chǔ)過程(無返回值)
create procedure 存儲(chǔ)過程名稱(參數(shù)1，參數(shù)2···) begin --sql語句 end; -
調(diào)用存儲(chǔ)過程
call 存儲(chǔ)過程名稱(); -
寫存儲(chǔ)過程的語法
declare聲明局部變量
set 可以賦值
輸入?yún)?shù)in(默認(rèn))者娱，輸出參數(shù)out,inout既可以作為輸入?yún)?shù)也可以作為輸出參數(shù)
case-when-else 相當(dāng)于switch語句
循環(huán)：while抡笼，repeat-until
-
游標(biāo)：是用來存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲(chǔ)過程和函數(shù)中可以使用光標(biāo)對結(jié)果集進(jìn)行循環(huán)的處理。光標(biāo)的使用包括光標(biāo)的聲明黄鳍、OPEN推姻、FETCH 和 CLOSE
image-20201203212032372例子：
create table emp( id int(11) not null auto_increment , name varchar(50) not null comment '姓名', age int(11) comment '年齡', salary int(11) comment '薪水', primary key(`id`) )engine=innodb default charset=utf8 ; insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹 王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800); -- 查詢emp表中數(shù)據(jù), 并逐行獲取進(jìn)行展示 create procedure pro_test11() begin declare e_id int(11); declare e_name varchar(50); declare e_age int(11); declare e_salary int(11); declare emp_result cursor for select * from emp; open emp_result; fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary); close emp_result; end$ DELIMITER $ create procedure pro_test12() begin DECLARE id int(11); DECLARE name varchar(50); DECLARE age int(11); DECLARE salary int(11); DECLARE has_data int default 1; DECLARE emp_result CURSOR FOR select * from emp; DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; --讀完數(shù)據(jù)觸發(fā) open emp_result; repeat fetch emp_result into id , name , age , salary; select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ',salary); until has_data = 0 end repeat; close emp_result; end$
-
存儲(chǔ)函數(shù)(有返回值)
-
創(chuàng)建語法結(jié)構(gòu)
create function 名稱(參數(shù)1，參數(shù)2···) returns 返回值類型(int,varchar等等) begin ······ end;
-
五框沟、觸發(fā)器
? 觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對象藏古，指在 insert/update/delete 之前或之后增炭，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語句集合
-
創(chuàng)建語法
create trigger 觸發(fā)器名稱 before/after insert/update/delete on 表名稱 begin 對應(yīng)觸發(fā)器的語句，如insert into / update / delete語句 end; show triggers 查看觸發(fā)器
六拧晕、索引
-
避免索引失效
全值匹配
最左前綴法則
-
范圍查詢右邊的列不能使用索引
select * from tb_seller where name='小米科技' and status >'1' and address='北京市'; --address的索引失效隙姿，因?yàn)樵诜秶樵冋Z句的右邊 在索引列上進(jìn)行運(yùn)算，索引失效
-
字符串不加單引號(hào)防症，索引失效：
? 在查詢時(shí)孟辑，沒有對字符串加單引號(hào)，MySQL的查詢優(yōu)化器會(huì)自動(dòng)的進(jìn)行類型轉(zhuǎn)換蔫敲，造成索引失效饲嗽。
盡量使用覆蓋索引，避免select *奈嘿。如果查詢列超出索引列貌虾，也會(huì)降低性能
用or分割開的條件， 如果or前的條件中的列有索引裙犹，而后面的列中沒有索引尽狠，那么涉及的索引都不會(huì)被用到。
以%開頭的like模糊查詢索引失效叶圃，如果僅僅是尾部模糊匹配袄膏，索引不會(huì)失效
in走索引，not in索引失效
······
七掺冠、SQL優(yōu)化
-
大批量插入數(shù)據(jù)優(yōu)化
按主鍵順序插入
-
關(guān)閉唯一性校驗(yàn)
? 在導(dǎo)入數(shù)據(jù)前執(zhí)行 SET UNIQUE_CHECKS=0沉馆，關(guān)閉唯一性校驗(yàn)，在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1德崭，恢復(fù)唯一性校驗(yàn)斥黑，可以提高導(dǎo)入的效率。
-
手動(dòng)提交事務(wù)
? 如果應(yīng)用使用自動(dòng)提交的方式眉厨，建議在導(dǎo)入前執(zhí)行 SET AUTOCOMMIT=0锌奴，關(guān)閉自動(dòng)提交，導(dǎo)入結(jié)束后再執(zhí)行 SET AUTOCOMMIT=1憾股，打開自動(dòng)提交鹿蜀，也可以提高導(dǎo)入的效率。
-
優(yōu)化select語句
- 一次性insert into服球，在事務(wù)中進(jìn)行數(shù)據(jù)插入耻姥，數(shù)據(jù)有序插入
優(yōu)化order by語句
-
優(yōu)化group by語句
group by 實(shí)際上也會(huì)進(jìn)行排序，與order by 相比只是多了分組工作
-
order by null禁止排序
explain select age,count(*) from emp group by 1 age order by null;
-
優(yōu)化嵌套查詢：子查詢變?yōu)檫B接查詢
-
優(yōu)化or
- OR之間的每個(gè)條件列都必須用到索引 有咨， 而且不能使用到復(fù)合索引琐簇； 如果沒有索引，則應(yīng)該考慮增加索引
- 使用union替換or
-
優(yōu)化分頁查詢
- 在索引上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容婉商。
- 對于主鍵自增的表似忧，可以把limit替換為范圍查詢
八、緩存失效情況
sql語句必須要完全一致(字母大小寫也要一樣)丈秩，這樣才會(huì)走緩存
查詢語句中有些不確定時(shí)盯捌，不會(huì)走緩存，如：now(),current_date()······
-
不使用任何表查詢語句
select 'A'; 查詢mysql蘑秽，information_schema或者performance_schema數(shù)據(jù)庫表時(shí)不走查詢緩存
在存儲(chǔ)函數(shù)饺著，觸發(fā)器或事件的主題內(nèi)執(zhí)行的查詢不走緩存
如果表更改，則使用該表的所有高速緩存查詢都將變?yōu)闊o效并從高速緩存中刪除
九肠牲、事務(wù)與鎖
-
ACID屬性
image-20201204093136316-
臟讀
臟讀是指一個(gè)事務(wù)在處理數(shù)據(jù)的過程中幼衰，讀取到另一個(gè)為提交事務(wù)的數(shù)據(jù)。
--原數(shù)據(jù) --id name --1 lisi --事務(wù)1 START TRANSACTION缀雳； updata t_table set name = 'wangwu' where id = 1; --此時(shí)事務(wù)2查詢id = 1 ROLLBACK渡嚣； --事務(wù)2 select * from t_table where id = 1; --查詢到 id = 1， name = 'wangwu'? 事務(wù)1并沒有提交肥印，name 還是 lisi识椰，但是事務(wù)2卻讀到了 name = wangwu，這就是臟讀深碱。如果換成A給B轉(zhuǎn)賬腹鹉，B查詢到了沒有提交的事務(wù)，認(rèn)為已經(jīng)收到A轉(zhuǎn)過來的錢敷硅，那豈不是很恐怖种蘸。
-
不可重復(fù)讀
? 不可重復(fù)讀是指對于數(shù)據(jù)庫中的某個(gè)數(shù)據(jù)，一個(gè)事務(wù)范圍內(nèi)的多次查詢卻返回了不同的結(jié)果竞膳，這是由于在查詢過程中，數(shù)據(jù)被另外一個(gè)事務(wù)修改并提交了诫硕。
--原數(shù)據(jù) --id name --1 lisi --事務(wù)1 select * from t_table where id = 1; -- 查詢到 id = 1, name = list, 事務(wù)2在此時(shí)提交 select * from t_table where id = 1; -- 查詢到 id = 1, name = wangwu --事務(wù)2 start transaction; update t_table set name = 'wangwu' where id = 1; COMMIT;? 不可重復(fù)讀和臟讀的區(qū)別是坦辟，臟讀讀取到的是一個(gè)未提交的數(shù)據(jù)，而不可重復(fù)讀讀取到的是前一個(gè)事務(wù)提交的數(shù)據(jù)章办。
? 而不可重復(fù)讀在一些情況也并不影響數(shù)據(jù)的正確性锉走，比如需要多次查詢的數(shù)據(jù)也是要以最后一次查詢到的數(shù)據(jù)為主。
-
幻讀
? 幻讀是事務(wù)非獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象藕届。例如事務(wù)T1對一個(gè)表中所有的行的某個(gè)數(shù)據(jù)項(xiàng)做了從“1”修改為“2”的操作挪蹭，這時(shí)事務(wù)T2又對這個(gè)表中插入了一行數(shù)據(jù)項(xiàng)，而這個(gè)數(shù)據(jù)項(xiàng)的數(shù)值還是為“1”并且提交給數(shù)據(jù)庫休偶。而操作事務(wù)T1的用戶如果再查看剛剛修改的數(shù)據(jù)梁厉，會(huì)發(fā)現(xiàn)還有一行沒有修改，其實(shí)這行是從事務(wù)T2中添加的，就好像產(chǎn)生幻覺一樣词顾，這就是發(fā)生了幻讀八秃。
? 幻讀和不可重復(fù)讀都是讀取了另一條已經(jīng)提交的事務(wù)（這點(diǎn)就臟讀不同），所不同的是不可重復(fù)讀查詢的都是同一個(gè)數(shù)據(jù)項(xiàng)肉盹，而幻讀針對的是一批數(shù)據(jù)整體（比如數(shù)據(jù)的個(gè)數(shù)）昔驱。
--原數(shù)據(jù) --id name --1 lisi --事務(wù)1 select * from t_table where id = 2; --返回NULL,此時(shí)事務(wù)2提交 select * from t_table where id = 2; --返回id = 2, name = wangwu --事務(wù)2 insert into t_table values(2,'wangwu'); COMMIT;總的來說，解決不可重復(fù)讀的方法是 鎖行上忍，解決幻讀的方式是 鎖表骤肛。
-
-
并發(fā)事務(wù)帶來的問題
image-20201204093229137 -
事務(wù)隔離級(jí)別
image-20201204093358856 innodb引擎，無索引窍蓝，行鎖升級(jí)為表鎖
間隙鎖
