剑指Offer(更新)之数据库 & JDBC（三）

1.数据库三范式是什么

第一范式：每一列属性都是不可再分的属性值，确保每一列的原子性

第二范式：（确保表中每列都和主键相关）一张数据表至少有一个主键

第三范式：（确保每列都和主键列直接相关，而不是间接相关）一张数据表有且只有一个主键

2.SQL分为哪几个大类

数据操纵语言（DML），数据定义语言（DDL）,数据控制语言（DCL）

• DML 数据操控:如select,insert,update,delete
• DCL 数据控制:如权限控制，grant,revoke
• DDL 数据定义:如create,alter,drop等表定义语句

3.SQL 约束有哪几种

六大约束：

• not null：非空
• default：默认
• unique key：唯一
• primary key：主键（包含非空为唯一两种约束）
• auto_increase：自动增长
• foreign key：外键

4.having、where、groupby的执行顺序

Where, Group By, Having, Order by

5.什么是事务，事物有哪四个特性（详细解释，其中由隔离性触发的问题有哪些）

事务就是将一组SQL语句放在同一批次内去执行；如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

• 原子性
• 一致性
• 隔离性

• 持久性

  （1）更新丢失：两事务同时更新，一个失败回滚覆盖另一个事务的更新

  （2）脏读：读到另一个未提交事务的数据

  （3）幻读：在一个事务过程中已经读取了一次表，此时恰巧另一个事务commit，导致这次事务再一次读取表时前后不一致。（表影响）

  （4）不可重复读：在一个事务过程中已经读取了一次a数据，此时恰巧另一个事务commit，导致这次事务再一次读取a数据时前后不一致。（行影响）

6.并发下事务会产生哪些问题，可用对应的哪个事务隔离级别来解决

• 读未提交（Read Uncommitted）（一事务写时禁止其他事务写）
• 读提交（Read Committed）（一事务写时禁止其他事务读写）
• 可重复读（Repeatable Read）（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）
• 串行化（Serializable）（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）

更新丢失	脏读	不可重复读	幻读
RU（读未提交）	避免
RC（读提交）	避免	避免
RR（可重复读）	避免	避免	避免
S（串行化）	避免	避免	避免	避免

7.Oracle和MySQL的区别（包括默认事务隔离级别）

• Oracle 的端口是1521 mysql的端口是3306
• Oracle中只有一个数值类型 number mysql中有B数值型和浮点型
• Oracle 是以用户分类的 ，mysql只有一个用户 是以数据库分类的；
• SQL语法分类： Oracle 中语法delete【from】 from可以省略，MYSQL不可以

• 组函数用法规则：

  mysql中组函数在select语句中可以随意使用，但在oracle中如果查询语句中有组函数，那其他列名必须是组函数处理过的，或者是group by子句中的列否则报错

• 自动增长的数据类型处理：

  MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

• 单引号的处理：

  MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

• 翻页的SQL语句的处理

  MYSQL处理翻页的SQL语句比较简单，用LIMIT开始位置，记录个数；ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置，并且只能用ROWNUM<100，不能用ROWNUM>80。

  以下是经过分析后较好的两种ORACLE翻页SQL语句(ID是唯一关键字的字段名)：

  语句一：

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;
           

语句二：

SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;
           

• 长字符串的处理

  长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节，如果要插入更长的字符串，请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告，返回上次操作。

• 日期字段的处理

  MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE，精确到秒，或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)年-月-日24小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式，可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’)

• 空字符的处理

  MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构，导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串

• 字符串的模糊比较

  MYSQL里用字段名like%‘字符串%’，ORACLE里也可以用字段名like%‘字符串%’但这种方法不能使用索引，速度不快，用字符串比较函数instr(字段名，‘字符串’)>0会得到更精确的查找结果。

8.什么是事务的传播行为，为什么要有传播行为

事务传播行为用来描述由某一个事务传播行为修饰的方法被嵌套进另一个方法的时事务如何传播。

• PROPAGATION_REQUIRED 如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。这是最常见的选择。
• PROPAGATION_SUPPORTS 支持当前事务，如果当前没有事务，就以非事务方式执行。
• PROPAGATION_MANDATORY 使用当前的事务，如果当前没有事务，就抛出异常。
• PROPAGATION_REQUIRES_NEW 新建事务，如果当前存在事务，把当前事务挂起。
• PROPAGATION_NOT_SUPPORTED 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
• PROPAGATION_NEVER 以非事务方式执行，如果当前存在事务，则抛出异常。
• PROPAGATION_NESTED 如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

9.drop table、delete from table和truncate的区别

truncate table和delete from table都可以删除整个数据库表的记录

delete

• DML语言
• 可以回退
• 可以有条件的删除
• DELETE FROM 表名 WHERE 条件

drop

• 用于删除表（表的结构、属性以及索引也会被删除）;
• drop table 表名

truncate

• DDL语言
• 无法回退
• 默认所有的表内容都删除

• 删除速度比delete快

  truncate table 表名

1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令Delete将被撤销，而TRUNCATE则不会被撤销。

2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比Delete操作后的表要快得多。

4、TRUNCATE不能触发任何Delete触发器。

5、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

6、不能清空父表。

10.列举几种表连接方式，有什么区别

• 笛卡尔积

  两表关联，把左表的列和右表的列通过笛卡尔积的形式表达出来。

select * from 表1 join 表2
           

• 左连接

  两表关联，左表全部保留，右表关联不上用null表示。

select * from t1 left join t2 on t1.id = t2.id;
           

• 右连接

  右表全部保留，左表关联不上的用null表示。

select * from t1 right join t2 on t1.id =t2.id;
           

• 内连接

  两表关联，保留两表中交集的记录。

select * from t1 inner join t2 on t1.id = t2.id;
           

• 左表独有

  两表关联，查询左表独有的数据。

select * from t1 left join t2 on t1.id = t2.id where t2.id is null;
           

• 右表独有

  两表关联，查询右表独有的数据。

select * from t1 right join t2 on t1.id = t2.id where t1.id is  null;
           

• 全连接

  两表关联，查询它们的所有记录。

select * from t1 left join t2 on t1.id = t2.id
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id
           

• 并集去交集

  两表关联，取并集然后去交集。

select * from t1 left join t2 on t1.id = t2.id where t2.id is null
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;
           

11.什么是视图，以及视图的优缺点

视图是一个虚拟表，是从数据库中一个或者多个表中导出来的表。

优点：

• 简单性；视图不仅可以简化用户对数据的理解，也可以简化他们的操作
• 安全性；通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。
• 逻辑数据独立性；视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。

12.什么是索引，索引的分类，索引有哪些优缺点，建立索引有哪些原则

索引是对数据库表中一列或多列的值进行排序的一种结构

记住两点：

（1）提高查询速度

（2）索引不是越多越好

• 主键索引（primary key）
• 唯一索引（unique）
• 常规索引（index）

• 全文索引（fulltext）

  优点：

  第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

  第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

  第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

  第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

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

  缺点：

  第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

  第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间

  就会更大。

  第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

  问题：

  原则：

  1．选择唯一性索引

  2．为经常需要排序、分组和联合操作的字段建立索引

  3．为常作为查询条件的字段建立索引

  4．限制索引的数目

  5．尽量使用数据量少的索引

  6．尽量使用前缀来索引

  7．删除不再使用或者很少使用的索引

  8 . 最左前缀匹配原则，非常重要的原则。

  9 .=和in可以乱序。

  10 . 尽量选择区分度高的列作为索引。

  11 .索引列不能参与计算，保持列“干净”。

  12 .尽量的扩展索引，不要新建索引。

13.如何优化数据库

优化数据库

当然最核心的是 怎么合理创建索引 怎么使用索引 索引失效 合理创建表字段 这4个方面

1：在子查询中慎重使用IN或者NOT IN语句，使用where (NOT) exists的效果要好的多或用 join 代替，小表关联大表（索引失效）

2：对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。（怎么创建索引）

3：最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.:

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

注意是尽量不要null，请根据实际业务选择

（合理创建表字段）

4：应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。（索引失效）

5：应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，（索引失效）

6：索引最左前缀匹配原则，SQL代码需要注意这个原则 （索引失效）

7：尽量避免向客户端返回大数据量，若数据量过大,建议分页或数量限制（设计上，这个和SQL没关系罗）

8：其他，非常多注意mysql 、oracle区别，版本问题 不同版本特性也不一样

14.描述JDBC连接数据库的步骤

JDBC编程的六个步骤：

1.加载驱动程序jar包

2.在java中加载驱动程序Class.forName(指定数据库的驱动程序)com.mysql.jdbc.Driver

3.创建数据库连接对象

String url = “jdbc:mysql://localhost:3306/ishop1?characterEncoding=utf8”;

String user = “root”;

String password = “123456”;

Connection conn = DriverManager.getConnection(url, user, password);

4.创建Statement 对象Statement state = conn.createStatement();

5.编写sql语句state.executeUpdate(insert_sql)

6.关闭数据库

15.什么是SQL注入，怎样防止

通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

如何防止：

• 采用PreparedStatement来避免sql注入，会自动对用户填写的数据进行验证(简单有效)；sql注入只对sql语句的准备(编译)过程有破坏作用；而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,而不再对sql语句进行解析,准备,因此也就避免了sql注入问题.
• 使用正则表达式过滤传入的参数（典型的SQL 注入攻击的正则表达式 ）
• 字符串过滤，敏感词过滤

16.什么是数据库连接池，实现原理以及优势

通过建立一个数据库连接池以及一套连接使用管理策略，使得一个数据库连接可以得到高效、安全的复用，避免了数据库连接频繁建立、关闭的开销。

优势：

1． 资源重用

由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数量）。

2． 更快的系统响应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。

3． 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接的配置，实现数据库连接池技术，几年钱也许还是个新鲜话题，对于目前的业务系统而言，如果设计中还没有考虑到连接池的应用，那么…….快在设计文档中加上这部分的内容吧。某一应用最大可用数据库连接数的限制，避免某一应用独占所有数据库资源。

4． 统一的连接管理，避免数据库连接泄漏

在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄漏。一个最小化的数据库连接池实现：

17.什么是存储过程，什么是函数，怎样创建存储过程和函数

存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

create procedure sp_name(proc_parameter[...])
begin
[characteristic ...] routine_body
end
           

sp_name：存储过程名称

proc_parameter：存储过程的参数列表

characteristic：存储过程的特性

routine_body：SQL语句的内容，可以用begin…end来标志SQL语句的开始和结束

18.什么是触发器，触发器有哪些作用

触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。

作用：

1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

2.审计。可以跟踪用户对数据库的操作。

3.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。

4.同步实时地复制表中的数据。

5.自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

创建：

CREATE TRIGGER trigger_name
        trigger_time
        trigger_event ON tbl_name
        FOR EACH ROW
        trigger_stmt
           

trigger_name:用来表示触发器的名称，可以自己设计

trigger_time:标识触发器的触发时机，取值是BEFORE或AFTER

trigger_event:标识触发事件，取值为INSERT,UPDATE和DELETE

tbl_name:标识建立触发器的表名，即在哪张表上建立触发器

trigger_stmt:触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END包含的多条语句。

举例：

delimiter $
create trigger tri_1
after insert on `order`
for each row 
begin 
update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
end$
delimiter ;