Python-複数表関連外キーカスケード
11945 ワード
******
******
foreign key( ) references ( )
***
on update cascade
on delete cascade
2.
1.
2.
3.
sql
----------------------------------------
,
1、
2、
3、
******
1, ( foreign key)
create table dept(id int primary key auto_increment,name char(20),job char(20));
create table emp(id int primary key auto_increment,name char(20),d_id int,foreign key(d_id) references dept(id));
2, ( foreign key)
，
？
create table teacher(id int primary key auto_increment,name char(15));
create table student(id int primary key auto_increment,name char(15));
#
create table tsr(
id int primary key auto_increment,
t_id int,s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references student(id)
);
insert into teacher values
(1," "),
(2," ");
insert into student values
(1," "),
(2," ");
#
insert into tsr values
(null,1,1),
(null,1,2),
(null,2,2);
3, ( foreign key unique)
，
create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table stu(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #
foreign key(customer_id) references customer(id) # unique
on delete cascade
on update cascade
);
insert into customer(name,qq,phone) values
(' ','31811231',13811341220),
(' ','123123123',15213146809),
(' ','283818181',1867141331),
(' ','283818181',1851143312),
(' ','888818181',1861243314),
(' ','112312312',18811431230)
;
#
insert into stu(class_name,customer_id) values
(' 3 ',3),
(' 19 ',4),
(' 19 ',5)
;
******
foreign key( ) references ( )
# foreign key ？
1： ， dep， emp
2： ， dep， emp
3： ， ，
1： ， dep， emp
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
2： ， dep， emp
insert into dep(dep_name,dep_comment) values
('sb ','sb ， python '),
(' ',' '),
('nb ','nb ');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);
3：
：
1、 emp， dep，
mysql> drop table emp;
Query OK, 0 rows affected (0.11 sec)
mysql> drop table dep;
Query OK, 0 rows affected (0.04 sec)
2、 ： ， ，
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
insert into dep(dep_name,dep_comment) values
('sb ','sb ， python '),
(' ',' '),
('nb ','nb ');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);
#
mysql> select * from dep;
+----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name | dep_comment |
+----+------------------+------------------------------------------------------------------------------------------+
| 1 | sb | sb ， python |
| 2 | | |
| 3 | nb | nb |
+----+------------------+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 1 | alex | male | 1 |
| 2 | egon | male | 2 |
| 3 | lxx | male | 1 |
| 4 | wxx | male | 1 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
5 rows in set (0.00 sec)
mysql> delete from dep where id=1;
Query OK, 1 row affected (0.02 sec)
mysql> select * from dep;
+----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name | dep_comment |
+----+------------------+------------------------------------------------------------------------------------------+
| 2 | | |
| 3 | nb | nb |
+----+------------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 2 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
#
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 2 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
mysql> update dep set id=200 where id =2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+-----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name | dep_comment |
+-----+------------------+------------------------------------------------------------------------------------------+
| 3 | nb | nb |
| 200 | | |
+-----+------------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 200 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
***
on delete cascade
on update cascade id
create table emp(
id int primary key auto_increment,
name char(20),
d_id int,
foreign key(d_id) references dept(id)
on delete cascade
on update cascade
);
1.
2.
2.
1.
add after|first
after
first
modify
change
drop
rename
2.
create table select * from ;
create table select * from where 1 = 2;
create table stu_copy2 select * from student1 where 1 = 2;
delete from tb1;
： ， id 0，
，delete
delete from tb1 where id > 10;
， truncate tb1；
：
3.
insert into select *from ;
insert into ( ) select from ;
sql
sql sql
1255241708 123
select *from user where account = ":"drop database mysql" and pwd = "123";