mysql数据库---查询和事务

1. 关联查询

条件或者结果分布于多张表，连接多张表查询

1.1 内连接

内连接是将多张表都出现的记录连接展示在结果层

没有主从表之分，结果与连接顺序无关

select ename,sal,dname from emp,dept where emp.deptno = dept.deptno and empno = 7788;

# inner join ... on ...
select *from emp inner join dept on emp.deptno=dept.deptno;

# inner join ... using(字段)
select * from emp inner join dept using(deptno);
	*关联字段名字必须一致
	*将关联字段去除
	*必须使用在等值连接

1.2 自然连接

自然连接都是等值连接(将两张表 名称一样 值一样的记录进行关联)，等值连接不一定是自然连接

select * from emp natural join dept;

1.3 外连接

以驱动表为基准(left 前 right后) 依次遍历并于夫鼠标建立连接；

如果在附属表中找到匹配记录就连接并展示；如果找不到则以null填充。

存在主从表之分，与连接顺序有关。

left [outer] join ... on ... : 左外连接
right [outer] join ... on ... : 右外连接
select * from dept left join emp on emp.deptno=dept.deptno;

1.4 自连接

自身连接自身

# 查询员工及其领导的姓名
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

2. 子查询

嵌套查询

2.1 子查询分类

# 单行子查询：子查询返回的结果是单行单列
	select dname from dept where deptno=(select deptno from emp where empno =7788);
# 多行子查询：子查询返货的结果是多行
	select dname from dept where deptno in (select distinct deptno from emp where sal >2000);
	any / all
	=any 相当于 in    >any:大于最小值			<any:小于最大值
					>all:大于最大值			 <all:小于最小值

练习

#1.查询30号部门中最高工资的员工信息(考虑并列)
	select * from emp where sal =(select max(sal) from emp where deptno=30) and deptno=30;
	
	#2.查询20号部门中工资次高的员工信息
	select * from emp where deptno=20 and sal = (select max(sal) from (select * from emp where deptno=20 and sal < any(select sal from emp where deptno=20)) emp2);
	
	#3.查询大于所在部门平均工资的员工信息
	
	# 关联查询
	# 分组求平均工资
	# 工资>平均工资
	
	select emp.* from emp,(select deptno ,avg(sal) avg from emp group by deptno) e where emp.deptno=e.deptno and emp.sal>e.avg;	
	
	# 子查询
	select * from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);
	
	
	# 查询工资>2000的员工所在部门名称
	select dname from dept where deptno in  (select deptno from emp where sal>2000)
	
	select dname from dept where exists (select * from emp where sal>2000 and emp.deptno=dept.deptno)
	
	in 和exists之间的区别：
	1. in 先执行子查询，将子查询的结果返回给主查询，由主查询继续检索；exists先执行主查询，将主查询的记录一次交给子查询，在子查询中进行匹配，如果匹配则返回true，并将主查询的结果显示在结果集，如果返回false则不展示。
	2.in 必须让主查询的条件字段和子查询的结果字段匹配；exists不关心子查询的返回结果。 
	
	选择：
	1.如果查询的结果分布于多张表，选择关联查询
	2.如果结果分布于一张表，关联查询和子查询都可以使用
	3.查询尽量不要过多的嵌套，理论上说尽可能使用关联查询代替子查询提升性能
	4.尽可能先过滤再关联

3.联合查询

union：去重
union all: 不去重

select * from emp where deptno = 20
union
select * from emp where sal > 2000;

联合查询多个结果集的字段信息需要一致。

4.事务

保证数据一致性。

又一系列的dml操作组成，要么同时成功，要么同时失败

在数据库中，任何的增删改操作都必须再事物的环境下运行。

# 开启事务
一组dml操作
# 提交/回滚事务

#查看事务的自动提交，默认开启
show variables like '%autocommit%'

如果事务的自动提交开启，默认会将每一个dml操作当作单独的事务进行处理

如果要将多个dml操作放到一个事务中处理：
1.关闭事务的自动提交
	set autocommit = 0;
2.手动开启事务
	begin; / start transaction;
3.一组dml操作
4.手动提交/回滚事务
	commit; （持久化数据到文件中）/ rollback;

• 事务的四大特性

  • 原子性：同一事务中的dml作为整体不可分割
  • 一致性：事务执行前后整体的状态不变
  • 隔离性：并发事务之间独立的
  • 持久性：事务提交完成，数据应该持久化到数据库，不会随意发生改变

• 并发事务会产生的问题

  • 脏读

    t2事务读到 t1事务未提交的数据

  • 不可重复读

    t2事务在可读范围内 t1事务修改了该数据并且进行了事务提交

    t2在多次读取的过程中发现数据不一致

  • 幻读(虚读)

    t2事务在可读范围内 t1事务删除记录并且进行了事务提交

    t2在多次读取过程中发现数据数目不一致

• 事务的隔离级别

  • 读未提交：不能解决任何并发问题
  • 读已提交：能够解决脏读问题(oracle)
  • 可重复度：解决 脏读和不可重复读(mysql)
  • 串行化： 解决所有问题

5. 存储程序

运行于服务器端程序

• 存储过程：输入和输出参数

  delimiter //;
  create procedure sel_emp(dno int)
  begin
  	select * from emp where deptno=dno;
  end //;
  	
  call sel_emp(10);
  
  # 参数模式：
  	in：默认，输入参数
  	out：输出参数
  	inout：输入输出参数
  	
  # 输入员工编号查询名称
  delimiter //;
  create procedure sel_ename(eno int,out name varchar(20))
  begin
  	select ename into name from emp where empno =eno;
  end//;
  
  call sel_ename(7788,@name);
  select @name;
  		
  #根据名称获取职位
  delimiter //;
  create procedure sel_job(inout name_job varchar(20))
  begin
  	select job into name_job from emp where ename =name_job;
  end//;
  
  set @v_name='SCOTT';
  call sel_job(@v_name);
  select @v_name;
  
  # 选择结构
  
  #根据成绩分级
  delimiter //;
  create procedure score(score int)
  begin
  	#声明变量
  	declare v_level varchar(20);
  	if score >= 85 then
  		set v_level ='A';
  	elseif score >=60 then
  		set v_level ='B';
  	else 
  		set v_level ='C';
  	end if;
  	select v_level;
  end //;
  call score(90);
  
  
  # 循环结构
  
  # whlie...do...end while
  # 1+...+100
  delimiter //;
  create procedure calc()
  begin
  	declare i int;
  	declare sum int;
  	set i=1;
  	set sum=0;
  	
  	while i<=100 do
  		set sum =sum+i;
  		set i= i+1;
  	end while;
  	select sum;
  end //;
  call calc();
  
  # loop...end loop
  delimiter //;
  create procedure calc1()
  begin
  	declare i int;
  	declare sum int;
  	set i=1;
  	set sum=0;
  	
  	lip:loop 
  		if i>100 then
  			leave lip;
  		end if;
  		set sum =sum+i;
  		set i= i+1;
  	end loop;
  	select sum;
  end //;
  call calc1();
  
  # repeat ... end repeat
  delimiter //;
  create procedure calc2()
  begin
  	declare i int;
  	declare sum int;
  	set i=1;
  	set sum=0;
  	
  	repeat
  		set sum =sum+i;
  		set i= i+1;
  		until i>100
  	end repeat;
  	select sum;
  end //;
  call calc2();
  

• 存储函数：有返回值

• 触发器：由事件驱动