数据库基础与关系型数据库

什么存储过程？用什么调用？
- 存储过程就是一个预编译的SQL语句，它允许模块化的设计，就是说只需要一次创建，在同一程序中就能多次调用。执行效率比普通的SQL语句高
- 调用方式：
  - 可以使用命令对象进行调用
  - 可以使用外部程序进行调用(如：Java程序)
存储过程的优缺点
- 优点：
  - 在一个存储过程中能够执行一系列的SQL语句
  - 执行效率比普通的SQL语句高
  - 存储过程能够多次使用，减少数据库开发人员的工作量
  - 在一个存储过程中能够引用其他的存储过程，从而可以简化复杂的操作
  - 存储过程存放在数据库中，直接使用存储过程名进行使用，减少网络通讯
- 缺点：
  - 移植性差
索引的作用？它的优缺点？
- 索引就是一个特殊的查询表，数据库可以利用索引加速对数据的检索。索引可以唯一，索引创建通过是一张表中的一列或多列
- 优点：
  - 索引类似字典目录，我们不需要翻阅一本字典就能够知道数据存放的位置。
- 缺点：
  - 降低了数据的录入速度，并且增大了数据库的尺寸大小
怎样的字段适合建索引？
- 非空、唯一和经常被查询的字段

索引类型有哪些？

Single Column	单行索引
Concatenated	多行索引
Unique	唯一索引
NonUnique	非唯一索引
Function-based	函数索引
Domain	域索引
Partitoned	分区索引
NonPartitoned	非分区索引
B-tree： Normal Rever Key Bitmap	正常型B树反转型B树位图索引

触发器的作用？
- 触发器就是一种特殊的存储过程，是由事件(包括Insert、update、delete)触发执行的。触发器可以强制约束，来维护数据的完整性和一致性，可以追踪数据库中的操作不允许未经过允许的更新和变化
什么是事务？什么是锁？
- 事务：
  - 就是绑定在一起的SQL语句组，如果事务中一个操作失败，则全部操作全会失败，并且回滚到操作之前的状态。如果我们需要将一个SQL语句组作为事务处理，则必须通过ACID(原子性、一致性、隔离性、持久性)测试
    - 原子性：事务中的操作不能分割
    - 一致性：事务执行前后数据的完整性必须一致
    - 隔离性：事务执行时不允许被其他事务干预
    - 持久性：事务执行结束将数据持久化到数据库中
- 锁：
  - 在所有的DBMS(数据库管理系统)中，锁是实现事务的关键，锁能够保证事务的完整性和并发性。事务的隔离级别是通过锁的不同粒度实现的
数据库事务的四大特性？未设置隔离级别会出现哪些读的问题？通过设置事务隔离级别能够解决哪些读的问题？
- ACID：
  - 原子性：事务中的操作不能分割
  - 一致性：事务执行前后数据完整性必须一致
  - 隔离性：事务执行过程中不允许被其他事务干预
  - 持久性：事务执行结束将数据持久化到数据库中
- 未设置隔离级别可能产生的问题：
  - 脏读：当一个事务未提交其他事务就能访问此事务中的数据
  - 幻读：多次查询同一组数据，获得的结果不同(产生的操作：其他事务进行了Insert和delete操作，即获取的数据多了或者少了)
  - 不可重复读：多次查询同一数据，获得的结果不同(产生的操作：其他事务进行了update操作，即获取的数据被修改了)
- 隔离级别：
  - 读未提交(Read_Uncommited)：最低的隔离级别，当一个事务未提交其他事务就能访问此事务中的数据，会出现脏读、幻读和不可重复读的问题
  - 读已提交(Read_Commited)：当一个事务已提交其他事务才能进行访问数据，会出现幻读和不可重复读的问题，Oracle和SQLServer的默认隔离级别
  - 可重复读(Read_Repeatable)：事务能够保证每次使用的数据都是一致的，会出现幻读，MySQL的默认隔离级别
  - 序列化(Serializable)：最高的隔离级别，可以解决脏读、幻读和不可重复读的问题
什么是视图？什么是游标？
- 视图：
  - 视图是一个虚拟表，与基本表拥有同样的功能。我们可以对视图进行CRUD操作，这些操作也会对基本表产生影响。视图通常是一张表或多张表的行或列的子集，我们可以将视图理解为一个SQL语句，能显示一张表或多张表中的数据
- 游标：
  - 临时存储从数据库中提取的数据块
视图的优缺点？
- 优点：
  - 使用简单的SQL就能获得复杂SQL得到的结果
  - 更方便的访问数据库，可以在数据库中选择一部分数据查询
  - 维护了数据的独立性，可以在多张表中检索数据
  - 同一数据可以有不同的视图
- 缺点：
  - 性能上，在查询视图，会将视图查询转化为基础表的查询。如果视图是由复杂的SQL进行定义的话，那么不能够修改视图的数据
表连接的方式有哪几种？有什么区别？
- 内连接(inner join)：只显示连接表中匹配的数据
- 外连接：
  - 左外连接(left join)：左边的表为驱动表，驱动表中所有数据都会显示，匹配表中只显示匹配的数据
  - 右外连接(right join)：右边的表为驱动表，驱动表中所有数据都会显示，匹配表中只显示匹配的数据
- 全连接(union/union all)：连接表中所有数据都会显示
- 交叉连接(cross join)：笛卡尔效应，显示的结果是连接表数的乘积
主键和外键的区别？
- 主键：唯一且不可为空
- 外键：可以为空，与另外一张的主键关联
在数据库中查询语句速度很慢，如果优化？
- 建立索引
- 优化SQL，避免全表查询
- 减少表的关联
- 使用PreparedStatement来查询，不要使用Statement。前者能够预编译SQL语句，能够避免SQL注入的问题；后者存在SQL注入的问题
数据库的三大范式？
- 第一范式：列不可再分
- 第二范式：行可以唯一区分，即主键约束
- 第三范式：表中非主属性不能与其他表中非主属性关联，即外键约束
union和union all的区别？
- union：使用union关键词进行表连接，返回的结果会将重复的数据筛选掉
- union all：使用union all关键词进行表连接，直接返回结果不会对结果做处理。效率比union高
group by和order by的区别？
- order by：asc 升序；desc 降序
- group by：
  - 使用group by字句的查询语句必须使用聚合函数
  - having只能用于group by字句中，并且having字句可以直接跟函数表达式
表和视图的关系？
- 表：是关系型数据中存储数据使用的
- 视图：是一个查询的SQL语句，用于显示一张表或多张表或其他视图中的数据
truncate和delete的区别？
- Truncate的功能与不带where字句的delete的功能一致，都是删除数据库中所有的行，但是效率要比delete高，比delete使用的系统和事务日志资源少
- delete操作每次删除一行，并且在事务日志中记录删除的每行
- Truncate是通过释放存储表数据的数据页进行删除数据，并且在事务日志中只记录页的释放
- delete、Truncate、drop的区别：
  - delete：删除内容，不删除定义，不释放空间
  - Truncate：删除内容，释放空间，不删除定义
  - drop：删除内容和定义，释放空间
如何实现数据库结果去重？
- 使用distinct关键字
数据库常见的锁有哪些？
- 行锁：开销大、加锁慢、会出现死锁，但锁粒度最小，出现锁冲突的概率最小，并发性最高
- 表锁：开销小、加锁快、不会出现死锁(因为MyISAM会一次性获取所有MySQL需要的锁)，但锁粒度大，出现锁冲突的概率高，并发性差
- 页锁：开销和加锁介于行锁和表锁之间，会出现死锁，锁粒度介于行锁和表锁之间，并发性一般
- 悲观锁：顾名思义，数据库认为每个线程都会对同一数据进行修改，所以对每次访问都进行加锁，每次只允许获得锁的线程进行访问，其他线程只能等待此线程释放锁才有机会访问，适用于改多查少的场景
- 乐观锁：顾名思义，数据库认为每个线程都不会对同一数据进行修改，所以每次访问都不会进行加锁，但最终结果会进行判断是否被修改，我们可以使用版本号的机制进行控制，适用于查多改少的场景
常用的数据库引擎有哪些？
- InnoDB引擎：
  - 提供了对数据库ACID事务的支持，支持行级锁和外键约束。使用行级锁的目的就是为了处理大数据容量的数据库系统。此引擎不支持全文搜索，并且启动比较慢，不会保存表的行数，当我们在进行“Select count(*) from table”的操作时，需要扫描全表。由于锁的粒度的，在进行写的操作不会加锁。所以在需要事物的支持，并且写操作多于读操作的情况下，我们可以使用InnoDB引擎，而且在高并发的场景下效率也有提升
- MyISAM引擎：
  - MySQL的默认引擎，不支持对数据库ACID事务的支持，不支持行级锁和外键约束，但提供了表级锁。因为使用了表级锁，所以在进行写操作时会进行加锁，这样也就会导致效率比较低。但不同于InnoDB的是，MyISAM能够保存表的行数，当我们在进行“Select count(*) from table”的操作时，我们可以直接使用保存的数据。所以在不需要事务的支持，并且读操作多于写操作的情况下，我们可以使用MyISAM
InnoDB索引和MyISAM索引的区别？
- 数据结构都是B+树：
  - MyISAM：存储的内容是实际数据的地址。也就是说索引和实际数据是分开的，只是索引指向实际数据。这种索引被称为非聚集索引
  - InnoDB：存储的内容就是实际数据。这种索引被称为聚集索引
- 主索引的区别：InnoDB的实际数据文件就是索引文件；MyISAM的实际数据和索引是分开的
- 辅助索引的区别：InnoDB的辅助索引data域存储对应记录主键的内容而不是地址；MyISAM辅助索引和主索引没什么区别
什么是内存泄漏？
- 内存泄漏一般说的是堆内存的泄漏，堆内存就是程序在堆中为其分配的内存空间，大小任意，使用结束显示的释放内存
- 当我们使用关键字new等，创建对象时，堆就会为其分配空间，使用结束调用free或delete进行释放内存，否则此内存就无法使用，我们称内存泄漏
为什么要使用数据库连接池？
- 在一个小型的应用程序中，我们可以在使用数据库时在创建数据库的连接，这样也不会造成资源的浪费影响效率。但是，在一个大型的应用程序中，避免不了会频繁的使用数据库，就会频繁的创建和关闭数据库的连接，会极大减低系统的性能，这样就会导致连接的使用造成系统的瓶颈
- 数据库连接池目的：
  - 为了达到连接的复用。通过建立一个数据库连接池和数据库的管理策略，使得一个数据库连接能够高效和安全的复用，避免连接的创建和关闭带来的开销
- 数据库连接池的原理：
  - 数据库连接池在内部对象池中维护着一定数量的连接对象，并对外部暴露获取和返回的方法。当我们要使用连接时，调用获取的方法getConnection()方法进行获取，使用结束调用返回方法releaseConnection()进行返回，此时连接并没有关闭，只是返回给数据库连接池进行管理，等待下一次的使用。
数据库语句有多少类型？
- DDL(Data Definition Language)：数据库定义语言
  - Create语句：创建数据库和数据库的一些对象
  - Drop语句：删除数据库中的表、权限、索引等
  - Truncate语句：删除表中所有数据，通过释放存储数据的数据页进行删除数据
  - Alert语句：修改数据库的数据以及定义
- DQL(Data Query Language)：数据查询语言
  - select语句：用于查询数据
- DML(Data Manipulation Language)：数据库操作语言
  - Insert语句：在数据库表中插入一条数据
  - Update语句：在数据库中修改一条数据
  - Delete语句：在数据库中删除一条数据
- DCL(Data Control Language)：数据库控制语言
char、Varchar2和Varchar的区别？
- char的存储长度是固定的，Varchar和Varchar2都是可变的　　
  - 当在进行存储"a,b,c"时，使用char(20)存储时 20个字节都会被使用，其中“a，b，c”只占用3个字节，其他17个字节都为空；在使用Varchar(20)/Varchar2(20)时，只会占用3个字节，也就是说会根据数据的长度进行存储。从效率上来说，char要高于Varchar和Varchar2
- Varchar和Varchar2的区别：
  - 现在这两个类型没什么区别，只是Varchar允许存储空字符串，而Oracle不允许这么做，所以Oracle自己开发了一个数据类型Varchar2，将Varchar列的空字符串设置为null，如果我们想要向后兼容，那么Oracle推荐使用Varchar2
从数据库中随机取50条数据？
- Oracle：
  - select * from (select * from table order by id) where rownum<=50
- Mysql：------>Round()：对指定小数位的数据进行四舍五入 rand():获得0<=x<1的随机数
  - select * from table order by rand() limit 50 数据库中数据量大时效率极差
  - select * from table t1 join (select Round( Rand*((select MAX(id) from table)-(select MIN(id) from table)) +(select MIN(id) from table) ) ) t2 where t1.id=t2.id order by t1.id limit 50 数据库中数据量大时效率也不错
- SQLServer：
  - select top 50 * from table order by newid() newid()：在扫描数据的时候都会生成一个数，这个数是个随机数，没有大小顺序，order by newid() 意思是按照newid()产生的值进行排序所以就能得到无序的
commit在哪里运用?
- commit作用就是提交DML操作使用的，在没有commit时数据只存在于内存中，不会写入物理文件中
- 在进行更新操作后commit之前都是锁表的状态，其他事务无法访问这个表，如果没有进行commit操作，那么就会一直处于锁表状态
Oracle序列的作用？
- Oracle不像MySQL和SQLServer拥有自增属性设置，只能使用序列实现
- Oracle使用序列生成唯一的编号，用于处理一个自增的字段
- Oracle的序列是原子性对象，当访问了一个序列编号，在处理下一个请求之前会自动自增到下一个编号，从而确保不会出现重复值
MySQL和Oracle的区别？
- 对事务的支持：
  - MySQL：InnoDB引擎在行级锁的情况下支持事务
  - Oracle：完全支持事务
- 事务的隔离：
  - MySQL：默认是Repeatable Read 隔离级别，可能会出现幻读的问题
  - Oracle：默认是Commited Read隔离级别，可能会出现幻读和不可重复读的问题
- 事务的提交：
  - MySQL：默认自动提交事务
  - Oracle：需要我们执行commit操作
- 分页查询：
  - MySQL：使用limit关键字完成分页查询
  - Oracle：要使用伪列Rownum完成
- 并发性：
  - MySQL：默认是表级锁，对资源的锁定粒度较大，在一个session使用时，将整张表进行锁定，其他session无法进行操作；虽然InnoDB是行级锁，但这个行级锁依赖于索引，如果表中没有索引或SQL语句中没有索引，那么仍然使用表级锁，所以并发性较差
  - Oracle：使用行级锁，不依赖索引，对资源锁定的粒度小，也就是说只锁定SQL需要的资源，所以并发性较好
- MySQL：轻量级的数据库，免费，没有数据恢复功能
- Oracle：重量级的数据库，收费，Oracle公司提供很多功能
谈一谈数据库优化方法的经验？
- 数据库的设计：
  - 数据库表：字段类型、字段长度、注释、字段命名规范
  - 数据库索引：外键、关联字段、查询频率比较高的字段
    - 如果数据库表字段>20,则最多支持16个索引
    - 如果数据库表字段<20，则根据查询效率字段来定
  - 数据库视图：相当于一张临时表，业务中，尽量少使用
  - 数据库引擎：根据业务，选择对应的表引擎技术
  - 数据库存储过程：尽量少用
  - 数据库字符：UTF-8或者页面字符保持一致
  - 数据库监听器/触发器：一般用于调用任务或者备份还原
- 业务调用的SQL优化
  - 尽量关联表，效率最高关联4张表，如果多于4张表，则需要开启两个链接事务，但是这两个事务，必须在一个service当中
  - 如果是查询语句，则建议使用*
  - 如果是查询语句，where条件后面，最好使用索引字段进行关联
- 数据库服务器的搭建(集群)：
  - 主从配置
  - 读写分离
  - 自动化(容器)

posted @ 2019-09-26 09:51 SvaloR 阅读(235) 评论(0) 编辑收藏举报

刷新页面返回顶部

SvaloR

数据库基础与关系型数据库

公告