数据库-10.08

数据库知识：
1.drop,truncate,delete区别
drop直接删掉表。truncate删除表中数据，再插入时自增长id又从1开始。delete删除表中数据，可以加where字句。
（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 不记录在日志中，所以它不能激活触发器。
sql常见面试题：
https://blog.csdn.net/hundan_520520/article/details/54881208
2.数据库关键字
https://blog.csdn.net/QuinnNorris/article/details/71056445
char 长度固定，存储字符串’abc’，使用char(10)，表示存储的字符将占10个字节（包括7个空字符）
varchar长度可变，使用varchar2(10),，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储。
char类型的效率比varchar效率稍高
常见语句
创建一张表 create table
向一张表中插入一条信息 insert into…values
从表中删除一条信息 delete from
在where的位置，更新内容为set的值 update…set…where
删除表 drop table
向表中添加某个属性 alter table…add
将表中的某个属性删除 alter table…drop
自然连接、内连接、外连接
自然连接：是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。
内连接：基本与自然连接相同，不同之处在于自然连接奥球是同名属性列的比较，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。
外连接：自然连接时某些属性值不同则会导致这些元组会被舍弃，那如何保存这些会被丢失的信息呢，外连接就解决了相应的问题。外连接分为左外连接、右外连接、全外连接。外连接必须用using或on指定连接条件。
讲得很好的连接以及区别：
https://www.cnblogs.com/caozengling/p/5318696.html
3.数据完整性约束
数据完整性：存储在数据库中的所有数据值均正确的状态。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
4.数据完整性

1. 实体完整性（实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.。）
2. 域完整性（域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。）
3. 参照完整性（参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性）
4. 用户定义的完整性。
   非空约束（not null）：not null为列级约束，只能作用在列上
   唯一约束：新建表std2，字段：id（唯一），name（非空），score（可为空）若有唯一约束的字段出现了重复值，则会报错
   主键约束：新建表std3，字段：id（主键），name（非空），score（可为空）一个表只能有一个主键，主键非空且唯一
   外键约束：新建表std4，字段：id（主键），name（非空），score（可为空），friend_id（外键关联std3.id）若std4表中插入std3中不存在的id，则报错
   默认值约束：height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2
   NULL值：表示UNKNOWN
   用什么来确保表格里的字段只接受特定范围里的值？
   触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。因此，微软建议使用Check限制而不是其他的方式来限制域的完整性。
   5.存储过程
   存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。
   1）存储过程是预编译过的，执行效率高。
   2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
   3）安全性高，执行存储过程需要有一定权限的用户。
   4）存储过程可以重复使用，可减少数据库开发人员的工作量。
   缺点：移植性差
   6.事务
   事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。
   要将有组语句作为事务考虑，就需要通过ACID测试，即原子性(要么执行，要么都不执行)，一致性（合法的数据才可以被写入），隔离性和持久性。
   原子性：在一个事物中所有操作要么成功，要么失败。如银行转账，A向B账户转账1000元，这里可分为三个操作，1.A向B转账、2.银行处理、3.B账户收到转账。原子性就是保证这三个操作要么都成功，要么多失败，如果1、2操作成功，3失败了，那么1、2操作要进行回滚
   一致性：
   一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
   隔离性：
   两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。
   事务之间的相互影响：脏读，不可重复读，幻读，丢失更新
   脏读：一个事务读取了另一个事务未提交的数据，而这个数据是可能回读的。不可重复读：不可重复读意味着，在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。不可重复读：不可重复读意味着，在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。幻读：幻读，是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.
   持久性：
   事务结束后，事务处理的结果必须得到固化。即一旦提交，对数据库改变是永久的。
   7、视图 游标
   视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。
   游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
   8、数据库三范式
   https://www.cnblogs.com/wsg25/p/9615100.html
   第一范式：要求数据库表的每一列都是不可分割的原子数据项
   第二范式：需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关
   第三范式：需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
   简单的说：第一范式的关键词是列的原子性，第二范式是不能包含部分依赖，第三范式是不能包含传递依赖
   9、order by 和group by
   order by 排序查询、asc升序、desc降序
   group by 分组查询、having 只能用于group by子句、作用于组内，having条件子句可以直接跟函数表达式。
   使用group by 子句的查询语句需要使用聚合函数（AVG count min sum ）。
   10、行转列和列转行
   1）使用decode函数
   2）使用case when语句
   11、索引的底层实现
   mysql使用的是B+tree
   一个m阶的B+树具有如下几个特征：
   1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
   2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
   3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素
   http://www.mamicode.com/info-detail-2377871.html
   12、触发器的作用？
   触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
   触发时间：有before,after.触发事件：有insert,update,delete三种。触发类型：有行触发、语句触发
   13、内联接,外联接区别？
   内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。
   在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种
   内连接 只连接匹配的行
   左外连接 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行）以及右边表中全部匹配的行
   右外连接 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行）以及左边表中全部匹配的行
   全外连接 包含左、右两个表的全部行，不管在另一边的表中是否存在与它们匹配的行
   14 索引的作用？和它的优点缺点是什么？
   索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的 数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。
   缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。
   在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都 会按顺序被保存在表格。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。
   15 什么是内存泄漏？
   一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等 创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。
   16 什么是事务？什么是锁？
   事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。
   锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。
   17 什么叫视图？游标是什么？
   视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
   游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
   18 查询A(ID,Name)表中第31至40条记录
   如果ID连续 select * from A where ID between 31 and 40
   如果ID不连续 select top 10 * from A where ID not in (select top 30 ID from A)或者
   select top 40 * from tableA except select top 30 * from tableA
   19、查询表A中存在ID重复三次以上的记录
   select * From A where in(select ID from A group by id having count(id)>3)
   20、什么是主键?什么是外键?
   主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。
   21、用一条SQL 语句 查询出每门课都大于80 分的学生姓名
   select distinct name from table where name not in(select distinct name from table where fenshu <= 80)
   select name from table group by name having min(fenshu)>80
   22、删除除了自动编号不同, 其他都相同的学生冗余信息
   delete tablename where 自动编号 not in (select min(自动编号)from tablename group by 学号，姓名，课程编号，课程名称，分数）
   23 having和where有什么区别
   where：约束声明，在返回结果前起作用，不能与聚合函数一起使用（统计前过滤，参加统计的数据变少）
   having：过滤声明，在查询返回结果以后对查询结果进行过滤，其可以和聚合函数一起使用（统计后过滤，参加统计的数据无变化）
   24 查询选修了课程的学员人数
   select 学员人数=count(distinct s#) from sc
   使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
   select sn,sd from s where s# not in(select s# from sc where c#=’c5’)
   25 触发器的作用
   触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
   26 什么是存储过程？用什么来调用？
   存储过程是一些预编译的SQL语句。
   更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。一个存储过程替代大T_SQL语句 ，可以降低网络通信量，提高通信速率，可以一定程度上确保数据安全
   26 索引的作用？和它的优点缺点是什么？
   索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。
   缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。
   维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？
   我是这样做的，尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。
   27 什么叫视图？游标是什么？
   视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
   游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
   28 超键、候选键、主键、外键分别是什么？
   超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
   候选键：是最小超键，即没有冗余元素的超键。
   主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
   外键：在一个表中存在的另一个表的主键称此表的外键。
   29 三个范式？
   第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。（列不可分）
   第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。（不存在部分依赖，不可以把多种数据保存在同一张表中，即一张表只能保存“一种”数据。）
   第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y（不存在传递依赖，每一列都和主键直接相关，而不能间接相关。）
   30 数据库优化
   1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
   2…应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   　　select id from t where num/2=100
   　　应改为:
   　　select id from t where num=1002
   3.任何地方都不要使用 select * from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
   4.尽量避免大事务操作，提高系统并发能力。
   %%%%MySQL 对于千万级的大表要怎么优化？
   第一、优化你的sql和索引；
   第二、加缓存，memcached,redis；
   第三、以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；
   第四、如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；
   第五、如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
   第六、才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
   31 表的连接方式有几种
   外连接，内连接，交叉连接
   外连接：左外连接，右外连接，全外连接
   左连接 select student.* ,Score.* from student left join Score on student.id=Score.sid
   left join 以左表为准，查询出左表的所有数据，右表中有对应的则显示出来，没有对应的则显示为null.
   右连接 select student.* ,Score.* from student right join Score on student.id=Score.sid
   right join 以右表为准，查询出右表的所有数据，左表中有对应的则显示出来，没有对应的则显示为null.
   全连接 select student.* ,Score.* from student full join Score on student.id=Score.sid
   总结：full join 是为left和right的集合，某表中某一行在另一表中无匹配行，则相应列的内容为NULL。
   内连接：类似求交集
   select student.* ,Score.* from student inner join Score on student.id=Score.sid
   交叉连接
   select student.* ,Score.* from student cross join Score
   叉联接也称作笛卡尔积。相当于两个表中的所有行进行排列组合。
   32 视图
   是虚拟的表，与包含数据的表不一样，视图是使用时动态数据的查询，也就是一条select查询表的结果。
   优点：方便查询，在编写查询后，可以方便的重用它而不必知道基本查询细节。
   保护数据，可以授予用户访问表的特定部分的权限而不是整个表的。
   33 索引
   索引的建立可以加快查询速度，修改数据，但是也会增加时间开销。
   一下几点是避免使用索引
   a、小数据量的表
   b、需要频繁大批量的更新操作或者插入数据
   c、如果列中有大量null
   d、频繁操作的列不宜做索引
   34 事务
   属性：原子性，一致性，隔离性，持久性
   35 join 和in 哪个查询更快
   同样表大小
   in<=join