面试准备--数据库

--

https://www.cnblogs.com/wenxiaofei/p/9853682.html

1.主键、外键、超键、候选键

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

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

2.为什么用自增列作为主键

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

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

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

3.触发器的作用？

是一个保证数据完整性和一致性的特殊存储过程，当某个表发生变动，触发对应触发器，则会自动做一些操作，例如改动另外一个对应表的内容。

好处：相对于外部程序、存储过程，触发器可以更快更高效的维护数据
坏处：（我自己的经验）触发器要用的恰到好处，一个大型应用里，触发器越少越好，触发器会使编程时源码的结构被迫打乱，为将来的程序修改、源码阅读带来很大不便

基本的语法规则是： 
create trigger 触发器名称(你自己命名的) on 表 for 用途(delete|update|insert) 
as 
delete|update|insert语句 

4.什么是存储过程？用什么来调用？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

调用：

1）可以用一个命令对象来调用存储过程。

2）可以供外部程序调用，比如：java程序。

语法：

create   procedure  porcedureName () 

begin 

select name from user; 

end;

-- 调用过程 

call porcedureName ();

--删除

drop

5.存储过程的优缺点？

优点：

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：移植性差（因为存储过程依赖于具体的数据库）

 

6.存储过程与函数的区别

本质是一样的

1.函数只有一个返回值

存储过程可以没有返回值，或者可以有多个输出参数

2.函数可嵌在SQL语句中使用，但存储过程不行

7.什么叫视图？游标是什么？

视图是一种虚拟的表，可从多个表中获取想要展示的数据。

游标可以在查询特定的集合中，一行一行或者多行读取出数据并处理

所谓视图（View）其实是执行查询语句后得到的结果，但这个查询结果可以仿真成数据表来使用，所以有人也称它为“虚拟数据表”，视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同的：数据表是实际存储记录的地方，然而视图并不保存任何记录，它存储的实际上是查询语句，其所呈现出来的记录实际来自于数据表

1.视图是一个虚拟表，是由查询语句产生的，不是真实存在的表；同真实的表一样，视图包含一系列带有名称的列和行数据。
2.好处有两点：
（1）安全，视图可以隐藏一部分数据，只展现想展现给别人看的那一部分数据
（2）在多表连接时可以使复杂的查询变得易于理解和使用。

游标是对查询出来的结果集进行处理的一个对象，游标可以定在该集合中的特定行，从而根据游标从
结果中检索出一行或多行。
游标是SQL 的一种数据访问机制 ，游标是一种处理数据的方法。


select语法学习：https://blog.csdn.net/tjjingpan/article/details/104358855

8.视图的优缺点

优点：

● 简单性。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。

● 安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。

● 逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

缺点：

● 性能：SQL Server必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，SQL Server也把它变成一个复杂的结合体，需要花费一定的时间。

● 修改限制：当用户试图修改视图的某些行时，SQL Server必须把它转化为对基本表的某些行的修改。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。

 

9.drop、truncate、 delete区别

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

drop > truncate > delete

delete有回滚，其他两个没有

truncate和delete * 一样

 

（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 不记录在日志中，所以它不能激活触发器。

 

10.什么是临时表，临时表什么时候删除?

临时表顾名思义，是一个临时的表，数据库不会将其序列化到磁盘上(有些也会序列化到磁盘上)而是存在于数据库服务器的内存中(因此会增加数据库服务器内存的消耗)，在使用完之后就会销毁。

临时表分为两种：会话临时表和全局临时表

区别在于

可用的作用域和销毁的时机不同。

会话临时表只在当前会话(连接)内可用，且在当前会话结束(断开数据库连接)后就会销毁；

全局临时表创建后在销毁之前所有用户都可以访问，销毁的时机是在创建该临时表的连接断开且没有其他会话访问时才销毁，实际上在创建全局临时表的会话断开后，其他用户就已经不能在访问该临时表了，但此时该临时表并不会立即销毁，而是等所有正在使用该全局临时表的会话(或者说连接)断开之后才会销毁。

当然有时考虑到内存占用的问题，我们也可以手动销毁(DROP)临时表。

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

如：

CREATE TEMPORARY TABLE tmp_table (

NAME VARCHAR (10) NOT NULL,

time date NOT NULL
);

select * from tmp_table;

 

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

关系型数据库：以关系模型来组织数据的数据库，而这个关系模型就是二维表（MySQL/SQL server/Oracle)

数据库事务必须具备ACID特性，ACID分别是Atomic原子性，Consistency一致性，
Isolation隔离性，Durability持久性。

优点：

1.使用简单，容易理解：二维表相对其他数据结构更直观，且关系型数据库可使用通用的SQL语句进行操作

2.复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询

缺点：

1.读写速度较差，当有海量数据且相关联，做查询会效率较低

2.高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库：（mongo DB/redis）

数据结构化存储方法的集合

优点：

1.格式灵活，可以是KeyValue，图片格式，文档格式

2.速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘

3.成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1.不提供sql支持，学习和使用成本较高；

2.无事务处理；

3.数据结构相对复杂，复杂查询方面稍欠。

 

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

一共有8种范式，所需要满足的范式越多，那表的层次及结构也就越复杂

通常我们设计只考虑到第3种即可

第一范式（1NF）：每个属性都不可再拆分；例如一张表里面有个地址，地址还可以分为城市，区域，街道；即不符合第一范式

第二范式：要求表中有主键，且表中其他字段需依赖主键；即可通过主键查询出各个信息

第三范式：要求表中不可以有其他表的存在，存储相同的信息，通常实现是在通过外键去建立关联

 

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

内连接：两个表的交集

左连接：SELECT * FROM test1 a LEFT JOIN test2 b ON a.id = b.id;

以test1为准，连接test2；无的话写NULL

右连接：SELECT * FORM test1 a RIGHT JOIN test2 b ON a.id = b.id;

以test2为准，连接test1；无的话写NULL

　全连接：（SELECT * FROM test1) UNION (SELECT * FROM test2);

test1和test2 的并集

 

交叉连接(笛卡尔积)语法：

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

SELECT * FROM table1 CROSS JOIN table2;

SELECT * FROM table1 JOIN table2;

SELECT * FROM table1 , table2;

 

14.varchar和char的使用场景?

1.长度区别：varchar不固定长度，char固定长度。char的长度是不可变的，而varchar的长度是可变的。

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

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

3.char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

 

15.SQL语言分类

SQL语言共分为四大类：

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

1. 数据查询语言DQL

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

SELECT
FROM
WHERE

2 .数据操纵语言DML

数据操纵语言DML主要有三种形式：

1. 插入：INSERT

2. 更新：UPDATE

3. 删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

4. 数据控制语言DCL

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

1. GRANT：授权。

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

3. COMMIT [WORK]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。

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

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

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