美文网首页Java技术分享
后端开发，那些你应该要知道的 oracle 基础

后端开发，那些你应该要知道的 oracle 基础

作者: 张少林同学 | 来源:发表于2017-10-22 22:12 被阅读46次
来自互联网.jpg

写在前面：

首先，标题是写给自己的。谈谈近况吧，小白入行时是学习 Android，几乎很少(基本没有)认认真真的研究过 sql，当初在学校的时候，也是一点课也不听，这些大家认为大学必修的数据库知识也是半知不解，当初学习 Android 的时候，由于比较懒，对于简单的数据库操作，都是使用 orm 框架简单操作，以致于后来因为 xxx 原因，现在在公司做的有点杂，基本上数据库操作是免不了的，做了一段时间后端才发现，在项目中 偷懒不写sql语句是不可能的，sql 是后端开发的灵魂。而每次 sql 不懂得都是面向谷歌查询，嗯，这次抽点时间把平时积累的笔记整理一下。当然了，sql 的水很深，这只是一篇简单的基础总结，今后会不定时将工作中遇到的 sql 使用场景及技巧通过博客的方式整理成文，记录小白的成长之路，flag 已立~

一、基本的表操作

建表

CREATE TABLE "IPMS"."I_CONSTRUCTION_SYSTEM" (
"CONSTRUCTION_SYSTEM_ID" VARCHAR2(255 BYTE) NOT NULL ,
"WIFI_NAME" VARCHAR2(255 BYTE) NULL ,
"MAC_ADDRESS" VARCHAR2(255 BYTE) NULL ,
"MARK_NAME" VARCHAR2(255 BYTE) NULL ,
"LONGITUDE" VARCHAR2(255 BYTE) NULL ,
"LATITUDE" VARCHAR2(255 BYTE) NULL ,
"CONTACT_PERSON" VARCHAR2(255 BYTE) NULL ,
"PHONE_NUMBER" VARCHAR2(255 BYTE) NULL ,
"ADDRESS" VARCHAR2(255 BYTE) NULL ,
"REPORT_TIME" VARCHAR2(255 BYTE) NULL,
PRIMARY KEY ("CONSTRUCTION_SYSTEM_ID")
)
NOCOMPRESS
;

COMMENT ON TABLE "IPMS"."I_CONSTRUCTION_SYSTEM" IS '智能施工系统app数据表';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."CONSTRUCTION_SYSTEM_ID" IS '主键id';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."WIFI_NAME" IS 'WIFI名称';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."MAC_ADDRESS" IS 'MAC地址';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."MARK_NAME" IS '站点名称';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."LONGITUDE" IS '经度';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."LATITUDE" IS '纬度';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."CONTACT_PERSON" IS '联系人';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."PHONE_NUMBER" IS '电话';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."ADDRESS" IS '地址';

COMMENT ON COLUMN "IPMS"."I_CONSTRUCTION_SYSTEM"."REPORT_TIME" IS '上报时间';

说明：以下为表约束条件
not null:定义列不能为空，null则可为空
PRIMARY KEY：主键约束,也可以使用:

constraint pk_I_CONSTRUCTION_SYSTEM_CONSTRUCTION_SYSTEM_ID primary key  CONSTRUCTION_SYSTEM_ID

UNIQUE:唯一性约束，每行中的列，值不能相同。

定义字段注释：comment on 列名 is xxx

创建序列

玩过 mysql 的肯定知道有个 AUTO_INCREMENT 属性，正常是用于主键或者 id 字段，每次插入数据的时候，自动增长，而 oracle 却没有此属性，而是使用序列实现的。

create sequence SEQ_I_CONSTRUCTION_SYSTEM
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
NOCYCLE
cache 20;

字段说明：
increment by：序列增量，默认为1，假如是正数，则自动递增，负数就递减。
start with:默认的起始值，递增时则为最小值，递减时则为最大值。
maxvalue:序列生成器可以生成的最大序列号，必须大于或等于start with
minvalue:序列生成器可以生成的最小序列号。
CYCLE:指定达到最大值序列值或者最小值时，是否继续生成序列号，默认NOCYCLE关闭该效果。
cache:指定在内存中可以预分配的序列号个数，默认20.

这样每次插入数据时，指定 id 主键值为序列值下一位 序列名.nextnvl即可。

获取序列值

  • 获取序列下一位
select SEQ_I_CONSTRUCTION_SYSTEM.nextval from dual;
  • 获取当前序列值
select SEQ_I_CONSTRUCTION_SYSTEM.currval from dual

注意：调用 currval之前必须先调用nextval,否则出错

删除序列

有时候想重设序列开始值为 1，直接更新序列时会报错的，可以直接干掉序列重建。

DROP SEQUENCE SEQ_I_CONSTRUCTION_SYSTEM;

修改表

  • 修改表名的方法
rename 旧表名 to  新表名。
  • 修改字段名
alter table [表名]  rename column 旧的字段名 to 新的字段名;
  • 修改字段属性
ALTER TABLE tableName modify(columnName 类型);

例子：

alter table I_CONSTRUCTION_SYSTEM modify(CONSTRUCTION_SYSTEM_ID NUMBER(38, 0));
alter table I_CONSTRUCTION_SYSTEM modify(MAC_ADDRESS VARCHAR2(32));
alter table I_CONSTRUCTION_SYSTEM modify(LONGITUDE VARCHAR2(32));
alter table I_CONSTRUCTION_SYSTEM modify(LATITUDE VARCHAR2(32));
alter table I_CONSTRUCTION_SYSTEM modify(CONTACT_PERSON VARCHAR2(32));
alter table I_CONSTRUCTION_SYSTEM modify(PHONE_NUMBER VARCHAR2(12));
alter table I_CONSTRUCTION_SYSTEM modify(REPORT_TIME DATE);
  • 添加新的字段
ALTER TABLE 表名 ADD (列名 NUMBER(18))//添加一列 
COMMENT ON COLUMN 表名.列名 IS '管理人员' //给列添加注释
  • 为已创建表添加主键约束
alter table 数据表 add constraint pk_数据表_列名 primary (列名)

pk 为 primary_key缩写，假如该列存在重复值，则报错。
select rowid from 数据表：查看重复的列id
delect from 数据表 where rowid = xxx: 删除重复数据即可解决。

删除表

  • 方法一：直接生效的，不能回滚，会直接删除表结构(危险)
drop table 表名
  • 方法二：直接生效的，不能回滚，不删除表的结构，只是针对表中的内容删除，会释放占用空间，而drop 和 delete不会
truncate table 表名
  • 方法三：不commit时是不生效的，不删除表的结构，只是针对表中的内容删除
delete from 表名（后面不跟where语句，则也删除表中所有的数据）

结论：使用drop 和 truncate相对delete是危险的，当使用drop 和 truncate 时不能回滚。delete相对安全，可以回滚，并且commit以后才会提交，并且不会删除表结构，也不会释放表所占用的空间

速度： drop>; truncate >; delete

  • 删除表中的一列数据
　alter table 表名 drop column 列名

dual表详解

  • dual 表是 oracle 中一张虚拟表，只有一行一列，数据类型为 varchar2(1),内部逻辑保证此表永远只有一条数据。
  • 可以用来调用系统内置函数
  • 可以执行查询操作，不可以执行删除，插入操作，否则会出现莫名的 xxx 错误。

例子：

  • 查看当前日期 时间
SELECT SYSDATE FROM dual;
  • 查看当前连接用户
SELECT USER FROM dual;
  • 查看当前日期 时间字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') AS report_time from dual;
  • 查看序列值
SELECT SEQ_I_DEVICE.NEXTVAL FROM DUAL;
  • 还可以当做计算器使用
select 1+2 from dual;

复制表

  • 复制表结构与数据
CREATE TABLE 新表 AS SELECT * FROM 旧表 WHERE 1 = 1;

其他

  • 查询表所有字段 (注意：表名必须与数据库中的表名完全一致,不能忽略大小写)
select * from user_tab_columns where Table_Name='表名';
  • 查询表字段注释
select * from user_col_comments where Table_Name='表名';

二、数据表查询(重要)

普通查询：

  • 查询表中所有数据
select * from 数据表
  • 指定条件过滤查询
select * from 数据表 where + 过滤条件
  • 查询部分字段
select column1,column2...  from 数据表 where + 过滤条件
  • 查询数据，去除重复，使用 distinct 修饰查询字段
select distinct column1,column... from 数据表
  • 使用 as 指定表名与字段名(as可省略)
select t.column1 as 指定名称,t.column2 as 指定名称 ... from 数据表 as t where + 过滤条件
  • 使用 in 操作符查询指定的多个条件值
select column1,column2... from 数据表 where column in(value1,value2...)
  • 指定某个字段范围过滤查询:between...and...
select * from 数据表 where column between A and B
  • 查询数据表并排序 order by
select column1,column2... from 数据表 order by column ASC|DESC

ASC:升序排序，DESC:降序排序 (默认为升序)

嵌套子查询

有时候我们不知道确定的查询条件，硬性指定查询条件可能会出现问题，这时候我们需要动态的指定条件，就可以使用嵌套子查询。

  • 单行子查询
    子查询嵌套在主查询的 where 子句中，单行比较符号（＝, >, <, >=, <=, <>）之后，用括号包含，只能返回一行数据
    例如：
select xxx,yyy,zzz from 数据表 where xxx = (select xxx from 另一张数据表 where + 过滤条件)
  • 多行子查询
    同样是嵌套在主查询的 where 子句中，但是必须在 集合比较操作符(in,not in)之后,用于匹配子查询返回的多行结果
    例如：
select xxx,yyy,zzz from 数据表 where xxx in (select xxx from 另一张数据表 where + 过滤条件)
  • 多列子查询
    当子查询结果为单行多列时，主查询条件可以用单行比较符连接，而子查询为多行多列时，主查询又处理多列时，必须使用集合比较符连接，并且主查询条件字段必须用括号包含，同时与子查询相同。
    例如：
select xxx,yyy,zzz from 数据表 where (xxx,yyy) in (select xxx,yyy from 另一张表 where + 过滤条件)
  • 嵌套视图子查询
    在主查询中的 from 子句中嵌套子查询，作为临时数据表结果，同时嵌套的视图子查询可以使用 order by 排序
select * from (select xxx,yyy,zzz from 另一张表 order by xxx) where + 过滤条件

多表关联查询

往往复杂的业务查询都是关联多张表的，通过每个表之间的字段关联查询出数据,其实表之间的关联就是类似中学时代学习的集合交集与并集。

  • INNER JOIN(内连接)：取两张表的交集数据,一般简写为 join
select a.column1,a.column2,b.column1.b.column2  from table_a a join table_b b on a.column1 = b.column1
  • left join(左关联):不管右表是否有匹配的字段，左表都返回数据
select a.column1,a.column2,b.column1.b.column2  from table_a a left join table_b b on a.column1 = b.column1
  • right join(右关联):不管左表是否有匹配的字段，右表都返回数据
select a.column1,a.column2,b.column1.b.column2  from table_a a right join table_b b on a.column1 = b.column1
  • full join：只要其中一个表存在匹配字段，则返回数据，相当于取并集。
select a.column1,a.column2,b.column1.b.column2  from table_a a full join table_b b on a.column1 = b.column1

三、更新

  • 简单的表字段更新
update table set column1 = xxx,column2 = yyy...where + 过滤条件
  • 嵌套子查询更新
update table_a set column1 = xxx,column2 = yyy...where column1 = (select column1 from table_b);

四、增加数据

  • 简单插入一行数据：
insert into table_a values(column1,column2,...)

其中字段数据类型需要与表对应。

五、常用函数

字符串函数：

  • LOWER(char):字母转换为小写
  • UPPER(char):字母转换为大写
  • LENGTH(char):返回字符串长度
  • SUBSTR(char, start, length):裁剪字符串，stat开始，length裁剪的长度
  • CONCAT(str1,str2):连接字符串

日期函数：

  • sysdate:返回系统日期时间
SELECT to_char(SYSDATE,'yyyy-MM-dd HH24:mi:ss') AS report_time FROM dual;
  • NEXT_DAY(day,char):返回指定日期后的第一个工作日char对应的日期
select NEXT_DAY(sysdate,'星期日') from dual
  • LAST_DAY(DAY):返回指定日期月份的最后一天的日期
select LAST_DAY(sysdate) from dual; --2017-10-31 18:19:58
  • MONTHS_BETWEEN(day1,day2):返回 day1 与 day2 相差的月份：
select MONTHS_BETWEEN(sysdate,SYSDATE) from dual; --0

转换函数：

  • to_char()函数:数字或日期转换为一个字符串
to_char( value, [ format_mask ], [ nls_language ] )

format_mask：可选，按指定格式转换为字符串
nls_language：可选，这是免入息审查贷款计划的语言，用于转换为字符串的值。
数字例子：

to_char(1210.73, '9999.9') would return '1210.7' 
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'

日期例子：

to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09' 
to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003' 
to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003' 
to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003' 
to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003' 
to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
to_char(datetime,'yyyy-MM-dd hh24:mi:ss')//转换为指定格式日期
  • to_date()函数：字符串转换日期
to_date(日期字符串格式,'yyyy-MM-dd HH24:mi:ss')
SELECT to_char(SYSDATE,'yyyy-MM-dd HH24:mi:ss') AS report_time FROM dual; -- 2017-10-22 13:58:14
  • to_number()函数：将字符型数据转换为数值型数据
select to_number('1994') from dual;  -- 1994

集合函数

  • AVG:计算一列值得平均值
  • count:统计列值个数
select count(*) from 数据表 --查询数据表总条数

select count(*) from 数据表 where 列名 = xxx --查询指定列数据值的条数

select count(列名) from 数据表 --统计某个字段的条数

select count(distint+列名) from 数据表 --统计某个字段的条数(去除重复)
  • max:求列最大值
  • min:求列最小值
  • sum:一列值得总和
select SUM(列名) from 数据表

其中 列名存储的数据类型必须是数值类型，当对某列数据进行求和时，如果该列存在null值u，则sum函数会忽略该值

其他常用函数

  • decode(expression , search , result [, search , result]… [, default])函数
    第一个字段为表达式，最后紧跟判断与结果字段，可以有无数个判断与结果字段集合。
if 表达式求值=xxx,则返回yyy值

也可以适用于行转列的需求。
例子：

select DECODE(20, 10, 5, 200, 10, 20, 30) from dual; -- 30
  • NVL(expr1,expr2)函数
    如果 expr1=null;则返回 xpr2，否则返回 xpr1,常用于 null 值判断。
    例子：
select NVL(20, 30) from dual; -- 20
select NVL(NULL, 30) from dual; -- 30

Linux 远程操作数据库

sqlplus 用户名/密码+@orcl

更多原创文章会在公众号第一时间推送,欢迎扫码关注 张少林同学

张少林同学.jpg

相关文章

网友评论

    本文标题：后端开发，那些你应该要知道的 oracle 基础

    本文链接：https://www.haomeiwen.com/subject/spvnuxtx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    Java技术分享

    关于我们|服务条款|联系我们|后端开发，那些你应该要知道的 oracle 基础|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！