数据库相关基础

一、 触发器的作用

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

二、 什么是存储过程？用什么来调用？

存储过程：是事先经过编译并存储在数据库中的一段 SQL 语句的集合。

调用：

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

（2）可供外部程序调用，如JAVA程序

三、 存储过程的的优缺点

优点：

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

（2） 较少网络流量：存储过程的代码存在数据库中，调用时值需要传递存储过程的名称和参数即可，因此降低了网络传输的数据量

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

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

缺点：

（1） 可移植性差

（2） 更改比较繁琐：更改参数，更改GetValue（）调用

四、 存储过程与函数的区别

存储过程 函数
用于在数据库中完成特定的操作或者任务（如插入、删除等） 用于特定的数据（如选择）
程序头部声明用procedure 程序头部声明用 function
程序头部声明时不需描述返回类型 程序头部声明要描述返回类型，而且PL/SQL块中至少要包括一个有效的 return 语句
可以使用in/out/in out 三种模式的参数 可以使用in/out/in out 三种模式的参数
可作为一个独立的PL/SQL语句来执行 不能独立执行，必须作为表达式的一部分调用
可以通过out/in out 返回零个或多个值 通过return 语句返回一个值，且改值要与声明部分一致，也可通过 out 类型的参数带出的变量
SQL语句(DML 或SELECT)中不可调用存储过程 SQL语句(DML 或SELECT)中可调用函数

五、 索引的作用？优缺点？

作用：提高系统的性能

优点：

（1） 通过创建唯一索引，可保证数据库表中的每一行数据的唯一性

（2） 加快数据的检索速度

（3） 加速表与表之间的连接

（4） 在使用分组和排序语句进行数据检索时，显著减少查询分组和排序的时间

（5） 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能

缺点：

（1） 耗费时间：创建和维护索引要耗费时间，且耗费时间随数据量增加而增加

（2） 耗费空间：索引需要占物理空间，如建立聚簇索引耗费更大的空间

（3） 维护数据速度慢：对表进行增、改、删时，索引也要动态维护

六、 什么样的字段适合建索引？

（1） 唯一的字段

（2） 不为空的字段

（3） 经常被查询的字段

七、 索引的类型有哪些？

  1. 单行索引：Single column

  2. 多行索引：Concatenated

  3. 唯一索引：Unique

  4. 非唯一索引：NonUnique

  5. 函数索引：Function-based

  6. 分区索引：Partitioned

  7. 非分区索引：NonPartitioned

八、 什么是事务？什么是锁？

  1. 事务：一条语句或者一组语句要么全部成功，对数据中的某些数据成功修改；要么全部失败，回滚，数据中的数据还原到这些语句执行前

  2. 锁：
    （1）共享锁（S）：可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。
    （2）排它锁（X）：如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。
    （3）更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。
    （4）意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。
    （5）架构锁：防止修改表结构时，并发访问的锁。
    （6）大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误。

（1） 更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。

（2） 不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。

（3） 脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。

（4） 幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。

九、 什么叫视图？游标是什么？

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

游标：是对查询出来的结果集作为一个单元来有效的处理。

游标是SQL 的一种数据访问机制 ，游标是一种处理数据的方法。

众所周知，使用SQL的select查询操作返回的结果是一个包含一行或者是多行的数据集，如果我们要对查询的结果再进行查询，比如（查看结果的第一行、下一行、最后一行、前十行等等操作）简单的通过select语句是无法完成的，因为这时候索要查询的结果不是数据表，而是已经查询出来的结果集。游标就是针对这种情况而出现的。

游标的操作步骤：声明游标—>打开游标—>读取数据—>关闭游标—>删除游标

十、 视图的优缺点？

优点：

（1） 对数据库的访问，视图可以有选择性的选区数据库里的一部分。可选表中的行或列的子集

（2） 通过简单的查询可以从复杂查询中得到结果

（3） 维护数据的独立性，视图可以从多个表检索数据

（4） 对于相同的数据可产生不同的视图

缺点：

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

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

十一、列举几种表的连接方式，有什么区别？

内连接：只有两个元素表相匹配的才能在结果集中显示

外连接：

（1） 左外连接：左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示

（2） 右外连接：右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

全外连接：连接的表中不匹配的数据全部会显示出来

交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

十二、主键和外键的区别？

主键：在本表中是唯一的、不可唯空的，外键可以重复可以唯空

外键：外键和另一张表的主键关联，不能创建对应表中不存在的外键

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

1.建索引

2.减少表之间的关联

3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面

4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据

5.尽量用PreparedStatement来查询，不要用Statement

十四、数据库的三范式是什么？

第一范式：列不可再分

第二范式：行可以唯一区分，主键约束

第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束 且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上

十五、Union 和 union all 有什么不同？

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。

UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

十六、Varchar2 和 Varchar 有什么区别？

Char的长度是固定的，而varchar2的长度是可以变化的，比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar2（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。 char的效率要被varchar2的效率高。 目前varchar是varchar2的同义词，工业标准的varchar类型可以存储空字符串，但是oracle不能这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型varchar2,这个类型不是一个标准的varchar，他将在数据库中varchar列可以存储空字符串的特性改为存储null值，如果你想有向后兼容的能力，oracle建议使用varchar2而不是varchar

十七、Oracle 和 Mysql 的区别？

1）库函数不同。

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

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

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

\1. 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的语法的不同。

十七、乐观锁和悲观锁

乐观锁：乐观锁在操作数据时非常乐观，认为别人不会同时修改数据。因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据，如果别人修改了数据则放弃操作，否则执行操作。

悲观锁：悲观锁在操作数据时比较悲观，认为别人会同时修改数据。因此操作数据时直接把数据锁住，直到操作完成后才会释放锁，上锁期间其他人不能修改数据。

悲观锁的实现方式是加锁，加锁既可以是对代码加锁（如Java 的 synchronized 关键字），也可以是对数据加锁（如 mysql 的排它锁）

Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

乐观锁的实现方式：CAS 机制 和 版本号机制

1.版本号控制

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

注意：使用版本号作为判断数据变化的标记，实际上可以根据实际情况选用其他能够标记数据版本的字段，如时间戳等

2. CAS机制

CAS即compare and swap（比较与交换），是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。

CAS算法涉及到三个操作数

（1） 需要读写的内存值 V

（2） 进行比较的值 A

（3） 拟写入的新值 B

当且仅当 V 的值等于 A（A == V） 时，CAS 通过原子方式用新值 B 来更新 V 的值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断地重试，直到操作成功为止

乐观锁的缺点

  1. ABA问题：如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 **”ABA”问题。
    ** 假设有两个线程——线程1和线程2，两个线程按照顺序进行以下操作：

    (1)线程1读取内存中数据为A；

    (2)线程2将该数据修改为B；

    (3)线程2将该数据修改为A；

    (4)线程1对数据进行CAS操作

    在第(4)步中，由于内存中数据仍然为A，因此CAS操作成功，但实际上该数据已经被线程2修改过了。这就是ABA问题

  2. 循环时间长，开销大：自旋CAS（也就是不成功就一直循环执行直到成功）如果长时间不成功，会给CPU带来非常大的执行开销。 如果JVM能支持处理器提供的pause指令那么效率会有一定的提升，pause指令有两个作用，第一它可以延迟流水线执行指令（de-pipeline）,使CPU不会消耗过多的执行资源，延迟的时间取决于具体实现的版本，在一些处理器上延迟时间是零。第二它可以避免在退出循环的时候因内存顺序冲突（memory order violation）而引起CPU流水线被清空（CPU pipeline flush），从而提高CPU的执行效率。

  3. 只能保证一个共享变量的原子操作：CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但是从 JDK 1.5开始，提供了AtomicReference类来保证引用对象之间的原子性，你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们可以使用锁或者利用AtomicReference类把多个共享变量合并成一个共享变量来操作。

悲观锁的实现

场景：有用户A和用户B，在同一家店铺去购买同一个商品，但是商品的可购买数量只有一个

商品表t_goods

​

如果不加锁情况下，用户A和用户B同时下单会报错。

  • 加入当用户A对下单购买商品（臭豆腐）的时候，先去尝试对该数据（臭豆腐）加上悲观锁

  • 加锁失败：说明商品（臭豆腐）正在被其他事务进行修改，当前查询需要等待或者抛出异常，具体返回的方式需要由开发者根据具体情况去定义

  • 加锁成功：对商品（臭豆腐）进行修改，也就是只有用户A能买，用户B想买（臭豆腐）就必须一直等待。当用户A买好后，用户B再想去买（臭豆腐）的时候会发现数量已经为0，那么B看到后就会放弃购买

  • 在此期间如果有其他对该数据（臭豆腐）做修改或加锁的操作，都会等待我们解锁后或者直接抛出异常

首先关闭 MySql 数据库的自动提交属性。因为 MYSQL默认使用的是 autocommit 模式，即当我们执行一个更新操作后，MYSQL 会立即将结果进行提交（SQL：set autocommit = 0）

悲观锁加锁的SQL语句：select num t_goods where id = 2 for update

事务A：查询数据 num = 1

​ Select num from t_goods where id = 2 for update;

事务B：事务B会一直等待事务A释放锁，如果事务A长期不释放锁，那么最终事务B会报错。报错：Lock wait timeout exceeded;try restarting transaction,表示语句被锁

乐观锁的实现

在进行更新语句的时候，加上版本号一起判断，如果版本号相同，则成功，版本号 version + 1；否则为失败，提示重新查询最新数据