oracle笔记 2

回忆
    ORACLE安装 和 删除
    启动和关闭
    SQL*PLUS  windows下窗口版本
               超级用户 system/manager
                       sys/change_on_install
               普通用户
                       scott/tiger
              DOS下的SQLPLUS版本
    如何使用
      命令show user
      SQL语句
难点
    约束  如何给表加约束？？？

SQL*PLUS环境命令
  结束时可以写; 也可以不写
  desc dept
  show user
  connect scott/tiger
  set linesize 200
  set pagesize 100
  spool c:\aa.txt
 
SQL语句
  不需要分号结束的命令,sqlplus环境命令
     show user
     connect scott/tiger
     desc dept
     set linesize 200
     set pagesize 100
 
 SQL语句结束时候一定要 有分号或者换行加/

《1》DDL语句(数据定义语言) Data Define Language
   create
   alter
   drop
   truncate 开头的语句 truncate table <表名>
      特点:<1>建立和修改数据对象
           <2>建立和修改直接存入库中,直接生效

    建立表
       create table class(--班级表
          classid number(2) primary key,
          cname varchar2(20) not null);

       /*wdsadsadsad
         asdsadsadsadsad
         多行注释  
        */  
       create table student( --学生表
         xh number(4) primary key, --学号
         name varchar2(10) not null, --姓名
         sex  char(2)  check (sex in ('男','女')),--性别
         birthday date,--生日
         sal number(7,2), --奖学金
         classid number(2) references class(classid) --班级          
       );

 外键引用的列一定是主键或有unique约束的列
 
  alter table student add (shengfenzheng number(18));

  drop table student;  删除结构
  delete from student; 只删除数据，速度慢，数据可以恢复
  truncate table student; 删除记录的 速度快 数据不能恢复

 《2》 DML语句(数据操作语言) Data Manupilate Language     
   select
   insert
   delete
   update
     特点:<1>对数据起作用的
         <2> 这些语句的修改是在内存中发生的
             要想改动存入库中必须要commit语句

 
  insert into student(xh,name,sex)
  values (&xh,'&n','&sd');
 
  insert into student(xh,name,sex)
  values (&学号,'&姓名','&性别');

  转义字符 \
  打开转义 set escape on
   
  insert into dept
  values (90,'JO&HI','北京');

  insert into dept
  values (92,'JO\&HI','大家');


 《3》 TCL(事务控制语句) Transaction Control Language
  commit;  提交  修改保存到数据库中
  rollback; 回滚  取消内存中的改动
  savepoint;保存点 分解事务的 把事务变小
       DDL语句 会自动提交以前未提交的事务
       关闭SQLplus工具 也会自动提交未提交的事务的
  事务 -- 就是一个完整的对数据的DML操作
  所有事务 都是要明确的提交和回滚的
  --转账
    update 账目表
    set 钱=钱-500
    where 帐号='A';
    update 账目表
    set 钱=钱+500
    where 帐号='B';
    commit;


    事务何时存在 DML语句中除select以外都会有事务
   
《《《《《《《注意》》》》》 / 重复运行上一条SQL语句   

  commit;    结束上一个事务 并且开始一个新的事务

  update student set sal = null where xh =1000;

  savepoint c111;
   
  insert into student(xh,name,sex) values (1004,'MIKE','男');

  rollback to c111; --撤销了插入的数据

  rollback;  --从c111这个点回滚到事务的开始点

《SQLPLUS规则》  
   a)DML语句后跟上DDL语句 DML语句的事务会被自动提交
   b)exit/quit命令 退出 SQLPLUS环境时也会自动提交事务
      点小叉子关闭sqlplus窗口 事务都自动回滚了
   c)非法操作是不能提交事务的 ，只能导致事务回滚
 
《4》 DCL语句(数据控制语句) Data Control Language                    grant 授予权限
     revoke 撤销权限
   权限 select ,insert,delete,update
        all (select ,insert,delete,update 总和)
   角色 connect （登陆数据库）,resource(建立表和对象)
   
   如何建一个自己的用户？
     必须是超级用户才能建用户
     --连接到超级用户
     connect system/manager
     --建立用户名zhangsan 密码m123
     create user zhangsan identified by m123;
     --授予必要的权限connect 你能够连接
                    resource 你能建表不受空间的限制,建立对象
     grant connect,resource to zhangsan;
     --这个普通用户就建好了 和scott用户的权限是一样的      
     grant DBA to zhangsan; --张三的权限和System一样
   
     --改张三的密码
    <<1>> 自己改自己的密码
        connect zhangsan/m123
        密码改为了mm1  
        alter user zhangsan identified by mm1;
    <<2>> 超级用户来改
        connect system/manager
        alter user zhangsan identified by mm1;  
 
 
     

   在scott/tiger这个用户下
     grant select on dept to zhangsan;
    在zhangsan下 可以使用select * from scott.dept;
         看到结果
    
 在scott/tiger这个用户下
       revoke select on dept from zhangsan;撤销授权
    在zhangsan下 可以使用select * from scott.dept;
         看不到结果
    


约束
   主键约束 --  每个表要有主键，唯一的标识一行数据
   非空约束
   唯一性约束
   外键约束
   检查约束
     脚本(SCRIPT)
        create table cla( --班级表
          id number(2) primary key, --班级编号
          cname varchar2(20) not null --班级名字
       );

        create table stu( --学生表
          xh number(4) primary key, --学号是主键
          xm varchar2(20) not null, --姓名非空
          age number(2) check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) unique, --身份证唯一  
          classid number(2) references cla(id) -- 班级编号外键
           --(引用的一定是主键或唯一性约束的字段)
         );
        
      <1>建立表的同时使用约束
  create table student( --学生表
           xh number(4) primary key, --学号主键
           xm varchar2(10) not null, --姓名不能为空
           sex char(2)  check (sex in ('男','女')), --性别
           birthday date unique, --日期
           sal number(7,2) check (sal between 500 and 1000),--奖学金 sal >=500 and sal <=1000
     classid number(2) references cla(id)
        );  --必须要先有cla表才对
            --一定先建立班级cla表
 
   主键约束 primary key
           not null
           check
           unique 唯一约束           

 create table student( --学生表
           xh number(4) constraint pk_stu primary key, --学号主键
           xm varchar2(10) constraint nn_stu not null, --姓名不能为空
           sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性别
           birthday date constraint uq_bir unique, --日期
           sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--奖学金 sal >=500 and sal <=1000
        );
      <2>建立约束的同时给约束指定名字，便于删除
        create table cla( --班级表
          id number(2) constraint pk_cla primary key, --班级编号
          cname varchar2(20) constraint nn_cla not null --班级名字
       );
      
      create table stu( --学生表
          xh number(4) constraint pk_stu primary key, --学号是主键
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份证唯一  
          classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束的字段)
         );
   
 
      <3>建完表后加约束
 
 学生表student
        create table student( --学生表
           xh number(4), --学号
           xm varchar2(10), --姓名
           sex char(2), --性别
           birthday date, --日期
           sal number(7,2) --奖学金
        );
 加约束
   加主键
    alter table student add constraint pk_stu
    primary key (xh);
   加非空
    alter table student modify (xm not null);
   检查约束
    alter table student add check(sex in ('男','女'));
    alter table student add constraint ck_sal check(sal between 500 and 1000));


 给student加班级字段
   alter table student add (classid number(2));


   班级表cla
    create table cla( --班级表
          id number(2), --班级编号
          cname varchar2(20) --班级名字
       );

添加 主键
 alter table cla add constraint pk_cla
       primary key (id);
加 not null
 alter table cla modify
       (cname not null);

               学生表student
      create table student( --学生表
          xh number(4) ,
          xm varchar2(20) , --姓名非空
          age number(2),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18), --身份证唯一  
          classid number(2) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束的字段)
         );

加外键约束
alter table student add constraint fk_stu
    foreign key (classid) references cla(id);

加主键
alter table student add constraint pk_stu
 primary key (xh);

加not null
alter table student modify(xm not null);

加检查约束
alter table student add constraint cc_age
 check (age >= 10 and age <=90);

加唯一约束
  alter table student add constraint
      uq_sfz unique(shenfenzheng);

加外键约束
 alter table student add constraint
    fk_stu foreign key (classid)
     references cla(id);

如何删除约束
   
  alter table student drop constraint
        fk_stu;
可以用一个统一的格式来删除
  alter table 表名 drop constraint 约束名

  <4>如何查看约束?? 约束一定加在表上

    一个表上到底有哪些约束???
  select constraint_name,constraint_type
      from user_constraints
        where table_name = 'STUDENT'
--查看表上有什么约束
  select * from user_constraints;
--查看约束作用在什么字段上
  select * from user_cons_columns
   where CONSTRAINT_NAME='PK_STU';

user_constraints数据字典表


  <5>约束是如何起作用的??

       create table cla( --班级表
          id number(2) constraint pk_cla primary key, --班级编号
          cname varchar2(20) constraint nn_cla not null --班级名字
       );
      
      create table stu( --学生表
          xh number(4) constraint pk_stu primary key, --学号是主键
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份证唯一  
          classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束unique的字段)
         );
   
   主键 = 非空 + 唯一
   非空
   唯一 = 有值的话  值要不同
          null的话  都是可以的
   外键 = 有值 一定要在被引用的表的数据中
          null的话  是可以的

ANSI SQL92 数据库的标准