MSZBSQL面试题2

基础: 1. 数据抽象：物理抽象、概念抽象、视图级抽象,内模式、模式、外模式 2. SQL语言包括数据定义、数据操纵(Data Manipulation),数据控制(Data Control) 数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等 数据操纵：Select ,insert,u

基础:
　　1. 数据抽象：物理抽象、概念抽象、视图级抽象,内模式、模式、外模式
　　2. SQL语言包括数据定义、数据操纵(Data Manipulation),数据控制(Data Control)
　　数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等
　　数据操纵：Select ,insert,update,delete,
　　数据控制：grant,revoke
　　3. SQL常用命令：
　　CREATE TABLE Student(
　　ID NUMBER PRIMARY KEY,
　　NAME VARCHAR2(50) NOT NULL);//建表
　　CREATE VIEW view_name AS
　　Select * FROM Table_name;//建视图
　　Create UNIQUE INDEX index_name ON TableName(col_name);//建索引
　　INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);//插入
　　INSERT INTO Viewname {column1,column2,…} values(exp1,exp2,…);//插入视图实际影响表
　　UPDATE tablename SET name=’zang 3’ condition;//更新数据
　　DELETE FROM Tablename WHERE condition;//删除
　　GRANT (Select,delete,…) ON (对象) TO USER_NAME [WITH GRANT OPTION];//授权
　　REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION] //撤权
　　列出工作人员及其领导的名字：
　　Select E.NAME, S.NAME FROM EMPLOYEE E S
　　WHERE E.SUPERName=S.Name
　　4. 视图：
　　5. 完整性约束：实体完整性、参照完整性、用户定义完整性
　　6.什么是第三范式：
　　1NF:每个属性是不可分的。
　　2NF:若关系R是１NF,且每个非主属性都完全函数依赖于R的键。例SLC(SID#, CourceID#, SNAME,Grade),则不是2NF;
　　3NF:若R是2NF，且它的任何非键属性都不传递依赖于任何候选键。
　　7. ER(实体/联系)模型
　　8. 索引作用
　　9. 事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。事务性质：原子性、
　　原子性。即不可分割性，事务要么全部被执行，要么就全部不被执行。
　　一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态
　　隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，
　　持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。
　　10. 锁：共享锁、互斥锁
　　两段锁协议：阶段１：加锁阶段 阶段２：解锁阶段
　　11. 死锁及处理：事务循环等待数据锁，则会死锁。
　　死锁处理：预防死锁协议，死锁恢复机制
　　12. 存储过程：存储过程就是编译好了的一些sql语句。
　　1.存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。
　　2. 可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
　　3.可以降低网络的通信量。存储过程主要是在服务器上运行，减少对客户机的压力。
　　4：存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因
　　5：存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。
　　13. 触发器： 当满足触发器条件，则系统自动执行触发器的触发体。
　　触发时间：有before,after.触发事件：有insert,update,delete三种。触发类型：有行触发、语句触发
　　14.内联接,外联接区别？
　　内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。
　　在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种

经典查询：

1.一道SQL语句面试题，关于group by
　　表内容：
　　2005-05-09 胜
　　2005-05-09 胜
　　2005-05-09 负
　　2005-05-09 负
　　2005-05-10 胜
　　2005-05-10 负
　　2005-05-10 负
　　如果要生成下列结果, 该如何写sql语句?
　　胜 负
　　2005-05-09 2 2
　　2005-05-10 1 2
　　------------------------------------------
　　create table #tmp(rq varchar(10),shengfu nchar(1))
　　insert into #tmp values('2005-05-09','胜')
　　insert into #tmp values('2005-05-09','胜')
　　insert into #tmp values('2005-05-09','负')
　　insert into #tmp values('2005-05-09','负')
　　insert into #tmp values('2005-05-10','胜')
　　insert into #tmp values('2005-05-10','负')
　　insert into #tmp values('2005-05-10','负')
　　1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
　　2) select N.rq,N.胜,M.负 from (
　　select rq,胜=count(*) from #tmp where shengfu='胜'group by rq)N inner join
　　(select rq,负=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
　　3)select a.col001,a.a1 胜,b.b1 负 from
　　(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
　　(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
　　where a.col001=b.col001
　　2.请教一个面试中遇到的SQL语句的查询问题
　　表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
　　------------------------------------------
　　select (case when a>b then a else b end ),
　　(case when b>c then b esle c end)
　　from table_name
　　3.面试题：一个日期判断的sql语句？
　　请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
　　------------------------------------------
　　select * from tb where datediff(dd,SendTime,getdate())=0
　　4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
　　大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
　　显示格式：
　　语文 数学 英语
　　及格 优秀 不及格
　　------------------------------------------
　　select
　　(case when 语文>=80 then '优秀'
　　when 语文>=60 then '及格'
　　else '不及格') as 语文,
　　(case when 数学>=80 then '优秀'
　　when 数学>=60 then '及格'
　　else '不及格') as 数学,
　　(case when 英语>=80 then '优秀'
　　when 英语>=60 then '及格'
　　else '不及格') as 英语,
　　from table
　　5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
　　------------------------------------------
　　用户临时表:create table #xx(ID int, IDValues int)
　　系统临时表:create table ##xx(ID int, IDValues int)
　　区别:
　　用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
　　当创建它的进程消失时这个临时表就自动删除.
　　全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
　　6.sqlserver2000是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
　　------------------------------------------
　　它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
　　SQL Server 2000 数据库有三种类型的文件：
　　主要数据文件
　　主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
　　次要数据文件
　　次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
　　日志文件
　　日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
　　7.请用一个sql语句得出结果
　　从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
　　如使用存储过程也可以。
　　table1
　　月份mon 部门dep 业绩yj
　　-------------------------------
　　一月份 01 10
　　一月份 02 10
　　一月份 03 5
　　二月份 02 8
　　二月份 04 9
　　三月份 03 8
　　table2
　　部门dep 部门名称dname
　　--------------------------------
　　01 国内业务一部
　　02 国内业务二部
　　03 国内业务三部
　　04 国际业务部
　　table3 （result）
　　部门dep 一月份 二月份 三月份
　　--------------------------------------
　　01 10 null null
　　02 10 8 null
　　03 null 5 8
　　04 null null 9
　　------------------------------------------
　　1)
　　select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
　　from table1 a,table2 b,table2 c,table2 d
　　where a.部门dep = b.部门dep and b.月份mon = '一月份' and
　　a.部门dep = c.部门dep and c.月份mon = '二月份' and
　　a.部门dep = d.部门dep and d.月份mon = '三月份' and
　　2)
　　select a.dep,
　　sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
　　sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
　　sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
　　sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
　　sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
　　sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
　　sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
　　sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
　　sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
　　sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
　　sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
　　sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
　　from table2 a left join table1 b on a.dep=b.dep
　　8.华为一道面试题
　　一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
　　------------------------------------------
　　select id, Count（*) from tb group by id having count(*)>1

　　select * from(select count(ID) as count from table group by ID)T where T.count>1

笔试题：

1、对数据库SQL2005、ORACLE熟悉吗？
　　SQL2005是微软公司的数据库产品。是一个RDBMS数据库，一般应用在一些中型数据库的应用，不能跨平台。
　　ORACLE是ORACLE公司的数据产品，支持海量数据存储，支持分布式布暑，支持多用户，跨平台，数据安全完整性控制性能优越，是一个ORDBMS，一般用在大型公司。
　　2、能不能设计数据库？如何实现数据库导入与导出的更新
　　使用POWERDISINE工具的使用，一般满足第三范式就可以了。EXP与IMP数据库的逻辑导入与导出
　　3、如何只显示重复数据，或不显示重复数据
　　显示重复：select * from tablename groupby id having count(*)>1
　　不显示重复：select * from tablename groupby id having count(*)=1
　　4、什么是数据库的映射
　　就是将数据库的表与字段对应到模型层类名与属性的过程
　　5、写分页有哪些方法，你一般用什么方法？用SQL语句写一个分页？
　　如何用存储过程写分页？
　　在SQLSERVER中使用TOP分页，在ORACLE中用ROWNUM，或分析函数ROW_NUMBER
　　使用TOP：
　　select top 20,n.* from tablename n minus select top 10,m.* fromtablename m
　　使用分析函数：
　　select * from
　　(select n.*,row_number() over(order by columnname) num from tablename n)
　　where num>=10 and num 　　使用过程时，只要将分页的范围用两个参数就可以实现。在ORACLE中，要将过程封装在包里，还要用动态游标变量才能实现数据集的返回。
　　6、ORACLE中左连接与右连接
　　左连接：LEFT JOIN 右连接：RIGHT JOIN
　　select n.column,m.column from tablename1 n left join tablename2 m
　　onn.columnname=m.columnname
　　用WHERE实现：
　　select n.column,m.column from tablename1 n， tablename2 m
　　where n.columnname(+)=m.columnname
　　7、什么是反射、序列化、反序列化？事务有几种级别？
　　反射是在程序运行时动态访问DDL的一种方式。序列化是将对象对二进制、XML等方式直接向文件的存储。反序列化是将存储到文件的对象取出的过程。事务的级别的三种：页面级、应用程序级、数据库级。
　　8、数据测试如何测试？
　　在PLSQL里对过程或函数可能通过专用的测试工具，通过对
　　9、用事务的时候，如果在业务逻辑层中，调用数据库访问层中的方法，访问层中有很多类，类又有很多方法，每个方法都要实现，那么如何处理？
　　通用数据访问层的实现
　　10、什么时候会用到触发器
　　A安全管理、B日志管理、C复杂业务逻辑实现
　　11、如何在数据库中显示树控制？ 用父ID与子ID来实现
　　12、如何实现数据库的优化？
　　A、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。
　　B、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。
　　C、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。
　　D、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。
　　E、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。
　　F、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。
　　13、EJB与JAVA BEAN的区别？
　　Java Bean 是可复用的组件，对JavaBean并没有严格的规范，理论上讲，任何一个Java类都可以是一个Bean。但通常情况下，由于Java Bean是被容器所创建（如Tomcat）的，所以Java Bean应具有一个无参的构造器，另外，通常Java Bean还要实现Serializable接口用于实现Bean的持久性。Java Bean实际上相当于微软COM模型中的本地进程内COM组件，它是不能被跨进程访问的。Enterprise Java Bean 相当于DCOM，即分布式组件。它是基于Java的远程方法调用（RMI）技术的，所以EJB可以被远程访问（跨进程、跨计算机）。但EJB必须被布署在诸如Webspere、WebLogic这样的容器中，EJB客户从不直接访问真正的EJB组件，而是通过其容器访问。
　　EJB容器是EJB组件的代理，EJB组件由容器所创建和管理。客户通过容器来访问真正的EJB组件。
　　14、forward 和redirect的区别
　　forward是服务器请求资源，服务器直接访问目标地址的URL，把那个URL的响应内容读取过来，然后把这些内容再发给浏览器，浏览器根本不知道服务器发送的内容是从哪儿来的，所以它的地址栏中还是原来的地址。
　　redirect就是服务端根据逻辑,发送一个状态码,告诉浏览器重新去请求那个地址，一般来说浏览器会用刚才请求的所有参数重新请求，所以session,request参数都可以获取。
　　15、HashMap和Hashtable的区别。
　　HashMap是Hashtable的轻量级实现（非线程安全的实现），他们都完成了Map接口，主要区别在于HashMap允许空（null）键值（key）,由于非线程安全，效率上可能高于Hashtable。
　　HashMap允许将null作为一个entry的key或者value，而Hashtable不允许。
　　HashMap把Hashtable的contains方法去掉了，改成containsvalue和containsKey。因为contains方法容易让人引起误解。
　　Hashtable继承自Dictionary类，而HashMap是Java1.2引进的Map interface的一个实现。
　　最大的不同是，Hashtable的方法是Synchronize的，而HashMap不是，在多个线程访问Hashtable时，不需要自己为它的方法实现同步，而HashMap 就必须为之提供外同步。
　　Hashtable和HashMap采用的hash/rehash算法都大概一样，所以性能不会有很大的差异。
　　16、数据库类型中 VARCHAR 和 CHAR 的区别是？
　　1．char 的长度是固定的,而 varchar2 的长度是可以变化的， 比如，存储字符串“abc"，对于 char (20)， 表示你存储的字符将占 20 个字节(包括 17 个空字符)，而同样的 VARCHAR2 (20)则只占用 3 个字节的长度， 20 只是最大值，当你存储的字符小于 20 时，按实际长度存储。由于 char 是以固定长度的，所以它的速度 会比 varchar 快得多!但程序处理起来要麻烦一点，要用 trim 之类的函数把两边的空格去掉!1．char 的长度是固定的,而 varchar2 的长度是可以变化的， 比如，存储字符串“abc"，对于 char (20)， 表示你存储的字符将占 20 个字节(包括 17 个空字符)，而同样的 VARCHAR2 (20)则只占用 3 个字节的长度， 20 只是最大值，当你存储的字符小于 20 时，按实际长度存储。由于 char 是以固定长度的，所以它的速度 会比 varchar 快得多!但程序处理起来要麻烦一点，要用 trim 之类的函数把两边的空格去掉!
　　2．CHAR 的效率比 VARCHAR2 的效率稍高。
　　3．目前 VARCHAR 是 VARCHAR2 的同义词。工业标准的 VARCHAR 类型可以存储空字符串，但是 oracle 不这样 做，尽管它保留以后这样做的权利。Oracle 自己开发了一个数据类型 VARCHAR2，这个类型不是一个标准的 VARCHAR，它将在数据库中 varchar 列可以存储空字符串的特性改为存储 NULL 值。如果你想有向后兼容的 能力，Oracle 建议使用 VARCHAR2 而不是 VARCHAR。
　　17、如何插入单引号到数据库表中
　　可以用 ASCII 码处理，其它特殊字符如&也一样，如： insert into t values('i'chr(39)'m'); -- chr(39)代表字符' 或者用两个单引号表示一个 or insert into t values('I''m'); -- 两个''可以表示一个'
　　18、怎么获得今天是星期几 ， 还关于其它日期函数用法
　　（1）可以用 to_char 来解决，如： select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
　　（2）在获取之前可以设置日期语言，如： ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
　　（3）在函数中指定，如： select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
　　(4)其它更多用法，可以参考 to_char 与 to_date 函数 如获得完整的时间格式 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; (5)随便介绍几个其它函数的用法： 本月的天数 SELECT to_char(last_day(SYSDATE),'dd') days FROM dual 今年的天数 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 下个星期一的日期 SELECT Next_day(SYSDATE,'monday') FROM dual 知道出生日期， 如何求年龄？
　　19、 求上个月月底的日期
　　现在时 4 月 23 求上个月的月底时几号？（3 月 31 日） select to_char(trunc(sysdate, 'MM') -1 , 'YYYY-MM-DD') from dual; select trunc(sysdate,’mm’)-1 from dual; select to_char() from dual;