Oracle入门笔记(二)

一、sql各种语句划分

 

SQL : Structured Query Language 
       结构化     查询  语言
---------------------------------------------------
DDL: Data Definition Language 
     数据   定义      语句
    create   drop  truncate  alter

DML: Data Manipulation Language
     数据   操纵        语句
    insert  update  delete
  
DQL: Data Query Language
     数据  查询  语句
     select from 

DCL: Data Control Language
     数据  控制   语句
     grant to
     revoke from 

TCL: Transactional Control Language
       事务         控制   语句
---------------------------------------------------

DDL: 数据定义语句
 create 创建  alter 修改   drop 删除  truncate 截断 

  * create 
     创建用户: create user 用户名 identified by 口令;
     创建表: create table 表名(
		列名1  数据类型,
		列名2  数据类型,
		...
		列名x  数据类型
              );
       create table student(
        sid number,
	sname varchar(20),
	phone varchar2(11)
       );

   * alter 修改
       修改用户密码:
        alter user 用户名 identified by 新密码;
       修改表结构:
        
	**新增一个列:
	alter table 表名 add (列名 类型,列名2 类型,..);
        Exec:
	给student 表添加一个新列 gender 字符类型:
	alter table student add(gender varchar(2) );
	
	**修改列的类型:
	alter table 表名 modify 列名 新类型;
	Exec:
	将student的性别列修改为number:
	alter table student modify gender number;

	**删除列:
	alter table 表名 drop column 列名;
	Exec:
	将student的性别列删除:
	alter table student drop column gender;

	**修改列的名字:
	alter table 表名 rename column 原列名 to 新列名;
	Exec:
	将student表的sname列更改为name列:
	alter table student rename column sname to name;

	**修改表名:
	alter table 表名 rename to 新名;

    * drop 删除
      删除用户: 
          drop user 用户名;  (这种写法用在没有内容的用户中)
          drop user 用户名 cascade;
      删除表:
          drop table 表名;
         ** 通过drop删除的表会被存放到系统回收空间中,
	  表以及其中数据原先占有的内存空间并没有被释放掉.
       recyclebin: 回收站
         ** 可以使用purge命令来清空回收站,
	    或者在删除表的同时添加purge关键字.
	    使用了purge命令的删除语句会立即释放表原先占有的内存空间

    * truncate 截断
       截断表:  truncate table 表名;
        * 保留表的结构,释放表中数据占有的内存空间.

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

DML : Data Manipulation Language 
     数据操纵语句
    
     * insert 新增
     向表中插入一条数据:
       insert into 表名 values(列1值,列2值...);
        * 如果表名之后没有括号写出列名,
	  那么values之后的括号中要根据表的列的默认顺序,
	   把所有的列都给赋值.
        * 如果某个列是字符类型,
	  那么values中的对应的值必须使用单引号引起来
       
       insert into 表名(列1,列3,列2)
                   values(列1值,列3值,列2值);
        * 可以在表名之后指定要给哪几个列插入数据.

       insert into student(sname,sid) values(&a,&b);
        注: & 符号可以用来匹配多次插入数据

     插入日期类型的字段:
     insert into student(sid,sname,birth)
		 values(100,'小明','15-7月 1915');
     * 如期类型可以通过字符串的方式来插入数据

     * update 更新
         update 表名 set 列名=值,列名=值... where 条件;
	 * 如果没有where条件,默认更新全表
	 * 更新操作将表中set后的列默认的更新为等号右侧的值
	   * 如果列是字符串,那么值需要用单引号

     * delete 删除
        delete from 表名 where 条件;
	* 如果没有where条件,那么默认清空整张表
	  效果等同于 truncate 相同
       



DQL : Data Query Language
     数据查询语句
     * select 查询 
      查询当前用户中的可用表名:
      select table_name from user_tables;
      查询所有的符合条件的记录:
      select * from 表名 where 条件;

--------------------------------------------------------
DCL:  Data Control Language 数据控制语句
 
 权限:
   
     grant 权限 [on 资源] to 用户 
         [with admin/grant option];
   
     revoke 权限 [on 资源] from 用户;

------------------------------------------------------
TCL : Transactional Control Language 
       事务控制语句

commit ; 提交
    * 将收受影响的DML操作,持久化到数据库中
rollback ; 回滚
    * 回退到上一次commit操作之后
savepoint ; 还原点
    * 在某个DML操作之后,可以设置还原点,
      通过rollback to 还原点名; 就能够回退到该状态.

      
事务:
    * 事务只能影响DML操作
    * 事务是一个整体操作,所有的操作步骤全部成功执行,
    那么整个操作才算完成;如果有一个步骤操作失败,
    那么整个操作全部失败需要回退到最初状态.

Oracle 开启事务:
     set autocommit off;   -- 设置自动提交关闭
                             打开事务

     set autocommit on;    -- 设置自动提交开启
                             关闭事务

事务特性:
     * 原子性 :事务不可再分
     * 一致性 :操作事务中数据时,类型保持一致
     * 持久性 :事务能够持久化数据到数据库中
     * 隔离性 :多个事务之间可能产生的隔离性问题

 隔离性问题:

   * 脏读: 
	  如果两个事务A和B,
           事务A读到事务B未提交的数据.

   * 不可重复读:(更新)
          两个事务A和B, 
	   如果事务A读取某张表,事务B同时读取这张表,并且做了修改操作,
	   事务B提交后,事务A再次读取这张表时读到了修改后的数据,跟上一次读取的数据不同
        * 数据量未发生变化而数据内容被修改
   * 幻读:(新增 删除)
        * 数据量发生变化
 以下为《Spring实战》上对事务隔离级别的介绍

 

隔离级别:
			     脏读  不可重复读 幻读 
   未提交读 read uncommitted:   √       √      √
   *提交读:  read committed :   ×       √      √
   可重复读: repeatable read :  ×       ×      √
   √串行化:   Serializable :    ×       ×      ×


Oracle支持提交读和串行化两个级别:

开启事务: set autocommit off;
更改隔离级别:
 提交读:
   set transaction isolation level read committed;

 串行化:
   set transaction isolation level serializable;


隐式提交:
    * 在事务中,执行DML操作时,执行了一个DDL操作,
      数据库会在DDL操作之前隐式的添加一个commit操作

显示提交:
    * 在事务中,手动执行commit操作


------------------------------------------------
** drop trucate 和delete 有什么区别?
  1 drop 和truncate 是DDL 而delete是DML
  2 drop 表示删除表,不会释放表原先占有的内存空间
    truncate是截断表,能够释放表中数据所占有的内存空间
    delete是删除表中的数据,不会释放数据占有的内存空间.
  3 drop使用purge命令时,可以释放表和数据所占有的内存空间.
  
--------------------------------
Exec:
1 创建打卡时间 cardtime表 结构如下:
   编号cid 员工姓名ename 手机号phone 打卡时间ctime 
  请写出创建表的语句

2 在原表的基础上新增一个员工住址列 addr

3 插入数据如下图：
   cid   ename    phone          ctime          addr
    1     LILEI  13725221234     12-4月 2014   山东济南
    2     Di_mi  15612341212     23-4月 2014   河北邯郸
    3     Lucy   15110105566     20-5月 2014   山东青岛
    4     HMM    18616521652     25-7月 2014   山东济南
    5     LILI   13225219800     15-4月 2014   北京朝阳 
    6     Tom    13556567878     4-8月 2014    香格里拉
    7     Tom_mi 13678261277     14-3月 2014   上海虹桥

 请写出新增语句，以'LILEI'为例

二、表的约束

 

 

 

什么约束？(下面是百度的专业回答)
   约束是在表中定义的用于维护数据库完整性的一些规则
   通过为表中的列定义约束可以防止将错误的数据插入表中，也可以保持表之间数据的一致性
   .若某个约束条件只作用于单独的列，可以将其定义为列约束也可定义为表约束；
   若某个约束条件作用域多个列，则必须定义为表约束
   表约束通常放在最后一个列定义的后面
   .如：CREATE TABLE products (     product_no integer,     name text,     price numeric CHECK (price > 0) ) 这是列约束
   CREATE TABLE products (     product_no integer,     name text,     price numeric,     discounted_price numeric,     CHECK (price > discounted_price)) 这是表约束
什么时候使用约束?使用什么约束?
   * 需要唯一标识数据的列,我们可以使用主键
   * 需要告诉数据库母子表之间的关系,需要指定外键
   * 如果该列上不能出现空值,比如一条数据的录入日期
       not null
     ....

   ** 约束越多,数据库数据越健壮;
      约束越多,数据库效率越低.
Oracle中表的约束:1、主键约束: primary key 主键: 能够通过该列上的一个唯一的值确定一条数据 * 特性: 非空且唯一 创建主键: create table test1( tid number constraint pk_tid primary key, tname varchar2(10), age number ); * constraint pk_tid : 约束名 (可有可无) primary key : 主键约束 将该列变为一个主键列,则这个列是非空且唯一 ** 复合主键: 一个主键是由多个列组成. 也就是说多个列组合在一起才起到一个主键的作用. create table test2( tid number , tname varchar2(10) , age number, constraint pk_t primary key(tid,tname) ); 表外添加主键:alter table 表名 add constraint 约束名 primary key(列名1,列2...);create table test3( tid number , tname varchar2(10) , age number ); 如何给test3添加主键? alter table test3 add constraint pk_t3 primary key(tid);2、外键约束: foreign key 一个部门中有多个员工 主表(母表) : 部门表 从表(子表) : 员工表 * 外键: 在子表中,如果有一个列上的数据引用了另外一张 表的主键列上的数据,那么这个在子表中的列就被称作外键列创建外键: 主表: create table mother( mid number primary key, mname varchar2(20) ); 子表: create table child( cid number primary key, cname varchar2(20), age number, birth date, mno number, constraint fk_c foreign key(mno) references mother(mid) );插入数据: 首先插入主表数据,然后插入子表数据删除数据: 首先删除对应的子表中子记录,然后删除主表数据表外给子表添加外键约束的写法:alter table 表名 add constraint 约束名 foreign key(列1,列2...) references 主表名(列1,列2...)
主键约束: primary key
    主键: 能够通过该列上的一个唯一的值确定一条数据
       * 特性: 非空且唯一
       * 一张表只能有一个主键

 创建主键:
 create table test1(
  tid number constraint pk_tid primary key,
  tname varchar2(10),
  age number
 );
 * constraint pk_tid : 约束名 (可有可无) 
   primary key : 主键约束
                 将该列变为一个主键列,则这个列是非空且唯一

  ** 复合主键:
    一个主键是由多个列组成.
    也就是说多个列组合在一起才起到一个主键的作用.
 create table test2(
  tid number ,
  tname varchar2(10) ,
  age number,
  constraint pk_t primary key(tid,tname)
 );

 表外添加主键:
alter table 表名 
  add constraint 约束名 primary key(列名1,列2...);

create table test3(
  tid number ,
  tname varchar2(10) ,
  age number
 );
  如何给test3添加主键?
  alter table test3 
   add constraint pk_t3 primary key(tid);


外键约束: foreign key
   一个部门中有多个员工
    主表(母表) : 部门表
    从表(子表) : 员工表

   * 外键: 在子表中,如果有一个列上的数据引用了另外一张
            表的主键列上的数据,那么这个在子表中的列就被称作外键列

创建外键:
 主表:
 create table mother(
  mid number primary key,
  mname varchar2(20)
 );
 子表:
 create table child(
  cid number primary key,
  cname varchar2(20),
  age number,
  birth date,
  mno number,
  constraint fk_c foreign key(mno)
   references mother(mid)
 );

插入数据:
    首先插入主表数据,然后插入子表数据
删除数据:
    首先删除对应的子表中子记录,然后删除主表数据

表外给子表添加外键约束的写法:
alter table 表名 
 add constraint 约束名 foreign key(列1,列2...)
   references 主表名(列1,列2...)


3、唯一性约束: unique
   * 数据库会在具有唯一性的列上,自动添加索引
   
创建唯一性约束:
create table Test4(
 tid number primary key,
 tname varchar2(20) unique
);
   * 带有唯一性的列可以不添加数据
   * 如果唯一性的列上带有数据,那么必须不能重复
表外添加:
 alter table test5 
    add constraint un_tname unique(tname);

非空约束: not null
   * 不允许在该列上出现空值
创建非空约束:
create table test5(
 tid number not null,
 tname varchar(20)
);
alter table test5 modify tid not null;

检查约束: check
   * 数据库需要对某些字段做另外的一些约束
比如学生年龄必须大于12岁:
create table teacher(
 sid number primary key,
 sname varchar(10),
 age number,
 check(age>56)
);

4、删除约束:
 alter table 表名 drop constraint 约束名;

--------------------------------------------------
Exec:
学生表Student :
  学号sno 姓名sname 年龄age
学科表Subject:
  学科号bno  学科名bname
成绩表Sc:
   学号sid  学科号bid  成绩score

* 分别给学生表和学科表添加主键
alter table student add constraint pk_sno primary key(sno);
alter table subject add constraint pk_bno primary key(bno);
  给成绩表添加联合主键
alter table sc add constraint pk_sc primary key(sid,bid);

* 给成绩表添加外键:
   sid引用学生表的学号,bid引用学科表的学科号
alter table sc add constraint fk_sid 
  foreign key(sid) references student(sno);

alter table sc add constraint fk_bid
  foreign key(bid) references subject(bno);

* 成绩不能为空
alter table sc modify score not null;