数据库总结_调用存储过程是dml吗-CSDN博客

本文链接：https://blog.csdn.net/phone13144830339/article/details/79418176

1.简述Oracle中的DML、DDL、DCL、TCL

•DML(数据操作语言)：主要指的是数据库的查询和更新操作。

如select、insert、update、delete、call等。

•DDL(数据定义语言)：主要指的是数据对象(表、用户)的创建。

如create、alter、drop、truncate、rename等。

•DCL(数据控制语言)：主要指的是进行权限的管理和操作。

如grant、revoke等。

•TCL(事务控制语言)：commit、set transaction、rollback等

2.事务隔离级别

事务隔离级别：一个事务对数据库的修改与并行的另一个事务的隔离程度。

两个并发事务同时访问数据库表相同的行时，可能存在以下三个问题：

(1) .幻想读：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。

(2) .不可重复读：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。

(3) .脏读：事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。

为了处理这些问题，SQL标准定义了以下几种事务隔离级别：

(1)READ UNCOMMITTED 幻想读、不可重复读和脏读都允许

(2)READ COMMITTED 允许幻想读、不可重复读，不允许脏读

(3)REPEATABLE READ 允许幻想读、不可重复读

(4)SERIALIZABLE 允许幻想读、不可重复读和脏读都不允许

Oracle数据库支持READ COMMITTED和SERIALIZABLE这两种事务隔离级别，所以Oracle不会出现脏读。MySQL支持READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

SQL 标准所定义的默认事务隔离级别是SERIALIZABLE，但是Oracle默认使用的是READ COMMITTED，MySQL默认使用的是REPEATABLE READ。

Oracle设置隔离级别可以使用SET TRANSACTION ISOLATION LEVEL [REPEATABLE READ|SERIALIZABLE]。

MySQL设置隔离级别可以使用SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]。

3.Oracle与MySQL的区别

(1) .Oracle是大型数据库(不开源，市场占有率达40%)，是OLTP（On-Line Transaction Processing联机事务处理系统）最好的工具；MySQL是中小型数据库(开源，市场占有率达20%)

(2) .Oracle支持大并发，Oracle安全性也会高一些

(3) .主键自增

MySQL有自动增长类型，主键可以指定自动增长

CREATE TABLE STUDENT(

`SID` INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',#自增长主键

`SNAME` VARCHAR(20) NOT NULL COMMENT '姓名',

`SAGE` INT COMMENT '年龄',

`SGENDER` ENUM('男','女') DEFAULT '男' COMMENT '性别'

)CHARSET utf8;

Oracle没有自动增长类型，主键一般使用序列，在插入记录的时候把序列的下一个值赋给该字段即可

-- 创建表

CREATE TABLE MYSELF (

ID NUMBER(2) CONSTRAINT PK_ID PRIMARY KEY,

NAME VARCHAR2(14)

) ;

-- 创建序列

create sequence myself_id_seq

increment by 1

start with 1;

--创建触发器

create or replace trigger trg_myself

before insert on MYSELF

for each row

begin

select myself_id_seq.nextval into :new.id from dual;

end trg_myself;

--添加记录，id会自动增长

insert into myself(name) values('LuCheng');

insert into myself(name) values('DengJuan');

(4) .翻页

MySQL：select * from student limit 0,10;#0：起始位置，10：每页显示记录数量

Oracle：select * from (select t.*,rownum row_num from student t) b

where b.row_num between 1 and 10

或

select * from

( select rownum r,t.* from tabName t where rownum <= 10 )

where r > 0

(5) .空字段处理

MySQL非空字段里面也有空内容

Oracle里面非空字段不允许有空

Oracle导入数据的时候，就要对空字段进行判断，如果是null或者空字符串，需要改成一个空格的字符串

(6) .事务隔离级别与传播特性

Oracle：READ COMMITTED 读已提交，允许幻想读、不可重复读，不允许脏读

MySQL：REPEATABLE READ 可重复读，允许幻想读，不允许不可重复读和脏读

4.SQL语句优化技巧

(1).修改最大连接数：alter system set processes=2000 scope = spfile;

(2).禁止回收站功能：alter system set recyclebin=off scope=spfile;

(3).建立表分区，将数据分别存储在不同的分区上

(4).建立合适的索引

(5).将索引数据和表数据分开在不同的表空间上

(6).可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用

(7).合理使用事务，合理设置事务隔离性

(8).尽量在应用层使用缓存，例如redis

(9).尽量避免全表扫描

(10).避免select *，where条件中的in、or、like、!=、<>、is not null

in用exists代替，or用union all代替(两条select语句中间使用union all)，<>改为<and>，is not null改为>=char(0)

·避免在where子句中使用is null或is not null对字段进行判断

如：select id from table where name is null

在这个查询中，就算我们为name字段设置了索引，查询分析器也不会使用，因此查询效率低下。为了避免这样的查询，在数据库设计的时候，尽量将可能出现null值的字段设置默认值，这里如果我们将 name 字段的默认值设置为0，那么我们就可以这样查询：

select id from table where name = 0

·避免在where子句中使用or来连接条件

如：select id from table where name = 'Oracle' or name = 'MySQL'

这种情况，我们可以这样写：

select id from tabel where name = 'Oracle'

union all

select id from tabel where name = 'MySQL'

·避免在where子句中使用!=或<>操作符

如：select name from table where id <> 0

数据库在查询时，对!=或<>操作符不会使用索引，而对于<、<=、=、>、>=、BETWEEN AND,数据库才会使用索引。因此对于上面的查询，正确的写法试应该是：

select name from table where id <0

union all

select name from table where id >0

·少用in或not in

虽然对于in的条件会使用索引，不会全表扫描，但是在某些特定的情况，使用其他方法也许效果更好。如：select name from table where id in(1,2,3,4,5)

像这种连续的数值，我们可以使用BETWEEN AND如：

select name from table where id between 1 and 5

·注意like中通配符的使用

下面的语句会导致全表扫描，尽量少用。如：select id from table where name like '%Oracle'

而下面的语句执行效率要快的多，因为它使用了索引：

select id from table where name like 'Oracle%'

·避免在where子句中对字段进行表达式操作

如：select name from id/2 = 100

正确的写法应该是：

select name from table where id = 100*2

·避免在where字句中对字段进行函数操作。

如：select id from table where substring(name,1,8) = 'Oracle'

或select id from table where datediff(day,datafield,'2018-01-01') >= 0

这两条语句中都对字段进行了函数处理，这样就使得查询分析器放弃了索引的使用。正确的写法是这样的：

select id from table where name like 'Oracle%'

或

select id from table where datafield <= '2018-01-01'

也就是说，不要在where字句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

·在子查询中，用exists代替in是一个好的选择

如：select name from a where id in(select id from b)

如果我们将这条语句换成下面的写法：

select name from a where id exists(select id from b where id = a.id)

这样，查询出来的结果一样，但是下面这条语句查询的速度要快的多。

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

5. 如何设计数据库

遵循数据设计三范式

•1NF 每个属性不可再拆分

•2NF 一个表中只能存一种数据，不可以把多种数据存到同一张表

•3NF 每一列数据都和主键直接相关，而不能间接相关

使用powerdesigner工具设计ER图(概念模型图),建立实体与实体之间的关系

•一对一关系：一张表中的一条记录对应另一张表的一条记录，反之亦然，就像人和身份证之间的关系

•一对多关系：一张表中的一条记录对应另一张表的多条记录，反之一对一关系，就像学生和班级之间的关系

•多对多关系：一张表中的一条记录对应另一张表的多条记录，反之亦然，就像学生和选修科目之间的关系

6.Oracle常用的函数

•字符函数：

函数名称	描述
Upper(string)	将字符串转为大写
Lower(string)	将字符串转为小写
Initcap(string)	将字符串首字母转为大写，其它小写
Length(string)	得到字符串的长度
Replace(oldString,newString[,updString])	将字符串的字符的子字符串替换成新的字符
Substr(oldString,strIndex[,endIndex])	截取字符串
Concat(string1,string2)	拼接字符串
Ascii(char)	返回字符的ASCII值
Chr(number)	将ASCII码转为字符
Instr(String,findString[,strIndex[,number]])	获取字符串出现的索引
Trim(string)	截取左右两端的空格

•数字函数：

函数名称	描述
Abs(number)	取数字的绝对值
Ceil(number)	向上取整
Floor(number)	向下取整
Power(number1,number2)	计算（number1）的（number2）次幂
Mod(number1,number2)	（number1)取模于（number2）
Round(number[,number2])	四舍五入
Trunc(number[,number2])	向上截取
Sqrt(number)	求平方根

•日期函数：

函数名称	描述
Sysdate	当前日期
Add_months(date,n)	数月后的日期
Last_day(date)	当前月的最后一天
Months_between(date1,date2)	间隔月数
Trunc(date,’day’\|’month’\|’yeaer’)	星期、月、年的第一天日期
Next_day(date[,n])	过一周日期
localtimestamp	会话日期和时间

•转换函数：

函数名称	描述
CharToRowid(string)	字符串转为rowid格式
RowidToChar(rowid)	Rowid转为string
To_char(number\|date[,format])	转为字符串
To_date(string)	转日期
To_number(string)	转数字

•聚组函数：

函数名称	描述
Avg(colme)	平均值
Sum(colname)	求和
Variance(colname)	方差
Count(*\|[distinct\|all] colname)	行数
Max(colname)	最大值
Min(colname)	最小值
Wm_concat(colname)	多值合并

•其他函数：

函数名称	描述
nvl(expr1,expr2)	若expr1为空，用expr2替代
nvl2(expr1,expr2, expr3)	若expre1为空，返回expr2,否则返回expr3
User	返回数据库用户名
Uid	返回当前会话所对应的用户id
Decode(colname,v1,n1,v2,n2...[,value])	条件取值
Rownum	当前行号
Sys_guid()	生成32位的随机数，包括0-9和A-F
Dbms_random(strNumber,endNumber)	生成随机数

7.什么是笛卡尔积，内连接，左外连接，右外连接，全连接，自然连接并且举例

笛卡尔积：多表查询产生迪卡尔积，显示时消除迪卡尔积的影响

(多表查询在大数据、海量数据表中不可能去用的)

内连接：所有满足条件的数据都会被显示出来

select * from emp e join dept d on (e.deptno = d.deptno);

select * from emp e join dept d using(deptno);

左外连接：左边一条记录和右边无关联，保留左边数据

select * from emp e left join dept d on e.deptno = d.deptno;

select * from emp e left outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno(+) = d.deptno;

右外连接：右边一条记录和左边无关联，保留右边数据

select * from emp e right join dept d on e.deptno = d.deptno;

select * from emp e right outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno = d.deptno(+);

全连接：左外连和右外连的结果合并去重复

select * from emp e left join dept d on e.deptno = d.deptno

union

select * from emp e right join dept d on e.deptno = d.deptno;

select * from emp full outer join dept using(deptno);

自然连接：自动筛选符合相同列属性值相关联的数据

select * from emp natural join dept;

8. 存储过程和函数的区别

函数例子：实现大写字母转小写字母（lower(string)函数）的功能

create or replace function myfunction1(str1 varchar2)

return varchar2

n number;

str2 varchar2(200):='';

begin

for i in 1..length(str1) loop

n:=ascii(substr(str1,i,i));

if ascii(substr(str1,i,i)) between ascii('A') and ascii('Z') then

n:=n+32;

end if;

str2:=concat(str2,chr(n));

end loop;

return str2;

end;

存储过程例子：删除该表中的重复记录

create or replace procedure deleteMul(tableName varchar2)

mycursor sys_refcursor;

v_sql varchar2(2000):='select t.column_name from user_col_comments t where t.table_name = ';

v_colnames user_col_comments.column_name%type;

leim varchar2(2000);

begin

v_sql:=v_sql||''''||tableName||'''';

open mycursor for v_sql;

loop

fetch mycursor into v_colnames;

exit when mycursor%notfound;

leim:=leim||v_colnames||',';

end loop;

close mycursor;

leim:=substr(leim,1,length(leim)-1);

execute immediate 'delete from '||tableName||' e1 where e1.rowid not in (select max(rowid) from '||tableName||' e2 group by '||leim||')';

commit;

end;

(1) .可以理解函数是存储过程的一种，都是预编译的。一次编译，多次运行。

(2) .存储过程程序头部声明使用procedure,函数程序头部声明使用function

(3) .存储过程可以使用in/out/in out三种模式参数，Oracle在函数可以使用in和out,MySQL函数不能使用out。

(4) .函数使用return返回值没有返回参数模式，存储过程通过out参数返回值

(5) .DML语句中不可调用存储过程，DML语句中可以调用函数

(6) .函数可以没有参数，但是一定需要一个返回值；存储过程可以没有参数，不需要返回值

9. Oralce怎样存储文件，能够存储哪些文件

Oracle 能存储 clob、nclob、 blob、 bfile

•Clob 可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型

•Nclob 可变字符类型的数据，不过其存储的是Unicode字符集的字符数据

•Blob 可变长度的二进制数据

•Bfile 数据库外面存储的可变二进制数据

10. Oracle中有哪几种文件

一般在存放在安装目录下的\oradata\orcl

•数据文件（一般后缀为.dbf或者.ora）

•日志文件(后缀名.log)

•控制文件（后缀名为.ctl）

11. Oracle导入和导出方式

(1).使用Oracle工具exp/imp

(2).使用plsql相关工具

导入/导出二进制的数据文件

导入/导出sql语句的文本文件

12. 比较truncate和delete命令

•truncate清除表记录和表结构（是DDL数据定义语言），delete只删除数据不删除表的结构(是DML数据操纵语言)；truncate操作立即生效(操作不记录到rollback日志，所以操作速度较快)但同时这个数据不能恢复,delect操作会记录到rollback日志，事务提交之后才生效

•truncate不能对视图进行删除

13. rowid, rownum的定义
(1).rowid和rownum都是虚列
(2).rowid是物理地址，用于定位oracle中具体数据的物理存储位置,而rownum则是sql的输出结果排序。rowid是相对不变的,rownum是经常变化的。

14. oracle 伪列删除表中重复记录：

delete table t where t.rowid!=(select max(t1.rowid) from table1 t1 where t1.name=t.name)

15.Oralce解释冷备份和热备份的不同点以及各自的优点

•冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法

•热备份是在数据库运行的情况下，采用归档方式备份数据的方法

冷备份的优点：
(1)．是非常快速的备份方法（只需拷贝文件）
(2)．容易归档（简单拷贝即可）
(3)．容易恢复到某个时间点上（只需将文件再拷贝回去）
冷备份的不足：
(1)．单独使用时，只能提供到“某一时间点上”的恢复。
(2)．在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
(3)．若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
(4)．不能按表或按用户恢复。

备份的方式：（备份所有的数据文件控制文件重做日期文件和密码文件）

热备份的优点：
(1)．可在表空间或数据文件级备份，备份时间短。
(2)．备份时数据库仍可使用。
(3)．可达到秒级恢复（恢复到某一时间点上）。
(4)．可对几乎所有数据库实体作恢复。
(5)．恢复是快速的，在大多数情况下在数据库仍工作时恢复

热备份的不足：

(1)．不能出错，否则后果严重。
(2)．若热备份不成功，所得结果不可用于时间点的恢复。
(3)．因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

备份的方式：exp/imp expdp/impdp

16.解释什么是死锁，如何解决Oracle中的死锁

简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行
解决办法：
1). 查找出被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
2). 杀进程中的会话
alter system kill session "sid,serial#";

17.是否使用过dblink

Databaselink 可以将不同的数据库之间连接起来通过同义词建立对象可以将对象当成当前数据的对象进行操作比如表User 在A库表Role在B库不同数据库之间是不同直接进行表关联必须建立databaselink 。举例

create database link A connect to B用户名 identified by B密码 using ‘B的连接描述符’;

create SYNONYM Rolefor Role@A;

Select * from user,role

18.什么是视图普通视图和物化视图的区别

针对多个物理表的sql查询的映射虚表用来控制列的权限问题(Oracle数据库存在对象权限、角色权限、系统权限）

)，更清晰表达列的数据。

视图优点：

(1)可以选择性选取需要字段

(2)简单查询得到复杂结果

(3)数据维护独立