sql常见面试题.doc

《sql常见面试题.doc》由会员分享，可在线阅读，更多相关《sql常见面试题.doc（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、五. 数据库部分1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。select * from employee order by deptid desc ,salary asc2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序创建表：mysql create table employee921(id int primary key auto_increment,name varchar(50),salary bigint,deptid int);插入实验数据：mysql insert into employee921 values(null,zs,100

2、0,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

3、 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(salar

4、y) from employee921 group by deptid having deptid = tid);（）select count(*) ,tid from (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.sa

5、lary,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 f

6、rom 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 tr

7、igger update_Student BEFORE update on student FOR EACH ROWdelete from articles where id=8;这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子这里要注意使用OLD.id触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。而UCH没有用触发器，效率和数据处理能力都很低。存储过程的实验步骤：mysql delimiter |mysql create procedure insertArt

8、icle_Procedure (pTitle varchar(50),pBid int,outpId int)- begin- insert into article1 value(null,pTitle,pBid);- select max(id) into pId from article1;- end;- |Query OK, 0 rows affected (0.05 sec)mysql call insertArticle_Procedure(传智播客,1,pid);- |Query OK, 0 rows affected (0.00 sec)mysql delimiter ;mys

9、ql select pid;+-+| pid |+-+| 3 |+-+1 row in set (0.00 sec)mysql select * from article1;+-+-+-+| id | title | bid |+-+-+-+| 1 | test | 1 | 2 | chuanzhiboke | 1 | 3 | 传智播客 | 1 |+-+-+-+3 rows in set (0.00 sec)触发器的实验步骤：create table board1(id int primary key auto_increment,name varchar(50),articleCount i

10、nt);create table article1(id int primary key auto_increment,title varchar(50),bid int references board1(id);delimiter |create trigger insertArticle_Trigger after insert on article1 for each row begin- update board1 set articleCount=articleCount+1 where id= NEW.bid;- end;- |delimiter ;insert into boa

11、rd1 value (null,test,0);insert into article1 value(null,test,1);还有，每插入一个帖子，都希望将版面表中的最后发帖时间，帖子总数字段进行同步更新，用触发器做效率就很高。下次课设计这样一个案例，写触发器时，对于最后发帖时间可能需要用declare方式声明一个变量，或者是用NEW.posttime来生成。4、数据库三范式是什么?第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分

12、出姓和名，必须设计成两个独立的字段。第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非

13、主属性非部分依赖于主关键字。第三范式的要求如下：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征：1，每一列只有一个值2，每一行都能区分。3，每一个表都不包含其他表已经包含的非主关键字信息。例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。5、说出一些数据库优化方面的经验?用PreparedStatement 一般来说比Statement性能高：一个sql

14、发给服务器去执行，涉及步骤：语法检查、语义分析， 编译，缓存二进制“inert into user values(1,1,1)”-二进制“inert into user values(2,2,2)”-二进制“inert into user values(?,?,?)”-有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。（比喻：就好比免检产品，就是为了提高效率，充分相信产品的制造商）（对于hibernate来说，就应该有一个变化：empleyee-deptid）Deptment对象，现在设计时就成了employee看mysql帮助文档子查询章节的最后部分，

15、例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：1. select e.name,e.salary where e.managerid=(select id from employee where name=zxx);2. select e.name,e.salary,m.name,m.salary from employees e,employees m wheree.managerid = m.id and m.name=zxx;表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟！缓存和执行指令。根据

16、缓存的特点，不要拼凑条件，而是用?和PreparedStatment语法检查和编译成为内部指令发给oracle服务器sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句还有索引对查询性能的改进也是值得关注的。备注：下面是关于性能的讨论举例4航班 3个城市m*nselect * from flight,city where flight.startcityid=city.cityid and city.name=beijing;m + nselect * from flight where startcityid = (select cityid

17、 from city where cityname=beijing);select flight.id,beijing,flight.flightTime from flight where startcityid = (select cityid from city where cityname=beijing)6、union和union all有什么不同?假设我们有一个表Student，包括以下字段与数据：drop table student;create table student(id int primary key,name nvarchar2(50) not null,score

18、number not null);insert into student values(1,Aaron,78);insert into student values(2,Bill,76);insert into student values(3,Cindy,89);insert into student values(4,Damon,90);insert into student values(5,Ella,73);insert into student values(6,Frado,61);insert into student values(7,Gill,99);insert into s

19、tudent values(8,Hellen,56);insert into student values(9,Ivan,93);insert into student values(10,Jay,90);commit;Union和Union All的区别。select *from studentwhere id 2 and id 6结果将是1 Aaron 782 Bill 763 Cindy 894 Damon 905 Ella 73如果换成Union All连接两个结果集，则返回结果是：1 Aaron 782 Bill 763 Cindy 893 Cindy 894 Damon 905 E

20、lla 73可以看到，Union和Union All的区别之一在于对重复结果的处理。UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：select * from gc_dfysunionselect * from ls_jg_dfys这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复

21、的数据，那么返回的结果集就会包含重复的数据了。从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL，7.分页语句取出sql表中第31到40的记录（以自动增长ID为主键）sql server方案1：select top 10 * from t where id not in (select top 30 id from t order by id ) orde by idsql server方案2：select top 10 * from t where id in (select top 40 id fro

22、m t order by id) order by id descmysql方案：select * from t order by id limit 30,10oracle方案：select * from (select rownum r,* from t where r308.用一条SQL语句 查询出每门课都大于80分的学生姓名name kecheng fenshu张三 语文 81张三 数学 75李四 语文 76李四 数学 90王五 语文 81王五 数学 100王五 英语 90准备数据的sql代码：create table score(id int primary key auto_incr

23、ement,name varchar(20),subject varchar(20),score int);insert into score values(null,张三,语文,81),(null,张三,数学,75),(null,李四,语文,76),(null,李四,数学,90),(null,王五,语文,81),(null,王五,数学,100),(null,王五 ,英语,90);提示：当百思不得其解时，请理想思维，把小变成大做，把大变成小做，答案：A: select distinct name from score where name not in (select distinct nam

24、e from score where score=80)B:select distince name t1 from score where 80 all (select score from score where name=t1);9.所有部门之间的比赛组合一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.答：select a.name, b.namefrom team a, team bwhere a.name b.name10.每个月份的发生额都比101科目多的科目

25、请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有112月份的发生额。AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。数据库名：JcyAudit，数据集：Select * from TestDB准备数据的sql代码：drop table if exists TestDB;create table TestDB(id int primary key auto_increment,AccID varchar(20), Occmonth date, DebitOccur bi

26、gint);insert into TestDB values(null,101,1988-1-1,100),(null,101,1988-2-1,110),(null,101,1988-3-1,120),(null,101,1988-4-1,100),(null,101,1988-5-1,100),(null,101,1988-6-1,100),(null,101,1988-7-1,100),(null,101,1988-8-1,100);-复制上面的数据，故意把第一个月份的发生额数字改小一点insert into TestDB values(null,102,1988-1-1,90),(n

27、ull,102,1988-2-1,110),(null,102,1988-3-1,120),(null,102,1988-4-1,100),(null,102,1988-5-1,100),(null,102,1988-6-1,100),(null,102,1988-7-1,100),(null,102,1988-8-1,100);-复制最上面的数据，故意把所有发生额数字改大一点insert into TestDB values(null,103,1988-1-1,150),(null,103,1988-2-1,160),(null,103,1988-3-1,180),(null,103,198

28、8-4-1,120),(null,103,1988-5-1,120),(null,103,1988-6-1,120),(null,103,1988-7-1,120),(null,103,1988-8-1,120);-复制最上面的数据，故意把所有发生额数字改大一点insert into TestDB values(null,104,1988-1-1,130),(null,104,1988-2-1,130),(null,104,1988-3-1,140),(null,104,1988-4-1,150),(null,104,1988-5-1,160),(null,104,1988-6-1,170),

29、(null,104,1988-7-1,180),(null,104,1988-8-1,140);-复制最上面的数据，故意把第二个月份的发生额数字改小一点insert into TestDB values(null,105,1988-1-1,100),(null,105,1988-2-1,80),(null,105,1988-3-1,120),(null,105,1988-4-1,100),(null,105,1988-5-1,100),(null,105,1988-6-1,100),(null,105,1988-7-1,100),(null,105,1988-8-1,100);答案：selec

30、t distinct AccID from TestDBwhere AccID not in(select TestDB.AccIDfrom TestDB,(select * from TestDB where AccID=101) as db101where TestDB.Occmonth=db101.Occmonth and TestDB.DebitOccur select flight.flightid,北京 startcity, e.cityname from flight,city e where flight.endcityid=e.cityid and flight.startc

31、ityid=(select cityid from city where cityname=北京);mysql select flight.flightid,s.cityname,e.cityname from flight,city s,city e where flight.startcityid=s.cityid and s.cityname=北京 and flight.endCityId=e.cityID order by e.cityName desc;2、查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）select c1.CityName,c2.CityName

32、,f.StartTime,f.flightIDfrom city c1,city c2,flight fwhere f.StartCityID=c1.cityIDand f.endCityID=c2.cityIDand c1.cityName=北京and c2.cityName=上海3、查询具体某一天（2005-5-8）的北京到上海的的航班次数select count(*) from(select c1.CityName,c2.CityName,f.StartTime,f.flightIDfrom city c1,city c2,flight fwhere f.StartCityID=c1.c

33、ityIDand f.endCityID=c2.cityIDand c1.cityName=北京and c2.cityName=上海and 查帮助获得的某个日期处理函数(startTime) like 2005-5-8%mysql中提取日期部分进行比较的示例代码如下：select * from flight where date_format(starttime,%Y-%m-%d)=1998-01-0215.查出比经理薪水还高的员工信息：Drop table if not exists employees;create table employees(id int primary key au

34、to_increment,name varchar(50),salary int,managerid int references employees(id);insert into employees values (null, lhm,10000,null), (null, zxx,15000,1),(null,flx,9000,1),(null,tg,10000,2),(null,wzg,10000,3);Wzg大于flx,lhm大于zxx解题思路：根据sql语句的查询特点，是逐行进行运算，不可能两行同时参与运算。涉及了员工薪水和经理薪水，所有，一行记录要同时包含两个薪水，所有想到要把这个表自关联组合一下。首先要组合出一个包含有各个员工及该员工的经理信息的长记录，譬如，左半部分是员工，右半部分是经理。而迪卡尔积会组合出很多垃圾信息，先去除这些垃圾信息。select e.* from employees e,employees m where e.manageri