mysql试题.docx
《mysql试题.docx》由会员分享，可在线阅读，更多相关《mysql试题.docx（30页珍藏版）》请在冰豆网上搜索。
mysql试题
大家自己看看，有错误自己改
五.数据库部分
1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。
employee:
eid,ename,salary,deptid;
select*fromemployeeorderbydeptiddesc,salary
2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序
创建表：
mysql>createtableemployee921（idintprimarykeyauto_increment,namevarchar（5
0）,salarybigint,deptidint）;
插入实验数据：
mysql>insertintoemployee921values（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语句：
（）selectavg（salary）fromemployee921groupbydeptid;
（）mysql>selectemployee921.id,employee921.name,employee921.salary,employee921.dep
tidtidfromemployee921wheresalary>（selectavg（salary）fromemployee921wheredeptid=tid）;
效率低的一个语句，仅供学习参考使用（在groupby之后不能使用where，只能使用having，在groupby之前可以使用where，即表示对过滤后的结果分组）：
mysql>selectemployee921.id,employee921.name,employee921.salary,employee921.dep
tidtidfromemployee921wheresalary>（selectavg（salary）fromemployee921groupbydeptidhavingdeptid=tid）;
（）selectcount（*）,tid
from（
selectemployee921.id,employee921.name,employee921.salary,employee921.deptidtid
fromemployee921
wheresalary>
（selectavg（salary）fromemployee921wheredeptid=tid）
）ast
groupbytid;
另外一种方式：
关联查询
selecta.ename,a.salary,a.deptid
fromempa,
（selectdeptd,avg（salary）avgsalfromempgroupbydeptid）b
wherea.deptid=b.deptidanda.salary>b.avgsal;
3、存储过程与触发器必须讲，经常被面试到?
createprocedureinsert_Student（_namevarchar（50）,_ageint,out_idint）
begin
insertintostudentvalue（null,_name,_age）;
selectmax（stuId）into_idfromstudent;
end;
callinsert_Student（'wfz',23,@id）;
select@id;
mysql>createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROW
->select*fromstudent;
触发器不允许返回结果
createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROW
insertintostudentvalue（null,'zxx',28）;
mysql的触发器目前不能对当前表进行操作
createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROW
deletefromarticleswhereid=8;
这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子
这里要注意使用OLD.id
触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。
因为触发器效率高。
而UCH没有用触发器，效率和数据处理能力都很低。
存储过程的实验步骤：
mysql>delimiter|
mysql>createprocedureinsertArticle_Procedure（pTitlevarchar（50）,pBidint,out
pIdint）
->begin
->insertintoarticle1value（null,pTitle,pBid）;
->selectmax（id）intopIdfromarticle1;
->end;
->|
QueryOK,0rowsaffected（0.05sec）
mysql>callinsertArticle_Procedure（'传智播客',1,@pid）;
->|
QueryOK,0rowsaffected（0.00sec）
mysql>delimiter;
mysql>select@pid;
+------+
|@pid|
+------+
|3|
+------+
1rowinset（0.00sec）
mysql>select*fromarticle1;
+----+--------------+------+
|id|title|bid|
+----+--------------+------+
|1|test|1|
|2|chuanzhiboke|1|
|3|传智播客|1|
+----+--------------+------+
3rowsinset（0.00sec）
触发器的实验步骤：
createtableboard1（idintprimarykeyauto_increment,namevarchar（50）,ar
ticleCountint）;
createtablearticle1（idintprimarykeyauto_increment,titlevarchar（50）
bidintreferencesboard1（id））;
delimiter|
createtriggerinsertArticle_Triggerafterinsertonarticle1foreachro
wbegin
->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->Deptment对象，现在设计时就成了employeedeptid）
看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：
1.selecte.name,e.salarywheree.managerid=（selectidfromemployeewherename='zxx'）;
2.selecte.name,e.salary,m.name,m.salaryfromemployeese,employeesmwhere
e.managerid=m.idandm.name='zxx';
表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等
将姓名和密码单独从用户表中独立出来。
这可以是非常好的一对一的案例哟！
sql语句全部大写，特别是列名和表名都大写。
特别是sql命令的缓存功能，更加需要统一大小写，sql语句发给oracle服务器语法检查和编译成为内部指令缓存和执行指令。
根据缓存的特点，不要拼凑条件，而是用?
和PreparedStatment
还有索引对查询性能的改进也是值得关注的。
备注：
下面是关于性能的讨论举例
4航班3个城市
m*n
select*fromflight,citywhereflight.startcityid=city.cityidandcity.name='beijing';
m+n
select*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,'Cindy',89）;
insertintostudentvalues（4,'Damon',90）;
insertintostudentvalues（5,'Ella',73）;
insertintostudentvalues（6,'Frado',61）;
insertintostudentvalues（7,'Gill',99）;
insertintostudentvalues（8,'Hellen',56）;
insertintostudentvalues（9,'Ivan',93）;
insertintostudentvalues（10,'Jay',90）;
commit;
Union和UnionAll的区别。
select*
fromstudent
whereid<4
union
select*
fromstudent
whereid>2andid<6
结果将是
1 Aaron 78
2 Bill 76
3 Cindy 89
4 Damon 90
5 Ella 73
如果换成UnionAll连接两个结果集，则返回结果是：
1 Aaron 78
2 Bill 76
3 Cindy 89
3 Cindy 89
4 Damon 90
5 Ella 73
可以看到，Union和UnionAll的区别之一在于对重复结果的处理。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。
如：
select*fromgc_dfys
union
select*fromls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
而UNIONALL只是简单的将两个结果合并后就返回。
这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
从效率上说，UNIONALL要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNIONALL，
7.分页语句
取出sql表中第31到40的记录（以自动增长ID为主键）
sqlserver方案1：
selecttop10*fromtwhereidnotin（selecttop30idfromtorderbyid）ordebyid
sqlserver方案2：
selecttop10*fromtwhereidin（selecttop40idfromtorderbyid）orderbyiddesc
mysql方案：
select*fromtorderbyidlimit30,10
oracle方案：
select*from（selectrownumr,*fromtwherer<=40）wherer>30
--------------------待整理进去的内容-------------------------------------
pageSize=20;
pageNo=5;
1.分页技术1（直接利用sql语句进行分页，效率最高和最推荐的）
mysql:
sql="select*fromarticleslimit"+（pageNo-1）*pageSize+","+pageSize;
oracle:
sql="select*from"+
"（selectrownumr,*from"+
"（select*fromarticlesorderbypostimedesc）"+
"whererownum<="+pageNo*pageSize+"）tmp"+
"wherer>"+（pageNo-1）*pageSize;
注释：
第7行保证rownum的顺序是确定的，因为oracle的索引会造成rownum返回不同的值
简洋提示：
没有orderby时，rownum按顺序输出，一旦有了orderby，rownum不按顺序输出了，这说明rownum是排序前的编号。
如果对orderby从句中的字段建立了索引，那么，rownum也是按顺序输出的，因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。
sqlserver:
sql="selecttop10*fromidnotid（selecttop"+（pageNo-1）*pageSize+"idfromarticles）"
DataSourceds=newInitialContext（）.lookup（jndiurl）;
Connectioncn=ds.getConnection（）;
//"select*fromuserwhereid=?
"--->binarydirective
PreparedStatementpstmt=cn.prepareSatement（sql）;
ResultSetrs=pstmt.executeQuery（）
while（rs.next（））
{
out.println（rs.getString
（1））;
}
2.不可滚动的游标
pageSize=20;
pageNo=5;
cn=null
stmt=null;
rs=null;
try
{
sqlserver:
sql="select*fromarticles";
DataSourceds=newInitialContext（）.lookup（jndiurl）;
Connectioncn=ds.getConnection（）;
//"select*fromuserwhereid=?
"--->binarydirective
PreparedStatementpstmt=cn.prepareSatement（sql）;
ResultSetrs=pstmt.executeQuery（）
for（intj=0;j<（pageNo-1）*pageSize;j++）
{
rs.next（）;
}
inti=0;
while（rs.next（）&&i<10）
{
i++;
out.println（rs.getString
（1））;
}
}
cacth（）{}
finnaly
{
if（rs!
=null）try{rs.close（）;}catch（Exceptione）{}
if（stm.........
if（cn............
}
3.可滚动的游标
pageSize=20;
pageNo=5;
cn=null
stmt=null;
rs=null;
try
{
sqlserver:
sql="select*fromarticles";
DataSourceds=newInitialContext（）.lookup（jndiurl）;
Connectioncn=ds.getConnection（）;
//"select*fromuserwhereid=?
"--->binarydirective
PreparedStatementpstmt=cn.prepareSatement（sql,ResultSet.TYPE_SCROLL_INSENSITIVE,...）;
//根据上面这行代码的异常SQLFeatureNotSupportedException，就可判断驱动是否支持可滚动游标
ResultSetrs=pstmt.executeQuery（）
rs.absolute（（pageNo-1）*pageSize）
inti=0;
while（rs.next（）&&i<10）
{
i++;
out.println（rs.getString
（1））;
}
}
cacth（）{}
finnaly
{
if（rs!
=null）try{rs.close（）;}catch（Exceptione）{}
if（stm.........
if（cn............
}
8.用一条SQL语句查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
准备数据的sql代码：
createtablescore（idintprimarykeyauto_increment,namevarchar（20）,subjectvarchar（20）,scoreint）;
insertintoscorevalues
（null,'张三','语文',81）,
（null,'张三','数学',75）,
（null,'李四','语文
升级会员 