sql 考点总结

sql 考点总结

1drop、truncate和delete的区别
delete 是dml 语言  删去要commit才能生效 没用commit之前 可以rollback 
delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录
delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变
DELETE操作不会减少表或索引所占用的空间

runcate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
truncate会删除表中所有记录，并且将重新设置高水线和所有的索引
3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。

drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

   如果想删除表，当然用drop； 

   如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

   如果和事务有关，或者想触发trigger，还是用delete；

   如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

 1）Truncate 是DDL 语句，DELETE 是DML语句。
2）Truncate 的速度远快于DELETE；
原因是：当执行DELETE操作时所有表数据先被COPY到回滚表空间，数据量不同花费时间长短不一。而TRUNCATE 是直接删除数据不进回滚表空间。
3）delete 数据可以运行Rollback 进行数据回滚。而Truncate 则是永久删除不能回滚。
4) Truncate 操作不会触发表上的delete触发器，而delete 会正常触发。
5) Truncate 语句不能带where 条件意味着只能全部数据删除，而DELETE可带where 条件进行删除数据。
6）Truncate 操作会重置表的高水位线（High Water Mark）,而delete 不会。
7）DELETE可以操作视图，TRUNCATE不能操作视图

集合操作符
Union操作符返回两个查询的结果集的并集，不包含重复值
Union ALL操作符返回两个查询的结果集的并集，包括所有重复⾏。
Minus是从第⼀个查询结果减去第二个查询结果,如果有相交部分就减去相交部分, 不包含重复值。
INTERSECT操作符只返回两个查询的公共行。是交集，不包含重复值。

第一范式：原子性，要求每一列的值不能再拆分了。
2）第二范式：一张表只描述一个实体（若列中有冗余数据，则不满足）防止了插入异常，更新异常 删除异常。
3）第三范式：所有列与主键值直接相关。

在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。
原子性（Atomicity）
事务必须是原子工作单元，对其进行的数据修改，要么全都执行，要么全都不执行。
以网上银行转账为例，要在A账户上增加1000元，同时要在B账户上减少1000元。要么同时执行，要么都不执行更改，以确保整个事务是一个原子工作单元。
一致性（Consistency）
事务在完成时，必须使所有的数据都保持一致状态，即所有的数据都要发生更改，以保证数据的完整性。在银行转账时，A账户和B账户的数据都要发生更改，以保证数据的完整性。
隔离性（Isolation）
两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时、运行中间某一时刻的数据。比如银行转账操作时，如果有其他的会话也在进行转账，那么当前事务内不能看到其他事务在运行时或运行中间某一时刻的数据。
持久性（Durability）
一旦事务被提交之后，数据库的变化就会被永远保留下来，即使运行数据库软件的机器后来崩溃也是如此。银行转账一旦操作完成，数据就会被永久地保留下来了，即使数据库系统关闭也不会丢失数据。

2.2、事务隔离的级别
为此我们需要通过提供不同类型的“锁”机制针对数据库事务进行不同程度的并发访问控制，由此产生了不同的事务隔离级别：隔离级别（低->高）。SQL、SQL2标准定义了四种隔离级别：

●读未提交（Read Uncommitted）

含义解释：只限制同一数据写事务禁止其他写事务。解决”更新丢失”。（一事务写时禁止其他事务写）

名称解释：可读取未提交数据

所需的锁：排他写锁

●读提交（Read Committed）

含义解释：只限制同一数据写事务禁止其它读写事务。解决”脏读”，以及”更新丢失”。（一事务写时禁止其他事务读写）

名称解释：必须提交以后的数据才能被读取

所需的锁：排他写锁、瞬间共享读锁

●可重复读（Repeatable Read）

含义解释：限制同一数据写事务禁止其他读写事务，读事务禁止其它写事务(允许读)。解决”不可重复读”，以及”更新丢失”和”脏读”。（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）

注意没有解决幻读，解决幻读的方法是增加范围锁（range lock）或者表锁。

名称解释：能够重复读取

所需的锁：排他写锁、共享读锁

●串行化（Serializable）

含义解释：限制所有读写事务都必须串行化实行。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（一事务写时禁止其他事务读写、一事务读时禁止其他事务读写）

所须的锁：范围锁或表锁

下表是各隔离级别对各种异常的控制能力。
 

　　以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，数据完整性越好
但执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，
所以平时选用何种隔离级别应该根据实际情况。

Mysql数据库与Oracle 数据库有什么区别？
1，应用方面，Mysql是中小型应用的数据库。一般用于个人和中小型企业。Oracle 属于大型数据库，一般用于具有相当规模的企业应用。
2，自动增长的数据类型方面：MySQL有自动增长的数据类型。Oracle没有自动增长的数据类型。需要用触发器建立一个自增序列。
3，group by 用法：Mysql中group by 在SELECT语句中可以随意使用，但在ORACLE中如果查询语句中有组函数，那么其他列必须是组函数处理过的或者是group by子句中的列，否则会报错。
4,引导方面：MySQL中可以用单引号、双引号包起字符串，Oracle中只可以用单引号包起字符串。
 mysql 分页用limit  oracle中则不太一样，它使用rownum定位记录，一般需要使用嵌套子查询
mysql开源免费的，而oracle则是收费的，并且价格非常高
mysql的管理工具较少，在Linux下的管理工具的安装有时需要安装额外的包（phpmyadmin，etc），有一定复杂性。
oracle有多重成熟命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。oracle支持大并发，大访问量，是OLTP最好的工具
对事务提交
mysql默认是自动提交，可以修改为手动提交
oracle默认不自动提交，需要手动提交，需要在写commit指令或点击commit按钮。 
mysql：默认用户是root，用户下可以创建好多数据库，每个数据库下还有好多表，一般情况下都是使用默认用户，不会创建多个用户；
oracle：创建一个数据库，数据库下有好多用户：sys、system、scott等，不同用户下有好多表，一般情况下只创建一个数据库用。
mysql：int、float、double等数值型，varchar、char字符型，date、datetime、time、year、timestamp等日期型。
oracle：number（数值型），varchar2、varchar、char（字符型），date（日期型）等…

Oracle跟SQL Server 2005的区别？
宏观上：
1). 最大的区别在于平台，oracle可以运行在不同的平台上，sql server只能运行在windows平台上，由于windows平台的稳定性和安全性影响了sql server的稳定性和安全性。
2). oracle使用的脚本语言为PL-SQL，而sql server使用的脚本为T-SQL
3).在SQL Server中，每一个DML语句都具有一个隐式的事务，语句执行结束时事务被自动提交到服务器端，而在Oracle中，当第一条SQL语句开始执行时，一个新的事务自动开始，除非显式地使用COMMIT进行提交，或者是使用ROLLBACK进行回滚，或者是退出某个Oracle工具时，这些事务才结束，否则SQL语句的操作仅在会话级别进行，并没有保存到数据库中。
（4）sql server 是中小型企业用的，oracle是中大型企业用的；oracle跨平台能在linux上运行，sqlserver只能在windows运行，而linux平台比windows平台稳定，所以安全性高；sqlserver操作简便，oracle比较复杂；oracle能把数据存在不同的硬盘上sqlserver不能；oracle能够回滚表空间查询sqlserver不能；oracle支持label security，sqlserver不能；oracle扩展性比较强。

8.Oracle中function和procedure的区别？
可以理解函数是存储过程的一种
存储过程：
（1）一般用于在数据库中完成特定的业务或任务
（2）可以定义返回类型，也可以不定义返回类型
（3）可返回多个参数
函数：
（1）一般用于特定的数据查询或数据转转换处理
（2）申请时必须要定义返回类型，且程序体中必须定义 return 语句
（3）最多返回一个值
（4）不能独立执行，必须作为表达式的一部分调用
注意：sql数据操纵语句中只能调用函数而不能调用存储过程
.Oracle的导入导出有几种方式，有何区别？
1). 使用oracle工具 exp/imp
2). 使用plsql相关工具
方法：1.导入/导出的是二进制的数据
2.plsql导入/导出的是sql语句的文本文件

二、Delete 与Truncate区别

1）Truncate 是DDL 语句，Delete 是DML语句。
2） Truncate 的速度远快于 Delete ；
原因是： 当执行DELETE操作时所有表数据先被COPY到回滚表空间，数据量不同花费时间长短不一。而TRUNCATE 是直接删除数据不进回滚表空间。
3） delete 数据可以运行Rollback 进行数据回滚。而Truncate 则是永久删除不能回滚。
4) Truncate 操作不会触发表上的delete触发器，而delete 会正常触发。
5） Truncate 语句不能带where 条件意味着只能全部数据删除，而DELETE可带where 条件进行删除数据。
6） Truncate 操作会重置表的高水位线（High Water Mark）,而delete 不会。

数据库的三大范式是什么
1） 第一范式：原子件，要求每一列的值不能再拆分了。
2） 第二范式： 一张表只描述一个实体（若列中有冗余数据，则不满足）
3）第三范式： 所有列与主键值直接相关

事务的特性（ACID）是指什么

1）原子性（Atomic）： 事务中的各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败。
2) 一致性（Consistent）： 事务结束后系统状态是一样的。
3）隔离性（Isolated）: 并发执行的事务彼此无法看到对方的中间状态。
4） 持久性（Durable）:事务完成后，即使发生灾难性故障，通过日志和同步备份可以在故障发生后重建数据。

Oracle中function和procedure的区别？

1. 函数可以理解是存储过程的一种
2. 函数可以没有参数,但是一定需要一个返回值；存储过程可以没有参数,不需要返回值
3. 两者都可以通过out参数返回值, 如果需要返回多个参数则建议使用存储过程
4. 在sql数据操纵语句中只能调用函数而不能调用存储过程

 Oracle的导入导出有几种方式，有何区别？
1. 使用oracle工具 exp/imp
2. 使用plsql相关工具
方法1. 导入/导出的是二进制的数据， 2.plsql导入/导出的是sql语句的文本文件

Oracle中有哪几种文件？
数据文件（一般后缀为.dbf或者.ora），日志文件(后缀名.log)，控制文件（后缀名为.ctl）

数据库怎样实现每隔30分钟备份一次？
通过操作系统的定时任务调用脚本导出数据库

解释data block , extent 和segment的区别？
data block 数据块，是oracle最小的逻辑单位，通常oracle从磁盘读写的就是块
extent 区，是由若干个相邻的block组成
segment段，是有一组区组成
tablespace表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件

十五、 解释什么是死锁，如何解决Oracle中的死锁？
简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行
解决办法：
1. 查找出被锁的表
2. 杀进程中的会话

0. 怎样创建一个存储过程, 游标在存储过程怎么使用, 有什么好处? 
附：存储过程的一般格式，游标使用参考问题 
1 .使用游标可以执行多个不相关的操作.如果希望当产生了结果集后,对结果集中的数据进行多种不相关的数据操作 
2. 使用游标可以提供脚本的可读性 
3. 使用游标可以建立命令字符串,使用游标可以传送表名,或者把变量传送到参数中,以便建立可以执行的命令字符串. 
但是个人认为游标操作效率不太高，并且使用时要特别小心，使用完后要及时关闭 
存储过程优缺点： 
优点： 
1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 
2. 可保证数据的安全性和完整性。 
3． 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。 
      通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。 
3. 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 
4. 可以降低网络的通信量, 不需要通过网络来传送很多sql语句到数据库服务器了 
5. 使体现企业规则的运算程序放入数据库服务器中，以便集中控制 
       当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。 
缺点： 
1. 可移植性差 
2. 占用服务器端多的资源，对服务器造成很大的压力 
3. 可读性和可维护性不好 

索引的优缺点： 
优点： 
1. 创建唯一性索引，保证数据库表中每一行数据的唯一性 
2. 大大加快数据的检索速度，这也是创建索引的最主要的原因 
3. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
缺点： 
1. 索引创建在表上，不能创建在视图上 
2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 
3. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大 
4. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度 

索引使用原则：
1.索引字段建议建立NOT NULL约束
2.经常与其他表进行连接的表，在连接字段上应该建立索引；
3.经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；
4.如果有两个或者以上的索引，其中有一个唯一性索引，而其它是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
5.至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
6.小表不要建立索引
7.对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引
8.列中有很多空值，但经常查询该列上非空记录时应该建立索引
9.经常进行连接查询的列应该创建索引
10.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。

索引使用原则：
1.索引字段建议建立NOT NULL约束
2.经常与其他表进行连接的表，在连接字段上应该建立索引；
3.经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；
4.如果有两个或者以上的索引，其中有一个唯一性索引，而其它是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
5.至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
6.小表不要建立索引
7.对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引
8.列中有很多空值，但经常查询该列上非空记录时应该建立索引
9.经常进行连接查询的列应该创建索引
10.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。
索引的优缺点：

左右连接、内连接、全连接的区别
left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

什么是分区？
就是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
什么时候使用分区表?
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加到新的分区中。
表分区有以下优点：
1.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4.均衡I/O：可 以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：已经存在的表没有方法可以直接转化为分区表。
分区表的分类？
分区表：范围分区(range)，列表分区(list)，散列分区(hash),复合分区,交换分区
范围分区
将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。

什么是临时表
临时表就是用来暂时保存临时数据（亦或叫中间数据）的一个数据库对象。
ORACLE临时表是会话或事务级别的，只对当前会话或事务可见。每个会话只能查看和修改自己的数据。
在oracle中，临时表分为会话级别(session)和事务级别(transaction)两种。
会话级的临时表在整个会话期间都存在，直到会话结束；
事务级别的临时表数据在transaction结束后消失，即commit/rollback或结束会话时，会清除临时表数据。
1、事务级临时表on commit delete rows；当COMMIT的时候删除数据(默认情况)。
2、会话级临时表on commit preserve rows；当COMMIT的时候保留数据，当会话结束删除数据。