部门表

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 两侧表中不符合连接条件的数据都查询出来

Mysql - 05

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 视图名

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;