数据库程序员面试分类真题27

数据库程序员面试分类真题27简答题1.

Oracle和MySQL中的分组(GROUPBY)有什么区别?正确答案：Oracle对于GROUPBY是严格的，所有要SELECT出来的字段必须在GRO（江南博哥）UPBY后边出现，否则会报错：“ORA-00979:notaGROUPBYexpression”。而MySQL则不同，如果SELECT出来的字段在GROUPBY后面没有出现，那么会随机取出一个值，而这样查询出来的数据不准确，语义也不明确。所以，作者建议在写SQL语句的时候，应该给数据库一个非常明确的指令，而不是让数据库去猜测，这也是写SQL语句的一个非常良好的习惯。

下面给出一个示例。有一张T_MAX_LHR表，数据如下图所示，有3个字段ARTICLE、AUTHOR和PRICE。请选出每个AUTHOR的PRICE最高的记录(要包含所有字段)。ARTICLEAUTHORPRICE0001B3.990002A10.990003C1.690004B19.950005A6.96首先给出建表语句：CREATETABLET_MAX_LHR(ARTICLEVARCHAR2(30),AUTHORVARCHAR2(30),PRICENUMBER);--Oracte--CREATETABLET_MAX_LHR(ARTICLEVARCHAR(30),AUTHORVARCHAR(30),PRICEFLOAT);--MySQLoracle通用INSERTINTOT_MAX_LHRVALUES('0001','B',3.99);INSERTINTOT_MAX_LHRVALUES('0002','A',10,99);INSERTINTOT_MAX_LHRVALUES('0003','C',1.69);INSERTINTOT_MAX_LHRVALUES('0004','B',19.95);INSERTINTOT_MAX_LHRVALUES('0005','A',6.96);COMMIT;SELECT*FROMT_MAX_LHR;在Oracle中的数据：在MySQL中的数据：分析数据后，正确答案应该是ARTICLEAUTHORPRICE0002A10.990003C1.690004B19.95

对于这个例子，很容易想到的SQL语句如下：

SELECTT.ARTICLE,T.AUTHOR,MAX(T.PRICE)FROMT_MAX_LHRTGROUPBYT.AUTHOR;

SELECT*FROMT_MAX_LHRTGROUPBYT.AUTHOR;

在Oracle中执行上面的SQL语句报错：

在MySQL中执行同样的SQL语句不会报错：

虽然执行不报错，可以查询出数据，但是从结果来看数据并不是最终想要的结果，甚至数据是错乱的。下面给出几种正确的写法(在Oracle和MySQL中均可执行)：

(1)使用相关子查询

SELECT*FROMT_MAX_LHRT

WHERE(T.AUTHOR.T.PRICE)IN(SELECTNT.AUTHOR,MAX(NT.PRICE)PRICEFROMT_MAX_LHRNTGROUPBYNT.AUTHOR)

ORDERBYT.ARTICLE;

SELECT*FROMT_MAX_LHRT

WHERET.PRICE=(SELECTMAX(NT.PRICE)PRICEFROMT_MAX_LHRNTWHERET.AUTHOR=NT.AUTHOR)

ORDERBYT.ARTICLE;

(2)使用非相关子查询

SELECTT.*FROMT_MAX_LHRT

JOIN(SELECTNT.AUTHOR,MAX(NT.PRICE)PRICEFROMT_MAX_LHRNTGROUPBYNT.AUTHOR)T1

ONT.AUTHOR=T1.AUTHORANDT.PRICE=T1.PRICEORDERBYT.ARTICLE;

(3)使用LEFTJOIN语句

SELECTT.*FROMT_MAX_LHRTLEFTOUTERJOINT_MAX_LHRT1ONT.AUTHOR=T1.AUTHORANDT.PRICE＜T1.PRICE

WHERET1.ARTICLEISNULLORDERBYT.ARTICLE;

在Oracle中的执行结果：

在MySQL中的执行结果：

2.

Oracle和MySQL中的分组(GROUPBY)后的聚合函数分别是什么?正确答案：在Oracle中，可以用WM_CONCAT函数或LISTAGG分析函数；在MySQL中可以使用GROUP_CONCAT函数。示例如下：

首先给出建表语句：

CREATETABLET_MAX_LHR(ARTICLEVARCHAR2(30),AUTHORVARCHAR2(30),PRICENUMBER);--Oracle

--CREATETABLET_MAX_LHR(ARTICLEVARCHAR(30),AUTHORVARCHAR(30),PRICEFLOAT);--MySQLoracle通用

INSERTINTOT_MAX_LHRVALUES('0001','B',3.99);

INSERTINTOT_MAXLHRVALUES('0002','A',10.99);

INSERTINTOT_MAX_LHRVALUES('0003','C',1.69);

INSERTINTOT_MAX_LHRVALUES('0004','B',19.95);

INSERTINTOT_MAX_LHRVALUES('0005','A',6.96);

COMMIT;

SELECT*FROMT_MAX_LHR;

在MySQL中：

在Oracle中：

3.

MySQL的分区表是什么?如何查询MySQL是否支持分区?正确答案：表分区是指根据一定规则，将数据库中的一张表分解成多个更小的、容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成，每个分区都是一个独立的对象。分区有利于管理大表，体现了“分而治之”的理念。一个表最多支持1024个分区。

在MySQL5.6.1之前可以通过命令“showvariableslike'%have_partitioning%'”来查看MySQL是否支持分区。若have_partintioning的值为YES，则表示支持分区。从MySQL5.6.1开始，该参数已经被去掉了，而是用SHOWPLUGINS来代替。若有partition行且STATUS列的值为ACTIVE，则表示支持分区，如下：

此外，也可以使用表INFORMATION_SCHEMA.PLUGINS来查询，如下：

4.

MySQL的分区表有哪些?正确答案：MySQL支持的分区类型主要包括RANGE分区、LIST分区、HASH分区和KEY分区。分区表中对每个分区再次分割就是子分区(Subpartitioning)，又称为复合分区。在MySQL5.5中引入了COLUMNS分区，细分为RANGECOLUMNS和LISTCOLUMNS分区。引入COLUMNS分区解决了MySQL5.5版本之前RANGE分区和LIST分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。

KEY分区类似HASH分区，HASH分区允许使用用户自定义的表达式，但KEY分区不允许使用用户自定义的表达式。HASH仅支持整数分区，而KEY分区支持除了BLOB和TEXT的其他类型的列作为分区键。KEY分区语法为

PARTITIONBYKEY(EXP)PARTITIONS4;//EXP是零个或多个字段名的列表

在进行KEY分区的时候，EXP可以为空，如果为空，那么默认使用主键作为分区键。若没有主键则会选择非空唯一键作为分区键。

MySQL允许分区键值为NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。需要注意以下几点：

1)RANGE分区：NULL值被当作最小值来处理。

2)LIST分区：NULL值必须出现在列表中，否则不被接受。

3)HASH/KEY分区：NULL值会被当作零值来处理。

通过ALTERTABLE命令可以对分区进行添加、删除、重定义、合并、拆分等操作；通过INFORMATION_SCHEMA.PARTITIONS可以查询分区数、行数等信息；通过EXPLAINPARTITIONS可以查看分区表的执行计划。

5.

MySQL的分库分表和表分区(Partitioning)有什么区别?正确答案：分库分表是指把数据库中的数据物理地拆分到多个实例或多台机器上去。分表指的是通过一定规则，将一张表分解成多张不同的表。

表分区(Partitioning)可以将一张表的数据分别存储为多个文件。如果在写SQL的时候，遵从了分区规则，那么就能把原本需要遍历全表的工作转变为只需要遍历表里某一个或某些分区的工作。这样降低了查询对服务器的压力，提升了查询效率。如果分区表使用得当，那么也可以大规模地提升MySQL的服务能力。但是这种分区方式，一方面，在使用的时候必须遵从分区规则写SQL语句，如果不符合分区规则，性能反而会非常低下；另一方面，Partitioning的结果受到MySQL实例，或者说MySQL单实例的数据文件无法分布式存储的限制，不管怎么分区，所有的数据还是都在一个服务器上，没办法通过水平扩展物理服务的方法把压力分摊出去。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

6.

如果MySQL密码丢了，那么如何找回密码?正确答案：步骤如下：

1)关闭MySQL，/data/3306/mysqlstop或pkillmysqld。

2)mysqld_safe--defaults-file=/data/3306/f--skip-grant-table&。

3)mysql-uroot-p-S/data/3306/mysql.sock，按ENTER进入。

4)修改密码，UPDATEmysql.userSETpassword=PASSWORD("oldlhr123")WHEREuser='root'andhost='localhost';。

7.

mysqldump备份mysqllhr库及MySQL库的命令是什么?正确答案：mysqldump-uroot-plhr123-S/data/3306/mysql.sock-B--events-xMySQLmysqllhr＞/opt/$(date+%F).sql。

8.

如何不进入MySQL客户端，执行一条SQL命令，帐号User，密码Passwd，库名DBName，SQL为SELECTsysdate()。正确答案：采用-e选项，命令为mysql-uUser-pPasswd-DDBName-e"SELECTsysdate0;"。

9.

一个给定数据库中，有办法查询所有的存储过程和存储函数吗?正确答案：有。比如给定的数据库名为lhrdb，可以对INFORMATION_SCHEMA.ROUTINES表上进行查询。对于存储例程内包体的查询，可通过SHOWCREATEFUNCTION(对于存储函数)和SHOWCREATEPROCEDURE(对于存储例程)语句来查询，如下：

10.

MySQL5.7支持语句级或行级的触发器吗?正确答案：在MySQL5.7中，触发器是针对行级的，即触发器在对插入、更新、删除的行级操作时被触发。但是，MySQL5.7不支持FOREACHSTATEMENT。

11.

MySQL的注释符号有哪些?正确答案：MySQL注释符有三种：

1)≠...

2)"--..."(注意一后面有一个空格)

3)/*...*/

12.

如何查看MySQL数据库的大小?正确答案：查询所有数据的大小：

selectconcat(round(sum(data_length/1024/1024/1024),2),'GB')asdatafrominformation_schema.tables;

查看指定数据库的大小，例如，查看数据库lhrdb的大小：

selectconcat(round(sum(data_length/1024/1024/1024),2),'GB')asdatafrominformafion_schema.tableswheretable_schema='lhrdb';

查看指定数据库的某个表的大小，例如，查看数据库lhrdb中t_lhr表的大小：

selectconcat(round(sum(data_length/1024/1024),2),'MB')asdamfrominformation_schema.tableswheretable_schema='lbrdb'andtable_name='t_lhr';

13.

如何查看MySQL的位数?正确答案：有如下几种办法：

1)mysql-V

2)mysql＞showvariableslike'%version_%'；

3)whichmysql|xargsfile(Linux/Unix系统)

4)echoSTATUS|mysql-uroot-ppassword|grepVer

14.

MySQL有关权限的表有哪几个?正确答案：MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表包括user、db、tables_priv、columns_priv、proce_priv和host。MySQL启动的时候读取这些信息到内存中，或者在权限变更生效的时候，重新读取到内存中。这些表的作用如下：

1)user：记录允许连接到服务器的用户账号信息，里面的权限是全局级的。

2)db：记录各个账号在各个数据库上的操作权限。

3)tables_priv：记录数据表级的操作权限。

4)columns_priv：记录数据列级的操作权限。

5)host：配合db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

6)procs_priv：规定谁可以执行哪个存储过程。

15.

如果MySQL数据库的服务器CPU非常高，那么该如何处理?正确答案：当服务器CPU很高时，可以先用操作系统命令top观察是否是mysqld占用导致的，如果不是，那么找出占用高的进程，并进行相关处理；如果是mysqld造成的，那么可以使用showprocesslist命令查看里面数据库的会话情况，是否有非常消耗资源的SQL在运行。找出消耗高的SQL，看看执行计划是否准确，INDEX是否缺失，或者是否由于数据量太大造成。一般来说，肯定要kill掉这些线程(同时观察CPU使用率是否下降)，等进行相应的调整(例如，加索引、改写SQL、改内存参数)之后，再重新运行这些SQL。也有可能是每个SQL消耗资源并不多，但是突然之间，有大量的会话连接数据库导致CPU飙升，这种情况就需要与应用一起来分析为何连接数会激增，再做出相应的调整，例如，限制连接数等。

16.

如何查询某个表属于哪个库?正确答案：可以通过INFORMATION_SCHEMA库来查询，例如，若想查询表T_MAX_LHR是属于哪个库的，则可以执行：

以上结果说明，T_MAX_LHR表属于dbl库。

17.

一张表里面有ID自增主键，当INSERT了17条记录之后，删除了第15、16、17条记录，再把MySQL重启，再INSERT一条记录，这条记录的ID是18还是15?正确答案：根据表的类型不同而不同：

1)如果表的类型是MyISAM，那么是18。因为MyISAM表会把自主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。

2)如果表的类型是InnoDB，那么是15。InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

18.

MySQL中的mysql_fetch_now()和mysql_fetch_array()数的区别是什么?正确答案：这两个函数返回的都是一个数组，区别就是第一个函数返回的数组是只包含值，只能以row[0]、row[1]这样数组下标来读取数据；而mysql_fetch_array()返回的数组既包含第一种，也包含键值对的形式，可以这样读取数据，假如，数据库的字段是usemame、passwd，则可以row['username']，row['passwd']