数据库面试题

数据库面试题

请写出你知道的主流数据库软件

MySQL：开源且跨平台
Oracle、DB2：跨平台不开源
SQL Server：不跨平台不开源

mysql的特点和应用

主要特点
– 适用于中小规模、关系型数据库系统
– 支持 Linux/Unix 、 Windows 等多种操作系统
– 使用 C 和 C++ 编写， 可移植性强
– 通过 API 支持 Python/Java/Perl/PHP 等语言
典型应用环境
– LAMP 平台， 与 Apache HTTP Server 组合
– LNMP 平台， 与 Nginx 组合

请写出MySQL的主配置文件和数据库目录，以及MySQL的端口号

/etc/my.cnf 主配置文件
/var/lib/mysql 数据库目录
默认端口号 3306

请写出数据库库名 命名规则？

仅可以使用数字，字母，下划线，不能纯数字
区分字母大小写，具有唯一性
不可使用指令关键字，特殊字符

请写出你知道的库管理命令（至少4个）

Show databases; // 显示已有的库
Use 库名 ; // 切换库
Select user(); 显示连接用户
Select database(); // 显示当前所在的库
Create database 库名 ; // 创建新库
Show tables; // 显示已有的表
Drop database 库名； // 删除库

请写出你知道的表管理命令（至少4个）

select * from 库名.表名; //查看表记录
install into 库名.表名 values(值列表); //插入表记录
update 库名.表名 set 字段=值；//修改表记录
delete from 表名; //删除表记录
desc 库名.表名； //查看表结构
drop table 库名.表名；//删除表

数据库增删改查命令语法

增：insert into 表名 values （字段值列表），（字段值列表）；

查：select 字段1，字段2 from 库名.表名 where 表达式；

更新：update 库名.表名 set 字段名=值，字段名=值 where 表达式；

删：delete from 库名.表名 where 表达式；

请写出四种数据类型种类

数值型
字符型
枚举型
日期时间型

请写出定长和变长的区别

定长： char( 字符数 )
最大长度 255 字符
不够指定字符数时在右边用空格补齐
字符数断超出时， 无法写入数据。
变长： varchar( 字符数 )
按数据实际大小分配存储空间
字符数断超出时， 无法写入数据。
大文本类型： text/blob
字符数大于 65535 存储时使用

9.请写出sql命令的四大分类

DDL 数据定义语言：create、alter、drop

DML 数据操作语言：insert、update、delete

DCL 数据控制语言：grant、revoke

DTL 数据事物语言：commit、rollback、savepoint（保存点，记录标记操作位置的，利用它可以回滚指定部分事务）

如何在数据库中执行命令行的任务

system

请写出你知道的索引类型？

Btree 、 B+tree 、 hash

请写出索引的优缺点？

索引优点

通过创建唯一性索引，指带有数据库并整合了数据库管理软件的计算机系统 可以保证数据库表中每一行数 据的唯一性可以加快数据的检索速度

索引缺点

当对表中的数据进行增加、删除和修改的时候，指带有数据库并整合了数据库管理软件的计算机系统 索引 也要动态的维护，指带有数据库并整合了数据库管理软件的计算机系统 降低了数据的维护速度
索引需要占物理空间

键值的类型有几种，分别是什么？

INDEX ：普通索引
UNIQUE ：唯一索引
FULLTEXT ：全文索引
PRIMARY KEY ：主键
FOREIGN KEY ：外键

请写出主键的使用规则？

一个表中只能有一个 primary key 字段
对应的字段值不允许有重复，指带有数据库并整合了数据库管理软件的计算机系统 且不允许赋 NULL 值
如果有多个字段都作为 PRIMARY KEY ，指带有数据库并整合了数据库管理软件的计算机系统 称为复合主 键，指带有数据库并整合了数据库管理软件的计算机系统 必须一起创建。
主键字段的 KEY 标志是 PRI
通常与 AUTO_INCREMENT 连用
经常把表中能够唯一标识记录的字段设置为主键字段

请写出index普通索引的使用规则？

一个表中可以有多个 INDEX 字段
字段的值允许有重复，指带有数据库并整合了数据库管理软件的计算机系统 切可以赋 NULL 值
经常把做查询条件的字段设置为 INDEX 字段
INDEX 字段的 KEY 标志是 MUL

在枚举类型中enum和set是什么意思？

从给定值集合中选择单个值，指带有数据库并整合了数据库管理软件的计算机系统 ENUM
定义格式： enum( 值 1, 值 2, 值 N)
从给定值集合中选择一个或多个值，指带有数据库并整合了数据库管理软件的计算机系统 SET
定义格式： set( 值 1, 值 2, 值 N)

请写出数据导入的命令格式

load data infile “目录/文件名” into table 库.表 fields terminated by “分隔符号” lines terminated by “\n”;

请写出数据导入时要注意的事项

字段分隔符要与文件内的一致
指定导入文件的绝对路径
导入数据的表字段类型要与文件字段匹配

请写出数据导出的命令格式

sql查询命令 into outfile “目录/文件名” fields terminated by “分隔符号” lines terminated by “\n”;

请写出数据导出的注意事项

导出的内容由 SQL 查询语句决定
导出的是表记录，不包括字段名
自动创建存储数据的文件
存储数据文件，具有唯一性

如何批量更新表记录

update 库名.表名 set 字段名=值，字段名=值；

如何删除表中所有记录

delete from 库名.表名；

请写出逻辑匹配中or和and和！的含义

或 与 非

请写出between 数字and 数字 ， distinct字段名 含义

在…之间… 去重显示

请写出下列聚集函数的含义

avg（字段名）：求平均值
sum：求和
min：统计最小值
max：统计最大值
count：统计个数

模糊匹配中%和_分别是什么意思

_表示1个字符
%表示0到n个字符

通常用法：where 字段名 like ’通配符‘；

在数据库中如何使用正则表达式(关键词)

where 字段名 regexp ‘正则表达式’

请写出limit 3,3的含义？

从第四行开始后的三行

用户授权和撤销

授权：grant 权限列表 on 库名 to 用户名@“客户端地址” identified by “密码” with grant option； 有授权权限

with grant option； 有授权权限

权限列表可以是all（所有），usage（无权限），select（个别权限），update（指定字段）

库名：* . * //所有库所有表 库名.* //一个库 库名.表名 //一张表

客户端地址

% 所有主机

192.168.4.% 网段内的所有主机

192.168.4.1 一台主机

loclahost 数据库服务器本机

撤销：revoke 权限列表 on 库名.表 from 用户名@“客户端地址”；

数据备份

完全备份：mysqldump -uroot -p密码 库名 > 目录/xx.sql

完全恢复：mysql -uroot -p密码 【库名】 < 目录/xx.sql

库名：-A //所有库 库名 // 单个库 库名表名// 单张表 -B 数据库1 数据库2 //多个库

请写出增量备份和差异备份的区别？

增量备份：备份上次备份后，所有新产生的数据
差异备份：备份完全备份后，所有新产生的数据

MySQL主从同步原理

master启用binlog日志

slave开启两个线程

IO：复制主binlog日志文件里Sql命令到本机中继日志文件

SQL：执行本机中继日志文件里的Sql语句实现和主的数据一致

PXC的特点（是一个集群4567，需要三台机器）

数据强一致性，无同步延迟

没有主从切换操作，无需使用虚拟ip

支持innodb存储引擎

多线程复制

部署使用简单

支持节点自动加入，无需手动拷贝数据

任何一台数据库服务器宕机都不影响用户存取数据，等到服务器运行后，自动同步宕机期间所有数据

MNA简介

数据库的自动故障切换操作能做到在0-30秒之间

MHA能保证故障切换过程中最大限度保证数据一致性，以达到真正意义上的高可用

MHA由管理节点和数据节点组成

MHA工作过程

由管理节点定时检测集群中的主节点，当主节点故障时，管理节点自动将拥有最新数据的从节点提升为新的主节点

Oracle和Mysql的区别？

1）库函数不同。

2）Oracle是用表空间来管理的，Mysql不是。

3）显示当前所有的表、用户、改变连接用户、显示当前连接用户、执行外部脚本的语句的不同。

4）分页查询时候时候，mysql用limit oracle用rownum

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. //如果只给定一个参数，它表示返回最大的记录行数目： mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 //换句话说，LIMIT n 等价于 LIMIT 0,n。

5）sql的语法的不同。

drop、truncate、 delete区别

最基本：

• drop直接删掉表。
• truncate删除表中数据，再插入时自增长id又从1开始。
• delete删除表中数据，可以加where字句。

（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3） 一般而言，drop > truncate > delete

（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。

（9） 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名 速度快,而且效率高,因为:?truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

数据库事务ACID特性？

（1）原子性：整个事务的操作看成一个操作，要么全部完成，要么全部不完成，事务发生异常会回滚。
（2）一致性：从一个一致的状态到另一个一致的状态，转账后总额不变。
（3）隔离性：隔离状态执行事务，使它们好像是系统在给定时间执行的唯一操作。
（4）持久性：事务对数据库所作的更改持久化在数据库中不会回滚。

SQL 有哪些优化方式？

1.选择最有效率的表名顺序(只在基于规则的优化器中有效)。
2.WHERE子句中的连接顺序。
3.SELECT子句中避免使用‘’。
4.减少访问数据库的次数。
5.在 SQLPlus , SQLForms 和 ProC 中重新设置 ARRAYSIZE 参数, 可以增加每次数据库访问的检索数据量 ,建议值为200。
6.使用 DECODE 函数来减少处理时间。
7整合简单,无关联的数据库访问。
8.删除重复记录。
9.用 TRUNCATE 替代 DELETE。
10.尽量多使用COMMIT。
11.用 Where 子句替换 HAVING 子句。
12.减少对表的查询。
13.通过内部函数提高SQL效率。
14.使用表的别名(Alias)。
15.用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
16.用EXISTS替换DISTINCT
17.SQL 语句用大写的（因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行）。