constraint、index、view(day04)

序列  sequence (除了表外,这个最重要)

回顾:
1.sql99中的表连接
select 字段列表
from 左表 {[inner]|{left|right|full} [outer]} join 右表
on 关联条件;

概念

用来产生主键的值(自己控制主键值,很容易重复,用sequence可以自动增长产生主键值)

序列可以产生1.0*10^38个数

  1. 集合操作
    union
    union all
    intersect
    minus

  2. 组函数和分组
    3.1 组函数
    count *
    max
    min
    sum
    avg

    distinct
    NULL
    3.2 分组
    group by
    having

    语法:
    select
    from
    where
    group by
    having
    order by

    执行:
    from
    where
    group by
    having
    select
    order by

  3. 子查询
    where子句
    select id,first_name,salary from s_emp
    where salary>(
    select salary froms _emp where first_name='Ben'
    );
    select id,first_name from s_emp
    where id in(
    select distinct manager_id from s_emp
    );
    select id,first_name from s_emp
    where id not in(
    select distinct manager_id from s_emp
    where manager_id is not null
    );
    select id,name from s_dept d
    where exists(select * from s_emp e
    where e.dept_id = d.id);
    having子句
    select dept_id,avg(salary) avgsal
    from s_emp
    group by dept_id
    having avg(salary)>(
    select avg(salary) from s_emp
    );
    from子句
    select e.id,e.first_name,e.salary,s.avgsal
    from s_emp e,
    (select dept_id,avg(salary) avgsal from s_emp
    group by dept_id) s
    where e.dept_id = s.dept_id
    and e.salary>s.avgsal;
    select子句
    select e.id,e.first_name,e.salary,(
    select name from s_dept d where e.dept_id = d.id
    ) dname
    from s_emp e;

 如何创建和使用序列

  • 创建序列:

create sequence  序列名; (和建立表一样)

用序列放入的主键值绝对不会重复

  • 使用序列:

序列名。nextval(使用序列值的下一个数)  

序列名。currval(使用当前序列数,基本不会用到)

 

一张表只能用一个序列,不能用两个序列,否则可能重复

可以两张表用一个序列,但是这样数会比较乱

5.表操作
5.1 创建表和删除表
create table 表名(
字段名 数据类型,
....
字段名 数据类型
);
drop table 表名;
5.2 数据操作语句
1)insert
insert into 表名[(字段列表)] values(值列表);
2) update
update 表名 set 字段=新值[,字段=新值,...][where子句];
3) delete
delete [from] 表名 [where子句];
commit;

演示:

(1) 创建表(之前要先删表)

create table testseq(
  id number primary key,
  name varchar2(30),
  salary number,
);

金沙官网线上 1

 

(2) 创建序列

create sequence testseq_id;

金沙官网线上 2

 

  (3) 使用序列,向表中插入数据 

insert into testseq values(
    testseq_id, nextval,
    'test'|| testseq_id.currval,
    1000*testseq_id.currval,
);

金沙官网线上 3

 

 truncate table 表名;  -- 属于ddl语句,功能上等效于没有where子句的delete语句

如何删除序列

drop sequence  序列名  ;(和删除表一样,不存在就说删不到)

drop sequence testseq_id;

 金沙官网线上 4

 

drop sequence testseq_id;

金沙官网线上 5

5.3 事务控制语句
commit;
rollback;
savepoint 保存点;
rollback to 保存点;

创建序列是可以规定其属性(知道就行)

  • create sequence  序列名(表名_要使用序列的字段名)
  • minva 1     最小值
  • maxvalue 9999999   最大值   
  • increment by  1   每次增长数
  • start with  216   从哪里开始
  • nocache   有没有缓存
  • noorded   能不能排序
  • nocycle ;  能不能循环

事务的ACID特性

索引  index(不重要,了解就行,DBA才用)


作用

加速查询速度

练习:
2.给所有'Carmen'的下属涨工资
1) 查询'Carmen'的编号
select id from emp_zsm_00
where first_name='Carmen';
2) 根据'Carmen'的编号 查询其下属的编号
select id from s_emp where manager_id = (
select id from emp_zsm_00
where first_name='Carmen'
);
3) 根据员工编号 改工资
update emp_zsm_金沙官网线上,00 set salary = salary + 500
where id in(
select id from emp_zsm_00 where manager_id =
(
select id from emp_zsm_00
where first_name='Carmen'
)
);

原理

底层通过树状结构组织数据,消耗了大量的空间和时间来加速查询。消耗的是建立索引的时间,一旦索引建立完毕,查询就很快。组织数据的树状结构消耗的空间很大。

 

索引就像书的目录,正常的查找数据像在书中一页一页查询数据量越大,索引的优势越大

ps:set  timing on  如此设置后就会统计每条操作的时间

 

假设有3亿条数据,要查询其中一条数据:

不用索引:

  用oracle(配置为2G内存,i3处理器时)

    查询一条数据大概需要60*8=480s

用索引:0.01s

3.删除和'Ben'同部门的员工
delete from emp_zsm_00 where dept_id=
(select dept_id from emp_zsm_00
where first_name='Ben')
and first_name !='Ben';

创建语法

对唯一性的字段,系统会自动建立索引,叫唯一性索引

非唯一性索引建立:在表的某个字段上创建索引

 

正常用法:

create index  索引名  on 表名(要创建索引的字段名);

 

快速建表(非正常用法)

create table  表名  as select  id,first_name  name,salary  from  查询的表名

(即建的表的数据和查询的数据一样)


演示:

金沙官网线上 6

金沙官网线上 7

 

  1. 约束 constraint
    数据完整性:
    实体完整性
    域完整性
    引用完整性
    自定义完整性

删除语法

drop index  索引名;

 

1.1 约束的概念
对表中的字段添加的限制
1.2 约束的具体类型
主键约束:primary key
非空、唯一
一张表只能有一个主键
唯一约束: unique
不能重复
允许为null
一个表中可以有多个唯一
非空约束: not null
不允许为null
检查约束:check(检查约束表达式)
字段的值必须能够使检查约束表达式为真
外键约束: foreign key references

总结

数据库脚本(xxx.sql)里是怎样写的?(大小写不敏感)

1.3 约束的实现方式
列级约束:定义完一个字段后,直接在后边添加约束
表级约束:定义完一个表的所有字段后,逗号隔开,
再添加约束

(1)删序列

drop sequence   序列名;

1.4 主键约束
1.4.1 列级实现:
1) 系统自动为约束命名
create table testcons_zsm_00(
id number(7) primary key,
name varchar2(20)
);
insert into testcons_zsm_00 values(1,'test1');
/* 违反了唯一约束 */
insert into testcons_zsm_00 values(1,'test2');

(2)删表   

drop table  表名  cascade constraint /* 解除关联*/  ; 
  • ERROR at line 1:
    ORA-00001: unique constraint (OPENLAB.SYS_C0042650) violated
    /* 主键字段的值不允许为null */
    insert into testcons_zsm_00(name) values('test2');
  • ERROR at line 1:
    ORA-01400: cannot insert NULL into ("OPENLAB"."TESTCONS_ZSM_00"."ID")

    2) 手动为约束命名 表名_字段_约束类型 : testcons_id_pk 字段 数据类型 constraint 约束名 约束类型 drop table testcons_zsm_00;
    create table testcons_zsm_00(

        id number(7) constraint testcons_id_pk_zsm_00 primary key,
        name varchar2(20)
    );
    insert into testcons_zsm_00 values(1,'test1');
    /* 违反了唯一约束 */
    insert into testcons_zsm_00 values(1,'test1');
    *
    

    ERROR at line 1: ORA-00001: unique constraint (OPENLAB.TESTCONS_ID_PK_ZSM_00) violated

(3)创建序列(设置属性)(一般之前删了几个就创建几个)

create  sequence  序列名  属性;

1.4.2 表级实现 复合主键(组合键)
drop table testcons_zsm_00;
create table testcons_zsm_00(
id number(7) primary key,
userid number(10) primary key,
name varchar2(20)
);
userid number(10) primary key,
*
ERROR at line 3:
ORA-02260: table can have only one primary key

(4)创建表(字段,列级约束,表级约束)  

create table  表名(字段以及约束);
   create table testcons_zsm_00(
       id number(7),
       userid number(10),
       name varchar2(20),
       constraint testcons_id_userid_pk_zsm_00 primary key(id,userid)
   );
   insert into testcons_zsm_00 values(1,1,'test1');
   insert into testcons_zsm_00 values(1,2,'test1');
   insert into testcons_zsm_00 values(2,1,'test1');
   insert into testcons_zsm_00 values(2,2,'test1');
   /* 违反了唯一约束 */
   insert into testcons_zsm_00 values(2,2,'test1');

   注意:不建议使用复合主键

(5)增加数据  

insert into  表名  values(  匹配表的要求的要增加的数据  );

1.5 唯一约束、非空约束和检查约束
1.5.1 列级实现
drop table testcons_zsm_00;
create table testcons_zsm_00(
id number primary key,
name varchar2(20) not null,
userid varchar2(18) unique,
sal number(11,2) check(sal>2000)
);
/* 不能向name字段插入null(非空约束的字段不允许为null) */
insert into testcons_zsm_00(id,userid,sal)
values(1,'1234',2500);

(6)提交修改  

commit  ;
   insert into testcons_zsm_00(id,name,userid,sal)
           values(1,'name1','1234',2500);
   /* 违反了唯一约束 */
   insert into testcons_zsm_00(id,name,userid,sal)
           values(2,'name2','1234',2350);
   /* 唯一约束的字段允许为null */
   insert into testcons_zsm_00(id,name,sal)
           values(3,'name3',2350);
   /* 唯一约束的字段 可以有多个null值 */
   insert into testcons_zsm_00(id,name,sal)
           values(4,'name4',2350);
  /* 违反了检查约束 */
   insert into testcons_zsm_00(id,name,sal)
           values(5,'name5',1350);
   *
   ERROR at line 1:
   ORA-02290: check constraint (OPENLAB.SYS_C0042698) violated

1.5.2 表级实现
   drop table testcons_zsm_00;
   /* 一个表中有两个唯一约束 */
   create table testcons_zsm_00(
       id number unique,
       name varchar2(20) not null,
       userid varchar2(18) unique,
       sal number(11,2) check(sal>2000)
   );
  insert into testcons_zsm_00  values(1,'test1','12',3000);
  /* 违反了id字段的唯一约束 */
  insert into testcons_zsm_00 values(1,'test1','123',3000);
  /* 违反了userid的唯一约束 */
  insert into testcons_zsm_00 values(2,'test1','12',3000);

 drop table testcons_zsm_00;
 /* id和userid组合作为唯一键 */
 create table testcons_zsm_00(
       id number ,
       name varchar2(20) not null,
       userid varchar2(18),
       sal number(11,2),
       unique(id,userid),
       check(sal>2000)
   );

本文由金沙官网线上发布于数据库,转载请注明出处:constraint、index、view(day04)

您可能还会对下面的文章感兴趣: