数据库面试问题汇总

数据库面试问题汇总1.数据库管理命令1.1查询所有数据库----showdatabases;1.2创建数据库，并将数据库字符集默认为utf-8----createdatabaseday814----defaultcharactersetutf8-----;1.3查看数据库的默认字符集----showcreatedatabaseday814;1.4删除数据库----dropdatabaseday15;1.5修改数据库(即修改数据库的默认字符集)-----alertdatabaseday814defaultcharactersetgbk;2.表管理命令2.1选择数据库命令-----useday814;2.2查看所有表----showtables;2.3创建表----createtablestudent(-----sidint,-----sageint,-----snamevarchar(20)-----);2.4查看表结构------descstudent;2.5删除表-----droptablestudent;2.6修改表添加字段-----altertablestudentaddcolumnsgendervarchar(2);删除字段-----altertablestudnetdropcolumnsgender;修改字段类型-----altertablestudentmodifycolumnsgendervarchar(100);修改字段名称-----altertablestudentchangecolumnsgendergendervarchar(100);修改表名称-----altertablestudentrenametoteacher;3.数据管理3.1增删改数据-----增加数据INSERTINTOstudentVALUES(1,’tom’,20,2);//要严格按照表的字段顺序插入数据增加部分数据INSERTINTOstudent(id,name)VALUES(2,’tina’);-----删除数据条件删除DELETEFROMstudentWHEREid=2;注意：DELETEFROM和TRUNCATEFROM的区别DELETEFROM:可以全表删除，也可以条件删除，只能删除数据，不能删除约束，最后删除之后，可以回滚事务；TRUNCATEFROM:只能全表删除，不能条件删除，即可以删除表的数据，也可以删除表的约束，不能回滚事务；-----修改数据带条件修改UPDATEstudentSETage=1,name=’tom’WHEREid=2;3.2查询数据（重点）3.2.1查询所有列(*通配符)---SELECT*FROMstudent;3.2.2查询指定列---SELECTsid,sname,genderFROMstudent;3.2.3查询时添加常量列---SELECTsid,sname,gender,sage,’java_Class’AS‘sgrade’FROMstudent;//在查询student表时，添加一个sgrade列，内容为java_Class;3.2.4查询时合并列---SELECTsid,sname,(servlet+jsp)AS‘总成绩’FROMstudent;3.2.5查询时去除重复记录---SELECTDISTINCTgenderFROMstudent;---SELECTDISTINCTaddressFROMstudent;3.2.6条件查询（模糊条件查询）---逻辑条件AND(与)OR(或)SELECT*FROMstudentWHEREsid=2ANDsname=’c++’;//交集SELECT*FROMstudentWHEREsid=2ORsname=’张三’;//并集---比较条件,,=最重要的不等于，另外BETWEENANDSELECT*FROMstudentWHEREjsp=90ANDjsp=75;---判空条件需求：查询地址为null的学生（包括null和空字符串）注意：NULL表示没有值空字符串表示有值的；----模糊条件LIKE表示模糊条件;%表示任意字符SELECT*FROMstudentWHEREsnameLIKE‘j%’;3.2.7聚合查询常用的聚合函数：sum()\avg()\max()\min()\count()\SELECTMAX(jsp)AS‘max_score’FROMstudent;SELECTAVG(jsp)AS‘averagescore‘FROMstudent;这里注意：count统计数据不包含null的数据3.2.8分页查询查询第1,2条记录SELECT*FROMstudentLIMIT0,2;//注意：LIMIT0:起始行行数，2：总行数；3.2.9查询排序ASC:升序；DESC降序SELECT*FROMstudentORDERBYsidASC;SELECT*FROMstudentORDERBYservletASC;SELECT*FROMstudentORDERBYservletASC,jspDESC;3.2.10分组查询--2.11分组查询(groupby)--需求：查询男女的人数--预期结果：--男3---女2--算法步骤：1)把学生按照性别分组(GROUPBYgender)--2)统计每组的人数(COUNT(*))SELECTgender,COUNT(*)FROMstudentGROUPBYgender;3.2.11分组查询后筛选--2.12分组查询后筛选--需求：查询总人数大于2的性别--算法：1)查询男女的人数--2）筛选出人数大于2的记录(having)---注意：分组之前条件使用where关键字，分组之后条件使用having关键字SELECTgender,COUNT(*)FROMstudentWHEREGROUPBYgenderHAVINGCOUNT(*)2;综上，所有的SQL语句指令，主要包含3个部分:数据库管理、表管理、数据管理。SQL语句分类：数据定义语言、数据查询语言、数据操作语言。4.数据约束4.1默认值4.2非空4.3唯一4.4主键（==非空+唯一）5.数据库设计：三大范式第一范式：要求表的每个字段必须是不可分割的独立单元；比如student:nameJava\狗娃应该改为nameoldnameJava狗娃第二范式：在第一张范式基础上，要求每张表只表达一个意思，表的每个字段都和主键有依赖；employee（员工）:员工编号员工姓名部门名称订单名称--违反第二范式员工表：员工编号员工姓名部门名称订单表：订单编号订单名称--符合第二范式第三范式：在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系；员工表：员工编号（主键）员工姓名部门编号部门名--符合第二范式，违反第三范式（数据冗余高）员工表：员工编号（主键）员工姓名部门编号--符合第三范式（降低数据冗余）部门表：部门编号部门名解析：个人对数据库三大范式的理解：首先数据表中的每个字段都必须是不可分割的独立单元；其次，在第一范式的基础上，要求每张表只表达一个意思，表的每个字段都和主键有依赖；最后，在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。6.存储过程带有逻辑的SQL语句，存储过程带上流过程控制语句（ifelseforwhile）;(1)存储过程执行效率非常快，因为是在服务端执行；(2)存储过程移植性比较差，不同数据的存储过程是不能移植的；7.数据库索引实现原理1.关于数据库索引采用的数据结构题目1：Mysql数据库用过吧？l里面的索引是基于什么数据结构。答：主要是基于Hash表和B+树题目2：很好请你说一下B+树的实现细节是什么样的？B-树和B+树有什么区别？联合索引在B+树中如何存储？答:首先，数据库使用树型结构来增加查询效率，并保持有序。那么，为什么不使用二叉树来实现数据结构呢，二叉树算法时间复杂度是lg(N)，查询速度和比较次数都是较小的。实际上，查询索引操作最耗资源的不在内存中，而是磁盘IO。索引是存在磁盘上的，当数据量比较大的时候，索引的大小可能达到几个G。那么，我们利用索引进行查询的时候，不可能把索引直接加载到内存中，只能一次读取一个磁盘页，一个磁盘页对应着一个节点，一次读取操作时一个磁盘io。在二叉树查询时，最坏的情况下查找的次数是树的高度，即io次数为树的高度。B-树就是比二叉树“矮胖”的树。二叉树的特征如下：1.根节点至少有两个子女2.每个中间节点包含k-1个元素和k个孩子，其中m/2=k=m3.每个叶子节点包含k-1个元素，其中m/2=k=m4.所有叶子节点位于同一层5.节点中的元素从小到大排列，正好是孩子节点的值域。（就是孩子节点的元素都比父节点中元素的最小值大，比父节点元素的最大值小）B-树查询的次数并不比二叉树的次数小，但是相比起磁盘io速度，内存中比较的耗时就不足为提了。所以只要树的高度足够低，io次数少，就可以提升查找性能。而每个节点中有多个元素，都只在内存中操作。而B+树是基于B-树的，增加了如下规则：1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。所以，B+树对比B-树有如下好处：io次数少：b+树中间节点只存索引，不存在实际的数据，所以可以存储更多的数据。索引树更加的矮胖，io次数更少。性能稳定：b+树数据只存在于叶子节点，查询性能稳定范围查询简单：b+树不需要中序遍历，遍历链表即可。B树：有序数组+平衡多叉树；B+树：有序数组链表+平衡多叉树；数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。8.数据库三大引擎之间差异(1)MyISAM特性：不支持事务、MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能适应；表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但也同时大大降低了并发性能；读写互相阻塞：不仅会在写入时阻塞读取，还会在读取时，阻塞写入，但读本身并不会阻塞另外的读。只会缓存索引：MyISAM可以通过key_Buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。(2)InnoDB特性：具有较好的事务支持，支持4个事务隔离级别，支持多版本读；行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响；读写阻塞与事务隔离级别相关；具有非常高效的缓存特性，能缓存特性，也能缓存数据；整个表和主键以Cluster方式存储，组成一颗平衡树；所有SecondaryIndex都会保存主键信息；(3)NDBCluster分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分；支持事务：和Innodb一样，支持事务；可与MySqld不在同一主机：可以和MysqlId分开存在于独立的主机上，然后通过网络和MysqlId通信交互；内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存放在与内存中；9.脏读和幻读在说数据库的脏读和幻读时，首先要说数据库的事务，事务的4大特性：（1）原子性：事务是数据库的逻辑工作单位，它对数据库的修改，要么全部执行，要么全部不执行；（2）一致性：事务前后，数据库的状态都满足所有的完整性约束；（3）隔离性：并发执行的N个事务是隔离的，一个不影响一个，一个事务在没有commit之前，被修改的数据不可能被其他事务看到（如