Oracle学习笔记(二)

一、sql各种语句划分

SQL : Structured Query Language        结构化     查询  语言---------------------------------------------------DDL: Data Definition Language      数据   定义      语句    create   drop  truncate  alterDML: 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 在原表的基础上新增一个员工住址列 addr3 插入数据如下图：   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;