腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySQL中的多表查詢與事務(wù)操作

更新時(shí)間：2024年03月20日 09:27:02 作者：笑道三千

這篇文章主要介紹了MySQL中的多表查詢與事務(wù)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

一,多表聯(lián)查

有些數(shù)據(jù)我們已經(jīng)拆分成多個(gè)表,他們之間通過(guò)外鍵進(jìn)行連接.當(dāng)我們要查詢兩個(gè)表的數(shù)據(jù),各取其中的一列或者多列.

這時(shí)候就需要使用多表聯(lián)查.

數(shù)據(jù)準(zhǔn)備:

# 創(chuàng)建部門表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('開(kāi)發(fā)部'),('市場(chǎng)部'),('財(cái)務(wù)部'); 
# 創(chuàng)建員工表
create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- 性別
 salary double, -- 工資
 join_date date, -- 入職日期
 dept_id int,
 foreign key (dept_id) references dept(id) -- 外鍵，關(guān)聯(lián)部門表(部門表的主鍵)
) 
insert into emp(name,gender,salary,join_date,dept_id) values('孫悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('豬八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);

如果現(xiàn)在我們想知道對(duì)應(yīng)員工所在的部門,就需要聯(lián)查,但是如果僅僅select * from emp,dept;

則會(huì)把所有的情況羅列出來(lái),但是有效的數(shù)據(jù),其實(shí)僅僅是emp.dept_id= dept.id,也就是外鍵等于主鍵的情況.

--所有的情況羅列出來(lái),員工表4*部門表3=12列
select * from emp,dept;
--滿足條件的情況,只有四條
select * from emp,dept where emp.`dept_id` = dept.`id`;
--只展示員工名字和部門名
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

二,子查詢

有的時(shí)候,我們需要先查一個(gè)表,得到結(jié)果后才能查詢另一個(gè)表.比如說(shuō),我們現(xiàn)在要查詢:開(kāi)發(fā)部中有哪些員工.

如果我們一步步來(lái),就需要走兩步:

select id from dept where name='開(kāi)發(fā)部' ;
select * from emp where dept_id = 1;

如果采用子查詢,就是把查詢的結(jié)果再次作為查詢的條件進(jìn)行查詢:

1) 一個(gè)查詢的結(jié)果做為另一個(gè)查詢的條件

2) 有查詢的嵌套，內(nèi)部的查詢稱為子查詢

3) 子查詢要使用括號(hào)

當(dāng)?shù)谝淮尾樵兊慕Y(jié)果是多行一列時(shí),還可以用in關(guān)鍵字,以下兩種寫法是等價(jià)的:

select name from dept where id =(select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

當(dāng)?shù)谝淮尾樵兪嵌嘈卸嗔袝r(shí),則需要將第一次的查詢結(jié)果起個(gè)別名,例如:查詢出 2011 年以后入職的員工信息，包括部門名稱.

select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;
--d是dept表的別名
--e是select * from emp where join_date >='2011-1-1'的結(jié)果表別名

三,事務(wù)

事務(wù)執(zhí)行是一個(gè)整體，所有的 SQL 語(yǔ)句都必須執(zhí)行成功。如果其中有 1 條 SQL 語(yǔ)句出現(xiàn)異常，則所有的

SQL 語(yǔ)句都要回滾，整個(gè)業(yè)務(wù)執(zhí)行失敗。

現(xiàn)在我們模擬張三轉(zhuǎn)給李四500塊錢,就需要張三減少500,李四增加500來(lái)

-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加數(shù)據(jù)
INSERT INTO account (NAME, balance) VALUES ('張三', 1000), ('李四', 1000);
-- 張三賬號(hào)-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號(hào)+500
update account set balance = balance + 500 where name='李四';

但是如果在執(zhí)行到張三賬戶-500的時(shí)候,服務(wù)出問(wèn)題了,李四的賬號(hào)并沒(méi)有+500 元，數(shù)據(jù)就出現(xiàn)問(wèn)題了。我們需要保證其中

一條 SQL 語(yǔ)句出現(xiàn)問(wèn)題，整個(gè)轉(zhuǎn)賬就算失敗。只有兩條 SQL 都成功了轉(zhuǎn)賬才算成功。這個(gè)時(shí)候就需要用到事務(wù)。

對(duì)于事物,分為手動(dòng)提交事務(wù)和自動(dòng)提交事務(wù).

3.1,手動(dòng)提交事務(wù)

主要的流程如下:

1,start transaction;開(kāi)啟事務(wù)

2,若全部sql語(yǔ)句執(zhí)行正常,則提交事務(wù):commit;

3,如有sql語(yǔ)句執(zhí)行出問(wèn)題,則回滾所有事務(wù)中的sql語(yǔ)句:rollback;

例如：

--開(kāi)啟事務(wù)
start transaction;
-- 張三賬號(hào)-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號(hào)+500
update account set balance = balance + 500 where name='李四';
rollback;

就會(huì)發(fā)現(xiàn)這兩條修改賬戶余額的sql都回滾了.

3.2,自動(dòng)提交事務(wù)

MySQL 默認(rèn)每一條 DML(增刪改)語(yǔ)句都是一個(gè)單獨(dú)的事務(wù)，每條語(yǔ)句都會(huì)自動(dòng)開(kāi)啟一個(gè)事務(wù)，語(yǔ)句執(zhí)行完畢

自動(dòng)提交事務(wù)，MySQL 默認(rèn)開(kāi)始自動(dòng)提交事務(wù)

--查看mysql是否開(kāi)啟自動(dòng)提交事務(wù),1:開(kāi)啟,0:未開(kāi)啟
select @@autocommit;
--設(shè)置為不自動(dòng)提交事務(wù)
set @@autocommit=1

這樣之后,每次寫的sql語(yǔ)句不會(huì)更新,而是需要執(zhí)行commit后才會(huì)更新.

3.3,回滾點(diǎn)

每次回滾總不能都全部回滾,前面操作都已經(jīng)成功，可以在當(dāng)前成功的位置設(shè)置一個(gè)回滾點(diǎn)。

可以供后續(xù)失敗操作返回到該位置，而不是返回所有操作，這個(gè)點(diǎn)稱之為回滾點(diǎn)。

--開(kāi)啟事務(wù)
start transaction;
savepoint 回滾點(diǎn)名字a;
savepoint 回滾點(diǎn)名字b;
rollback to 回滾點(diǎn)名字a;

3.4,事務(wù)的隔離級(jí)別

事務(wù)在操作時(shí)的理想狀態(tài)：所有的事務(wù)之間保持隔離，互不影響。因?yàn)椴l(fā)操作，多個(gè)用戶同時(shí)訪問(wèn)同一個(gè)數(shù)據(jù)?？赡芤l(fā)并發(fā)訪問(wèn)的問(wèn)題：

MySQL 數(shù)據(jù)庫(kù)有四種隔離級(jí)別:

需要注意的事情:

--查看隔離級(jí)別
select @@tx_isolation;
--設(shè)置隔離級(jí)別
set global transaction isolation level 級(jí)別字符串;
--示例設(shè)置隔離級(jí)別為read committed
set global transaction isolation level read committed;

隔離級(jí)別越高，性能越差，安全性越高。

四,mysql用戶角色的創(chuàng)建

我們現(xiàn)在默認(rèn)使用的都是 root 用戶，超級(jí)管理員，擁有全部的權(quán)限。

但是，一個(gè)公司里面的數(shù)據(jù)庫(kù)服務(wù)器上面可能同時(shí)運(yùn)行著很多個(gè)項(xiàng)目的數(shù)據(jù)庫(kù)。

所以，我們應(yīng)該可以根據(jù)不同的項(xiàng)目建立不同的用戶，分配不同的權(quán)限來(lái)管理和維護(hù)數(shù)據(jù)庫(kù)。

4.1,創(chuàng)建用戶

CREATE USER '用戶名'@'主機(jī)名' IDENTIFIED BY '密碼';

示例,創(chuàng)建用戶1,讓他能在任何電腦登錄,密碼為123:

create user 'user1'@'%' identified by '123';

4.2,給用戶授權(quán)

新創(chuàng)建的用戶是沒(méi)有權(quán)限的,需要另外給他授權(quán).

--權(quán)限有: CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的權(quán)限則使用 ALL
GRANT 權(quán)限 1, 權(quán)限 2... ON 數(shù)據(jù)庫(kù)名.表名 TO '用戶名'@'主機(jī)名';
--數(shù)據(jù)庫(kù)名.表名:該用戶可以操作哪個(gè)數(shù)據(jù)庫(kù)的哪些表。如果要授予該用戶對(duì)所有數(shù)據(jù)庫(kù)和表的相應(yīng)操作權(quán)限則可用*表示，如*.*
--示例:
grant CREATE,ALTER,SELECT,INSERT,UPDATE on db2.account to 'user1'@'%';
--查看用戶權(quán)限
SHOW GRANTS FOR '用戶名'@'主機(jī)名';
--撤銷用戶授權(quán)
revoke all on 數(shù)據(jù)庫(kù)名.表名 from '用戶名'@'主機(jī)名';
--刪除用戶
DROP USER '用戶名'@'主機(jī)名';
--修改管理員登錄密碼(未登錄情況)
mysqladmin -uroot -p password 新密碼
--修改普通用戶密碼
set password for '用戶名'@'主機(jī)名' = password('新密碼');