数据库 & JDBC面试题整理
- 数据库 & JDBC
- 数据库三范式是什么
- SQL分为哪几个大类
- SQL 约束有哪几种(Constraints)
- having、where、groupby的执行顺序
- 什么是事务,事物有哪四个特性(详细解释,其中由隔离性触发的问题有哪些)
- 并发下事务会产生哪些问题,可用对应的哪个事务隔离级别来解决
- MySQL和Oracle的区别(包括默认事务隔离级别)
- 什么是事务的传播行为,为什么要有传播行为
- drop table、delete from table和truncate的区别
- 列举几种表连接方式,有什么区别
- 什么是视图,以及视图的优缺点
- 什么是索引,索引的分类,索引有哪些优缺点,建立索引有哪些原则
- 如何优化数据库
- 描述JDBC连接数据库的步骤
- 什么是SQL注入,怎样防止
- 什么是数据库连接池,实现原理以及优势
- 什么是存储过程,什么是函数,怎样创建存储过程和函数
- 什么是触发器,触发器有哪些作用
数据库 & JDBC
数据库三范式是什么
第一范式:当关系模式R的所有属性都不能再分解为更基本的数据单位时。简记为1NF。
第二范式:满足第一范式,且R得所有非主属性都完全依赖于R的每一个候选关键属性。简记为2NF。
第三范式:满足第二范式,且X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字。简记为3NF。
SQL分为哪几个大类
DDL(数据定义语言) - Create、Alter、Drop 这些语句自动提交,隐式提交。(Data definition language)
create table/view/index/syn/cluster
drop table ...
alter table ...
DQL(数据查询语言) - Select 查询语句。(Data query language)
DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit显示提交。(Data manipulation language)
TCL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。(Transaction control language)
DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。(Data control language)
SQL 约束有哪几种(Constraints)
- PRIMARY KEY 约束 - PRIMARY KEY 约束唯一标识数据库表中的每条记录。
- FOREIGN KEY 约束 - 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
- NOT NULL约束 - NOT NULL 约束强制列不接受 NULL 值。
- UNIQUE约束 - UNIQUE 约束唯一标识数据库表中的每条记录。
- CHECK约束 - CHECK 约束用于限制列中的值的范围。
- DEFAULT 约束 - DEFAULT 约束用于向列中插入默认值。
having、where、groupby的执行顺序
from–where–group by–having–select–order by
什么是事务,事物有哪四个特性(详细解释,其中由隔离性触发的问题有哪些)
- 数据库事务(简称:事务)是数据库管理系统执行过程中的一个逻辑单位。
- 事务四个特性(ACID)
原子性(Atomicity)- 原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚。
一致性(Consistency)- 也就是说一个事务执行之前和执行之后都必须处于一致性状态。
隔离性(Isolation)- 多个并发事务之间要相互隔离。
持久性(Durability)- 一个事务一旦被提交了,对数据库中的数据的改变就是永久性的。
事务并发引起的问题
脏读:一个事务处理过程里读取了另一个未提交的事务中的数据。
幻读:一前后多次读取,数据总量不一致。
不可重复读:一个事务内读取表中的某一行数据,多次读取结果不同。
脏读是读取前一事务未提交的脏数据,不可重复读是重新读取了前一事务已提交的数据。
幻读和不可重复读的区别 - 这两者有些相似。但不可重复读重点在于update和delete,单个数据,而幻读的重点在于insert,数据整体。
如果使用锁机制来实现这两种隔离级别,在可重复读中,该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复 读了。但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会 发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。需要Serializable隔离级别 ,读用读锁,写用写锁,读锁和写锁互斥,这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。
隔离性
读未提交(READ_UNCOMMITTED)
读已提交(READ_COMMITTED)
读已提交是不同的时候执行的时候只能获取到已经提交的数据。
可重复读(REPEATABLE_READ)
可重复读取,在数据读出来之后加锁,类似"select * from XXX for update",明确数据读取出来就是为了更新用的,所以要加一把锁,防止别人修改它。
串行化(SERIALIZABLE)。不管多少事物,挨个运行完一个事物的所有子事物之后才可以执行另外一个事物里面的所有子事物。
并发下事务会产生哪些问题,可用对应的哪个事务隔离级别来解决
MySQL和Oracle的区别(包括默认事务隔离级别)
MySQL | Oracle | |
---|---|---|
服务支持 | 轻量型数据库,并且免费,没有服务恢复数据。 | 重量型数据库,收费,Oracle公司对Oracle数据库有任何服务。 |
默认端口号 | 3306 | 1521 |
对事务的提交 | 自动提交 | 不自动提交 |
主键 | 在创建表时只要指定表的主键为auto increment,Mysql将自动增长 | 主键一般使用的序列,插入记录时将序列号的下一个值付给该字段即可 |
分页查询 | MySQL是直接在SQL语句中写"select… from …where…limit m, n",有limit就可以实现分页 | Oracle则是需要用到伪列ROWNUM和嵌套查询 |
对事务的支持 | MySQL在innodb存储引擎的行级锁的情况下才可支持事务 | Oracle则完全支持事务 |
事务隔离级别 | read commited的隔离级别 | repeatable read的隔离级别 |
并发性 | 以表级锁为主, 虽然InnoDB引擎的表可以用行级锁,但这个行级锁的机制依赖于表的索引 | 行级锁,对资源锁定的粒度要小很多,只是锁定sql需要的资源,不依赖与索引 |
保存数据的持久性 | 数据库更新或者重启,则会丢失数据 | 把提交的sql操作线写入了在线联机日志文件中,保持到了磁盘上,可以随时恢复 |
Mysql 和 Oracle分页查询
select * from table limit (start-1)*limit,limit
SELECT * FROM
(SELECT A.*, ROWNUM RN FROM
(SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40
)
WHERE RN >= 21
什么是事务的传播行为,为什么要有传播行为
事务传播行为用来指的就是当一个事务方法被另一个事务方法调用时,这个事务方法应该如何进行。
为了保证系统不出错。事务之间如何交互 。
drop table、delete from table和truncate的区别
DROP | TRUNCATE | DELETE |
---|---|---|
DDL语言 | DDL语言 | DML语言 |
无法回退 | 无法回退 | 可以回退 |
表内容和表结构删除 | 表内容删除 | 表结构在 |
速度最快 | 速度快 | 删除速度慢,要逐行删除 |
列举几种表连接方式,有什么区别
sql表连接分成外连接{左外,右外,全外}、内连接和交叉连接。
什么是视图,以及视图的优缺点
视图(VIEW)也被称作虚表,即虚拟的表,是一组数据的逻辑表示,其本质是对应于一条SELECT语句,结果集被赋予一个名字,即视图名字。
优点:
(1)为用户集中数据,简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中,定义视图可将它们集中在一起,从而方便用户的数据查询和处理。
(2)屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构,并且数据库表的更改也不影响用户对数据库的使用。
(3)简化用户权限的管理。只需授予用户使用视图的权限,而不必指定用户只能使用表的特定列,也增加了安全性。
(4)便于数据共享。各用户不必都定义和存储自己所需的数据,可共享数据库的数据,这样同样的数据只需存储一次。
(5)可以重新组织数据以便输出到其他应用程序中。
缺点:
(1)性能差
sql server必须把视图查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,即使是视图的一个简单查询,sql server也要把它变成一个复杂的结合体,需要花费一定的时间。
(2)修改限制
当用户试图修改试图的某些信息时,数据库必须把它转化为对基本表的某些信息的修改,对于简单的试图来说,这是很方便的,但是,对于比较复杂的试图,可能是不可修改的。
什么是索引,索引的分类,索引有哪些优缺点,建立索引有哪些原则
索引
索引是为了加速对表中数据行的检索而创建的一种分散的存储结构实现原理(B+树) 通俗点说类似于一本书的目录,通过目录可以快速查到你想要的数据。
索引的优缺点
优点: 大大缩短查询时间,加索引至少比普通查询快100倍
缺点:
(1)索引的创建和维护需要时间,随着数量增加需要的时间也会增加
(2)索引是需要占用物理空间的,(也就是常说的用空间换时间)表空间是有最大上限设置的 如果一个表有大量索引会更快的到达上限值
索引使用的注意事项
索引的设计原则
1.选择唯一性索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。
2.为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
3.为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
4.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
5.尽量使用数据量少的索引
如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6.尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
7.删除不再使用或者很少使用的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
8 . 最左前缀匹配原则,非常重要的原则。
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
9 .=和in可以乱序。
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
10 . 尽量选择区分度高的列作为索引。
区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就 是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条 记录
11 .索引列不能参与计算,保持列“干净”。
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
12 .尽量的扩展索引,不要新建索引。
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
索引分类
1.普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
CREATE INDEX index_name ON table(column)--创建
ALTER TABLE table_name ADD INDEX index_name ON (column)--修改
2.唯一索引
索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
CREATE UNIQUE INDEX indexName ON table(column)
ALTER TABLE table_name ADD UNIQUE indexName ON (column)
3.主键索引
是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
CREATE TABLE 'table' (
'id' int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY ('id')
);
4.复合索引(组合索引)
用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引) 查询时使用创建时第一个开始 索引才有效 使用遵循左前缀集合
复合语句:
ALTER TABLE `table` ADD INDEX indexName (name,xb,age);
修改性能和检索性能是互相矛盾的,切记一点要注意,
如何优化数据库
- 设计方面,考虑最优字段,尽量不要设置空值数据,虽然稍微的放空填入的数据值会使用户操作体验更好
- 查询方面,尽量使用子查询完成多个逻辑的SQL操作,多使用JOIN,对于临时表 多使用联合查询来解决,这样能够保证数据库整齐性,使用连接(JOIN)来代替子查询(Sub-Queries)
- 使用事务或锁定表,来保证数据库数据完整性
- 使用索引,索引能够更有效率的查询数据
- 查询时最好不要使用*号进行查询概括,在有索引的字段尽量不要使用函数操作,尽量不要使用like进行模糊查询,因为影响系统性能
描述JDBC连接数据库的步骤
- 在项目中导入java.sql包
- 加载数据库驱动程序
Class.forName(“com.mysql.cj.jdbc.Driver”); - 定义数据库的链接地址
String url=“jdbc:mysql://localhost/studentserverTimezone=GMT%2B8&useSSL=false”;
String databasename=“root”;
String pass=“123456”; - 得到与数据库的连接对象
con = DriverManager.getConnection(url,databasename,pass); - 声明sql语句
select * from rj1602 where Sno=‘201616040212’; - 得到语句对象
Statement sql - 执行sql语句
sql = con.CreateStatement();
sql.executeQuery(“select * from rj1602 where Sno=‘201616040212’”) - 处理sql语句的返回结果
- 关闭对象
什么是SQL注入,怎样防止
所谓SQL注入,就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。
如何防止sql注入
1、采用PreparedStatement来避免sql注入,会自动对用户填写的数据进行验证(简单有效)
sql注入只对sql语句的准备(编译)过程有破坏作用
而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,
而不再对sql语句进行解析,准备,因此也就避免了sql注入问题.
2、使用正则表达式过滤传入的参数(典型的SQL 注入攻击的正则表达式 )
3、字符串过滤,敏感词过滤
什么是数据库连接池,实现原理以及优势
数据库连接池(Connection pooling)是程序启动时建立足够的数据库连接,并将这些连接组成一个连接池,由程序动态地对池中的连接进行申请,使用,释放。
实现原理
连接池的工作原理主要由三部分组成,分别为
- 连接池的建立
- 连接池中连接的使用管理
- 连接池的关闭
- 连接池的建立。一般在系统初始化时,连接池会根据系统配置建立,并在池中创建了几个连接对象,以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭,这样避免了连接随意建立和关闭造成的系统开销。Java中提供了很多容器类可以方便的构建连接池,例如Vector、Stack等。
- 连接池的管理。连接池管理策略是连接池机制的核心,连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略是:
(1)当客户请求数据库连接时,首先查看连接池中是否有空闲连接,如果存在空闲连接,则将连接分配给客户使用;如果没有空闲连接,则查看当前所开的连接数是否已经达到最大连接数,如果没达到就重新创建一个连接给请求的客户;如果达到就按设定的最大等待时间进行等待,如果超出最大等待时间,则抛出异常给客户。
(2)当客户释放数据库连接时,先判断该连接的引用次数是否超过了规定值,如果超过就从连接池中删除该连接,否则保留为其他客户服务。
(3)该策略保证了数据库连接的有效复用,避免频繁的建立、释放连接所带来的系统资源开销。 - 连接池的关闭。当应用程序退出时,关闭连接池中所有的连接,释放连接池相关的资源,该过程正好与创建相反。
优势
- 较少了网络开销
- 系统的性能会有一个实质的提升
- 没了麻烦的TIME_WAIT状态
什么是存储过程,什么是函数,怎样创建存储过程和函数
存储过程
就是一些编译好了的SQL语句,这些SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后给这些代码块取一个名字,在用到这个功能的时候调用即可。
存储过程详解参考:
http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html
函数
SQL 拥有很多可用于计数和计算的内建函数。
创建函数
能够说出具体语法
create function f_name --f_name 函数名称
(@a varchar(20),@b int) --@a 第一个参数 varchar类型 @b第二个参数 int类型
returns varchar(20) --返回值类型
as
begin declare @c varchar(30) --创建变量,在函数体中使用
set @c='基本操作' --对变量赋值
return (@c) --返回值,这里注意一下,好像要打括号才能把值返回出去
end
调用函数
select dbo.f_name('参数1',2) --这里注意调用的时候,前面加上 dbo.
select dbo.f_name(列名1,2) as name from Table_name --还有就是像我们调用系统函数那样,什么COUNT(),SUM()的那样用
什么是触发器,触发器有哪些作用
触发器(TRIGGER)是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时,会激活促发其执行相应的操作。
触发器作用