1、高級查詢

(1)了解笛卡爾積：笛卡爾乘積是指在數(shù)學中，兩個集合X和Y的笛卡爾積(Cartesian product)，又稱直積，表示為X?×?Y，第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員，笛卡爾積在SQL中的實現(xiàn)方式既是交叉連接(Cross Join)。所有連接方式都會先生成臨時笛卡爾積表，笛卡爾積是關系代數(shù)里的一個概念，表示兩個表中的每一-行數(shù)據(jù)任意組合。

(2) 關聯(lián)查詢(連接查詢)

(a)內(nèi)連接：內(nèi)連接與連接順序無關

關聯(lián)表中都出現(xiàn)的字段值最終才能出現(xiàn)再結果集中

SELECT * FROM app_order_base a,app_order_details b WHERE a.id=b.goods_id

SELECT * FROM app_order_base a INNER JOIN app_order_details b on a.id=b.goods_id

#通用列字段名稱必須一致，可以去掉重復字段

SELECT * FROM app_order_base a INNER JOIN app_order_details USING(id)

(b)外連接：有主從表之分，與連接順序有關

依次遍歷主表中記錄，與從表中記錄進行匹配;如果匹配到則連接展 示，否則以null填充。

左外連接：left[outer] join on

SELECT * FROM app_order_base a LEFT JOIN app_order_details b on a.id=b.goods_id

右外連接：right[outer] join on

SELECT * FROM app_order_base a RIGHT JOIN app_order_details b on a.id=b.goods_id

自然連接(特殊連接)：

自然連接肯定是等值連接，但是等值連接不一定是自然連接

SELECT * FROM app_order_base NATURAL JOIN app_order_details;

查看數(shù)據(jù)庫編碼

#查看數(shù)據(jù)庫編碼

SHOW VARIABLES LIKE '%char%'

(3)子查詢(嵌套查詢)：

嵌套查詢：將一個查詢的結果當作另一個查詢的條件或者結果集。

子查詢比較接近思考方式，最自然的查詢。

分類

(a)單行子查詢:子查詢的結果有一條

(b)多行子查詢:子查詢的結果有多條

in: in(value,value)

any: =any 相當于in

>any 大于最小值

all： >all 大于最大值

#in和exists的區(qū)別：

in 先執(zhí)行子查詢，將結果返回給主查詢，主查詢繼續(xù)執(zhí)行

exists 先執(zhí)行主查詢，將主查詢的值依次在子查詢中進行匹配，根據(jù)是否匹配返回true和false，如果是true值連接展示，否則不展示

#多表和子查詢

子查詢 --->查詢條件和結果放在一張表中。

查詢結果分布于多張表

關聯(lián)查詢

(4)聯(lián)合查詢：

關鍵字：union/union all union可以去除重復

2、存儲引擎和事務

(1)存儲引擎(show ENGINES)：數(shù)據(jù)庫底層軟件組織，dbms通過存儲引擎實現(xiàn)對數(shù)據(jù)的操作，MySQL核心就是存儲引擎。

MySQL中可以設置多種存儲引擎，不同的存儲引擎在索引，存儲，以及策略上是不同的。

數(shù)據(jù)庫存儲引擎是數(shù)據(jù)庫底層軟件組織，數(shù)據(jù)庫管理系統(tǒng)(DBMS)使用數(shù)據(jù)引擎進行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。不同的存儲引擎提供不同的存儲機制、素引技巧、鎖定水平等功能，使用不同的存儲引擎，還可以獲得特定的功能。Mysql 的核心就是存儲引擎。

InnoDB是事務型數(shù)據(jù)庫的首選，執(zhí)行安全性數(shù)據(jù)庫，行鎖定和外鍵。mysql5.5 之后默認使用。

MyISAM插入速度和查詢效率較高，但不支持事務。

MEMORY將表中的數(shù)據(jù)存儲在內(nèi)存中，速度較快。

各個存儲引擎的不同之處：

功能

MyISAM

MEMORY

InnoDB

Archive

存儲限制

256TB

RAM

64TB

None

支持事務

N

N

Y

N

支持全文索引

Y

N

N

N

支持數(shù)索引

Y

Y

Y

N

支持哈希索引

N

Y

N

N

支持數(shù)據(jù)緩存

N

N/A

Y

N

支持外鍵

N

N

Y

N

mysql5.5以前默認存儲引擎為MyISAM：支持全文搜索，不支持事務。

mysql5.5+默認存儲引擎為INNODB：支持事務、行級鎖。

(2)事務：保持數(shù)據(jù)一致性，一組DML操作要么同時成功，要么同時失敗。

(a)事務的acid特性：

原子性：放在同一事務的一組操作時不可分割的；

一致性：在事務的執(zhí)行前后整體的狀態(tài)保持不變；

隔離性：并發(fā)事務之間相互不干擾；

持久性：事務執(zhí)行之后將永久化到數(shù)據(jù)庫；

(b) 事務語法(數(shù)據(jù)庫中)：MySQL默認采用事務自動提交。

#查看MySQL的事務自動提交

show variables like 'autocommit'

#修改自動提交

set autocommit = 0; #手動提交事務

set autocommit = 1; #自動提交事務

#顯示開啟事務(begin)：

start transaction;

#手動提交事務或回滾

commit; #提交

rollback #回滾

(c)并發(fā)事務產(chǎn)生的問題

臟讀：一個事務執(zhí)行范圍內(nèi)讀到了另一條未提交的數(shù)據(jù)；

不可重復讀：一個事務在只讀范圍內(nèi)，被另一事務修改并提交事務，導致多次讀取事務不一致的問題。

幻讀(虛讀)：一個事務只讀范圍內(nèi)，被另一事務刪除或者添加數(shù)據(jù)，導致讀取數(shù)據(jù)不一致問題。

(d)事務隔離級別：

讀未提交：不能處理任何問題；

讀已提交：解決臟讀問題；

可重復讀：解決臟讀和不可重復讀問題；

串行化：解決所有問題，效率較低；

#查看事務隔離級別

SELECT @@tx_isolation

#修改事務隔離級別

set session transaction isolation level

3、存儲程序

(1)、運行于服務器端程序

(2)、優(yōu)點：

簡化開發(fā)，執(zhí)行效率較高，

(3)缺點：

程序保存在服務器端，占用服務器資源

數(shù)據(jù)遷移的時候考慮遷移所有的存儲程序

調(diào)試編寫程序不方便

(4)、分類

存儲過程：服務器端運行的可重復調(diào)用的sql代碼塊，包含名稱，輸入輸出參數(shù)，一組sql。

#修改結束標志

delimiter //;

#創(chuàng)建存儲過程

create procedure sel_emp()

begin

#sql

select * from student;

end;

#存儲過程調(diào)用

call sel_emp();

#參數(shù)的傳入

delimiter //;

create procedure findEmpByNo(in eno int)

begin

selete * from emp where deptno = dno;

end;

call findEmpByNo(20);

#參數(shù)的輸出

delimiter //;

create procedure findNameByNo(in eno int,out v_name varchar(20))

begin

select ename into v_name from emp where empno = eno;

end;

call findNameByNo(7788,@v_name);

select @v_name;

存儲函數(shù)：存儲在服務器端，有返回值，函數(shù)作為sql一部分使用。

delimiter //;

create function findNameByNo(eno int)

returns varchar(20) #返回值類型

DETERMINISTIC #確定的

begin

declare v_name varchar(20);#與上邊定義的一致

select ename from where empon=eno;

return v_name;

end;

#調(diào)用

select findNameByNo(7788);

函數(shù)和存儲過程的區(qū)別：

關鍵字不同

存儲過程三種參數(shù)模式實現(xiàn)數(shù)據(jù)輸入輸出，函數(shù)有返回值返回數(shù)據(jù)

存儲過程可以作為單獨個體執(zhí)行，函數(shù)只能作為sql的一部分執(zhí)行

觸發(fā)器

存儲程序，存儲再服務器端

由事件(增刪改)調(diào)用，不能傳參

不要添加過多的觸發(fā)器(降低效率)

存儲程序中不能使用事務控制

#創(chuàng)建觸發(fā)器

delimiter //;

create trigger tri_user

after delete

on userinfo for each row

begin

#old new

insert into user_bak values(old.uid,old.uname,old.password)

end;

4、清除表中數(shù)據(jù)的方式：

(1)、清空全部數(shù)據(jù)，不寫日志，不可恢復，速度很快

truncate table 表名;·

(2)、清空全部數(shù)據(jù)，寫日志，可恢復，速度很慢

delete from 表名;

本文地址：https://blog.csdn.net/Java_xiaoxinxin/article/details/107319331

如您對本文有疑問或者有任何想說的，請點擊進行留言回復，萬千網(wǎng)友為您解惑！

總結

以上是生活随笔為你收集整理的mysql 事b务 查询_MySQL进阶学习笔记二（包括连接查询、子查询、联合查询、事务、存储过程）...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。