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

2017-10-22 本文已影响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