Day54、约束（主键约束、唯一约束、检查约束、非空约束）、数据库对象（序列、索引、视图、分页）

复习：

多表查询

1.oracle

 内连接：

 select 字段 from 表1,表2 where 连接条件;

 外连接： (+)

2.sql99

 内连接：

  select ...from 表1 inner join 表2 on 连接条件;

  外连接：

  select ...from 表1 left outer join 表2 on 连接条件;

  select ...from 表1 right outer join 表2 on 连接条件;

  select ...from 表1 full outer join 表2 on 连接条件;

 

组函数和分组：

1.组函数：

 count

 max

 min

 sum

 avg

 都可以使用distinct  对NULL值忽略

2.分组

 group by

 having

 

 语法顺序：

 select

  from

    where

     group by

      having

        order by

 执行顺序：

 from

 where

   group by

     having

      select

        order by

 

子查询：

1.where之后

2.having之后

3.from之后

 

 

练习：

2、列出所有在 'Sales'部门工作的员工的信息

   分别用两表连接和子查询实现

 select e.id,e.first_name

    from s_emp e,s_dept d

       where e.dept_id = d.id and d.name='Sales';

 select e.id,e.first_name

    from s_emp e inner join s_dept d

      on e.dept_id = d.id

        where d.name='Sales';

 select id,first_name from s_emp where dept_id in

   (select id from s_dept where name='Sales');

3、显示部门人数比编号为31的部门的人数多的部门名称和人数

  select e.dept_id,d.name,count(e.id)

     from s_emp e,s_dept d

       where e.dept_id = d.id

         group by dept_id,d.name

           having count(e.id)>(

    select count(id) from s_emp where dept_id=31

   );

-------------------------------------------------------

1、约束(constraint)

 1.1 约束的作用

   对数据库表中的值添加限制

 1.2 约束的类型

   主键约束：primarykey

     不允许重复

     不允许为null

     一个表中只能由一个主键

   唯一约束：unique

     不允许重复

     可以为null  可以有多个null

     一个表中可以有多个唯一字段

   检查约束：check(表达式)

   非空约束：not null

   外键约束：foreignkey    references

 1.3约束的实现方式

   列级约束：直接在字段后面添加约束

   表级约束：表中的所有字段定义完后，逗号隔开添加的约束

 1.4 约束的实施

  1.4.1 主键的列级约束

    1)系统自动命名约束名

    create table testcons_zsm_00(

      id number primary key,

      name varchar2(20)

    );

    insert  into testcons_zsm_00values(1,'Tom');

     commit;

    /* 主键约束的字段值不允许重复 */

    insert  into testcons_zsm_00values(1,'Jerry');

    *

ERROR at line 1:

ORA-00001: unique constraint(OPENLAB.SYS_C0031039) violated

    /* 主键约束的字段值不允许为null */

    insert  into testcons_zsm_00values(null,'Jerry');

                                         *

ERROR at line 1:

ORA-01400: cannot insert NULL into("OPENLAB"."TESTCONS_ZSM_00"."ID")

    /* 一个表中只能有一个主键 */

    create table testcons_zsm_00(

      id number primary key,

      name varchar2(20) primary key

     );

                         *

ERROR at line 3:

ORA-02260: table can have only one primarykey

 

    2）为约束命名

   constraint 表名_字段_约束类型约束类型...

   create table testcons_zsm_00(

      id number constraint cons_id_pk_zsm_00 primary key,

      name varchar2(20)

    );

   insert into testcons_zsm_00 values(1,'Tom');

   commit;

   insert into testcons_zsm_00 values(1,'Tom');

    *

ERROR at line 1:

ORA-00001: unique constraint(OPENLAB.CONS_ID_PK_ZSM_00) violated

 

  1.4.2 唯一约束和非空约束的列级实现

    drop table testcons_zsm_00;

    create table testcons_zsm_00(

      id number primary key,

      userid number unique,

      name varchar2(20) not null

    );

    insert into testcons_zsm_00 values(1,1,'Test1');

    commit;

    /* 唯一约束的字段值不允许重复 */

     insert into testcons_zsm_00values(2,1,'Test1');

    *

ERROR at line 1:

ORA-00001: unique constraint(OPENLAB.SYS_C0031095) violated

    /* 唯一约束的字段值可以为null */

    insert into testcons_zsm_00 values(2,null,'Test1');

    /* 唯一约束的字段值可以有多个null值 */

    insert into testcons_zsm_00 values(3,null,'Test2');

    commit;

    /* 非空约束的字段不允许为null */

    insert into testcons_zsm_00 values(4,4,null);

                                            *

ERROR at line 1:

ORA-01400: cannot insert NULL into ("OPENLAB"."TESTCONS_ZSM_00"."NAME")

    /* 一个表中可以有多个唯一约束 */

    drop table testcons_zsm_00;

    create table testcons_zsm_00(

      id number unique,

      userid number unique,

      name varchar2(20) not null

    );

    /* 为唯一约束和非空约束命名 */

     createtable testcons_zsm_00(

      id number primary key,

      userid number constraint cons_userid_uk_zsm_00 unique,

      name varchar2(20) constraint cons_name_nn_zsm_00 not null

    );

  1.4.3 检查约束的列级实现

   drop table testcons_zsm_00;

   create table testcons_zsm_00(

      id number primary key,

      name varchar2(20) not null,

      salary number(7,2)

         constraint cons_sal_ck_zsm_00 check(salary>=2000)

   );

   insert into testcons_zsm_00 values(1,'test',8500);

   commit;

   /* 违反检查约束 */

   insert into testcons_zsm_00 values(2,'test',1500);

    *

ERROR at line 1:

ORA-02290: check constraint(OPENLAB.CONS_SAL_CK_ZSM_00) violated

 

  1.4.4 主键的表级约束

    联合主键：多个字段联合起来做主键

   drop table testcons_zsm_00;

    把id和fname做联合主键

   create table testcons_zsm_00(

     id number,

     fname varchar2(20),

     sname varchar2(20),

     salary number,

     constraint cons_id_fname_pk_zsm_00 primary key(id,fname)

   );

   insert into testcons_zsm_00 values(1,'aa','cc',2000);

   insert into testcons_zsm_00 values(1,'bb','cc',2000);

   insert into testcons_zsm_00 values(2,'bb','cc',2000);

   insert into testcons_zsm_00 values(2,'aa','cc',2000);

   /* 两个字段不能同时重复 */

   insert into testcons_zsm_00 values(2,'aa','cc',2000);

    *

ERROR at line 1:

ORA-00001: unique constraint(OPENLAB.CONS_ID_FNAME_PK_ZSM_00) violated

 

   1.4.5 主键、唯一和检查约束的表级实现

   drop table testcons_zsm_00;

   create table testcons_zsm_00(

     id number,

     userid number,

     name varchar2(20),

     salary number(7,2),

     constraint testcons_id_pk_zsm_00 primary key(id),

     constraint testcons_userid_uk_zsm_00 unique(userid),

     constraint testcons_sal_ck_zsm_00 check(salary>2000)

   );

 

  1.4.6 非空约束在数据库的层面上，没有联合非空的需求，

        所以没有表级的实现方式

 

 1.5 外键约束

   1）概念：一个表中的字段的值，受另一张表的某个字段的限制

           取值只能是另一张表中对应字段的值或者null

     子表(从表):外键所在的表

     父表(主表):被引用的必须是唯一性的字段(主键或唯一)

   2）创建表时，先建主表，再建从表

     （除非先不添加外键约束，建完表后通过更改表添加外键约束）

     --主表

     create table parent_zsm_00(

       id number primary key,

       name varchar2(20) not null

     );

     create table child_zsm_00(

       id number primary key,

       name varchar2(20) not null,

       fid number constraint child_fid_fk_zsm_00

          references parent_zsm_00(id)

     );

   3)写入数据时，一般是先向主表写入数据，

     否则从表中外键的值设置为null

    insert into parent_zsm_00 values(1,'test1');

    insert into parent_zsm_00 values(2,'test2');

    insert into parent_zsm_00 values(3,'test3');

    commit;

    insert into child_zsm_00 values(1,'t',1);

     insertinto child_zsm_00 values(2,'t',1);

    insert into child_zsm_00 values(3,'t',3);

    insert into child_zsm_00 values(4,'t',1);

    insert into child_zsm_00 values(5,'t',null);

    commit;

    /* 违反了完整性约束主表中没有对应的值 */

    insert into child_zsm_00 values(6,'t',4);

*

ERROR at line 1:

ORA-02291: integrity constraint(OPENLAB.CHILD_FID_FK_ZSM_00) violated

- parent key not found

  

   4）删除或修改数据时，外键的作用

     不能造成从表中的数据孤立

     /* 违反了完整性约束主表中的数据被从表引用（不能删除） */

     delete from parent_zsm_00 where id=3;

     *

ERROR at line 1:

ORA-02292: integrity constraint(OPENLAB.CHILD_FID_FK_ZSM_00) violated

- child record found

 

   5）删除表时，先删除从表，再删主表

     drop table parent_zsm_00;

                 *

     ERROR at line 1:

     ORA-02449: unique/primary keys in table

          referenced by foreign keys

 

     /* 级联删除-- 表 */

     drop table 表名 cascade constraints;

 

     drop table parent_zsm_00 cascade constraints;

     drop table child_zsm_00 cascade constraints;

 

    6）级联删除和级联置空

      on delete cascade: 级联删除

        

      create table parent_zsm_00(

       id number primary key,

       name varchar2(20) not null

      );

      create table child_zsm_00(

       id number primary key,

       name varchar2(20) not null,

       fid number,

       constraint child_fid_fk_zsm_00 foreign key(fid)

            references parent_zsm_00(id)

       on delete cascade

      );

      /* 向主表中插入测试数据 */

      insert into parent_zsm_00 values(1,'test1');

      insert into parent_zsm_00 values(2,'test2');

      insert into parent_zsm_00 values(3,'test3');

      commit;

      /* 向从表中插入测试数据 */

      insert into child_zsm_00 values(1,'c1',1);

      insert into child_zsm_00 values(2,'c2',2);

      insert into child_zsm_00 values(3,'c3',3);

      insert into child_zsm_00 values(4,'c4',1);

      insert into child_zsm_00 values(5,'c5',2);

      insert into child_zsm_00 values(6,'c6',3);

      insert into child_zsm_00 values(7,'c7',1);

      insert into child_zsm_00 values(8,'c8',null);

      commit;

      /* 查看两表中数据 */

      select * from parent_zsm_00;

      select * from child_zsm_00;

 

      delete from parent_zsm_00 where id=3;

      commit;

 

 

      on delete set null:级联置空

 

 

2.其它的数据库对象和分页

 2.1 数据库对象

   2.1.1 序列（产生主键的值）

   sequence

   1)创建序列

    create sequence 序列名;

   

    create sequence seq_zsm_00;

   2)使用序列

    /* 创建测试表 */

    create table testseq_zsm_00(

      id number primary key,

      name varchar2(20) not null

    );

     序列的两个属性：

     currval：当前值

    nextval：下一个值

     第一次使用序列必须使用nextval   序列名.nextval     

 

    insert into testseq_zsm_00

       values(seq_zsm_00.nextval,'test'||seq_zsm_00.currval);

    

    /  -- 执行上一条sql语句

    3）删除序列

    drop sequence 序列名;

    drop sequence seq_zsm_00;

 

 

    2.1.2 索引

     index

     全表扫描

     

     1）作用：提高查询效率

     

     用时间和空间换取时间

       创建索引会消耗大量的时间和空间

 

     2）对表中的唯一性字段自动创建索引

 

     /* 创建测试表 */

     create table test_index(

        id number primary key,

        name varchar2(20)

     );

     /* 创建一个序列 */

     create sequence test_index_seq;

     /* 创建存储过程循环写入数据 */

     create or replace procedure testindex

     as

     begin

       for i in 1..1000000 loop

         insert into test_index

               values(test_index_seq.nextval,'test'

                       ||test_index_seq.currval);

       end loop;

       commit;

     end;

     /

     /* 执行存储过程 */

     begin

       testindex;

     end;

 

     

     /* 测试 */

     select id,name from s_index where id=1000000;

     select id,name from s_index where name='test1000000';

     

     set timing on;-- 打开显示执行时间的功能

 

     3）创建索引

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

      create table test_index_zsm_00(

        id number primary key,

        name varchar2(20)

      );     

      create index testindex_idx_zsm_00

            on test_index_zsm_00(name);

 

   2.1.3.视图

    view

    

     创建和删除视图是需要权限的

    1）视图就是一条select语句，是逻辑上的虚拟表

       可以从一个表或多个表或视图中创建，

       是对基表(被引用的表或视图)数据的引用

    2）视图的作用

       可以简化查询

       保护数据(权限)

    3）创建视图

       create [or replace] view 视图名

       as

         select 语句;

 

       /* 使用select语句创建一张表复制s_emp */

       create table emp_zsm_00

           as select id,first_name,salary from s_emp;

 

       /* 创建视图 */

       create or replace view vm_emp_zsm_00

       as

         select * from emp_zsm_00;

       /* 操作视图 */

       select * from vm_emp_zsm_00;

       select * from emp_zsm_00;

 

       insert into vm_emp_zsm_00 values(100,'test',2500);

 

        update emp_zsm_00 set salary = salary+1000;

    

  2.2 分页

  sql server: top

  mysql:limit

  oracle:rownum(伪列)

  

  select rownum,first_name,salary from s_emp;

  select rownum,id,name from s_dept;

 

   /*每页10条，显示emp_zsm_00的第一页数据 */

  select rownum,first_name,salary from emp_zsm_00

      where rownum<=10;

   /*每页10条，显示emp_zsm_00的第二页数据 */

  select rownum,first_name,salary from emp_zsm_00

      where rownum>10 and rownum<=20;-- no rows selected

   使用rownum进行判断时，只要有一条数据不满足条件就停止判断

  

   /*使用子查询 */

  select * from

    (select rownum rid,first_name,salary from emp_zsm_00

      where rownum<=20)

   where rid>10;

 

   /*按照工资降序排序，显示第二页 */

   先排序？还是先分页？

  select * from

   (

    select rownum rid,id,first_name,salary from

     (

       select id,first_name,salary from emp_zsm_00

          order by salary desc

      )

     where rownum<=20

    )

   where rid>10;

        

   每页n条，查询第m页

  select * from

   (

    select rownum rid,id,first_name,salary from

     (

       select id,first_name,salary from emp_zsm_00

          order by salary desc

      )

     where rownum<=n*m

    )

   where rid>n*(m-1);

 

----------------------------------------------------

1、select

2、ddl

  create  drop  alter

3、dml

  insert  delete  update

4、tcl

  commit  rollback  savepoint

 

----------------------------------------------------

练习：

1.创建表

 a.user_info:用户信息表

   id：       数字类型 主键  用序列产生

   nickname：字符串  非空

   age：      数字  检查约束 范围

   star_id：  数字  外键

   blood_id：数字  外键

 b.star:    星座表

   id：       数字 主键

   name：     字符串  非空

 c.blood:   血型表

   id         数字  主键

   name       字符串 非空

2.插入数据测试约束

3.多表查询 列出用户的的信息