sql结构化查询语言

本文延续上一篇内容oracle基础-1

本文用例中用到的表

-- emp表--员工信息表
create table EMP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)
  • 排序查询 降序 select * from 表名 order by 字段名 desc 升序 select * from 表名 order by 字段名 asc

例1: 查询所有员工信息,按照工资从高到低排序

  select * from emp order by sal desc;

例2: 查询20部门中的员工信息,按工资从高到低,如果工资相等,再按奖金从低到高排序

  select * from emp where deptno = 10 order by sal desc,comm asc ;
  • 结果去重 distinct 未使用distinct
  select job from emp;

结果

使用 distinct

  select distinct job from emp;

结果

数据库索引

索引是一种额数的查询表,可以加快数据的检索

类似于书的目录,不需要查询整本数内容就可以找到想要的数据

缺点:减慢了数据的录入速度,同时增加了数据库的尺寸大小

oracle 常用的索引

  • normal
  • unique 唯一索引
  • 适合建立在值不重复的字段上
  • 主键默认就会建立一个唯一索引
  • bitmap
  • 适合建立在只有某几个值的字段 (如性别字段)

什么样的字段适合建立索引

  1. 经常被查询的字段
  2. 不为空且字段值不重复
  3. 字段的值不经常修改

创建索引的命令

create 索引类型 index 索引名 on 表名 (字段名);

例:

create unique index UK_USER_IDCARD on T_USER (IDCARD)

序列 Sequences

是一种特殊的数据对象,一般用来自增

-- Create sequence 
create sequence seq_sno  --seq_sno是序列名
minvalue 1          --最小值:下次循环开始的值
maxvalue 9999999    --最大值
start with 1        --初始值:第一次开始的值
increment by 1        --增量
cache 20            --缓存:
cycle                --循环:不写这一条,默认不循环
order;                --排序

使用

insert into 表名(id) values(seq_sno.nextval);

伪列rownum和rowid

rowid和rownum都是序列,但含义完全不同

rowid是物理地址,用于定位oracle中具体数据的物理存储位置,rownum则是sql的输出结果排序。通俗的讲:rowid是相对不变的,rownum会变化,尤其是使用order by的时候

rownum

表示查询某条记录在整个结果集中的位置,同一条记录查询条件不同对应的rownum是不同的而rowid是不会变的

  • 在oracle中,要按特定条件查询前n条记录,用rownum就可以了。select * from emp where rownum<3;
  • 这里要注意的是不能对rownum用''>'',也就是说如果用select * from emp where rownum>3;查询的结果是失败的。
  • 原因:对应rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,以此内推,这个伪字段可以用于限制查询返回的总行数,
    因为rownum都是从1开始,但是1以上的自然数在rownum做判断时认为都是false条件,所有无法查到rownum=n(n>1的自然数)。

rowid

同ROWNUM伪列不同的是,它是物理存在的,ROWID是一种数据类型,它使用基于64为编码的18个字符来唯一标识一条记录物理位置的一个ID,类似于Java中一个对象的哈希码,都是为了唯一标识对应对象的物理位置,需要注意的是ROWID虽然可以在表中进行查询,但是其值并未存储在表中,所以不支持增删改操作

ROWID由18个字符组成,组成结构如下:

数据对象编号文件编号块编号行编号
OOOOOOFFFBBBBBBRRR

由于ROWID用来唯一标识表中数据的唯一性,所以可以利用这个特性去除重复

oracle 常见的系统函数

  • sysdate 获取系统时间
  select sysdate from dual;

结果:

  • length 获取字符长度
  select length('abc中文') from dual;

结果:

  • lengthb 获取字节数
  select lengthb('abc中文') from dual;

结果:

  • upper 把字符转换为大写
  • lower 把字符转换为小写
  • substr 截取字符串 substr(str,offset,len) 从offset位置开始截取冷儿字符
  select substr('abcdefg',1,4) from dual; --返回abcd
  • instr 查找子字符串 instr(str,str2,pos,[times]) 从str的pos位置开始查找str2出现的位置,如果没找到,返回0,times是找第几次出现的位置
  • replace 子字符串替换 replace(str1,str2,str3) 把str1中的所有str2替换成str3
  • trim 去除前后空格
  select trim('  abcd  ') from dual;  --返回abcd
  • to_number 把字符串转换为数字类型
  select to_number('12312312') from dual; --返回number类型 12312312
  • to_char 把某些特殊数据转换为字符串
  select to_char(sysdate,'yyyy--MM-dd') from dual;
  -- 指定了日期显示按yyyy--MM-dd显示
  select to_char(sysdate,'DDD') from dual;
  -- 返回指定日期是那一年的第几天
  select to_char(sysdate,'DD') from dual;
  -- 返回指定日期是那一月的第几天
  select to_char(sysdate,'D') from dual;
  -- 返回指定日期是那一周的第几天
  • to_date 把字符串日期数据 转换为日期类型
  select to_date('2019-07-10 15:08:09','yyyy-MM-dd HH24:mi:ss') from dual;

结果:

  -- 如果要添加的数据是 类似于birthday这种数据,则需要用到这个
  insert into t_user(id,birthday) values(11,to_date('2019-07-10 15:08:09','yyyy-MM-dd HH24:mi:ss'));
  • 日期相关
  • 获取当前日期前4年的时间 select sysdate,sysdate-interval '4' year from dual;
  • 获取当前日期前4月的时间 select sysdate,sysdate-interval '4' month from dual;
  • 获取当前日期前4天的时间 select sysdate,sysdate-interval '4' day from dual;
  • 获取当前日期前4小时的时间 select sysdate,sysdate-interval '4' hour from dual;
  • 获取当前日期前4分钟的时间 select sysdate,sysdate-interval '4' minute from dual;
  • 获取两个日期相差的月份 select months_between(sysdate,to_date('2019-02-10','yyyy-MM-dd')) from dual;
  • 在一个日期的基础上加上或减去相应的月份,返回新的日期 select add_months(sysdate,5) from dual;
  • 返回指定日期的当月的最后一天 select last_day(sysdate) from dual;
  • nvl(a,b) 如果a=null ,返回b,否则返回a
  • nvl2(a,b,c) 如果a=null ,返回c,否则返回b
  • decode(a,值1,结果1,值2,结果2,值3,结果3,默认值) 根据a等于那个值,返回相应结果,否则返回默认值
  select degree, decode (trunc(degree/10)*10,60,'及格',70,'良好',80,'优秀',90,'完美','继续努力') from t_score;

结果:

类似实现方法

case when语句

  select sal, 
         case
           when sal>3000 then '高收入'
           when sal>2000 then '中收入'
             else '低收入'
               end
         收入水平,ename,job from emp;

结果:

或者用于 更新数据操作,例: 根据工作不同 加不同的工资

  update emp set sal=
  case job
    when 'CLERK' then nvl(sal,0)+200
      when 'SALESMAN' then nvl(sal,0)+300
        when 'PRESIDENT' then nvl(sal,0)+400
          when 'MANAGER' then nvl(sal,0)+500
            when 'ANALYST' then nvl(sal,0)+600
              end;

聚合函数

  • max() 最大值
  • min() 最小值
  • avg() 平均值
  • count() 求总记录数(不统计为null的记录总数)
  • sum() 求某个列的值加起来的总和

分组查询 group by

一般和聚合函数结合使用

例1:求出每个部门的最高工资 最低工资 工资总和 平均工资 部门人数

 --求出每个部门的最高工资 最低工资 工资总和 平均工资 部门人数
  select 
  deptno 部门编号, 
  max(sal) 最高工资,
  min(sal) 最低工资,
  sum(sal) 工资总和,
  avg(sal) 平均工资,
  count(*) 部门人数 
  from emp group by deptno;

结果:

例2: 查询员工数大于3的部门

  --查询员工数大于等于2的部门 
select 
 deptno 部门编号, 
 max(sal) 最高工资,
 min(sal) 最低工资,
 sum(sal) 工资总和,
 avg(sal) 平均工资,
 count(*) 部门人数 
from emp group by deptno
having count(empno)>3; 
--group 后用 having 关键字

结果:

例3: 行转列

将图1 经过查询后 转化为图2所示结果

图1

图2

```sh
select A ,
sum(decode(B,'胜',1)) 胜,
sum(decode(B,'负',1)) 负
from t_sf group by A;

```


遥望漉雪千山都过尽,隔海隔山你的背影。