Oracle

最新推荐文章于 2023-09-26 14:59:51 发布

zengshangchun

最新推荐文章于 2023-09-26 14:59:51 发布

阅读量568

点赞数

分类专栏： Oracle 文章标签： java

本文链接：https://blog.csdn.net/zengshangchun/article/details/84349732

版权

Oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1，新建stuinfo表：

create table user (
stuId NUMBER generated as identity,
stuName VARCHAR2(20) NOT NULL,
stuAge NUMBER(3,0) NOT NULL
);
//自增，12c才有的，以前版本自增需要序列
generated as identity	//自增，不能手动给值。
generated by default on null as identity	//自增，可以手动给值，给null值就用自增序号
generated as identity (start with 10 increment by 2)		//设置初始值10，间隔值2

2，截断命令，删除stuinfo表数据，不删除表结构：

truncate table stuinfo;
delete from 表名

2.1，删除表：

drop table 表名

3，更改：

alter table 旧表名 rename 新表名	    //修改表名
alter table 表名 add 字段名 数据类型 属性  	 //添加字段
alter table 表名 change 旧字段名 新字段名 数据类型		//修改字段
alter table 表名 drop 字段名						//删除字段 
alter table 表名 add constralnt 主键名 primary key 表名(主键字段)   //添加主键

4，删除

delete from 表名 where 条件

5，插入表数据：
字段可以省略

insert into stuinfo (stuNo,stuName,stuAge,stuId,stuSeat) values('12345','张三',18,'1234567',12);
insert into stuinfo select * from student  //把查询出来的数据插入stuinfo 表

6，修改表数据：

update stuinfo set stuAge=20 where stuNo='123';

7，查询数据：

select 字段名1 as 新名,字段名2 as 新名,distinct(字段名，不显示相同的信息行) from 表名
where  查询条件
group by 分组字段1,分组字段2 having 分组字段筛选 
order by 排序的列名 asc,排序的列名 desc     // asc升序，desc降序
rowunm  	//返回一个代表改行的次序，用于分页查询
like '%数学%'  //模糊查询，匹配0或者多个字符，'李__'，匹配单个字符。like concat("%数学%"，"李__")   [a-z] [0-9] [a,d,f]  ^[2-6]
查空值：is null   /  is not null

分页查询：

select * from (select e.*,rownum as rn from (select * from stuinfo order by stuage desc) e) where rn>=3 and rn<6;

说明如下：
select * from stuinfo order by stuage desc	//对stuinfo表的stuage字段降序排列
select e.*,rownum as rn from (select * from stuinfo order by stuage desc) e) //为上面查询的数据添加rownum伪列，好做分页查询

8，内连接：

inner join 表名 on 表名.字段名＝表名.字段名

9，左外连接：

left join 表名 on 表名.字段名＝表名.字段名

10，右外连接：

right join 表名 on 表名.字段名＝表名.字段名

11，子查询：

＝：返回一个值
in：返回多个值
not in：取反

12，数据类型转换：

to_char(字段名,'yyyy-MM-dd')   			 //查询的输出格式,转成字符串
to_date('2000-10-05','yyyy-MM-dd') 		//插入日期，转成日期
to_number('100')				//转成数值类型

13，集合操作符：
union：联合

//返回表中所有不重复的列值，并排序，排序要放在最后一个select 语句之后 
select empno from employee
union
select rempno from retireEmp
order by empno;

union all：联合所有，合并两个查询选定的所有行，包括重复的行
intersect：交集，返回两个查询都有的行
minus：减集，返回由第一个查询选定而未被第二个查询选定的行，即从第一个查询语句选定的所有行中减去第二个查询语句所选定的公共行的内容
14，连接操作符：
|| 将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起

//将查询的 岗位和员工姓名组合在一起，用_隔开
select job||'_'||ename from employee;

15，滤空函数：

//如果exp1的值为空，则返回exp2的值，否则返回exp1的值
NVL(exp1,exp2)
	
//如果exp1的值为空，则返回exp3的值，否则返回exp2的值	
NVL2(exp1,exp2,exp3)

//如果value的值为if1，则返回then1的值，如果value的值为if2，则返回then2的值，否则返回else的值		
decode(value,if1,then1,if2,then2,else)

16，分析函数：
语法：函数名（[参数]）OVER （[分区子句][排序子句]）
分区子句（partition by）：表示将查询结果分为不同的组
排序子句（order by）：表示对每个分区进行排序
用于为每条记录产生一个从1-N的自然数，也就是排名，[]中的可以省略

rank：返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名
dense_rank：返因一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的，最后一条相同记录和下一条不同记录的排名之间不会空出排名
row_number：返回一个唯一的值，当遇到相同的数据时，排名按照记录集中记录的顺序依次递增
//举例如下：
select stuno,stuname,stuage,
	rank() over (order by stuage),
	dense_rank() over (order by stuage),
	row_number() over (partition by stuname order by stuage)
FROM stuinfo;

17，创建表空间：

//worktbs：名字；datafile：指定一个或者多个数据文件；autoextend：自动扩展，on为扩展，off为不扩展
create tablespace worktbs
datafile 'D:\zsc.dbf'
size 10m autoextend on;

18，删除表空间：

drop tablespace worktbs

19，创建用户：

//student：用户名；123456：密码；default：确定默认表空间；temporary：确定默认临时表空间
create user student
identified by 123456
default tablespace tablespace
temporary tablespace tablespace

20，授予权限：

//授予connect和resource两个角色
grant connect,resource to martin;
//撤销connect和resource两个角色
revoke connect,resource from martin;
//允许martin用户查看emp表中的记录
grant select on scott.emp to martin;
//允许martin用户修改emp表中的记录
grant update on scott.emp to martin;

21，序列，用于做自增；
A.创建序列:

create sequence seql    	//seql：用户名
start with 1			//指定一个初始值
increment by 1		//序列号间的间隔，为正数升序排列，负数就降序排列
maxvalue 100		//最大值，指定了nomaxvalue，升序将最大值设为10的27次方，降序最大值设为-1
minvalue 1			//最小值，可以省略，指定了nominvalue，升序将最小值设为1，降序将最小值设为10的26次方
nocycle			//序列达到最大值或最小值后，将不能继续生成值，cycle就相反
cache 20;		//预先分配一组20个号保留在内存中，nocache：不分配。不设置就默认预存20个号

B.访问序列

seql.nextval	//返回自增值
seql.currval	//返回当前值，即最后一次引用的值
//查看序列的当前值
select seql.currval from dual;
//查入序列的值在表中
insert into toys(seql.nextval,'zsc',20);

C.修改序列

alter sequence seql

D.删除序列

drop sequence seql;

22，sys_guid()函数：生成32位唯一编码

select sys_guid() from dual;

23，同义词，为对象取别名，简化SQL语句
A.私有同义词，只能被当前模式的用户访问

create [or replace] synonym [schema.] synonym_name for [schema.]object_name;
or replace：表示在同义词存在的情况下替换该同义词
synonym_name：要创建的同义词名称
object_name：指定要为之创建同义词的对象的名称

B.公有同义词，可以被所有数据库用户访问

create [or replace] public synonym [schema.] synonym_name for [schema.]object_name;

C.删除同义词

drop [public]synonym [schema.]synonym_name;

24，索引

A，B树索引，也称标准索引

//创建索引
create [unique] index index_name on table_name(column_list) [tablespace tab_name];
unique：指定为唯一索引，默认为非唯一索引
index_name：创建索引的名称
table_name：表示为之创建索引的表名
column_list：创建索引的列名和列表，可以多列，逗号隔开
tablespace tab_name：指定表空间

B.反向索引

create index index_reverse on employee(empno) reverse;

C.位图索引

create bitmap index index_job on employee(job);

D.删除索引

drop index index_job;

25，视图

A.创建视图

//将employee表中的数据查出来放入新建的v_employee视图
create or replace view v_employee as select * from employee;

B.删除视图

drop view v_employee;

26，三连表查询当月数据

SELECT s.`party_member_id`,m.name AS mName,b.id AS bId,b.name AS bName,SUM(s.`learning_time`) AS s_um FROM party_learning_situation s INNER JOIN party_member m ON s.`party_member_id`=m.id INNER JOIN party_branch b ON m.branch_id=b.id
WHERE s.`party_constitution_id`>0 AND DATE_FORMAT(s.`learning_date`,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m')  GROUP BY s.`party_member_id` ORDER BY s_um DESC

27，三连表查询本季度数据

SELECT s.`party_member_id`,m.name AS mName,b.id AS bId,b.name AS bName,SUM(s.`learning_time`) AS s_um FROM party_learning_situation s INNER JOIN party_member m ON s.`party_member_id`=m.id INNER JOIN party_branch b ON m.branch_id=b.id
WHERE s.`party_constitution_id`>0 AND QUARTER(s.`learning_date`)=QUARTER(NOW())  GROUP BY s.`party_member_id` ORDER BY s_um DESC

28，三连表查询当年数据

SELECT s.`party_member_id`,m.name AS mName,b.id AS bId,b.name AS bName,SUM(s.`learning_time`) AS s_um FROM party_learning_situation s INNER JOIN party_member m ON s.`party_member_id`=m.id INNER JOIN party_branch b ON m.branch_id=b.id
WHERE s.`party_constitution_id`>0 AND YEAR(s.`learning_date`)=YEAR(NOW())  GROUP BY s.`party_member_id` ORDER BY s_um DESC

29，设置自增的起始值

ALTER TABLE party_member AUTO_INCREMENT=1000

30，B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点。
A,给出其中一个结点值，求其最终父结点

select o.name,o.id,o.PARENTDEPTCODE  from SYS_ORG AS o start with o.id='38b03716fc5140049d829524828ee651' connect by o.id = prior o.PARENTDEPTCODE

start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。
connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历，
parentid、subid两列谁放在“=”前都无所谓，关键是prior跟谁在一起。
B,给出其中一个结点值，求其子结点。

select o.name,o.id,o.PARENTDEPTCODE  from SYS_ORG AS o start with o.PARENTDEPTCODE='38b03716fc5140049d829524828ee651' connect by prior id = o.PARENTDEPTCODE

zengshangchun

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle

1，新建stuinfo表：create table stuinfo (stuNo CHAR(6) NOT NULL,stuName VARCHAR2(20) NOT NULL,stuAge NUMBER(3,0) NOT NULL,stuId NUMBER(18,0),stuSeat NUMBER(2,0));2，截断命令，删除stuinfo表数据，不删除表结构：truncat...
复制链接

扫一扫

专栏目录