mysql 在查询语句中遍历表 mysql查询原理是遍历吗

转载

网络安全战士 2024-07-05 10:40:39

文章标签 mysql 在查询语句中遍历表数据库 mysql 子查询字段 文章分类 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();