Oracle基础知识

小辉煌

已于 2023-01-06 11:06:35 修改

阅读量721

点赞数

文章标签： oracle 数据库

于 2022-10-21 15:44:29 首次发布

本文链接：https://blog.csdn.net/xiaohuihui_321/article/details/127447150

版权

1、 || 代表“+”，即拼接“||”左右两边的字符串。

select 'abc'||'defg' from dual; -- abcdefg

2、truncate关键字

在数据库操作中， TRUNCATE命令(是一个DDL命令)可以把表中的所有数据一次性全部删除，

语法是：truncate table 表名

TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：

1)、TRUNCATE是DDL命令，删除的数据不能恢复;DELETE命令是DML 命令，删除后的数据可以通过日志文件恢复。

2)、如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。

由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE 命令慎用。

3、escape关键字

escape关键字经常用于使某些特殊字符，如通配符：'%','_'转义为它们原来的字符的意义，被定义的转义字符通常使用'\',但是也可以使用其他的符号。

用法：

select * from table WHERE name LIKE '%/%ab' ESCAPE '/' ；--查找的是末尾为%ab的数据.

4、nulls first 和 nulls last

如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
使用语法如下：

select * from zl_cbqc order by cb_ld nulls first; --将cb_id值为null的记录始终放在最前

5、创建索引

create index idx_emp_empno on emp(empno); -- 给表emp添加一个empno的索引，索引名叫idx_emp_empno

6、explain的使用

explain plan for select * from table where a = '1';

select * from table(dbms_xplan.display);

7、Sign(number)函数

判断数字number(也可以是公式)是否是0、正数、负数，分别返回0、1、-1；

select sign(3-2) from dual; -- 1

8、复制表的定义及数据,修改表名

create table test1 as select * from test2;--复制表的定义及数据

--也可以先复制表的定义，再新增数据:

create table test1 as select * from test2 where 1=2;--只备份表，不备份数据

9、修改表名称


ALTER TABLE 旧表名 RENAME TO 新表名;--oracle表名及字段名都默认大写为系统命令

10、给表添加约束条件

alter table emp add constraints ch_sal check(sal > 0); -- 工资必须大于0

11、清空数据

清空数据：truncate table 千万条数据的表

清空数据：delete from table 数据量较少的表

删除表以及表中的数据：drop table 表名

delete 与 truncate 应用区别(Oracle)：

--刪除所有部門信息:使用delete
--1、是一行一行地释放数据，在事务日志中要记录每一条记录的删除。
--2、仅仅能删除数据，不能清空标识。
--3、会激发触发器。
delete from gem_file;
--刪除所有部門信息:TRUNCATE
--1、直接释放数据页，dao并且在事务日志中也只记录数据页的释放。
--2、不仅是删除表里面的数据，而且还会清空表里面主键的标识。
--3、不会激发触发器。
truncate table gem_file;

12、添加索引

通过下面的SQL语句创建针对表table的唯一索引

-- unique 唯一索引 去掉就不是唯一的
-- idx_Id 是索引名字
-- TblForIndex 是表名，Id 是栏位名称
create unique index idx_Id on table(Id);

--删除索引
drop index idx_Id;

--单一索引：
Create Index <Index-Name> On <Table-Name>(Column_name)
Create Index start_date_index on SYS_YUEBAO_SHIYONG(start_date);-- 给表SYS_YUEBAO_SHIYONG的字段start_date添加索引叫start_date_index

--复合索引：
Create Index i_deptno_job on emp(deptno,job); --在emp表的deptno、job列建立索引

13、简单case语句

case到end之间相当于一个具体的值，可以做运算，取别名，嵌套case 等等。
只要把case到end当作一个运算结果的表达式就可以了。

select 
	cust_last_name,
	case credit_limit when 100 then 'low' when 5000 then 'high' else 'medium' end 
from customers;--credit_limit 是100则输出low，是5000则输出high，其他则输出medium

14、搜索case语句使用

select 
	case when id between 1 and 10 then 'low'
	when id between 20 and 30 then 'mid'
	when id between 40 and 50 then 'high'
	else 'unknow'
	end
from product;

简单case和搜索case之间的区别：

1).简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。

2). 搜索case可以作为比较条件，那么可以使用like、!=、between …and、<、=、is null、is not null等，比简单case的使用更加广泛，完全可以替代简单case。

15、字段相关的一些常用sql

ALTER table tableName MODIFY (name varchar2(20));--修改name类型和长度

alter table user_visit_log drop (create_date); --删除字段create_date

alter table ukey rename column name to stuname;--修改字段名称 把name改成stuname

16、自定义函数

create [or replace] function 函数名[{参数[in] 数据类型...}]

return 数据类型

{AS|IS}

[说明部分]

begin

可执行部分

return (表达式)

[exception

错误处理部分]

end[函数名];

其中，参数是可选的，但只能是IN类型(IN关键字可以省略)。

在定义部分的return 数据类型，用来表示函数的数据类型，也就是返回值的数据类型，此部分不可省略。

测试：求两个数的和
create or replace function two_sum(x number,y number)
return number
is
begin
	return (x+y);
	exception when others then
	dbms_output.put_line('数字相加时出现异常');
end two_sum; -- end后面的函数名称可以不写

测试：创建一个通过用户账号返回用户名称的函数
create or replace function get_user_name(userId number default 100)
return varchar2
as
username varchar2(100); -- 定义了一个内部属性
begin
select user_name into username from tellers where user_id = userId;-- 查到结果并把结果赋值给username 属性
return username; -- 返回该属性
end get_user_name;


--创建性别函数
create or replace function createsex -- 此函数没有参数
return varchar2 -- 返回字符串类型
is
status number; -- 定义数字类型的属性 status
sex varchar2(2); -- 定义字符串类型的属性 sex
begin
	select round(dbms_random.value(0,1)) into status from dual; -- 把随机获取的值赋值给status
	if status = 1 then
		sex := '男';
	else 
		sex := '女';
	end if;
	return sex;
end;

17、创建存储过程

存储过程和函数以命名的数据库对象形式存储于数据库当中，存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或者修改代码。存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的权限，只有被授权的用户或创建者本身才能执行存储过程或调用函数。

关键字：PROCEDURE

常规存储过程：

create or replace procedure 存储过程名称

declare

as/is

定义部分

begin

执行内容；

exception 异常处理部分

end;

无名称的存储过程（无名块）：

declare

v_name varchar2(100);

begin

v_name := get_emp_name(7788);-- get_emp_name是自定义的函数（根据员工编号返回员工名称）

dbms_output.put_line('员工名称':||v_name);

end;

//1、测试：创建一个显示雇员总人数的存储过程无名块
declare
total number:=0; -- 定义一个数字类型属性total并默认值为0
begin
	select count(*) into total from emp; -- 查询到总人数并赋值给total
	dbms.output.put_line('总人数'||total);
end;
//2、改成有名称的存储过程
create or replace procedure emp_total
as
total number:=0;
begin
	select count(*) into total from emp;
	dbms.output.put_line('总人数'||total);
end;
//3、使用存储过程
① 在无名块调用
declare
begin
	emp_total
end;
② 使用execute存储过程名称
execute emp_total
③ 在其他的存储过程中使用
    //显示员工表中的员工名称、员工职位和员工工资和总人数
create or replace procedure printEmp
is
cursor emp_cursor is select ename,job,sal from emp; -- 查询结果是一个集合，用emp_cursor来接，cursor关键字，游标(Cursor)是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。
begin
dbms_output.put_line('名称'||'  '|| '职位' '	'|| '薪水');
for emp_record in emp_cursor
loop --loop实现简单的得循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环。
		dbms_output.put_line(emp_record.ename||'  '|| emp_record.job '	'|| emp_record.sal);		
end loop;
		emp_total;--调用存储过程
end;
//4、 参数传递
参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。
可以传递的类型有
IN(定义一个输入参数变量，用于传递参数给存储过程)、
OUT(定义一个输出参数变量，用于从存储过程获取数据)、
INOUT(定义一个输入、输出参数变量，兼有以上两者的功能)
例子：(IN)
编写一个给雇员增加工资的存储过程change_salary,通过IN类型的参数传递要增加工资的雇员编号和要增加的工资额。
create or replace procedure change_salary(p_empno in emp.empno%type defalut 8888,p_raise number default 100) 
-- p_empno in emp.empno%type:你的p_empno 就是你定义的变量，和面的那个emp是你数据库里面存在的表，他的表里面有意个empno字段，然后%type就是empno的数据类型，
-- 总体说这句话就是，让p_empno 与你数据库里面的表字段的属性匹配，这个你就可以很方便的对emp进行-- 操作了，不会因为类型不匹配而报莫名的错误。
as
v_name varchar2(20);
begin
	select ename into v_name from emp where empno = p_empno;
	update emp set sal = sal+p_raise where empno = p_empno;
	dbms_output.put_line('员工号是：'||p_empno'，姓名是：  '||v_name ',涨工资：' || p_raise);
	commit;
	exception
	 when others then
	 dbms_output.put_line('数据库提取数据失败'); 
	 rollback;
end;

execute change_salary;
execute change_salary(777,2000);
也可以写成execute change_salary(p_empno=>777,p_raise=>2000);或者
execute change_salary(p_raise=>2000,p_empno=>777);
例子：（OUT）
create or replace procedure change_salary(p_empno in emp.empno%type default 8888,p_raise number default 100,v_deptno out number)
as
v_name varchar2(20);
begin
	select ename,deptno into v_name,v_deptno from emp where empno = p_empno;
	update emp set sal = sal+p_raise where empno = p_empno;
	dbms_output.put_line('员工号是：'||p_empno'，姓名是：  '||v_name ',涨工资：' || p_raise);
	commit;
	exception
	 when others then
	 dbms_output.put_line('数据库提取数据失败'); 
	 rollback;
end;
使用out变量进行获取值
declare
v_deptno number;
begin
change_salary(p_empno=>777,p_raise=>1000,v_deptno=>v_deptno);
dbms_output.put_line('部门编号是：'||v_deptno);
end;
例子：（INOUT） 电话号连接+86
create or replace procedure change_phone(phone in out varchar2)
is

begin
	phone:='+86'||phone;
end;

create or replace procedure test_inout
is
phone varchar2(20):= '18002123033';
begin
change_phone(phone);
dbms_output.put_line('电话号被修改为：'||phone);
end;

execute test_inout;

18.当前用户是root，修改root密码的sql

--修改root密码为master2022Mysql
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'master2022Mysql';
 
flush privileges;

19.oracle什么时候需要commit

DML语言，比如update，delete，insert等修改表中数据的需要commit;
DDL语言，比如create，drop等改变表结构的，就不需要写commit（因为内部隐藏了commit）;

DDL 数据定义语言：
create table 创建表   
alter table 修改表   
drop table 删除表   
truncate table 删除表中所有行   
create index 创建索引   
drop index 删除索引
当执行DDL语句时，在每一条语句前后，oracle都将提交当前的事务。如果用户使用insert命令将记录插入到数据库后，执行了一条DDL语句(如create
table)，此时来自insert命令的数据将被提交到数据库。当DDL语句执行完成时，DDL语句会被自动提交，不能回滚。

DML 数据操作语言：
insert 将记录插入到数据库  
update 修改数据库的记录  
delete 删除数据库的记录
当执行DML命令如果没有提交，将不会被其他会话看到。除非在DML命令之后执行了DDL命令或DCL命令，或用户退出会话，或终止实例，此时系统会自动
此时需要提交事务，发出commit命令，使未提交的DML命令提交。

20、锁的基本概念

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

Oracle数据库的锁类型

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。

DML锁的目的在于保证并发情况下的数据完整性，。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

当Oracle 执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

21.两张表字段一致，把一张表的数据插入到另一张表去

insert into table1 select * from table2; --把table2的数据备份到table1中
-- 如果字段位置不一致的话，需要指定字段如：
insert into table1 (id,crt_date,crt_time,user_id,user_name,path,ip_addr) select (id,crt_date,crt_time,user_id,user_name,path,ip_addr) from table2;