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 新表名;

写在最后

  哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!

 相关推荐:

posted @ 2017-06-01 11:24  Marydon  阅读(701)  评论(0编辑  收藏  举报