42，数据库（04）

最新推荐文章于 2024-05-28 20:31:26 发布

wang73ying

最新推荐文章于 2024-05-28 20:31:26 发布

阅读量508

点赞数

分类专栏：数据库文章标签：数据库 oracle

本文链接：https://blog.csdn.net/wang73ying/article/details/13754551

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

/*
达内学习 Oracle day41 2013-10-29*/
组函数和分组
1.处理特点
对一组数据处理返回一个结果
2.常见的组函数
count max min avg sum
3.组函数可以使用 distinct
4.组函数对 NULL 处理是忽略

分组
1.按照一定的分组标准把数据分成若干组
2.group by 分组标准
3.如何组数据进行过滤 having
4.sql的执行顺序
from
      where
           group by
         having
      select
          order by
5.分组中结合表连接
在分组语句中select 后字段的限制
要么是分组标准要么是经过合适的组函数
处理过的。
----------------------------------
子查询:
    把一个查询的结果作为另一个查询的
    基础。
1.where 后
select distinct manager_id from s_emp;
select id ,first_name from s_emp
      where id in(select distinct
      manager_id from s_emp);
2.having 后
3.from   后
select *from(select dept_id id,avg(salary) sal
      from s_emp
         group by dept_id)where sal>
(select avg(salary) from s_emp
   where dept_id=42);
-------------------------------------
DDL
1.建表语句
create table 表名(
      字段名     类型,
      字段名     类型,
      字段名     类型
);
2.删表语句
drop table 表名 ;
3.数据类型
number
char
varchar2
4.date 类型
1.默认格式   'dd-MON-yy'
2.没有时分秒信息
    如果采用默认格式插入的时分秒都是0
3.to_char 以指定格式显示日期
    yyyy
    mm
    dd

    hh24
    mi
    ss

    mon
    month
    day
    pm
4.把日期的时分秒放入数据库
    sysdate
    to_date(日期字符串,日期格式字符串)
5.按照天小时分钟秒为单位进行调整
6.特殊调整
    add_months
    last_day
    next_day
7.日期的计算
    months_between
    round
    trunc
------------------------------------
DML   和事务控制
1.insert
insert into 表名 values(值1,值2);
insert into 表名(字段1,字段3)
values(值1,值3);
2.delete
delete from 表名 where 条件;
3.update
update 表名 set 字段=值1,字段2=值
      where 条件;
DML操作是有事务特性的。
事务也叫交易 transation
原子性:事务中的操作是一个不可的整体
    一起成功一起失败
    转账
    update account set money=money-5000
        where id='A';
    -- commit;
    /* commit;*/
    update account set money=money+5000
        where id='B';
    if(a&&b){
        commit;
    }else{
        rollback;
    }
    create table account(
        id     varchar2(10) primary key,
        money number
    );
    insert into account values('A',10000);
    insert into account values('B',1);
    commit;
隔离性:事务中的操作再没有提交以前对
    另一个事务数据的变化是不可见的。
    但事务会锁定这条数据,没提交以前
    别的事务对同样的数据做操作会产生
    锁定状态。
    insert into account values
    ('C',99999999);
    commit;
    rollback; 只能撤销未提交的数据。
     insert into account values
    ('D',89999999);
一致性
持久性

--------------------
事务的原子性太严格,有时要做部分成功部分
失败。
insert into account values('E',1);
savepoint a;
insert into account values('F',2);
savepoint b;
insert into account values('G',3);

rollback to b;
commit;

--------------------------------------
数据库中的约束:constraints
对数据库表中的字段加的限制。
1.五种具体的约束
a.主键约束    primary key
    非空并且唯一
    一个表的主键只能有一个
b.唯一性约束 unique
    值不能重复
c.非空约束     not null
    值不能是NULL值
d.检查约束     check
    表中字段的值必须符合检查条件
e.外键约束     references(关联引用)
                 foreign key
2.表中约束的分类
a.列级约束
    在定义表的某一列时直接对表的这一列
    加约束限制。
b.表级约束
    在定义完表的所有列之后再选择某些列
    加约束限制。
3.主键的列级约束
create table testcol_cons(
      id   number primary key,
      name varchar2(30)
);
insert into testcol_cons values(1,'a');
insert into testcol_cons values
(NULL,'b');
insert into testcol_cons values(1,'c');
ERROR at line 1:
ORA-00001: unique constraint
(OPENLAB.SYS_C00360658)
如果给一个加约束没有起名字则系统会
为这个约束自动建立一个名字。
4.建立字段的约束时自己给约束命名
   pk uk   nn ck fk
   drop    table testcol_cons111;
   create table testcol_cons111(
      id   number constraint
      testcol_cons111_id_pk primary key,
      name varchar2(30)
   );
   insert into testcol_cons111
   values(1,'c');
   insert into testcol_cons111
   values(1,'c');
   ERROR at line 1:
ORA-00001: unique constraint (OPENLAB.TESTCOL_CONS111_ID_PK) violated

5.主键约束和唯一性约束的列级约束
   建立一张表
   id      number   设置成主键
   fname   varchar2(30) 设置成唯一
   要求给每个约束起名字。
   drop    table testcol_cons111;
   create table testcol_cons111(
      id   number constraint
      testcol_cons111_id_pk primary key,
      fname varchar2(30) constraint
      testcol_cons111_fname_uk unique
   );
   insert into testcol_cons111 values
   (1,'test');
   insert into testcol_cons111 values
   (2,'test');
   insert into testcol_cons111 values
   *
   ERROR at line 1:
   ORA-00001: unique constraint
   (OPENLAB.TESTCOL_CONS111_FNAME_UK)
   violated

   6.建立一张表
     id     number primary key
     fname varchar2(30) unique
     sname varchar2(30) not null
     salary number   检查条件
                      salary>3500
     create table testcolucons(
         id   number constraint
         testcolucons_id_pk primary key,
fname varchar2(30) constraint
testcolucons_fname_uk unique,
sname varchar2(30) constraint
testcolucons_sname_nn not null,
         salary number       constraint
testcolucons_salary_ck
check(salary>3500)
     );
    insert into testcolucons values
    (1,'test','test',3499);
    ERROR at line 1:
    ORA-02290: check constraint
    (OPENLAB.TESTCOLUCONS_SALARY_CK)
    violated
7.表级约束的主键约束
    在定义完表的所有列之后再选择某些
    列加约束限制。
    表级约束的优势在于可以做联合约束。
    在数据库中没有联合非空的需求
    导致非空没有表级约束。
    create table testtable_cons(
        id   number,
        fname varchar2(30),
sname varchar2(30),
salary number,
constraint testtable_cons_id_pk
primary key(id)
    );
8.唯一性和检查约束的表级约束
    drop   table testtable_cons;
    create table testtable_cons(
        id   number,
        fname varchar2(30),
sname varchar2(30),
salary number,
constraint testtable_cons_id_pk
primary key(id),
constraint testtable_cons_fname_uk
unique(fname),
        constraint testtable_cons_salary_ck
check(salary>3500)
    );
---------------------------------------
外键约束:涉及到两张表一张叫父表(主表)
另一张叫子表(从表)。
子表中一个字段称之外键外键字段的取值
受限于父表的字段值。
外键的取值要么是NULL 要么是父表中字段
的取值。
定义了外键的表就是子表。
create table parent(
    id   number   primary key,
    name varchar2(30)
);
create table childtest(
    id   number   primary key,
    name varchar2(30),
    fid number constraint
    childtest_fid_fk
    references parent(id)
);
insert into childtest values(1,'test',1);
RROR at line 1:
RA-02291: integrity constraint
(OPENLAB.CHILDTEST_FID_FK)
1.建立表先建立父表后建立子表
除非先不考虑主外键关系
2.插入数据一般先插入父表后插入子表数据
除非子表的外键值是NULL
3.删除数据
先删子表数据后删父表数据
除非子表中没有和主表字段对应的数据
或者设置了级联(on delete cascade
                 on delete set null)
4.修改数据设置外键的值要么为NULL
要么设置成父表中字段对应的值
5.删除表
先删子表后删父表
除非你使用先解除主外键关系再删除表
drop table parent;
drop table parent cascade constraints;
举例:
部门表
create table deptparent133(
    id    number primary key,
    name varchar2(30)
);
insert into deptparent133 values(1,'test');
insert into deptparent133 values(2,'project');
commit;
员工表子表
dept_id
create table empchild133(
    id    number   primary key,
    name varchar2(30),
    dept_id number constraints
    empchild133_dept_id_fk references
    deptparent133(id)
);
部门1中有三个员工
部门2中有二个员工
insert into empchild133 values(1,'a',1);
insert into empchild133 values(2,'b',1);
insert into empchild133 values(3,'c',1);
insert into empchild133 values(4,'d',2);
insert into empchild133 values(5,'e',2);
commit;
取缔一个部门
delete from deptparent133 where id=1;
先删子表和父表字段关联的数据
delete from empchild133 where
dept_id=1;
--------------------------------
/*设置级联*/
部门表
drop    table deptparent133
cascade constraints;
create table deptparent133(
    id    number primary key,
    name varchar2(30)
);
insert into deptparent133 values(1,'test');
insert into deptparent133 values(2,'project');
commit;
员工表子表
dept_id
drop    table empchild133 cascade
constraints;
create table empchild133(
    id    number   primary key,
    name varchar2(30),
    dept_id number constraints
    empchild133_dept_id_fk references
    deptparent133(id) on delete set null
);
部门1中有三个员工
部门2中有二个员工
insert into empchild133 values(1,'a',1);
insert into empchild133 values(2,'b',1);
insert into empchild133 values(3,'c',1);
insert into empchild133 values(4,'d',2);
insert into empchild133 values(5,'e',2);
commit;
取缔一个部门
delete from deptparent133 where id=1;

--------------------
外键的表级约束
drop    table empchild133 cascade
constraints;
create table empchild133(
    id    number   primary key,
    name varchar2(30),
    dept_id number,constraints
    empchild133_dept_id_fk
    foreign key(dept_id) references
    deptparent133(id) on delete set null
);
--------------------------------------
数据库的其它对象
1.序列
生成主键的值
创建序列
create sequence 序列名;
create sequence testseq123;
测试序列
select testseq123.nextval from dual;
select testseq123.currval from dual;
使用序列
create table testpkuse_seq(
      id   number primary key,
      name varchar2(30)
);
insert into testpkuse_seq values
(testseq123.nextval,
'test'||testseq123.currval);

复杂的序列
CREATE SEQUENCE s_customer_id
   MINVALUE 1
   MAXVALUE 9999999   1.0*10 27
   INCREMENT BY 1
   START WITH 216
   NOCACHE             默认20
   NOORDER
   NOCYCLE;
CREATE SEQUENCE s_customer_idt
   MINVALUE 100
   MAXVALUE 9999999
   INCREMENT BY 1
   START WITH 216
   NOCACHE
   NOORDER
   NOCYCLE;
select s_customer_idt.nextval
from dual;
   删除序列
   drop sequence 序列名;
   drop sequence s_customer_idt;
----------------------------------------
2.索引
用来加速查询
消耗了大量的空间和时间
3亿条数据   7-8分钟
建立索引   消耗了大量的空间和时间
              0.01秒0.00
如何建立索引
1.唯一性字段上系统会自动建立索引
    唯一性索引
2.在非唯一性字段上可以人为的创建
    索引。
    create   index 索引名 on
    表名(字段);
    set timing on;
    create table testemp101 as
    select id,salary from s_emp;
    create index testemp101_id_ind
    on testemp101(id);
3.删除索引
    索引和表占不是一个空间
    drop index 索引名;
    drop index testemp101_id_ind;
3.视图
视图本质上是一条sql 相对于视图对应
的数据视图的空间是可以忽略的。
create or replace view myview
as select id,first_name,salary from
s_emp;
create or replace view myview2
as select id,first_name from
s_emp;
可以对同一份物理数据做不同的表现
简化查询
select * from myview;
select * from (select id,first_name,
salary from s_emp);
select * from myview2;
---------------------------------------
三范式:
第一范式:表中的字段不可再分
      任何的关系型数据库必须满足第一范式
第二范式:满足第一范式的基础上
      所有的非主属性完全依赖主属性
      表中的数据可以被唯一区分
第三范式:在第二方式的基础上消除了传递
      依赖。
一张表能不能表达1:m?
一个部门中有多个员工
id did dname eid ename eage
1   d001 test   e001 ea    19
2   d001 test   e002 ea    21
3   d001 test   e003 ec    20
4   d002 ios    e004 ed    23
5   d002 ios    e005 ee    26

好处方便查询
坏处数据冗余
     增删改
消除传递依赖 -----拆表
id->did->dname
id->eid->did->dname
id->eid->ename
拆分出部门表
did dname
d001 test
d002 ios
拆出员工表
eid ename eage did
e001 ea    19    d001
e002 ea    21    d001
e003 ec    20    d001
e004 ed    23    d002
e005 ee    26    d002
如果要做 1:1 只要把did 设置成唯一

一张表表达多对多
学生选课
id   sid   sname sage cid cname ctime
1    s001 yangmi 32   c001 c     15
2    s001 yangmi 32   c002 c++   8
3    s001 yangmi 32   c003 ios   35
4    s002 xiaobei44   c001 c     15
5    s002 xiaobei44   c004 vc    35
6    s003 xlong 53   c001 c     15
拆表
学生选课关系表
id   sid      cid
1    s001     c001
2    s001     c002
3    s001     c003
4    s002     c001
5    s002     c004
6    s003     c001

学生表
sid   sname sage
s001 yangmi 32
s002 xiaobei 44
s003 xlong   53
课程表
cid cname ctime
c001 c     15
c002 c++   8
c003 ios   35
c004 vc    35

s001号学生选了哪些课程
select distinct s.sname,c.cname
     from student s,course c,stucour sc
         where s.sid=sc.sid and
        c.cid=sc.cid and
        s.sid='s001';
--------------------------------------
分页技术:
    oracle   rownum
    mysql    limit
    sqlserver top

select id,first_name,salary
from s_emp;
select rownum,first_name,salary
from s_emp;
假设一页显示7条要第一页数据
select rownum,first_name,salary
from s_emp where rownum<8;
显示第二页数据 [8,15)
select rownum,first_name,salary
from s_emp where rownum<15 and
rownum>7;

select * from (select rownum r,
first_name,salary
from s_emp where rownum<15)where r>7;

按照工资排序每页显示7 条
显示第二页数据

select   first_name,salary from s_emp
    order by salary;
先编号还是先排序?先排序
select* from(
    select rownum r,first_name,salary
    from(select first_name,salary
        from s_emp order by salary)
    where rownum<2*7+1
)where r>(2-1)*7;

---------------------------------------
列出sql中和 NULL 相关的知识点?