• day43 字段的修改、添加和删除,多表关系(外键),单表详细操作(增删改查)


    字段的修改、添加和删除

    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;

    多表关系(外键)

    一对一:丈夫-妻子,用户-身份证,作者-作者详情
    一对多:部门-员工,班级-学生,书-出版社
    多对多:老师-班级,课程-学生,出版社-作者

    # 书 - 出版社 - 作者 - 作者详情 外键分布
    # 外键是 建立表与表关联 的字段,通常 一个表的外键 是 另一个表的主键(唯一键也可以)

    # 一对一:外键在任何一方都可以,此时外键要设置 唯一键

    作者(author):id,name,sex,age,mobile
    作者详情(author_detail): id,info,address,author_id
    ----------------------------------------------------
    作者(author):id,name,sex,age,mobile, detail_id
    1 Tom 1
    2 Bom 2
    3 Bob 3

    作者详情(author_detail): id,info,address
    1 Tom_info
    2 Bom_info

    # 一对多:外键必须放在多的一方,此时外键值不唯一

    书(book):id,name,price,publish_id
    1 西游记 1
    2 东游记 2
    3 西厢记 1
    4 流浪记 1

    出版社(publish): id,name,address,phone
    1 老奶奶出版社
    2 小奶奶出版社

    # 多对多:一定要创建第三张表(关系表),每一个外键值不唯一,看可以多个外键建立联合唯一

    作者(author):id, name, age
    出版社(publish):id, name, address
    作者与出版社关系表:id, author_id, publish_id
    id author_id publish_id
    1 1 1
    2 1 2
    3 2 1
    4 2 2

    #外键

    # 作者(author):id,name,sex,age,mobile, detail_id
    # 作者详情(author_detail): id,info,address

    # 1、外键的 字段名 可以自定义(名字随意),通常命名规范(关联表_关联字段)

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

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

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

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

     

    #一对一:无级联关系

    # 作者详情(author_detail): id,info,address
    create table author_detail(
      id int primary key auto_increment,
      info varchar(256),
      address varchar(256)
      );

    # 作者表id,name,sex,age,mobile, detail_id
    create table author(
      id int primary key auto_increment,
      name varchar(64) not null,
      mobile char(11) unique not null,
      sex enum('男', '女') default '男',
      age int default 0,
      detail_id int unique not null,
      foreign key(detail_id) references author_detail(id)
      );

    # 必须先创建被关联表数据,有关联表外键关联的记录后,关联表才可以创建数据
    mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
    mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
    mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
    mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);

    # 修改关联表 author
    mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
    mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据,就可以修改
    # 删除关联表 author
    mysql>: delete from author where detail_id=3; # 直接删除

    # 修改被关联表 author_detail
    mysql>: update author_detail set id=10 where id=1; # 无法修改
    # 删除被关联表 author_detail
    mysql>: delete from author_detail where id=1; # 无法删除

    # 没有级联关系下:
    # 增加:先增加被关联表记录,再增加关联表记录
    # 删除:先删除关联表记录,再删除被关联表记录
    # 更新:关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定,可以修改)

    #一对一:有级联关系

    mysql>: drop table author;
    mysql>: drop table author_detail;


    # 作者详情(author_detail): id,info,address
    create table author_detail(
      id int primary key auto_increment,
      info varchar(256),
      address varchar(256)
      );

    # 作者表id,name,sex,age,mobile, detail_id
    create table author(
      id int primary key auto_increment,
      name varchar(64) not null,
      mobile char(11) unique not null,
      sex enum('男', '女') default '男',
      age int default 0,
      detail_id int unique not null,
      foreign key(detail_id) references author_detail(id)
      on update cascade
      on delete cascade
      );

    # 必须先创建被关联表数据,有关联表外键关联的记录后,关联表才可以创建数据
    mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1); # 错误
    mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
    mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
    mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
    mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);

    # 修改关联表 author
    mysql>: update author set detail_id=3 where detail_id=2; # 失败,3详情不存在
    mysql>: update author set detail_id=1 where detail_id=2; # 失败,1详情已被关联
    mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
    mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据,就可以修改
    # 删除关联表 author
    mysql>: delete from author where detail_id=3; # 直接删除

    # 修改被关联表 author_detail
    mysql>: update author_detail set id=10 where id=1; # 级联修改,同步关系关联表外键

    # 删除被关联表 author_detail
    mysql>: delete from author where detail_id=10; # 可以删除对被关联表无影响
    mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
    mysql>: delete from author_detail where id=10; # 可以删除,将关联表的记录级联删除掉

    #一对多

    # 一对多:外键必须放在多的一方,此时外键值不唯一

    # 出版社(publish): id,name,address,phone
    create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
    );

    # 书(book):id,name,price,publish_id, author_id
    create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int, # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
    );

    # 增:先增加被关联表(publish)的数据,再增加关联表(book)的数据
    mysql>: insert into publish(name, address, phone) values
    ('人民出版社', '北京', '010-110'),
    ('西交大出版社', '西安', '010-119'),
    ('老男孩出版社', '上海', '010-120');

    mysql>: insert into book(name, price, publish_id) values
    ('西游记', 6.66, 1),
    ('东游记', 8.66, 1),
    ('python从入门到入土', 2.66, 2),
    ('轮程序员修养之道', 3.66, 3),
    ('好好活着', 88.88, 3);
    # 没有被关联的字段,插入依旧错误
    mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4); # 失败


    # 更新:直接更新被关联表的(publish) 主键,关联表(book) 外键 会级联更新
    mysql>: update publish set id=10 where id=1;
    # 更新:直接更新关联表的(book) 外键,修改的值对应被关联表(publish) 主键 如果存在,可以更新成功,反之失败
    mysql>: update book set publish_id=2 where id=4; # 成功
    mysql>: update book set publish_id=1 where id=4; # 失败


    # 删:
    # 删被关联表,关联表会被级联删除
    mysql>: delete from publish where id = 2;

    # 删关联表,被关联表不会发生变化
    mysql>: delete from book where publish_id = 3;

    # 假设:书与作者也是 一对多 关系,一个作者可以出版多本书
    create table book(
      id int primary key auto_increment,
      name varchar(64) not null,
      price decimal(5, 2) default 0,
      publish_id int, # 一对多的外键不能设置唯一
      foreign key(publish_id) references publish(id)
      on update cascade
      on delete cascade

    # 建立与作者 一对多 的外键关联
      author_id int,
      foreign key(author_id) references author(id)
      on update cascade
      on delete cascade
      );

    #多对多

    # 多对多:一定要创建第三张表(关系表),每一个外键值不唯一,看可以多个外键建立联合唯一

    mysql>: drop table author;
    mysql>: drop table author_detail;
    mysql>: drop table book;
    mysql>: drop table publish;

    # 作者(author):id, name, age
    create table author(
      id int primary key auto_increment,
      name varchar(64),
      age int unsigned default 0
      );

    # 出版社(publish):id, name, address
    create table publish(
      id int primary key auto_increment,
      name varchar(64),
      address varchar(256)
      );

    # 作者与出版社关系表:id, author_id, publish_id
    create table author_publish(
      id int primary key auto_increment,
    # 关系表一定有多个外键,关联着多张表
    # 关联作者表
      author_id int,
      foreign key(author_id) references author(id)
      on update cascade
      on delete cascade,
    # 关联出版社表
      publish_id int,
      foreign key(publish_id) references publish(id)
      on update cascade
      on delete cascade,
    # 建立两个字段的联合唯一
      unique(author_id, publish_id)
      );

    # 注:关系表 关联着 作者 和 出版社 两张表,在表结构上 作者 与 出版社 两表键没有任何关系


    # 增:两张被关联表,没有前后关系,但关系表必须在两个表都提供数据后才能进行 关系匹配
    mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
    mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');

    # 操作关系表:
    mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

    # 关系表操作:增、删、改,只要两张被关系表有提供对应的操作数据,都可以操作成功,且对两张被关系表没有影响


    # 操作两张被关系表:
    # 增:不会影响关系表
    mysql>: insert into publish(name, address) values('西交大出版社', '西安');
    # 改:关系表都会级联更新
    mysql>: update publish set id=10 where id=1;
    # 删:关系表都会级联删除
    mysql>: delete from author where name='ruakei';

  • 相关阅读:
    STL
    Makefile
    配置pyqt5环境 for python3.4 on Linux Mint 17.1
    SELinux Policy Macros
    python爬虫
    python常用模块
    python中if __name__ == '__main__':
    Centos 7 .Net core后台守护进程Supervisor配置
    阅读Google Protocol Buffers 指南,整理pb语法
    Google Protocol Buffers 入门
  • 原文地址:https://www.cnblogs.com/wwei4332/p/11581298.html
Copyright © 2020-2023  润新知