Mysql - 05

目录

1   多表的连接查询

2   标准表连接语法

2.1     外连接

3   事务

3.1     事务四个特性：ACID

3.2     事务操作

3.2.1 开始事务

3.2.2 提交事务

3.2.3 回滚事务

3.3     多个事务并发执行时数据访问问题

3.3.1 脏数据

3.3.2 不可重复读

3.3.3 幻读

3.4 事务隔离级别

4   视图 view

4.1     创建视图

4.2     查看视图

4.3     删除视图

5   索引

5.1     索引的数据结构 B+Tree(了解)

5.2     创建索引

5.3     查看索引

5.4     删除索引

---

1   多表的连接查询

按指定的连接条件，把两张表，连接成一张表

 

l  自连接
一张表，看做是两张表

 

部门表

desc departments;

select * from departments;

 

查询员工，显示部门名称

select

 e.employee_id,e.first_name,e.salary,

 d.department_id,d.department_name

from

   employees e,departments d

where

   e.department_id=d.department_id;

 

 

地区表

desc locations;

select * from locations;

 

查询部门，显示部门所在的城市

select

   d.department_id,d.department_name,

   l.city

from

   departments d,locations l

where

   d.location_id=l.location_id;

 

 

 

查询部门，显示部门经理的名字

select

   d.department_id,d.department_name,

   e.first_name mgr

from

   departments d, employees e

where

   d.manager_id=e.employee_id;

 

 

查询员工，显示他上级主管名

select

   e1.employee_id,e1.first_name,e1.salary,

e2.first_name mgr

from

   employees e1, employees e2

where

   e1.manager_id=e2.employee_id;

查询员工，显示部门名和城市

select

   e.employee_id,e.first_name,

   d.department_name,

   l.city          

from

   employees e,

departments d,

locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

 

l  外连接语法

n  语法不统一

n  mysql 不提供外连接语法

n  oracle

u  a.c1*=b.c2

u  a.c1=*b.c2

n  其他

u  a.c1(+)  =  b.c2

u  a.c1  =  b.c2(+)

---

2   标准表连接语法

l  上面的非标准语法，也是事实标准，所有数据库都支持

l  外连接，每个数据库厂商都有各自的语法

l  为了统一外连接语法，才设计了标准表连接语法

 

2.1     外连接

l  内连接

只查询符合连接条件的数据，连接条件以内的数据

l  外连接
连接条件以外的数据，也查询出来

n  左外连接

u  左侧表中不符合连接条件的数据也查询出来

n  右外连接

u  右侧表中不符合连接条件的数据也查询出来

n  全外连接

u  两侧表中不符合连接条件的数据都查询出来

 

l  select ...
from a

inner join b on(连接条件)

inner join c on(连接条件)

join d on(连接条件)

n  inner 可以省略

l  左外连接

select ...

from a

   left outer join b on(连接条件)  

n  outer 可以省略

l  右外连接

select ...

from a

   right join b on(连接条件)

l  全外连接

n  mysql 不支持

n  full outer join

外连接测试

查询107个员工，显示部门名，没有部门显示null

select

   e.employee_id,e.first_name,

   d.department_name

from

   employees e

left join departments d

on (e.department_id=d.department_id);

 

查询所有27个部门，有经理显示经理名，没有显示null

select

d.department_id,d.department_name,

e.first_name mgr

from departments d

  left join employees e

  on (d.manager_id=e.employee_id);

 

每个城市的人数，不知道哪个城市，也要显示null

select l.city, count(*)

from

employees e

left join departments d

on (e.department_id=d.department_id)

left join locations l

on (d.location_id=l.location_id)

group by l.city;

---

3   事务

事务由一组sql语句组成，要么整体成功，要么整体失败

转账

A --> B

第一步

update user set money=money-100

where id='A'

第二步

update user set money=money+100

where id='B'

如果第二步失败，第一步修改也不能生效

 

3.1     事务四个特性：ACID

A - 原子性 Atomic

   数据操作的最小单元是事务，而不是SQL语句 

C - 一致性 Consistency

转账前 a+b = 100

转帐后 a+b = 100

I - 隔离性 Isolation

一个事物进行中时，另一事物不能操作数据

D - 持久性 Durancy

事务没有提交之前，数据操作只保存在日志文件中

提交事务之后，数据持久生效

 

3.2     事务操作

l  开始事务

l  提交事务

l  回滚事务

 

3.2.1 开始事务

l  start transaction

l  begin

l  set auto_commit=no

l  开始事务之后，数据操作保存在日志文件中

3.2.2 提交事务

l  commit

l  数据操作在表中永久生效

l  事务日志被清空

3.2.3 回滚事务

l  rollback

l  数据操作不生效，回退到事务开始之前的状态

l  事务日志被清空

事务测试

会话一	会话二
use db1	use db1
begin;	begin;
insert into user(username) values('a');   update user set username='xxx' where id=1;
select * from user;
	select * from user;
commit;
	select * from user;
	commit;
	select * from user;
会话一	会话二
begin;	begin;
insert into user(username) values('b');   update user set username='yyy' where id=2;
select * from user;
rollback;
select * from user;

 

3.3     多个事务并发执行时数据访问问题

3.3.1 脏数据

读取到另一个事务未提交的数据

3.3.2 不可重复读

再次查询时，得到的数据与第一次的查询结果不一致

3.3.3 幻读

l  查询到根本不存在的数据

l  存在的数据查询不到

 

3.4 事务隔离级别

在数据访问安全，和事务并发性能之间，提供折中方案，在保证安全的前提下，使事务在一定程度上可以并发执行

四种隔离级别：

set tx_isolation='read-uncommitted';

set tx_isolation='read-committed';

set tx_isolation='repeatable-read';

set tx_isolation='serializable';

l  read-uncimmitted

n  脏读

n  不可重复读

n  幻读

l  read-committed

n  不可重复读

n  幻读

l  repeatable-read

n  幻读

l  serializable

n  数据安全，性能差

l  repeatable-read 
最佳折中方案，数据库的默认隔离级别

事务隔离级别测试

会话一	会话二
set tx_isolation= 'read-uncommitted';	set tx_isolation= 'read-uncommitted';
rollback; begin;	rollback; begin;
	select * from user;
insert into user(username) values('c');   update user set username='kkk' where id=2;
	select * from user;
rollback;
会话一	会话二
set tx_isolation='read-committed';	set tx_isolation='read-committed';
rollback; begin	rollback; begin;
	select * from user;
insert into user(username) values('d');   update user set username='qqq' where id=2;
	select * from user;
commit;
	select * from user;
会话一	会话二
set tx_isolation='repeatable-read';	set tx_isolation='repeatable-read';
rollback; begin;	rollback; begin;
	select * from user;
insert into user(username) values('e');   update user set username='www' where id=2;
	select * from user;
commit;
	select * from user;
select * from user;
	update user set password='123';
	select * from user;
会话一	会话二
rollback; begin;	rollback; begin;
	select * from user;
delete from user where id>=4;
	select * from user;
commit;
	select * from user;
	update user set password='456';
	select * from user;
	commit;
	select * from user;

---

4   视图 view

把 select 查询语句，保存下来，起个名字

可以从这个名字查询

l  视图不存数据

l  为什么使用视图：

n  简化查询

n  安全

l  mysql的视图，不允许有子查询

 

4.1     创建视图

create view 视图名

as

select ...

4.2     查看视图

和查看表相同

show tables;

desc 视图;

show create table 视图\G

show create view 视图\G

4.3     删除视图

drop view 视图;

视图测试

use hr;

创建视图

create view v1

as

select

   e.employee_id,e.first_name,

   d.department_name,

   l.city          

from

   employees e,

departments d,

locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

 

从 v1 查询

select * from v1;

select first_name,city from v1;

select * from v1 where city='Seattle';

 

select count(*) from v1

where city='Seattle';

 

查看视图

show tables;

desc v1;

show create table v1\G

show create view v1\G

 

删除视图

drop view v1;

---

5   索引

索引类似一个字典，在字典中可以快速找到数据，并定位数据的存储位置

索引测试

use db1;

select * from kecheng where id=87736675;

解释查询语句的执行效率

  *）使用什么索引

  *）索引数据的宽度

  *) 扫描的数据行数

explain select * from kecheng where id=87736675;

删除学生课程多对多中间表

drop table xs_kc_link;

取消课程主键自增

alter table kecheng

modify id int;

取消主键，删除主键索引

alter table kecheng

drop primary key;

select * from kecheng where id=87736675;

explain select * from kecheng where id=87736675;

 

5.1     索引的数据结构 B+Tree(了解)

 

5.2     创建索引

 

create index 索引名 on 表(字段)

create index 索引名

on 表(a,b)

   *) where a=1 and b=2

   *) where a=1

   *) where b=2 不使用组合索引

 

use hr;

create index first_name_index

on employees(first_name);

 

    *) where first_name='Eleni'

*) where first_name like 'El%'

*) where first_name like '%El' 不使用索引

 

explain select * from employees

where first_name='Eleni';

explain select * from employees

where first_name like 'El%';

explain select * from employees

where first_name like '%El';、

删除first_name的索引

alter table employees

drop index first_name_index;

 

创建first_name,last_name组合索引

create index first_last_index

on employees(first_name,last_name);

 

explain select * from employees

where first_name='TJ' and

last_name='Olson';

 

explain select * from employees

where first_name='TJ';

 

explain select * from employees

where last_name='Olson';

 

5.3     查看索引

show create table employees\G

5.4     删除索引

alter table employees

drop index first_last_index;