Oracle 数据库基础SQL语句

–创建表空间

create tablespace tab_01 datafile 'd:\database\data\datafiles_01' size 50M;

–创建临时表空间

create temporary tablespace temp_01 tempfile 'd:\database\data\temp_01.dbf' size 50M;

–创建表格

create table crTale_01(
       id number(11) primary key not null,
       username varchar2(20) unique not null,    --unique :表示不能相同
       pwd varchar2(16) not null,
       sex char(2) check(sex='男' or sex='女')  not null,
       age number(3) not null,
       birthday date,
       address varchar2(200)
);

–删除表格

drop table crTale_01

–添加表注释

comment on table crTale_01 is '测试表_01';

–字段注释

comment on column crTale_01.id is '主键id';
comment on column crTale_01.username is '姓名';

–插入数据

insert into crTale_01 values(1001,'江小白','123456','男',22,sysdate,'重庆市沙坪坝');
insert into crTale_01 values(1002,'江大黑','123456','女',21,sysdate,'重庆市沙坪坝');
insert into crTale_01 values(1003,'翠花','123456','女',21,sysdate,'重庆市沙坪坝');

–查询表格所有内容

select * from crTale_01

–修改数据

update crTale_01 set username='老王' where id=1001

–删除数据

delete from  crTale_01 where id=1003

–提交事务

commit

同义词：给查询的表格对象取一个别名，用于简化对象访问和提高对象访问的安全性.

–同义词有两种类型：
–私有同义词。（私有同义词只能被当前模式用户访问）
–共有同义词。（共有同义词可以被所有数据库用户访问）

–“ or replace” 表示同义词存在的情况下替换该同义词。
–创建私有同义词

create or replace synonym ta_01 for crTale_01;

–创建共有同义词

create public synonym ta_02 for crTale_01;

–通过别名查询

select * from ta_01
select * from ta_02

–删除同义词

drop synonym ta_01

索引:类似于书的目录，我们可以通过目录快速的找到我们查询内容的位置，不需要从第一页每页的开始查找。在数据库中，索引允许程序快速地找到表中的数据，而不必全表扫描。在数据库中，索引是表中数据和相应存储位置的列表。

索引是一种树状结构，从逻辑设计和物理设计实现两个方面进行分类。从逻辑设计方面看，可以分为单列索引或者组合索引、唯一性索引、非唯一性索引和基于函数的索引等；从物理设计实现角度看，索引可以分为分区索引和非分区索引、B树索引、正向索引和反向索引、位图索引等。

–创建索引

create index ta_sex on crTale_01(sex);

–创建唯一性索引

create unique index ta_id on crTale_01(id);

–创建位图索引

create bitmap index ta_sex on crTale_01(sex);

修改索引

–重建索引

alter index  ta_sex rebuild

–合并索引

alter index ta_sex coalesce;

–删除索引

drop index ta_sex；

视图：是查询一个或多个表格的 select 语句的描述。视图创建后相当于虚表或特殊的表来对待

–创建视图1
–视图名称与表明不能一样，其中 “or replace” 表示如果有同名的视图存在，则覆盖已有视图，既可以修改已有的视图

create or replace  view view_01 as
-- sql 语句1
select * from crTale_01

–创建视图2

create view view_02 as
-- sql 语句2
select * from crTale_01 where sex='男';

–视图查询

select *　from view_02

–删除视图

--具有drop view 和 drop any view 权限的用户才可以删除
drop view view_02;

序列：是一数据库对象，利用它可以生成唯一的整数，一般用来使序列自动的生成主键值。

–创建序列

create sequence my_num 
start with 1003    --指定生成的第一个序列号
increment by 2     --指定序列号之间的间隔
maxvalue 99999     --指定序列号可生成的最大值
cache 20;          --值 （缓存量：默认为20）| nocache (没有缓存)

–修改序列（不能修改 “start with ”参数）
cycle 如果达到最大值，重新开始| nocycle (默认)不重新开始

alter sequence my_num
maxvalue 9999
cycle;

–删除序列

drop sequence my_num;

–插入数据使用序列

insert into crTale_01 values(my_num.nextval,'江小白','123456','男',23,sysdate,'中国')
insert into crTale_01 values(my_num.nextval,'翠花','123456','男',23,sysdate,'中国')

函数

数值函数

– 1、ABS(n) 返回数字n的绝对值.

select 'ABS(-12.3)：' || abs(-12.3) FROM DUAL;

– 2、CEIL(n) 返回大于等于数字n的最小整数.

select 'CEIL(5.3)：'   || ceil(5.3)  FROM DUAL;
select 'CEIL(-5.3)：'  || ceil(-5.3)  FROM DUAL;
select 'CEIL(5)：'     || ceil(5)  FROM DUAL;

– 3、FLOOR(n) 返回小于等于数字n的最大整数.

select 'FLOOR(5.3)：'   || floor(5.3)  FROM DUAL;
select 'FLOOR(-5.3)：'  || floor(-5.3)  FROM DUAL;
select 'FLOOR(5)：'     || floor(5)  FROM DUAL;

– 4、ROUND(n,[m]) 用于执行四舍五入运算。
– 如果省略 m ，则四舍五入至整数位；
– 如果 m 是负数，则四舍五入到小数点前 m 位；
– 如果 m 是正数，则四舍五入至小数点后 m 位。

select 'ROUND(2020.01091230)：'    || round(2020.01091230) from dual;
select 'ROUND(2020.01091230,3)：'  || round(2020.01091230,3) from dual;
select 'ROUND(2020.01091230,-3)：' || round(2020.01091230,-3) from dual;

– 5、TRUNC(n,[m]) 用于截取数字。
– 如果省略数字 m ，则将数字 n 的小数部分截取；
– 如果数字 m 是正数，则将数字 n 截取至小数点后第 m 位；
– 如果数字 m 是负数，则将数字 n 截取至小数点前的 m 位；

select 'TRUNC(2020.01091230)：'    || trunc(2020.01091230) from dual;
select 'TRUNC(2020.01091230,3)：'  || trunc(2020.01091230,3) from dual;
select 'TRUNC(2020.01091230,-3)：' || trunc(2020.01091230,-3) from dual;

字符函数

– 1、 LTRIM(char [,set]) 去掉字符串char 左端包含的set 中的任何字符。set 默认为空格.

select 'LTRIM:' || ltrim('this is','th') from dual;

– 2、LOWER(char) 将字符串转化为小写格式.

select lower('HELLO WORD') FROM DUAL;

– 3、UPPER(char) 将字符串转化为大写格式.

select upper('hello word') FROM DUAL;

– 4、LENGTH(char) 返回字符串长度.

select length('word') from dual;

– 5、RTRIM(char [,set]) 去掉字符串 char 右端包含的set 中的任何字符。 set 默认为空格.

select rtrim('hello word','or') from dual

– 6、REPLACE(char,search_string[,replace_string]) 将字符串中的字符替换为指定的字符.

select replace('hello word','word',',what are you doing ?') from dual;

转换函数

-- 1、TO_NUMBER() 将符合特定数字格式的字符串转变成数字值
– 这里解释哈下面的 ‘9999D9999’ 写法，“D” 前面“9” 的个数是根据前面 “2020.0109” 小数点前面有多少位数字决定的，
– 同理 “D” 后面的 “9” 位数也是一样的道理

select to_number('2020.0109','9999D9999') from dual;

– 2、TO_CHAR() 将日期型转变为字符串

select to_char(sysdate,'dd-mm-yyyy') from dual;

– 3 TO_DATE() 将符合特定格式的字符串转变为date类型的值.

select to_date('09-01-2020','dd-mm-yyyy') from dual;

– 4、NVL(expr1,expr2) 将NULL 转变为实际值。
– 如果expr1 是null ,则返回 expr2 ；
– 如果expr1 不是null,则返回expr1 .
– 参数expr1 和expr2 可以是任意数据类型，但两者的数据类型必须要匹配.

select nvl(username,'木有') from crTale_01 where id=1001

– 5、NVL2(expr1,expr2，expr3) 将NULL 转变为实际值。
– 如果expr1 是null ,则返回 expr3 ；
– 如果expr1 不是null,则返回expr2 .
– 参数expr1 可以是任意数据类型，而参数expr2 和expr3 可以是出long 之外的任何数据类型.

select nvl2(username,'存在','木有') from crTale_01 where id=1001

多表查询

使用集合操作符

– 1、 union 和 union all ：表示或者的意思，相当于 or
– union 返回查询检索到的所有不重复的数据
– union all 返回查询所有的数据，包括重复的数据

select * from crTale_01 where address='中国'
union 
select * from crTale_01 where sex='男'

select * from crTale_01 where sex='男' or address='中国'

select * from crTale_01 where address='中国'
union all
select * from crTale_01 where sex='男'

– 2、minus : 表示返回第一个查询检索到的数据减去第二个查询检索到的数据

select * from crTale_01 where sex='男'
minus
select * from crTale_01 where address='中国'

– 3、intersect : 表示返回两个查询都检索到的数据，相当于 and

select * from crTale_01 where sex='男'
intersect
select * from crTale_01 where address='中国';

select * from crTale_01 where sex='男' and address='中国';

连接查询：查询两张或者两张以上的表格或者视图

– 1、使用内连接.

select  username,sex,remarks,crTale_02.ttime 
from crTale_01,crTale_02
where crTale_01.id=crTale_02.id;

– 2、自连接
自连接是指在同一表之间的连接查询，它主要用在自参照表上显示上下级关系。

select  username,sex,remarks,t2.ttime 
from crTale_01 t1,crTale_02 t2
where t1.id=t2.id and t1.username='老王';

– 3、内连接
用于返回满足连接条件的记录；
关键词：inner join
默认连接情况在没有指定的情况下都是内连接方式

select username,sex,age,remarks,t2.ttime
from  crTale_01 t1 inner join crTale_02 t2 
on t1.id=t2.id and t1.username='老王';
--等价于
select username,sex,age,remarks,t2.ttime
from  crTale_01 t1, crTale_02 t2 
where t1.id=t2.id and t1.username='老王';

外连接是内连接的扩展，它不仅返回满足连接条件的所有记录，还会返回不满足连接条件的记录

– 4、左连接
关键词：left join
当使用左外连接时，不仅返回满足连接条件的所有记录，还会返回连接关键词“left join”左边表格中不满足连接条件的其他数据

select username,sex,age,remarks,t2.ttime
from  crTale_01 t1 left join crTale_02 t2 
on t1.id=t2.id and t1.username='老王';

– 4、右连接
关键词：right join
当使用左外连接时，不仅返回满足连接条件的所有记录，还会返回连接关键词“left join”右边表格中不满足连接条件的其他数据

select username,sex,age,remarks,t2.ttime
from  crTale_01 t1 right join crTale_02 t2 
on t1.id=t2.id and t1.username='老王';

事物处理

事物用去确保数据库数据的一致性，事物处理和锁是两个紧密联系的概念。事物就是一个单元的工作。

– 1、用commit 提交事务

--修改数据
update crTale_01 set username='老王' where id=1001
--提交事务
commit

– 2、回退事务
回退可以撤销已进行的操作。

--修改数据
update crTale_01 set username='老王' where id=1001
--回滚事务
rollback
select username from crTale_01 where id=1001
结果为：江小白

– 3、设置保存点

savepoint a;
或者
exec dbms_transaction.savepoint('a')

– 4、取消部分事物

rollback  to  a;
或者
exec dbms_transaction.rollback_savepoint('a')

– 5、取消全部事物

rollback；
或者
exec dbms_transaction.rollback('a')

事物的ACID属性

数据库事物具有ACID特性。ACID是指原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durabilily）。ACID特性描述了事物处理的规则。

1、原子性
原子性表示事处理要么全部进行，要么全部撤销。

2、一致性
一致性表示事物处理要将数据库从一种状态转变成另一种状态。

3、隔离性
隔离性表示在事物处理提交之前，事务处理的效果不能由系统中其他事物处理看到。

4、持久性
持久性表示一旦提交事务处理，它就永远生效。

过程和函数

存储过程

–存在意义：需要在应用中经常需要通过执行特定的操作，基于这些操作建立一个特定的过程

– 1、建立一个不带参数的过程

create or replace procedure time_out
is 
begin
   dbms_output.put_line('当前系统时间为：' || systimestamp);
   dbms_output.put_line('当前系统时间为：' || to_char(sysdate,'yyyy-mm-dd'));
end;

--调用执行
begin 
  time_out;
end;

– 2、建立一个带参数的过程

create or replace procedure inser_data(name varchar,pwd varchar2,sex char,age number,address varchar2)
is 
begin
   insert into crTale_01 values(my_num.nextval,name,pwd,sex,age,sysdate,address);
end inser_data;

--调用执行
begin 
  inser_data('小明','123654','男',20,'重庆市沙坪坝');
end;

– 3、建立一个带OUT 参数的过程

--创建
create  or replace procedure testOut(val1 number,val2  number,val out number)
is 
begin
  val:=val1+val2;
end;

--调用
declare
  result number;
  begin
    testOut(10,20,result);
    dbms_output.put_line(result);
 end;

–删除过程

drop procedure testOut

函数

–存在意义：需要在应用中经常需要通过执行SQL 语句来返回特定数据

– 1、创建函数


	--创建
	--这里提醒一哈，在函数参数列表中的参数名不要和sql语句中的条件 字段同名(eg: inid 不和下面 where 语句中的名相同)，
	--创建函数可能没问题，但是调用就有问题了.
	
	create or replace function get_username(inid number)
	return varchar2
	is
	retname varchar2(30);
	begin
	  select username into retname from crTale_01 where id=inid;
	  return retname;
	end;
	
	 --调用方式1
	declare 
	 id number :=1001;
	 username varchar2(30);
	begin
	   username:=get_username(id);
	   dbms_output.put_line('用户名为：' || username);
	end;
	   
	 --调用方式2
	select  get_username(1001) from dual;
	 
	 --删除函数
	 drop function get_user

原文链接：https://blog.csdn.net/qq_44804494/article/details/103912970