索引

此時是要獲取所有的數據，是否有索引作用不大
select * from emp
– 因為數據庫以及自動的給主鍵列empno創建了索引，查詢條件中出現empno，自動使用索引進行查詢
– 是否使用索引，語句沒有區別
select * from emp where empno = 7654;

– 因為沒有給ename列創建索引，此時是逐個查詢比較，效率低下
select * from emp where ename = ‘BLAKE’
– 創建一個索引，默認升序排列
create index index_emp_ename on emp(ename)
– 因為已經創建了索引，所以此時使用索引進行查詢，效率提升
select * from emp where ename = ‘BLAKE’

– 顯示索引
show index from emp;

– 刪除索引
drop index index_emp_ename on emp
alter table emp drop index index_emp_ename

– 沒有給sal、hiredate創建索引，效率低下
select * from emp order by sal ,hiredate

– 創建索引
create index index_emp_sal_hiredate on emp (sal ,hiredate desc)

– 使用索引，效率提升
select * from emp order by sal ,hiredate

事務

– 創建account賬戶表
create table account(
id int primary key auto_increment,
username varchar(30) not null,
balance double
);
– 為account賬戶表同時插入兩條數據
insert into account (username, balance) values(‘張三’, 2000),(‘李四’, 2000);
– 查看account賬戶表中的數據
select * from account;

– 轉賬操作
– 默認事務自動提交，一條dml語句是一個事務
update account set balance= balance-1000 where id =1

update account set balance = balance +1000 where id=2

– 如果要多個操作是一個事務，需要事務必須手動的提交
– 事務開始，成功不會自動提交，失敗也不會自動回滾
start transaction

update account set balance= balance-1000 where id =1

update account set balance = balance1 +1000 where id=2

– 手動提交
commit
– 手動回滾
rollback;

mysql 查詢、設置事務隔離級別 transaction_isolation
select @@transaction_isolation

– 設置當前會話的事務級別為 READ-COMMITTED
set session transaction isolation level read uncommitted
set session transaction isolation level read committed
set session transaction isolation level repeatable read

set session transaction isolation level read uncommitted

select * from dept

視圖

– 創建單表視圖
create or replace view myview1
as
select empno,ename,job,mgr,hiredate,deptno from emp
where hiredate <‘1981-09-28’
with check option

select * from myview1

select empno,ename,job from myview1 where job =‘MANAGER’

insert into myview1 values(9999,‘9999’,‘9999’,7839,‘1980-12-23’,30)
– 1369 - CHECK OPTION failed ‘mydb.myview1’
insert into myview1 values(1000,‘9999’,‘9999’,7839,‘1999-12-23’,30)

select * from emp

– 創建多表視圖
create or replace view myview2
as
select e.empno,e.ename,e.sal,e.comm,d.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal>2500

select * from myview2

– 創建統計視圖
create or replace view myview3
as
select e.deptno 部門編號, d.dname 部門名稱,avg(sal) 平均工資,max(sal) 最高工資,count(*) 部門人數
from emp e
join dept d
using(deptno)
where deptno is not null
group by e.deptno

select * from myview3

– 創建基于視圖的視圖
create or replace view myview4
as
select * from myview3 where 最高工資>=3000

select * from myview4

總結

以上是生活随笔為你收集整理的索引，事务，视图的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。