Mysql05隔离级别，视图，索引，事务，左右连接

Mysql数据库day05

 

行内视图子查询

l  子查询在from后面

l  从一个查询的查询结果，再查询

 

select … from (select …) t

 

l  行内视图后面必须起一个别名

 

Select字段列表中的子查询

l  select a,b,c,(select …) from …

 

多表关联查询

l  多张表，按条件连接成一张表

 

l  Select 表1.字段列表, 表2.字段列表

from  表1,表2

where 表1.字段1=表2.字段2

        

l  自连接：将一张表看作是两张表进行连接

 

l  标准表连接语法：

select …

from

           表1

inner join 表2

           on  连接条件

           inner join 表3

           on  连接条件

           inner join 表4

           on  连接条件

 

l  外连接

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

n  左外连接

a  left outer join  b  on  …

                   左侧a表，条件外的数据也查询出来

n  右外连接

a  right outer join  b  on  …

右侧b表，条件外的数据也查询出来

 

 

select查询结构

         select

         distinct

         from

         join  on

         left join on

         right join on

         where

         group by

         having

         order by

         limit

 

事务

l  事务是数据操作的最小单元

l  多个数据增删改操作，完成的一项业务处理

l  如果事务事务成功，其中每一项操作都生效

        如果事务事务失败，其中每一项操作都失败

l  数据库数据操作，以事务为一个最小操作单元，

        不应该以一个sql语句为一个操作单元；

        要么整个事务成功，要么整个事务失败

l  在当前事务中对数据的修改，只对当前连接可见

 

l  ACID

            A - 原型性 Atomic

            C - 一致性 Consistency

                    转账前 a+b = 100

                    转帐后 a+b = 100

            I - 隔离性 Isolation

                    一个事物进行中时，

                    另一事物不能操作数据

            D - 持久性 Durancy

                    提交事务之后，

                    数据持久生效

 

l  事务操作

 

n  开始事务           

            start transaction; / begin;

       

n  提交事务           

            commit;

       

n  回滚事务

            rollback;

 

隔离级别

l  set tx_isolation='read-uncommitted';

l  set tx_isolation='read-committed';

l  set tx_isolation='repeatable-read';

l  set tx_isolation='serializable';

l  隔离级别越低，效率越高，数据访问冲突越多

l  隔离级别越高，效率越低，数据访问冲突越少

l  数据库默认隔离级别是repeatable-read

数据访问冲突问题

n  脏读

读取到其他事物未提交的数据

 

n  幻读

一个事务添加或删除数据并提交，

另一个事务查询不到新数据，或仍查询到已删除的数据

 

n  不可重复读

再次查询的数据，与第一次查询的数据不一致

        

      隔离级别对应数据访问冲突现象

        

read-uncommitted	脏读，幻读，不可重复读
read-committed	幻读，不可重复读
repeatable-read	幻读
serializable

 

 

视图

l  将一个查询保存在数据库中

l  可以从这个查询的查询结果，再查询

l  作用：

n  简化查询

n  安全

可以让低权限用户，只能从视图查询，

而不能去碰真实数据表

      创建视图

                   create  (or replace)  view  v1

                   as

                   select …

      查看视图

                   show tables;

                   desc v1;

                   show create table v1\G

      删除视图

                   drop view v1;

 

索引

l  提高字段的过滤查询速度

l  常见索引数据结构：

n  B-Tree

n  哈希表

 

创建索引

    create index index_name on tb1(name);

n  where name='abc'

n  where name like 'abc%'

n  order by name

n  where name like '%abc%' 不使用索引

                   create index index_name on tb1(name, birthday);

n  where name='abc' and birthday='xxxxxx'

n  where name='abc'

n  where birthday='xxxx' 第二个字段单独过滤不使用索引

           

查看索引

                   show create table tb1\G

删除索引

                   alter table tb1 drop index index_name;

练习

1.       只有一个下属的主管信息

主管id             手下

100                     3

120                     5

130                     1

160                     1



                   员工id              first_name       salary

                   130                     xxx                      xxx

                   160                     xxx                      xxx

                  

                   select employee_id,first_name,salary

                   from employees where employee_id in

                   (select manager_id from employees

                    where manager_id is not null

group by manager_id having count(*)=1)



2.       平均工资最高的部门编号



过滤条件是用 9000 过滤部门



部门                   平均工资

30                       6000

50                       7000

80                       5000

90                       9000

100                     9000

                   select department_id,round(avg(salary),2) a

                   from employees

where department_id is not null

group by department_id

having a=

                   (select  max(a)  from

                   (select department_id, round(avg(salary),2) a

                   from employees

where department_id is not null

group by department_id) t) -- 必须起别名



3.       平均工资最低的工种，查询做这些工作的人

工种         平均工资

A                5000

B                9000

C                3000

D                12000

E                3000

Select employee_id,first_name,salary,job_id

From employees

Where job_id in

(select job_id from employees

group by job_id

having round(avg(salary),2)=

(select min(a) from

(select job_id,round(avg(salary),2) a

from employees

group by job_id) t))



4.       查询员工工资，同时列出最高工资

select

         employee_id,

         first_name,

         salary,

         (select max(salary) from employees) max

From

         Employees;

        

5.       部门表

Select * from departments;



6.       查询员工信息，并显示部门名称

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;

        

7.       地区表

Select * from locations;



8.       查询部门，同时显示部门所在城市

select

         d.department_id,

         d.department_name,

         L.location_id,

         L.city

from

         departments d,locations L

where

         d.location_id=L.location_id;

        

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



         select

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

                    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;

        

10.   查询部门，显示部门经理名

select

           d.department_id,

           d.department_name,

           d.manager_id,

           e.first_name

from

           departments  d,

           employees  e

where

           d.manager_id=e.employee_id;

        

11.   查询员工，显示主管名

select

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

           e2.first_name  manager

from

           employees e1,employees e2

where

           e1.manager_id=e2.employee_id;

        

12.   查询员工，显示主管名、部门、部门经理名、城市

employees e1

employees e2

departments d

employees e3

locations l



select e1.employee_id,e1.first_name,

      e2.first_name mgr, d.department_name,

      e3.first_name manager,l.city

from  employees  e1

join   employees  e2

on    e1.manager_id=e2.employee_id

join   departments  d

on    e1.department_id=d.department_id

join   employees  e3

on    d.manager_id=e3.employee_id

join   locations l

on    d.location_id=l.location_id;



13.   查询所有员工，显示部门名，没有部门显示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;



14.   按城市分组，计算每个城市的员工数量

select

   l.city, count(*) c

from employees e

join departments d

on  e.department_id=d.department_id

join locations l

on  d.location_id=l.location_id

group by l.city

order by c desc;



15.   Seattle 市所有的员工信息

select

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

           l.city

from employees e

join departments d

on  e.department_id=d.department_id

join locations l

on  d.location_id=l.location_id

where l.city='Seattle';



16.   事务测试

use test;

drop table if exists tb1;

create table tb1(

  id int primary key auto_increment,

  name varchar(20)

回话1	回话2
Use  test	User test
begin;	begin;
Insert into tb1(name) values('aa');
select * from tb1;	select * from tb1;
commit;
	select * from tb1;
	commit;
	select * from tb1;

);

 

回话1	回话2
Use test	Use test
begin;	begin;
insert into tb1(name) values('bb');
update tb1 set name='aaaa' where id=1;
	Select * from tb1;
	update tb1 set name='aaaaaaaa' where id=1;
rollback;
Select * from tb1;	Select * from tb1;
	commit;

 

 

17.   隔离级别测试

回话1	回话2
set tx_isolation= 'read-uncommitted';	set tx_isolation= 'read-uncommitted';
rollback; begin;	rollback; begin;
Insert into tb1(name) values('bb');
	Select * from tb1;
Update tb1 set name='a' where id=1;
	Select * from tb1;
rollback;
	Select * from tb1;

 

 

回话1	回话2
set tx_isolation= 'read-committed'	set tx_isolation= 'read-committed'
rollback; begin;	rollback; begin;
Insert into tb1(name) values('bb');
Update tb1 set name='aaaaaaaaaaaa' where id=1;
	select * from tb1;
commit;
	select * from tb1;

 

回话1	回话2
set tx_isolation= 'repeatable-read'	set tx_isolation= 'repeatable-read'
rollback; begin;	rollback; begin;
insert into tb1(name) values('cc');
update tb1 set name='bbbbbbbb' where id=4;
	select * from tb1;
commit;
	select * from tb1;
	update tb1 set name=concat('*',name);
	select * from tb1;

 

 

回话1	回话2
set tx_isolation= 'repeatable-read'	set tx_isolation= 'repeatable-read'
rollback; begin;	rollback; begin;
	select * from tb1;
delete from tb1 where id=4;
	select * from tb1;
commit;
	select * from tb1;
	update  tb1  set name=concat('#',name);
	select * from tb1;

 

 

18.   视图测试

use  hr;

 

create or replace view v1  as

select

         l.city, count(*) c

from employees e

join departments d

on  e.department_id=d.department_id

join locations l

on  d.location_id=l.location_id

group by  l.city;

 

show tables;

desc v1;

show create table v1\G

          

 

select * from v1;

select c from v1;

select * from v1 where c=1;

 

 

 

 

练习

         子查询:

1.       工资多于工种 IT_PROG 平均工资的员工

select employee_id,first_name,salary

from employees

where salary>(

           select avg(salary) from employees

           where job_id='IT_PROG'

);

 

2.       平均工资最高的工种, 显示工作全名

Jobs表保存工作全名

 

select job_id,job_title from jobs

where job_id in

(

select job_id from employees

group by job_id

having avg(salary)=(

select max(a) from

(select job_id,avg(salary) a from employees

group by job_id) t

)

);

 

3.       每个部门拿最高工资的人

select employee_id,first_name,salary,department_id

from employees

where (department_id,salary) in

(

select department_id,max(salary)

from employees

where department_id is not null

group by department_id

)

 

4.       每年第一个入职的人

 

select employee_id,first_name,salary,hire_date

from employees

where hire_date in

(

select min(hire_date)

from employees

group by extract(year from hire_date)

);

 

5.       平均工资最高的部门编号

 

select department_id,round(avg(salary),2) a

from employees

where department_id is not null

group by department_id

having a =

(

select max(a) from

(select department_id,round(avg(salary),2) a

from employees

where department_id is not null

group by department_id) t

);

 

6.       下属人数最多的人，查询其个人信息

 

select employee_id,first_name,salary

from employees

where employee_id in

(

Select manager_id

From employees

Where manager_id is not null

Group by manager_id

Having count(*)=

(

select max(c) from

(

Select manager_id, count(*) c

From employees

Where manager_id is not null

Group by manager_id

) t

)

)