java笔试题数据库.docxVIP

数据库部分 1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。 employee: eid,ename,salary,deptid; select * from employee order by deptid desc,salary 2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序 创建表： mysql create table employee921(id int primary key auto_increment,name varchar(5 0),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,z l,1000,2) , (null,zl,1100,2); 编写sql语句： （）select avg(salary) from employee921 group by deptid; （）mysql select employee921.id,employee921.name,employee921.salary,employee921.dep tid tid from employee921 where salary (select avg(salary) from employee921 where deptid = tid); 效率低的一个语句，仅供学习参考使用（在group by之后不能使用where，只能使用having，在group by之前可以使用where，即表示对过滤后的结果分组）： mysql select employee921.id,employee921.name,employee921.salary,employee921.dep tid tid from employee921 where salary (select avg(salary) from employee921 group by deptid having deptid = tid); （）select count(*) ,tid from ( select employee921.id,employee921.name,employee921.salary,employee921.deptid tid 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.salaryb.avgsal 3、存储过程与触发器必须讲，经常被面试到? 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 student FOR EACH ROW - select * from student; 触发器不允许返回结果 create trigger update_Student BEFORE update on student FOR EACH ROW insert into student value(null,zxx,28); mysql的触发器目前不能对当前表进行操作 create trigger update_Student BEFORE update on student FOR EACH ROW delete from articles where id=8; 这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子 这里要注意使用