Python-複数表関連外キーカスケード

  
     

    
     ******
          
          

       
            
       
                  
       
                   
     ******
    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";