实例5(默认规则).sql

经典的sql资料

--上课内容：第三单元表的创建和管理 

        -- 第五节 维护数据的完整性——默认、规则

-- 3.5.1 自定义数据类型

      -- 自定义一个数据类型
	sp_addtype 'udt_gender', 'char(1)'

      -- 删除一个自定义数据类型
	sp_droptype 'udt_gender'

-- 3.5.2 默认

  -- 3.5.2.1 创建一个默认—— CREATE DEFAULT 默认名 AS 默认值

    -- 例：创建一个性别的默认值'M'
    create default df_gender as 'M'

  -- 3.5.2.2 创建表,绑定默认在列上
 
    --语法：sp_bindefault 绑定名，'表名.列名'
    create table emp21 (eid int,gender char(1))
    insert into emp21(eid) values(1) 
    select * from emp21

   --例： 绑定 df_gender默认在emp21表的gender列上
    sp_bindefault df_gender, 'emp21.gender'
    insert into emp21(eid) values(2) 
    select * from emp21

   -- 解除默认绑定语法：sp_unbindefault '表名.列名'
   --例： 解除绑定在emp21表的gender列上的 df_gender默认
    sp_unbindefault 'emp21.gender'
    insert into emp21(eid) values(7) 
    select * from emp21


  -- 3.5.2.3 创建表,绑定默认在自定义的数据类型上 

   -- 语法： sp_bindefault 绑定名，'自定义数据类型名'
    create table emp22 (eid int,gender udt_gender)
    insert into emp22(eid) values(1) 
    select * from emp22

   -- 例： 绑定 df_gender默认在自定义的数据类型udt_gender上
    sp_bindefault df_gender, 'udt_gender'
    insert into emp22(eid) values(2) 
    select * from emp22

  -- 解除默认绑定语法：sp_unbindefault '自定义数据类型名'
  -- 例： 解除绑定在自定义数据类型 udt_genere 上的 df_gender默认
    sp_unbindefault 'udt_gender'
    insert into emp22(eid) values(3) 
    select * from emp22

-- 3.5.3 规则

  -- 3.5.3.1 创建一个规则—— CREATE RULE 规则名 AS @变量名 具体要求

    -- 例：创建一个性别的规则值只能是'N'或'F'
    create RULE r_gender as @gender in ('M','F')


  -- 3.5.3.2 创建表,绑定规则在列上 

    -- 语法：sp_bindRULE 绑定名，'表名.列名'
    create table emp23 (eid int,gender char(1))
    insert into emp23(eid,gender) values(1,'M') 
    insert into emp23(eid,gender) values(2,'T') 
    select * from emp23

    -- 例： 绑定 r_gender规则在emp23表的gender列上
    sp_bindRULE r_gender, 'emp23.gender'
    insert into emp23(eid,gender) values(3,'T') 
    select * from emp23

   -- 解除规则绑定语法：sp_unbinRULE '表名.列名'

   -- 例： 解除绑定在emp23表的gender列上的 r_gender规则
    sp_unbindRULE 'emp23.gender'
    insert into emp23(eid,gender) values(4,'T') 
    select * from emp23


  -- 3.5.3.3 创建表,绑定规则在自定义的数据类型上 

   -- 语法： sp_binRULE 绑定名，'自定义数据类型名'
    create table emp24 (eid int,gender udt_gender)
    insert into emp24(eid,gender) values(1,'M')
    insert into emp24(eid,gender) values(2,'T') 
    select * from emp24

   --例： 绑定 r_gender规则在自定义的数据类型udt_gender上
    sp_bindRULE r_gender, 'udt_gender'
    insert into emp24(eid,gender) values(3,'T') 
    insert into emp24(eid,gender) values(4,'F') 
    select * from emp24

   -- 解除规则绑定语法：sp_unbindRULE '自定义数据类型名'
   --例： 解除绑定在自定义数据类型 udt_genere 上的 r_gender规则
    sp_unbindRULE 'udt_gender'
    insert into emp24(eid,gender) values(5,'T') 
    select * from emp24




-- 练习：
-- 1. 创建一个emp31表(eid int,ename char(10),flag char(1),indate datetime,mobile char(11)),
   -- 创建默认，设置默认值为0(不是党员)，并绑定在emp表的flag字段（是否为党员）上
   -- 并验证

-- 2. 为emp表的flag字段上解除默认绑定
   -- 并验证

-- 3. 创建规则，设置为flag(是否为党员)只能输入0或1
   -- 绑定在emp表的flag上
   -- 并验证

-- 4. 解除绑定在emp表的flag上的规则
   -- 并验证

-- 5. 在emp31表创建默认，设置默认值为13901982199，并绑定在emp表的mobile字段上
   -- 并验证

-- 6. 为emp表的mobile字段上解除默认绑定
   -- 并验证

-- 7. 创建规则，设置为13开头的11位手机号码
   -- 绑定在emp表的mobile上
   -- 并验证

-- 8. 解除绑定在emp表的mobile上的规则
   -- 并验证

-- 9. 为emp表加一个工资字段salary decimal(8,2)，并为salary字段创建规则，要求输入的值限定在1000-9999之间；为salary字段创建默认，值为2000
   -- 并验证











-- 练习参考答案：

-- 1. 创建一个emp31表(eid int,ename char(10),flag char(1),indate datetime,mobile char(11))
   -- 创建默认，设置默认值为0(不是党员)，并绑定在emp表的flag字段（是否为党员）上
   -- 并验证
create table emp31(eid int,ename char(10),flag char(1),indate datetime,mobile char(11))
create default df_flag as '0'
sp_bindefault df_flag,'emp31.flag'
insert into emp31(eid,ename) values(1,'may')
insert into emp31(eid,ename,indate,mobile) values(2,'may2','20031212','13901019188')
select * from emp31

-- 2. 为emp表的flag字段上解除默认绑定
   -- 并验证
sp_unbindefault 'emp31.flag'
insert into emp31(eid,ename) values(3,'may3')
select * from emp31

-- 3. 创建规则，设置为flag(是否为党员)只能输入0或1
   -- 绑定在emp表的flag上
   -- 并验证
create rule r_flag as @flag in ('1','0')
sp_bindrule r_flag, 'emp31.flag'
insert into emp31(eid,ename,flag) values(4,'may4','1')
select * from emp31
insert into emp31(eid,ename,flag) values(5,'may5','2')


-- 4. 解除绑定在emp表的flag上的规则
   -- 并验证
sp_unbindrule  'emp31.flag'
insert into emp31(eid,ename,flag) values(5,'may5','2')

-- 5. 在emp31表创建默认，设置默认值为13901982199，并绑定在emp表的mobile字段上
   -- 并验证
create default df_mobile as '13901982199'
sp_bindefault df_mobile,'emp31.mobile'
insert into emp31(eid,ename) values(6,'may6')
select * from emp31

-- 6. 为emp表的mobile字段上解除默认绑定
   -- 并验证
sp_unbindefault 'emp31.flag'
insert into emp31(eid,ename) values(7,'may7')
select * from emp31

-- 7. 创建规则，设置为13开头的11位手机号码
   -- 绑定在emp表的mobile上
   -- 并验证
create rule r_mobile as @mobile like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
sp_bindrule r_mobile, 'emp31.mobile'
insert into emp31(eid,ename,mobile) values(8,'may8','13901900188')
select * from emp31
insert into emp31(eid,ename,mobile) values(9,'may9','1390190018F')
insert into emp31(eid,ename,mobile) values(10,'may10','23901900188')

-- 8. 解除绑定在emp表的mobile上的规则
   -- 并验证
sp_unbindrule  'emp31.mobile'
insert into emp31(eid,ename,mobile) values(9,'may9','1390190018F')
insert into emp31(eid,ename,mobile) values(10,'may10','23901900188')

-- 9. 为emp表加一个工资字段salary decimal(8,2)，并为salary字段创建规则，要求输入的值限定在1000-9999之间；为salary字段创建默认，值为2000
   -- 并验证
alter table emp31 add salary decimal(8,2)
create rule r_salary as @salary>1000 and @salary<9999

sp_bindrule r_salary, 'emp31.salary'
select * from emp31
insert into emp31(eid,ename,salary) values(11,'may11',2000)
insert into emp31(eid,ename,salary) values(12,'may12',1000)
insert into emp31(eid,ename,salary) values(12,'may12',12000)

sp_unbindrule  'emp31.salary'
insert into emp31(eid,ename,salary) values(12,'may12',1000)

create default df_salary as 2000
sp_bindefault df_salary,'emp31.salary'
insert into emp31(eid,ename) values(13,'may13')

sp_unbindefault 'emp31.salary'
insert into emp31(eid,ename) values(14,'may14')