mysql优化_mysql sql优化_mysql优化面试题

tid from employee921 where salary > (select avg(salary) fromemployee921 group by deptid having deptid = tid);

（）select count(*) ,tid

from (

selectemployee921.id,employee921.name,employee921.salary,employee921.deptidtid

from employee921

where salary >

(select avg(salary) from employee921 where deptid = tid)

) as t

group by tid ;

另外一种方式：关联查询

select a.ename,a.salary,a.deptid

from emp a,

(select deptd,avg(salary) avgsal from emp group by deptid ) b

where a.deptid=b.deptid and a.salary>b.avgsal;

1、存储过程与触发器必须讲，经常被面试到?

create procedure insert_Student (_name varchar(50),_age int ,out_id int)

begin

insert into student value(null,_name,_age);

select max(stuId) into _id from student;

end;

call insert_Student('wfz',23,@id);

select @id;

mysql> create trigger update_Student BEFORE update on studentFOR EACH ROW

-> select * from student;

触发器不允许返回结果

create trigger update_Student BEFORE update on student FOR EACHROW

insert into student value(null,'zxx',28);

mysql的触发器目前不能对当前表进行操作

create trigger update_Student BEFORE update on student FOR EACHROW

delete from articles where id=8;

这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子

这里要注意使用OLD.id

触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。mysql优化面试题mysql优化面试题而UCH没有用触发器，效率和数据处理能力都很低。

存储过程的实验步骤：

mysql> delimiter |

mysql> create procedure insertArticle_Procedure (pTitlevarchar(50),pBid int,out

pId int)

-> begin

-> insert into article1 value(null,pTitle,pBid);

-> select max(id) into pId from article1;

-> end;

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-34607-1.html