sql常见面试题

五.数据库部分1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。select*fromemployeeorderbydeptiddesc,salaryasc2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序创建表：mysqlcreatetableemployee921(idintprimarykeyauto_increment,namevarchar(50),salarybigint,deptidint);插入实验数据：mysqlinsertintoemployee921values(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语句：（）selectavg(salary)fromemployee921groupbydeptid;（）mysqlselectemployee921.id,employee921.name,employee921.salary,employee921.deptidtidfromemployee921wheresalary(selectavg(salary)fromemployee921wheredeptid=tid)orderbysalary效率低的一个语句，仅供学习参考使用（在groupby之后不能使用where，只能使用having，在groupby之前可以使用where，即表示对过滤后的结果分组）：mysqlselectemployee921.id,employee921.name,employee921.salary,employee921.deptidtidfromemployee921wheresalary(selectavg(salary)fromemployee921groupbydeptidhavingdeptid=tid);（）selectcount(*),tidfrom(selectemployee921.id,employee921.name,employee921.salary,employee921.deptidtidfromemployee921wheresalary(selectavg(salary)fromemployee921wheredeptid=tid))astgroupbytid;另外一种方式：关联查询selecta.ename,a.salary,a.deptidfromempa,(selectdeptd,avg(salary)avgsalfromempgroupbydeptid)bwherea.deptid=b.deptidanda.salaryb.avgsal;3、存储过程与触发器必须讲，经常被面试到?createprocedureinsert_Student(_namevarchar(50),_ageint,out_idint)begininsertintostudentvalue(null,_name,_age);selectmax(stuId)into_idfromstudent;end;callinsert_Student('wfz',23,@id);select@id;mysqlcreatetriggerupdate_StudentBEFOREupdateonstudentFOREACHROW-select*fromstudent;触发器不允许返回结果createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROWinsertintostudentvalue(null,'zxx',28);mysql的触发器目前不能对当前表进行操作createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROWdeletefromarticleswhereid=8;这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子这里要注意使用OLD.id触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。而UCH没有用触发器，效率和数据处理能力都很低。存储过程的实验步骤：mysqldelimiter|mysqlcreateprocedureinsertArticle_Procedure(pTitlevarchar(50),pBidint,outpIdint)-begin-insertintoarticle1value(null,pTitle,pBid);-selectmax(id)intopIdfromarticle1;-end;-|QueryOK,0rowsaffected(0.05sec)mysqlcallinsertArticle_Procedure('传智播客',1,@pid);-|QueryOK,0rowsaffected(0.00sec)mysqldelimiter;mysqlselect@pid;+------+|@pid|+------+|3|+------+1rowinset(0.00sec)mysqlselect*fromarticle1;+----+--------------+------+|id|title|bid|+----+--------------+------+|1|test|1||2|chuanzhiboke|1||3|传智播客|1|+----+--------------+------+3rowsinset(0.00sec)触发器的实验步骤：createtableboard1(idintprimarykeyauto_increment,namevarchar(50),articleCountint);createtablearticle1(idintprimarykeyauto_increment,titlevarchar(50),bidintreferencesboard1(id));delimiter|createtriggerinsertArticle_Triggerafterinsertonarticle1foreachrowbegin-updateboard1setarticleCount=articleCount+1whereid=NEW.bid;-end;-|delimiter;insertintoboard1value(null,'test',0);insertintoarticle1value(null,'test',1);还有，每插入一个帖子，都希望将版面表中的最后发帖时间，帖子总数字段进行同步更新，用触发器做效率就很高。下次课设计这样一个案例，写触发器时，对于最后发帖时间可能需要用declare方式声明一个变量，或者是用NEW.posttime来生成。4、数据库三范式是什么?第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。第三范式的要求如下：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征：1，每一列只有一个值2，每一行都能区分。3，每一个表都不包含其他表已经包含的非主关键字信息。例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。5、说出一些数据库优化方面的经验?用PreparedStatement一般来说比Statement性能高：一个sql发给服务器去执行，涉及步骤：语法检查、语义分析，编译，缓存二进制“inertintouservalues(1,1,1)”-二进制“inertintouservalues(2,2,2)”-二进制“inertintouservalues(?,?,?)”-有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。（比喻：就好比免检产品，就是为了提高效率，充分相信产品的制造商）（对于hibernate来说，就应该有一个变化：empleyee-deptid）Deptment对象，现在设计时就成了employee看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：1.selecte.name,e.salarywheree.managerid=(selectidfromemployeewherename='zxx');2.selecte.name,e.salary,m.name,m.salaryfromemployeese,employeesmwheree.managerid=m.idandm.name='zxx';表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟！缓存和执行指令。根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment语法检查和编译成为内部指令发给oracle服务器sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句还有索引对查询性能的改进也是值得关注的。备注：下面是关于性能的讨论举例4航班3个城市m*nselect*fromflight,citywhereflight.startcityid=city.cityidandcity.name='beijing';m+nselect*fromflightwherestartcityid=(selectcityidfromcitywherecityname='beijing');selectflight.id,'beijing',flight.flightTimefromflightwherestartcityid=(selectcityidfromcitywherecityname='beijing')6、union和unionall有什么不同?假设我们有一个表Student，包括以下字段与数据：droptablestudent;createtablestudent(idintprimarykey,namenvarchar2(50)notnull,scorenumbernotnull);insertintostudentvalues(1,'Aaron',78);insertintostudentvalues(2,'Bill',76);insertintostudentvalues(3,