1 唯一约束:
  1)修改表的时候设置唯一约束
    alter table 表名
    add constraint 约束名
    unique(列名1,列名2,列名3...)
    create table worker8(
      id number(4),
      name varchar2(50),
      password varchar2(50),
      age number(3)
    )
    alter table worker8
    add constraint uq_name_pwd_w8
    unique(name,password)
    案例:创建一张book2表,id number(4),bname varchar2(50),author varchar2(50),pub varchar2(50),numinput number(10) 修改book2表的时候,设置主键约束pk_id_bname_b2修饰 id和bname,设计唯一约束uq_author_pub_b2修饰author和pub
      create table book2(
        id number(4),
        bname varchar2(50),
        author varchar2(50),
        pub varchar2(50),
        numinput number(10)
      )
      alter table book2
      add constraint pk_id_bname_b2
      primary key(id,bname)
      alter table book2
      add constraint uq_author_pub_b2
      unique(author,pub)
  2) 删除唯一约束:
    alter table 表名 drop constraint 约束名
    案例:删除worker7中唯一约束UQ_NAME_W7
      alter table worker7 drop constraint UQ_NAME_W7
    案例:删除worker8中唯一约束UQ_NAME_PWD_W8
      alter table worker8 drop constraint uq_name_pwd_w8
  3) 主键约束和唯一约束有什么区别?
    a)一张表中只能定义一个主键约束,但可以定义多个唯一约束。
    b)对于指定为主键所修饰的一个列或者多个列的组合值,其中任何一个列都不能出现空值,而对于唯一约束修饰的列,该列的列值可以为空。

 

2 having子句:对分组以后的数据再次进行过滤,经常跟聚合函数结合使用。
  1)格式:
    select 列名/聚合函数
    from 表名
    where 条件 :对整张表的数据进行过滤
    group by 列名
    having 子句 :对分组以后的数据再次进行过滤
    order by 列名/别名/聚合函数 asc/desc
  2)执行的顺序:首先执行where条件,对表中所有的数据进行过滤,然后执行group by,根据某一个列把过滤后数据分成几组,对每一个组的数据使用聚合函数,之后使用having子句对分组以后的数据再次进行过滤,最后执行order by进行排序。
  3)having子句经常跟聚合函数结合使用。
    案例:查询emp表中,每个部门的平均工资高于2000的部门的编号,部门人数,平均工资
      select deptno,count(*),avg(sal)
      from emp
      group by deptno
      having avg(sal) > 2000
    案例:查询emp表中员工的姓名中不是以'K'开头的信息,每个部门最低工资高于1000的部门编号,工资总和,平均工资以及最低工资,最后根据平均工资进行升序排列
      select deptno,sum(sal),avg(sal),min(sal)
      from emp
      where ename not like 'K%'
      group by deptno
      having min(sal)>1000
      order by avg(sal) asc
    案例:查询emp表中部门编号是10,30号部门的员工,每个职业的最高工资低于5000的职业名称,人数,平均工资,最高工资最后根据人数进行升序排列,如果人数一致,根据最高工资进行降序排列。
      select job,count(*),avg(sal),max(sal)
      from emp
      where deptno in(10,30)
      group by job
      having max(sal)<5000
      order by count(*) asc,max(sal) desc

 

3 检查约束:用于限定某列的列值必须要满足某一个特定的条件,避免用户输入非法数据。
  1)创建表的时候设置检查约束:
    create table customer3(
      cid number(4) primary key,
      cname varchar2(50) unique,
      sex char(3) check(sex in('男','女')),
      age number(3),
      email varchar2(50)
    )
    insert into customer3
    values(1,'戚继光','男',33,'guang@126.com')
    insert into customer3
    values(2,'李清照','女',24,'zhao@163.com')
    --反例
    insert into customer3
    values(3,'东方不败','中',32,'bai@126.com')
  2)修改表的时候设置检查约束
    alter table 表名
    add constraint 约束名 check(约束条件)
    案例:设置customer3表中age列上检查约束,要求age必须在1~120之间
      alter table customer3
      add constraint chk_age_cus3
      check(age between 1 and 120)
      insert into customer3
      values(4,'苏东坡','男',42,'po@126.com')
      insert into customer3
      values(5,'张三丰','男',120,'feng@163.com')
      insert into customer3
      values(6,'张君宝','男',160,'jun@163.com')
    案例:修改customer3表的时候,要求email列要包含@
      alter table customer3
      add constraint chk_email_cus3
      check(email like '%@%')
      --反例:
      insert into customer3
      values(7,'任我行','男',35,'xing126.com')
  3)删除检查约束:
    alter table 表名 drop constraint 约束名
    案例:删除customer3中age和email列上的检查约束
      alter table customer3 drop constraint chk_age_cus3
      alter table customer3 drop constraint chk_email_cus3

 

4 常见日期处理函数:date
  1) 常用的日期格式:
    yyyy-mm-dd : 年月日
    y: 年份 m:月份 d:几号
    eg: 1999-12-15
      yyyy-mm-dd hh24:mi:ss 年月日 时分秒
      h: 小时 mi:分钟 s:秒
    eg: 2001-11-15 16:17:19
  2) Oracle默认的日期格式:
    dd-Mon月-yy
    eg: 2010-12-16 16-12月-10
  3) sysdate: 当前的系统时间
    select sysdate from dual
    sysdate:以天为单位
    案例:查询昨天,今天,明天
      select sysdate-1,sysdate,sysdate+1 from dual
  4) to_char(日期数据,'日期格式'):把日期类型数据(date)按照我们指定的格式转换为char类型字符进行显示。
    案例:查询当前的系统时间,按照yyyy-mm-dd格式显示
      select sysdate,to_char(sysdate,'yyyy-mm-dd') from dual
    案例:查询当前的系统时间,按照yyyy-mm-dd hh24:mi:ss格式来显示
      select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
      from dual
    案例:查询emp表中员工的编号,姓名,职位,入职时间,要求入职时间使用yyyy-mm-dd hh24:mi:ss格式显示
      select empno,ename,job,hiredate,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')
      from emp

  5) 使用to_char()函数获得日期的某一个部分
    select sysdate,to_char(sysdate,'mm') from dual
    案例:查询emp表中12月份入职的员工的编号,姓名,职位,入职时间
      select empno,ename,job,hiredate from emp
      where to_char(hiredate,'mm') = '12'

  6) to_date函数:把满足日期格式char类型数据转换为对应格式date类型的数据,经常用于插入操作。
    to_date('日期字符串','日期格式')
    create table worker9(
      id number(4) primary key,
      name varchar2(50),
      hiredate date
    )
    insert into worker9
    values(1,'李世民',
      to_date('2012-12-21 12:19:21',
      'yyyy-mm-dd hh24:mi:ss'))
    select id,name,hiredate,
    to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')
    from worker9
    往worker9中插入3条数据
      id name hiredate
      2 刘备 2001-11-12 21:22:22
      3 关羽 2012-12-10 10:15:21
      4 张飞 2018-10-16 13:16:29
        insert into worker9
        values(2,'刘备',to_date('2001-11-12 21:22:22',
          'YYYY-MM-DD HH24:MI:SS'))
        insert into worker9
        values(3,'关羽',to_date('2012-12-10 10:15:21',
          'yyyy-mm-dd hh24:mi:ss'))
        insert into worker9
        values(4,'张飞',to_date('2018-10-16 13:16:29',
          'yyyy-mm-dd hh24:mi:ss'))

  7) 使用默认的日期格式插入date
    insert into worker9
    values(5,'曹操','12-12月-12')
    缺点:不能插入时分秒
    往worker9中插入2条数据
    id name hiredate
    6 夏侯惇 2016-10-15
    7 典韦 2017-11-11
    insert into worker9
    values(6,'夏侯惇','15-10月-16')
    insert into worker9
    values(7,'典韦','11-11月-17')

  8)months_between(d1,d2):求出日期d1和d2之间间隔了多少个月
    案例:查询emp表中员工的姓名,职位,工资,入职时间以及工作了多少个月
    select ename,job,sal,hiredate,
    months_between(sysdate,hiredate)
    from emp

  9)add_months(d1,数字):在日期d1之后数字个月
    select sysdate,add_months(sysdate,8) from dual

 

5 嵌套查询(子查询):在一条查询语句内部又包含了一条查询语句
  案例:查询emp表中工资高于平均工资的员工的编号,姓名,职位,工资
  1)求出emp表中平均工资
    select avg(sal) from emp --2077
  2)高于平均工资的员工信息
    select empno,ename,job,sal from emp
    where sal > (
      select avg(sal) from emp
    )
  案例:查询emp表中工资高于30号部门最高工资的所有员工的编号,姓名,职位,工资,以及入职时间
    1)查询30号部门最高工资
      select max(sal) from emp
      where deptno = 30
    2)高于30号部门最高工资的员工信息
      select empno,ename,job,sal,hiredate
      from emp
      where sal > (
        select max(sal) from emp where deptno = 30
      )
  案例:查询KING所在部门的编号,名称,地址
  分析:
    KING ---》deptno ---》dept
    1)查询KING所在部门的编号
      select deptno from emp
      where ename = 'KING'
    2)根据编号查询该部门下的信息
      select * from dept
      where deptno = (
        select deptno from emp
        where ename = 'KING'
      )
  案例:查询部门名称是ACCOUNTING下,所有员工的信息
    1)查询ACCOUNTING部门的编号
      select deptno from dept
      where dname = 'ACCOUNTING'
    2)根据部门编号查询该部门下员工的信息
      select * from emp
      where deptno = (
        select deptno from dept
        where dname = 'ACCOUNTING'
      )
  案例:查询跟JONES在同一个部门下的所有员工的信息
    1)根据JONES查询该员工所在部门的编号
        select deptno from emp
        where ename = 'JONES'
    2)根据部门编号查询该部门下所有员工的信息
      select * from emp
      where deptno = (
      select deptno from emp
      where ename = 'JONES'
      )
  案例:查询跟JONES在同一个部门下的所有员工的信息,不包含JONES
    select * from emp
    where deptno = (
      select deptno from emp
      where ename = 'JONES'
    ) and ename <> 'JONES'