ORACLE第二天

回忆

    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 数据库的标准