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

快捷導(dǎo)航

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

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

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

一,多表聯(lián)查

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

這時候就需要使用多表聯(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 ('開發(fā)部'),('市場部'),('財務(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)在我們想知道對應(yīng)員工所在的部門,就需要聯(lián)查,但是如果僅僅select * from emp,dept;

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

--所有的情況羅列出來,員工表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`;

二,子查詢

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

如果我們一步步來,就需要走兩步:

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

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

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

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

3) 子查詢要使用括號

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

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,則需要將第一次的查詢結(jié)果起個別名,例如:查詢出 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í)行是一個整體，所有的 SQL 語句都必須執(zhí)行成功。如果其中有 1 條 SQL 語句出現(xiàn)異常，則所有的

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

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

-- 創(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);
-- 張三賬號-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號+500
update account set balance = balance + 500 where name='李四';

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

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

對于事物,分為手動提交事務(wù)和自動提交事務(wù).

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

主要的流程如下:

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

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

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

例如：

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

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

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

MySQL 默認(rèn)每一條 DML(增刪改)語句都是一個單獨的事務(wù)，每條語句都會自動開啟一個事務(wù)，語句執(zhí)行完畢

自動提交事務(wù)，MySQL 默認(rèn)開始自動提交事務(wù)

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

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

3.3,回滾點

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

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

--開啟事務(wù)
start transaction;
savepoint 回滾點名字a;
savepoint 回滾點名字b;
rollback to 回滾點名字a;

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

事務(wù)在操作時的理想狀態(tài)：所有的事務(wù)之間保持隔離，互不影響。因為并發(fā)操作，多個用戶同時訪問同一個數(shù)據(jù)。可能引發(fā)并發(fā)訪問的問題：

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

需要注意的事情:

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

隔離級別越高，性能越差，安全性越高。

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

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

但是，一個公司里面的數(shù)據(jù)庫服務(wù)器上面可能同時運行著很多個項目的數(shù)據(jù)庫。

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

4.1,創(chuàng)建用戶

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';

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

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

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

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

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