(sql常见面试题.docVIP

五. 数据库部分1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。select * from employee order by deptid descsalary asc 2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序创建表：mysql create table employee921(id int primary key auto_increment,name varchar(50),salary bigint,deptid int); 插入实验数据：mysql insert into employee921 values(null,zs,1000,1),(null,ls,1100,1),(null,ww,1100,1),(null,zl,900,1) ,(null,zl,1000,2), (null,zl,900,2) ,(null,zl,1000,2) , (null,zl,1100,2); 编写sql语句： （）select avg(salary) from employee921 group by deptid;（）mysql select employee921.id,employee921.name,employee921.salary,employee921.deptid tid from employee921 where salary (select avg(salary) from employee921 where deptid = tid) order by salary 效率低的一个语句，仅供学习参考使用（在group by之后不能使用where，只能使用having，在group by之前可以使用where，即表示对过滤后的结果分组）：mysql select employee921.id,employee921.name,employee921.salary,employee921.deptid tid from employee921 where salary (select avg(salary) from employee921 group by deptid having deptid = tid);（）select count(*) ,tidfrom (select employee921.id,employee921.name,employee921.salary,employee921.deptid tidfrom employee921where salary (select avg(salary) from employee921 where deptid = tid)) as tgroup by tid ; 另外一种方式：关联查询select a.ename,a.salary,a.deptidfrom emp a,(select deptd,avg(salary) avgsal from emp group by deptid ) bwhere a.deptid=b.deptid and a.salaryb.avgsal;3、存储过程与触发器必须讲，经常被面试到?create procedure insert_Student (_name varchar(50),_age int ,out _id int)begininsert 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 student FOR EACH ROW- select * from student;触发器不允许返回结果 create trigger update_Student BEFORE update on student FOR EACH ROWinsert into student value(null,zxx,28);mysql的触发器目前不能对当前表进行操作 create trigger update_Student BEFORE update on student FOR EACH ROWdelete from articles where id=8;这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子这里要注意使用OLD.id 触发器用处还是很多的，比如校