腳本之家服務器常用軟件

快捷導航

MySQL中的多表查詢與事務操作

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

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

一,多表聯查

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

這時候就需要使用多表聯查.

數據準備:

# 創(chuàng)建部門表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('開發(fā)部'),('市場部'),('財務部'); 
# 創(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) -- 外鍵，關聯部門表(部門表的主鍵)
) 
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);

如果現在我們想知道對應員工所在的部門,就需要聯查,但是如果僅僅select * from emp,dept;

則會把所有的情況羅列出來,但是有效的數據,其實僅僅是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`;

二,子查詢

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

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

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

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

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

2) 有查詢的嵌套，內部的查詢稱為子查詢

3) 子查詢要使用括號

當第一次查詢的結果是多行一列時,還可以用in關鍵字,以下兩種寫法是等價的:

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);

當第一次查詢是多行多列時,則需要將第一次的查詢結果起個別名,例如:查詢出 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'的結果表別名

三,事務

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

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

現在我們模擬張三轉給李四500塊錢,就需要張三減少500,李四增加500來

-- 創(chuàng)建數據表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加數據
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的時候,服務出問題了,李四的賬號并沒有+500 元，數據就出現問題了。我們需要保證其中

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

對于事物,分為手動提交事務和自動提交事務.

3.1,手動提交事務

主要的流程如下:

1,start transaction;開啟事務

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

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

例如：

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

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

3.2,自動提交事務

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

自動提交事務，MySQL 默認開始自動提交事務

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

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

3.3,回滾點

每次回滾總不能都全部回滾,前面操作都已經成功，可以在當前成功的位置設置一個回滾點。

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

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

3.4,事務的隔離級別

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

MySQL 數據庫有四種隔離級別:

需要注意的事情:

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

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

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

我們現在默認使用的都是 root 用戶，超級管理員，擁有全部的權限。

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

所以，我們應該可以根據不同的項目建立不同的用戶，分配不同的權限來管理和維護數據庫。

4.1,創(chuàng)建用戶

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

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

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

4.2,給用戶授權

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

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