mysql 创建两张表 建立关联 外键

创建数据库

mysql> create database lesson53;
Query OK, 1 row affected (0.01 sec)

 

创建第一张表

CREATE TABLE lesson53.ClassCharger(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean  -- show create table ClassCharger: tinyint(1)

);

给第一张表添加数据

mysql> INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
    ->                                                        ("丹丹",14,0),
    ->                                                        ("歪歪",22,0),
    ->                                                        ("姗姗",20,0),
    ->                                                        ("小雨",21,0);

创建第二张表

mysql> CREATE TABLE Student2(
    ->        id INT PRIMARY KEY auto_increment,
    ->        name VARCHAR (20),
    ->        charger_id TINYINT,
    ->        CONSTRAINT charger_id FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
    -> );

     -- constraint 外键名称 foreign key (外键名称) references 主表名称(主表列名称)
     --切记:作为外键一定要和关联主键的数据类型保持一致

Query OK, 0 rows affected (0.11 sec)

 

增加外键和删除外键

ALTER TABLE student  ADD CONSTRAINT abc
                     FOREIGN KEY(charger_id)
                     REFERENCES  classcharger(id);

ALTER TABLE student DROP FOREIGN KEY abc;