MYSQL多表查詢與事務
- 2019 年 10 月 10 日
- 筆記
多表查詢
避免笛卡爾積
select * from emp,dept where emp.`dept_id` = dept.`id`;#設置過濾條件（隱式內連接）
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;#查詢姓名和部門
select * from emp e inner join dept d on e.`dept_id` = d.`id`;#顯式內連接
select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';#添加過濾條件
select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';顯式內連接並顯示指定列
select e.`id` 編號,e.`name` 姓名,e.`gender` 性別,e.`salary` 工資,d.`name` 部門名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';#修正表頭
select * from dept d left join emp e on d.`id` = e.`dept_id`;#左外連接查詢。
select * from dept right join emp on dept.`id` = emp.`dept_id`;#右外連接查詢
嵌套查詢
select * from emp where dept_id = (select id from dept where name='市場部');#使用子查詢
子查詢只有一個值的時候
select * from emp where salary = (select max(salary) from emp);#查詢最高薪水對應的姓名
select * from emp where salary < (select avg(salary) from emp);#小於平均工資的員工
子查詢有多個值的時候使用in
select name from dept where id in (select dept_id from emp where salary > 5000);#薪水大於5k的部門
select * from emp where dept_id in (select id from dept where name in('開發部','財務部'));#查詢部門人員
子查詢是多行多列的時候使用FROM後面作為表進行二次查詢
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;#子查詢作為表需要取別名，否則這張表沒有名稱則無法訪問表中的字段
或：
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';
事務
手動提交事務
start transaction;
update account set balance = balance – 500 where name='張三';
update account set balance = balance + 500 where name='李四';
commit;#執行完commit數據才真正發生改變
start transaction;
update account set balance = balance – 500 where name='張三';
rollback;#執行過程中使用rollback表示執行錯誤並回滾到原始狀態
自動提交事務
查看是否自動提交：
select @@autocommit;# @@表示全局變量，結果為1表示開啟，0表示關閉。
set @@autocommit=0;#關閉自動提交
update account set balance=balance+500 where id=2;
commit;#手動提交
回滾點
在某些成功的操作完成之後，後續的操作有可能成功有可能失敗，但是不管成功還是失敗，前面操作都已經成功，可以在當前成功的位置設置一個回滾點。可以供後續失敗操作返回到該位置，而不是返回所有操作，這個點稱之為回滾點。
事務與隔離
set global transaction isolation level read uncommitted;#設置事務隔離級別為讀未提交
讀未提交時，當A對數據進行操作但未提交時，B讀取的信息是未提交信息，可能不一致。
讀已提交時，當A對數據進行操作但未提交時，B讀取的信息是原始信息，不出現臟讀。
讀已提交時，當A對數據進行操作已提交時，B讀取的信息在A提交前後兩次讀取不一致，出現不可重複讀。
使用serializable隔離級別，一個事務沒有執行完，其他事務的SQL執行不了，可以擋住幻讀
DCL語句
mysqld是MySQL的主程序，服務器端。mysql是MySQL的命令行工具，客戶端。
創建用戶user1密碼123隻能在本機使用：
create user 'user1'@'localhost' identified by '123';
創建用戶user1密碼123可以在任何計算機使用：
create user 'user2'@'%' identified by '123';
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';#給user1分配到test數據庫的部分權限
grant all on *.* to 'user2'@'%';#給user2分配到所有數據庫的所有表權限
revoke all on test.* from 'user1'@'localhost';#撤銷user1用戶對test數據庫所有表的操作權限
show grants for 'user1'@'localhost';#查詢用戶權限，usage是指登錄權限
drop user 'user1'@'localhost';#刪除用戶user1
mysqladmin -uroot -p password 1#修改管理員的用戶密碼為1(登出狀態使用)
set password for 'user2'@'%'=password('1');#修改普通用戶user1的密碼為1
