MySQL面试题 - 第1篇 - 基本概念

主键、外键、超键、候选键

• **超键：**在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

• **候选键：**是最小超键，即没有冗余元素的超键。

• **主键：**数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

• **外键：**在一个表中存在的另一个表的主键称此表的外键。

为什么使用自增列作为主键

• 如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、

• 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、

• 如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

• 数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

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

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

触发器的作用

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

什么是存储过程

• 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说值需要创建一次，以后在此程序中就可以调用多次，如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要更快
• 调用
  • 可以使用一个命令对象来调用的存储过程
  • 可以通过外部程序调用，如：Java程序

存储过程的优缺点

• 优点：

  • 存储过程是预编译过的，执行效率高。
  • 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
  • 安全性高，执行存储过程需要有一定权限的用户。
  • 存储过程可以重复使用，可减少数据库开发人员的工作量。

• 缺点：

  • 移植性差

存储过程和函数的区别

什么是视图？游标是什么？

• 视图：

  • 是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

• 游标：

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

视图的优缺点

• 优点：

  • 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
  • 用户通过简单的查询可以从复杂查询中得到结果。
  • 维护数据的独立性，试图可从多个表检索数据。
  • 对于相同的数据可产生不同的视图。

• 缺点：

• 性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

drop、truncate、 delete区别

• 最基本：
  • drop直接删掉表。
  • truncate删除表中数据，再插入时自增长id又从1开始。
  • delete删除表中数据，可以加where字句。
• DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
• 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
• 一般而言，drop > truncate > delete
• 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view
• TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
• truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
• delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
• truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。
• 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果与事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
• Truncate table 表名 速度快,而且效率高,因为:?truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
• 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

什么是临时表？临时表什么时候删除

• 临时表可以手动删除：

DROP TEMPORARY TABLE IF EXISTS temp_tb;

• 临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。

• 创建临时表的语法与创建表语法类似，不同之处是增加关键字TEMPORARY，

CREATE TEMPORARY TABLE tmp_table (
    NAME VARCHAR (10) NOT NULL,
    time date NOT NULL
);

select * from tmp_table;

非关系型数据库和关系型数据库区别，优势比较?

• 非关系型数据库的优势：
• **性能：**NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
• **可扩展性：**同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。 关系型数据库的优势：
• **复杂查询：**可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• **事务支持：**使得对于安全性能很高的数据访问要求得以实现。
• 其他：
  • 对于这两类数据库，对方的优势就是自己的弱势，反之亦然。
  • NOSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能，比如MongoDB。
  • 对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如Redis set nx。

数据库范式，根据某个场景设计数据表

• 第一范式:(确保每列保持原子性)所有字段值都是不可分解的原子值。
• **第一范式是最基本的范式。**如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
• **第一范式的合理遵循需要根据系统的实际需求来定。**比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式
• 用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。
• 第二范式:(确保表中的每列都和主键相关)在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
• **第二范式在第一范式的基础之上更进一层。**第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
• 比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。
• 第三范式:(确保每列都和主键列直接相关,而不是间接相关) 数据表中的每一列数据都和主键直接相关，而不能间接相关。
• 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
• 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
• BCNF:符合3NF，并且，主属性不依赖于主属性。
• 若关系模式属于第二范式，且每个属性都不传递依赖于键码，则R属于BC范式。
• 通常BC范式的条件有多种等价的表述：每个非平凡依赖的左边必须包含键码；每个决定因素必须包含键码。
• BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的关系都必然满足第三范式。
• 还可以这么说：若一个关系达到了第三范式，并且它只有一个候选码，或者它的每个候选码都是单属性，则该关系自然达到BC范式。
• 一般，一个数据库设计符合3NF或BCNF就可以了。
• 第四范式:要求把同一表内的多对多关系删除。
• 第五范式:从最终结构重新建立原始结构。

什么是 内连接、外连接、交叉连接、笛卡尔积等?

• 内连接: 只连接匹配的行

• 左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

• 右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

• 例如1：

SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username

• 例如2：

SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

• 全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

• 交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

• 例如：

SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

• 注意：

• 很多公司都只是考察是否知道其概念，但是也有很多公司需要不仅仅知道概念，还需要动手写sql,一般都是简单的连接查询，具体关于连接查询的sql练习，参见以下链接：

• https://www.nowcoder.com/ta/sql

• https://leetcode-cn.com/problemset/database/

varchar和char的使用场景?

• char的长度是不可变的，而varchar的长度是可变的。

  • 定义一个char[10]和varchar[10]。
  • 如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

• char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。

  • char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。
  • varchar是以空间效率为首位。

• **char的存储方式是：****对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。

• varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。**

• 两者的存储数据都非unicode的字符数据。

SQL语言分类

• SQL语言共分为四大类：

  • 数据查询语言DQL
  • 数据操纵语言DML
  • 数据定义语言DDL
  • 数据控制语言DCL。

• 数据查询语言DQL

  • 数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：SELECT FROM WHERE

• 数据操纵语言DML

  • 插入：INSERT
  • 更新：IPDATE
  • 删除：DELETE

• 数据定义语言DDL

  • 数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
  • 表 视图 索引 同义词 簇
  • DDL操作是隐性提交的！不能rollback

• 数据控制语言DCL

  • 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

  • **GRANT：**授权。

  • **ROLLBACK [WORK] TO [SAVEPOINT]：**回退到某一点。回滚---ROLLBACK；回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

  • **COMMIT [WORK]：**提交。

  • 在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

    提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

    • 显式提交

    • 用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；

    • 隐式提交

    • 用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

    • 自动提交

    • 若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后， 系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

    • 参考文章：https://www.cnblogs.com/study-s/p/5287529.html

like %和-的区别

• 通配符的分类

• **%百分号通配符:**表示任何字符出现任意次数(可以是0次).

• **_下划线通配符:**表示只能匹配单个字符,不能多也不能少,就是一个字符.

• like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.

• 注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,SELECT * FROM products WHERE products.prod_name like '1000';只能匹配的结果为1000,而不能匹配像JetPack 1000这样的结果.

• %通配符使用: 匹配以"yves"开头的记录:(包括记录"yves") SELECT FROM products WHERE products.prod_name like 'yves%';

• 匹配包含"yves"的记录(包括记录"yves") SELECT FROM products WHERE products.prod_name like '%yves%';

• 匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意) SELECT * FROM products WHERE products.prod_name like '%yves';

• *通配符使用:* SELECT *FROM products WHERE products.prod_name like '_yves'; 匹配结果为: 像"yyves"这样记录. SELECT* FROM products WHERE products.prodname like 'yves'; 匹配结果为: 像"yvesHe"这样的记录.(一个下划线只能匹配一个字符,不能多也不能少)

注意事项:

• 注意大小写,在使用模糊匹配时,也就是匹配文本时,mysql是可能区分大小的,也可能是不区分大小写的,这个结果是取决于用户对MySQL的配置方式.如果是区分大小写,那么像YvesHe这样记录是不能被"yves__"这样的匹配条件匹配的.

• 注意尾部空格,"%yves"是不能匹配"heyves "这样的记录的.

• 注意NULL,%通配符可以匹配任意字符,但是不能匹配NULL,也就是说SELECT * FROM products WHERE products.prod_name like '%;是匹配不到products.prod_name为NULL的的记录.

技巧与建议:

• 正如所见， MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符。 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起 来是最慢的。

• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数

• 参考博文：https://blog.csdn.net/u011479200/article/details/78513632

count(*)、count(1)、count(column)的区别

• count(*)对行的数目进行计算,包含NULL
• count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
• count()还有一种使用方式,count(1)这个用法和count(*)的结果是一样的。

性能问题:

• 任何情况下SELECT COUNT( * ) FROM tablename是最优选择;

• 尽量减少SELECT COUNT() FROM tablename WHERE COL = ‘value’ 这种查询;

• 杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

• 如果表没有主键,那么count(1)比count(*)快。

• 如果有主键,那么count(主键,联合主键)比count(*)快。

• 如果表只有一个字段,count(*)最快。

• count(1)跟count(主键)一样,只扫描主键。count(*)跟count(非主键)一样,扫描整个表。明显前者更快一些。

最左前缀原则

• 多列索引：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

• 为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。

• 注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

• **最左前缀原则：**顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。