oracle4，约束（主键，非空，检查，唯一，外键），索引，视图，练习

约束

--创建表
create table y_user (
       unum number(10) primary key,
       uname varchar2(100) not null,
       usex char(4) check(usex='男' or usex ='女'),
       uage number(3) check(uage < 150 and uage >0),
       uqq varchar2(200) unique
       
)
----------------------------------------------
create table y_user (
       unum number(10) ,
       uname varchar2(100),
       usex char(4),
       uage number(3),
       uqq varchar2(200)，
       constraints pk_y_user_unum primary key(unum),
       constraints ck_y_user_uname check(uname is not null),
       constraints ck_y_user_uage check(uage < 150 and uage > 0),
       constraints ck_y_user_usex check(usex ='男' or usex ='女'),
       constraints un_y_user_uqq unique(uqq)
)
-------------------------------------------------------------------------------------
--查询
select * from y_user;
--删除表
drop table y_user;
--添加或删除主键约束
alter table y_user add constraints pk_y_user_unum primary key(unum) 
alter table y_user drop constraints pk_y_user_unum    
--添加或删除非空约束
alter table y_user add constraints ck_y_user_uname check(uname is not null)
alter table y_user drop constraints ck_y_user_uname
--添加或删除检查约束(年龄)
alter table y_user add constraints ck_y_user_uage check(uage < 150 and uage > 0)
alter table y_user drop constraints ck_y_user_uage
--添加或删除检查约束（性别）
alter table y_user add constraints ck_y_user_usex check(usex='男' or usex = '女')
alter table y_user drop constraints ck_y_user_usex
--添加删除唯一约束：
alter table y_user add constraints un_y_user_uqq unique(uqq),
alter table y_user drop constaints un_y_user_uqq,
------------------------------------------------------------------------------------------------------------------------------------
--约束：
      --主键约束
      --唯一约束
      --检查约束
      --非空约束
      --外键
--插入数据：
     --问题1：unum是主键，可以重复添加
           insert into y_user values(1,'小小','女',10,'123456');
           insert into y_user values(1,'小','女',10,'123456');
     --问题2：名字可以为空？
           insert into y_user values(2,'','女',10,'123');       
     --问题3：性别可以随便填？
           insert into y_user values(3,'小小','2',10,'16');
     --问题4：年龄可以大于200岁？
           insert into y_user values(4,'小小','女',300,'1456');
     --问题5：他们的qq可以重复？
           insert into y_user values(5,'小小','女',10,'123456');
           insert into y_user values(6,'小小','女',10,'123456');
--解决：
           --1：添加主键约束：非空唯一
                  在创建表字段后面加 primary key;
                  在创建表的最后加
                             constraints pk_表名_字段名 primary key(字段名)
                             constraints pk_y_user_unum primary key(字段名)
                  创建表后添加主键：
                             格式：alter table 表名 add constraints pk_表名_字段名 primary key(字段名)
                             alter table y_user add constraints pk_y_user_unum primary key(unum) 
                  创建表后删除主键：
                             格式：alter table 表名 drop  constraints pk_表名_字段名
                             alter table y_user drop constraints pk_y_user_unum 
           --2:设置非空约束：
                  在创建表字段后面添加：not null,
                  在创建表的最后加
                              格式：constraints ck_表名_字段名 check(字段名 is not null)
                                constraints ck_y_user_uname check(uname is not null)
                  创建表后添加非空主键：
                               格式：alter table 表名 add constraints ck_表名_字段名 check(字段名 is not null)
                               alter table y_user add constraints ck_y_user_uname check(uname is not null)
                  创建表后删除非空主键：
                               格式：alter table 表名 drop  constraints ck_表名_字段名  
                                alter table y_user drop constraints ck_y_user_uname
            --3:设置检查约束：性别
                  在创建表字段后面添加：check(字段名 比较运算符 值) check(usex ='男' or usex = '女')
                  在创建表的最后加
                              格式：constraints ck_表名_字段名 check(字段名 比较运算符 值)
                              constraints ck_y_user_usex check(usex ='男' or usex= '女')
                  创建表后添加判断约束：
                               格式：alter table 表名 add constraints ck_表名_字段名 check(字段名 比较运算符 值)
                               alter table y_user add constraints ck_y_user_usex check(usex ='男' or usex = '女')
                  创建表后删除判断约束：
                               格式：alter table 表名 drop  constraints ck_表名_字段名  
                                alter table y_user drop constraints ck_y_user_usex                     
            --4:设置判断约束：      
                  在创建表字段后面添加：check(字段名 比较运算符 值) check(uage>150 and uage < 0),  
                  在创建表的最后加
                              格式：constraints ck_表名_字段名 check(字段名 比较运算符 值)
                                constraints ck_y_user_uage check(uage>150 and uage < 0)
                  创建表后添加判断约束：
                               格式：alter table 表名 add constraints ck_表名_字段名 check(字段名 比较运算符 值)
                               alter table y_user add constraints ck_y_user_uage check(uage>150 and uage < 0)
                  创建表后删除判断约束：
                               格式：alter table 表名 drop  constraints ck_表名_字段名  
                                alter table y_user drop constraints ck_y_user_uage  
            --5:设置唯一约束：
                  在创建表字段后面添加：unique,
                  在创建表的最后加：
                                格式：constraints un_表名_字段名 unique(字段名);
                                constraints un_y_user_uqq unique(字段名);
                  创建表后添加唯一约束：
                               格式：alter table 表名 add constraints un_表名_字段名 unique(字段名)
                               alter table y_user constraints un_y_user_uqq unique(uqq),
                  创建表后删除唯一约束：
                               格式：alter table 表名 drop constraints un_表名_字段名
                               alter table y_user drop constraints un_y_user_uqq,  
-----------------------------------------------------------------------------------------------------------------------------------
--外键约束：
   --创建表
   create table student(
          sno number(10) primary key,
          sname varchar2(100) not null,
          sage varchar2(10) check(sage < 150 and sage >0),
          ssex varchar2(4) check(ssex ='男' or ssex='女'),
          sfav varchar2(200) ,
          sqq varchar2(10) unique,
          sid number(10), --references clazz(cid)
          constraints fk_studnet_sid foreign key(sid) references clazz(cid)
          
   )
--添加外键
alter table studnet add constraints  fk_studnet_sid foreign key(sid) references clazz(cid)
alter table student drop constraints fk_student_sid
   --插入模拟数据
          insert into student values (1,'天天01','33','男','打游戏','123456',1);
           insert into student values (2,'天天02','33','男','打游戏','12345',1);
            insert into student values (3,'迪迪01','33','男','打游戏','1234',2);
             insert into student values (4,'迪迪02','33','男','打游戏','123',2);
             insert into student values (5,'迪迪03','33','男','打游戏','12',3);
   --查看
   select * from student;
   drop table student;
   --创建表clazz
   create table clazz(
          cid number(10) primary key,
          cname varchar2(100) not null,
          cdesc varchar2(100)
   )
   --插入模拟数据
          insert into clazz values(1,'成长','好');
          insert into clazz values(2,'自由','棒');
   --查看表
   select * from clazz;
--问题1：可以在学生中插入没有的班级？
   insert into student values (4,'迪迪02','33','男','打游戏','123',3);
   --解决：引入外键
          格式：
          在表的字段名的后面加references 表名（字段名） references clazz(cid)
          在表的最后加；constraints fk_子表名_字段名 foreign key(字段名) references 父表名（字段名）
                        constraints fk_student_sid foreign key(sid) references clazz(cid)
          创建表后的添加 alter table 子表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名（字段名）
                         alter table studnet add  constraints fk_student_sid foreign key(sid) references clazz(cid)
          创建表后删除：alter table 子表名 drop constraints fk_子表名_字段名
                        alter table studnet drop  constraints fk_student_sid
--外键的描述
   当存在外键时，想子表插入父表不存在的数据时，会自动报错，
   当一张的某个字段需要依赖另外一张表的某个字段时，使用外键，
   外键的选取，一般选择父表的主键作为子表的外键，
   主动依赖的表称之为子表，被依赖的表称之为父表，
--外键的缺点：
   当删除父表的时候，无法删除，当删除子表的时候，可以正常的删除，（项目中最好不要使用外键）
--使用外键时的删除（对于父表的删除）：
   --使用级联删除
      格式：on delete cascade,
      使用此关键字，删除父表的选择的数据以及对应子表中的数据，无法保留子表中的数据历史，
      格式：on delete set null
      删除父表的时候，将子表的依赖字段设置为空，但是子表的依赖字段不能设置为空。
                               
   

          
            
                             
           

二维表的维护（字段名的新添，修改，删除，修改表名，显示表的结构）

--二维表的维护
select * from student;
  --添加新的字段名(不要设置约束，如果设置非空约束的化，字段里面的数据是空的，这不就很矛盾)
    --格式： alter table 表名 add 字段名 类型 
             alter table student add pnone number(11);
   --修改字段的类型：
     --格式：alter table 表名 modify 字段名 新的类型
             alter table student modify pnone varchar(11);
   --修改字段的名称
      --格式：alter table 表名 rename column原来字段名 to 新字段名
      alter table student rename column pnone to sphone;
    --删除字段
      --格式：alter table 表名  drop column 字段名
         alter table student drop column sphone;
    --修改表名 rename 原表名 to 新表名
           rename sstudent to student;
    --显示表的结构：在command  window 中
    desc 表名；

索引和视图

--序列
--创建序列
  --默认序列
  create sequence 序列名;
  create sequence cc;  --创建序列
  select cc.currval from dual; --查看当前序列的值
  select cc.nextval from dual; --下一个序列值自动增加
  --自定义序列
  create sequence 序列名 start with 值 increment by 值;
  create sequence aa --创建自定义序列
  start with 5 --开始的序列值
  increment  by 5; --默认步长5
  select aa.currval from dual; --查看当前序列的值
  select aa.nextval from dual; --下一个序列值自动增加
--概叙：
  --默认开始是没有值的，也就是指针指的是没有值的位置，
  --序列名.currval 查看当前序列的值，开始没有
  --序列名.nextval 每次执行都会自动加一，默认步长为1
  --作为主键使用，可以动态获取主键的值，防止主键的值冲突
  --主键非空唯一，不一定要连续的。
          --创建表
          create table teacher (
                 aid number(10) primary key,
                 aname varchar2(100)
          )
          --插入数据
          select * from teacher;
          insert into teacher values(cc.nextval,'消息');
--删除序列
drop sequence 序列名
drop sequence aa;
-------------------------------------------------------------------------------------
--视图：
     --概念：
              --创建视图要具有dba权限
              --可以保护真实表，隐藏重要数据，保护数据。
              --对于视图的操作可以映射到真实表中
              --可以手动开启只读模式 关键字  with read only
     --创建视图：
              create view 视图名 as select 内容 from 真实表名
              create view stu as select sno,sname,sage from student; 
              create view stu as select sno,sname,sage from student with read only;
              select * from student;
              select * from stu;
     --删除视图
              drop view 视图名
              drop view stu;
            

表的练习

create table Employess(
       employeeID varchar2(6) primary key not null ,--员工编号
       ename varchar2(10) not null ,--姓名
       Birthday date not null,--出生日期
       Sex varchar2(2) not null,--性别
       Address varchar(20) ,--地址
       Zip varchar(6) ,--邮编
       Phonenumver varchar2(12) ,--电话号码
       Emailaddress varchar2(30) ,--电子邮件
       departmentID varchar(3) not null,  --员工部门号 
       constraints fk_employess_departmentID foreign key (departmentID) reference departments(departmentID)，
       constraints ck_sex check(sex = '男' or sex ='女')
)
--设置的约束名的最长字符是30个，多了设置不了
alter table Employess add constraints fk_e_d_departmentID foreign key (departmentID) reference departments(departmentID);
alter table Employess add constraints ck_sex check(sex = '男' or sex ='女')
create table Departments(
       departmentID varchar2(3) not null primary key,--部门编号
       departmentName varchar2(20) not null ,--部门号
       note varchar2(16), --备注
       constraints un_deparmentName unique(departmentName)
)
alter table departments add constraints un_deparmentName unique(departmentName);
create table Salary (
       employeeID varchar2(6) not null primary key ,--员工编号
       income number(8,2) not null,--收入
       outcome number(8,2) not null --支出

)
--对于Employess添加
insert into Employess(employeeID,ename,Birthday,Sex,departmentid) values (010001,'王平',to_date('1981-01-05','yyyy-mm-dd'),'女'，'1');
insert into Employess(employeeID,ename,Birthday,Sex,departmentid) values (010003,'韦严平',to_date('1979-11-05','yyyy-mm-dd'),'男'，'2');
insert into Employess(employeeID,ename,Birthday,Sex,departmentid) values (010021,'王平',to_date('1984-07-02','yyyy-mm-dd'),'女'，'3');
insert into Employess(employeeID,ename,Birthday,Sex,departmentid) values (010031,'王平',to_date('1980-08-04','yyyy-mm-dd'),'男'，'4');
insert into Employess(employeeID,ename,Birthday,Sex,departmentid) values (010005,'王平',to_date('1985-02-10','yyyy-mm-dd'),'男'，'5');
--DepartmentS表的添加
insert into Departments (departmentID,departmentName)  values (1,'办公室');
insert into Departments (departmentID,departmentName)  values (2,'人力资源部');
insert into Departments (departmentID,departmentName)  values (3,'销售部');
insert into Departments (departmentID,departmentName)  values (4,'财务部');
insert into Departments (departmentID,departmentName)  values (5,'生产部');
--对于Salary表的添加
insert into Salary (employeeID,income,outcome) values (010001,3000,100);
insert into Salary (employeeID,income,outcome) values (010003,3000,50);
insert into Salary (employeeID,income,outcome) values (010021,5000,50);
insert into Salary (employeeID,income,outcome) values (010031,3000,50);
insert into Salary (employeeID,income,outcome) values (010005,2500,50);
--修改表的结构
alter table employess add QQ number(15)
--
update salary set income = 2980  where employeeID=010001;
update Departments set departmentName = '生产计划部' where departmentID = 5;
update salary set income= income+100 ;