我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

Oracle数据库常用语句总结大全

2024-09-28 18:48

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

Oracle数据库常用语句总结大全

创建/删除数据库

# 创建
create database databasename
# 删除
drop database databasename

创建/删除表

# 常规创建
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
# 使用旧表创建新表
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 [unique] 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 列=新的值 [where 条件] --更新满足条件的记录
# 例如：
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 … [WHERE …] [DELETE … WHERE …]
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (value_list) [WHERE …]
LOG ERRORS INTO … REJECT LIMIT [integer|UNLIMITED]
  • target_table 是要更新的表
  • source_table 是参考表
  • USING … ON () 是两个表的连接条件，用于判断记录是否匹配
  • WHEN MATCHED 对于满足匹配条件的记录进行的操作，可以更新或删除
  • WHEN NOT MATCHED 对于不满足匹配条件的记录，可以插入
  • LOG ERRORS INTO 可以将匹配错误的记录记录到日志表中

案例

素材

create table class="lazy" data-src_table(
id number(6),
name varchar2(32),
salary number(6),
bonus number(6));
 
insert into class="lazy" data-src_table values(1,'Vincent',1000,100);
insert into class="lazy" data-src_table values(2,'Victor',2000,200);
insert into class="lazy" data-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 class="lazy" data-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 class="lazy" data-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 class="lazy" data-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清空，尝试将class="lazy" data-src_table的3条数据同步进去，同时salary增加1000

truncate table tgt_table;
 
merge into tgt_table t
using class="lazy" data-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表清空，然后尝试将class="lazy" data-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 class="lazy" data-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 [ASC|DESC], column2 [ASC|DESC], ...;

其中，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;

注意：以上语法和示例仅供参考，实际使用时应根据具体的表和列名进行调整。子查询可以嵌套在主查询中，以便根据子查询的结果来筛选、计算或检查数据。

总结 

到此这篇关于Oracle数据库常用语句总结大全的文章就介绍到这了,更多相关Oracle常用语句大全内容请搜索编程网(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.lsjlt.com)！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

oracle常见语句oracle语句大全和用法oracle基本语句大全
阅读原文内容投诉

Oracle数据库常用语句总结大全

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

Oracle数据库常用语句总结大全

目录创建/删除数据库创建/删除表其他表操作增加字段修改字段重命名字段删除字段主键/索引/视图相关数据插入数据更新数据删除字段拼接merge into语法案例素材同时更新和插入记录同步错误分页查询分组查询/筛选排序连接查询1. 内连接（INN
Oracle数据库常用语句总结大全
2024-09-28

SQL Server数据库常用语句总结大全

目录1. 建库语句2. 删库语句3. 创建数据表4. 创建视图5. 约束语句5.1 主键约束（PRIMARY KEY）5.2 外键约束（FOREIGN KEY）5.3 非空约束（NOT NULL）5.4 唯一约束（UNIQUE）6. 增加语
SQL Server数据库常用语句总结大全
2024-08-14

MongoDB中的常用语句总结大全

2024-04-02

数据库常用的sql语句大全-

前言本片博客使用mysql数据库进行数据操作，使用Navicat for mysql 这个IDE进行可视化操作。每个SQL语句都是亲身实验验证的，并且经过自己的思考的。能够保证sql语句的可运行性。sql语句的命令不区分大小写，但储存的数据是区分大小写的。在这
数据库常用的sql语句大全-
2015-12-25

MySQL 数据库常用操作语句的总结

2023-09-11

总结php中常用的数据库删除语句

2023-05-14

MongoDB数据库常用28条查询语句总结

目录初始MongoDBMongoDB特点1、查询所有记录2、查询去掉后的当前聚集集合中的某列的重复数据3、查询 age = 22 的记录4、查询 age > 22 的记录5、查询 age = 25 的记录
2023-05-18

常用oracle数据库函数总结

2024-04-02

SQL数据库语句大全

2024-04-02

数据库SQL语句优化总结

2024-04-02

MSSQL2005数据库的备份语句总结

2024-04-02

Oracle数据库常用操作sql语句

2024-04-02

数据库基本-SQL语句大全

2024-04-02

SQL Server 数据库部分常用语句小结（四）

42.统计APP应用的DB连接及IP情况select b.hostname ,a.client_net_address, b.program_name ,count(1) as Qtyfrom sys.dm_exec_connections a(nolock)
SQL Server 数据库部分常用语句小结（四）
2017-10-21

【StarRocks】数据结构和修改表语句大全

StarRocks的改表语句 文章目录 StarRocks的改表语句1 数据类型2 修改表结构3 示例 1 数据类型 2 修改表结构 StarRocks 支持多种 DDL 操作。 可以通过 ALTER TABLE
2023-08-17

MySQL数据库常用SQL语句

2024-04-02

MySQL数据库主要的SQL语句大全

2024-04-02

oracle数据库去除重复数据常用的方法总结

2024-04-02

SQL Server数据库的基本操作语句总结

2024-04-02

Mysql数据库常用命令总结

2024-04-02

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈