【Oracle】Oracle常用语句大全
创建/删除数据库# 创建
create database databasename
# 删除
drop database databasename
创建/删除表
# 常规创建
create table tabname(col1 type1 ,col2 type2 ,..)
# 使用旧表创建新表
select * into table_new from table_old
# 使用旧表创建新表
create table tab_new as select col1,col2… from tab_old
# 删除表
drop table tabname
其他表利用
# 重命名表
alter table 原表名 rename to 新表名
增加字段
[*] 语法：alter table 表名 add (字段名 字段类型 默认值 是否为空);
[*] 例如：
alter table tablename add (ID varchar2(30) default '空' not null);
修改字段
[*] 语法：alter table 表名 modify (字段名 字段类型 默认值 是否为空);
[*] 例如：
alter table tablename modify (ID number(4));
重命名字段
[*] 语法：alter table 表名 rename column 列名 to 新列名 --其中：column是关键字
[*] 例如：
alter table tablename rename column ID to newID;
删除字段
[*] 语法：alter table 表名 drop column 字段名;
[*] 例如：
alter table tablename drop column ID;
主键/索引/视图相关
# 添加主键
alter table tabname add primary key(col)
# 删除主键
alter table tabname drop primary key(col)
# 创建索引
create index idxname on tabname(col….)
# 删除索引 （索引是不可更改的，想更改必须删除重新建。）
drop index idxname
# 创建视图
create view 视图名 as select statement
# 删除视图
drop view viewname
数据插入
# 全字段添加-语法：
insert into 表名 values(所有列的值);
#例如：
insert into test values(1,'zhangsan',20);
# 自定义字段添加-语法：
insert into 表名(列) values(对应的值);
# 例如：
insert into test(id,name) values(2,'lisi');
# 将表1数据插入到表2-语法：
insert into 表2 select * from 表1;
# 例如：
insert into test(name,role,createId,createdate) select name,role,id,now() from test_user;
数据更新
# 语法：
update 表 set 列=新的值 --更新满足条件的记录
# 例如：
update test set name='zhangsan2' where name='zhangsan'
数据删除
# delete语法：
delete from 表名 where 条件 --删除满足条件的记录
# 例如
delete from test where id = 1;
delete from test -->删除所有 delete删除的时候，会记录日志 删除会很慢
# truncate语法：
truncate table 表名 --删除所有数据，不会影响表结构，不会记录日志，数据不能恢复，删除得很快
字段拼接
[*]CONCAT（char1，char2），在oracle中concat函数只能拼接两个值，这根mysql不一样
[*]返回两个字符串毗连后的效果，两个参数char1，char2是要毗连的两个字符串。
[*]等价利用：毗连利用符“||”
[*]假如char1，char2任何一个为NULL，相当于毗连了一个空格
[*]注意：建议多个字串毗连时，用“||”更直观
#字符串函数CONCAT()函数，用来连接字符串
SELECT CONCAT(ename,sal) FROM emp
SELECT CONCAT( CONCAT(ename,','),sal) FROM emp
SELECT ename||','||sal FROM emp
merge into
[*]在Oracle中，通例的DML语句只能完成单一功能，，例如insert/delete/update只能三选一，而merge into语句可以同时对一张表举行更新/插入/删除。
[*]merge into常用在数据同步的场景，它会选定一张基表作为数据源，然后与目标表的记载举行匹配，根据匹配的效果，可以对目标表同时举行更新/插入/删除利用。
语法
MERGE INTO target_table
USING source_table ON (join_condition)
WHEN MATCHED THEN UPDATE SET …
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (value_list)
LOG ERRORS INTO … REJECT LIMIT
[*]target_table 是要更新的表
[*]source_table 是参考表
[*]USING … ON () 是两个表的毗连条件，用于判断记载是否匹配
[*]WHEN MATCHED 对于满足匹配条件的记载举行的利用，可以更新或删除
[*]WHEN NOT MATCHED 对于不满足匹配条件的记载，可以插入
[*]LOG ERRORS INTO 可以将匹配错误的记载记载到日志表中
案例
素材
create table src_table(
id number(6),
name varchar2(32),
salary number(6),
bonus number(6));
insert into src_table values(1,'Vincent',1000,100);
insert into src_table values(2,'Victor',2000,200);
insert into src_table values(3,'Grace',3000,300);
create table tgt_table(
id number(6),
name varchar2(32),
age number(6),
salary number(6),
bonus number(6));
insert into tgt_table values(1,'someone',1,0,0);
insert into tgt_table values(3,'someone',3,0,0);
insert into tgt_table values(4,'someone',4,0,0);
commit;
同时更新和插入
merge into tgt_table t
using src_table s on (t.id=s.id)
when matched then update set t.name=s.name, t.salary=s.salary, t.age=10
when not matched then insert values(s.id,s.name,10,s.salary,s.bonus+50);
commit;
select * from tgt_table;
[*]When matched 为匹配存在的记载（id为1，3），更新了name, salary, age个字段
[*]When not match 为目标表不存在的记载（id为2），插入了该记载，同时bouns字段加50
[*]Matched 和 not matched的子句是独立的，可以任意选择一项，或同时出现
[*]目标表中id为4的记载在源表中不存在（不满足毗连条件），因此不会涉及
对于匹配的记载，可以使用where子句进一步限制范围
merge into tgt_table t
using src_table s on (t.id=s.id)
when matched then update set t.bonus=s.bonus where s.id>=2;
Select * from tgt_table order by id;
[*]这里对匹配的记载bonus字段举行更新，同时进一步限制id>=2的记载才更新
[*]Where 子句的条件可以通过源表大概目标表指定，这里是通过源表的s.id指定
在update子句后，还可以跟上delete … where …子句，对匹配上的记载举行删除利用
merge into tgt_table t
using src_table s on (t.id=s.id)
when matched then update set t.bonus=s.bonus+50 where s.id>=3 delete where id>=2;
select * from tgt_table order by id;
[*]delete where id>=2 指定将ID大于等于2的记载删除，但注意id为2的记载并未被删除，只有3被删除了
[*]由于delete只会在update匹配的到记载范围内删除，update子句有个where s.id>=3，delete也会受到这个条件的限制
[*]现实实行的效果是 delete where s.id>=3 and id>=2，只有id为3的记载满足这个条件
[*]id为4的记载不在匹配范围内，不受merge into语句的影响，也不会被删除
记载同步错误
数据同步的时，源表和目标表的结构/数据类型/约束大概并不一致，这就导致数据同步大概部分失败，现在我们修改tgt表，限制salary字段值不能凌驾3000，即插入凌驾3000的数字将失败：
Alter table tgt_table modify salary number(6) check(salary<=3000);
起首调用dbms_errlog.create_error_log为tgt_table创建一张错误日志表，表名为errlog：
exec dbms_errlog.create_error_log('tgt_table', 'errlog');
[*]第一个参数指定要创建错误日志的基表，第二个参数是错误日志表名
然后在实行merge into 语句时，在最后跟上log errors into子句，假如语句实行过程中遇到错误，则会将错误记载到错误日志中，方便后期排查和修复，这里将tgt_table清空，实行将src_table的3条数据同步进去，同时salary增加1000
truncate table tgt_table;
merge into tgt_table t
using src_table s on (t.id=s.id)
when not matched then insert values(s.id,s.name,10,s.salary+1000,s.bonus)
log errors into errlog('something is wrong.');
select * from tgt_table;
[*]这里将tgt_table表清空，然后实行将src_table中的3条记载同步进来，同步过程中我们将salary增加了1000
[*]由于tgt_table表salary有约束不能凌驾3000，因此语句回滚，一条记载都没同步进来
[*]errlog中的’something is wrong.’ 是用户界说的错误标记，可以帮助辨认是哪个语句导致的错误
查询errlog表，可以看到导致失败的缘故原由，id为3的记载，salary在增加1000后为4000，违背了目标表的约束（check salary<=3000）
假如我们不想让出现错误的时候语句就回滚，可以在后面跟上一个reject limit N子句，限制只有出现N个以上的错误时才回滚语句：
merge into tgt_table t
using src_table s on (t.id=s.id)
when not matched then insert values(s.id,s.name,10,s.salary+1000,s.bonus)
log errors into errlog('Allow one error.') reject limit 1;
select * from tgt_table;
[*]我们在上面的语句后面增加了1个reject limit 1子句，当出现1个及以下的错误时，并不会回滚
[*]因此id为1和2的记载成功插入，语句并未报错
[*]errlog的用户标记修改为’Allow one error.’
[*]通过Allow one error标签，我们发现ID为3的记载因违背约束没有被插入
[*]但这此有reject limit 1子句，语句答应出现1个及以下错误，因此满足条件的记载被成功插入
[*]假如选择reject limit unlimited，则不限制错误数量
分页查询
在Oracle中，可以使用ROWNUM关键字来实现分页查询。
示例1：查询表中的前10条记载
SELECT *
FROM your_table
WHERE ROWNUM <= 10;
示例2：查询表中的第11到20条记载
SELECT *
FROM (
SELECT t.*, ROWNUM AS rnum
FROM (
SELECT *
FROM your_table
ORDER BY your_column
) t
WHERE ROWNUM <= 20
)
WHERE rnum >= 11;
示例3：查询表中的第21到30条记载，并按照指定的列举行排序
SELECT *
FROM (
SELECT t.*, ROWNUM AS rnum
FROM (
SELECT *
FROM your_table
ORDER BY your_column
) t
WHERE ROWNUM <= 30
)
WHERE rnum >= 21;
注意，在查询效果之前使用ROWNUM，否则大概会产生错误的效果。此外，使用子查询的方式可以在外层查询中使用rnum举行过滤，从而实现分页效果。
分组查询/筛选
在Oracle中，可以使用分组查询和筛选语法来对数据举行分组和筛选。
根本的语法如下：
SELECT column1, column2, ..., aggregate_function(column)
FROM your_table
WHERE conditions
GROUP BY column1, column2, ...
HAVING conditions;
其中，column1, column2, …是要查询的列，aggregate_function是聚合函数（如SUM、COUNT、AVG等），your_table是要查询的表，conditions是查询条件，column1, column2, …是要举行分组的列。
示例1：统计每个部分的员工数量，并筛选出员工数量大于5的部分
SELECT department, COUNT(*) AS employee_count
FROM your_table
GROUP BY department
HAVING COUNT(*) > 5;
示例2：计算每个部分的平均工资，并筛选出平均工资大于1000的部分
SELECT department, AVG(salary) AS average_salary
FROM your_table
GROUP BY department
HAVING AVG(salary) > 1000;
示例3：统计每个部分的最高工资，并筛选出最高工资大于5000的部分
SELECT department, MAX(salary) AS highest_salary
FROM your_table
GROUP BY department
HAVING MAX(salary) > 5000;
在HAVING子句中可以使用聚合函数举行筛选，用来对分组后的效果再举行一次筛选。与WHERE子句差别的是，HAVING子句是在分组后举行筛选，可以使用聚合函数，而WHERE子句是在分组前举行筛选，不能使用聚合函数。
注意，在SELECT子句中，除了分组的列和聚合函数外，还可以选择其他需要显示的列，如：
SELECT department, job, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM your_table
GROUP BY department, job
HAVING COUNT(*) > 5;
排序
在Oracle中，可以使用ORDER BY子句对查询效果举行排序。ORDER BY子句的语法如下：
SELECT column1, column2, ...
FROM your_table
WHERE conditions
ORDER BY column1 , column2 , ...;
其中，column1, column2, …是要排序的列，ASC表示升序（默认），DESC表示降序。
示例1：按照工资升序排序查询效果
SELECT *
FROM your_table
ORDER BY salary ASC;
示例2：按照部分升序、工资降序排序查询效果
SELECT *
FROM your_table
ORDER BY department ASC, salary DESC;
示例3：按照姓名升序、年事降序排序查询效果
SELECT *
FROM your_table
ORDER BY name ASC, age DESC;
可以根据需要在ORDER BY子句中指定多个列，并对每个列设置排序方式。假如不指定排序方式，默认为升序。
注意，ORDER BY子句应该在WHERE子句之后使用，用于对筛选后的效果举行排序。假如只有一个列需要排序，可以直接写上列名，假如有多个列需要排序，使用逗号分隔。
毗连查询
在Oracle中，可以使用差别的毗连查询语法来联结多个表。以下是Oracle中的一些常见毗连查询语法及示例：
1. 内毗连（INNER JOIN）：
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
示例：
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
2.左毗连（LEFT JOIN）：
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
示例：
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
3.右毗连（RIGHT JOIN）：
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
示例：
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
4.全外毗连（FULL OUTER JOIN）：
SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;
示例：
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
5.交错毗连（CROSS JOIN）：
SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;
示例：
SELECT employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;
注意：以上语法和示例仅供参考，现实使用时应根据具体的表和列名举行调解。毗连查询可以通过指定JOIN条件，将多个表中的数据关联起来，从而获取更加丰富的查询效果。
子查询
在Oracle中，可以使用子查询来作为查询语句的一部分，以便在查询中使用子查询的效果。以下是Oracle中子查询的语法和示例：
语法：
SELECT column1, column2, ...
FROM table1
WHERE columnN IN (SELECT columnM FROM table2 WHERE condition);
示例：
[*]使用子查询获取某个表中符合条件的数据：
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
[*]使用子查询获取某个表中的最大值或最小值：
SELECT employee_id
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
[*]使用子查询作为存在性检查：
SELECT employee_id, first_name, last_name
FROM employees
WHERE EXISTS (SELECT * FROM job_history WHERE job_history.employee_id = employees.employee_id);
[*]使用子查询作为计算列：
SELECT employee_id, first_name, last_name, (SELECT MAX(salary) FROM employees) - salary AS salary_diff
FROM employees;
注意：以上语法和示例仅供参考，现实使用时应根据具体的表和列名举行调解。子查询可以嵌套在主查询中，以便根据子查询的效果来筛选、计算或检查数据。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]