数据库概念知识

1 为什么要一定要设置主键?

其实这个不是一定的，有些场景下，小系统或者没什么用的表，不设置主键也没关系，mysql最好是用自增主键，主要是以下两个原因：如果定义了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则innodb 会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则innodb 会选择内置6字节长的ROWID作为隐含的聚集索引。所以，反正都要生成一个主键，那你还不如自己指定一个主键，提高查询效率！

2.自增主键用完了怎么办?

在mysql中，Int整型的范围（-2147483648~2147483648），约20亿！因此不用考虑自增ID达到最大值这个问题。而且数据达到千万级的时候就应该考虑分库分表了。

3.为什么不直接存储图片、音频、视频等大容量内容?

我们在实际应用中，都是文件形式存储的。mysql中，只存文件的存放路径。虽然mysql中blob类型可以用来存放大容量文件，但是，我们在生产中，基本不用！主要有如下几个原因：

• Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，查询效率会非常慢。
• 数据库特别大，内存占用高，维护也比较麻烦。
• binlog太大，如果是主从同步的架构，会导致主从同步效率问题！因此，不推荐使用blob等类型！

4.有几种不同的join?

Inner join, left join, right join, full join.

Inner join, 就是只要有一个列能够匹配， 就简单的返回两个table中所有的对应行。

select columnnames from tablename1 inner join tablename2 on columnname1=columnname2

Left join也就是left outer join。当有一个列能够匹配时就返回左边表中所有的行。

select columnnames from tablename1 left join tablename2 on columnname1=columnname2

Right join也就是right outer join，当有列匹配时，返回右边表格中所有的行。

select columnnames from tablename1 right join tablename2 on columnname1=columnname2

Full join也就是full outer join, 当有匹配时， 会返回左边表格和右边表格任意情况下的行组合。

5.Sql和my sql的区别是什么?

Sql是结构化查询语言，my sql是一个关系型数据库。

6.Delete和truncate的区别是什么?

Delete是dml，truncate是ddl。

Delete是用来删除一行或者多行。Truncate是用来删除一个表中的所有行。

我们可以用where跟delete结合使用。Truncate不可以。

7.Drop跟truncate的区别是什么?

truncate是删除表中所有的行，drop是删除整张表。这两个操作都不可以回撤。

8.什么是关系？有几种关系?

关系是指多表在数据库中的关联。

有4种关系。

1对1，多对一，多对多，一对多。

9.什么是主键？

唯一标识一条记录，不能有重复的，不允许为空。

10. 什么是外键？

表的外键是另一表的主键, 外键可以有重复的, 可以是空值。

11.如何随机的从表中取行?

select * from tablename sample 10

12.Sql server的TCP/ip端口是什么?

1433

13.什么是SQL？

SQL（结构化查询语言）是一种设计用于检索和操作数据的数据库。它属于美国国家标准协会（ANSI）的一种标准，可用于执行Select（选择）、Update（更新）、Delete（删除）和Insert（插入）等数据任务。

14.SQL中的Constraints(约束)是什么？

它可用于设置表中数据类型的限制。在创建或更新表语句时，可以使用约束。一些限制是：

NOT NULL、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、DEFAULT

15.什么是Index（索引）？

索引用于加速查询的性能。它可以更快地从表中检索数据。可以在一组列上创建索引。

16.Clustered（群集）和Non-Clustered Index（非群集）索引之间有什么区别？

聚集索引——有助于轻松检索数据，并且只有一个聚集索引与一个表一起分配。它会更改记录在数据库中的保存方式。

非聚集索引——与聚集索引相比，非聚集索引很慢。并且在非集群索引的情况下，该表可以具有多个索引，为表创建一个对象，该表是搜索后指向表的一个点。

17.什么是Aggregate Functions（聚合函数）？

它是一个返回单个值的数学函数。SQL中的聚合函数是：

AVG（）——返回平均值

COUNT（）——返回行数

MAX（）——返回最大值

MIN（）——返回最小值

ROUND（）——基于十进制规范，此函数对数字字段进行舍入

SUM（）——返回总和

18.主键是用自增还是UUID?

最好是用自增主键，主要是以下两个原因：

1.如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

2.如果使用非自增主键（如uuid），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到索引页的随机某个位置，此时MySQL为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成索引碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

不过，也不是所有的场景下都得使用自增主键，可能场景下，主键必须自己生成，不在乎那些性能的开销。那也没有问题。

19.CHAR 和 VARCHAR 的区别？

char和varchar类型声明长度表示用户想保存的最大字符数，其中char(M)定义的列的长度为固定的，M的取值可以0-255之间，当保存char值时，在它们的右边填充空格以达到指定的长度。

当检索到char值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。char存储定长数据很方便，char字段上的索引效率很高。

varchar(M)定义的列的长度是可变长度字符串，在MySQL5.0以上的版本中，varchar的数据类型长度支持到了65535，因为起始位和结束位占去了3个字节，所以其整体最大长度为65532字节（varchar的最大有效长度由最大行大小和使用的字符集确定）。

同样在char和varchar尾部加空格，检索时char类型后的被删掉，而varchar类型的保存。

20.主键为什么不推荐有业务含义?

最好是主键是无意义的自增ID，然后另外创建一个业务主键ID， 因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。

还有就是，带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。

21.货币字段用什么类型?

货币字段一般都用 Decimal类型， float和double是以二进制存储的，数据大的时候，可能存在误差。

22.索引应该建在那些列上？

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

23.什么是存储过程？有哪些优缺点？

存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)

存储过程的优点

• 能够将代码封装起来
• 保存在数据库之中
• 让编程语言进行调用
• 存储过程是一个预编译的代码块，执行效率比较高
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

存储过程的缺点：

• 每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
• 业务逻辑放在数据库上，难以迭代

24.字段为什么要定义为NOT NULL?

一般情况，都会设置一个默认值，不会出现字段里面有null，又有空的情况。主要有以下几个原因：

索引性能不好，Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。

如果某列存在null的情况，可能导致count() 等函数执行不对的情况。看一下2个图就明白了：

sql 语句写着也麻烦，既要判断是否为空，又要判断是否为null等。

25 索引的分类有哪些？

唯一索引：唯一索引不允许两行具有相同的索引值

主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

26 时间字段用什么类型?

这个看具体情况和实际场景，timestamp ，datatime ，bigint 都行！把理由讲清楚就行！

timestamp，该类型是四个字节的整数，它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间，是无法用timestamp类型存储的。

但是它有一个优势，timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区，该字段的值会自动变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！

datetime，占用8个字节，它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，它存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！

bigint，也是8个字节，自己维护一个时间戳，查询效率高，不过数据写入，显示都需要做转换。

27.什么是最左前缀原则？

最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

38.什么是事务？

事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

ACID — 数据库事务正确执行的四个基本要素包含：
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。

举个例子:A向B转账，转账这个流程中如果出现问题，事务可以让数据恢复成原来一样【A账户的钱没变，B账户的钱也没变】。

39.mysql有关权限的表都有哪几个?

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

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

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

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

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

40.什么情况下应不建或少建索引?

表记录太少

经常插入、删除、修改的表

数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段

41.数据表损坏的修复方式有哪些？

使用 myisamchk 来修复，具体步骤：

1.修复前将mysql服务停止。
2.打开命令行方式，然后进入到mysql的/bin目录。
3.执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。

OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）

42.MySQL数据库cpu飙升到100%的话他怎么处理？

列出所有进程 show processlist 观察所有进程 多秒没有状态变化的(干掉)

查看慢查询，找出执行时间长的sql；

explain分析sql是否走索引，sql优化；

检查其他子系统是否正常，是否缓存失效引起，需要查看buffer命中率；

43.维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。
最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

44.游标是什么？

是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

45数据库NULL是什么意思?

NULL(空)这个值是数据库世界里一个非常难缠的东西。

NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

46.关系型数据库和非关系型数据库区别？

关系型数据库的优点：

1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；

2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；

3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；

4、支持SQL，可用于复杂的查询。

5.支持事务

关系型数据库的缺点：

1、为了维护一致性所付出的巨大代价就是其读写性能比较差；

2、固定的表结构；

3、不支持高并发读写需求；

4、不支持海量数据的高效率读写

非关系型数据库优点：

1.无需经过sql层的解析，读写性能很高

2.基于键值对，数据没有耦合性，容易扩展

3.存储数据的格式：nosql的存储格式是key,value形式

非关系型数据库缺点：
不提供sql

47.查询语句的执行先后顺序？

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select—from—where—group by—having—order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序 与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据

48.什么情况下会造成死锁？

所谓死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

49死锁如何解决？

查出的线程杀死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

设置锁的超时时间
Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。

50.触发器的作用？

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

51在数据库中查询语句速度很慢，如何优化？

1.建索引 ,2.减少表之间的关联 3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面 4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据 5.尽量用PreparedStatement来查询，不要用Statement

52 什么是PL/SQL？

PL/SQL是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。

53使用SQL命令将教师表teacher中工资salary字段的值增加500，应该使用的命令?

Update teacher set salary=salary+500

54.mdf、.ldf、.tif 、.ndf哪个不是sql 数据库文件的后缀？

.tif

55.SQL语言中，条件“年龄BETWEEN 40 AND 50”表示年龄在40至50之间更准确的描述是?

包括40岁和50岁

56.drop、delete与truncate分别在什么场景之下使用？

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

不再需要一张表的时候，用drop

想删除部分数据行时候，用delete，并且带上where子句

保留表而删除所有数据的时候用truncate

57.MyISAM索引与InnoDB索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

58.有哪几种类型的SQL命令？

SQL命令分为以下类型：

DDL（数据定义语言） - 用于定义数据库的结构。

DCL（数据控制语言） - 用于为用户提供权限。

DML（数据操作语言） - 用于管理数据。

DQL（数据查询语言） - 所有命令都在SQL中，用于检索DQL中的数据。

TCL（事务控制语言） - 用于管理DML所做的更改。