MySQL中的多表查詢與事務(wù)操作
一,多表聯(lián)查
有些數(shù)據(jù)我們已經(jīng)拆分成多個表,他們之間通過外鍵進(jìn)行連接.當(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é)果再次作為查詢的條件進(jìn)行查詢:
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ù)??赡芤l(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)限來管理和維護(hù)數(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('新密碼');
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
MYSQL 完全備份、主從復(fù)制、級聯(lián)復(fù)制、半同步小結(jié)
這篇文章主要介紹了MYSQL 完全備份、主從復(fù)制、級聯(lián)復(fù)制、半同步小結(jié),小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2019-05-05Redhat7.3安裝MySQL8.0.22的詳細(xì)教程(二進(jìn)制安裝)
這篇文章主要介紹了Redhat7.3安裝MySQL8.0.22(二進(jìn)制安裝),本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-01-01Centos7下安裝MySQL8.0.23的步驟(小白入門級別)
這篇文章主要介紹了Centos7下安裝MySQL8.0.23的步驟(小白入門級別),本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-01-01IDEA連接mysql又報錯!Server returns invalid timezone. Go to tab an
這篇文章主要介紹了IDEA連接mysql又報錯!Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' prope問題,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),需要的朋友可以參考下2020-05-05