oracle 常用SQL
一、SQL语句
（一）基础篇
1.1.1 where 子句
1.1.1.1 where后面可以跟多个条件表达式，表达式之间用and或or连接
--查询除去emp表前5条的员工信息（即rownum>5） --方法一 select e2.* from (select e1.*,rownum r from (select * from emp) e1) e2 where e2.r>5; --方法二(推荐使用) select e2.* from (select e1.*,rownum r from emp e1) e2 where e2.r>5; --查询emp表前5条的员工信息 --方法一：不显示rownum列 select * from emp where rownum<6; --方法二：显示rownum列 select e1.*, rownum r from emp e1 where rownum<6;
1.1.1.2 跟between and
--查询员工表第5-10条数据 --方法一 select e2.* from (select e1.*,rownum r from emp e1) e2 where e2.r between 5 and 10; --方法二 select e2.* from (select e1.*,rownum r from emp e1) e2 where e2.r >=5 and e2.r <=10; --方法三 select e2.* from (select e1.*, rownum r from emp e1 where rownum <=10) e2 where e2.r >=5;
1.1.1.3 跟in(多值运算符)，in后面是一个集合，其含义相当于任意一个值，等价于any
--查询部门编号为10,20,50的员工信息 --方法一 select * from emp where deptno in(10,20,50); --方法二 select * from emp where deptno=10 or deptno=20 or deptno=50; --方法三 select * from emp where deptno=any(10,20,50);
1.1.1.4 跟like,在字符串的比较时，用like和通配符进行模糊查找
--查询部门员工姓名以T结尾的员工信息 select * from emp where ename like '%T';
1.1.1.5 跟is null运算符，用于测试某个的值是否为空,只能使用is null
--查询comm为空的员工信息 select * from emp where comm is null;
1.1.1.6 跟order by子句，用于对查询结果进行分组
--查询员工薪水大于1500的员工信息，升序排列 select * from emp where sal>1500 order by sal asc;--降序（desc）,默认是升序排列
1.1.2日期类操作抽取到了文章ORACLE-SQL（三）
1.1.1.7 跟group by
1.1.3 sql语句中的分支
1.1.3.1 分支表达式 case when(then)
--查询员工姓名及薪水，薪水为800的+50；薪水为950的+5 --方法一 select ename,case when sal=800 then sal+50 when sal=950 then sal+5 else sal end sal from emp; --方法二 select ename,(case sal when 800 then sal+50 when 950 then sal+5 else sal end) as 员工薪水 from emp; 说明：对列起别名 end后面不能再跟列名
1.1.3.2 分支函数 decode(value,if1,then1,if2,then2,else) 是对case when 的简化
--使用分支函数 select ename,decode(sal,800,sal+50,950,sal+5,sal) as 员工薪水 from emp;
1.1.4 group by 子句
语法顺序：select from where group by order by
--查询员工所在的部门编号，不重复 select deptno from emp group by deptno;
1.1.5 having 子句
语法顺序：select from where group by having order by
--查询员工的部门编号不为空的且不重复升序排列 select deptno 部门编号 from emp group by deptno having deptno is not null order by deptno asc;
1.1.6 非关联子查询
1.1.7 分页查询
select e.* from (select emp.*,rownum rowno from emp where rownum<=15) e where e.rowno>=11;
1.1.8 操作字段
1.1.8.1 添加主键、外键、唯一约束
1.1.8.1.1 添加主键约束
方式一：
cityId number primary key not null;
方式二：
constraint PK_cityId primary key (cityId);
1.1.8.1.2 添加外键约束
方式一：（在create table时执行）
constraint FK_provinceId1 foreign key (provinceId) references province1 (provinceId);
方式二：
alter table city1 add constraint FK_provinceId1 foreign key (provinceId) references province1 (provinceId);
注意：
添加外键约束，大前提：外键（表中的列）必须具有唯一性（只能是主键或唯一键），否则报错。
UpdateTime--2017年5月12日10:46:12
1.1.8.1.3 添加唯一约束
constraint UK_PATIENT_COURSEINFO unique (ORG_CODE, PATIENT_ID, COURSE_NO);
1.1.8.2 外键约束
--user_constraints 所有的约束都存放在这里 --constraint_type='R' 表示外键约束 --禁用外键约束 alter table student1 disable constraint FK_CID; --启用外键约束 alter table student1 enable constraint FK_CID; --删除外键约束 alter table student1 drop constraint FK_CID; --查询数据库所有的外键 select table_name,constraint_name from user_constraints where constraint_type='R'; --启用所有的外键约束 select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'; --禁用所有的外键约束 select 'alter table'||table_name||'disable constraint'||constraint_name||';' from user_constraints where constraint_type='R'; --删除所有的外键约束 select 'alter table'||table_name||'drop constraint'||constraint_name||';' from user_constraints where constraint_type='R';
UpdateTime--2016年10月31日14:41:18
1.1.8.3 新增字段（列）
1.1.8.3.1 新增字段并指定数据类型及大小
语法：ALTER TABLE 表名 ADD 字段名称 类型[指定大小或长度];
举例：
-- Add/modify columns alter table OUTPATIENT_CONS_ASSESSMENTINFO add label VARCHAR2(255);
1.1.8.3.2 新增字段、指定数据类型及大小并添加默认值
语法：ALTER TABLE 表名 ADD 字段名 类型[大小或长度] DEFAULT 默认值;
举例：
--默认值为0 alter table TSORGDEPT add fstatus number(1) default 0 not null; --默认时间为系统当前时间 alter table TSORGDEPT add fauddate date default sysdate;
1.1.8.4 删除字段
语法：ALTER TABLE 表名 DROP COLUMN 名称;
举例：
alter table OUTPATIENT_CONSULT_INFO drop column swap_type;
UpdateTime--2017年6月13日18:33:46
1.1.8.5 修改字段
1.1.8.5.1 修改字段数据类型及大小
语法：ALTER TABLE 表名 MODIFY 名称 类型[指定大小或长度];
举例：
-- Add/modify columns alter table PATIENT_INFO modify sex VARCHAR2(3);
1.1.8.5.2 字段重命名
语法：ALTER TABLE 表名 RENAME COLUMN 旧名称 TO 新名称;
举例：
-- Add/modify columns alter table OUTPATIENT_INFO rename column reserve_field5 to AA;
1.1.8.5.3 为字段添加默认值
语法：ALTER TABLE 表名 MODIFY 列名 DEFAULT 默认值
举例：
-- Add/modify columns alter table OUTPATIENT_CONSULT_INFO modify apply_date default sysdate;
1.1.8.6 为字段添加注释
语法：COMMENT ON COLUMN 表名.字段名称 IS '注释';
举例：
-- Add comments to the columns comment on column OUTPATIENT_CONS_ASSESSMENTINFO.label is '标签';
1.1.9 操作表
1.1.9.1 给表添加注释
--创建城市表 create table city1( cityId number primary key not null, provinceId number not null, cityName varchar2(20) not null, constraint FK_provinceId1 foreign key (provinceId) references province1 (provinceId) );
--添加城市表注释 comment on table city1 is '城市表'; comment on column city1.cityId is '城市编号'; comment on column city1.provinceId is '省份编号'; comment on column city1.cityName is '城市名称';
1.1.9.2 清空表数据
方式一：（推荐使用）
truncate table 表名;
UpdateTime--2017年7月31日16:57:40
特点：a.删除速度快；b.执行删除操作不需要收到提交事务，即执行该操作无法回滚。
方式二：
delete from 表名;
特点：执行删除操作可以回滚事务
情景2：将一张表中的数据插入到另一张表中
语法：
INSERT INTO 已存在的表 SELECT * FROM 表名
说明：从一个表复制数据，然后把数据插入到一个已存在的表中；目标表中任何已存在的行都不会受影响。
1.1.10 INSERT INTO
1.1.11 表连接
1.1.11.1 左连接
说明：以左表为主表，左表的内容将会全部展示出来；右表内容不存在的将显示为空
--查询员工的员工编号，员工姓名及所在部门名称 select emp.empno,emp.ename,dept.dname from emp left join dept on emp.deptno=dept.deptno; --查询所有的员工信息及所在部门名称 select emp.*,dept.dname from emp left join dept on emp.deptno=dept.deptno; --查询两表所有的信息（以左表为主） select * from emp left join dept on emp.deptno=dept.deptno;
3张表关联
表A---------------------------------关联第一张表B-----------------------关联第二张表c
语法：
select * from 表名A left join 表B on A.columnX=B.columnM and A.columnY=B.columnN left join 表c on 表A=表c的id
案例：
见SQL语句提升篇文章
1.1.11.2 分页查询+左连接
--查询第11-15条所有的员工信息及所在部门名称 select e.* from (select emp.*,rownum rowno,dept.dname from emp left join dept on emp.deptno=dept.deptno where rownum<=15) e where e.rowno>=11;
1.1.11.3 连接
--左表的内容全部展示出来 select emp.*,rownum rowno,dept.dname from emp left join dept on emp.deptno=dept.deptno where rownum<=12 order by empno; --只展示公共部分(与dept表的deptno有关联的数据) --方法一 select emp.*,rownum,dept.dname from emp,dept where rownum<=12 and emp.deptno=dept.deptno order by empno; --方法二：内链接 select emp.*,rownum,dept.dname from emp join dept on emp.deptno=dept.deptno where rownum<=12 order by empno; --交叉查询 select emp.*,rownum, dept.dname,dept.loc from emp cross join dept where rownum<=12 order by empno; --自然连接 select * from emp natural inner join dept where ename='tom';
UpdateTime--2016年12月9日16:16:36
1.1.11.4 (+)的用法
说明：oracle中的(+)是一种特殊的用法，(+)表示外连接，并且总是放在非主表的一方。
举例：
左外连接：A表(左表)为主表 select A.a,B.a from A LEFT JOIN B ON A.b=B.b; 等价于 select A.a,B.a from A,B where A.b = B.b(+); 右外连接：B表(右表)为主表 select A.a,B.a from A RIGHT JOIN B ON A.b=B.b; 等价于 select A.a,B.a from A,B where A.b (+) = B.b;
UpdateTime--2016年10月16日16:36:40
1.1.12 union(过滤掉重复的值)与union all(不去重)
说明：将查询结果进行拼接
1.1.13 distinct（去重）
UpdateTime--2017年7月10日17:14:34
用法：
a.distinct + 单个字段，表示对该字段进行去重处理；
b.distinct + column1，column2，。。。，表示的是使用n个字段进行联合去重，即查出来的是这n个字段的值相加结果不一致的数据；
而不是：对第一个字段做去重处理后，再将其他字段查询出来。
--查询员工表中不同的部门 select distinct deptno from emp;
1.1.14 oracle数据库查询语句中中将汉字转换成数字进行排序操作
--查询学生分数表中不同的学期，并按升序排列 SELECT DISTINCT TERM FROM STUDENT_MARK ORDER BY translate(TERM,'一二三四五六七八九','123456789');
1.1.15 查询某一行多列数据的最大值和最小值
--查询水果第一季销量最大值和最小值 SELECT NAME, GREATEST(T.NUMS1, T.NUMS2, T.NUMS3, T.NUMS4) 最大销量, LEAST(T.NUMS1, T.NUMS2, T.NUMS3, T.NUMS4) 最小销量 FROM DEMO_PIVOT T WHERE SEASON = '第一季'
1.1.16 批量修改
1.1.17 对查询的数据进行编辑
方式一
SELECT t.*,ROWID FROM torganization t WHERE ROWNUM < 11
方式二
SELECT t.* FROM torganization t WHERE ROWNUM < 11 FOR UPDAT
1.1.18 查询某张表共有多少个字段
语法：
SELECT COUNT(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='表名'
UpdateTime--2016年12月30日08:55:31
--查询BASE_AC_MENU这张表共有多少个字段 --方法一 SELECT COUNT(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='BASE_AC_MENU' --方法二 SELECT COUNT('COLUMN_NAM') FROM( SELECT T.COLUMN_NAME FROM USER_COL_COMMENTS T WHERE T.TABLE_NAME=UPPER('BASE_AC_MENU')) --查询BASE_AC_MENU这张表字段明细 --方法一 SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='BASE_AC_MENU' --方法二 SELECT T.COLUMN_NAME FROM USER_COL_COMMENTS T WHERE T.TABLE_NAME=UPPER('BASE_AC_MENU')
1.1.19 一个汉字占多少个字节？
UTF-8字符集,一个汉字占三个字节,gbk字符集,一个汉字占两个字节,比如varchar(10)类型的字段,UTF-8的汉字,只能存3个,gbk字符集的汉字却能存5个。
CreateTime--2016年10月9日17:45:19
1.1.20 汉字排序
参考链接：http://blog.163.com/ah_zhao/blog/static/3852961201101214037617/
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序，系统的默认排序方式为拼音排序
举例：
表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。
//按照笔划排序 select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M'); //按照部首排序 select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M'); //按照拼音排序，此为系统的默认排序方式 select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
UpdateTime--2017年7月13日15:54:28
1.1.21 查重计数
--查询科室表对重复的科室名称字段共有多少条数据 SELECT COUNT(1) FROM TDEPTORG WHERE FDEPTNAME IN (SELECT FDEPTNAME FROM TDEPTORG GROUP BY FDEPTNAME HAVING COUNT(1) > 1)
UpdateTime--2017年10月31日09:35:25
1.1.23 如何判断一个字段值的长度
--查询手机号码（去重+长度=11） select DISTINCT t.mf16 from HC_DATASET_FIL t where length(t.mf16)=11
1.1.24 表重命名
rename 旧表名 to 新表名；
本文来自博客园，作者：Marydon，转载请注明原文链接：https://www.cnblogs.com/Marydon20170307/p/6927987.html
