秋招准备(二)数据库校招笔试面试整理

数据库的常用操作

这部分说白了就是sql语句的练习，打算最近把mysql必知必会刷一遍，这里先整理一点简单的

• having
  只有满足HAVING短语指定条件的组才输出。
  HAVING短语与WHERE子句的区别：作用对象不同。
  1》WHERE子句作用于基表或视图，从中选择满足条件的元组。
  2》HAVING短语作用于组，从中选择满足条件的组。

举列：查询选修了3门以上课程的学生学号
select sno from sc group by sno having count(cno)>3

• groupBy
  group_by的意思是根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组。

select 字段 from 表名 where 条件 group by 字段
或者
select 字段 from 表名 group by 字段 having 过滤条件
注意：对于过滤条件，可以先用where，再用group by或者是先用group by，再用having

同时groupby还可以配合聚合函数一起使用：

常用的聚合函数：count() , sum() , avg() , max() , min()

• orderBy
  使用order by，一般是用来，依照查询结果的某一列（或多列）属性，进行排序（升序：ASC；降序：DESC；默认为升序）。

select 字段 from 表名 order by 字段 DESC(ASC)

• 子查询
  MySQL子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。 另外，MySQL子查询可以嵌套在另一个子查询中。
  MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。 子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。

以下查询返回在位于美国(USA)的办公室工作的员工。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

左连接，右连接，全连接，内连接

• 左连接
  是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。
  左表数据全部查询，右表中不包含部分显示为null
• 右连接
  是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。
  与左连接同理，相反
• 内连接
  使用比较运算符根据每个表共有的列的值匹配两个表中的行。
• 全连接
  两张表中全部信息显示。
  **MySQL不支持FULL JOIN！！！**可以使用UNION ALL子句，将两个JOIN为如下：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
     SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

数据库事务

特性

四个特性，ACID，原子性，一致性，隔离性，持久性

• 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性：如果事务执行之前数据库是一个完整的状态，那么事务结束后，无论事务是否执行成功，数据库仍然是一个完整的状态。

数据库的完整状态：当一个数据库中的所有的数据都符合数据库中所定义的所有约束，此时可以称数据库是一个完整的状态。

• 隔离型：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。

• 持久性：指一个事务一旦被提交，他对数据库的影响是永久性的。

出现的问题

• 幻读：一个事务读取到另一个事务未提交的数据。（强调提交操作）
• 不可重复读：在当前事务中，读取到了另一事务提交的更新和删除的数据。（强调更新和删除操作）
• 幻读：在当前事务中，读取到了另一事务提交的插入的数据。（强调插入操作）

隔离级别

• 读未提交：不防止任何隔离型问题，不防止脏读/不可重复读/幻读问题
• 读已提交：可以防止脏读问题，但是不能防止 不可重复读/幻读问题。(oracle默认隔离级别)
• 可重复读：可以防止脏读/不可重复读，不能防止幻读。(mysql默认隔离级别)
• 序列化：数据库被设计为单线程，可以防止上述所有问题

mysql默认隔离级别

为什么mysql使用可重复读作为默认的隔离级别

• Binlog
  Binlog是MySQL的逻辑操作日志，广泛应用于复制和恢复，MySQL 5.1以前，Statement是Binlog的默认格式，即依次记录系统接受的SQL请求；5.1及以后，MySQL提供了Row和Mixed两个Binlog格式。

从MySQL 5.1开始，如果打开语句级Binlog，就不支持RC和Read-Uncommited隔离级别。要想使用RC隔离级别，必须使用Mixed或Row格式。

Mysql存储引擎

查看存储引擎：show engines
有MylSAM,InnoDB,MEMORY,MERGE,这里我们说一说MyISAM和InnoDB

• MylSAM

  • 如果表中绝大数都只是读查询，可以考虑 MylSAM
  • 不支持事务
  • 不支持外键
  • 支持全文索引

• InnoDB

  • 适合更新请求密集的表
  • 支持事务
  • 自动从灾难中恢复
  • 外键支持
  • 支持自动增加列AUTO_INCREMENT属性

• 两者的区别

  • InnoDB支持事务，MyISAM不支持
  • InnoDB支持外键，MyISAM不支持
  • InnoDB支持行锁，会死锁，并发性能好，MyISAM仅支持表锁，不会死锁，并发性能差
  • InnoDB必须有主键，没有指定就为每一行数据生成 不可见的ROWID列作为主键，MyISAM可以没有主键
    InnoDB不支持全文索引，MyISAM支持,5.6开始InnoDB也支持全文索引了
  • MyISAM内置了一个计数器来存储表的行数。执行 select count(*) 时直接从计数器中读取，速度非常快。而InnoDB不保存这些信息
  • InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件)，MyISAM将索引和数据分开进行存储。
  • 对AUTO_INCREMENT的处理方式不一样。如果将某个字段设置为INCREMENT，InnoDB中规定必须包含只有该字段的索引。但是在MyISAM中，也可以将该字段和其他字段一起建立联合索引。
  • delete from table的处理方式不一样。MyISAM会重新建立表。InnoDB不会重新建立表，而是一行一行的删除。
  • 如果执行大量的SELECT，MyISAM是更好的选择。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

Mysql行级锁

MYISAM引擎只支持表级锁，而INNODB引擎能支持够支持行级锁

• 共享锁
  又称读锁，是读取操作创建的锁，其它用户可以并发读取数据，但是任何事务都不能对数据进行修改。如果事务T对数据加上共享锁后，其它事务只能对A再加共享锁，不能加排他锁。

SELECT … LOCK IN SHARE MODE;
会对查询出的每一条数据加共享锁，如果其它线程再加排它锁就会阻塞。

• 排他锁
  又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

SELECT … FOR UPDATE;
在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。
另外mysql的innodb对数据的update,delete,insert都会给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。所以加上排它锁普通的select还是可以用的。

乐观锁和悲观锁

• 每次去哪数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
• 悲观锁每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
• 两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

drop、truncate和delete的区别

• 执行速度 drop > truncate > delete
• delete一行一行删除，truncate 删除表全部数据，drop 连表数据和表结构一起删除
• delete 是 DML 语句，没提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚
• 有FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句
• delete删除行会出现id不联系，truncate是id重新开始

索引

什么是索引

索引是一种数据结构，能够帮助我们快速的检索数据库中的数据

索引的类型

• 主键索引：主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
• 唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空。
• 普通索引：基本的索引类型，值可以为空，没有唯一性的限制。
• 全文索引：全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。

索引的数据结构

• Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。
• B+ Tree索引：B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。
• 其它区别：

哈希索引适合等值查询，但是无法进行范围查询

哈希索引没办法利用索引完成排序

哈希索引不支持多列联合索引的最左匹配规则

如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

聚簇索引、覆盖索引

B+树叶子节点可以存储的内容

InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值
在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引
主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询

为什么用b+树呢:

• b树查找不稳定，b+树因为都在叶子节点所以稳定
• 相同数据量的情况下,b+树比b树更矮胖，因此查询io也要少，真实场景一般高度为3
• b+树叶子节点有链表，所以范围查询更简单