数据库面试剖析

1、数据库面试一、SQL语言包括数据定义(DDL )、数据操纵(Data Manipulation),数据控制(DataControl) , DQL , DTL数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index 等数据操纵：Select ,i nsert,update,delete,数据控制：gran t,revoke二、SQL常用命令：CREATE TABLE Stude nt(ID NUMBER PRIMARY KEY,NAME VARCHAR2(50) NOT NULL);/ 建表CREATE VIEW view_ name

2、 ASSelect * FROM Table_ name;/建视图Create UNIQUE INDEX in dex_ name ON TableName(col_ name);/建索引INSERT INTO table name colu mn 1,colu mn 2, values(exp1,exp2,);插 入INSERT INTO View name colu mn 1,colum n2, values(exp1,exp2,);/插入视图实际影响表UPDATE table name SET name= za ng 3 con ditio n; 更新数据DELETE FROM Tabl

3、e name WHERE con ditio n;删除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 SWHERE E.SUPERName=S.Name三、完整性约束：实体完整性、参照完整性、用户定义完整性四、索引作用，优缺点（详情）第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，

4、可以 大大加快 数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。缺点第一，创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。使用范围：

5、在经常需要搜索的列 上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加 快排序查询时间；在经常使用在 WHERE子句中的列上面创建索引 ，加快条件的判断速度。不适用范围：第一，对于那些在 查询中很少使用或者参考的列不应该创建索引。这是因 为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而 降低了

6、系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三，对于那些定义为text, image和bit数据类型的列不应该增加索引 。这是因为，这些列 的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索 弓I。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能

7、远远大于检索性能时，不应该创建索引。五、事务是一系列的数据库操作，是数据库应用的基本逻辑单位。事务性质：原子性、原子性。即不可分割性，事务要么全部被执行，要么就全部不被执行。一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。六、锁共享锁、互斥锁七、死锁及处理：事务循环等待数据锁，则会死锁。死锁处理：预防死锁协议，死锁恢复机制八、存储过程：存储过程就是编译好了的一些sql语句。1. 存储过程

8、因为SQL语句已经预编绎过了，因此运行的速度比较快。2. 可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。3. 可以降低网络的通信量。存储过程主要是在服务器上运行，减少对客户机的压力。4 :存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返 回错误原因5:存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据 逻辑。九、触发器当满足触发器条件，则系统自动执行触发器的触发体。触发时间：有before,after.触发事件

9、：有insert,update,delete 三种。触发类型：有行触发、 语句触发十、内联接，外联接区别？内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限 制另一个表的行。分左连接、右连接、全连接三种十一、innodb 与 myisammyisam索引文件和数据文件是分离的 ,myisam索引的存储方式是非聚合的，索引文件 存储在MYI文件。innodb索引和数据文件是保存在一起的；数据共享的话会放在ibdata，独享的话会放在ibdinnodb每个表只有一个聚集索引。如果木有主键，则会选择一个非空唯

10、一索引来代替主键；如果再不存在则会定义一个隐藏的主键进行聚集。innodb普通索引，这个跟myisam的差不多 但是与myisam索引的不同是叶子上存储是 主键的值而不是记录的指针地址，而且用他检索的时候会检索两边索引因为他需要先通过他查找主键(因为他叶子上面是的主键值)然后通过主键到聚集索引中检索得到数据MylSAM 是非事务安全型的，而InnoDB是事务安全型的。MylSAM锁的粒度是表级，而 InnoDB支持行级锁定。MyISAM支持全文类型索引，而InnoDB不支持全文索引。MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MylSAM。MyISAM表是保存成文

11、件的形式，在跨平台的数据转移中使用 MylSAM存储会省去不 少的麻烦。InnoDB表比MylSAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table table name type=innodb)十二、聚集和非聚集非聚集索引叶子页包含一个指向表中的记录的指针地址，记录的物理顺序和索引的顺序不一致；聚集索引则数据行和键值一起保存在叶子页而且记录的排列顺序与索引的排列顺序一致。十三、数据库事务隔离级别脏读，两个并发的事务， 事务A :领导给singo发工资、事务B : singo查询工资账户”事务B读取了事务A尚未提交的数据。不可重复读，两个并发的事务，事务A

12、: sin go消费”事务B : sin go的老婆网上转账”事务A事先读取了数据，事务 B紧接了更新了数据，并提交了事务，而事 务A再次读取该数据时，数据已经发生了改变。（在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改， 那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复）幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的 全部数据行”同时，第二个事务也修改这个表中的

13、数据， 这种修改是向表中插入 一行新数据”那么，以后就会发生操作第一个事务的用户发现表中 还有没有修改的数据行，就好象发生了幻觉一样一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。脏读不可重复读幻读Read uncommittedrRead committedX7Repeatable readxXSerializableXXX多数数据库的默认级别就是Read committed ，比女口 Sql Server , Oracle 。Mysql的默认隔离级别就是Repeatable read。分布式领域CAP理论，Consistency（ 致性）,数据一致更新，所

14、有数据变动都是同步的Availability（ 可用性）,好的响应性能Partition tolerance（分区容错性）可靠性定理：任何分布式系统只可同时满足二点，没法三者兼顾。关系数据库的 ACID模型拥有高一致性 +可用性很难进行分区Atomicity 原子性：一个事务中所有操作都必须全部完成，要么全部不完成。Consistency致性.在事务开始或结束时，数据库应该在一致状态。Isolation 隔离层.事务将假定只有它自己在操作数据库，彼此不知晓。Durability.一旦事务完成，就不能返回。|BASE模型反 ACID模型，完全不同ACID模型，牺牲高一致性，获得可用性或可靠性：B

15、asically Available 基本可用。支持分区失败（e.g. shardi ng碎片划分数据库）Soft state 软状态 状态可以有一段时间不同步，异步。Even tually con siste nt最终一致，最终数据是一致的就可以了，而不是时时高一致。实现一：（行转列，列转行）教师号 星期号是否有课1 2有1 3有2 1有3 2有1 2有写一条sql语句让你变为这样的表教师号星期一星期二星期三1212 13 1各星期下的数字表示：对应的教师在星期几已经排的课数 答：create database studygo use study gocreate table Teacher

16、CourseiIDint not null,TeacherIDint ,WeekID intHasCourse)5bit ,in sertintoTeacherCourse(ID, TeacherID , WeekID , HasCourse)values(1, 1,2,1)in sertintoTeacherCourse(ID, TeacherID , WeekID , HasCourse)values(2, 1,3,1)in sertintoTeacherCourse(ID, TeacherID , WeekID , HasCourse)values(3,2,1,1)in sertinto

17、TeacherCourse(ID, TeacherID , WeekID , HasCourse)values(4, 3,2, 1)in sertintoTeacherCourse(ID, TeacherID , WeekID , HasCourse)values(5, 1,2,1)select TeacherlD , WeekID count(*) from TeacherCourse where HasCourse=1 group by TeacherID , WeekID order by TeacherID select TeacherID,count (case WeekIDwhen

18、 1 then 1 else null end) as 星期一,cou nt (case WeekIDwhen 2 then 1 else null end) as 星期二,cou nt (case WeekIDwhen 3 then 1 else null end) as 星期三,cou nt (case WeekIDwhen 4 then 1 else null end) as 星期四，cou nt (case WeekIDwhen 5 then 1 else nullend) as 星期五,cou nt (case WeekIDwhen 6 then 1 else nullend) as

19、 星期六，cou nt (case WeekIDwhen 7 then 1 else nullend) as 星期日from TeacherCoursewhere HasCourse=1group by TeacherIDorder by TeacherID实现二：(取第二大值)select top 1 * from(select top 2 * from table name order by colu mnn ame) aorder by colu mnn ame desc实现三：查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：sel

20、ect top 10 * from A where ID (select max(ID) from (select top 30 ID from A order by A )T) order by A |查询表A中存在ID重复三次以上的记录，完整的查询语句如下：select * from(select coun t(ID) as count from table group by ID)T where T.co un t3MySQL 中的分页查询实现importjava.i o.ln putStream;importjava.sql.C onn ecti on;importjava.sql.P

21、reparedStateme nt;importjava.sql.ResultSet;importjava.sql.SQLExcepti on;importjava.util.ArrayList;import java.util.List;importweek07.doma in. Flight;importweek07.doma in. News;importweekO7.util.C onnectionU tils;public class FlightDaoImpIForMySQL *分页查询flight表的所有列的数据* parampage* 第几页* paramrowsPerPage

22、s* 每页显示行数* paramcon* 数据库连接* return查询到的数据以 News对象的形式存储到 List中返回* throwsException*/public List listPagesNewsDB(int page, int rowsPerPage,Conn ecti oncon) throws Excepti onint from = (page - 1) * rowsPerPage;String sqlselect * from flight limit ?, ?;Connection conn = n ull;PreparedStateme ntpStmt = nu

23、II;ResultSet rs = n ull;Flight flight = n ull;List flights = new ArrayList();try connConn ecti onU tils.ope nConn ecti on();pStmt = conn .prepareStateme nt(sql);pStmt.setl nt(1,from);pStmt.set In t(2,rowsPerPage);rs = pStmt.executeQuery();while (rs. next() Stringid = rs.getStri ng(1);Stringnum = rs.

24、getStri ng(2);flight = new Flighted,nu m);flights.add(flight); catch (SQLExceptione) e.pri ntStackTrace(); fin allyConn ecti onU tils.closeResultSet(rs);Conn ecti onU tils.closeStateme nt(pStmt);Conn ectio nU tils.closeC onn ectio n(conn);return flights;public static void main( Stri ngargs) throwsEx

25、cepti onList ff = new FlightDaolmplForMySQL(). listPagesNewsDB(2, 3,Conn ecti onU tils.ope nConn ecti on();for(Flight f:ff)System.out.pri ntln( =+f.getld()+=+f.getNum()+=);/Oracle 中的分页查询实现importjava.io.Filel nputStream;importjava.i o.ln putStream;importjava.sql.C onn ecti on;importjava.sql.PreparedS

26、tateme nt;importjava.sql.ResultSet;importjava.sql.SQLExcepti on;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.List;importorg.dom4j.Docume nt;importorg.dom4j.Eleme nt;importorg.dom4j.io.SAXReader;importweek07.doma in. News;importweekO7.util.C onnectionU tils;publicclass N

27、ewsDaoImplimpleme ntsINewsDao /*分页查询news表的所有列的数据* param page* 第几页* param rowsPerPages* 每页显示行数* param con*数据库连接return查询到的数据以News对象的形式存储到 List中返回throws Exceptio nselect n ews_id.title,content,author,pubDate, rn from(selectnews_id,title,content, author, pubDate,rownum rnfromnews*/whererownumwhere rn =p

28、ublic ListConnection?;JlistPagesNewsDB(i ntcon) throws Excepti onpage, introwsPerPage,int to = from + rowsPerPage;String sql =selectn ews_id,title,+content,author,pubDate,rn ” + from +(selectn ews_id,title, content, +author,pubDate,row numrn +from n ews whererow num ? ) + whereConnectionconn =null;P

29、reparedStateme ntpStmt =null;ResultSet rs=nu II;News n ews=n ull;int fromrnnew ArrayList();Listn ewses(page - 1) * rowsPerPage + 1;=try conn = Conn ecti onU tils.ope nConn ecti on();pStmtconn .prepareStateme nt(sql);pStmt.setl nt(1.to);pStmt.setl nt(2,from);rs = pStmt.executeQuery();while (rs. next(

30、) n ews = new News();n ews.setNewsld(rs.getL on g(1);n ews.setTitle(rs.getStri ng(2);n ews.setC on te nt(rs.getStri ng(3);n ews.setAuthor(rs.getStri ng(4);n ews.setPubdate(rs.getDate(5);n ewses.add( news); catch (SQLExceptione) e.pri ntStackTrace(); fin ally Conn ecti onU tils.closeResultSet(rs);Con

31、n ecti onU tils.closeStateme nt(pStmt);Conn ectio nU tils.closeC onn ectio n(conn);retur nn ewses;二、分页，CTE实现DECLARE Start INTDECLARE End INSELECT Start = 14000,E nd = 14050;WITH EmployeePage AS (SELECT LastName, FirstName, EmailAddress,ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) A

32、S RowNumberFROM EmployeeSELECT LastName, FirstName, EmailAddressFROM EmployeePageWHERE RowNumber Start AND RowNumber = EndORDER BY LastName, FirstName, EmailAddressGO、JDBC , Hibernate分页怎样实现?答：方法分别为：1) Hibernate的分页：Query query = sessi on. createQuery(fromStude nt);query.setFirstResult(firstResult);/设置每页开始的记录号query.setMaxResults(resultNumber);/设置每页显示的记录数Collectio nstude nts = query.list();2) JDBC的分页：根据不同的数据库采用不同的sql分页语句例如：Oracle 中的 sql 语句为：SELECT * FROM (SELECT a.*,