MySQL入门(alter语法 与 外键)

时间:2021-11-10 04:31:43

MySQL入门(三)

字段的修改、添加、与删除

修改表字段使用alter table语句,谨记!

create table tf1(
    id int primary key auto_increment,
    x int,
    y int
);

# 修改
alter table tf1 modify x char(4) default '';
alter table tf1 change y m char(4) default '';

# 增加
mysql>: alter table 表名 add 字段名 类型[(长度) 约束];  # 末尾
eg>: alter table tf1 add z int unsigned;

mysql>: alter table 表名 add 字段名 类型[(宽度) 约束] first;  # 首位
eg>: alter table tf1 add a int unsigned first;

mysql>: alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;  # 某字段后
eg>: alter table tf1 add xx int unsigned after x;

mysql>: alter table 表名 drop 字段名;  # 删除字段
eg>: alter table tf1 drop a;

多表关系(外键)

外键基础知识

"""
多表关系主要如下:
一对一:外键在任何一方都可以,此时外键要设置 唯一键
一对多:外键必须放在多的一方,此时外键值不唯一
多对多:一定要创建第三张表(关系表),每一个外键值不唯一,看可以多个外键建立联合唯一
"""
# 1、外键的 字段名 可以自定义(名字随意),通常命名规范(关联表_关联字段)

# 2、外键要通过 foreign key 语法建立表与表之间的关联

# 3、[constraint 外键名 ]foreign key(所在表的外键字段) references 关联表(关联字段)
# eg:foreign key(detail_id) references author_detail(id)

# 4、级联关系
#   级联更新 on update cascade
#   级联删除 on delete cascade

# 重点:外键字段本身可以唯一或不唯一,但是外键关联的字段一定唯一

"""
非级联的两张关系表,若数据已经被从表引用,则主表的那条数据无法更新或删除,只有先删了从表的数据才能操作主表的数据;
有级联的两张关系表,主表的数据更新或删除,会同时影响从表,会跟着一起更新或删除。
"""

"""
了解内容:(除级联外的其他reference_option)
set null:从父表中删除或更新该行,并将子表中的一个或多个外键列设置为NULL。ON DELETE SET NULLON UPDATE SET NULL。

restrict:拒绝父表的删除或更新操作。指定 RESTRICT(或NO ACTION)与省略ON DELETEor ON UPDATE子句相同。

no action :标准SQL中的关键字。在MySQL中等效于RESTRICT。

set default: 
"""

设置外键

# 建表语句
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

# 升级版  product_order表具有其他两个表的外键。一个引用product的两个字段,一个引用customer的一列:
CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

添加外键

如果我们忘记设置外键也可以后续添加(如果没有那个字段就需要先手动添加):

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

删除外键

我们可以使用alter table语法来删除外键:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

若是不知道外键名称可以使用show create table语法来查看: