oracle_sql笔记--未完

sql 操作

常用语句

注释

单行注释

--注释内容

多行注释

/*
	注释内容
*/

表空间，表，表结构查询语句

查询某表所在表空间
select tablespace_name from all_tables where table_name = upper(' [table_name] ');
显示当前数据库的所有表和视图
select * from tab;
显示表空间中的所有表
select TABLE_NAME,TABLESPACE_NAME from all_tables where TABLESPACE_NAME=upper(' [tablespace_name] ');

常用创建语句

sql 建表语句

oracle建表、建主键、外键基本语法
-创建表格语法:

create table 表名(       

字段名1  字段类型(长度)   是否为空,        

字段名2  字段类型         是否为空 );

-增加主键     

alter table 表名 add constraint 主键名 primary key (字段名1);

-增加外键:     
alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2);

oracle命令建立主键外键
约束

primary key(pk_id) 	-- 主键
not null 			-- 非空
default 			-- 默认值  
check 				-- 条件 （n < 10 and n >= 3）或 (n = 1 or n = 2）或 （n in(1,2）)
unique 				-- 不重复   
constraint [别名] 	-- 可做异常提示使用
-- 设置外键
constraint [fk_name] foreign key([fk_id]) references [table_name]([fk_id]) on delete cascade		
on delete cascade 	-- 联协删除 即当父表中数据删除时外键关联项会同时被删除
on delete set null 	-- 父表被删除时设置为空
-- 联合主键
constraint pk_name primary key ([pk_1],[pk_2])
-- 联合主键做外键
constraint fk_name foreign key(fk_1,fk_2) references [table_name]([fk_1],[fk_2])

实例：

1、创建一张学生表
create table t_stu(  
  stuid      number(10)   primary key,        
  stuname    varchar2(20) not null,  		
  stusex     varchar2(2)  default '男' check(stusex in('男','女')),
  phone		 char(11)     constraint stu03_sname 	unique	
);  

 2、创建一张课程表
create table t_couse(  
  couseid     number(10)   primary key,  
  cousename   varchar2(20) not null,  
  cousetype   varchar2(4)
);  
 
3、创建一张学生课程成绩表（包括主外键）
create table t_score(  
  scoreid    number(10) primary key,  
  stuid      number(10) references t_stu(stuid),  
  couseid    number(10),  
  constraint fk_couseid foreign key(couseid)  	
  references t_couse(couseid)  					
  on delete cascade							
);

CREATE TABLE log(
log_id int(10)   unsigned NOT NULL auto_increment,
log_time         datetime NOT NULL,
log_user         varchar(30) NOT NULL,
log_title        varchar(30) default NULL,
log_content      text default NULL,
PRIMARY KEY(log_id));

orale表管理：

-- 追加列
alter table [table_name] add(col,type,constraint);
-- 删除列
alter table [table_name] drop column [col]；
-- 修改列
alter table [table_name] modify(col,type,constraint);
-- 列重命名（一般不会使用）
alter table [table_name] rename column [old_col_name] to [new_col_name];
-- 表重命名 
rename [old_table_name] to [new_table_name];

-- 追加约束
alter table [table_name] add constraint 约束名 约束内容；
-- 追加联合主键
alter table [table_name] add constraint pk_name primary key ([pk_1],[pk_2]);

视图

创建视图

create or replace  view view_a
as 
select col1,col2,...
from table1 a,table2 b
where a.fk_id=b.fked_id;

select * from view_a;

删除视图

drop view view_a;

序列

创建序列

create sequence 序列名
[start with a]		-- 从 a 开始
[increment by b]	-- 每次增加 b
[maxvalue c]		-- 最大值是 c
[mincalue d]		-- 最小值是 d

获取序列的值

nextval: 		-- 获取序列的下一个值
currval: 		-- 获取序列的当前值
-- 使用格式：
序列名.nextval

例： 通过序列和触发器创建一个自增主键表

drop table student;
drop sequence student_sequence;
drop trigger student_trigger;



-- 创建表
create table student(
stu_id number(11) primary key,
stu_no varchar2(8) not null unique,
stu_name varchar2(20) not null,
age number(3),
address varchar2(100));

-- 创建序列
CREATE SEQUENCE student_sequence 
INCREMENT BY 1  
START WITH 1 
NOMAXVALUE  
nocycle 

nocache;
-- 创建触发器
create or replace trigger student_trigger 
before insert on student
for each row 
begin 
select student_sequence.nextval into :new.STU_ID from dual; 
end;
-- 插入数据时已经不需要输入id
insert into student(stu_no,stu_name) values('20200001','小明',22,'北京');
insert into student(stu_no,stu_name) values('20200002','小刚',21,'天津');
insert into student(stu_no,stu_name) values('20200003','小红',19,'太原');
insert into student(stu_no,stu_name) values('20200004','小美',21,'北京');
insert into student(stu_no,stu_name) values('20200005','小军',23,'北京');
insert into student(stu_no,stu_name) values('20200006','小强',22,'天津');
insert into student(stu_no,stu_name) values('20200007','小灰',21,'北京');
insert into student(stu_no,stu_name) values('20200008','小壮',19,'太原');
insert into student(stu_no,stu_name) values('20200009','小爱',20,'北京');
insert into student(stu_no,stu_name) values('20200010','小玲',19,'太原');
-- 提交
commit;

索引

唯一索引
索引在表包含大量信息时提高查询效率，一般的会把主键列自动创建为唯一索引

-- 手动创建索引
create index 索引名 on 表名(列名);
-- 删除索引
drop index 索引名;

常用语句（删、改、增）

删除语句

delete from [table_name] where ...;

插入语句

-- 插入一条数据
insert into [table_name](col1,col2,...) values(val1,val2,...);

-- 同时插入多条数据
insert all
into [table_name](col1,col2,...) values(val1,val2,...)
into [table_name](col1,col2,...) values(val1,val2,...)
into [table_name](col1,col2,...) values(val1,val2,...)
select 1 from dual;
-- 同时插入多条数据 方法2
insert into [table_name](col1,col2,...)
(select val1,val2,... from dual union all
 select val1,val2,... from dual union all
 select val1,val2,... from dual);
-- 通过 union all 合并记录 将结果集插入表中 (union all 比 union 效率高 ， 但不检查是否重复)

更改数据

update [table_name] set [col1=?,col2=?,...] where ...;

查询语句

常用查询

-- 基本语法：
select [col1,col2,...] from [table1,table2,...] where ...;

-- 去重 distinct
select distinct [col1,col2,...] from [table1,table2,...] where ...;

-- 字符串连接 ||
select [col1||'str1'||col2||'str2'] from [table1,table2,...] where...;

-- 模糊查找 like '%' '_'
-- '%' 表示 0 个 或 n 个任意字符
-- '_' 表示 1 个 任意字符
select [col,...] from [table,...] where (col)name like '%李';	-- name = ??李
select [col,...] from [table,...] where (col)name like '李_';	-- name = 李?
-- 可以同时使用多个 '%' '_'

-- 日期默认格式	DD-MON-YY	示例： 2020年8月15日  ->   '15-8月-20'
select [col,...] from [table,...] where (col)date < '15-8月-20';	-- 日期在2020年8月15日以前

-- 判断是否为空 写法		is (not) null
select [col,...] from [table,...] where col is null;	-- col 为空

-- 其他比较运算
where	col between [val1] and [val2]
	-- 等价于 col >= [val1] and col <= [val2]    --- [val1] < [val2]
where	col in(val1,val2,...)
	-- col 是集合内某个值
where	col >= all(val1,val2,...)
-- col 大于等于 集合内所有值
where	col <= any(val1,val2,...)
-- col 小于等于 集合内任意一个

排序

关键字 order by [col] asc(desc);

格式：

-- asc  升序	从小到大
-- desc 降序	从大到小
select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc);
-- 默认为升序 一般情况升序也写上 asc 
select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc),[col2] asc(desc),...;
-- 可以根据多列排序
-- 排序先根据 col1 排序 再根据 col2 排序 以此类推
-- 每个列 需要单独指定 排序规则 默认为 升序

分组

关键字 group by [col]
格式

select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc);
-- 默认为升序 一般情况升序也写上 asc 
select [col1,col2,...] from [table1,table2,...]
where ...
group by [col]
order by [col1] asc(desc),[col2] asc(desc),...;
-- 根据某项分成几组 相同数据分为一组
-- 一旦使用了分组,只能查询分组中的信息

过滤

where: 是对分组前的数据进行过滤，过滤的是单条数据，where 后面不能跟分组函数，必须写在分组前面
having: 是对分组后的数据进行过滤，过滤的是组数据，必须写在分组后面

select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc);
-- 默认为升序 一般情况升序也写上 asc 
select [col1,col2,...] from [table1,table2,...]
where ...
group by [col]
having ...
order by [col1] asc(desc),[col2] asc(desc),...;

子查询

格式示例

-- 查询工资高于 ALLEN 的工资的员工信息
select *
from emp
where sal>(select sal from emp where 'ALLEN');

子查询不只可以运用到查询上，增删改 也可以使用子查询。

分页

rownum
– rownum 会按添加顺序给表的数据添加一个编号，编号为从 1 开始自然增长的正整数

格式：

-- 查询第 10 ~ 20 条的全部数据
SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ORDER BY GMT_CREATE DESC) ROW_
         WHERE ROWNUM <= 20)
 WHERE ROWNUM_ >= 10;

这应该是我们大部分程序里所用到的版本，因为这个版本很容易实现复用，中间ROW_部分，就是我们平常写到的sql语句，然后再将起始条数和终止条数作为专门的分页sql语句传入即可查询出我们想要的结果。
结合 between

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ORDER BY GMT_CREATE DESC) A)
 WHERE RN BETWEEN 10 AND 20;

这个就是换汤不换药了，而且查询效率更低，因为：
由于查询条件BETWEEN 10 AND 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，这个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比原始的查询低得多。

结合 minus

SELECT * FROM TABLE1 WHERE ROWNUM <= 20
MINUS
SELECT * FROM TABLE1 WHERE ROWNUM <= 10;

查询了两次，效率上更差了一些。
rowid
rowid 与 rownum 的区别

SELECT *
  FROM (SELECT RID
          FROM (SELECT R.RID, ROWNUM LINENUM
                  FROM (SELECT ROWID RID
                          FROM TABLE1
                         WHERE TABLE1_ID = XX
                         ORDER BY order_date DESC) R
                 WHERE ROWNUM <= 20)
         WHERE LINENUM >= 10) T1,
       TABLE1 T2
 WHERE T1.RID = T2.ROWID;

和前面ROWNUM实现方式相比，该SQL的实现方式更加繁琐，通用性也不是非常好，因为要将原始的查询语句分成两部分（查询字段在最外层，表及其查询条件在最内层），想要复用就很困难了；
但这种实现在特定场景下还是有优势的：比如我们经常要翻页到很后面，比如10000条记录中我们经常需要查9000-9100及其以后的数据；此时该方案效率可能要比前面的高；
因为前面的方案中是通过ROWNUM <= 9100来控制的，这样就需要查询出9100条数据，然后取最后9000-9100之间的数据，而这个方案直接通过ROWID取需要的那100条数据；

集合

格式：使用集合关键字连接两个完整的查询语句

select * from table where ...
union
select * from table where ...;

-- 并集
-- 保留全部的查询结果
union: 				-- 检查重复
union all:		 	-- 不检查重复
-- 交集
intersect: 			-- 保留相同的查询结果
-- 差集
minus： 			-- 从一个查询结果中去除另一个查询结果

多表连接

内联

-- 只会包含有两表中关联的数据
select * from table1,table2 where table1.fid = table2,fid

外联

左外联  		-- 无论是否有关联都会包含全部 table1 的数据  无关联数据的列显示 null
select * from table1
left outer join table2 on table1.fid=table2.fid
右外联		-- 无论是否有关联都会包含全部 table2 的数据  无关联数据的列显示 null
select * from table1
right outer join table2 on table1.fid=table2.fid
满外联		-- 无论是否有关联都会包含全部 table1 和 table2 的数据  无关联数据的列显示 null
select * from table1
full outer join table2 on table1.fid=table2.fid

sql函数

单行函数

• 操作数据对象
• 只对一行进行变换
• 每行返回一个结果
• 可以转换字符类型
• 可以嵌套
• 参数可以是一个列或一个值

通用函数

nvl(val1,val2) 			-- 当 val1 不为空时 return val1	
						-- 当 val1 为空时 return val2
						-- val1 与 val2 数据类型相同
--再使用表达式计算时 如: (sal+count) 如果 conut 的值为 null 则无法计算出结果这时使用 (sal+nvl(count,0)) 默认 count 的值为 null 时 当做 0 来计算。
nvl2(val1,val2,val3)	-- 当 val1 不为空时 return val2	
						-- 当 val1 为空时 return val3
						-- val1 val2 val3 数据类型相同
nullif(val1,val2)		-- if val1 = val2 return null
						-- else return val1

字符函数

对字符串进行操作的函数

lower(str);					-- 转换为小写
upper(str); 				-- 转换为大写
initcap(str);				-- 首字母大写
concat(str1,str2);			-- 连接字符串
substr(str,start,len);		-- 截取字符串 (从 start 开始 截取 len 个字符)
trim(char from str);		-- 去除字符串中前后的 char 字符 默认为 ' '
replace(str,char1,char2);	-- 将字符串中所有的 char1 字符 替换为 char2 字符
length(str);				-- 获取字符串长度

数值函数

mod(a,b);					-- 求余数	a % b
power(a,b);					-- 求幂数	a ^ b 
sqrt(a);					-- 求平方根
round(a,b);					-- 四舍五入 保留 b 位小数
trunc(a,b);					-- 去尾法 保留 b 位小数

日期函数

日期格式	'DD-MON-YY'
sysdate 						-- 获取系统日期
to_date(str)					-- 日期类型转换函数
date + num 						-- 日期 + 天数 = 日期
date - to_date(str)				-- 日期 - 日期 = 相差天数	得到的数据非常准确会带有很长的小数	可以使用 trunc/round 函数保留0位小数得到天数结果
months_between(date1,date2);	-- 获取两个日期相差的月数
add_months(date,num);			-- 给指定日期加上指定月数
last_day(date);					-- 获取指定日期所在月的最后一天
next_day(date,num);				-- 获取指定日期之后的第一个周几 (在西方周日是一周的开始周六是一周的结束，所以 1 代表周日 2 代表周一 ... 7 代表周六）
next_day(date,'星期一'); 		-- next_day 的第二种写法
round(date,'MONTH');			-- 四舍五入 设置精确到 ('YEAR','MONTH','DAY')	  对于日期与月份等是对半分精确的 如： 按月精确 日期按30天算 不足15天舍去  
trunc(date,'MONTH'); 			-- 去尾法 设置精确到 ('YEAR','MONTH','DAY')	

转换函数

to_date(str,pattern);				-- 将字符串转换为日期
to_char(date,pattern);				-- 将日期转换为指定格式的字符串
/*
pattern 模式字符串
	不指定显示模式 默认为 'DD-MON-YY'
	YYYY	-- 年
	YEAR	-- 年的全称	2021年
	MM		-- 月
	MONTH	-- 月的全称	8月
	DY		-- 
	DAY		-- 天的全称	15日
	DD		-- 天
	HH		-- 小时 12时制
	HH24 	-- 小时 24时制
	MI		-- 分钟
	SS		-- 秒
*/

条件表达式

/*
	case col						-- 默认 col = 表达式
		when 表达式1 then 结果1			
		when 表达式2 then 结果2
		...
		else ...
	end
	
	或者可以这么写
	case 							-- 可以做 > < >= 等等
		when col<表达式1 then 结果1
		when col=表达式2 then 结果2
		...
		else ...
	end
*/
-- 条件表达式结果直接输出出来
select col1,
	   col2,
	   case col						
		   when 表达式1 then 结果1			
		   when 表达式2 then 结果2
		   ...
		   else 结果n
	   end
from table;
-- 条件表达式作为操作条件
select col1,col2
from table
where case col						
		   when 表达式1 then 结果1			
		   when 表达式2 then 结果2
		   ...
		   else 结果n
	   end
decode(表达式,值1,结果1,值2,结果2,...,结果3);
				-- 当 表达式值 等于 值1 时 返回 结果1
				-- 当 表达式值 等于 值2 时 返回 结果2
				-- 当 表达式值 为 其他值 时 返回 结果3

多行函数

多行函数 又叫 分组函数 作用于一组数据 返回一个值的函数

min(col)				-- 最小值
max(col)				-- 最大值
count(col)				-- 返回数据个数		不计算空数据	
sum(col)				-- 求和				不计算空数据	
avg(col)				-- 求平均			不计算空数据	

关于dual表

1.dual 伪表
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：

　　1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;

　　2、用来调用系统函数

　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

　　select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名

　　select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale

　　select dbms_random.random from dual;--获得一个随机数

　　3、得到序列的下一个值或当前值，用下面语句

　　select your_sequence.nextval from dual;--获得序列your_sequence的下一个值

　　select your_sequence.currval from dual;--获得序列your_sequence的当前值

　　4、可以用做计算器 select 7*9 from dual;

　　------

　　Oracle系统中dual表是一个“神秘”的表，网上有很多网友都对该表进行了测试，该表只有一行一列，其实该表和系统中的其他表一样，一样可以执行插入、更新、删除操作，还可以执行drop操作。但是不要去执行drop表的操作，否则会使系统不能用，数据库起不了，会报Database startup crashes with ORA-1092错误。此时也不要慌乱，可以通过执行以下步骤来进行恢复。可以用sys用户登陆。

　　SQL> create pfile=’d:pfile.bak’ from spfile

　　SQL> shutdown immediate

　　在d:pfile.bak文件中最后加入一条：

　　replication_dependency_tracking = FALSE

　　重新启动数据库：

　　SQL> startup pfile=’d:pfile.bak’

　　SQL> create table “sys”.”DUAL”

　　[an error occurred while processing this directive]

　　=====

　　DUAL ? 有什么神秘的? 当你想得到ORACLE系统时间, 简简单单敲一行SQL　不就得了吗? 故弄玄虚….

　　SQL> select sysdate from dual;

　　SYSDATE

　　---------

　　28-SEP-03

　　哈哈, 确实DUAL的使用很方便. 但是大家知道DUAL倒底是什么OBJECT, 它有什么特殊的行为吗? 来,我们一起看一看.　首先搞清楚DUAL是什么OBJECT :

　　SQL> connect system/manager

　　Connected.

　　SQL> select owner, object_name , object_type from dba_objects where object_name like '%DUAL%';　OWNER OBJECT_NAME OBJECT_TYPE

　　--------------- --------------- -------------

　　SYS DUAL TABLE　PUBLIC DUAL SYNONYM

　　原来DUAL是属于SYS schema的一个表,然后以PUBLIC SYNONYM的方式供其他数据库USER使用.

　　再看看它的结构:

　　SQL> desc dual　Name Null? Type

　　----------------------------------------- -------- ----------------------------

　　DUMMY VARCHAR2(1)

　　SQL>

　　只有一个名字叫DUMMY的字符型COLUMN .

　　然后查询一下表里的数据:

　　SQL> select dummy from dual;

　　DUMMY

　　----------

　　X

　　哦, 只有一条记录, DUMMY的值是’X’ .很正常啊,没什么奇怪嘛. 好,下面就有奇妙的东西出现了!

　　插入一条记录:

　　SQL> connect sys as sysdba

　　Connected.

　　SQL> insert into dual values ( 'Y');

　　1 row created.

　　SQL> commit;

　　Commit complete.

　　SQL> select count(*) from dual;

　　COUNT(*)

　　----------

　　2

　　迄今为止,一切正常. 然而当我们再次查询记录时,奇怪的事情发生了

　　SQL> select * from dual;

　　DUMMY

　　----------

　　X

　　刚才插入的那条记录并没有显示出来 ! 明明DUAL表中有两条记录, 可就是只显示一条!

　　再试一下删除 ,狠一点,全删光 !

　　SQL> delete from dual; /*注意没有限定条件,试图删除全部记录*/

　　1 row deleted.

　　SQL> commit;

　　Commit complete.

　　哈哈,也只有一条记录被删掉,

　　SQL> select * from dual;

　　DUMMY
　　

----------

Y

为什么会这样呢? 难道SQL的语法对DUAL不起作用吗?带着这个疑问,

我查询了一些ORACLE官方的资料. 原来ORACLE对DUAL表的操作做了一些内部处理,尽量保证DUAL表中只返回一条记录.当然这些内部操作是不可见的 .　看来ORACLE真是蕴藏着无穷的奥妙啊!