四、Oracle之DDL语句

/*
 一、DDL语句 数据定义语言 使用数据库语句创建数据库的对象
    1.表空间  实例分配的一块空间，是用于项目开发 建表和插入数据的
    创建表空间
      create  tablespace 为表空间名
      datafile 物理文件路径，指定表空间对应数据文件
      size   定义表空间的初始化大小
      autoextend on 自动增长，当表空间存储占满时，自动增长
      next  指定的是依次自动增长的大小     
*/
      create  tablespace MyTablespace
      datafile 'c:\MyTablespace.dbf'
      size   10m
      autoextend on
      next  20m  



--向表空间建表和插入数据 创建的普通用户
/*
  2.1、管理员创建用户
     create  user  用户名
     identified by  密码
     default tablespace 表空间名

     Oracle和其他数据库产品的区别在于，表和其他的数据库对象都存储在用户下 
*/      

     create  user  it
     identified by  it
     default tablespace MyTablespace

/*
  2.2、管理员给创建的用户授予权限
   权限 和角色
      DBA:      拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
      RESOURCE: 拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
      CONNECT:  拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
    grant  角色 to 用户
   授予权限语法 
      grant  权限 to 用户
*/

grant  connect to it

grant  RESOURCE to it

grant alter session to it

/*
  2.3 oracle数据库表的划分
      表根据表空间划分
      如果一个表空间由多个用户管理 表根据用户划分     
*/

/*
  3.1、设计表
     根据需求设计表中的列  
     A- 列的数据类型
         1）数值类型  int long float  double bigint tinyint
                   oracle使用  ①number(v1,v2) v1v2默认不写是32位的整数
                               ②number(v1) v1数值总长度 v2小数位数 默认为0
                               number(6,2)--9999.99
         2）字符类型   
                   ①char()    固定长度的字符类型 char(10) 'zs' 
                                       存储数据2个长度占用空间10个长度
                   ②varchar() 可变长度的字符类型 varchar(10) 'zs' 
                                       存储数据2个长度占用空间2个长度
                   ③varchar2() 可变长度的字符类型 varchar(10) 'zs' 
                                       存储数据2个长度占用空间2个长度
         3）日期类型
                   ①date        mySql的日期格式  yyyy-mm-dd 
                   ②dateTime    mySql的日期格式  yyyy-MM-dd hh:mm:ss
                   ③timestmp
                   ④oracle的date格式
                         yyyy-MM-dd hh:mm:ss
                   timestmp  更精确的日期格式 秒后9位纳秒
         4）大文本类型

                ①clob  字符类型大文本 存储4G的长度文本
                ②blob  二进制类型大文本  存储4G的长度 文件的相对路径/usr/admin/image/
                ③long  长文本类型  2G的长度

     B- 1）表的约束
        ①主键约束 primary key
        ②外键约束 foreign key
        ③非空约束 not null
        ④唯一约束 unique
        ⑤检查约束 check(gender in(0,1))--检查列值是否满足表达式
      2）自定义约束名称
       constraint 约束名 约束类型(列名)

*/

create table person(

 pid number(20),
 name varchar2(10),
 phone char(11) unique, --唯一约束
 gender number(1) default 1 check(gender in (0,1)) ,--默认值1,检查约束
 birthday date ,
 constraint pk_person_pid primary key(pid)


)

create table p(

 pid number(20) not null,
 name varchar2(10),
 phone char(11) unique, --唯一约束
 gender number(1) default 1 check(gender in (0,1)) ,--默认值1,检查约束
 birthday date ,
 constraint pk_p_pid primary key(pid)


)

alter table person add constraint aafdsfa unique(name);

---插入数据测试约束
--oracle事务需要手动选择提交或者回滚
insert into person values(1,'zs','11122223333',1, to_date('1991-11-11','yyyy-mm-dd'));
select * from person;
---测试约束
insert into p values(1,'zs','11122223333',1,sysdate);--违反主键约束
insert into p  values(2,'zs','11122223333',1,sysdate);--违反唯一约束
insert into p  values(2,'zs','11122225555',1,sysdate);
insert into p  values(2,'zs','11122225555',null,sysdate);
insert into p  values(3,null,'11122226666',1,sysdate);--违反非空约束
insert into p  values(3,'','11122226666',1,sysdate);--空串当作null处理
insert into p  values(3,' ','11122226666',1,sysdate);
insert into p  values(4,'xiaofang','11122227777',9,sysdate);--违反检查约束
insert into p  values(4,'xiaofang','11122227777',0,sysdate);
delete from p  where name is null 
commit


/*
  3.2更改表结构  alter table 表名
     1)添加一列            add(列名 数据类型)
     2)修改数据类型        modify(列名 数据类型)
     3)重命名列            rename column 旧列  to 新列名
     4)删除一列            drop column  列名
     5)添加主键约束        add constraint 约束名 约束类型(列）
*/
--添加用户地址address列 alter alert
alter table person add (address varchar2(40))
--修改列address列长度为100 类型为char
alter table person modify (address char(100))
alter table person modify (pname number(10))--列值存在数据不允许修改
--重命名性别gender 改成 sex
alter table person rename column gender to sex
--删除地址列
alter table person drop column address
--给p表添加主键约束
alter table p add constraint pk_p_pid primary key(pid)


/*
  3.3 删除表 2.drop  table 用户.表明

*/

drop table person