• 设为首页
  • 收藏本站
  • 积分充值
  • VIP赞助
  • 手机版
  • 微博
  • 微信
    微信公众号 添加方式：
    1:搜索微信号（888888）
    2:扫描左侧二维码
  • 登录 立即注册
    • 切换风格
    快捷导航
    福建二哥 › 门户 › 查看主题

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

    发布者: 山止川行 | 发布时间: 2025-6-18 09:23| 查看数: 23| 评论数: 0|帖子模式

    创建/删除数据库
    1. # 创建
    2. create database databasename
    3. # 删除
    4. drop database databasename
    复制代码
    创建/删除表
    1. # 常规创建
    2. create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
    3. # 使用旧表创建新表
    4. select * into table_new from table_old
    5. # 使用旧表创建新表
    6. create table tab_new as select col1,col2… from tab_old
    7. # 删除表
    8. drop table tabname
    复制代码
    其他表操作
    1. # 重命名表
    2. alter table 原表名 rename to 新表名
    复制代码
    增加字段


    • 语法：
      1. alter table 表名 add (字段名 字段类型 默认值 是否为空);
      复制代码
    • 例如：
      1. alter table tablename add (ID varchar2(30) default '空' not null);
      复制代码

    修改字段


    • 语法：
      1. alter table 表名 modify (字段名 字段类型 默认值 是否为空);
      复制代码
    • 例如：
      1. alter table tablename modify (ID number(4));
      复制代码

    重命名字段


    • 语法：
      1. alter table 表名 rename column 列名 to 新列名 --其中：column是关键字
      复制代码
    • 例如：
      1. alter table tablename rename column ID to newID;
      复制代码

    删除字段


    • 语法：
      1. alter table 表名 drop column 字段名;
      复制代码
    • 例如：
      1. alter table tablename drop column ID;
      复制代码

    主键/索引/视图相关
    1. # 添加主键
    2. alter table tabname add primary key(col)
    3. # 删除主键
    4. alter table tabname drop primary key(col)
    5. # 创建索引
    6. create [unique] index idxname on tabname(col….)
    7. # 删除索引 （索引是不可更改的，想更改必须删除重新建。）
    8. drop index idxname
    9. # 创建视图
    10. create view 视图名 as select statement
    11. # 删除视图
    12. drop view viewname
    复制代码
    数据插入
    1. # 全字段添加-语法：
    2. insert into 表名 values(所有列的值);
    3. #例如：
    4. insert into test values(1,'zhangsan',20);

    6. # 自定义字段添加-语法：
    7. insert into 表名(列) values(对应的值);
    8. # 例如：
    9. insert into test(id,name) values(2,'lisi');

    11. # 将表1数据插入到表2-语法：
    12. insert into 表2 select * from 表1;
    13. # 例如：
    14. insert into test(name,role,createId,createdate) select name,role,id,now() from test_user;
    复制代码
    数据更新
    1. # 语法：
    2. update 表 set 列=新的值 [where 条件] --更新满足条件的记录
    3. # 例如：
    4. update test set name='zhangsan2' where name='zhangsan'
    复制代码
    数据删除
    1. # delete语法：
    2. delete from 表名 where 条件 --删除满足条件的记录
    3. # 例如
    4. delete from test where id = 1;

    6. delete from test -->删除所有 delete删除的时候，会记录日志 删除会很慢

    8. # truncate语法：
    9. truncate table 表名 --删除所有数据，不会影响表结构，不会记录日志，数据不能恢复，删除得很快
    复制代码
    字段拼接


    • CONCAT（char1，char2），在oracle中concat函数只能拼接两个值，这根mysql不一样
    • 返回两个字符串连接后的结果，两个参数char1，char2是要连接的两个字符串。
    • 等价操作：连接操作符“||”
    • 如果char1，char2任何一个为NULL，相当于连接了一个空格
    • 注意：建议多个字串连接时，用“||”更直观
    1. #字符串函数  CONCAT()函数，用来连接字符串
    2. SELECT CONCAT(ename,sal) FROM emp

    4. SELECT CONCAT( CONCAT(ename,','),sal) FROM emp

    6. SELECT ename||','||sal FROM emp
    复制代码
    merge into


    • 在Oracle中，常规的DML语句只能完成单一功能，，例如insert/delete/update只能三选一，而merge into语句可以同时对一张表进行更新/插入/删除。
    • merge into常用在数据同步的场景，它会选定一张基表作为数据源，然后与目标表的记录进行匹配，根据匹配的结果，可以对目标表同时进行更新/插入/删除操作。

    语法
    1. MERGE INTO target_table
    2. USING source_table ON (join_condition)
    3. WHEN MATCHED THEN UPDATE SET … [WHERE …] [DELETE … WHERE …]
    4. WHEN NOT MATCHED THEN INSERT (column_list) VALUES (value_list) [WHERE …]
    5. LOG ERRORS INTO … REJECT LIMIT [integer|UNLIMITED]
    复制代码

    • target_table 是要更新的表
    • source_table 是参考表
    • USING … ON () 是两个表的连接条件，用于判断记录是否匹配
    • WHEN MATCHED 对于满足匹配条件的记录进行的操作，可以更新或删除
    • WHEN NOT MATCHED 对于不满足匹配条件的记录，可以插入
    • LOG ERRORS INTO 可以将匹配错误的记录记录到日志表中

    案例


    素材
    1. create table src_table(
    2. id number(6),
    3. name varchar2(32),
    4. salary number(6),
    5. bonus number(6));

    7. insert into src_table values(1,'Vincent',1000,100);
    8. insert into src_table values(2,'Victor',2000,200);
    9. insert into src_table values(3,'Grace',3000,300);


    12. create table tgt_table(
    13. id number(6),
    14. name varchar2(32),
    15. age number(6),
    16. salary number(6),
    17. bonus number(6));

    19. insert into tgt_table values(1,'someone',1,0,0);
    20. insert into tgt_table values(3,'someone',3,0,0);
    21. insert into tgt_table values(4,'someone',4,0,0);
    22. commit;
    复制代码
    同时更新和插入
    1. merge into tgt_table t
    2. using src_table s on (t.id=s.id)
    3. when matched then update set t.name=s.name, t.salary=s.salary, t.age=10
    4. when not matched then insert values(s.id,s.name,10,s.salary,s.bonus+50);

    6. commit;

    8. 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子句进一步限制范围
    1. merge into tgt_table t
    2. using src_table s on (t.id=s.id)
    3. when matched then update set t.bonus=s.bonus where s.id>=2;

    5. Select * from tgt_table order by id;
    复制代码

    • 这里对匹配的记录bonus字段进行更新，同时进一步限制id>=2的记录才更新
    • Where 子句的条件可以通过源表或者目标表指定，这里是通过源表的s.id指定
    在update子句后，还可以跟上delete … where …子句，对匹配上的记录进行删除操作
    1. merge into tgt_table t
    2. using src_table s on (t.id=s.id)
    3. when matched then update set t.bonus=s.bonus+50 where s.id>=3 delete where id>=2;

    5. 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的数字将失败：
    1. Alter table tgt_table modify salary number(6) check(salary<=3000);
    复制代码
    首先调用dbms_errlog.create_error_log为tgt_table创建一张错误日志表，表名为errlog：
    1. exec dbms_errlog.create_error_log('tgt_table', 'errlog');
    复制代码

    • 第一个参数指定要创建错误日志的基表，第二个参数是错误日志表名
    然后在执行merge into 语句时，在最后跟上log errors into子句，如果语句执行过程中遇到错误，则会将错误记录到错误日志中，方便后期排查和修复，这里将tgt_table清空，尝试将src_table的3条数据同步进去，同时salary增加1000
    1. truncate table tgt_table;

    3. merge into tgt_table t
    4. using src_table s on (t.id=s.id)
    5. when not matched then insert values(s.id,s.name,10,s.salary+1000,s.bonus)
    6. log errors into errlog('something is wrong.');

    8. 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个以上的错误时才回滚语句：
    1. merge into tgt_table t
    2. using src_table s on (t.id=s.id)
    3. when not matched then insert values(s.id,s.name,10,s.salary+1000,s.bonus)
    4. log errors into errlog('Allow one error.') reject limit 1;

    6. 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条记录
    1. SELECT *
    2. FROM your_table
    3. WHERE ROWNUM <= 10;
    复制代码
    示例2：查询表中的第11到20条记录
    1. SELECT *
    2. FROM (
    3.     SELECT t.*, ROWNUM AS rnum
    4.     FROM (
    5.         SELECT *
    6.         FROM your_table
    7.         ORDER BY your_column
    8.     ) t
    9.     WHERE ROWNUM <= 20
    10. )
    11. WHERE rnum >= 11;
    复制代码
    示例3：查询表中的第21到30条记录，并按照指定的列进行排序
    1. SELECT *
    2. FROM (
    3.     SELECT t.*, ROWNUM AS rnum
    4.     FROM (
    5.         SELECT *
    6.         FROM your_table
    7.         ORDER BY your_column
    8.     ) t
    9.     WHERE ROWNUM <= 30
    10. )
    11. WHERE rnum >= 21;
    复制代码
    注意，在查询结果之前使用ROWNUM，否则可能会产生错误的结果。此外，使用子查询的方式可以在外层查询中使用rnum进行过滤，从而实现分页效果。

    分组查询/筛选

    在Oracle中，可以使用分组查询和筛选语法来对数据进行分组和筛选。
    基本的语法如下：
    1. SELECT column1, column2, ..., aggregate_function(column)
    2. FROM your_table
    3. WHERE conditions
    4. GROUP BY column1, column2, ...
    5. HAVING conditions;
    复制代码
    其中，column1, column2, …是要查询的列，aggregate_function是聚合函数（如SUM、COUNT、AVG等），your_table是要查询的表，conditions是查询条件，column1, column2, …是要进行分组的列。
    示例1：统计每个部门的员工数量，并筛选出员工数量大于5的部门
    1. SELECT department, COUNT(*) AS employee_count
    2. FROM your_table
    3. GROUP BY department
    4. HAVING COUNT(*) > 5;
    复制代码
    示例2：计算每个部门的平均工资，并筛选出平均工资大于1000的部门
    1. SELECT department, AVG(salary) AS average_salary
    2. FROM your_table
    3. GROUP BY department
    4. HAVING AVG(salary) > 1000;
    复制代码
    示例3：统计每个部门的最高工资，并筛选出最高工资大于5000的部门
    1. SELECT department, MAX(salary) AS highest_salary
    2. FROM your_table
    3. GROUP BY department
    4. HAVING MAX(salary) > 5000;
    复制代码
    在HAVING子句中可以使用聚合函数进行筛选，用来对分组后的结果再进行一次筛选。与WHERE子句不同的是，HAVING子句是在分组后进行筛选，可以使用聚合函数，而WHERE子句是在分组前进行筛选，不能使用聚合函数。
    注意，在SELECT子句中，除了分组的列和聚合函数外，还可以选择其他需要显示的列，如：
    1. SELECT department, job, COUNT(*) AS employee_count, AVG(salary) AS average_salary
    2. FROM your_table
    3. GROUP BY department, job
    4. HAVING COUNT(*) > 5;
    复制代码
    排序

    在Oracle中，可以使用ORDER BY子句对查询结果进行排序。ORDER BY子句的语法如下：
    1. SELECT column1, column2, ...
    2. FROM your_table
    3. WHERE conditions
    4. ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
    复制代码
    其中，column1, column2, …是要排序的列，ASC表示升序（默认），DESC表示降序。
    示例1：按照工资升序排序查询结果
    1. SELECT *
    2. FROM your_table
    3. ORDER BY salary ASC;
    复制代码
    示例2：按照部门升序、工资降序排序查询结果
    1. SELECT *
    2. FROM your_table
    3. ORDER BY department ASC, salary DESC;
    复制代码
    示例3：按照姓名升序、年龄降序排序查询结果
    1. SELECT *
    2. FROM your_table
    3. ORDER BY name ASC, age DESC;
    复制代码
    可以根据需要在ORDER BY子句中指定多个列，并对每个列设置排序方式。如果不指定排序方式，默认为升序。
    注意，ORDER BY子句应该在WHERE子句之后使用，用于对筛选后的结果进行排序。如果只有一个列需要排序，可以直接写上列名，如果有多个列需要排序，使用逗号分隔。

    连接查询

    在Oracle中，可以使用不同的连接查询语法来联结多个表。以下是Oracle中的一些常见连接查询语法及示例：

    1. 内连接（INNER JOIN）：
    1. SELECT column1, column2, ...
    2. FROM table1
    3. INNER JOIN table2 ON table1.column = table2.column;
    复制代码
    示例：
    1. SELECT employees.employee_id, employees.first_name, departments.department_name
    2. FROM employees
    3. INNER JOIN departments ON employees.department_id = departments.department_id;
    复制代码
    2.左连接（LEFT JOIN）：
    1. SELECT column1, column2, ...
    2. FROM table1
    3. LEFT JOIN table2 ON table1.column = table2.column;
    复制代码
    示例：
    1. SELECT employees.employee_id, employees.first_name, departments.department_name
    2. FROM employees
    3. LEFT JOIN departments ON employees.department_id = departments.department_id;
    复制代码
    3.右连接（RIGHT JOIN）：
    1. SELECT column1, column2, ...
    2. FROM table1
    3. RIGHT JOIN table2 ON table1.column = table2.column;
    复制代码
    示例：
    1. SELECT employees.employee_id, employees.first_name, departments.department_name
    2. FROM employees
    3. RIGHT JOIN departments ON employees.department_id = departments.department_id;
    复制代码
    4.全外连接（FULL OUTER JOIN）：
    1. SELECT column1, column2, ...
    2. FROM table1
    3. FULL OUTER JOIN table2 ON table1.column = table2.column;
    复制代码
    示例：
    1. SELECT employees.employee_id, employees.first_name, departments.department_name
    2. FROM employees
    3. FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
    复制代码
    5.交叉连接（CROSS JOIN）：
    1. SELECT column1, column2, ...
    2. FROM table1
    3. CROSS JOIN table2;
    复制代码
    示例：
    1. SELECT employees.first_name, departments.department_name
    2. FROM employees
    3. CROSS JOIN departments;
    复制代码
    注意：以上语法和示例仅供参考，实际使用时应根据具体的表和列名进行调整。连接查询可以通过指定JOIN条件，将多个表中的数据关联起来，从而获取更加丰富的查询结果。

    子查询

    在Oracle中，可以使用子查询来作为查询语句的一部分，以便在查询中使用子查询的结果。以下是Oracle中子查询的语法和示例：
    语法：
    1. SELECT column1, column2, ...
    2. FROM table1
    3. WHERE columnN IN (SELECT columnM FROM table2 WHERE condition);
    复制代码
    示例：

    • 使用子查询获取某个表中符合条件的数据：
    1. SELECT *
    2. FROM employees
    3. WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
    复制代码

    • 使用子查询获取某个表中的最大值或最小值：
    1. SELECT employee_id
    2. FROM employees
    3. WHERE salary = (SELECT MAX(salary) FROM employees);
    复制代码

    • 使用子查询作为存在性检查：
    1. SELECT employee_id, first_name, last_name
    2. FROM employees
    3. WHERE EXISTS (SELECT * FROM job_history WHERE job_history.employee_id = employees.employee_id);
    复制代码

    • 使用子查询作为计算列：
    1. SELECT employee_id, first_name, last_name, (SELECT MAX(salary) FROM employees) - salary AS salary_diff
    2. FROM employees;
    复制代码
    注意：以上语法和示例仅供参考，实际使用时应根据具体的表和列名进行调整。子查询可以嵌套在主查询中，以便根据子查询的结果来筛选、计算或检查数据。

    总结

    到此这篇关于Oracle数据库常用语句总结大全的文章就介绍到这了,更多相关Oracle常用语句大全内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

    来源：https://www.jb51.net/database/328102x93.htm
    免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

    最新评论

    新人须知

    常见问题

    维权申诉

    关注微博

    扫描微信

    关于合作

    通讯联系

    QQ Archiver 手机版 小黑屋 福建二哥 ( 闽ICP备2022004717号|闽公网安备35052402000345号 )

    Powered by Discuz! X3.5 © 2001-2023

    快速回复 返回顶部 返回列表