1、简单说一说drop、delete与truncate的区别,及适用场合
SQL中的drop、delete、truncate都表示删除,但是三者有一些差别
- delete和truncate只删除表的数据不删除表的结构
- 从删除速度来讲, drop> truncate >delete
- delete语句是dml语句,,事务提交之后才生效;如果有相应的dml trigger,执行的时候将被触发.
- truncate,drop是ddl语句, 操作立即生效
- truncate删除的数据不能恢复,delete删除的数据可以通过日志文件恢复数据
适用场合:
- 不再需要一张表的时候,用drop删除表
- 想删除表中数据行时候,用delete,可以带where子句
- 保留表而删除所有数据的时候用truncate
2、数据库的三大范式是什么?
1)第一范式:原子性,要求表中的每一列不可拆分
2)第二范式:一张表只描述一个实体
3)第三范式:所有列与主键直接相关,而不是间接相关
3、如何使用Oracle的游标
- oracle中的游标分为显示游标和隐式游标
- 显示游标是用declare cursor...is命令定义的游标,它可以对查询语句(select)返回的多条记录进行处理;隐式游标是在执行DML语句时由PL/SQL自动定义的。
- 显式游标的操作:打开游标、操作游标、关闭游标;PL/SQL隐式地打开SQL游标,并在它内部处理SQL语句,然后关闭它
4、怎样优化Oracle数据库?
个人理解,数据库性能最关键的因素在于IO,因为操作内存是快速的,但是读写磁盘是速度很慢的,优化数据库最关键的问题在于减少磁盘的IO
优化的一些原则:
1). Oracle的运行环境(网络,硬件等)
2). 使用合适的优化器
3). 合理配置oracle实例参数
4). 建立合适的索引(减少IO)
5). 将索引数据和表数据分开在不同的表空间上(降低IO冲突)
6). 建立表分区,将数据分别存储在不同的分区上(以空间换取时间,减少IO)
5、sql语句应该考虑哪些安全性?
- 防止sql注入,对特殊字符进行转义,过滤
- 使用预编译的sql语句绑定变量。
- 最小权限原则,特别是不要用root账户,为不同的类型的动作或者组建使用不同的账户。
- 当sql运行出错时,不要把数据库返回的错误信息全部显示给用户,以防止泄漏服务器和数据库相关信息。
6、SQL语句分为哪几类?
1)DML(数据操作语言)insert、delete、update、select
2)DDL(数据定义语言)create、drop、alter
3)DCL(数据控制语言)grant:把权限授予用户、revoke:把权限从用户收回
4)TCL(事务控制语言):commit、rollback
7、oralce查询语句
1)如何只显示重复数据?
select * from table group by id having count(*)>1
2)分页语句
select * from (select t.*,rownum row_num from mytable t) b where b.row_num between 1 and 10
3)删除重复记录
delete from emp where rowid not in(select max(rowid) from emp group by empid,empname);
4)从数据库随机取50条
select * from (select * from t_example order by dbms_random.random) where rownum <= 50
8、事务的特性(ACID)是什么?
1)原子性(Atomicity):事务中的各项操作,要么全做要么全不做,任何一项操作的失败都会导致整个事务的失败
2)一致性(Consistency):事务结束后系统状态是一样的
3)隔离性(Isolation):并发执行的事务彼此无法看到对方的中间状态
4)持久性(Durability):事务完成后,即使发生灾难性的故障,通过日志和同步备份可以在故障发生后重建数据
9、使用存储过程的优缺点
优点:
- 存储过程有很强的灵活性,可以完成复杂的判断和较复杂的运算。
- 可保证数据的安全性和完整性。
- 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数 据的安全。
- 存储过程可极大地改善SQL语句的性能。
- 可以降低网络的通信量,
- 使体现企业规则的运算程序放入数据库服务器中,以便集中控制。 当企业规则发生变化时在服务器中改变存储过程即可,无须修改任何应用程序。
缺点:
- 可移植性差
- 占用服务器端多的资源,对服务器造成很大的压力
- 可读性和可维护性不好
10、请简述项目中优化sql语句执行效率的方法?
1)将where中用的比较频繁的字段建立索引
2)减少表之间的关联
3)避免在索引列上使用计算,not,in和<>等操作
4)select子句中避免使用‘*’,只查询使用的字段
5)尽量用PreparedStatement来查询,不要用Statement
11、请简述你对触发器的理解
触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。
1)可以实现一般的约束无法完成的复杂约束,从而实现更为复杂的完整性要求。
2)使用触发器还可以完成日志跟踪功能。
3)由于触发器主要是用来保证数据库的完整性的,所以要创建一个触发器,首先要明确该触发器应该属于那一种(DML,DDL,INSTEAD OF,SYSTEM)因为他们各有个的用途;其次就是要确定触发器被触发以后所完成的业务操作。
12、什么情况下设置了索引但无法使用
1)以“%”开头的LIKE语句 (模糊查询)
2)OR语句前后没有同时使用索引
3)数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)
13、请简述oracle中的锁
1) 行级锁主要是在执行操作过程中,锁定指定的行。主要的锁行语句有:
insert ,update,delete ,及select ....for update。
2) 表级锁:指在运行操作指令过程中,由用户指定锁定某张表。
lock table XXX in mode share;--共享锁,
3)oracle中的锁分为共享锁,排他锁,共享排它,行共享,行排他等
14、 怎样创建一个一个索引,索引使用的原则,有什么优点和缺点 ?
创建索引的基本语法:
CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;
索引使用原则:
1)索引字段建议建立NOT NULL约束
2)经常与其他表进行连接的表,在连接字段上应该建立索引;
3)经常出现在Where子句中的字段,应该建立索引;
4)频繁DML的表,不要建立太多的索引;
5)不要将那些频繁修改的列作为索引列;
6)表中数据量少时,不适合创建索引
索引优点:
1)创建唯一性索引,保证数据库表中每一行数据的唯一性
2)大大加快数据的检索速度,这也是创建索引的最主要的原因
3)加速表和表之间的连接
4)在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
索引缺点:
1)索引创建在表上,不能创建在视图上
2)创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
3)索引需要占物理空间
4.)当对表中的数据进行增删改的时候,索引也要动态的维护,降低了数据的维护速度
15. 怎样创建一个视图,视图的好处, 视图可以控制权限吗?
创建语法:create view 视图名 as select 列名 … from 表 where 条件
好处:
1)可以简单的将视图理解为sql查询语句,不占系统空间
2)一些安全性很高的系统,不会公布系统的表结构,可能会使用视图将一些敏感信息过滤或者重命名后公布结构
3)简化SQL查询
可以控制权限的,在使用的时候需要将视图的使用权限grant给用户