AI 助理
备案控制台
开发者社区 数据库 文章 正文

四、oracle基本sql语句和函数详解

2017-11-12 1388
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

一、oracle常用数据类型

 

  

一、  数据定义语言(ddl)

数据定义语言ddl(data definition language)用于改变数据库结构，包括创建、更改和删除数据库对象。

用于操纵表结构的数据定义语言命令有：

create table

alter table

truncate table

drop table

eg、

--创建tb_stu表数据结构

create table tb_stu(

   id number,

   name varchar2(20)

);

 

--修改tb_stu表数据结构，新增一列

alter table tb_stu add pwd varchar2(6);

--修改字段

alter table tb_stu modify pwd varchar2(20);

--重命名表: rename table_name1 to table_name2;

RENAME student TO tb_student;

--重命名列：alter table table_name rename column col_oldname to colnewname;

ALTER TABLE student RENAME COLUMN pwd TO pwd1;

--删除字段

alter table tb_stu drop column pwd;

 

 

 

--删除tb_stu表数据

truncate table tb_stu;

 

--删除tb_stu表

drop table tb_stu;

二、  数据操作语言(dml)

数据操纵语言dml(data manipulation language)用于检索、插入和修改数据，dml利用insert、select、update 及 delete 等语句来操作数据库对象所包含的数据。

(1)、利用现有的表创建表

语法：create table <new_table_name> as

         select column_names from <old_table_name>;

eg、

1)、create table tb_dept as select * from dept;

2)、create table tb_dept as select a.deptno, a.dname from dept a;

3)、create table tb_dept as select * from dept a where a.deptno=10;

 

(2)、选择无重复的行，使用distinct关键字

eg、select distinct a.dname from tb_dept a;

 

(3)、使用列别名

select a.deptno 部门编号, a.dname "部门 名称" from tb_dept a;

--中间有空格，用""

 

(4)、插入来自其他表中的记录

语法：insert into <table_name> [(cloumn_list)]

         select column_names from <other_table_name>;

eg、insert into student2 select * from student;

 

三、  事务控制语言(tcl)

放到“事务”讲解

四、  数据控制语言(dcl)

放到“用户和权限”讲解

五、  sql操作符

略

六、  oracle函数

1、字符函数

initcap(char)函数：首字母大写

eg、select initcap('hello') from dual;

输出结果：Hello

 

lower(char)函数：字母全部转换为小写

eg、select lower('heLLo') from dual;

输出结果：hello

 

upper(char)函数：字母全部转换为大写

eg、select upper('hello') from dual;

输出结果：HELLO

 

ltrim(char,set)函数：去掉左边指定的字符

eg、select ltrim('xyzadmin','xyz') from dual;

输出结果：admin

 

rtrim(char,set)函数：去掉右边指定的字符

eg、select rtrim('xyzadmin','admin') from dual;

输出结果：xyz

 

translate(char, from, to)函数：返回将from中的每个字符替换为to中相应字符以后的string

eg、1)、select translate('jack','j','b') from dual; --将j替换为b

输出结果：back

2)、select translate('123abc','2dc','4e') from dual;

输出结果：143ab

解析：2替换为4，

d因为字符串里没有，所以不作替换，

c由于没有对应的替换字符，所以字符串里的c会被删除

 

replace(char, searchstring, [rep string]) 函数：替换

eg、select replace('jack and jue' ,'j', 'bl') from dual;

输出结果：black and blue

 

instr(char, m, n)函数：返回截取的字符串在源字符串中的位置，没有返回0

eg、select instr ('worldwide', 'd') from dual;

输出结果：5

instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

 

substr(char, m, n)函数：截取字符串

eg、select substr('abcdefg', 3, 2) from dual;

输出结果：cd

 

concat(expr1, expr2)函数：连接字符串

eg、select concat('Hello',' world') from dual;

输出结果：Hello world

 

chr(number)函数：给出整数,返回对应的字符;

eg、select chr(54740), chr(65) from dual;

输出结果：赵 A

 

lpad和rpad函数：粘贴字符

lpad在列的左边粘贴字符

rpad在列的右边粘贴字符

eg、SELECT lpad('林计钦', 10, '*') FROM dual;

输出结果：****林计钦

注意：不够字符则用*来填满

 

trim函数：去除字符串左右两边的空字符串；如果不指定,默认为空格符。

 

length(char)函数：返回字符串的长度

eg、select length('abcdef') from dual;

输出结果：6

2、日期时间函数

add_months函数：增加或减去月份

eg、

select to_char(add_months(to_date('2011-11-30','yyyy-mm-dd'),2),'yyyy-mm-dd') from dual;--前进

输出结果：2012-01-31

select to_char(add_months(to_date('2011-11-30','yyyy-mm-dd'),-2),'yyyy-mm-dd') from dual;--后退

输出结果：2011-9-30

 

months_between(date2,date1)函数：给出date2-date1的月份

eg、select months_between('19-12月-1999','19-3月-1999') from dual;

输出结果：9

 

last_day函数：返回日期的最后一天

eg、select to_char(last_day(sysdate), 'yyyy-mm-dd hh24:mi:ss') from dual;

输出结果：2011-11-30 23:27:20

 

round和trunc函数：按照指定的精度进行四舍五入

eg、select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

输出结果： 56  -55  55  -55

 

next_day(date,'day')函数：给出日期date和星期几计算下一个星期的日期

eg、select next_day('30-11月-2011','星期三') next_day from dual;

输出结果：07-12月-11

 

extract(datetime)函数：获取时间函数

eg、

输出结果：

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --获取年

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --获取月

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --获取日

select to_char(sysdate,'hh24') from dual; --获取小时

select to_char(sysdate, 'mi') from dual;--获取分钟

select to_char(sysdate, 'ss') from dual;--获取秒

 

select extract(year from systimestamp) year 

      ,extract(month from systimestamp) month 

      ,extract(day from systimestamp) day 

      ,extract(minute from systimestamp) minute 

      ,extract(second from systimestamp) second 

      ,extract(timezone_hour from systimestamp) th 

      ,extract(timezone_minute from systimestamp) tm 

      ,extract(timezone_region from systimestamp) tr 

      ,extract(timezone_abbr from systimestamp) ta 

from dual;

3、数字函数

abs函数：绝对值

eg、select abs(-15) from dual;

输出结果：15

round(m, n)四舍五入函数：

eg、select round(45.926, 2) from dual;

输出结果：45.93

select round(45.926, -1) from dual;

输出结果：50

select round(245.926, -2) from dual;

输出结果：200

select round(275.926, -2) from dual;

输出结果：300

 

trunc(m, n)截取函数

一、日期

trunc函数为指定元素而截取的日期值。

其具体的语法格式如下：

TRUNC（date[,fmt]）

其中：date 一个日期值

      fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

如果当日日期是：2011-3-18

select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18

select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.

select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天

select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日

select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天

select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41

select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确

 

二、数字

trunc（number,num_digits）

number 需要截尾取整的数字。

num_digits 用于指定取整精度的数字。num_digits 的默认值为 0。

trunc()函数截取时不进行四舍五入

 

select trunc(123.458) from dual --123

select trunc(123.458,0) from dual --123

select trunc(123.458,1) from dual --123.4

select trunc(123.458,-1) from dual --120

select trunc(123.458,-4) from dual --0

select trunc(123.458,4) from dual --123.458

select trunc(123) from dual --123

select trunc(123,1) from dual --123

select trunc(123,-1) from dual --120

 

mod(m,n)求余函数

eg、select mod(5,2) from dual;

输出结果：1

 

ceil(n)函数：取大于等于数值n的最小整数

eg、select ceil(44.778) from dual;

输出结果：45

 

floor(n)函数：取小于等于数值n的最大整数

eg、select ceil(44.778) from dual;

输出结果：44     

4、转换函数

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

select to_date('2011-12-01 10:33:56', 'yyyy-mm-dd hh24:mi:ss') from dual;

select to_number('1000') from dual;

5、混合函数

nvl(string1, replace_with)功能：如果string1为null，则nvl函数返回replace_with的值，否则返回string1的值。 　　

注意事项：string1和replace_with必须为同一数据类型，除非显示的使用to_char函数。

 

nvl2(e1, e2, e3)的功能为：如果e1为null，则函数返回e3，否则返回e2。

 

nullif(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(null)，否则返回第一个值。

eg、select nullif('a', 'a') from dual;

输出结果：返回空，无值

select nullif('a', '1') from dual;

输出结果：a

 

6、分组函数

avg(n)、min(n)、max(n)、sun(n)、count(n)

八、oracle多表查询

 

多表联合查询

通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。

                

两个表连接有四种连接方式：

* 相等连接

* 不等连接(看作单表查询)

* 外连接(左连接、右连接；左连接即左表全部显示，右表只显示匹配的信息，右连接反之。)

* 自连接(自关联)

        

1．相等连接

通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中    

显示雇员名称和所在部门的编号和名称。

执行以下查询：

SELECT a.ename, b.id, b.dname

FROM employee a,dept b  

WHERE a.id=b.id

说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。

以上训练中，不在两个表中同时出现的列，前面的表名前缀可以省略。所以以上例子可以简化为如下的表示：

SELECT *

FROM emp e, dept d

WHERE e.deptno=d.deptno;

   

2．外连接

在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。

为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这里用如下的例子予以说明。

     

使用外连显示不满足相等条件的记录。

显示雇员名称和所在部门的编号和名称。

执行以下查询：

左连接方法一(推荐使用，简洁)：

--+号(附表)对面的是主表，可以理解为主表全部显示，+号这边如果没有匹配就显示空值

--不管dept是否存在，emp都会显示(emp当主表)

SELECT * FROM emp e, dept d WHERE d.deptno(+)=e.deptno; --左连接

SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno(+); --右连接

--不管emp是否存在，dept都会显示(dept当主表)

SELECT * FROM emp e, dept d WHERE e.deptno(+)=d.deptno; --左连接

SELECT * FROM emp e, dept d WHERE d.deptno=e.deptno(+); --右连接

 

左连接方法二：

--不管dept是否存在，employee都会显示

SELECT * FROM emp e LEFT JOIN dept d  ON d.deptno=e.deptno;

SELECT * FROM dept d RIGHT JOIN emp e ON d.deptno=e.deptno;

   

3、自连接(一般用在树形权限结构中)

自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。

SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理  

FROM employee worker, employee manager 

WHERE worker.mgr = manager.empno;

       

------------

执行结果为：

1.SMITH 的经理是 FORD 

2.ALLEN 的经理是 BLAKE 

3.WARD 的经理是 BLAKE 

 

  

注：在操作多表联合查询时，若出现以下情况，将形成笛卡尔积

– 联接条件被省略

– 联接条件无效

– 第一个表中的所有行被联接到第二个表中的所有行上

为了避免笛卡尔积，请始终包括有效的联接条件

    

何为笛卡尔积？

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。

 

九、集合连接

十、扩展

1、cmd控制台登录oracle数据库：sqlplus scott/oracle@orcl

 

2、获取当前时间

--> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

输出结果值：2011-11-30 19:40:55

 

获取当前时间(小数点精确到后面6位,6是可变的)

-->select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') from dual;

输出结果值：2011-11-30 19:45:35.791000

 

3、查看表结构：desc dept;

 

4、修改日期格式：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';(注意：只在当前会话中生效)

 

5、两种插入空值方式

方法一：insert into tb_stu values(1, null, to_date('20111130', 'yyyymmdd'));

方法二：insert into tb_stu(id, birthday) values(1, to_date('20111130', 'yyyymmdd'));

 

6、清屏：clear screen;

 

7、oracle中两个单引号表示一个单引号

eg、update tb_stu set pwd='1''2' where id=1;

 

8、oracle匹配

eg、select * from tb_stu where pwd like 'A%' --匹配多个

select * from tb_stu where pwd like 'A_' --匹配一个

 

9、drop table tb_dept purge; --永久删除

   drop table tb_dept; --把表放入回收站，并没有彻底的删除表

  

10、select to_char(33, 'L99.99') from dual; --获取本地货币符号，输出结果为：￥33.00

    select to_char(33, '$99.99') from dual; --获取美元

  

11、oracle中为空的两种表达方式

select '' from dual;

select null from dual;

  

12、查询当前数据库中的所有表名

select * from tab;

转：http://www.cnblogs.com/linjiqin/archive/2012/03/05/2379964.html

文章可以转载，必须以链接形式标明出处。


本文转自 张冲andy 博客园博客，原文链接：  http://www.cnblogs.com/andy6/p/5843778.html  ，如需转载请自行联系原作者
文章标签：
SQL
关系型数据库
数据库
Oracle
关键词：
Oracle数据库一键上云函数
SQL函数
Oracle数据库一键上云sql
Oracle数据库一键上云SQL语句
SQL Oracle
技术小美
目录
相关文章
进步`于辰
|
1月前
|
SQL Oracle 关系型数据库
[Oracle]面试官：你举例几个内置函数，并且说说如何使用内置函数作正则匹配
本文介绍了多种SQL内置函数，包括单行函数、非空判断函数、日期函数和正则表达式相关函数。每种函数都有详细的参数说明和使用示例，帮助读者更好地理解和应用这些函数。文章强调了字符串操作、数值处理、日期计算和正则表达式的使用方法，并提供了丰富的示例代码。作者建议读者通过自测来巩固学习成果。
进步`于辰
20 1
[Oracle]面试官：你举例几个内置函数，并且说说如何使用内置函数作正则匹配
云深知何处
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中，发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句，首先分析了 where 条件中各列的选择性，并创建了不同类型的索引，包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划，最终确定了使用复合索引（包含函数表达式）能够显著降低查询成本，提高执行效率。
云深知何处
33 3
1100237741946300
|
2月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中，SQL函数是处理和分析数据的强大工具
1100237741946300
53 5
1100237741946300
|
2月前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域，Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
1100237741946300
303 6
1100237741946300
|
2月前
|
SQL 数据库 索引
SQL中COUNT函数结合条件使用的技巧与方法
在SQL查询中，COUNT函数是一个非常常用的聚合函数，用于计算表中满足特定条件的记录数
1100237741946300
573 5
hju6meadphitw
|
2月前
|
SQL 存储 Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代，数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一，广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
hju6meadphitw
70 3
何雨晨
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
64 1
dlwlrma-晴子
|
2月前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
dlwlrma-晴子
51 0
t-25772328213-6-45193
|
3月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数（三）
可以用C（或者与C兼容，比如C++）语言编写用户自定义函数（User-defined functions）。这些函数被编译到动态可加载目标文件（也称为共享库）中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性，二者的实际编码约定本质上是相同的（因此，标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码）
t-25772328213-6-45193
163 1
推荐码发放
|
SQL 存储 数据库
SQL Server函数与存储过程 计算时间
SQL Server函数与存储过程 计算时间 一、通过一个开始时间、结束时间计算出一个工作日天数（不包含工作日与节假日）； 　　1、函数 --创建函数，参数 @bengrq 开始时间，@endrq 结束时间 create function [dbo].
推荐码发放
1789 0

热门文章

最新文章

  • 1
    PostgreSQL SQL HINT的使用
  • 2
    MS SQL 锁与事务
  • 3
    超有用的sql查询语句。项目、面试都有用
  • 4
    PL/SQL块结构和组成元素
  • 5
    ADO.NET——Command(执行SQL) &amp; DataReader(读取数据库)
  • 6
    （三）mybatisPlus自定义Sql语句
  • 7
    tempdb对SQL Server性能的影响 <转>
  • 8
    MySQL入门实战3：SQL问题诊断与调优
  • 9
    找到SQL Server数据库历史增长信息
  • 10
    SQL SERVER2005建Link Server
  • 1
    实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
    278
  • 2
    实时计算 Flink版产品使用合集之可以通过配置Oracle数据库的schema注册表来监测表结构的变化吗
    55
  • 3
    Oracle 部署及基础使用，字节跳动资深面试官亲述
    49
  • 4
    Oracle WebLogic Server远程代码执行漏洞 CVE-2020-14750 已亲自复现_cve-2020-14750漏洞复现
    170
  • 5
    Oracle dataguard 容灾技术实战（笔记），教你一种更清晰的Linux运维架构
    117
  • 6
    实时计算 Flink版产品使用合集之在读取Oracle归档日志时出现日志数量大幅增加的情况如何解决
    111
  • 7
    实时计算 Flink版产品使用合集之是否支持 Oracle 分区表
    52
  • 8
    Oracle效率分析，Github标星25K+超火的前端实战项目
    48
  • 9
    实时计算 Flink版产品使用合集之oracle cdc 抽取新增一张表 可以从savepoint恢复吗
    65
  • 10
    Oracle Linux: How To Disable NUMA At OS Level (Doc ID 2193586.1)
    72

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
  • SQLServer数据库的创建

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月