Oracle 基础
SQL分类
DML(Data Mannipulation Language)数据操纵语言：查询、操纵数据表资料行
SELECT : 检索数据库表或视图数据
INSERT : 将数据行新增至数据库表或视图中
UPDATE : 修改表或视图中现有的数据行
DELETE : 删除表或视图中现有的数据行
注意：DML语句不会自动提交事务！
DDL(Data Definition Language)数据定义语言：建立、修改、删除数据库中数据表对象
CREATE TABLE : 创建表
ALTER TABLE : 修改表
DROP TABLE : 删除表
注意：DLL语句会自动提交事务！
所以：DML语句事务提交之前可以回滚，DDL语句不能回滚事务
DCL(Data Control Language)数据控制语言：用于执行权限授予与收回操作
GRANT : 给用户或角色授予权限
REVOKE : 收回用户或角色的所有权限
1.授权
GRANT <权限列表> to <user_name>;
2.收回权限
REVOKE <权限列表> from <user_name>
Oracle 的权限列表
connect 连接
resource 资源
unlimited tablespace 无限表空间
dba 管理员
session 会话
TCL(Transactional Control Language)事物控制语言：维护数据的一致性
COMMIT : 提交已经进行的数据库改变
ROLLBACK : 回滚已经进行的数据改变
SAVEPOINT : 设置保存点，用于部分数据改变的取消
虚拟表dualOracle中的dual表是一个单行单列的虚拟表。Oracle的SELECT语法的限制为 SELECT * | [column1 [AS alias1], column2 [AS alias2]] FROM table，所以没有表名就没有办法查询，而时间日期并不存放在任何表中，于是这个dual虚拟表的概念就被引入了。
--虚表
select * from dual;
SELECT ABS(100),ABS(-100),ABS('100') FROM DUAL;
select sysdate from dual;
序列语法
最大值： https://www.cnblogs.com/CandiceW/p/10062413.html
CREATE SEQUENCE <sequencen_name>
INCREMENT BY n
START WITH n
[MAXVALUE n][MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
INCREMENT BY n --表示序列每次增长的幅度;默认值为1.
START WITH n --表示序列开始时的序列号。默认值为1.
MAXVALUE n --表示序列可以生成的最大值(升序).
MINVALUE n --表示序列可以生成的最小值(降序).
CYCLE --表示序列到达最大值后，在重新开始生成序列.默认值为 NOCYCLE。
CACHE --允许更快的生成序列.
ALTER SEQUENCE <sequencen_name>
INCREMENT BY n
START WITH n
[MAXVALUE n][MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
类似mssql 的自增id
- 序列刚建立后序列的CURRVAL值为NULL，所以不能直接使用。否则报错“SQL 错误 [8002] [72000]: ORA-08002: 序列 SE_1.CURRVAL 尚未在此会话中定义”
可以先初始化序列:
方法:select <sequence_name>.nextval from dual;
示例:select se_1.nextval from dual;
之后就可以使用CURRVAL属性了
当前值：select <sequence_name>.currval from dual;
下一个值：select <sequence_name>.nextval from dual;
- 不能直接修改START WITH值，比如 alter sequence se_1 start with 1，会报错：SQL 错误 [2283] [42000]: ORA-02283: 无法变更启动序列号
--创建基本序列从1开始递增
CREATE SEQUENCE quence;
--查询序列
SELECT quence.nextval FROM dual;
SELECT quence.currval FROM dual;
-- 前面可以加上数据库 SELECT dbname.quence.currval FROM dual;
--删除序列
DROP SEQUENCE quence;
其他属性
-- Create sequence
create sequence test
minvalue 1 --最小值
maxvalue 9999 --最大值
start with 10 --开始值，默认是1
increment by 1; --增量值1
--创建序列
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
--表使用序列,数据库是先拿序列的下一个id，然后插入数据，如果插入数据失败，序列会更新
insert into 表名 values(序列名.nextval,列1值,列2值, ...);
曲线救国，修改启动号
-- 创建序列
create sequence se_1
increment by 1
start with 100
maxvalue 999999
cycle;
-- 初始化序列，返回100
select se_1.nextval from dual;
-- 步长-99
alter sequence se_1 increment by -99;
-- 将当前值更新为1
select se_1.nextval from dual;
-- 更新步长为1
alter sequence se_1 increment by 1;
DDLCREATE TABLE基本创建
CREATE TABLE imx_file
(
imx01 VARCHAR2(60),
imx02 VARCHAR2(10),
imx03 SMALLINT,
imx04 DATE,
imx05 NUMBER(6)
)
现有数据来创建表
#从查询表中字段创建表
CREATE TABLE … AS SELECT
#复制表和数据
CREATE TABLE imx_temp AS SELECT * FROM imx_file
#复制表结构
CREATE TABLE imx_temp AS SELECT * FROM imx_file WHERE 1=0
#从几个表中选取记录来创建
CREATE TABLE imx_temp AS
SELECT imx01,imd02 FROM imx_file,imd_file
WHERE imx01=imd01
ALTER TABLE
1.向表中添加新字段
ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL],字段2 类型 [NOT NULL].... );
ALTER TABLE imx_file add imx11 varchar2(10);
2.修改表中字段
ALTER TABLE <table_name> modify(字段1 类型,字段2 类型.... );
ALTER TABLE imx_file modify imx11 varchar2(20);
ALTER TABLE imx_file rename column imx1 to imx2;
ALTER TABLE imx_temp modiy imx11 null/not null;
3 .删除表中字段
ALTER TABLE <table_name> drop(字段1,字段2.... );
ALTER TABLE imx_file drop column imx11;
4 .修改表的名称
RENAME <table_name> to <new table_name>;
5 .对已经存在的表添加约束
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 约束类型 (针对的字段名);
示例：
Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);
6 .对表里的约束禁用;
ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;
7 .对表里的约束重新启用;
ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;
8 .删除表中约束
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
示例:
ALTER TABLE emp drop CONSTRAINT <Primary key>;
DROP TABLE
截断表的结果是将表中数据全部删除，和使用DELETE语句的效果相同，两者的区别在与截断操作不记录Redo Log信息，所以更快，但操作无法恢复，所以在程序撰写过程中，一般对于临时表使用截断语句
DROP TABLE imx_temp;
TRUNCATE TABLE imx_temp; --截断表
DESC TABLE
desc 表名称 不能在sql查询窗口使用
创建同义词
CREATE SYNONYM <synonym_name> for <tablename/viewname>
同义词即是给表或视图取一个别名。
示例:
create synonym mm for emp;
DROP SYNONYMmm ;
DMLINSERT
插入多条记录注意oracle不能像mysql一样直接插入多条数据 values(),()
--方法一：INSERT ALL INTO
INSERT ALL
INTO sale(MONTH,sell) VALUES('200001',10000)
INTO sale(MONTH,sell) VALUES('200002',10001)
select 1 from dual;
--方法二：从子查询中插入数据 insert列的数据个数必须和subQuery子查询的列的个数及数据类型完全匹配
INSERT INTO table_name1(column_name1,column_name2,...)
select column_name1,column_name2...FROM table_name2;
插入自增序列
--创建序列
create sequence quence;
--
insert into table(id) values(quence.NEXTVAL)
SELECT
NULL
默认情况下，升序排列时它在最后，降序排列时它在首位。
WHERE XXX IS NOT NULL;--IS NULL
如果希望NULL值数据在首位：ORDER BY XXX NULLS FIRSET|(LAST);
字符串连接符
||
SELECT 'A'||'B' FROM dual;
ROWID
返回数据唯一id值
ROWNUM
mssql ：top 1，mysql：limit 1
返回行号，一般都会外面套一层select
SELECT * FROM ACCOUNT WHERE ROWNUM=1
--先将查询排序做为1个结果集，再从这个子查询的结果集里面再取rownum=1的数据,取得到才是按排序后的第一条数据
SELECT * FROM
(SELECT t_salary.* FROM t_salary
WHERE slip='201308'
ORDER BY slip ASC,item ASC
)
WHERE rownum = 1
分页查询
利用rownum
SELECT *
FROM
(
SELECT t.*, rownum rn
FROM
(
SELECT * FROM TABLE ORDER BY COLUMN ASC -- 分组
) t
)
WHERE
rn > (currentPage-1)*pageSize
AND rn <= currentPage*pageSize;
ANY,SOME,ALL
https://www.cnblogs.com/feiquan/p/8654171.html
intersect交集
交集（结果为前五条数据）
SELECT PERSON_NAME FROM PERSON WHERE ROWNUM <=5
INTERSECT
SELECT PERSON_NAME FROM PERSON WHERE ROWNUM <=8;
MINUS差集
--下面结果为空，第一个查询的5条记录减去第二个查询的记录
SELECT PERSON_NAME FROM PERSON WHERE ROWNUM <=5
MINUS
SELECT PERSON_NAME FROM PERSON WHERE ROWNUM <=8;
--下面返回3条记录
SELECT PERSON_NAME FROM PERSON WHERE ROWNUM <=8
MINUS
SELECT PERSON_NAME FROM PERSON WHERE ROWNUM <=5;
UPDATE
update from
oracle不支持两表关联更新没update from的语法
https://blog.csdn.net/liangmengbk/article/details/103886792
UPDATE PERSON P
SET (UP_D, UP_TYPE) =
(
SELECT M.YYYYMMDD,M.MOB_KIND FROM MOBILIZE_PER M where M.corp_no='2931'
and M.MOBI_NO ='YD211031004' AND M.CORP_NO = P.CORP_NO AND M.PNL_NO =P.PNL_NO
)
WHERE EXISTS
(
SELECT * FROM MOBILIZE_PER M WHERE M.corp_no='2931' and M.MOBI_NO ='YD211031004'
AND M.CORP_NO = P.CORP_NO AND M.PNL_NO =P.PNL_NO
);
DELETE
语法：
DELETE FROM [WHERE ]
注意事项：
如果有外键关联，则删除数据之前,需先删除外键关联数据
delete from gem_file where gem01 = '2BSB'; --where还可以用子查询作为condition
delete 与 truncate 应用区别：
1、对于删除整个表的所有数据时，delete并不会释放表所占用的空间
2、如果用户确定是 删除 整表的所有数据，那么使用 truncate table 速度更快truncate 不可恢复表
delete from gem_file;
truncate table gem_file;
浙公网安备 33010602011771号