数据库

数据库原理

1、基本sql语句
			select * from table where 条件
			insert int table (字段) values (值)
			update table set 字段名=字段值 where 条件
			delete from table where 条件
			create table tablename (字段)
			drop table tablename
			grant(为用户授予系统权限)
			revoke(收回系统权限)
			
2、delete、truncate、drop的区别
	同：delete和truncate都可以删除一个表中的数据，而drop是直接删除这个表（包括结构和数据）。
	不同：a、truncate和drop是DDL(数据定义语言)，它会被隐式的提交，一旦执行将不能回滚。delete操作则是每次从表中删除一行数据，同时将删除的操作以日志的形式保存，以便将来回滚操作。
		 b、用delete操作后，被删除的数据占用的内存空间还在，还可以恢复。而用truncate和drop操作删除数据以后，被删除的数据会立即释放占用的存储空间，被删除的数据不能恢复。
		 c、速度drop > truncate > delete
		 
3、内连接和外连接的区别
	内连接只显示符合连接条件的记录，可能造成信息丢失
	外连接除了显示符合连接条件的记录外，还显示表中的记录
	select fieldlist from table1 inner join table2 on table1.column = table2.column
	
4、什么是事务，什么是ACID
	事务是指满足ACID特性的一组操作，可以通过commit提交一个事务，也可以通过rollback进行回滚。
	A-原子性-事务是一个不可分割的整体
	C-一致性-一个事务执行前后，数据库数据必须保持一致性的状态
	I-隔离性-一个事务所做的修改在最终提交以前，对其他事务是不可见的
	D-持久性-一旦事务提交，其所做的修改将会永远保存到数据库。及时系统崩溃，事务执行的结果也不能丢失。
	
5、什么是存储过程
	将为了完成特定功能的SQL语句集进行编译优化后，存储在数据库服务器中，用户通过指定存储过程的名字调用执行。
	其实存储过程就是对一系列SQL操作的批处理
	每次只能给一个变量赋值，不支持集合的操作
	好处：代码封装，保证安全性/代码复用/预先编译，因此性能高

6、并发一致性问题（针对事务的隔离性）
	丢失修改-T1先修改，T2随后修改，T2的修改覆盖了T1的修改。
	读脏数据-T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么T2读取的数据是脏数据。
	不可重复读-T2读取一个数据，T1对该数据做修改。如果T2再次读这个数据，此时读取的结果和第一次读取的结果不同。
	幻读-T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和第一次读		取的结果不同。
	
7、封锁实现并发控制
	封锁粒度-行级锁和表级锁
	封锁类型-a、读写锁
			 排它锁--写锁--X锁
			 共享锁--读锁--S锁
		    b、意向锁
		     解决表锁与之前可能存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗。
		     意向锁在原来X/S锁上引入了IX/IS，IX/IS是表锁，用来表示一个事务想要在表中的某个数据行上加X/S锁。
		     	数据库判断冲突：原来 step1：判断表是否已被其他事务用表锁锁表
							      step2：判断表中的每一行是否已被行锁锁住。
							 现在  step1：同上   step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁													   住了，因此，事务B申请表的写锁会被阻塞。
	封锁协议-一级封锁协议--事务T要修改数据A时必须加X锁，直到T结束才释放锁。
		    二级封锁协议--在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁。
		    三级封锁协议--在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才能释放S锁。
	两段锁协议-加锁和解锁分为两个阶段执行
		   MySQL的InnoDB存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同		   	   一时刻被释放，这被称为隐式锁定。
  
8、事务隔离级别实现并发一致性问题
	未提交读--事务中的修改，即使没有提交，对其它事务也是可见的。
	提交读--一个事务只能读取已经提交的事务所做的修改。
	可重复读--保证在同一个事务中多次读取同样数据的结果是一样的。
	可串行化--强制事务串行执行，需要加锁实现，而其它隔离级别通常不需要。
	
9、多版本并发控制MVCC？

10、Next-Key Lock？

11、范式
	第一范式--数据库表的每一列都是不可分割的基本数据项
	第二范式--每个非主属性完全函数依赖于键码，可以通过分解来满足。
	第三范式--每个非主属性都不依赖于函数，依赖于键码

12、什么是视图？
	视图是从数据库的基本表中选取出来的数据组成的逻辑窗口，简化数据查询语句，提高数据的安全性，它是一个虚表。
	
13、什么是游标?
	游标实质上是一种能从包含多条记录的结果集中每次提取一条记录的机制，游标总是与一句sql选择语句相关联。
	在存储过程中使用游标可以对一个结果集进行移动遍历。
	
14、什么是触发器？
	触发器是一种特殊类型的存储过程，它由事件触发。
	触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。
	触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。
BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

15、union和union all的区别
	union--表求并集以后会去掉重复的元素，所以会对产生的结果排序并删掉重复记录再返回结果
	union all--将两个结果集合并以后就返回结果

MySQL数据库

1、二叉查找树BST、平衡二叉树AVL、多路平衡树（B树）、B+树、红黑树的区别
	BST--是左结点小于根节点，右结点大于根节点的一种排序树。O（logN） 极端状况变成线性链表
	AVL--是左结点小于根节点，右结点大于根节点。并且还规定了左子树和右子树的高度差不得超过1。O（logN）
		AVL树每一个节点只能存放一个元素，并且每个节点只有两个子节点。当进行查找时，就需要多次磁盘IO。
                平衡树要求每个节点的左子树和右子树的高度差至多等于1，这个要求实在是太严了，导致每次进行插入/删除节点
                的时候，几乎都会破坏平衡树的第二个规则，进而我们都需要通过左旋和右旋来进行调整，使之再次成为一颗符合要求的平衡树。
                所以平衡树适合查找，不适合插入、删除操作。
	B树--m阶的B树规定了：根结点至少有两个子女；每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m；
		每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m；所有的叶子结点都位于同一层；
		每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。
		B树的查找不稳定，并且遍历麻烦。
	B+树--每个非叶子结点存放的元素只用于索引作用，所有数据保存在叶子结点。
                B+树最多有三层，因为非叶子节点只能存放指针，所以一次可以读取更多的非叶子节点，使磁盘IO的次数减少，查找稳定。
		因为所有的数据都在叶子结点。每个叶子结点也通过指针指向构成了一种链表结构，所以遍历数据也会简单很多。
	红黑树--节点是红色或黑色；根节点是黑色；每个叶子节点都是黑色的空节点（NIL节点）；
		   每个红色节点的两个子节点都是黑色。
		   从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。O(log2n)
		   树的最长路径不大于两倍的最短路径的长度
		   红黑树广泛用于TreeMap、TreeSet，以及jdk1.8后的HashMap。
	Mysql设计利用了磁盘预读原理，将一个B+Tree节点大小设为一个页大小，在新建节点时直接申请一个页的空间，
这样就能保证一个节点物理上存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了每个Node节点只需要一次I/O操作。
		   
2、MySQL索引（B+树索引，哈希索引，全文索引，空间数据索引）
	索引本质上是数据结构，排序＋查找

3、查询性能优化
	使用EXPLAIN关键字分析SQL语句。
		id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
		id--同上下，不同大的先执行
		select_type--查询类型，有简单查询、联合查询、子查询等
		type--显示查询使用了哪种类型，system > const > eq_ref > ref > range > index > all
		key--实际使用的索引
		ref--哪一列的索引被使用了
		rows--扫描的行数
	查询优化
		a、慢查询的开启并捕获
		b、explain+慢SQL分析
		c、show profile查询SQL的执行细节和生命周期
		d、SQL数据库服务器的参数调优
	
4、索引的优化
	全值匹配我最爱  最左前缀要遵守
	带头大哥不能死  中间兄弟不能断
	索引列上少计算  范围之后全失效
	LIKE百分写最右  覆盖索引不写量
	不等空值还有or  索引失效要少用
	VAR引号不可丢  SQL高级也不难
	
5、InnoDB和MYISAM的区别
	事务--InnoDB是事务型的，可以使用Commit和Rollback语句
	并发--MyISAM只支持表锁，InnoDB支持行锁
	外键--InnoDB支持外键
	备份--InnoDB支持在线热备份
	崩溃回复--MyISAM崩溃后发生损坏的概率比InnoDB高很多，恢复速度也更慢。
	其它特性--MyISAM支持压缩表和空间数据索引
	
6、水平切分和垂直切分

7、主从复制
	binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
	I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
	SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

8、读写分离
	主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
	通常使用代理方式实现
	
9、添加索引--alter table  用于普通索引，唯一索引，主键索引。
		   create index 用于普通索引，唯一索引。
   删除索引--alter table
   		    drop index
   使用explain语句判断索引是否生效
   
10、主键和外键的区别
表中经常有一个列或多个列的组合，其值能唯一的标识表中的每一行。这样的一列或多列称为表的主键。
外键：用来表示表与表之间联系，是表中的一个列。
主键和外键都是用于维护数据库完整性的一种机制。
区别：--主键是唯一标识一条记录，不能重复，不允许为空；而外键可以重复，可以是空值；
	 --主键是用来保持数据完整性，外键是用来建立与其他表联系用的；
	 --主键只有一个，外键可以有多个。

问题

如果表连接时，右边表有好几个字段值和连接字段值一样，信息会都保存吗