夫妻表增删改查实例fightingxuan的博客-

-- 1..1  crud 插入   drop table hunsband; drop table wife; create table hunsband(        id number constraint hunsband_id_pk primary key,        name varchar2(255) constraint hunsband_name_nn not null,       salary number,       birthday date ); create table wife(        id number constraint wife_id_pk primary key,        name varchar2(255) constraint wife_name_nn not null,        salary number,        birthday date,        hunsband_id number  references hunsband(id) ); -- 1..1  插入数据时，尤其是插入外键时，那么一定关联的表中主键列出现过的值 insert into hunsband values(1,'pantf',2000,'1-1月-2020'); insert into hunsband values(2,'mapp',2000,'1-1月-2019'); insert into wife values(1,'taost',3000,'1-1月-2019',1); insert into wife values(2,'zhangtt',5000,'1-1月-2020',2); --insert into wife values(3,'matt',5000,'1-1月-2020',3); -- 更新  -- 更新没有外键的表 update hunsband set name = 'ddd' where id = 1; --更新没有外键表的主键 update hunsband set  id = 10 where name = 'mapp'; insert into hunsband(id,name) values(12,'wangzh'); update hunsband set id = 15 where name = 'wangzh'; -- 修改数据时该数据没有被关联 那么就可以修改主键列，如果被关联那么就可以修改非主键列 -- 更新 妻子表   修改外键表并不对对被关联并没有产生任何的影响，可以随意修改 select * from wife; update wife set id = 18 where name = 'zhangtt'; -- 修改外键一定是要修改成关联的表中主键列存在的值 update wife set hunsband_id = 151 where id = 18; -- 删除 -- 删除丈夫数据 delete from hunsband where id = 1 ; -- 删除妻子数据 删除有外键表的数据对关联的表没有影响 delete from wife; select * from wife; select * from hunsband; delete from hunsband; -- 如果说我非要 直接删除丈夫 -- 从建表的时候就需要指定当关联的主键被删除时候，外键应该进行什么样的操作 -- on delete no action -- on delete cascade  当被关联表中数据删除时，那么外键数据也级联删除 -- on delete set null 当被关联的表中的数据删除时 外键质控 drop table wife; drop table hunsband; create table hunsband(        id number constraint hunsband_id_pk primary key,        name varchar2(255) constraint hunsband_name_nn not null,       salary number,       birthday date ); create table wife(        id number constraint wife_id_pk primary key,        name varchar2(255) constraint wife_name_nn not null,        salary number,        birthday date,        hunsband_id number references hunsband(id) on delete cascade ); select * from hunsband; select * from wife; insert into hunsband values(1,'pantf',2000,'1-1月-2019'); insert into hunsband values(2,'wangzh',3000,'1-2月-2019'); insert into wife values(1,'taost',2000,'1-1月-2019',1); delete from hunsband where id = 1; drop table wife; drop table hunsband;