MySQL中的多表查詢與事務(wù)操作
一,多表聯(lián)查
有些數(shù)據(jù)我們已經(jīng)拆分成多個表,他們之間通過外鍵進行連接.當我們要查詢兩個表的數(shù)據(jù),各取其中的一列或者多列.
這時候就需要使用多表聯(lián)查.
數(shù)據(jù)準備:
# 創(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) 子查詢要使用括號
當?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);
當?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 默認每一條 DML(增刪改)語句都是一個單獨的事務(wù)，每條語句都會自動開啟一個事務(wù)，語句執(zhí)行完畢
自動提交事務(wù)，MySQL 默認開始自動提交事務(wù)
--查看mysql是否開啟自動提交事務(wù),1:開啟,0:未開啟 select @@autocommit; --設(shè)置為不自動提交事務(wù) set @@autocommit=1
這樣之后,每次寫的sql語句不會更新,而是需要執(zhí)行commit后才會更新.
3.3,回滾點
每次回滾總不能都全部回滾,前面操作都已經(jī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)在默認使用的都是 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('新密碼');
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog
這篇文章主要介紹了如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-02-02
MYSQL跨服務(wù)器同步數(shù)據(jù)經(jīng)驗分享
這篇文章主要介紹了MYSQL跨服務(wù)器同步數(shù)據(jù)詳細過程,需要的朋友可以參考下2014-03-03
字符集的概念大家都清楚，校對規(guī)則很多人不了解，一般數(shù)據(jù)庫開發(fā)中也用不到這個概念，mysql在這方便貌似很先進，大概介紹一下2012-07-07
在企業(yè)應(yīng)用中,成熟的業(yè)務(wù)通常數(shù)據(jù)量都比較大,所以需要配置多臺主從數(shù)據(jù)服務(wù)器以實現(xiàn)讀寫分離,本文主要介紹了MySQL主從復(fù)制與讀寫分離的使用示例,感興趣的可以了解一下2023-09-09
windows下MySQL5.6版本安裝及配置過程附有截圖和詳細說明
這篇文章主要介紹了windows下MySQL5.6版本安裝及配置過程附有截圖和詳細說明，需要的朋友可以參考下2013-06-06
本文主要介紹了MySQL鎖情況查看命令，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
這篇文章主要介紹了MySQL?5.7版本中二進制日志（bin_log）的配置和使用,文中通過圖文及代碼介紹的非常詳細,需要的朋友可以參考下2025-02-02
MySQL?數(shù)據(jù)庫的對庫的操作及其數(shù)據(jù)類型
這篇文章主要介紹了MySQL?數(shù)據(jù)庫的對庫的操作及其數(shù)據(jù)類型,下面文字圍繞數(shù)據(jù)庫的對庫的操作及其數(shù)據(jù)類型的相關(guān)資料展開詳細介紹，需要的小伙伴可以參考一下，希望對你有所幫助2021-12-12
用SQL語句解決mysql導入大數(shù)據(jù)文件的問題